AP Discounts Available
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Discounts Available Report
Application: Payables
Source: Discounts Available Report (XML)
Short Name: APXINDIA_XML
DB package: AP_APXINDIA_XMLP_PKG
Description: Discounts Available Report
Application: Payables
Source: Discounts Available Report (XML)
Short Name: APXINDIA_XML
DB package: AP_APXINDIA_XMLP_PKG
SELECT substr(vendor_name,1,60) C_VENDOR_NAME, V.segment1 C_VENDOR_NUM, substr(vendor_site_code,1,20) C_VENDOR_SITE_CODE, substr(invoice_num,1,32) C_INVOICE_NUM, substr(description,1,60) C_DESCRIPTION, DECODE(S.ALWAYS_TAKE_DISC_FLAG, 'Y', P.DUE_DATE, DECODE(SIGN(:c_test_date --8266843 -nvl(TO_DATE(P.DISCOUNT_DATE,'DD/MM/YYYY'), --8266843 to_date('31/12/2999','DD/MM/YYYY'))), -1, P.DISCOUNT_DATE, DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.SECOND_DISCOUNT_DATE,'DD/MM/YYYY'), to_date('31/12/2999','DD/MM/YYYY'))), -1, P.SECOND_DISCOUNT_DATE, DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.THIRD_DISCOUNT_DATE,'DD/MM/YYYY'), to_date('31/12/2999','DD/MM/YYYY'))), -1, P.THIRD_DISCOUNT_DATE, P.DUE_DATE)))) C_DISCOUNT_DATE, due_date C_DUE_DATE, nvl(invoice_amount,0) C_INCOME, gross_amount C_GROSS_AMOUNT, amount_remaining C_AMOUNT_REMAINING, ap_utilities_pkg.ap_round_currency(nvl(DECODE(P.GROSS_AMOUNT, 0, 0, DECODE(S.ALWAYS_TAKE_DISC_FLAG, 'Y', P.DISCOUNT_AMOUNT_AVAILABLE, GREATEST( DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.DISCOUNT_DATE,'DD/MM/YYYY'), to_date('01/01/1901','DD/MM/YYYY'))), 1, 0, nvl(P.DISCOUNT_AMOUNT_AVAILABLE,0)), DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.SECOND_DISCOUNT_DATE,'DD/MM/YYYY'), to_date('01/01/1901','DD/MM/YYYY'))), 1, 0, nvl(P.SECOND_DISC_AMT_AVAILABLE,0)), DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.THIRD_DISCOUNT_DATE,'DD/MM/YYYY'), to_date('01/01/1901','DD/MM/YYYY'))), 1, 0, nvl(P.THIRD_DISC_AMT_AVAILABLE,0)) )) * (P.AMOUNT_REMAINING/ DECODE(P.GROSS_AMOUNT, 0, 1, P.GROSS_AMOUNT))),0)/I.payment_cross_rate,I.invoice_currency_code) C_DISCOUNT_INCOME, I.invoice_currency_code C_CURRENCY_CODE FROM ap_invoices I, po_vendors V, ap_payment_schedules P, po_vendor_sites S, ap_system_parameters O WHERE I.vendor_site_id = S.vendor_site_id AND I.vendor_id = V.vendor_id AND I.invoice_id = P.invoice_id AND I.cancelled_date IS NULL -- bug 12712125 AND P.payment_status_flag in ('N', 'P') AND DECODE(S.ALWAYS_TAKE_DISC_FLAG, 'Y', P.DUE_DATE, DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.DISCOUNT_DATE,'DD/MM/YYYY'), to_date('31/12/2999','DD/MM/YYYY'))), -1, P.DISCOUNT_DATE, DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.SECOND_DISCOUNT_DATE,'DD/MM/YYYY'), to_date('31/12/2999','DD/MM/YYYY'))), -1, P.SECOND_DISCOUNT_DATE, DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.THIRD_DISCOUNT_DATE,'DD/MM/YYYY'), to_date('31/12/2999','DD/MM/YYYY'))), -1, P.THIRD_DISCOUNT_DATE, P.DUE_DATE)))) <= (:P_DISC_THRU_DATE) AND invoice_amount +0 >= :p_min_inv_amount AND ap_utilities_pkg.ap_round_currency(DECODE(P.GROSS_AMOUNT, 0, 0, DECODE(S.ALWAYS_TAKE_DISC_FLAG, 'Y', P.DISCOUNT_AMOUNT_AVAILABLE, GREATEST( DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.DISCOUNT_DATE,'DD/MM/YYYY'), to_date('01/01/1901','DD/MM/YYYY'))), 1, 0, nvl(P.DISCOUNT_AMOUNT_AVAILABLE,0)), DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.SECOND_DISCOUNT_DATE,'DD/MM/YYYY'), to_date('01/01/1901','DD/MM/YYYY'))), 1, 0, nvl(P.SECOND_DISC_AMT_AVAILABLE,0)), DECODE(SIGN(:c_test_date -nvl(TO_DATE(P.THIRD_DISCOUNT_DATE,'DD/MM/YYYY'), to_date('01/01/1901','DD/MM/YYYY'))), 1, 0, nvl(P.THIRD_DISC_AMT_AVAILABLE,0)) )) * (P.AMOUNT_REMAINING/ DECODE(P.GROSS_AMOUNT, 0, 1, P.GROSS_AMOUNT)))/I.payment_cross_rate,I.invoice_currency_code)>= :p_min_disc_amount &C_WHERE_CLAUSE /*Bug 16291918*/ |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Supplier Type (skip for All) |
|
LOV Oracle | |
Supplier Name (skip for All) |
|
LOV Oracle | |
Pay Group (skip for All) |
|
LOV Oracle | |
Minimum Discount Amount |
|
Number | |
Minimum Invoice Amount |
|
Number | |
Ending Discount Date |
|
Date |