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
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 |