----应付模块对帐语句
----将指定日期范围内的所有的应付发票和付款分录全部按供应商地点取出
----可按科目或其他方式对帐
----本月发票
select pv.vendor_name,
pv.segment1,
pvs.vendor_site_code,
ai.invoice_num,
ai.gl_date,
ai.invoice_currency_code,
ai.invoice_type_lookup_code,
aae.event_type_code,
aae.source_table,
aal.ae_line_type_code,
gcc.concatenated_segments,
gcc.segment2,
gcc.segment3,
aal.entered_dr,
aal.entered_cr,
aal.accounted_dr,
aal.accounted_cr,
abs(nvl(aal.accounted_dr,0)- nvl(aal.accounted_cr,0))
from ap_invoices_all ai,
ap_accounting_events_all aae,
ap_ae_headers_all aah,
ap_ae_lines_all aal,
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations_kfv gcc
where ai.INVOICE_ID = aae.SOURCE_ID
and aae.SOURCE_TABLE = 'AP_INVOICES' --AP_CHECKS
and aae.accounting_event_id = aah.accounting_event_id
and aah.ae_header_id = aal.ae_header_id
and ai.vendor_id = pv.vendor_id
and ai.vendor_site_id = pvs.vendor_site_id
and aal.code_combination_id = gcc.code_combination_id
and ai.gl_date >= to_date('2007-08-01','yyyy-mm-dd')
and ai.org_id = 102
union all
----本月付款
select pv.vendor_name,
pv.segment1,
pvs.vendor_site_code,
to_char(ac.check_number),
ac.check_date,
ac.currency_code,
ac.bank_account_name, --ai.invoice_type_lookup_code,
aae.event_type_code,
aae.source_table,
aal.ae_line_type_code,
gcc.concatenated_segments,
gcc.segment2,
gcc.segment3,
aal.entered_dr,
aal.entered_cr,
aal.accounted_dr,
aal.accounted_cr,
abs(nvl(aal.accounted_dr,0)- nvl(aal.accounted_cr,0))
from ap_checks_all ac,
ap_accounting_events_all aae,
ap_ae_headers_all aah,
ap_ae_lines_all aal,
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations_kfv gcc
where ac.check_id = aae.SOURCE_ID
and aae.SOURCE_TABLE = 'AP_CHECKS' --AP_CHECKS
and aae.accounting_event_id = aah.accounting_event_id
and aah.ae_header_id = aal.ae_header_id
and ac.vendor_id = pv.vendor_id
and ac.vendor_site_id = pvs.vendor_site_id
and aal.code_combination_id = gcc.code_combination_id
and ac.check_date >= to_date('2007-08-01','yyyy-mm-dd')
and ac.org_id = 102
--收款分录
SELECT hou.NAME org_name,
acr.receipt_number,
acr.receipt_date,
hca.account_number customer,
hpt.party_name customer_name,
'ARD' source_table,
ard.line_id source_id,
ard.source_type,
crh.account_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 ar_cash_receipts_all acr,
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_cash_receipt_history_all crh,
ar_distributions_all ard,
gl_code_combinations_kfv gcc_ori
WHERE acr.org_id = hou.organization_id
AND acr.pay_from_customer = hca.cust_account_id
AND hca.party_id = hpt.party_id
AND acr.customer_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 acr.cash_receipt_id = crh.cash_receipt_id
AND ard.source_table = 'CRH'
AND ard.source_id = crh.cash_receipt_history_id
AND ard.code_combination_id = gcc_ori.code_combination_id
AND hpt.party_name = 'xxxxxx'
--现金核销分录
SELECT hou.NAME org_name,
acr.receipt_number,
acr.receipt_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 ar_cash_receipts_all acr,
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 acr.org_id = hou.organization_id
AND acr.pay_from_customer = hca.cust_account_id
AND hca.party_id = hpt.party_id
AND acr.customer_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 acr.cash_receipt_id = ara.cash_receipt_id
AND ara.application_type = 'CASH'
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'
--收款历史
SELECT hou.NAME org_name,
acr.receipt_number,
acr.receipt_date,
hca.account_number customer,
hpt.party_name customer_name,
'CRH' source_table,
crh.cash_receipt_history_id source_id,
--ard.source_type,
crh.account_code_combination_id orig_ccid, --crh.bank_charge_account_ccid
gcc_ori.concatenated_segments orig_acct,
crh.amount,
(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 crh.status 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 crh.status 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 ar_cash_receipts_all acr,
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_cash_receipt_history_all crh,
gl_code_combinations_kfv gcc_ori
WHERE acr.org_id = hou.organization_id
AND acr.pay_from_customer = hca.cust_account_id
AND hca.party_id = hpt.party_id
AND acr.customer_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 acr.cash_receipt_id = crh.cash_receipt_id
AND crh.account_code_combination_id = gcc_ori.code_combination_id
AND hpt.party_name = 'xxxxxx'
--现金核销
SELECT hou.NAME org_name,
acr.receipt_number,
acr.receipt_date,
hca.account_number customer,
hpt.party_name customer_name,
'RA' source_table,
ara.receivable_application_id source_id,
ara.code_combination_id orig_ccid,
gcc_ori.concatenated_segments orig_acct,
ara.amount_applied,
(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 ara.status IN ('APP', '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 ara.status IN ('APP', '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 ar_cash_receipts_all acr,
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,
gl_code_combinations_kfv gcc_ori
WHERE acr.org_id = hou.organization_id
AND acr.pay_from_customer = hca.cust_account_id
AND hca.party_id = hpt.party_id
AND acr.customer_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 acr.cash_receipt_id = ara.cash_receipt_id
AND ara.code_combination_id = gcc_ori.code_combination_id
AND hpt.party_name = 'xxxxxx';
--付款分录
SELECT hou.NAME org_name,
ac.check_number,
ac.check_date,
pv.segment1 vendor,
pv.vendor_name,
'AEL' source_table,
ael.ae_line_id source_id,
ael.ae_line_type_code line_type, --ar_lookups.lookup_type = 'AUTOGL_TYPE'
gcc_ori.code_combination_id orig_ccid,
gcc_ori.concatenated_segments orig_acct,
ael.entered_dr,
ael.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 ael.ae_line_type_code IN ('LIABILITY', 'AP ACCRUAL') THEN gcc_pay.segment4 WHEN
ael.ae_line_type_code IN ('PREPAY') THEN gcc_pre.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 ael.ae_line_type_code IN ('LIABILITY', 'AP ACCRUAL') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_pay.segment4 || '.' ||
gcc_ori.segment5
WHEN ael.ae_line_type_code IN ('PREPAY') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_pre.segment4 || '.' ||
gcc_ori.segment5
END) new_acct,
gcc_pay.segment4 pay_acc,
gcc_pre.segment4 pre_acc
FROM ap_checks_all ac,
hr_operating_units hou,
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations_kfv gcc_pay,
gl_code_combinations_kfv gcc_pre,
ap_accounting_events_all ae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
gl_code_combinations_kfv gcc_ori
WHERE ac.org_id = hou.organization_id
AND ac.vendor_id = pv.vendor_id
AND ac.vendor_site_id = pvs.vendor_site_id
AND pvs.accts_pay_code_combination_id = gcc_pay.code_combination_id(+)
AND pvs.prepay_code_combination_id = gcc_pre.code_combination_id(+)
AND ac.check_id = ae.source_id
AND ae.source_table = 'AP_CHECKS'
AND ae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND ael.code_combination_id = gcc_ori.code_combination_id
AND pv.vendor_name = 'xxxxxx'
--发票分录
SELECT hou.NAME org_name,
ai.invoice_num,
ai.invoice_date,
pv.segment1 vendor,
pv.vendor_name,
'AEL' source_table,
ael.ae_line_id source_id,
ael.ae_line_type_code line_type, --ar_lookups.lookup_type = 'AUTOGL_TYPE'
gcc_ori.code_combination_id orig_ccid,
gcc_ori.concatenated_segments orig_acct,
ael.entered_dr,
ael.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 ael.ae_line_type_code IN ('LIABILITY','AP ACCRUAL') THEN gcc_pay.segment4 WHEN
ael.ae_line_type_code IN ('PREPAY') THEN gcc_pre.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 ael.ae_line_type_code IN ('LIABILITY','AP ACCRUAL') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_pay.segment4 || '.' ||
gcc_ori.segment5
WHEN ael.ae_line_type_code IN ('PREPAY') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_pre.segment4 || '.' ||
gcc_ori.segment5
END) new_acct,
gcc_pay.segment4 pay_acc,
gcc_pre.segment4 pre_acc
FROM ap_invoices_all ai,
hr_operating_units hou,
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations_kfv gcc_pay,
gl_code_combinations_kfv gcc_pre,
ap_accounting_events_all ae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
gl_code_combinations_kfv gcc_ori
WHERE ai.org_id = hou.organization_id
AND ai.vendor_id = pv.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND pvs.accts_pay_code_combination_id = gcc_pay.code_combination_id(+)
AND pvs.prepay_code_combination_id = gcc_pre.code_combination_id(+)
AND ai.invoice_id = ae.source_id
AND ae.source_table = 'AP_INVOICES'
AND ae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND ael.code_combination_id = gcc_ori.code_combination_id
AND pv.vendor_name = 'xxxxxx';
--发票分配
SELECT hou.NAME org_name,
ai.invoice_num,
ai.invoice_date,
pv.segment1 vendor,
pv.vendor_name,
'AID' source_table,
aid.invoice_distribution_id source_id,
aid.line_type_lookup_code line_type, --ar_lookups.lookup_type = 'AUTOGL_TYPE'
gcc_ori.code_combination_id orig_ccid,
gcc_ori.concatenated_segments orig_acct,
aid.amount,
(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 aid.line_type_lookup_code IN ('ITEM') THEN gcc_pay.segment4 WHEN
aid.line_type_lookup_code IN ('PREPAY') THEN gcc_pre.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 aid.line_type_lookup_code IN ('ITEM') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_pay.segment4 || '.' ||
gcc_ori.segment5
WHEN aid.line_type_lookup_code IN ('PREPAY') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_pre.segment4 || '.' ||
gcc_ori.segment5
END) new_acct,
gcc_pay.segment4 pay_acc,
gcc_pre.segment4 pre_acc
FROM ap_invoices_all ai,
hr_operating_units hou,
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations_kfv gcc_pay,
gl_code_combinations_kfv gcc_pre,
ap_invoice_distributions_all aid,
gl_code_combinations_kfv gcc_ori
WHERE ai.org_id = hou.organization_id
AND ai.vendor_id = pv.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND pvs.accts_pay_code_combination_id = gcc_pay.code_combination_id(+)
AND pvs.prepay_code_combination_id = gcc_pre.code_combination_id(+)
AND ai.invoice_id = aid.invoice_id
AND aid.dist_code_combination_id = gcc_ori.code_combination_id
AND pv.vendor_name = 'xxxxxx'
--核销
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'
--调整
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,
adj.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 adj.adjustment_type NOT IN ('REC') 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 adj.adjustment_type NOT IN ('REC') 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_adjustments_all adj,
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 rct.customer_trx_id = adj.customer_trx_id
AND ard.source_table = 'ADJ'
AND ard.source_id = adj.adjustment_id
AND ard.code_combination_id = gcc_ori.code_combination_id
AND hpt.party_name = 'xxxxxx';
--事务处理及分录
SELECT hou.NAME org_name,
rct.trx_number,
rct.trx_date,
hca.account_number customer,
hpt.party_name customer_name,
'LGD' source_table,
lgd.cust_trx_line_gl_dist_id source_id,
lgd.account_class acct_class, --ar_lookups.lookup_type = 'AUTOGL_TYPE'
gcc_ori.code_combination_id orig_ccid,
gcc_ori.concatenated_segments orig_acct,
lgd.amount,
(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 lgd.account_class IN ('REC') THEN gcc_rec.segment4 WHEN
lgd.account_class IN ('REV') THEN gcc_rev.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 lgd.account_class IN ('REC') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rec.segment4 || '.' ||
gcc_ori.segment5
WHEN lgd.account_class IN ('REV') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rev.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,
ra_cust_trx_line_gl_dist_all lgd,
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 rct.customer_trx_id = lgd.customer_trx_id
--AND lgd.account_set_flag = 'N'
AND lgd.code_combination_id = gcc_ori.code_combination_id
AND hpt.party_name = 'xxxxxx'
--调整
SELECT hou.NAME org_name,
rct.trx_number,
rct.trx_date,
hca.account_number customer,
hpt.party_name customer_name,
'ADJ' source_table,
adj.adjustment_id source_id,
adj.account_class acct_class, --ar_lookups.lookup_type = 'AUTOGL_TYPE'
gcc_ori.code_combination_id orig_ccid,
gcc_ori.concatenated_segments orig_acct,
adj.amount,
(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 lgd.account_class IN ('REC') THEN gcc_rec.segment4 WHEN
lgd.account_class IN ('REV') THEN gcc_rev.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 lgd.account_class IN ('REC') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rec.segment4 || '.' ||
gcc_ori.segment5
WHEN lgd.account_class IN ('REV') THEN
gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rev.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_adjustments_all adj,
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 rct.customer_trx_id = adj.customer_trx_id
AND adj.code_combination_id = gcc_ori.code_combination_id
AND hpt.party_name = 'xxxxxx';
327
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
