[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_MEASURE3 FII_MEASURE3,
f.FII_MEASURE4 FII_MEASURE4,
f.FII_MEASURE5 FII_MEASURE5,
f.FII_MEASURE6 FII_MEASURE6,
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_MEASURE14 FII_MEASURE14,
'''||l_url_1||''' FII_ATTRIBUTE5,
'''||l_url_2||''' FII_ATTRIBUTE6,
'''||l_url_3||''' FII_ATTRIBUTE7,
to_number(null) FII_DIM1,
to_number(null) FII_DIM2,
to_number(null) FII_DIM3,
to_number(null) FII_DIM4
FROM
(SELECT
ID,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE3,
FII_MEASURE4,
FII_MEASURE5,
FII_MEASURE6,
FII_MEASURE7,
FII_MEASURE8,
SUM(FII_MEASURE1) OVER() FII_MEASURE9,
SUM(FII_MEASURE2) OVER() FII_MEASURE10,
SUM(FII_MEASURE3) OVER() FII_MEASURE11,
SUM(FII_MEASURE4) OVER() FII_MEASURE12,
SUM(FII_MEASURE6) OVER() FII_MEASURE13,
SUM(FII_MEASURE7) OVER() FII_MEASURE14,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
FROM
(SELECT f.'||l_viewby_id||' id,
SUM(f.open_amt'||l_curr_suffix||' ) FII_MEASURE1,
SUM(f.open_count) FII_MEASURE2,
SUM(f.open_amt'||l_curr_suffix||' ) -
SUM(f.open_past_due_amt'||l_curr_suffix||' )
FII_MEASURE3,
SUM(f.open_due_count) FII_MEASURE4,
decode(SUM(open_amt'||l_curr_suffix||' - open_past_due_amt'||l_curr_suffix||'),
0, 0,
(SUM(wt_open_due_amt'||l_curr_suffix||') /
SUM(open_amt'||l_curr_suffix||' - open_past_due_amt'||l_curr_suffix||')) -
:ASOF_DATE_JULIEN )
FII_MEASURE5,
SUM(f.open_past_due_amt'||l_curr_suffix||' )
FII_MEASURE6,
SUM(f.open_past_due_count) FII_MEASURE7,
decode(SUM(open_past_due_amt'||l_curr_suffix||'), 0, 0,
:ASOF_DATE_JULIEN -
SUM(wt_open_past_due_amt'||l_curr_suffix||') /
SUM(open_past_due_amt'||l_curr_suffix||'))
FII_MEASURE8
FROM FII_AP_LIA_IB_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)
AND f.gid = :GID
GROUP BY f.'||l_viewby_id||'
HAVING (SUM(f.open_amt'||l_curr_suffix||' ) > 0
OR SUM(f.open_count) > 0)
)) 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_MEASURE3 FII_MEASURE3,
f.FII_MEASURE4 FII_MEASURE4,
f.FII_MEASURE5 FII_MEASURE5,
f.FII_MEASURE6 FII_MEASURE6,
f.FII_MEASURE7 FII_MEASURE7,
f.FII_MEASURE13 FII_MEASURE13,
f.FII_MEASURE14 FII_MEASURE14,
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,
'''||l_url_1||''' FII_ATTRIBUTE5,
'''||l_url_1||''' FII_DIM1 /* Added for Bug 3096072 */
FROM
(select
ID,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE3,
FII_MEASURE4,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk,
FII_MEASURE5,
FII_MEASURE6,
FII_MEASURE7,
SUM(FII_MEASURE1) OVER() FII_MEASURE13,
SUM(FII_MEASURE2) OVER() FII_MEASURE14,
SUM(FII_MEASURE3) OVER() FII_MEASURE15,
SUM(FII_MEASURE4) OVER() FII_MEASURE16,
SUM(FII_MEASURE5) OVER() FII_MEASURE17,
SUM(FII_MEASURE6) OVER() FII_MEASURE18,
SUM(FII_MEASURE7) OVER() FII_MEASURE19
from
(SELECT
f.'||l_viewby_id||' ID,
SUM(f.open_amt'||l_curr_suffix||') FII_MEASURE1,
SUM(f.open_amt'||l_curr_suffix||') + SUM(f.open_payment_amt'||l_curr_suffix||')
+ SUM(f.open_discount_taken'||l_curr_suffix||') FII_MEASURE2,
SUM(f.open_count) FII_MEASURE3,
SUM(f.open_discount_offered'||l_curr_suffix||') FII_MEASURE4,
SUM(f.open_discount_taken'||l_curr_suffix||') FII_MEASURE5,
SUM(f.open_discount_lost'||l_curr_suffix||') FII_MEASURE6,
SUM(f.open_discount_remaining'||l_curr_suffix||') FII_MEASURE7
FROM FII_AP_LIA_IB_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)
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_MEASURE3 FII_MEASURE3,
f.FII_MEASURE4 FII_MEASURE4,
f.FII_MEASURE5 FII_MEASURE5,
f.FII_MEASURE6 FII_MEASURE6,
f.FII_MEASURE7 FII_MEASURE7,
f.FII_MEASURE8 FII_MEASURE8,
f.FII_MEASURE9 FII_MEASURE9,
f.FII_MEASURE13 FII_MEASURE13,
f.FII_MEASURE14 FII_MEASURE14,
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,
'''||l_url_1||''' FII_ATTRIBUTE5,
'''||l_url_2||''' FII_ATTRIBUTE6,
'''||l_url_3||''' FII_ATTRIBUTE7,
'''||l_url_4||''' FII_ATTRIBUTE8
FROM
(SELECT
id,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE3,
FII_MEASURE4,
FII_MEASURE5,
FII_MEASURE6,
FII_MEASURE7,
FII_MEASURE8,
FII_MEASURE9,
SUM(FII_MEASURE1) OVER() FII_MEASURE13,
SUM(FII_MEASURE2) OVER() FII_MEASURE14,
SUM(FII_MEASURE3) OVER() FII_MEASURE15,
SUM(FII_MEASURE4) OVER() FII_MEASURE16,
SUM(FII_MEASURE5) OVER() FII_MEASURE17,
SUM(FII_MEASURE6) OVER() FII_MEASURE18,
SUM(FII_MEASURE7) OVER() FII_MEASURE19,
SUM(FII_MEASURE8) OVER() FII_MEASURE20,
SUM(FII_MEASURE9) OVER() FII_MEASURE21,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
FROM
( SELECT f.'||l_viewby_id||' id,
sum(f.open_amt'||l_curr_suffix||') FII_MEASURE1,
sum(f.open_amt'||l_curr_suffix||') -
sum(f.open_past_due_amt'||l_curr_suffix||') FII_MEASURE2,
sum(f.open_due_count) FII_MEASURE3,
sum(f.open_due_bucket3'||l_curr_suffix||') FII_MEASURE4,
sum(f.open_due_bucket3_count) FII_MEASURE5,
sum(f.open_due_bucket2'||l_curr_suffix||') FII_MEASURE6,
sum(f.open_due_bucket2_count) FII_MEASURE7,
sum(f.open_due_bucket1'||l_curr_suffix||') FII_MEASURE8,
sum(f.open_due_bucket1_count) FII_MEASURE9
FROM FII_AP_LIA_IB_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)
AND f.gid = :GID
HAVING sum(f.open_amt'||l_curr_suffix||') <> 0 /* bug # 3148973 */
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,
f.FII_MEASURE2,
f.FII_MEASURE3,
f.FII_MEASURE4,
f.FII_MEASURE5,
f.FII_MEASURE6,
f.FII_MEASURE7,
f.FII_MEASURE8,
f.FII_MEASURE9,
f.FII_MEASURE13,
f.FII_MEASURE14,
f.FII_MEASURE15,
f.FII_MEASURE16,
f.FII_MEASURE17,
f.FII_MEASURE18,
f.FII_MEASURE19,
f.FII_MEASURE20,
f.FII_MEASURE21,
'''||l_url_1||''' FII_ATTRIBUTE5,
'''||l_url_2||''' FII_ATTRIBUTE6,
'''||l_url_3||''' FII_ATTRIBUTE7,
'''||l_url_4||''' FII_ATTRIBUTE8
FROM
(SELECT
id,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE3,
FII_MEASURE4,
FII_MEASURE5,
FII_MEASURE6,
FII_MEASURE7,
FII_MEASURE8,
FII_MEASURE9,
SUM(FII_MEASURE1) OVER() FII_MEASURE13,
SUM(FII_MEASURE2) OVER() FII_MEASURE14,
SUM(FII_MEASURE3) OVER() FII_MEASURE15,
SUM(FII_MEASURE4) OVER() FII_MEASURE16,
SUM(FII_MEASURE5) OVER() FII_MEASURE17,
SUM(FII_MEASURE6) OVER() FII_MEASURE18,
SUM(FII_MEASURE7) OVER() FII_MEASURE19,
SUM(FII_MEASURE8) OVER() FII_MEASURE20,
SUM(FII_MEASURE9) OVER() FII_MEASURE21,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
FROM
( SELECT f.'||l_viewby_id||' id,
sum(f.open_amt'||l_curr_suffix||') FII_MEASURE1,
sum(f.open_past_due_amt'||l_curr_suffix||')
FII_MEASURE2,
sum(f.open_past_due_count) FII_MEASURE3,
sum(f.open_past_due_bucket3'||l_curr_suffix||')
FII_MEASURE4,
sum(f.open_past_due_bucket3_count) FII_MEASURE5,
sum(f.open_past_due_bucket2'||l_curr_suffix||')
FII_MEASURE6,
sum(f.open_past_due_bucket2_count) FII_MEASURE7,
sum(f.open_past_due_bucket1'||l_curr_suffix||')
FII_MEASURE8,
sum(f.open_past_due_bucket1_count) FII_MEASURE9
FROM FII_AP_LIA_IB_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)
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';