[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_PAST_DUE_REC_BUCKET'
AND bbc.bucket_id = bb.bucket_id;
'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,' ||
l_bucket_graph_sql || ',
sum(f.wtd_ddso_due_num) FII_AR_WEIGHTED_DDSO_G,
sum(f.current_open_amount) FII_AR_OPEN_REC_AMT,
sum(f.past_due_open_amount) FII_AR_PASTDUE_REC_AMT,
sum(f.past_due_count) FII_AR_PASTDUE_REC_CT,
sum(f.wtd_ddso_due_num) FII_AR_WEIGHTED_DDSO_NUM,
NULL FII_AR_DISPUTE_AMT,
NULL FII_AR_DISPUTE_CT, '||
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;
'SELECT
v.viewby VIEWBY,
v.viewby_code VIEWBYID, '||
l_dispute_bkt_graph_sql || ',
NULL FII_AR_WEIGHTED_DDSO_G,
NULL FII_AR_OPEN_REC_AMT,
NULL FII_AR_PASTDUE_REC_AMT,
NULL FII_AR_PASTDUE_REC_CT,
NULL FII_AR_WEIGHTED_DDSO_NUM,
sum(past_due_dispute_amount) FII_AR_DISPUTE_AMT,
sum(past_due_dispute_count) FII_AR_DISPUTE_CT, '||
l_dispute_bkt_sql || l_cust_clause || '
FROM fii_ar_disputes'||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 ' || l_where_clause ||
' GROUP BY v.viewby, v.viewby_code ' || l_cust_clause || ') inline_view
GROUP BY viewby, viewbyid ' || l_cust_clause2
||l_order_by;
SELECT 7, l_data_rec(1).l_label_7, l_data_rec(1).l_pdue_bkt7_amt
FROM dual
UNION
SELECT 6, l_data_rec(1).l_label_6, l_data_rec(1).l_pdue_bkt6_amt
FROM dual
UNION
SELECT 5, l_data_rec(1).l_label_5, l_data_rec(1).l_pdue_bkt5_amt
FROM dual
UNION
SELECT 4, l_data_rec(1).l_label_4, l_data_rec(1).l_pdue_bkt4_amt
FROM dual
UNION
SELECT 3, l_data_rec(1).l_label_3, l_data_rec(1).l_pdue_bkt3_amt
FROM dual
UNION
SELECT 2, l_data_rec(1).l_label_2, l_data_rec(1).l_pdue_bkt2_amt
FROM dual
UNION
SELECT 1, l_data_rec(1).l_label_1, l_data_rec(1).l_pdue_bkt1_amt
FROM dual
UNION
SELECT 0, l_data_rec(1).l_label_0, l_data_rec(1).l_curr_rec_amt
FROM dual;
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_PAST_DUE_REC_BUCKET'
AND bbc.bucket_id = bb.bucket_id;
delete from FII_AR_REC_AGING_SUM_GT;
'SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ ''' ||
l_curr_label || ''' ,''' ||
l_bis_bucket_rec.range1_name || ''' ,''' ||
l_bis_bucket_rec.range2_name || ''' ,''' ||
l_bis_bucket_rec.range3_name || ''' ,''' ||
l_bis_bucket_rec.range4_name || ''' ,''' ||
l_bis_bucket_rec.range5_name || ''' ,''' ||
l_bis_bucket_rec.range6_name || ''' ,''' ||
l_bis_bucket_rec.range7_name || ''' ,
SUM(f.current_bucket_1_amount) + SUM(f.current_bucket_2_amount)
+ SUM(f.current_bucket_3_amount),
SUM(f.past_due_bucket_1_amount),
SUM(f.past_due_bucket_2_amount),
SUM(f.past_due_bucket_3_amount),
SUM(f.past_due_bucket_4_amount),
SUM(f.past_due_bucket_5_amount),
SUM(f.past_due_bucket_6_amount),
SUM(f.past_due_bucket_7_amount)
FROM fii_ar_net_rec'||l_cust_suffix||'_mv'||l_curr_suffix||' f,
( SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/ *
FROM fii_time_structures cal, '||
fii_ar_util_pkg.get_from_statement ||
' gt WHERE cal.report_date = '''||l_as_of_date||
''' AND bitand(cal.record_type_id, '||l_itd_bitand||') = 512
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;
INSERT INTO FII_AR_REC_AGING_SUM_GT
( ord_seq, label, amount, creation_date, created_by, last_update_date, last_updated_by, last_update_login )
VALUES
( l_bucket_rec.l_ord_seq(i),
l_bucket_rec.l_label(i),
l_bucket_rec.l_amount(i),
sysdate,
l_fii_user_id,
sysdate,
l_fii_user_id,
l_fii_login_id);
l_sqlstmt := 'SELECT
amount FII_AR_REC_AMT_G,
label FII_AR_REC_LABEL,
amount FII_AR_REC_AMT,
DECODE( amount, 0, NULL,
NULL, NULL,
DECODE(ord_seq,
0, '''||l_curr_rec_url ||''',
'''||l_bkt_url ||''')) FII_AR_REC_AMT_DRILL
FROM FII_AR_REC_AGING_SUM_GT
ORDER BY ord_seq desc';