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;