Post date: Dec 08, 2017 9:2:56 AM
--Table TRX
CREATE TABLE IRG.ZXC_AGING_CUST1
(
CUSTOMER_ID NUMBER,
INVOICE_NUMBER VARCHAR2(50 BYTE),
INVOICE_DATE DATE,
GL_DATE DATE,
INVOICE_AMOUNT NUMBER,
TAX_AMOUNT NUMBER,
ACCTD_OUTSTANDING_AMOUNT NUMBER,
DUE_DATE DATE,
DAYS_OUTSTANDING NUMBER,
INSTALLMENT_NUMBER NUMBER,
DAYS_LATE_AS_OF NUMBER,
CURRENT_OS_AMT NUMBER,
CASH_RECEIPT_AMT NUMBER,
ADJ_AMT NUMBER,
CREDIT_MEMO_AMT NUMBER,
CREDIT_MEMO_AMT_1 NUMBER,
CUSTOMER_TRX_ID NUMBER
)
--Table RECEIPT
CREATE TABLE IRG.ZXC_AGING_CUST2
(
CUSTOMER_ID NUMBER,
INVOICE_NUMBER VARCHAR2(50 BYTE),
TRX_TYPE VARCHAR2(100 BYTE),
ACCTD_OUTSTANDING_AMOUNT NUMBER,
GL_DATE DATE
)
After that run procedure attached, and then check query below:
SELECT zac.*
,rgld.GL_POSTED_DATE
,zac.customer_id
,zac.invoice_number,
rgld.code_combination_id,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8 account,
gl_flexfields_pkg.get_concat_description
(gcc.chart_of_accounts_id,
gcc.code_combination_id
) description,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,--- chart of account id
2,----- Position of segment
gcc.segment2 ---- Segment value
)account_description, rgld.ORG_ID--, rgld.CUSTOMER_TRX_ID
-- ,zac.*
FROM irg.zxc_aging_cust1 zac,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations gcc,
ar_payment_schedules_all apsa,
ra_cust_trx_types_all t
WHERE 1 = 1
-- AND zac.invoice_number = apsa.trx_number
AND zac.customer_trx_id = apsa.customer_trx_id
AND apsa.customer_id = zac.customer_id
AND apsa.customer_trx_id = rgld.customer_trx_id
AND gcc.code_combination_id = rgld.code_combination_id
AND apsa.CUST_TRX_TYPE_ID = t.CUST_TRX_TYPE_ID
-- AND apsa.org_id = :p_org_id
AND rgld.account_class = 'REC'
and rgld.SET_OF_BOOKS_ID = :p_ledger_id
-- and upper(substr(t.NAME, 1, 12)) != 'INV-INTERORG'
-- and INVOICE_NUMBER = 2165170183
-- and zac.CUSTOMER_ID=138196
-- and rgld.GL_POSTED_DATE is not null
--------------------
--Receipts
/* Formatted on 2016/10/27 11:54 (Formatter Plus v4.8.8) */
SELECT zxcac2.customer_id,
zxcac2.invoice_number receipt_number,
acra.cash_receipt_id,
arpa.code_combination_id,
arpa.acctd_amount_applied_from,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8 account,
gl_flexfields_pkg.get_concat_description
(gcc.chart_of_accounts_id,
gcc.code_combination_id
) description,
gl_flexfields_pkg.get_description_sql
(gcc.chart_of_accounts_id,--- chart of account id
2,----- Position of segment
gcc.segment2 ---- Segment value
)account_description
FROM ar_cash_receipts_all acra,
irg.zxc_aging_cust2 zxcac2,
ar_receivable_applications_all arpa,
gl_code_combinations gcc
WHERE 1 = 1
--and receipt_number='2160003'
AND zxcac2.invoice_number = acra.receipt_number
AND arpa.cash_receipt_id = acra.cash_receipt_id
-- AND acra.org_id = :p_org_id
and acra.SET_OF_BOOKS_ID = :p_ledger_id
and gcc.code_combination_id=arpa.CODE_COMBINATION_ID
-- and zxcac2.CUSTOMER_ID=138196