[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X' date_available FROM sys.dual
WHERE EXISTS (
SELECT 1 from BOM_Inventory_Components
WHERE Component_Item_Id = X_Member_Item_Id
AND Bill_Sequence_Id = X_Bill_Sequence_Id
AND (( RowId <> X_RowID ) or
(X_RowId IS NULL))
AND ( X_Disable_Date IS NULL
OR ( Trunc(X_Disable_Date) >
Trunc(Effectivity_Date)
)
)
AND ( Trunc(X_Effectivity_Date) <
Trunc(Disable_Date)
OR Disable_Date IS NULL
)
);
SELECT 'X' unit_available FROM sys.dual
WHERE EXISTS (
SELECT 1 from BOM_Inventory_Components
WHERE Component_Item_Id = X_Member_Item_Id
AND Bill_Sequence_Id = X_Bill_Sequence_Id
AND (RowId <> X_RowID
OR X_RowId IS NULL)
AND (X_To_End_Item_Number IS NULL
OR X_To_End_Item_Number >
From_End_Item_Unit_Number)
AND (X_From_End_Item_Number <
To_End_Item_Unit_Number
OR To_End_Item_Unit_Number IS NULL
)
);
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(3);
g_token_tbl.delete(2);
g_token_tbl.delete(2);
g_Token_Tbl.DELETE(2);
g_token_tbl.delete(3);
g_token_tbl.delete(2);
g_token_tbl.delete(2);
g_token_tbl.delete(2);
SELECT 'Valid'
INTO l_dummy
FROM bom_bill_of_materials
WHERE assembly_item_id =
g_rev_comp_unexp_rec.component_item_id
AND organization_id =
g_rev_comp_unexp_rec.organization_id;
SELECT locator_type
FROM mtl_item_sub_ast_trk_val_v
WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
AND organization_id = g_rev_comp_Unexp_rec.organization_id
AND secondary_inventory_name =
g_rev_component_rec.supply_subinventory;
SELECT locator_type
FROM mtl_item_sub_trk_val_v
WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
AND organization_id = g_rev_comp_Unexp_rec.organization_id
AND secondary_inventory_name =
g_rev_component_rec.supply_subinventory;
SELECT locator_type
FROM mtl_sub_ast_trk_val_v
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND secondary_inventory_name =
g_rev_component_rec.supply_subinventory;
SELECT locator_type
FROM mtl_subinventories_trk_val_v
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND secondary_inventory_name =
g_rev_component_rec.supply_subinventory;
SELECT DECODE(restrict_subinventories_code, 1, 'Y', 'N'),
inventory_asset_flag
INTO l_RestrictSubInventory,
l_InventoryAsset
FROM mtl_system_items
WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
AND organization_id = g_rev_comp_Unexp_rec.organization_id;
SELECT 'checking for duplicates' dummy
FROM sys.dual
WHERE EXISTS (
SELECT null
FROM mtl_item_locations
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND inventory_location_id = g_rev_comp_Unexp_rec.supply_locator_id
AND subinventory_code <> g_rev_component_rec.supply_subinventory);
SELECT stock_locator_control_code
INTO l_org_locator_control
FROM mtl_parameters
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id;
SELECT location_control_code
INTO l_item_locator_control
FROM mtl_system_items
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND inventory_item_id = g_rev_comp_Unexp_rec.component_item_id;
SELECT RESTRICT_LOCATORS_CODE
INTO l_item_loc_restricted
FROM mtl_system_items
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND inventory_item_id = g_rev_comp_Unexp_rec.component_item_id;
SELECT 'Valid'
INTO l_dummy
FROM mtl_item_locations mil,
mtl_secondary_locators msl
WHERE msl.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND msl.organization_id =
g_rev_comp_Unexp_rec.organization_id
AND msl.subinventory_code =
g_rev_component_rec.supply_subinventory
AND msl.secondary_locator =
g_rev_comp_Unexp_rec.supply_locator_id
AND mil.inventory_location_id = msl.secondary_locator
AND mil.organization_id =
msl.organization_id
AND NVL(mil.disable_date, SYSDATE+1) > SYSDATE ;
SELECT 'Valid'
INTO l_dummy
FROM mtl_item_locations mil
WHERE mil.subinventory_code =
g_rev_component_rec.supply_subinventory
AND mil.inventory_location_id =
g_rev_comp_Unexp_rec.supply_locator_id
AND mil.organization_id =
g_rev_comp_Unexp_rec.organization_id
AND NVL(mil.DISABLE_DATE, SYSDATE+1) > SYSDATE;
SELECT 'Valid' valid_op_seq
FROM bom_operational_routings bor,
bom_operation_sequences bos
WHERE bor.assembly_item_id = g_rev_comp_Unexp_rec.revised_item_id
AND bor.organization_id = g_rev_comp_Unexp_rec.organization_id
AND NVL(bor.alternate_routing_designator, 'NONE') =
NVL(g_rev_component_rec.alternate_bom_code, 'NONE')
AND bos.routing_sequence_id = bor.routing_sequence_id
AND bos.operation_seq_num =
decode( g_rev_component_rec.new_operation_sequence_number,
NULL,
g_rev_component_rec.Operation_Sequence_Number,
g_rev_component_rec.new_Operation_sequence_number
);
SELECT 'Already Used' op_seq_used
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = g_rev_comp_Unexp_rec.bill_sequence_id
AND bic.component_item_id = g_rev_comp_Unexp_rec.component_item_id
AND bic.operation_seq_num =
decode( g_rev_component_rec.new_operation_sequence_number,
NULL,
g_rev_component_rec.operation_sequence_number,
g_rev_component_rec.new_operation_sequence_number
);
-- Effectivity date so it cannot be inserted. So generate an error
-- hence, this function will return a false.
FOR l_valid_op IN Valid_Op_Seq LOOP
-- if operation_seq exists in Operation_Sequences then
-- verify that the same component does not already exist
-- for that bill with the same operation seq.
FOR l_Op_Seq_Used IN c_Op_Seq_Used LOOP
RETURN 2;
SELECT 'Valid' is_Valid
FROM mtl_system_items assy,
mtl_system_items comp
WHERE assy.organization_id = g_rev_comp_Unexp_rec.organization_id
AND assy.inventory_item_id = g_rev_comp_Unexp_rec.revised_item_id
AND comp.organization_id = g_rev_comp_Unexp_rec.organization_id
AND comp.inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
AND ( ( assy.bom_item_type IN ( l_Planning, l_Standard)
AND g_rev_component_rec.optional = 2 /* NO */
)
OR
( assy.bom_item_type IN ( l_Model, l_Option_Class)
AND assy.pick_components_flag = 'Y'
/* PTO Model or PTO Option Class */
AND comp.bom_item_type = l_Standard
AND comp.replenish_to_order_flag = 'Y'
AND comp.base_item_id IS NULL
AND g_rev_component_rec.Optional = 1
)
);
SELECT 1
FROM BOM_BILL_OF_MATERIALS bom
WHERE bom.common_bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bom.organization_id <> g_rev_comp_Unexp_rec.organization_id
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.organization_id = bom.organization_id
AND msi.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND msi.bom_enabled_flag = 'Y'
);
SELECT 1
FROM BOM_BILL_OF_MATERIALS bom
WHERE bom.common_bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bom.organization_id <> g_rev_comp_Unexp_rec.organization_id
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.organization_id = bom.organization_id
AND msi.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
);
SELECT 1
FROM BOM_BILL_OF_MATERIALS bom
WHERE bom.common_bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bom.organization_id <> g_rev_comp_Unexp_rec.organization_id
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.organization_id = bom.organization_id
AND msi.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND msi.bom_enabled_flag = 'Y'
AND (( bom.assembly_type = 1 AND
msi.eng_item_flag = 'N'
)
OR bom.assembly_type = 2
)
);
SELECT 1
FROM bom_bill_of_materials
WHERE assembly_item_id = g_Rev_Comp_Unexp_Rec.revised_item_id
AND organization_id = g_Rev_Comp_Unexp_Rec.Organization_Id
AND NVL(alternate_bom_designator, 'NONE') = 'NONE';
SELECT count(component_sequence_id) number_of_desgs
FROM bom_reference_designators
WHERE component_sequence_id = p_rev_comp_unexp_rec.component_sequence_id;
SELECT revised_item_id,
change_notice,
organization_id
FROM eng_revised_items
WHERE revised_item_sequence_id =
p_rev_comp_Unexp_rec.revised_item_sequence_id;
SELECT 'Valid'
FROM eng_revised_items eri
WHERE eri.revised_item_sequence_id =
p_rev_comp_Unexp_rec.revised_item_sequence_id
AND eri.bill_sequence_id IS NULL
AND NOT EXISTS (SELECT 1
FROM bom_bill_of_materials bom
WHERE bom.bill_sequence_id =
p_rev_comp_Unexp_rec.bill_sequence_id
);
SELECT 'Valid'
FROM BOM_inventory_components
WHERE item_num = p_rev_component_rec.item_sequence_number
AND bill_sequence_id = p_Rev_Comp_Unexp_rec.bill_sequence_id;
SELECT distinct 'I'
FROM fnd_product_installations
WHERE application_id = 300
AND status = 'I';
SELECT mi.replenish_to_order_flag, mi.pick_components_flag
FROM mtl_system_items mi, eng_revised_items eri
WHERE mi.inventory_item_id = eri.revised_item_id
AND mi.organization_id = eri.organization_id
AND eri.revised_item_sequence_id =
p_rev_comp_Unexp_rec.revised_item_sequence_id;
SELECT effectivity_control
FROM mtl_system_items
WHERE inventory_item_id = p_rev_comp_unexp_rec.revised_item_id
AND organization_id = p_rev_comp_unexp_rec.organization_id;
SELECT To_End_Item_Unit_Number
FROM BOM_Inventory_Components
WHERE component_sequence_id =
g_rev_comp_unexp_rec.old_component_sequence_id;
SELECT assy.bom_item_type,
assy.pick_components_flag,
assy.replenish_to_order_flag,
assy.wip_supply_type,
DECODE(NVL(assy.base_item_id, 0), 0 , 'N', 'Y'),
assy.eng_item_flag,
assy.atp_components_flag,
assy.atp_flag,
assy.bom_enabled_flag,
comp.bom_item_type,
comp.pick_components_flag,
comp.replenish_to_order_flag,
comp.wip_supply_type,
DECODE(NVL(comp.base_item_id, 0), 0 , 'N', 'Y'),
comp.eng_item_flag,
comp.atp_components_flag,
comp.atp_flag,
comp.bom_enabled_flag,
comp.ato_forecast_control
INTO g_Assy_Item_Type,
g_Assy_PTO_flag,
g_Assy_ATO_flag,
g_Assy_Wip_Supply_Type,
g_Assy_Config,
g_Assy_Eng_Flag,
g_Assy_ATP_Comp_flag,
g_Assy_ATP_Check_flag,
g_Assy_Bom_Enabled_flag,
g_Comp_Item_Type,
g_Comp_PTO_flag,
g_Comp_ATO_flag,
g_Comp_Wip_Supply_Type,
g_Comp_Config,
g_Comp_Eng_Flag,
g_Comp_ATP_Comp_flag,
g_Comp_ATP_Check_flag,
g_Comp_Bom_Enabled_flag,
g_Comp_ATO_Forecast_Control
FROM mtl_system_items assy,
mtl_system_items comp
WHERE assy.organization_id = g_rev_Comp_Unexp_Rec.Organization_Id
AND assy.inventory_item_id = g_rev_Comp_Unexp_Rec.revised_item_id
AND comp.organization_id = g_rev_Comp_Unexp_Rec.Organization_Id
AND comp.inventory_item_id = g_rev_Comp_Unexp_Rec.Component_item_id;
g_token_tbl.delete;
g_token_tbl.delete;
g_token_tbl.delete;
SELECT 'Valid'
INTO l_dummy
FROM eng_revised_items eri,
bom_bill_of_materials bom
WHERE eri.revised_item_sequence_id =
p_Rev_Comp_Unexp_Rec.revised_item_sequence_id
AND bom.bill_sequence_id = eri.bill_sequence_id
AND bom.common_bill_sequence_id <> bom.bill_sequence_id;
IF p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
THEN
--
-- Verify that the user is not trying to Update non-updateable columns
--
IF p_Old_Rev_Component_Rec.Shipping_Allowed <>
p_rev_component_rec.shipping_allowed
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_SHIP_ALLOWED_NOT_UPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => g_token_tbl
);
-- ACD Type not updateable
--
IF p_rev_component_rec.acd_type <>
p_old_rev_component_rec.acd_type
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_ACD_TYPE_NOT_UPDATEABLE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => g_token_tbl
);
-- Verify that the user is not trying to update a component which
-- is Disabled on the ECO
--
IF p_old_rev_component_rec.acd_type = 3
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_COMPONENT_DISABLED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => g_token_tbl
);
-- User cannot update to_end_item_unit_number when the component
-- is disabled.
IF p_rev_component_rec.acd_type = 3 AND
p_rev_component_rec.to_end_item_unit_number <>
p_old_rev_component_rec.to_end_item_unit_number
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_DISABLE_TOUNIT_NONUPD'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => g_token_tbl
);
END IF; /* Operation UPDATE ENDS */
(Bom_GLOBALS.G_OPR_CREATE, Bom_GLOBALS.G_OPR_UPDATE)
THEN
--
-- Verify that the disable date is greater than effectivity date
-- for both operations Create and Update
--
IF p_rev_component_rec.disable_date <
p_rev_component_rec.start_effective_date THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_COMP_DIS_DATE_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
-- Verify that if the user is trying to create or update rev. comp
-- to quantity related when the quantity_per_assembly is fractional
--
*********************************************************************/
IF round(p_rev_component_rec.quantity_per_assembly) <>
p_rev_component_rec.quantity_per_assembly AND
p_rev_component_rec.quantity_related = 1
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_QTY_REL_QTY_FRACTIONAL'
, p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
g_token_tbl.delete(2);
SELECT 'Component Implemented'
INTO l_dummy
FROM bom_inventory_components
WHERE component_sequence_id =
p_rev_comp_Unexp_rec.old_component_sequence_id
AND implementation_date IS NOT NULL;
-- Creates and in Case of Updates new_operation_sequence must be valid
-- if the user is trying to update operation_sequence_number
--
*********************************************************************/
IF ( p_rev_component_rec.operation_sequence_number <> 1 AND
p_rev_Component_rec.ACD_Type = 1
) OR
( NVL(p_rev_component_rec.new_operation_sequence_number,1) <> 1 AND
( ( p_rev_component_rec.ACD_Type = 2 AND
p_rev_component_rec.transaction_type =
Bom_Globals.G_OPR_CREATE
) OR
p_rev_component_rec.transaction_type =
Bom_Globals.G_OPR_UPDATE
) AND
NVL(p_old_rev_component_rec.operation_sequence_number, 1) <>
NVL(p_rev_component_rec.new_operation_sequence_number, 1)
)
THEN
--dbms_output.put_line('Verifying operation Sequence Number. . . ');
g_Token_Tbl.DELETE(2);
SELECT operation_seq_num
INTO l_result
FROM bom_inventory_components
WHERE component_sequence_id =
p_rev_comp_unexp_rec.old_component_sequence_id
AND operation_seq_num =
p_Rev_Component_rec.old_operation_sequence_number;
g_Token_Tbl.DELETE(2);
-- Check for Overlapping dates for the component being inserted.
--
*********************************************************************/
IF Check_Overlap_Dates
(X_Effectivity_date => p_rev_component_rec.start_effective_date,
X_Disable_date => p_rev_component_rec.disable_date,
X_Member_Item_Id => p_rev_comp_unexp_rec.component_item_id,
X_Bill_Sequence_id => p_rev_comp_unexp_rec.bill_sequence_id,
X_Rowid => NULL)
THEN
--if function return true then component dates overlapp
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_COMP_OPSEQ_DATE_OVERLAP'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_component_rec.optional, 0) <>
p_rev_component_rec.optional
)
)
THEN
l_Result := Check_PTO_ATO_for_Optional;
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_component_rec.planning_percent, 0) <>
p_rev_component_rec.planning_percent
)
)
THEN
l_Result := Check_Planning_Percent;
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(3);
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
)
AND
( NVL(p_Old_rev_component_rec.required_for_revenue, 0) <>
p_rev_component_rec.required_for_revenue OR
NVL(p_old_rev_component_rec.required_to_ship, 0) <>
p_rev_component_rec.required_to_ship
)
)
)
THEN
l_Result := Chk_Req_For_Rev_Or_Shp;
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(2);
Bom_GLOBALS.G_OPR_UPDATE
) AND
NVL(p_Old_rev_component_rec.check_atp, 0) <>
p_rev_component_rec.check_atp
)
)
THEN
l_result := Check_ATP;
g_Token_Tbl.DELETE(2);
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
)
AND
p_rev_component_rec.mutually_exclusive = 1
THEN
l_result := Check_Mutually_Exclusive;
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_component_rec.wip_supply_type, 0) <>
p_rev_component_rec.wip_supply_type
)
)
AND
Check_Supply_Type
( p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl ) = FALSE
THEN
l_return_status := FND_API.G_RET_STS_ERROR;
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
) AND
p_rev_component_rec.minimum_allowed_quantity is not null
AND
NOT Check_Min_Quantity THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_MIN_QUANTITY_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
) AND
p_rev_component_rec.maximum_allowed_quantity IS NOT NULL
AND
NOT Check_Max_Quantity THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_MAX_QUANTITY_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
) AND
(Is_OE_Installed = 'I'
AND ( ( Is_Item_ATO = 'Y' OR
Is_Item_PTO = 'Y'
) AND
(round(p_rev_component_rec.quantity_per_assembly)
<> p_rev_component_rec.quantity_per_assembly)
)
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_COMP_QTY_FRACTIONAL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_component_rec.supply_subinventory, 'NONE') <>
NVL(p_rev_component_rec.supply_subinventory, 'NONE')
)
)
AND
NOT Check_Supply_SubInventory THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'ENG_SUBINV_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_comp_unexp_rec.supply_locator_id, 0) <>
NVL(p_rev_comp_unexp_rec.supply_locator_id, 0)
)
)
AND
NOT Check_Locators
THEN
--dbms_output.put_line('Locators check returned with an error-' ||
-- to_char(l_locator_control));
END IF; -- Operation in UPDATE or CREATE
g_token_tbl.delete(2);
PROCEDURE Check_Entity_Delete
( x_return_status OUT NOCOPY VARCHAR2
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
, p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
IF p_rev_component_rec.Transaction_Type = Bom_GLOBALS.G_OPR_DELETE THEN
BEGIN
SELECT 'Component cancelled'
INTO l_dummy
FROM sys.dual
WHERE NOT EXISTS
(SELECT 1 from bom_inventory_components
WHERE component_sequence_id =
p_rev_comp_Unexp_rec.component_sequence_id
)
AND EXISTS (SELECT 1 from eng_revised_components
WHERE component_sequence_id =
p_rev_comp_Unexp_rec.component_sequence_id);
END Check_Entity_Delete;
* error if the operation is UPDATE and the record DOES NOT
* EXIST.
* In case of UPDATE if the record exists then the procedure
* will return the old record in the old entity parameters
* with a success status.
****************************************************************************/
PROCEDURE Check_Existence
( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
, p_rev_comp_unexp_rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
, x_old_rev_component_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
, x_old_rev_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_token_tbl Error_Handler.Token_Tbl_Type;
(Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE)
THEN
Error_Handler.Add_Error_Token
( x_Mesg_token_tbl => l_Mesg_Token_Tbl
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_message_name => 'ENG_REV_COMP_DOESNOT_EXIST'
, p_token_tbl => l_token_tbl
);
SELECT revised_item_sequence_id
FROM bom_inventory_components
WHERE component_item_id = p_rev_comp_unexp_rec.component_item_id
AND bill_sequence_id = p_rev_comp_unexp_rec.bill_sequence_id
AND operation_seq_num = p_rev_component_rec.operation_sequence_number
AND effectivity_date = p_rev_component_rec.start_effective_date;
-- In case of an update, based on the revised item information
-- Bill Sequence Id and Revised Item Sequence Id is queried from
-- the database. The revised item sequence id can however be different
-- from that in the database and should be checked and given an
-- error.
*******************************************************************/
IF p_rev_component_rec.transaction_type IN
(Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE,
Bom_Globals.G_OPR_CANCEL)
THEN
FOR Component IN c_GetComponent LOOP
IF Component.revised_item_sequence_id <>
p_rev_comp_unexp_rec.revised_item_sequence_id
THEN
l_Token_Tbl(1).token_name :=
'REVISED_COMPONENT_NAME';
SELECT 1
FROM sys.dual
WHERE NOT EXISTS
( SELECT component_sequence_id
FROM bom_inventory_components
WHERE component_item_id = p_component_item_id
AND bill_sequence_id = p_bill_sequence_id
AND effectivity_date = p_effectivity_date
AND operation_seq_num = p_operation_seq_num
) AND
EXISTS
( SELECT component_sequence_id
FROM eng_revised_components
WHERE component_item_id = p_component_item_id
AND bill_sequence_id = p_bill_sequence_id
AND effectivity_date = p_effectivity_date
AND operation_sequence_num = p_operation_seq_num
);
SELECT component_item_id
FROM bom_inventory_components
WHERE component_item_id = p_component_item_id
AND bill_sequence_id = p_bill_sequence_id
AND effectivity_date = p_effectivity_date
AND operation_seq_num = p_operation_seq_num
AND acd_type = 3;
SELECT effectivity_control
FROM mtl_system_items
WHERE inventory_item_id = p_component_item_id
AND organization_id = p_organization_id;
SELECT bom_item_type
INTO l_rev_comp_item_type
FROM mtl_system_items
WHERE inventory_item_id = p_component_item_id
AND organization_id = p_organization_id;
l_token_tbl.DELETE(2);
l_token_tbl.DELETE;