Customer outstanding amount.
The table ar_payment_Schedules_all gives you the outstanding information as on date. For eg. if the system date is 13th April and if you query the ar_payment_schedules_all table, the amount_due_remaining column will give you the open amount as on that date.
However if you want the oustanding as on some previous date, lets say as on 31st March, in that case you have to rollback all the applications that would have occured from 1st april to 13th april.
Find below the script that I used to get the oustanding as on any previous date. Pls. note that I am using a temp table here to populate the details.
CREATE TABLE ZXC_AGING_CUST1
(
CUSTOMER_ID NUMBER,
INVOICE_NUMBER VARCHAR2 (20),
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
);
CREATE TABLE ZXC_AGING_CUST2
(
CUSTOMER_ID NUMBER,
INVOICE_NUMBER VARCHAR2 (20),
TRX_TYPE VARCHAR2 (100),
ACCTD_OUTSTANDING_AMOUNT NUMBER,
GL_DATE DATE
);
DECLARE
v_cash_receipt NUMBER;
v_adjustment NUMBER;
v_credit_memo NUMBER;
v_as_of_outstanding NUMBER;
v_cash_receipt_acctd NUMBER;
v_adjustment_acctd NUMBER;
v_credit_memo_acctd NUMBER;
v_credit_memo_acctd_1 NUMBER;
v_as_of_outstanding_acctd NUMBER;
p_as_of_date DATE;
CURSOR cs_get_trx (
p_as_of_date1 IN DATE)
IS
SELECT ps.customer_id CUST_ACCOUNT_ID,
trx.creation_date INV_CREATION_DATE,
ps.trx_number INVOICE_NUMBER,
trx.trx_date INVOICE_DATE,
ps.gl_date GL_DATE,
NVL (ps.amount_due_original, 0) INVOICE_AMOUNT,
NVL (ps.tax_original, 0) TAX_AMOUNT,
NVL (ps.acctd_amount_due_remaining, 0) ACCTD_OUTSTANDING_AMOUNT,
ps.due_date,
CEIL (SYSDATE - ps.due_date) DAYS_OUTSTANDING,
ps.payment_schedule_id,
ps.number_of_due_dates INSTALLMENT_NUMBER,
trx.customer_trx_id,
CEIL (p_as_of_date1 - ps.due_date) DAYS_LATE_AS_OF
FROM ra_customer_trx TRX, ar_payment_schedules PS
WHERE trx.customer_trx_id = ps.customer_trx_id
AND ps.gl_date <= p_as_of_date1
AND ps.gl_date_closed > p_as_of_date1;
CURSOR cs_get_receipt (
p_as_of_date2 IN DATE)
IS
SELECT ps.customer_id CUST_ACCOUNT_ID,
ps.payment_schedule_id,
CEIL (p_as_of_date - ps.GL_DATE) days_late_as_of_r,
ps.gl_date,
cr.receipt_number,
app.cash_receipt_id,
SUM (app.acctd_amount_applied_from) ACCTD_AMOUNT_APPLIED
FROM ar_receivable_applications app,
ar_cash_receipts cr,
ar_payment_schedules ps
WHERE app.cash_receipt_id = cr.cash_receipt_id
AND app.payment_schedule_id = ps.payment_schedule_id
AND app.status IN ('ACC', 'UNAPP', 'UNID', 'OTHER ACC')
AND NVL (app.confirmed_flag, 'Y') = 'Y'
AND app.gl_date <= p_as_of_date2
AND ps.gl_date <= p_as_of_date2
AND ps.gl_date_closed > p_as_of_date2
AND ( ( app.reversal_gl_date IS NOT NULL
AND ps.gl_date <= p_as_of_date2)
OR app.reversal_gl_date IS NULL)
GROUP BY ps.customer_id,
cr.receipt_number,
app.cash_receipt_id,
ps.payment_schedule_id,
ps.gl_date
HAVING SUM (app.acctd_amount_applied_from) <> 0;
BEGIN
BEGIN
mo_global.set_policy_context ('S', '69');
END;
DELETE zxc_aging_cust1;
p_as_of_date := TO_DATE ('&Enter_as_of_date', 'DD-MON-RRRR');
FOR invoice IN cs_get_trx (p_as_of_date)
LOOP
/* cash applied after p_as_of_date */
SELECT NVL (
SUM (
NVL (acctd_amount_applied_to, 0.0)
+ NVL (acctd_earned_discount_taken, 0.0)
+ NVL (acctd_unearned_discount_taken, 0.0)),
0.0)
INTO v_cash_receipt_acctd
FROM ar_receivable_applications
WHERE TRUNC (gl_date) > p_as_of_date
AND status || '' = 'APP'
AND NVL (confirmed_flag, 'Y') = 'Y'
AND applied_payment_schedule_id = invoice.payment_schedule_id
AND application_type LIKE 'CASH%';
/* adjustments applied after p_as_of_date */
SELECT NVL (SUM (ar_adjustments.acctd_amount), 0.0)
INTO v_adjustment_acctd
FROM ar_adjustments
WHERE TRUNC (gl_date) > p_as_of_date
AND status = 'A'
AND payment_schedule_id = invoice.payment_schedule_id;
/* invoice credited after p_as_of_date */
SELECT NVL (SUM (NVL (acctd_amount_applied_to, 0.0)), 0.0)
INTO v_credit_memo_acctd
FROM ar_receivable_applications
WHERE applied_payment_schedule_id = invoice.payment_schedule_id
AND NVL (confirmed_flag, 'Y') = 'Y'
AND status || '' = 'APP'
AND TRUNC (gl_date) > p_as_of_date
AND application_type LIKE 'CM%';
/* credit memo applied after p_as_of_date */
SELECT NVL (SUM (NVL (acctd_amount_applied_to, 0.0)), 0.0)
INTO v_credit_memo_acctd_1
FROM ar_receivable_applications
WHERE payment_schedule_id = invoice.payment_schedule_id
AND NVL (confirmed_flag, 'Y') = 'Y'
AND status || '' = 'APP'
AND TRUNC (gl_date) > p_as_of_date
AND application_type LIKE 'CM%';
/* calculate actual outstanding amount */
v_as_of_outstanding_acctd :=
invoice.acctd_outstanding_amount
+ v_cash_receipt_acctd
- v_adjustment_acctd
+ v_credit_memo_acctd
- v_credit_memo_acctd_1;
INSERT INTO zxc_aging_cust1 (customer_id,
invoice_number,
invoice_date,
gl_date,
invoice_amount,
tax_amount,
acctd_outstanding_amount,
due_date,
days_outstanding,
installment_number,
days_late_as_of,
current_os_amt,
cash_receipt_amt,
adj_amt,
credit_memo_amt,
credit_memo_amt_1)
VALUES (invoice.cust_account_id,
invoice.invoice_number,
invoice.invoice_date,
invoice.gl_date,
invoice.invoice_amount,
invoice.tax_amount,
v_as_of_outstanding_acctd,
invoice.due_date,
invoice.days_outstanding,
invoice.installment_number,
invoice.days_late_as_of,
invoice.acctd_outstanding_amount,
v_cash_receipt_acctd,
v_adjustment_acctd,
v_credit_memo_acctd,
v_credit_memo_acctd_1);
END LOOP;
COMMIT;
FOR receipt IN cs_get_receipt (p_as_of_date)
LOOP
INSERT INTO zxc_aging_cust2 (customer_id,
invoice_number,
trx_type,
acctd_outstanding_amount,
gl_date)
VALUES (receipt.cust_account_id,
receipt.receipt_number,
'RECEIPT',
-1 * receipt.acctd_amount_applied,
receipt.gl_date);
END LOOP;
COMMIT;
END;
The table ar_payment_Schedules_all gives you the outstanding information as on date. For eg. if the system date is 13th April and if you query the ar_payment_schedules_all table, the amount_due_remaining column will give you the open amount as on that date.
However if you want the oustanding as on some previous date, lets say as on 31st March, in that case you have to rollback all the applications that would have occured from 1st april to 13th april.
Find below the script that I used to get the oustanding as on any previous date. Pls. note that I am using a temp table here to populate the details.
CREATE TABLE ZXC_AGING_CUST1
(
CUSTOMER_ID NUMBER,
INVOICE_NUMBER VARCHAR2 (20),
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
);
CREATE TABLE ZXC_AGING_CUST2
(
CUSTOMER_ID NUMBER,
INVOICE_NUMBER VARCHAR2 (20),
TRX_TYPE VARCHAR2 (100),
ACCTD_OUTSTANDING_AMOUNT NUMBER,
GL_DATE DATE
);
DECLARE
v_cash_receipt NUMBER;
v_adjustment NUMBER;
v_credit_memo NUMBER;
v_as_of_outstanding NUMBER;
v_cash_receipt_acctd NUMBER;
v_adjustment_acctd NUMBER;
v_credit_memo_acctd NUMBER;
v_credit_memo_acctd_1 NUMBER;
v_as_of_outstanding_acctd NUMBER;
p_as_of_date DATE;
CURSOR cs_get_trx (
p_as_of_date1 IN DATE)
IS
SELECT ps.customer_id CUST_ACCOUNT_ID,
trx.creation_date INV_CREATION_DATE,
ps.trx_number INVOICE_NUMBER,
trx.trx_date INVOICE_DATE,
ps.gl_date GL_DATE,
NVL (ps.amount_due_original, 0) INVOICE_AMOUNT,
NVL (ps.tax_original, 0) TAX_AMOUNT,
NVL (ps.acctd_amount_due_remaining, 0) ACCTD_OUTSTANDING_AMOUNT,
ps.due_date,
CEIL (SYSDATE - ps.due_date) DAYS_OUTSTANDING,
ps.payment_schedule_id,
ps.number_of_due_dates INSTALLMENT_NUMBER,
trx.customer_trx_id,
CEIL (p_as_of_date1 - ps.due_date) DAYS_LATE_AS_OF
FROM ra_customer_trx TRX, ar_payment_schedules PS
WHERE trx.customer_trx_id = ps.customer_trx_id
AND ps.gl_date <= p_as_of_date1
AND ps.gl_date_closed > p_as_of_date1;
CURSOR cs_get_receipt (
p_as_of_date2 IN DATE)
IS
SELECT ps.customer_id CUST_ACCOUNT_ID,
ps.payment_schedule_id,
CEIL (p_as_of_date - ps.GL_DATE) days_late_as_of_r,
ps.gl_date,
cr.receipt_number,
app.cash_receipt_id,
SUM (app.acctd_amount_applied_from) ACCTD_AMOUNT_APPLIED
FROM ar_receivable_applications app,
ar_cash_receipts cr,
ar_payment_schedules ps
WHERE app.cash_receipt_id = cr.cash_receipt_id
AND app.payment_schedule_id = ps.payment_schedule_id
AND app.status IN ('ACC', 'UNAPP', 'UNID', 'OTHER ACC')
AND NVL (app.confirmed_flag, 'Y') = 'Y'
AND app.gl_date <= p_as_of_date2
AND ps.gl_date <= p_as_of_date2
AND ps.gl_date_closed > p_as_of_date2
AND ( ( app.reversal_gl_date IS NOT NULL
AND ps.gl_date <= p_as_of_date2)
OR app.reversal_gl_date IS NULL)
GROUP BY ps.customer_id,
cr.receipt_number,
app.cash_receipt_id,
ps.payment_schedule_id,
ps.gl_date
HAVING SUM (app.acctd_amount_applied_from) <> 0;
BEGIN
BEGIN
mo_global.set_policy_context ('S', '69');
END;
DELETE zxc_aging_cust1;
p_as_of_date := TO_DATE ('&Enter_as_of_date', 'DD-MON-RRRR');
FOR invoice IN cs_get_trx (p_as_of_date)
LOOP
/* cash applied after p_as_of_date */
SELECT NVL (
SUM (
NVL (acctd_amount_applied_to, 0.0)
+ NVL (acctd_earned_discount_taken, 0.0)
+ NVL (acctd_unearned_discount_taken, 0.0)),
0.0)
INTO v_cash_receipt_acctd
FROM ar_receivable_applications
WHERE TRUNC (gl_date) > p_as_of_date
AND status || '' = 'APP'
AND NVL (confirmed_flag, 'Y') = 'Y'
AND applied_payment_schedule_id = invoice.payment_schedule_id
AND application_type LIKE 'CASH%';
/* adjustments applied after p_as_of_date */
SELECT NVL (SUM (ar_adjustments.acctd_amount), 0.0)
INTO v_adjustment_acctd
FROM ar_adjustments
WHERE TRUNC (gl_date) > p_as_of_date
AND status = 'A'
AND payment_schedule_id = invoice.payment_schedule_id;
/* invoice credited after p_as_of_date */
SELECT NVL (SUM (NVL (acctd_amount_applied_to, 0.0)), 0.0)
INTO v_credit_memo_acctd
FROM ar_receivable_applications
WHERE applied_payment_schedule_id = invoice.payment_schedule_id
AND NVL (confirmed_flag, 'Y') = 'Y'
AND status || '' = 'APP'
AND TRUNC (gl_date) > p_as_of_date
AND application_type LIKE 'CM%';
/* credit memo applied after p_as_of_date */
SELECT NVL (SUM (NVL (acctd_amount_applied_to, 0.0)), 0.0)
INTO v_credit_memo_acctd_1
FROM ar_receivable_applications
WHERE payment_schedule_id = invoice.payment_schedule_id
AND NVL (confirmed_flag, 'Y') = 'Y'
AND status || '' = 'APP'
AND TRUNC (gl_date) > p_as_of_date
AND application_type LIKE 'CM%';
/* calculate actual outstanding amount */
v_as_of_outstanding_acctd :=
invoice.acctd_outstanding_amount
+ v_cash_receipt_acctd
- v_adjustment_acctd
+ v_credit_memo_acctd
- v_credit_memo_acctd_1;
INSERT INTO zxc_aging_cust1 (customer_id,
invoice_number,
invoice_date,
gl_date,
invoice_amount,
tax_amount,
acctd_outstanding_amount,
due_date,
days_outstanding,
installment_number,
days_late_as_of,
current_os_amt,
cash_receipt_amt,
adj_amt,
credit_memo_amt,
credit_memo_amt_1)
VALUES (invoice.cust_account_id,
invoice.invoice_number,
invoice.invoice_date,
invoice.gl_date,
invoice.invoice_amount,
invoice.tax_amount,
v_as_of_outstanding_acctd,
invoice.due_date,
invoice.days_outstanding,
invoice.installment_number,
invoice.days_late_as_of,
invoice.acctd_outstanding_amount,
v_cash_receipt_acctd,
v_adjustment_acctd,
v_credit_memo_acctd,
v_credit_memo_acctd_1);
END LOOP;
COMMIT;
FOR receipt IN cs_get_receipt (p_as_of_date)
LOOP
INSERT INTO zxc_aging_cust2 (customer_id,
invoice_number,
trx_type,
acctd_outstanding_amount,
gl_date)
VALUES (receipt.cust_account_id,
receipt.receipt_number,
'RECEIPT',
-1 * receipt.acctd_amount_applied,
receipt.gl_date);
END LOOP;
COMMIT;
END;
