[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* transaction type is Update or Delete and the record
* does not exist then the return status would be an
* error as well. Mesg_Token_Table will carry the
* error messsage and the tokens associated with the
* message.
*********************************************************************/
PROCEDURE Check_Existence
( p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
, p_rtg_header_unexp_rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
, x_old_rtg_header_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Rec_Type
, x_old_rtg_header_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_token_tbl Error_Handler.Token_Tbl_Type;
(BOM_Rtg_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
THEN
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
SELECT bom_item_type
, decode(eng_item_flag, 'N', 1, 2)
, NVL(eam_item_type, 0 )
INTO l_bom_item_type
, l_assembly_type
, l_eam_item_type
FROM mtl_system_items
WHERE inventory_item_id = p_assembly_item_id
AND organization_id = p_organization_id;
IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
IF p_rtg_header_rec.eng_routing_flag = FND_API.G_MISS_NUM
THEN
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
SELECT 'SubInv exists'
FROM SYS.DUAL
WHERE NOT EXISTS ( SELECT null
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory
);
SELECT DECODE(restrict_subinventories_code, 1, 'Y', 'N')
restrict_code
, inventory_asset_flag
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_assembly_item_id
AND organization_id = p_organization_id ;
SELECT 'checking for duplicates' dummy
FROM sys.dual
WHERE EXISTS (
SELECT null
FROM mtl_item_locations
WHERE organization_id = p_organization_id
AND inventory_location_id = p_locator_id
AND subinventory_code <> p_subinventory
);
SELECT stock_locator_control_code
INTO l_org_locator_control
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT location_control_code
INTO l_item_locator_control
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_assembly_item_id ;
SELECT RESTRICT_LOCATORS_CODE
INTO l_item_loc_restricted
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_assembly_item_id ;
SELECT 'Valid'
INTO l_dummy
FROM mtl_item_locations mil,
mtl_secondary_locators msl
WHERE msl.inventory_item_id = p_assembly_item_id
AND msl.organization_id = p_organization_id
AND msl.subinventory_code = p_subinventory
AND msl.secondary_locator = p_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 = p_subinventory
AND mil.inventory_location_id = p_locator_id
AND mil.organization_id = p_organization_id
AND NVL(mil.DISABLE_DATE, SYSDATE+1) > SYSDATE;
SELECT bom_item_type
, pick_components_flag
, bom_enabled_flag
, eng_item_flag
, serial_number_control_code
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT NVL(common_routing_sequence_id, routing_sequence_id)
common_routing, routing_sequence_id
FROM bom_operational_routings
WHERE assembly_item_id = P_assembly_item_id
AND organization_id = P_org_id
AND NVL(alternate_routing_designator,'XXXX') =
NVL(P_alt_routing_code, 'XXXX');
SELECT 1 dummy
FROM sys.dual
WHERE not exists (
SELECT NULL
FROM bom_operational_routings bor
WHERE bor.routing_sequence_id = P_cmn_rtg_id
AND NVL(bor.alternate_routing_designator,
'Primary Alternate') = NVL(P_alt_desg, 'Primary Alternate')
AND bor.common_routing_sequence_id =
bor.routing_sequence_id
AND bor.assembly_item_id <> P_item_id
AND bor.organization_id = P_org_id
AND nvl(bor.cfm_routing_flag, 2) = p_cfm_rtg_flag
AND bor.routing_type =
decode(P_rtg_type, 1, 1, bor.routing_type));
SELECT 'Y' has_ops
FROM sys.dual
WHERE exists ( Select null
FROM Bom_Operation_Sequences
WHERE routing_sequence_id =
p_routing_sequence_id );
SELECT 'Y'
FROM sys.dual
WHERE exists ( Select null
FROM bom_operational_routings
WHERE common_routing_sequence_id <>
p_common_routing_sequence_id
AND CTP_flag = 1
AND organization_id = p_organization_id
AND assembly_item_id = p_assembly_item_id ) ;
SELECT 'Y'
FROM sys.dual
WHERE exists ( Select null
FROM Bom_Operation_Sequences
WHERE routing_sequence_id =p_routing_sequence_id
AND NVL(disable_date, trunc(sysdate) + 1)
> trunc(sysdate)
);
SELECT 'Y'
FROM sys.dual
WHERE exists ( Select null
FROM Bom_Operational_Routings
WHERE organization_id = P_organization_id
AND assembly_item_id = P_assembly_item_id
AND mixed_model_map_flag = 1
AND line_id = p_line_id
AND common_routing_sequence_id
<> p_common_routing_sequence_id );
SELECT 'Y'
FROM sys.dual
WHERE exists ( Select null
FROM Bom_Operational_Routings
WHERE organization_id = p_organization_id
AND Assembly_Item_Id = p_assembly_item_id
AND priority = p_priority
AND common_routing_sequence_id <>
p_common_routing_sequence_id
);
SELECT locator_type
FROM mtl_item_sub_ast_trk_val_v
WHERE inventory_item_id =p_rtg_header_unexp_rec.assembly_item_id
AND organization_id = p_rtg_header_unexp_rec.organization_id
AND secondary_inventory_name =
p_rtg_header_rec.completion_subinventory;
SELECT locator_type
FROM mtl_item_sub_trk_val_v
WHERE inventory_item_id = p_rtg_header_unexp_rec.assembly_item_id
AND organization_id = p_rtg_header_unexp_rec.organization_id
AND secondary_inventory_name =
p_rtg_header_rec.completion_subinventory;
SELECT locator_type
FROM mtl_sub_ast_trk_val_v
WHERE organization_id = p_rtg_header_unexp_rec.organization_id
AND secondary_inventory_name =
p_rtg_header_rec.completion_subinventory;
SELECT locator_type
FROM mtl_subinventories_trk_val_v
WHERE organization_id = p_rtg_header_unexp_rec.organization_id
AND secondary_inventory_name =
p_rtg_header_rec.completion_subinventory;
SELECT NULL from dual
WHERE exists
(SELECT 1 /* Checking for the BOM components operation seq. num. for alternate */
FROM BOM_BILL_OF_MATERIALS BOM, BOM_COMPONENT_OPERATIONS BCO
WHERE BOM.ORGANIZATION_ID = p_rtg_header_unexp_rec.organization_id
AND BOM.ASSEMBLY_ITEM_ID = p_rtg_header_unexp_rec.assembly_item_id
AND BOM.ALTERNATE_BOM_DESIGNATOR = p_rtg_header_rec.alternate_routing_code
AND BOM.BILL_SEQUENCE_ID = BCO.BILL_SEQUENCE_ID)
OR exists
(SELECT 1 /* Checking for the BOM components operation seq. num. for alternate */
FROM BOM_BILL_OF_MATERIALS BOM, BOM_INVENTORY_COMPONENTS BIC
WHERE BOM.ORGANIZATION_ID = p_rtg_header_unexp_rec.organization_id
AND BOM.ASSEMBLY_ITEM_ID = p_rtg_header_unexp_rec.assembly_item_id
AND BOM.ALTERNATE_BOM_DESIGNATOR = p_rtg_header_rec.alternate_routing_code
AND BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BIC.OPERATION_SEQ_NUM > 1)
;
SELECT '1'
INTO l_dummy
FROM bom_operational_routings
WHERE alternate_routing_designator IS NULL
AND assembly_item_id =
p_rtg_header_unexp_rec.assembly_item_id
AND organization_id =
p_rtg_header_unexp_rec.organization_id;
SELECT '1'
INTO l_dummy
FROM bom_alternate_designators
WHERE
alternate_designator_code = p_rtg_header_rec.alternate_routing_code
AND organization_id = p_rtg_header_unexp_rec.organization_id
AND disable_date is not null
AND disable_date <= sysdate;
l_token_tbl.delete;
l_token_tbl.delete;
SELECT '1'
INTO l_dummy
FROM bom_operational_routings
WHERE routing_sequence_id =
p_rtg_header_unexp_rec.common_routing_sequence_id
AND NVL(common_routing_sequence_id,
routing_sequence_id) <> routing_sequence_id;
l_token_tbl.delete;
IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
FOR l_Operation in c_check_ops(
p_routing_sequence_id =>
p_rtg_header_unexp_rec.routing_sequence_id)
LOOP
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
/* This check is not required as we should be able to update the routing details even for
a routing referencing another routing as common -- bug 2923716
IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
FOR l_checkCommon IN c_CheckCommon
( P_assembly_item_id => p_rtg_header_unexp_rec.assembly_item_id
, P_org_id => p_rtg_header_unexp_rec.organization_id
, P_alt_routing_code=>p_rtg_header_rec.alternate_routing_code)
LOOP
IF l_CheckCommon.common_routing <>
l_CheckCommon.routing_sequence_id
THEN
l_token_tbl.delete;
= BOM_Rtg_Globals.G_OPR_UPDATE
AND p_rtg_header_rec.ctp_flag <>
p_old_rtg_header_rec.ctp_flag )
)
THEN
-- for flow routing type, CFM routing flag = 1;
IF p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
AND p_rtg_header_rec.cfm_routing_flag = 1
AND p_rtg_header_unexp_rec.line_id
<> p_old_rtg_header_unexp_rec.line_id
THEN
FOR l_active_ops_rec in c_check_active_ops(
p_routing_sequence_id =>
p_rtg_header_unexp_rec.common_routing_sequence_id)
LOOP
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
= BOM_Rtg_Globals.G_OPR_UPDATE
AND p_rtg_header_rec.mixed_model_map_flag <>
p_old_rtg_header_rec.mixed_model_map_flag )
)
THEN
FOR l_active_mixed_rec in c_check_active_mixed(
P_assembly_item_id =>
p_rtg_header_unexp_rec.assembly_item_id
, P_organization_id =>
p_rtg_header_unexp_rec.organization_id
, p_line_id =>
p_rtg_header_unexp_rec.line_id
, p_common_routing_sequence_id
=>
p_rtg_header_unexp_rec.common_routing_sequence_id
)
LOOP
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
= BOM_Rtg_Globals.G_OPR_UPDATE
AND p_rtg_header_rec.priority <>
NVL(p_old_rtg_header_rec.priority
, FND_API.G_MISS_NUM ) )
)
THEN
FOR l_priority_rec in c_check_priority(
p_assembly_item_id =>
p_rtg_header_unexp_rec.assembly_item_id
, p_organization_id =>
p_rtg_header_unexp_rec.organization_id
, p_priority =>
p_rtg_header_rec.priority
, p_common_routing_sequence_id
=>
p_rtg_header_unexp_rec.common_routing_sequence_id
)
LOOP
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
= BOM_Rtg_Globals.G_OPR_UPDATE
AND p_rtg_header_rec.Completion_Subinventory <>
NVL( p_old_rtg_header_rec.Completion_Subinventory
,FND_API.G_MISS_CHAR )
)
)
THEN
IF NOT Check_SubInv_Exists
( p_organization_id =>
p_rtg_header_unexp_rec.organization_id
, p_subinventory
=>p_rtg_header_rec.Completion_Subinventory
)
THEN
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
IF (( p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
AND NVL(p_rtg_header_unexp_rec.completion_locator_id , 0) <>
NVL(p_rtg_header_unexp_rec.completion_locator_id , 0)
)
OR (p_rtg_header_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE
AND p_rtg_header_rec.completion_subinventory is not null --BUG 3872490
AND p_rtg_header_rec.completion_subinventory <> FND_API.G_MISS_CHAR) --BUG 3872490
)
AND NOT Check_Locators( p_organization_id => p_rtg_header_unexp_rec.organization_id
, p_assembly_item_id=> p_rtg_header_unexp_rec.assembly_item_id
, p_locator_id => p_rtg_header_unexp_rec.completion_locator_id
, p_subinventory => p_rtg_header_rec.completion_subinventory )
THEN
IF l_locator_control = 4 THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
l_token_tbl(1).token_name :='ASSEMBLY_ITEM_NAME';
IF p_rtg_header_rec.transaction_type IN (BOM_Rtg_Globals.G_OPR_UPDATE) AND -- Added for SSOS (bug 2689249)
p_rtg_header_rec.ser_start_op_seq IS NOT NULL AND
l_bom_item_type IN (1,2) THEN -- If the item is a model/option class item routing
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
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 => 'BOM_SER_OP_CONFIG_RTG_EXISTS'
, p_token_tbl => l_token_tbl
, p_message_type => 'W'
);
PROCEDURE Check_Entity_Delete
( x_return_status IN OUT NOCOPY VARCHAR2
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, p_rtg_header_rec IN Bom_Rtg_Pub.rtg_header_Rec_Type
, p_rtg_header_Unexp_Rec IN Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
, x_rtg_header_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_header_Unexposed_Rec_Type
)
IS
l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
SELECT description,
delete_group_sequence_id,
delete_type
FROM bom_delete_groups
WHERE delete_group_name = p_rtg_header_rec.Delete_Group_Name
AND organization_id = p_rtg_header_Unexp_Rec.organization_id;
IF p_rtg_header_rec.Delete_Group_Name IS NULL OR
p_rtg_header_rec.Delete_Group_Name = FND_API.G_MISS_CHAR
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_DG_NAME_MISSING'
, p_mesg_token_tbl => l_mesg_token_tbl
, x_mesg_token_tbl => x_mesg_token_tbl
);
If c_CheckGroup.delete_type <> 3 /* Bill */ then
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
, p_mesg_token_tbl=>l_mesg_token_tbl
, x_mesg_token_tbl=>x_mesg_token_tbl
);
c_Checkgroup.delete_group_sequence_id;
( p_message_name => 'NEW_DELETE_GROUP'
, p_message_type => 'W'
, p_mesg_token_tbl => l_mesg_token_tbl
, x_mesg_token_tbl => x_mesg_token_tbl
);
SELECT DECODE(p_rtg_header_rec.eng_routing_flag, 1, 2, 1)
INTO l_rtg_header_unexp_rec.routing_type
FROM SYS.DUAL ;
l_err_text := G_PKG_NAME || ' Validation (Check Entity Delete) '
|| substrb(SQLERRM,1,200);
END Check_Entity_Delete;
Select 'IsNotValid'
from dual
where p_rtg_header_rec.ser_start_op_seq NOT IN
(select bos.OPERATION_SEQ_NUM
from bom_operation_sequences bos
where bos.ROUTING_SEQUENCE_ID = p_rtg_header_unexp_rec.routing_sequence_id
and nvl(bos.OPERATION_TYPE,1) = 1
and nvl(bos.EFFECTIVITY_DATE, sysdate-1) <= sysdate
and nvl(bos.disable_date , sysdate + 1) >= sysdate
and bos.OPTION_DEPENDENT_FLAG = 2
and bos.count_point_type = 1)
;
select lot_control_code
from mtl_system_items m
where m.organization_id = p_organization_id
and m.inventory_item_id = p_assembly_item_id;
SELECT COUNT(1)
INTO l_nw_opern_count
FROM BOM_OPERATION_SEQUENCES bos,
BOM_OPERATION_NETWORKS bon
WHERE
bon.FROM_OP_SEQ_ID = bos.OPERATION_SEQUENCE_ID
AND bos.ROUTING_SEQUENCE_ID = p_routing_sequence_id;
SELECT bor.CFM_ROUTING_FLAG,
bor.SERIALIZATION_START_OP,
msib.SERIAL_NUMBER_CONTROL_CODE
INTO l_cfm_routing_flag,
l_ser_start_op_seq,
l_ser_num_control_code
FROM BOM_OPERATIONAL_ROUTINGS bor, MTL_SYSTEM_ITEMS_B msib
WHERE
bor.ASSEMBLY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND bor.ORGANIZATION_ID = msib.ORGANIZATION_ID
AND bor.ROUTING_SEQUENCE_ID = p_routing_sequence_id;