[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_item_org := 'Selected';
l_item_cat := 'Selected';
l_jobstatus := 'Selected';
SELECT trim(both '''' from l_org)
INTO l_org
FROM dual;
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,
jobstatus.value OPI_ATTRIBUTE2,
itemorg.value OPI_ATTRIBUTE3,
itemorg.description OPI_ATTRIBUTE4,
uom.unit_of_measure OPI_ATTRIBUTE5,
''pFunctionName='||l_job_info_drill ||'&jobId=''||f.job_id||
''&orgId=''||f.organization_id ||
''&jobName=''|| mtp.organization_code ||
''&repId=''|| replace(f.job_name,'' '','''') ||
''&jobType=''|| f.job_type ||
''&cloInd=''|| decode(''' || l_flag || ''', ''Y'',
decode(f.job_status_code, 12, 1, 2), 1) OPI_ATTRIBUTE6,
f.opi_measure1 OPI_MEASURE1,
f.opi_measure2 OPI_MEASURE2,
f.opi_measure3 OPI_MEASURE3
FROM
(SELECT
(rank() over
(&ORDER_BY_CLAUSE nulls last, job_name, job_id, job_type, inventory_item_id, organization_id)) -1 rnk,
job_name,
job_id,
job_type,
inventory_item_id,
organization_id,
job_status_code,
uom_code,
opi_measure1 opi_measure1,
opi_measure2 opi_measure2,
sum(opi_measure2)over() opi_measure3
FROM
(SELECT job.job_name,
job.job_id,
job.job_type,
job.assembly_item_id inventory_item_id,
job.organization_id,
job.job_status_code,
job.uom_code,
sum(wip_f.completion_quantity) opi_measure1,
sum(decode(''' || l_currency_code || ''',
''B'', wip_f.completion_value_b,
''G'', wip_f.completion_value_g,
''SG'', wip_f.completion_value_sg))
opi_measure2
FROM opi_dbi_jobs_f job,
opi_dbi_wip_comp_f wip_f
WHERE job.line_type = 1
AND trunc(wip_f.transaction_date) >= &BIS_CURRENT_EFFECTIVE_START_DATE
AND trunc(wip_f.transaction_date) <= &BIS_CURRENT_ASOF_DATE
AND job.job_id = wip_f.job_id
AND wip_f.transaction_date >= '''||g_gsd||'''
'|| l_item_org_where || l_jobstatus_where||'
GROUP BY
job.job_name,
job.job_id,
job.job_type,
job.organization_id,
job.job_status_code,
job.uom_code,
job.assembly_item_id)) f,
mtl_parameters mtp,
opi_mfg_wo_status_lvl_v jobstatus,
eni_item_org_v itemorg,
mtl_units_of_measure_vl uom
WHERE
jobstatus.id = f.job_status_code
AND mtp.organization_id = f.organization_id
AND uom.uom_code = f.uom_code
AND itemorg.inventory_item_id = f.inventory_item_id
AND itemorg.organization_id = f.organization_id'
|| l_item_cat_where || '
AND (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE nulls last';