[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlstmt := 'select action FII_MEASURE1,
to_char(action_date, '''||l_date_mask||''') FII_MEASURE2,
usr.user_name FII_MEASURE3
from
(select :CREATED action,
creation_date action_date,
created_by by_whom
from ap_checks_all
where check_id=:CHECK_ID
and creation_date is not null
union all
select :STOPPED action,
stopped_date action_date,
stopped_by by_whom
from ap_checks_all
where check_id=:CHECK_ID
and stopped_date is not null
union all
select :STOP_RELEASED action,
released_date action_date,
released_by by_whom
from ap_checks_all
where check_id=:CHECK_ID
and released_date is not null
union all
select :CLEARED action,
creation_date action_date,
created_by by_whom
from ap_payment_history_all
where check_id=:CHECK_ID
and transaction_type=''PAYMENT CLEARING''
and matched_flag=''N''
and creation_date is not null
union all
select :RECONCILED action,
creation_date action_date,
created_by by_whom
from ap_payment_history_all
where check_id=:CHECK_ID
and transaction_type=''PAYMENT CLEARING''
and matched_flag=''Y''
and creation_date is not null
union all
select :UNRECONCILED action,
creation_date action_date,
created_by by_whom
from ap_payment_history_all
where check_id=:CHECK_ID
and transaction_type=''PAYMENT UNCLEARING''
and matched_flag=''Y''
and creation_date is not null
union all
select :UNCLEARED action,
creation_date action_date,
created_by by_whom
from ap_payment_history_all
where check_id=:CHECK_ID
and transaction_type=''PAYMENT UNCLEARING''
and matched_flag=''N''
and creation_date is not null
union all
select :VOIDED action,
apc.void_date action_date,
pay.last_updated_by by_whom
from ap_checks_all apc, ap_invoice_payments_all pay
where apc.check_id=:CHECK_ID
and apc.check_id=pay.check_id
and void_date is not null
) a,
fnd_user_view usr
Where a.by_whom = usr.user_id
&ORDER_BY_CLAUSE';
SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
4.column aliases for the columns selected by sub-query match the AK MEASURE name
5.moved urls to top level. moved grand totals to second level.
3.AK FII_AP_PAID_INV_DETAIL # of rows displayed = -30, # of rows displayed in portlet = -10.
*/
/* Main SQL section */
IF l_report_source = 'FII_AP_PAID_INV_DETAIL' then
sqlstmt := '
Select h.FII_MEASURE1 FII_MEASURE1,
h.FII_MEASURE2 FII_MEASURE2,
h.FII_MEASURE3 FII_MEASURE3,
h.FII_MEASURE4 FII_MEASURE4,
h.FII_MEASURE5 FII_MEASURE5,
h.FII_MEASURE6 FII_MEASURE6,
h.FII_MEASURE7 FII_MEASURE7,
h.FII_MEASURE8 FII_MEASURE8,
h.FII_MEASURE9 FII_MEASURE9,
h.FII_MEASURE10 FII_MEASURE10,
h.FII_MEASURE11 FII_MEASURE11,
h.FII_MEASURE12 FII_MEASURE12,
h.FII_MEASURE13 FII_MEASURE13,
h.FII_MEASURE14 FII_MEASURE14,
h.FII_MEASURE15 FII_MEASURE15,
h.FII_MEASURE16 FII_MEASURE16,
h.FII_MEASURE17 FII_MEASURE17,
h.FII_MEASURE18 FII_MEASURE18,
h.FII_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_ATTRIBUTE2 FII_ATTRIBUTE2,
h.FII_ATTRIBUTE3 FII_ATTRIBUTE3,
h.FII_ATTRIBUTE4 FII_ATTRIBUTE4,
h.FII_ATTRIBUTE5 FII_ATTRIBUTE5,
h.FII_ATTRIBUTE6 FII_ATTRIBUTE6,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
from
(
Select g.FII_MEASURE1 FII_MEASURE1,
g.FII_MEASURE2 FII_MEASURE2,
g.FII_MEASURE3 FII_MEASURE3,
g.FII_MEASURE4 FII_MEASURE4,
g.FII_MEASURE5 FII_MEASURE5,
g.FII_MEASURE6 FII_MEASURE6,
g.FII_MEASURE7 FII_MEASURE7,
g.FII_MEASURE8 FII_MEASURE8,
g.FII_MEASURE9 FII_MEASURE9,
g.FII_MEASURE10 FII_MEASURE10,
g.FII_MEASURE11 FII_MEASURE11,
g.FII_MEASURE12 FII_MEASURE12,
g.FII_MEASURE13 FII_MEASURE13,
g.FII_MEASURE14 FII_MEASURE14,
g.FII_MEASURE15 FII_MEASURE15,
g.FII_MEASURE16 FII_MEASURE16,
g.FII_MEASURE17 FII_MEASURE17,
g.FII_MEASURE18 FII_MEASURE18,
sum(g.FII_MEASURE9) over() FII_MEASURE21,
sum(g.FII_MEASURE16) over() FII_MEASURE22,
sum(g.FII_MEASURE10) over() FII_ATTRIBUTE2,
sum(g.FII_MEASURE11) over() FII_ATTRIBUTE3,
sum(g.FII_MEASURE12) over() FII_ATTRIBUTE4,
sum(g.FII_MEASURE14) over() FII_ATTRIBUTE5,
sum(g.FII_MEASURE15) over() FII_ATTRIBUTE6,
( rank() over (&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) - 1 rnk
from
(
select
f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
to_char(f.entered_date,'''||l_date_mask||''') FII_MEASURE5,
to_char(min(f.due_date), '''||l_date_mask||''') FII_MEASURE6,
f.invoice_currency_code FII_MEASURE7,
sum(f.base_amount) FII_MEASURE8,
sum(f.invoice_amount) FII_MEASURE9,
sum(f.payment_amount) FII_MEASURE10,
sum(f.on_time_payment_amount) FII_MEASURE11,
sum(f.late_payment_amount) FII_MEASURE12,
decode(nvl(hold.FII_MEASURE13, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE13,
sum(f.discount_offered) FII_MEASURE14,
sum(f.discount_taken) FII_MEASURE15,
sum(f.discount_lost) FII_MEASURE16,
term.name FII_MEASURE17,
f.source FII_MEASURE18
from
(
select base.invoice_number invoice_number,
base.invoice_id invoice_id,
base.invoice_type invoice_type,
base.invoice_date invoice_date,
base.entered_date entered_date,
min(f.due_date) due_date,
base.invoice_currency_code invoice_currency_code,
base.invoice_amount base_amount,
base.'||l_invoice_amount||' invoice_amount,
sum(f.'||l_payment_amount||') payment_amount,
sum(f.'||l_on_time_payment_amt||') on_time_payment_amount,
sum(f.'||l_late_payment_amt||') late_payment_amount,
base.'||l_discount_offered||' discount_offered,
sum(f.'||l_discount_taken||') discount_taken,
sum(f.'||l_discount_lost||') discount_lost,
base.source source,
base.terms_id,
base.org_id,
base.supplier_id
from fii_ap_invoice_b base,
fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''PAYMENT''
and base.invoice_id = f.invoice_id
and base.cancel_flag = ''N'' '
||l_org_where||l_supplier_where|| '
group by base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.invoice_amount,
base.'||l_invoice_amount||',
base.'||l_discount_offered||',
base.source,
base.terms_id,
base.org_id,
base.supplier_id
union
select base.invoice_number invoice_number,
base.invoice_id invoice_id,
base.invoice_type invoice_type,
base.invoice_date invoice_date,
base.entered_date entered_date,
min(base.due_date) due_date,
base.invoice_currency_code invoice_currency_code,
0 base_amount,
0 invoice_amount,
0 payment_amount,
0 on_time_payment_amount,
0 late_payment_amount,
0 discount_offered,
0 discount_taken,
sum(f.'||l_discount_lost||') discount_lost,
base.source source,
base.terms_id terms_id,
base.org_id org_id,
base.supplier_id supplier_id
from fii_ap_invoice_b base,
fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''DISCOUNT''
and base.invoice_id = f.invoice_id
and base.cancel_flag = ''N'' '
||l_org_where||l_supplier_where|| '
and f.invoice_id in (select distinct f.invoice_id
from fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''PAYMENT''
'||l_org_where||l_supplier_where|| '
)
group by base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.source,
base.terms_id,
base.org_id,
base.supplier_id
)
f, (select distinct invoice_id,
''Y'' FII_MEASURE13
from fii_ap_inv_holds_b f
where 1 = 1
'||l_org_where||l_supplier_where|| '
group by invoice_id) hold,
ap_terms_tl term, POA_SUPPLIERS_V viewby_dim
where hold.invoice_id (+)= f.invoice_id
and f.SUPPLIER_ID = viewby_dim.id
and f.terms_id = term.term_id
and term.language = userenv(''LANG'')
group by f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
hold.FII_MEASURE13,
term.name,
f.source
) g
) h
where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE';
Select h.FII_MEASURE1 FII_MEASURE1,
h.FII_MEASURE2 FII_MEASURE2,
h.FII_MEASURE3 FII_MEASURE3,
h.FII_MEASURE4 FII_MEASURE4,
h.FII_MEASURE5 FII_MEASURE5,
h.FII_MEASURE6 FII_MEASURE6,
h.FII_MEASURE7 FII_MEASURE7,
h.FII_MEASURE8 FII_MEASURE8,
h.FII_MEASURE9 FII_MEASURE9,
h.FII_MEASURE10 FII_MEASURE10,
h.FII_MEASURE11 FII_MEASURE11,
h.FII_MEASURE12 FII_MEASURE12,
h.FII_MEASURE13 FII_MEASURE13,
h.FII_MEASURE14 FII_MEASURE14,
h.FII_MEASURE15 FII_MEASURE15,
h.FII_MEASURE16 FII_MEASURE16,
h.FII_MEASURE17 FII_MEASURE17,
h.FII_MEASURE18 FII_MEASURE18,
h.FII_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_ATTRIBUTE2 FII_ATTRIBUTE2,
h.FII_ATTRIBUTE3 FII_ATTRIBUTE3,
h.FII_ATTRIBUTE4 FII_ATTRIBUTE4,
h.FII_ATTRIBUTE5 FII_ATTRIBUTE5,
h.FII_ATTRIBUTE6 FII_ATTRIBUTE6,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
from
(
Select g.FII_MEASURE1 FII_MEASURE1,
g.FII_MEASURE2 FII_MEASURE2,
g.FII_MEASURE3 FII_MEASURE3,
g.FII_MEASURE4 FII_MEASURE4,
g.FII_MEASURE5 FII_MEASURE5,
g.FII_MEASURE6 FII_MEASURE6,
g.FII_MEASURE7 FII_MEASURE7,
g.FII_MEASURE8 FII_MEASURE8,
g.FII_MEASURE9 FII_MEASURE9,
g.FII_MEASURE10 FII_MEASURE10,
g.FII_MEASURE11 FII_MEASURE11,
g.FII_MEASURE12 FII_MEASURE12,
g.FII_MEASURE13 FII_MEASURE13,
g.FII_MEASURE14 FII_MEASURE14,
g.FII_MEASURE15 FII_MEASURE15,
g.FII_MEASURE16 FII_MEASURE16,
g.FII_MEASURE17 FII_MEASURE17,
g.FII_MEASURE18 FII_MEASURE18,
sum(g.FII_MEASURE9) over() FII_MEASURE21,
sum(g.FII_MEASURE16) over() FII_MEASURE22,
sum(g.FII_MEASURE10) over() FII_ATTRIBUTE2,
sum(g.FII_MEASURE11) over() FII_ATTRIBUTE3,
sum(g.FII_MEASURE12) over() FII_ATTRIBUTE4,
sum(g.FII_MEASURE14) over() FII_ATTRIBUTE5,
sum(g.FII_MEASURE15) over() FII_ATTRIBUTE6,
( rank() over (&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) - 1 rnk
from
(
select
f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
to_char(f.entered_date,'''||l_date_mask||''') FII_MEASURE5,
to_char(min(f.due_date), '''||l_date_mask||''') FII_MEASURE6,
f.invoice_currency_code FII_MEASURE7,
sum(f.base_amount) FII_MEASURE8,
sum(f.invoice_amount) FII_MEASURE9,
sum(f.payment_amount) FII_MEASURE10,
sum(f.on_time_payment_amount) FII_MEASURE11,
sum(f.late_payment_amount) FII_MEASURE12,
decode(nvl(hold.FII_MEASURE13, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE13,
sum(f.discount_offered) FII_MEASURE14,
sum(f.discount_taken) FII_MEASURE15,
sum(f.discount_lost) FII_MEASURE16,
term.name FII_MEASURE17,
f.source FII_MEASURE18
from
(
select base.invoice_number invoice_number,
base.invoice_id invoice_id,
base.invoice_type invoice_type,
base.invoice_date invoice_date,
base.entered_date entered_date,
min(f.due_date) due_date,
base.invoice_currency_code invoice_currency_code,
base.invoice_amount base_amount,
base.'||l_invoice_amount||' invoice_amount,
sum(f.'||l_payment_amount||') payment_amount,
sum(f.'||l_on_time_payment_amt||') on_time_payment_amount,
sum(f.'||l_late_payment_amt||') late_payment_amount,
base.'||l_discount_offered||' discount_offered,
sum(f.'||l_discount_taken||') discount_taken,
sum(f.'||l_discount_lost||') discount_lost,
base.source source,
base.terms_id,
base.org_id,
base.supplier_id
from fii_ap_invoice_b base,
fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''PAYMENT''
and f.check_id = :CHECK_ID
and base.invoice_id = f.invoice_id
and base.cancel_flag = ''N'' '
||l_org_where||l_supplier_where|| '
group by base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.invoice_amount,
base.'||l_invoice_amount||',
base.'||l_discount_offered||',
base.source,
base.terms_id,
base.org_id,
base.supplier_id
union
select base.invoice_number invoice_number,
base.invoice_id invoice_id,
base.invoice_type invoice_type,
base.invoice_date invoice_date,
base.entered_date entered_date,
min(base.due_date) due_date,
base.invoice_currency_code invoice_currency_code,
0 base_amount,
0 invoice_amount,
0 payment_amount,
0 on_time_payment_amount,
0 late_payment_amount,
0 discount_offered,
0 discount_taken,
sum(f.'||l_discount_lost||') discount_lost,
base.source source,
base.terms_id terms_id,
base.org_id org_id,
base.supplier_id supplier_id
from fii_ap_invoice_b base,
fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''DISCOUNT'' '
||l_org_where||l_supplier_where|| '
and base.invoice_id = f.invoice_id
and base.cancel_flag = ''N''
and f.invoice_id in (select distinct f.invoice_id
from fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''PAYMENT''
and f.check_id = :CHECK_ID '
||l_org_where||l_supplier_where|| '
)
group by base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.source,
base.terms_id,
base.org_id,
base.supplier_id
)
f, (select distinct invoice_id,
''Y'' FII_MEASURE13
from fii_ap_inv_holds_b f
where 1 = 1
'||l_org_where||l_supplier_where|| '
group by invoice_id) hold,
ap_terms_tl term, POA_SUPPLIERS_V viewby_dim
where hold.invoice_id (+)= f.invoice_id
and f.SUPPLIER_ID = viewby_dim.id '
||l_org_where||l_supplier_where|| '
and f.terms_id = term.term_id
and term.language = userenv(''LANG'')
group by f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
hold.FII_MEASURE13,
term.name,
f.source
) g
) h
where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE';
Select h.FII_MEASURE1 FII_MEASURE1,
h.FII_MEASURE2 FII_MEASURE2,
h.FII_MEASURE3 FII_MEASURE3,
h.FII_MEASURE4 FII_MEASURE4,
h.FII_MEASURE5 FII_MEASURE5,
h.FII_MEASURE6 FII_MEASURE6,
h.FII_MEASURE7 FII_MEASURE7,
h.FII_MEASURE8 FII_MEASURE8,
h.FII_MEASURE9 FII_MEASURE9,
h.FII_MEASURE10 FII_MEASURE10,
h.FII_MEASURE11 FII_MEASURE11,
h.FII_MEASURE12 FII_MEASURE12,
h.FII_MEASURE13 FII_MEASURE13,
h.FII_MEASURE14 FII_MEASURE14,
h.FII_MEASURE15 FII_MEASURE15,
h.FII_MEASURE16 FII_MEASURE16,
h.FII_MEASURE17 FII_MEASURE17,
h.FII_MEASURE18 FII_MEASURE18,
h.FII_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_ATTRIBUTE2 FII_ATTRIBUTE2,
h.FII_ATTRIBUTE3 FII_ATTRIBUTE3,
h.FII_ATTRIBUTE4 FII_ATTRIBUTE4,
h.FII_ATTRIBUTE5 FII_ATTRIBUTE5,
h.FII_ATTRIBUTE6 FII_ATTRIBUTE6,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
from
(
Select g.FII_MEASURE1 FII_MEASURE1,
g.FII_MEASURE2 FII_MEASURE2,
g.FII_MEASURE3 FII_MEASURE3,
g.FII_MEASURE4 FII_MEASURE4,
g.FII_MEASURE5 FII_MEASURE5,
g.FII_MEASURE6 FII_MEASURE6,
g.FII_MEASURE7 FII_MEASURE7,
g.FII_MEASURE8 FII_MEASURE8,
g.FII_MEASURE9 FII_MEASURE9,
g.FII_MEASURE10 FII_MEASURE10,
g.FII_MEASURE11 FII_MEASURE11,
g.FII_MEASURE12 FII_MEASURE12,
g.FII_MEASURE13 FII_MEASURE13,
g.FII_MEASURE14 FII_MEASURE14,
g.FII_MEASURE15 FII_MEASURE15,
g.FII_MEASURE16 FII_MEASURE16,
g.FII_MEASURE17 FII_MEASURE17,
g.FII_MEASURE18 FII_MEASURE18,
sum(g.FII_MEASURE9) over() FII_MEASURE21,
sum(g.FII_MEASURE16) over() FII_MEASURE22,
sum(g.FII_MEASURE10) over() FII_ATTRIBUTE2,
sum(g.FII_MEASURE11) over() FII_ATTRIBUTE3,
sum(g.FII_MEASURE12) over() FII_ATTRIBUTE4,
sum(g.FII_MEASURE14) over() FII_ATTRIBUTE5,
sum(g.FII_MEASURE15) over() FII_ATTRIBUTE6,
( rank() over (&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) - 1 rnk
from
(
select
f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
to_char(f.entered_date,'''||l_date_mask||''') FII_MEASURE5,
to_char(min(f.due_date), '''||l_date_mask||''') FII_MEASURE6,
f.invoice_currency_code FII_MEASURE7,
sum(f.base_amount) FII_MEASURE8,
sum(f.invoice_amount) FII_MEASURE9,
sum(f.payment_amount) FII_MEASURE10,
sum(f.on_time_payment_amount) FII_MEASURE11,
sum(f.late_payment_amount) FII_MEASURE12,
decode(nvl(hold.FII_MEASURE13, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE13,
sum(f.discount_offered) FII_MEASURE14,
sum(f.discount_taken) FII_MEASURE15,
sum(f.discount_lost) FII_MEASURE16,
term.name FII_MEASURE17,
f.source FII_MEASURE18
from
(
select base.invoice_number invoice_number,
base.invoice_id invoice_id,
base.invoice_type invoice_type,
base.invoice_date invoice_date,
base.entered_date entered_date,
min(f.due_date) due_date,
base.invoice_currency_code invoice_currency_code,
base.invoice_amount base_amount,
base.'||l_invoice_amount||' invoice_amount,
sum(f.'||l_payment_amount||') payment_amount,
sum(f.'||l_on_time_payment_amt||') on_time_payment_amount,
sum(f.'||l_late_payment_amt||') late_payment_amount,
base.'||l_discount_offered||' discount_offered,
sum(f.'||l_discount_taken||') discount_taken,
sum(f.'||l_discount_lost||') discount_lost,
base.source source,
base.terms_id,
base.org_id,
base.supplier_id
from fii_ap_invoice_b base,
fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''PAYMENT''
and f.check_id = :CHECK_ID
and f.'||l_late_payment_amt||'<> 0
and base.invoice_id = f.invoice_id
and base.cancel_flag = ''N'' '
||l_org_where||l_supplier_where|| '
group by base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.invoice_amount,
base.'||l_invoice_amount||',
base.'||l_discount_offered||',
base.source,
base.terms_id,
base.org_id,
base.supplier_id
union
select base.invoice_number invoice_number,
base.invoice_id invoice_id,
base.invoice_type invoice_type,
base.invoice_date invoice_date,
base.entered_date entered_date,
min(base.due_date) due_date,
base.invoice_currency_code invoice_currency_code,
0 base_amount,
0 invoice_amount,
0 payment_amount,
0 on_time_payment_amount,
0 late_payment_amount,
0 discount_offered,
0 discount_taken,
sum(f.'||l_discount_lost||') discount_lost,
base.source source,
base.terms_id terms_id,
base.org_id org_id,
base.supplier_id supplier_id
from fii_ap_invoice_b base,
fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''DISCOUNT'' '
||l_org_where||l_supplier_where|| '
and base.invoice_id = f.invoice_id
and base.cancel_flag = ''N''
and f.invoice_id in (select distinct f.invoice_id
from fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''PAYMENT''
and f.check_id = :CHECK_ID
and f.'||l_late_payment_amt||'<> 0 '
||l_org_where||l_supplier_where|| '
)
group by base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.source,
base.terms_id,
base.org_id,
base.supplier_id
)
f, (select distinct invoice_id,
''Y'' FII_MEASURE13
from fii_ap_inv_holds_b f
where 1 = 1
'||l_org_where||l_supplier_where|| '
group by invoice_id) hold,
ap_terms_tl term, POA_SUPPLIERS_V viewby_dim
where hold.invoice_id (+)= f.invoice_id
and f.SUPPLIER_ID = viewby_dim.id '
||l_org_where||l_supplier_where||'
and f.terms_id = term.term_id
and term.language = userenv(''LANG'')
group by f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
hold.FII_MEASURE13,
term.name,
f.source
) g
) h
where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE';
Select h.FII_MEASURE1 FII_MEASURE1,
h.FII_MEASURE2 FII_MEASURE2,
h.FII_MEASURE3 FII_MEASURE3,
h.FII_MEASURE4 FII_MEASURE4,
h.FII_MEASURE5 FII_MEASURE5,
h.FII_MEASURE6 FII_MEASURE6,
h.FII_MEASURE7 FII_MEASURE7,
h.FII_MEASURE8 FII_MEASURE8,
h.FII_MEASURE9 FII_MEASURE9,
h.FII_MEASURE10 FII_MEASURE10,
h.FII_MEASURE11 FII_MEASURE11,
h.FII_MEASURE12 FII_MEASURE12,
h.FII_MEASURE13 FII_MEASURE13,
h.FII_MEASURE14 FII_MEASURE14,
h.FII_MEASURE15 FII_MEASURE15,
h.FII_MEASURE16 FII_MEASURE16,
h.FII_MEASURE17 FII_MEASURE17,
h.FII_MEASURE18 FII_MEASURE18,
h.FII_MEASURE21 FII_MEASURE21,
h.FII_MEASURE22 FII_MEASURE22,
h.FII_ATTRIBUTE2 FII_ATTRIBUTE2,
h.FII_ATTRIBUTE3 FII_ATTRIBUTE3,
h.FII_ATTRIBUTE4 FII_ATTRIBUTE4,
h.FII_ATTRIBUTE5 FII_ATTRIBUTE5,
h.FII_ATTRIBUTE6 FII_ATTRIBUTE6,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_2||''' FII_ATTRIBUTE11,
'''||l_url_3||''' FII_ATTRIBUTE12,
'''||l_url_4||''' FII_ATTRIBUTE13
from
(
Select g.FII_MEASURE1 FII_MEASURE1,
g.FII_MEASURE2 FII_MEASURE2,
g.FII_MEASURE3 FII_MEASURE3,
g.FII_MEASURE4 FII_MEASURE4,
g.FII_MEASURE5 FII_MEASURE5,
g.FII_MEASURE6 FII_MEASURE6,
g.FII_MEASURE7 FII_MEASURE7,
g.FII_MEASURE8 FII_MEASURE8,
g.FII_MEASURE9 FII_MEASURE9,
g.FII_MEASURE10 FII_MEASURE10,
g.FII_MEASURE11 FII_MEASURE11,
g.FII_MEASURE12 FII_MEASURE12,
g.FII_MEASURE13 FII_MEASURE13,
g.FII_MEASURE14 FII_MEASURE14,
g.FII_MEASURE15 FII_MEASURE15,
g.FII_MEASURE16 FII_MEASURE16,
g.FII_MEASURE17 FII_MEASURE17,
g.FII_MEASURE18 FII_MEASURE18,
sum(g.FII_MEASURE9) over() FII_MEASURE21,
sum(g.FII_MEASURE16) over() FII_MEASURE22,
sum(g.FII_MEASURE10) over() FII_ATTRIBUTE2,
sum(g.FII_MEASURE11) over() FII_ATTRIBUTE3,
sum(g.FII_MEASURE12) over() FII_ATTRIBUTE4,
sum(g.FII_MEASURE14) over() FII_ATTRIBUTE5,
sum(g.FII_MEASURE15) over() FII_ATTRIBUTE6,
( rank() over (&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) - 1 rnk
from
(
select
f.invoice_number FII_MEASURE1,
f.invoice_id FII_MEASURE2,
f.invoice_type FII_MEASURE3,
to_char(f.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
to_char(f.entered_date,'''||l_date_mask||''') FII_MEASURE5,
to_char(min(f.due_date), '''||l_date_mask||''') FII_MEASURE6,
f.invoice_currency_code FII_MEASURE7,
sum(f.base_amount) FII_MEASURE8,
sum(f.invoice_amount) FII_MEASURE9,
sum(f.payment_amount) FII_MEASURE10,
sum(f.on_time_payment_amount) FII_MEASURE11,
sum(f.late_payment_amount) FII_MEASURE12,
decode(nvl(hold.FII_MEASURE13, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE13,
sum(f.discount_offered) FII_MEASURE14,
sum(f.discount_taken) FII_MEASURE15,
sum(f.discount_lost) FII_MEASURE16,
term.name FII_MEASURE17,
f.source FII_MEASURE18
from
(
select base.invoice_number invoice_number,
base.invoice_id invoice_id,
base.invoice_type invoice_type,
base.invoice_date invoice_date,
base.entered_date entered_date,
min(f.due_date) due_date,
base.invoice_currency_code invoice_currency_code,
base.invoice_amount base_amount,
base.'||l_invoice_amount||' invoice_amount,
sum(f.'||l_payment_amount||') payment_amount,
sum(f.'||l_on_time_payment_amt||') on_time_payment_amount,
sum(f.'||l_late_payment_amt||') late_payment_amount,
base.'||l_discount_offered||' discount_offered,
sum(f.'||l_discount_taken||') discount_taken,
sum(f.'||l_discount_lost||') discount_lost,
base.source source,
base.terms_id,
base.org_id,
base.supplier_id
from fii_ap_invoice_b base,
fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''PAYMENT''
and f.check_id = :CHECK_ID
and f.no_days_late = 0
and base.invoice_id = f.invoice_id
and base.cancel_flag = ''N'' '
||l_org_where||l_supplier_where||'
group by base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.invoice_amount,
base.'||l_invoice_amount||',
base.'||l_discount_offered||',
base.source,
base.terms_id,
base.org_id,
base.supplier_id
union
select base.invoice_number invoice_number,
base.invoice_id invoice_id,
base.invoice_type invoice_type,
base.invoice_date invoice_date,
base.entered_date entered_date,
min(base.due_date) due_date,
base.invoice_currency_code invoice_currency_code,
0 base_amount,
0 invoice_amount,
0 payment_amount,
0 on_time_payment_amount,
0 late_payment_amount,
0 discount_offered,
0 discount_taken,
sum(f.'||l_discount_lost||') discount_lost,
base.source source,
base.terms_id terms_id,
base.org_id org_id,
base.supplier_id supplier_id
from fii_ap_invoice_b base,
fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''DISCOUNT'' '
||l_org_where||l_supplier_where|| '
and base.invoice_id = f.invoice_id
and base.cancel_flag = ''N''
and f.invoice_id in (select distinct f.invoice_id
from fii_ap_pay_sched_b f
where f.action_date >= :PERIOD_START
and f.action_date <= &BIS_CURRENT_ASOF_DATE
and f.action = ''PAYMENT''
and f.check_id = :CHECK_ID
and f.no_days_late = 0 '
||l_org_where||l_supplier_where||'
)
group by base.invoice_number,
base.invoice_id,
base.invoice_type,
base.invoice_date,
base.entered_date,
base.invoice_currency_code,
base.source,
base.terms_id,
base.org_id,
base.supplier_id
)
f, (select distinct invoice_id,
''Y'' FII_MEASURE13
from fii_ap_inv_holds_b f
where 1 = 1
'||l_org_where||l_supplier_where|| '
group by invoice_id) hold,
ap_terms_tl term, POA_SUPPLIERS_V viewby_dim
where hold.invoice_id (+)= f.invoice_id
and f.SUPPLIER_ID = viewby_dim.id '
||l_org_where||l_supplier_where||'
and f.terms_id = term.term_id
and term.language = userenv(''LANG'')
group by f.invoice_number,
f.invoice_id,
f.invoice_type,
f.invoice_date,
f.entered_date,
f.invoice_currency_code,
hold.FII_MEASURE13,
term.name,
f.source
) g
) h
where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE';
sqlstmt := 'select f.check_number FII_MEASURE1,
f.check_id FII_MEASURE2,
code.payment_method_name FII_MEASURE3,
decode('''||l_currency||''', ''_prim_g'', nvl(f.base_amount, f.amount)*fii_currency.get_global_rate_primary(asp.base_currency_code,f.check_date),
''_sec_g'', nvl(f.base_amount, f.amount)*fii_currency.get_global_rate_secondary(asp.base_currency_code,f.check_date),
''_b'', nvl(f.base_amount, f.amount)*fii_currency.get_rate(asp.base_currency_code, '''||l_curr||''', f.check_date, bis_common_parameters.get_rate_type)
)
FII_MEASURE4,
f.check_date FII_MEASURE5,
code1.displayed_field FII_MEASURE6,
f.bank_account_name FII_MEASURE7,
f.bank_account_num FII_MEASURE8,
bankacct.bank_name FII_MEASURE9,
bankacct.bank_number FII_MEASURE10,
f.currency_code FII_MEASURE12,
f.amount FII_MEASURE11,
count(distinct pay.invoice_id) FII_MEASURE13,
count(distinct case when pay.no_days_late <> 0 then i.invoice_id else null end) FII_MEASURE14,
count(distinct case when pay.no_days_late = 0 then i.invoice_id else null end) FII_MEASURE15,
sum(decode('''||l_currency||''', ''_prim_g'', nvl(f.base_amount, f.amount)*fii_currency.get_global_rate_primary(asp.base_currency_code,f.check_date),
''_sec_g'', nvl(f.base_amount, f.amount)*fii_currency.get_global_rate_secondary(asp.base_currency_code,f.check_date),
''_b'', nvl(f.base_amount, f.amount)*fii_currency.get_rate(asp.base_currency_code,'''||l_curr||''' , f.check_date, bis_common_parameters.get_rate_type)
)) over() FII_MEASURE20,
sum(count(distinct pay.invoice_id)) over() FII_MEASURE21,
sum(count(distinct case when pay.no_days_late <> 0 then i.invoice_id else null end)) over() FII_ATTRIBUTE2,
sum(count(distinct case when pay.no_days_late = 0 then i.invoice_id else null end)) over() FII_ATTRIBUTE3,
'''||l_url_1||''' FII_ATTRIBUTE10,
'''||l_url_1||''' FII_ATTRIBUTE11,
'''||l_url_2||''' FII_ATTRIBUTE12,
'''||l_url_3||''' FII_ATTRIBUTE13,
'''||l_url_4||''' FII_ATTRIBUTE14
from ap_checks_all f, IBY_PAYMENT_METHODS_VL code, ap_lookup_codes code1,
iby_payee_assigned_bankacct_v bankacct, ap_invoices_all i,
ap_system_parameters_all asp,
fii_ap_pay_sched_b pay
where trunc(pay.action_date) <= &BIS_CURRENT_ASOF_DATE
and trunc(pay.action_date) >= :PERIOD_START
and code1.lookup_type = ''CHECK STATE''
and f.payment_method_code = code.payment_method_code
and f.status_lookup_code = code1.lookup_code
and f.external_bank_account_id = bankacct.ext_bank_account_id(+)
and f.check_id = pay.check_id
and f.void_date is null
and pay.invoice_id = i.invoice_id
and pay.action = ''PAYMENT''
and i.org_id = asp.org_id
and i.invoice_type_lookup_code <> ''EXPENSE REPORT''
'
||l_org_where||l_supplier_where||'
group by f.check_number, f.check_id, code.payment_method_name, f.amount,
f.check_date, code1.displayed_field, f.bank_account_name,
f.bank_account_num, bankacct.bank_name, bankacct.bank_number,
f.currency_code, asp.base_currency_code, f.base_amount
&ORDER_BY_CLAUSE ';