Supplier aging report Query in oracle apps r12 : Vendor aging report Query

Supplier aging report Query in oracle apps r12 : Vendor aging report Query

Supplier aging or Vendor aging is the most common Required report in Oracle payables. This report is also available in the standard format of the Oracle apps but if you want to customize this report then you can refer this post. Here I am sharing the Supplier aging report Query which can helps to develop the custom Vendor aging report in oracle apps. As we know that aging helps to show the figures/amount in the Days bucket. In the same way Supplier aging helps to show the Liability towards supplier in the Days bucket like 0 to 30 , 30 to 60,60 to 90,90 to 180 like that.

 

 

 

Example of Supplier aging report Query in oracle apps r12

 

Select supplier_number, vendor_name,
sum(Due_Amount) Due_Amount,
sum(bucket1) "<=30",
sum(bucket2) "31-60",
sum(bucket3) "61-90",
sum(bucket4) "91-180",
sum(bucket5) "181-270",
sum(bucket6) ">270"
FROM(
SELECT aia.invoice_date,
         round(nvl(ps.amount_remaining,0)*nvl(aia.exchange_rate, 1),3) Due_Amount,
         supp.vendor_name, supp.segment1 Vendor_Number,
         CASE
         when trunc(trunc(sysdate))-aia.invoice_date <= 30 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(aia.exchange_rate, 1),3),0)
         END bucket1
         ,
          CASE
         when trunc(sysdate)-aia.invoice_date BETWEEN 31 AND 60 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(aia.exchange_rate, 1),3),0)
         END  bucket2,
             CASE
         when trunc(sysdate)-aia.invoice_date BETWEEN 61 AND 90 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(aia.exchange_rate, 1),3),0)
         END  bucket3, 
             CASE
         when trunc(sysdate)-aia.invoice_date BETWEEN 91 AND 180 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(aia.exchange_rate, 1),3),0)
         END  bucket4  ,  
             CASE
         when trunc(sysdate)-aia.invoice_date BETWEEN 181 AND 270 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(aia.exchange_rate, 1),3),0)
         END  bucket5  ,  
             CASE
         when trunc(sysdate)-aia.invoice_date > 270 then
         NVL(round(nvl(ps.amount_remaining,0)*nvl(aia.exchange_rate, 1),3),0)
         END  bucket6
             FROM ap_payment_schedules_all ps, ap_invoices_all aia, ap_suppliers_all supp
   WHERE     aia.invoice_id = ps.invoice_id
         AND ps.org_id = :P_ORG_ID
         AND aia.vendor_id = supp.vendor_id
         AND aia.cancelled_by IS NULL
         AND ps.amount_remaining <> 0
         )
GROUP BY   supplier_number, vendor_name      
ORDER BY  vendor_name

 

 

 

Vendor aging report Query