[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * FROM BOM_OPERATION_RESOURCES
WHERE (( l_bo_id = BOM_Rtg_Globals.G_ECO_BO
AND NVL(ACD_TYPE, FND_API.G_MISS_NUM)
= NVL(p_acd_type,FND_API.G_MISS_NUM))
OR
( l_bo_id = BOM_Rtg_Globals.G_RTG_BO
/* AND ACD_TYPE IS NULL
Bug 6378493 Commenting out the condition on the parameter ACD_type */
)
)
AND RESOURCE_SEQ_NUM = p_resource_sequence_number
AND OPERATION_SEQUENCE_ID = p_operation_sequence_id
;
SELECT old_operation_sequence_id
INTO l_operation_sequence_id
FROM BOM_OPERATION_SEQUENCES
WHERE operation_sequence_id = p_operation_sequence_id ;
* user has to perform any insert/update/deletes to the
* Operation Resources table.
*********************************************************************/
PROCEDURE Perform_Writes
( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
, p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_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_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
* user has to perform any insert/update/deletes to the
* Operation Resources table.
*********************************************************************/
PROCEDURE Perform_Writes
( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
, p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
, p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
, x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
('Operatin Resource : Executing Insert Row. . . ') ;
Insert_Row
( p_rev_op_resource_rec => l_rev_op_resource_rec
, p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
ELSIF l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Operatin Resource : Executing Update Row. . . ') ;
Update_Row
( p_rev_op_resource_rec => l_rev_op_resource_rec
, p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
ELSIF l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
THEN
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Operatin Resource : Executing Delete Row. . . ') ;
Delete_Row
( p_rev_op_resource_rec => l_rev_op_resource_rec
, p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
) ;
* Procedure : Insert_Row
* Parameters IN : Revised Operation Resource exposed column record
* Revised Operation Resource unexposed column record
* Parameters OUT: Return Status
* Message Token Table
* Purpose : This procedure will insert a record in the Operation Resource
* table; BOM_OPERATION_RESOURCES
PROCEDURE Insert_Row
( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
, p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
-- Error Handlig Variables
l_err_text VARCHAR2(2000) ;
INSERT INTO BOM_OPERATION_RESOURCES
(
operation_sequence_id
, resource_seq_num
, resource_id
, activity_id
, standard_rate_flag
, assigned_units
, usage_rate_or_amount
, usage_rate_or_amount_inverse
, basis_type
, schedule_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, resource_offset_percent
, autocharge_type
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, request_id
, program_application_id
, program_id
, program_update_date
, schedule_seq_num
, substitute_group_num
, principle_flag
, change_notice
, acd_type
, original_system_reference
, setup_id
)
VALUES (
p_rev_op_res_unexp_rec.operation_sequence_id
, p_rev_op_resource_rec.resource_sequence_number
, p_rev_op_res_unexp_rec.resource_id
, p_rev_op_res_unexp_rec.activity_id
, p_rev_op_resource_rec.standard_rate_flag
, p_rev_op_resource_rec.assigned_units
, p_rev_op_resource_rec.usage_rate_or_amount
, p_rev_op_resource_rec.usage_rate_or_amount_inverse
, p_rev_op_resource_rec.basis_type
, p_rev_op_resource_rec.schedule_flag
, SYSDATE -- Last Update Date
, BOM_Rtg_Globals.Get_User_Id -- Last Updated By
, SYSDATE -- Creation Date
, BOM_Rtg_Globals.Get_User_Id -- Created By
, BOM_Rtg_Globals.Get_Login_Id -- Last Update Login
, p_rev_op_resource_rec.resource_offset_percent
, p_rev_op_resource_rec.autocharge_type
, p_rev_op_resource_rec.attribute_category
, p_rev_op_resource_rec.attribute1
, p_rev_op_resource_rec.attribute2
, p_rev_op_resource_rec.attribute3
, p_rev_op_resource_rec.attribute4
, p_rev_op_resource_rec.attribute5
, p_rev_op_resource_rec.attribute6
, p_rev_op_resource_rec.attribute7
, p_rev_op_resource_rec.attribute8
, p_rev_op_resource_rec.attribute9
, p_rev_op_resource_rec.attribute10
, p_rev_op_resource_rec.attribute11
, p_rev_op_resource_rec.attribute12
, p_rev_op_resource_rec.attribute13
, p_rev_op_resource_rec.attribute14
, p_rev_op_resource_rec.attribute15
, Fnd_Global.Conc_Request_Id -- Request Id
, BOM_Rtg_Globals.Get_Prog_AppId -- Application Id
, BOM_Rtg_Globals.Get_Prog_Id -- Program Id
, SYSDATE -- program_update_date
, p_rev_op_resource_rec.schedule_sequence_number
, nvl(p_rev_op_resource_rec.substitute_group_number, p_rev_op_res_unexp_rec.substitute_group_number)
, p_rev_op_resource_rec.principle_flag
, p_rev_op_resource_rec.eco_name
, p_rev_op_resource_rec.acd_type
, p_rev_op_resource_rec.original_system_reference
, p_rev_op_res_unexp_rec.setup_id
) ;
('Unexpected Error occured in Insert . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Op Resource Insert) ' ||
SUBSTR(SQLERRM, 1, 200);
END Insert_Row ;
* Procedure : Update_Row
* Parameters IN : Revised Operation Resource exposed column record
* Revised Operation Resource unexposed column record
* Parameters OUT: Return Status
* Message Token Table
* Purpose : Update_Row procedure will update the production record with
* the user given values. Any errors will be returned by filling
* the Mesg_Token_Tbl and setting the return_status.
****************************************************************************/
PROCEDURE Update_Row
( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
, p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
-- Error Handlig Variables
l_err_text VARCHAR2(2000) ;
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing update operation . . .') ;
UPDATE BOM_OPERATION_RESOURCES
SET
resource_id = p_rev_op_res_unexp_rec.resource_id
, activity_id = p_rev_op_res_unexp_rec.activity_id
, standard_rate_flag = p_rev_op_resource_rec.standard_rate_flag
, assigned_units = p_rev_op_resource_rec.assigned_units
, usage_rate_or_amount = p_rev_op_resource_rec.usage_rate_or_amount
, usage_rate_or_amount_inverse = p_rev_op_resource_rec.usage_rate_or_amount_inverse
, basis_type = p_rev_op_resource_rec.basis_type
, schedule_flag = p_rev_op_resource_rec.schedule_flag
, last_update_date = SYSDATE /* Last Update Date */
, last_updated_by = BOM_Rtg_Globals.Get_User_Id /* Last Updated By */
, last_update_login = BOM_Rtg_Globals.Get_Login_Id /* Last Update Login */
, resource_offset_percent = p_rev_op_resource_rec.resource_offset_percent
, autocharge_type = p_rev_op_resource_rec.autocharge_type
, attribute_category = p_rev_op_resource_rec.attribute_category
, attribute1 = p_rev_op_resource_rec.attribute1
, attribute2 = p_rev_op_resource_rec.attribute2
, attribute3 = p_rev_op_resource_rec.attribute3
, attribute4 = p_rev_op_resource_rec.attribute4
, attribute5 = p_rev_op_resource_rec.attribute5
, attribute6 = p_rev_op_resource_rec.attribute6
, attribute7 = p_rev_op_resource_rec.attribute7
, attribute8 = p_rev_op_resource_rec.attribute8
, attribute9 = p_rev_op_resource_rec.attribute9
, attribute10 = p_rev_op_resource_rec.attribute10
, attribute11 = p_rev_op_resource_rec.attribute11
, attribute12 = p_rev_op_resource_rec.attribute12
, attribute13 = p_rev_op_resource_rec.attribute13
, attribute14 = p_rev_op_resource_rec.attribute14
, attribute15 = p_rev_op_resource_rec.attribute15
, program_application_id = BOM_Rtg_Globals.Get_Prog_AppId /* Application Id */
, program_id = BOM_Rtg_Globals.Get_Prog_Id /* Program Id */
, program_update_date = SYSDATE /* program_update_date */
, schedule_seq_num = p_rev_op_resource_rec.schedule_sequence_number
, substitute_group_num = nvl(p_rev_op_resource_rec.substitute_group_number, p_rev_op_res_unexp_rec.substitute_group_number)
, principle_flag = p_rev_op_resource_rec.principle_flag
, original_system_reference = p_rev_op_resource_rec.original_system_reference
, setup_id = p_rev_op_res_unexp_rec.setup_id
, request_id = Fnd_Global.Conc_Request_Id
WHERE operation_sequence_id = p_rev_op_res_unexp_rec.operation_sequence_id
AND resource_seq_num = p_rev_op_resource_rec.resource_sequence_number
AND NVL(acd_type, 0) = NVL(p_rev_op_resource_rec.acd_type,0) ;
('Unexpected Error occured in Update . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Op Resource Update) ' ||
SUBSTR(SQLERRM, 1, 200);
END Update_Row ;
* Procedure : Delete_Row
* Parameters IN : Revised Operation Resource exposed column record
* Revised Operation Resource unexposed column record
* Parameters OUT: Return Status
* Message Token Table
* Purpose : Delete_Row procedure will delete the production record with
* the user given values. Any errors will be returned by filling
* the Mesg_Token_Tbl and setting the return_status.
*
*********************************************************************/
PROCEDURE Delete_Row
( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
, p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
-- Error Handlig Variables
l_err_text VARCHAR2(2000) ;
DELETE FROM BOM_OPERATION_RESOURCES
WHERE operation_sequence_id = p_rev_op_res_unexp_rec.operation_sequence_id
AND resource_seq_num = p_rev_op_resource_rec.resource_sequence_number
AND NVL(acd_type, 1) = NVL(p_rev_op_resource_rec.acd_type,1) ;
DELETE FROM BOM_SUB_OPERATION_RESOURCES sor
WHERE NOT EXISTS ( SELECT 'AnOther Res not exist'
FROM BOM_OPERATION_RESOURCES bor
WHERE bor.substitute_group_num = sor.substitute_group_num
AND bor.operation_sequence_id = sor.operation_sequence_id
)
AND sor.substitute_group_num = nvl(p_rev_op_resource_rec.substitute_group_number, p_rev_op_res_unexp_rec.substitute_group_number)
AND sor.operation_sequence_id = p_rev_op_res_unexp_rec.operation_sequence_id ;
( p_Message_Name => 'BOM_RES_DELETE_SUB_RES'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Message_Type => 'W'
, p_token_tbl => l_token_tbl
) ;
('Unexpected Error occured in Delete . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Op Resource Delete) ' ||
SUBSTR(SQLERRM, 1, 200);
END Delete_Row ;