AP Supplier Paid Invoice History - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Supplier Paid Invoice History (XML) - Not Supported: Reserved For Future Use
Short Name: APXINVPH_XML
DB package: AP_APXINVPH_XMLP_PKG
            SELECT  inv.vendor_id C_VENDOR_ID,
                    inv.vendor_site_id C_VENDOR_SITE_ID,
                    vendor_name C_VENDOR_NAME,
                    decode(:SORT_BY_ALTERNATE, 'Y', upper(vendor_name_alt), upper(vendor_name)) C_SORT_VENDOR_NAME,
                    vendor_site_code C_VENDOR_SITE_CODE,
                    decode(:SORT_BY_ALTERNATE, 'Y', upper(vendor_site_code_alt), upper(vendor_site_code)) C_SORT_VENDOR_SITE_CODE,
                    inv.PAYMENT_CURRENCY_CODE C_CURRENCY_CODE,
                    upper(inv.PAYMENT_CURRENCY_CODE) C_SORT_CURRENCY_CODE,
                    segment1 C_VENDOR_NUMBER,
                    Nvl(SUM(C_TOTAL_INVOICE_AMT_TEMP),0) C_TOTAL_INVOICE_AMT,
                    Nvl(SUM(C_TOTAL_AMOUNT_PAID_TEMP),0) C_TOTAL_AMOUNT_PAID,
                Nvl(SUM(C_TOTAL_PREPAID_AMT_TEMP),0) C_TOTAL_PREPAID_AMT,
                    Nvl(SUM(C_TOTAL_DISC_AMT_TAKEN_TEMP),0)  C_TOTAL_DISC_AMT_TAKEN,
                    Nvl(SUM(C_TOTAL_AMT_REMAINING_TEMP),0)   C_TOTAL_AMT_REMAINING
            FROM    (select  i.vendor_id,
                     i.vendor_site_id,
                     i.payment_currency_code PAYMENT_CURRENCY_CODE,
                     i.invoice_id,
            Nvl(I.pay_curr_invoice_amount,  I.invoice_amount) C_TOTAL_INVOICE_AMT_TEMP,
            Abs(NVL(amount_paid,0) - ap_utilities_pkg.ap_round_currency(Abs(Nvl(ap_invoices_utility_pkg.get_prepaid_amount(i.invoice_id),0)) * Nvl(i.payment_cross_rate, 1), i.payment_currency_code))  C_TOTAL_AMOUNT_PAID_TEMP,
            ap_utilities_pkg.ap_round_currency(Nvl(Abs(ap_invoices_utility_pkg.get_prepaid_amount(i.invoice_id)),0) * Nvl(i.payment_cross_rate,1), i.payment_currency_code) C_TOTAL_PREPAID_AMT_TEMP,
            NVL(discount_amount_taken, 0) + ap_utilities_pkg.ap_round_currency(Nvl(Abs(ap_invoices_pkg.get_amount_withheld(i.invoice_id)),0) * Nvl(i.payment_cross_rate, 1), i.payment_currency_code) C_TOTAL_DISC_AMT_TAKEN_TEMP,
            nvl(I.pay_curr_invoice_amount,I.invoice_amount) - NVL(amount_paid, 0) -  NVL(discount_amount_taken, 0) - ap_utilities_pkg.ap_round_currency(Nvl(Abs(ap_invoices_pkg.get_amount_withheld(i.invoice_id)),0) * Nvl(i.payment_cross_rate, 1), i.payment_currency_code) C_TOTAL_AMT_REMAINING_TEMP
                       from  ap_invoices i, ap_invoice_distributions id
                      where  i.invoice_id = id.invoice_id(+)
                        and  id.line_type_lookup_code(+)='AWT'
                    and  payment_status_flag != 'N'
                    and  i.invoice_date BETWEEN :LP_START_DATE AND :LP_END_DATE
                    and  i.invoice_amount >= nvl(:P_MIN_AMOUNT,0)
                   ) inv,
                    po_vendors v,
                    po_vendor_sites s
            WHERE   v.vendor_id = inv.vendor_id
            AND     s.vendor_site_id = inv.vendor_site_id
            AND     (:P_VENDOR_TYPE_LOOKUP_CODE is null OR
                 (v.vendor_type_lookup_code = :P_VENDOR_TYPE_LOOKUP_CODE))
            &P_VENDOR_ID_SQL
            GROUP BY inv.vendor_id,
                     inv.vendor_site_id,
                 vendor_name,
                     decode(:SORT_BY_ALTERNATE, 'Y', upper(vendor_name_alt), upper(vendor_name)),
                 inv.PAYMENT_CURRENCY_CODE,
                     segment1,
                     vendor_site_code,
                     decode(:SORT_BY_ALTERNATE, 'Y', upper(vendor_site_code_alt), upper(vendor_site_code))
            ORDER BY decode(:SORT_BY_ALTERNATE, 'Y', UPPER(vendor_name_alt), UPPER(vendor_name)),
                     decode(:SORT_BY_ALTERNATE, 'Y', UPPER(vendor_site_code_alt), UPPER(vendor_site_code)),
                 UPPER(inv.PAYMENT_CURRENCY_CODE)
Parameter NameSQL textValidation
Ledger
 
Invoice Order
 
LOV Oracle
Minimum Invoice Amount
 
Number
Summarize Invoices by Site?
 
LOV Oracle
Supplier Name (skip for All)
 
LOV Oracle
Supplier Type (skip for All)
 
LOV Oracle
End Invoice Date
 
Date
Start Invoice Date
 
Date