AP Supplier Paid Invoice History - draft

Description
Categories: BI Publisher
Columns: C Vendor Id, C Vendor Site Id, C Vendor Name, C Sort Vendor Name, C Vendor Site Code, C Sort Vendor Site Code, C Currency Code, C Sort Currency Code, C Vendor Number, C Total Invoice Amt ...
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 Name SQL text Validation
Start Invoice Date
 
Date
End Invoice Date
 
Date
Supplier Type (skip for All)
 
LOV Oracle
Supplier Name (skip for All)
 
LOV Oracle
Summarize Invoices by Site?
 
LOV Oracle
Minimum Invoice Amount
 
Number
Invoice Order
 
LOV Oracle
Ledger