AP Invoice on Hold - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Invoice on Hold Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINROH_XML
DB package: AP_APXINROH_XMLP_PKG
            SELECT  decode(:P_ORDER_BY,
                   'Hold Name','Do not sort by vendor name',
                        decode(:SORT_BY_ALTERNATE, 'Y', upper(hp.organization_name_phonetic), upper(hp.party_name))) C_SORT_VENDOR_NAME,
                    hp.party_name C_MASTER_VENDOR_NAME,
                    inv1.vendor_id C_VENDOR_ID,
                h.hold_lookup_code C_HOLD_CODE_MASTER,
                decode(:P_ORDER_BY,
                   'Vendor Name','Do not sort by Hold Name',
                         upper(decode(h.hold_lookup_code,
                            null,:C_NLS_NA,
                                 alc.displayed_field))) C_SORT_NLS_HOLD_CODE1,
                    decode(h.hold_lookup_code,
                        null,:C_NLS_NA,
                             alc.displayed_field) C_MASTER_NLS_HOLD_CODE,
                    decode(h.hold_lookup_code,
                        null,:C_NLS_NA,
                             h.hold_lookup_code) C_HOLD_CODE,
                to_char(inv1.invoice_date,'YYYYMM') C_SORT_MONTH,
                to_char(inv1.invoice_date,'fmMonth YYYY') C_MONTH_NAME,
                decode(:P_ORDER_BY,
                   'Vendor Name','Do not sort by vendor name',
                        decode(:SORT_BY_ALTERNATE, 'Y', upper(hp.organization_name_phonetic), upper(hp.party_name))) C_M_SORT_VENDOR_NAME,
                    hp.party_name C_DETAIL_VENDOR_NAME,
                    decode(h.hold_lookup_code,
                        null,:C_NLS_NA,
                             alc.displayed_field) C_DETAIL_NLS_HOLD_CODE,
                    inv1.invoice_date C_INVOICE_DATE,
                B.batch_name C_BATCH_NAME,
                    inv1.invoice_id C_INVOICE_ID,
                    inv1.invoice_num C_INVOICE_NUM,
                    DECODE(inv1.invoice_currency_code,
                                          :C_BASE_CURRENCY_CODE, inv1.invoice_amount,
                                          inv1.base_amount) C_ORIGINAL_AMOUNT,
                            DECODE(inv1.invoice_currency_code,
                                           :C_BASE_CURRENCY_CODE, inv1.invoice_amount,
                                          inv1.base_amount) -
                            DECODE(inv1.payment_currency_code,
                                         :C_BASE_CURRENCY_CODE,
                                              nvl(inv1.amount_paid,0) + NVL(discount_amount_taken,0),
                                         DECODE(F.minimum_accountable_unit,
                                               NULL, ROUND(((DECODE(inv1.payment_cross_rate_type,
                                                                         'EMU FIXED', 1/inv1.payment_cross_rate,
                                                                         inv1.exchange_rate)) *
                                                                          nvl(inv1.amount_paid,0)),
                                                                         F.precision),
                                               ROUND(((DECODE(inv1.payment_cross_rate_type,
                                                               'EMU FIXED', 1/inv1.payment_cross_rate,
                                                               inv1.exchange_rate)) * nvl(inv1.amount_paid,0)) /
                                                               F.minimum_accountable_unit) *
                                                               F.minimum_accountable_unit)   +
                                        DECODE(F.minimum_accountable_unit,
                                             NULL, ROUND(((DECODE(inv1.payment_cross_rate_type,
                                                                       'EMU FIXED', 1/inv1.payment_cross_rate,
                                                                      inv1.exchange_rate)) *
                                                                      NVL(inv1.discount_amount_taken,0)),
                                                                      F.precision),
                                            ROUND(((DECODE(inv1.payment_cross_rate_type,
                                                            'EMU FIXED', 1/inv1.payment_cross_rate,
                                                            inv1.exchange_rate)) *
                                                            nvl(inv1.discount_amount_taken,0)) /
                                                            F.minimum_accountable_unit) *
                                                            F.minimum_accountable_unit))
                 C_AMOUNT_REMAINING,
                    inv1.description C_DESCRIPTION
            FROM    hz_parties hp,
                    ap_invoices inv1,
                    ap_batches B,
                    ap_payment_schedules S,
                    ap_holds h,
                    ap_lookup_codes alc,
                    fnd_currencies_vl F
            WHERE   hp.party_id = inv1.party_id
              AND   h.invoice_id = inv1.invoice_id
              AND   B.batch_id(+) = inv1.batch_id
              AND   S.invoice_id(+) = inv1.invoice_id
              AND   h.hold_lookup_code = nvl(:P_HOLD_CODE,h.hold_lookup_code)
              AND   h.release_lookup_code IS NULL
              AND   F.currency_code = :C_BASE_CURRENCY_CODE
             &c_vendor_clause   AND     trunc(inv1.creation_date) >= DECODE(:P_START_CREATION_DATE,
                            null,trunc(inv1.creation_date),
                                 :P_START_CREATION_DATE)
              AND   trunc(inv1.creation_date) <= DECODE(:P_END_CREATION_DATE,
                            null,trunc(inv1.creation_date),
                                 :P_END_CREATION_DATE)
              AND   (
                     (NVL(S.due_date, sysdate) >= DECODE(:P_START_DUE_DATE, null,
                         NVL(S.due_date, sysdate), :P_START_DUE_DATE)
                     AND NVL(S.due_date, sysdate) <= DECODE(:P_END_DUE_DATE, null,
                         NVL(S.due_date, sysdate), :P_END_DUE_DATE) )
                     AND
                     ( (NVL(S.discount_date, sysdate) >=
                               DECODE(:P_START_DISCOUNT_DATE, null,
                     NVL(S.discount_date, sysdate), :P_START_DISCOUNT_DATE)
                        AND NVL(S.discount_date, sysdate) <=
                                DECODE(:P_END_DISCOUNT_DATE, null,
                     NVL(S.discount_date, sysdate), :P_END_DISCOUNT_DATE) )
                        OR
                            (NVL(S.second_discount_date, sysdate) >=
                                 DECODE(:P_START_DISCOUNT_DATE, null,
                        NVL(S.second_discount_date, sysdate),:P_START_DISCOUNT_DATE)
                        AND NVL(S.second_discount_date, sysdate) <=
                                 DECODE(:P_END_DISCOUNT_DATE, null,
                         NVL(S.second_discount_date, sysdate), :P_END_DISCOUNT_DATE) )
                        OR
                           (NVL(S.third_discount_date, sysdate) >=
                                 DECODE(:P_START_DISCOUNT_DATE, null,
                        NVL(S.third_discount_date, sysdate), :P_START_DISCOUNT_DATE)
                        AND NVL(S.third_discount_date, sysdate) <=
                                DECODE(:P_END_DISCOUNT_DATE, null,
                        NVL(S.third_discount_date, sysdate), :P_END_DISCOUNT_DATE) )
                     )
                    )
              AND   alc.lookup_type = 'HOLD CODE'
              AND   alc.lookup_code = h.hold_lookup_code
            GROUP BY
                    h.hold_lookup_code,
                    alc.displayed_field,
                    hp.party_name,
                    decode(:SORT_BY_ALTERNATE, 'Y', upper(hp.organization_name_phonetic), upper(hp.party_name)),
                    inv1.vendor_id,
                    inv1.invoice_date,
                    inv1.invoice_id,
                    B.batch_name,
                    inv1.invoice_num,
                    DECODE(inv1.invoice_currency_code,
                        :C_BASE_CURRENCY_CODE, inv1.invoice_amount,
                                               inv1.base_amount),
                                    DECODE(inv1.invoice_currency_code,
                                           :C_BASE_CURRENCY_CODE, inv1.invoice_amount,
                                          inv1.base_amount) -
                            DECODE(inv1.payment_currency_code,
                                          :C_BASE_CURRENCY_CODE,
                                              nvl(inv1.amount_paid,0) + NVL(discount_amount_taken,0),
                                         DECODE(F.minimum_accountable_unit,
                                               NULL, ROUND(((DECODE(inv1.payment_cross_rate_type,
                                                                         'EMU FIXED', 1/inv1.payment_cross_rate,
                                                                         inv1.exchange_rate)) *
                                                                          nvl(inv1.amount_paid,0)),
                                                                         F.precision),
                                               ROUND(((DECODE(inv1.payment_cross_rate_type,
                                                               'EMU FIXED', 1/inv1.payment_cross_rate,
                                                               inv1.exchange_rate)) * nvl(inv1.amount_paid,0)) /
                                                               F.minimum_accountable_unit) *
                                                               F.minimum_accountable_unit)   +
                                        DECODE(F.minimum_accountable_unit,
                                             NULL, ROUND(((DECODE(inv1.payment_cross_rate_type,
                                                                       'EMU FIXED', 1/inv1.payment_cross_rate,
                                                                      inv1.exchange_rate)) *
                                                                      NVL(inv1.discount_amount_taken,0)),
                                                                      F.precision),
                                            ROUND(((DECODE(inv1.payment_cross_rate_type,
                                                            'EMU FIXED', 1/inv1.payment_cross_rate,
                                                            inv1.exchange_rate)) *
                                                            nvl(inv1.discount_amount_taken,0)) /
                                                            F.minimum_accountable_unit) *
                                                            F.minimum_accountable_unit)),
                       inv1.description
			&C_ORDER_BY
Parameter Name SQL text Validation
Ledger
 
Display Invalid Account Number
 
LOV Oracle
Include Hold Descriptions
 
LOV Oracle
To Due or Discount Date
 
Date
From Due or Discount Date
 
Date
Include Due or Discount Date
 
LOV Oracle
To Entered Date
 
Date
From Entered Date
 
Date
On Hold by Period Option
 
LOV Oracle
Order By
 
LOV Oracle
Trading Partner
 
LOV Oracle
Hold Name
 
LOV Oracle