1 Reply Latest reply on Oct 8, 2013 9:22 PM by Yuvaraj C.

    Query to get Supplier Aging Analysis on AP

    Miranga Ariyaratne

      Hi all,

       

      This seems like a common question. But i haven't found a solution yet. Does anyone have a query which i can use to create a Supplier Aging Analysis for R12?

       

      The application can give me a report for any backdated date which lists all the suppliers, with their total outstandings as at that date along with what transactions make up that outstandings. I need to create an aging report for this exact report.

       

      Any help is much appreciated

      Thanks

      Miranga

        • 1. Re: Query to get Supplier Aging Analysis on AP
          Yuvaraj C.

          Try this:

          Your technical person should help you pass the values for bind variables

           

          SELECT v.vendor_name C_VENDOR_NAME,

          v.segment1 C_VENDOR_NUMBER,

          decode(upper(:P_SORT_OPTION), 'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT, '%',upper(v.vendor_name), v.vendor_name), i.invoice_type_lookup_code) C_PRIMARY_BRK,

          decode(upper(:P_SORT_OPTION), 'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT, '%', decode(:SORT_BY_ALTERNATE, 'Y', upper(v.vendor_name_alt), upper(v.vendor_name)),

          decode(:SORT_BY_ALTERNATE, 'Y', v.vendor_name_alt, v.vendor_name)), i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,

          v.vendor_name C_SHORT_VENDOR_NAME,

          v.vendor_id C_VENDOR_ID,

          i.vendor_site_id C_CONTACT_SITE_ID,

          vs.vendor_site_code C_VENDOR_SITE_CODE,

          decode(:SORT_BY_ALTERNATE, 'Y', vs.vendor_site_code_alt, vs.vendor_site_code) C_VENDOR_SITE_CODE_BRK,

          nvl(vs.state,' ') C_VENDOR_STATE,

          nvl(substr(vs.city,1,15),' ') C_VENDOR_CITY,

          ps.payment_num C_REFERENCE_NUMBER,

          i.vendor_site_id C_ADDRESS_ID,

          nvl(substr(i.invoice_type_lookup_code,1,20), ' ') C_INVOICE_TYPE,

          i.invoice_id C_PAYMENT_SCHED_ID,

          -- RM nvl(to_char(ps.due_date,'DD-MON-RR'),' ') C_DUE_DATE,

          nvl(to_char(decode(:P_AGING, 'Due Date', ps.due_date, i.invoice_date),'DD-MON-RR'),' ') C_DUE_DATE,

          /* Bug: 1549982 */

          /* Bug 2083419 For foreign currencies, show amounts in functional currency */

          /* decode(i.invoice_currency_code,:C_BASE_CURRENCY_CO DE, decode(:C_BASE_MIN_ACCT_UNIT, 0,round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *nvl(i.exchange_rate,1)),:C_BASE_PRECISION), round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) * nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) * :C_BASE_MIN_ACCT_UNIT), decode(i.exchange_rate,NULL,0, decode(:C_BASE_MIN_ACCT_UNIT,

          0,round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) * nvl(i.exchange_rate,1)),:C_BASE_PRECISION), round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) * nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) * :C_BASE_MIN_ACCT_UNIT))) C_AMT_DUE_REMAINING,

          decode(i.invoice_currency_code,:C_BASE_CURRENCY_CO DE, decode(:C_BASE_MIN_ACCT_UNIT, 0,round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) * nvl(i.exchange_rate,1)),:C_BASE_PRECISION), round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) * nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) * :C_BASE_MIN_ACCT_UNIT), decode(i.exchange_rate,NULL,0, decode(:C_BASE_MIN_ACCT_UNIT, 0,round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) * nvl(i.exchange_rate,1)),:C_BASE_PRECISION), round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) * nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) * :C_BASE_MIN_ACCT_UNIT))) C_AMT_DUE_ORIGINAL, */ -- ps.gross_amount C_GROSS_AMOUNT,

          i.invoice_currency_code C_INVOICE_CUR_CODE,

          i.payment_cross_rate C_PAYMENT_CROSS_RATE,

          i.cancelled_amount C_CANCELLED_AMOUNT,

          i.accts_pay_code_combination_id C_VENDOR_TRX_ID,

          i.invoice_num C_INVOICE_NUMBER,

          i.invoice_num C_INVOICE_NUM_SHORT,

          to_char(i.invoice_date,'DD-MON-RR') C_INVOICE_DATE,

          -- ceil(to_date(to_char(sysdate,'DD-MON-RR')) - decode(:P_AGING, 'Due Date', ps.due_date, i.invoice_date)) C_DAYS_PAST_DUE, -- Commented for MCR 52037

          ceil(to_date(to_char(:P_AS_OF_DATE,'DD-MON-RR')) - decode(:P_AGING, 'Due Date', ps.due_date, i.invoice_date)) C_DAYS_PAST_DUE, -- Added for MCR 52037

          decode(i.invoice_currency_code, :C_BASE_CURRENCY_CODE, ' ', decode(i.exchange_rate, NULL,'*', ' ')) C_DATA_CONVERTED,

          nvl(i.exchange_rate, 1) C_EXCHANGE_RATE,

          decode(:C_VENDOR_NAME_SELECT, '%',decode(:SORT_BY_ALTERNATE, 'Y', upper(v.vendor_name_alt), upper(v.vendor_name)),

          decode(:SORT_BY_ALTERNATE, 'Y', v.vendor_name_alt, v.vendor_name)) C_VENDOR_NAME_BRK,

          decode(:p_seq_numbers, 'N', i.voucher_num, i.doc_sequence_value) VOUCHER_NUMBER

          FROM ap_payment_schedules ps,

          ap_invoices i,

          ap_suppliers v,

          po_vendor_sites vs

          WHERE i.invoice_id = ps.invoice_id

          AND i.vendor_id = v.vendor_id

          AND i.vendor_site_id = vs.vendor_site_id

          &P_VENDOR_PREDICATE

          &KCI_BUILD_WHERE

          AND ((TO_DATE(:P_AS_OF_DATE) - ps.due_date)

          between :C_MINDAYS and :C_MAXDAYS)

          AND i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT

          -- Added for MCR 52037

          AND nvl((select min(accounting_date) from ap_invoice_distributions where invoice_id = i.invoice_id),to_date(i.gl_date) ) <= TO_DATE(:P_AS_OF_DATE)

          and ( ( i.cancelled_date is not null and i.cancelled_date > to_date(:P_AS_OF_DATE))

          or (i.cancelled_date is null and ( nvl((select sum(amount) from apps.ap_invoice_distributions d where invoice_id = i.invoicE_id and d.accounting_date < TO_DATE(:P_AS_OF_DATE)),ps.gross_amount))-

          nvl((select sum(amount) from apps.ap_invoice_payments p where p.invoice_id = i.invoice_id and p.accounting_date < TO_DATE(:P_AS_OF_DATE)),0)!= 0))

          &P_AMOUNT_PREDICATE

          --AND i.payment_status_flag in ('N','P')

          &P_ORDER_BY


          Hope it helps!


          Regards,

          Yuvaraj