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 |
