[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
action_delete CONSTANT NUMBER(1) := 3;
ecg_action_delete CONSTANT NUMBER(1) := 3;
SELECT change_id
FROM ENG_ENGINEERING_CHANGES
WHERE change_mgmt_type_code = 'CHANGE_ORDER'
AND organization_id = org_id
AND change_notice = eco;
SELECT COUNT(*)
FROM bom_lists
WHERE sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Delete rev effective structures from list');
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM bom_bill_of_materials b
WHERE b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_org_id
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
AND b.effectivity_control =4);
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete st in Match attributes='||
to_char(l_bom_lists_count));
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM
bom_inventory_comps_interface ci,
eng_revised_items_interface ri
WHERE
ci.acd_type IN (action_replace, action_delete)
AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_eco
AND ri.organization_id = p_org_id
/* For bug 8550652
AND NOT EXISTS (
SELECT NULL
FROM eng_revised_items_interface rii,
bom_inventory_comps_interface cii
WHERE rii.change_notice = p_eco
AND rii.organization_id = p_org_id
AND rii.revised_item_sequence_id =
ri.revised_item_sequence_id
AND cii.revised_item_sequence_id =
ri.revised_item_sequence_id
AND cii.acd_type IN (action_add))
For bug 8550652 */
AND NOT EXISTS (
SELECT NULL
FROM bom_structures_b b,
bom_components_b c
WHERE
b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_org_id
AND ( (l.alternate_designator IS NULL AND b.alternate_bom_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
AND (c.item_num = ci.item_num
OR ci.item_num IS NULL)
AND (Nvl(c.basis_type,4) = Decode(ci.basis_type, FND_API.G_MISS_NUM,4,ci.basis_type) -- 5214239
OR ci.basis_type is NULL)
AND (c.component_quantity = ci.component_quantity
OR ci.component_quantity IS NULL)
AND (c.component_yield_factor = ci.component_yield_factor
OR ci.component_yield_factor IS NULL)
AND (c.planning_factor = ci.planning_factor
OR ci.planning_factor IS NULL)
AND (c.quantity_related = ci.quantity_related
OR ci.quantity_related IS NULL)
AND (c.so_basis = ci.so_basis
OR ci.so_basis IS NULL)
AND (c.optional = ci.optional
OR ci.optional IS NULL)
AND (c.mutually_exclusive_options =
ci.mutually_exclusive_options
OR ci.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = ci.include_in_cost_rollup
OR ci.include_in_cost_rollup IS NULL)
AND (c.check_atp = ci.check_atp
OR ci.check_atp IS NULL)
AND (c.shipping_allowed = ci.shipping_allowed
OR ci.shipping_allowed IS NULL)
AND (c.required_to_ship = ci.required_to_ship
OR ci.required_to_ship IS NULL)
AND (c.required_for_revenue = ci.required_for_revenue
OR ci.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = ci.include_on_ship_docs
OR ci.include_on_ship_docs IS NULL)
AND (c.low_quantity = ci.low_quantity
OR ci.low_quantity IS NULL)
AND (c.high_quantity = ci.high_quantity
OR ci.high_quantity IS NULL)
AND (c.wip_supply_type = ci.wip_supply_type
OR ci.wip_supply_type IS NULL)
AND (c.supply_subinventory = ci.supply_subinventory
OR ci.supply_subinventory IS NULL)
AND (c.supply_locator_id = ci.supply_locator_id
OR ci.supply_locator_id IS NULL)
AND (c.component_remarks = ci.component_remarks
OR ci.component_remarks IS NULL)
AND (c.attribute_category = ci.attribute_category
OR ci.attribute_category IS NULL)
AND (c.attribute1 = ci.attribute1 OR ci.attribute1 IS NULL)
AND (c.attribute2 = ci.attribute2 OR ci.attribute2 IS NULL)
AND (c.attribute3 = ci.attribute3 OR ci.attribute3 IS NULL)
AND (c.attribute4 = ci.attribute4 OR ci.attribute4 IS NULL)
AND (c.attribute5 = ci.attribute5 OR ci.attribute5 IS NULL)
AND (c.attribute6 = ci.attribute6 OR ci.attribute6 IS NULL)
AND (c.attribute7 = ci.attribute7 OR ci.attribute7 IS NULL)
AND (c.attribute8 = ci.attribute8 OR ci.attribute8 IS NULL)
AND (c.attribute9 = ci.attribute9 OR ci.attribute9 IS NULL)
AND (c.attribute10 = ci.attribute10 OR ci.attribute10 IS NULL)
AND (c.attribute11 = ci.attribute11 OR ci.attribute11 IS NULL)
AND (c.attribute12 = ci.attribute12 OR ci.attribute12 IS NULL)
AND (c.attribute13 = ci.attribute13 OR ci.attribute13 IS NULL)
AND (c.attribute14 = ci.attribute14 OR ci.attribute14 IS NULL)
AND (c.attribute15 = ci.attribute15 OR ci.attribute15 IS NULL)
AND c.operation_seq_num =
NVL(ci.operation_seq_num, c.operation_seq_num)
AND c.component_item_id = ci.component_item_id
AND c.bill_sequence_id = b.bill_sequence_id
AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND ( ( b.effectivity_control IN (G_STRUCT_DATE_EFF, G_STRUCT_REV_EFF) )
OR
(
( b.effectivity_control IN (G_STRUCT_UNIT_EFF, G_STRUCT_SER_EFF) )
AND NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
ri.from_end_item_unit_number
AND c.from_end_item_unit_number <= ri.from_end_item_unit_number )
)
)
);
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete st in match_attribute='||
to_char(l_bom_lists_count));
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete stmt Check_Combination ='||
to_char(l_bom_lists_count));
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items_b ri,
mtl_system_items_b ci,
bom_inventory_comps_interface c,
eng_revised_items_interface r
WHERE ((ci.bom_item_type = planning_type AND
ri.bom_item_type <> planning_type)
OR
(ci.bom_item_type IN (model_type, option_class_type) AND
ri.bom_item_type = standard_type AND
ri.base_item_id IS NULL)
OR
(ci.replenish_to_order_flag = 'Y' AND
ci.bom_item_type = option_class_type AND
ri.pick_components_flag = 'Y')
OR
(ci.replenish_to_order_flag = 'Y' AND
ci.bom_item_type = standard_type AND
ri.pick_components_flag = 'Y' AND
ri.bom_item_type = standard_type)
OR
(ci.pick_components_flag = 'Y' AND
ri.replenish_to_order_flag = 'Y')
/* commented for bug 3548357 and 3508992
OR
(ri.bom_item_type <> planning_type AND
ri.atp_components_flag = 'N'
AND (ri.replenish_to_order_flag = 'Y' OR
ri.pick_components_flag = 'Y' OR
ri.wip_supply_type = phantom)
AND (ci.atp_flag = 'Y'
OR ci.atp_components_flag = 'Y'))*/
)
AND ri.inventory_item_id = l.assembly_item_id
AND ri.organization_id = p_organization
AND ci.inventory_item_id = c.component_item_id
AND ci.organization_id = p_organization
AND c.acd_type IN (action_add, action_change)
AND c.revised_item_sequence_id = r.revised_item_sequence_id
AND r.change_notice = p_eco
AND r.organization_id = p_organization);
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 1 Check_Combination ='||
to_char(l_bom_lists_count));
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items_b i,
mtl_system_items_b ci,
bom_inventory_comps_interface c,
eng_revised_items_interface r
WHERE ((c.optional = yes AND i.bom_item_type
NOT IN (model_type, option_class_type))
OR
(c.planning_factor <> default_planning_factor
AND ((i.bom_item_type IN
(model_type, option_class_type)
AND c.optional = no
AND ci.ato_forecast_control <>
g_consume_and_derive)
OR (i.bom_item_type = standard_type)))
)
AND i.inventory_item_id = l.assembly_item_id
AND i.organization_id = r.organization_id
AND ci.inventory_item_id = c.component_item_id
AND ci.organization_id = r.organization_id
AND c.acd_type = action_add
AND c.revised_item_sequence_id = r.revised_item_sequence_id
AND r.change_notice = p_eco
AND r.organization_id = p_organization);
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 2 in Check_Combination='||
to_char(l_bom_lists_count));
DELETE FROM bom_lists bl
WHERE bl.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items_b ri_itm, -- revised item
mtl_system_items_b ci_itm, -- component item
bom_inventory_components c,
bom_bill_of_materials bom,
bom_inventory_comps_interface o, -- old component
bom_inventory_comps_interface n, -- new component
eng_revised_items_interface ri
WHERE ri_itm.inventory_item_id = bom.assembly_item_id
AND ri_itm.organization_id = bom.organization_id
AND ci_itm.inventory_item_id = c.component_item_id
AND ci_itm.organization_id = bom.organization_id
AND ((NVL(n.optional, c.optional) = yes AND
ri_itm.bom_item_type NOT IN
(model_type, option_class_type))
OR
(NVL(n.planning_factor, c.planning_factor) <>
default_planning_factor
AND ((ri_itm.bom_item_type IN
(model_type, option_class_type)
AND NVL(n.optional, c.optional) = no
AND ci_itm.ato_forecast_control <> g_consume_and_derive)
OR (ri_itm.bom_item_type = standard_type)))
)
AND (c.item_num = o.item_num OR o.item_num IS NULL)
AND (c.component_quantity = o.component_quantity
OR o.component_quantity IS NULL)
AND (c.component_yield_factor = o.component_yield_factor
OR o.component_yield_factor IS NULL)
AND (c.planning_factor = o.planning_factor
OR o.planning_factor IS NULL)
AND (c.quantity_related = o.quantity_related
OR o.quantity_related IS NULL)
AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
AND (c.optional = o.optional
OR o.optional IS NULL)
AND (c.mutually_exclusive_options =
o.mutually_exclusive_options
OR o.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = o.include_in_cost_rollup
OR o.include_in_cost_rollup IS NULL)
AND (c.check_atp = o.check_atp
OR o.check_atp IS NULL)
AND (c.shipping_allowed = o.shipping_allowed
OR o.shipping_allowed IS NULL)
AND (c.required_to_ship = o.required_to_ship
OR o.required_to_ship IS NULL)
AND (c.required_for_revenue = o.required_for_revenue
OR o.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = o.include_on_ship_docs
OR o.include_on_ship_docs IS NULL)
AND (c.low_quantity = o.low_quantity
OR o.low_quantity IS NULL)
AND (c.high_quantity = o.high_quantity
OR o.high_quantity IS NULL)
AND (c.wip_supply_type = o.wip_supply_type
OR o.wip_supply_type IS NULL)
AND (c.supply_subinventory = o.supply_subinventory
OR o.supply_subinventory IS NULL)
AND (c.supply_locator_id = o.supply_locator_id
OR o.supply_locator_id IS NULL)
AND (c.component_remarks = o.component_remarks
OR o.component_remarks IS NULL)
AND (c.attribute_category = o.attribute_category
OR o.attribute_category IS NULL)
AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
AND c.operation_seq_num =
NVL(o.operation_seq_num, c.operation_seq_num)
AND c.component_item_id = o.component_item_id
AND c.bill_sequence_id = bom.bill_sequence_id
AND ( (bom.alternate_bom_designator IS NULL AND bl.alternate_designator IS NULL)
OR (bom.alternate_bom_designator = bl.alternate_designator) )
AND bom.organization_id = ri.organization_id
AND bom.assembly_item_id = bl.assembly_item_id
AND o.component_sequence_id = n.old_component_sequence_id
AND n.acd_type = action_change
AND n.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_eco
AND ri.organization_id = p_organization
AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND ((NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
ri.from_end_item_unit_number
AND c.from_end_item_unit_number <= ri.from_end_item_unit_number
AND ri_itm.effectivity_control = unit_control)
OR
ri_itm.effectivity_control = date_control)
);
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt3 in Check_Combination ='||
to_char(l_bom_lists_count));
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM bom_inventory_comps_interface ci,
eng_revised_items_interface ri
WHERE ci.operation_seq_num NOT IN (
SELECT o.operation_seq_num
FROM bom_operation_sequences o,
bom_operational_routings r
WHERE NVL(TRUNC(o.disable_date), NVL(ri.scheduled_date,
TRUNC(SYSDATE)) + 1)
> NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND r.common_routing_sequence_id =
o.routing_sequence_id
AND (NVL(r.alternate_routing_designator, 'NONE') =
NVL(l.alternate_designator, 'NONE')
OR
(r.alternate_routing_designator IS NULL
AND NOT EXISTS (
SELECT NULL
FROM bom_operational_routings rr
WHERE rr.alternate_routing_designator =
l.alternate_designator
AND rr.assembly_item_id =
l.assembly_item_id
AND rr.organization_id =
ri.organization_id))
)
AND r.organization_id = ri.organization_id
AND r.assembly_item_id = l.assembly_item_id)
AND ci.operation_seq_num <> 1
AND ci.acd_type IN (action_add, action_change)
AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_eco
AND ri.organization_id = p_organization
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM eng_revised_items_interface ri,
mtl_system_items_b ri_itm
WHERE ri.use_up = yes
AND ri.change_notice = p_eco
AND ri.organization_id = p_organization
AND ri.use_up_item_id <> l.assembly_item_id
AND ri_itm.organization_id = ri.organization_id
AND ri_itm.inventory_item_id = l.assembly_item_id
AND NOT EXISTS (
SELECT NULL
FROM bom_inventory_components c,
bom_bill_of_materials b
WHERE c.component_item_id = ri.use_up_item_id
AND b.bill_sequence_id = c.bill_sequence_id
AND b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_organization
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
AND c.implementation_date IS NOT NULL
AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND ((NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
ri.from_end_item_unit_number
AND c.from_end_item_unit_number <= ri.from_end_item_unit_number
AND ri_itm.effectivity_control = unit_control)
OR
ri_itm.effectivity_control = date_control)
)
);
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 4 in Check_Combination ='||
to_char(l_bom_lists_count));
DELETE FROM bom_lists bl
WHERE bl.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM bom_inventory_comps_interface c,
eng_revised_items_interface r
WHERE c.revised_item_sequence_id = r.revised_item_sequence_id
AND r.change_notice = p_change_order
AND r.organization_id = p_organization
AND bl.assembly_item_id = c.component_item_id);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM bom_inventory_components c,
bom_bill_of_materials b,
bom_inventory_comps_interface ci,
eng_revised_items_interface ri
WHERE c.implementation_date IS NOT NULL
AND c.operation_seq_num =
NVL(ci.operation_seq_num, default_operation_seq_num)
AND c.component_item_id = ci.component_item_id
AND c.bill_sequence_id = b.bill_sequence_id
AND ci.acd_type = action_add
AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_change_order
AND ri.organization_id = p_organization
AND b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_organization
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND ( ( b.effectivity_control IN (G_STRUCT_DATE_EFF, G_STRUCT_REV_EFF) )
OR
( b.effectivity_control IN (G_STRUCT_UNIT_EFF, G_STRUCT_SER_EFF)
AND NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
ri.from_end_item_unit_number
AND c.from_end_item_unit_number <= ri.from_end_item_unit_number )
)
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT /*+ ORDERED USE_NL (B C CI CIR RI CO RI_ITM) */ NULL
FROM bom_structures_b b,
bom_components_b c,
bom_inventory_comps_interface ci,
bom_inventory_comps_interface cir,
eng_revised_items_interface ri,
bom_components_b co,
mtl_system_items_b ri_itm
WHERE c.implementation_date IS NOT NULL
AND c.operation_seq_num =
NVL(ci.operation_seq_num, NVL(cir.operation_seq_num,
co.operation_seq_num))
AND c.component_item_id = ci.component_item_id
AND c.bill_sequence_id = b.bill_sequence_id
AND ci.acd_type = action_change
AND ci.old_component_sequence_id = cir.component_sequence_id
AND ci.component_item_id <> cir.component_item_id
AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_change_order
AND ri.organization_id = p_organization
AND co.operation_seq_num = NVL(cir.operation_seq_num,
co.operation_seq_num)
AND co.bill_sequence_id = c.bill_sequence_id
AND co.component_item_id = cir.component_item_id
AND b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_organization
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL) OR (b.alternate_bom_designator = l.alternate_designator))
AND ri_itm.inventory_item_id = b.assembly_item_id
AND ri_itm.organization_id = b.organization_id
AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND ((NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
ri.from_end_item_unit_number
AND c.from_end_item_unit_number <= ri.from_end_item_unit_number
AND ri_itm.effectivity_control = unit_control)
OR
ri_itm.effectivity_control = date_control)
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items_b i,
bom_inventory_comps_interface ci,
eng_revised_items_interface ri
WHERE i.eng_item_flag = 'Y'
AND i.inventory_item_id = ci.component_item_id
AND i.organization_id = p_organization
AND ci.acd_type = action_add
AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_change_order
AND ri.organization_id = p_organization
)
AND EXISTS (
SELECT NULL
FROM bom_bill_of_materials b
WHERE b.assembly_type = mfg
AND b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_organization
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items_b i,
bom_inventory_comps_interface ci,
bom_inventory_comps_interface cir,
eng_revised_items_interface ri
WHERE i.eng_item_flag = 'Y'
AND i.inventory_item_id = ci.component_item_id
AND i.organization_id = p_organization
AND ci.acd_type = action_change
AND ci.old_component_sequence_id = cir.component_sequence_id
AND ci.component_item_id <> cir.component_item_id
AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_change_order
AND ri.organization_id = p_organization
)
AND EXISTS
(
SELECT NULL
FROM bom_structures_b b
WHERE
b.assembly_type = mfg
AND b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_organization
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM bom_components_b c,
bom_structures_b b,
bom_inventory_comps_interface ci,
eng_revised_items_interface ri,
mtl_system_items_b ri_itm
WHERE c.operation_seq_num =
NVL(ci.operation_seq_num, c.operation_seq_num)
AND c.component_item_id = ci.component_item_id
AND c.bill_sequence_id = b.bill_sequence_id
AND ci.acd_type IN (action_delete, action_change)
AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_change_order
AND ri.organization_id = p_organization
AND b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_organization
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
AND ri_itm.organization_id = ri.organization_id
AND ri_itm.inventory_item_id = ri.revised_item_id
AND NVL(TRUNC(c.disable_date), NVL(ri.scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(ri.scheduled_date,TRUNC(SYSDATE))
AND ((NVL(c.to_end_item_unit_number, ri.from_end_item_unit_number) >=
ri.from_end_item_unit_number
AND c.from_end_item_unit_number <= ri.from_end_item_unit_number
AND ri_itm.effectivity_control = unit_control)
OR
ri_itm.effectivity_control = date_control)
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM bom_bill_of_materials cb,
bom_bill_of_materials b,
bom_inventory_comps_interface ci,
eng_revised_items_interface ri
WHERE NOT EXISTS (
SELECT NULL
FROM mtl_system_items_b i
WHERE i.eng_item_flag = DECODE(cb.assembly_type,
1, 'N',
2, i.eng_item_flag)
AND i.bom_enabled_flag = 'Y'
AND i.organization_id = cb.organization_id
AND i.inventory_item_id = ci.component_item_id)
AND cb.organization_id <> b.organization_id
AND cb.common_bill_sequence_id = b.bill_sequence_id
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
AND b.organization_id = p_organization
AND b.assembly_item_id = l.assembly_item_id
AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = p_change_order
AND ri.organization_id = p_organization);
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Check_Component ='||
to_char(l_bom_lists_count));
SAVEPOINT begin_deletes;
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM bom_bill_of_materials b
WHERE b.common_bill_sequence_id <> b.bill_sequence_id
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
AND b.organization_id = p_organization
AND b.assembly_item_id = l.assembly_item_id);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM bom_bill_of_materials b
WHERE b.assembly_item_id = l.assembly_item_id
AND b.organization_id = p_organization
AND ( (b.alternate_bom_designator IS NULL AND l.alternate_designator IS NULL)
OR (b.alternate_bom_designator = l.alternate_designator) )
AND b.structure_type_id IN
(SELECT Structure_Type_Id FROM Bom_Structure_Types_B
WHERE Structure_Type_Name= Bom_Globals.G_PKG_ST_TYPE_NAME
)
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = l.assembly_item_id
AND msi.organization_id = p_organization
AND msi.bom_item_type = 5
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items_b i
WHERE i.inventory_item_id = l.assembly_item_id
AND i.organization_id = p_organization
AND ((i.bom_item_type = DECODE(p_model_item_access,
no, model_type))
OR
(i.bom_item_type = DECODE(p_model_item_access,
no, option_class_type))
OR
(i.bom_item_type = DECODE(p_planning_item_access,
no, planning_type))
OR
(i.bom_item_type = DECODE(p_standard_item_access,
no, standard_type))
)
);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM eng_revised_items_interface r,
mtl_system_items_b i
WHERE r.item_type <> NVL(i.item_type, 'NONE')
AND i.organization_id = p_organization
AND i.inventory_item_id = l.assembly_item_id
AND r.change_notice = p_change_order
AND r.organization_id = p_organization);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM eng_revised_items_interface r,
mtl_system_items_b i
WHERE r.base_item_id <> NVL(i.base_item_id, -1)
AND i.organization_id = r.organization_id
AND i.inventory_item_id = l.assembly_item_id
AND r.change_notice = p_change_order
AND r.organization_id = p_organization);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (SELECT NULL
FROM eng_revised_items_interface r,
mtl_system_items_b i,
bom_parameters bp
WHERE (i.inventory_item_status_code in ('Obsolete','Inactive')
OR i.inventory_item_status_code = nvl(bp.bom_delete_status_code, FND_API.G_MISS_CHAR)
OR i.bom_enabled_flag = 'N' )-- Modified for bug 13362684
AND i.organization_id = bp.organization_id -- Added for bug 13362684
AND i.organization_id = r.organization_id
AND i.inventory_item_id = l.assembly_item_id
AND r.change_notice = p_change_order
AND r.organization_id = p_organization);
DELETE FROM bom_lists l
WHERE l.sequence_id = p_list_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items_b i
WHERE i.effectivity_control <> 1
AND i.organization_id = p_organization
AND i.inventory_item_id = l.assembly_item_id);
select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Restrict_List='||
to_char(l_bom_lists_count));
ROLLBACK TO begin_deletes;
SELECT msi.concatenated_segments
FROM mtl_system_items_vl msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = p_org_id;
SELECT CONCATENATED_SEGMENTS
FROM mtl_item_locations_kfv
WHERE inventory_location_id = p_supply_locator_id;
PROCEDURE mass_update(list_id IN NUMBER,
profile IN ProgramInfoStruct,
change_order IN VARCHAR2,
org_id IN NUMBER,
delete_mco IN NUMBER,
error_message IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
-- ERES change begins :
l_eres_enabled VARCHAR2(10);
SELECT l.assembly_item_id,
l.alternate_designator,
b.bill_sequence_id,
itm.effectivity_control,
ri.new_item_revision,
ri.scheduled_date,
ri.mrp_active,
ri.update_wip,
ri.use_up,
ri.use_up_item_id,
ri.revised_item_sequence_id,
ri.increment_rev,
ri.use_up_plan_name,
ri.from_end_item_unit_number
FROM bom_lists l,
eng_revised_items_interface ri,
bom_bill_of_materials b,
mtl_system_items_b itm
WHERE
((l.alternate_designator IS NULL
AND b.alternate_bom_designator IS NULL)
OR b.alternate_bom_designator = l.alternate_designator)
AND b.organization_id = p_org_id
AND b.assembly_item_id = l.assembly_item_id
AND l.sequence_id = p_list_id
AND l.organization_id = p_org_id
AND ri.change_notice = change_order
AND ri.organization_id = p_org_id
AND itm.inventory_item_id = l.assembly_item_id
AND itm.organization_id = p_org_id
AND ((itm.effectivity_control = unit_control
AND ri.from_end_item_unit_number IS NOT NULL)
OR
(itm.effectivity_control = date_control
AND ri.scheduled_date IS NOT NULL));
SELECT i.description,
i.change_order_type_id,
i.responsible_organization_id,
i.cancellation_comments,
i.priority_code,
i.reason_code,
i.estimated_eng_cost,
i.estimated_mfg_cost,
i.approval_list_name
FROM eng_eng_changes_interface i
WHERE i.change_notice = change_order
AND i.organization_id = org_id;
CURSOR c_comp_delete(
x_scheduled_date bom_inventory_components.effectivity_date%TYPE,
x_bill_sequence_id bom_inventory_components.bill_sequence_id%TYPE,
x_from_unit_number eng_revised_items_interface.from_end_item_unit_number%TYPE)
IS
SELECT /*+ NO_EXPAND */ NVL(o.operation_seq_num,
c.operation_seq_num) operation_sequence_number,
o.component_item_id,
NVL(o.item_num,
c.item_num) item_num,
NVL(o.basis_type,
c.basis_type) basis_type,
NVL(o.component_quantity,
c.component_quantity) component_quantity,
NVL(o.component_yield_factor,
c.component_yield_factor) component_yield_factor,
c.effectivity_date old_effectivity_date,
NVL(o.planning_factor,
c.planning_factor) planning_factor,
NVL(o.quantity_related,
c.quantity_related) quantity_related,
NVL(o.so_basis,
c.so_basis) so_basis,
NVL(o.optional,
c.optional) optional,
NVL(o.mutually_exclusive_options,
c.mutually_exclusive_options) mutually_exclusive_options,
NVL(o.include_in_cost_rollup,
c.include_in_cost_rollup) include_in_cost_rollup,
NVL(o.check_atp,
c.check_atp) check_atp,
NVL(o.shipping_allowed,
c.shipping_allowed) shipping_allowed,
NVL(o.required_to_ship,
c.required_to_ship) required_to_ship,
NVL(o.required_for_revenue,
c.required_for_revenue) required_for_revenue,
NVL(o.include_on_ship_docs,
c.include_on_ship_docs) include_on_ship_docs,
NVL(o.low_quantity,
c.low_quantity) low_quantity,
NVL(o.high_quantity,
c.high_quantity) high_quantity,
ecg_action_delete acd_type,
c.component_sequence_id old_component_sequence_id,
NVL(o.wip_supply_type,
c.wip_supply_type) wip_supply_type,
NVL(o.supply_subinventory,
c.supply_subinventory) supply_subinventory,
NVL(o.supply_locator_id,
c.supply_locator_id) supply_locator_id,
c.from_end_item_unit_number old_from_end_item_unit_number,
ci_itm.bom_item_type,
ri.from_end_item_unit_number,
o.to_end_item_unit_number,
Nvl(o.component_remarks,c.component_remarks)
component_remarks --Bug 3347094
FROM mtl_system_items_b ci_itm,
mtl_system_items_b ri_itm,
bom_bill_of_materials b,
bom_inventory_components c,
bom_inventory_comps_interface o,
eng_revised_items_interface ri
WHERE (c.item_num = o.item_num OR o.item_num IS NULL)
AND (Nvl(c.basis_type,4) = Decode(o.basis_type,FND_API.G_MISS_NUM,4,o.basis_type) OR o.basis_type is NULL) -- 5214239
AND (c.component_quantity = o.component_quantity OR
o.component_quantity IS NULL)
AND (c.component_yield_factor = o.component_yield_factor OR
o.component_yield_factor IS NULL)
AND (c.planning_factor = o.planning_factor OR
o.planning_factor IS NULL)
AND (c.quantity_related = o.quantity_related OR
o.quantity_related IS NULL)
AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
AND (c.optional = o.optional OR o.optional IS NULL)
AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
o.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
o.include_in_cost_rollup IS NULL)
AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
AND (c.shipping_allowed = o.shipping_allowed OR
o.shipping_allowed IS NULL)
AND (c.required_to_ship = o.required_to_ship OR
o.required_to_ship IS NULL)
AND (c.required_for_revenue = o.required_for_revenue OR
o.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = o.include_on_ship_docs OR
o.include_on_ship_docs IS NULL)
AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
AND (c.wip_supply_type = o.wip_supply_type OR
o.wip_supply_type IS NULL)
AND (c.supply_subinventory = o.supply_subinventory OR
o.supply_subinventory IS NULL)
AND (c.supply_locator_id = o.supply_locator_id OR
o.supply_locator_id IS NULL)
AND (c.component_remarks = o.component_remarks OR
o.component_remarks IS NULL)
AND (c.attribute_category = o.attribute_category OR
o.attribute_category IS NULL)
AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
AND c.operation_seq_num = NVL(o.operation_seq_num,
c.operation_seq_num)
AND c.bill_sequence_id = x_bill_sequence_id
AND c.component_item_id = o.component_item_id
AND ci_itm.inventory_item_id = c.component_item_id
AND ci_itm.organization_id = org_id
AND o.acd_type = action_delete
AND o.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = change_order
AND ri.organization_id = org_id
AND b.bill_sequence_id = c.bill_sequence_id
AND ri_itm.inventory_item_id = b.assembly_item_id
AND ri_itm.organization_id = org_id
/* Check for implemenation date is done to avoid mass changes from including
unimplemented components in the mass changes list*/
AND c.implementation_date IS NOT NULL
AND NVL(TRUNC(c.disable_date), NVL(x_scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(x_scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(x_scheduled_date,TRUNC(SYSDATE))
AND ((NVL(c.to_end_item_unit_number, x_from_unit_number) >=
x_from_unit_number
AND c.from_end_item_unit_number <= x_from_unit_number
AND ri_itm.effectivity_control = unit_control)
OR
ri_itm.effectivity_control = date_control);
SELECT NVL(o.operation_seq_num,
c.operation_seq_num) operation_sequence_number,
n.operation_seq_num new_operation_sequence_number,
o.component_item_id,
NVL(o.item_num, c.item_num) item_num,
NVL(o.basis_type, c.basis_type) basis_type,
NVL(o.component_quantity,
c.component_quantity) component_quantity,
NVL(o.component_yield_factor,
c.component_yield_factor) component_yield_factor,
c.effectivity_date old_effectivity_date,
NVL(o.planning_factor,
c.planning_factor) planning_factor,
NVL(o.quantity_related,
c.quantity_related) quantity_related,
NVL(o.so_basis, c.so_basis) so_basis,
NVL(o.optional, c.optional) optional,
NVL(o.mutually_exclusive_options,
c.mutually_exclusive_options) mutually_exclusive_options,
NVL(o.include_in_cost_rollup,
c.include_in_cost_rollup) include_in_cost_rollup,
NVL(o.check_atp, c.check_atp) check_atp,
NVL(o.shipping_allowed,
c.shipping_allowed) shipping_allowed,
NVL(o.required_to_ship,
c.required_to_ship) required_to_ship,
NVL(o.required_for_revenue,
c.required_for_revenue) required_for_revenue,
NVL(o.include_on_ship_docs,
c.include_on_ship_docs) include_on_ship_docs,
NVL(o.low_quantity,
c.low_quantity) low_quantity,
NVL(o.high_quantity,
c.high_quantity) high_quantity,
ecg_action_delete acd_type,
c.component_sequence_id old_component_sequence_id,
NVL(o.wip_supply_type,
c.wip_supply_type) wip_supply_type,
NVL(o.supply_subinventory,
c.supply_subinventory) supply_subinventory,
NVL(o.supply_locator_id,
c.supply_locator_id) supply_locator_id,
c.from_end_item_unit_number old_from_end_item_unit_number,
ci_itm.bom_item_type,
ri.from_end_item_unit_number,
NVL(o.to_end_item_unit_number, c.to_end_item_unit_number) to_end_item_unit_number,
Nvl(o.component_remarks,c.component_remarks)
component_remarks --Bug 3347094
FROM mtl_system_items_b ci_itm,
mtl_system_items_b ri_itm,
bom_bill_of_materials b,
bom_inventory_components c,
bom_inventory_comps_interface n, -- new attributes
bom_inventory_comps_interface o, -- old attributes
eng_revised_items_interface ri
WHERE n.old_component_sequence_id = o.component_sequence_id
AND (n.component_item_id <> o.component_item_id)
AND (c.item_num = o.item_num OR o.item_num IS NULL)
AND (Nvl(c.basis_type,4) = Decode(o.basis_type,FND_API.G_MISS_NUM,4,o.basis_type) OR o.basis_type IS NULL) -- 5214239
AND (c.component_quantity = o.component_quantity OR
o.component_quantity IS NULL)
AND (c.component_yield_factor = o.component_yield_factor OR
o.component_yield_factor IS NULL)
AND (c.component_remarks = o.component_remarks OR
o.component_remarks IS NULL)
AND (c.attribute_category = o.attribute_category OR
o.attribute_category IS NULL)
AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
AND (c.planning_factor = o.planning_factor OR
o.planning_factor IS NULL)
AND (c.quantity_related = o.quantity_related OR
o.quantity_related IS NULL)
AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
AND (c.optional = o.optional OR o.optional IS NULL)
AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
o.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
o.include_in_cost_rollup IS NULL)
AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
AND (c.shipping_allowed = o.shipping_allowed OR
o.shipping_allowed IS NULL)
AND (c.required_to_ship = o.required_to_ship OR
o.required_to_ship IS NULL)
AND (c.required_for_revenue = o.required_for_revenue OR
o.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = o.include_on_ship_docs OR
o.include_on_ship_docs IS NULL)
AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
AND (c.wip_supply_type = o.wip_supply_type OR
o.wip_supply_type IS NULL)
AND (c.supply_subinventory = o.supply_subinventory OR
o.supply_subinventory IS NULL)
AND (c.supply_locator_id = o.supply_locator_id OR
o.supply_locator_id IS NULL)
AND c.operation_seq_num = NVL(o.operation_seq_num,
c.operation_seq_num)
AND c.bill_sequence_id = x_bill_sequence_id
AND c.component_item_id = o.component_item_id
AND o.acd_type = action_replace
AND o.revised_item_sequence_id = ri.revised_item_sequence_id
AND ci_itm.inventory_item_id = c.component_item_id
AND ci_itm.organization_id = org_id
AND ri.change_notice = change_order
AND ri.organization_id = org_id
AND b.bill_sequence_id = c.bill_sequence_id
AND ri_itm.inventory_item_id = b.assembly_item_id
AND ri_itm.organization_id = org_id
/* Check for implemenation date is done to avoid mass changes from including
unimplemented components in the mass changes list*/
AND c.implementation_date IS NOT NULL
AND NVL(TRUNC(c.disable_date), NVL(x_scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(x_scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(x_scheduled_date,TRUNC(SYSDATE))
AND ((NVL(c.to_end_item_unit_number, x_from_unit_number) >=
x_from_unit_number
AND c.from_end_item_unit_number <= x_from_unit_number
AND ri_itm.effectivity_control = unit_control)
OR
ri_itm.effectivity_control = date_control);
SELECT NVL(o.operation_seq_num,
c.operation_seq_num) old_operation_sequence_number,
n.operation_seq_num new_operation_sequence_number,
c.operation_seq_num operation_sequence_number,
n.component_item_id,
NVL(n.item_num, c.item_num) item_num,
NVL(n.basis_type , c.basis_type ) basis_type,
NVL(n.component_quantity,
c.component_quantity) component_quantity,
NVL(n.component_yield_factor,
c.component_yield_factor) component_yield_factor,
DECODE(n.component_item_id,
o.component_item_id,
NVL(x_scheduled_date,TRUNC(SYSDATE)),
NULL) new_effectivity_date,
DECODE(n.component_item_id,
o.component_item_id,
GREATEST(NVL(x_scheduled_date,
TRUNC(SYSDATE)),
n.disable_date),
NULL) disable_date,
NVL(n.component_remarks,
c.component_remarks) component_remarks,
DECODE(n.component_item_id,
o.component_item_id,
c.effectivity_date,
NULL) old_effectivity_date,
NVL(n.planning_factor,
c.planning_factor) planning_factor,
NVL(n.quantity_related,
c.quantity_related) quantity_related,
NVL(n.so_basis, c.so_basis) so_basis,
NVL(n.optional, c.optional) optional,
NVL(n.mutually_exclusive_options,
c.mutually_exclusive_options) mutually_exclusive_options,
NVL(n.include_in_cost_rollup,
c.include_in_cost_rollup) include_in_cost_rollup,
NVL(n.check_atp, c.check_atp) check_atp,
NVL(n.shipping_allowed,
c.shipping_allowed) shipping_allowed,
NVL(n.required_to_ship,
c.required_to_ship) required_to_ship,
NVL(n.required_for_revenue,
c.required_for_revenue) required_for_revenue,
NVL(n.include_on_ship_docs,
c.include_on_ship_docs) include_on_ship_docs,
NVL(n.low_quantity, c.low_quantity) low_quantity,
NVL(n.high_quantity,
c.high_quantity) high_quantity,
DECODE(n.component_item_id,
o.component_item_id,
ecg_action_change,
ecg_action_add) acd_type,
NVL(n.wip_supply_type,
DECODE(o.wip_supply_type, NULL,
c.wip_supply_type, FND_API.G_MISS_NUM)) wip_supply_type, /* bug fix : 9019348 */
NVL(n.supply_subinventory,
DECODE(o.supply_subinventory,
NULL,
c.supply_subinventory,
FND_API.G_MISS_CHAR)) supply_subinventory,
NVL(n.supply_locator_id,
DECODE(o.supply_locator_id,
NULL,
c.supply_locator_id,
FND_API.G_MISS_NUM)) supply_locator_id,
DECODE(n.component_item_id,
o.component_item_id,
c.from_end_item_unit_number,
NULL) old_from_end_item_unit_number,
ci_itm.bom_item_type,
ri.from_end_item_unit_number,
DECODE(n.component_item_id,
o.component_item_id,
GREATEST(x_from_unit_number,
NVL(n.to_end_item_unit_number, c.to_end_item_unit_number)),
NULL) to_end_item_unit_number,
-- added attribute information to resolve BUG# 2784395
NVL(n.attribute_category, c.attribute_category) attribute_category ,
NVL(n.attribute1, c.attribute1) attribute1 ,
NVL(n.attribute2, c.attribute2) attribute2 ,
NVL(n.attribute3, c.attribute3) attribute3 ,
NVL(n.attribute4, c.attribute4) attribute4 ,
NVL(n.attribute5, c.attribute5) attribute5 ,
NVL(n.attribute6, c.attribute6) attribute6 ,
NVL(n.attribute7, c.attribute7) attribute7 ,
NVL(n.attribute8, c.attribute8) attribute8 ,
NVL(n.attribute9, c.attribute9) attribute9 ,
NVL(n.attribute10, c.attribute10) attribute10,
NVL(n.attribute11, c.attribute11) attribute11,
NVL(n.attribute12, c.attribute12) attribute12,
NVL(n.attribute13, c.attribute13) attribute13,
NVL(n.attribute14, c.attribute14) attribute14,
NVL(n.attribute15, c.attribute15) attribute15
-- added attribute information to resolve BUG# 2784395
FROM mtl_system_items_b ci_itm,
mtl_system_items_b ri_itm,
bom_bill_of_materials b,
bom_inventory_components c,
bom_inventory_comps_interface n, -- new attributes
bom_inventory_comps_interface o, -- old attributes
eng_revised_items_interface ri
WHERE n.old_component_sequence_id = o.component_sequence_id
AND (c.item_num = o.item_num OR o.item_num IS NULL)
AND (Nvl(c.basis_type,4) = Decode(o.basis_type,FND_API.G_MISS_NUM,4,o.basis_type) OR o.basis_type is NULL) -- 5214239
AND (c.component_quantity = o.component_quantity OR
o.component_quantity IS NULL)
AND (c.component_yield_factor = o.component_yield_factor OR
o.component_yield_factor IS NULL)
AND (c.component_remarks = o.component_remarks OR
o.component_remarks IS NULL)
AND (c.attribute_category = o.attribute_category OR
o.attribute_category IS NULL)
AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
AND (c.planning_factor = o.planning_factor OR
o.planning_factor IS NULL)
AND (c.quantity_related = o.quantity_related OR
o.quantity_related IS NULL)
AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
AND (c.optional = o.optional OR o.optional IS NULL)
AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
o.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
o.include_in_cost_rollup IS NULL)
AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
AND (c.shipping_allowed = o.shipping_allowed OR
o.shipping_allowed IS NULL)
AND (c.required_to_ship = o.required_to_ship OR
o.required_to_ship IS NULL)
AND (c.required_for_revenue = o.required_for_revenue OR
o.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = o.include_on_ship_docs OR
o.include_on_ship_docs IS NULL)
AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
AND (c.wip_supply_type = o.wip_supply_type OR
o.wip_supply_type IS NULL)
AND (c.supply_subinventory = o.supply_subinventory OR
o.supply_subinventory IS NULL)
AND (c.supply_locator_id = o.supply_locator_id OR
o.supply_locator_id IS NULL)
AND c.operation_seq_num = NVL(o.operation_seq_num,
c.operation_seq_num)
AND c.bill_sequence_id = x_bill_sequence_id
AND c.component_item_id = o.component_item_id
AND o.acd_type = action_replace
AND o.revised_item_sequence_id = ri.revised_item_sequence_id
AND ci_itm.inventory_item_id = c.component_item_id
AND ci_itm.organization_id = org_id
AND ri.change_notice = change_order
AND ri.organization_id = org_id
AND b.bill_sequence_id = c.bill_sequence_id
AND ri_itm.inventory_item_id = b.assembly_item_id
AND ri_itm.organization_id = org_id
/* Check for implemenation date is done to avoid mass changes from including
unimplemented components in the mass changes list*/
AND c.implementation_date IS NOT NULL
AND NVL(TRUNC(c.disable_date), NVL(x_scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(x_scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(x_scheduled_date,TRUNC(SYSDATE))
AND ((NVL(c.to_end_item_unit_number, x_from_unit_number) >=
x_from_unit_number
AND c.from_end_item_unit_number <= x_from_unit_number
AND ri_itm.effectivity_control = unit_control)
OR
ri_itm.effectivity_control = date_control);
SELECT NVL(i.operation_seq_num,
default_operation_seq_num) operation_seq_num,
i.component_item_id,
i.item_num,
i.basis_type basis_type,
NVL(i.component_quantity,
default_component_quantity) component_quantity,
NVL(i.component_yield_factor,
default_component_yield_factor) component_yield_factor,
i.component_remarks,
NVL(i.planning_factor,
default_planning_factor) planning_factor,
NVL(i.quantity_related,
default_quantity_related) quantity_related,
i.so_basis,
i.optional,
i.mutually_exclusive_options,
NVL(i.include_in_cost_rollup,
default_include_in_cost_rollup) include_in_cost_rollup,
NVL(i.check_atp, default_check_atp) check_atp,
i.shipping_allowed,
i.required_to_ship,
i.required_for_revenue,
i.include_on_ship_docs,
i.low_quantity,
i.high_quantity,
ecg_action_add acd_type,
i.wip_supply_type,
i.supply_subinventory,
i.supply_locator_id,
ci_itm.bom_item_type,
ri.from_end_item_unit_number,
i.to_end_item_unit_number,
GREATEST(ri.scheduled_date, i.disable_date) disable_date,
-- added Attribute information for BUG #2784395
i.attribute_category,
i.attribute1,
i.attribute2,
i.attribute3,
i.attribute4,
i.attribute5,
i.attribute6,
i.attribute7,
i.attribute8,
i.attribute9,
i.attribute10,
i.attribute11,
i.attribute12,
i.attribute13,
i.attribute14,
i.attribute15
-- added Attribute information for BUG #2784395
FROM mtl_system_items_b ci_itm,
mtl_system_items_b bi_itm,
bom_bill_of_materials b,
bom_inventory_comps_interface i,
eng_revised_items_interface ri
WHERE i.acd_type = action_add
AND ci_itm.inventory_item_id = i.component_item_id
AND ci_itm.organization_id = org_id
AND ri.revised_item_sequence_id = i.revised_item_sequence_id
AND ri.change_notice = change_order
AND ri.organization_id = org_id
AND b.bill_sequence_id = x_bill_sequence_id
AND bi_itm.inventory_item_id = b.assembly_item_id
AND bi_itm.organization_id = org_id
AND ((bi_itm.effectivity_control = date_control
AND ci_itm.effectivity_control = date_control)
OR
(bi_itm.effectivity_control = unit_control
AND ci_itm.effectivity_control IN (date_control,unit_control)));
SELECT n.component_item_id,
NVL(o.operation_seq_num,
c.operation_seq_num) operation_sequence_number,
brd.COMPONENT_REFERENCE_DESIGNATOR reference_designator_name,
--nvl(brd.acd_type,1) acd_type,
1 acd_type, --modified for 10039721
brd.REF_DESIGNATOR_COMMENT Ref_Designator_Comment,
brd.Attribute_category,
brd.Attribute1,
brd.Attribute2,
c.component_sequence_id old_component_sequence_id,
brd.attribute3,
brd.attribute4,
brd.attribute5,
brd.attribute7,
brd.attribute6,
brd.attribute8,
brd.attribute9,
brd.attribute11,
brd.attribute10,
brd.attribute12,
brd.attribute13,
brd.attribute14,
brd.attribute15
FROM bom_inventory_components c,
bom_inventory_comps_interface n, -- new attributes
bom_inventory_comps_interface o, -- old attributes
eng_revised_items_interface ri,
bom_reference_designators brd,
mtl_system_items_b ri_itm,
bom_bill_of_materials b
WHERE n.old_component_sequence_id = o.component_sequence_id
AND brd.component_sequence_id = c.component_Sequence_id
AND (n.component_item_id <> o.component_item_id)
AND (c.item_num = o.item_num OR o.item_num IS NULL)
AND (c.component_quantity = o.component_quantity OR
o.component_quantity IS NULL)
AND (c.component_yield_factor = o.component_yield_factor OR
o.component_yield_factor IS NULL)
AND (c.component_remarks = o.component_remarks OR
o.component_remarks IS NULL)
AND (c.attribute_category = o.attribute_category OR
o.attribute_category IS NULL)
AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
AND (c.planning_factor = o.planning_factor OR
o.planning_factor IS NULL)
AND (c.quantity_related = o.quantity_related OR
o.quantity_related IS NULL)
AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
AND (c.optional = o.optional OR o.optional IS NULL)
AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
o.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
o.include_in_cost_rollup IS NULL)
AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
AND (c.shipping_allowed = o.shipping_allowed OR
o.shipping_allowed IS NULL)
AND (c.required_to_ship = o.required_to_ship OR
o.required_to_ship IS NULL)
AND (c.required_for_revenue = o.required_for_revenue OR
o.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = o.include_on_ship_docs OR
o.include_on_ship_docs IS NULL)
AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
AND (c.wip_supply_type = o.wip_supply_type OR
o.wip_supply_type IS NULL)
AND (c.supply_subinventory = o.supply_subinventory OR
o.supply_subinventory IS NULL)
AND (c.supply_locator_id = o.supply_locator_id OR
o.supply_locator_id IS NULL)
AND c.operation_seq_num = NVL(o.operation_seq_num,
c.operation_seq_num)
AND c.bill_sequence_id = x_bill_sequence_id
AND c.component_item_id = o.component_item_id
AND o.acd_type = action_replace
AND o.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = change_order
AND ri.organization_id = org_id
AND b.bill_sequence_id = x_bill_sequence_id
AND ri_itm.inventory_item_id = b.assembly_item_id
AND ri_itm.organization_id = org_id
/* Check for implemenation date is done to avoid mass changes from including
unimplemented components in the mass changes list*/
AND c.implementation_date IS NOT NULL
AND NVL(TRUNC(c.disable_date), NVL(x_scheduled_date,TRUNC(SYSDATE)) + 1) >
NVL(x_scheduled_date,TRUNC(SYSDATE))
AND TRUNC(c.effectivity_date) <= NVL(x_scheduled_date,TRUNC(SYSDATE))
AND ((NVL(c.to_end_item_unit_number, x_from_unit_number) >=
x_from_unit_number
AND c.from_end_item_unit_number <= x_from_unit_number
AND ri_itm.effectivity_control = unit_control)
OR
ri_itm.effectivity_control = date_control)
AND nvl(brd.acd_type,1) <> 3;
select revised_item_id,revised_item_sequence_id,
new_item_revision,organization_id
from eng_revised_items
where change_notice = change_order
and organization_id=org_id;
error_message := 'A fatal error occurred while processing mass_update.';
UPDATE bom_lists
SET organization_id = org_id
WHERE sequence_id = list_id;
SAVEPOINT begin_mass_update;
SELECT organization_code
INTO l_org_code
FROM org_organization_definitions
WHERE organization_id = org_id;
l_item_tbl.DELETE;
l_comp_tbl.DELETE;
l_error_tbl.DELETE;
l_rev_tbl.DELETE;
l_ref_designator_tbl.DELETE;
l_sub_component_tbl.DELETE;
l_rev_operation_tbl.DELETE;
l_rev_op_resource_tbl.DELETE;
l_rev_sub_resource_tbl.DELETE;
INSERT INTO eng_engineering_changes(
change_notice,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
description,
status_type,
initiation_date,
request_id,
program_application_id,
program_id,
program_update_date,
approval_status_type,
change_order_type_id,
responsible_organization_id,
hierarchy_flag,
organization_hierarchy)
SELECT i.change_notice,
i.organization_id,
SYSDATE,
profile.userid,
SYSDATE,
profile.userid,
profile.loginid,
i.description,
default_status, -- open
SYSDATE,
profile.reqstid,
profile.appid,
profile.progid,
SYSDATE,
default_approval_status, -- approved
i.change_order_type_id,
i.responsible_organization_id,
2,
NULL
FROM eng_eng_changes_interface i
WHERE i.change_notice = change_order
AND i.organization_id = org_id;
SELECT type_name
INTO l_change_type_code
--FROM eng_change_order_types
FROM eng_change_order_types_vl
WHERE change_order_type_id = eco_rec.change_order_type_id;
SELECT name
INTO l_department_name
FROM hr_all_organization_units
WHERE organization_id = eco_rec.responsible_organization_id;
UPDATE mtl_item_revisions
SET change_notice = change_order,
ecn_initiation_date = SYSDATE,
revised_item_sequence_id =
bom_list.revised_item_sequence_id
WHERE inventory_item_id = bom_list.assembly_item_id
AND organization_id = org_id
AND revision = bom_list.new_item_revision;
INSERT INTO eng_revised_items(
change_notice,
organization_id,
revised_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
disposition_type,
new_item_revision,
early_schedule_date,
status_type,
scheduled_date,
bill_sequence_id,
mrp_active,
request_id,
program_application_id,
program_id,
program_update_date,
update_wip,
use_up,
use_up_item_id,
revised_item_sequence_id,
use_up_plan_name,
eco_for_production, --- bug 1890000
from_end_item_unit_number)
VALUES (
change_order,
org_id,
bom_list.assembly_item_id,
SYSDATE,
profile.userid,
SYSDATE,
profile.userid,
profile.loginid,
default_disposition, -- no change required
bom_list.new_item_revision,
LEAST(bom_list.scheduled_date, SYSDATE),
default_status, -- open status
NVL(bom_list.scheduled_date,SYSDATE),
bom_list.bill_sequence_id,
NVL(bom_list.mrp_active, no),
profile.reqstid,
profile.appid,
profile.progid,
SYSDATE,
NVL(bom_list.update_wip, no),
NVL(bom_list.use_up, no),
bom_list.use_up_item_id,
bom_list.revised_item_sequence_id,
bom_list.use_up_plan_name,
2,
bom_list.from_end_item_unit_number);
l_item_tbl(l_item_cnt).update_wip := NVL(bom_list.update_wip, no);
INSERT INTO eng_current_scheduled_dates(
change_notice,
organization_id,
revised_item_id,
scheduled_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
schedule_id,
program_application_id,
program_id,
program_update_date,
request_id,
revised_item_sequence_id)
VALUES(
change_order,
org_id,
bom_list.assembly_item_id,
NVL(bom_list.scheduled_date,SYSDATE),
SYSDATE,
profile.userid,
SYSDATE,
profile.userid,
profile.loginid,
eng_current_scheduled_dates_s.NEXTVAL,
profile.appid,
profile.progid,
SYSDATE,
profile.reqstid,
bom_list.revised_item_sequence_id);
INSERT INTO bom_inventory_components(
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
effectivity_date,
disable_date,
change_notice,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
low_quantity,
high_quantity,
acd_type,
old_component_sequence_id,
component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
supply_subinventory,
supply_locator_id,
revised_item_sequence_id,
bom_item_type)
SELECT NVL(i.operation_seq_num, c.operation_seq_num),
i.component_item_id,
SYSDATE,
r.last_updated_by,
SYSDATE,
r.created_by,
r.last_update_login,
NVL(i.item_num, c.item_num),
NVL(i.component_quantity, c.component_quantity),
NVL(i.component_yield_factor,
c.component_yield_factor),
NVL(r.scheduled_date,SYSDATE),
r.scheduled_date,
r.change_notice,
NVL(i.planning_factor, c.planning_factor),
NVL(i.quantity_related, c.quantity_related),
NVL(i.so_basis, c.so_basis),
NVL(i.optional, c.optional),
NVL(i.mutually_exclusive_options,
c.mutually_exclusive_options),
NVL(i.include_in_cost_rollup, c.include_in_cost_rollup),
NVL(i.check_atp, c.check_atp),
NVL(i.shipping_allowed, c.shipping_allowed),
NVL(i.required_to_ship, c.required_to_ship),
NVL(i.required_for_revenue, c.required_for_revenue),
NVL(i.include_on_ship_docs, c.include_on_ship_docs),
NVL(i.low_quantity, c.low_quantity),
NVL(i.high_quantity, c.high_quantity),
ecg_action_delete,
c.component_sequence_id,
bom_inventory_components_s.NEXTVAL,
r.bill_sequence_id,
r.request_id,
r.program_application_id,
r.program_id,
SYSDATE,
NVL(i.wip_supply_type, c.wip_supply_type),
NVL(i.supply_subinventory, c.supply_subinventory),
NVL(i.supply_locator_id, c.supply_locator_id),
r.revised_item_sequence_id,
itm.bom_item_type
FROM mtl_system_items_b itm,
bom_inventory_components c,
bom_inventory_comps_interface i,
eng_revised_items_interface ri,
eng_revised_items r
WHERE (c.item_num = i.item_num OR i.item_num IS NULL)
AND (c.component_quantity = i.component_quantity OR
i.component_quantity IS NULL)
AND (c.component_yield_factor = i.component_yield_factor OR
i.component_yield_factor IS NULL)
AND (c.planning_factor = i.planning_factor OR
i.planning_factor IS NULL)
AND (c.quantity_related = i.quantity_related OR
i.quantity_related IS NULL)
AND (c.so_basis = i.so_basis OR i.so_basis IS NULL)
AND (c.optional = i.optional OR i.optional IS NULL)
AND (c.mutually_exclusive_options = i.mutually_exclusive_options OR
i.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = i.include_in_cost_rollup OR
i.include_in_cost_rollup IS NULL)
AND (c.check_atp = i.check_atp OR i.check_atp IS NULL)
AND (c.shipping_allowed = i.shipping_allowed OR
i.shipping_allowed IS NULL)
AND (c.required_to_ship = i.required_to_ship OR
i.required_to_ship IS NULL)
AND (c.required_for_revenue = i.required_for_revenue OR
i.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = i.include_on_ship_docs OR
i.include_on_ship_docs IS NULL)
AND (c.low_quantity = i.low_quantity OR i.low_quantity IS NULL)
AND (c.high_quantity = i.high_quantity OR i.high_quantity IS NULL)
AND (c.wip_supply_type = i.wip_supply_type OR
i.wip_supply_type IS NULL)
AND (c.supply_subinventory = i.supply_subinventory OR
i.supply_subinventory IS NULL)
AND (c.supply_locator_id = i.supply_locator_id OR
i.supply_locator_id IS NULL)
AND (c.component_remarks = i.component_remarks
OR i.component_remarks IS NULL)
AND (c.attribute_category = i.attribute_category
OR i.attribute_category IS NULL)
AND (c.attribute1 = i.attribute1 OR i.attribute1 IS NULL)
AND (c.attribute2 = i.attribute2 OR i.attribute2 IS NULL)
AND (c.attribute3 = i.attribute3 OR i.attribute3 IS NULL)
AND (c.attribute4 = i.attribute4 OR i.attribute4 IS NULL)
AND (c.attribute5 = i.attribute5 OR i.attribute5 IS NULL)
AND (c.attribute6 = i.attribute6 OR i.attribute6 IS NULL)
AND (c.attribute7 = i.attribute7 OR i.attribute7 IS NULL)
AND (c.attribute8 = i.attribute8 OR i.attribute8 IS NULL)
AND (c.attribute9 = i.attribute9 OR i.attribute9 IS NULL)
AND (c.attribute10 = i.attribute10 OR i.attribute10 IS NULL)
AND (c.attribute11 = i.attribute11 OR i.attribute11 IS NULL)
AND (c.attribute12 = i.attribute12 OR i.attribute12 IS NULL)
AND (c.attribute13 = i.attribute13 OR i.attribute13 IS NULL)
AND (c.attribute14 = i.attribute14 OR i.attribute14 IS NULL)
AND (c.attribute15 = i.attribute15 OR i.attribute15 IS NULL)
AND NVL(TRUNC(c.disable_date),
r.scheduled_date+1) > r.scheduled_date
AND TRUNC(c.effectivity_date) <= r.scheduled_date
AND c.operation_seq_num = NVL(i.operation_seq_num,
c.operation_seq_num)
AND c.bill_sequence_id = r.bill_sequence_id
AND c.component_item_id = i.component_item_id
AND itm.inventory_item_id = c.component_item_id
AND itm.organization_id = r.organization_id
AND r.revised_item_sequence_id = bom_list.revised_item_sequence_id
AND i.acd_type = action_delete
AND i.revised_item_sequence_id = ri.revised_item_sequence_id
AND ri.change_notice = change_order
AND ri.organization_id = org_id;
FOR comp_delete IN c_comp_delete(bom_list.scheduled_date,
bom_list.bill_sequence_id,
bom_list.from_end_item_unit_number)
LOOP
--get lookup values
l_component_item_name := Get_Item_Name(comp_delete.component_item_id, org_id);
l_location_name := Get_Location_Name(comp_delete.supply_locator_id);
l_comp_tbl(l_comp_cnt).operation_sequence_number := comp_delete.operation_sequence_number;
l_comp_tbl(l_comp_cnt).acd_type := comp_delete.acd_type;
l_comp_tbl(l_comp_cnt).old_effectivity_date := comp_delete.old_effectivity_date;
l_comp_tbl(l_comp_cnt).old_operation_sequence_number := comp_delete.operation_sequence_number;
l_comp_tbl(l_comp_cnt).item_sequence_number := comp_delete.item_num;
l_comp_tbl(l_comp_cnt).basis_type := comp_delete.basis_type;
l_comp_tbl(l_comp_cnt).quantity_per_assembly := comp_delete.component_quantity;
l_comp_tbl(l_comp_cnt).planning_percent := comp_delete.planning_factor;
l_comp_tbl(l_comp_cnt).projected_yield := comp_delete.component_yield_factor;
l_comp_tbl(l_comp_cnt).include_in_cost_rollup := comp_delete.include_in_cost_rollup;
l_comp_tbl(l_comp_cnt).wip_supply_type := comp_delete.wip_supply_type;
l_comp_tbl(l_comp_cnt).so_basis := comp_delete.so_basis;
l_comp_tbl(l_comp_cnt).optional := comp_delete.optional;
l_comp_tbl(l_comp_cnt).mutually_exclusive := comp_delete.mutually_exclusive_options;
l_comp_tbl(l_comp_cnt).check_atp := comp_delete.check_atp;
l_comp_tbl(l_comp_cnt).shipping_allowed := comp_delete.shipping_allowed;
l_comp_tbl(l_comp_cnt).required_to_ship := comp_delete.required_to_ship;
l_comp_tbl(l_comp_cnt).required_for_revenue := comp_delete.required_for_revenue;
l_comp_tbl(l_comp_cnt).include_on_ship_docs := comp_delete.include_on_ship_docs;
l_comp_tbl(l_comp_cnt).quantity_related := comp_delete.quantity_related;
l_comp_tbl(l_comp_cnt).supply_subinventory := comp_delete.supply_subinventory;
l_comp_tbl(l_comp_cnt).minimum_allowed_quantity := comp_delete.low_quantity;
l_comp_tbl(l_comp_cnt).maximum_allowed_quantity := comp_delete.high_quantity;
l_comp_tbl(l_comp_cnt).old_from_end_item_unit_number := comp_delete.old_from_end_item_unit_number;
l_comp_tbl(l_comp_cnt).from_end_item_unit_number := comp_delete.from_end_item_unit_number;
l_comp_tbl(l_comp_cnt).to_end_item_unit_number := comp_delete.to_end_item_unit_number;
l_comp_tbl(l_comp_cnt).comments := comp_delete.component_remarks; -- Bug 3347094
END LOOP; --comp_delete
INSERT INTO bom_inventory_components(
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
effectivity_date,
disable_date,
change_notice,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
low_quantity,
high_quantity,
acd_type,
old_component_sequence_id,
component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
supply_subinventory,
supply_locator_id,
revised_item_sequence_id,
bom_item_type)
SELECT NVL(o.operation_seq_num, c.operation_seq_num),
o.component_item_id,
SYSDATE,
r.last_updated_by,
SYSDATE,
r.created_by,
r.last_update_login,
NVL(o.item_num, c.item_num),
NVL(o.component_quantity, c.component_quantity),
NVL(o.component_yield_factor,
c.component_yield_factor),
NVL(r.scheduled_date,SYSDATE),
r.scheduled_date,
r.change_notice,
NVL(o.planning_factor, c.planning_factor),
NVL(o.quantity_related, c.quantity_related),
NVL(o.so_basis, c.so_basis),
NVL(o.optional, c.optional),
NVL(o.mutually_exclusive_options,
c.mutually_exclusive_options),
NVL(o.include_in_cost_rollup, c.include_in_cost_rollup),
NVL(o.check_atp, c.check_atp),
NVL(o.shipping_allowed, c.shipping_allowed),
NVL(o.required_to_ship, c.required_to_ship),
NVL(o.required_for_revenue, c.required_for_revenue),
NVL(o.include_on_ship_docs, c.include_on_ship_docs),
NVL(o.low_quantity, c.low_quantity),
NVL(o.high_quantity, c.high_quantity),
ecg_action_delete,
c.component_sequence_id,
bom_inventory_components_s.NEXTVAL,
r.bill_sequence_id,
r.request_id,
r.program_application_id,
r.program_id,
SYSDATE,
NVL(o.wip_supply_type, c.wip_supply_type),
NVL(o.supply_subinventory, c.supply_subinventory),
NVL(o.supply_locator_id, c.supply_locator_id),
r.revised_item_sequence_id,
itm.bom_item_type
FROM mtl_system_items_b itm,
bom_inventory_components c,
bom_inventory_comps_interface n, -- new attributes
bom_inventory_comps_interface o, -- old attributes
eng_revised_items_interface ri,
eng_revised_items r
WHERE n.old_component_sequence_id = o.component_sequence_id
AND (n.component_item_id <> o.component_item_id)
AND (c.item_num = o.item_num OR o.item_num IS NULL)
AND (c.component_quantity = o.component_quantity OR
o.component_quantity IS NULL)
AND (c.component_yield_factor = o.component_yield_factor OR
o.component_yield_factor IS NULL)
AND (c.component_remarks = o.component_remarks OR
o.component_remarks IS NULL)
AND (c.attribute_category = o.attribute_category OR
o.attribute_category IS NULL)
AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
AND (c.planning_factor = o.planning_factor OR
o.planning_factor IS NULL)
AND (c.quantity_related = o.quantity_related OR
o.quantity_related IS NULL)
AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
AND (c.optional = o.optional OR o.optional IS NULL)
AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
o.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
o.include_in_cost_rollup IS NULL)
AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
AND (c.shipping_allowed = o.shipping_allowed OR
o.shipping_allowed IS NULL)
AND (c.required_to_ship = o.required_to_ship OR
o.required_to_ship IS NULL)
AND (c.required_for_revenue = o.required_for_revenue OR
o.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = o.include_on_ship_docs OR
o.include_on_ship_docs IS NULL)
AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
AND (c.wip_supply_type = o.wip_supply_type OR
o.wip_supply_type IS NULL)
AND (c.supply_subinventory = o.supply_subinventory OR
o.supply_subinventory IS NULL)
AND (c.supply_locator_id = o.supply_locator_id OR
o.supply_locator_id IS NULL)
AND NVL(TRUNC(c.disable_date),
r.scheduled_date+1) > r.scheduled_date
AND TRUNC(c.effectivity_date) <= r.scheduled_date
AND c.operation_seq_num = NVL(o.operation_seq_num,
c.operation_seq_num)
AND c.bill_sequence_id = r.bill_sequence_id
AND c.component_item_id = o.component_item_id
AND o.acd_type = action_replace
AND o.revised_item_sequence_id = ri.revised_item_sequence_id
AND itm.inventory_item_id = c.component_item_id
AND itm.organization_id = r.organization_id
AND r.revised_item_sequence_id = bom_list.revised_item_sequence_id
AND ri.change_notice = change_order
AND ri.organization_id = org_id;
INSERT INTO bom_inventory_components(
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
effectivity_date,
disable_date,
change_notice,
component_remarks,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
supply_subinventory,
supply_locator_id,
revised_item_sequence_id,
bom_item_type)
SELECT NVL(n.operation_seq_num, c.operation_seq_num),
n.component_item_id,
SYSDATE,
r.last_updated_by,
SYSDATE,
r.created_by,
r.last_update_login,
NVL(n.item_num, c.item_num),
NVL(n.component_quantity, c.component_quantity),
NVL(n.component_yield_factor, c.component_yield_factor),
NVL(r.scheduled_date,SYSDATE),
GREATEST(r.scheduled_date, n.disable_date),
r.change_notice,
NVL(n.component_remarks, c.component_remarks),
NVL(n.attribute_category, c.attribute_category),
NVL(n.attribute1, c.attribute1),
NVL(n.attribute2, c.attribute2),
NVL(n.attribute3, c.attribute3),
NVL(n.attribute4, c.attribute4),
NVL(n.attribute5, c.attribute5),
NVL(n.attribute6, c.attribute6),
NVL(n.attribute7, c.attribute7),
NVL(n.attribute8, c.attribute8),
NVL(n.attribute9, c.attribute9),
NVL(n.attribute10, c.attribute10),
NVL(n.attribute11, c.attribute11),
NVL(n.attribute12, c.attribute12),
NVL(n.attribute13, c.attribute13),
NVL(n.attribute14, c.attribute14),
NVL(n.attribute15, c.attribute15),
NVL(n.planning_factor, c.planning_factor),
NVL(n.quantity_related, c.quantity_related),
NVL(n.so_basis, c.so_basis),
NVL(n.optional, c.optional),
NVL(n.mutually_exclusive_options,
c.mutually_exclusive_options),
NVL(n.include_in_cost_rollup, c.include_in_cost_rollup),
NVL(n.check_atp, c.check_atp),
NVL(n.shipping_allowed, c.shipping_allowed),
NVL(n.required_to_ship, c.required_to_ship),
NVL(n.required_for_revenue, c.required_for_revenue),
NVL(n.include_on_ship_docs, c.include_on_ship_docs),
NVL(n.low_quantity, c.low_quantity),
NVL(n.high_quantity, c.high_quantity),
DECODE(n.component_item_id, o.component_item_id,
ecg_action_change, ecg_action_add),
bom_inventory_components_s.NEXTVAL,
DECODE(n.component_item_id,
o.component_item_id,
c.component_sequence_id,
bom_inventory_components_s.CURRVAL),
r.bill_sequence_id,
r.request_id,
r.program_application_id,
r.program_id,
SYSDATE,
NVL(n.wip_supply_type,
DECODE(o.wip_supply_type, NULL, c.wip_supply_type, NULL)),
NVL(n.supply_subinventory, DECODE(o.supply_subinventory,
NULL, c.supply_subinventory, NULL)),
NVL(n.supply_locator_id, DECODE(o.supply_locator_id, NULL,
c.supply_locator_id, NULL)),
r.revised_item_sequence_id,
itm.bom_item_type
FROM mtl_system_items_b itm,
bom_inventory_components c,
bom_inventory_comps_interface n, -- new attributes
bom_inventory_comps_interface o, -- old attributes
eng_revised_items_interface ri,
eng_revised_items r
WHERE n.old_component_sequence_id = o.component_sequence_id
AND (c.item_num = o.item_num OR o.item_num IS NULL)
AND (c.component_quantity = o.component_quantity OR
o.component_quantity IS NULL)
AND (c.component_yield_factor = o.component_yield_factor OR
o.component_yield_factor IS NULL)
AND (c.component_remarks = o.component_remarks OR
o.component_remarks IS NULL)
AND (c.attribute_category = o.attribute_category OR
o.attribute_category IS NULL)
AND (c.attribute1 = o.attribute1 OR o.attribute1 IS NULL)
AND (c.attribute2 = o.attribute2 OR o.attribute2 IS NULL)
AND (c.attribute3 = o.attribute3 OR o.attribute3 IS NULL)
AND (c.attribute4 = o.attribute4 OR o.attribute4 IS NULL)
AND (c.attribute5 = o.attribute5 OR o.attribute5 IS NULL)
AND (c.attribute6 = o.attribute6 OR o.attribute6 IS NULL)
AND (c.attribute7 = o.attribute7 OR o.attribute7 IS NULL)
AND (c.attribute8 = o.attribute8 OR o.attribute8 IS NULL)
AND (c.attribute9 = o.attribute9 OR o.attribute9 IS NULL)
AND (c.attribute10 = o.attribute10 OR o.attribute10 IS NULL)
AND (c.attribute11 = o.attribute11 OR o.attribute11 IS NULL)
AND (c.attribute12 = o.attribute12 OR o.attribute12 IS NULL)
AND (c.attribute13 = o.attribute13 OR o.attribute13 IS NULL)
AND (c.attribute14 = o.attribute14 OR o.attribute14 IS NULL)
AND (c.attribute15 = o.attribute15 OR o.attribute15 IS NULL)
AND (c.planning_factor = o.planning_factor OR
o.planning_factor IS NULL)
AND (c.quantity_related = o.quantity_related OR
o.quantity_related IS NULL)
AND (c.so_basis = o.so_basis OR o.so_basis IS NULL)
AND (c.optional = o.optional OR o.optional IS NULL)
AND (c.mutually_exclusive_options = o.mutually_exclusive_options OR
o.mutually_exclusive_options IS NULL)
AND (c.include_in_cost_rollup = o.include_in_cost_rollup OR
o.include_in_cost_rollup IS NULL)
AND (c.check_atp = o.check_atp OR o.check_atp IS NULL)
AND (c.shipping_allowed = o.shipping_allowed OR
o.shipping_allowed IS NULL)
AND (c.required_to_ship = o.required_to_ship OR
o.required_to_ship IS NULL)
AND (c.required_for_revenue = o.required_for_revenue OR
o.required_for_revenue IS NULL)
AND (c.include_on_ship_docs = o.include_on_ship_docs OR
o.include_on_ship_docs IS NULL)
AND (c.low_quantity = o.low_quantity OR o.low_quantity IS NULL)
AND (c.high_quantity = o.high_quantity OR o.high_quantity IS NULL)
AND (c.wip_supply_type = o.wip_supply_type OR
o.wip_supply_type IS NULL)
AND (c.supply_subinventory = o.supply_subinventory OR
o.supply_subinventory IS NULL)
AND (c.supply_locator_id = o.supply_locator_id OR
o.supply_locator_id IS NULL)
AND NVL(TRUNC(c.disable_date),
r.scheduled_date+1) > r.scheduled_date
AND TRUNC(c.effectivity_date) <= r.scheduled_date
AND c.operation_seq_num = NVL(o.operation_seq_num,
c.operation_seq_num)
AND c.bill_sequence_id = r.bill_sequence_id
AND c.component_item_id = o.component_item_id
AND o.acd_type = action_replace
AND o.revised_item_sequence_id = ri.revised_item_sequence_id
AND itm.inventory_item_id = c.component_item_id
AND itm.organization_id = r.organization_id
AND r.revised_item_sequence_id = bom_list.revised_item_sequence_id
AND ri.change_notice = change_order
AND ri.organization_id = org_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Preparing update of ' ||l_component_item_name||
' on '||l_revised_item_name);
INSERT INTO bom_inventory_components(
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
effectivity_date,
disable_date,
change_notice,
component_remarks,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
low_quantity,
high_quantity,
acd_type,
old_component_sequence_id,
component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
supply_subinventory,
supply_locator_id,
revised_item_sequence_id,
bom_item_type)
SELECT NVL(i.operation_seq_num, default_operation_seq_num),
i.component_item_id,
SYSDATE,
r.last_updated_by,
SYSDATE,
r.created_by,
r.last_update_login,
i.item_num,
NVL(i.component_quantity, default_component_quantity),
NVL(i.component_yield_factor,
default_component_yield_factor),
NVL(r.scheduled_date,SYSDATE),
GREATEST(r.scheduled_date, i.disable_date),
r.change_notice,
i.component_remarks,
i.attribute_category,
i.attribute1,
i.attribute2,
i.attribute3,
i.attribute4,
i.attribute5,
i.attribute6,
i.attribute7,
i.attribute8,
i.attribute9,
i.attribute10,
i.attribute11,
i.attribute12,
i.attribute13,
i.attribute14,
i.attribute15,
NVL(i.planning_factor, default_planning_factor),
NVL(i.quantity_related, default_quantity_related),
i.so_basis,
i.optional,
i.mutually_exclusive_options,
NVL(i.include_in_cost_rollup,
default_include_in_cost_rollup),
NVL(i.check_atp, default_check_atp),
i.shipping_allowed,
i.required_to_ship,
i.required_for_revenue,
i.include_on_ship_docs,
i.low_quantity,
i.high_quantity,
ecg_action_add,
bom_inventory_components_s.NEXTVAL,
bom_inventory_components_s.CURRVAL,
r.bill_sequence_id,
r.request_id,
r.program_application_id,
r.program_id,
SYSDATE,
i.wip_supply_type,
i.supply_subinventory,
i.supply_locator_id,
r.revised_item_sequence_id,
itm.bom_item_type
FROM mtl_system_items_b itm,
bom_inventory_comps_interface i,
eng_revised_items_interface ri,
eng_revised_items r
WHERE r.revised_item_sequence_id =
bom_list.revised_item_sequence_id
AND i.acd_type = action_add
AND itm.inventory_item_id = i.component_item_id
AND itm.organization_id = r.organization_id
AND ri.revised_item_sequence_id = i.revisedeco_name
AND ri.change_notice = change_order
AND ri.organization_id = org_id;
/* --not needed since no inserts being done
EXCEPTION
WHEN Dup_Val_On_Index THEN
DELETE FROM bom_inventory_components
WHERE revised_item_sequence_id = bom_list.revised_item_sequence_id;
DELETE FROM eng_current_scheduled_dates
WHERE revised_item_sequence_id = bom_list.revised_item_sequence_id;
DELETE FROM eng_revised_items
WHERE revised_item_sequence_id = bom_list.revised_item_sequence_id;
DELETE FROM mtl_item_revisions
WHERE revised_item_sequence_id = bom_list.revised_item_sequence_id;
/* Bug 2614633 - Update change notice value and revised item sequence id in
MTL_ITEM_REVISIONS Table for the newly created Revision by Mass change
*/
For item in rev_item
loop
If (item.new_item_revision is not NULL) then
UPDATE mtl_item_revisions_b
SET change_notice = change_order,
ecn_initiation_date = SYSDATE,
revised_item_sequence_id = item.revised_item_sequence_id
WHERE inventory_item_id = item.revised_item_id and
organization_id = item.organization_id and
revision = item.new_item_revision ;
UPDATE bom_inventory_components
SET check_atp = no
WHERE revised_item_sequence_id IN (
SELECT r.revised_item_sequence_id
FROM eng_revised_items r,
mtl_system_items_b i
WHERE i.atp_components_flag = 'N'
AND i.pick_components_flag = 'N'
AND i.replenish_to_order_flag = 'N'
AND i.wip_supply_type <> phantom
AND i.inventory_item_id = r.revised_item_id
AND i.organization_id = r.organization_id
AND r.change_notice = change_order
AND r.organization_id = org_id);
UPDATE bom_inventory_components
SET check_atp = no
WHERE component_sequence_id IN (
SELECT component_sequence_id
FROM bom_inventory_components c,
eng_revised_items r
WHERE r.change_notice = change_order
AND r.organization_id = org_id
AND r.revised_item_sequence_id = c.revised_item_sequence_id
AND c.component_quantity <= 0);
UPDATE bom_inventory_components
SET wip_supply_type = phantom
WHERE component_sequence_id IN (
SELECT c.component_sequence_id
FROM mtl_system_items_b i,
mtl_system_items_b ci,
bom_inventory_components c,
eng_revised_items r
WHERE ci.bom_item_type IN (model_type, option_class_type)
AND ci.inventory_item_id = c.component_item_id
AND ci.organization_id = r.organization_id
AND c.revised_item_sequence_id = r.revised_item_sequence_id
AND i.inventory_item_id = r.revised_item_id
AND i.organization_id = r.organization_id
AND r.change_notice = change_order
AND r.organization_id = org_id);
UPDATE bom_inventory_components
SET optional = yes
WHERE component_sequence_id IN (
SELECT c.component_sequence_id
FROM mtl_system_items_b i,
mtl_system_items_b ci,
bom_inventory_components c,
eng_revised_items r
WHERE ci.base_item_id IS NULL
AND ci.replenish_to_order_flag = 'Y'
AND ci.bom_item_type = standard_type
AND i.pick_components_flag = 'Y'
AND i.bom_item_type IN (model_type, option_class_type)
AND ci.inventory_item_id = c.component_item_id
AND ci.organization_id = r.organization_id
AND c.revised_item_sequence_id = r.revised_item_sequence_id
AND i.inventory_item_id = r.revised_item_id
AND i.organization_id = r.organization_id
AND r.change_notice = change_order
AND r.organization_id = org_id);
IF delete_mco = yes THEN
DELETE FROM bom_inventory_comps_interface
WHERE revised_item_sequence_id IN
(SELECT revised_item_sequence_id
FROM eng_revised_items_interface
WHERE change_notice = change_order
AND organization_id = org_id);
DELETE FROM eng_revised_items_interface
WHERE change_notice = change_order
AND organization_id = org_id;
DELETE FROM eng_eng_changes_interface
WHERE change_notice = change_order
AND organization_id = org_id;
END IF; --delete_eco
ROLLBACK TO begin_mass_update;
ROLLBACK TO begin_mass_update;
ROLLBACK TO begin_mass_update;
END mass_update;