AP Discounts Available

            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*/