This Open Receipt Report is used to review a list of receipts which are in open status for the date range that you specify.
Script used for Open Receipt Report is used to review a list of receipts which are in open status for the date range that you specify.
SELECT coll.coll_name “COLLECTOR”, cons.group_name “PARENT”,
cons.group_acc “GROUP_ACC”, hca.account_number “CUST_NUMBER”,
hp.party_name “CUSTOMER_NAME”, rcpt.posted_date “POSTED_DATE”,
rcpt.receipt_date “RCPT_DATE”, rcpt.currency “CURRENCY”,
rcpt.receipt_method “RCPT_METHOD”, rcpt.receipt_number “RCPT_NUMBER”,
rcpt.stat “RCPT_STATUS”, rcpt.receipt_amount “RCPT_AMOUNT”,
ROUND (gbp_receipt, 2) “GBP_RECEIPT”,
ROUND (CASE
WHEN rcpt.receipt_date >= ’01-SEP-2010′
THEN gbp_receipt
ELSE 0
END,
2
) “CURRENT_MONTH_GBP”,
rcpt.gbp_remaining “GBP_REMAINING”,
ROUND
(CASE
WHEN rcpt.receipt_date < SYSDATE – 1
AND rcpt.receipt_date > SYSDATE – 31
THEN rcpt.gbp_remaining
ELSE 0
END,
2
) “DAYS_1_30”,
ROUND
(CASE
WHEN rcpt.receipt_date < SYSDATE – 31
AND rcpt.receipt_date > SYSDATE – 61
THEN rcpt.gbp_remaining
ELSE 0
END,
2
) “DAYS_31_60”,
ROUND
(CASE
WHEN rcpt.receipt_date < SYSDATE – 61
AND rcpt.receipt_date > SYSDATE – 91
THEN rcpt.gbp_remaining
ELSE 0
END,
2
) “DAYS_61_90”,
ROUND
(CASE
WHEN rcpt.receipt_date < SYSDATE – 91
AND rcpt.receipt_date > SYSDATE – 181
THEN rcpt.gbp_remaining
ELSE 0
END,
2
) “DAYS_91_180”,
ROUND
(CASE
WHEN rcpt.receipt_date < SYSDATE – 181
AND rcpt.receipt_date > SYSDATE – 361
THEN rcpt.gbp_remaining
ELSE 0
END,
2
) “DAYS_181_360”,
ROUND
(CASE
WHEN rcpt.receipt_date < SYSDATE – 361
AND rcpt.receipt_date > SYSDATE – 999999
THEN rcpt.gbp_remaining
ELSE 0
END,
2
) “DAYS_361”,
rcpt.reason_code “REASON_CODE”, rcpt.narrative “NARRATIVE”
FROM ar_payment_schedules_all aps,
hz_parties hp,
hz_cust_accounts hca,
—————– collector ———————
(SELECT ac.NAME coll_name, acp.credit_hold credit_hold,
acp.cust_account_id cust_id
FROM hz_cust_accounts hca2,
hz_customer_profiles acp,
ar_collectors ac
WHERE acp.cust_account_id = hca2.cust_account_id
AND acp.site_use_id IS NULL
AND acp.collector_id = ac.collector_id(+)) coll,
——— sub select receipt data ———–
(SELECT aps3.payment_schedule_id pid, aps3.customer_id cust_id,
acr.status stat, acr.receipt_date receipt_date,
acr.creation_date posted_date, acr.currency_code currency,
acr.receipt_number receipt_number, acr.status status,
arm.NAME receipt_method, acr.attribute4 reason_code,
acr.attribute3 narrative, acr.amount receipt_amount,
(CASE
WHEN aps3.CLASS = ‘PMT’ AND acr.status != ‘REV’
THEN DECODE (aps3.invoice_currency_code,
‘GBP’, (acr.amount),
((acr.amount * acr.exchange_rate)
)
)
ELSE 0
END
) gbp_receipt,
aps3.amount_due_remaining * -1 amt_remaining,
(CASE
WHEN aps3.CLASS = ‘PMT’ AND aps3.amount_due_remaining <> 0
THEN DECODE (aps3.invoice_currency_code,
‘GBP’, (aps3.amount_due_remaining * -1),
( ( aps3.amount_due_remaining
* aps3.exchange_rate
)
* -1
)
)
ELSE 0
END
) gbp_remaining
FROM ar_payment_schedules_all aps3,
ar_cash_receipts_all acr,
ar_receipt_methods arm
WHERE aps3.cash_receipt_id = acr.cash_receipt_id
AND acr.receipt_method_id = arm.receipt_method_id
AND aps3.CLASS = ‘PMT’
AND aps3.org_id = 210) rcpt,
————— sub select group ——————
(SELECT hcar.cust_account_id GROUP_ID, hcar.status status,
hcar.related_cust_account_id related_id,
hca2.account_number group_acc, hp2.party_name group_name
FROM hz_cust_acct_relate_all hcar,
hz_cust_accounts hca2,
hz_parties hp2
WHERE hp2.party_id = hca2.party_id
AND hcar.cust_account_id = hca2.cust_account_id
AND hcar.status = ‘A’
AND hcar.org_id = 210) cons
WHERE aps.org_id = 210
AND hp.party_id(+) = hca.party_id
AND rcpt.pid = aps.payment_schedule_id
AND coll.cust_id(+) = aps.customer_id
AND rcpt.cust_id = hca.cust_account_id(+)
AND cons.related_id(+) = hca.cust_account_id
AND aps.amount_due_remaining <> 0
What we expect in the script
This script helps us to comprehend how Open Receipt Report is used to review a list of receipts which are in open status for the date range that you specify. Couple of tables which are being used in the query are. hz_cust_acct_relate_all hcar, hz_cust_accounts hca2, hz_parties hp2 etc.
Summary
This Post described the script Open Receipt Report is used to review a list of receipts which are in open status for the date range that you specify. In Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
