[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- 120.2 - Added procedure update_instance_usage to update instance usage records when updating instance dates.
-- Also added code to check if record exists in woru when inserting an instance record in woru.
-- Added code to update usage record in WORU for a resource having multiple rows only when expanding the resource dates.
-- 120.3 - Changes for accounting period cursor check.
-- 120.4 - Accounting period check rectified.
-- 120.5 - Changes for instance usage records. removed commented and debug statements.
-- 120.6 - Changes to support shift work order functionality. Remove all usage records to be updated from database
-- and re-insert these records again.
-- Populate first_unit_completion_date and last_unit_start_date in WO table too. Needed for forms lock_row.
-- Moved code to sync up wori and wor with woru at the end of update_resource_usage procedure.
-- 3/8/2005 Prashant Kathotia Initial Creation
-- 08/15/2006 - Changes for Bug 5408720 - Anju Gupta
***************************************************************************/
/*************************************************************************************************************************
* Procedure : update_resource
* Parameters IN :
p_curr_inst_rec
p_eam_res_tbl
* Parameters OUT NOCOPY:
x_eam_res_tbl
x_return_status
* Purpose : Procedure will propagate changes from instance level to resource level during
Bottom Up Scheduling.
************************************************************************************************************************/
procedure update_resource( p_curr_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type,
p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type,
x_eam_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type,
x_return_status OUT NOCOPY VARCHAR2 ) IS
l_resource_rec EAM_PROCESS_WO_PUB.eam_res_rec_type;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_resource ') ; END IF ;
l_resource_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
UPDATE wip_operation_resources
SET start_date = l_res_start_date ,
completion_date = l_res_end_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num;
UPDATE wip_operation_resource_usage
SET start_date = l_res_start_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND start_date = ( SELECT MIN(start_date)
FROM wip_operation_resource_usage
WHERE wip_entity_id = l_wip_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL)
AND instance_id IS NULL
AND serial_number IS NULL;
UPDATE wip_operation_resource_usage
SET completion_date = l_res_end_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND completion_date = ( SELECT MAX(completion_date)
FROM wip_operation_resource_usage
WHERE wip_entity_id = l_wip_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL)
AND instance_id IS NULL
AND serial_number IS NULL;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_resource ') ; END IF ;
END update_resource;
* Procedure : update_operations
* Parameters IN :
p_curr_res_rec
p_eam_op_tbl
* Parameters OUT NOCOPY:
x_eam_op_tbl
x_return_status
* Purpose : Procedure will propagate changes from resource level to operations level during
Bottom Up Scheduling.
************************************************************************************************************************/
procedure update_operations ( p_curr_res_rec IN EAM_PROCESS_WO_PUB.eam_res_rec_type,
p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type,
x_eam_op_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_tbl_type,
x_return_status OUT NOCOPY VARCHAR2 ) IS
l_operation_rec EAM_PROCESS_WO_PUB.eam_op_rec_type ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_operations ') ; END IF ;
l_operation_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
UPDATE wip_operations
SET first_unit_start_date = l_op_start_date,
first_unit_completion_date = l_op_end_date,
last_unit_start_date = l_op_start_date,
last_unit_completion_date = l_op_end_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_id
AND operation_seq_num = l_op_seq_num ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_operations ') ; END IF ;
END update_operations;
* Procedure : update_workorder
* Parameters IN :
p_curr_op_rec
p_eam_wo_rec
* Parameters OUT NOCOPY:
x_eam_wo_rec
x_return_status
* Purpose : Procedure will propagate changes from operations level to work order level during
Bottom Up Scheduling.
************************************************************************************************************************/
procedure update_workorder( p_curr_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type,
p_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type,
x_eam_wo_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type,
x_return_status OUT NOCOPY VARCHAR2 ) IS
l_op_start_date DATE ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_workorder ') ; END IF ;
UPDATE wip_discrete_jobs
SET scheduled_start_date = l_wo_start_date,
scheduled_completion_date = l_wo_end_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_curr_op_rec.wip_entity_id
AND organization_id = p_curr_op_rec.organization_id ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_workorder') ; END IF ;
END update_workorder;
SELECT start_date,
completion_date,
operation_seq_num,
resource_seq_num
FROM wip_operation_resources
WHERE wip_entity_id = c_wip_entity_id;
SELECT MIN(start_date) as min_inst_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND (instance_id IS NOT NULL OR serial_number IS not NULL) ;
SELECT MAX(completion_date) as max_inst_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND (instance_id is not null OR serial_number IS not NULL) ;
SELECT MIN(start_date) as min_inst_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND instance_id = c_instance_id
AND ( serial_number IS NULL OR serial_number = c_serial_num);
SELECT MAX(completion_date) as max_inst_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND instance_id = c_instance_id
AND ( serial_number IS NULL OR serial_number = c_serial_num);
SELECT MIN(start_date) as min_res_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL ;
SELECT MAX(completion_date) as max_inst_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL ;
SELECT start_date, completion_date, instance_id, serial_number
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num;
SELECT operation_seq_num, first_unit_start_date,
last_unit_completion_date
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id;
SELECT scheduled_start_date,
scheduled_completion_date
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id;
UPDATE wip_operation_resource_usage
SET start_date = c_instusage_min_date
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = c_opresource_rec.operation_seq_num
AND resource_seq_num = c_opresource_rec.resource_seq_num
AND instance_id IS NULL
AND serial_number IS NULL
AND rownum = 1;
UPDATE wip_operation_resource_usage
SET completion_date = c_instusage_max_date
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = c_opresource_rec.operation_seq_num
AND resource_seq_num = c_opresource_rec.resource_seq_num
AND instance_id IS NULL
AND serial_number IS NULL
AND rownum = 1;
UPDATE wip_op_resource_instances
SET start_date = c_instusagemin_date,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = c_opresource_rec.operation_seq_num
AND resource_seq_num = c_opresource_rec.resource_seq_num
AND instance_id = c_instdates_rec.instance_id
AND (serial_number IS NULL OR (serial_number = c_instdates_rec.serial_number));
UPDATE wip_op_resource_instances
SET completion_date = c_instusagemax_date,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = c_opresource_rec.operation_seq_num
AND resource_seq_num = c_opresource_rec.resource_seq_num
AND instance_id = c_instdates_rec.instance_id
AND (serial_number IS NULL OR (serial_number = c_instdates_rec.serial_number));
UPDATE wip_operation_resources
SET start_date = c_resusagemin_date,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = c_opresource_rec.operation_seq_num
AND organization_id = p_org_id
AND resource_seq_num = c_opresource_rec.resource_seq_num ;
UPDATE wip_operation_resources
SET completion_date = c_resusagemax_date,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = c_opresource_rec.operation_seq_num
AND organization_id = p_org_id
AND resource_seq_num = c_opresource_rec.resource_seq_num ;
select min(start_date), max(completion_date)
into l_min_res_date, l_max_res_date
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = c_operation_rec.operation_seq_num;
UPDATE wip_operations
SET first_unit_start_date = l_min_res_date,
last_unit_start_date = l_min_res_date,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = c_operation_rec.operation_seq_num ;
UPDATE wip_operations
SET first_unit_completion_date = l_max_res_date ,
last_unit_completion_date = l_max_res_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = c_operation_rec.operation_seq_num ;
select min(first_unit_start_date), max(last_unit_completion_date)
into l_min_date, l_max_date
from wip_operations
where wip_entity_id = p_wip_entity_id;
UPDATE wip_discrete_jobs
SET scheduled_start_date = l_min_date,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_org_id ;
UPDATE wip_discrete_jobs
SET scheduled_completion_date = l_max_date,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_org_id ;
* Procedure : insert_into_woru
* Parameters IN : p_eam_res_usage_rec
* Parameters OUT : x_return_status
* Purpose : Inserts a resource usage record in wip_operation_resource_usage table
************************************************************************************************************************/
procedure insert_into_woru ( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type,
x_return_status OUT NOCOPY VARCHAR2 ) IS
CURSOR res_usage_rec_check_csr IS
SELECT 1
FROM WIP_OPERATION_RESOURCE_USAGE
WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
AND start_date = p_eam_res_usage_rec.start_date
AND completion_date = p_eam_res_usage_rec.completion_date
AND instance_id IS NULL
AND serial_number IS NULL ;
SELECT 1
FROM WIP_OPERATION_RESOURCE_USAGE
WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
AND start_date = p_eam_res_usage_rec.start_date
AND completion_date = p_eam_res_usage_rec.completion_date
AND instance_id = p_eam_res_usage_rec.instance_id
AND ( serial_number IS NULL OR serial_number = p_eam_res_usage_rec.serial_number );
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside insert_into_woru' ) ; END IF ;
IF ( p_eam_res_usage_rec.instance_id IS NULL ) THEN -- ckeck if recource record has already been inserted
OPEN res_usage_rec_check_csr;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Insert record in WORU ' ) ; END IF ;
INSERT INTO WIP_OPERATION_RESOURCE_USAGE
( wip_entity_id
, operation_seq_num
, resource_seq_num
, organization_id
, start_date
, completion_date
, assigned_units
, instance_id
, serial_number
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date)
VALUES
( p_eam_res_usage_rec.wip_entity_id
, p_eam_res_usage_rec.operation_seq_num
, p_eam_res_usage_rec.resource_seq_num
, p_eam_res_usage_rec.organization_id
, p_eam_res_usage_rec.start_date
, p_eam_res_usage_rec.completion_date
, NVL( p_eam_res_usage_rec.assigned_units , 1 )
, p_eam_res_usage_rec.instance_id
, p_eam_res_usage_rec.serial_number
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, p_eam_res_usage_rec.request_id
, p_eam_res_usage_rec.program_application_id
, p_eam_res_usage_rec.program_id
, SYSDATE);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting insert_into_woru with status ' || l_return_status ) ; END IF ;
END insert_into_woru;
* Procedure : update_woru
* Parameters IN : p_eam_res_usage_rec
* Purpose : Table Handler :- Updates a resource usage record in wip_operation_resource_usage table
************************************************************************************************************************/
procedure update_woru( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type) IS
BEGIN
IF p_eam_res_usage_rec.instance_id IS NULL THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_woru: Updating resource record ' ) ; END IF ;
UPDATE WIP_OPERATION_RESOURCE_USAGE
SET start_date = p_eam_res_usage_rec.start_date ,
completion_date = p_eam_res_usage_rec.completion_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
AND organization_id = p_eam_res_usage_rec.organization_id
AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
AND start_date = p_eam_res_usage_rec.old_start_date
AND completion_date = p_eam_res_usage_rec.old_completion_date
AND instance_id IS NULL ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_woru: Updating resource Instance record ' ) ; END IF ;
UPDATE WIP_OPERATION_RESOURCE_USAGE
SET start_date = p_eam_res_usage_rec.start_date ,
completion_date = p_eam_res_usage_rec.completion_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
AND organization_id = p_eam_res_usage_rec.organization_id
AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
AND start_date = p_eam_res_usage_rec.old_start_date
AND completion_date = p_eam_res_usage_rec.old_completion_date
AND instance_id = p_eam_res_usage_rec.instance_id
AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_woru' ) ; END IF ;
END update_woru ;
* Procedure : delete_from_woru
* Parameters IN : p_eam_res_usage_rec
* Purpose : Table Handler :- Deletes a resource usage record in wip_operation_resource_usage table
************************************************************************************************************************/
procedure delete_from_woru( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type ) IS
l_count NUMBER;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside delete_from_woru' ) ; END IF ;
DELETE FROM WIP_OPERATION_RESOURCE_USAGE
WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
AND organization_id = p_eam_res_usage_rec.organization_id
AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
AND start_date = p_eam_res_usage_rec.start_date
AND completion_date = p_eam_res_usage_rec.completion_date
AND instance_id = p_eam_res_usage_rec.instance_id
AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
SELECT count(*)
INTO l_count
FROM wip_operation_resource_usage
WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
AND operation_seq_num =p_eam_res_usage_rec.operation_seq_num
AND organization_id = p_eam_res_usage_rec.organization_id
AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
AND instance_id = p_eam_res_usage_rec.instance_id
AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
DELETE FROM wip_op_resource_instances
WHERE wip_entity_id =p_eam_res_usage_rec.wip_entity_id
AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
AND organization_id = p_eam_res_usage_rec.organization_id
AND resource_seq_num =p_eam_res_usage_rec.resource_seq_num
AND instance_id = p_eam_res_usage_rec.instance_id
AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting delete_from_woru' ) ; END IF ;
END delete_from_woru ;
* Procedure : update_wori
* Parameters IN : p_eam_res_usage_rec
p_eam_res_inst_tbl
* Parameters OUT : x_eam_res_inst_tbl
x_return_status
* Purpose : Updates/Deletes record from wip_op_resource_instances table . Returns changed
instance records in x_eam_res_inst_tbl.
************************************************************************************************************************/
procedure update_wori ( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type
,p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
,x_eam_res_inst_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
,x_return_status OUT NOCOPY VARCHAR ) IS
l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type ;
l_update VARCHAR2(1) ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_wori ' ) ; END IF ;
SELECT count(*)
INTO l_count
FROM wip_operation_resource_usage
WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
AND operation_seq_num =p_eam_res_usage_rec.operation_seq_num
AND organization_id = p_eam_res_usage_rec.organization_id
AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
AND instance_id = p_eam_res_usage_rec.instance_id
AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
SELECT min(start_date), max(completion_date)
INTO l_min_start_date,l_max_completion_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_operation_seq_num
AND organization_id = l_org_id
AND resource_seq_num = l_res_seq_num
AND instance_id = l_instance_id
AND (serial_number IS NULL OR (serial_number = l_serial_number));
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Update WORI' ) ; END IF ;
UPDATE wip_op_resource_instances
SET start_date = l_min_start_date,
completion_date = l_max_completion_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_operation_seq_num
AND organization_id = l_org_id
AND resource_seq_num = l_res_seq_num
AND instance_id = l_instance_id
AND (serial_number IS NULL OR (serial_number = l_serial_number));
-- now update the pl/sql table for instances.
l_eam_res_inst_tbl_index := l_eam_res_inst_tbl.FIRST;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_wori ' ) ; END IF ;
END update_wori;
* Procedure : update_wor
* Parameters IN : p_eam_res_usage_rec
p_eam_res_tbl
* Parameters OUT : x_eam_res_tbl
x_return_status
* Purpose : Updates/Deletes record from wip_op_resource_instances table . Returns changed
instance records in x_eam_res_tbl.
************************************************************************************************************************/
procedure update_wor ( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type
,p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type
,x_eam_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type
,x_return_status OUT NOCOPY VARCHAR ) IS
l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
l_update VARCHAR2(1) ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_wor' ) ; END IF ;
SELECT min(start_date), max(completion_date)
INTO l_min_start_date,l_max_completion_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_operation_seq_num
AND organization_id = l_org_id
AND resource_seq_num = l_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL;
l_update := 'N' ;
IF ( NVL(l_update,'Y') = 'Y' ) THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating dates in from WOR ' ) ; END IF ;
UPDATE wip_operation_resources
SET start_date = l_min_start_date,
completion_date = l_max_completion_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_operation_seq_num
AND organization_id = l_org_id
AND resource_seq_num = l_res_seq_num ;
-- now update the pl/sql table for resources.
l_eam_res_tbl_index := l_eam_res_tbl.FIRST;
END IF ; -- end of l_update
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' exiting update_wor ' ) ; END IF ;
END update_wor;
* Procedure : update_resource_usage
* Parameters IN :
p_eam_res_tbl
p_eam_res_inst_tbl
p_eam_res_usage_tbl
* Parameters OUT NOCOPY:
x_eam_res_tbl
x_eam_res_usage_tbl
x_eam_res_inst_tbl
x_return_status
x_message_name
* Purpose : Procedure will update Resource Usage table when a resource is added, its dates are changed
or usage record is added/updated/deleted. Corresponding changes are also done to WORI
************************************************************************************************************************/
procedure update_resource_usage(
p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type
, p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
, p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type
, x_eam_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type
, x_eam_res_usage_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type
, x_eam_res_inst_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
, x_return_status OUT NOCOPY VARCHAR2
, x_message_name OUT NOCOPY VARCHAR2
) IS
CURSOR get_inst_usage_min_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ,
c_instance_id NUMBER , c_serial_num VARCHAR2 ) IS
SELECT MIN(start_date) as min_inst_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND instance_id = c_instance_id
AND ( serial_number IS NULL OR serial_number = c_serial_num) ;
SELECT MAX(completion_date) as max_inst_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND instance_id = c_instance_id
AND ( serial_number IS NULL OR serial_number = c_serial_num) ;
SELECT MIN(start_date) as min_res_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL ;
SELECT MAX(completion_date) as max_inst_usg_date
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL ;
SELECT start_date, completion_date, instance_id, serial_number
FROM wip_operation_resource_usage
WHERE wip_entity_id = c_wip_entity_id
AND operation_seq_num = c_op_seq_num
AND resource_seq_num = c_res_seq_num;
SAVEPOINT UPDATE_RES_USAGE ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Entering update_resource_usage ') ; END IF ;
-- insert record into the resource usage pl/sql table
l_eam_res_usage_tbl( l_eam_res_usage_tbl.COUNT + 1) := l_eam_res_usage_rec ;
ELSIF ( l_eam_res_inst_tbl(l_res_inst_tbl_index).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE )THEN
l_wip_entity_id := l_eam_res_inst_tbl(l_res_inst_tbl_index).wip_entity_id ;
UPDATE wip_operation_resource_usage
SET start_date = l_eam_res_inst_tbl(l_res_inst_tbl_index).start_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND start_date = l_min_date
AND instance_id = l_instance_id
AND ( serial_number IS NULL OR serial_number = l_serial_num);
UPDATE wip_operation_resource_usage
SET completion_date = l_eam_res_inst_tbl(l_res_inst_tbl_index).completion_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND completion_date = l_max_date
AND instance_id = l_instance_id
AND ( serial_number IS NULL OR serial_number = l_serial_num);
-- insert record into the resource usage pl/sql table
l_eam_res_usage_tbl( l_eam_res_usage_tbl.COUNT + 1) := l_eam_res_usage_rec ;
ELSIF ( l_eam_res_tbl(l_res_tbl_index).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE )THEN
l_wip_entity_id := l_eam_res_tbl(l_res_tbl_index).wip_entity_id ;
select count(*) into l_woru_count
from wip_operation_resource_usage where
wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL ;
UPDATE wip_operation_resource_usage
SET start_date = l_eam_res_tbl(l_res_tbl_index).start_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL ;
UPDATE wip_operation_resource_usage
SET completion_date = l_eam_res_tbl(l_res_tbl_index).completion_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND instance_id IS NULL
AND serial_number IS NULL ;
select count(*) into l_woru_count
from wip_operation_resource_usage
where
wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND instance_id = c_instdates_rec.instance_id
AND ( serial_number IS NULL OR serial_number = c_instdates_rec.serial_number);
IF l_woru_count=1 THEN -- Update WORU rows for instances.Only for 24 hr resources
UPDATE wip_operation_resource_usage
SET start_date = l_eam_res_tbl(l_res_tbl_index).start_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND instance_id = c_instdates_rec.instance_id
AND ( serial_number IS NULL OR serial_number = c_instdates_rec.serial_number);
UPDATE wip_operation_resource_usage
SET completion_date =l_eam_res_tbl(l_res_tbl_index).completion_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND instance_id = c_instdates_rec.instance_id
AND ( serial_number IS NULL OR serial_number = c_instdates_rec.serial_number);
UPDATE wip_operation_resource_usage
SET start_date = l_eam_res_tbl(l_res_tbl_index).start_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND start_date = l_min_date
AND instance_id IS NULL
AND serial_number IS NULL ;
UPDATE wip_operation_resource_usage
SET completion_date = l_eam_res_tbl(l_res_tbl_index).completion_date ,
last_update_date = sysdate ,
last_updated_by = FND_GLOBAL.user_id ,
creation_date = sysdate ,
created_by = FND_GLOBAL.user_id ,
last_update_login = FND_GLOBAL.login_id
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = l_op_seq_num
AND resource_seq_num = l_res_seq_num
AND completion_date = l_max_date
AND instance_id IS NULL
AND serial_number IS NULL ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Purging resource and instance records for update from WORU ' ) ; END IF ;
IF ( l_eam_res_usage_tbl(i).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE ) THEN
IF ( l_eam_res_usage_tbl(i).instance_id IS NULL ) THEN
DELETE FROM wip_operation_resource_usage
WHERE wip_entity_id = l_eam_res_usage_tbl(i).wip_entity_id
AND operation_seq_num = l_eam_res_usage_tbl(i).operation_seq_num
AND resource_seq_num = l_eam_res_usage_tbl(i).resource_seq_num
AND instance_id IS NULL
AND start_date = l_eam_res_usage_tbl(i).old_start_date
AND completion_date = l_eam_res_usage_tbl(i).old_completion_date ;
DELETE FROM wip_operation_resource_usage
WHERE wip_entity_id = l_eam_res_usage_tbl(i).wip_entity_id
AND operation_seq_num = l_eam_res_usage_tbl(i).operation_seq_num
AND resource_seq_num = l_eam_res_usage_tbl(i).resource_seq_num
AND instance_id = l_eam_res_usage_tbl(i).instance_id
AND ( serial_number IS NULL OR serial_number = l_eam_res_usage_tbl(i).serial_number )
AND start_date = l_eam_res_usage_tbl(i).old_start_date
AND completion_date = l_eam_res_usage_tbl(i).old_completion_date ;
IF ( l_eam_res_usage_rec.transaction_type IN ( EAM_PROCESS_WO_PUB.G_OPR_CREATE , EAM_PROCESS_WO_PUB.G_OPR_UPDATE) ) THEN
-- call insert usage method
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Txn: Create resource usage ') ; END IF ;
insert_into_woru( l_eam_res_usage_rec , l_return_status ) ;
ELSIF ( l_eam_res_usage_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_DELETE ) THEN
-- call method delete usage and update inst table.
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Txn:Delete resource usage ') ; END IF ;
delete_from_woru( l_eam_res_usage_rec ) ;
update_wori ( l_eam_res_usage_rec
,p_eam_res_inst_tbl
,l_eam_res_inst_tbl
,l_return_status );
update_wor ( l_eam_res_usage_rec
, l_eam_res_tbl
, l_out_eam_res_tbl
, l_return_status );
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_resource_usage ') ; END IF ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Error occured in update_resource_usage API ') ; END IF ;
ROLLBACK TO UPDATE_RES_USAGE ;
END update_resource_usage;