AP Invoice Audit - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Invoice Audit Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINDUP_XML
DB package: AP_APXINDUP_XMLP_PKG
            SELECT  distinct NVL(substr(upper(translate(i1.invoice_num,'a!@#\/-_$%^&*.','a')),
                          1,:P_MATCH_LENGTH),'NomatchKluDge1') C_MATCH_STRING,
                    v.vendor_name C_VENDOR_NAME,
                    upper(v.vendor_name) C_UPPER_VENDOR_NAME,
                    i1.invoice_num C_INVOICE_NUMBER,
                    i1.invoice_currency_code C_CURRENCY_CODE,
                    NVL(i1.invoice_amount,0) C_INVOICE_AMOUNT,
                    NVL(i1.invoice_amount,0) - NVL(i1.amount_paid,0)
                        - NVL(i1.discount_amount_taken,0)   C_AMOUNT_REMAINING,
                    i1.invoice_date C_INVOICE_DATE,
                    b.batch_name C_BATCH_NAME,
                    i1.description C_DESCRIPTION
            FROM                                      ap_invoices_all i1,
                               ap_invoices_all i2,
                ap_batches b,
                po_vendors v
            WHERE  i1.invoice_id     <> i2.invoice_id
            AND     NVL(substr(upper(translate(i1.invoice_num,'a!@#\/-_$%^&*.','a')),
                                1,:P_MATCH_LENGTH),'NomatchKluDge1')
                        = NVL(substr(upper(translate(i2.invoice_num,'a!@#\/-_$%^&*.','a')),
                                1,:P_MATCH_LENGTH),'NomatchKluDge1')
            AND    i2.invoice_date between :P_AUDIT_BEGIN and :P_AUDIT_END
            AND    i1.invoice_amount =  nvl(i2.invoice_amount,-1)
            AND    i1.vendor_id      =  i2.vendor_id
            AND    i2.invoice_date   =  DECODE(:P_INV_DATE_COMP,
                                                        'Y',i1.invoice_date,
                                    i2.invoice_date)
            AND     nvl(i1.vendor_id,-1)    =  v.vendor_id
            AND     i1.batch_id     =  b.batch_id(+)
            AND     i1.invoice_date between :P_COMPARE_BEGIN and :P_COMPARE_END
            &P_ORG_COND
            &C_VENDOR_TYPE_PREDICATE
            &C_VENDOR_NAME_PREDICATE
            ORDER BY upper(v.vendor_name),
                     NVL(substr(upper(translate(i1.invoice_num,'a!@#\/-_$%^&*.','a')),
                          1,:P_MATCH_LENGTH),'NomatchKluDge1'),
                     nvl(i1.invoice_amount,0), upper(i1.invoice_num)
Parameter Name SQL text Validation
Ledger
 
Compare Invoice Dates
 
LOV Oracle
Comparison To Invoice Date
 
Date
Comparison From Invoice Date
 
Date
Audit To Invoice Date
 
Date
Audit From Invoice Date
 
Date
Matching Invoice Number Length
 
Supplier Name
 
LOV Oracle
Supplier Type
 
LOV Oracle
Download