[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlstmt := 'select VIEWBY,
VIEWBYID,
sum(FII_MEASURE1) FII_MEASURE1,
sum(FII_MEASURE12) FII_MEASURE12,
sum(FII_MEASURE2) FII_MEASURE2,
sum(FII_MEASURE16) FII_MEASURE16,
sum(FII_MEASURE4) FII_MEASURE4,
sum(FII_MEASURE5) FII_MEASURE5,
sum(FII_MEASURE7) FII_MEASURE7,
sum(FII_MEASURE8) FII_MEASURE8,
sum(FII_MEASURE10) FII_MEASURE10,
sum(FII_MEASURE11) FII_MEASURE11,
sum(FII_MEASURE60) FII_MEASURE60,
sum(FII_MEASURE61) FII_MEASURE61,
sum(FII_MEASURE14) FII_MEASURE14,
sum(FII_MEASURE15) FII_MEASURE15,
sum(FII_MEASURE62) FII_MEASURE62,
sum(FII_MEASURE63) FII_MEASURE63,
sum(FII_MEASURE18) FII_MEASURE18,
sum(FII_MEASURE28) FII_MEASURE28,
sum(FII_MEASURE29) FII_MEASURE29,
sum(FII_MEASURE20) FII_MEASURE20,
sum(FII_MEASURE24) FII_MEASURE24,
sum(FII_ATTRIBUTE2) FII_ATTRIBUTE2,
sum(FII_ATTRIBUTE4) FII_ATTRIBUTE4,
sum(FII_MEASURE23) FII_MEASURE23,
sum(FII_MEASURE26) FII_MEASURE26,
sum(FII_MEASURE21) FII_MEASURE21,
sum(FII_MEASURE22) FII_MEASURE22,
sum(FII_MEASURE25) FII_MEASURE25,
sum(FII_ATTRIBUTE6) FII_ATTRIBUTE6,
sum(FII_ATTRIBUTE3) FII_ATTRIBUTE3,
sum(FII_ATTRIBUTE5) FII_ATTRIBUTE5,
sum(FII_GRAND_TOTAL1) FII_GRAND_TOTAL1,
sum(FII_GRAND_TOTAL2) FII_GRAND_TOTAL2,
sum(FII_GRAND_TOTAL3) FII_GRAND_TOTAL3,
sum(FII_MEASURE66) FII_MEASURE66,
sum(FII_MEASURE67) FII_MEASURE67,
decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''', ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''', null) FII_ATTRIBUTE10,
decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_3||''', ''SUPPLIER+POA_SUPPLIERS'','''||l_url_2||''', null) FII_ATTRIBUTE11
FROM (select VIEWBY,
VIEWBYID,
FII_MEASURE1 FII_MEASURE1,
FII_MEASURE12 FII_MEASURE12,
FII_MEASURE2 FII_MEASURE2,
FII_MEASURE16 FII_MEASURE16,
FII_MEASURE4 FII_MEASURE4,
FII_MEASURE5 FII_MEASURE5,
FII_MEASURE7 FII_MEASURE7,
FII_MEASURE8 FII_MEASURE8,
FII_MEASURE10 FII_MEASURE10,
FII_MEASURE11 FII_MEASURE11,
FII_MEASURE60 FII_MEASURE60,
FII_MEASURE61 FII_MEASURE61,
FII_MEASURE14 FII_MEASURE14,
FII_MEASURE15 FII_MEASURE15,
FII_MEASURE62 FII_MEASURE62,
FII_MEASURE63 FII_MEASURE63,
FII_MEASURE18 FII_MEASURE18,
FII_MEASURE28 FII_MEASURE28,
FII_MEASURE29 FII_MEASURE29,
sum(FII_MEASURE1) over() FII_MEASURE20,
sum(FII_MEASURE14) over() FII_MEASURE24,
sum(FII_MEASURE4) over() FII_ATTRIBUTE2,
sum(FII_MEASURE7) over() FII_ATTRIBUTE4,
decode(sum(FII_MEASURE61) over(), 0, 0,
(sum(FII_MEASURE60) over() - sum(FII_MEASURE61) over()) * 100
/sum(FII_MEASURE61) over()
) FII_MEASURE23, -- Bug #3969884
decode(sum(FII_MEASURE63) over(), 0, 0,
(sum(FII_MEASURE62) over() - sum(FII_MEASURE63) over()) * 100
/sum(FII_MEASURE63) over()
) FII_MEASURE26, -- Bug #3969884
decode(sum(FII_MEASURE2) over(), 0, 0,
(sum(FII_MEASURE1) over() - sum(FII_MEASURE2) over()) *100/sum(FII_MEASURE2) over()) FII_MEASURE21, -- Bug #3969884
sum(FII_MEASURE60) over() FII_MEASURE22,
sum(FII_MEASURE62) over() FII_MEASURE25,
sum(FII_MEASURE10) over() FII_ATTRIBUTE6,
decode(sum(FII_MEASURE5) over(), 0, 0,
(sum(FII_MEASURE4) over() - sum(FII_MEASURE5) over()) *100
/sum(FII_MEASURE5) over()) FII_ATTRIBUTE3, -- Bug #3969884
decode(sum(FII_MEASURE8) over(), 0, 0,
(sum(FII_MEASURE7) over() - sum(FII_MEASURE8) over()) *100
/sum(FII_MEASURE8) over()) FII_ATTRIBUTE5, -- Bug #3969884
decode(sum(FII_MEASURE7) over(), 0, 0,
sum(FII_MEASURE18) over() * 100/sum(FII_MEASURE7) over()
) FII_GRAND_TOTAL1, -- Bug #3969884
decode(sum(FII_MEASURE12) over(), 0, 0,
sum(FII_MEASURE28) over()
/sum(FII_MEASURE12) over() ) FII_GRAND_TOTAL2, -- Bug #3969884
case when sum(FII_MEASURE12) over() = 0 then 0 else
case when sum(FII_MEASURE16) over() = 0 then 0 else
case when sum(FII_MEASURE29) over() = 0 then 0 else
(
(sum(FII_MEASURE28) over()
/sum(FII_MEASURE12) over() -
sum(FII_MEASURE29) over()
/sum(FII_MEASURE16) over()
)*100)
/(sum(FII_MEASURE29) over()
/sum(FII_MEASURE16) over() )
end
end
end FII_GRAND_TOTAL3, -- Bug #3969884
decode(sum(FII_MEASURE4) over(), 0, 0,
sum(FII_MEASURE62) over()*100/sum(FII_MEASURE4) over()) FII_MEASURE66, -- Bug #3969884
case when sum(FII_MEASURE4) over() = 0 then 0 else
case when sum(FII_MEASURE5) over() = 0 then 0 else
case when sum(FII_MEASURE63) over() = 0 then 0 else
((sum(FII_MEASURE62) over()*100/sum(FII_MEASURE4) over()) - (sum(FII_MEASURE63) over()*100/sum(FII_MEASURE5) over()))
end
end
end FII_MEASURE67, -- Bug #3969884
( rank() over (&ORDER_BY_CLAUSE nulls last, VIEWBYID)) - 1 rnk
FROM (
select VIEWBY,
VIEWBYID,
sum(FII_MEASURE1) FII_MEASURE1,
sum(FII_MEASURE12) FII_MEASURE12,
sum(FII_MEASURE2) FII_MEASURE2,
sum(FII_MEASURE16) FII_MEASURE16,
sum(FII_MEASURE4) FII_MEASURE4,
sum(FII_MEASURE5) FII_MEASURE5,
sum(FII_MEASURE7) FII_MEASURE7,
sum(FII_MEASURE8) FII_MEASURE8,
sum(FII_MEASURE10) FII_MEASURE10,
sum(FII_MEASURE11) FII_MEASURE11,
sum(FII_MEASURE60) FII_MEASURE60,
sum(FII_MEASURE61) FII_MEASURE61,
sum(FII_MEASURE14) FII_MEASURE14,
sum(FII_MEASURE15) FII_MEASURE15,
sum(FII_MEASURE62) FII_MEASURE62,
sum(FII_MEASURE63) FII_MEASURE63,
sum(FII_MEASURE18) FII_MEASURE18,
sum(FII_MEASURE28) FII_MEASURE28,
sum(FII_MEASURE29) FII_MEASURE29
from(
select viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end) FII_MEASURE1,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.paid_amt_b else 0 end) FII_MEASURE12,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end) FII_MEASURE2,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.paid_amt_b else 0 end) FII_MEASURE16,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.'||l_paid_inv_count||' else 0 end) FII_MEASURE4,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.'||l_paid_inv_count||' else 0 end) FII_MEASURE5,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.'||l_payment_count||' else 0 end) FII_MEASURE7,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.'||l_payment_count||' else 0 end) FII_MEASURE8,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.paid_on_time_amt'||l_currency||' else 0 end) FII_MEASURE10,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.paid_on_time_amt'||l_currency||' else 0 end) FII_MEASURE11,
0 FII_MEASURE60,
0 FII_MEASURE61,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.paid_late_amt'||l_currency||' else 0 end) FII_MEASURE14,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.paid_late_amt'||l_currency||' else 0 end) FII_MEASURE15,
0 FII_MEASURE62,
0 FII_MEASURE63,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.e_payment_count else 0 end) FII_MEASURE18,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.invoice_to_payment_days else 0 end) FII_MEASURE28,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.invoice_to_payment_days else 0 end) FII_MEASURE29
from FII_AP_PAID_XB_MV f,
fii_time_structures cal, '
||l_table_name||' viewby_dim
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
and f.'||l_column_name||' = viewby_dim.id '
||l_org_where||l_supplier_where||
' group by viewby_dim.value, viewby_dim.id, to_number(null)
union
select
viewby_dim.value VIEWBY,
viewby_dim.id VIEWBYID,
0 FII_MEASURE1,
0 FII_MEASURE12,
0 FII_MEASURE2,
0 FII_MEASURE16,
0 FII_MEASURE4,
0 FII_MEASURE5,
0 FII_MEASURE7,
0 FII_MEASURE8,
0 FII_MEASURE10,
0 FII_MEASURE11,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.'||l_paid_on_time||' else 0 end) FII_MEASURE60,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.'||l_paid_on_time||' else 0 end) FII_MEASURE61,
0 FII_MEASURE14,
0 FII_MEASURE15,
sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.'||l_paid_late||' else 0 end) FII_MEASURE62,
sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.'||l_paid_late||' else 0 end) FII_MEASURE63,
0 FII_MEASURE18,
0 FII_MEASURE28,
0 FII_MEASURE29
FROM FII_AP_PAYOL_XB_MV f,
fii_time_structures cal, '
||l_table_name||' viewby_dim
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
and f.'||l_column_name||' = viewby_dim.id '
||l_org_where||l_supplier_where||
' group by viewby_dim.value, viewby_dim.id, to_number(null))
group by VIEWBY, VIEWBYID)
)
where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
group by VIEWBY, VIEWBYID
&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_MEASURE4) FII_MEASURE4,
sum(f.FII_MEASURE28) FII_MEASURE28,
sum(f.FII_MEASURE5) FII_MEASURE5,
sum(f.FII_MEASURE8) FII_MEASURE8,
sum(f.FII_MEASURE9) FII_MEASURE9,
sum(f.FII_MEASURE11) FII_MEASURE11,
sum(f.FII_MEASURE12) FII_MEASURE12,
sum(f.FII_MEASURE14) FII_MEASURE14,
sum(f.FII_MEASURE15) FII_MEASURE15,
sum(f.FII_MEASURE20) FII_MEASURE20,
sum(f.FII_MEASURE21) FII_MEASURE21,
sum(f.FII_MEASURE22) FII_MEASURE22,
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_ATTRIBUTE2) FII_ATTRIBUTE2,
sum(f.FII_ATTRIBUTE3) FII_ATTRIBUTE3,
sum(f.FII_ATTRIBUTE4) FII_ATTRIBUTE4,
sum(f.FII_ATTRIBUTE5) FII_ATTRIBUTE5,
sum(f.FII_ATTRIBUTE6) FII_ATTRIBUTE6,
decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''',
''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''', null) FII_ATTRIBUTE10,
sum(f.FII_CV1) FII_CV1,
sum(f.FII_CV2) FII_CV2,
sum(f.FII_ATTRIBUTE7) FII_ATTRIBUTE7,
sum(f.FII_ATTRIBUTE8) FII_ATTRIBUTE8,
sum(f.FII_ATTRIBUTE13) FII_ATTRIBUTE13,
sum(f.FII_ATTRIBUTE14) FII_ATTRIBUTE14
from
(select ID,
FII_MEASURE1,
FII_MEASURE2,
FII_MEASURE4,
FII_MEASURE28,
FII_MEASURE5,
FII_MEASURE8,
FII_MEASURE9,
FII_MEASURE11,
FII_MEASURE12,
FII_MEASURE14,
FII_MEASURE15,
FII_MEASURE20,
FII_MEASURE21,
FII_MEASURE22,
FII_MEASURE23,
FII_MEASURE24,
FII_MEASURE25,
FII_MEASURE26,
FII_ATTRIBUTE2,
FII_ATTRIBUTE3,
FII_ATTRIBUTE4,
FII_ATTRIBUTE5,
FII_ATTRIBUTE6,
FII_CV1,
FII_CV2,
FII_ATTRIBUTE7,
FII_ATTRIBUTE8,
FII_ATTRIBUTE13,
FII_ATTRIBUTE14,
( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
from
(select f.'||l_column_name||' ID,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end) FII_MEASURE1,
sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end) FII_MEASURE2,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_inv_count'||l_per_type||' else 0 end) FII_MEASURE4,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then
(f.paid_amt'||l_currency||' + f.paid_dis_taken'||l_currency||') else 0 end) FII_MEASURE28,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end) FII_MEASURE5,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end) FII_MEASURE8,
sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end) FII_MEASURE9,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end) FII_MEASURE11,
sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end) FII_MEASURE12,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end) FII_MEASURE14,
sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end) FII_MEASURE15,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
FII_MEASURE20,
decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end),0), 0, 0,
(sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
- sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over())
*100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over())
FII_MEASURE21,
decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end),0), 0, 0,
(sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over()
- sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over())
*100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over())
FII_MEASURE22,
decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end),0), 0, 0,
(sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
- sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over())
*100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over())
FII_MEASURE23,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end)) over()
FII_MEASURE24,
decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end),0), 0, 0,
(sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end)) over()
- sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end)) over())
*100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end)) over())
FII_MEASURE25,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
FII_MEASURE26,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_inv_count'||l_per_type||' else 0 end)) over()
FII_ATTRIBUTE2,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
+ sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
FII_ATTRIBUTE3,
case when(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) = 0 then 0
when(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) = 0 then 0
else
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
*100/(sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
+ sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over())
end
FII_ATTRIBUTE4,
decode(nvl(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end),0), 0, 0,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over()
* 100/sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end)) over())
FII_ATTRIBUTE5,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over()
FII_ATTRIBUTE6,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end) FII_CV1,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then
(f.paid_amt'||l_currency||' + f.paid_dis_taken'||l_currency||') else 0 end) FII_CV2,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
+ sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
FII_ATTRIBUTE7,
sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
FII_ATTRIBUTE8,
case when(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then f.paid_amt'||l_currency||' else 0 end)) = 0
then 0
when(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then f.paid_dis_taken'||l_currency||' else 0 end)) = 0
then 0
else
sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then f.paid_dis_taken'||l_currency||' else 0 end)) over()
*100/(sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then f.paid_amt'||l_currency||' else 0 end)) over()
+ sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then f.paid_dis_taken'||l_currency||' else 0 end)) over()) end FII_ATTRIBUTE13,
decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end),0), 0, 0,
sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over()
* 100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end)) over()) FII_ATTRIBUTE14
from FII_AP_PAID_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)'
||l_org_where||l_supplier_where||'
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';