[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
f.FII_MEASURE1 FII_MEASURE1,
f.FII_MEASURE2 FII_MEASURE2,
f.FII_MEASURE4 FII_MEASURE4,
f.FII_MEASURE5 FII_MEASURE5,
f.FII_MEASURE7 FII_MEASURE7,
f.FII_MEASURE8 FII_MEASURE8,
f.FII_MEASURE10 FII_MEASURE10,
f.FII_MEASURE11 FII_MEASURE11,
f.FII_MEASURE13 FII_MEASURE13,
f.FII_MEASURE14 FII_MEASURE14,
f.FII_MEASURE16 FII_MEASURE16,
f.FII_MEASURE17 FII_MEASURE17,
f.FII_MEASURE19 FII_MEASURE19,
f.FII_MEASURE20 FII_MEASURE20,
f.FII_MEASURE22 FII_MEASURE22,
f.FII_MEASURE23 FII_MEASURE23,
f.FII_MEASURE25 FII_MEASURE25,
f.FII_MEASURE26 FII_MEASURE26,
f.FII_MEASURE28 FII_MEASURE28,
f.FII_MEASURE29 FII_MEASURE29,
f.FII_MEASURE30 FII_MEASURE30,
f.FII_DIM1 FII_DIM1,
f.FII_DIM2 FII_DIM2,
f.FII_DIM3 FII_DIM3,
f.FII_DIM4 FII_DIM4,
f.FII_DIM5 FII_DIM5,
f.FII_DIM7 FII_DIM7,
f.FII_DIM8 FII_DIM8,
f.FII_DIM9 FII_DIM9,
f.FII_DIM10 FII_DIM10,
f.FII_FSG_COL1 FII_FSG_COL1,
f.FII_FSG_COL2 FII_FSG_COL2,
f.FII_FSG_COL3 FII_FSG_COL3,
f.FII_FSG_COL4 FII_FSG_COL4,
f.FII_FSG_COL5 FII_FSG_COL5,
f.FII_FSG_COL6 FII_FSG_COL6,
'''||l_url_1||''' FII_ATTRIBUTE11,
'''||l_url_2||''' FII_ATTRIBUTE12,
'''||l_url_3||''' FII_ATTRIBUTE13
FROM
(SELECT
id,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE4,
FII_MEASURE5,
FII_MEASURE7,
FII_MEASURE8,
FII_MEASURE10,
FII_MEASURE11,
FII_MEASURE13,
FII_MEASURE14,
FII_MEASURE16,
FII_MEASURE17,
FII_MEASURE19,
FII_MEASURE20,
FII_MEASURE22,
FII_MEASURE23,
FII_MEASURE25,
FII_MEASURE26,
SUM(FII_MEASURE1) OVER() FII_MEASURE28,
SUM(FII_MEASURE2) OVER() FII_MEASURE29,
SUM(FII_MEASURE4) OVER() FII_MEASURE30,
SUM(FII_MEASURE5) OVER() FII_DIM1,
SUM(FII_MEASURE7) OVER() FII_DIM2,
SUM(FII_MEASURE8) OVER() FII_DIM3,
SUM(FII_MEASURE10) OVER() FII_DIM4,
SUM(FII_MEASURE11) OVER() FII_DIM5,
SUM(FII_MEASURE13) OVER() FII_DIM7,
SUM(FII_MEASURE14) OVER() FII_DIM8,
SUM(FII_MEASURE16) OVER() FII_DIM9,
SUM(FII_MEASURE17) OVER() FII_DIM10,
SUM(FII_MEASURE19) OVER() FII_FSG_COL1,
SUM(FII_MEASURE20) OVER() FII_FSG_COL2,
SUM(FII_MEASURE22) OVER() FII_FSG_COL3,
SUM(FII_MEASURE23) OVER() FII_FSG_COL4,
SUM(FII_MEASURE25) OVER() FII_FSG_COL5,
SUM(FII_MEASURE26) OVER() FII_FSG_COL6,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
FROM
(
SELECT f.'||l_viewby_id||' id,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE1,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE2,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE4,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE5,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.e_invoice_count ELSE 0 END) FII_MEASURE13,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.e_invoice_count ELSE 0 END) FII_MEASURE14,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.distribution_count ELSE 0 END) FII_MEASURE7,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.distribution_count ELSE 0 END) FII_MEASURE8,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.e_distribution_count ELSE 0 END) FII_MEASURE16,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.e_distribution_count ELSE 0 END) FII_MEASURE17,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.e_invoice_amt'||l_currency||' ELSE 0 END) FII_MEASURE10,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.e_invoice_amt'||l_currency||' ELSE 0 END) FII_MEASURE11,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_amt_entered'||l_currency||' - f.e_invoice_amt'||l_currency||'
ELSE 0 END) FII_MEASURE19,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.invoice_amt_entered'||l_currency||' - f.e_invoice_amt'||l_currency||'
ELSE 0 END) FII_MEASURE20,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_count_entered - f.e_invoice_count ELSE 0 END) FII_MEASURE22,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.invoice_count_entered - f.e_invoice_count ELSE 0 END) FII_MEASURE23,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.distribution_count - f.e_distribution_count ELSE 0 END) FII_MEASURE25,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.distribution_count - f.e_distribution_count ELSE 0 END) FII_MEASURE26
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
'||l_org_WHERE||l_supplier_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)
AND f.gid = :GID
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)
&ORDER_BY_CLAUSE';
select viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
f.FII_MEASURE1 FII_MEASURE1,
f.FII_MEASURE2 FII_MEASURE2,
f.FII_MEASURE4 FII_MEASURE4,
f.FII_MEASURE5 FII_MEASURE5,
f.FII_MEASURE7 FII_MEASURE7,
f.FII_MEASURE8 FII_MEASURE8,
f.FII_MEASURE9 FII_MEASURE9,
f.FII_MEASURE10 FII_MEASURE10,
f.FII_MEASURE11 FII_MEASURE11,
f.FII_MEASURE12 FII_MEASURE12,
f.FII_MEASURE13 FII_MEASURE13,
f.FII_MEASURE15 FII_MEASURE15,
f.FII_MEASURE16 FII_MEASURE16,
f.FII_MEASURE17 FII_MEASURE17,
f.FII_MEASURE18 FII_MEASURE18,
f.FII_MEASURE19 FII_MEASURE19,
f.FII_MEASURE20 FII_MEASURE20,
f.FII_MEASURE21 FII_MEASURE21,
f.FII_MEASURE22 FII_MEASURE22,
f.FII_MEASURE23 FII_MEASURE23,
f.FII_MEASURE24 FII_MEASURE24,
f.FII_MEASURE25 FII_MEASURE25,
'''||l_url_1||''' FII_ATTRIBUTE5,
'''||l_url_2||''' FII_ATTRIBUTE6,
'''||l_url_3||''' FII_ATTRIBUTE7,
'''||l_url_4||''' FII_ATTRIBUTE8,
'''||l_url_5||''' FII_ATTRIBUTE10,
'''||l_url_6||''' FII_ATTRIBUTE11,
'''||l_url_7||''' FII_ATTRIBUTE12,
'''||l_url_8||''' FII_ATTRIBUTE13
FROM
(select
id,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE4,
FII_MEASURE5,
FII_MEASURE7,
FII_MEASURE8,
FII_MEASURE9,
FII_MEASURE10,
FII_MEASURE11,
FII_MEASURE12,
FII_MEASURE13,
SUM(FII_MEASURE1) over() FII_MEASURE15,
SUM(FII_MEASURE2) over() FII_MEASURE16,
SUM(FII_MEASURE4) over() FII_MEASURE17,
SUM(FII_MEASURE5) over() FII_MEASURE18,
SUM(FII_MEASURE7) over() FII_MEASURE19,
SUM(FII_MEASURE8) over() FII_MEASURE20,
SUM(FII_MEASURE9) over() FII_MEASURE21,
SUM(FII_MEASURE10) over() FII_MEASURE22,
SUM(FII_MEASURE11) over() FII_MEASURE23,
SUM(FII_MEASURE12) over() FII_MEASURE24,
SUM(FII_MEASURE13) over() FII_MEASURE25,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
FROM
(SELECT f.'||l_viewby_id||' id,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE1,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE2,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE4,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE5,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.stANDard_count ELSE 0 END) FII_MEASURE7,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.withholding_count ELSE 0 END) FII_MEASURE8,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.prepayment_count ELSE 0 END) FII_MEASURE9,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.credit_count ELSE 0 END) FII_MEASURE10,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.debit_count ELSE 0 END) FII_MEASURE11,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.mixed_count ELSE 0 END) FII_MEASURE12,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.interest_count ELSE 0 END) FII_MEASURE13
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
'||l_org_WHERE||l_supplier_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)
AND f.gid = :GID
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)
&ORDER_BY_CLAUSE';
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_MEASURE7) FII_MEASURE7,
SUM(f.FII_MEASURE8) FII_MEASURE8,
SUM(f.FII_MEASURE10) FII_MEASURE10,
SUM(f.FII_MEASURE11) FII_MEASURE11,
SUM(f.FII_MEASURE15) FII_MEASURE15,
SUM(f.FII_MEASURE16) FII_MEASURE16,
SUM(f.FII_MEASURE17) FII_MEASURE17,
SUM(f.FII_MEASURE18) FII_MEASURE18,
SUM(f.FII_MEASURE19) FII_MEASURE19,
SUM(f.FII_MEASURE20) FII_MEASURE20,
SUM(f.FII_MEASURE23) FII_MEASURE23,
SUM(f.FII_MEASURE24) FII_MEASURE24,
SUM(f.FII_MEASURE25) FII_MEASURE25,
SUM(f.FII_MEASURE26) FII_MEASURE26,
SUM(f.FII_MEASURE27) FII_MEASURE27,
SUM(f.FII_MEASURE28) FII_MEASURE28,
SUM(f.FII_MEASURE29) FII_MEASURE29,
SUM(f.FII_MEASURE30) FII_MEASURE30,
SUM(f.FII_DIM1) FII_DIM1,
SUM(f.FII_DIM2) FII_DIM2,
SUM(f.FII_DIM3) FII_DIM3,
SUM(f.FII_DIM4) FII_DIM4,
SUM(f.FII_DIM5) FII_DIM5,
'''||l_url_1||''' FII_ATTRIBUTE3,
'''||l_url_2||''' FII_ATTRIBUTE4,
'''||l_url_3||''' FII_ATTRIBUTE5,
'''||l_url_4||''' FII_ATTRIBUTE7,
'''||l_url_5||''' FII_ATTRIBUTE8,
'''||l_url_6||''' FII_ATTRIBUTE10,
'''||l_url_7||''' FII_ATTRIBUTE11,
'''||l_url_8||''' FII_ATTRIBUTE12,
SUM(f.FII_CV5) FII_CV5
FROM
(SELECT id ID,
FII_MEASURE1 FII_MEASURE1,
FII_MEASURE2 FII_MEASURE2,
FII_MEASURE3 FII_MEASURE3,
FII_MEASURE7 FII_MEASURE7,
FII_MEASURE8 FII_MEASURE8,
FII_MEASURE10 FII_MEASURE10,
FII_MEASURE11 FII_MEASURE11,
FII_MEASURE15 FII_MEASURE15,
FII_MEASURE16 FII_MEASURE16,
FII_MEASURE17 FII_MEASURE17,
FII_MEASURE18 FII_MEASURE18,
FII_MEASURE19 FII_MEASURE19,
FII_MEASURE20 FII_MEASURE20,
SUM(FII_MEASURE1) OVER() FII_MEASURE23,
SUM(FII_MEASURE2) OVER() FII_MEASURE24,
SUM(FII_MEASURE3) OVER() FII_MEASURE25,
SUM(FII_MEASURE7) OVER() FII_MEASURE26,
SUM(FII_MEASURE8) OVER() FII_MEASURE27,
SUM(FII_MEASURE10) OVER() FII_MEASURE28,
SUM(FII_MEASURE11) OVER() FII_MEASURE29,
SUM(FII_MEASURE15) OVER() FII_MEASURE30,
SUM(FII_MEASURE16) OVER() FII_DIM1,
SUM(FII_MEASURE17) OVER() FII_DIM2,
SUM(FII_MEASURE18) OVER() FII_DIM3,
SUM(FII_MEASURE19) OVER() FII_DIM4,
SUM(FII_MEASURE20) OVER() FII_DIM5,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk,
(DECODE(SUM(nvl(FII_MEASURE2,0)) over(),0,null,
DECODE(SUM(nvl(FII_MEASURE10,0)) over(),0,0,
SUM(nvl(FII_MEASURE10,0)) over() / SUM(nvl(FII_MEASURE2,0)) over()))*100) -
(DECODE(SUM(nvl(FII_MEASURE3,0)) over(),0,null,
DECODE(SUM(nvl(FII_MEASURE11,0)) over(),0,0,
SUM(nvl(FII_MEASURE11,0)) over()/ SUM(nvl(FII_MEASURE3,0)) over()))*100) FII_CV5 /* Changes made for Bug 3110651 */
FROM
(SELECT f.'||l_viewby_id||' id,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_amt_entered'||l_currency||' ELSE 0 END) FII_MEASURE1,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE2,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.invoice_count_entered ELSE 0 END) FII_MEASURE3,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.e_invoice_amt'||l_currency||' ELSE 0 END) FII_MEASURE7,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.e_invoice_amt'||l_currency||' ELSE 0 END) FII_MEASURE8,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.e_invoice_count ELSE 0 END) FII_MEASURE10,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.e_invoice_count ELSE 0 END) FII_MEASURE11,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.xml_count ELSE 0 END) FII_MEASURE15,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.edi_count ELSE 0 END) FII_MEASURE16,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.ers_count ELSE 0 END) FII_MEASURE17,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.isp_count ELSE 0 END) FII_MEASURE18,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.asbn_count ELSE 0 END) FII_MEASURE19,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.other_integrated_count ELSE 0 END) FII_MEASURE20
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
'||l_org_where||l_supplier_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)
AND f.gid = :GID
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 viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
SUM(FII_MEASURE1) FII_MEASURE1,
SUM(FII_MEASURE2) FII_MEASURE2,
SUM(FII_MEASURE4) FII_MEASURE4,
SUM(FII_MEASURE5) FII_MEASURE5,
SUM(FII_MEASURE7) FII_MEASURE7,
SUM(FII_MEASURE8) FII_MEASURE8,
SUM(FII_MEASURE12) FII_MEASURE12,
SUM(FII_MEASURE13) FII_MEASURE13,
SUM(FII_MEASURE15) FII_MEASURE15,
SUM(FII_MEASURE17) FII_MEASURE17,
SUM(FII_MEASURE19) FII_MEASURE19,
SUM(FII_MEASURE21) FII_MEASURE21,
SUM(FII_MEASURE23) FII_MEASURE23,
SUM(FII_MEASURE25) FII_MEASURE25,
SUM(FII_MEASURE26) FII_MEASURE26,
SUM(FII_MEASURE27) FII_MEASURE27,
SUM(FII_MEASURE28) FII_MEASURE28,
SUM(FII_MEASURE29) FII_MEASURE29,
SUM(FII_MEASURE30) FII_MEASURE30,
SUM(FII_DIM1) FII_DIM1,
SUM(FII_DIM2) FII_DIM2,
SUM(FII_DIM3) FII_DIM3,
SUM(FII_DIM4) FII_DIM4,
SUM(FII_DIM5) FII_DIM5,
SUM(FII_DIM7) FII_DIM7,
SUM(FII_DIM8) FII_DIM8,
'''||l_url_1||''' FII_ATTRIBUTE14
FROM
(select id,
FII_MEASURE1 FII_MEASURE1,
FII_MEASURE2 FII_MEASURE2,
FII_MEASURE4 FII_MEASURE4,
FII_MEASURE5 FII_MEASURE5,
FII_MEASURE7 FII_MEASURE7,
FII_MEASURE8 FII_MEASURE8,
FII_MEASURE12 FII_MEASURE12,
FII_MEASURE13 FII_MEASURE13,
FII_MEASURE15 FII_MEASURE15,
FII_MEASURE17 FII_MEASURE17,
FII_MEASURE19 FII_MEASURE19,
FII_MEASURE21 FII_MEASURE21,
FII_MEASURE23 FII_MEASURE23,
SUM(FII_MEASURE1) OVER() FII_MEASURE25,
SUM(FII_MEASURE2) OVER() FII_MEASURE26,
SUM(FII_MEASURE4) OVER() FII_MEASURE27,
SUM(FII_MEASURE5) OVER() FII_MEASURE28,
SUM(FII_MEASURE7) OVER() FII_MEASURE29,
SUM(FII_MEASURE8) OVER() FII_MEASURE30,
SUM(FII_MEASURE12) OVER() FII_DIM1,
SUM(FII_MEASURE13) OVER() FII_DIM2,
SUM(FII_MEASURE15) OVER() FII_DIM3,
SUM(FII_MEASURE17) OVER() FII_DIM4,
SUM(FII_MEASURE21) OVER() FII_DIM5,
SUM(FII_MEASURE23) OVER() FII_DIM7,
SUM(FII_MEASURE19) OVER() FII_DIM8,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
from
(select id,
SUM(FII_MEASURE1) FII_MEASURE1,
SUM(FII_MEASURE2) FII_MEASURE2,
SUM(FII_MEASURE4) FII_MEASURE4,
SUM(FII_MEASURE5) FII_MEASURE5,
SUM(FII_MEASURE7) FII_MEASURE7,
SUM(FII_MEASURE8) FII_MEASURE8,
SUM(FII_MEASURE12) FII_MEASURE12,
SUM(FII_MEASURE13) FII_MEASURE13,
SUM(FII_MEASURE15) FII_MEASURE15,
SUM(FII_MEASURE17) FII_MEASURE17,
SUM(FII_MEASURE19) FII_MEASURE19,
SUM(FII_MEASURE21) FII_MEASURE21,
SUM(FII_MEASURE23) FII_MEASURE23
from
(SELECT f.'||l_viewby_id||' id,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.inv_on_hold_amt'||l_period_suffix||l_currency||' ELSE 0 END) FII_MEASURE1,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.inv_on_hold_amt'||l_period_suffix||l_currency||' ELSE 0 END) FII_MEASURE2,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.inv_on_hold_count'||l_period_suffix||' ELSE 0 END) FII_MEASURE4,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.inv_on_hold_count'||l_period_suffix||' ELSE 0 END) FII_MEASURE5,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.days_on_hold'||l_period_suffix||' ELSE 0 END) FII_MEASURE7,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.days_on_hold'||l_period_suffix||' ELSE 0 END) FII_MEASURE8,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.no_of_holds_placed ELSE 0 END) FII_MEASURE12,
SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
THEN f.no_of_holds_placed ELSE 0 END) FII_MEASURE13,
0 FII_MEASURE15,
0 FII_MEASURE17,
0 FII_MEASURE19,
0 FII_MEASURE21,
0 FII_MEASURE23
FROM FII_AP_HATY_XB_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
'||l_org_where||l_supplier_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)
AND f.gid = :GID
GROUP BY f.'||l_viewby_id||'
UNION ALL
SELECT f.'||l_viewby_id||' id,
0 FII_MEASURE1,
0 FII_MEASURE2,
0 FII_MEASURE4,
0 FII_MEASURE5,
0 FII_MEASURE7,
0 FII_MEASURE8,
0 FII_MEASURE12,
0 FII_MEASURE13,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.variance_hold_count ELSE 0 END) FII_MEASURE15,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.po_matching_hold_count ELSE 0 END) FII_MEASURE17,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.invoice_hold_count ELSE 0 END) FII_MEASURE19,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.user_defined_hold_count ELSE 0 END) FII_MEASURE21,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
THEN f.other_hold_count ELSE 0 END) FII_MEASURE23
FROM FII_AP_HCAT_IB_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
'||l_org_where||l_supplier_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)
AND f.hold_release_flag=''H'' /*added for bug no.3096078*/
AND f.gid = :GID
GROUP BY f.'||l_viewby_id||')
group by 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.pwk_end(l_as_of_date-91) +1, l_start_date) into p_report_start from dual;
select nvl(fii_time_api.pwk_end(l_as_of_date) +1, l_start_date-1) into p_period_start from dual;
select sequence into p_cur_effective_num
from fii_time_week
where l_as_of_date between start_date and end_date;
select nvl(fii_time_api.ent_lysper_end(l_as_of_date), l_start_date-1) into p_report_start from dual;
select nvl(fii_time_api.ent_cper_start(l_as_of_date), l_start_date) into p_period_start from dual;
select sequence into p_cur_effective_num
from fii_time_ent_period
where l_as_of_date between start_date and end_date;
select nvl(fii_time_api.ent_lysqtr_end(l_as_of_date), l_start_date-1) into p_report_start from dual;
select nvl(fii_time_api.ent_cqtr_start(l_as_of_date), l_start_date) into p_period_start from dual;
select sequence into p_cur_effective_num
from fii_time_ent_qtr
where l_as_of_date between start_date and end_date;
select nvl(fii_time_api.ent_pyr_start(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 p_report_start from dual; /* Bug 3325387 */
select nvl(fii_time_api.ent_cyr_start(l_as_of_date), l_start_date) into p_period_start from dual;
select sequence into p_cur_effective_num
from fii_time_ent_year
where l_as_of_date between start_date and end_date;
/*select count(*) into l_count from all_tables where table_name = 'FII_AR_SALES_CREDITS' and
rownum = 1 and owner = l_fii_schema; */
SELECT
(case when FII_MEASURE1 = fii_time_api.cwk_end(&BIS_CURRENT_ASOF_DATE) then to_char(&BIS_CURRENT_ASOF_DATE) else FII_MEASURE1 end) FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE3,
FII_MEASURE5,
(CASE WHEN (enddate-fii_time_api.cwk_end(&BIS_CURRENT_ASOF_DATE)) = 0 then '''||l_url_2||''' else '''||l_url_1||''' END ) FII_ATTRIBUTE1
FROM(
SELECT
tcur.end_date enddate,
name FII_MEASURE1,
inline_view.invoice_entered FII_MEASURE2,
inline_view.invoice_count FII_MEASURE3,
inline_view.invoice_amt FII_MEASURE5,
to_char(tcur.end_date,''DD/MM/YYYY'') drill_date
FROM
(
SELECT
inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
SUM(invoice_count_entered) invoice_entered ,
SUM(e_invoice_count) invoice_count,
SUM(e_invoice_amt) invoice_amt
FROM
(
SELECT
t.sequence FII_SEQUENCE,
f.invoice_count_entered invoice_count_entered,
f.e_invoice_count e_invoice_count,
f.e_invoice_amt'||l_currency||' e_invoice_amt
FROM FII_AP_IVATY_XB_MV f,
'||l_period_type||' t
WHERE
f.gid = :GID
AND f.time_id = t.'||l_id_column||'
AND f.period_type_id = :P_PERIOD_ID
AND t.end_date between to_date(:P_REPORT_START,''DD-MM-YYYY'') AND to_date(:PERIOD_START,''DD-MM-YYYY'')
'||l_org_where||l_supplier_where||'
UNION ALL
SELECT
:P_CUR_EFFECTIVE_NUM FII_SEQUENCE,
f.invoice_count_entered invoice_count_entered,
f.e_invoice_count e_invoice_count,
f.e_invoice_amt'||l_currency||' e_invoice_amt
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,:RECORD_TYPE_ID)= :RECORD_TYPE_ID
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_period_type||' tcur
WHERE inline_view.fii_effective_num (+)= tcur.sequence
AND tcur.start_date > to_date(:P_REPORT_START,''DD-MM-YYYY'')
AND tcur.start_date <= &BIS_CURRENT_ASOF_DATE
ORDER BY tcur.start_date)';
SELECT
name FII_MEASURE1,
inline_view.invoice_entered FII_MEASURE2,
inline_view.invoice_count FII_MEASURE3,
inline_view.invoice_amt FII_MEASURE5,
(CASE WHEN (tcur.end_date-:P_DATE_STRING) = 0 then '''||l_url_2||''' else '''||l_url_1||''' END ) FII_ATTRIBUTE1
FROM
(
SELECT
inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
SUM(invoice_count_entered) invoice_entered ,
SUM(e_invoice_count) invoice_count,
SUM(e_invoice_amt) invoice_amt,
drill_date drill_date
FROM
(
SELECT
t.sequence FII_SEQUENCE,
f.invoice_count_entered invoice_count_entered,
f.e_invoice_count e_invoice_count,
f.e_invoice_amt'||l_currency||' e_invoice_amt,
to_char(t.end_date,''DD/MM/YYYY'') drill_date
FROM FII_AP_IVATY_XB_MV f,
'||l_period_type||' t
WHERE
f.gid = :GID
AND f.time_id = t.'||l_id_column||'
AND f.period_type_id = :P_PERIOD_ID
AND t.end_date > to_date(:P_REPORT_START,''DD-MM-YYYY'') /*Changed for bug no.3069214*/
AND t.end_date < to_date(:PERIOD_START,''DD-MM-YYYY'')
'||l_org_where||l_supplier_where||'
UNION ALL
SELECT
:P_CUR_EFFECTIVE_NUM FII_SEQUENCE,
f.invoice_count_entered invoice_count_entered,
f.e_invoice_count e_invoice_count,
f.e_invoice_amt'||l_currency||' e_invoice_amt,
to_char(cal.report_date,''DD/MM/YYYY'') drill_date
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,:RECORD_TYPE_ID)= :RECORD_TYPE_ID
AND cal.report_date = &BIS_CURRENT_ASOF_DATE
'||l_org_where||l_supplier_where||'
) inner_inline_view
GROUP BY inner_inline_view.FII_SEQUENCE, drill_date
) inline_view,
'||l_period_type||' tcur
WHERE inline_view.fii_effective_num (+)= tcur.sequence
AND tcur.start_date > to_date(:P_REPORT_START,''DD-MM-YYYY'')
AND tcur.start_date <= &BIS_CURRENT_ASOF_DATE
ORDER BY tcur.start_date';