[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_id)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = inv_org_id
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
l_SQLText := 'SELECT fii.name VIEWBY,
nvl(sum(s.early_line_cnt),0) ISC_MEASURE_1,
nvl(sum(s.early_line_cnt),0)/
decode(sum(s.shipped_line_cnt),0,null,
sum(s.shipped_line_cnt))*100 ISC_MEASURE_2,
nvl(sum(s.on_time_line_cnt),0) ISC_MEASURE_3,
nvl(sum(s.on_time_line_cnt),0)/
decode(sum(s.shipped_line_cnt),0,null,
sum(s.shipped_line_cnt))*100 ISC_MEASURE_4,
nvl(sum(s.late_line_cnt),0) ISC_MEASURE_5,
nvl(sum(s.early_line_cnt),0)/
decode(sum(s.shipped_line_cnt),0,null,
sum(s.shipped_line_cnt))*100 ISC_MEASURE_10,--duplicate row for graph
nvl(sum(s.on_time_line_cnt),0)/
decode(sum(s.shipped_line_cnt),0,null,
sum(s.shipped_line_cnt))*100 ISC_MEASURE_11, --duplicate row for graph
nvl(sum(s.late_line_cnt),0)/
decode(sum(s.shipped_line_cnt),0,null,
sum(s.shipped_line_cnt))*100 ISC_MEASURE_6,
nvl(sum(s.shipped_line_cnt),0) ISC_MEASURE_7,
nvl(sum(s.scheduled_line_cnt),0) ISC_MEASURE_8,
nvl(sum(s.shipped_line_cnt),0)/
decode(sum(scheduled_line_cnt),0,null,
sum(scheduled_line_cnt))*100 ISC_MEASURE_9
FROM (SELECT start_date,early_line_cnt,on_time_line_cnt,
late_line_cnt,shipped_line_cnt,scheduled_line_cnt
FROM (SELECT dates.start_date START_DATE,
fact.early_line_cnt EARLY_LINE_CNT,
fact.on_time_line_cnt ON_TIME_LINE_CNT,
fact.late_line_cnt LATE_LINE_CNT,
fact.shipped_line_cnt SHIPPED_LINE_CNT,
0 SCHEDULED_LINE_CNT,
fact.inv_org_id INV_ORG_ID
FROM (SELECT fii.start_date START_DATE,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) CURR_DAY
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) dates,
ISC_DBI_FM_0000_MV fact,
FII_TIME_RPT_STRUCT_V cal
WHERE fact.agg_level = :ISC_AGG_LEVEL
AND cal.report_date IN (dates.curr_day)
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_inv_cat_where||l_item_where||l_cust_where||'
UNION ALL
SELECT dates.start_date START_DATE,
0 EARLY_LINE_CNT,
0 ON_TIME_LINE_CNT,
0 LATE_LINE_CNT,
0 SHIPPED_LINE_CNT,
schedule_line_cnt SCHEDULED_LINE_CNT,
fact.inv_org_id INV_ORG_ID
FROM (SELECT fii.start_date START_DATE,
least(fii.end_date,&BIS_CURRENT_ASOF_DATE) CURR_DAY
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) dates,
ISC_DBI_FM_0001_MV fact,
FII_TIME_RPT_STRUCT_V cal
WHERE fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
AND fact.customer_flag = :ISC_CUST_FLAG
AND cal.report_date = dates.curr_day
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_inv_cat_where||l_item_where||l_cust_where||')
'||l_org_where||')s,
'|| l_period_type ||' fii
WHERE fii.start_date = s.start_date(+)
AND fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
GROUP BY fii.name,fii.start_date
ORDER BY fii.start_date';