[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_item_org := 'Selected';
l_item_cat := '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
f.job_name ||''('' || mtp.organization_code || '')'' OPI_ATTRIBUTE1
,job_status.value OPI_ATTRIBUTE2
,itemorg.value OPI_ATTRIBUTE3
,itemorg.description OPI_ATTRIBUTE4
,v2.unit_of_measure OPI_ATTRIBUTE5
,''pFunctionName='||l_job_info_drill ||'&jobId=''||f.job_id||
''&orgId=''||f.org_id ||
''&jobName=''|| mtp.organization_code ||
''&repId=''|| f.job_name ||
''&jobType=''|| f.job_type
OPI_ATTRIBUTE6
,f.opi_measure1 OPI_MEASURE1
,f.opi_measure2 OPI_MEASURE2
,f.opi_measure3 OPI_MEASURE3
,f.opi_measure4 OPI_MEASURE4
,f.opi_measure5 OPI_MEASURE5
,f.opi_measure6 OPI_MEASURE6
,f.opi_measure7 OPI_MEASURE7
,f.opi_measure8 OPI_MEASURE8
,f.opi_measure9 OPI_MEASURE9
,f.opi_measure10 OPI_MEASURE10
from
(select
(rank() over (&ORDER_BY_CLAUSE nulls last,job_id,job_name,org_id,assembly_item_id)) - 1 rnk
,job_id
,job_type
,job_name
,org_id
,assembly_item_id
,job_status_code
,opi_attribute1
,opi_attribute2
,opi_measure1
,opi_measure2
,opi_measure3
,opi_measure4
,opi_measure5
,opi_measure6
,opi_measure7
,opi_measure8
,opi_measure9
,opi_measure10
from
(select
org_id
,assembly_item_id
,job_id
,job_type
,job_name
,job_name opi_attribute1
,status opi_attribute2
,job_status_code
,start_qty opi_measure1
,actual_qty opi_measure2
,standard_value opi_measure3
,actual_value opi_measure4
,(actual_value - standard_value) opi_measure5
,(actual_value - standard_value)*100/(decode(standard_value,0,null,standard_value)) opi_measure6
,standard_value_tot opi_measure7
,actual_value_tot opi_measure8
,(actual_value_tot - standard_value_tot) opi_measure9
,(actual_value_tot - standard_value_tot)*100/(decode(standard_value_tot,0,null,standard_value_tot)) opi_measure10
from
(select
jobs.job_name job_name,
jobs.job_id job_id,
jobs.job_type,
jobs.organization_id org_id,
jobs.assembly_item_id assembly_item_id,
jobs.job_status_code job_status_code,
jobs.status status,
jobs.start_quantity start_qty,
fact.actual_prd_qty actual_qty,
decode(''' || l_currency_code || ''', ''B'',fact.standard_value_b,
''G'',fact.standard_value_g,
''SG'',fact.standard_value_sg) standard_value,
decode(''' || l_currency_code || ''', ''B'',fact.actual_value_b,
''G'',fact.actual_value_g,
''SG'',fact.actual_value_sg) actual_value,
sum(decode(''' || l_currency_code || ''', ''B'',fact.standard_value_b,
''G'',fact.standard_value_g,
''SG'',fact.standard_value_sg)) over()
standard_value_tot,
sum(decode(''' || l_currency_code || ''', ''B'',fact.actual_value_b,
''G'',fact.actual_value_g,
''SG'',fact.actual_value_sg)) over()
actual_value_tot
from
OPI_DBI_CURR_UNREC_VAR_F fact,
OPI_DBI_JOBS_F jobs
where
fact.job_id = jobs.job_id
and fact.job_type = jobs.job_type
and fact.inventory_item_id = jobs.assembly_item_id
and fact.organization_id = jobs.organization_id '
|| l_item_org_where
|| l_jobstatus_where
|| l_org_where
|| '
group by
jobs.job_name,
jobs.job_id,
jobs.job_type,
jobs.organization_id,
jobs.assembly_item_id,
jobs.job_status_code,
jobs.status,
jobs.start_quantity,
fact.actual_prd_qty,
fact.standard_value_b,
fact.standard_value_g,
fact.standard_value_sg,
fact.actual_value_b,
fact.actual_value_g,
fact.actual_value_sg
)))f
,eni_item_org_v itemorg
,mtl_units_of_measure_vl v2
,mtl_parameters mtp
,OPI_MFG_WO_STATUS_LVL_V job_status
where
mtp.organization_id = f.org_id
and itemorg.id = f.assembly_item_id||''-''|| f.org_id
and itemorg.organization_id = f.org_id
and itemorg.primary_uom_code = V2.uom_code
and f.job_status_code = job_status.id
and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1) ' ||
l_item_cat_where || '
&ORDER_BY_CLAUSE nulls last';