[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_measure3 OPI_MEASURE3,
fact.opi_measure5 OPI_MEASURE5,
fact.opi_measure6 OPI_MEASURE6,
fact.opi_measure8 OPI_MEASURE8,
fact.opi_measure9 OPI_MEASURE9,
fact.opi_measure10 OPI_MEASURE10
from
(select
(rank() over
(&ORDER_BY_CLAUSE nulls last,organization_id,job_name)) - 1 rnk,
organization_id,
job_status_code,
job_name,
job_id,
job_type,
opi_date1,
opi_measure1,
opi_measure3,
opi_measure5,
opi_measure6,
opi_measure8,
opi_measure9,
opi_measure10
from
(select
organization_id,
job_status_code,
job_name,
job_id,
job_type,
opi_date1,
opi_measure1,
opi_measure3,
opi_measure5,
opi_measure6,
opi_measure8,
opi_measure9,
opi_measure8/opi_measure9*100 opi_measure10
from
(select
job.organization_id organization_id,
job.job_status_code job_status_code,
job.job_name job_name,
job.job_id job_id,
job.job_type job_type,
job.completion_date opi_date1,
job.actual_qty_completed opi_measure1,
sum(std.std_usage_qty) opi_measure3,
sum(act.actual_qty) opi_measure5,
sum(std.std_usage_qty)*100/decode(sum(act.actual_qty),0,null,sum(act.actual_qty)) opi_measure6,
sum(sum(std.std_usage_qty)) over() opi_measure8,
sum(sum(act.actual_qty)) over() opi_measure9
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 '
|| 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.job_name,
job.job_id,
job.job_type,
job.completion_date,
job.actual_qty_completed,
job.assembly_item_id
)))fact,
fii_time_day time,
mtl_parameters mtp,
opi_mfg_wo_status_lvl_v jobstatus
where
fact.job_status_code = jobstatus.id 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
';