This procedure is developed to update existing work order (WO) of "EAM" ( Enterprise Asset Management ).
I have updated the status of work order from "Complete" to "Complete No Charges". There are some constraints
- You can change System Status to "Complete No Charges" and vice versa only if WO is Completed.
-----------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE cust_wo_complete_no_charg (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_user_id IN NUMBER
)
IS
CURSOR c1
IS
SELECT wod.wip_entity_id, ROWNUM count_, we.wip_entity_name
FROM eam_work_order_details wod, wip_entities we
WHERE wod.user_defined_status_id = 4
AND wod.organization_id = 85
AND we.organization_id = wod.organization_id
AND we.wip_entity_id = wod.wip_entity_id
AND exists (
SELECT wdj.wip_entity_id
FROM wip_discrete_jobs wdj
WHERE wdj.work_order_type = 50
AND wdj.organization_id = 85
and wdj.WIP_ENTITY_ID = wod.WIP_ENTITY_ID
AND wdj.status_type = 4);
c1rec c1%ROWTYPE;
l_count_updated NUMBER := 0;
l_count_error NUMBER := 0;
l_user_name VARCHAR2 (100);
error_flag CHAR;
MESSAGE VARCHAR2 (2000) := '';
dummy NUMBER;
l_api_name CONSTANT VARCHAR2 (30) := 'DISPLAY_MESSAGES';
TYPE message_rectyp IS RECORD (
mess VARCHAR2 (1000)
);
TYPE MESSAGE_TYPE IS TABLE OF message_rectyp
INDEX BY BINARY_INTEGER;
message2 MESSAGE_TYPE;
------------------------------------------------------------------------
l_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
l_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
l_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
l_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
l_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
l_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
l_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
l_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
l_out_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
l_out_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
l_out_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
l_out_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
l_out_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
l_out_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
l_out_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
l_out_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
l_out_eam_direct_items_tbl eam_process_wo_pub.eam_direct_items_tbl_type;
x_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
x_eam_op_tbl eam_process_wo_pub.eam_op_tbl_type;
x_eam_op_network_tbl eam_process_wo_pub.eam_op_network_tbl_type;
x_eam_res_tbl eam_process_wo_pub.eam_res_tbl_type;
x_eam_res_inst_tbl eam_process_wo_pub.eam_res_inst_tbl_type;
x_eam_sub_res_tbl eam_process_wo_pub.eam_sub_res_tbl_type;
x_eam_res_usage_tbl eam_process_wo_pub.eam_res_usage_tbl_type;
x_eam_mat_req_tbl eam_process_wo_pub.eam_mat_req_tbl_type;
x_eam_direct_items_tbl eam_process_wo_pub.eam_direct_items_tbl_type;
x_status VARCHAR2 (4000);
x_msg_cnt NUMBER;
x_msg_data VARCHAR2 (4000);
-----------------------------------------------------------------------------
BEGIN
BEGIN
SELECT a.user_name
INTO l_user_name
FROM fnd_user a
WHERE a.user_id = p_user_id;
EXCEPTION
WHEN OTHERS
THEN
l_user_name := 'Unknown';
END;
OPEN c1;
LOOP
FETCH c1
INTO c1rec;
EXIT WHEN c1%NOTFOUND;
l_eam_wo_rec := NULL;
l_eam_wo_rec.wip_entity_id := c1rec.wip_entity_id;
l_eam_wo_rec.user_defined_status_id := 5;
l_eam_wo_rec.status_type := 5;
l_eam_wo_rec.organization_id := 85;
l_eam_wo_rec.transaction_type := eam_process_wo_pvt.g_opr_update;
eam_process_wo_pub.process_wo
(p_bo_identifier => 'EAM',
p_api_version_number => 1.0,
p_init_msg_list => TRUE,
p_commit => 'Y',
p_eam_wo_rec => l_eam_wo_rec,
p_eam_op_tbl => l_eam_op_tbl,
p_eam_op_network_tbl => l_eam_op_network_tbl,
p_eam_res_tbl => l_eam_res_tbl,
p_eam_res_inst_tbl => l_eam_res_inst_tbl,
p_eam_sub_res_tbl => l_eam_sub_res_tbl,
p_eam_res_usage_tbl => l_eam_res_usage_tbl,
p_eam_mat_req_tbl => l_eam_mat_req_tbl,
p_eam_direct_items_tbl => l_out_eam_direct_items_tbl,
x_eam_wo_rec => x_eam_wo_rec,
x_eam_op_tbl => x_eam_op_tbl,
x_eam_op_network_tbl => x_eam_op_network_tbl,
x_eam_res_tbl => x_eam_res_tbl,
x_eam_res_inst_tbl => x_eam_res_inst_tbl,
x_eam_sub_res_tbl => x_eam_sub_res_tbl,
x_eam_res_usage_tbl => x_eam_res_usage_tbl,
x_eam_mat_req_tbl => x_eam_mat_req_tbl,
x_eam_direct_items_tbl => x_eam_direct_items_tbl,
x_return_status => x_status,
x_msg_count => x_msg_cnt,
p_debug => 'N'
-- p_output_dir => '/home/oracle/EAM',
-- p_debug_filename => 'waqas_EAM_WO_DEBUG.log',
-- p_debug_file_mode => 'a'
);
COMMIT;
-- DBMS_OUTPUT.put_line ('Status : ' || x_status);
IF (x_status = 'S')
THEN
l_count_updated := l_count_updated + 1;
message2 (c1rec.count_).mess :=
c1rec.count_ || ' : Work Order : ' || c1rec.wip_entity_name
|| ' S ';
ELSIF x_msg_cnt > 0
THEN
l_count_error := l_count_error + 1;
message2 (c1rec.count_).mess :=
c1rec.count_ || ' Error in WO : ' || c1rec.wip_entity_name;
FOR i IN 1 .. NVL (x_msg_cnt, 0)
LOOP
message2 (c1rec.count_).mess :=
message2 (c1rec.count_).mess
|| fnd_msg_pub.get (i, fnd_api.g_false);
END LOOP;
END IF;
END LOOP;
CLOSE c1;
BEGIN
fnd_file.put_line (fnd_file.LOG,
' Message No of Work Orders Updated '
|| l_count_updated
|| ' '
);
IF (l_count_error > 0)
THEN
fnd_file.put_line (fnd_file.LOG,
' No of Work Orders Not Updated '
|| l_count_error
|| ' '
);
retcode := 1;
END IF;
fnd_file.put_line (fnd_file.LOG,
' Work Orders Updated By ' || l_user_name || ' '
);
FOR i IN 1 .. message2.COUNT
LOOP
fnd_file.put_line (fnd_file.LOG, message2 (i).mess);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
' Message No of Work Orders Updated '
|| l_count_updated
|| ' '
);
IF (l_count_error > 0)
THEN
fnd_file.put_line (fnd_file.LOG,
' No of Work Orders Not Updated '
|| l_count_error
|| ' '
);
retcode := 2;
END IF;
fnd_file.put_line (fnd_file.LOG,
' Work Orders Updated By ' || l_user_name || ' '
);
FOR i IN 1 .. message2.COUNT
LOOP
fnd_file.put_line (fnd_file.LOG, message2 (i).mess);
END LOOP;
END;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
fnd_file.put_line (fnd_file.LOG, ' Message Error Occured ');
retcode := 2;
EXCEPTION
WHEN OTHERS
THEN
fnd_msg_pub.add_exc_msg (' Message Error Occured ', l_api_name);
retcode := 2;
END;
END;
/
1: Location of file
