[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
(SELECT 1
FROM org_access o
WHERE o.responsibility_id = fnd_global.resp_id
AND o.resp_application_id = fnd_global.resp_appl_id
AND o.organization_id = fact.inv_org_id)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = fact.inv_org_id
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
SELECT 1
FROM ISC_DBI_CFM_008_MV fact
WHERE fact.time_snapshot_date_id BETWEEN :l_effective_start_date
AND :l_as_of_date
AND rownum = 1 ';
SELECT 0 ISC_ATTRIBUTE_2,
0 ISC_ATTRIBUTE_3,
0 ISC_MEASURE_1,
0 ISC_MEASURE_2,
0 ISC_MEASURE_3,
0 ISC_MEASURE_4,
0 ISC_MEASURE_5,
0 ISC_MEASURE_6,
0 ISC_MEASURE_7,
0 ISC_MEASURE_8,
0 ISC_MEASURE_9
FROM dual
WHERE 1 = 2 -- no snapshot taken in the current period';
SELECT c.bucket ISC_ATTRIBUTE_2, -- bucket name
c.bucket_type ISC_ATTRIBUTE_3, -- bucket type
c.curr_line_cnt ISC_MEASURE_1, -- pdue line cnt
c.prev_pdue_value ISC_MEASURE_2, -- pdue prior
c.curr_pdue_value ISC_MEASURE_3, -- pdue
(c.curr_pdue_value - c.prev_pdue_value)
/ decode(c.prev_pdue_value, 0, NULL,
abs(c.prev_pdue_value)) * 100 ISC_MEASURE_4, -- pdue change
c.curr_pdue_value
/ decode(sum(c.curr_pdue_value) over (), 0, NULL,
abs(sum(c.curr_pdue_value) over ())) * 100 ISC_MEASURE_5, -- pdue % of total
sum(c.curr_line_cnt) over () ISC_MEASURE_6, -- gd total pdue line cnt
sum(c.curr_pdue_value) over () ISC_MEASURE_7, -- gd total pdue
(sum(c.curr_pdue_value) over () - sum(c.prev_pdue_value) over ())
/ decode(sum(c.prev_pdue_value) over (), 0, NULL,
abs(sum(c.prev_pdue_value) over ())) * 100 ISC_MEASURE_8, -- gd total pdue change
sum(c.curr_pdue_value) over ()
/ decode(sum(c.curr_pdue_value) over (), 0, NULL,
abs(sum(c.curr_pdue_value) over ())) * 100 ISC_MEASURE_9 -- gd total pdue % of total
FROM
(SELECT decode(rownum,
1, :ISC_R1,
2, :ISC_R2,
3, :ISC_R3,
4, :ISC_R4,
5, :ISC_R5,
6, :ISC_R6,
7, :ISC_R7,
8, :ISC_R8,
9, :ISC_R9,
10, :ISC_R10,
NULL) BUCKET,
rownum BUCKET_TYPE,
decode(rownum,
1, m.curr_line_cnt_1,
2, m.curr_line_cnt_2,
3, m.curr_line_cnt_3,
4, m.curr_line_cnt_4,
5, m.curr_line_cnt_5,
6, m.curr_line_cnt_6,
7, m.curr_line_cnt_7,
8, m.curr_line_cnt_8,
9, m.curr_line_cnt_9,
10, m.curr_line_cnt_10,
NULL) CURR_LINE_CNT,
decode(rownum,
1, m.curr_pdue_value_1,
2, m.curr_pdue_value_2,
3, m.curr_pdue_value_3,
4, m.curr_pdue_value_4,
5, m.curr_pdue_value_5,
6, m.curr_pdue_value_6,
7, m.curr_pdue_value_7,
8, m.curr_pdue_value_8,
9, m.curr_pdue_value_9,
10, m.curr_pdue_value_10,
NULL) CURR_PDUE_VALUE,
decode(rownum,
1, m.prev_pdue_value_1,
2, m.prev_pdue_value_2,
3, m.prev_pdue_value_3,
4, m.prev_pdue_value_4,
5, m.prev_pdue_value_5,
6, m.prev_pdue_value_6,
7, m.prev_pdue_value_7,
8, m.prev_pdue_value_8,
9, m.prev_pdue_value_9,
10, m.prev_pdue_value_10,
NULL) PREV_PDUE_VALUE
FROM (SELECT sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket1_line_cnt, 0)) CURR_LINE_CNT_1,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket2_line_cnt, 0)) CURR_LINE_CNT_2,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket3_line_cnt, 0)) CURR_LINE_CNT_3,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket4_line_cnt, 0)) CURR_LINE_CNT_4,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket5_line_cnt, 0)) CURR_LINE_CNT_5,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket6_line_cnt, 0)) CURR_LINE_CNT_6,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket7_line_cnt, 0)) CURR_LINE_CNT_7,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket8_line_cnt, 0)) CURR_LINE_CNT_8,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket9_line_cnt, 0)) CURR_LINE_CNT_9,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket10_line_cnt, 0)) CURR_LINE_CNT_10,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket1_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_1,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket2_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_2,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket3_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_3,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket4_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_4,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket5_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_5,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket6_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_6,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket7_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_7,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket8_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_8,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket9_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_9,
sum(decode(fact.time_snapshot_date_id, a.day,
fact.bucket10_pdue_amt_'||l_curr_suffix||', 0)) CURR_PDUE_VALUE_10,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket1_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_1,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket2_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_2,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket3_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_3,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket4_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_4,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket5_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_5,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket6_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_6,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket7_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_7,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket8_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_8,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket9_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_9,
sum(decode(fact.time_snapshot_date_id, b.day,
fact.bucket10_pdue_amt_'||l_curr_suffix||', 0)) PREV_PDUE_VALUE_10
FROM (SELECT max(time_snapshot_date_id) day
FROM '||l_mv1||' fact
WHERE fact.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
AND &BIS_CURRENT_ASOF_DATE
) a,
(SELECT max(time_snapshot_date_id) day
FROM '||l_mv1||' fact
WHERE fact.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
) b,
'||l_mv1||' fact'||l_prod_cat_from||'
WHERE fact.time_snapshot_date_id IN (a.day, b.day)'
||l_flags_where||'
AND fact.late_schedule_flag = 1'
||l_inv_org_where
||l_prod_cat_where
||l_prod_where
||l_cust_where
||') m,
(SELECT 1 FROM DUAL -- dummy table with 10 rows
UNION ALL SELECT 1 FROM DUAL
UNION ALL SELECT 1 FROM DUAL
UNION ALL SELECT 1 FROM DUAL
UNION ALL SELECT 1 FROM DUAL
UNION ALL SELECT 1 FROM DUAL
UNION ALL SELECT 1 FROM DUAL
UNION ALL SELECT 1 FROM DUAL
UNION ALL SELECT 1 FROM DUAL
UNION ALL SELECT 1 FROM DUAL) ) c
WHERE c.bucket IS NOT NULL
ORDER BY c.bucket_type';