AP Supplier Paid Invoice History- Not Supported: Reserved For Future Use
Description
Categories: BI Publisher
Imported from BI Publisher
Description: (Old) Supplier Payment History
Application: Payables
Source: Supplier Paid Invoice History (XML) - Not Supported: Reserved For Future Use
Short Name: APXINVPH_XML
DB package: AP_APXINVPH_XMLP_PKG
Description: (Old) Supplier Payment History
Application: Payables
Source: Supplier Paid Invoice History (XML) - Not Supported: Reserved For Future Use
Short Name: APXINVPH_XML
DB package: AP_APXINVPH_XMLP_PKG
Run
AP Supplier Paid Invoice History- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
---|---|---|---|
Operating Unit |
|
LOV | |
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 |