AP Invoice Aging Report- Not Supported: Reserved For Future Use

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Invoice Aging Report
Application: Payables
Source: Invoice Aging Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINAGE_XML
DB package: AP_APXINAGE_XMLP_PKG
Run AP Invoice Aging Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
            SELECT  hp.party_name C_VENDOR_NAME,
                     v.segment1 C_VENDOR_NUMBER,
                    decode(upper(:P_SORT_OPTION),
                      'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
                                  '%',upper(hp.party_name),
                                      hp.party_name),
                               i.invoice_type_lookup_code) C_PRIMARY_BRK,
                    decode(upper(:P_SORT_OPTION),
                      'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
                                  '%', decode(:SORT_BY_ALTERNATE, 'Y', upper(hp.organization_name_phonetic), upper(hp.party_name)),
                                          decode(:SORT_BY_ALTERNATE, 'Y', hp.organization_name_phonetic, hp.party_name)),
                               i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,
                    hp.party_name C_SHORT_VENDOR_NAME,
                    v.vendor_id C_VENDOR_ID,
                    i.vendor_site_id C_CONTACT_SITE_ID,
                    vs.vendor_site_code C_VENDOR_SITE_CODE,
                    decode(:SORT_BY_ALTERNATE, 'Y', vs.vendor_site_code_alt, vs.vendor_site_code) C_VENDOR_SITE_CODE_BRK,
                    nvl(vs.state,' ')  C_VENDOR_STATE,
                    nvl(substr(vs.city,1,15),' ') C_VENDOR_CITY,
                    ps.payment_num C_REFERENCE_NUMBER,
                    i.vendor_site_id C_ADDRESS_ID,
                    nvl(substr(i.invoice_type_lookup_code,1,20), ' ') C_INVOICE_TYPE,
                    i.invoice_id C_PAYMENT_SCHED_ID,
                    nvl(to_char(ps.due_date,'DD-MON-RR'),' ') C_DUE_DATE,
                     decode(i.invoice_currency_code,:C_BASE_CURRENCY_CODE,
                       decode(:C_BASE_MIN_ACCT_UNIT,
                       0,round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *
                                nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
                         round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *
                                nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                                          :C_BASE_MIN_ACCT_UNIT),
                   decode(i.exchange_rate,NULL,0,
                       decode(:C_BASE_MIN_ACCT_UNIT,
                       0,round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *
                                nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
                         round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *
                                nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                                          :C_BASE_MIN_ACCT_UNIT)))  C_AMT_DUE_REMAINING,
                   decode(i.invoice_currency_code,:C_BASE_CURRENCY_CODE,
                       decode(:C_BASE_MIN_ACCT_UNIT,
                       0,round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
                                nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
                         round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
                                nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                                          :C_BASE_MIN_ACCT_UNIT),
                   decode(i.exchange_rate,NULL,0,
                       decode(:C_BASE_MIN_ACCT_UNIT,
                       0,round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
                                nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
                         round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
                                nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                                          :C_BASE_MIN_ACCT_UNIT)))  C_AMT_DUE_ORIGINAL,
                    i.accts_pay_code_combination_id  C_VENDOR_TRX_ID,
                    i.invoice_num C_INVOICE_NUMBER,
                    i.invoice_num C_INVOICE_NUM_SHORT,
                    to_char(i.invoice_date,'DD-MON-RR') C_INVOICE_DATE,
                    ceil(to_date(to_char(SYSDATE,'DD-MON-RR'),'DD-MON-RR') -
                                 to_date(ps.due_date,'DD-MON-RR')) C_DAYS_PAST_DUE,
                    decode(i.invoice_currency_code,
                       :C_BASE_CURRENCY_CODE, ' ',
                                              decode(i.exchange_rate,
                                                 NULL,'*',
                                                      ' ')) C_DATA_CONVERTED,
                    nvl(i.exchange_rate, 1) C_EXCHANGE_RATE,
                    decode(:C_VENDOR_NAME_SELECT,
                       '%',decode(:SORT_BY_ALTERNATE, 'Y',  upper(hp.organization_name_phonetic), upper(hp.party_name)),
                              decode(:SORT_BY_ALTERNATE, 'Y', hp.organization_name_phonetic, hp.party_name)) C_VENDOR_NAME_BRK,
                AP_APXINAGE_XMLP_PKG.c_per_inv_due_amt_1formula(:C_SUM_INV_DUE_AMT_1, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_1,
                AP_APXINAGE_XMLP_PKG.c_pgbrk_data_convertedformula(:C_SUM_DATA_CONVERTED) C_PGBRK_DATA_CONVERTED,
                AP_APXINAGE_XMLP_PKG.c_per_inv_due_amt_2formula(:C_SUM_INV_DUE_AMT_2, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_2,
                AP_APXINAGE_XMLP_PKG.c_per_inv_due_amt_3formula(:C_SUM_INV_DUE_AMT_3, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_3,
                AP_APXINAGE_XMLP_PKG.c_per_inv_due_amt_4formula(:C_SUM_INV_DUE_AMT_4, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_4,
                AP_APXINAGE_XMLP_PKG.c_per_v_inv_amt_1formula(:C_SUM_V_INV_AMT_1, :C_SUM_V_DUE_REMAINING) C_PER_V_INV_AMT_1,
                AP_APXINAGE_XMLP_PKG.c_per_v_inv_amt_2formula(:C_SUM_V_INV_AMT_2, :C_SUM_V_DUE_REMAINING) C_PER_V_INV_AMT_2,
                AP_APXINAGE_XMLP_PKG.c_per_v_inv_amt_3formula(:C_SUM_V_INV_AMT_3, :C_SUM_V_DUE_REMAINING) C_PER_V_INV_AMT_3,
                AP_APXINAGE_XMLP_PKG.c_per_v_inv_amt_4formula(:C_SUM_V_INV_AMT_4, :C_SUM_V_DUE_REMAINING) C_PER_V_INV_AMT_4,
                AP_APXINAGE_XMLP_PKG.c_v_data_convertedformula(:C_SUM_V_DATA_CONVERTED) C_V_DATA_CONVERTED,
                AP_APXINAGE_XMLP_PKG.c_contact_lineformula(i.vendor_site_id) C_CONTACT_LINE,
                AP_APXINAGE_XMLP_PKG.c_percent_remainingformula(decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.gross_amount , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.gross_amount , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.gross_amount , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.gross_amount , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_PERCENT_REMAINING,
                AP_APXINAGE_XMLP_PKG.c_inv_due_amt_1formula(ceil ( to_date ( to_char ( SYSDATE , 'DD-MON-RR' ) , 'DD-MON-RR' ) - ps.due_date ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_INV_DUE_AMT_1,
                AP_APXINAGE_XMLP_PKG.c_inv_due_amt_2formula(ceil ( to_date ( to_char ( SYSDATE , 'DD-MON-RR' ) , 'DD-MON-RR' ) - ps.due_date ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_INV_DUE_AMT_2,
                AP_APXINAGE_XMLP_PKG.c_inv_due_amt_3formula(ceil ( to_date ( to_char ( SYSDATE , 'DD-MON-RR' ) , 'DD-MON-RR' ) - ps.due_date ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_INV_DUE_AMT_3,
                AP_APXINAGE_XMLP_PKG.c_inv_due_amt_4formula(ceil ( to_date ( to_char ( SYSDATE , 'DD-MON-RR' ) , 'DD-MON-RR' ) - ps.due_date ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_INV_DUE_AMT_4,
                AP_APXINAGE_XMLP_PKG.c_check_data_convertedformula(decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , ' ' , decode ( i.exchange_rate , NULL , '*' , ' ' ) )) C_CHECK_DATA_CONVERTED
            FROM    ap_payment_schedules ps,
                    ap_invoices i,
                    hz_parties hp,
                    ap_suppliers v,
                    po_vendor_sites vs
            WHERE   i.invoice_id = ps.invoice_id
            AND     i.party_id = hp.party_id
            AND    hp.party_id = v.party_id (+)
            AND     i.vendor_site_id = vs.vendor_site_id (+)
            &P_PARTY_PREDICATE
            AND     ((to_date(to_char(SYSDATE,'DD-MON-RR'),'DD-MON-RR') - ps.due_date)
                          between :C_MINDAYS and  :C_MAXDAYS)
            AND     i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
            AND     i.cancelled_date IS NULL
            AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
            &P_AMOUNT_PREDICATE
            AND     i.payment_status_flag in ('N','P')
            &P_ORDER_BY
Parameter Name SQL text Validation
Operating Unit
 
LOV
Sort Invoices By
 
LOV Oracle
Include Invoice Detail
 
LOV Oracle
Include Site Detail
 
LOV Oracle
Minimum Amount Due
 
Number
Maximum Amount Due
 
Number
Invoice Type
 
LOV Oracle
Trading Partner
 
LOV Oracle
Aging Period Name
 
LOV Oracle
Download
Blitz Report™

Blitz Report™ provides multiple benefits:

Blitz Report™