JE Hungarian Payables Invoice Aging - draft

Description
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
JE Hungarian Payables Invoice Aging - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
Copy
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_