【OracleEBS】AR 核銷

--核銷
SELECT hou.NAME org_name,
rct.trx_number,
rct.trx_date,
hca.account_number customer,
hpt.party_name customer_name,
'ARD' source_table,
ard.line_id source_id,
ard.source_type,
ara.code_combination_id,
gcc_ori.code_combination_id orig_ccid,
gcc_ori.concatenated_segments orig_acct,
ard.amount_dr entered_dr,
ard.amount_cr entered_cr,
(SELECT gcc_new.code_combination_id
FROM gl_code_combinations_kfv gcc_new
WHERE gcc_new.segment1 = gcc_ori.segment1
AND gcc_new.segment2 = gcc_ori.segment2
AND gcc_new.segment3 = gcc_ori.segment3
AND gcc_new.segment4 = (CASE
WHEN ard.source_type IN ('REC', 'UNAPP') THEN
gcc_rec.segment4
END)
AND gcc_new.segment5 = gcc_ori.segment5
AND gcc_new.chart_of_accounts_id = gcc_ori.chart_of_accounts_id) new_ccid,
(CASE
WHEN ard.source_type IN ('REC', 'UNAPP') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' ||
gcc_ori.segment3 || '.' || gcc_rec.segment4 || '.' ||
gcc_ori.segment5
END) new_acct,
gcc_rec.segment4 rec_acc,
gcc_rev.segment4 rev_acc
FROM ra_customer_trx_all rct,
hr_operating_units hou,
hz_cust_accounts hca,
hz_parties hpt,
hz_cust_site_uses_all csu,
gl_code_combinations_kfv gcc_rec,
gl_code_combinations_kfv gcc_rev,
ar_receivable_applications_all ara,
ar_distributions_all ard,
gl_code_combinations_kfv gcc_ori
WHERE rct.org_id = hou.organization_id
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hpt.party_id
AND rct.bill_to_site_use_id = csu.site_use_id
AND csu.gl_id_rec = gcc_rec.code_combination_id(+)
AND csu.gl_id_rev = gcc_rev.code_combination_id(+)
AND ara.customer_trx_id = rct.customer_trx_id
AND ara.application_type = 'CM'
AND ard.source_table = 'RA'
AND ard.source_id = ara.receivable_application_id
AND ard.code_combination_id = gcc_ori.code_combination_id
AND hpt.party_name = 'xxxxxx'