【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'
本文来自博客园,作者:Iven_lin,转载请注明原文链接:https://www.cnblogs.com/ivenlin/p/18084414