JE Hungarian Payables Invoice Aging - draft

Description
Categories: BI Publisher
Application: European Localizations
Source: Hungarian Payables Invoice Aging Report (XML) - Not Supported: Reserved For Future Use
Short Name: JEHUIAGE_XML
DB package: JE_JEHUIAGE_XMLP_PKG
SELECT  substr(p.party_name,1,80)  C_VENDOR_NAME,
               UPPER(substr(p.party_name,1,80)) C_UPPER_NAME,
               p.organization_name_phonetic C_VENDOR_NAME_ALT,             
               UPPER(p.organization_name_phonetic) C_UPPER_NAME_ALT,
        decode(upper(:P_SORT_OPTION),
          'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
                      '%',upper(substr(p.party_name,1,80)), 
                          substr(p.party_name,1,80)),
                   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(p.organization_name_phonetic), upper(substr(p.party_name,1,80))), 
                              decode(:SORT_BY_ALTERNATE, 'Y', p.organization_name_phonetic, substr(p.party_name,1,80))),
                   i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,
        substr(p.party_name,1,80)  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,
        vs.vendor_site_code_alt C_VENDOR_SITE_CODE_ALT,
        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(:C_BASE_MIN_ACCT_UNIT,
           0,round((nvl(ps.amount_remaining, 0) *
                    nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
             round((nvl(ps.amount_remaining, 0) *
                    nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                              :C_BASE_MIN_ACCT_UNIT)  C_AMT_DUE_REMAINING,
        decode(:C_BASE_MIN_ACCT_UNIT,
           0,round((nvl(ps.gross_amount, 0) *
                    nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
             round((nvl(ps.gross_amount, 0) *
                    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,
        null                  C_CHECK_NUMBER,
        to_char(i.invoice_date,'DD-MON-RR') C_INVOICE_DATE,
        ceil(trunc(SYSDATE) - ps.due_date) 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(p.organization_name_phonetic), upper(substr(p.party_name,1,80))),
                  decode(:SORT_BY_ALTERNATE, 'Y', p.organization_name_phonetic, substr(p.party_name,1,80))) C_VENDOR_NAME_BRK,
                   'Invoice'					C_PART_OF_SELECT, 
	JE_JEHUIAGE_XMLP_PKG.c_per_inv_due_amt_1formula(:C_SUM_INV_DUE_AMT_1, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_1, 
	JE_JEHUIAGE_XMLP_PKG.c_pgbrk_data_convertedformula(:C_SUM_DATA_CONVERTED) C_PGBRK_DATA_CONVERTED, 
	JE_JEHUIAGE_XMLP_PKG.c_per_inv_due_amt_2formula(:C_SUM_INV_DUE_AMT_2, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_2, 
	JE_JEHUIAGE_XMLP_PKG.c_per_inv_due_amt_3formula(:C_SUM_INV_DUE_AMT_3, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_3, 
	JE_JEHUIAGE_XMLP_PKG.c_per_inv_due_amt_4formula(:C_SUM_INV_DUE_AMT_4, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_4, 
	JE_JEHUIAGE_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, 
	JE_JEHUIAGE_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, 
	JE_JEHUIAGE_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, 
	JE_JEHUIAGE_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, 
	JE_JEHUIAGE_XMLP_PKG.c_v_data_convertedformula(:C_SUM_V_DATA_CONVERTED) C_V_DATA_CONVERTED, 
	JE_JEHUIAGE_XMLP_PKG.c_contact_lineformula(i.vendor_site_id) C_CONTACT_LINE, 
	JE_JEHUIAGE_XMLP_PKG.c_percent_remainingformula(decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.gross_amount , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.gross_amount , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_PERCENT_REMAINING, 
	JE_JEHUIAGE_XMLP_PKG.c_check_data_convertedformula(decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , ' ' , decode ( i.exchange_rate , NULL , '*' , ' ' ) )) C_CHECK_DATA_CONVERTED, 
	JE_JEHUIAGE_XMLP_PKG.cf_due_dateformula(nvl ( to_char ( ps.due_date , 'DD-MON-RR' ) , ' ' )) CF_DUE_DATE, 
	JE_JEHUIAGE_XMLP_PKG.c_inv_due_amt_1formula(ceil ( trunc ( SYSDATE ) - ps.due_date ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_INV_DUE_AMT_1, 
	JE_JEHUIAGE_XMLP_PKG.c_inv_due_amt_2formula(ceil ( trunc ( SYSDATE ) - ps.due_date ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_INV_DUE_AMT_2, 
	JE_JEHUIAGE_XMLP_PKG.c_inv_due_amt_3formula(ceil ( trunc ( SYSDATE ) - ps.due_date ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_INV_DUE_AMT_3, 
	JE_JEHUIAGE_XMLP_PKG.c_inv_due_amt_4formula(ceil ( trunc ( SYSDATE ) - ps.due_date ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_INV_DUE_AMT_4
FROM    ap_payment_schedules ps,
        ap_invoices i,
        ap_suppliers v,
        ap_supplier_sites vs,
        hz_parties p
WHERE   i.invoice_id = ps.invoice_id
AND     p.party_id = v.party_id
AND     i.set_of_books_id = :P_LEDGER_ID
AND     i.legal_entity_id = NVL(:P_LEGAL_ENTITY_ID, i.legal_entity_id)
AND     i.vendor_id = v.vendor_id
AND     i.vendor_site_id = vs.vendor_site_id
&P_VENDOR_PREDICATE
AND     ((trunc(SYSDATE) - 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')
UNION ALL
SELECT  substr(p.party_name,1,80)  C_VENDOR_NAME,
               UPPER(substr(p.party_name,1,80))  C_UPPER_NAME,
               p.organization_name_phonetic C_VENDOR_NAME_ALT,             
               UPPER(p.organization_name_phonetic) C_UPPER_NAME_ALT,
        decode(upper(:P_SORT_OPTION),
          'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
                      '%',upper(substr(p.party_name,1,80)), 
                          substr(p.party_name,1,80)),
                  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(p.organization_name_phonetic), upper(substr(p.party_name,1,80))), 
                              decode(:SORT_BY_ALTERNATE, 'Y', p.organization_name_phonetic, substr(p.party_name,1,80))),
                   i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,
        substr(p.party_name,1,80)  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,
       vs.vendor_site_code_alt C_VENDOR_SITE_CODE_ALT,
      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,
       nvl(ip.payment_base_amount, ip.amount) C_AMT_DUE_REMAINING,
        decode(:C_BASE_MIN_ACCT_UNIT,
           0,round((nvl(ps.gross_amount, 0) *
                    nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
             round((nvl(ps.gross_amount, 0) *
                    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,
        &P_SELECT_CHECK_NUMBER                C_CHECK_NUMBER,
        to_char(i.invoice_date,'DD-MON-RR') C_INVOICE_DATE,
        ceil(trunc(SYSDATE) - ps.due_date) 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(p.organization_name_phonetic), upper(substr(p.party_name,1,80))),
                  decode(:SORT_BY_ALTERNATE, 'Y', p.organization_name_phonetic, substr(p.party_name,1,80))) C_VENDOR_NAME_BRK,
                   'Payment'					C_PART_OF_SELECT, 
	JE_JEHUIAGE_XMLP_PKG.c_per_inv_due_amt_1formula(:C_SUM_INV_DUE_AMT_1, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_1, 
	JE_JEHUIAGE_XMLP_PKG.c_pgbrk_data_convertedformula(:C_SUM_DATA_CONVERTED) C_PGBRK_DATA_CONVERTED, 
	JE_JEHUIAGE_XMLP_PKG.c_per_inv_due_amt_2formula(:C_SUM_INV_DUE_AMT_2, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_2, 
	JE_JEHUIAGE_XMLP_PKG.c_per_inv_due_amt_3formula(:C_SUM_INV_DUE_AMT_3, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_3, 
	JE_JEHUIAGE_XMLP_PKG.c_per_inv_due_amt_4formula(:C_SUM_INV_DUE_AMT_4, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_4, 
	JE_JEHUIAGE_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, 
	JE_JEHUIAGE_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, 
	JE_JEHUIAGE_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, 
	JE_JEHUIAGE_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, 
	JE_JEHUIAGE_XMLP_PKG.c_v_data_convertedformula(:C_SUM_V_DATA_CONVERTED) C_V_DATA_CONVERTED, 
	JE_JEHUIAGE_XMLP_PKG.c_contact_lineformula(i.vendor_site_id) C_CONTACT_LINE, 
	JE_JEHUIAGE_XMLP_PKG.c_percent_remainingformula(decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.gross_amount , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.gross_amount , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_PERCENT_REMAINING, 
	JE_JEHUIAGE_XMLP_PKG.c_check_data_convertedformula(decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , ' ' , decode ( i.exchange_rate , NULL , '*' , ' ' ) )) C_CHECK_DATA_CONVERTED, 
	JE_JEHUIAGE_XMLP_PKG.cf_due_dateformula(nvl ( to_char ( ps.due_date , 'DD-MON-RR' ) , ' ' )) CF_DUE_DATE, 
	JE_JEHUIAGE_XMLP_PKG.c_inv_due_amt_1formula(ceil ( trunc ( SYSDATE ) - ps.due_date ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_INV_DUE_AMT_1, 
	JE_JEHUIAGE_XMLP_PKG.c_inv_due_amt_2formula(ceil ( trunc ( SYSDATE ) - ps.due_date ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_INV_DUE_AMT_2, 
	JE_JEHUIAGE_XMLP_PKG.c_inv_due_amt_3formula(ceil ( trunc ( SYSDATE ) - ps.due_date ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_INV_DUE_AMT_3, 
	JE_JEHUIAGE_XMLP_PKG.c_inv_due_amt_4formula(ceil ( trunc ( SYSDATE ) - ps.due_date ), decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( nvl ( ps.amount_remaining , 0 ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT )) C_INV_DUE_AMT_4
FROM    ap_suppliers v,
        ap_supplier_sites vs,
        ap_invoices i,
        ap_checks c,
        ap_payment_schedules ps,
        ap_invoice_payments ip,
        hz_parties p
WHERE  v.vendor_id = i.vendor_id
AND    p.party_id = v.party_id
AND    i.set_of_books_id = :P_LEDGER_ID
AND    i.legal_entity_id = NVL(:P_LEGAL_ENTITY_ID, i.legal_entity_id)
&P_VENDOR_PREDICATE
AND    vs.vendor_site_id = i.vendor_site_id  
AND    i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
AND    i.cancelled_date IS NULL
AND    i.invoice_id = ip.invoice_id
AND    c.status_lookup_code = 'NEGOTIABLE'
AND    c.check_id = ip.check_id
AND    ((trunc(SYSDATE) - ps.due_date) BETWEEN :C_MINDAYS AND :C_MAXDAYS)
AND    ps.payment_num = ip.payment_num
AND    ps.invoice_id       = ip.invoice_id
AND    nvl(ip.payment_base_amount,ip.amount) != 0
&P_CHECK_AMOUNT_PREDICATE
&P_ORDER_BY
Parameter Name SQL text Validation
Report Type
 
LOV Oracle
Aging Period Type
 
LOV Oracle
Trading Partner (skip for All)
 
LOV Oracle
Invoice Type (skip for All)
 
LOV Oracle
Maximum Amount Due
 
Number
Minimum Amount Due
 
Number
Report Format
 
LOV Oracle
Summary Type
 
LOV Oracle
Sort Invoices By
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ledger
 
LOV Oracle