In this blog we are going to see sample SQL of AR Aging Statement. There is a seeded report for AR Aging Statement, but that is very complex and in case if any customization needed it will be very difficult to do that. So I have prepared below SQL for AR Aging Statement (7 bucket) report which picks all open Invoices, Credit Memos, Debit Memos and Unapplied Payments.
SQL:
with ar_txn as ( select rt.type Transaction_Type ,hp.party_name Customer_Name ,hp.party_number Customer_Number ,aps.due_date ,addr.city city ,hps_bill.party_site_number ,aps.gl_date ,coalesce(:p_as_of_date,sysdate) - aps.due_date total_days ,ra.trx_date Transaction_Date ,ra.trx_number Transaction_Number ,aps.amount_due_original ,aps.amount_due_remaining from ra_customer_trx_all ra ,ar_payment_schedules_all aps ,ra_cust_trx_types_all rt ,hz_cust_accounts hc ,hz_parties hp ,hz_cust_acct_sites_all hcasa_bill ,hz_cust_site_uses_all hcsua_bill ,hz_party_sites hps_bill ,hz_locations addr where 1 = 1 and ra.customer_trx_id = aps.customer_trx_id and ra.org_id = aps.org_id and ra.complete_flag = 'Y' and ra.cust_trx_type_seq_id = rt.cust_trx_type_seq_id and ra.bill_to_customer_id = hc.cust_account_id and hc.status = 'A' and hp.party_id = hc.party_id and hcasa_bill.cust_account_id = ra.bill_to_customer_id and hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id and hcsua_bill.site_use_code = 'BILL_TO' and hcsua_bill.site_use_id = ra.bill_to_site_use_id and hps_bill.party_site_id = hcasa_bill.party_site_id and addr.LOCATION_ID(+) = hps_bill.LOCATION_ID and hcasa_bill.status = 'A' and hcsua_bill.status = 'A' and aps.amount_due_remaining <> 0 and aps.status = 'OP' and aps.class != 'PMT' and (coalesce(null,:p_cust_name) is null or hp.party_name in (:p_cust_name)) and aps.gl_date <= nvl(:p_as_of_date,sysdate) and ra.org_id = :p_org_id ) ,invoice as ( select Transaction_Number ,Customer_Name ,Customer_Number ,city Customer_Address ,case when Transaction_Type = 'INV' then 'Invoice' when Transaction_Type = 'CM' then 'Credit Memo' when Transaction_Type = 'DM' then 'Debit Memo' end Transaction_Type ,to_char(Transaction_Date,'mm/dd/yyyy') Transaction_Date ,to_char(due_date,'mm/dd/yyyy') Due_Date ,amount_due_remaining Outstanding_Amount ,case when total_days < 1 then amount_due_remaining else 0 end Current_Amount ,case when total_days between 1 and 30 then amount_due_remaining else 0 end Past_Due_1_30 ,case when total_days between 31 and 60 then amount_due_remaining else 0 end Past_Due_31_60 ,case when total_days between 61 and 90 then amount_due_remaining else 0 end Past_Due_61_90 ,case when total_days between 91 and 180 then amount_due_remaining else 0 end Past_Due_91_180 ,case when total_days between 181 and 360 then amount_due_remaining else 0 end Past_Due_181_360 ,case when total_days > 360 then amount_due_remaining else 0 end Past_Due_361 from ar_txn ) ,ar_pymt as ( select acra.receipt_number Transaction_Number ,hp.party_name Customer_Name ,hp.party_number Customer_Number ,hp.city Customer_Address ,'Payment' Transaction_Type ,to_char(acra.receipt_date,'mm/dd/yyyy') Transaction_Date ,to_char(aps.due_date,'mm/dd/yyyy') Due_Date ,aps.amount_due_remaining Outstanding_Amount ,to_date(nvl(to_char(:p_as_of_date,'yyyy-mm-dd'),sysdate)) - to_date(to_char(aps.due_date,'yyyy-mm-dd')) total_days from ar_cash_receipts_all acra ,ar_payment_schedules_all aps ,hz_cust_accounts hc ,hz_parties hp where 1 = 1 and acra.status = 'UNAPP' and acra.amount <> 0 and acra.org_id = :p_org_id and aps.cash_receipt_id = acra.cash_receipt_id and aps.class = 'PMT' and hc.cust_account_id = acra.pay_from_customerand hc.status = 'A' and hp.party_id = hc.party_id and (coalesce(null,:p_cust_name) is null or hp.party_name in (:p_cust_name)) ) ,payment as ( select Transaction_Number ,Customer_Name ,Customer_Number ,Customer_Address ,Transaction_Type ,Transaction_Date ,Due_Date ,Outstanding_Amount ,case when total_days < 1 then Outstanding_Amount else 0 end Current_Amount ,case when total_days between 1 and 30 then Outstanding_Amount else 0 end Past_Due_1_30 ,case when total_days between 31 and 60 then Outstanding_Amount else 0 end Past_Due_31_60 ,case when total_days between 61 and 90 then Outstanding_Amount else 0 end Past_Due_61_90 ,case when total_days between 91 and 180 then Outstanding_Amount else 0 end Past_Due_91_180 ,case when total_days between 181 and 360 then Outstanding_Amount else 0 end Past_Due_181_360 ,case when total_days > 360 then Outstanding_Amount else 0 end Past_Due_361 from ar_pymt ) select * from ( select * from invoice union all select * from payment ) order by customer_name,Due_Date asc
SQL:
with ar_txn as ( select rt.type Transaction_Type ,hp.party_name Customer_Name ,hp.party_number Customer_Number ,aps.due_date ,addr.city city ,hps_bill.party_site_number ,aps.gl_date ,coalesce(:p_as_of_date,sysdate) - aps.due_date total_days ,ra.trx_date Transaction_Date ,ra.trx_number Transaction_Number ,aps.amount_due_original ,aps.amount_due_remaining from ra_customer_trx_all ra ,ar_payment_schedules_all aps ,ra_cust_trx_types_all rt ,hz_cust_accounts hc ,hz_parties hp ,hz_cust_acct_sites_all hcasa_bill ,hz_cust_site_uses_all hcsua_bill ,hz_party_sites hps_bill ,hz_locations addr where 1 = 1 and ra.customer_trx_id = aps.customer_trx_id and ra.org_id = aps.org_id and ra.complete_flag = 'Y' and ra.cust_trx_type_seq_id = rt.cust_trx_type_seq_id and ra.bill_to_customer_id = hc.cust_account_id and hc.status = 'A' and hp.party_id = hc.party_id and hcasa_bill.cust_account_id = ra.bill_to_customer_id and hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id and hcsua_bill.site_use_code = 'BILL_TO' and hcsua_bill.site_use_id = ra.bill_to_site_use_id and hps_bill.party_site_id = hcasa_bill.party_site_id and addr.LOCATION_ID(+) = hps_bill.LOCATION_ID and hcasa_bill.status = 'A' and hcsua_bill.status = 'A' and aps.amount_due_remaining <> 0 and aps.status = 'OP' and aps.class != 'PMT' and (coalesce(null,:p_cust_name) is null or hp.party_name in (:p_cust_name)) and aps.gl_date <= nvl(:p_as_of_date,sysdate) and ra.org_id = :p_org_id ) ,invoice as ( select Transaction_Number ,Customer_Name ,Customer_Number ,city Customer_Address ,case when Transaction_Type = 'INV' then 'Invoice' when Transaction_Type = 'CM' then 'Credit Memo' when Transaction_Type = 'DM' then 'Debit Memo' end Transaction_Type ,to_char(Transaction_Date,'mm/dd/yyyy') Transaction_Date ,to_char(due_date,'mm/dd/yyyy') Due_Date ,amount_due_remaining Outstanding_Amount ,case when total_days < 1 then amount_due_remaining else 0 end Current_Amount ,case when total_days between 1 and 30 then amount_due_remaining else 0 end Past_Due_1_30 ,case when total_days between 31 and 60 then amount_due_remaining else 0 end Past_Due_31_60 ,case when total_days between 61 and 90 then amount_due_remaining else 0 end Past_Due_61_90 ,case when total_days between 91 and 180 then amount_due_remaining else 0 end Past_Due_91_180 ,case when total_days between 181 and 360 then amount_due_remaining else 0 end Past_Due_181_360 ,case when total_days > 360 then amount_due_remaining else 0 end Past_Due_361 from ar_txn ) ,ar_pymt as ( select acra.receipt_number Transaction_Number ,hp.party_name Customer_Name ,hp.party_number Customer_Number ,hp.city Customer_Address ,'Payment' Transaction_Type ,to_char(acra.receipt_date,'mm/dd/yyyy') Transaction_Date ,to_char(aps.due_date,'mm/dd/yyyy') Due_Date ,aps.amount_due_remaining Outstanding_Amount ,to_date(nvl(to_char(:p_as_of_date,'yyyy-mm-dd'),sysdate)) - to_date(to_char(aps.due_date,'yyyy-mm-dd')) total_days from ar_cash_receipts_all acra ,ar_payment_schedules_all aps ,hz_cust_accounts hc ,hz_parties hp where 1 = 1 and acra.status = 'UNAPP' and acra.amount <> 0 and acra.org_id = :p_org_id and aps.cash_receipt_id = acra.cash_receipt_id and aps.class = 'PMT' and hc.cust_account_id = acra.pay_from_customerand hc.status = 'A' and hp.party_id = hc.party_id and (coalesce(null,:p_cust_name) is null or hp.party_name in (:p_cust_name)) ) ,payment as ( select Transaction_Number ,Customer_Name ,Customer_Number ,Customer_Address ,Transaction_Type ,Transaction_Date ,Due_Date ,Outstanding_Amount ,case when total_days < 1 then Outstanding_Amount else 0 end Current_Amount ,case when total_days between 1 and 30 then Outstanding_Amount else 0 end Past_Due_1_30 ,case when total_days between 31 and 60 then Outstanding_Amount else 0 end Past_Due_31_60 ,case when total_days between 61 and 90 then Outstanding_Amount else 0 end Past_Due_61_90 ,case when total_days between 91 and 180 then Outstanding_Amount else 0 end Past_Due_91_180 ,case when total_days between 181 and 360 then Outstanding_Amount else 0 end Past_Due_181_360 ,case when total_days > 360 then Outstanding_Amount else 0 end Past_Due_361 from ar_pymt ) select * from ( select * from invoice union all select * from payment ) order by customer_name,Due_Date asc
Comments
Post a Comment