[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
INTO g_actual_recs_to_process
FROM eni_dbi_pco_worker_assignments;
SELECT UNIQUE -- Collects all modified common/non-common bills whose orgs are in org temp table
bbom.assembly_item_id AS assembly_item_id,
bbom.organization_id AS organization_id
FROM
mlog$_bom_components_b mbic,
bom_structures_b bbom
WHERE
mbic.bill_sequence_id = bbom.bill_sequence_id and
bbom.alternate_bom_designator IS NULL and
bbom.bill_sequence_id = bbom.common_bill_sequence_id and
exists (select 1 from bom_structures_b bbom_common
where bbom_common.common_bill_sequence_id = bbom.bill_sequence_id
and bbom_common.organization_id IN
(select * from eni_dbi_part_count_org_temp))
and mbic.snaptime$$ > NVL(last_collected_date,mbic.snaptime$$)
UNION -- Collects all deleted/modified bills whose orgs are in the org temp table
SELECT UNIQUE
bbom.assembly_item_id AS assembly_item_id,
bbom.organization_id AS organization_id
FROM
mlog$_bom_structures_b bbom -- Bug # 3394284
WHERE
bbom.dmltype$$ <> 'I' and
bbom.alternate_bom_designator IS NULL and
bbom.bill_sequence_id = bbom.common_bill_sequence_id and
bbom.organization_id IN (select * from eni_dbi_part_count_org_temp)
UNION -- This query collects all the common bills for the organizations in the temp table
SELECT UNIQUE -- Collects any newly commoned bills
bbom.assembly_item_id AS assembly_item_id,
bbom.organization_id AS organization_id
FROM
mlog$_bom_structures_b mlog_bbom, bom_structures_b bbom
WHERE
mlog_bbom.alternate_bom_designator IS NULL and
mlog_bbom.bill_sequence_id <> mlog_bbom.common_bill_sequence_id and
mlog_bbom.organization_id IN (select * from eni_dbi_part_count_org_temp) and
mlog_bbom.common_bill_sequence_id = bbom.bill_sequence_id;
select sysdate into g_collection_date from dual;
DELETE FROM bom_implosion_temp bit
WHERE EXISTS (SELECT NULL
FROM eni_dbi_pco_worker_assignments p
WHERE p.assembly_item_id = bit.parent_item_id
and p.organization_id = bit.organization_id);
INSERT INTO eni_dbi_pco_worker_assignments
(
assembly_item_id,
organization_id,
pto_flag,
bom_type,
worker_id,
incr_status
)
(SELECT unique
bit.parent_item_id AS assembly_item_id,
bit.organization_id AS organization_id,
(select msi.pick_components_flag from mtl_system_items_b msi
where msi.inventory_item_id = bit.parent_item_id and
msi.organization_id = bit.organization_id) AS pick_components_flag,
(select msi.bom_item_type from mtl_system_items_b msi
where msi.inventory_item_id = bit.parent_item_id and
msi.organization_id = bit.organization_id) AS bom_item_type,
NULL as worker_id,
0 as incr_status
FROM
BOM_IMPLOSION_TEMP bit, BOM_BILL_OF_MATERIALS bbom
WHERE
bit.parent_item_id = bbom.assembly_item_id
AND bit.organization_id = bbom.organization_id
AND bbom.bill_sequence_id = bbom.common_bill_sequence_id
AND bbom.alternate_bom_designator IS NULL);
DELETE FROM bom_implosion_temp;
-- Insert statement to insert into the worker assigments table when
-- no organization is selected
INSERT INTO eni_dbi_pco_worker_assignments
(
assembly_item_id,
organization_id,
pto_flag,
bom_type,
worker_id,
incr_status
)
(select
bbom.assembly_item_id AS inventory_item_id,
bbom.organization_id,
msi.pick_components_flag AS pto_flag,
msi.bom_item_type AS bom_type,
NULL worker_id,
0 AS incr_status
from bom_structures_b bbom,mtl_system_items_b msi -- Bug # 3394284
where bill_sequence_id = common_bill_sequence_id and
bbom.alternate_bom_designator IS NULL and
bbom.organization_id = msi.organization_id and
bbom.assembly_item_id = msi.inventory_item_id and
msi.bom_item_type <> 2);
-- Statement to insert into the worker assignments table when
-- an organization is selected.
-- All the common / non-common bills in the organization selected are inserted.
INSERT INTO eni_dbi_pco_worker_assignments
(
assembly_item_id,
organization_id,
pto_flag,
bom_type,
worker_id,
incr_status
)
(select UNIQUE
NVL(bbom.common_assembly_item_id,assembly_item_id) AS inventory_item_id,
NVL(bbom.common_organization_id,bbom.organization_id) AS organization_id,
msi.pick_components_flag AS pto_flag,
msi.bom_item_type AS bom_type,
NULL worker_id,
0 AS incr_status
from bom_structures_b bbom,mtl_system_items_b msi
where
bbom.alternate_bom_designator IS NULL and
bbom.organization_id = g_organization_id and
NVL(bbom.common_organization_id,bbom.organization_id) = msi.organization_id and
NVL(bbom.common_assembly_item_id,bbom.assembly_item_id) = msi.inventory_item_id and
msi.bom_item_type <> 2 );
DELETE FROM eni_dbi_part_count_f
WHERE (assembly_item_id,organization_id) IN
(SELECT assembly_item_id, organization_id
FROM eni_dbi_pco_worker_assignments);
SELECT sysdate into g_collection_date from dual;
SELECT COUNT(*)
INTO l_error_occured
FROM ENI_DBI_PCO_WORKER_ASSIGNMENTS
WHERE incr_status = -1;
DELETE FROM ENI_DBI_PART_COUNT_F
WHERE
(assembly_item_id,organization_id) IN
(SELECT assembly_item_id, organization_id FROM eni_dbi_pco_worker_assignments);
INSERT INTO ENI_DBI_PART_COUNT_F(
assembly_item_id,
organization_id,
item_id_fk,
component_item_id,
effectivity_date,
disable_date,
bom_level,
ITEM_CATALOG_GROUP_ID
)
(SELECT assembly_item_id,
organization_id,
'-1',
component_item_id,
effectivity_date,
disable_date,
bom_level,
-1
FROM ENI_DBI_PART_COUNT_INCR_TEMP);
INSERT INTO eni_dbi_part_count_org_temp
(organization_id)
(SELECT organization_id from hr_all_organization_units);
INSERT INTO eni_dbi_part_count_org_temp
(organization_id) VALUES (p_organization_id);
SELECT NVL((SELECT 1 from eni_dbi_part_count_org_temp
where organization_id = p_organization_id),-1)
INTO l_org_exists
FROM DUAL;
INSERT INTO eni_dbi_part_count_org_temp
(organization_id) VALUES (p_organization_id);
SELECT count(*)
INTO l_num_recs_to_process
FROM eni_dbi_pco_worker_assignments;
SELECT count(*)
INTO l_error
FROM eni_Dbi_pco_worker_assignments
WHERE incr_status = -1;
UPDATE eni_dbi_pco_worker_assignments
SET worker_id = p_worker_id , incr_status = 1
WHERE worker_id IS NULL AND incr_status = 0
AND rownum <= p_batch_size;
UPDATE eni_dbi_pco_worker_assignments
SET incr_status = 0
WHERE worker_id = p_worker_id AND incr_status = 1;
SELECT
assembly_item_id,
organization_id,
pto_flag,
bom_type
FROM
eni_dbi_pco_worker_assignments worker_bills
WHERE
worker_bills.worker_id = p_worker_id and
worker_bills.incr_status = 1 ;
SELECT bbom.bill_sequence_id INTO l_bill_sequence_id
FROM BOM_STRUCTURES_B bbom -- Bug # 3394284
WHERE bbom.assembly_item_id = l_inventory_item_id
AND bbom.organization_id = l_org_id
AND bbom.alternate_bom_designator IS NULL
AND bbom.bill_sequence_id = bbom.common_bill_sequence_id;
UPDATE eni_dbi_pco_worker_assignments
SET incr_status = -1
WHERE
worker_id = p_worker_id AND
assembly_item_id = l_inventory_item_id AND
organization_id = l_org_id;
INSERT /*+ APPEND */ INTO eni_dbi_part_count_f
(
assembly_item_id,
organization_id,
item_id_fk,
component_item_id,
effectivity_date,
disable_date,
bom_level,
ITEM_CATALOG_GROUP_ID
)
SELECT
b1.top_item_id,
b1.organization_id,
'-1',
b1.component_item_id AS component_item_id,
trunc(b1.effectivity_date),
trunc(NVL(b1.disable_date,to_date('1-1-2085','dd-mm-yyyy'))),
b1.plan_level,
-1
FROM
bom_explosion_temp b1,mtl_system_items_b i
WHERE
b1.component_sequence_id IS NOT NULL and
b1.bom_item_type <> 2 and
-- Filtering out the Option classes items which
-- donot have Bills attached to them.
-- Bug 3968305: use wip_supply_type at component-level, not item-level
b1.wip_supply_type <> 6 and
-- Filtering out the phantom items.
b1.component_item_id = i.inventory_item_id and
b1.organization_id = i.organization_id and
not exists (
select 1 from bom_bill_of_materials bbom
where bbom.assembly_item_id = b1.component_item_id and
bbom.organization_id = b1.organization_id) and
(NOT(((1,'PTO') IN (select i3.bom_item_type,i3.item_type
from mtl_system_items_b i3
where i3.inventory_item_id = b1.top_item_id and
i3.organization_id = b1.organization_id)) and
((2,'POC') IN (select i2.bom_item_type,i2.item_type
from mtl_system_items_b i2
where i2.inventory_item_id = b1.assembly_item_id and
i2.organization_id = b1.organization_id))));
INSERT /*+ APPEND */ INTO eni_dbi_part_count_incr_temp
(
assembly_item_id,
organization_id,
component_item_id,
effectivity_date,
disable_date,
bom_level
)
SELECT
b1.top_item_id,
b1.organization_id,
b1.component_item_id AS component_item_id,
trunc(b1.effectivity_date),
trunc(NVL(b1.disable_date,to_date('1-1-2085','dd-mm-yyyy'))),
b1.plan_level
FROM
bom_explosion_temp b1,mtl_system_items_b i
WHERE
b1.component_sequence_id IS NOT NULL and
b1.bom_item_type <> 2 and
-- Filtering out the Option classes items which
-- donot have Bills attached to them.
-- Bug 3968305: use wip_supply_type at component-level, not item-level
b1.wip_supply_type <> 6 and
-- Filtering out the phantom items.
b1.component_item_id = i.inventory_item_id and
b1.organization_id = i.organization_id and
not exists (
select 1 from bom_bill_of_materials bbom
where bbom.assembly_item_id = b1.component_item_id and
bbom.organization_id = b1.organization_id) and
(NOT(((1,'PTO') IN (select i3.bom_item_type,i3.item_type
from mtl_system_items_b i3
where i3.inventory_item_id = b1.top_item_id and
i3.organization_id = b1.organization_id)) and
((2,'POC') IN (select i2.bom_item_type,i2.item_type
from mtl_system_items_b i2
where i2.inventory_item_id = b1.assembly_item_id and
i2.organization_id = b1.organization_id))));
FND_FILE.PUT_LINE(FND_FILE.LOG,'The following error has occured while inserting into the Part Count fact table');