[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE EAM_WORK_ORDER_IMPORT
SET process_status = 3,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id IN (SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE top_wip_entity_id= p_top_wip_entity_id);
-- Update for One work order in hierarchy has failed.Fail entire structure with status 3
-- Since some work orders has errored out,they will be in status 4 Hence only pick up
-- those work orders which in status 2
UPDATE EAM_WORK_ORDER_IMPORT
SET process_status = 3,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id in (SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE top_wip_entity_id= p_top_wip_entity_id
AND process_status =2);
INSERT INTO EAM_WORK_ORDER_IMPORT_ERRORS(
header_id ,
group_id ,
row_id ,
error_message ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
VALUES(
p_top_wip_entity_id,
p_group_id,
1,
p_wo_relationship_exc_tbl(work_rel_counter),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
FOR log_counter IN (SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = p_group_id
AND top_wip_entity_id=p_top_wip_entity_id
)
LOOP
fnd_file.put_line(FND_FILE.LOG,'Status Relationship Error for work order --' || log_counter.wip_entity_id);
FOR log_counter IN ( SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = p_group_id
AND top_wip_entity_id=p_top_wip_entity_id
)
LOOP
fnd_file.put_line(FND_FILE.LOG,'Status Relationship Error for work order --' || log_counter.wip_entity_id);
UPDATE EAM_WORK_ORDER_IMPORT
SET process_status = 4 ,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id = p_wo_rec.wip_entity_id;
-- get the error messages from the work order api exception stack and insert
-- into EAM_WORK_ORDER_IMPORT_ERRORS
l_msg_count := fnd_msg_pub.count_msg;
INSERT INTO EAM_WORK_ORDER_IMPORT_ERRORS(
header_id ,
group_id ,
row_id ,
error_message ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login
)
VALUES(
p_wo_rec.header_id,
p_wo_rec.group_id,
p_wo_rec.row_id,
temp_err_mesg,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
-- Following code loops through the out tables from work order API and updates the status
-- of corresponding import tables.It also updates the who columns
FOR counter_res_ins IN 1..p_eam_res_inst_tbl.count LOOP
UPDATE EAM_RESOURCE_INSTANCE_IMPORT
SET return_status = p_eam_res_inst_tbl(counter_res_ins).return_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id = p_eam_res_inst_tbl(counter_res_ins).wip_entity_id
AND (instance_id = p_eam_res_inst_tbl(counter_res_ins).instance_id OR instance_id IS NULL)
AND (serial_number = p_eam_res_inst_tbl(counter_res_ins).serial_number OR serial_number IS NULL);
UPDATE EAM_RESOURCE_USAGE_IMPORT
SET return_status = p_eam_res_usage_tbl(counter_res_usg).return_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id = p_eam_res_usage_tbl(counter_res_usg).wip_entity_id
AND resource_seq_num = p_eam_res_usage_tbl(counter_res_usg).resource_seq_num;
UPDATE EAM_RESOURCE_IMPORT
SET return_status = p_eam_res_tbl(counter_res).return_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id = p_eam_res_tbl(counter_res).wip_entity_id
AND resource_seq_num = p_eam_res_tbl(counter_res).resource_seq_num;
UPDATE EAM_MATERIAL_IMPORT
SET return_status = p_eam_mat_req_tbl(counter_mat).return_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id = p_eam_mat_req_tbl(counter_mat).wip_entity_id
AND inventory_item_id = p_eam_mat_req_tbl(counter_mat).inventory_item_id;
UPDATE EAM_OPERATION_IMPORT
SET return_status = p_eam_op_tbl(counter_op).return_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id = p_eam_op_tbl(counter_op).wip_entity_id
AND operation_seq_num = p_eam_op_tbl(counter_op).operation_seq_num;
UPDATE EAM_WORK_ORDER_IMPORT
SET return_status = p_return_status,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE group_id = p_group_id
AND wip_entity_id = p_wo_rec.wip_entity_id;
SELECT *
FROM EAM_OPERATION_IMPORT EOI
WHERE eoi.group_id = l_group_id
AND eoi.wip_entity_id = l_wip_entity_id;
SELECT *
FROM EAM_MATERIAL_IMPORT EMI
WHERE emi.group_id = l_group_id
AND emi.wip_entity_id = l_wip_entity_id;
SELECT *
FROM EAM_RESOURCE_IMPORT ERI
WHERE eri.group_id = l_group_id
AND eri.wip_entity_id = l_wip_entity_id;
SELECT *
FROM EAM_RESOURCE_USAGE_IMPORT ERUI
WHERE erui.group_id = l_group_id
AND erui.wip_entity_id = l_wip_entity_id;
SELECT *
FROM EAM_RESOURCE_INSTANCE_IMPORT ERIM
WHERE erim.group_id = l_group_id
AND erim.wip_entity_id = l_wip_entity_id;
-- Update the status of work order from Pending to Running
UPDATE EAM_WORK_ORDER_IMPORT
SET process_status = 2
WHERE group_id = l_group_id
AND process_status = 1 ;
DELETE EAM_WORK_ORDER_IMPORT_ERRORS
WHERE header_id IN (
SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND process_status =2
);
-- Update the rows and set the value of top_wip_entity_id
UPDATE EAM_WORK_ORDER_IMPORT ewoi
SET top_wip_entity_id = (
SELECT distinct wsr.top_level_object_id
FROM WIP_SCHED_RELATIONSHIPS wsr
WHERE wsr.relationship_type =1
AND (ewoi.wip_entity_id = wsr.child_object_id OR ewoi.wip_entity_id = wsr.parent_object_id)
)
WHERE process_status = 2;
SELECT *
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND process_status = 2
ORDER BY top_wip_entity_id ASC;
-- Delete tables befor calling work order api
l_eam_op_tbl.delete;
l_eam_op_network_tbl.delete;
l_eam_res_tbl.delete;
l_eam_res_inst_tbl.delete;
l_eam_sub_res_tbl.delete;
l_eam_res_usage_tbl.delete;
l_eam_mat_req_tbl.delete;
l_eam_di_tbl.delete;
l_eam_wo_quality_tbl.delete;
l_eam_meter_reading_tbl.delete;
l_eam_wo_comp_rebuild_tbl.delete;
l_eam_wo_comp_mr_read_tbl.delete;
l_eam_op_comp_tbl.delete;
l_eam_request_tbl.delete;
l_out_eam_op_tbl.delete;
l_out_eam_op_network_tbl.delete;
l_out_eam_res_tbl.delete;
l_out_eam_res_inst_tbl.delete;
l_out_eam_sub_res_tbl.delete;
l_out_eam_res_usage_tbl.delete;
l_out_eam_mat_req_tbl.delete;
l_out_eam_di_tbl.delete;
l_out_eam_wo_quality_tbl.delete;
l_out_eam_meter_reading_tbl.delete;
l_out_eam_wo_comp_rebuild_tbl.delete;
l_out_eam_wo_comp_mr_read_tbl.delete;
l_out_eam_op_comp_tbl.delete;
l_out_eam_request_tbl.delete;
l_out_eam_counter_prop_tbl.delete;
l_wo_relationship_exc_tbl.delete;
l_wo_relationship_exc_tbl.delete;
FOR log_counter IN ( SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND top_wip_entity_id = l_old_top_wip_entity_id) LOOP
fnd_file.put_line(FND_FILE.LOG,'Status Sucess for work order --' || log_counter.wip_entity_id);
DELETE
FROM EAM_RESOURCE_INSTANCE_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id IN (
SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND top_wip_entity_id = l_old_top_wip_entity_id
);
DELETE
FROM EAM_RESOURCE_USAGE_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id IN (
SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND top_wip_entity_id = l_old_top_wip_entity_id
);
DELETE
FROM EAM_RESOURCE_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id IN (
SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND top_wip_entity_id = l_old_top_wip_entity_id
);
DELETE
FROM EAM_MATERIAL_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id IN (
SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND top_wip_entity_id = l_old_top_wip_entity_id
);
DELETE
FROM EAM_OPERATION_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id IN (
SELECT wip_entity_id
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND top_wip_entity_id = l_old_top_wip_entity_id
);
DELETE
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND top_wip_entity_id = l_old_top_wip_entity_id;
DELETE
FROM EAM_RESOURCE_INSTANCE_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id = l_import_wo_record.wip_entity_id;
DELETE
FROM EAM_RESOURCE_USAGE_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id = l_import_wo_record.wip_entity_id;
DELETE
FROM EAM_RESOURCE_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id = l_import_wo_record.wip_entity_id;
DELETE
FROM EAM_MATERIAL_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id = l_import_wo_record.wip_entity_id;
DELETE
FROM EAM_OPERATION_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id = l_import_wo_record.wip_entity_id;
DELETE
FROM EAM_WORK_ORDER_IMPORT
WHERE group_id = l_group_id
AND wip_entity_id = l_import_wo_record.wip_entity_id;