AP Supplier Payment History - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Supplier Payment History (XML) - Not Supported: Reserved For Future Use
Short Name: APXPPHIS_XML
DB package: AP_APXPPHIS_XMLP_PKG
            SELECT  V.vendor_name C_VENDOR_NAME,
                upper(V.vendor_name) C_SORT_VENDOR_NAME,
                    V.segment1 C_VENDOR_NUMBER,
                    VS.vendor_site_code C_VENDOR_SITE_CODE,
                    decode(VS.address_line1,
                    null,'',
                         VS.address_line1||',') C_ADDRESS_LINE1,
                decode(VS.city,
                    null,'',
                                 VS.city||',') C_CITY,
                decode(VS.state,
                    null,VS.province||',',
                                 VS.state||',') C_STATE,
                VS.zip C_ZIP,
                    C.bank_account_name C_BANK_ACCOUNT_NAME,
                    C.check_number C_CHECK_NUMBER,
                    C.check_date C_CHECK_DATE,
                    C.amount C_CHECK_AMOUNT,
                    C.currency_code C_CURRENCY_CODE,
                    SUM(DECODE(I.payment_currency_code, :C_BASE_CURRENCY_CODE, IP.amount,
                               IP.payment_base_amount)) C_BASE_AMOUNT,
                    C.void_date C_VOID_DATE,
                    C.check_id C_CHECK_ID
            FROM    ap_checks C, ap_invoice_payments IP, ap_invoices I,
                    po_vendor_sites VS, po_vendors V
            WHERE   V.vendor_id = DECODE(:P_VENDOR_ID, null, V.vendor_id,
                                         :P_VENDOR_ID)
            AND     NVL(V.vendor_type_lookup_code, 'x') =
                    DECODE(:P_VENDOR_TYPE, null,
                           NVL(V.vendor_type_lookup_code, 'x'),
                           :P_VENDOR_TYPE)
            AND     VS.vendor_id = V.vendor_id
            AND    C.vendor_site_id = VS.vendor_site_id  AND     IP.invoice_id = I.invoice_id
            AND     C.check_id = IP.check_id
            AND     C.check_date
                                 BETWEEN NVL(:P_START_PAYMENT_DATE, sysdate - 9000)
                                     AND NVL(:P_END_PAYMENT_DATE, sysdate + 9000)
            GROUP BY C.check_id, V.vendor_name,
                    V.segment1,
                    VS.vendor_site_code,
                    VS.address_line1,
                    VS.city,
                    VS.state,
                    VS.province,
                    VS.zip,
                    C.bank_account_name,
                    C.check_number,
                    C.check_date,
                    C.amount,
                    C.currency_code,
                    C.void_date
            /*ORDER BY DECODE(:P_VENDOR_ID,
                    null, UPPER(V.vendor_name), V.vendor_name),
                    VS.vendor_site_code, &P_ORDER_BY*/
             ORDER BY DECODE(:P_VENDOR_ID,
						                    null, UPPER(V.vendor_name), V.vendor_name), VS.vendor_site_code,
						                    V.segment1,
						                    decode(VS.address_line1, null,'',VS.address_line1||','),
						                    decode(VS.city,null,'',VS.city||','),
						                    decode(VS.state,null,VS.province||',',VS.state||','),
						                    VS.zip, &P_ORDER_BY
Parameter Name SQL text Validation
Ledger Id
 
Order By Option
 
LOV Oracle
Invoice Detail
 
LOV Oracle
End Payment Date
 
Date
Start Payment Date
 
Date
Supplier Name (skip for All)
 
LOV Oracle
Supplier Type (skip for All)
 
LOV Oracle