[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sum(decode(bbc.range1_low, null, 0, 1) +
decode(bbc.range2_low, null, 0, 1) +
decode(bbc.range3_low, null, 0, 1) +
decode(bbc.range4_low, null, 0, 1) +
decode(bbc.range5_low, null, 0, 1) +
decode(bbc.range6_low, null, 0, 1) +
decode(bbc.range7_low, null, 0, 1) +
decode(bbc.range8_low, null, 0, 1) +
decode(bbc.range9_low, null, 0, 1) +
decode(bbc.range10_low, null, 0, 1)) bucket_count
INTO l_bucket_ct
FROM bis_bucket_customizations bbc,
bis_bucket bb
WHERE bb.short_name = 'FII_DBI_CURRENT_REC_BUCKET'
AND bbc.bucket_id = bb.bucket_id;
/*This means no particular sort column is selected in the report. So sort on
the default column in descending order. NVL is added to make sure the null
values appear last. */
l_order_by_clause := 'ORDER BY NVL(FII_AR_CURR_REC_AMT, -999999999) DESC';
'SELECT VIEWBY,
VIEWBYID ';
SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
v.viewby VIEWBY,
v.viewby_code VIEWBYID,
sum(f.total_open_amount) FII_AR_OPEN_REC_AMT,
sum(f.current_open_amount) FII_AR_CURR_REC_AMT,
sum(f.current_open_count) FII_AR_CURR_REC_CT,
sum(f.wtd_terms_out_current_num) WTD_TERMS_OUT_CURRENT_NUM,
sum(f.wtd_terms_out_current_num) / NULLIF(sum(f.current_open_amount),0) FII_AR_WEIGHTED_TO ' ||
l_bucket_sql || l_cust_clause || '
FROM fii_ar_net_rec'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
(SELECT /*+ no_merge '||l_gt_hint|| ' */ *
FROM fii_time_structures cal, '||
fii_ar_util_pkg.get_from_statement ||
' gt WHERE cal.report_date = :ASOF_DATE
AND bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
AND '|| fii_ar_util_pkg.get_where_statement || ') v
WHERE f.time_id = v.time_id
AND f.period_type_id = v.period_type_id
AND f.org_id = v.org_id
AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_where_clause ||
' GROUP BY v.viewby, v.viewby_code ' || l_cust_clause || ' ) inner_view ' || l_order_by_clause;