[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_resource_group := 'Selected';
l_resource_dept := 'Selected';
l_resource := 'Selected';
l_jobstatus := 'Selected';
select fnd_global.resp_id into l_respid from dual ;
select id into l_org from (select id from bis_organizations_v where responsibility_id = l_respid order by value asc) where rownum=1;
select process_enabled_flag
into l_flag
from mtl_parameters mp
where mp.organization_id = trim(both '''' from l_org);
'SELECT
fact.job_name ||''(''||mtp.organization_code||'')'' OPI_ATTRIBUTE1,
jobstatus.value OPI_ATTRIBUTE2,
fact.opi_date1 OPI_DATE1,
''pFunctionName='||l_job_info_drill ||'&jobId=''||fact.job_id||
''&orgId=''||fact.organization_id ||
''&jobName=''|| mtp.organization_code ||
''&repId=''|| fact.job_name ||
''&jobType=''|| fact.job_type OPI_ATTRIBUTE4,
fact.opi_measure1 OPI_MEASURE1,
fact.opi_measure2 OPI_MEASURE2,
fact.opi_measure3 OPI_MEASURE3,
fact.opi_measure4 OPI_MEASURE4,
fact.opi_measure5 OPI_MEASURE5,
fact.opi_measure6 OPI_MEASURE6,
fact.opi_measure7 OPI_MEASURE7,
fact.opi_measure8 OPI_MEASURE8,
fact.opi_measure9 OPI_MEASURE9,
fact.opi_measure10 OPI_MEASURE10,
fact.opi_measure11 OPI_MEASURE11,
fact.opi_measure12 OPI_MEASURE12,
fact.opi_measure13 OPI_MEASURE13
FROM
(SELECT
(rank() over
(&ORDER_BY_CLAUSE nulls last,organization_id,job_name)) - 1 rnk,
organization_id,
job_status_code,
job_name,
job_type,
job_id,
opi_date1,
opi_measure1,
opi_measure2,
opi_measure3,
opi_measure4,
opi_measure5,
opi_measure6,
opi_measure7,
opi_measure8,
opi_measure9,
opi_measure10,
opi_measure11,
(opi_measure8-opi_measure10) opi_measure12,
(opi_measure8-opi_measure10)/decode(opi_measure10,0,null,opi_measure10)*100 opi_measure13
FROM
(select
organization_id,
job_status_code,
job_name,
job_type,
job_id,
opi_date1,
opi_measure1,
decode('''|| l_currency_code || ''', ''B'',opi_measure2_b,''G'',opi_measure2_g,''SG'',opi_measure2_sg) opi_measure2,
opi_measure3,
decode('''|| l_currency_code || ''', ''B'',opi_measure4_b,''G'',opi_measure4_g,''SG'',opi_measure4_sg) opi_measure4,
opi_measure5,
decode('''|| l_currency_code || ''', ''B'',opi_measure6_b,''G'',opi_measure6_g,''SG'',opi_measure6_sg) opi_measure6,
decode('''|| l_currency_code || ''', ''B'',opi_measure7_b,''G'',opi_measure7_g,''SG'',opi_measure7_sg) opi_measure7,
decode('''|| l_currency_code || ''', ''B'',opi_measure8_b,''G'',opi_measure8_g,''SG'',opi_measure8_sg) opi_measure8,
opi_measure9,
decode('''|| l_currency_code || ''', ''B'',opi_measure10_b,''G'',opi_measure10_g,''SG'',opi_measure10_sg) opi_measure10,
opi_measure11
from
(select
job.organization_id organization_id,
job.job_status_code job_status_code,
job.job_name job_name,
job.job_type job_type,
job.job_id job_id,
job.completion_date opi_date1,
job.actual_qty_completed opi_measure1,
sum(act.actual_val_g) opi_measure2_g,
sum(act.actual_val_b) opi_measure2_b,
sum(act.actual_val_sg) opi_measure2_sg,
sum(act.actual_qty) opi_measure3,
sum(std.std_usage_val_g) opi_measure4_g,
sum(std.std_usage_val_b) opi_measure4_b,
sum(std.std_usage_val_sg) opi_measure4_sg,
sum(std.std_usage_qty) opi_measure5,
sum(act.actual_val_g) - sum(std.std_usage_val_g) opi_measure6_g,
sum(act.actual_val_b) - sum(std.std_usage_val_b) opi_measure6_b,
sum(act.actual_val_sg) - sum(std.std_usage_val_sg) opi_measure6_sg,
(sum(act.actual_val_g) - sum(std.std_usage_val_g))/(decode(sum(std.std_usage_val_g),0,
null,sum(std.std_usage_val_g)))*100 opi_measure7_g,
(sum(act.actual_val_b) - sum(std.std_usage_val_b))/(decode(sum(std.std_usage_val_b),0,
null,sum(std.std_usage_val_b)))*100 opi_measure7_b,
(sum(act.actual_val_sg) - sum(std.std_usage_val_sg))/(decode(sum(std.std_usage_val_sg),0,
null,sum(std.std_usage_val_sg)))*100 opi_measure7_sg,
sum(sum(act.actual_val_sg)) over() opi_measure8_sg,
sum(sum(act.actual_val_b)) over() opi_measure8_b,
sum(sum(act.actual_val_g)) over() opi_measure8_g,
sum(sum(act.actual_qty)) over() opi_measure9,
sum(sum(std.std_usage_val_g)) over() opi_measure10_g,
sum(sum(std.std_usage_val_b)) over() opi_measure10_b,
sum(sum(std.std_usage_val_sg)) over() opi_measure10_sg,
sum(sum(std.std_usage_qty)) over() opi_measure11
from
opi_dbi_res_std_f std,
opi_dbi_res_actual_f act,
opi_dbi_jobs_f job,
eni_resource_v resview
where
job.completion_date between &BIS_CURRENT_EFFECTIVE_START_DATE and
&BIS_CURRENT_ASOF_DATE and
job.job_id = act.job_id and
job.source = act.source and
job.organization_id = act.organization_id and
job.assembly_item_id = act.assembly_item_id and
job.job_type = act.job_type and
job.job_id = std.job_id and
job.source = std.source and
job.organization_id = std.organization_id and
job.assembly_item_id = std.assembly_item_id and
job.job_type = std.job_type and
std.resource_id = act.resource_id and
job.organization_id = resview.organization_id and
job.status IN (''Cancelled'', ''Complete - No Charges'',
''Closed'') and
resview.resource_id = act.resource_id and
job.job_type <> 5'
|| l_resource_where
|| l_resource_grp_where
|| l_resource_dept_where
|| l_jobstatus_where
|| l_org_where
|| '
group by
job.organization_id,
job.job_status_code,
job.completion_date,
job.actual_qty_completed,
job.job_name,
job.job_type,
job.job_id,
job.assembly_item_id
)))fact,
fii_time_day time,
mtl_parameters mtp,
opi_mfg_wo_status_lvl_v jobstatus
where
jobstatus.id = fact.job_status_code and
mtp.organization_id = fact.organization_id and
time.report_date = fact.opi_date1 and
(rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE nulls last';