[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * FROM BOM_SUB_OPERATION_RESOURCES
WHERE (( l_bo_id = BOM_Rtg_Globals.G_ECO_BO
AND ACD_TYPE = p_acd_type )
OR
( l_bo_id = BOM_Rtg_Globals.G_RTG_BO
AND ACD_TYPE IS NULL )
)
AND BASIS_TYPE = p_basis_type
AND RESOURCE_ID = p_resource_id
AND SUBSTITUTE_GROUP_NUM = p_substiute_group_number
AND OPERATION_SEQUENCE_ID = p_operation_sequence_id
AND REPLACEMENT_GROUP_NUM = p_replacement_group_number ; --bug 2489765
* user has to perform any insert/update/deletes to the
* Sub Operation Resources table.
*********************************************************************/
PROCEDURE Perform_Writes
( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
, p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_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_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type ;
* user has to perform any insert/update/deletes to the
* Operation Resources table.
*********************************************************************/
PROCEDURE Perform_Writes
( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
, p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_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_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type ;
('Sub Operatin Sequence: Executing Insert Row. . . ') ;
Insert_Row
( p_rev_sub_resource_rec => l_rev_sub_resource_rec
, p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => x_mesg_token_tbl
) ;
ELSIF l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
THEN
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Sub Operatin Sequence: Executing Update Row. . . ') ;
Update_Row
( p_rev_sub_resource_rec => l_rev_sub_resource_rec
, p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => x_mesg_token_tbl
) ;
ELSIF l_rev_sub_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
THEN
IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
('Sub Operatin Sequence: Executing Delete Row. . . ') ;
Delete_Row
( p_rev_sub_resource_rec => l_rev_sub_resource_rec
, p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => x_mesg_token_tbl
) ;
* Procedure : Insert_Row
* Parameters IN : Revised Sub Operation Resource exposed column record
* Revised Sub Operation Resource unexposed column record
* Parameters out: Return Status
* Message Token Table
* Purpose : This procedure will insert a record in the Sub Operation Resource
* table; BOM_SUB_OPERATION_RESOURCES
PROCEDURE Insert_Row
( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
, p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_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_SUB_OPERATION_RESOURCES
(
operation_sequence_id
, substitute_group_num
, resource_id
, replacement_group_num
, 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
, principle_flag
, 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
, change_notice
, acd_type
, original_system_reference
, setup_id
)
VALUES (
p_rev_sub_res_unexp_rec.operation_sequence_id
, nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
, p_rev_sub_res_unexp_rec.resource_id
, p_rev_sub_resource_rec.replacement_group_number
, p_rev_sub_res_unexp_rec.activity_id
, p_rev_sub_resource_rec.standard_rate_flag
, p_rev_sub_resource_rec.assigned_units
, p_rev_sub_resource_rec.usage_rate_or_amount
, p_rev_sub_resource_rec.usage_rate_or_amount_inverse
, p_rev_sub_resource_rec.basis_type
, p_rev_sub_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_sub_resource_rec.resource_offset_percent
, p_rev_sub_resource_rec.autocharge_type
, p_rev_sub_resource_rec.principle_flag
, p_rev_sub_resource_rec.attribute_category
, p_rev_sub_resource_rec.attribute1
, p_rev_sub_resource_rec.attribute2
, p_rev_sub_resource_rec.attribute3
, p_rev_sub_resource_rec.attribute4
, p_rev_sub_resource_rec.attribute5
, p_rev_sub_resource_rec.attribute6
, p_rev_sub_resource_rec.attribute7
, p_rev_sub_resource_rec.attribute8
, p_rev_sub_resource_rec.attribute9
, p_rev_sub_resource_rec.attribute10
, p_rev_sub_resource_rec.attribute11
, p_rev_sub_resource_rec.attribute12
, p_rev_sub_resource_rec.attribute13
, p_rev_sub_resource_rec.attribute14
, p_rev_sub_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_sub_resource_rec.schedule_sequence_number
, p_rev_sub_resource_rec.eco_name
, p_rev_sub_resource_rec.acd_type
, p_rev_sub_resource_rec.original_system_reference
, p_rev_sub_res_unexp_rec.setup_id
) ;
('Unexpected Error occured in Insert . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Sub Op Resource Insert) ' ||
SUBSTR(SQLERRM, 1, 200);
END Insert_Row ;
* Procedure : Update_Row
* Parameters IN : Revised Sub Operation Resource exposed column record
* Revised Sub 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_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
, p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_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_SUB_OPERATION_RESOURCES
SET
replacement_group_num = NVL(p_rev_sub_resource_rec.new_replacement_group_number, p_rev_sub_resource_rec.replacement_group_number) -- bug 3741570
, resource_id = NVL(p_rev_sub_res_unexp_rec.new_resource_id, p_rev_sub_res_unexp_rec.resource_id)
, schedule_seq_num = p_rev_sub_resource_rec.schedule_sequence_number
, activity_id = p_rev_sub_res_unexp_rec.activity_id
, standard_rate_flag = p_rev_sub_resource_rec.standard_rate_flag
, assigned_units = p_rev_sub_resource_rec.assigned_units
, usage_rate_or_amount = p_rev_sub_resource_rec.usage_rate_or_amount
, usage_rate_or_amount_inverse = p_rev_sub_resource_rec.usage_rate_or_amount_inverse
, basis_type = NVL(p_rev_sub_resource_rec.new_basis_type,p_rev_sub_resource_rec.basis_type)
, schedule_flag = p_rev_sub_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_sub_resource_rec.resource_offset_percent
, autocharge_type = p_rev_sub_resource_rec.autocharge_type
, principle_flag = p_rev_sub_resource_rec.principle_flag
, attribute_category = p_rev_sub_resource_rec.attribute_category
, attribute1 = p_rev_sub_resource_rec.attribute1
, attribute2 = p_rev_sub_resource_rec.attribute2
, attribute3 = p_rev_sub_resource_rec.attribute3
, attribute4 = p_rev_sub_resource_rec.attribute4
, attribute5 = p_rev_sub_resource_rec.attribute5
, attribute6 = p_rev_sub_resource_rec.attribute6
, attribute7 = p_rev_sub_resource_rec.attribute7
, attribute8 = p_rev_sub_resource_rec.attribute8
, attribute9 = p_rev_sub_resource_rec.attribute9
, attribute10 = p_rev_sub_resource_rec.attribute10
, attribute11 = p_rev_sub_resource_rec.attribute11
, attribute12 = p_rev_sub_resource_rec.attribute12
, attribute13 = p_rev_sub_resource_rec.attribute13
, attribute14 = p_rev_sub_resource_rec.attribute14
, attribute15 = p_rev_sub_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 */
, original_system_reference = p_rev_sub_resource_rec.original_system_reference
, setup_Id = p_rev_sub_res_unexp_rec.setup_id
, request_id = Fnd_Global.Conc_Request_Id
WHERE NVL(acd_type, 0) = NVL(p_rev_sub_resource_rec.acd_type,0)
AND basis_type = p_rev_sub_resource_rec.basis_type
AND substitute_group_num = nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
AND resource_id = p_rev_sub_res_unexp_rec.resource_id
AND replacement_group_num = p_rev_sub_resource_rec.replacement_group_number -- bug 3741570
AND operation_sequence_id = p_rev_sub_res_unexp_rec.operation_sequence_id ;
('Unexpected Error occured in Update . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Sub Op Resource Update) ' ||
SUBSTR(SQLERRM, 1, 200);
END Update_Row ;
* Procedure : Delete_Row
* Parameters IN : Revised Sub Operation Resource exposed column record
* Revised Sub 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_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
, p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_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_SUB_OPERATION_RESOURCES
WHERE NVL(acd_type, 0) = NVL(p_rev_sub_resource_rec.acd_type,0)
AND basis_type = p_rev_sub_resource_rec.basis_type
AND substitute_group_num = nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
AND resource_id = p_rev_sub_res_unexp_rec.resource_id
AND replacement_group_num = p_rev_sub_resource_rec.replacement_group_number -- bug 3741570
AND operation_sequence_id = p_rev_sub_res_unexp_rec.operation_sequence_id
;
('Unexpected Error occured in Delete . . .' || SQLERRM);
l_err_text := G_PKG_NAME || ' : Utility (Sub Op Resource Delete) ' ||
SUBSTR(SQLERRM, 1, 200);
END Delete_Row ;