[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)))';
l_SQLText := 'SELECT fii1.start_date VIEWBY, '
|| l_att_2 || ' ISC_ATTRIBUTE_2,
nvl(s.p_line_shipped, 0) ISC_MEASURE_2,
nvl(s.c_line_shipped, 0) ISC_MEASURE_1,
nvl(s.c_late_schedule, 0) ISC_MEASURE_3,
nvl(s.p_late_schedule, 0) ISC_MEASURE_4,
nvl(s.p_late_promise, 0) ISC_MEASURE_6,
nvl(s.c_late_promise, 0) ISC_MEASURE_5,
null CURRENCY -- obsoleted item from DBI 5.0
FROM (SELECT dates.start_date START_DATE,
sum(decode(dates.period, ''C'',
fact.shipped_line_cnt, 0)) C_LINE_SHIPPED,
sum(decode(dates.period, ''P'',
fact.shipped_line_cnt, 0)) P_LINE_SHIPPED,
sum(decode(dates.period, ''C'',
fact.late_line_cnt, 0)) C_LATE_SCHEDULE,
sum(decode(dates.period, ''P'',
fact.late_line_cnt, 0)) P_LATE_SCHEDULE,
sum(decode(dates.period, ''C'',
fact.late_line_promise_cnt, 0)) C_LATE_PROMISE,
sum(decode(dates.period, ''P'',
fact.late_line_promise_cnt, 0)) P_LATE_PROMISE
FROM (SELECT fii1.start_date START_DATE,
''C'' PERIOD,
least(fii1.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii1
WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT p1.start_date START_DATE,
''P'' PERIOD,
p2.day REPORT_DATE
FROM (SELECT fii1.start_date START_DATE,
ROWNUM ID
FROM '||l_period_type||' fii1
WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii1.start_date DESC) p1,
(SELECT least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE) DAY,
ROWNUM ID
FROM '||l_period_type||' fii1
WHERE fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY fii1.start_date DESC) p2
WHERE p1.id = p2.id(+)) dates,
ISC_DBI_FM_0000_MV fact
WHERE fact.agg_level = :ISC_AGG_LEVEL
AND fact.time_id = to_char(dates.report_date,''j'')
AND fact.period_type_id = 1 '
||l_org_where||l_inv_cat_where||l_item_where||l_cust_where||'
GROUP BY dates.start_date) s,
'|| l_period_type ||' fii1
WHERE fii1.start_date = s.start_date(+)
AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii1.start_date';
l_SQLText := 'SELECT fii1.NAME VIEWBY, '
|| l_att_2 || ' ISC_ATTRIBUTE_2,
nvl(s.p_line_shipped, 0) ISC_MEASURE_2,
nvl(s.c_line_shipped, 0) ISC_MEASURE_1,
nvl(s.c_late_schedule, 0) ISC_MEASURE_3,
nvl(s.p_late_schedule, 0) ISC_MEASURE_4,
nvl(s.p_late_promise, 0) ISC_MEASURE_6,
nvl(s.c_late_promise, 0) ISC_MEASURE_5,
null CURRENCY -- obsoleted item from DBI 5.0
FROM (SELECT dates.start_date START_DATE,
sum(decode(dates.period, ''C'',
fact.shipped_line_cnt, 0)) C_LINE_SHIPPED,
sum(decode(dates.period, ''P'',
fact.shipped_line_cnt, 0)) P_LINE_SHIPPED,
sum(decode(dates.period, ''C'',
fact.late_line_cnt, 0)) C_LATE_SCHEDULE,
sum(decode(dates.period, ''P'',
fact.late_line_cnt, 0)) P_LATE_SCHEDULE,
sum(decode(dates.period, ''C'',
fact.late_line_promise_cnt, 0)) C_LATE_PROMISE,
sum(decode(dates.period, ''P'',
fact.late_line_promise_cnt, 0)) P_LATE_PROMISE
FROM (SELECT fii1.start_date START_DATE,
''C'' PERIOD,
least(fii1.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii1
WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT p1.start_date START_DATE,
''P'' PERIOD,
p2.day REPORT_DATE
FROM (SELECT fii1.start_date START_DATE,
ROWNUM ID
FROM '||l_period_type||' fii1
WHERE fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii1.start_date DESC) p1,
(SELECT least(fii1.end_date, &BIS_PREVIOUS_ASOF_DATE) DAY,
ROWNUM ID
FROM '||l_period_type||' fii1
WHERE fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY fii1.start_date DESC) p2
WHERE p1.id = p2.id(+)) dates,
ISC_DBI_FM_0000_MV fact,
FII_TIME_RPT_STRUCT_V cal
WHERE fact.agg_level = :ISC_AGG_LEVEL
AND cal.report_date = dates.report_date
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
AND fact.time_id = cal.time_id
AND fact.period_type_id = cal.period_type_id'
||l_org_where||l_inv_cat_where||l_item_where||l_cust_where||'
GROUP BY dates.start_date) s,
'|| l_period_type ||' fii1
WHERE fii1.start_date = s.start_date(+)
AND fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii1.start_date';