AP Invoice Aging - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Invoice Aging Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXINAGE_XML
DB package: AP_APXINAGE_XMLP_PKG
            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 NameSQL textValidation
Ledger
 
Aging Period Name
 
LOV Oracle
Trading Partner
 
LOV Oracle
Invoice Type
 
LOV Oracle
Maximum Amount Due
 
Number
Minimum Amount Due
 
Number
Include Site Detail
 
LOV Oracle
Include Invoice Detail
 
LOV Oracle
Sort Invoices By
 
LOV Oracle