[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
month data is also selected and made corresponding changes in Select clause.*/
-- Construct the sql query to be sent
sqlstmt := '
SELECT viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
sum(f.FII_MEASURE1) FII_MEASURE1,
sum(f.FII_MEASURE15) FII_MEASURE15,
sum(f.FII_MEASURE2) FII_MEASURE2,
sum(f.FII_MEASURE4) FII_MEASURE4,
sum(f.FII_MEASURE5) FII_MEASURE5,
sum(f.FII_MEASURE6) FII_MEASURE6,
sum(f.FII_MEASURE7) FII_MEASURE7,
sum(f.FII_MEASURE9) FII_MEASURE9,
sum(f.FII_MEASURE10) FII_MEASURE10,
sum(f.FII_MEASURE11) FII_MEASURE11,
sum(f.FII_MEASURE12) FII_MEASURE12,
sum(f.FII_MEASURE13) FII_MEASURE13,
sum(f.FII_MEASURE14) FII_MEASURE14,
'''||l_url_1||''' FII_ATTRIBUTE5,
'''||l_url_2||''' FII_ATTRIBUTE6,
'''||l_url_3||''' FII_ATTRIBUTE7
from
(select ID,
FII_MEASURE1,
FII_MEASURE15,
FII_MEASURE2,
FII_MEASURE4,
FII_MEASURE5,
FII_MEASURE6,
FII_MEASURE7,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk,
SUM(FII_MEASURE1) OVER() FII_MEASURE9,
DECODE(NVL(FII_MEASURE1,0), 0, 0,(SUM(FII_MEASURE2) OVER()/
SUM(FII_MEASURE1) OVER() * 100)) FII_MEASURE10,
-- DECODE(NVL(FII_MEASURE2,0), 0, 0,sum(FII_MEASURE2 * days_past_due) over()/
-- sum(FII_MEASURE2) over() ) FII_MEASURE11,
DECODE(sum(FII_MEASURE2) over (), 0, 0,
:ASOF_DATE_JULIEN - sum(wt_open_past_due_amt) over()/
sum(FII_MEASURE2) over() ) FII_MEASURE11,
SUM(FII_MEASURE5) OVER() FII_MEASURE12,
SUM(FII_MEASURE6) OVER() FII_MEASURE13,
DECODE(NVL((FII_MEASURE5+FII_MEASURE6), 0), 0, 0,(SUM(FII_MEASURE6) OVER() /
SUM(FII_MEASURE5+FII_MEASURE6) OVER() * 100)) FII_MEASURE14
FROM
(SELECT f.'||l_viewby_id||' ID,
SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
THEN f.open_amt'||l_curr_suffix||' ELSE 0 END) FII_MEASURE1,
SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
THEN f.open_amt'||l_curr_suffix||' ELSE 0 END) FII_MEASURE15,
SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
THEN f.open_past_due_amt'||l_curr_suffix||' ELSE 0 END) FII_MEASURE2,
decode(sum(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID then f.open_past_due_amt'||l_curr_suffix||' else 0 end) ,0,0,
:ASOF_DATE_JULIEN - SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID then f.wt_open_past_due_amt'||l_curr_suffix||' else 0 end)/
sum(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID then f.open_past_due_amt'||l_curr_suffix||' else 0 end)) FII_MEASURE4,
-- 0 days_past_due,
SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
THEN f.on_time_payment_amt'||l_curr_suffix||' ELSE 0 END)
FII_MEASURE5,
SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
THEN f.late_payment_amt'||l_curr_suffix||' ELSE 0 END)
FII_MEASURE6,
SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE THEN (case when bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID then f.open_payment_amt'||l_curr_suffix||' else 0 end) ELSE 0 END)
FII_MEASURE7,
SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
THEN f.wt_open_past_due_amt'||l_curr_suffix||' ELSE 0 END) wt_open_past_due_amt
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 or bitand(cal.record_type_id,64)=64)
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)
GROUP BY viewby_dim.value, viewby_dim.id
&ORDER_BY_CLAUSE';
SELECT decode(t1.multiplier,''1'', :FIIBIND1,
''2'', :FIIBIND2,
''3'', :FIIBIND3,
''4'', :FIIBIND4,
''5'', :FIIBIND5,
''6'', :FIIBIND6) FII_MEASURE1,
DECODE(t1.multiplier, ''1'', SUM(open_past_due_bucket1_count),
''2'', SUM(open_past_due_bucket2_count),
''3'', SUM(open_past_due_bucket3_count),
''4'', SUM(open_due_bucket3_count),
''5'', SUM(open_due_bucket2_count),
''6'', SUM(open_due_bucket1_count)) FII_MEASURE2
FROM FII_AP_LIA_IB_MV f,
fii_time_structures cal,
gl_row_multipliers t1
WHERE t1.multiplier <= 6
AND f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
'||l_org_where||'
'||l_sup_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 t1.multiplier order by t1.multiplier asc';
SELECT viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
sum(f.FII_MEASURE1) FII_MEASURE1,
sum(f.FII_MEASURE2) FII_MEASURE2
FROM
(select id,
FII_MEASURE1,
( rank() over (&ORDER_BY_CLAUSE nulls last, id)) - 1 rnk,
FII_MEASURE2
from
(SELECT f.'||l_viewby_id||' id,
SUM(f.open_amt'||l_curr_suffix||' ) FII_MEASURE1,
SUM(SUM(f.open_amt'||l_curr_suffix||')) over() FII_MEASURE2,
SUM(f.open_count) open_count,
SUM(f.open_due_count) due_count,
0 weighted_avg_days_due,
SUM(f.open_past_due_amt'||l_curr_suffix||' )
past_due_amt,
SUM(f.open_past_due_count) past_due_count,
0 weighted_avg_days_past_due
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_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 (f.rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
GROUP BY viewby_dim.value, viewby_dim.id
&ORDER_BY_CLAUSE';
select 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,
decode(SUM(open_amt'||l_curr_suffix||' - open_past_due_amt'||l_curr_suffix||'),
0, 0,
(SUM(dd_open_due_amt'||l_curr_suffix||') /
SUM(open_amt'||l_curr_suffix||' - open_past_due_amt'||l_curr_suffix||')) -
:ASOF_DATE_JULIEN) FII_MEASURE4,
sum(f.open_past_due_amt'||l_curr_suffix||') FII_MEASURE5,
sum(f.open_past_due_count) FII_MEASURE6,
decode(SUM(f.open_past_due_amt'||l_curr_suffix||'), 0, 0,
:ASOF_DATE_JULIEN - SUM(f.dd_open_past_due_amt'||l_curr_suffix||')
/SUM(f.open_past_due_amt'||l_curr_suffix||')) FII_MEASURE7,
sum(f.open_discount_remaining'||l_curr_suffix||') FII_MEASURE8,
sum(f.open_discount_offered'||l_curr_suffix||') FII_MEASURE9,
sum(f.inv_on_hold_amt'||l_curr_suffix||') FII_MEASURE10,
sum(f.inv_on_hold_amt'||l_curr_suffix||')/sum(f.open_amt'||l_curr_suffix||')*100 FII_MEASURE11
from FII_AP_LIA_KPI_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||'
AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)';
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(on_hold_past_due_amt) FII_MEASURE4,
sum(inv_on_hold_amt) - sum(on_hold_past_due_amt) FII_MEASURE5,
sum(gt_inv_on_hold_amt) FII_MEASURE6,
sum(gt_inv_on_hold_count) FII_MEASURE7,
sum(gt_no_of_holds) FII_MEASURE8,
sum(gt_on_hold_past_due_amt) FII_MEASURE9,
sum(gt_hold_due_amt) FII_MEASURE10,
decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'',
'''||l_url_4||''', ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
null) FII_ATTRIBUTE1
FROM
(SELECT f.'||l_column_name||' id,
sum(f.inv_on_hold_amt'||l_currency||') inv_on_hold_amt,
( rank() over (ORDER BY sum(f.inv_on_hold_amt'||l_currency||') '||l_order2||' nulls last, f.'||l_column_name||')) - 1 rnk,
sum(f.inv_on_hold_count) inv_on_hold_count,
sum(f.no_of_holds) no_of_holds,
sum(f.on_hold_past_due_amt'||l_currency||') on_hold_past_due_amt,
sum(sum(f.inv_on_hold_amt'||l_currency||')) over() gt_inv_on_hold_amt,
sum(sum(inv_on_hold_count)) over() gt_inv_on_hold_count,
sum(sum(no_of_holds)) over() gt_no_of_holds,
sum(sum(f.on_hold_past_due_amt'||l_currency||')) over() gt_on_hold_past_due_amt,
sum(sum(f.inv_on_hold_amt'||l_currency||') - sum(f.on_hold_past_due_amt'||l_currency||')) over() gt_hold_due_amt
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)
GROUP BY viewby_dim.value, viewby_dim.id
&ORDER_BY_CLAUSE ' ;