模拟AR中的Account Overview查询.
费话不多说了,上菜.
SELECT AR.customer_number,
AR.customer_name,
NVL(SUM(AR.entered_dr), 0) entered_dr,
NVL(SUM(AR.entered_cr), 0) entered_cr,
NVL(SUM(AR.accounted_dr), 0) accounted_dr,
NVL(SUM(AR.accounted_cr), 0) accounted_cr,
AR.TYPE
FROM ( --Debit Memo
SELECT ct.bill_to_customer_id,
rc.customer_number,
rc.customer_name,
ct.trx_number original_trx_number,
NULL apply_trx_number,
ctlg.gl_date,
ctlg.account_class,
gc.segment4 ACCOUNT,
ct.attribute1 commercial_number,
ct.ct_reference sales_order,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.amount, 0)),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.amount, 0),
NULL))) entered_dr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.amount, 0),
NULL),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.amount, 0)))) entered_cr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.acctd_amount, 0)),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.acctd_amount, 0),
NULL))) accounted_dr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.acctd_amount, 0),
NULL),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.acctd_amount, 0)))) accounted_cr,
'DM' TYPE
FROM ar.ra_customer_trx_all ct,
ar.ra_cust_trx_line_gl_dist_all ctlg,
gl.gl_code_combinations gc,
apps.ra_customers rc
WHERE ct.set_of_books_id = 63
AND ct.customer_trx_id = ctlg.customer_trx_id
AND gc.code_combination_id = ctlg.code_combination_id
AND ctlg.account_class = 'REC'
AND rc.customer_id = ct.bill_to_customer_id
AND EXISTS
(SELECT 'True'
FROM apps.ra_cust_trx_types_all rctt
WHERE rctt.org_id = 211
AND rctt.end_date IS NULL
AND rctt.TYPE IN ('DM')
AND rctt.cust_trx_type_id = ct.cust_trx_type_id)
AND gc.segment4 = '13110'
AND TO_CHAR(ctlg.gl_date, 'YYYY-MM') BETWEEN '&START_GL_DATE' AND
'&END_GL_DATE'
UNION ALL
--Invoice
SELECT ct.bill_to_customer_id,
rc.customer_number,
rc.customer_name,
ct.trx_number original_trx_number,
NULL apply_trx_number,
ctlg.gl_date,
ctlg.account_class,
gc.segment4 ACCOUNT,
ct.attribute1 commercial_number,
ct.ct_reference sales_order,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.amount, 0)),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.amount, 0),
NULL))) entered_dr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.amount, 0),
NULL),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.amount, 0)))) entered_cr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.acctd_amount, 0)),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.acctd_amount, 0),
NULL))) accounted_dr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.acctd_amount, 0),
NULL),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.acctd_amount, 0)))) accounted_cr,
'INV' TYPE
FROM ar.ra_customer_trx_all ct,
ar.ra_cust_trx_line_gl_dist_all ctlg,
gl.gl_code_combinations gc,
apps.ra_customers rc
WHERE ct.set_of_books_id = 63
AND ct.customer_trx_id = ctlg.customer_trx_id
AND gc.code_combination_id = ctlg.code_combination_id
AND ctlg.account_class = 'REC'
AND rc.customer_id = ct.bill_to_customer_id
AND EXISTS
(SELECT 'True'
FROM apps.ra_cust_trx_types_all rctt
WHERE rctt.org_id = 211
AND rctt.end_date IS NULL
AND rctt.TYPE IN ('INV')
AND rctt.cust_trx_type_id = ct.cust_trx_type_id)
AND gc.segment4 = '13110'
AND TO_CHAR(ctlg.gl_date, 'YYYY-MM') BETWEEN '&START_GL_DATE' AND
'&END_GL_DATE'
UNION ALL
--Credit Memo
SELECT ct.bill_to_customer_id,
rc.customer_number,
rc.customer_name,
ct.trx_number original_trx_number,
NULL apply_trx_number,
ctlg.gl_date,
ctlg.account_class,
gc.segment4 ACCOUNT,
ct.attribute1 commercial_number,
ct.ct_reference sales_order,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.amount, 0)),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.amount, 0),
NULL))) entered_dr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.amount, 0),
NULL),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.amount, 0)))) entered_cr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.acctd_amount, 0)),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.acctd_amount, 0),
NULL))) accounted_dr,
TO_NUMBER(DECODE(ctlg.account_class,
'REC',
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
-NVL(ctlg.acctd_amount, 0),
NULL),
DECODE(SIGN(NVL(ctlg.amount, 0)),
-1,
NULL,
NVL(ctlg.acctd_amount, 0)))) accounted_cr,
'CM' TYPE
FROM ar.ra_customer_trx_all ct,
ar.ra_cust_trx_line_gl_dist_all ctlg,
gl.gl_code_combinations gc,
apps.ra_customers rc
WHERE ct.set_of_books_id = 63
AND ct.customer_trx_id = ctlg.customer_trx_id
AND gc.code_combination_id = ctlg.code_combination_id
AND ctlg.account_class = 'REC'
AND rc.customer_id = ct.bill_to_customer_id
AND EXISTS
(SELECT 'True'
FROM apps.ra_cust_trx_types_all rctt
WHERE rctt.org_id = 211
AND rctt.end_date IS NULL
AND rctt.TYPE IN ('CM')
AND rctt.cust_trx_type_id = ct.cust_trx_type_id)
AND gc.segment4 = '13110'
AND TO_CHAR(ctlg.gl_date, 'YYYY-MM') BETWEEN '&START_GL_DATE' AND
'&END_GL_DATE'
UNION ALL
--Adjustment
SELECT ct.bill_to_customer_id,
rc.customer_number,
rc.customer_name,
ct.trx_number original_trx_number,
adj.adjustment_number,
adj.gl_date,
dis.source_type,
gc.segment4,
NULL,
NULL,
dis.amount_dr entered_dr,
dis.amount_cr entered_cr,
dis.acctd_amount_dr amount_dr,
dis.acctd_amount_cr amount_cr,
'ADJ' TYPE
FROM ar.ar_adjustments_all adj,
ar.ar_distributions_all dis,
ar.ra_customer_trx_all ct,
apps.ra_customers rc,
gl.gl_code_combinations gc
WHERE adj.set_of_books_id = 63
AND dis.source_table = 'ADJ'
AND dis.source_type = 'REC'
AND adj.adjustment_id = dis.source_id
AND ct.customer_trx_id = adj.customer_trx_id
AND ct.bill_to_customer_id = rc.customer_id
AND gc.code_combination_id = dis.code_combination_id
AND gc.segment4 = '13110'
AND TO_CHAR(adj.gl_date, 'YYYY-MM') BETWEEN '&START_GL_DATE' AND
'&END_GL_DATE'
UNION ALL
--Apply for Transactions
SELECT ct.bill_to_customer_id,
rc.customer_number,
rc.customer_name,
ct.trx_number,
cr.receipt_number,
rec.gl_date,
dis.source_type,
gc.segment4,
NULL,
NULL,
dis.amount_dr entered_dr,
dis.amount_cr entered_cr,
dis.acctd_amount_dr amount_dr,
dis.acctd_amount_cr amount_cr,
'APP' TYPE
FROM ar.ar_distributions_all dis,
ar.ar_receivable_applications_all rec,
ar.ra_customer_trx_all ct,
apps.ra_customers rc,
gl.gl_code_combinations gc,
ar.ar_cash_receipts_all cr
WHERE rec.set_of_books_id = 63
AND rec.receivable_application_id = dis.source_id
AND dis.source_table = 'RA'
AND rec.applied_customer_trx_id = ct.customer_trx_id
AND rc.customer_id = ct.bill_to_customer_id
AND gc.code_combination_id = dis.code_combination_id
AND rec.application_type = 'CASH'
AND rec.display = 'Y'
AND cr.cash_receipt_id = rec.cash_receipt_id
AND gc.segment4 = '13110'
AND TO_CHAR(rec.gl_date, 'YYYY-MM') BETWEEN '&START_GL_DATE' AND
'&END_GL_DATE') AR
-- WHERE AR.TYPE IN ('INV', 'DM', 'CM', 'ADJ')
GROUP BY AR.customer_number, AR.customer_name, AR.TYPE
UNION ALL
SELECT REC.customer_number,
REC.customer_name,
NVL(SUM(REC.entered_dr), 0) entered_dr,
NVL(SUM(REC.entered_cr), 0) entered_cr,
NVL(SUM(REC.amount_dr), 0) amount_dr,
NVL(SUM(REC.amount_cr), 0) amount_cr,
'Receipts' TYPE
FROM (SELECT cr.pay_from_customer customer_id,
rc.customer_number,
rc.customer_name,
cr.receipt_number,
cr.status,
ct.trx_number applied_trx_number,
ard.source_table,
ard.source_type,
ard.amount_cr entered_dr,--
ard.amount_dr entered_cr,--
ard.acctd_amount_cr amount_dr,--
ard.acctd_amount_dr amount_cr,--
gc.segment4,
rec.gl_date
FROM apps.ar_cash_receipts_all cr,
apps.ar_receivable_applications_all rec,
apps.ar_distributions_all ard,
apps.ra_customers rc,
apps.ra_customer_trx_all ct,
gl.gl_code_combinations gc
WHERE cr.set_of_books_id = 63
AND cr.cash_receipt_id = rec.cash_receipt_id
AND ard.source_table = 'RA'
AND rec.receivable_application_id = ard.source_id
AND cr.pay_from_customer = rc.customer_id(+)
AND ct.customer_trx_id(+) = rec.applied_customer_trx_id
AND gc.code_combination_id = ard.code_combination_id
AND TO_CHAR(rec.gl_date, 'YYYY-MM') BETWEEN '&START_GL_DATE' AND
'&END_GL_DATE'
UNION ALL
SELECT cr.pay_from_customer customer_id,
rc.customer_number,
rc.customer_name,
cr.receipt_number,
cr.status,
NULL trx_number,
ard.source_table,
ard.source_type,
ard.amount_cr entered_dr, --
ard.amount_dr entered_cr,--
ard.acctd_amount_cr amount_dr, --
ard.acctd_amount_dr amount_cr, --
gc.segment4,
crh.gl_date
FROM apps.ar_cash_receipts_all cr,
apps.ar_cash_receipt_history_all crh,
apps.ar_distributions_all ard,
apps.ra_customers rc,
gl.gl_code_combinations gc
WHERE cr.set_of_books_id = 63
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.cash_receipt_history_id = ard.source_id
AND ard.source_table = 'CRH'
AND cr.pay_from_customer = rc.customer_id(+)
AND gc.code_combination_id = ard.code_combination_id
AND TO_CHAR(crh.gl_date, 'YYYY-MM') BETWEEN '&START_GL_DATE' AND
'&END_GL_DATE') rec
WHERE REC.source_type IN ( 'REMITTANCE','CONFIRMATION' )
--上面的借/贷互换,主要这时取的是银行科目,所以要转换成收款科目.
GROUP BY REC.customer_number, REC.customer_name, 'Receipts'
