AP aging report query in oracle apps

AP aging report query in oracle apps

In this post , We will discuss about AP aging report query in oracle apps. AP aging reports helps us to identify the creditor aging which is due in terms of the Due Date. In other words AP aging helps to divide our current outstanding of our suppliers in terms of the Days or Buckets. for example If I want to see the how much outstanding amount is due with in 30 days and how much is next 30 days from 31 to 60 days. It helps us to plan cash flows of  the companies. Here below I will share the AP aging report query in oracle apps.

 

Complete Example of AP aging report query in oracle apps

 

SELECT   org_name,

              vendor_name,

              vendor_number,

              vendor_site_details,

              invoice_number,

              invoice_date,

              gl_Date,

              invoice_type,

              due_date,

              past_due_days,

              amount_remaining,

              CASE

                 WHEN past_due_days >= -999 AND past_due_days < 0

                 THEN

                  amount_remaining

                 ELSE

                    0

              END

                 CURRENT_BUCKET,

              CASE

                 WHEN past_due_days >= 0 AND past_due_days <= 30

                 THEN

                  amount_remaining

                 ELSE

                    0

              END

                 "0 to 30",

              CASE

                 WHEN past_due_days > 30 AND past_due_days <= 60

                 THEN

                  amount_remaining

                 ELSE

                    0

              END

                  "31 to 60",

              CASE

                 WHEN past_due_days > 60 AND past_due_days <= 90

                 THEN

                amount_remaining

                 ELSE

                    0

              END

                  "61 to 90",

              CASE

                 WHEN past_due_days > 90 AND past_due_days <= 120

                 THEN

                 amount_remaining

                 ELSE

                    0

              END

                   "61 to 120",

              CASE

                 WHEN past_due_days > 120 AND past_due_days <= 999999

                 THEN

                 amount_remaining

                 ELSE

                    0

              END

                   " More than 120 Days"

       FROM   (SELECT   hou.name org_name,

                        pv.vendor_name vendor_name,

                        pv.segment1 vendor_number,

                        pvs.vendor_site_code || ' ' || pvs.city || ' ' || state

                           vendor_site_details,

                        i.invoice_num invoice_number,

                        i.payment_status_flag,

                        i.invoice_type_lookup_code invoice_type,

                        i.invoice_date Invoice_Date,

                        i.gl_date Gl_Date,

                        ps.due_date Due_Date,

                        ps.amount_remaining,

                        (CEIL (SYSDATE - ps.due_date)) past_due_days, -- DAYS_DUE,

                        DECODE (

                           i.invoice_currency_code,

                           'USD',

                           DECODE (

                              0,

                              0,

                              ROUND (

                                 ( (NVL (ps.amount_remaining, 0)

                                    / (NVL (i.payment_cross_rate, 1)))

                                  * NVL (i.exchange_rate, 1)),

                                 2

                              ),

                              ROUND( ( (NVL (ps.amount_remaining, 0)

                                        / (NVL (i.payment_cross_rate, 1)))

                                      * NVL (i.exchange_rate, 1))

                                    / 0)

                              * 0

                           ),

                           DECODE (

                              i.exchange_rate,

                              NULL,

                              0,

                              DECODE (

                                 0,

                                 0,

                                 ROUND (

                                    ( (NVL (ps.amount_remaining, 0)

                                       / (NVL (ps.payment_cross_rate, 1)))

                                     * NVL (i.exchange_rate, 1)),

                                    2

                                 ),

                                 ROUND( ( (NVL (ps.amount_remaining, 0)

                                           / (NVL (i.payment_cross_rate, 1)))

                                         * NVL (i.exchange_rate, 1))

                                       / 0)

                                 * 0

                              )

                           )

                        )

                           amt_due_remaining

                 FROM   ap_payment_schedules_all ps,

                        ap_invoices_all i,

                        ap_suppliers pv,

                        ap_supplier_sites_all pvs,

                        hr_operating_units hou

                WHERE       i.invoice_id = ps.invoice_id

                        AND i.vendor_id = pv.vendor_id

                        AND i.vendor_site_id = pvs.vendor_site_id

                        AND i.org_id = hou.organization_id

                        AND i.cancelled_date IS NULL

                        AND (NVL (ps.amount_remaining, 0)

                             * NVL (i.exchange_rate, 1)) != 0

                        AND i.payment_status_flag IN ('N', 'P')

                           and i.org_id =:p_org_id)

   ORDER BY   vendor_number,Invoice_number