[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(p_alternate_bom_code, FND_API.G_MISS_CHAR,
'Missing', NULL, 'XXXX', p_alternate_bom_code)
INTO l_dummy
from sys.dual;
SELECT assembly_item_id
, organization_id
, alternate_bom_designator
, common_assembly_item_id
, common_organization_id
, specific_assembly_comment
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, assembly_type
, assembly_type
, common_bill_sequence_id
, bill_sequence_id
, structure_type_id
, implementation_date
INTO l_bom_head_unexp_rec.assembly_item_id
, l_bom_head_unexp_rec.organization_id
, l_bom_header_rec.alternate_bom_code
, l_bom_head_unexp_rec.common_assembly_item_id
, l_bom_head_unexp_rec.common_organization_id
, l_bom_header_rec.assembly_comment
, l_bom_header_rec.attribute_category
, l_bom_header_rec.attribute1
, l_bom_header_rec.attribute2
, l_bom_header_rec.attribute3
, l_bom_header_rec.attribute4
, l_bom_header_rec.attribute5
, l_bom_header_rec.attribute6
, l_bom_header_rec.attribute7
, l_bom_header_rec.attribute8
, l_bom_header_rec.attribute9
, l_bom_header_rec.attribute10
, l_bom_header_rec.attribute11
, l_bom_header_rec.attribute12
, l_bom_header_rec.attribute13
, l_bom_header_rec.attribute14
, l_bom_header_rec.attribute15
, l_bom_head_unexp_rec.assembly_type
, l_bom_header_rec.assembly_type
, l_bom_head_unexp_rec.common_bill_sequence_id
, l_bom_head_unexp_rec.bill_sequence_id
, l_bom_head_unexp_rec.structure_type_id
, l_bom_header_rec.bom_implementation_date
FROM bom_bill_of_materials
WHERE assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND effectivity_control <>4 -- Rev effective structures should be filtered.
AND NVL(alternate_bom_designator, 'XXXX') =
NVL(DECODE( p_alternate_bom_code,FND_API.G_MISS_CHAR,
NULL, p_alternate_bom_code
), 'XXXX');
SELECT decode(p_alternate_bom_code, FND_API.G_MISS_CHAR,
'Missing', NULL, 'XXXX', p_alternate_bom_code)
INTO l_dummy
from sys.dual;
SELECT assembly_item_id
, organization_id
, alternate_bom_designator
, common_assembly_item_id
, common_organization_id
, specific_assembly_comment
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, assembly_type
, common_bill_sequence_id
, bill_sequence_id
, structure_type_id
, implementation_date
INTO l_bom_head_unexp_rec.assembly_item_id
, l_bom_head_unexp_rec.organization_id
, l_bom_header_rec.alternate_bom_code
, l_bom_head_unexp_rec.common_assembly_item_id
, l_bom_head_unexp_rec.common_organization_id
, l_bom_header_rec.assembly_comment
, l_bom_header_rec.attribute_category
, l_bom_header_rec.attribute1
, l_bom_header_rec.attribute2
, l_bom_header_rec.attribute3
, l_bom_header_rec.attribute4
, l_bom_header_rec.attribute5
, l_bom_header_rec.attribute6
, l_bom_header_rec.attribute7
, l_bom_header_rec.attribute8
, l_bom_header_rec.attribute9
, l_bom_header_rec.attribute10
, l_bom_header_rec.attribute11
, l_bom_header_rec.attribute12
, l_bom_header_rec.attribute13
, l_bom_header_rec.attribute14
, l_bom_header_rec.attribute15
, l_bom_head_unexp_rec.assembly_type
, l_bom_head_unexp_rec.common_bill_sequence_id
, l_bom_head_unexp_rec.bill_sequence_id
, l_bom_head_unexp_rec.structure_type_id
, l_bom_header_rec.bom_implementation_date
FROM bom_structures_b
WHERE assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND NVL(alternate_bom_designator, 'XXXX') =
NVL(DECODE( p_alternate_bom_code,FND_API.G_MISS_CHAR,
NULL, p_alternate_bom_code
), 'XXXX');
* Procedure : Insert_Row
* Parameters IN : BOM Header exposed column record
* BOM Header unexposed column record
* Parameters OUT: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an insert into the
* BOM_Bill_Of_Materials table thus creating a new bill
*********************************************************************/
PROCEDURE Insert_Row
( 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_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_Status IN OUT NOCOPY VARCHAR2
)
IS
l_preferred_flag Varchar2(1);
INSERT INTO bom_bill_of_materials
( assembly_item_id
, organization_id
, alternate_bom_designator
, common_assembly_item_id
, common_organization_id
, assembly_type
, bill_sequence_id
, common_bill_sequence_id
, specific_assembly_comment -- Added on 05/31/01
, original_system_reference -- Added on 05/31/01
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, structure_type_id
, implementation_date
, effectivity_control
, Obj_Name
, pk1_value
, pk2_value
, is_preferred
, source_bill_sequence_id
, request_id
, program_id
, program_application_id
, program_update_date
)
VALUES
( p_bom_head_unexp_rec.assembly_item_id
, p_bom_head_unexp_rec.organization_id
, DECODE(p_bom_header_rec.alternate_bom_code,
FND_API.G_MISS_CHAR,
NULL,
p_bom_header_rec.alternate_bom_code)
, DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
FND_API.G_MISS_NUM,
NULL,
p_bom_head_unexp_rec.bill_sequence_id,
NULL,
p_bom_head_unexp_rec.common_assembly_item_id
)
, DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
FND_API.G_MISS_NUM,
NULL,
p_bom_head_unexp_rec.bill_sequence_id,
NULL,
p_bom_head_unexp_rec.common_organization_id
)
, p_bom_header_rec.Assembly_Type /* Assembly Type */
, p_bom_head_unexp_rec.bill_sequence_id
, DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
FND_API.G_MISS_NUM,
p_bom_head_unexp_rec.bill_sequence_id,
NULL,
p_bom_head_unexp_rec.bill_sequence_id,
p_bom_head_unexp_rec.common_bill_sequence_id
)
, DECODE(p_bom_header_rec.assembly_comment,
FND_API.G_MISS_CHAR,
NULL,
p_bom_header_rec.assembly_comment)
, DECODE(p_bom_header_rec.original_system_reference,
FND_API.G_MISS_CHAR,
NULL,
p_bom_header_rec.original_system_reference)
, p_bom_header_rec.attribute_category
, p_bom_header_rec.attribute1
, p_bom_header_rec.attribute2
, p_bom_header_rec.attribute3
, p_bom_header_rec.attribute4
, p_bom_header_rec.attribute5
, p_bom_header_rec.attribute6
, p_bom_header_rec.attribute7
, p_bom_header_rec.attribute8
, p_bom_header_rec.attribute9
, p_bom_header_rec.attribute10
, p_bom_header_rec.attribute11
, p_bom_header_rec.attribute12
, p_bom_header_rec.attribute13
, p_bom_header_rec.attribute14
, p_bom_header_rec.attribute15
, SYSDATE
, BOM_Globals.Get_User_Id
, SYSDATE
, BOM_Globals.Get_User_Id
, BOM_Globals.Get_User_Id
, p_bom_head_unexp_rec.structure_type_id
, p_bom_header_rec.bom_implementation_date
, l_effectivity_control
, NULL
, p_bom_head_unexp_rec.assembly_item_id
, p_bom_head_unexp_rec.organization_id
, decode ( l_preferred_flag, 'N',null,'Y')
-- , p_bom_head_unexp_rec.source_bill_sequence_id
, DECODE(p_bom_head_unexp_rec.source_bill_sequence_id,
FND_API.G_MISS_NUM,
p_bom_head_unexp_rec.bill_sequence_id,
NULL,
p_bom_head_unexp_rec.bill_sequence_id,
p_bom_head_unexp_rec.source_bill_sequence_id
)
, Fnd_Global.Conc_Request_Id
, Fnd_Global.Conc_Program_Id
, Fnd_Global.Prog_Appl_Id
, sysdate
);
' :Inserting Record ' ||
SQLERRM
, x_mesg_token_Tbl => x_mesg_token_tbl
);
END Insert_Row;
* Procedure : Update_Row
* Parameters IN : BOM Header exposed column record
* BOM Header unexposed column record
* Parameters OUT: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an Update into the
* BOM_Bill_Of_Materials table.
*********************************************************************/
PROCEDURE Update_Row
( 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_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_Status IN OUT NOCOPY VARCHAR2
)
IS
BEGIN
--
-- The only fields that are updateable in BOM Header is the
-- common bill information
--
IF Bom_Globals.Get_Debug = 'Y' THEN
Error_Handler.Write_Debug('Updating bill seq ' || p_bom_head_unexp_rec.bill_sequence_id);
UPDATE bom_bill_of_materials
SET common_assembly_item_id =
DECODE(p_bom_head_unexp_rec.common_assembly_item_id,
FND_API.G_MISS_NUM,
null,
p_bom_head_unexp_rec.common_assembly_item_id
)
, common_organization_id =
DECODE(p_bom_head_unexp_rec.common_organization_id,
FND_API.G_MISS_NUM,
null,
p_bom_head_unexp_rec.common_organization_id
)
, common_bill_sequence_id =
DECODE(p_bom_head_unexp_rec.common_bill_sequence_id,
FND_API.G_MISS_NUM,
p_bom_head_unexp_rec.bill_sequence_id,
NULL,
p_bom_head_unexp_rec.bill_sequence_id,
p_bom_head_unexp_rec.common_bill_sequence_id
)
, specific_assembly_comment =
DECODE(p_bom_header_rec.assembly_comment,
FND_API.G_MISS_CHAR,
NULL,
p_bom_header_rec.assembly_comment
)
, original_system_reference =
DECODE(p_bom_header_rec.original_system_reference,
FND_API.G_MISS_CHAR,
NULL,
p_bom_header_rec.original_system_reference
)
, source_bill_sequence_id =
DECODE(p_bom_head_unexp_rec.source_bill_sequence_id,
FND_API.G_MISS_NUM,
p_bom_head_unexp_rec.bill_sequence_id,
NULL,
p_bom_head_unexp_rec.bill_sequence_id,
p_bom_head_unexp_rec.source_bill_sequence_id
)
, last_update_date = SYSDATE
, last_updated_by = BOM_Globals.Get_User_Id
, last_update_login = BOM_Globals.Get_User_Id
, attribute_category = p_bom_header_rec.attribute_category
, attribute1 = p_bom_header_rec.attribute1
, attribute2 = p_bom_header_rec.attribute2
, attribute3 = p_bom_header_rec.attribute3
, attribute4 = p_bom_header_rec.attribute4
, attribute5 = p_bom_header_rec.attribute5
, attribute6 = p_bom_header_rec.attribute6
, attribute7 = p_bom_header_rec.attribute7
, attribute8 = p_bom_header_rec.attribute8
, attribute9 = p_bom_header_rec.attribute9
, attribute10= p_bom_header_rec.attribute10
, attribute11= p_bom_header_rec.attribute11
, attribute12= p_bom_header_rec.attribute12
, attribute13= p_bom_header_rec.attribute13
, attribute14= p_bom_header_rec.attribute14
, attribute15= p_bom_header_rec.attribute15
, request_id = Fnd_Global.Conc_Request_Id
, program_id = Fnd_Global.Conc_Program_Id
, program_application_id = Fnd_Global.Prog_Appl_Id
, program_update_date = sysdate
, structure_type_id =
DECODE(p_bom_head_unexp_rec.structure_type_id,
FND_API.G_MISS_NUM,
structure_type_id,
NULL,
structure_type_id,
p_bom_head_unexp_rec.structure_type_id
)
WHERE bill_sequence_id = p_bom_head_unexp_rec.bill_sequence_id
;
END Update_Row;
* Procedure : Delete_Row
* Parameters IN : BOM Header exposed column record
* BOM Header unexposed column record
* Parameters OUT: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an Delete from the
* BOM_Bill_Of_Materials by creating a delete Group.
*********************************************************************/
PROCEDURE Delete_Row
( 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_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_Status IN OUT NOCOPY VARCHAR2
)
IS
Cursor CheckGroup is
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 DG.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 =>
l_mesg_token_tbl
);
DG.delete_group_sequence_id;
( p_message_name => 'NEW_DELETE_GROUP'
, p_mesg_token_tbl => l_mesg_token_Tbl
, x_mesg_token_tbl => l_mesg_token_tbl
, p_message_type => 'W' /* Warning */
);
select assembly_type into l_assembly_type
from bom_structures_b
where bill_sequence_id =l_bom_head_unexp_rec.bill_sequence_id;
MODAL_DELETE.DELETE_MANAGER
( new_group_seq_id => l_dg_sequence_id,
name => l_bom_header_rec.Delete_Group_Name,
group_desc => l_bom_header_rec.dg_description,
org_id => l_bom_head_unexp_rec.organization_id,
bom_or_eng => l_assembly_type /* dg type should be same as bill type */,
del_type => 2 /* Bill */,
ent_bill_seq_id => l_bom_head_unexp_rec.bill_sequence_id,
ent_rtg_seq_id => NULL,
ent_inv_item_id => l_bom_head_unexp_rec.assembly_item_id,
ent_alt_designator => l_bom_header_rec.alternate_bom_code,
ent_comp_seq_id => NULL,
ent_op_seq_id => NULL,
user_id => BOM_Globals.Get_User_Id
);
END Delete_Row;
Insert_Row
( p_bom_header_rec => p_bom_header_rec
, p_bom_head_unexp_rec => p_bom_head_unexp_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
BOM_GLOBALS.G_OPR_UPDATE
THEN
Update_Row
( p_bom_header_rec => p_bom_header_rec
, p_bom_head_unexp_rec => p_bom_head_unexp_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
BOM_GLOBALS.G_OPR_DELETE
THEN
Delete_Row
( p_bom_header_rec => p_bom_header_rec
, p_bom_head_unexp_rec => p_bom_head_unexp_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
select effectivity_control into l_eff_control
from mtl_system_items_b
where inventory_item_id = item_id
and organization_id = org_id;