[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_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
, p_bom_head_unexp_rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
, x_old_bom_header_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_Rec_Type
, x_old_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_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_Globals.G_OPR_UPDATE, BOM_Globals.G_OPR_DELETE)
THEN
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
Bom_Globals.G_OPR_UPDATE;
SELECT bom_item_type, decode(eng_item_flag, 'N', 1, 2)
,tracking_quantity_ind
INTO l_bom_item_type, l_assembly_type,l_tracking_qty_ind
FROM mtl_system_items
WHERE inventory_item_id = p_assembly_item_id
AND organization_id = p_organization_id;
SELECT process_enabled_flag
INTO l_OPM_org
FROM mtl_parameters
WHERE organization_id = p_organization_id;
-- If user is trying to update an Engineering Item from BOM
-- Business Object, the user should not be allowed.
--
/*IF l_assembly_type = 2 -- Engineering Item
THEN
Error_Handler.Add_Error_Token
( p_Message_name => 'BOM_ASSEMBLY_TYPE_ENG'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_mesg_token_tbl => l_mesg_token_tbl
);
SELECT NVL(common_bill_sequence_id,bill_sequence_id) common_bill_seq,
bill_sequence_id
FROM bom_bill_of_materials
WHERE assembly_item_id = p_bom_head_unexp_rec.assembly_item_id
AND organization_id = p_bom_head_unexp_rec.organization_id
AND NVL(alternate_bom_designator, 'XXXX') =
NVL(p_bom_header_rec.alternate_bom_code, 'XXXX');
select bom_enabled_flag into bom_enabled from mtl_system_items
where inventory_item_id = p_bom_head_unexp_rec.assembly_item_id
AND organization_id = p_bom_head_unexp_rec.organization_id;
SELECT structure_type_id INTO struct_type_id FROM bom_structure_types_b
WHERE structure_type_name = 'Packaging Hierarchy';
SELECT Count(1) INTO l_count FROM bom_alternate_designators
WHERE structure_type_id = struct_type_id and alternate_designator_code = p_bom_header_rec.alternate_bom_code
AND organization_id = p_bom_head_unexp_rec.organization_id AND is_preferred = 'Y';
SELECT master_organization_id INTO l_mater_org_id FROM mtl_parameters
WHERE organization_id = p_bom_head_unexp_rec.organization_id;
SELECT '1'
INTO l_dummy
FROM bom_bill_of_materials
WHERE alternate_bom_designator IS NULL
AND assembly_item_id =
p_bom_head_unexp_rec.assembly_item_id
AND organization_id =
p_bom_head_unexp_rec.organization_id
AND ((p_bom_header_rec.assembly_type= 2)
OR
(p_bom_header_rec.assembly_type =1
and assembly_type = 1));
SELECT COUNT(1)
INTO l_count
FROM BOM_ALTERNATE_DESIGNATORS BAD
WHERE
BAD.ORGANIZATION_ID = p_bom_head_unexp_rec.organization_id
AND BAD.ALTERNATE_DESIGNATOR_CODE = p_bom_header_rec.alternate_bom_code
AND BAD.STRUCTURE_TYPE_ID IN
( SELECT BST.STRUCTURE_TYPE_ID
FROM BOM_STRUCTURE_TYPES_B BST
START WITH BST.STRUCTURE_TYPE_ID = p_bom_head_unexp_rec.structure_type_id
CONNECT BY PRIOR BST.PARENT_STRUCTURE_TYPE_ID = BST.STRUCTURE_TYPE_ID
);
IF ( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
AND p_bom_head_unexp_rec.structure_type_id IS NOT NULL
AND p_bom_head_unexp_rec.structure_type_id <> FND_API.G_MISS_NUM
AND p_bom_head_unexp_rec.structure_type_id <> p_old_bom_head_unexp_rec.structure_type_id )
THEN
SELECT COUNT(1)
INTO l_count
FROM BOM_STRUCTURE_TYPES_B BST
WHERE BST.STRUCTURE_TYPE_ID = p_bom_head_unexp_rec.structure_type_id
START WITH BST.STRUCTURE_TYPE_ID = p_old_bom_head_unexp_rec.structure_type_id
CONNECT BY PRIOR BST.STRUCTURE_TYPE_ID = BST.PARENT_STRUCTURE_TYPE_ID;
SELECT STRUCTURE_TYPE_NAME
INTO l_existing_str_type
FROM BOM_STRUCTURE_TYPES_B
WHERE STRUCTURE_TYPE_ID = p_old_bom_head_unexp_rec.structure_type_id;
( p_Message_Name => 'BOM_UPDATE_STRTYPE_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_token_tbl
);
END IF; -- end if p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
** 1. Common_bill_Sequence_Id is non-updateable. So once a common
** bom is created user can only delete it and cannot simply
** update it to piont to another bom as common
** 2. Manufactuing BOM's cannot refer to an Engineering BOM as common
** 3. If the BOM being updated already has components, then it cannot
** refer to another BOM as common
** 4. If a BOM is referencing another bill as common, then only the
** parent BOM is updateable
** 5. If a BOM is already referencing another bill as common, then
** this BOM cannot be used as common for another BOM. i.e it is
** not permitted to create a chain of common BOM's
** 6. The current BOM and the bill being referenced as common must have the
** same master org
** 7. If a BOM in one org is referening a BOM in another org as common, then
** make sure that then all the components that exist under the parent org
** must exist both the orgs
** 8. If a BOM in one org is referencing a BOM in another org as common, then
** any substitute components under the components must also exist in both
** orgs
** 9. When referencing another bom as common, the items must have the same
** bom_item_type, pick_components_flag, replenish_to_order_flag and
** bom_enabled_flag
** --------------------------------------------------------------------**/
--
-- If the user is performing an update operation, then the user
-- must not enter the value for common organization code and
-- common assembly item name. Providing these values would mean that
-- the user is attempting to update these non-updateable columns
--
IF p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
AND
( ( p_bom_header_rec.common_organization_code IS NOT NULL
AND p_bom_header_rec.common_organization_code <>
FND_API.G_MISS_CHAR
AND NVL(p_bom_header_rec.common_organization_code, 'XXX') <>
NVL(p_old_bom_head_rec.common_organization_code,'XXX')
)
OR
( p_bom_header_rec.common_assembly_item_name IS NOT NULL
AND p_bom_header_rec.common_assembly_item_name <>
FND_API.G_MISS_CHAR
AND NVL(p_bom_header_rec.common_assembly_item_name, 'NONE') <>
NVL(p_old_bom_head_rec.common_assembly_item_name, 'NONE')
)
)
THEN
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
-- If the user is trying to perform an update, and the bill is
-- referencing another bill as common, then this bill is not
-- updateable. Only the parent bill is
--
FOR CheckCommon IN c_CheckCommon
LOOP
IF CheckCommon.common_bill_seq <>
CheckCommon.bill_sequence_id
THEN
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
SELECT '1'
INTO l_dummy
FROM bom_bill_of_materials
WHERE bill_sequence_id =
p_bom_head_unexp_rec.source_bill_sequence_id
AND NVL(source_bill_sequence_id, bill_sequence_id) <>
bill_sequence_id;
l_token_tbl.delete;
SELECT '1'
INTO l_dummy
FROM mtl_system_items assy,
mtl_system_items common
WHERE assy.inventory_item_id =
p_bom_head_unexp_rec.assembly_item_id
AND assy.organization_id =
p_bom_head_unexp_rec.organization_id
AND common.inventory_item_id =
p_bom_head_unexp_rec.common_assembly_item_id
AND common.organization_id =
p_bom_head_unexp_rec.common_organization_id
AND ((common.eng_item_flag = 'N' and
assy.eng_item_flag = common.eng_item_flag)
OR
common.eng_item_flag <> 'N');
IF p_bom_header_Rec.ENABLE_ATTRS_UPDATE = 'Y'
THEN
-- Add operation sequence number validation here
--call bompcmbm.validate_operation_sequence_id
BOMPCMBM.Validate_Operation_Sequence_Id(p_src_bill_sequence_id => p_bom_head_unexp_rec.source_bill_sequence_id
, p_assembly_item_id => p_bom_head_unexp_rec.assembly_item_id
, p_organization_id => p_bom_head_unexp_rec.organization_id
, p_alt_desg => p_bom_header_rec.alternate_bom_code
, x_Return_Status => l_valid_op_seq);
IF ( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE AND
p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
p_bom_head_unexp_rec.source_bill_sequence_id <> FND_API.G_MISS_NUM AND
p_old_bom_head_unexp_rec.source_bill_sequence_id IS NULL AND
p_old_bom_head_unexp_rec.organization_id <>
p_bom_head_unexp_rec.common_organization_id
)
OR
( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_CREATE AND
p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
p_bom_head_unexp_rec.source_bill_sequence_id <> FND_API.G_MISS_NUM AND
p_bom_head_unexp_rec.common_organization_id <>
p_bom_head_unexp_rec.organization_id
)
THEN
BEGIN
SELECT '1'
INTO l_dummy
FROM mtl_parameters mp1, mtl_parameters mp2
WHERE mp1.organization_id = p_bom_head_unexp_rec.organization_id
AND mp2.organization_id =
DECODE(p_bom_header_rec.transaction_type, Bom_Globals.G_OPR_CREATE,
p_bom_head_unexp_rec.common_organization_id,
Bom_Globals.G_OPR_UPDATE,
p_old_bom_head_unexp_rec.common_organization_id
)
AND mp1.master_organization_id = mp2.master_organization_id;
SELECT bom_item_type, base_item_id, replenish_to_order_flag,
pick_components_flag--, DECODE(eng_item_flag, 'Y', 2, 1)
INTO bit, base_id, ato, pto--, assmtype
FROM mtl_system_items
WHERE inventory_item_id = p_bom_head_unexp_rec.assembly_item_id
AND organization_id = p_bom_head_unexp_rec.organization_id;
SELECT assembly_type
INTO assmtype
FROM bom_structures_b
WHERE bill_sequence_id = p_bom_head_unexp_rec.source_bill_sequence_id;
SELECT count(*)
INTO l_count
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = p_bom_head_unexp_rec.source_bill_sequence_id
AND nvl(bic.disable_date, sysdate + 1) >= sysdate --- Bug: 3448641
AND not exists
(SELECT 'x'
FROM mtl_system_items s
WHERE s.organization_id = p_bom_head_unexp_rec.organization_id
AND s.inventory_item_id = bic.component_item_id
AND ((assmtype = 1 AND s.eng_item_flag = 'N')
OR (assmtype = 2)
)
/* Commented the following for Bug2984763 */
AND s.bom_enabled_flag = 'Y' /* Uncommented for bug 5925020 */
AND s.inventory_item_id <> p_bom_head_unexp_rec.assembly_item_id
AND ((bit = 1 AND s.bom_item_type <> 3)
OR (bit = 2 AND s.bom_item_type <> 3)
OR (bit = 3)
OR (bit = 4
AND (s.bom_item_type = 4
OR
( s.bom_item_type IN (2, 1)
AND s.replenish_to_order_flag = 'Y'
AND base_id IS NOT NULL
AND ato = 'Y'
)
)
)
)
AND (bit = 3
OR
pto = 'Y'
OR
s.pick_components_flag = 'N'
)
AND (bit = 3
OR
NVL(s.bom_item_type, 4) <> 2
OR
(s.bom_item_type = 2
AND (( pto = 'Y'
AND s.pick_components_flag = 'Y'
)
OR ( ato = 'Y'
AND s.replenish_to_order_flag = 'Y'
)
)
)
)
AND not( bit = 4
AND pto = 'Y'
AND s.bom_item_type = 4
AND s.replenish_to_order_flag = 'Y'
)
);
l_token_tbl.DELETE;
l_token_tbl.DELETE;
SELECT count(*)
INTO l_count
FROM bom_inventory_components bic,
bom_substitute_components bsc
WHERE bic.bill_sequence_id =p_bom_head_unexp_rec.source_bill_sequence_id
AND bic.component_sequence_id = bsc.component_sequence_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_bom_head_unexp_rec.organization_id
and msi1.inventory_item_id = bsc.substitute_component_id
and msi1.bom_enabled_flag = 'Y'
and msi2.organization_id = p_bom_head_unexp_rec.common_organization_id
and msi2.inventory_item_id = msi1.inventory_item_id
AND ((assmtype = 1 AND msi1.eng_item_flag = 'N')
OR (assmtype = 2)
)
AND msi1.inventory_item_id <> p_bom_head_unexp_rec.assembly_item_id
AND ((bit = 1 AND msi1.bom_item_type <> 3)
OR (bit = 2 AND msi1.bom_item_type <> 3)
OR (bit = 3)
OR (bit = 4
AND (msi1.bom_item_type = 4
OR
( msi1.bom_item_type IN (2, 1)
AND msi1.replenish_to_order_flag = 'Y'
AND base_id IS NOT NULL
AND ato = 'Y'
)
)
)
)
AND (bit = 3
OR
pto = 'Y'
OR
msi1.pick_components_flag = 'N'
)
AND (bit = 3
OR
NVL(msi1.bom_item_type, 4) <> 2
OR
(msi1.bom_item_type = 2
AND (( pto = 'Y'
AND msi1.pick_components_flag = 'Y'
)
OR ( ato = 'Y'
AND msi1.replenish_to_order_flag = 'Y'
)
)
)
)
AND not( bit = 4
AND pto = 'Y'
AND msi1.bom_item_type = 4
AND msi1.replenish_to_order_flag = 'Y'
)
);
l_token_tbl.DELETE;
IF ( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_UPDATE AND
p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
p_bom_head_unexp_rec.source_bill_sequence_id <> FND_API.G_MISS_NUM AND
p_old_bom_head_unexp_rec.source_bill_sequence_id IS NULL
)
OR
( p_bom_header_rec.transaction_type = Bom_Globals.G_OPR_CREATE AND
p_bom_head_unexp_rec.source_bill_sequence_id IS NOT NULL AND
p_bom_head_unexp_rec.source_bill_sequence_id <> FND_API.G_MISS_NUM
)
THEN
BEGIN
SELECT 1
INTO l_count
FROM mtl_system_items msi1, mtl_system_items msi2
WHERE
--Bug 2217522 msi1.organization_id = p_bom_head_unexp_rec.common_organization_id
msi1.organization_id = p_bom_head_unexp_rec.organization_id --Bug 2217522
AND msi1.inventory_item_id = p_bom_head_unexp_rec.assembly_item_id
AND msi2.organization_id = p_bom_head_unexp_rec.common_organization_id
AND msi2.inventory_item_id=p_bom_head_unexp_rec.common_assembly_item_id
AND msi2.bom_enabled_flag = 'Y'
AND msi1.bom_item_type = msi2.bom_item_type
AND msi1.pick_components_flag = msi2.pick_components_flag
AND msi1.replenish_to_order_flag = msi2.replenish_to_order_flag
AND msi1.effectivity_control = msi2.effectivity_control;
l_token_tbl.DELETE;
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_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type
, p_bom_head_Unexp_Rec IN Bom_Bo_Pub.Bom_Head_Unexposed_Rec_Type
, x_bom_head_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Head_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_bom_header_rec.Delete_Group_Name
AND organization_id = p_bom_head_Unexp_Rec.organization_id;
IF p_bom_header_rec.Delete_Group_Name IS NULL OR
p_bom_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 <> 2 /* 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
);
END Check_Entity_Delete;