The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_Delete constant number := 3;
Select brd.component_reference_designator
From bom_reference_designators brd
Where brd.component_sequence_id = p_old_component_sequence_id
And nvl(brd.acd_type, X_Add) = X_Add
Union
Select brd.component_reference_designator
From bom_reference_designators brd,
bom_inventory_components bic
Where DECODE(bic.old_component_sequence_id, NULL,
bic.component_sequence_id,
bic.old_component_sequence_id) = p_old_component_sequence_id
And bic.component_sequence_id = brd.component_sequence_id
And bic.implementation_date is null
And brd.acd_type = X_Add
Minus
Select brd.component_reference_designator
From bom_reference_designators brd,
bom_inventory_components bic
Where DECODE(bic.old_component_sequence_id, NULL,
bic.component_sequence_id,
bic.old_component_sequence_id) = p_old_component_sequence_id
And bic.component_sequence_id = brd.component_sequence_id
And bic.implementation_date is null
And brd.acd_type = X_Delete;
SELECT acd_type, old_component_sequence_id
FROM bom_inventory_components bic
WHERE bic.component_sequence_id =
p_Ref_Desg_Unexp_Rec.component_sequence_id;
SELECT component_quantity
FROM bom_inventory_components
WHERE component_sequence_id =
p_Ref_Desg_Unexp_rec.component_sequence_id
AND quantity_related = 1;
SELECT 1
INTO l_dummy
FROM bom_inventory_components
WHERE component_sequence_id =
p_ref_desg_unexp_rec.component_sequence_id
AND implementation_date IS NOT NULL
AND exists ( SELECT 1
FROM bom_reference_designators
WHERE component_sequence_id =
p_ref_desg_unexp_rec.component_sequence_id
AND component_reference_designator =
p_ref_designator_rec.reference_designator_name
AND acd_type = 1
);
SELECT 'Non-Standard Comp'
INTO l_dummy
FROM bom_inventory_Components bic
WHERE bic.component_sequence_id =
p_Ref_Desg_Unexp_Rec.component_sequence_id
AND bic.bom_item_type in (1, 2, 3); /*MODEL,OPTION CLASS,PLANNING*/
SELECT 'Planning Bill'
INTO l_dummy
FROM sys.dual
WHERE EXISTS (SELECT 'Planning Item'
FROM bom_bill_of_materials bom,
mtl_system_items msi,
bom_inventory_components bic
WHERE msi.bom_item_type = 3 /* PLANNING */
AND msi.inventory_item_id = bom.assembly_item_id
AND msi.organization_id = bom.organization_id
AND bom.bill_sequence_id = bic.bill_sequence_id
AND bic.component_sequence_id =
p_Ref_Desg_Unexp_Rec.Component_sequence_id
);
* If a ref. designator is being added or deleted, and if the Quantity
* Related is 1 then their must be a check that the number of designators
* is equal to the component_quantity.
*************************************************************************/
IF (p_ref_designator_rec.Transaction_Type = BOM_GLOBALS.G_OPR_CREATE) THEN
BEGIN
OPEN c_QuantityRelated;
* Procedure : Check_Entity_Delete
* Parameters IN : Reference Designator Record as given by the User
* Reference Designator Unexposed Record
* Parameters OUT: Return_Status - Indicating success or faliure
* Mesg_Token_Tbl - Filled with any errors or warnings
* Purpose : Entity Delete procedure will verify if the entity can
* can be deleted without violating any business rules.
* In case of errors the Mesg_token_Tbl is populated and
* process return with a status other than 'S'
* Warning will not prevent the entity from being deleted.
********************************************************************/
PROCEDURE Check_Entity_Delete
( x_return_status OUT NOCOPY VARCHAR2
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
, p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
SELECT 'Related'
INTO l_dummy
FROM bom_inventory_components
WHERE quantity_related = 1
AND component_sequence_id =
p_ref_desg_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_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
, p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
, x_old_ref_designator_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
, x_old_ref_desg_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_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_REF_DESG_DOESNOT_EXIST'
, p_token_tbl => l_token_tbl
);
SELECT revised_item_sequence_id
FROM bom_inventory_components
WHERE component_item_id= p_ref_desg_unexp_rec.component_item_id
AND operation_seq_num=p_ref_designator_rec.operation_sequence_number
AND effectivity_date = p_ref_designator_rec.start_effective_date
AND bill_sequence_id = p_ref_desg_unexp_rec.bill_sequence_id;
SELECT 'parent not disabled'
INTO l_dummy
FROM bom_inventory_components bic
WHERE bic.component_sequence_id =
p_Ref_Desg_Unexp_Rec.component_sequence_id
AND NVL(bic.acd_type, 0) <> 3;