[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
NVL(common_assembly_item_id,assembly_item_id),
NVL(common_organization_id ,organization_id)
INTO
l_item,l_org
from
bom_bill_of_materials
where
assembly_item_id = l_item_temp and
organization_id = l_org_temp and
alternate_bom_designator IS NULL;
SELECT count(*)
INTO l_org_exists
FROM eni_dbi_part_count_org_temp
WHERE organization_id = l_org_temp;
x_custom_sql :=' SELECT NULL AS VIEWBY,
NULL AS ENI_MEASURE1,
NULL AS ENI_MEASURE2,
NULL AS ENI_MEASURE4,
NULL AS ENI_MEASURE5,
NULL AS ENI_ATTRIBUTE11,
NULL AS ENI_ATTRIBUTE2 FROM DUAL';
'SELECT
name AS VIEWBY,
current_pco AS ENI_MEASURE1,
prior_pco AS ENI_MEASURE2,
current_bom_levels AS ENI_MEASURE4,
prior_bom_levels AS ENI_MEASURE5,
current_pco AS ENI_ATTRIBUTE11,
current_bom_levels AS ENI_ATTRIBUTE2
FROM ((select
sum(case when (t.c_end_date BETWEEN
pco.effectivity_date AND pco.disable_date)
then part_count
else NULL end) as current_pco,
sum(case when (t.p_end_date BETWEEN
pco.effectivity_date AND pco.disable_date)
then part_count
else NULL end) as prior_pco,
max(case when (t.c_end_date BETWEEN
pco.effectivity_date AND pco.disable_date)
then max_bom_level
else NULL end) as current_bom_levels,
max(case when (t.p_end_date BETWEEN
pco.effectivity_date AND pco.disable_date)
then max_bom_level
else NULL end) as prior_bom_levels,
t.name,
t.start_date
from
eni_dbi_part_count_mv pco,
' || l_from_clause || '
where
'||l_where_clause|| '
'||l_item_org_clause||'
group by
t.name,t.start_date)
UNION ALL
(select NULL AS current_pco,
NULL AS prior_pco,
NULL AS current_bom_levels,
NULL AS prior_bom_levels,
t.name,
t.start_date
from
' || l_from_clause || '
where
(NOT EXISTS(select * from eni_dbi_part_count_mv where
assembly_item_id = :LITEM
and organization_id = :LORG)) and ' --Bug 5083920
|| l_where_clause ||'))
order by
'||l_order_by;
'(select
eiv.value as VIEWBY,
sum(case when ('||'&'||'BIS_CURRENT_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
then part_count
else NULL end) as ENI_MEASURE1,
sum(case when ('||'&'||'BIS_PREVIOUS_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
then part_count
else NULL end) as ENI_MEASURE2,
max(case when ('||'&'||'BIS_CURRENT_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
then max_bom_level
else NULL end) as ENI_MEASURE4,
max(case when ('||'&'||'BIS_PREVIOUS_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
then max_bom_level
else NULL end) as ENI_MEASURE5,
sum(case when ('||'&'||'BIS_CURRENT_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
then part_count
else NULL end) as ENI_ATTRIBUTE11,
max(case when ('||'&'||'BIS_CURRENT_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'',''DD-MM-YYYY'')))
then max_bom_level
else NULL end) as ENI_ATTRIBUTE2
from
eni_dbi_part_count_mv pco, ENI_ITEM_ORG_V eiv
where
(' || '&'|| 'BIS_CURRENT_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'', ''DD-MM-YYYY''))
OR
' || '&'|| 'BIS_PREVIOUS_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'', ''DD-MM-YYYY'')))
'||l_item_org_clause||'
group by
eiv.value
UNION ALL
select eiv.value as VIEWBY,
NULL as ENI_MEASURE1,
NULL AS ENI_MEASURE2,
NULL AS ENI_MEASURE4,
NULL AS ENI_MEASURE5,
NULL AS ENI_ATTRIBUTE11,
NULL AS ENI_ATTRIBUTE2
from
eni_item_org_v eiv
where
eiv.inventory_item_id = '||l_item_1||' and
eiv.organization_id = '||l_org_1||' and
( NOT EXISTS (select * from eni_dbi_part_count_mv pco
where
item_id = '|| l_item_1 || ' and
organization_id = '|| l_org_1 || ' and
(' || '&'|| 'BIS_CURRENT_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'', ''DD-MM-YYYY''))
OR
' || '&'|| 'BIS_PREVIOUS_ASOF_DATE BETWEEN
trunc(pco.effectivity_date) AND
NVL(trunc(pco.disable_date),to_date(''01-01-3000'', ''DD-MM-YYYY''))))))
order by '
||l_order_by;