[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT h.VIEWBY VIEWBY,
h.VIEWBYID VIEWBYID,
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_MEASURE8 FII_MEASURE8,
h.FII_MEASURE9 FII_MEASURE9,
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_MEASURE19 FII_MEASURE19,
h.FII_MEASURE20 FII_MEASURE20,
h.FII_MEASURE21 FII_MEASURE21,
decode('''||l_view_by||''',''SUPPLIER+POA_SUPPLIERS'','''||l_url_2||''',null)
FII_ATTRIBUTE2, -- for fii_measure6
decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''',
''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
null) FII_ATTRIBUTE3, -- for fii_measure4
decode('''||l_view_by||''',''SUPPLIER+POA_SUPPLIERS'','''||l_url_3||''',null)
FII_ATTRIBUTE4, -- for fii_measure9
decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''',
''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
null) FII_ATTRIBUTE5 /* Added for Bug 3096072 */
FROM
(SELECT g.VIEWBY VIEWBY,
g.VIEWBYID VIEWBYID,
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_MEASURE8 FII_MEASURE8,
g.FII_MEASURE9 FII_MEASURE9,
g.FII_MEASURE11 FII_MEASURE11,
g.FII_MEASURE12 FII_MEASURE12,
sum(g.FII_MEASURE1) over() FII_MEASURE13,
sum(g.FII_MEASURE2) over() FII_MEASURE14,
sum(g.FII_MEASURE3) over() FII_MEASURE15,
sum(g.FII_MEASURE4) over() FII_MEASURE16,
sum(g.FII_MEASURE3 - g.FII_MEASURE8) over() FII_MEASURE17,
sum(g.FII_MEASURE6) over() FII_MEASURE18,
sum(g.FII_MEASURE8) over() FII_MEASURE19,
sum(g.FII_MEASURE9) over() FII_MEASURE20,
sum(g.FII_MEASURE11) over() FII_MEASURE21,
( rank() over (&ORDER_BY_CLAUSE nulls last, VIEWBYID)) - 1 rnk
FROM
(SELECT viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
sum(open_amt) FII_MEASURE1,
sum(open_count) FII_MEASURE2,
sum(inv_on_hold_amt) FII_MEASURE3,
sum(inv_on_hold_count) FII_MEASURE4,
sum(inv_on_hold_amt) - sum(on_hold_past_due_amt) FII_MEASURE5,
sum(on_hold_due_count) FII_MEASURE6,
sum(on_hold_past_due_amt) FII_MEASURE8,
sum(on_hold_past_due_count) FII_MEASURE9,
sum(no_of_holds) FII_MEASURE11,
decode(sum(inv_on_hold_count),0,0,
sum(days_on_hold)/sum(inv_on_hold_count)) FII_MEASURE12
-- sum(sum(open_amt)) over() FII_MEASURE13,
-- sum(sum(open_count)) over() FII_MEASURE14,
-- sum(sum(inv_on_hold_amt)) over() FII_MEASURE15,
-- sum(sum(inv_on_hold_count)) over() FII_MEASURE16,
-- sum(sum(inv_on_hold_amt) - sum(on_hold_past_due_amt)) over() FII_MEASURE17,
-- sum(sum(on_hold_due_count)) over() FII_MEASURE18,
-- sum(sum(on_hold_past_due_amt)) over() FII_MEASURE19,
-- sum(sum(on_hold_past_due_count)) over() FII_MEASURE20,
-- sum(sum(no_of_holds)) over() FII_MEASURE21,
FROM
(SELECT f.'||l_column_name||' id,
sum(f.open_amt'||l_currency||' ) open_amt,
sum(f.open_count) open_count,
0 inv_on_hold_amt,
0 inv_on_hold_count,
0 on_hold_due_count,
0 on_hold_past_due_amt,
0 on_hold_past_due_count,
0 no_of_holds,
0 days_on_hold
FROM FII_AP_LIA_IB_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
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)
AND f.gid = :GID
GROUP BY f.'||l_column_name||'
UNION ALL
SELECT f.'||l_column_name||' id,
0 open_amt,
0 open_count,
sum(f.inv_on_hold_amt'||l_currency||') inv_on_hold_amt,
sum(f.inv_on_hold_count) inv_on_hold_count,
sum(f.on_hold_due_count) on_hold_due_count,
sum(f.on_hold_past_due_amt'||l_currency||') on_hold_past_due_amt,
sum(f.on_hold_past_due_count) on_hold_past_due_count,
sum(f.no_of_holds) no_of_holds,
sum(f.days_on_hold) days_on_hold
FROM FII_AP_HLIA_I_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
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)
AND f.gid = :GID
GROUP BY f.'||l_column_name||'
) f,
('||l_table_name||') viewby_dim
WHERE f.id = viewby_dim.id
GROUP BY viewby_dim.value, viewby_dim.id) g ) h
WHERE ((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_MEASURE6 FII_MEASURE6,
f.FII_MEASURE8 FII_MEASURE8,
f.FII_MEASURE10 FII_MEASURE10,
f.FII_MEASURE12 FII_MEASURE12,
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,
f.FII_MEASURE22 FII_MEASURE22,
decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''',
''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
null) FII_ATTRIBUTE2 -- for fii_measure3
FROM
(SELECT
id ,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE3,
FII_MEASURE4,
FII_MEASURE6,
FII_MEASURE8,
FII_MEASURE10,
sum(FII_MEASURE1) over() FII_MEASURE12,
sum(FII_MEASURE2) over() FII_MEASURE13,
sum(FII_MEASURE3) over() FII_MEASURE14,
sum(FII_MEASURE4) over() FII_MEASURE15,
sum(FII_MEASURE6) over() FII_MEASURE16,
sum(FII_MEASURE8) over() FII_MEASURE17,
sum(FII_MEASURE10) over() FII_MEASURE18,
decode (sum(FII_MEASURE2) over(),0,0,
((sum(FII_MEASURE4) over() / sum(FII_MEASURE2) over()) *100))
FII_MEASURE19,
decode (sum(FII_MEASURE4) over(),0,0,
((sum(FII_MEASURE6) over() / sum(FII_MEASURE4) over()) *100))
FII_MEASURE20,
decode (sum(FII_MEASURE4) over(),0,0,
((sum(FII_MEASURE8) over() / sum(FII_MEASURE4) over()) *100))
FII_MEASURE21,
decode (sum(FII_MEASURE4) over(),0,0,
((sum(FII_MEASURE10) over() / sum(FII_MEASURE4) over()) *100))
FII_MEASURE22,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
FROM
(SELECT f.'||l_column_name||' id,
sum(INV_ON_HOLD_AMT'||l_currency||' ) FII_MEASURE1,
sum(INV_ON_HOLD_AMT'||l_currency||'+
ON_HOLD_PAYMENT_AMOUNT'||l_currency||'+
ON_HOLD_DIS_TAKEN'||l_currency||') FII_MEASURE2,
sum(INV_ON_HOLD_COUNT) FII_MEASURE3,
sum(ON_HOLD_DIS_TAKEN'||l_currency||' +
ON_HOLD_DIS_LOST'||l_currency||' +
ON_HOLD_DIS_REMAINING'||l_currency||') FII_MEASURE4,
sum(ON_HOLD_DIS_TAKEN'||l_currency||') FII_MEASURE6,
sum(ON_HOLD_DIS_LOST'||l_currency||') FII_MEASURE8,
sum(ON_HOLD_DIS_REMAINING'||l_currency||') FII_MEASURE10
FROM FII_AP_HLIA_I_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
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)
AND f.gid = :GID
GROUP BY f.'||l_column_name||'
)) f,
('||l_table_name||') viewby_dim
WHERE f.id = viewby_dim.id
and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE' ;
SELECT
h.VIEWBY VIEWBY,
h.VIEWBYID VIEWBYID,
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_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_MEASURE19 FII_MEASURE19,
h.FII_MEASURE20 FII_MEASURE20,
decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_2||''',
''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
null) FII_ATTRIBUTE2 -- for fii_measure2
FROM
(SELECT g.VIEWBY VIEWBY,
g.VIEWBYID VIEWBYID,
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,
sum(g.FII_MEASURE1) over() FII_MEASURE13,
sum(g.FII_MEASURE2) over() FII_MEASURE14,
sum(g.FII_MEASURE3) over() FII_MEASURE15,
sum(g.FII_MEASURE4) over() FII_MEASURE16,
sum(g.FII_MEASURE5) over() FII_MEASURE17,
sum(g.FII_MEASURE6) over() FII_MEASURE18,
sum(g.FII_MEASURE7) over() FII_MEASURE19,
sum(g.FII_MEASURE8) over() FII_MEASURE20,
( rank() over (&ORDER_BY_CLAUSE nulls last, VIEWBYID)) - 1 rnk
FROM
(SELECT viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
sum(inv_on_hold_amt) FII_MEASURE1,
sum(inv_on_hold_count) FII_MEASURE2,
sum(no_of_holds) FII_MEASURE3,
sum(VARIANCE_HOLD_COUNT) FII_MEASURE4,
sum(PO_MATCHING_HOLD_COUNT) FII_MEASURE5,
sum(INVOICE_HOLD_COUNT) FII_MEASURE6,
sum(USER_DEFINED_HOLD_COUNT) FII_MEASURE7,
sum(OTHER_HOLD_COUNT) FII_MEASURE8
-- sum(sum(inv_on_hold_amt)) over() FII_MEASURE13,
-- sum(sum(inv_on_hold_count)) over() FII_MEASURE14,
-- sum(sum(no_of_holds)) over() FII_MEASURE15,
-- sum(sum(VARIANCE_HOLD_COUNT)) over() FII_MEASURE16,
-- sum(sum(PO_MATCHING_HOLD_COUNT)) over() FII_MEASURE17,
-- sum(sum(INVOICE_HOLD_COUNT)) over() FII_MEASURE18,
-- sum(sum(USER_DEFINED_HOLD_COUNT)) over() FII_MEASURE19,
-- sum(sum(OTHER_HOLD_COUNT)) over() FII_MEASURE20,
FROM
(SELECT f.'||l_column_name||' id,
sum(f.inv_on_hold_amt'||l_currency||' ) inv_on_hold_amt,
sum(f.inv_on_hold_count) inv_on_hold_count,
sum(f.no_of_holds) no_of_holds,
0 VARIANCE_HOLD_COUNT,
0 PO_MATCHING_HOLD_COUNT,
0 INVOICE_HOLD_COUNT,
0 USER_DEFINED_HOLD_COUNT,
0 OTHER_HOLD_COUNT
FROM FII_AP_HLIA_I_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
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)
AND f.gid = :GID
GROUP BY f.'||l_column_name||'
UNION ALL
SELECT f.'||l_column_name||' id,
0 inv_on_hold_amt,
0 inv_on_hold_count,
0 no_of_holds,
sum(f.VARIANCE_HOLD_COUNT) VARIANCE_HOLD_COUNT,
sum(f.PO_MATCHING_HOLD_COUNT) PO_MATCHING_HOLD_COUNT,
sum(f.INVOICE_HOLD_COUNT) INVOICE_HOLD_COUNT,
sum(f.USER_DEFINED_HOLD_COUNT) USER_DEFINED_HOLD_COUNT,
sum(f.OTHER_HOLD_COUNT) OTHER_HOLD_COUNT
FROM FII_AP_HCAT_IB_MV f,
fii_time_structures cal
WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
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)
AND f.gid = :GID
GROUP BY f.'||l_column_name||'
) f,
('||l_table_name||') viewby_dim
WHERE f.id = viewby_dim.id
GROUP BY viewby_dim.value, viewby_dim.id) g ) h
WHERE ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
&ORDER_BY_CLAUSE' ;
SELECT
f.HOLD_CODE FII_MEASURE1,
count(f.HOLD_CODE) FII_MEASURE2,
count(distinct(f.INVOICE_ID)) FII_MEASURE3,
sum(count(f.HOLD_CODE)) over() FII_MEASURE14,
sum(count(distinct(f.INVOICE_ID))) over() FII_MEASURE15
FROM FII_AP_INV_HOLDS_B f
WHERE f.hold_date <= &BIS_CURRENT_ASOF_DATE
'||l_org_where||l_supplier_where||'
'||l_cat_join||'
AND (f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date IS NULL)
GROUP BY f.HOLD_CODE
&ORDER_BY_CLAUSE' ;
/*select count(*) into l_count from all_tables where table_name = 'FII_AR_SALES_CREDITS' and
rownum = 1 and owner = l_fii_schema;
' SELECT name VIEWBY,
id VIEWBYID,
name FII_MEASURE1,
sum(open_amt) FII_MEASURE2,
sum(open_count) FII_MEASURE3,
sum(inv_on_hold_amt) FII_MEASURE4,
sum(inv_on_hold_count) FII_MEASURE5,
id FII_MEASURE6,
'''||l_url_1||''' FII_ATTRIBUTE1
FROM
(SELECT
t.ent_period_id id,
to_char(t.end_date,'''||l_date_mask||''') name,
to_char(t.end_date,''DD/MM/YYYY'') drill_date,
sum( f.open_amt'||l_currency||') open_amt,
sum(f.open_count) open_count,
0 inv_on_hold_amt,
0 inv_on_hold_count
FROM FII_AP_LIA_IB_MV f,
fii_time_structures cal,
fii_time_ent_period t
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 = t.end_date
AND t.end_date >= :PREVIOUS_DATE
AND t.end_date < &BIS_CURRENT_ASOF_DATE
AND f.gid = :GID2'||l_org_where||l_supplier_where||'
GROUP BY t.ent_period_id, t.end_date
UNION ALL
SELECT
10000000 id,
to_char(cal.report_date,'''||l_date_mask||''') name,
to_char(cal.report_date,''DD/MM/YYYY'') drill_date,
sum(f.open_amt'||l_currency||') open_amt,
sum(f.open_count) open_count,
0 inv_on_hold_amt,
0 inv_on_hold_count
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
AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
AND cal.report_date = &BIS_CURRENT_ASOF_DATE
AND f.gid = :GID2'||l_org_where||l_supplier_where||'
GROUP BY cal.report_date
UNION ALL
SELECT
t.ent_period_id id,
to_char(t.end_date,'''||l_date_mask||''') name,
to_char(t.end_date,''DD/MM/YYYY'') drill_date,
0 open_amt,
0 open_count,
sum(f.inv_on_hold_amt'||l_currency||') inv_on_hold_amt,
sum(f.inv_on_hold_count) inv_on_hold_count
FROM FII_AP_HLIA_I_MV f,
fii_time_structures cal,
fii_time_ent_period t
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 = t.end_date
AND t.end_date >= :PREVIOUS_DATE
AND t.end_date < &BIS_CURRENT_ASOF_DATE
AND f.gid = :GID2'||l_org_where||l_supplier_where||'
GROUP BY t.ent_period_id, t.end_date
UNION ALL
SELECT
10000000 id,
to_char(cal.report_date,'''||l_date_mask||''') name,
to_char(cal.report_date,''DD/MM/YYYY'') drill_date,
0 open_amt,
0 open_count,
sum(f.inv_on_hold_amt'||l_currency||') inv_on_hold_amt,
sum(f.inv_on_hold_count) inv_on_hold_count
FROM FII_AP_HLIA_I_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 = &BIS_CURRENT_ASOF_DATE
AND f.gid = :GID2'||l_org_where||l_supplier_where||'
GROUP BY cal.report_date)
GROUP by id, name, drill_date
ORDER BY id asc ' ;