[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 = f.inv_org_id)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = f.inv_org_id
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
(SELECT sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket1_line_cnt, 0)) CURR1,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket2_line_cnt, 0)) CURR2,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket3_line_cnt, 0)) CURR3,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket4_line_cnt, 0)) CURR4,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket5_line_cnt, 0)) CURR5,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket6_line_cnt, 0)) CURR6,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket7_line_cnt, 0)) CURR7,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket8_line_cnt, 0)) CURR8,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket9_line_cnt, 0)) CURR9,
sum(decode(to_date(f.time_id,''j''), &BIS_CURRENT_ASOF_DATE,
f.bucket10_line_cnt, 0)) CURR10,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket1_line_cnt, 0)) PREV1,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket2_line_cnt, 0)) PREV2,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket3_line_cnt, 0)) PREV3,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket4_line_cnt, 0)) PREV4,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket5_line_cnt, 0)) PREV5,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket6_line_cnt, 0)) PREV6,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket7_line_cnt, 0)) PREV7,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket8_line_cnt, 0)) PREV8,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket9_line_cnt, 0)) PREV9,
sum(decode(to_date(f.time_id,''j''), &BIS_PREVIOUS_ASOF_DATE,
f.bucket10_line_cnt, 0)) PREV10
FROM ISC_DBI_FM_0000_MV f
WHERE f.time_id in
(to_char(&BIS_CURRENT_ASOF_DATE,''j''),to_char(&BIS_PREVIOUS_ASOF_DATE,''j''))
AND f.period_type_id = 1
AND f.agg_level = :ISC_AGG_LEVEL';
(SELECT sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket1_line_cnt, 0)) CURR1,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket2_line_cnt, 0)) CURR2,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket3_line_cnt, 0)) CURR3,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket4_line_cnt, 0)) CURR4,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket5_line_cnt, 0)) CURR5,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket6_line_cnt, 0)) CURR6,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket7_line_cnt, 0)) CURR7,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket8_line_cnt, 0)) CURR8,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket9_line_cnt, 0)) CURR9,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
f.bucket10_line_cnt, 0)) CURR10,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket1_line_cnt, 0)) PREV1,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket2_line_cnt, 0)) PREV2,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket3_line_cnt, 0)) PREV3,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket4_line_cnt, 0)) PREV4,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket5_line_cnt, 0)) PREV5,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket6_line_cnt, 0)) PREV6,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket7_line_cnt, 0)) PREV7,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket8_line_cnt, 0)) PREV8,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket9_line_cnt, 0)) PREV9,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
f.bucket10_line_cnt, 0)) PREV10
FROM ISC_DBI_FM_0000_MV f,
FII_TIME_RPT_STRUCT_V cal
WHERE f.time_id = cal.time_id
AND f.agg_level = :ISC_AGG_LEVEL
AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND cal.period_type_id = f.period_type_id
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id';
SELECT bucket ISC_ATTRIBUTE_2,
bucket_type ISC_ATTRIBUTE_3,
sum(c.prev_shipped_line_cnt) ISC_MEASURE_2, -- Lines Shipped - prior
sum(c.curr_shipped_line_cnt) ISC_MEASURE_1, -- Lines Shipped
(sum(c.curr_shipped_line_cnt) - sum(c.prev_shipped_line_cnt))
/ decode( sum(c.prev_shipped_line_cnt),0,
NULL,
abs(sum(c.prev_shipped_line_cnt))) * 100
ISC_MEASURE_3, -- Lines Shipped Change
sum(c.curr_shipped_line_cnt)
/ decode ( sum(sum(c.curr_shipped_line_cnt)) over (), 0,
NULL,
sum(sum(c.curr_shipped_line_cnt)) over ()) * 100
ISC_MEASURE_4, -- Percent of Total
sum(sum(c.curr_shipped_line_cnt)) over ()
ISC_MEASURE_5, -- Gd Total for Lines Shipped
(sum(sum(c.curr_shipped_line_cnt)) over ()
- sum(sum(c.prev_shipped_line_cnt)) over ())
/ decode( sum(sum(c.prev_shipped_line_cnt)) over (),0,
NULL,
abs(sum(sum(c.prev_shipped_line_cnt)) over())) * 100
ISC_MEASURE_6, -- Gd Total for Change
sum(sum(c.curr_shipped_line_cnt)) over ()
/ decode ( sum(sum(c.curr_shipped_line_cnt)) over (), 0,
NULL,
sum(sum(c.curr_shipped_line_cnt)) over ()) * 100
ISC_MEASURE_7 -- Gd Total for Percent 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, CURR1,
2, CURR2,
3, CURR3,
4, CURR4,
5, CURR5,
6, CURR6,
7, CURR7,
8, CURR8,
9, CURR9,
10, CURR10,
null) CURR_SHIPPED_LINE_CNT,
decode(rownum,
1, PREV1,
2, PREV2,
3, PREV3,
4, PREV4,
5, PREV5,
6, PREV6,
7, PREV7,
8, PREV8,
9, PREV9,
10, PREV10,
null) PREV_SHIPPED_LINE_CNT
FROM'
||l_row_line_cnts||'
(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 BUCKET IS NOT NULL
GROUP BY BUCKET_TYPE,BUCKET
ORDER BY BUCKET_TYPE';