AP Discounts Available

Description
Categories: BI Publisher, Financials, Procurement
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 NameSQL textValidation
Ledger
 
Ending Discount Date
 
Date
Minimum Invoice Amount
 
Number
Minimum Discount Amount
 
Number
Pay Group (skip for All)
 
LOV Oracle
Supplier Name (skip for All)
 
LOV Oracle
Supplier Type (skip for All)
 
LOV Oracle