Saturday 22 September 2012

AR 7 Bucket Ageing Query


Select MAIN_TAB.*
from
(select AR_TAB.account_number ,
AR_TAB.party_name,
AR_TAB.customer_trx_id,
AR_TAB.Trans_Type,
AR_TAB.trans_type_name,
AR_TAB.cust_trx_type_id,
AR_TAB.invoice_currency_code,
AR_TAB.location_code,
AR_TAB.trx_num,
AR_TAB.Trans_Date,
AR_TAB.gl_date,
AR_TAB.Due_Date,
AR_TAB.Fcy_Amt,
AR_TAB.Inr_Amt,
AR_TAB.exchange_rate,
AR_TAB.amount_due_remaining_FCY,
AR_TAB.Amount_Remaining_INR,
nvl(AR_TAB.ar_acctd_amt_due,0) ar_acctd_amt_due,
(nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0)) outstanding_amount_inr,
(nvl(AR_TAB.Fcy_Amt,0)-nvl(AR_TAB.ar_fcy_amt_due,0)) Outstanding_amount_fcy,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) <=0 then (AR_TAB.INR_AMT - AR_TAB.ar_acctd_amt_due)
else 0 end ) Current_bal
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 1 and 30 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 31 and 60 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 61 and 90 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_90_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 91 and 120 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_120_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 121 and 150 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_150_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 151 and 180 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_180_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) > 180 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) more_than_180_Days_Past_Due
from
(SELECT
hca.account_number account_number,
hp.PARTY_NAME party_name,
cta.CUSTOMER_TRX_ID customer_trx_id
--,acl.name collector_name
,decode(ps.class,'INV', 'Invoice', 'DM', 'Debit Memo','CM','Credit Memo') Trans_Type
,rctt.name trans_type_name,
rctt.CUST_TRX_TYPE_ID cust_trx_type_id
,cta.INVOICE_CURRENCY_CODE invoice_currency_code,
hou.LOCATION_CODE location_code,
ps.trx_number trx_num
,ps.trx_date Trans_Date,
ps.GL_DATE gl_date,
ps.due_date Due_Date,
ps.AMOUNT_DUE_ORIGINAL Fcy_Amt,
round((ps.AMOUNT_DUE_ORIGINAL * nvl(ps.EXCHANGE_RATE,1)),2) Inr_Amt ,
ps.EXCHANGE_RATE exchange_rate,
ps.AMOUNT_DUE_REMAINING amount_due_remaining_FCY
,ps.acctd_amount_due_remaining Amount_Remaining_INR,
(select round(sum(nvl(a.AMOUNT_APPLIED* nvl(ps.EXCHANGE_RATE,1) ,0)),2)
from ar_receivable_applications_all a
where a.APPLIED_PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.APPLIED_PAYMENT_SCHEDULE_ID) ar_acctd_amt_due,
(select round(sum(nvl(a.AMOUNT_APPLIED,0)),2) from ar_receivable_applications_all a where a.APPLIED_PAYMENT_SCHEDULE_ID=ps.PAYMENT_SCHEDULE_ID
and a.DISPLAY='Y' and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.APPLIED_PAYMENT_SCHEDULE_ID) ar_fcy_amt_due
/*,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) <=0 then ps.acctd_amount_due_remaining
else 0 end ) Current_bal
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 1 and 30 then ps.acctd_amount_due_remaining
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 31 and 60 then ps.acctd_amount_due_remaining
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 61 and 90 then ps.acctd_amount_due_remaining
else 0 end ) One_90_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 91 and 120 then ps.acctd_amount_due_remaining
else 0 end ) One_120_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 121 and 150 then ps.acctd_amount_due_remaining
else 0 end ) One_150_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 151 and 180 then ps.acctd_amount_due_remaining
else 0 end ) One_180_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) > 180 then ps.acctd_amount_due_remaining
else 0 end ) more_than_180_Days_Past_Due*/
FROM apps.ar_payment_schedules_all ps
,apps.hz_cust_accounts hca
,apps.hz_parties hp
--,apps.ar_collectors acl
,apps.ra_customer_trx_all cta
,apps.ra_cust_trx_types_all rctt
,hr_organization_units_v hou
--ar_receivable_applications_all ara
WHERE hca.cust_account_id = ps.CUSTOMER_ID
AND   ps.CUSTOMER_TRX_ID=cta.CUSTOMER_TRX_ID
--AND   ara.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
--AND ara.DISPLAY='Y'
AND cta.cust_trx_type_id = rctt.cust_trx_type_id
--AND (ps.status = 'OP' or(ps.STATUS='CL' and ps.GL_DATE_CLOSED < :p_to_date))
AND ps.class <> 'PMT'
AND ps.CLASS = nvl(:p_class,ps.CLASS)
AND ps.CLASS in('INV','DM')
--AND ps.customer_id > 0
--AND ps.AMOUNT_DUE_REMAINING <> 0
--AND trunc(ps.GL_DATE) < trunc(sysdate)
AND cta.INVOICE_CURRENCY_CODE <> 'INR'
AND hou.ORGANIZATION_ID=cta.ORG_ID
AND cta.ORG_ID = nvl(:p_org_id,cta.ORG_ID)
AND hp.PARTY_ID=hca.PARTY_ID
--AND hp.PARTY_NAME='Tea Promotors Export Pvt. Ltd.'
AND ps.INVOICE_CURRENCY_CODE<>'INR'
AND rctt.ORG_ID=cta.ORG_ID
AND ps.AMOUNT_ADJUSTED is null -- added on 10jul2012
--AND cta.TRX_NUMBER='511120003'
--AND trunc(ps.DUE_DATE) between  nvl(:p_from_date,trunc(ps.DUE_DATE))  and nvl(:p_to_date,trunc(ps.DUE_DATE))
--AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE)
AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE))) AR_TAB
--AND AR_TAB.Amount_Remaining_INR <>0
where (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0)) <> 0
union all /*For credit Memo*/
select AR_TAB.account_number ,
AR_TAB.party_name,
AR_TAB.customer_trx_id,
AR_TAB.Trans_Type,
AR_TAB.trans_type_name,
AR_TAB.cust_trx_type_id,
AR_TAB.invoice_currency_code,
AR_TAB.location_code,
AR_TAB.trx_num,
AR_TAB.Trans_Date,
AR_TAB.gl_date,
AR_TAB.Due_Date,
AR_TAB.Fcy_Amt,
AR_TAB.Inr_Amt,
AR_TAB.exchange_rate,
AR_TAB.amount_due_remaining_FCY,
AR_TAB.Amount_Remaining_INR,
nvl(AR_TAB.ar_acctd_amt_due,0) ar_acctd_amt_due,
-(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0)) outstanding_amount_inr,
-(nvl(abs(AR_TAB.Fcy_Amt),0)-nvl(AR_TAB.ar_fcy_amt_due,0)) Outstanding_amount_fcy,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) <=0 then -(abs(AR_TAB.INR_AMT) - abs(AR_TAB.ar_acctd_amt_due))
else 0 end ) Current_bal
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 1 and 30 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 31 and 60 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 61 and 90 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_90_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 91 and 120 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_120_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 121 and 150 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_150_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 151 and 180 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_180_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) > 180 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) more_than_180_Days_Past_Due
from
(SELECT
hca.account_number account_number,
hp.PARTY_NAME party_name,
cta.CUSTOMER_TRX_ID customer_trx_id
--,acl.name collector_name
,decode(ps.class,'INV', 'Invoice', 'DM', 'Debit Memo','CM','Credit Memo') Trans_Type
,rctt.name trans_type_name,
rctt.CUST_TRX_TYPE_ID cust_trx_type_id
,cta.INVOICE_CURRENCY_CODE invoice_currency_code,
hou.LOCATION_CODE location_code,
ps.trx_number trx_num
,ps.trx_date Trans_Date,
ps.GL_DATE gl_date,
ps.due_date Due_Date,
ps.AMOUNT_DUE_ORIGINAL Fcy_Amt,
round((ps.AMOUNT_DUE_ORIGINAL * nvl(ps.EXCHANGE_RATE,1)),2) Inr_Amt ,
ps.EXCHANGE_RATE exchange_rate,
ps.AMOUNT_DUE_REMAINING amount_due_remaining_FCY
,ps.acctd_amount_due_remaining Amount_Remaining_INR,
(select round(sum(nvl(a.AMOUNT_APPLIED*nvl(ps.EXCHANGE_RATE,1),0)),2)
from ar_receivable_applications_all a
where a.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.PAYMENT_SCHEDULE_ID) ar_acctd_amt_due,
(select round(sum(nvl(abs(a.AMOUNT_APPLIED),0)),2)
from ar_receivable_applications_all a
where a.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.PAYMENT_SCHEDULE_ID) ar_fcy_amt_due
/*,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) <=0 then ps.acctd_amount_due_remaining
else 0 end ) Current_bal
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 1 and 30 then ps.acctd_amount_due_remaining
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 31 and 60 then ps.acctd_amount_due_remaining
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 61 and 90 then ps.acctd_amount_due_remaining
else 0 end ) One_90_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 91 and 120 then ps.acctd_amount_due_remaining
else 0 end ) One_120_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 121 and 150 then ps.acctd_amount_due_remaining
else 0 end ) One_150_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 151 and 180 then ps.acctd_amount_due_remaining
else 0 end ) One_180_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) > 180 then ps.acctd_amount_due_remaining
else 0 end ) more_than_180_Days_Past_Due*/
FROM apps.ar_payment_schedules_all ps
,apps.hz_cust_accounts hca
,apps.hz_parties hp
--,apps.ar_collectors acl
,apps.ra_customer_trx_all cta
,apps.ra_cust_trx_types_all rctt
,hr_organization_units_v hou
--ar_receivable_applications_all ara
WHERE hca.cust_account_id = ps.CUSTOMER_ID
AND   ps.CUSTOMER_TRX_ID=cta.CUSTOMER_TRX_ID
--AND   ara.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
--AND ara.DISPLAY='Y'
AND cta.cust_trx_type_id = rctt.cust_trx_type_id
--AND (ps.status = 'OP' or(ps.STATUS='CL' and ps.GL_DATE_CLOSED < :p_to_date))
AND ps.class <> 'PMT'
AND ps.CLASS = nvl(:p_class,ps.CLASS)
AND ps.CLASS in('CM')
--AND ps.customer_id > 0
--AND ps.AMOUNT_DUE_REMAINING <> 0
--AND trunc(ps.GL_DATE) < trunc(sysdate)
AND cta.INVOICE_CURRENCY_CODE <> 'INR'
AND hou.ORGANIZATION_ID=cta.ORG_ID
AND cta.ORG_ID = nvl(:p_org_id,cta.ORG_ID)
AND hp.PARTY_ID=hca.PARTY_ID
--AND hp.PARTY_NAME='Tea Promotors Export Pvt. Ltd.'
AND ps.INVOICE_CURRENCY_CODE<>'INR'
AND rctt.ORG_ID=cta.ORG_ID
AND ps.AMOUNT_ADJUSTED is null -- added on 10jul2012
--AND cta.TRX_NUMBER='511120003'
--AND trunc(ps.DUE_DATE) between  nvl(:p_from_date,trunc(ps.DUE_DATE))  and nvl(:p_to_date,trunc(ps.DUE_DATE))
--AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE)
AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE))) AR_TAB
--AND AR_TAB.Amount_Remaining_INR <>0
where (nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0)) <> 0) MAIN_TAB
order by MAIN_TAB.PARTY_NAME asc;

3 comments:

  1. Thank You.
    Your Query Helped us lot.

    Regards!
    Nitin

    ReplyDelete
  2. very good information thanks fro your stuff

    ReplyDelete
  3. hi swarup this query gives only the invoice amount remaining where the ar aging means customer balance (invoice due-cm's) right.

    ReplyDelete