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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

            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