[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 = inv_org)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = inv_org
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
SELECT fii.name VIEWBY,
s.prev_bklg_value ISC_MEASURE_1, -- bklg prior
s.curr_bklg_value ISC_MEASURE_2, -- bklg
(s.curr_bklg_value - s.prev_bklg_value)
/ decode(s.prev_bklg_value, 0, NULL,
abs(s.prev_bklg_value)) * 100 ISC_MEASURE_3, -- bklg change
s.prev_pdue_value ISC_MEASURE_4, -- pdue prior
s.curr_pdue_value ISC_MEASURE_5, -- pdue
(s.curr_pdue_value - s.prev_pdue_value)
/ decode(s.prev_pdue_value, 0, NULL,
abs(s.prev_pdue_value)) * 100 ISC_MEASURE_6 -- pdue change
FROM (SELECT start_date START_DATE,
sum(c_y_bklg) + sum(c_book_ytd)
- sum(c_fulf_ytd) CURR_BKLG_VALUE,
sum(p_y_bklg) + sum(p_book_ytd)
- sum(p_fulf_ytd) PREV_BKLG_VALUE,
sum(curr_pdue_value) CURR_PDUE_VALUE,
sum(prev_pdue_value) PREV_PDUE_VALUE
FROM /* Compute year backlog balance */
(SELECT dates.start_date START_DATE,
fact.inv_org_id INV_ORG,
decode(dates.period, ''C'',
fact.bklg_amt_'||l_curr_suffix||', 0) C_Y_BKLG,
decode(dates.period, ''P'',
fact.bklg_amt_'||l_curr_suffix||', 0) P_Y_BKLG,
0 C_BOOK_YTD,
0 P_BOOK_YTD,
0 C_FULF_YTD,
0 P_FULF_YTD,
NULL CURR_PDUE_VALUE,
NULL PREV_PDUE_VALUE
FROM (SELECT fii.start_date START_DATE,
''C'' PERIOD,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY fii.start_date DESC) p1,
(SELECT fii.start_date START_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii.start_date DESC) p2
WHERE p1.id(+) = p2.id) dates,
'||l_mv1||' fact,
FII_TIME_DAY day'||l_prod_cat_from||'
WHERE day.report_date = dates.report_date
AND day.ent_year_start_date = fact.time_snapshot_date_id'
||l_flags_where
||l_prod_cat_where
||l_prod_where
||l_cust_where||'
UNION ALL /* Computer YTD net Booking */
SELECT dates.start_date START_DATE,
fact.inv_org_id INV_ORG,
0 C_Y_BKLG,
0 P_Y_BKLG,
decode(dates.period, ''C'',
fact.booked_amt2_'||l_curr_suffix||', 0) C_BOOK_YTD,
decode(dates.period, ''P'',
fact.booked_amt2_'||l_curr_suffix||', 0) P_BOOK_YTD,
0 C_FULF_YTD,
0 P_FULF_YTD,
NULL CURR_PDUE_VALUE,
NULL PREV_PDUE_VALUE
FROM (SELECT fii.start_date START_DATE,
''C'' PERIOD,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY fii.start_date DESC) p1,
(SELECT fii.start_date START_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii.start_date DESC) p2
WHERE p1.id(+) = p2.id) dates,
'||l_mv_book||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE cal.report_date = dates.report_date
AND fact.time_id = cal.time_id
AND fact.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id, 119) = cal.record_type_id'
||l_flags_where2
||l_prod_cat_where
||l_prod_where
||l_cust_where||'
UNION ALL /* Computer YTD net fulfillment */
SELECT dates.start_date START_DATE,
fact.inv_org_id INV_ORG,
0 C_Y_BKLG,
0 P_Y_BKLG,
0 C_BOOK_YTD,
0 P_BOOK_YTD,
decode(dates.period, ''C'',
fact.booked_amt_'||l_curr_suffix||', 0) C_FULF_YTD,
decode(dates.period, ''P'',
fact.booked_amt_'||l_curr_suffix||', 0) P_FULF_YTD,
NULL CURR_PDUE_VALUE,
NULL PREV_PDUE_VALUE
FROM (SELECT fii.start_date START_DATE,
''C'' PERIOD,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY fii.start_date DESC) p1,
(SELECT fii.start_date START_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii.start_date DESC) p2
WHERE p1.id(+) = p2.id) dates,
'||l_mv_fulf||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE cal.report_date = dates.report_date
AND fact.time_id = cal.time_id
AND fact.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id, 119) = cal.record_type_id'
||l_flags_where2
||l_prod_cat_where
||l_prod_where
||l_cust_where||'
UNION ALL
SELECT dates.start_date START_DATE,
fact.inv_org_id INV_ORG,
0 C_Y_BKLG,
0 P_Y_BKLG,
0 C_BOOK_YTD,
0 P_BOOK_YTD,
0 C_FULF_YTD,
0 P_FULF_YTD,
decode(fact.time_snapshot_date_id, dates.curr_day,
fact.pdue_amt_'||l_curr_suffix||', NULL) CURR_PDUE_VALUE,
decode(fact.time_snapshot_date_id, dates.prev_day,
fact.pdue_amt_'||l_curr_suffix||', NULL) PREV_PDUE_VALUE
FROM (SELECT curr.start_date START_DATE,
curr.day CURR_DAY,
prev.day PREV_DAY
FROM (SELECT start_date,
day,
rownum ID
FROM
(SELECT fii.start_date START_DATE,
max(fact.time_snapshot_date_id) DAY
FROM '||l_period_type||' fii,
'||l_mv2||' fact
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
AND fact.time_snapshot_date_id (+) >= fii.start_date
AND fact.time_snapshot_date_id (+) <= fii.end_date
AND fact.time_snapshot_date_id (+) <= &BIS_CURRENT_ASOF_DATE
GROUP BY fii.start_date)
ORDER BY start_date DESC) curr,
(SELECT start_date,
day,
rownum ID
FROM
(SELECT fii.start_date START_DATE,
max(fact.time_snapshot_date_id) DAY
FROM '||l_period_type||' fii,
'||l_mv2||' fact
WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
AND fact.time_snapshot_date_id (+) >= fii.start_date
AND fact.time_snapshot_date_id (+) <= fii.end_date
AND fact.time_snapshot_date_id (+) <= &BIS_PREVIOUS_ASOF_DATE
GROUP BY fii.start_date)
ORDER BY start_date DESC) prev
WHERE curr.id = prev.id(+)) dates,
'||l_mv2||' fact'||l_prod_cat_from||'
WHERE fact.time_snapshot_date_id IN (dates.curr_day, dates.prev_day)
AND fact.late_schedule_flag = 1'
||l_flags_where
||l_prod_cat_where
||l_prod_where
||l_cust_where||')'
||l_inv_org_where||'
GROUP BY start_date) s,
'||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
AND fii.start_date = s.start_date(+)
ORDER BY fii.start_date';