select cux_bq_common.get_lookup_meaning('PAYMENT TYPE',aca.payment_type_flag) as "类型",hou.name as "业务实体",decode(aca.party_id,'','********************************************************************************',hzp.party_name) as "贸易伙伴",decode(aca.vendor_id,'******************************',null),pv.segment1) as "供应商编号",decode(aca.vendor_site_id,'**************',decode(sign(aca.vendor_site_id),-1,null,pvs.vendor_site_code)) as "供应商地点",hzl.address1 || ' ' || hzl.address2 || ' ' || hzl.city || ' ' ||
hzl.state || ' ' || hzl.postal_code) as "贸易伙伴地址",ft.territory_short_name as "收款人国家(地区)",aca.check_date as "付款日期",aca.amount as "付款额",decode(aca.paycard_reference_id,cba.bank_account_name,aca.bank_account_name) as "银行帐户",cba.currency_code as "帐户币种",aca.currency_code as "付款币种",iby1.payment_method_name as "付款方法",pd.payment_document_name as "付款单据",aca.check_number as "单据编号",iby2.payment_profile_name as "付款处理配置文件",aca.remit_to_supplier_name as "收款方供应商名称",aca.remit_to_supplier_site as "收款方供应商地点",ibybnk.masked_bank_account_num as "汇入帐户",aca.void_check_number as "撤销付款",aca.address_line1 as "地址行1",aca.address_line2 as "地址行2",aca.address_line3 as "地址行3",aca.city as "城市",aca.state as "省(州)",aca.zip as "邮政编码",aca.province as "省",aca.county as "县",fdsc.name as "单据类别",nvl(aca.doc_sequence_value,aca.check_voucher_num) as "凭证编号",aca.future_pay_due_date as "到期日",aca.checkrun_name as "付款处理请求",aca.anticipated_value_date as "预计值日期",cux_bq_common.get_lookup_meaning('CHECK STATE',aca.status_lookup_code) as "状态",gdct.user_conversion_type as "汇率类型",aca.exchange_date as "汇率日期",aca.exchange_rate as "汇率",aca.base_amount as "本位币金额",gdct1.user_conversion_type as "到期汇率类型",aca.maturity_exchange_date as "到期汇率日期",aca.maturity_exchange_rate as "到期汇率",aca.paycard_authorization_number as "授权编号",cux_bq_common.get_lookup_meaning('CUX3_AP_EFLOW_EXAMINE_TYPE',aca.attribute13) as "预检/确认",aca.attribute9 as "Inter-Company",aca.attribute8 as "Cash Flow Item",ai.invoice_num as "编号",ai.invoice_date as "日期",ai.invoice_amount as "金额",ai.gl_date as "GL日期",ai.amount_paid as "付款金额",ai.description as "摘要",cux_bq_common.get_lookup_meaning('POSTING STATUS',ap_checks_pkg.get_posting_status(aca.check_id)) as "会计"
from ap_checks_all aca,hr_operating_units hou,hz_parties hzp,hz_party_sites hps,ap_suppliers pv,ap_supplier_sites_all pvs,hz_locations hzl,fnd_territories_tl ft,ce_bank_accounts cba,ce_bank_acct_uses_all cbau,iby_payment_methods_vl iby1,iby_payment_profiles iby2,ce_payment_documents pd,fnd_doc_sequence_categories fdsc,gl_daily_conversion_types gdct,gl_daily_conversion_types gdct1,ap_invoice_payments_all aip,ap_invoices_all ai,iby_ext_bank_accounts ibybnk
where 1 = 1
--and aca.check_id = 316500
and aca.org_id = hou.organization_id
and aca.party_id = hzp.party_id(+)
and aca.vendor_id = pv.vendor_id(+)
and aca.vendor_site_id = pvs.vendor_site_id(+)
and aca.party_site_id = hps.party_site_id(+)
and hps.location_id = hzl.location_id(+)
and aca.country = ft.territory_code(+)
and ft.language(+) = 'ZHS'
and aca.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
and cbau.bank_account_id = cba.bank_account_id(+)
and cbau.org_id(+) = aca.org_id
and iby1.payment_method_code(+) = aca.payment_method_code
and aca.payment_document_id = pd.payment_document_id(+)
and iby2.payment_profile_id(+) = aca.payment_profile_id
and fdsc.application_id(+) = 200
and aca.doc_category_code = fdsc.code(+)
and aca.exchange_rate_type = gdct.conversion_type(+)
and aca.maturity_exchange_rate_type = gdct1.conversion_type(+)
and aip.invoice_id = ai.invoice_id(+)
and aip.check_id(+) = aca.check_id
and ibybnk.ext_bank_account_id(+) = aca.external_bank_account_id
--筛选条件
and aca.status_lookup_code = 'ISSUED';
