[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bbom.bill_sequence_id ,
bbom.assembly_type,
bbom.assembly_item_id,
bic.ACD_TYPE,
bic.bom_item_type,
bic.component_item_id,
bic.implementation_date,
msi.bom_item_type
INTO l_rec_BSI,
l_rec_AST,
l_rec_AII,
l_parent_acd_type,
l_rec_BIT,
l_rec_CII,
l_rec_ID,
l_parent_BIT
FROM mtl_system_items msi,
bom_inventory_components bic,
bom_bill_of_materials bbom
WHERE msi.inventory_item_id = bbom.assembly_item_id
AND msi.organization_id = bbom.organization_id
AND bic.component_sequence_id =
p_Sub_Comp_Unexp_Rec.component_sequence_id
AND bic.bill_sequence_id = bbom.bill_sequence_id;
SELECT 1
INTO l_dummy
FROM bom_bill_of_materials
WHERE bill_sequence_id = source_bill_sequence_id
AND bill_sequence_id = l_rec_BSI; --p_sub_comp_unexp_rec.bill_Sequence_id; --Bug 5726557
SELECT msi.bom_enabled_flag
INTO l_assy_bom_enabled
FROM mtl_system_items_b msi,
bom_bill_of_materials bbom
WHERE bbom.bill_sequence_id = l_rec_BSI
AND bbom.assembly_item_id = msi.inventory_item_id
AND bbom.organization_id = msi.organization_id;
IF (p_sub_component_rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE OR
p_sub_component_rec.Transaction_Type = Bom_Globals.G_OPR_CREATE)
AND
Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
THEN
Bom_Globals.Check_Approved_For_Process
( p_change_notice => p_sub_component_rec.Eco_Name
, p_organization_id => p_Sub_Comp_Unexp_Rec.Organization_Id
, x_processed =>l_processed
, x_err_text =>l_err_text
);
SELECT bom_item_type, bom_enabled_flag, eng_item_flag ,tracking_quantity_ind
INTO l_sbc_item_type, l_sbc_bom_enabled_flag, l_sbc_eng_item_flag,l_sbc_tracking_qty_ind
FROM mtl_system_items
WHERE organization_id = p_Sub_Comp_Unexp_Rec.Organization_Id
AND inventory_item_id = decode(p_Sub_Comp_Unexp_Rec.new_substitute_component_id,
NULL,
p_Sub_Comp_Unexp_Rec.substitute_component_id,
FND_API.G_MISS_NUM,
p_Sub_Comp_Unexp_Rec.substitute_component_id,
p_Sub_Comp_Unexp_Rec.new_substitute_component_id
);
SELECT 1
INTO l_dummy
FROM bom_bill_of_materials bbom
WHERE rownum =1
AND bbom.common_bill_sequence_id = l_rec_BSI
AND bbom.organization_id <> bbom.common_organization_id
AND not exists
(SELECT null
FROM mtl_system_items msi
WHERE msi.organization_id = bbom.organization_id
AND msi.inventory_item_id =
decode(p_Sub_Comp_Unexp_Rec.new_substitute_component_id,
NULL,
p_Sub_Comp_Unexp_Rec.substitute_component_id,
FND_API.G_MISS_NUM,
p_Sub_Comp_Unexp_Rec.substitute_component_id,
p_Sub_Comp_Unexp_Rec.new_substitute_component_id
)
AND msi.bom_enabled_flag = 'Y'
AND msi.bom_item_type = 4
AND (bbom.assembly_type = 2 OR
(bbom.assembly_type = 1 AND msi.eng_item_flag = 'N')));
l_token_tbl.DELETE;
IF (p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE AND
p_Sub_Comp_Unexp_Rec.New_Substitute_Component_Id IN
(l_rec_AII, l_rec_CII))
OR
(p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_CREATE AND
p_Sub_Comp_Unexp_Rec.Substitute_Component_Id IN
(l_rec_AII,l_rec_CII))
THEN
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_SUBCOMP_SAMEAS_ITEM_COMP'
, p_token_tbl => l_token_tbl
);
p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE
)
THEN
l_sub_comp_unique:= Verify_Unique_Substitute( p_sub_component_rec
, p_sub_comp_Unexp_rec
);
SELECT count(*)
INTO l_dummy
FROM bom_substitute_components sub_comp,
bom_inventory_components rev_comp
WHERE rev_comp.component_sequence_id =
p_Sub_Comp_Unexp_Rec.Component_Sequence_Id
AND sub_comp.component_sequence_id =
rev_comp.old_component_Sequence_id
AND sub_comp.Substitute_Component_Id =
p_Sub_Comp_Unexp_Rec.Substitute_Component_Id;
/* When the Substitute component is updated with new Substitute Component, It should be checked that the
New Substitute Component does not exists already */
IF ( p_Sub_Comp_Unexp_Rec.new_substitute_component_id is not null
and p_Sub_Comp_Unexp_Rec.new_substitute_component_id <> FND_API.G_MISS_NUM
and p_sub_component_rec.transaction_type = Bom_Globals.G_OPR_UPDATE) THEN
select count(*) into l_temp_var
FROM BOM_SUBSTITUTE_COMPONENTS
WHERE substitute_component_id = p_Sub_Comp_Unexp_Rec.new_substitute_component_id
AND COMPONENT_SEQUENCE_ID = p_Sub_Comp_Unexp_Rec.component_sequence_id
AND NVL(DECODE(ACD_TYPE, FND_API.G_MISS_NUM, null, acd_type), 0) =
NVL(DECODE(p_sub_component_rec.acd_type, FND_API.G_MISS_NUM, null, p_sub_component_rec.acd_type), 0) ;
SELECT 'x' INTO l_dummy FROM mtl_system_items WHERE
inventory_item_id = p_sub_comp_unexp_rec.component_item_id
AND organization_id = p_sub_comp_unexp_rec.organization_id
AND rounding_control_type = 1;
* Procedure : Check_Entity_Delete
* Parameter IN : Substitute Component Record
* Substitute component Record of Unexposed Columns
* Parameter OUT : Return_Status - Indicating status of the process.
* Mesg_Token_Tbl - Table of Errors and their tokens
*
* Purpose : Entity_Delete procedure will verify if the record
* can be delete without violating any dependency rules
*********************************************************************/
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_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
, p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
END Check_Entity_Delete;
* when the operation is Update/Delete and does not exist when
* the operation is Create. If the operation is Update/Delete
* the procedure will query the existing record and return them
* as old records.
***************************************************************************/
PROCEDURE Check_Existence
( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
, p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
, x_old_sub_component_rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Component_Rec_Type
, x_old_sub_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Comp_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
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_SUB_COMP_DOESNOT_EXIST'
, p_token_tbl => l_token_tbl
);
Bom_Globals.G_OPR_UPDATE;
SELECT revised_item_sequence_id
FROM bom_inventory_components
WHERE component_item_id = p_sub_comp_unexp_rec.component_item_id
AND operation_seq_num = p_sub_component_rec.operation_sequence_number
AND effectivity_date = p_sub_component_rec.start_effective_date
AND bill_sequence_id = p_sub_comp_unexp_rec.bill_sequence_id;
SELECT bom_item_type
FROM mtl_system_items
WHERE inventory_item_id = p_sub_comp_unexp_rec.substitute_component_id
AND organization_id = p_sub_comp_unexp_rec.organization_id;
SELECT process_enabled_flag
INTO l_OPM_org
FROM mtl_parameters
WHERE organization_id = p_sub_comp_unexp_rec.organization_id;
l_token_tbl.DELETE(2);
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_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
, p_bom_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
)
IS
l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
-- Call Check Entity Delete
Check_Entity_Delete
( p_sub_component_rec => l_sub_component_rec
, p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
, x_return_status => x_return_status
, x_mesg_token_tbl => x_mesg_token_tbl
);
END Check_Entity_Delete;