Step 1. Create Below Function xx_paymentFormula To Calculate Unallocated Receipt Amount. Whose Class is "PMT" in ar_payment_schedules_all Table.
--------------------------------------------------------------------Start---------------------------------------
CREATE OR REPLACE function APPS.xx_paymentFormula(p_cust_id number,p_as_of_date date,p_org_id number) return Number is
l_payment number(38,2);
l_payment1 number(38,2);
l_total number(38,2);
begin
select nvl(sum(receiptL.AMOUNT_APPLIED),0) into l_payment
from apps.ar_payment_Schedules_all pay
,AR_RECEIVABLE_APPLICATIONS_ALL receiptL
where pay.class ='PMT'
and pay.status='OP'
and receiptl.status ='ACC'
and display='Y'
and pay.org_id =p_org_id
--and pay.cash_receipt_id=395302
and pay.PAYMENT_SCHEDULE_ID =receiptL.PAYMENT_SCHEDULE_ID
AND TRUNC (pay.gl_date) <=p_as_of_date
and pay.CUSTOMER_ID =p_cust_id;
select nvl(sum(receiptL.AMOUNT_APPLIED),0) into l_payment1
from apps.ar_payment_Schedules_all pay
,AR_RECEIVABLE_APPLICATIONS_ALL receiptL
where pay.class ='PMT'
and pay.status='OP'
-- and receiptl.status ='ACC'
and receiptl.status ='UNAPP'
-- and display='Y'
and pay.org_id =p_org_id
-- and pay.cash_receipt_id=395302
and pay.PAYMENT_SCHEDULE_ID =receiptL.PAYMENT_SCHEDULE_ID
AND TRUNC (pay.gl_date) <=p_as_of_date
and pay.CUSTOMER_ID =p_cust_id;
/* select nvl(sum(abs(AMOUNT_DUE_REMAINING)),0) into l_payment
from apps.ar_payment_Schedules_all pay
,AR_RECEIVABLE_APPLICATIONS_ALL receiptL
where pay.class ='PMT'
and pay.status='OP'
and receiptl.status ='ACC'
and display='Y'
and pay.org_id =fnd_profile.value('ORG_ID')
--and pay.cash_receipt_id=395295
and pay.PAYMENT_SCHEDULE_ID =receiptL.PAYMENT_SCHEDULE_ID
AND TRUNC (pay.gl_date) <=:p_in_as_of_date_low
and pay.CUSTOMER_ID =:CUST_ID;
select nvl(sum(abs(AMOUNT_DUE_REMAINING)),0) into l_payment1
from ar_payment_Schedules_all ab
where org_id=fnd_profile.value('ORG_ID')
and status='OP' and class ='PMT' AND TRUNC (gl_date) <=:p_in_as_of_date_low
and customer_id=:CUST_ID
and exists
(select 1 from ar_receivable_applications_all
where status='UNAPP' and org_id=fnd_profile.value('ORG_ID') and PAYMENT_SCHEDULE_ID=ab.PAYMENT_SCHEDULE_ID
having sum(AMOUNT_APPLIED)>0
);
*/
l_total:=l_payment+l_payment1;
return l_total;
exception
when others then
return null;
end;
/
----------------------------------------End----------------------------------------
Step 2:- Use Below Query To create Rdf
Use Below code to initialise AR module in before report trigger in rdf
exec mo_global.init('AR')
-------------------------------------------Query Start----------------------------------------------------------------
SELECT customer_id,
rac_customer_number cust_account_num,
rac_customer_name cust_name,
hca.ORIG_SYSTEM_REFERENCE,
xx_paymentFormula(customer_id,:p_as_on_date,a.org_id) PAYMENT,
SUM (
NVL (
CASE
WHEN ROUND ( (TO_DATE (:p_as_on_date) - due_date)) >= -99999999
AND ROUND ( (TO_DATE (:p_as_on_date) - due_date)) <=
99999999
THEN
arpt_sql_func_util.
get_balance_due_as_of_date (a.payment_schedule_id,
:p_as_on_date,
a.CLASS)
END,
0))
outstanding,
SUM (
NVL (
CASE
WHEN ROUND ( (TO_DATE (:p_as_on_date) - due_date)) >=
-999999999999999
AND ROUND ( (TO_DATE (:p_as_on_date) - due_date)) <= 0
THEN
arpt_sql_func_util.
get_balance_due_as_of_date (a.payment_schedule_id,
:p_as_on_date,
a.CLASS)
END,
0))
buk1,
SUM (
NVL (
CASE
WHEN ROUND ( (TO_DATE (:p_as_on_date) - due_date)) >= 1
AND ROUND ( (TO_DATE (:p_as_on_date) - due_date)) <= 30
THEN
arpt_sql_func_util.
get_balance_due_as_of_date (a.payment_schedule_id,
:p_as_on_date,
a.CLASS)
END,
0))
buk2,
SUM (
NVL (
CASE
WHEN ROUND ( (TO_DATE (:p_as_on_date) - due_date)) >= 31
AND ROUND ( (TO_DATE (:p_as_on_date) - due_date)) <= 60
THEN
arpt_sql_func_util.
get_balance_due_as_of_date (a.payment_schedule_id,
:p_as_on_date,
a.CLASS)
END,
0))
buk3,
SUM (
NVL (
CASE
WHEN ROUND ( (TO_DATE (:p_as_on_date) - due_date)) >= 61
AND ROUND ( (TO_DATE (:p_as_on_date) - due_date)) <= 90
THEN
arpt_sql_func_util.
get_balance_due_as_of_date (a.payment_schedule_id,
:p_as_on_date,
a.CLASS)
END,
0))
buk4,
SUM (
NVL (
CASE
WHEN ROUND ( (TO_DATE (:p_as_on_date) - due_date)) >= 91
AND ROUND ( (TO_DATE (:p_as_on_date) - due_date)) <= 180
THEN
arpt_sql_func_util.
get_balance_due_as_of_date (a.payment_schedule_id,
:p_as_on_date,
a.CLASS)
END,
0))
buk5,
SUM (
NVL (
CASE
WHEN ROUND ( (TO_DATE (:p_as_on_date) - due_date)) >= 181
AND ROUND ( (TO_DATE (:p_as_on_date) - due_date)) <= 360
THEN
arpt_sql_func_util.
get_balance_due_as_of_date (a.payment_schedule_id,
:p_as_on_date,
a.CLASS)
END,
0))
buk6,
SUM (
NVL (
CASE
WHEN ROUND ( (TO_DATE (:p_as_on_date) - due_date)) >=361
AND ROUND ( (TO_DATE (:p_as_on_date) - due_date)) <=
9999999
THEN
arpt_sql_func_util.
get_balance_due_as_of_date (a.payment_schedule_id,
:p_as_on_date,
a.CLASS)
END,
0))
buk7,
null
buk8,
a.org_id
FROM ar_payment_schedules_v a,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas
WHERE a.CUSTOMER_ID = hca.CUST_ACCOUNT_ID
AND hca.CUST_ACCOUNT_ID = hcas.CUST_ACCOUNT_ID
AND UPPER (NVL (hcas.ATTRIBUTE1, 'XX')) =
UPPER (NVL (:P_LINE_OF_BUSINESS, NVL (hcas.ATTRIBUTE1, 'XX')))
AND a.org_id = :P_ORG_ID
AND NVL (hca.ORIG_SYSTEM_REFERENCE, 'XX') =
NVL (:P_CUST_REF_NUM, NVL (hca.ORIG_SYSTEM_REFERENCE, 'XX'))
AND a.rac_customer_number = NVL (:p_from_cust, a.rac_customer_number)
AND a.status = 'OP'
AND a.CLASS<>'PMT'
AND a.CLASS IN ('INV', 'DM', 'CM')
and a.gl_date <=NVL (:p_as_on_date, a.gl_date)
GROUP BY rac_customer_number,
customer_id,
a.org_id,
rac_customer_name,
hca.ORIG_SYSTEM_REFERENCE
ORDER BY rac_customer_name
---------------------------------Query End--------------------------------------------------------------------
For Rtf Please Click here and check your Mail.............
No comments:
Post a Comment