rendered paste bodySELECT substr(gc2.segment2,1,6)||'-'||v.vendor_name C_VENDOR_NAME,
decode(upper(:P_SORT_OPTION),
'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
'%',upper(v.vendor_name),
v.vendor_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(v.vendor_name_alt), upper(v.vendor_name)),
decode(:SORT_BY_ALTERNATE, 'Y', v.vendor_name_alt, v.vendor_name)),
i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,
v.vendor_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,
/* Bug: 1549982 */
/* Bug 2083419 For foreign currencies, show amounts in functional currency */
decode(i.invoice_currency_code,:C_BASE_CURRENCY_CODE,
decode(:C_BASE_MIN_ACCT_UNIT,
0,round(((nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
round(((nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 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(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
round(((nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 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')) -
ceil(:P_DATE - --modified by CFE 18/11/2004
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(v.vendor_name_alt), upper(v.vendor_name)),
decode(:SORT_BY_ALTERNATE, 'Y', v.vendor_name_alt, v.vendor_name)) C_VENDOR_NAME_BRK
,gc.segment1 C_SEGMENT1 -- code projet
FROM ap_payment_schedules ps,
ap_invoices i,
po_vendors v,
po_vendor_sites vs
-- Début ajout B.Saerens le 18/02/2004
,gl_code_combinations gc
,gl_code_combinations gc2
-- xxst_flex_value_hierarchy_v2 a,
-- fnd_flex_value_norm_hierarchy b
-- Fin ajout B.Saerens le 18/02/2004
WHERE i.invoice_id = ps.invoice_id
AND i.vendor_id = v.vendor_id
AND i.vendor_site_id = vs.vendor_site_id
AND i.gl_date <= :P_DATE
AND gc.code_combination_id = (select d.DIST_CODE_COMBINATION_ID
from ap_invoice_distributions d
where d.invoice_id = i.invoice_id
and LINE_TYPE_LOOKUP_CODE = 'ITEM'
and rownum = 1)
and gc2.code_combination_id = i.accts_pay_code_combination_id
--and gc.segment1 = b.child_flex_value_low
--and a.flex_value_set_id = b.flex_value_set_id
--and a.parent_flex_value = b.parent_flex_value
and (EXISTS ( select 'x'
from xxst_flex_value_hierarchy_v2 a
, fnd_flex_value_norm_hierarchy b
where a.flex_value_set_id = b.flex_value_set_id
and a.parent_flex_value = b.parent_flex_value
and b.child_flex_value_low = gc.segment1)
OR fnd_profile.value('XXST_GL_FULL_ACCESS')='Y'
)
and EXISTS (Select aid.invoice_id, min(trunc(aid.accounting_date))
from ap_invoice_distributions_all aid
Where
aid.invoice_id = i.invoice_id
group by aid.invoice_id, trunc(aid.accounting_date)
having sum(aid.amount) != 0 And min(trunc(aid.accounting_date)) <= :P_DATE )
&P_VENDOR_PREDICATE
-- AND ((to_date(to_char(SYSDATE,'DD-MON-RR')) - ps.due_date)
AND (trunc(:P_DATE) - trunc(ps.due_date) -- modified by CFE 18/11/2004
between :C_MINDAYS and :C_MAXDAYS)
AND i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
AND ( (i.cancelled_date IS NULL) or (i.cancelled_date > :P_date)) --modified by CFE 06/03/05
AND (nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 0) * nvl(i.exchange_rate,1)) != 0
&P_AMOUNT_PREDICATE
-- AND i.payment_status_flag in ('N','P') --modified by CFE 25/11/2004
&P_ORDER_BY