[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 = mv.inv_org_id)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = mv.inv_org_id
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
SELECT 1
FROM ISC_BOOK_SUM2_PDUE_F mv
WHERE mv.time_snapshot_date_id BETWEEN :l_effective_start_date
AND :l_as_of_date
AND rownum = 1';
(SELECT sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket1_line_cnt, 0)) CURR1,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket2_line_cnt, 0)) CURR2,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket3_line_cnt, 0)) CURR3,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket4_line_cnt, 0)) CURR4,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket5_line_cnt, 0)) CURR5,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket6_line_cnt, 0)) CURR6,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket7_line_cnt, 0)) CURR7,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket8_line_cnt, 0)) CURR8,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket9_line_cnt, 0)) CURR9,
sum(decode(mv.time_snapshot_date_id,a.day,
mv.bucket10_line_cnt, 0)) CURR10,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket1_line_cnt, 0)) PREV1,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket2_line_cnt, 0)) PREV2,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket3_line_cnt, 0)) PREV3,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket4_line_cnt, 0)) PREV4,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket5_line_cnt, 0)) PREV5,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket6_line_cnt, 0)) PREV6,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket7_line_cnt, 0)) PREV7,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket8_line_cnt, 0)) PREV8,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket9_line_cnt, 0)) PREV9,
sum(decode(mv.time_snapshot_date_id, b.day,
mv.bucket10_line_cnt, 0)) PREV10
FROM (SELECT max(mv.time_snapshot_date_id) DAY
FROM ISC_DBI_FM_0006_MV mv
WHERE mv.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
AND &BIS_CURRENT_ASOF_DATE
) a,
(SELECT max(mv.time_snapshot_date_id) DAY
FROM ISC_DBI_FM_0006_MV mv
WHERE mv.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
) b,
ISC_DBI_FM_0006_MV mv
WHERE mv.time_snapshot_date_id IN (a.day, b.day)
AND mv.item_cat_flag = :ISC_ITEM_CAT_FLAG
AND mv.customer_flag = :ISC_CUSTOMER_FLAG'
||l_inv_org_where||l_inv_cat_where||l_item_where||l_customer_where||'),';
SELECT bucket ISC_ATTRIBUTE_2,
bucket_type ISC_ATTRIBUTE_3,
line_cnt ISC_MEASURE_2,
prev_line_cnt ISC_MEASURE_3,
(line_cnt - prev_line_cnt)
/ decode( prev_line_cnt,0,
NULL,
abs(prev_line_cnt)) * 100
ISC_MEASURE_4, -- Past Due Schedule Line Change
line_cnt
/ decode ( sum(line_cnt) over (),0,
NULL,
sum(line_cnt) over ()) * 100
ISC_MEASURE_5, -- Percent of Total
sum(line_cnt) over () ISC_MEASURE_6, -- Grand Total for Past Due Schedule Lines
(sum(line_cnt) over () - sum(prev_line_cnt) over())
/ decode( sum(prev_line_cnt) over (),0,
NULL,
abs(sum(prev_line_cnt) over ())) * 100
ISC_MEASURE_7, -- Grand Total Past Due Schedule Line Change
sum(line_cnt) over ()
/ decode ( sum(line_cnt) over (),0,
NULL,
sum(line_cnt) over ()) * 100
ISC_MEASURE_1, -- Grand Total for Percent of Total
null CURRENCY -- obsolete from DBI 5.0
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) 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_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
ORDER BY BUCKET_TYPE';
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 CURRENCY
FROM dual
WHERE 1 = 2 /* No snapshot has been taken during this period*/';