[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT attr_group_id
FROM ENG_CHANGES_EXT_B
WHERE change_id = p_change_id;
SELECT change_order_type_id INTO l_change_order_type_id
FROM eng_engineering_changes
WHERE change_id = p_change_id;
SELECT DISTINCT attr_group_id
FROM bom_structures_ext_b
WHERE
bill_sequence_id = p_bill_sequence_id AND
structure_type_id = p_structure_type_id;
SELECT bill_sequence_id,structure_type_id
FROM bom_bill_of_materials
WHERE
assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND NVL(alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
SELECT DISTINCT attr_group_id
FROM bom_components_ext_b
WHERE bill_sequence_id = p_bill_sequence_id
AND structure_type_id = p_structure_type_id;
SELECT bill_sequence_id,structure_type_id
FROM bom_bill_of_materials
WHERE
assembly_item_id = p_revised_item_id
AND organization_id = p_organization_id
AND NVL(alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
SELECT component_sequence_id
FROM bom_components_b
WHERE
bill_sequence_id = p_bill_sequence_id AND
component_item_id = p_component_item_id AND
change_notice = p_eco_name;
SELECT
effectivity_date
FROM
mtl_item_revisions_b
WHERE
inventory_item_id = p_inventory_item_id AND
organization_id = p_organization_id AND
(p_new_revision IS NULL -- Added for bug 14032254,the "IS NULL" condition along with
OR revision <= UPPER(p_new_revision)) -- the NULL default value is for compatibility consideration
ORDER BY
effectivity_date DESC, revision DESC;
SELECT
bic.component_sequence_id
FROM
bom_inventory_components bic,
mtl_system_items_kfv it
WHERE
bic.bill_sequence_id = p_from_sequence_id
AND bic.component_item_id = it.inventory_item_id
AND it.organization_id = p_reference_org_id
AND it.concatenated_segments = p_component_item.component_item_name;
SELECT
sc.substitute_component_id,
it.concatenated_segments substitute_component_name
FROM
bom_substitute_components sc,
mtl_system_items_kfv it
WHERE
sc.substitute_component_id = it.inventory_item_id
AND it.organization_id = p_reference_org_id
AND sc.component_sequence_id = p_component_sequence_id
AND NVL(sc.acd_type,1) = 1; --Only added components are taken
SELECT
DECODE(p_new_substitute_comp ,NULL ,NVL(sc.acd_type,1) ,DECODE(p_new_substitute_comp.acd_type
,fnd_api.g_miss_num ,NVL(sc.acd_type,1) ,p_new_substitute_comp.acd_type)) acd_type
,DECODE(p_new_substitute_comp ,NULL ,sc.substitute_item_quantity ,DECODE(p_new_substitute_comp.substitute_item_quantity
,fnd_api.g_miss_num ,sc.substitute_item_quantity ,p_new_substitute_comp.substitute_item_quantity)) substitute_item_quantity
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute_category ,DECODE(p_new_substitute_comp.attribute_category
,fnd_api.g_miss_char ,sc.attribute_category ,p_new_substitute_comp.attribute_category )) attribute_category
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute1 ,DECODE(p_new_substitute_comp.attribute1
,fnd_api.g_miss_char ,sc.attribute1 ,p_new_substitute_comp.attribute1 )) attribute1
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute2 ,DECODE(p_new_substitute_comp.attribute2
,fnd_api.g_miss_char ,sc.attribute2 ,p_new_substitute_comp.attribute2 )) attribute2
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute3 ,DECODE(p_new_substitute_comp.attribute3
,fnd_api.g_miss_char ,sc.attribute3 ,p_new_substitute_comp.attribute3 )) attribute3
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute4 ,DECODE(p_new_substitute_comp.attribute4
,fnd_api.g_miss_char ,sc.attribute4 ,p_new_substitute_comp.attribute4 )) attribute4
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute5 ,DECODE(p_new_substitute_comp.attribute5
,fnd_api.g_miss_char ,sc.attribute5 ,p_new_substitute_comp.attribute5 )) attribute5
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute6 ,DECODE(p_new_substitute_comp.attribute6
,fnd_api.g_miss_char ,sc.attribute6 ,p_new_substitute_comp.attribute6 )) attribute6
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute7 ,DECODE(p_new_substitute_comp.attribute7
,fnd_api.g_miss_char ,sc.attribute7 ,p_new_substitute_comp.attribute7 )) attribute7
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute8 ,DECODE(p_new_substitute_comp.attribute8
,fnd_api.g_miss_char ,sc.attribute8 ,p_new_substitute_comp.attribute8 )) attribute8
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute9 ,DECODE(p_new_substitute_comp.attribute9
,fnd_api.g_miss_char ,sc.attribute9 ,p_new_substitute_comp.attribute9 )) attribute9
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute10 ,DECODE(p_new_substitute_comp.attribute10
,fnd_api.g_miss_char ,sc.attribute10 ,p_new_substitute_comp.attribute10 )) attribute10
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute11 ,DECODE(p_new_substitute_comp.attribute11
,fnd_api.g_miss_char ,sc.attribute11 ,p_new_substitute_comp.attribute11 )) attribute11
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute12 ,DECODE(p_new_substitute_comp.attribute12
,fnd_api.g_miss_char ,sc.attribute12 ,p_new_substitute_comp.attribute12 )) attribute12
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute13 ,DECODE(p_new_substitute_comp.attribute13
,fnd_api.g_miss_char ,sc.attribute13 ,p_new_substitute_comp.attribute13 )) attribute13
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute14 ,DECODE(p_new_substitute_comp.attribute14
,fnd_api.g_miss_char ,sc.attribute14 ,p_new_substitute_comp.attribute14 )) attribute14
,DECODE(p_new_substitute_comp ,NULL ,sc.attribute15 ,DECODE(p_new_substitute_comp.attribute15
,fnd_api.g_miss_char ,sc.attribute15 ,p_new_substitute_comp.attribute15 )) attribute15
,DECODE(p_new_substitute_comp ,NULL ,sc.original_system_reference ,DECODE(p_new_substitute_comp.original_system_reference
,fnd_api.g_miss_char ,sc.original_system_reference ,p_new_substitute_comp.original_system_reference)) original_system_reference
,DECODE(p_new_substitute_comp ,NULL ,sc.enforce_int_requirements ,DECODE(p_new_substitute_comp.enforce_int_requirements
,fnd_api.g_miss_num ,sc.enforce_int_requirements ,p_new_substitute_comp.enforce_int_requirements)) enforce_int_requirements
,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.start_effective_date) start_effective_date
,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.new_substitute_component_name ) new_substitute_component_name
,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.from_end_item_unit_number) from_end_item_unit_number
,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.new_routing_revision) new_routing_revision
,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.return_status) return_status
,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.inverse_quantity) inverse_quantity
,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.row_identifier ) row_identifier
,DECODE(p_new_substitute_comp ,NULL ,NULL ,p_new_substitute_comp.program_id) program_id
FROM
bom_substitute_components sc,
mtl_system_items_kfv it
WHERE
sc.substitute_component_id = it.inventory_item_id AND
it.organization_id = p_reference_org_id AND
it.concatenated_segments = p_substitute_component_name AND
sc.component_sequence_id = p_component_sequence_id;
IF p_component_item.substitute_component_tbl(i).transaction_type <> ENG_GLOBALS.g_opr_delete THEN
l_substitute_comp_tbl.EXTEND(1);
IF l_component_item.substitute_component_tbl(i).transaction_type = ENG_GLOBALS.g_opr_update THEN
OPEN c_merged_substitute_comp(p_new_substitute_comp => l_component_item.substitute_component_tbl(i)
,p_substitute_component_name => l_component_item.substitute_component_tbl(i).substitute_component_name
,p_Component_Sequence_Id => l_Component_Sequence_Id);
SELECT
bic.component_sequence_id
FROM
bom_inventory_components bic,
mtl_system_items_kfv it
WHERE
bic.bill_sequence_id = p_from_sequence_id
AND bic.component_item_id = it.inventory_item_id
AND it.organization_id = p_reference_org_id
AND it.concatenated_segments = p_component_item.component_item_name;
SELECT
component_reference_designator reference_designator_name
FROM
bom_reference_designators
WHERE
component_sequence_id = p_component_sequence_id
AND NVL(acd_type,1) = 1; --Only added components are taken
SELECT
DECODE(p_new_ref_designator ,NULL ,NVL(acd_type,1) ,DECODE(p_new_ref_designator.acd_type
,fnd_api.g_miss_num ,NVL(acd_type,1) ,p_new_ref_designator.acd_type)) acd_type
,DECODE(p_new_ref_designator ,NULL ,attribute_category ,DECODE(p_new_ref_designator.attribute_category
,fnd_api.g_miss_char ,attribute_category ,p_new_ref_designator.attribute_category )) attribute_category
,DECODE(p_new_ref_designator ,NULL ,attribute1 ,DECODE(p_new_ref_designator.attribute1
,fnd_api.g_miss_char ,attribute1 ,p_new_ref_designator.attribute1 )) attribute1
,DECODE(p_new_ref_designator ,NULL ,attribute2 ,DECODE(p_new_ref_designator.attribute2
,fnd_api.g_miss_char ,attribute2 ,p_new_ref_designator.attribute2 )) attribute2
,DECODE(p_new_ref_designator ,NULL ,attribute3 ,DECODE(p_new_ref_designator.attribute3
,fnd_api.g_miss_char ,attribute3 ,p_new_ref_designator.attribute3 )) attribute3
,DECODE(p_new_ref_designator ,NULL ,attribute4 ,DECODE(p_new_ref_designator.attribute4
,fnd_api.g_miss_char ,attribute4 ,p_new_ref_designator.attribute4 )) attribute4
,DECODE(p_new_ref_designator ,NULL ,attribute5 ,DECODE(p_new_ref_designator.attribute5
,fnd_api.g_miss_char ,attribute5 ,p_new_ref_designator.attribute5 )) attribute5
,DECODE(p_new_ref_designator ,NULL ,attribute6 ,DECODE(p_new_ref_designator.attribute6
,fnd_api.g_miss_char ,attribute6 ,p_new_ref_designator.attribute6 )) attribute6
,DECODE(p_new_ref_designator ,NULL ,attribute7 ,DECODE(p_new_ref_designator.attribute7
,fnd_api.g_miss_char ,attribute7 ,p_new_ref_designator.attribute7 )) attribute7
,DECODE(p_new_ref_designator ,NULL ,attribute8 ,DECODE(p_new_ref_designator.attribute8
,fnd_api.g_miss_char ,attribute8 ,p_new_ref_designator.attribute8 )) attribute8
,DECODE(p_new_ref_designator ,NULL ,attribute9 ,DECODE(p_new_ref_designator.attribute9
,fnd_api.g_miss_char ,attribute9 ,p_new_ref_designator.attribute9 )) attribute9
,DECODE(p_new_ref_designator ,NULL ,attribute10 ,DECODE(p_new_ref_designator.attribute10
,fnd_api.g_miss_char ,attribute10 ,p_new_ref_designator.attribute10 )) attribute10
,DECODE(p_new_ref_designator ,NULL ,attribute11 ,DECODE(p_new_ref_designator.attribute11
,fnd_api.g_miss_char ,attribute11 ,p_new_ref_designator.attribute11 )) attribute11
,DECODE(p_new_ref_designator ,NULL ,attribute12 ,DECODE(p_new_ref_designator.attribute12
,fnd_api.g_miss_char ,attribute12 ,p_new_ref_designator.attribute12 )) attribute12
,DECODE(p_new_ref_designator ,NULL ,attribute13 ,DECODE(p_new_ref_designator.attribute13
,fnd_api.g_miss_char ,attribute13 ,p_new_ref_designator.attribute13 )) attribute13
,DECODE(p_new_ref_designator ,NULL ,attribute14 ,DECODE(p_new_ref_designator.attribute14
,fnd_api.g_miss_char ,attribute14 ,p_new_ref_designator.attribute14 )) attribute14
,DECODE(p_new_ref_designator ,NULL ,attribute15 ,DECODE(p_new_ref_designator.attribute15
,fnd_api.g_miss_char ,attribute15 ,p_new_ref_designator.attribute15 )) attribute15
,DECODE(p_new_ref_designator ,NULL ,original_system_reference ,DECODE(p_new_ref_designator.original_system_reference
,fnd_api.g_miss_char ,original_system_reference ,p_new_ref_designator.original_system_reference)) original_system_reference
,DECODE(p_new_ref_designator ,NULL ,ref_designator_comment ,DECODE(p_new_ref_designator.ref_designator_comment
,fnd_api.g_miss_char ,ref_designator_comment ,p_new_ref_designator.ref_designator_comment )) ref_designator_comment
,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.start_effective_date) start_effective_date
,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.new_reference_designator ) new_reference_designator
,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.from_end_item_unit_number) from_end_item_unit_number
,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.new_routing_revision) new_routing_revision
,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.return_status) return_status
,DECODE(p_new_ref_designator ,NULL ,NULL ,p_new_ref_designator.row_identifier ) row_identifier
FROM
bom_reference_designators
WHERE
component_reference_designator = p_ref_designator_name AND
component_sequence_id = p_component_sequence_id;
IF p_component_item.reference_designator_tbl(i).transaction_type <> ENG_GLOBALS.g_opr_delete THEN
l_ref_designator_tbl.EXTEND(1);
IF l_component_item.reference_designator_tbl(i).transaction_type = ENG_GLOBALS.g_opr_update THEN
OPEN c_merged_ref_designators(p_new_ref_designator => l_component_item.reference_designator_tbl(i)
,p_ref_designator_name => l_component_item.reference_designator_tbl(i).reference_designator_name
,p_component_sequence_id => l_Component_Sequence_Id);
SELECT
msi.concatenated_segments component_item_name,
bic.component_item_id,
bic.operation_seq_num
FROM bom_inventory_components bic,
mtl_system_items_kfv msi
WHERE bic.bill_sequence_id = p_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> p_to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = p_reference_org_id
AND ((p_unit_assembly = 'N'
AND ((UPPER(p_view_scope) = G_VIEW_SCOPE_ALL) -- ALL
OR (UPPER(p_view_scope) = G_VIEW_SCOPE_CURRENT
AND (effectivity_date <= p_as_of_date
AND
( (disable_date > p_as_of_date
AND disable_date > SYSDATE
)
OR disable_date IS NULL
)
)
)
OR -- CURRENT
(UPPER(p_view_scope ) = G_VIEW_SCOPE_CURR_FUTURE
AND
( (disable_date > p_as_of_date
AND disable_date > SYSDATE
)
OR disable_date IS NULL
)
)
) -- CURRENT + FUTURE
)
OR (p_unit_assembly = 'Y'
AND ((UPPER(p_view_scope) = G_VIEW_SCOPE_ALL) -- ALL
OR (UPPER(p_view_scope) = G_VIEW_SCOPE_CURRENT
AND disable_date IS NULL
AND (from_end_item_unit_number <= p_unit_number
AND (to_end_item_unit_number >=
p_unit_number
OR to_end_item_unit_number IS NULL
)
)
)
OR -- CURRENT
(UPPER(p_view_scope) = G_VIEW_SCOPE_CURR_FUTURE
AND disable_date IS NULL
AND (to_end_item_unit_number >= p_unit_number
OR to_end_item_unit_number IS NULL
)
)
) -- CURRENT + FUTURE
)
)
AND ((p_base_item_flag = -1
AND p_itm_type = 4
AND msi.bom_item_type = 4
)
OR p_base_item_flag <> -1
OR p_itm_type <> 4
)
AND (UPPER(p_implementation_scope) = G_VIEW_SCOPE_ALL OR
(UPPER(p_implementation_scope) = G_IMPLEMENT_SCOPE_IMPLEMENT AND implementation_date IS NOT NULL) OR
(UPPER(p_implementation_scope) = G_IMPLEMENT_SCOPE_UNIMPLEMENT AND implementation_date IS NULL));
SELECT
DECODE(p_new_comp_item ,NULL ,bic.disable_date ,DECODE(p_new_comp_item.disable_date
,fnd_api.g_miss_date ,bic.disable_date ,p_new_comp_item.disable_date)) disable_date
,DECODE(p_new_comp_item ,NULL ,NVL(bic.acd_type,1) ,DECODE(p_new_comp_item.acd_type
,fnd_api.g_miss_char ,NVL(bic.acd_type,1) ,p_new_comp_item.acd_type)) acd_type
,DECODE(p_new_comp_item ,NULL ,bic.basis_type ,DECODE(p_new_comp_item.basis_type
,fnd_api.g_miss_num ,bic.basis_type ,p_new_comp_item.basis_type)) basis_type
,DECODE(p_new_comp_item ,NULL ,bic.component_quantity ,DECODE(p_new_comp_item.quantity_per_assembly
,fnd_api.g_miss_num ,bic.component_quantity ,p_new_comp_item.quantity_per_assembly )) quantity_per_assembly
,DECODE(p_new_comp_item ,NULL ,bic.component_quantity ,DECODE(p_new_comp_item.inverse_quantity
,fnd_api.g_miss_num ,bic.component_quantity ,p_new_comp_item.inverse_quantity )) inverse_quantity
,DECODE(p_new_comp_item ,NULL ,bic.include_in_cost_rollup ,DECODE(p_new_comp_item.include_in_cost_rollup
,fnd_api.g_miss_num ,bic.include_in_cost_rollup ,p_new_comp_item.include_in_cost_rollup)) include_in_cost_rollup
,DECODE(p_new_comp_item ,NULL ,bic.wip_supply_type ,DECODE(p_new_comp_item.wip_supply_type
,fnd_api.g_miss_num ,bic.wip_supply_type ,p_new_comp_item.wip_supply_type)) wip_supply_type
,DECODE(p_new_comp_item ,NULL ,bic.so_basis ,DECODE(p_new_comp_item.so_basis
,fnd_api.g_miss_num ,bic.so_basis ,p_new_comp_item.so_basis)) so_basis
,DECODE(p_new_comp_item ,NULL ,bic.optional ,DECODE(p_new_comp_item.optional
,fnd_api.g_miss_num ,bic.optional ,p_new_comp_item.optional)) optional
,DECODE(p_new_comp_item ,NULL ,bic.mutually_exclusive_options ,DECODE(p_new_comp_item.mutually_exclusive
,fnd_api.g_miss_num ,bic.mutually_exclusive_options ,p_new_comp_item.mutually_exclusive)) mutually_exclusive
,DECODE(p_new_comp_item ,NULL ,bic.check_atp ,DECODE(p_new_comp_item.check_atp
,fnd_api.g_miss_num ,bic.check_atp ,p_new_comp_item.check_atp)) check_atp
,DECODE(p_new_comp_item ,NULL ,bic.shipping_allowed ,DECODE(p_new_comp_item.shipping_allowed
,fnd_api.g_miss_num ,bic.shipping_allowed ,p_new_comp_item.shipping_allowed)) shipping_allowed
,DECODE(p_new_comp_item ,NULL ,bic.required_to_ship ,DECODE(p_new_comp_item.required_to_ship
,fnd_api.g_miss_num ,bic.required_to_ship ,p_new_comp_item.required_to_ship)) required_to_ship
,DECODE(p_new_comp_item ,NULL ,bic.required_for_revenue ,DECODE(p_new_comp_item.required_for_revenue
,fnd_api.g_miss_num ,bic.required_for_revenue ,p_new_comp_item.required_for_revenue)) required_for_revenue
,DECODE(p_new_comp_item ,NULL ,bic.include_on_ship_docs ,DECODE(p_new_comp_item.include_on_ship_docs
,fnd_api.g_miss_num ,bic.include_on_ship_docs ,p_new_comp_item.include_on_ship_docs)) include_on_ship_docs
,DECODE(p_new_comp_item ,NULL ,bic.quantity_related ,DECODE(p_new_comp_item.quantity_related
,fnd_api.g_miss_num ,bic.quantity_related ,p_new_comp_item.quantity_related)) quantity_related
,DECODE(p_new_comp_item ,NULL ,bic.supply_subinventory ,DECODE(p_new_comp_item.supply_subinventory
,fnd_api.g_miss_char ,bic.supply_subinventory ,p_new_comp_item.supply_subinventory)) supply_subinventory
,DECODE(p_new_comp_item ,NULL ,bic.attribute_category ,DECODE(p_new_comp_item.attribute_category
,fnd_api.g_miss_char ,bic.attribute_category ,p_new_comp_item.attribute_category)) attribute_category
,DECODE(p_new_comp_item ,NULL ,bic.attribute1 ,DECODE(p_new_comp_item.attribute1
,fnd_api.g_miss_char ,bic.attribute1 ,p_new_comp_item.attribute1)) attribute1
,DECODE(p_new_comp_item ,NULL ,bic.attribute2 ,DECODE(p_new_comp_item.attribute2
,fnd_api.g_miss_char ,bic.attribute2 ,p_new_comp_item.attribute2)) attribute2
,DECODE(p_new_comp_item ,NULL ,bic.attribute3 ,DECODE(p_new_comp_item.attribute3
,fnd_api.g_miss_char ,bic.attribute3 ,p_new_comp_item.attribute3)) attribute3
,DECODE(p_new_comp_item ,NULL ,bic.attribute4 ,DECODE(p_new_comp_item.attribute4
,fnd_api.g_miss_char ,bic.attribute4 ,p_new_comp_item.attribute4)) attribute4
,DECODE(p_new_comp_item ,NULL ,bic.attribute5 ,DECODE(p_new_comp_item.attribute5
,fnd_api.g_miss_char ,bic.attribute5 ,p_new_comp_item.attribute5)) attribute5
,DECODE(p_new_comp_item ,NULL ,bic.attribute6 ,DECODE(p_new_comp_item.attribute6
,fnd_api.g_miss_char ,bic.attribute6 ,p_new_comp_item.attribute6)) attribute6
,DECODE(p_new_comp_item ,NULL ,bic.attribute7 ,DECODE(p_new_comp_item.attribute7
,fnd_api.g_miss_char ,bic.attribute7 ,p_new_comp_item.attribute7)) attribute7
,DECODE(p_new_comp_item ,NULL ,bic.attribute8 ,DECODE(p_new_comp_item.attribute8
,fnd_api.g_miss_char ,bic.attribute8 ,p_new_comp_item.attribute8)) attribute8
,DECODE(p_new_comp_item ,NULL ,bic.attribute9 ,DECODE(p_new_comp_item.attribute9
,fnd_api.g_miss_char ,bic.attribute9 ,p_new_comp_item.attribute9)) attribute9
,DECODE(p_new_comp_item ,NULL ,bic.attribute10 ,DECODE(p_new_comp_item.attribute10
,fnd_api.g_miss_char ,bic.attribute10 ,p_new_comp_item.attribute10)) attribute10
,DECODE(p_new_comp_item ,NULL ,bic.attribute11 ,DECODE(p_new_comp_item.attribute11
,fnd_api.g_miss_char ,bic.attribute11 ,p_new_comp_item.attribute11)) attribute11
,DECODE(p_new_comp_item ,NULL ,bic.attribute12 ,DECODE(p_new_comp_item.attribute12
,fnd_api.g_miss_char ,bic.attribute12 ,p_new_comp_item.attribute12)) attribute12
,DECODE(p_new_comp_item ,NULL ,bic.attribute13 ,DECODE(p_new_comp_item.attribute13
,fnd_api.g_miss_char ,bic.attribute13 ,p_new_comp_item.attribute13)) attribute13
,DECODE(p_new_comp_item ,NULL ,bic.attribute14 ,DECODE(p_new_comp_item.attribute14
,fnd_api.g_miss_char ,bic.attribute14 ,p_new_comp_item.attribute14)) attribute14
,DECODE(p_new_comp_item ,NULL ,bic.attribute15 ,DECODE(p_new_comp_item.attribute15
,fnd_api.g_miss_char ,bic.attribute15 ,p_new_comp_item.attribute15)) attribute15
,DECODE(p_new_comp_item ,NULL ,bic.from_end_item_unit_number ,DECODE(p_new_comp_item.from_end_item_unit_number
,fnd_api.g_miss_char ,bic.from_end_item_unit_number ,p_new_comp_item.from_end_item_unit_number)) from_end_item_unit_number
,DECODE(p_new_comp_item ,NULL ,bic.to_end_item_unit_number ,DECODE(p_new_comp_item.to_end_item_unit_number
,fnd_api.g_miss_char ,bic.to_end_item_unit_number ,p_new_comp_item.to_end_item_unit_number)) to_end_item_unit_number
,DECODE(p_new_comp_item ,NULL ,bic.enforce_int_requirements ,DECODE(p_new_comp_item.enforce_int_requirements
,fnd_api.g_miss_char ,bic.enforce_int_requirements ,p_new_comp_item.enforce_int_requirements)) enforce_int_requirements
,DECODE(p_new_comp_item ,NULL ,bic.auto_request_material ,DECODE(p_new_comp_item.auto_request_material
,fnd_api.g_miss_char ,bic.auto_request_material ,p_new_comp_item.auto_request_material)) auto_request_material
,DECODE(p_new_comp_item ,NULL ,bic.suggested_vendor_name ,DECODE(p_new_comp_item.suggested_vendor_name
,fnd_api.g_miss_char ,bic.suggested_vendor_name ,p_new_comp_item.suggested_vendor_name)) suggested_vendor_name
,DECODE(p_new_comp_item ,NULL ,bic.unit_price ,DECODE(p_new_comp_item.unit_price
,fnd_api.g_miss_num ,bic.unit_price ,p_new_comp_item.unit_price)) unit_price
,DECODE(p_new_comp_item ,NULL ,bic.original_system_reference ,DECODE(p_new_comp_item.original_system_reference
,fnd_api.g_miss_num ,bic.original_system_reference ,p_new_comp_item.original_system_reference)) original_system_reference
,DECODE(p_new_comp_item ,NULL ,SYSDATE ,DECODE(p_new_comp_item.start_effective_date
,fnd_api.g_miss_date ,bic.effectivity_date ,p_new_comp_item.start_effective_date)) start_effective_date
,DECODE(p_new_comp_item ,NULL ,bic.item_num ,DECODE(p_new_comp_item.item_sequence_number
,fnd_api.g_miss_num ,bic.item_num ,p_new_comp_item.item_sequence_number)) item_sequence_number
,DECODE(p_new_comp_item ,NULL ,bic.planning_factor ,DECODE(p_new_comp_item.planning_percent
,fnd_api.g_miss_num ,bic.planning_factor ,p_new_comp_item.planning_percent)) planning_percent
,DECODE(p_new_comp_item ,NULL ,bic.component_yield_factor ,DECODE(p_new_comp_item.projected_yield
,fnd_api.g_miss_num ,bic.component_yield_factor ,p_new_comp_item.projected_yield)) projected_yield
,DECODE(p_new_comp_item ,NULL ,bic.high_quantity ,DECODE(p_new_comp_item.maximum_allowed_quantity
,fnd_api.g_miss_num ,bic.high_quantity ,p_new_comp_item.maximum_allowed_quantity)) maximum_allowed_quantity
,DECODE(p_new_comp_item ,NULL ,bic.low_quantity ,DECODE(p_new_comp_item.minimum_allowed_quantity
,fnd_api.g_miss_num ,bic.low_quantity ,p_new_comp_item.minimum_allowed_quantity)) minimum_allowed_quantity
,DECODE(p_new_comp_item ,NULL ,bic.component_remarks ,DECODE(p_new_comp_item.comments
,fnd_api.g_miss_char ,component_remarks ,p_new_comp_item.comments)) comments
,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.new_effectivity_date) new_effectivity_date
,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.old_effectivity_date) old_effectivity_date
,DECODE(p_new_comp_item ,NULL ,1 ,p_new_comp_item.old_operation_sequence_number) old_operation_sequence_number
,DECODE(p_new_comp_item ,NULL ,1 ,p_new_comp_item.new_operation_sequence_number) new_operation_sequence_number
,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.location_name) location_name
,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.cancel_comments) cancel_comments
,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.old_from_end_item_unit_number) old_from_end_item_unit_number
,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.new_from_end_item_unit_number) new_from_end_item_unit_number
,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.new_routing_revision) new_routing_revision
,DECODE(p_new_comp_item ,NULL ,NULL ,p_new_comp_item.return_status) return_status
FROM
bom_inventory_components bic,
mtl_system_items_kfv it
WHERE
bic.component_item_id = it.inventory_item_id AND
it.organization_id = p_reference_org_id AND
it.concatenated_segments = p_component_item_name AND
bic.operation_seq_num = nvl(p_operation_sequence_number,1) AND
bic.bill_sequence_id = p_bill_sequence_id;
SELECT
bom_item_type
,DECODE (base_item_id, NULL, -1, 0) base_item_id
INTO
l_bom_item_type
,l_base_item_flag
FROM
mtl_system_items_b
WHERE
inventory_item_id = p_from_item_id AND
organization_id = p_reference_org_id;
IF p_revised_item.component_item_tbl(i).transaction_type <> ENG_GLOBALS.g_opr_delete THEN
l_revised_comp_tbl.EXTEND(1);
IF l_revised_item.component_item_tbl(i).transaction_type = ENG_GLOBALS.g_opr_update THEN
OPEN c_merged_component(p_new_comp_item => l_revised_item.component_item_tbl(i)
,p_component_item_name => l_revised_item.component_item_tbl(i).component_item_name
,p_operation_sequence_number => l_revised_item.component_item_tbl(i).operation_sequence_number
,p_bill_sequence_id => p_from_sequence_id);
SELECT
COUNT(1)
INTO
l_Count
FROM
bom_bill_of_materials bb,
mtl_system_items_kfv it,
mtl_parameters mp
WHERE
bb.assembly_item_id = it.inventory_item_id AND
it.organization_id = bb.organization_id AND
bb.organization_id = mp.organization_id AND
mp.organization_code = p_Organization_Code AND
it.concatenated_segments = p_Item_Number AND
((p_alternate_bom_code IS NULL AND bb.alternate_bom_designator IS NULL) OR
(bb.alternate_bom_designator = p_alternate_bom_code));
SELECT bill_sequence_id, source_bill_sequence_id INTO l_bill_sequence_id, l_src_bill_sequence_id
FROM BOM_BILL_OF_MATERIALS bb,
mtl_system_items_kfv it,
mtl_parameters mp
WHERE bb.assembly_item_id = it.inventory_item_id
AND it.organization_id = bb.organization_id
AND bb.organization_id = mp.organization_id
AND mp.organization_code = p_Organization_Code
AND it.concatenated_segments = p_Item_Number
AND NVL(bb.alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
SELECT COUNT(1) INTO l_Count
FROM
eng_revised_items eri,
mtl_system_items_kfv msi,
mtl_parameters mp
WHERE
eri.revised_item_id = msi.inventory_item_id AND
msi.organization_id = eri.organization_id AND
eri.organization_id = mp.organization_id AND
mp.organization_code = p_org_code AND
msi.concatenated_segments = p_item_number AND
eri.new_item_revision = p_revision ;
SELECT master_organization_id INTO l_master_org
FROM mtl_parameters
WHERE organization_id= p_organization_id;
SELECT bill_sequence_id INTO l_bill_sequence_id
FROM
bom_bill_of_materials
WHERE
assembly_item_id = p_assembly_item_id AND
organization_id = p_organization_id AND
NVL(alternate_bom_designator, 'NONE') =
decode(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code);
* Remove the components which have transaction type as 'DELETE'
* Find the components which are present only in the reference organization
and add them to the list with transaction type as 'CREATE'
* Modify the transaction type of the components which have 'UPDATE' as the
transaction type to 'CREATE'
-- 4. For each component item the above processing will be done for substitute
components and reference designators
************************************************************************************/
PROCEDURE process_replicate_bom(
p_organization_code IN VARCHAR2
,p_revised_item_obj IN inv_ebi_revised_item_obj
,p_name_value_tbl IN inv_ebi_name_value_tbl
,x_revised_item_obj OUT NOCOPY inv_ebi_revised_item_obj
,x_out OUT NOCOPY inv_ebi_eco_output_obj
) IS
l_revised_item inv_ebi_revised_item_obj;
SELECT
organization_code
INTO
l_reference_org_code
FROM
mtl_parameters
WHERE
organization_id = l_from_org_id;
SELECT
concatenated_segments
INTO
l_reference_item_num
FROM
mtl_system_items_kfv
WHERE
inventory_item_id = l_from_item_id AND
organization_id = l_from_org_id;
SELECT
bill_sequence_id
INTO
l_from_sequence_id
FROM
bom_bill_of_materials
WHERE
assembly_item_id = l_from_item_id AND
organization_id = l_from_org_id AND
((l_revised_item.alternate_bom_code IS NULL AND alternate_bom_designator IS NULL) OR
(alternate_bom_designator = l_revised_item.alternate_bom_code));
SELECT bill_sequence_id,structure_type_id INTO l_bill_sequence_id,l_structure_type_id
FROM bom_bill_of_materials
WHERE assembly_item_id = l_assembly_item_id
AND organization_id = l_organization_id
AND NVL(alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
SELECT component_sequence_id INTO l_component_sequence_id
FROM bom_components_b
WHERE bill_sequence_id = l_bill_sequence_id
AND component_item_id = l_component_item_id
AND change_notice = p_eco_name;
SELECT bill_sequence_id,structure_type_id INTO l_bill_sequence_id,l_structure_type_id
FROM bom_bill_of_materials
WHERE assembly_item_id = l_assembly_item_id
AND organization_id = l_organization_id
AND NVL(alternate_bom_designator, 'NONE') = DECODE(p_alternate_bom_code,FND_API.G_MISS_CHAR,'NONE',NULL,'NONE',p_alternate_bom_code) ;
SELECT count(1)
INTO l_count
FROM eng_change_type_processes
WHERE change_order_type_id = p_change_order_type_id
AND NVL(eng_change_priority_code,'X') = NVL(p_priority_code, 'X');
SELECT sequence_number, change_lifecycle_status_id,change_wf_route_template_id, workflow_status, change_wf_route_id
FROM eng_lifecycle_statuses
WHERE entity_name = 'ENG_CHANGE_TYPE'
AND entity_id1 = p_change_id
ORDER BY sequence_number;
SELECT COUNT(1) INTO l_count
FROM
eng_change_tasks_vl tsk,
eng_change_type_org_tasks typtsk
WHERE
tsk.organization_id = typtsk.organization_id AND
typtsk.organization_id = p_organization_id AND
tsk.change_template_id = typtsk.change_template_or_task_id AND
typtsk.template_or_task_flag ='E' AND
typtsk.change_type_id = p_change_order_type_id AND
typtsk.complete_before_status_code = p_status_code;
SELECT meaning
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
, p_bom_update_without_eco IN VARCHAR2
, p_allow_unimp_bom_updates IN VARCHAR2
, p_effectivity_date IN DATE
, x_old_effectivity_date OUT NOCOPY DATE
, x_old_op_sequence_num OUT NOCOPY VARCHAR2
, x_old_fm_end_item_unit OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Bill_seq (p_assembly_item_id NUMBER) IS
SELECT bill_sequence_id
FROM bom_bill_of_materials
WHERE assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND nvl(alternate_bom_designator,'x') = nvl(p_alternate_bom_code,'x');
SELECT bic.effectivity_date, bic.operation_seq_num, bic.from_end_item_unit_number
FROM bom_inventory_components bic
,eng_revised_items eri
WHERE eri.revised_item_id = p_rev_item_id
AND eri.organization_id = p_organization_id
AND eri.bill_sequence_id = p_bill_sequence_id
AND bic.component_item_id = p_component_item_id
AND bic.operation_seq_num = p_op_sequence_number
AND bic.bill_sequence_id = eri.bill_sequence_id
AND bic.revised_item_sequence_id = eri.revised_item_sequence_id
AND eri.scheduled_date = (SELECT MAX(erj.scheduled_date)
FROM bom_inventory_components bcc
,eng_revised_items erj
WHERE erj.revised_item_id = p_rev_item_id
AND erj.organization_id = p_organization_id
AND erj.bill_sequence_id = p_bill_sequence_id
AND bcc.component_item_id = p_component_item_id
AND bcc.operation_seq_num = p_op_sequence_number
AND bcc.bill_sequence_id = erj.bill_sequence_id
AND bcc.revised_item_sequence_id = erj.revised_item_sequence_id
--Start of Bug 13587336
AND ( bic.disable_date > p_effectivity_date OR bic.disable_date IS NULL)
AND bic.effectivity_date <= p_effectivity_date);
SELECT bic.effectivity_date, bic.operation_seq_num, bic.from_end_item_unit_number
FROM bom_inventory_components bic
,eng_revised_items eri
WHERE eri.revised_item_id = p_rev_item_id
AND eri.organization_id = p_organization_id
AND eri.bill_sequence_id = p_bill_sequence_id
AND bic.component_item_id = p_component_item_id
AND bic.operation_seq_num = p_op_sequence_number
AND bic.bill_sequence_id = eri.bill_sequence_id
AND bic.revised_item_sequence_id = eri.revised_item_sequence_id
AND eri.implementation_date = (SELECT MAX(erj.implementation_date)
FROM bom_inventory_components bcc
,eng_revised_items erj
WHERE erj.revised_item_id = p_rev_item_id
AND erj.organization_id = p_organization_id
AND erj.bill_sequence_id = p_bill_sequence_id
AND bcc.component_item_id = p_component_item_id
AND bcc.operation_seq_num = p_op_sequence_number
AND bcc.bill_sequence_id = erj.bill_sequence_id
AND bcc.revised_item_sequence_id = erj.revised_item_sequence_id
AND erj.implementation_date IS NOT NULL
--Start of Bug 13587336
AND ( bic.disable_date > p_effectivity_date OR bic.disable_date IS NULL)
AND bic.effectivity_date <= p_effectivity_date);
SELECT bic.effectivity_date, bic.operation_seq_num, bic.from_end_item_unit_number
FROM bom_inventory_components bic
WHERE bic.component_item_id = p_component_item_id
AND bic.bill_sequence_id = p_bill_sequence_id
AND bic.operation_seq_num = p_op_sequence_number
--Start of Bug 13351524
AND ( bic.disable_date > p_effectivity_date OR bic.disable_date IS NULL)
AND bic.effectivity_date <= p_effectivity_date
ORDER BY bic.effectivity_date DESC;
l_allow_unimp_bom_update VARCHAR2(1) := FND_API.G_FALSE;
INV_EBI_UTIL.debug_line('STEP 30: p_allow_unimp_bom_updates ' || p_allow_unimp_bom_updates);
l_allow_unimp_bom_update := p_allow_unimp_bom_updates;
IF (l_allow_unimp_bom_update IS NULL OR l_allow_unimp_bom_update = fnd_api.g_miss_char) THEN
l_allow_unimp_bom_update := INV_EBI_FLAGS.get_allow_unimp_bom_updates;
INV_EBI_UTIL.debug_line('STEP 40: l_allow_unimp_bom_update ' || l_allow_unimp_bom_update);
IF( l_allow_unimp_bom_update = FND_API.G_FALSE) THEN
--To Retrive old effectivity date if BOM Components are implemented
INV_EBI_UTIL.debug_line('STEP 70: Opening Cursor for implemented BOM Components');
IF(l_eco_exist <> FND_API.G_TRUE AND p_bom_update_without_eco = FND_API.G_TRUE) THEN
/*SELECT COUNT(1) INTO l_count
FROM eng_revised_items
WHERE revised_item_id = l_assembly_item_id
AND organization_id = p_organization_id;*/ -- Commented this query for Bug 9527466
INV_EBI_UTIL.debug_line('STEP 110:BOM exists without Change Order and p_bom_update_without_eco ' || p_bom_update_without_eco);
SELECT COUNT(1) INTO l_count
FROM eng_revised_items eri,
bom_inventory_components bic
WHERE eri.revised_item_id = l_assembly_item_id
AND eri.organization_id = p_organization_id
AND eri.bill_sequence_id = l_bill_sequence_id
AND bic.component_item_id = l_component_item_id
AND bic.operation_seq_num = p_op_sequence_number
AND bic.bill_sequence_id = eri.bill_sequence_id
AND bic.revised_item_sequence_id = eri.revised_item_sequence_id
AND bic.change_notice IS NOT NULL;
SELECT COUNT(1) INTO l_count
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = l_bill_sequence_id
AND bic.component_item_id = l_component_item_id
AND bic.operation_seq_num = p_op_sequence_number
AND bic.change_notice IS NULL;
SELECT
revision
FROM
mtl_item_revisions_b
WHERE
inventory_item_id = p_inventory_item_id AND
organization_id = p_organization_id AND
effectivity_date <= p_revision_date AND
implementation_date IS NOT NULL
ORDER BY
effectivity_date DESC, revision DESC;
SELECT
revision,
revision_id ,
revision_label,
revision_reason,
description,
attribute_category,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM
mtl_item_revisions_b
WHERE
inventory_item_id = p_inventory_item_id AND
organization_id = p_organization_id AND
effectivity_date <= p_revision_date AND
implementation_date IS NOT NULL
ORDER BY
effectivity_date DESC, revision DESC;
SELECT item_catalog_group_id, approval_status
INTO l_item_catalog_group_id, l_approval_status
FROM mtl_system_items_kfv
WHERE organization_id = l_master_org
AND concatenated_segments = p_item_name;
SELECT COUNT(1) INTO l_count
FROM
mtl_item_revisions_b mir,
mtl_system_items_kfv msi
WHERE
mir.organization_id = msi.organization_id AND
msi.organization_id = p_organization_id AND
mir.inventory_item_id = msi.inventory_item_id AND
msi.concatenated_segments = p_item_name AND
mir.revision = l_master_item_rev.revision;
SELECT type_name
INTO l_change_type_code
FROM eng_change_order_types_vl
WHERE change_order_type_id = p_change_type_id
AND change_mgmt_type_code = 'CHANGE_ORDER'
AND type_classification='HEADER';
SELECT status_name
INTO l_status_name
FROM eng_change_statuses_vl
WHERE status_code = p_status_code;
SELECT
msi.concatenated_segments component_item_name ,
bic.component_item_id
FROM
bom_inventory_components bic,
mtl_system_items_kfv msi
WHERE
bic.bill_sequence_id = p_src_bill_sequence_id AND
bic.component_item_id = msi.inventory_item_id AND
msi.organization_id = p_src_organization_id AND
NVL(bic.disable_date, sysdate + 1) >= sysdate AND
NOT EXISTS
( SELECT 'x'
FROM mtl_system_items_kfv s
WHERE s.organization_id = p_organization_id
AND s.inventory_item_id = bic.component_item_id
AND ((p_assy_type = 1 AND s.eng_item_flag = 'N')
OR (p_assy_type = 2)
)
AND s.inventory_item_id <> p_assembly_item_id
AND ((p_item_type = 1 AND s.bom_item_type <> 3)
OR (p_item_type = 2 AND s.bom_item_type <> 3)
OR (p_item_type = 3)
OR (p_item_type = 4
AND (s.bom_item_type = 4
OR(
s.bom_item_type IN (2, 1)
AND s.replenish_to_order_flag = 'Y'
AND p_base_item_flag IS NOT NULL
AND p_replenish_to_order_flag = 'Y'
)
)
)
)
AND (p_item_type = 3
OR
p_pick_components_flag = 'Y'
OR
s.pick_components_flag = 'N'
)
AND (p_item_type = 3
OR
NVL(s.bom_item_type, 4) <> 2
OR
(s.bom_item_type = 2
AND (( p_pick_components_flag = 'Y'
AND s.pick_components_flag = 'Y'
)
OR ( p_replenish_to_order_flag = 'Y'
AND s.replenish_to_order_flag = 'Y'
)
)
)
)
AND NOT( p_item_type = 4
AND p_pick_components_flag = 'Y'
AND s.bom_item_type = 4
AND s.replenish_to_order_flag = 'Y'
)
);
SELECT
msi.concatenated_segments sub_comp_item_name ,
bsc.substitute_component_id
FROM bom_inventory_components bic,
bom_substitute_components bsc,
mtl_system_items_kfv msi
WHERE bic.bill_sequence_id = p_src_bill_sequence_id
AND bic.component_sequence_id = bsc.component_sequence_id
AND bsc.substitute_component_id = msi.inventory_item_id
AND msi.organization_id = p_src_organization_id
AND bsc.substitute_component_id NOT IN
(SELECT msi1.inventory_item_id
FROM mtl_system_items msi1, mtl_system_items msi2
WHERE msi1.organization_id = p_organization_id
AND msi1.inventory_item_id = bsc.substitute_component_id
AND msi2.organization_id = p_src_organization_id
AND msi2.inventory_item_id = msi1.inventory_item_id
AND ((p_assy_type = 1 AND msi1.eng_item_flag = 'N')
OR (p_assy_type = 2)
)
AND msi1.inventory_item_id <> p_assembly_item_id
AND ( (p_item_type = 1 AND msi1.bom_item_type <> 3)
OR (p_item_type = 2 AND msi1.bom_item_type <> 3)
OR (p_item_type = 3)
OR ( p_item_type = 4
AND ( msi1.bom_item_type = 4
OR ( msi1.bom_item_type IN (2, 1)
AND msi1.replenish_to_order_flag = 'Y'
AND p_base_item_flag IS NOT NULL
AND p_replenish_to_order_flag = 'Y'
)
)
)
)
AND (p_item_type = 3
OR
p_pick_components_flag = 'Y'
OR
msi1.pick_components_flag = 'N'
)
AND (p_item_type = 3
OR
NVL(msi1.bom_item_type, 4) <> 2
OR
(msi1.bom_item_type = 2
AND (( p_pick_components_flag = 'Y'
AND msi1.pick_components_flag = 'Y'
)
OR ( p_replenish_to_order_flag = 'Y'
AND msi1.replenish_to_order_flag = 'Y'
)
)
)
)
AND NOT( p_item_type = 4
AND p_pick_components_flag = 'Y'
AND msi1.bom_item_type = 4
AND msi1.replenish_to_order_flag = 'Y'
)
);
SELECT
bom_item_type,
base_item_id,
replenish_to_order_flag,
pick_components_flag ,
DECODE(eng_item_flag, 'Y', 2, 1)
INTO
l_bom_item_type,
l_base_item_flag,
l_replenish_to_order_flag,
l_pick_components_flag,
l_assm_type
FROM
mtl_system_items
WHERE
inventory_item_id = p_assembly_item_id AND
organization_id = p_organization_id;
SELECT organization_id
FROM bom_bill_of_materials
WHERE
common_bill_sequence_id = p_bill_sequence_id AND
common_assembly_item_id = p_common_assy_item_id AND
common_organization_id = p_common_org_id;
l_acd_update CONSTANT NUMBER :=2;
l_acd_delete CONSTANT NUMBER :=3;
l_bom_update_without_eco VARCHAR2(1) := FND_API.G_TRUE;
l_allow_unimp_bom_update VARCHAR2(1) := NULL;
ELSIF(UPPER(p_name_val_list.name_value_table(i).param_name) = G_BOM_UPDATES_ALLOWED) THEN
l_bom_update_without_eco := p_name_val_list.name_value_table(i).param_value;
ELSIF(UPPER(p_name_val_list.name_value_table(i).param_name) = G_ALLOW_UNIMP_BOM_UPDATES) THEN
l_allow_unimp_bom_update := p_name_val_list.name_value_table(i).param_value;
SELECT status_name, status_type
INTO l_eco_rec.status_name, l_status_type
FROM eng_change_statuses_vl
WHERE status_code = 1; -- ECO Status Set to 'Open';
SELECT status_name, status_type
INTO l_eco_rec.status_name, l_status_type
FROM eng_change_statuses_vl
WHERE status_code = 4; -- Scheduled
l_revised_item_tbl(l_revised_item_tbl_count).updated_revised_item_revision := p_revised_item_type_tbl(i).upd_revised_item_revision;
l_revised_item_tbl(l_revised_item_tbl_count).update_wip := p_revised_item_type_tbl(i).up_wip ;
l_revised_item_tbl(l_revised_item_tbl_count).updated_routing_revision := p_revised_item_type_tbl(i).upd_routing_revision ;
l_revised_item_tbl(l_revised_item_tbl_count).selection_option := p_revised_item_type_tbl(i).selection_option ;
l_revised_item_tbl(l_revised_item_tbl_count).selection_date := p_revised_item_type_tbl(i).selection_date ;
l_revised_item_tbl(l_revised_item_tbl_count).selection_unit_number := p_revised_item_type_tbl(i).selection_unit_number;
IF l_rev_component_tbl(l_rev_component_tbl_count).acd_type IN (l_acd_update,l_acd_delete)
THEN
INV_EBI_UTIL.debug_line('STEP 70: BEFORE CALLING INV_EBI_CHANGE_ORDER_HELPER.get_existing_component_attr '|| ' ORG ID: '|| l_organization_id ||
' REVISED ITEM NAME: '|| l_rev_component_tbl(l_rev_component_tbl_count).revised_item_name ||
' COMPONENT ITEM NAME: '|| l_rev_component_tbl(l_rev_component_tbl_count).component_item_name);
,p_bom_update_without_eco => l_bom_update_without_eco -- Bug 8340804
,p_allow_unimp_bom_updates => l_allow_unimp_bom_update -- Bug 10423301
,p_effectivity_date => l_rev_component_tbl(l_rev_component_tbl_count).start_effective_date -- Bug 8340804
,x_old_effectivity_date => l_old_effectivity_date
,x_old_op_sequence_num => l_old_op_sequence_num
,x_old_fm_end_item_unit => l_old_fm_end_item_unit
);
SELECT change_id ,change_notice INTO x_out.change_id,x_out.change_notice
FROM eng_engineering_changes
WHERE change_notice = l_eco_rec.eco_name
AND organization_id = l_organization_id;
l_transaction_type := INV_EBI_ITEM_PUB.g_otype_update;
l_bom_header_rec.delete_group_name := p_structure_header.delete_group_name ;
l_bom_header_rec.enable_attrs_update := p_structure_header.enable_attrs_update ;
IF(l_transaction_type = INV_EBI_ITEM_PUB.g_otype_update) THEN
l_bom_already_commoned := is_bom_already_commoned(
p_item_number => p_assembly_item_name,
p_organization_code => p_organization_code,
p_alternate_bom_code => p_alternate_bom_code
);
,p_last_update_status IN VARCHAR2
,p_revised_item_sequence_id IN NUMBER
,p_name_val_list IN inv_ebi_name_value_list
,x_eco_obj OUT NOCOPY inv_ebi_eco_obj
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_eco_change_order_obj inv_ebi_change_order_obj;
SELECT
eec.change_notice
,eec.change_order_type_id
,eec.change_notice_prefix
,eec.change_notice_number
,mp.organization_code
,mp.organization_id
,eec.change_name
,eec.description
,eec.cancellation_comments
,eec.status_code
,ecsv.status_name
,eec.priority_code
,eec.reason_code
,eec.estimated_eng_cost
,eec.estimated_mfg_cost
,eec.attribute_category
,eec.attribute1
,eec.attribute2
,eec.attribute3
,eec.attribute4
,eec.attribute5
,eec.attribute6
,eec.attribute7
,eec.attribute8
,eec.attribute9
,eec.attribute10
,eec.attribute11
,eec.attribute12
,eec.attribute13
,eec.attribute14
,eec.attribute15
,eec.ddf_context
,eeal.approval_list_name
,eec.approval_date
,eec.approval_request_date
,eec.change_mgmt_type_code
,eec.original_system_reference
,eec.organization_hierarchy
,hp.party_name assignee
,ppa.name project_name
,ppe.name task_number
,eec.source_type_code
,eec.source_name
,eec.need_by_date
,eec.effort
,haou.name eco_department_name
,eec.internal_use_only
,eec.plm_or_erp_change
,eec.status_type
,eec.implementation_date
,eec.cancellation_date
,ecot.type_name
FROM
eng_engineering_changes eec
,mtl_parameters mp
,eng_change_statuses_vl ecsv
,eng_ecn_approval_lists eeal
,hz_parties hp
,pa_projects_all ppa
,pa_proj_elements ppe
,hr_all_organization_units_vl haou
,eng_change_order_types_vl ecot
WHERE
eec.change_id = p_change_id AND
eec.change_order_type_id =ecot.change_order_type_id AND
mp.organization_id = eec.organization_id AND
ecsv.status_code(+) = eec.status_code AND
eeal.approval_list_id(+) = eec.approval_list_id AND
hp.party_id(+) = eec.assignee_id AND
ppa.project_id(+) = eec.project_id AND
ppe.proj_element_id(+) = eec.task_id AND
haou.organization_id(+) = eec.responsible_organization_id;
SELECT
ecor.revision
,ecor.comments
,ecor.attribute_category
,ecor.attribute1
,ecor.attribute2
,ecor.attribute3
,ecor.attribute4
,ecor.attribute5
,ecor.attribute6
,ecor.attribute7
,ecor.attribute8
,ecor.attribute9
,ecor.attribute10
,ecor.attribute11
,ecor.attribute12
,ecor.attribute13
,ecor.attribute14
,ecor.attribute15
,eec.change_mgmt_type_code
,ecor.original_system_reference
FROM
eng_change_order_revisions ecor
,eng_engineering_changes eec
WHERE
ecor.change_id = eec.change_id AND
ecor.change_id = p_change_id;
SELECT
mif.item_number substitute_component_name
,bsc.acd_type
,bsc.substitute_item_quantity
,bsc.attribute_category
,bsc.attribute1
,bsc.attribute2
,bsc.attribute3
,bsc.attribute4
,bsc.attribute5
,bsc.attribute6
,bsc.attribute7
,bsc.attribute8
,bsc.attribute9
,bsc.attribute10
,bsc.attribute11
,bsc.attribute12
,bsc.attribute13
,bsc.attribute14
,bsc.attribute15
,bsc.original_system_reference
,bsc.enforce_int_requirements
,bsc.program_id
FROM
bom_inventory_components bic
,eng_revised_items eri
,mtl_item_flexfields mif
,bom_substitute_components bsc
WHERE
eri.change_id = p_change_id AND
eri.revised_item_id = p_revised_item_id AND
bic.component_item_id = p_component_id AND
bic.revised_item_sequence_id(+) = eri.revised_item_sequence_id AND
bsc.component_sequence_id = bic.component_sequence_id AND
mif.inventory_item_id = bsc.substitute_component_id AND
mif.organization_id = eri.organization_id;
SELECT
brd.component_reference_designator reference_designator_name
,brd.acd_type
,brd.ref_designator_comment
,brd.attribute_category
,brd.attribute1
,brd.attribute2
,brd.attribute3
,brd.attribute4
,brd.attribute5
,brd.attribute6
,brd.attribute7
,brd.attribute8
,brd.attribute9
,brd.attribute10
,brd.attribute11
,brd.attribute12
,brd.attribute13
,brd.attribute14
,brd.attribute15
,brd.original_system_reference
FROM
bom_inventory_components bic
,eng_revised_items eri
,bom_reference_designators brd
WHERE
eri.change_id = p_change_id AND
eri.revised_item_id = p_revised_item_id AND
bic.component_item_id = p_component_id AND
bic.revised_item_sequence_id(+)=eri.revised_item_sequence_id AND
brd.component_sequence_id(+)=bic.component_sequence_id;
SELECT
bic.component_item_id
,bic.disable_date
,bic.operation_seq_num
,mif.item_number component_item_name
,bic.acd_type
,bic.basis_type
,bic.component_quantity
,bic.component_quantity inverse_quantity
,bic.include_in_cost_rollup
,bic.wip_supply_type
,bic.so_basis
,bic.optional
,bic.mutually_exclusive_options
,bic.check_atp
,bic.shipping_allowed
,bic.required_to_ship
,bic.required_for_revenue
,bic.include_on_ship_docs
,bic.quantity_related
,bic.supply_subinventory
,bic.attribute_category
,bic.attribute1
,bic.attribute2
,bic.attribute3
,bic.attribute4
,bic.attribute5
,bic.attribute6
,bic.attribute7
,bic.attribute8
,bic.attribute9
,bic.attribute10
,bic.attribute11
,bic.attribute12
,bic.attribute13
,bic.attribute14
,bic.attribute15
,bic.from_end_item_unit_number
,bic.to_end_item_unit_number
,bic.enforce_int_requirements
,bic.auto_request_material
,bic.suggested_vendor_name
,bic.unit_price
,bic.original_system_reference
FROM
bom_inventory_components bic
,eng_revised_items eri
,mtl_item_flexfields mif
WHERE
eri.change_id = p_change_id AND
eri.revised_item_id = p_revised_item_id AND
bic.revised_item_sequence_id(+)=eri.revised_item_sequence_id AND
mif.inventory_item_id=bic.component_item_id AND
mif.organization_id=eri.organization_id;
SELECT
eri.revised_item_id
,mif.item_number revised_item_name
,eri.new_item_revision
,eri.alternate_bom_designator
,eri.status_code
,eri.status_type
,eri.mrp_active
,mif1.item_number use_up_item_name
,eri.use_up_plan_name
,eri.disposition_type
,eri.update_wip
,eri.cancel_comments
,eri.attribute_category
,eri.attribute1
,eri.attribute2
,eri.attribute3
,eri.attribute4
,eri.attribute5
,eri.attribute6
,eri.attribute7
,eri.attribute8
,eri.attribute9
,eri.attribute10
,eri.attribute11
,eri.attribute12
,eri.attribute13
,eri.attribute14
,eri.attribute15
,eri.scheduled_date
,eri.from_end_item_unit_number
,eri.original_system_reference
,eri.from_cum_qty
,eri.lot_number
,eri.completion_subinventory
,eri.priority
,eri.ctp_flag
,eri.new_routing_revision
,eri.routing_comment
,eri.eco_for_production
,eri.transfer_or_copy
,eri.transfer_or_copy_item
,eri.transfer_or_copy_bill
,eri.transfer_or_copy_routing
,eri.copy_to_item
,eri.copy_to_item_desc
,eri.selection_option
,eri.selection_date
,eri.selection_unit_number
,eri.enable_item_in_local_org
,eri.create_bom_in_local_org
,eri.new_structure_revision
,eri.plan_level
,eri.new_revision_label
,eri.new_revision_reason
,eri.revised_item_sequence_id
,eriv.revised_item_status
,eri.organization_id
FROM
eng_revised_items eri
,mtl_item_flexfields mif
,mtl_item_flexfields mif1
,eng_revised_items_v eriv
WHERE
eri.change_id = p_change_id AND
mif.inventory_item_id = eri.revised_item_id AND
mif.organization_id = eri.organization_id AND
mif1.inventory_item_id(+) = eri.use_up_item_id AND
eri.revised_item_sequence_id = eriv.revised_item_sequence_id AND
mif1.organization_id(+) = eri.organization_id;
SELECT
msl.concatenated_segments
,bev.common_organization_name
,bev.assembly_type
,bev.attribute1
,bev.attribute2
,bev.attribute3
,bev.attribute4
,bev.attribute5
,bev.attribute6
,bev.attribute7
,bev.attribute8
,bev.attribute9
,bev.attribute10
,bev.attribute11
,bev.attribute12
,bev.attribute13
,bev.attribute14
,bev.attribute15
,bev.bom_implementation_date
,bst.structure_type_name
FROM
bom_explosions_v bev
,eng_revised_items eri
,bom_structure_types_vl bst
,mtl_system_items_vl msl
WHERE
eri.change_id = p_change_id AND
bev.assembly_item_id = p_revised_item_id AND
bev.access_flag = 'T' AND
bev.organization_id = eri.organization_id(+) AND
bst.structure_type_id = bev.structure_type_id AND
msl.inventory_item_id(+) = bev.assembly_item_id AND
msl.organization_id(+) = bev.organization_id;
SELECT
change_id
FROM
eng_Revised_items
WHERE
revised_item_sequence_id = p_revised_item_sequence_id;
IF NVL(p_last_update_status,'Y') = 'N' THEN
l_include_rev_items := fnd_api.g_false;
,ri.update_wip
,ri.cancel_comments
,NULL
,ri.attribute_category
,ri.attribute1
,ri.attribute2
,ri.attribute3
,ri.attribute4
,ri.attribute5
,ri.attribute6
,ri.attribute7
,ri.attribute8
,ri.attribute9
,ri.attribute10
,ri.attribute11
,ri.attribute12
,ri.attribute13
,ri.attribute14
,ri.attribute15
,ri.from_end_item_unit_number
,NULL
,ri.original_system_reference
,NULL
,NULL
,NULL
,NULL
,NULL
,ri.from_cum_qty
,ri.lot_number
,ri.completion_subinventory
,NULL
,ri.priority
,ri.ctp_flag
,ri.new_routing_revision
,NULL
,ri.routing_comment
,ri.eco_for_production
,NULL
,ri.transfer_or_copy
,ri.transfer_or_copy_item
,ri.transfer_or_copy_bill
,ri.transfer_or_copy_routing
,ri.copy_to_item
,ri.copy_to_item_desc
,NULL
,NULL
,ri.selection_option
,ri.selection_date
,ri.selection_unit_number
,NULL
,NULL
,NULL
,NULL
,ri.enable_item_in_local_org
,ri.create_bom_in_local_org
,ri.new_structure_revision
,ri.plan_level
,NULL
,NULL
,NULL
,NULL
,NULL
,ri.new_revision_label
,ri.new_revision_reason
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL);
SELECT mp.organization_code INTO l_org_code
FROM eng_engineering_changes ec, mtl_parameters mp
WHERE ec.change_id = p_eco_tbl(i).change_id
AND ec.organization_id = mp.organization_id;
INV_EBI_UTIL.debug_line('STEP 40: LAST UPDATE STATUS '|| x_eco_tbl(i).last_update_status);
SELECT change_id
FROM eng_engineering_changes
WHERE change_notice = p_chg_notice;
SELECT COUNT(1) into l_entity_exist
FROM eng_engineering_changes
WHERE change_notice = l_eco_tbl(i);
SELECT COUNT(1) into l_entity_exist
FROM mtl_parameters
WHERE organization_code = l_org_tbl(i);
SELECT change_id
INTO l_chg_id
FROM eng_engineering_changes
WHERE change_notice = l_valid_eco_tbl(i)
AND organization_id = l_org_id;
INV_EBI_UTIL.debug_line('STEP 50: LAST UPDATE STATUS '|| x_eco_tbl(i).LAST_UPDATE_STATUS);
SELECT inv_ebi_change_id_obj(eci.change_id, 'Y')
FROM(
SELECT eec.change_id
FROM eng_engineering_changes eec
WHERE eec.last_update_date <> eec.creation_date
AND eec.last_update_date >= l_from_date
AND eec.last_update_date <= l_to_date
UNION
SELECT eri.change_id
FROM eng_revised_items eri
WHERE eri.last_update_date <> eri.creation_date
AND eri.last_update_date >= l_from_date
AND eri.last_update_date <= l_to_date ) eci;
SELECT inv_ebi_change_id_obj(geco.change_id,geco.last_update_status)
FROM (SELECT b.change_id,b.last_update_status
FROM THE (SELECT CAST( l_eco as inv_ebi_change_id_obj_tbl)
FROM dual ) b
INTERSECT
SELECT c.change_id,c.last_update_status
FROM THE (SELECT CAST( l_eco_output_tbl as inv_ebi_change_id_obj_tbl)
FROM dual ) c ) geco;
l_last_x_hrs := INV_EBI_UTIL.get_config_param_value(p_name_value_list,'Updated in the last X Hrs');
INV_EBI_UTIL.debug_line('STEP 60: INPUT PARAMETER FOR LAST UPDATED HRS IS '|| l_last_x_hrs);
INV_EBI_UTIL.debug_line('STEP 190: CHANGE ID '|| x_eco(i).CHANGE_ID || ' LAST UPDATE STATUS ' ||x_eco(i).LAST_UPDATE_STATUS);