[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_IMPORT_DS_PVT
Source
1 PACKAGE BODY EAM_WO_IMPORT_DS_PVT as
2 /* $Header: EAMVDSIB.pls 120.0 2005/06/08 02:56:21 appldev noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVDSIB.pls
11 --
12 -- DESCRIPTION
13 -- Package Body for importing Work Order from Interface Tables for
14 -- Detailed Scheduling Project
15 -- NOTES
16 --
17 -- HISTORY
18 --
19 -- 23-SEP-2004 Milind Maduskar Initial Creation
20 ***************************************************************************/
21 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_WO_IMPORT_DS_PVT';
22
23 -- Start of comments
24 -- API name : log_network_error_messages
25 -- Type : Private.
26 -- Function : This procedure will mark all the work orders in hierarchy as invalid
27 -- Pre-reqs : None.
28 -- Parameters :
29 -- IN
30 -- p_group_id IN NUMBER
31 -- Required Group Identifier
32 -- p_top_wip_entity_id IN NUMBER
33 -- Required Top wip entity id of the work order in hierarchy
34 -- p_validate_fail IN BOOLEAN
35 -- Required Will have value of 'TRUE' if this procedure is called because Validate_Structure
36 -- returned error.
37 -- Will have value of 'FALSE' if this procedure is called because l_relationship_errors= TRUE.
38 -- (Update for one of work order in hierarchy has failed.Hence fail entired hierarchy)
39 -- p_wo_relationship_exc_tbl IN EAM_PROCESS_WO_PUB.wo_relationship_exc_tbl_type
40 -- Required Table containing list of exception messages
41 --
42 -- Initial version 1.0
43 --
44 -- End of comments
45
46 PROCEDURE log_network_error_messages(p_group_id NUMBER,
47 p_top_wip_entity_id NUMBER,
48 p_validate_fail BOOLEAN,
49 p_wo_relationship_exc_tbl EAM_PROCESS_WO_PUB.wo_relationship_exc_tbl_type
50 )
51 IS
52 temp_err_mesg VARCHAR2(4000);
53
54 PRAGMA AUTONOMOUS_TRANSACTION;
55 BEGIN
56
57 IF p_validate_fail THEN
58 -- Validate_Structure failed .Fail entire structure with status 3
59 UPDATE EAM_WORK_ORDER_IMPORT
60 SET process_status = 3,
61 last_update_date = sysdate,
62 last_updated_by = fnd_global.user_id,
63 last_update_login = fnd_global.login_id
64 WHERE group_id = p_group_id
65 AND wip_entity_id IN (SELECT wip_entity_id
66 FROM EAM_WORK_ORDER_IMPORT
67 WHERE top_wip_entity_id= p_top_wip_entity_id);
68
69 ELSE
70 -- Update for One work order in hierarchy has failed.Fail entire structure with status 3
71 -- Since some work orders has errored out,they will be in status 4 Hence only pick up
72 -- those work orders which in status 2
73 UPDATE EAM_WORK_ORDER_IMPORT
74 SET process_status = 3,
75 last_update_date = sysdate,
76 last_updated_by = fnd_global.user_id,
77 last_update_login = fnd_global.login_id
78 WHERE group_id = p_group_id
79 AND wip_entity_id in (SELECT wip_entity_id
80 FROM EAM_WORK_ORDER_IMPORT
81 WHERE top_wip_entity_id= p_top_wip_entity_id
82 AND process_status =2);
83 END IF;
84
85 IF p_validate_fail THEN
86 fnd_file.put_line(FND_FILE.LOG,'Status Relationship Error .Structure failed while updating work order hierarchy with parent work order ' || p_top_wip_entity_id);
87 fnd_file.put_line(FND_FILE.LOG,'Error Stack: ');
88 IF p_wo_relationship_exc_tbl.count > 0 THEN
89 FOR work_rel_counter IN p_wo_relationship_exc_tbl.FIRST..p_wo_relationship_exc_tbl.LAST LOOP
90 INSERT INTO EAM_WORK_ORDER_IMPORT_ERRORS(
91 header_id ,
92 group_id ,
93 row_id ,
94 error_message ,
95 last_update_date ,
96 last_updated_by ,
97 creation_date ,
98 created_by ,
99 last_update_login
100 )
101 VALUES(
102 p_top_wip_entity_id,
103 p_group_id,
104 1,
105 p_wo_relationship_exc_tbl(work_rel_counter),
106 sysdate,
107 fnd_global.user_id,
108 sysdate,
109 fnd_global.user_id,
110 fnd_global.login_id
111 );
112 fnd_file.put_line(FND_FILE.LOG,p_wo_relationship_exc_tbl(work_rel_counter));
113 END LOOP;
114 END IF;
115 fnd_file.put_line(FND_FILE.LOG,'Hence Marking following workorders under hierarchy as failed :');
116 FOR log_counter IN (SELECT wip_entity_id
117 FROM EAM_WORK_ORDER_IMPORT
118 WHERE group_id = p_group_id
119 AND top_wip_entity_id=p_top_wip_entity_id
120 )
121 LOOP
122 fnd_file.put_line(FND_FILE.LOG,'Status Relationship Error for work order --' || log_counter.wip_entity_id);
123 END LOOP;
124
125 ELSE -- Validate structure has not failed.Error occured before calling validate structure
126
127 fnd_file.put_line(FND_FILE.LOG,'Status Relationship Error .Structure failed while updating the one of work order under the hierarchy with parent work order ' || p_top_wip_entity_id);
128 fnd_file.put_line(FND_FILE.LOG,'Error Stack :' || temp_err_mesg);
129 fnd_file.put_line(FND_FILE.LOG,'Hence Marking following workorders under hierarchy as failed :');
130 FOR log_counter IN ( SELECT wip_entity_id
131 FROM EAM_WORK_ORDER_IMPORT
132 WHERE group_id = p_group_id
133 AND top_wip_entity_id=p_top_wip_entity_id
134 )
135 LOOP
136 fnd_file.put_line(FND_FILE.LOG,'Status Relationship Error for work order --' || log_counter.wip_entity_id);
137 END LOOP;
138 END IF;
139
140 COMMIT;
141 END log_network_error_messages;
142
143 -- Start of comments
144 -- API name : log_error_messages
145 -- Type : Private.
146 -- Function : In EAM_WORK_ORDER_IMPORT_ERRORS, inserts a row for each error message retrieved from work order API
147 -- message stack. Update the column RETURN_STATUS of the import with the API return_status,
148 -- also update RETURN_STATUS in all child tables with the return status got from the output child
149 -- records such as the ones for operations, resources etc. Update the WHO columns like LAST_UPDATE_DATE,
150 -- LAST_UPDATED_BY, LAST_UPDATE_LOGIN whenever any update is done to any import table record
151 -- Pre-reqs : None.
152 -- Parameters :
153 -- IN :
154 -- l_return_status IN VARCHAR2 Required
155 -- Status returned by work order api.This can be 'E'/'U'
156 -- l_group_id IN NUMBER Required
157 -- Group Id
158 -- p_wo_rec IN EAM_WORK_ORDER_IMPORT%ROWTYPE
159 -- Required Work Order Record fetched from cursor
160 -- p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type Required
161 -- Operation out table from work order api
162 -- p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type Required
163 -- Resource out table from work order api
164 -- p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type Required
165 -- Resource Instance out table from work order api
166 -- p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type Required
167 -- Resource Usage out table from work order api
168 -- p_eam_mat_req_tbl IN EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type Required
169 -- Material out table from work order api
170 --
171 -- Initial version 1.0
172 --
173 -- End of comments
174
175 PROCEDURE log_error_messages(p_return_status VARCHAR2,
176 p_group_id NUMBER,
177 p_wo_rec EAM_WORK_ORDER_IMPORT%ROWTYPE,
178 p_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type,
179 p_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type,
180 p_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type,
181 p_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
182 p_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type
183 )
184 IS
185 l_msg_count NUMBER;
186 msg_index NUMBER;
187 temp_err_mesg VARCHAR2(4000);
188
189 PRAGMA AUTONOMOUS_TRANSACTION;
190 BEGIN
191
192 UPDATE EAM_WORK_ORDER_IMPORT
193 SET process_status = 4 ,
194 last_update_date = sysdate,
195 last_updated_by = fnd_global.user_id,
196 last_update_login = fnd_global.login_id
197 WHERE group_id = p_group_id
198 AND wip_entity_id = p_wo_rec.wip_entity_id;
199
200 -- get the error messages from the work order api exception stack and insert
201 -- into EAM_WORK_ORDER_IMPORT_ERRORS
202
203 l_msg_count := fnd_msg_pub.count_msg;
204 IF(l_msg_count>0) THEN
205 msg_index := l_msg_count;
206 FOR i IN 1..l_msg_count LOOP
207 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_NEXT,
208 p_encoded => 'F',
209 p_data => temp_err_mesg,
210 p_msg_index_out => msg_index);
211
212 INSERT INTO EAM_WORK_ORDER_IMPORT_ERRORS(
213 header_id ,
214 group_id ,
215 row_id ,
216 error_message ,
217 last_update_date ,
218 last_updated_by ,
219 creation_date ,
220 created_by ,
221 last_update_login
222 )
223 VALUES(
224 p_wo_rec.header_id,
225 p_wo_rec.group_id,
226 p_wo_rec.row_id,
227 temp_err_mesg,
228 sysdate,
229 fnd_global.user_id,
230 sysdate,
231 fnd_global.user_id,
232 fnd_global.login_id
233 );
234
235 END LOOP;
236 END IF; -- end if for l_msg_count>0
237 fnd_file.put_line(FND_FILE.LOG,'Status Error for work order --' || p_wo_rec.wip_entity_id);
238 fnd_file.put_line(FND_FILE.LOG,'Error Stack --' || temp_err_mesg);
239
240
241 -- Following code loops through the out tables from work order API and updates the status
242 -- of corresponding import tables.It also updates the who columns
243
244 FOR counter_res_ins IN 1..p_eam_res_inst_tbl.count LOOP
245 UPDATE EAM_RESOURCE_INSTANCE_IMPORT
246 SET return_status = p_eam_res_inst_tbl(counter_res_ins).return_status,
247 last_update_date = sysdate,
248 last_updated_by = fnd_global.user_id,
249 last_update_login = fnd_global.login_id
250 WHERE group_id = p_group_id
251 AND wip_entity_id = p_eam_res_inst_tbl(counter_res_ins).wip_entity_id
252 AND (instance_id = p_eam_res_inst_tbl(counter_res_ins).instance_id OR instance_id IS NULL)
253 AND (serial_number = p_eam_res_inst_tbl(counter_res_ins).serial_number OR serial_number IS NULL);
254 END LOOP;
255
256 FOR counter_res_usg IN 1..p_eam_res_usage_tbl.count LOOP
257 UPDATE EAM_RESOURCE_USAGE_IMPORT
258 SET return_status = p_eam_res_usage_tbl(counter_res_usg).return_status,
259 last_update_date = sysdate,
260 last_updated_by = fnd_global.user_id,
261 last_update_login = fnd_global.login_id
262 WHERE group_id = p_group_id
263 AND wip_entity_id = p_eam_res_usage_tbl(counter_res_usg).wip_entity_id
264 AND resource_seq_num = p_eam_res_usage_tbl(counter_res_usg).resource_seq_num;
265 END LOOP;
266
267 FOR counter_res IN 1..p_eam_res_tbl.count LOOP
268 UPDATE EAM_RESOURCE_IMPORT
269 SET return_status = p_eam_res_tbl(counter_res).return_status,
270 last_update_date = sysdate,
271 last_updated_by = fnd_global.user_id,
272 last_update_login = fnd_global.login_id
273 WHERE group_id = p_group_id
274 AND wip_entity_id = p_eam_res_tbl(counter_res).wip_entity_id
275 AND resource_seq_num = p_eam_res_tbl(counter_res).resource_seq_num;
276 END LOOP;
277
278 FOR counter_mat IN 1..p_eam_mat_req_tbl.count LOOP
279 UPDATE EAM_MATERIAL_IMPORT
280 SET return_status = p_eam_mat_req_tbl(counter_mat).return_status,
281 last_update_date = sysdate,
282 last_updated_by = fnd_global.user_id,
283 last_update_login = fnd_global.login_id
284 WHERE group_id = p_group_id
285 AND wip_entity_id = p_eam_mat_req_tbl(counter_mat).wip_entity_id
286 AND inventory_item_id = p_eam_mat_req_tbl(counter_mat).inventory_item_id;
287 END LOOP;
288
289 FOR counter_op IN 1..p_eam_op_tbl.count LOOP
290 UPDATE EAM_OPERATION_IMPORT
291 SET return_status = p_eam_op_tbl(counter_op).return_status,
292 last_update_date = sysdate,
293 last_updated_by = fnd_global.user_id,
294 last_update_login = fnd_global.login_id
295 WHERE group_id = p_group_id
296 AND wip_entity_id = p_eam_op_tbl(counter_op).wip_entity_id
297 AND operation_seq_num = p_eam_op_tbl(counter_op).operation_seq_num;
298 END LOOP;
299
300 UPDATE EAM_WORK_ORDER_IMPORT
301 SET return_status = p_return_status,
302 last_update_date = sysdate,
303 last_updated_by = fnd_global.user_id,
304 last_update_login = fnd_global.login_id
305 WHERE group_id = p_group_id
306 AND wip_entity_id = p_wo_rec.wip_entity_id;
307 COMMIT;
308 END;
309
310 -- Start of comments
311 -- API name : IMPORT_WORKORDER
312 -- Type : Private.
313 -- Pre-reqs : None.
314 -- Function : This is a concurrent program.It reads the data from interface tables and updates correponding entries
315 -- in WIP tables.It logs the error into the error table.The log file of the concurrent program gives
316 -- the details of work order that has been imported sucessfully and the work orders that has errored
317 -- out while importing.
318 -- Parameters :
319 -- IN :
320 -- P_GROUP_ID IN NUMBER Required
321 -- Is a mandatory parameter.Its an id to process a group of records together.
322 -- OUT
323 -- errbuf OUT NOCOPY VARCHAR2 mandatory out parameters for the concurrent program.
324 -- retcode OUT NOCOPY NUMBER mandatory out parameters for the concurrent program.
325 -- Initial version 1.0
326 -- End of comments
327
328 PROCEDURE IMPORT_WORKORDER(
329 errbuf OUT NOCOPY VARCHAR2,
330 retcode OUT NOCOPY NUMBER,
331 P_GROUP_ID IN NUMBER)
332 IS
333
334 l_top_wip_entity_id NUMBER;
335 l_old_top_wip_entity_id NUMBER;
336 l_entering_rel_structure BOOLEAN:=false;
337 l_exiting_rel_structure BOOLEAN:=false;
338 l_standalone BOOLEAN:=false;
339 l_last_record BOOLEAN:=false;
340 l_relationship_errors BOOLEAN:=false;
341 l_group_id NUMBER;
342
343 l_output_dir VARCHAR2(255);
344 l_return_status VARCHAR2(1);
345 l_msg_count NUMBER;
346
347 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
348 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
349 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
350 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
351 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
352 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
353 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
354 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
355 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
356 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
357 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
358 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
359 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
360 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
361 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
362 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
363 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
364
365 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
366 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
367 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
368 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
369 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
370 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
371 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
372 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
373 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
374 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
375 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
376 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
377 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
378 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
379 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
380 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
381 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
382
383 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
384 l_eam_res_rec EAM_PROCESS_WO_PUB.eam_res_rec_type;
385 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
386 l_eam_res_usage_rec EAM_PROCESS_WO_PUB.eam_res_usage_rec_type;
387 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type ;
388
389 l_operation_index NUMBER;
390 l_material_index NUMBER;
391 l_resource_index NUMBER;
392 l_resource_usage_index NUMBER;
393 l_resource_instance_index NUMBER;
394 l_conc_status BOOLEAN;
395 l_error_message VARCHAR2(4000);
396
397 TYPE l_import_wo_records IS REF CURSOR RETURN EAM_WORK_ORDER_IMPORT%ROWTYPE;
398 l_import_wo l_import_wo_records;
399 l_import_wo_record EAM_WORK_ORDER_IMPORT%ROWTYPE;
400
401 l_wo_relationship_exc_tbl EAM_PROCESS_WO_PUB.wo_relationship_exc_tbl_type;
402
403 l_msg_data VARCHAR2(4000);
404
405 CURSOR import_wo_oper_cur (l_group_id NUMBER,l_wip_entity_id NUMBER) IS
406 SELECT *
407 FROM EAM_OPERATION_IMPORT EOI
408 WHERE eoi.group_id = l_group_id
409 AND eoi.wip_entity_id = l_wip_entity_id;
410
411 CURSOR import_wo_material_cur (l_group_id NUMBER,l_wip_entity_id NUMBER) IS
412 SELECT *
413 FROM EAM_MATERIAL_IMPORT EMI
414 WHERE emi.group_id = l_group_id
415 AND emi.wip_entity_id = l_wip_entity_id;
416
417 CURSOR import_wo_resource_cur (l_p_group_id NUMBER,l_wip_entity_id NUMBER) IS
418 SELECT *
419 FROM EAM_RESOURCE_IMPORT ERI
420 WHERE eri.group_id = l_group_id
421 AND eri.wip_entity_id = l_wip_entity_id;
422
423 CURSOR import_wo_resource_usage_cur (l_group_id NUMBER,l_wip_entity_id NUMBER) IS
424 SELECT *
425 FROM EAM_RESOURCE_USAGE_IMPORT ERUI
426 WHERE erui.group_id = l_group_id
427 AND erui.wip_entity_id = l_wip_entity_id;
428
429 CURSOR import_wo_res_instance_cur (l_group_id NUMBER,l_wip_entity_id NUMBER) IS
430 SELECT *
431 FROM EAM_RESOURCE_INSTANCE_IMPORT ERIM
432 WHERE erim.group_id = l_group_id
433 AND erim.wip_entity_id = l_wip_entity_id;
434
435 BEGIN
436
437 fnd_file.put_line(FND_FILE.LOG,'-----------START OF WORK ORDER IMPORT CONCURRENT PROGRAM-----------');
438
439 retcode:=0;
440 l_error_message :='No error message ';
441 l_group_id:= P_GROUP_ID;
442
443 -- Update the status of work order from Pending to Running
444 UPDATE EAM_WORK_ORDER_IMPORT
445 SET process_status = 2
446 WHERE group_id = l_group_id
447 AND process_status = 1 ;
448
449 -- Clear out any previously errored rows which are resubmitted
450 DELETE EAM_WORK_ORDER_IMPORT_ERRORS
451 WHERE header_id IN (
452 SELECT wip_entity_id
453 FROM EAM_WORK_ORDER_IMPORT
454 WHERE group_id = l_group_id
455 AND process_status =2
456 );
457
458 -- Update the rows and set the value of top_wip_entity_id
459 UPDATE EAM_WORK_ORDER_IMPORT ewoi
460 SET top_wip_entity_id = (
461 SELECT distinct wsr.top_level_object_id
462 FROM WIP_SCHED_RELATIONSHIPS wsr
463 WHERE wsr.relationship_type =1
464 AND (ewoi.wip_entity_id = wsr.child_object_id OR ewoi.wip_entity_id = wsr.parent_object_id)
465 )
466 WHERE process_status = 2;
467
468 /* get output directory path from database */
469 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
470 COMMIT;
471
472 -- Open Cursor
473 IF NOT l_import_wo%ISOPEN THEN
474 OPEN l_import_wo FOR
475 SELECT *
476 FROM EAM_WORK_ORDER_IMPORT
477 WHERE group_id = l_group_id
478 AND process_status = 2
479 ORDER BY top_wip_entity_id ASC;
480 END IF;
481
482 LOOP
483 BEGIN
484 FETCH l_import_wo INTO
485 l_import_wo_record;
486
487 IF l_import_wo%NOTFOUND THEN
488 l_last_record:=TRUE;
489 l_top_wip_entity_id:=null;
490 END IF ;
491
492 -- Delete tables befor calling work order api
493 l_eam_op_tbl.delete;
494 l_eam_op_network_tbl.delete;
495 l_eam_res_tbl.delete;
496 l_eam_res_inst_tbl.delete;
497 l_eam_sub_res_tbl.delete;
498 l_eam_res_usage_tbl.delete;
499 l_eam_mat_req_tbl.delete;
500 l_eam_di_tbl.delete;
501 l_eam_wo_quality_tbl.delete;
502 l_eam_meter_reading_tbl.delete;
503 l_eam_wo_comp_rebuild_tbl.delete;
504 l_eam_wo_comp_mr_read_tbl.delete;
505 l_eam_op_comp_tbl.delete;
506 l_eam_request_tbl.delete;
507 l_out_eam_op_tbl.delete;
508 l_out_eam_op_network_tbl.delete;
509 l_out_eam_res_tbl.delete;
510 l_out_eam_res_inst_tbl.delete;
511 l_out_eam_sub_res_tbl.delete;
512 l_out_eam_res_usage_tbl.delete;
513 l_out_eam_mat_req_tbl.delete;
514 l_out_eam_di_tbl.delete;
515 l_out_eam_wo_quality_tbl.delete;
516 l_out_eam_meter_reading_tbl.delete;
517 l_out_eam_wo_comp_rebuild_tbl.delete;
518 l_out_eam_wo_comp_mr_read_tbl.delete;
519 l_out_eam_op_comp_tbl.delete;
520 l_out_eam_request_tbl.delete;
521 l_out_eam_counter_prop_tbl.delete;
522
523 IF l_last_record = FALSE THEN
524 l_top_wip_entity_id :=l_import_wo_record.top_wip_entity_id;
525
526 IF l_top_wip_entity_id IS NULL THEN
527 l_standalone :=TRUE;
528 END IF;
529
530 IF (l_top_wip_entity_id IS NOT NULL AND l_old_top_wip_entity_id IS NULL) OR
531 (l_top_wip_entity_id <> l_old_top_wip_entity_id) THEN
532 l_entering_rel_structure :=TRUE;
533 ELSE
534 l_entering_rel_structure :=FALSE;
535 END IF;
536 END IF;
537
538 IF (l_old_top_wip_entity_id IS NOT NULL AND l_top_wip_entity_id IS NULL) OR
539 (l_top_wip_entity_id <> l_old_top_wip_entity_id) THEN
540 l_exiting_rel_structure := TRUE;
541 ELSE
542 l_exiting_rel_structure := FALSE;
543 END IF;
544
545 IF l_exiting_rel_structure = TRUE THEN
546
547 IF l_relationship_errors = TRUE THEN
548 ROLLBACK TO EAM_REL_STRUCTURE_START;
549 l_wo_relationship_exc_tbl.delete;
550 log_network_error_messages(l_group_id,l_old_top_wip_entity_id,false,l_wo_relationship_exc_tbl);
551
552 ELSE
553 l_wo_relationship_exc_tbl.delete;
554 EAM_WO_NETWORK_VALIDATE_PVT.Validate_Structure
555 (
556 p_api_version => 1.0,
557 p_init_msg_list => FND_API.G_FALSE,
558 p_commit => FND_API.G_FALSE,
559 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
560
561 p_work_object_id => l_old_top_wip_entity_id,
562 p_work_object_type_id => 1,
563 p_exception_logging => 'Y',
564
565 p_validate_status => 'Y',
566 p_output_errors => 'Y',
567
568 x_return_status => l_return_status,
569 x_msg_count => l_msg_count,
570 x_msg_data => l_msg_data,
571 x_wo_relationship_exc_tbl => l_wo_relationship_exc_tbl
572 );
573
574 IF l_return_status = FND_API.G_RET_STS_ERROR OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
575 log_network_error_messages(l_group_id,l_old_top_wip_entity_id,true,l_wo_relationship_exc_tbl);
576 ROLLBACK TO EAM_REL_STRUCTURE_START;
577 ELSE
578 -- if it succedds we need to purge data
579 -- Purge this import record and associated records from the child tables.
580
581 FOR log_counter IN ( SELECT wip_entity_id
582 FROM EAM_WORK_ORDER_IMPORT
583 WHERE group_id = l_group_id
584 AND top_wip_entity_id = l_old_top_wip_entity_id) LOOP
585 fnd_file.put_line(FND_FILE.LOG,'Status Sucess for work order --' || log_counter.wip_entity_id);
586 END LOOP;
587
588 DELETE
589 FROM EAM_RESOURCE_INSTANCE_IMPORT
590 WHERE group_id = l_group_id
591 AND wip_entity_id IN (
592 SELECT wip_entity_id
593 FROM EAM_WORK_ORDER_IMPORT
594 WHERE group_id = l_group_id
595 AND top_wip_entity_id = l_old_top_wip_entity_id
596 );
597
598 DELETE
599 FROM EAM_RESOURCE_USAGE_IMPORT
600 WHERE group_id = l_group_id
601 AND wip_entity_id IN (
602 SELECT wip_entity_id
603 FROM EAM_WORK_ORDER_IMPORT
604 WHERE group_id = l_group_id
605 AND top_wip_entity_id = l_old_top_wip_entity_id
606 );
607
608 DELETE
609 FROM EAM_RESOURCE_IMPORT
610 WHERE group_id = l_group_id
611 AND wip_entity_id IN (
612 SELECT wip_entity_id
613 FROM EAM_WORK_ORDER_IMPORT
614 WHERE group_id = l_group_id
615 AND top_wip_entity_id = l_old_top_wip_entity_id
616 );
617
618 DELETE
619 FROM EAM_MATERIAL_IMPORT
620 WHERE group_id = l_group_id
621 AND wip_entity_id IN (
622 SELECT wip_entity_id
623 FROM EAM_WORK_ORDER_IMPORT
624 WHERE group_id = l_group_id
625 AND top_wip_entity_id = l_old_top_wip_entity_id
626 );
627
628 DELETE
629 FROM EAM_OPERATION_IMPORT
630 WHERE group_id = l_group_id
631 AND wip_entity_id IN (
632 SELECT wip_entity_id
633 FROM EAM_WORK_ORDER_IMPORT
634 WHERE group_id = l_group_id
635 AND top_wip_entity_id = l_old_top_wip_entity_id
636 );
637
638 DELETE
639 FROM EAM_WORK_ORDER_IMPORT
640 WHERE group_id = l_group_id
641 AND top_wip_entity_id = l_old_top_wip_entity_id;
642
643 COMMIT;
644 END IF;
645 END IF;
646 END IF;
647
648 IF l_entering_rel_structure = TRUE THEN
649 SAVEPOINT EAM_REL_STRUCTURE_START;
650 l_relationship_errors := FALSE;
651 END IF;
652
653 IF l_last_record THEN
654 COMMIT;
655 EXIT;
656 END IF;
657
658 l_old_top_wip_entity_id:=l_top_wip_entity_id;
659
660 SAVEPOINT EAM_WORK_ORDER_START;
661
662 l_eam_wo_rec.header_id :=l_import_wo_record.header_id;
663 l_eam_wo_rec.batch_id :=l_import_wo_record.group_id;
664 l_eam_wo_rec.row_id :=l_import_wo_record.row_id;
665 l_eam_wo_rec.wip_entity_name :=l_import_wo_record.wip_entity_name;
666 l_eam_wo_rec.wip_entity_id :=l_import_wo_record.wip_entity_id;
667 l_eam_wo_rec.organization_id :=l_import_wo_record.organization_id;
668 l_eam_wo_rec.description :=l_import_wo_record.description;
669 l_eam_wo_rec.asset_number :=l_import_wo_record.asset_number;
670 l_eam_wo_rec.asset_group_id :=l_import_wo_record.asset_group_id;
671 l_eam_wo_rec.rebuild_item_id :=l_import_wo_record.rebuild_item_id;
672 l_eam_wo_rec.rebuild_serial_number :=l_import_wo_record.rebuild_serial_number;
673 l_eam_wo_rec.maintenance_object_id :=l_import_wo_record.maintenance_object_id;
674 l_eam_wo_rec.maintenance_object_type :=l_import_wo_record.maintenance_object_type;
675 l_eam_wo_rec.maintenance_object_source :=l_import_wo_record.maintenance_object_source;
676 l_eam_wo_rec.class_code :=l_import_wo_record.class_code;
677 l_eam_wo_rec.asset_activity_id :=l_import_wo_record.asset_activity_id;
678 l_eam_wo_rec.activity_type :=l_import_wo_record.activity_type;
679 l_eam_wo_rec.activity_cause :=l_import_wo_record.activity_cause;
680 l_eam_wo_rec.activity_source :=l_import_wo_record.activity_source;
681 l_eam_wo_rec.work_order_type :=l_import_wo_record.work_order_type;
682 l_eam_wo_rec.status_type :=l_import_wo_record.status_type;
683 l_eam_wo_rec.job_quantity :=l_import_wo_record.job_quantity;
684 l_eam_wo_rec.date_released :=l_import_wo_record.date_released;
685 l_eam_wo_rec.owning_department :=l_import_wo_record.owning_department;
686 l_eam_wo_rec.priority :=l_import_wo_record.priority;
687 l_eam_wo_rec.requested_start_date :=l_import_wo_record.requested_start_date;
688 l_eam_wo_rec.due_date :=l_import_wo_record.due_date;
689 l_eam_wo_rec.shutdown_type :=l_import_wo_record.shutdown_type;
690 l_eam_wo_rec.firm_planned_flag :=l_import_wo_record.firm_planned_flag;
691 l_eam_wo_rec.notification_required :=l_import_wo_record.notification_required;
692 l_eam_wo_rec.tagout_required :=l_import_wo_record.tagout_required;
693 l_eam_wo_rec.plan_maintenance :=l_import_wo_record.plan_maintenance;
694 l_eam_wo_rec.project_id :=l_import_wo_record.project_id;
695 l_eam_wo_rec.task_id :=l_import_wo_record.task_id;
696 l_eam_wo_rec.end_item_unit_number :=l_import_wo_record.end_item_unit_number;
697 l_eam_wo_rec.schedule_group_id :=l_import_wo_record.schedule_group_id;
698 l_eam_wo_rec.bom_revision_date :=l_import_wo_record.bom_revision_date;
699 l_eam_wo_rec.routing_revision_date :=l_import_wo_record.routing_revision_date;
700 l_eam_wo_rec.alternate_routing_designator :=l_import_wo_record.alternate_routing_designator;
701 l_eam_wo_rec.alternate_bom_designator :=l_import_wo_record.alternate_bom_designator;
702 l_eam_wo_rec.routing_revision :=l_import_wo_record.routing_revision;
703 l_eam_wo_rec.bom_revision :=l_import_wo_record.bom_revision;
704 l_eam_wo_rec.parent_wip_entity_id :=l_import_wo_record.parent_wip_entity_id;
705 l_eam_wo_rec.manual_rebuild_flag :=l_import_wo_record.manual_rebuild_flag;
706 l_eam_wo_rec.pm_schedule_id :=l_import_wo_record.pm_schedule_id;
707 l_eam_wo_rec.wip_supply_type :=l_import_wo_record.wip_supply_type;
708 l_eam_wo_rec.material_account :=l_import_wo_record.material_account;
709 l_eam_wo_rec.material_overhead_account :=l_import_wo_record.material_overhead_account;
710 l_eam_wo_rec.resource_account :=l_import_wo_record.resource_account;
711 l_eam_wo_rec.outside_processing_account :=l_import_wo_record.outside_processing_account;
712 l_eam_wo_rec.material_variance_account :=l_import_wo_record.material_variance_account;
713 l_eam_wo_rec.resource_variance_account :=l_import_wo_record.resource_variance_account;
714 l_eam_wo_rec.outside_proc_variance_account :=l_import_wo_record.outside_proc_variance_account;
715 l_eam_wo_rec.std_cost_adjustment_account :=l_import_wo_record.std_cost_adjustment_account;
716 l_eam_wo_rec.overhead_account :=l_import_wo_record.overhead_account;
717 l_eam_wo_rec.overhead_variance_account :=l_import_wo_record.overhead_variance_account;
718 l_eam_wo_rec.scheduled_start_date :=l_import_wo_record.scheduled_start_date;
719 l_eam_wo_rec.scheduled_completion_date :=l_import_wo_record.scheduled_completion_date;
720 l_eam_wo_rec.common_bom_sequence_id :=l_import_wo_record.common_bom_sequence_id;
721 l_eam_wo_rec.common_routing_sequence_id :=l_import_wo_record.common_routing_sequence_id;
722 l_eam_wo_rec.po_creation_time :=l_import_wo_record.po_creation_time;
723 l_eam_wo_rec.gen_object_id :=l_import_wo_record.gen_object_id;
724 l_eam_wo_rec.attribute_category :=l_import_wo_record.attribute_category;
725 l_eam_wo_rec.attribute1 :=l_import_wo_record.attribute1;
726 l_eam_wo_rec.attribute2 :=l_import_wo_record.attribute2;
727 l_eam_wo_rec.attribute3 :=l_import_wo_record.attribute3;
728 l_eam_wo_rec.attribute4 :=l_import_wo_record.attribute4;
729 l_eam_wo_rec.attribute5 :=l_import_wo_record.attribute5;
730 l_eam_wo_rec.attribute6 :=l_import_wo_record.attribute6;
731 l_eam_wo_rec.attribute7 :=l_import_wo_record.attribute7;
732 l_eam_wo_rec.attribute8 :=l_import_wo_record.attribute8;
733 l_eam_wo_rec.attribute9 :=l_import_wo_record.attribute9;
734 l_eam_wo_rec.attribute10 :=l_import_wo_record.attribute10;
735 l_eam_wo_rec.attribute11 :=l_import_wo_record.attribute11;
736 l_eam_wo_rec.attribute12 :=l_import_wo_record.attribute12;
737 l_eam_wo_rec.attribute13 :=l_import_wo_record.attribute13;
738 l_eam_wo_rec.attribute14 :=l_import_wo_record.attribute14;
739 l_eam_wo_rec.attribute15 :=l_import_wo_record.attribute15;
740 l_eam_wo_rec.material_issue_by_mo :=l_import_wo_record.material_issue_by_mo;
741 l_eam_wo_rec.issue_zero_cost_flag :=l_import_wo_record.issue_zero_cost_flag;
742 l_eam_wo_rec.user_id :=l_import_wo_record.user_id;
743 l_eam_wo_rec.responsibility_id :=l_import_wo_record.responsibility_id;
744 l_eam_wo_rec.request_id :=l_import_wo_record.request_id;
745 l_eam_wo_rec.program_id :=l_import_wo_record.program_id;
746 l_eam_wo_rec.program_application_id :=l_import_wo_record.program_application_id;
747 l_eam_wo_rec.source_line_id :=l_import_wo_record.source_line_id;
748 l_eam_wo_rec.source_code :=l_import_wo_record.source_code;
749 l_eam_wo_rec.validate_structure :='Y';
750 l_eam_wo_rec.ds_scheduled_flag :='Y';
751 l_eam_wo_rec.return_status :=l_import_wo_record.return_status;
752 l_eam_wo_rec.transaction_type :=l_import_wo_record.transaction_type;
753
754 l_operation_index := 1;
755
756 FOR l_wo_op IN import_wo_oper_cur (l_group_id,l_eam_wo_rec.wip_entity_id)
757 LOOP
758 l_eam_op_rec.header_id :=l_wo_op.header_id;
759 l_eam_op_rec.batch_id :=l_wo_op.group_id;
760 l_eam_op_rec.row_id :=l_wo_op.row_id;
761 l_eam_op_rec.wip_entity_id :=l_wo_op.wip_entity_id;
762 l_eam_op_rec.organization_id :=l_wo_op.organization_id;
763 l_eam_op_rec.operation_seq_num :=l_wo_op.operation_seq_num;
764 l_eam_op_rec.standard_operation_id :=l_wo_op.standard_operation_id;
765 l_eam_op_rec.department_id :=l_wo_op.department_id;
766 l_eam_op_rec.operation_sequence_id :=l_wo_op.operation_sequence_id;
767 l_eam_op_rec.description :=l_wo_op.description;
768 l_eam_op_rec.minimum_transfer_quantity :=l_wo_op.minimum_transfer_quantity;
769 l_eam_op_rec.count_point_type :=l_wo_op.count_point_type;
770 l_eam_op_rec.backflush_flag :=l_wo_op.backflush_flag;
771 l_eam_op_rec.shutdown_type :=l_wo_op.shutdown_type;
772 l_eam_op_rec.start_date :=l_wo_op.start_date;
773 l_eam_op_rec.completion_date :=l_wo_op.completion_date;
774 l_eam_op_rec.attribute_category :=l_wo_op.attribute_category;
775 l_eam_op_rec.attribute1 :=l_wo_op.attribute1;
776 l_eam_op_rec.attribute2 :=l_wo_op.attribute2;
777 l_eam_op_rec.attribute3 :=l_wo_op.attribute3;
778 l_eam_op_rec.attribute4 :=l_wo_op.attribute4;
779 l_eam_op_rec.attribute5 :=l_wo_op.attribute5;
780 l_eam_op_rec.attribute6 :=l_wo_op.attribute6;
781 l_eam_op_rec.attribute7 :=l_wo_op.attribute7;
782 l_eam_op_rec.attribute8 :=l_wo_op.attribute8;
783 l_eam_op_rec.attribute9 :=l_wo_op.attribute9;
784 l_eam_op_rec.attribute10 :=l_wo_op.attribute10;
785 l_eam_op_rec.attribute11 :=l_wo_op.attribute11;
786 l_eam_op_rec.attribute12 :=l_wo_op.attribute12;
787 l_eam_op_rec.attribute13 :=l_wo_op.attribute13;
788 l_eam_op_rec.attribute14 :=l_wo_op.attribute14;
789 l_eam_op_rec.attribute15 :=l_wo_op.attribute15;
790 l_eam_op_rec.long_description :=l_wo_op.long_description;
791 l_eam_op_rec.request_id :=l_eam_wo_rec.request_id;
792 l_eam_op_rec.program_application_id :=l_eam_wo_rec.program_application_id;
793 l_eam_op_rec.program_id :=l_eam_wo_rec.program_id;
794 l_eam_op_rec.return_status :=l_wo_op.return_status;
795 l_eam_op_rec.transaction_type :=l_wo_op.transaction_type;
796
797 l_eam_op_tbl(l_operation_index) := l_eam_op_rec;
798 l_operation_index := l_operation_index + 1;
799
800 END LOOP;
801
802 l_material_index := 1;
803
804 FOR l_wo_mat IN import_wo_material_cur (l_group_id,l_eam_wo_rec.wip_entity_id)
805 LOOP
806 l_eam_mat_req_rec.header_id :=l_wo_mat.header_id;
807 l_eam_mat_req_rec.batch_id :=l_wo_mat.group_id;
808 l_eam_mat_req_rec.row_id :=l_wo_mat.row_id;
809 l_eam_mat_req_rec.wip_entity_id :=l_wo_mat.wip_entity_id;
810 l_eam_mat_req_rec.organization_id :=l_wo_mat.organization_id;
811 l_eam_mat_req_rec.operation_seq_num :=l_wo_mat.operation_seq_num;
812 l_eam_mat_req_rec.inventory_item_id :=l_wo_mat.inventory_item_id;
813 l_eam_mat_req_rec.quantity_per_assembly :=l_wo_mat.quantity_per_assembly;
814 l_eam_mat_req_rec.department_id :=l_wo_mat.department_id;
815 l_eam_mat_req_rec.wip_supply_type :=l_wo_mat.wip_supply_type;
816 l_eam_mat_req_rec.date_required :=l_wo_mat.date_required;
817 l_eam_mat_req_rec.required_quantity :=l_wo_mat.required_quantity;
818 l_eam_mat_req_rec.requested_quantity :=l_wo_mat.requested_quantity;
819 l_eam_mat_req_rec.released_quantity :=l_wo_mat.released_quantity;
820 l_eam_mat_req_rec.quantity_issued :=l_wo_mat.quantity_issued;
821 l_eam_mat_req_rec.supply_subinventory :=l_wo_mat.supply_subinventory;
822 l_eam_mat_req_rec.supply_locator_id :=l_wo_mat.supply_locator_id;
823 l_eam_mat_req_rec.mrp_net_flag :=l_wo_mat.mrp_net_flag;
824 l_eam_mat_req_rec.mps_required_quantity :=l_wo_mat.mps_required_quantity;
825 l_eam_mat_req_rec.mps_date_required :=l_wo_mat.mps_date_required;
826 l_eam_mat_req_rec.component_sequence_id :=l_wo_mat.component_sequence_id;
827 l_eam_mat_req_rec.comments :=l_wo_mat.comments;
828 l_eam_mat_req_rec.attribute_category :=l_wo_mat.attribute_category;
829 l_eam_mat_req_rec.attribute1 :=l_wo_mat.attribute1;
830 l_eam_mat_req_rec.attribute2 :=l_wo_mat.attribute2;
831 l_eam_mat_req_rec.attribute3 :=l_wo_mat.attribute3;
832 l_eam_mat_req_rec.attribute4 :=l_wo_mat.attribute4;
833 l_eam_mat_req_rec.attribute5 :=l_wo_mat.attribute5;
834 l_eam_mat_req_rec.attribute6 :=l_wo_mat.attribute6;
835 l_eam_mat_req_rec.attribute7 :=l_wo_mat.attribute7;
836 l_eam_mat_req_rec.attribute8 :=l_wo_mat.attribute8;
837 l_eam_mat_req_rec.attribute9 :=l_wo_mat.attribute9;
838 l_eam_mat_req_rec.attribute10 :=l_wo_mat.attribute10;
839 l_eam_mat_req_rec.attribute11 :=l_wo_mat.attribute11;
840 l_eam_mat_req_rec.attribute12 :=l_wo_mat.attribute12;
841 l_eam_mat_req_rec.attribute13 :=l_wo_mat.attribute13;
842 l_eam_mat_req_rec.attribute14 :=l_wo_mat.attribute14;
843 l_eam_mat_req_rec.attribute15 :=l_wo_mat.attribute15;
844 l_eam_mat_req_rec.auto_request_material :=l_wo_mat.auto_request_material;
845 l_eam_mat_req_rec.suggested_vendor_name :=l_wo_mat.suggested_vendor_name;
846 l_eam_mat_req_rec.vendor_id :=l_wo_mat.vendor_id;
847 l_eam_mat_req_rec.unit_price :=l_wo_mat.unit_price;
848 l_eam_mat_req_rec.request_id :=l_eam_wo_rec.request_id;
849 l_eam_mat_req_rec.program_application_id :=l_eam_wo_rec.program_application_id;
850 l_eam_mat_req_rec.program_id :=l_eam_wo_rec.program_id;
851 l_eam_mat_req_rec.return_status :=l_wo_mat.return_status;
852 l_eam_mat_req_rec.transaction_type :=l_wo_mat.transaction_type;
853
854 l_eam_mat_req_tbl(l_material_index) := l_eam_mat_req_rec;
855 l_material_index := l_material_index + 1;
856 END LOOP;
857
858 l_resource_index :=1;
859 FOR l_wo_res IN import_wo_resource_cur (l_group_id,l_eam_wo_rec.wip_entity_id)
860 LOOP
861 l_eam_res_rec.header_id :=l_wo_res.header_id;
862 l_eam_res_rec.batch_id :=l_wo_res.group_id;
863 l_eam_res_rec.row_id :=l_wo_res.row_id;
864 l_eam_res_rec.wip_entity_id :=l_wo_res.wip_entity_id;
865 l_eam_res_rec.organization_id :=l_wo_res.organization_id;
866 l_eam_res_rec.operation_seq_num :=l_wo_res.operation_seq_num;
867 l_eam_res_rec.resource_seq_num :=l_wo_res.resource_seq_num;
868 l_eam_res_rec.resource_id :=l_wo_res.resource_id;
869 l_eam_res_rec.uom_code :=l_wo_res.uom_code;
870 l_eam_res_rec.basis_type :=l_wo_res.basis_type;
871 l_eam_res_rec.usage_rate_or_amount :=l_wo_res.usage_rate_or_amount;
872 l_eam_res_rec.activity_id :=l_wo_res.activity_id;
873 l_eam_res_rec.scheduled_flag :=l_wo_res.scheduled_flag;
874 l_eam_res_rec.firm_flag :=l_wo_res.firm_flag;
875 l_eam_res_rec.assigned_units :=l_wo_res.assigned_units;
876 l_eam_res_rec.maximum_assigned_units :=l_wo_res.max_assigned_units;
877 l_eam_res_rec.autocharge_type :=l_wo_res.autocharge_type;
878 l_eam_res_rec.standard_rate_flag :=l_wo_res.standard_rate_flag;
879 l_eam_res_rec.applied_resource_units :=l_wo_res.applied_resource_units;
880 l_eam_res_rec.applied_resource_value :=l_wo_res.applied_resource_value;
881 l_eam_res_rec.start_date :=l_wo_res.start_date;
882 l_eam_res_rec.completion_date :=l_wo_res.completion_date;
883 l_eam_res_rec.schedule_seq_num :=l_wo_res.schedule_seq_num;
884 l_eam_res_rec.substitute_group_num :=l_wo_res.substitute_group_num;
885 l_eam_res_rec.replacement_group_num :=l_wo_res.replacement_group_num;
886 l_eam_res_rec.attribute_category :=l_wo_res.attribute_category;
887 l_eam_res_rec.attribute1 :=l_wo_res.attribute1;
888 l_eam_res_rec.attribute2 :=l_wo_res.attribute2;
889 l_eam_res_rec.attribute3 :=l_wo_res.attribute3;
890 l_eam_res_rec.attribute4 :=l_wo_res.attribute4;
891 l_eam_res_rec.attribute5 :=l_wo_res.attribute5;
892 l_eam_res_rec.attribute6 :=l_wo_res.attribute6;
893 l_eam_res_rec.attribute7 :=l_wo_res.attribute7;
894 l_eam_res_rec.attribute8 :=l_wo_res.attribute8;
895 l_eam_res_rec.attribute9 :=l_wo_res.attribute9;
896 l_eam_res_rec.attribute10 :=l_wo_res.attribute10;
897 l_eam_res_rec.attribute11 :=l_wo_res.attribute11;
898 l_eam_res_rec.attribute12 :=l_wo_res.attribute12;
899 l_eam_res_rec.attribute13 :=l_wo_res.attribute13;
900 l_eam_res_rec.attribute14 :=l_wo_res.attribute14;
901 l_eam_res_rec.attribute15 :=l_wo_res.attribute15;
902 l_eam_res_rec.department_id :=l_wo_res.department_id;
903 l_eam_res_rec.request_id :=l_eam_wo_rec.request_id;
904 l_eam_res_rec.program_application_id :=l_eam_wo_rec.program_application_id;
905 l_eam_res_rec.program_id :=l_eam_wo_rec.program_id;
906 l_eam_res_rec.return_status :=l_wo_res.return_status;
907 l_eam_res_rec.transaction_type :=l_wo_res.transaction_type;
908
909 l_eam_res_tbl(l_resource_index) := l_eam_res_rec;
910 l_resource_index := l_resource_index + 1;
911 END LOOP;
912
913 l_resource_usage_index :=1;
914 FOR l_wo_res_usage IN import_wo_resource_usage_cur (l_group_id,l_eam_wo_rec.wip_entity_id)
915 LOOP
916 l_eam_res_usage_rec.header_id :=l_wo_res_usage.header_id;
917 l_eam_res_usage_rec.batch_id :=l_wo_res_usage.group_id;
918 l_eam_res_usage_rec.row_id :=l_wo_res_usage.row_id;
919 l_eam_res_usage_rec.wip_entity_id :=l_wo_res_usage.wip_entity_id;
920 l_eam_res_usage_rec.operation_seq_num :=l_wo_res_usage.operation_seq_num;
921 l_eam_res_usage_rec.resource_seq_num :=l_wo_res_usage.resource_seq_num;
922 l_eam_res_usage_rec.organization_id :=l_wo_res_usage.organization_id;
923 l_eam_res_usage_rec.start_date :=l_wo_res_usage.start_date;
924 l_eam_res_usage_rec.completion_date :=l_wo_res_usage.completion_date;
925 l_eam_res_usage_rec.assigned_units :=l_wo_res_usage.assigned_units;
926 l_eam_res_usage_rec.request_id :=l_eam_wo_rec.request_id;
927 l_eam_res_usage_rec.program_application_id :=l_eam_wo_rec.program_application_id;
928 l_eam_res_usage_rec.program_id :=l_eam_wo_rec.program_id;
929 l_eam_res_usage_rec.instance_id :=l_wo_res_usage.instance_id;
930 l_eam_res_usage_rec.serial_number :=l_wo_res_usage.serial_number;
931 l_eam_res_usage_rec.return_status :=l_wo_res_usage.return_status;
932 l_eam_res_usage_rec.transaction_type :=l_wo_res_usage.transaction_type;
933
934 l_eam_res_usage_tbl(l_resource_usage_index) := l_eam_res_usage_rec;
935 l_resource_usage_index := l_resource_usage_index + 1;
936 END LOOP;
937
938
939 l_resource_instance_index :=1;
940 FOR l_wo_res_instance IN import_wo_res_instance_cur (l_group_id,l_eam_wo_rec.wip_entity_id)
941 LOOP
942 l_eam_res_inst_rec.header_id :=l_wo_res_instance.header_id;
943 l_eam_res_inst_rec.batch_id :=l_wo_res_instance.group_id;
944 l_eam_res_inst_rec.row_id :=l_wo_res_instance.row_id;
945 l_eam_res_inst_rec.wip_entity_id :=l_wo_res_instance.wip_entity_id;
946 l_eam_res_inst_rec.organization_id :=l_wo_res_instance.organization_id;
947 l_eam_res_inst_rec.operation_seq_num :=l_wo_res_instance.operation_seq_num;
948 l_eam_res_inst_rec.resource_seq_num :=l_wo_res_instance.resource_seq_num;
949 l_eam_res_inst_rec.instance_id :=l_wo_res_instance.instance_id;
950 l_eam_res_inst_rec.serial_number :=l_wo_res_instance.serial_number;
951 l_eam_res_inst_rec.start_date :=l_wo_res_instance.start_date;
952 l_eam_res_inst_rec.completion_date :=l_wo_res_instance.completion_date;
953 l_eam_res_inst_rec.top_level_batch_id :=l_wo_res_instance.top_level_batch_id;
954 l_eam_res_inst_rec.return_status :=l_wo_res_instance.return_status;
955 l_eam_res_inst_rec.transaction_type :=l_wo_res_instance.transaction_type;
956
957 l_eam_res_inst_tbl(l_resource_instance_index) := l_eam_res_inst_rec;
958 l_resource_instance_index := l_resource_instance_index + 1;
959 END LOOP;
960
961 EAM_PROCESS_WO_PUB.Process_WO
962 ( p_bo_identifier => 'EAM'
963 , p_init_msg_list => TRUE
964 , p_api_version_number => 1.0
965 , p_eam_wo_rec => l_eam_wo_rec
966 , p_eam_op_tbl => l_eam_op_tbl
967 , p_eam_op_network_tbl => l_eam_op_network_tbl
968 , p_eam_res_tbl => l_eam_res_tbl
969 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
970 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
971 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
972 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
973 , p_eam_direct_items_tbl => l_eam_di_tbl
974 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
975 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
976 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
977 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
978 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
979 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
980 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
981 , p_eam_request_tbl => l_eam_request_tbl
982 , x_eam_wo_rec => l_out_eam_wo_rec
983 , x_eam_op_tbl => l_out_eam_op_tbl
984 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
985 , x_eam_res_tbl => l_out_eam_res_tbl
986 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
987 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
988 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
989 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
990 , x_eam_direct_items_tbl => l_out_eam_di_tbl
991 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
992 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
993 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
994 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
995 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
996 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
997 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
998 , x_eam_request_tbl => l_out_eam_request_tbl
999 , p_commit => 'N'
1000 , x_return_status => l_return_status
1001 , x_msg_count => l_msg_count
1002 , p_debug => 'N'
1003 , p_debug_filename => 'EAMVDSIB.log'
1004 , p_debug_file_mode => 'W'
1005 , p_output_dir => l_output_dir
1006 );
1007
1008 IF(l_return_status <> 'S') THEN
1009
1010 -- set the error logic code
1011 ROLLBACK TO EAM_WORK_ORDER_START;
1012
1013 -- Call procedure for logging and setting the appropriate error messages
1014 log_error_messages(l_return_status,l_group_id,l_import_wo_record,l_out_eam_op_tbl,l_out_eam_res_tbl,l_out_eam_res_inst_tbl,l_out_eam_res_usage_tbl,l_out_eam_mat_req_tbl);
1015
1016 IF l_standalone = FALSE THEN
1017 l_relationship_errors := TRUE;
1018 END IF;
1019
1020 ELSE -- Work Order API Returned Suceess
1021
1022 IF (l_return_status = 'S' AND l_standalone = TRUE) THEN
1023 -- For stand alone workorders if work order API returns sucess then
1024 -- Purge all the data from import table and all associated child tables
1025
1026 fnd_file.put_line(FND_FILE.LOG,'Status Sucess for work order --' || l_import_wo_record.wip_entity_id);
1027
1028 DELETE
1029 FROM EAM_RESOURCE_INSTANCE_IMPORT
1030 WHERE group_id = l_group_id
1031 AND wip_entity_id = l_import_wo_record.wip_entity_id;
1032
1033 DELETE
1034 FROM EAM_RESOURCE_USAGE_IMPORT
1035 WHERE group_id = l_group_id
1036 AND wip_entity_id = l_import_wo_record.wip_entity_id;
1037
1038 DELETE
1039 FROM EAM_RESOURCE_IMPORT
1040 WHERE group_id = l_group_id
1041 AND wip_entity_id = l_import_wo_record.wip_entity_id;
1042
1043 DELETE
1044 FROM EAM_MATERIAL_IMPORT
1045 WHERE group_id = l_group_id
1046 AND wip_entity_id = l_import_wo_record.wip_entity_id;
1047
1048 DELETE
1049 FROM EAM_OPERATION_IMPORT
1050 WHERE group_id = l_group_id
1051 AND wip_entity_id = l_import_wo_record.wip_entity_id;
1052
1053 DELETE
1054 FROM EAM_WORK_ORDER_IMPORT
1055 WHERE group_id = l_group_id
1056 AND wip_entity_id = l_import_wo_record.wip_entity_id;
1057
1058
1059 COMMIT;
1060 END IF;
1061
1062 END IF; -- End if of error check
1063
1064 EXCEPTION WHEN OTHERS THEN
1065 l_error_message := 'UNEXPECTED ERROR IN OUTER BLOCK: ' || SQLERRM;
1066 fnd_file.put_line(FND_FILE.LOG,'Status Error.Unexpected error occured while processing work order ' || l_error_message);
1067 END;
1068
1069 END LOOP;
1070
1071 CLOSE l_import_wo;
1072 -- setting completion text for concurrent program
1073 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', l_error_message);
1074
1075 fnd_file.put_line(FND_FILE.LOG,'------------END OF WORK ORDER IMPORT CONCURRENT PROGRAM-----------');
1076
1077 END import_workorder;
1078 END EAM_WO_IMPORT_DS_PVT;