[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE_RES_USAGE CONSTANT NUMBER := 7;
G_MATERIAL_UPDATE CONSTANT NUMBER := 8;
SELECT enable_workflow
INTO l_workflow_enabled
FROM EAM_ENABLE_WORKFLOW
WHERE MAINTENANCE_OBJECT_SOURCE =p_maint_obj_source;
SELECT eam_wo_workflow_enabled
INTO l_workflow_enabled
FROM WIP_EAM_PARAMETERS
WHERE organization_id =p_organization_id;
SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
INTO l_wf_event_seq
FROM DUAL;
l_parameter_list.DELETE;
OR (p_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE --workorder updated
--and old status is not same as new status
AND (p_old_eam_wo_rec.user_defined_status_id <>p_eam_wo_rec.user_defined_status_id OR NVL(p_old_eam_wo_rec.pending_flag,'N')='Y' )
))
AND (WF_EVENT.TEST(l_status_changed_event) <> 'NONE') --if status change event enabled
) THEN
SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
INTO l_wf_event_seq
FROM DUAL;
l_parameter_list.DELETE;
SELECT NVL((SUM(system_estimated_mat_cost) + SUM(system_estimated_lab_cost) + SUM(system_estimated_eqp_cost)),0)
INTO l_cost_estimate
FROM WIP_EAM_PERIOD_BALANCES
WHERE wip_entity_id = p_eam_wo_rec.wip_entity_id;
SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
INTO l_wf_event_seq
FROM DUAL;
INSERT INTO EAM_WO_WORKFLOWS
(WIP_ENTITY_ID,WF_ITEM_TYPE,WF_ITEM_KEY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
VALUES
(p_eam_wo_rec.wip_entity_id,p_workflow_name,l_event_key,SYSDATE,FND_GLOBAL.user_id,
SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id
);
l_parameter_list.DELETE;
PROCEDURE UPDATE_INTERMEDIA_INDEX
(
p_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type,
p_old_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type,
p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type,
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
)
IS
l_update_index BOOLEAN;
l_update_index := FALSE;
p_event => 'INSERT',
p_wip_entity_id =>p_eam_wo_rec.wip_entity_id,
p_organization_id =>p_eam_wo_rec.organization_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login =>FND_GLOBAL.login_id
);
IF (p_eam_wo_rec.transaction_type = G_OPR_UPDATE AND
((NVL(p_eam_wo_rec.description,'') <> NVL(p_old_eam_wo_rec.description,'')) OR
(p_eam_wo_rec.maintenance_object_id <> p_old_eam_wo_rec.maintenance_object_id) OR
(p_eam_wo_rec.user_defined_status_id <> p_old_eam_wo_rec.user_defined_status_id) OR
(NVL(p_eam_wo_rec.asset_activity_id,-1) <> NVL(p_old_eam_wo_rec.asset_activity_id,-1))
OR (NVL(p_eam_wo_rec.owning_department,-1) <> NVL(p_old_eam_wo_rec.owning_department,-1))
OR (NVL(p_eam_wo_rec.project_id,-1) <> NVL(p_old_eam_wo_rec.project_id,-1))
OR (NVL(p_eam_wo_rec.task_id,-1) <> NVL(p_old_eam_wo_rec.task_id,-1))
OR (NVL(p_eam_wo_rec.priority,-1) <> NVL(p_old_eam_wo_rec.priority,-1))
OR (NVL(p_eam_wo_rec.work_order_type,'') <> NVL(p_old_eam_wo_rec.work_order_type,''))
OR (NVL(p_eam_wo_rec.activity_type,'') <> NVL(p_old_eam_wo_rec.activity_type,''))
OR (NVL(p_eam_wo_rec.activity_cause,'') <> NVL(p_old_eam_wo_rec.activity_cause,''))
OR (NVL(p_eam_wo_rec.activity_source,'') <> NVL(p_old_eam_wo_rec.activity_source,''))
)
) THEN
l_update_index := TRUE;
IF(l_update_index = FALSE) THEN
IF(p_eam_op_tbl IS NOT NULL AND p_eam_op_tbl.COUNT>0) THEN
FOR i IN p_eam_op_tbl.FIRST .. p_eam_op_tbl.LAST LOOP
IF(p_eam_op_tbl(i).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_CREATE
OR p_eam_op_tbl(i).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_DELETE) THEN
l_update_index:=TRUE;
IF(l_update_index = FALSE) THEN
IF(p_eam_res_tbl IS NOT NULL AND p_eam_res_tbl.COUNT>0) THEN
FOR i IN p_eam_res_tbl.FIRST .. p_eam_res_tbl.LAST LOOP
IF(p_eam_res_tbl(i).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_CREATE
OR p_eam_res_tbl(i).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_DELETE) THEN
l_update_index:=TRUE;
IF(l_update_index = FALSE) THEN
IF(p_eam_res_inst_tbl IS NOT NULL AND p_eam_res_inst_tbl.COUNT>0) THEN
FOR i IN p_eam_res_inst_tbl.FIRST .. p_eam_res_inst_tbl.LAST LOOP
IF(p_eam_res_inst_tbl(i).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_CREATE
OR p_eam_res_inst_tbl(i).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_DELETE) THEN
l_update_index:=TRUE;
IF(l_update_index = TRUE) THEN
EAM_TEXT_UTIL.PROCESS_WO_EVENT
(
p_event => 'UPDATE',
p_wip_entity_id =>p_eam_wo_rec.wip_entity_id,
p_organization_id =>p_eam_wo_rec.organization_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login =>FND_GLOBAL.login_id
);
END UPDATE_INTERMEDIA_INDEX;
/* Bug 5224748. Call Add_usage only for non firm work orders. For firm wos, Bottom Up should handle the insertions*/
IF ( x_bottomup_scheduled = G_NON_FIRM_WORKORDER ) THEN
EAM_RES_USAGE_UTILITY_PVT.Add_Usage
( p_eam_res_usage_rec => l_eam_res_usage_rec
, x_mesg_token_tbl => l_mesg_token_tbl
, x_return_status => l_return_status
);
OR (l_eam_res_usage_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_DELETE ) --deleted
OR (l_eam_res_usage_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE AND --updating the resource usage
(
--NVL(l_eam_res_rec.schedule_seq_num,l_eam_res_rec.resource_seq_num)<>NVL(l_old_eam_res_rec.schedule_seq_num,l_old_eam_res_rec.resource_seq_num)
-- OR
l_eam_res_usage_rec.start_date <> l_eam_res_usage_rec.old_start_date --shedule_seq_num,start_date,completion_date
OR l_eam_res_usage_rec.completion_date <> l_eam_res_usage_rec.old_completion_date
-- OR l_eam_res_rec.resource_id <> l_old_eam_res_rec.resource_id --resource_code,usage_rate_or_amount,scheduled_flag,assigned_units
-- OR l_eam_res_rec.usage_rate_or_amount <> l_old_eam_res_rec.usage_rate_or_amount
-- OR l_eam_res_rec.scheduled_flag <> l_old_eam_res_rec.scheduled_flag
-- OR NVL(l_eam_res_rec.assigned_units,0) <> NVL(l_old_eam_res_rec.assigned_units,0)
)
)
) THEN
x_bottomup_scheduled := G_UPDATE_RES_USAGE;
IF l_eam_sub_res_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Populate NULL columns') ;
IF l_eam_res_inst_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Populate NULL columns') ;
-- OR (l_eam_res_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_DELETE AND l_eam_res_rec.scheduled_flag=1) --deleted and was scheduled
OR (l_eam_res_inst_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE AND --updating the resource iinstance
(
--NVL(l_eam_res_rec.schedule_seq_num,l_eam_res_rec.resource_seq_num)<>NVL(l_old_eam_res_rec.schedule_seq_num,l_old_eam_res_rec.resource_seq_num)
-- OR
l_eam_res_inst_rec.start_date <> l_old_eam_res_inst_rec.start_date --shedule_seq_num,start_date,completion_date
OR l_eam_res_inst_rec.completion_date <> l_old_eam_res_inst_rec.completion_date
-- OR l_eam_res_rec.resource_id <> l_old_eam_res_rec.resource_id --resource_code,usage_rate_or_amount,scheduled_flag,assigned_units
-- OR l_eam_res_rec.usage_rate_or_amount <> l_old_eam_res_rec.usage_rate_or_amount
-- OR l_eam_res_rec.scheduled_flag <> l_old_eam_res_rec.scheduled_flag
-- OR NVL(l_eam_res_rec.assigned_units,0) <> NVL(l_old_eam_res_rec.assigned_units,0)
)
)
) THEN
x_bottomup_scheduled := G_UPDATE_RES_USAGE;
IF l_eam_op_network_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Populate NULL columns') ;
AND l_eam_op_network_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_CREATE,EAM_PROCESS_WO_PVT.G_OPR_DELETE)
) THEN -- if op dependency is added or deleted
x_schedule_wo := G_SCHEDULE_WO;
AND l_eam_op_network_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_CREATE,EAM_PROCESS_WO_PVT.G_OPR_DELETE)
) THEN -- if op dependency is added or deleted
x_bottomup_scheduled := G_BU_SCHEDULE_WO;
IF l_eam_mat_req_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
IF l_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE THEN
x_material_shortage := G_MATERIAL_UPDATE;
x_material_shortage := G_MATERIAL_UPDATE;
SELECT concatenated_segments into l_token_tbl(1).token_value
FROM mtl_system_items_kfv msik
WHERE msik.inventory_item_id = l_eam_mat_req_rec.inventory_item_id
AND msik.organization_id = l_eam_mat_req_rec.organization_id;
x_material_shortage := G_MATERIAL_UPDATE;
select primary_uom_code, stock_enabled_flag,
purchasing_item_flag, description
into l_uom_code, l_non_stock_flag,
l_purch_flag, l_description
from mtl_system_items_b_kfv
where inventory_item_id = l_eam_mat_req_rec.inventory_item_id
and organization_id = l_eam_mat_req_rec.organization_id;
((l_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
and l_old_eam_mat_req_rec.auto_request_material = 'N'
and l_eam_mat_req_rec.auto_request_material = 'Y' -- AUTO_REQUEST_MATERIAL flag turned on
) OR
(l_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
and l_old_eam_mat_req_rec.required_quantity <> l_eam_mat_req_rec.required_quantity -- reqd qty updating
) OR
(l_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
and l_eam_mat_req_rec.auto_request_material = 'Y' -- new mtl line adding with auto req flag on.
)
)
THEN
l_eam_mat_req_rec.invoke_allocations_api := 'Y';
select status_type into l_wo_status from
wip_discrete_jobs
where wip_entity_id = l_eam_mat_req_rec.wip_entity_id
and organization_id = l_eam_mat_req_rec.organization_id;
select mic.category_id into l_purch_cat_id
from mtl_item_categories mic,
mtl_default_category_sets mdcs where
mic.inventory_item_id = l_eam_mat_req_rec.inventory_item_id
and mic.organization_id = l_eam_mat_req_rec.organization_id
and mic.category_set_id = mdcs.category_set_id
and mdcs.functional_area_id = 2;
OR (l_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
/*and l_old_eam_mat_req_rec.required_quantity < l_eam_mat_req_rec.required_quantity commented for #6118897*/)
OR (l_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
and nvl(l_old_eam_mat_req_rec.auto_request_material,'N')='N')
)then
select mic.category_id into l_purch_cat_id
from mtl_item_categories mic,
mtl_default_category_sets mdcs where
mic.inventory_item_id = l_eam_mat_req_rec.inventory_item_id
and mic.organization_id = l_eam_mat_req_rec.organization_id
and mic.category_set_id = mdcs.category_set_id
and mdcs.functional_area_id = 2;
SELECT SUM(nvl(req_qty,0)) INTO l_req_qty
FROM
(SELECT SUM(nvl(quantity,0)) req_qty
FROM po_requisitions_interface_all pria
WHERE pria.wip_entity_id = l_eam_mat_req_rec.wip_entity_id
AND pria.destination_organization_id = l_eam_mat_req_rec.organization_id
AND pria.wip_operation_seq_num = l_eam_mat_req_rec.operation_seq_num
AND pria.item_id = l_eam_mat_req_rec.inventory_item_id
AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
UNION ALL
SELECT sum(rql.quantity) req_qty
FROM po_requisition_lines_all rql, po_requisition_headers_all rqh
WHERE rql.wip_entity_id = l_eam_mat_req_rec.wip_entity_id
AND rql.destination_organization_id = l_eam_mat_req_rec.organization_id
AND rql.wip_operation_seq_num = l_eam_mat_req_rec.operation_seq_num
AND rql.requisition_header_id = rqh.requisition_header_id(+)
AND upper(NVL(rqh.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND rql.wip_resource_seq_num is null
AND rql.item_id = l_eam_mat_req_rec.inventory_item_id
);
SELECT sum(pda.quantity_ordered) INTO l_po_ordered_qty
FROM po_lines_all poa, po_distributions_all pda
WHERE poa.po_line_id = pda.po_line_id
AND pda.wip_entity_id = l_eam_mat_req_rec.wip_entity_id
AND pda.destination_organization_id= l_eam_mat_req_rec.organization_id
AND pda.wip_operation_seq_num = l_eam_mat_req_rec.operation_seq_num
AND upper(nvl(poa.cancel_flag, 'N')) <> 'Y'
AND pda.wip_resource_seq_num is null
AND poa.item_id = l_eam_mat_req_rec.inventory_item_id;
IF l_eam_direct_items_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
IF l_eam_direct_items_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE THEN
x_material_shortage := G_MATERIAL_UPDATE;
x_material_shortage := G_MATERIAL_UPDATE;
x_material_shortage := G_MATERIAL_UPDATE;
select status_type into l_wo_status from
wip_discrete_jobs
where wip_entity_id = l_eam_direct_items_rec.wip_entity_id
and organization_id = l_eam_direct_items_rec.organization_id;
OR (l_eam_direct_items_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
/*and l_old_eam_direct_items_rec.required_quantity < l_eam_direct_items_rec.required_quantity commented for # 6118897*/)
OR (l_eam_direct_items_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
and nvl(l_old_eam_direct_items_rec.auto_request_material,'N')='N'
and nvl(l_eam_direct_items_rec.auto_request_material,'N')='Y')
) then
-- query modified for bug# 3691325.
/*Added Union for #6118897 to avoid duplicate req creation -- start --*/
SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
FROM
(SELECT SUM(nvl(quantity,0)) req_qty
FROM po_requisitions_interface_all pria
WHERE pria.wip_entity_id = l_eam_direct_items_rec.wip_entity_id
AND pria.destination_organization_id = l_eam_direct_items_rec.organization_id
AND pria.wip_operation_seq_num = l_eam_direct_items_rec.operation_seq_num
AND pria.item_id is null
AND pria.wip_resource_seq_num = l_eam_direct_items_rec.direct_item_sequence_id
AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
UNION ALL
select greatest(nvl(po_quantity_ordered-po_quantity_cancelled,0), nvl(rql_quantity_ordered,0), nvl(quantity_received,0)) req_qty
from eam_work_order_direct_items_v ewodi
where ewodi.wip_entity_id = l_eam_direct_items_rec.wip_entity_id
and ewodi.organization_id = l_eam_direct_items_rec.organization_id
and ewodi.operation_seq_num = l_eam_direct_items_rec.operation_seq_num
and ewodi.direct_item_sequence_id = l_eam_direct_items_rec.direct_item_sequence_id
);
IF l_eam_res_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Populate NULL columns') ;
OR (l_eam_res_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_DELETE AND l_eam_res_rec.scheduled_flag=1) --deleted and was scheduled
OR (l_eam_res_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE AND --updating the resource
(NVL(l_eam_res_rec.schedule_seq_num,l_eam_res_rec.resource_seq_num)<>NVL(l_old_eam_res_rec.schedule_seq_num,l_old_eam_res_rec.resource_seq_num)
OR l_eam_res_rec.start_date <> l_old_eam_res_rec.start_date --shedule_seq_num,start_date,completion_date
OR l_eam_res_rec.completion_date <> l_old_eam_res_rec.completion_date
OR l_eam_res_rec.resource_id <> l_old_eam_res_rec.resource_id --resource_code,usage_rate_or_amount,scheduled_flag,assigned_units
OR l_eam_res_rec.usage_rate_or_amount <> l_old_eam_res_rec.usage_rate_or_amount
OR l_eam_res_rec.scheduled_flag <> l_old_eam_res_rec.scheduled_flag
OR NVL(l_eam_res_rec.assigned_units,0) <> NVL(l_old_eam_res_rec.assigned_units,0))
)
) THEN
x_schedule_wo := G_SCHEDULE_WO;
-- OR (l_eam_res_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_DELETE AND l_eam_res_rec.scheduled_flag=1) --deleted and was scheduled
OR (l_eam_res_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE AND --updating the resource
(
--NVL(l_eam_res_rec.schedule_seq_num,l_eam_res_rec.resource_seq_num)<>NVL(l_old_eam_res_rec.schedule_seq_num,l_old_eam_res_rec.resource_seq_num)
-- OR
l_eam_res_rec.start_date <> l_old_eam_res_rec.start_date --shedule_seq_num,start_date,completion_date
OR l_eam_res_rec.completion_date <> l_old_eam_res_rec.completion_date
-- OR l_eam_res_rec.resource_id <> l_old_eam_res_rec.resource_id --resource_code,usage_rate_or_amount,scheduled_flag,assigned_units
-- OR l_eam_res_rec.usage_rate_or_amount <> l_old_eam_res_rec.usage_rate_or_amount
-- OR l_eam_res_rec.scheduled_flag <> l_old_eam_res_rec.scheduled_flag
-- OR NVL(l_eam_res_rec.assigned_units,0) <> NVL(l_old_eam_res_rec.assigned_units,0)
)
)
) THEN
x_bottomup_scheduled := G_UPDATE_RES_USAGE;
IF l_eam_op_rec.transaction_type IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Populate NULL columns') ;
IF (l_eam_op_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE AND
(l_eam_op_rec.start_date<>l_old_eam_op_rec.start_date
OR l_eam_op_rec.completion_date<>l_old_eam_op_rec.completion_date)) THEN
l_eam_op_rec.start_date := l_old_eam_op_rec.start_date; --set op dates to prev. dates
IF (l_eam_op_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE AND
(l_eam_op_rec.start_date<>l_old_eam_op_rec.start_date
OR l_eam_op_rec.completion_date<>l_old_eam_op_rec.completion_date)) THEN
-- l_eam_op_rec.start_date := l_old_eam_op_rec.start_date; --set op dates to prev. dates
select msn.current_status
into l_current_status
from mtl_serial_numbers msn
where inventory_item_id = nvl(l_eam_wo_rec.rebuild_item_id, l_eam_wo_rec.asset_group_id)
and serial_number = nvl(l_eam_wo_rec.rebuild_serial_number, l_eam_wo_rec.asset_number);
x_material_shortage := G_MATERIAL_UPDATE;
IF l_eam_wo_rec.Transaction_Type IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE)
THEN
IF GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('EAM WO: Populate Null Columns . . .'); END IF;
x_material_shortage := G_MATERIAL_UPDATE;
(l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE and
--fix for 3296919.added following condition so that activity is updateable
(l_old_eam_wo_rec.asset_activity_id is null OR l_old_eam_wo_rec.asset_activity_id<>l_eam_wo_rec.asset_activity_id)))
THEN
IF EAM_OP_UTILITY_PVT.NUM_OF_ROW
( p_eam_op_tbl => l_eam_op_tbl
, p_wip_entity_id => p_eam_wo_rec.wip_entity_id
, p_organization_id => p_eam_wo_rec.organization_id
)
THEN
l_out_eam_wo_rec := l_eam_wo_rec;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Calling api to update material shortage flag') ; END IF;
OR (l_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE --workorder updated
AND NVL(l_old_eam_wo_rec.pending_flag,'N') = 'N' --old status is not pending
--and old status is not same as new status
AND (l_old_eam_wo_rec.status_type <>l_eam_wo_rec.status_type)
)
) THEN
IF(WF_EVENT.TEST(l_status_pending_event) <> 'NONE') THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Calling Wkflow required check API...') ; END IF;
and l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
) then
select pm_suggested_start_date, pm_suggested_end_date into
l_eam_wo_rec.pm_suggested_start_date,
l_eam_wo_rec.pm_suggested_end_date
from eam_work_order_details
where wip_entity_id = l_eam_wo_rec.wip_entity_id;
/* Bug # 4926626 : Disable hiearchy if automatic (replaced) rebuild WO is updated
with the Asset Number and parent WO is completed */
IF (l_eam_wo_rec.parent_wip_entity_id IS NOT NULL AND
l_eam_wo_rec.manual_rebuild_flag = 'N' AND
l_eam_wo_rec.maintenance_object_type = 3 ) THEN
IF (l_old_eam_wo_rec.maintenance_object_type = 2 AND
l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE) THEN
SELECT serial_number, inventory_item_id, last_vld_organization_id
INTO l_serial_number, l_inv_item_id, l_org_id
FROM csi_item_instances
WHERE instance_id = l_eam_wo_rec.maintenance_object_id;
wip_eam_genealogy_pvt.update_eam_genealogy
(
p_api_version => 1.0,
p_object_type => 2,
p_serial_number => l_serial_number,
p_inventory_item_id => l_inv_item_id,
p_organization_id => l_org_id,
p_genealogy_type => 5,
p_end_date_active => sysdate,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Update Genealogy completed with status '||l_return_status) ; END IF;
l_other_message := 'EAM_UPDATE_GENEALOGY_FAIL';
IF ((l_old_eam_wo_rec.maintenance_object_type = 2 AND l_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE) OR
(l_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_CREATE )) THEN
/* If the parent WO is complete, disable meter hierarchy and IB hierarchy */
SELECT status_type INTO l_current_status
FROM wip_discrete_jobs
WHERE wip_entity_id = l_eam_wo_rec.parent_wip_entity_id;
SELECT firm_planned_flag
INTO l_firm_planned_flag
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = l_wip_entity_id;
x_bottomup_scheduled := G_UPDATE_RES_USAGE;
l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE and
((l_eam_wo_rec.asset_activity_id <> l_old_eam_wo_rec.asset_activity_id) or l_old_eam_wo_rec.asset_activity_id is null)
))
then
null ;
select
l_eam_op_tbl(J).wip_entity_id,
l_eam_op_tbl(J).operation_seq_num,
bsor.resource_seq_num,
bso.organization_id,
bsor.LAST_UPDATE_DATE,
bsor.LAST_UPDATED_BY,
bsor.CREATION_DATE,
bsor.CREATED_BY,
bsor.LAST_UPDATE_LOGIN,
bsor.REQUEST_ID,
bsor.PROGRAM_APPLICATION_ID,
bsor.PROGRAM_ID,
bsor.PROGRAM_UPDATE_DATE,
bsor.RESOURCE_ID,
br.unit_of_measure,
bsor.BASIS_TYPE,
bsor.USAGE_RATE_OR_AMOUNT,
bsor.ACTIVITY_ID,
bsor.SCHEDULE_FLAG,
bsor.ASSIGNED_UNITS,
DECODE(bsor.autocharge_type,1,2,4,3,2,2,3,3,2) autocharge_type,
bsor.STANDARD_RATE_FLAG,
0 APPLIED_RESOURCE_UNITS,
0 APPLIED_RESOURCE_VALUE,
nvl(l_eam_op_tbl(J).start_date,sysdate) start_date,
nvl(l_eam_op_tbl(J).completion_date,sysdate) completion_date,
bsor.ATTRIBUTE_CATEGORY,
bsor.ATTRIBUTE1,
bsor.ATTRIBUTE2,
bsor.ATTRIBUTE3,
bsor.ATTRIBUTE4,
bsor.ATTRIBUTE5,
bsor.ATTRIBUTE6,
bsor.ATTRIBUTE7,
bsor.ATTRIBUTE8,
bsor.ATTRIBUTE9,
bsor.ATTRIBUTE10,
bsor.ATTRIBUTE11,
bsor.ATTRIBUTE12,
bsor.ATTRIBUTE13,
bsor.ATTRIBUTE14,
bsor.ATTRIBUTE15,
bso.DEPARTMENT_ID,
decode(bsor.SCHEDULE_FLAG,2,null,bsor.resource_seq_num) ,
bsor.SUBSTITUTE_GROUP_NUM
from bom_standard_operations bso,
bom_std_op_resources bsor,
bom_resources br
where bso.standard_operation_id = bsor.standard_operation_id
and br.resource_id = bsor.resource_id
and bso.standard_operation_id = l_eam_op_tbl(J).standard_operation_id
and bso.organization_id = l_eam_op_tbl(J).organization_id;
(l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE and
l_eam_wo_rec.status_type <> l_old_eam_wo_rec.status_type
)
) then
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling Change_Status API') ; END IF ;
(l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE and
l_old_eam_wo_rec.status_type <> 3 and l_eam_wo_rec.status_type = 3)
)
THEN
IF(l_eam_wo_rec.date_released IS NULL OR
l_eam_wo_rec.date_released = FND_API.G_MISS_DATE) THEN
/*Bug#4425025 - set date_released to greatest of min open period start date
*and scheduled start date if scheduled start date is in past*/
IF (l_eam_wo_rec.scheduled_start_date < sysdate)
THEN
select nvl(min(period_start_date),l_eam_wo_rec.scheduled_start_date)
into l_min_open_period_date
from org_acct_periods
where organization_id=l_eam_wo_rec.organization_id
and open_flag = 'Y'
and period_close_date is null;
UPDATE WIP_DISCRETE_JOBS
SET date_released = l_eam_wo_rec.date_released,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.User_Id,
last_update_login = FND_GLOBAL.Login_Id
WHERE wip_entity_id=l_eam_wo_rec.wip_entity_id
AND organization_id=l_eam_wo_rec.organization_id;
(l_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_UPDATE and
l_eam_wo_rec.status_type = 3 and
((l_old_eam_wo_rec.date_released is null and l_old_eam_wo_rec.status_type IN (17,6,7)) OR (l_old_eam_wo_rec.status_type=1)))
)
THEN
update wip_requirement_operations
set released_quantity=required_quantity,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.User_Id,
last_update_login = FND_GLOBAL.Login_Id
where wip_entity_id=l_eam_wo_rec.wip_entity_id
and organization_id=l_eam_wo_rec.organization_id
and required_quantity > 0;
l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE and
((l_eam_wo_rec.asset_activity_id <> l_old_eam_wo_rec.asset_activity_id) or l_old_eam_wo_rec.asset_activity_id is null)
))
then
x_schedule_wo := G_SCHEDULE_WO;
-- if activity is deleted then also scheduler should get call
if (x_schedule_wo <> G_SCHEDULE_WO and l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE
and l_old_eam_wo_rec.asset_activity_id is not null
and (l_eam_wo_rec.asset_activity_id is null or l_eam_wo_rec.asset_activity_id = FND_API.G_MISS_NUM)) then
x_schedule_wo := G_SCHEDULE_WO;
(l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE
-- AND l_eam_wo_rec.status_type <> 6 --status not equal to on-hold
AND
((l_eam_wo_rec.requested_start_date IS NULL AND l_old_eam_wo_rec.requested_start_date IS NOT NULL) --changing from forward to backward sched
OR (l_eam_wo_rec.requested_start_date IS NOT NULL AND l_old_eam_wo_rec.requested_start_date IS NULL) --changing from b/w to f/w sched
OR l_eam_wo_rec.requested_start_date <> l_old_eam_wo_rec.requested_start_date --changing dates
OR l_eam_wo_rec.due_date <> l_old_eam_wo_rec.due_date
OR l_eam_wo_rec.scheduled_start_date <> l_old_eam_wo_rec.scheduled_start_date
OR l_eam_wo_rec.scheduled_completion_date <> l_old_eam_wo_rec.scheduled_completion_date
OR (l_eam_wo_rec.firm_planned_flag=1 AND l_old_eam_wo_rec.firm_planned_flag=2 )) --changing from firm to non-firm
)) THEN
x_bottomup_scheduled := G_BU_SCHEDULE_WO;
IF(l_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_UPDATE AND
--if changing status to cancelled,complete-no-chrg,close do not call scheduler
(l_eam_wo_rec.status_type IN (7,5,12,14,15)) AND (l_old_eam_wo_rec.status_type <> l_eam_wo_rec.status_type)) THEN
NULL;
ELSIF(l_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_UPDATE AND
--if changing status from on-hold,cancelled,complete-no-chrg,close to something else .call scheduler
(l_old_eam_wo_rec.status_type IN (6,7,5,12,14,15)) AND (l_eam_wo_rec.status_type NOT IN (6,7,5,12,14,15))) THEN
x_schedule_wo := G_SCHEDULE_WO;
(l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE
--AND l_eam_wo_rec.status_type <> 6 --status not equal to on-hold
AND
((l_eam_wo_rec.requested_start_date IS NULL AND l_old_eam_wo_rec.requested_start_date IS NOT NULL) --changing from forward to backward sched
OR (l_eam_wo_rec.requested_start_date IS NOT NULL AND l_old_eam_wo_rec.requested_start_date IS NULL) --changing from b/w to f/w sched
OR l_eam_wo_rec.requested_start_date <> l_old_eam_wo_rec.requested_start_date --changing dates
OR l_eam_wo_rec.due_date <> l_old_eam_wo_rec.due_date
OR l_eam_wo_rec.scheduled_start_date <> l_old_eam_wo_rec.scheduled_start_date
OR l_eam_wo_rec.scheduled_completion_date <> l_old_eam_wo_rec.scheduled_completion_date
OR (l_eam_wo_rec.firm_planned_flag=2 AND l_old_eam_wo_rec.firm_planned_flag=1 )) --changing from firm to non-firm
)) THEN
x_schedule_wo := G_SCHEDULE_WO; --3521842
select 1
into l_count
from wip_operations
where wip_entity_id = l_eam_wo_rec.wip_entity_id
and department_id is null;
select count(*)
into l_count
from wip_eam_parameters
where organization_id = l_eam_wo_rec.organization_id
and default_department_id is null;
UPDATE WIP_DISCRETE_JOBS
SET estimation_status = 2,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.User_Id,
last_update_login = FND_GLOBAL.Login_Id
WHERE wip_entity_id=l_eam_wo_rec.wip_entity_id
AND organization_id=l_eam_wo_rec.organization_id;
UPDATE_RES_USAGE_BU_ERR EXCEPTION;
SELECT start_date,
completion_date,
operation_seq_num,
resource_seq_num
FROM wip_operation_resources
WHERE wip_entity_id = c_wip_entity_id;
l_wkorder_old_op_tbl.delete;
l_wkorder_old_op_dt_tbl.delete;
l_wkorder_new_op_tbl.delete;
l_wkorder_new_op_dt_tbl.delete;
IF l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE THEN
EAM_WO_UTILITY_PVT.Query_Row
( p_wip_entity_id => p_eam_wo_rec.wip_entity_id
, p_organization_id => p_eam_wo_rec.organization_id
, x_eam_wo_rec => l_old_eam_wo_rec
, x_Return_status => l_return_status
);
if l_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE then
l_wip_entity_id := l_eam_wo_rec.wip_entity_id;
select SCHEDULED_START_DATE into l_wo_old_sch_start_date
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
select operation_seq_num,first_unit_start_date
bulk collect into l_wkorder_old_op_tbl,l_wkorder_old_op_dt_tbl
from wip_operations
where organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id ;
IF l_material_shortage = G_MATERIAL_UPDATE THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling teh Check_Shortage procedure for materials ...'); END IF;
select material_shortage_check_date,
material_shortage_flag
into l_eam_wo_rec.material_shortage_check_date ,
l_eam_wo_rec.material_shortage_flag
from EAM_WORK_ORDER_DETAILS
where wip_entity_id = l_eam_wo_rec.wip_entity_id;
ELSE IF l_bottomup_scheduled IN ( G_BU_SCHEDULE_WO, G_UPDATE_RES_USAGE ) THEN
IF ( l_bottomup_scheduled = G_UPDATE_RES_USAGE ) THEN
l_out_eam_res_tbl := l_eam_res_tbl;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling update_resource_usage for firm work orders') ; END IF ;
EAM_SCHED_BOTTOM_UP_PVT.update_resource_usage(
p_eam_res_tbl => l_eam_res_tbl
, p_eam_res_inst_tbl => l_eam_res_inst_tbl
, p_eam_res_usage_tbl => l_eam_res_usage_tbl
, x_eam_res_tbl => l_out_eam_res_tbl
, x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
, x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
, x_return_status => l_return_status
, x_message_name => l_err_text
) ;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' update_resource_usage for firm work orders returned status is ' || l_return_status ) ; END IF ;
RAISE UPDATE_RES_USAGE_BU_ERR;
--Added parameter p_woru_modified to the procedure. This is done to ensure that dates are changed correctly from summary tab in Create Update WO page
EAM_SCHED_BOTTOM_UP_PVT.schedule_bottom_up_pvt (
p_api_version_number => 1.0
, p_commit => FND_API.G_FALSE
, p_wip_entity_id => l_eam_wo_rec.wip_entity_id
, p_org_id => l_eam_wo_rec.organization_id
, p_woru_modified => l_woru_modified
, x_return_status => l_return_status
, x_message_name => l_err_text
) ;
IF((l_scheduled=G_SCHEDULE_WO) OR (l_bottomup_scheduled IN ( G_BU_SCHEDULE_WO, G_UPDATE_RES_USAGE ))
OR (l_eam_res_tbl.COUNT > 0) OR (l_eam_res_usage_tbl.COUNT > 0) OR (l_eam_res_inst_tbl.COUNT > 0)
) THEN
l_emp_assignment := TRUE;
Update eam_work_order_details
set ASSIGNMENT_COMPLETE = EAM_ASSIGN_EMP_PUB.Get_Emp_Assignment_Status(l_eam_wo_rec.wip_entity_id,l_eam_wo_rec.organization_id)
where wip_entity_id = l_eam_wo_rec.wip_entity_id;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('enters inside update'); END IF;
UPDATE_DATES(l_eam_wo_rec,
l_eam_op_tbl,
l_eam_res_tbl,
l_eam_res_inst_tbl);
select operation_seq_num,first_unit_start_date
bulk collect into l_wkorder_new_op_tbl,l_wkorder_new_op_dt_tbl
from wip_operations
where organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id;
update WIP_REQUIREMENT_OPERATIONS
set date_required = date_required + no_of_days
where organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id
and operation_seq_num = l_wkorder_old_op_tbl(ii);
update WIP_REQUIREMENT_OPERATIONS
set date_required = l_wkorder_new_op_dt_tbl(jj)
where organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id
and operation_seq_num = l_wkorder_new_op_tbl(jj);
select SCHEDULED_START_DATE into l_wo_new_sch_start_date
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
update wip_requirement_operations
set date_required = date_required + (l_wo_new_sch_start_date-l_wo_old_sch_start_date)
where operation_seq_num = 1
and organization_id = l_organization_id
and wip_entity_id = l_wip_entity_id ;
UPDATE wip_op_resource_instances
SET start_date = c_opresource_rec.start_date,
completion_date=c_opresource_rec.completion_date
WHERE wip_entity_id = l_wip_entity_id
AND operation_seq_num = c_opresource_rec.operation_seq_num
AND resource_seq_num = c_opresource_rec.resource_seq_num;
and l_out_eam_mat_req_tbl(kk).transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE
THEN
EAM_MATERIALISSUE_PVT.cancel_alloc_matl_del
(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_wip_entity_id => l_out_eam_mat_req_tbl(kk).wip_entity_id,
p_operation_seq_num => l_out_eam_mat_req_tbl(kk).operation_seq_num,
p_inventory_item_id => l_out_eam_mat_req_tbl(kk).inventory_item_id,
p_wip_entity_type => WIP_CONSTANTS.EAM,
p_repetitive_schedule_id => null,
x_return_status => l_return_status,
x_msg_data => l_err_text,
x_msg_count => l_msg_count
);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling UPDATE_INTERMEDIA_INDEX'); END IF;
IF(l_eam_wo_rec.maintenance_object_source =1 ) THEN --update intermedia index only for EAM workorders
UPDATE_INTERMEDIA_INDEX
(
l_eam_wo_rec,
l_old_eam_wo_rec,
l_eam_op_tbl,
l_eam_res_tbl,
l_eam_res_inst_tbl
);
WHEN UPDATE_RES_USAGE_BU_ERR THEN
l_token_tbl(1).token_name := 'WORK_ORDER_NAME';
SELECT wip_entity_name into l_token_tbl(1).token_value
FROM WIP_ENTITIES we
WHERE we.wip_entity_id = l_eam_wo_rec.wip_entity_id
AND we.organization_id = l_eam_wo_rec.organization_id;
SELECT wip_entity_name into l_token_tbl(1).token_value
FROM WIP_ENTITIES we
WHERE we.wip_entity_id = l_eam_wo_rec.wip_entity_id
AND we.organization_id = l_eam_wo_rec.organization_id;
AND NVL(p_transaction_type, FND_API.G_MISS_NUM) NOT IN (EAM_PROCESS_WO_PVT.G_OPR_SYNC, EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)
)
OR
(p_entity_name IN ('OPERATION','OPERATION_RESOURCE','RESOURCE_INSTANCE','SUB_RESOURCE','DIRECT_ITEMS','MATERIAL_REQUIREMENTS')
AND NVL(p_transaction_type, FND_API.G_MISS_NUM) NOT IN (EAM_PROCESS_WO_PVT.G_OPR_SYNC, EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
)
OR
(p_entity_name IN ('OPERATION_NETWORK', 'RESOURCE_USAGE')
AND NVL(p_transaction_type, FND_API.G_MISS_NUM) NOT IN (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
)
OR
(p_entity_name IN ('WORK_ORDER_COMPLETEION','OPERATION_COMPLETEION')
AND NVL(p_transaction_type, FND_API.G_MISS_NUM) NOT IN (EAM_PROCESS_WO_PVT.G_OPR_COMPLETE, EAM_PROCESS_WO_PVT.G_OPR_UNCOMPLETE)
)
OR
(p_entity_name IN ('QUALITY_ENTRY','METER_READING')
AND NVL(p_transaction_type, FND_API.G_MISS_NUM) NOT IN (EAM_PROCESS_WO_PVT.G_OPR_CREATE)
)
OR
(p_entity_name IN ('WORK_ORDER_COMPL_REBUILD','W_ORDER_COMPL_METER_READING')
AND NVL(p_transaction_type, FND_API.G_MISS_NUM) NOT IN (EAM_PROCESS_WO_PVT.G_OPR_UPDATE)
)
OR
(p_entity_name IN ('WORK_SERVICE_REQUEST')
AND NVL(p_transaction_type, FND_API.G_MISS_NUM) NOT IN (EAM_PROCESS_WO_PVT.G_OPR_CREATE,EAM_PROCESS_WO_PVT.G_OPR_UPDATE)
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
IF p_entity_name = 'WORK_ORDER'
THEN
l_out_mesg_token_tbl := l_mesg_token_tbl;
PROCEDURE UPDATE_DATES
(x_eam_wo_rec IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type,
x_eam_op_tbl IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_tbl_type,
x_eam_res_tbl IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type,
x_eam_res_inst_tbl IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
)
IS
CURSOR operations
(l_wip_entity_id NUMBER,l_org_id NUMBER,l_op_seq_num NUMBER)
IS
SELECT first_unit_start_date,
last_unit_completion_date
FROM wip_operations
WHERE wip_entity_id=l_wip_entity_id
AND organization_id=l_org_id
AND operation_seq_num=l_op_seq_num;
SELECT start_date,
completion_date
FROM wip_operation_resources
WHERE wip_entity_id=l_wip_entity_id
AND organization_id=l_org_id
AND operation_seq_num=l_op_seq_num
AND resource_seq_num=l_res_seq_num;
SELECT start_date,
completion_date
FROM wip_op_resource_instances
WHERE wip_entity_id=l_wip_entity_id
AND organization_id=l_org_id
AND operation_seq_num=l_op_seq_num
AND resource_seq_num=l_res_seq_num
AND instance_id=l_instance_id
AND serial_number(+)=l_serial_number;
IF(x_eam_wo_rec.TRANSACTION_TYPE IN (EAM_PROCESS_WO_PUB.G_OPR_CREATE,EAM_PROCESS_WO_PUB.G_OPR_UPDATE)) THEN
SELECT scheduled_start_date,scheduled_completion_date
INTO l_start_date,l_completion_date
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id=x_eam_wo_rec.wip_entity_id
AND organization_id=x_eam_wo_rec.organization_id;
IF( x_eam_op_tbl(i).TRANSACTION_TYPE IN (EAM_PROCESS_WO_PUB.G_OPR_CREATE,EAM_PROCESS_WO_PUB.G_OPR_UPDATE)) THEN
OPEN operations(x_eam_wo_rec.wip_entity_id,x_eam_wo_rec.organization_id
,x_eam_op_tbl(i).operation_seq_num);
IF( x_eam_res_tbl(i).TRANSACTION_TYPE IN (EAM_PROCESS_WO_PUB.G_OPR_CREATE,EAM_PROCESS_WO_PUB.G_OPR_UPDATE)) THEN
OPEN resources(x_eam_wo_rec.wip_entity_id,x_eam_wo_rec.organization_id
,x_eam_res_tbl(i).operation_seq_num,x_eam_res_tbl(i).resource_seq_num);
IF( x_eam_res_inst_tbl(i).TRANSACTION_TYPE IN (EAM_PROCESS_WO_PUB.G_OPR_CREATE,EAM_PROCESS_WO_PUB.G_OPR_UPDATE)) THEN
OPEN resource_instances(x_eam_wo_rec.wip_entity_id,x_eam_wo_rec.organization_id
,x_eam_res_inst_tbl(i).operation_seq_num,x_eam_res_inst_tbl(i).resource_seq_num
,x_eam_res_inst_tbl(i).instance_id,x_eam_res_inst_tbl(i).serial_number);
END UPDATE_DATES;
SELECT cii.inventory_item_id,
cii.instance_number,
cii.instance_id,
cii.serial_number,
wdj.primary_item_id,
wdj.maintenance_object_type,
wdj.maintenance_object_id,
wdj.maintenance_object_source,
NVL(ewod.pending_flag,'N'),
ewod.user_defined_status_id,
wdj.status_type,
ewod.workflow_type,
eam_linear_location_id,
owning_department
FROM wip_discrete_jobs wdj,csi_item_instances cii,eam_work_order_details ewod
WHERE wdj.wip_entity_id = l_eam_wo_comp_rec.wip_entity_id
AND wdj.maintenance_object_type = 3
AND wdj.maintenance_object_id = cii.instance_id
AND wdj.wip_entity_id = ewod.wip_entity_id(+)
UNION
SELECT wdj.maintenance_object_id,
null,
null,
null,
wdj.primary_item_id,
wdj.maintenance_object_type,
wdj.maintenance_object_id,
wdj.maintenance_object_source,
NVL(ewod.pending_flag,'N'),
ewod.user_defined_status_id,
wdj.status_type,
ewod.workflow_type,
eam_linear_location_id,
owning_department
FROM wip_discrete_jobs wdj,eam_work_order_details ewod
WHERE wdj.wip_entity_id = l_eam_wo_comp_rec.wip_entity_id
AND wdj.maintenance_object_type = 2
AND wdj.wip_entity_id = ewod.wip_entity_id(+);
SELECT wip_entity_name INTO l_wip_entity_name
FROM wip_entities
WHERE wip_entity_id = l_eam_wo_comp_rec.wip_entity_id;
SELECT msi.concatenated_segments
INTO l_asset_group_name
FROM mtl_system_items_kfv msi
WHERE msi.inventory_item_id = l_asset_group_id
AND rownum = 1;
SELECT msi.concatenated_segments
INTO l_asset_activity
FROM mtl_system_items_kfv msi
WHERE msi.inventory_item_id = l_asset_activity_id
AND rownum = 1;
UPDATE EAM_WORK_ORDER_DETAILS
SET user_defined_status_id=3,
pending_flag='Y',
last_update_date=SYSDATE,
last_update_login=FND_GLOBAL.login_id,
last_updated_by=FND_GLOBAL.user_id
WHERE wip_entity_id= l_eam_wo_comp_rec.wip_entity_id;
SELECT NVL((SUM(system_estimated_mat_cost) + SUM(system_estimated_lab_cost) + SUM(system_estimated_eqp_cost)),0)
INTO l_cost_estimate
FROM WIP_EAM_PERIOD_BALANCES
WHERE wip_entity_id = l_eam_wo_comp_rec.wip_entity_id;
SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
INTO l_wf_event_seq
FROM DUAL;
IF GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Inserting into EAM_WO_WORKFLOWS ...'); END IF;
INSERT INTO EAM_WO_WORKFLOWS
(WIP_ENTITY_ID,WF_ITEM_TYPE,WF_ITEM_KEY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
VALUES
(l_eam_wo_comp_rec.wip_entity_id,l_workflow_name,l_event_key,SYSDATE,FND_GLOBAL.user_id,
SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id
);
l_parameter_list.DELETE;
p_event => 'UPDATE',
p_wip_entity_id =>l_eam_wo_comp_rec.wip_entity_id,
p_organization_id =>l_eam_wo_comp_rec.organization_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login =>FND_GLOBAL.login_id
);
--Invoke Update_Pm_When_Uncomplete to reverse last service dates and last service reading if this is the last work order
--This should be called before Perform_Writes as the logic to calculate if this is the last work order or not will not work if record
-- will be inserted into Eam_Job_Completion_Txns for uncompletion transaction.
IF l_eam_wo_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UNCOMPLETE THEN
if( l_maint_obj_source = 1) then -- added to check whether work order is of 'EAM' or 'CRMO'.'EAM=1'
eam_pm_utils.update_pm_when_uncomplete(l_eam_wo_comp_rec.organization_id, l_eam_wo_comp_rec.wip_entity_id);
IF GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling WO Completion update_row ...'); END IF;
EAM_WO_COMP_UTILITY_PVT.update_row
( p_eam_wo_comp_rec => l_eam_wo_comp_rec
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_return_status => l_return_status
);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Quality : Insert_Row...') ; END IF ;
EAM_WO_QUA_UTILITY_PVT.insert_row
(
p_collection_id => colllection_id_temp
, p_eam_wo_quality_tbl => p_eam_wo_quality_tbl
, x_eam_wo_quality_tbl => l_eam_wo_quality_tbl
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
);
l_other_message := 'EAM_WOCMP_QA_INSERT_REC';
END ; -- END For Quality Insert row
SELECT cii.instance_number,cii.instance_id
into l_asset_instance_number,
l_asset_instance_id
FROM wip_discrete_jobs wdj,csi_item_instances cii
WHERE wdj.wip_entity_id = l_eam_wo_comp_rec.wip_entity_id
AND wdj.maintenance_object_type = 3
AND wdj.maintenance_object_id = cii.instance_id
AND cii.serial_number = l_asset_number;
l_eam_failure_codes_tbl(i).transaction_type:= Eam_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_codes_tbl.delete(i);
SELECT OWNING_DEPARTMENT_ID
INTO l_department_id
FROM eam_org_maint_defaults
WHERE object_id = l_maint_object_id
AND object_type = 60 AND organization_id = l_org_id ;
SELECT area_id
INTO l_eam_location_id
FROM eam_org_maint_defaults
WHERE object_id = l_maint_object_id
AND object_type = 60 AND organization_id = l_org_id ;
l_eam_failure_entry_record.transaction_type := Eam_Process_Failure_Entry_PUB.G_FE_UPDATE;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_METERREADING_VALIDATE_PVT.insert_row procedure...') ; END IF ;
EAM_METERREADING_UTILITY_PVT.INSERT_ROW
(
p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
, p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
, x_eam_meter_reading_tbl => x_eam_meter_reading_tbl
, x_eam_counter_prop_tbl => x_eam_counter_prop_tbl
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling AM_METERREADING_UTILITY_PVT.Update_Genealogy procedure...') ; END IF ;
EAM_METERREADING_UTILITY_PVT.UPDATE_GENEALOGY
(
p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl ,
x_eam_wo_comp_rebuild_tbl => x_eam_wo_comp_rebuild_tbl ,
x_return_status => l_return_status ,
x_mesg_token_tbl => l_mesg_token_tbl
);
l_other_message := 'EAM_WOCMPL_UPDATE_GEN';
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling AM_METERREADING_UTILITY_PVT.UPDATE_REBUILD_WORK_ORDER procedure...') ; END IF ;
EAM_METERREADING_UTILITY_PVT.UPDATE_REBUILD_WORK_ORDER
(
p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl ,
x_eam_wo_comp_rebuild_tbl => x_eam_wo_comp_rebuild_tbl ,
x_return_status => l_return_status ,
x_mesg_token_tbl => l_mesg_token_tbl
);
l_other_message := 'EAM_WOCMPL_UPDATE_WORK';
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_METERREADING_UTILITY_PVT.UPDATE_ACTIVITY procedure...') ; END IF ;
EAM_METERREADING_UTILITY_PVT.UPDATE_ACTIVITY
(
p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl ,
x_eam_wo_comp_rebuild_tbl => x_eam_wo_comp_rebuild_tbl ,
x_return_status => l_return_status ,
x_mesg_token_tbl => l_mesg_token_tbl
);
l_other_message := 'EAM_WOCMPL_UPDATE_ACT';
/* -- For work order completeion call Eam_Meters_util. Update_Last_Service_Reading_Dates
IF l_eam_wo_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_COMPLETE AND p_eam_meter_reading_tbl.COUNT > 0 THEN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_METERREADING_UTILITY_PVT.UPDATE_LAST_SERVICE_READING procedure...') ; END IF ;
EAM_METERREADING_UTILITY_PVT.UPDATE_LAST_SERVICE_READING
(
p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
, x_eam_meter_reading_tbl => x_eam_meter_reading_tbl
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
);
select qp.plan_id,name bulk collect into l_plan_id_tbl,l_plan_name_tbl
from qa_results qr,qa_plans qp
where qr.plan_id = qp.plan_id
and collection_id = l_eam_wo_comp_rec.qa_collection_id;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_ASSET_LOG_PVT.INSERT_ROW procedure...') ; END IF ;
EAM_ASSET_LOG_PVT.INSERT_ROW
(
p_api_version => 1.0,
p_event_date => sysdate,
p_event_type => 'EAM_SYSTEM_EVENTS',
p_event_id => 12,
p_organization_id => l_eam_wo_comp_rec.organization_id,
p_instance_id => l_maint_object_id,
p_comments => null,
p_reference => l_plan_name_tbl(N),
p_ref_id => l_plan_id_tbl(N),
p_operable_flag => null,
p_reason_code => null,
x_return_status => l_asset_ops_return_status,
x_msg_count => l_asset_ops_msg_count,
x_msg_data => l_asset_ops_msg_data
);
SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
INTO l_wf_event_seq
FROM DUAL;
l_parameter_list.DELETE;
p_event => 'UPDATE',
p_wip_entity_id =>l_eam_wo_comp_rec.wip_entity_id,
p_organization_id =>l_eam_wo_comp_rec.organization_id,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.user_id,
p_last_update_login =>FND_GLOBAL.login_id
);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_ASSET_LOG_PVT.INSERT_ROW procedure...') ; END IF ;
EAM_ASSET_LOG_PVT.INSERT_ROW
(
p_api_version => 1.0,
p_event_date => sysdate,
p_event_type => 'EAM_SYSTEM_EVENTS',
p_event_id => 8,
p_organization_id => l_eam_wo_comp_rec.organization_id,
p_instance_id => l_maint_object_id,
p_comments => null,
p_reference => l_wip_entity_name,
p_ref_id => l_eam_wo_comp_rec.wip_entity_id,
p_operable_flag => null,
p_reason_code => null,
x_return_status => l_asset_ops_return_status,
x_msg_count => l_asset_ops_msg_count,
x_msg_data => l_asset_ops_msg_data
);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_ASSET_LOG_PVT.INSERT_ROW procedure...') ; END IF ;
EAM_ASSET_LOG_PVT.INSERT_ROW
(
p_api_version => 1.0,
p_event_date => sysdate,
p_event_type => 'EAM_SYSTEM_EVENTS',
p_event_id => 9,
p_organization_id => l_eam_wo_comp_rec.organization_id,
p_instance_id => l_maint_object_id,
p_comments => null,
p_reference => l_wip_entity_name,
p_ref_id => l_eam_wo_comp_rec.wip_entity_id,
p_operable_flag => null,
p_reason_code => null,
x_return_status => l_asset_ops_return_status,
x_msg_count => l_asset_ops_msg_count,
x_msg_data => l_asset_ops_msg_data
);
SELECT wip_entity_name INTO l_wip_entity_name
FROM wip_entities
WHERE wip_entity_id = l_eam_op_comp_rec.wip_entity_id;
SELECT nvl(wdj.asset_group_id,wdj.rebuild_item_id),
nvl(wdj.asset_number,wdj.rebuild_serial_number),
wdj.primary_item_id,
wdj.maintenance_object_source,
ewod.workflow_type
INTO l_asset_group_id,
l_asset_number,
l_asset_activity_id,
l_maint_obj_source,
l_workflow_type
FROM wip_discrete_jobs wdj,eam_work_order_details ewod
WHERE wdj.wip_entity_id = l_eam_op_comp_rec.wip_entity_id
AND wdj.wip_entity_id = ewod.wip_entity_id(+);
SELECT msi.concatenated_segments
INTO l_asset_group_name
FROM mtl_system_items_kfv msi
WHERE msi.inventory_item_id = l_asset_group_id
AND rownum = 1;
SELECT msi.concatenated_segments
INTO l_asset_activity
FROM mtl_system_items_kfv msi
WHERE msi.inventory_item_id = l_asset_activity_id
AND rownum = 1;
IF GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Operation Completeion validating Quality Record Insert Row . . .'); END IF;
EAM_WO_QUA_UTILITY_PVT.insert_row
(
p_collection_id => colllection_id_temp
, p_eam_wo_quality_tbl => p_eam_wo_quality_tbl
, x_eam_wo_quality_tbl => l_eam_wo_quality_temp_tbl
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
);
l_other_message := 'EAM_OP_QUAINSERT_CSEV_SKIP';
SELECT cii.instance_number,cii.instance_id into l_asset_instance_number,
l_asset_instance_id
FROM wip_discrete_jobs wdj,csi_item_instances cii
WHERE wdj.wip_entity_id = l_eam_op_comp_rec.wip_entity_id
AND wdj.maintenance_object_type = 3
AND wdj.maintenance_object_id = cii.instance_id
AND cii.serial_number = l_asset_number;
SELECT last_unit_completion_date
INTO l_op_sched_end_date
FROM WIP_OPERATIONS
WHERE wip_entity_id= l_eam_op_comp_rec.wip_entity_id
AND operation_seq_num = l_eam_op_comp_rec.operation_seq_num;
select DECODE(count(won.next_operation),0,'Y','N')
INTO l_is_last_operation
from wip_operation_networks won
where won.wip_entity_id = l_eam_op_comp_rec.wip_entity_id and
won.prior_operation = l_eam_op_comp_rec.operation_seq_num;
SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
INTO l_wf_event_seq
FROM DUAL;
l_parameter_list.DELETE;
EAM_REQUEST_UTILITY_PVT.INSERT_ROW
( p_eam_request_rec => l_eam_request_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
);
IF l_eam_request_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE THEN
EAM_REQUEST_UTILITY_PVT.DELETE_ROW
( p_eam_request_rec => l_eam_request_rec
, x_return_status => l_return_status
, x_mesg_token_tbl => l_mesg_token_tbl
);
l_other_message := 'EAM_WR_INSERT_REC';