[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(t.multiplier,1, fnd_message.get_string(''FII'',''FII_AP_HOLD_PO''),
2, fnd_message.get_string(''FII'',''FII_AP_HOLD_VAR''),
3, fnd_message.get_string(''FII'',''FII_AP_HOLD_INV''),
4, fnd_message.get_string(''FII'',''FII_AP_HOLD_USR''),
5, fnd_message.get_string(''FII'',''FII_AP_HOLD_OTR''), null) FII_MEASURE1,
DECODE(t.multiplier, 1, SUM(po_matching_hold_count),
2, SUM(variance_hold_count) ,
3, SUM(invoice_hold_count),
4, SUM(user_defined_hold_count),
5, SUM(other_hold_count)) FII_MEASURE2
FROM fii_ap_hcat_ib_mv f,
gl_row_multipliers t,
fii_time_structures cal
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
'||l_supplier_where||' '||l_org_where||'
AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
AND f.hold_release_flag = ''H'' /*added this code for bugno. 3108542*/
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
AND f.gid = :GID
AND t.multiplier in (1,2,3,4,5)
GROUP by t.multiplier';
SELECT
viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
sum(f.FII_MEASURE1) FII_MEASURE1,
sum(f.FII_MEASURE2) FII_MEASURE2,
sum(f.FII_MEASURE3) FII_MEASURE3,
sum(f.FII_MEASURE4) FII_MEASURE4
from
(select id,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE3,
FII_MEASURE4,
( rank() over (order by ID asc)) - 1 rnk
from
(select f.'||l_viewby_id||' id,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.'||l_paid_late_count||' ELSE 0 END) FII_MEASURE1,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.'||l_paid_late_count||' ELSE 0 END) FII_MEASURE2,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.'||l_paid_on_time_count||' ELSE 0 END) FII_MEASURE3,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.'||l_paid_on_time_count||' ELSE 0 END) FII_MEASURE4
FROM FII_AP_PAYOL_XB_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND f.gid = :GID
'||l_org_WHERE||' '||l_supplier_WHERE||'
group by f.'||l_viewby_id||')) f,
'||l_viewby_string||' viewby_dim
where f.id = viewby_dim.id
and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
GROUP BY viewby_dim.value, viewby_dim.id
&ORDER_BY_CLAUSE';
select nvl(min(start_date), trunc(sysdate)) into l_start_date from fii_time_ent_year;
select nvl(fii_time_api.ent_pyr_END(l_as_of_date), l_start_date-1) into l_ent_pyr_END from dual;
select nvl(fii_time_api.ent_cyr_start(l_as_of_date), l_start_date) into l_ent_cyr_start from dual;
select nvl(fii_time_api.ent_cyr_END(l_as_of_date), l_start_date) into l_ent_cyr_END from dual;
select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start
(fii_time_api.ent_pyr_start(l_as_of_date))),l_start_date-1)
into l_ent_pyr_start from dual; /* Bug 3325387 */
select nvl(fii_time_api.pwk_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
select nvl(fii_time_api.sd_lyswk(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
select nvl(fii_time_api.sd_lyswk(l_p_as_of_date), l_start_date) into l_start from dual;
SELECT sequence into l_curr_effective_num
FROM fii_time_week
WHERE l_as_of_date between start_date AND END_date;
select nvl(fii_time_api.ent_pper_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
select nvl(fii_time_api.ent_sd_lysper_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
select nvl(fii_time_api.ent_sd_lysper_END(l_p_as_of_date), l_start_date) into l_start from dual;
SELECT sequence into l_curr_effective_num
FROM fii_time_ent_period
WHERE l_as_of_date between start_date AND END_date;
select nvl(fii_time_api.ent_pqtr_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
select nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
SELECT ent_qtr_id into l_curr_effective_num
FROM fii_time_ent_qtr
WHERE l_as_of_date between start_date AND END_date;
select nvl(fii_time_api.ent_sd_lysqtr_END(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date)), l_start_date) into l_begin_date from dual;
select nvl(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date), l_start_date-1) into l_start from dual;
select nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
SELECT sequence into l_curr_effective_num
FROM fii_time_ent_qtr
WHERE l_as_of_date between start_date AND END_date;
select nvl(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date), l_start_date) into l_start from dual;
select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(l_ent_cyr_start))), l_start_date) into l_begin_date from dual;
inv_graph_sql := 'SELECT
t.name FII_MEASURE3,
max(inline_view.e_invoice_count) FII_MEASURE1,
max(inline_view.m_invoice_count) FII_MEASURE2
FROM
(SELECT inner_inline_view.FII_SEQUENCE FII_SEQUENCE,
SUM(e_invoice_count) e_invoice_count,
SUM(m_invoice_count) m_invoice_count
FROM
( SELECT
t.sequence FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
(f.invoice_count_entered - f.e_invoice_count) m_invoice_count
FROM FII_AP_IVATY_XB_MV f,
'||l_page_period_type||' t
WHERE f.gid = :GID
AND f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between
to_date(:FII_BIND9, ''DD/MM/YYYY'') /* Bug 3325387 */
AND to_date(:FII_BIND10, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT
t.sequence FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
(f.invoice_count_entered - f.e_invoice_count) m_invoice_count
FROM FII_AP_IVATY_XB_MV f,
fii_time_structures cal,
'||l_page_period_type||' t ,
fii_time_day day
WHERE f.gid = :GID
AND f.period_type_id = cal.period_type_id
AND f.time_id = cal.time_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1 /*made changes for bug no.3108435*/
AND cal.report_date = &BIS_CURRENT_ASOF_DATE
AND cal.report_date = day.report_date
AND day.'||l_pk||' = t.'||l_pk||'
'||l_org_WHERE||' '||l_supplier_WHERE||'
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE FII_SEQUENCE (+)= t.sequence
AND t.start_date >= to_date(:FII_BIND14, ''DD/MM/YYYY'')
AND t.END_date <= to_date(:FII_BIND12, ''DD/MM/YYYY'')
GROUP BY t.sequence, t.name, t.'||l_pk||'
ORDER BY t.sequence';
inv_graph_sql := 'SELECT
t.name FII_MEASURE3,
inline_view.e_invoice_count FII_MEASURE1,
inline_view.m_invoice_count FII_MEASURE2
FROM
( SELECT
inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
SUM(e_invoice_count) e_invoice_count,
SUM(m_invoice_count) m_invoice_count
FROM
( SELECT
t.'||l_pk||' FII_SEQUENCE,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
(CASE WHEN t.start_date > to_date(:FII_BIND7,''DD/MM/YYYY'')
AND t.start_date <= to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) e_invoice_count,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
(CASE WHEN t.start_date > to_date(:FII_BIND7,''DD/MM/YYYY'')
AND t.start_date <= to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN (f.invoice_count_entered - f.e_invoice_count) ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) m_invoice_count
FROM FII_AP_IVATY_XB_MV f,
'||l_page_period_type||' t
WHERE f.gid = :GID
AND f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'') AND &BIS_CURRENT_ASOF_DATE
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT
t.'||l_pk||' FII_SEQUENCE,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
(CASE WHEN t.start_date > to_date(:FII_BIND13,''DD/MM/YYYY'')
AND t.start_date <= to_date(:FII_BIND7, ''DD/MM/YYYY'')
THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) e_invoice_count,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
(CASE WHEN t.start_date > to_date(:FII_BIND13,''DD/MM/YYYY'')
AND t.start_date <= to_date(:FII_BIND7, ''DD/MM/YYYY'')
THEN (f.invoice_count_entered - f.e_invoice_count)
ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) m_invoice_count
FROM FII_AP_IVATY_XB_MV f,
'||l_page_period_type||' t
WHERE f.gid = :GID
AND f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND14, ''DD/MM/YYYY'')
AND to_date(:FII_BIND7, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT :FII_BIND5 FII_SEQUENCE,
(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE AND
bitand(cal.record_type_id, :FII_BIND1) = :FII_BIND1
THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END) e_invoice_count,
(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE AND
bitand(cal.record_type_id, :FII_BIND1) = :FII_BIND1
THEN (f.invoice_count_entered - f.e_invoice_count) ELSE TO_NUMBER(NULL) END) m_invoice_count
FROM FII_AP_IVATY_XB_MV f,
fii_time_structures cal
WHERE f.gid = :GID
AND f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, (to_date(:FII_BIND7, ''DD/MM/YYYY'')) )
'||l_org_WHERE||' '||l_supplier_WHERE||'
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE inline_view.fii_effective_num (+)= t.'||l_pk||'
AND t.start_date <= &BIS_CURRENT_ASOF_DATE
AND t.start_date > to_date(:FII_BIND13, ''DD/MM/YYYY'')
ORDER BY t.start_date';
SELECT
t.name FII_MEASURE3,
inline_view.e_invoice_count FII_MEASURE1,
inline_view.m_invoice_count FII_MEASURE2
FROM
(
SELECT inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
SUM(e_invoice_count) e_invoice_count,
SUM(m_invoice_count) m_invoice_count
FROM
(
SELECT
t.sequence FII_SEQUENCE,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) e_invoice_count,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN (f.invoice_count_entered - f.e_invoice_count) ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) m_invoice_count
FROM FII_AP_IVATY_XB_MV f,
'||l_page_period_type||' t
WHERE f.gid = :GID
AND f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'') AND &BIS_CURRENT_ASOF_DATE
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT
:FII_BIND5 FII_SEQUENCE,
(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE AND
bitand(cal.record_type_id, :FII_BIND1) = :FII_BIND1
THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END ) e_invoice_count,
(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE AND
bitand(cal.record_type_id, :FII_BIND1) = :FII_BIND1
THEN (f.invoice_count_entered - f.e_invoice_count) ELSE TO_NUMBER(NULL) END ) m_invoice_count
FROM FII_AP_IVATY_XB_MV f,
fii_time_structures cal
WHERE f.gid = :GID
AND f.period_type_id = cal.period_type_id
AND f.time_id = cal.time_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, (to_date(:FII_BIND7, ''DD/MM/YYYY'')))
'||l_org_WHERE||' '||l_supplier_WHERE||'
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE inline_view.fii_effective_num (+)= t.sequence
AND t.start_date <= &BIS_CURRENT_ASOF_DATE
AND t.start_date > to_date(:FII_BIND14, ''DD/MM/YYYY'')
ORDER BY t.start_date';
select nvl(min(start_date), trunc(sysdate)) into l_start_date from fii_time_ent_year;
select fii_time_api.ent_pyr_start(l_as_of_date) into l_ent_pyr_start from dual;
select nvl(fii_time_api.ent_pyr_END(l_as_of_date), l_start_date-1) into l_ent_pyr_END from dual;
select fii_time_api.ent_cyr_start(l_as_of_date) into l_ent_cyr_start from dual;
select nvl(fii_time_api.ent_cyr_END(l_as_of_date), l_start_date) into l_ent_cyr_END from dual;
select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(l_ent_pyr_start)),l_start_date-1)
into l_ent_year_st1 from dual;
select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(l_ent_cyr_start))),l_start_date-1)
into l_ent_year_st2 from dual;
select nvl(fii_time_api.pwk_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
select nvl(fii_time_api.sd_lyswk(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
select nvl(fii_time_api.sd_lyswk(l_p_as_of_date), l_start_date) into l_start from dual;
SELECT sequence into l_curr_effective_num
FROM fii_time_week
WHERE l_as_of_date between start_date AND END_date;
select nvl(fii_time_api.ent_pper_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
select nvl(fii_time_api.ent_sd_lysper_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
select nvl(fii_time_api.ent_sd_lysper_END(l_p_as_of_date), l_start_date) into l_start from dual;
SELECT sequence into l_curr_effective_num
FROM fii_time_ent_period
WHERE l_as_of_date between start_date AND END_date;
select nvl(fii_time_api.ent_pqtr_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
select nvl(fii_time_api.ent_sd_pqtr_END(l_as_of_date), l_start_date) into
l_p_as_of_date from dual;
SELECT ent_qtr_id into l_curr_effective_num
FROM fii_time_ent_qtr
WHERE l_as_of_date between start_date AND END_date;
select
nvl(fii_time_api.ent_sd_lysqtr_END(nvl(fii_time_api.ent_sd_lysqtr_END(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date)),
l_start_date)), l_start_date) into l_begin_date from dual;
select
fii_time_api.ent_sd_lysqtr_END(nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date),
l_start_date-1)) into l_start from dual;
select nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
SELECT sequence into l_curr_effective_num
FROM fii_time_ent_qtr
WHERE l_as_of_date between start_date AND END_date;
select nvl(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date), l_start_date) into l_start from dual;
elec_late_payment_sql := 'SELECT t.name FII_MEASURE5,
max(inline_view.electronic) FII_MEASURE1,
max(inline_view.prior_electronic) FII_MEASURE2,
max(inline_view.paid_late) FII_MEASURE3,
max(inline_view.prior_paid_late) FII_MEASURE4
FROM
( SELECT
inner_inline_view.fii_sequence FII_SEQUENCE,
(CASE WHEN SUM(invoice_count_entered) = 0 THEN 0
ELSE (SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
TO_NUMBER(NULL) prior_electronic,
(CASE WHEN SUM(paid_invoice_count) = 0 THEN 0
ELSE (SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
TO_NUMBER(NULL) prior_paid_late
FROM
( SELECT
t.sequence FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
f.invoice_count_entered invoice_count_entered,
f.paid_late_count'||ltd||' paid_late_count,
f.paid_inv_count'||ltd||' paid_invoice_count
FROM FII_AP_MGT_KPI_MV f,
'||l_page_period_type||' t
WHERE
f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND15, ''DD/MM/YYYY'') AND to_date(:FII_BIND10, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT
t.sequence FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
f.invoice_count_entered invoice_count_entered,
f.paid_late_count'||ltd||' paid_late_count,
f.paid_inv_count'||ltd||' paid_invoice_count
FROM FII_AP_MGT_KPI_MV f,
fii_time_structures cal,
'||l_page_period_type||' t,
fii_time_day day
WHERE
f.period_type_id = cal.period_type_id
AND f.time_id = cal.time_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date = &BIS_CURRENT_ASOF_DATE
AND cal.report_date = day.report_date
AND day.'||l_pk||' = t.'||l_pk||'
'||l_org_WHERE||' '||l_supplier_WHERE||'
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE FII_SEQUENCE (+)= t.sequence
AND t.start_date >= to_date(:FII_BIND16, ''DD/MM/YYYY'')
AND t.END_date <= to_date(:FII_BIND12, ''DD/MM/YYYY'')
GROUP BY t.sequence, t.name, t.'||l_pk||'
ORDER BY t.sequence';
SELECT t.name FII_MEASURE5,
inline_view.electronic FII_MEASURE1,
inline_view.prior_electronic FII_MEASURE2,
inline_view.paid_late FII_MEASURE3,
inline_view.prior_paid_late FII_MEASURE4
FROM
( SELECT
inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
(CASE WHEN SUM(invoice_count_entered) = 0 THEN 0 ELSE
(SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
to_number(null) prior_electronic,
(CASE WHEN SUM(paid_invoice_count) = 0 THEN 0 ELSE
(SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
to_number(null) prior_paid_late
FROM
(
SELECT
t.'||l_pk||' FII_SEQUENCE,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
f.e_invoice_count ELSE TO_NUMBER(NULL) END ) e_invoice_count,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN f.invoice_count_entered
ELSE TO_NUMBER(NULL) END ) invoice_count_entered,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
f.paid_late_count'||ltd||'
ELSE TO_NUMBER(NULL) END ) paid_late_count,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
f.paid_inv_count'||ltd||'
ELSE TO_NUMBER(NULL) END ) paid_invoice_count
FROM FII_AP_MGT_KPI_MV f,
'||l_page_period_type||' t
WHERE
f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND14, ''DD/MM/YYYY'') AND to_date(:FII_BIND7, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT :FII_BIND5 FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
f.invoice_count_entered invoice_count_entered,
f.paid_late_count'||ltd||' paid_late_count,
f.paid_inv_count'||ltd||' paid_invoice_count
FROM FII_AP_MGT_KPI_MV f,
fii_time_structures cal
WHERE
f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
'||l_org_WHERE||' '||l_supplier_WHERE||'
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE inline_view.fii_effective_num (+)= t.'||l_pk||'
AND t.start_date <= &BIS_CURRENT_ASOF_DATE
AND t.start_date > to_date(:FII_BIND13, ''DD/MM/YYYY'')
ORDER BY t.start_date';
SELECT t.name FII_MEASURE5,
inline_view.electronic FII_MEASURE1,
inline_view.prior_electronic FII_MEASURE2,
inline_view.paid_late FII_MEASURE3,
inline_view.prior_paid_late FII_MEASURE4
FROM
( SELECT
inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
(CASE WHEN SUM(invoice_count_entered) = 0 THEN 0 ELSE
(SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
to_number(null) prior_electronic,
(CASE WHEN SUM(paid_invoice_count) = 0 THEN 0 ELSE
(SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
to_number(null) prior_paid_late
FROM
( SELECT
t.sequence FII_SEQUENCE,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) e_invoice_count,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.invoice_count_entered ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) invoice_count_entered,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7, ''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.paid_late_count'||ltd||' ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) paid_late_count,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.paid_inv_count'||ltd||'
ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) paid_invoice_count
FROM FII_AP_MGT_KPI_MV f,
'||l_page_period_type||' t
WHERE
f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'') AND
to_date(:FII_BIND8, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT
:FII_BIND5 FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
f.invoice_count_entered invoice_count_entered,
f.paid_late_count'||ltd||' paid_late_count,
f.paid_inv_count'||ltd||' paid_invoice_count
FROM FII_AP_MGT_KPI_MV f,
fii_time_structures cal
WHERE
f.period_type_id = cal.period_type_id
AND f.time_id = cal.time_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
'||l_org_WHERE||' '||l_supplier_WHERE||'
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE inline_view.fii_effective_num (+)= t.sequence
AND t.start_date <= &BIS_CURRENT_ASOF_DATE
AND t.start_date > to_date(:FII_BIND14, ''DD/MM/YYYY'')
ORDER BY t.start_date';
elec_late_payment_sql := 'SELECT t.name FII_MEASURE5,
max(inline_view.electronic) FII_MEASURE1,
max(inline_view.prior_electronic) FII_MEASURE2,
max(inline_view.paid_late) FII_MEASURE3,
max(inline_view.prior_paid_late) FII_MEASURE4
FROM
( SELECT
inner_inline_view.fii_sequence FII_SEQUENCE,
(CASE WHEN SUM(invoice_count_entered) = 0 THEN 0
ELSE (SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
TO_NUMBER(NULL) prior_electronic,
(CASE WHEN SUM(paid_invoice_count) = 0 THEN 0
ELSE (SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
TO_NUMBER(NULL) prior_paid_late
FROM
(
(
SELECT
t.sequence FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
f.invoice_count_entered invoice_count_entered,
0 paid_late_count,
0 paid_invoice_count
FROM FII_AP_IVATY_XB_MV f,
'||l_page_period_type||' t
WHERE f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND15, ''DD/MM/YYYY'') AND to_date(:FII_BIND10, ''DD/MM/YYYY'')
'||l_org_where||' '||l_supplier_WHERE||'
UNION ALL
SELECT
t.sequence FII_SEQUENCE,
0 e_invoice_count,
0 invoice_count_entered,
f.paid_late_count'||ltd||' paid_late_count,
(f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) paid_invoice_count
FROM FII_AP_PAYOL_XB_MV f,
'||l_page_period_type||' t
WHERE f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND15, ''DD/MM/YYYY'')
AND to_date(:FII_BIND10, ''DD/MM/YYYY'')
'||l_org_where||' '||l_supplier_WHERE||'
)
UNION ALL
(
SELECT
t.sequence FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
f.invoice_count_entered invoice_count_entered,
0 paid_late_count,
0 paid_invoice_count
FROM FII_AP_IVATY_XB_MV f,
fii_time_structures cal,
'||l_page_period_type||' t,
fii_time_day day
WHERE
f.period_type_id = cal.period_type_id
AND f.time_id = cal.time_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date = &BIS_CURRENT_ASOF_DATE
AND cal.report_date = day.report_date
AND day.'||l_pk||' = t.'||l_pk||'
'||l_org_where||' '||l_supplier_WHERE||'
UNION ALL
SELECT
t.sequence FII_SEQUENCE,
0 e_invoice_count,
0 invoice_count_entered,
f.paid_late_count'||ltd||' paid_late_count,
(f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) paid_invoice_count
FROM FII_AP_PAYOL_XB_MV f,
fii_time_structures cal,
'||l_page_period_type||' t,
fii_time_day day
WHERE f.period_type_id = cal.period_type_id
AND f.time_id = cal.time_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date = &BIS_CURRENT_ASOF_DATE
AND cal.report_date = day.report_date
AND day.'||l_pk||' = t.'||l_pk||'
'||l_org_where||' '||l_supplier_WHERE||'
)
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE FII_SEQUENCE (+)= t.sequence
AND t.start_date >= to_date(:FII_BIND16, ''DD/MM/YYYY'')
AND t.END_date <= to_date(:FII_BIND12, ''DD/MM/YYYY'')
GROUP BY t.sequence, t.name, t.'||l_pk||'
ORDER BY t.sequence';
SELECT t.name FII_MEASURE5,
inline_view.electronic FII_MEASURE1,
inline_view.prior_electronic FII_MEASURE2,
inline_view.paid_late FII_MEASURE3,
inline_view.prior_paid_late FII_MEASURE4
FROM
( SELECT
inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
(CASE WHEN SUM(invoice_count_entered) = 0 THEN 0 ELSE
(SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
to_number(null) prior_electronic,
(CASE WHEN SUM(paid_invoice_count) = 0 THEN 0 ELSE
(SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
to_number(null) prior_paid_late
FROM
(
(
SELECT
t.'||l_pk||' FII_SEQUENCE,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN f.e_invoice_count
ELSE TO_NUMBER(NULL) END ) e_invoice_count,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN f.invoice_count_entered
ELSE TO_NUMBER(NULL) END ) invoice_count_entered,
0 paid_late_count,
0 paid_invoice_count
FROM FII_AP_IVATY_XB_MV f,
'||l_page_period_type||' t
WHERE
f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND14, ''DD/MM/YYYY'') AND to_date(:FII_BIND7, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
union all
SELECT
t.'||l_pk||' FII_SEQUENCE,
0 e_invoice_count,
0 invoice_count_entered,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN f.paid_late_count'||ltd||'
ELSE TO_NUMBER(NULL) END ) paid_late_count,
(CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN (f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||')
ELSE TO_NUMBER(NULL) END ) paid_invoice_count
FROM FII_AP_PAYOL_XB_MV f,
'||l_page_period_type||' t
WHERE
f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND14, ''DD/MM/YYYY'') AND to_date(:FII_BIND7, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT :FII_BIND5 FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
f.invoice_count_entered invoice_count_entered,
0 paid_late_count,
0 paid_invoice_count
FROM FII_AP_IVATY_XB_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date = (&BIS_CURRENT_ASOF_DATE)
'||l_org_WHERE||' '||l_supplier_WHERE||'
union all
SELECT :FII_BIND5 FII_SEQUENCE,
0 e_invoice_count,
0 invoice_count_entered,
f.paid_late_count'||ltd||' paid_late_count,
(f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) paid_invoice_count
FROM FII_AP_PAYOL_XB_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date = (&BIS_CURRENT_ASOF_DATE)
'||l_org_WHERE||' '||l_supplier_WHERE||'
)
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE inline_view.fii_effective_num (+)= t.'||l_pk||'
AND t.start_date <= &BIS_CURRENT_ASOF_DATE
AND t.start_date > to_date(:FII_BIND13, ''DD/MM/YYYY'')
ORDER BY t.start_date';
SELECT t.name FII_MEASURE5,
inline_view.electronic FII_MEASURE1,
inline_view.prior_electronic FII_MEASURE2,
inline_view.paid_late FII_MEASURE3,
inline_view.prior_paid_late FII_MEASURE4
FROM
(
SELECT
inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
(CASE WHEN SUM(invoice_count_entered) = 0 THEN 0 ELSE
(SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
to_number(null) prior_electronic,
(CASE WHEN SUM(paid_invoice_count) = 0 THEN 0 ELSE
(SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
to_number(null) prior_paid_late
FROM
(
(
SELECT
t.sequence FII_SEQUENCE,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) e_invoice_count,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.invoice_count_entered ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) invoice_count_entered,
0 paid_late_count,
0 paid_invoice_count
FROM FII_AP_IVATY_XB_MV f,
'||l_page_period_type||' t
WHERE f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'')
AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT
t.sequence FII_SEQUENCE,
0 e_invoice_count,
0 invoice_count_entered,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7, ''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN f.paid_late_count'||ltd||' ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) paid_late_count,
(CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
to_date(:FII_BIND7, ''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
THEN (f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) ELSE TO_NUMBER(NULL) END)
ELSE TO_NUMBER(NULL) END ) paid_invoice_count
FROM FII_AP_PAYOL_XB_MV f,
'||l_page_period_type||' t
WHERE f.time_id = t.'||l_pk||'
AND f.period_type_id = :FII_BIND6
AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'')
AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
'||l_org_WHERE||' '||l_supplier_WHERE||'
)
UNION ALL
(
SELECT
:FII_BIND5 FII_SEQUENCE,
f.e_invoice_count e_invoice_count,
f.invoice_count_entered invoice_count_entered,
0 paid_late_count,
0 paid_invoice_count
FROM FII_AP_IVATY_XB_MV f,
fii_time_structures cal
WHERE f.period_type_id = cal.period_type_id
AND f.time_id = cal.time_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date = (&BIS_CURRENT_ASOF_DATE)
'||l_org_WHERE||' '||l_supplier_WHERE||'
UNION ALL
SELECT
:FII_BIND5 FII_SEQUENCE,
0 e_invoice_count,
0 invoice_count_entered,
f.paid_late_count'||ltd||' paid_late_count,
(f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) paid_invoice_count
FROM FII_AP_PAYOL_XB_MV f,
fii_time_structures cal
WHERE f.period_type_id = cal.period_type_id
AND f.time_id = cal.time_id
AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
AND cal.report_date = (&BIS_CURRENT_ASOF_DATE)
'||l_org_WHERE||' '||l_supplier_WHERE||'
)
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE
) inline_view, '||l_page_period_type||' t
WHERE inline_view.fii_effective_num (+)= t.sequence
AND t.start_date <= &BIS_CURRENT_ASOF_DATE
AND t.start_date > to_date(:FII_BIND14, ''DD/MM/YYYY'')
ORDER BY t.start_date';
SELECT viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
f.invoice_count_entered_cur FII_MEASURE1,
f.invoice_count_entered_pre FII_MEASURE2,
decode(f.invoice_count_entered_cur,0,0,
(f.e_invoice_count_cur * 100 /f.invoice_count_entered_cur))
FII_MEASURE3,
decode(f.invoice_count_entered_pre,0,0,
(f.e_invoice_count_pre * 100 /f.invoice_count_entered_pre))
FII_MEASURE4,
f.paid_inv_count_cur FII_MEASURE5,
f.paid_inv_count_pre FII_MEASURE6,
decode(f.paid_inv_count_cur,0,0,
(f.paid_late_count_cur *100/f.paid_inv_count_cur))
FII_MEASURE7,
decode(f.paid_inv_count_pre,0,0,
(f.paid_late_count_pre *100/f.paid_inv_count_pre))
FII_MEASURE8,
decode(f.paid_amt_cur,0,0,
(f.invoice_to_payment_days_cur / f.paid_amt_cur))
FII_MEASURE9,
decode(f.paid_amt_pre,0,0,
(f.invoice_to_payment_days_pre / f.paid_amt_pre))
FII_MEASURE10,
f.payment_count_cur FII_MEASURE11,
f.payment_count_pre FII_MEASURE12,
decode(f.paid_invoice_amt_cur,0,0,
(f.paid_dis_offered_cur * 100 /f.paid_invoice_amt_cur))
FII_MEASURE13,
decode(f.paid_invoice_amt_pre,0,0,
(f.paid_dis_offered_pre * 100 /f.paid_invoice_amt_pre))
FII_MEASURE14,
decode(f.total_paid_amt_cur,0,0,
(f.paid_dis_taken_cur * 100 /f.total_paid_amt_cur))
FII_MEASURE15,
decode(f.total_paid_amt_pre,0,0,
(f.paid_dis_taken_pre * 100 /f.total_paid_amt_pre))
FII_MEASURE16,
decode(sum(f.e_invoice_count_cur) over(),0,0,
sum(f.e_invoice_count_cur) over() *100 /
sum(f.invoice_count_entered_cur) over())
FII_ATTRIBUTE1,
decode(sum(f.e_invoice_count_pre) over(),0,0,
sum(f.e_invoice_count_pre) over() *100 /
sum(f.invoice_count_entered_pre) over())
FII_ATTRIBUTE2,
decode(sum(f.paid_inv_count_cur) over(),0,0,
sum(f.paid_late_count_cur) over() *100 /
sum(f.paid_inv_count_cur) over())
FII_ATTRIBUTE3,
decode(sum(f.paid_inv_count_pre) over(),0,0,
sum(f.paid_late_count_pre) over() *100 /
sum(f.paid_inv_count_pre) over())
FII_ATTRIBUTE4,
decode(sum(f.paid_invoice_amt_cur) over(),0,0,
sum(f.paid_dis_offered_cur) over() *100 /
sum(f.paid_invoice_amt_cur) over())
FII_ATTRIBUTE5,
decode(sum(f.paid_invoice_amt_pre) over(),0,0,
sum(f.paid_dis_offered_pre) over() *100 /
sum(f.paid_invoice_amt_pre) over())
FII_ATTRIBUTE6,
decode(sum(f.total_paid_amt_cur) over(),0,0,
sum(f.paid_dis_taken_cur) over() *100 /
sum(f.total_paid_amt_cur) over())
FII_ATTRIBUTE7,
decode(sum(f.total_paid_amt_pre) over(),0,0,
sum(f.paid_dis_taken_pre) over() *100 /
sum(f.total_paid_amt_pre) over())
FII_ATTRIBUTE8,
decode(sum(f.paid_amt_cur) over(),0,0,
sum(f.invoice_to_payment_days_cur) over() /
sum(f.paid_amt_cur) over())
FII_ATTRIBUTE10,
decode(sum(f.paid_amt_pre) over(),0,0,
sum(f.invoice_to_payment_days_pre) over() /
sum(f.paid_amt_pre) over())
FII_ATTRIBUTE11,
sum(f.invoice_count_entered_cur) over() FII_ATTRIBUTE12,
sum(f.invoice_count_entered_pre) over() FII_ATTRIBUTE13,
sum(f.paid_inv_count_cur) over() FII_ATTRIBUTE14,
sum(f.paid_inv_count_pre) over() FII_ATTRIBUTE15,
sum(f.payment_count_cur) over() FII_ATTRIBUTE16,
sum(f.payment_count_pre) over() FII_ATTRIBUTE17
FROM
(SELECT
f.'||l_viewby_id||' ID,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
then f.invoice_count_entered else to_number(null) end) invoice_count_entered_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.invoice_count_entered else to_number(null) end) invoice_count_entered_pre,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
then f.e_invoice_count else to_number(null) end) e_invoice_count_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.e_invoice_count else to_number(null) end) e_invoice_count_pre,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
then f.'||l_paid_inv_count||' else to_number(null) end) paid_inv_count_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.'||l_paid_inv_count||' else to_number(null) end) paid_inv_count_pre,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
then f.'||l_paid_on_time_count||' else to_number(null) end) paid_on_time_count_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.'||l_paid_on_time_count||' else to_number(null) end) paid_on_time_count_pre,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
then f.'||l_paid_late_count||' else to_number(null) end) paid_late_count_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.'||l_paid_late_count||' else to_number(null) end) paid_late_count_pre,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
then f.invoice_to_payment_days else to_number(null) end)
invoice_to_payment_days_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.invoice_to_payment_days else to_number(null) end)
invoice_to_payment_days_pre,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
then f.paid_amt_b else to_number(null) end)
paid_amt_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.paid_amt_b else to_number(null) end)
paid_amt_pre,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
then f.paid_invoice_amt'||l_per_type||l_curr_suffix||' else to_number(null) end) paid_invoice_amt_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.paid_invoice_amt'||l_per_type||l_curr_suffix||' else to_number(null) end) paid_invoice_amt_pre,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
then f.paid_dis_offered'||l_per_type||l_curr_suffix||' else to_number(null) end) paid_dis_offered_cur,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
then f.paid_dis_offered'||l_per_type||l_curr_suffix||' else to_number(null) end) paid_dis_offered_pre,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
then f.'||l_payment_count||' else to_number(null) end) payment_count_cur,
sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then f.'||l_payment_count||' else to_number(null) end) payment_count_pre,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
then (f.paid_amt'||l_curr_suffix||'+ f.paid_dis_taken'||l_curr_suffix||') else to_number(null) end) total_paid_amt_cur,
sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then (f.paid_amt'||l_curr_suffix||'+ f.paid_dis_taken'||l_curr_suffix||') else to_number(null) end) total_paid_amt_pre,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
then f.paid_dis_taken'||l_curr_suffix||' else to_number(null) end) paid_dis_taken_cur,
sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then f.paid_dis_taken'||l_curr_suffix||' else to_number(null) end) paid_dis_taken_pre
FROM FII_AP_MGT_KPI_MV f, fii_time_structures cal
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
'||l_sup_where||' '||l_org_where||'
AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
GROUP BY f.'||l_viewby_id||') f,
('||l_viewby_string||') viewby_dim
WHERE f.id = viewby_dim.id';