SELECT DISTINCT xah.ae_header_id
,xah.description 凭证说明
,xah.accounting_date 核算日期
,xal.ae_line_num
,xl1.meaning 交易方类别
,decode(xal.party_type_code,
'C',
hz.party_number,
po.segment1) third_party_number
,decode(xal.party_type_code,
'C',
hz.party_name,
po.vendor_name) 供应商名称
,decode(xal.party_type_code,
'C',
hs.party_site_number,
ps.vendor_site_code) 地点
,xl2.meaning 帐户类别
,ccid.segment1 || '.' || ccid.segment2 || '.' ||
ccid.segment3 || '.' || ccid.segment4 || '.' ||
ccid.segment5 || '.' || ccid.segment6 || '.' ||
ccid.segment7 || '.' || ccid.segment8 || '.' ||
ccid.segment9 帐户
,xla_oa_functions_pkg.get_ccid_description(ccid.chart_of_accounts_id,
ccid.code_combination_id) 帐户描述
,xal.accounted_dr 借方
,xal.accounted_cr 贷方
,decode(xdl.source_distribution_type,
'AP_INV_DIST',
'发票',
'付款') 来源类型
,decode(xdl.source_distribution_type,
'AP_INV_DIST',
(SELECT ai.invoice_num
FROM ap_invoices_all ai
,ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id =
xdl.source_distribution_id_num_1
AND aid.invoice_id = ai.invoice_id),
NULL) 发票编号
,decode(xdl.source_distribution_type,
'AP_INV_DIST',
(SELECT ai.invoice_date
FROM ap_invoices_all ai
,ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id =
xdl.source_distribution_id_num_1
AND aid.invoice_id = ai.invoice_id),
NULL) 发票日期
,decode(xdl.source_distribution_type,
'AP_PMT_DIST',
(SELECT ai.invoice_num
FROM ap_invoices_all ai
WHERE ai.invoice_id =
xdl.applied_to_source_id_num_1),
NULL) 发票编号
,decode(xdl.source_distribution_type,
'AP_PMT_DIST',
(SELECT ai.invoice_date
FROM ap_invoices_all ai
WHERE ai.invoice_id =
xdl.applied_to_source_id_num_1),
NULL) 发票日期
FROM xla_ae_lines xal
,xla_ae_headers xah
,xla_distribution_links xdl
,ap_suppliers po
,ap_supplier_sites_all ps
,hz_parties hz
,hz_cust_accounts hca
,hz_party_sites hs
,hz_cust_acct_sites_all hcas
,hz_cust_site_uses_all hczu
,xla_lookups xl1
,xla_lookups xl2
,gl_code_combinations ccid
,(SELECT ccid.code_combination_id
,sav.id_flex_num chart_of_accounts_id
,decode(s.segment_num,
1,
ccid.segment1,
2,
ccid.segment2,
3,
ccid.segment3,
4,
ccid.segment4,
5,
ccid.segment5,
6,
ccid.segment6,
7,
ccid.segment7,
8,
ccid.segment8,
9,
ccid.segment9,
10,
ccid.segment10,
11,
ccid.segment11,
12,
ccid.segment12,
13,
ccid.segment13,
14,
ccid.segment14,
15,
ccid.segment15) account_segment_value
FROM fnd_id_flex_segments s
,fnd_segment_attribute_values sav
,gl_code_combinations ccid
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.enabled_flag = 'Y'
AND sav.application_column_name = s.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = s.id_flex_code
AND sav.id_flex_num = s.id_flex_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = 'GL_ACCOUNT'
AND ccid.chart_of_accounts_id = s.id_flex_num) account_segments
WHERE xah.ae_header_id = xal.ae_header_id
AND xah.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND po.vendor_id(+) = xal.party_id
AND ps.vendor_site_id(+) = xal.party_site_id
AND hz.party_id(+) = hca.party_id
AND hca.cust_account_id(+) = xal.party_id
AND hs.party_site_id(+) = hcas.party_site_id
AND hcas.cust_acct_site_id(+) = hczu.cust_acct_site_id
AND hczu.site_use_id(+) = xal.party_site_id
AND xl1.lookup_code(+) = xal.party_type_code
AND xl1.lookup_type(+) = 'XLA_PARTY_TYPE'
AND xl2.lookup_type = 'XLA_ACCOUNTING_CLASS'
AND xl2.lookup_code = xal.accounting_class_code
AND ccid.code_combination_id(+) = xal.code_combination_id
AND account_segments.code_combination_id(+) = ccid.code_combination_id
AND xal.application_id = 200
ORDER BY xah.ae_header_id
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10359218/viewspace-753055/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10359218/viewspace-753055/