[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
start_date
, completion_date
, batch_id
INTO
l_eam_res_inst_rec.start_date
, l_eam_res_inst_rec.completion_date
, l_eam_res_inst_rec.top_level_batch_id
FROM wip_op_resource_instances
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = p_operation_seq_num
AND resource_seq_num = p_resource_seq_num
AND instance_id = p_instance_id
AND serial_number IS null;
SELECT
serial_number
, start_date
, completion_date
, batch_id
INTO
l_eam_res_inst_rec.serial_number
, l_eam_res_inst_rec.start_date
, l_eam_res_inst_rec.completion_date
, l_eam_res_inst_rec.top_level_batch_id
FROM wip_op_resource_instances
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = p_operation_seq_num
AND resource_seq_num = p_resource_seq_num
AND instance_id = p_instance_id
AND serial_number = p_serial_number;
* Procedure : Insert_Row
* Parameters IN : Resource Instances column record
* Parameters OUT NOCOPY: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an insert into the
* win_op_resource_instances table.
*********************************************************************/
PROCEDURE Insert_Row
( p_eam_res_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
)
IS
BEGIN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Writing Resource Instance rec for ' || p_eam_res_inst_rec.instance_id); END IF;
INSERT INTO WIP_OP_RESOURCE_INSTANCES
( wip_entity_id
, organization_id
, operation_seq_num
, resource_seq_num
, instance_id
, serial_number
, start_date
, completion_date
, batch_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
)
VALUES
( p_eam_res_inst_rec.wip_entity_id
, p_eam_res_inst_rec.organization_id
, p_eam_res_inst_rec.operation_seq_num
, p_eam_res_inst_rec.resource_seq_num
, p_eam_res_inst_rec.instance_id
, p_eam_res_inst_rec.serial_number
, p_eam_res_inst_rec.start_date
, p_eam_res_inst_rec.completion_date
, p_eam_res_inst_rec.top_level_batch_id
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
);
, p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
, x_mesg_token_Tbl => x_mesg_token_tbl
);
END Insert_Row;
* Procedure : Update_Row
* Parameters IN : Resource Instances column record
* Parameters OUT NOCOPY: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an Update on the
* wip_op_resource_instances table.
*********************************************************************/
PROCEDURE Update_Row
( p_eam_res_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
)
IS
BEGIN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating Resource Instance: '|| p_eam_res_inst_rec.instance_id); END IF;
UPDATE WIP_OP_RESOURCE_INSTANCES
SET serial_number = p_eam_res_inst_rec.serial_number
, start_date = p_eam_res_inst_rec.start_date
, completion_date = p_eam_res_inst_rec.completion_date
, batch_id = p_eam_res_inst_rec.top_level_batch_id
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_eam_res_inst_rec.wip_entity_id
AND organization_id = p_eam_res_inst_rec.organization_id
AND operation_seq_num = p_eam_res_inst_rec.operation_seq_num
AND resource_seq_num = p_eam_res_inst_rec.resource_seq_num
AND instance_id = p_eam_res_inst_rec.instance_id
AND (serial_number IS NULL OR (serial_number = p_eam_res_inst_rec.serial_number));
END Update_Row;
* Procedure : Delete_Row
* Parameters IN : Resource Instances column record
* Parameters OUT NOCOPY: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an Update on the
* wip_op_resource_instances table.
*********************************************************************/
PROCEDURE Delete_Row
( p_eam_res_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
)
IS
BEGIN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Deleting Resource Instance: '|| p_eam_res_inst_rec.instance_id); END IF;
DELETE FROM WIP_OP_RESOURCE_INSTANCES
WHERE wip_entity_id = p_eam_res_inst_rec.wip_entity_id
AND organization_id = p_eam_res_inst_rec.organization_id
AND operation_seq_num = p_eam_res_inst_rec.operation_seq_num
AND resource_seq_num = p_eam_res_inst_rec.resource_seq_num
AND instance_id = p_eam_res_inst_rec.instance_id
AND (serial_number IS NULL OR (serial_number=p_eam_res_inst_rec.serial_number));
DELETE FROM WIP_OPERATION_RESOURCE_USAGE
WHERE wip_entity_id = p_eam_res_inst_rec.wip_entity_id
AND organization_id = p_eam_res_inst_rec.organization_id
AND operation_seq_num = p_eam_res_inst_rec.operation_seq_num
AND resource_seq_num = p_eam_res_inst_rec.resource_seq_num
AND instance_id = p_eam_res_inst_rec.instance_id;
DELETE FROM WIP_OPERATION_RESOURCE_USAGE
WHERE wip_entity_id = p_eam_res_inst_rec.wip_entity_id
AND organization_id = p_eam_res_inst_rec.organization_id
AND operation_seq_num = p_eam_res_inst_rec.operation_seq_num
AND resource_seq_num = p_eam_res_inst_rec.resource_seq_num
AND instance_id = p_eam_res_inst_rec.instance_id
AND serial_number = p_eam_res_inst_rec.serial_number;
END Delete_Row;
Insert_Row
( p_eam_res_inst_rec => p_eam_res_inst_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
ELSIF p_eam_res_inst_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
THEN
Update_Row
( p_eam_res_inst_rec => p_eam_res_inst_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
ELSIF p_eam_res_inst_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE
THEN
Delete_Row
( p_eam_res_inst_rec => p_eam_res_inst_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);