[Home] [Help]
PACKAGE BODY: APPS.EAM_COMPLETION
Source
1 PACKAGE BODY eam_completion AS
2 /* $Header: EAMWCMPB.pls 120.9 2011/11/08 13:26:42 vchidura ship $*/
3
4
5
6 FUNCTION IS_WORKFLOW_ENABLED
7 (p_maint_obj_source IN NUMBER,
8 p_organization_id IN NUMBER
9 ) RETURN VARCHAR2
10 IS
11 l_workflow_enabled VARCHAR2(1);
12 BEGIN
13
14 BEGIN
15 SELECT enable_workflow
16 INTO l_workflow_enabled
17 FROM EAM_ENABLE_WORKFLOW
18 WHERE MAINTENANCE_OBJECT_SOURCE =p_maint_obj_source;
19 EXCEPTION
20 WHEN NO_DATA_FOUND THEN
21 l_workflow_enabled := 'N';
22 END;
23
24 --IF EAM workorder,check if workflow is enabled for this organization or not
25 IF(l_workflow_enabled ='Y' AND p_maint_obj_source=1) THEN
26 BEGIN
27 SELECT eam_wo_workflow_enabled
28 INTO l_workflow_enabled
29 FROM WIP_EAM_PARAMETERS
30 WHERE organization_id =p_organization_id;
31 EXCEPTION
32 WHEN NO_DATA_FOUND THEN
33 l_workflow_enabled := 'N';
34 END;
35 END IF; --check for workflow enabled at org level
36
37 RETURN l_workflow_enabled;
38
39 END IS_WORKFLOW_ENABLED;
40
41
42
43 -- Bug 2803819-dgupta: This qa_enable API is now redundant and should be
44 -- removed in next cleanup.
45 PROCEDURE qa_enable(qa_id NUMBER,
46 errCode OUT NOCOPY NUMBER,
47 errMsg OUT NOCOPY VARCHAR2) IS
48
49 i_msg_data VARCHAR2(250);
50 i_return_status VARCHAR2(250);
51 i_msg_count NUMBER;
52
53 BEGIN
54 errCode := 0; --initial to success
55 QA_RESULT_GRP.ENABLE(p_api_version => 1.0,
56 p_init_msg_list => 'F',
57 p_commit => 'F',
58 p_validation_level => 0,
59 p_collection_id => qa_id,
60 p_return_status => i_return_status,
61 p_msg_count => i_msg_count,
62 p_msg_data => i_msg_data);
63 if (i_return_status <> 'S') then
64 if (i_msg_count = -1) then
65 fnd_message.set_name('QA','QA_ACTION_FAILED');
66 else
67 fnd_message.set_name('QA', i_msg_data);
68 end if; -- end message count check
69 errCode := 1; --fail
70 errMsg := fnd_message.get;
71 return;
72 end if; -- end error check
73
74 END qa_enable;
75
76 PROCEDURE process_lot_serial(
77 s_subinventory VARCHAR2,
78 s_locator_id VARCHAR2,
79 s_lot_serial_tbl Lot_Serial_Tbl_Type,
80 s_org_id NUMBER,
81 s_wip_entity_id NUMBER,
82 s_qa_collection_id NUMBER,
83 s_rebuild_item_id NUMBER,
84 s_acct_period_id NUMBER,
85 s_user_id NUMBER,
86 s_transaction_type NUMBER,
87 s_project_id NUMBER,
88 s_task_id NUMBER,
89 s_commit VARCHAR2,
90 errCode OUT NOCOPY NUMBER,
91 errMsg OUT NOCOPY VARCHAR2,
92 x_statement OUT NOCOPY NUMBER) IS
93
94 i_transaction_header_id NUMBER;
95 i_transaction_temp_id NUMBER;
96 i_serial_transaction_temp_id NUMBER;
97 i_transaction_temp_id_s NUMBER;
98 i_transaction_quantity NUMBER;
99 i_primary_quantity NUMBER;
100 i_transaction_action_id NUMBER;
101 i_transaction_type_id NUMBER;
102 i_transaction_source_type_id NUMBER;
103 i_project_id NUMBER;
104 i_task_id NUMBER;
105 i_revision VARCHAR2(3) := null;
106 item wma_common.Item;
107 l_statement NUMBER := 0;
108 l_revision_control_code NUMBER := 1;
109 l_transaction_quantity NUMBER;
110 l_initial_msg_count NUMBER := 0;
111
112 BEGIN
113
114 -- prepare the data to insert into MTL_MATERIAL_TRANSACTIONS_TEMP,
115 -- MTL_SERIAL_NUMBERS_TEMP, and MTL_TRANSACTION_LOTS_TEMP
116 select mtl_material_transactions_s.nextval into i_transaction_header_id
117 from dual;
118
119 l_statement := 1;
120 x_statement := l_statement;
121
122 -- get the item info
123 item := wma_derive.getItem(s_rebuild_item_id, s_org_id, s_locator_id);
124 if (item.invItemID is null) then
125 fnd_message.set_name ('EAM', 'EAM_ITEM_DOES_NOT_EXIST');
126 errCode := 1;
127 errMsg := fnd_message.get;
128 return;
129 end if; -- end item info check
130
131 l_statement := 2;
132 x_statement := l_statement;
133
134
135 begin
136 select nvl(revision_qty_control_code,1)
137 into l_revision_control_code
138 from mtl_system_items
139 where inventory_item_id = s_rebuild_item_id
140 and organization_id = s_org_id;
141
142 exception
143 when others then
144 null;
145 end;
146
147 if (l_revision_control_code = 2) then
148
149 -- get bom_revision
150 bom_revisions.get_revision (examine_type => 'ALL',
151 org_id => s_org_id,
152 item_id => s_rebuild_item_id,
153 rev_date => sysdate,
154 itm_rev => i_revision);
155 end if;
156
157 -- get transaction source type id
158 i_transaction_source_type_id := inv_reservation_global.g_source_type_wip;
159
160 l_statement := 3;
161 x_statement := l_statement;
162
163 -- set i_transaction_quantity and i_primary_quantity to be the sum of all
164 -- quantities in the lot_serial_tbl
165 -- also verify all quantities are greater than 0
166 i_transaction_quantity := 0;
167 FOR i IN 1..s_lot_serial_tbl.COUNT LOOP
168 IF s_lot_serial_tbl(i).quantity is null or s_lot_serial_tbl(i).quantity < 1 THEN
169 errCode := 1;
170 fnd_message.set_name('EAM', 'EAM_NEGATIVE_TXN_QUANTITY');
171 errMsg := fnd_message.get;
172 return;
173 ELSE
174 i_transaction_quantity := i_transaction_quantity + s_lot_serial_tbl(i).quantity;
175 END IF;
176 END LOOP;
177 i_primary_quantity := i_transaction_quantity;
178
179 -- prepare the rest data
180 if(s_transaction_type = 1) then -- Complete Transaction
181 i_transaction_action_id := WIP_CONSTANTS.CPLASSY_ACTION;
182 i_transaction_type_id := WIP_CONSTANTS.CPLASSY_TYPE;
183 elsif(s_transaction_type = 2) then -- Uncomplete Transaction
184 i_transaction_quantity := - i_transaction_quantity;
185 i_primary_quantity := - i_primary_quantity;
186 i_transaction_action_id := WIP_CONSTANTS.RETASSY_ACTION;
187 i_transaction_type_id := WIP_CONSTANTS.RETASSY_TYPE;
188 else
189 fnd_message.set_name('EAM','EAM_INVALID_TRANSACTION_TYPE');
190 errCode := 1;
191 errMsg := fnd_message.get;
192 return;
193 end if; -- end prepare data
194
195 l_statement := 4;
196 x_statement := l_statement;
197
198 -- call inventory API to insert data to mtl_material_transactions_temp
199 -- the spec file is INVTRXUS.pls
200
201 errCode := inv_trx_util_pub.insert_line_trx(
202 p_trx_hdr_id => i_transaction_header_id,
203 p_item_id => s_rebuild_item_id,
204 p_revision => i_revision,
205 p_org_id => s_org_id,
206 p_trx_action_id => i_transaction_action_id,
207 p_subinv_code => s_subinventory,
208 p_locator_id => s_locator_id,
209 p_trx_type_id => i_transaction_type_id,
210 p_trx_src_type_id => i_transaction_source_type_id,
211 p_trx_qty => i_transaction_quantity,
212 p_pri_qty => i_primary_quantity,
213 p_uom => item.primaryUOMCode,
214 p_date => sysdate,
215 p_user_id => s_user_id,
216 p_trx_src_id => s_wip_entity_id,
217 x_trx_tmp_id => i_transaction_temp_id,
218 x_proc_msg => errMsg);
219
220 l_statement := 5;
221 x_statement := l_statement;
222
223 if (errCode <> 0) then
224 return;
225 end if;
226
227 FOR i IN 1..s_lot_serial_tbl.COUNT LOOP
228
229 if(s_transaction_type = 1) then -- Complete Transaction
230 l_transaction_quantity := s_lot_serial_tbl(i).quantity;
231 else -- Uncomplete Transaction
232 l_transaction_quantity := - s_lot_serial_tbl(i).quantity;
233 end if;
234
235 -- Check whether the item is under lot or serial control or not
236 -- If it is, insert the data to coresponding tables
237 if(item.lotControlCode = WIP_CONSTANTS.LOT) then
238
239 -- the item is under lot control
240
241 -- call inventory API to insert data to mtl_transaction_lots_temp
242 -- the spec file is INVTRXUS.pls
243 errCode := inv_trx_util_pub.insert_lot_trx(
244 p_trx_tmp_id => i_transaction_temp_id,
245 p_user_id => s_user_id,
246 p_lot_number => s_lot_serial_tbl(i).lot_number,
247 p_trx_qty => l_transaction_quantity,
248 p_pri_qty => l_transaction_quantity,
249 x_ser_trx_id => i_serial_transaction_temp_id,
250 x_proc_msg => errMsg);
251
252 if (errCode <> 0) then
253 return;
254 end if;
255
256 else
257 null;
258 end if; -- end lot control check
259
260 -- Check if the item is under serial control or not
261 if(item.serialNumberControlCode in (WIP_CONSTANTS.FULL_SN,
262 WIP_CONSTANTS.DYN_RCV_SN)) then
263 -- item is under serial control
264
265 -- Check if the item is under lot control or not
266 if(item.lotControlCode = WIP_CONSTANTS.LOT) then
267
268 -- under lot control
269 i_transaction_temp_id_s := i_serial_transaction_temp_id;
270 else
271 i_transaction_temp_id_s := i_transaction_temp_id;
272 end if; -- end lot control check
273
274
275 -- call inventory API to insert data to mtl_serial_numbers_temp
276 -- the spec file is INVTRXUS.pls
277 errCode := inv_trx_util_pub.insert_ser_trx(
278 p_trx_tmp_id => i_transaction_temp_id_s,
279 p_user_id => s_user_id,
280 p_fm_ser_num => s_lot_serial_tbl(i).serial_number,
281 p_to_ser_num => s_lot_serial_tbl(i).serial_number,
282 x_proc_msg => errMsg);
283 if (errCode <> 0) then
284 return;
285 end if;
286
287 else
288 null;
289 end if; -- end serial control check
290
291 end LOOP;
292
293 l_statement := 6;
294 x_statement := l_statement;
295
296 l_initial_msg_count := FND_MSG_PUB.count_msg;
297 -- Call Inventory API to process to item
298 -- the spec file is INVTRXWS.pls
299 errCode := inv_lpn_trx_pub.process_lpn_trx(
300 p_trx_hdr_id => i_transaction_header_id,
301 p_commit => s_commit,
302 x_proc_msg => errMsg);
303 /* Added as a FIX for the Issue 1 of bug:2881879 */
304 if(FND_MSG_PUB.count_msg> 0) then
305 if(l_initial_msg_count = 0 and errCode = 0) then
306 FND_MSG_PUB.Delete_msg;
307 end if;
308 end if;
309 /* Added for bug no :2911698
310 Since the error message is not getting added into the message stack
311 */
312 if(errCode <> 0 and errMsg is not null) then
313 eam_execution_jsp.add_message(p_app_short_name => 'EAM',p_msg_name =>
314 'EAM_RET_MAT_PROCESS_MESSAGE',p_token1=> 'ERRMESSAGE',
315 p_value1 => errMsg);
316 end if;
317
318 l_statement := 7;
319 x_statement := l_statement;
320
321 END process_lot_serial;
322
323
324 PROCEDURE process_item(
325 s_inventory_item_tbl EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type,
326 s_org_id NUMBER,
327 s_wip_entity_id NUMBER,
328 s_qa_collection_id NUMBER,
329 s_rebuild_item_id NUMBER,
330 s_acct_period_id NUMBER,
331 s_user_id NUMBER,
332 s_transaction_type NUMBER,
333 s_project_id NUMBER,
334 s_task_id NUMBER,
335 s_commit VARCHAR2,
336 errCode OUT NOCOPY NUMBER,
337 errMsg OUT NOCOPY VARCHAR2,
338 x_statement OUT NOCOPY NUMBER) IS
339 l_subinventory VARCHAR2(30);
340 l_locator VARCHAR2(30);
341 l_lot_serial_rec Lot_Serial_Rec_Type;
342 l_lot_serial_tbl Lot_Serial_Tbl_Type;
343 BEGIN
344 IF s_inventory_item_tbl.COUNT = 0 THEN
345 RETURN;
346 END IF;
347
348 l_subinventory := s_inventory_item_tbl(1).subinventory;
349 l_locator := s_inventory_item_tbl(1).locator;
350 l_lot_serial_rec.lot_number := s_inventory_item_tbl(1).lot_number;
351 l_lot_serial_rec.serial_number := s_inventory_item_tbl(1).serial_number;
352 l_lot_serial_rec.quantity := s_inventory_item_tbl(1).quantity;
353 l_lot_serial_tbl(1) := l_lot_serial_rec;
354
355 FOR i in 2..(s_inventory_item_tbl.COUNT+1) LOOP
356 IF (i > s_inventory_item_tbl.COUNT
357 OR s_inventory_item_tbl(i).subinventory <> l_subinventory
358 OR s_inventory_item_tbl(i).locator <> l_locator) THEN
359
360 process_lot_serial(l_subinventory,
361 l_locator,
362 l_lot_serial_tbl,
363 s_org_id,
364 s_wip_entity_id,
365 s_qa_collection_id,
366 s_rebuild_item_id,
367 s_acct_period_id,
368 s_user_id,
369 s_transaction_type,
370 s_project_id,
371 s_task_id,
372 s_commit,
373 errCode,
374 errMsg,
375 x_statement);
376 if (errCode <> 0) then
377 return;
378 end if;
379 l_lot_serial_tbl.DELETE;
380 END IF;
381 IF (i <= s_inventory_item_tbl.COUNT) THEN
382 l_subinventory := s_inventory_item_tbl(i).subinventory;
383 l_locator := s_inventory_item_tbl(i).locator;
384 l_lot_serial_rec.lot_number := s_inventory_item_tbl(i).lot_number;
385 l_lot_serial_rec.serial_number := s_inventory_item_tbl(i).serial_number;
386 l_lot_serial_rec.quantity := s_inventory_item_tbl(i).quantity;
387 l_lot_serial_tbl(l_lot_serial_tbl.COUNT+1) := l_lot_serial_rec;
388 END IF;
389 END LOOP;
390
391 END process_item;
392
393 -- Added three arguments as part of bug 3448770 fix
394 PROCEDURE process_shutdown(s_asset_group_id NUMBER,
395 s_organization_id NUMBER,
396 s_asset_number VARCHAR2,
397 s_start_date DATE,
398 s_end_date DATE,
399 s_user_id NUMBER,
400 s_maintenance_object_type NUMBER,
401 s_maintenance_object_id NUMBER,
402 s_wip_entity_id NUMBER DEFAULT NULL ) IS
403
404 i_asset_status_id NUMBER;
405 BEGIN
406 -- get the asset_status_id from eam_asset_status_history_s sequence
407 SELECT eam_asset_status_history_s.nextval INTO i_asset_status_id FROM dual;
408
409 -- Enhancement Bug 3852846
410 UPDATE eam_asset_status_history
414 , last_update_login = FND_GLOBAL.login_id
411 SET enable_flag = 'N'
412 , last_update_date = SYSDATE
413 , last_updated_by = FND_GLOBAL.user_id
415 WHERE organization_id = s_organization_id
416 AND wip_entity_id = s_wip_entity_id
417 AND operation_seq_num IS NULL
418 AND (enable_flag is NULL OR enable_flag = 'Y');
419
420 INSERT INTO eam_asset_status_history(asset_status_id,
421 organization_id,
422 asset_group_id,
423 asset_number,
424 start_date,
425 end_date,
426 wip_entity_id, -- Fix for Bug 3448770
427 maintenance_object_type,
428 maintenance_object_id,
429 created_by,
430 creation_date,
431 last_updated_by,
432 last_update_date,
433 enable_flag) -- Enhancement Bug 3852846
434 VALUES (i_asset_status_id,
435 s_organization_id,
436 s_asset_group_id,
437 s_asset_number,
438 s_start_date,
439 s_end_date,
440 s_wip_entity_id, -- Fix for Bug 3448770
441 s_maintenance_object_type,
442 s_maintenance_object_id,
443 s_user_id,
444 sysdate,
445 s_user_id,
446 sysdate,
447 'Y'); -- Enhancement Bug 3852846
448
449 END process_shutdown;
450
451 PROCEDURE lock_row(
452 p_wip_entity_id IN NUMBER,
453 p_organization_id IN NUMBER,
454 p_rebuild_item_id IN NUMBER,
455 p_parent_wip_entity_id IN NUMBER,
456 p_asset_number IN VARCHAR2,
457 p_asset_group_id IN NUMBER,
458 p_manual_rebuild_flag IN VARCHAR2,
459 p_asset_activity_id IN NUMBER,
460 p_status_type IN NUMBER,
461 x_return_status OUT NOCOPY NUMBER,
462 x_msg_count OUT NOCOPY NUMBER,
463 x_msg_data OUT NOCOPY VARCHAR2) IS
464
465 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
466 l_api_version CONSTANT NUMBER := 1.0;
467 l_full_name CONSTANT VARCHAR2(60) := 'eam_completion' || '.' ||
468 l_api_name;
469
470 CURSOR C IS
471 SELECT wip_entity_id, organization_id, rebuild_item_id,
472 rebuild_serial_number, parent_wip_entity_id, asset_number,
473 asset_group_id, manual_rebuild_flag, primary_item_id, status_type,
474 completion_subinventory, completion_locator_id, lot_number,
475 project_id, task_id
476 FROM wip_discrete_jobs
477 WHERE wip_entity_id = P_WIP_ENTITY_ID
478 FOR UPDATE OF status_type NOWAIT;
479 Recinfo C%ROWTYPE;
480
481 BEGIN
482
483 -- Standard Start of API savepoint
484 SAVEPOINT apiname_apitype;
485
486 /*Standard call to check for call compatibility.
487 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
488 l_api_name, g_pkg_name) THEN
489 RAISE fnd_api.g_exc_unexpected_error;
490 END IF;
491
492 -- Initialize message list if p_init_msg_list is set to TRUE.
493 IF fnd_api.to_boolean(p_init_msg_list) THEN
494 fnd_msg_pub.initialize;
495 END IF;*/
496
497 -- Initialize API return status to success
498 x_return_status := 0;
499
500 -- API body
501
502 OPEN C;
503 FETCH C INTO Recinfo;
504 if (C%NOTFOUND) then
505 CLOSE C;
506 FND_MESSAGE.Set_Name('EAM', 'FORM_RECORD_DELETED');
507 APP_EXCEPTION.Raise_Exception;
508 end if;
509 CLOSE C;
510 if (
511 (p_wip_entity_id is null or
512 Recinfo.wip_entity_id = p_wip_entity_id)
513 AND (p_organization_id is null or
514 Recinfo.organization_id = p_organization_id)
515 AND (p_rebuild_item_id is null or
516 Recinfo.rebuild_item_id = p_rebuild_item_id)
517 AND (p_parent_wip_entity_id is null or
518 Recinfo.parent_wip_entity_id = p_parent_wip_entity_id)
519 AND (p_asset_number is null
520 or Recinfo.asset_number = p_asset_number)
521 AND (p_asset_group_id is null or
522 Recinfo.asset_group_id = p_asset_group_id)
523 AND (p_manual_rebuild_flag is null or
524 Recinfo.manual_rebuild_flag = p_manual_rebuild_flag)
525 AND (p_asset_activity_id is null or
526 Recinfo.primary_item_id = p_asset_activity_id)
527 AND (p_status_type is null or
528 Recinfo.status_type = p_status_type)
529 ) then
530 return;
531 else
532 FND_MESSAGE.Set_Name('EAM', 'FORM_RECORD_CHANGED');
533 APP_EXCEPTION.Raise_Exception;
534 end if;
535
536
537 -- Standard call to get message count and if count is 1, get message info.
538 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
539
540 EXCEPTION
541 WHEN fnd_api.g_exc_error THEN
542 ROLLBACK TO apiname_apitype;
543 x_return_status := 1;
544 fnd_msg_pub.count_and_get(p_count => x_msg_count,
545 p_data => x_msg_data);
549 x_return_status := 1;
546
547 WHEN fnd_api.g_exc_unexpected_error THEN
548 ROLLBACK TO apiname_apitype;
550 fnd_msg_pub.count_and_get(
551 p_count => x_msg_count
552 ,p_data => x_msg_data);
553
554 WHEN OTHERS THEN
555 ROLLBACK TO apiname_apitype;
556 x_return_status := 1;
557
558 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
559 fnd_msg_pub.add_exc_msg('eam_completion', l_api_name);
560 END IF;
561
562 fnd_msg_pub.count_and_get(p_count => x_msg_count,
563 p_data => x_msg_data);
564
565
566 END Lock_Row;
567
568 PROCEDURE complete_work_order_form(
569 x_wip_entity_id IN NUMBER,
570 x_rebuild_jobs IN VARCHAR2,
571 x_transaction_type IN NUMBER,
572 x_transaction_date IN DATE,
573 x_user_id IN NUMBER := fnd_global.user_id,
574 x_request_id IN NUMBER := null,
575 x_application_id IN NUMBER := null,
576 x_program_id IN NUMBER := null,
577 x_reconcil_code IN VARCHAR2 := null,
578 x_actual_start_date IN DATE,
579 x_actual_end_date IN DATE,
580 x_actual_duration IN NUMBER,
581 x_subinventory IN VARCHAR2 := null,
582 x_locator_id IN NUMBER := null,
583 x_lot_number IN VARCHAR2 := null,
584 x_serial_number IN VARCHAR2 := null,
585 x_reference IN VARCHAR2 := null,
586 x_qa_collection_id IN NUMBER := null,
587 x_shutdown_start_date IN DATE := null,
588 x_shutdown_end_date IN DATE := null,
589 x_attribute_category IN VARCHAR2 := null,
590 x_attribute1 IN VARCHAR2 := null,
591 x_attribute2 IN VARCHAR2 := null,
592 x_attribute3 IN VARCHAR2 := null,
593 x_attribute4 IN VARCHAR2 := null,
594 x_attribute5 IN VARCHAR2 := null,
595 x_attribute6 IN VARCHAR2 := null,
596 x_attribute7 IN VARCHAR2 := null,
597 x_attribute8 IN VARCHAR2 := null,
598 x_attribute9 IN VARCHAR2 := null,
599 x_attribute10 IN VARCHAR2 := null,
600 x_attribute11 IN VARCHAR2 := null,
601 x_attribute12 IN VARCHAR2 := null,
602 x_attribute13 IN VARCHAR2 := null,
603 x_attribute14 IN VARCHAR2 := null,
604 x_attribute15 IN VARCHAR2 := null,
605 errCode OUT NOCOPY NUMBER,
606 errMsg OUT NOCOPY VARCHAR2) IS
607 l_inventory_item_rec EAM_WorkOrderTransactions_PUB.Inventory_Item_Rec_Type;
608 l_inventory_item_tbl EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type;
609 BEGIN
610 l_inventory_item_rec.subinventory := x_subinventory;
611 l_inventory_item_rec.locator := x_locator_id;
612 l_inventory_item_rec.lot_number := x_lot_number;
613 l_inventory_item_rec.serial_number := x_serial_number;
614 l_inventory_item_rec.quantity := 1;
615 l_inventory_item_tbl(1) := l_inventory_item_rec;
616
617 complete_work_order_generic(x_wip_entity_id => x_wip_entity_id,
618 x_rebuild_jobs => x_rebuild_jobs,
619 x_transaction_type => x_transaction_type,
620 x_transaction_date => x_transaction_date,
621 x_user_id => x_user_id,
622 x_request_id => x_request_id,
623 x_application_id => x_application_id,
624 x_program_id => x_program_id,
625 x_reconcil_code => x_reconcil_code,
626 x_actual_start_date => x_actual_start_date,
627 x_actual_end_date => x_actual_end_date,
628 x_actual_duration => x_actual_duration,
629 x_inventory_item_info => l_inventory_item_tbl,
630 x_reference => x_reference,
631 x_qa_collection_id => x_qa_collection_id,
632 x_shutdown_start_date => x_shutdown_start_date,
633 x_shutdown_end_date => x_shutdown_end_date,
634 x_attribute_category => x_attribute_category,
635 x_attribute1 => x_attribute1,
636 x_attribute2 => x_attribute2,
637 x_attribute3 => x_attribute3,
638 x_attribute4 => x_attribute4,
639 x_attribute5 => x_attribute5,
640 x_attribute6 => x_attribute6,
641 x_attribute7 => x_attribute7,
642 x_attribute8 => x_attribute8,
643 x_attribute9 => x_attribute9,
644 x_attribute10 => x_attribute10,
645 x_attribute11 => x_attribute11,
646 x_attribute12 => x_attribute12,
647 x_attribute13 => x_attribute13,
648 x_attribute14 => x_attribute14,
649 x_attribute15 => x_attribute15,
650 errCode => errCode,
651 errMsg => errMsg);
652
653 END complete_work_order_form;
654
655 /* Added for bug# 3238163 */
656 PROCEDURE complete_work_order_commit(
657 x_wip_entity_id IN NUMBER,
658 x_rebuild_jobs IN VARCHAR2,
659 x_transaction_type IN NUMBER,
663 x_application_id IN NUMBER := null,
660 x_transaction_date IN DATE,
661 x_user_id IN NUMBER := fnd_global.user_id,
662 x_request_id IN NUMBER := null,
664 x_program_id IN NUMBER := null,
665 x_reconcil_code IN VARCHAR2 := null,
666 x_commit IN VARCHAR2 := fnd_api.g_false,
667 x_actual_start_date IN DATE,
668 x_actual_end_date IN DATE,
669 x_actual_duration IN NUMBER,
670 x_subinventory IN VARCHAR2 := null,
671 x_locator_id IN NUMBER := null,
672 x_lot_number IN VARCHAR2 := null,
673 x_serial_number IN VARCHAR2 := null,
674 x_reference IN VARCHAR2 := null,
675 x_qa_collection_id IN NUMBER := null,
676 x_shutdown_start_date IN DATE := null,
677 x_shutdown_end_date IN DATE := null,
678 x_attribute_category IN VARCHAR2 := null,
679 x_attribute1 IN VARCHAR2 := null,
680 x_attribute2 IN VARCHAR2 := null,
681 x_attribute3 IN VARCHAR2 := null,
682 x_attribute4 IN VARCHAR2 := null,
683 x_attribute5 IN VARCHAR2 := null,
684 x_attribute6 IN VARCHAR2 := null,
685 x_attribute7 IN VARCHAR2 := null,
686 x_attribute8 IN VARCHAR2 := null,
687 x_attribute9 IN VARCHAR2 := null,
688 x_attribute10 IN VARCHAR2 := null,
689 x_attribute11 IN VARCHAR2 := null,
690 x_attribute12 IN VARCHAR2 := null,
691 x_attribute13 IN VARCHAR2 := null,
692 x_attribute14 IN VARCHAR2 := null,
693 x_attribute15 IN VARCHAR2 := null,
694 errCode OUT NOCOPY NUMBER,
695 errMsg OUT NOCOPY VARCHAR2) IS
696 l_inventory_item_rec EAM_WorkOrderTransactions_PUB.Inventory_Item_Rec_Type;
697 l_inventory_item_tbl EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type;
698 BEGIN
699 l_inventory_item_rec.subinventory := x_subinventory;
700 l_inventory_item_rec.locator := x_locator_id;
701 l_inventory_item_rec.lot_number := x_lot_number;
702 l_inventory_item_rec.serial_number := x_serial_number;
703 l_inventory_item_rec.quantity := 1;
704 l_inventory_item_tbl(1) := l_inventory_item_rec;
705
706 complete_work_order_generic(x_wip_entity_id => x_wip_entity_id,
707 x_rebuild_jobs => x_rebuild_jobs,
708 x_transaction_type => x_transaction_type,
709 x_transaction_date => x_transaction_date,
710 x_user_id => x_user_id,
711 x_request_id => x_request_id,
712 x_application_id => x_application_id,
713 x_program_id => x_program_id,
714 x_reconcil_code => x_reconcil_code,
715 x_commit => x_commit,
716 x_actual_start_date => x_actual_start_date,
717 x_actual_end_date => x_actual_end_date,
718 x_actual_duration => x_actual_duration,
719 x_inventory_item_info => l_inventory_item_tbl,
720 x_reference => x_reference,
721 x_qa_collection_id => x_qa_collection_id,
722 x_shutdown_start_date => x_shutdown_start_date,
723 x_shutdown_end_date => x_shutdown_end_date,
724 x_attribute_category => x_attribute_category,
725 x_attribute1 => x_attribute1,
726 x_attribute2 => x_attribute2,
727 x_attribute3 => x_attribute3,
728 x_attribute4 => x_attribute4,
729 x_attribute5 => x_attribute5,
730 x_attribute6 => x_attribute6,
731 x_attribute7 => x_attribute7,
732 x_attribute8 => x_attribute8,
733 x_attribute9 => x_attribute9,
734 x_attribute10 => x_attribute10,
735 x_attribute11 => x_attribute11,
736 x_attribute12 => x_attribute12,
737 x_attribute13 => x_attribute13,
738 x_attribute14 => x_attribute14,
739 x_attribute15 => x_attribute15,
740 errCode => errCode,
741 errMsg => errMsg);
742
743 END complete_work_order_commit;
744
745 -- Procedure called via JSP
746
747 PROCEDURE complete_work_order(
748 x_wip_entity_id IN NUMBER,
749 x_rebuild_jobs IN VARCHAR2,
750 x_transaction_type IN NUMBER,
751 x_transaction_date IN DATE,
752 x_user_id IN NUMBER := fnd_global.user_id,
753 x_request_id IN NUMBER := null,
754 x_application_id IN NUMBER := null,
755 x_program_id IN NUMBER := null,
756 x_reconcil_code IN VARCHAR2 := null,
757 x_actual_start_date IN DATE,
758 x_actual_end_date IN DATE,
759 x_actual_duration IN NUMBER,
760 x_subinventory IN VARCHAR2 := null,
761 x_locator_id IN NUMBER := null,
762 x_lot_number IN VARCHAR2 := null,
763 x_serial_number IN VARCHAR2 := null,
764 x_reference IN VARCHAR2 := null,
765 x_qa_collection_id IN NUMBER := null,
766 x_shutdown_start_date IN DATE := null,
770 x_attribute1 IN VARCHAR2 := null,
767 x_shutdown_end_date IN DATE := null,
768 x_commit IN VARCHAR2 := fnd_api.g_false,
769 x_attribute_category IN VARCHAR2 := null,
771 x_attribute2 IN VARCHAR2 := null,
772 x_attribute3 IN VARCHAR2 := null,
773 x_attribute4 IN VARCHAR2 := null,
774 x_attribute5 IN VARCHAR2 := null,
775 x_attribute6 IN VARCHAR2 := null,
776 x_attribute7 IN VARCHAR2 := null,
777 x_attribute8 IN VARCHAR2 := null,
778 x_attribute9 IN VARCHAR2 := null,
779 x_attribute10 IN VARCHAR2 := null,
780 x_attribute11 IN VARCHAR2 := null,
781 x_attribute12 IN VARCHAR2 := null,
782 x_attribute13 IN VARCHAR2 := null,
783 x_attribute14 IN VARCHAR2 := null,
784 x_attribute15 IN VARCHAR2 := null,
785 errCode OUT NOCOPY NUMBER,
786 errMsg OUT NOCOPY VARCHAR2) IS
787
788
789 l_inventory_item_rec EAM_WorkOrderTransactions_PUB.Inventory_Item_Rec_Type;
790 l_inventory_item_tbl EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type;
791 BEGIN
792 l_inventory_item_rec.subinventory := x_subinventory;
793 l_inventory_item_rec.locator := x_locator_id;
794 l_inventory_item_rec.lot_number := x_lot_number;
795 l_inventory_item_rec.serial_number := x_serial_number;
796 l_inventory_item_rec.quantity := 1;
797 l_inventory_item_tbl(1) := l_inventory_item_rec;
798
799 complete_work_order_generic(x_wip_entity_id => x_wip_entity_id,
800 x_rebuild_jobs => x_rebuild_jobs,
801 x_transaction_type => x_transaction_type,
802 x_transaction_date => x_transaction_date,
803 x_user_id => x_user_id,
804 x_request_id => x_request_id,
805 x_application_id => x_application_id,
806 x_program_id => x_program_id,
807 x_reconcil_code => x_reconcil_code,
808 x_actual_start_date => x_actual_start_date,
809 x_actual_end_date => x_actual_end_date,
810 x_actual_duration => x_actual_duration,
811 x_inventory_item_info => l_inventory_item_tbl,
812 x_reference => x_reference,
813 x_qa_collection_id => x_qa_collection_id,
814 x_shutdown_start_date => x_shutdown_start_date,
815 x_shutdown_end_date => x_shutdown_end_date,
816 x_attribute_category => x_attribute_category,
817 x_attribute1 => x_attribute1,
818 x_attribute2 => x_attribute2,
819 x_attribute3 => x_attribute3,
820 x_attribute4 => x_attribute4,
821 x_attribute5 => x_attribute5,
822 x_attribute6 => x_attribute6,
823 x_attribute7 => x_attribute7,
824 x_attribute8 => x_attribute8,
825 x_attribute9 => x_attribute9,
826 x_attribute10 => x_attribute10,
827 x_attribute11 => x_attribute11,
828 x_attribute12 => x_attribute12,
829 x_attribute13 => x_attribute13,
830 x_attribute14 => x_attribute14,
831 x_attribute15 => x_attribute15,
832 errCode => errCode,
833 errMsg => errMsg);
834
835 END complete_work_order;
836
837
838
839 /***************************************************************************
840 *
841 * This procedure will be used to complete and uncomplete EAM work order
842 *
843 * PARAMETER:
844 *
845 * x_wip_entity_id Work Order ID
846 * x_rebuild_jobs A flag used to determine work order type
847 * (N:Regular EAM work order/ Y:Rebuild work order)
848 * x_transaction_type The type of transaction (Complete(1) / Uncomplete(2))
849 * x_transaction_date The date of transaction
850 * x_user_id User ID
851 * x_request_id, For concurrent processing
852 * x_appplication_id, For concurrent processing
853 * x_program_id For concurrent processing
854 * x_reconcil_code This parameter was predefined in FND_LOOKUP_VALUES
855 * where lookup_type = 'WIP_EAM_RECONCILIATION_CODE'
856 * x_subinventory For rebuild work order with material issue only
857 * x_locator_id For rebuild work order with material issue only
858 * x_lot_number For rebuild work order with material issue only
859 * x_serial_number For rebuild work order with material issue only
860 * x_reference For regular EAM work order only
861 * x_qa_collection_id For regular EAM work order only
862 * (null if the the work order is not under QA control)
863 * x_shutdown_start_date Shutdown information for regular EAM
864 * x_shutdown_end_date Shutdown information for regular EAM
865 * x_commit default to fnd_api.g_true
866 * whether to commit the changes to DB
867 * x_attribute_category For descriptive flex field
868 * x_attribute1-15 For descriptive flex field
869 * errCode OUT 0 if procedure success, 1 otherwise
870 * errMsg OUT NOCOPY The informative error message
871 *
872 ***************************************************************************/
876 x_rebuild_jobs IN VARCHAR2,
873
874 PROCEDURE complete_work_order_generic(
875 x_wip_entity_id IN NUMBER,
877 x_transaction_type IN NUMBER,
878 x_transaction_date IN DATE,
879 x_user_id IN NUMBER := fnd_global.user_id,
880 x_request_id IN NUMBER := null,
881 x_application_id IN NUMBER := null,
882 x_program_id IN NUMBER := null,
883 x_reconcil_code IN VARCHAR2 := null,
884 x_actual_start_date IN DATE,
885 x_actual_end_date IN DATE,
886 x_actual_duration IN NUMBER,
887 x_inventory_item_info IN EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type := INVENTORY_ITEM_NULL,
888 x_reference IN VARCHAR2 := null,
889 x_qa_collection_id IN NUMBER := null,
890 x_shutdown_start_date IN DATE := null,
891 x_shutdown_end_date IN DATE := null,
892 x_commit IN VARCHAR2 := fnd_api.g_false,
893 x_attribute_category IN VARCHAR2 := null,
894 x_attribute1 IN VARCHAR2 := null,
895 x_attribute2 IN VARCHAR2 := null,
896 x_attribute3 IN VARCHAR2 := null,
897 x_attribute4 IN VARCHAR2 := null,
898 x_attribute5 IN VARCHAR2 := null,
899 x_attribute6 IN VARCHAR2 := null,
900 x_attribute7 IN VARCHAR2 := null,
901 x_attribute8 IN VARCHAR2 := null,
902 x_attribute9 IN VARCHAR2 := null,
903 x_attribute10 IN VARCHAR2 := null,
904 x_attribute11 IN VARCHAR2 := null,
905 x_attribute12 IN VARCHAR2 := null,
906 x_attribute13 IN VARCHAR2 := null,
907 x_attribute14 IN VARCHAR2 := null,
908 x_attribute15 IN VARCHAR2 := null,
909 errCode OUT NOCOPY NUMBER,
910 errMsg OUT NOCOPY VARCHAR2) IS
911
912
913 i_asset_number VARCHAR2(30);
914 i_manual_rebuild_flag VARCHAR2(1);
915 i_acct_period_id NUMBER;
916 i_asset_group_id NUMBER;
917 i_asset_activity_id NUMBER;
918 i_org_id NUMBER;
919 i_parent_wip_entity_id NUMBER;
920 i_parent_status_type NUMBER; -- used for uncomplete transaction
921 i_project_id NUMBER;
922 i_rebuild_item_id NUMBER;
923 i_rebuild_serial_number VARCHAR2(30);
924 i_status_type NUMBER; -- status of work order
925 i_task_id NUMBER;
926 i_transaction_id NUMBER;
927 msg_count NUMBER;
928 i_work_request_status NUMBER;
929 i_completion_date DATE;
930 i_program_update_date DATE;
931 l_statement NUMBER := 0;
932 x_statement NUMBER := 0;
933 l_valid NUMBER := 0;
934 l_subinventory VARCHAR2(30) := null;
935 l_locator NUMBER := null;
936 l_lot_number VARCHAR2(80) := null;
937 i_maintenance_source_id NUMBER; -- added as part of bug #2774571 to check whether the work order is of 'EAM' or 'CMRO'
938
939 -- this boolean need to pass to invttmtx.tdatechk
940 i_open_past_period BOOLEAN;
941
942 /* --replaced with select statement for bug #2414513.
943 | -- Cursor to hold all child jobs information
944 | cursor child_jobs_cursor(c_wip_entity_id NUMBER) is
945 | select we.wip_entity_name, wdj.status_type
946 | from wip_discrete_jobs wdj, wip_entities we
947 | where wdj.wip_entity_id = we.wip_entity_id
948 | and wdj.parent_wip_entity_id = c_wip_entity_id
949 | and wdj.manual_rebuild_flag = 'Y';
950 |
951 | -- Aggregate variable to hold each child job information
952 | child_job child_jobs_cursor%ROWTYPE;
953 */
954 child_job_var VARCHAR2(2):='0';
955 network_child_job_var VARCHAR2(2):='0'; -- Bug no 3049128 added as part to check for Work Order Completion as part of Work Order Linking Project
956 network_parent_job_var VARCHAR2(2):='0'; -- Bug no 3049128 added as part to check for Work Order Completion as part of Work Order Linking Project
957 sibling_parent_job_var VARCHAR2(2):='0';
958
959 i_maintenance_object_type NUMBER; -- Fix for Bug 3448770
960 i_maintenance_object_id NUMBER; -- Fix for Bug 3448770
961 l_wip_entity_name VARCHAR2(240);
962 l_workflow_enabled VARCHAR2(1);
963 l_approval_required BOOLEAN;
964 l_workflow_name VARCHAR2(200);
965 l_workflow_process VARCHAR2(200);
966 l_status_pending_event VARCHAR2(240);
967 l_status_changed_event VARCHAR2(240);
968 l_new_eam_status NUMBER;
969 l_new_system_status NUMBER;
970 l_old_eam_status NUMBER;
971 l_old_system_status NUMBER;
972 l_workflow_type NUMBER;
973 l_event_name VARCHAR2(240);
974 l_parameter_list wf_parameter_list_t;
975 l_event_key VARCHAR2(200);
976 l_wf_event_seq NUMBER;
977 l_cost_estimate NUMBER;
978 network_child_name VARCHAR2(240); -- fix for 9572411
979
980
981 BEGIN
982
983 SAVEPOINT job_comp;
984 errCode := 0; -- initial to success
985 msg_count := 0;
986 i_open_past_period := FALSE;
987
988 l_statement := 15;
989 x_statement := l_statement;
990
991 -- Validate all required information
992 if(x_wip_entity_id = null or x_rebuild_jobs = null
993 or x_transaction_type = null or x_transaction_date = null
994 or x_actual_start_date = null or x_actual_end_date = null
995 or x_actual_duration = null) then
996 ROLLBACK TO job_comp;
997 fnd_message.set_name('EAM','EAM_WORK_ORDER_MISSING_INFO');
1001 return;
998 FND_MSG_PUB.Add;
999 errCode := 1;
1000 errMsg := fnd_message.get;
1002 end if; -- end validate data
1003
1004 l_statement := 25;
1005 x_statement := l_statement;
1006
1007 -- get transaction_id from sequence eam_job_completion_txns_s
1008 select eam_job_completion_txns_s.nextval into i_transaction_id from dual;
1009
1010 if(x_request_id is not null and x_application_id is not null and
1011 x_program_id is not null) then
1012 i_program_update_date := sysdate;
1013 else
1014 i_program_update_date := null;
1015 end if; -- end concurrent program check
1016
1017 l_statement := 35;
1018 x_statement := l_statement;
1019
1020 -- get some value from wip_discrete_jobs table
1021 select wdj.parent_wip_entity_id,
1022 wdj.asset_group_id,
1023 wdj.asset_number,
1024 wdj.primary_item_id,
1025 wdj.manual_rebuild_flag,
1026 wdj.rebuild_item_id,
1027 wdj.rebuild_serial_number,
1028 wdj.project_id,
1029 wdj.task_id,
1030 wdj.organization_id,
1031 wdj.maintenance_object_source, -- added as part of bug #2774571 to check whether the work order is of 'EAM' or 'CMRO'
1032 wdj.maintenance_object_type, -- Fix for Bug 3448770
1033 wdj.maintenance_object_id,
1034 wdj.status_type,
1035 ewod.user_defined_status_id,
1036 ewod.workflow_type,
1037 we.wip_entity_name
1038 into i_parent_wip_entity_id,
1039 i_asset_group_id,
1040 i_asset_number,
1041 i_asset_activity_id,
1042 i_manual_rebuild_flag,
1043 i_rebuild_item_id,
1044 i_rebuild_serial_number,
1045 i_project_id,
1046 i_task_id,
1047 i_org_id,
1048 i_maintenance_source_id, -- added as part of bug #2774571 to check whether the work order is of 'EAM' or 'CMRO'
1049 i_maintenance_object_type, -- Fix for Bug 3448770
1050 i_maintenance_object_id,
1051 l_old_system_status,
1052 l_old_eam_status,
1053 l_workflow_type,
1054 l_wip_entity_name
1055 from wip_discrete_jobs wdj,eam_work_order_details ewod,wip_entities we
1056 where wdj.wip_entity_id = x_wip_entity_id
1057 AND wdj.wip_entity_id = ewod.wip_entity_id(+)
1058 AND wdj.wip_entity_id = we.wip_entity_id;
1059
1060 l_workflow_enabled:=Is_Workflow_enabled(i_maintenance_source_id,i_org_id);
1061 l_status_changed_event := 'oracle.apps.eam.workorder.status.changed';
1062 l_status_pending_event := 'oracle.apps.eam.workorder.status.change.pending';
1063
1064 l_statement := 45;
1065 x_statement := l_statement;
1066
1067 --fix for bug # 2446276 ----------------------------------------
1068 if(x_transaction_type = 2) then
1069 if(x_rebuild_jobs <> 'Y') then
1070 l_valid := EAM_WORKORDER_UTIL_PKG.check_released_onhold_allowed(
1071 'N',
1072 i_org_id,
1073 i_asset_group_id,
1074 i_asset_number,
1075 i_asset_activity_id);
1076 else
1077 l_valid := EAM_WORKORDER_UTIL_PKG.check_released_onhold_allowed(
1078 'Y',
1079 i_org_id,
1080 i_rebuild_item_id,
1081 i_rebuild_serial_number,
1082 i_asset_activity_id);
1083 end if;
1084 if l_valid = 1 then
1085 FND_MESSAGE.SET_NAME('EAM', 'EAM_WO_NO_UNCOMPLETE');
1086 FND_MSG_PUB.Add;
1087 errCode := 2;
1088 errMsg := fnd_message.get;
1089 return;
1090 end if;
1091 end if; /* end if for fix 2446276 */
1092
1093 -- get acct_period_id from routine invttmtx.tdatechk
1094 -- this routine will return the value via acct_period_id OUT variable
1095 -- open_past_period is an IN OUT boolean
1096 invttmtx.tdatechk(i_org_id,x_transaction_date,i_acct_period_id,
1097 i_open_past_period);
1098
1099 -- Check whether the transaction type is Complete or Uncomplete
1100 If (x_transaction_type = 1) then -- Complete Transaction
1101 -- Bug no 3049128 added as part to check for Work Order Completion as part of Work Order Linking Project
1102
1103 begin
1104 SELECT '1'
1105 INTO network_child_job_var
1106 FROM dual
1107 WHERE EXISTS (SELECT '1'
1108 FROM wip_discrete_jobs
1109 WHERE wip_entity_id IN
1110 (
1111 SELECT DISTINCT child_object_id
1112 FROM eam_wo_relationships
1113 WHERE parent_relationship_type =1
1114 START WITH parent_object_id = x_wip_entity_id AND parent_relationship_type = 1
1115 CONNECT BY parent_object_id = prior child_object_id AND parent_relationship_type = 1
1116 )
1117 AND status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
1118 WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED,WIP_CONSTANTS.CANCELLED, WIP_CONSTANTS.PEND_CLOSE)
1119
1120 );
1121
1122 if (network_child_job_var = '1') then --In the network Work Order has Uncompleted Child Work Orders
1123 -- fix for 9572411 begin
1124 SELECT wip_entity_name
1125 INTO network_child_name
1126 FROM wip_entities
1127 where wip_entity_id= (SELECT wip_entity_id
1128 FROM wip_discrete_jobs
1129 WHERE wip_entity_id IN
1130 ( SELECT DISTINCT child_object_id
1131 FROM eam_wo_relationships
1132 WHERE parent_relationship_type =1
1133 START WITH parent_object_id = x_wip_entity_id AND parent_relationship_type = 1
1134 CONNECT BY parent_object_id = prior child_object_id AND parent_relationship_type = 1)
1135 AND status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
1139 fnd_message.set_name('EAM','EAM_NETWRK_CHILD_JOB_NOT_COMP');
1136 WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED,WIP_CONSTANTS.CANCELLED, WIP_CONSTANTS.PEND_CLOSE)
1137 AND ROWNUM =1
1138 );
1140 fnd_message.set_token('WIP_ENTITY_NAME',network_child_name);
1141 FND_MSG_PUB.Add;
1142 errCode := 1;
1143 errMsg :=fnd_message.get;
1144 ROLLBACK TO job_comp;
1145 return;
1146 -- fix for 9572411 end
1147 else
1148 null;
1149 end if;
1150 exception
1151 WHEN OTHERS THEN
1152 null;
1153 end;
1154 -- end of Bug fix 3049128
1155
1156 -- Bug no 3735589
1157 begin
1158 SELECT '1'
1159 INTO sibling_parent_job_var
1160 FROM dual
1161 WHERE EXISTS (SELECT '1'
1162 FROM wip_discrete_jobs
1163 WHERE wip_entity_id IN
1164 (
1165 SELECT DISTINCT parent_object_id
1166 FROM eam_wo_relationships
1167 WHERE parent_relationship_type =2 and
1168 child_object_id = x_wip_entity_id
1169 )
1170 AND status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
1171 WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED)
1172
1173 );
1174
1175 if (sibling_parent_job_var = '1') then
1176 -- fix for 9572411 begin
1177 SELECT wip_entity_name
1178 INTO network_child_name
1179 FROM wip_entities
1180 where wip_entity_id= (SELECT wip_entity_id
1181 FROM wip_discrete_jobs
1182 WHERE wip_entity_id IN
1183 ( SELECT DISTINCT parent_object_id
1184 FROM eam_wo_relationships
1185 WHERE parent_relationship_type =2 and
1186 child_object_id = x_wip_entity_id
1187 )
1188 AND status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
1189 WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED)
1190 AND ROWNUM =1
1191 );
1192 ROLLBACK TO job_comp;
1193 fnd_message.set_name('EAM','EAM_NETWRK_SIB_JOB_NOT_COM');
1194 fnd_message.set_token('WIP_ENTITY_NAME',network_child_name);
1195 FND_MSG_PUB.Add;
1196 errCode := 1;
1197 errMsg :=fnd_message.get;
1198 --errMsg := 'EAM_NETWRK_SIB_JOB_NOT_COM';
1199 return;
1200 else
1201 null;
1202 end if;
1203 exception
1204 WHEN OTHERS THEN
1205 null;
1206 end;
1207 -- end of bug fix 3735589
1208
1209
1210
1211 IF(l_workflow_enabled='Y' AND x_transaction_type=2
1212 AND (WF_EVENT.TEST(l_status_pending_event) <> 'NONE') )THEN
1213 EAM_WORKFLOW_DETAILS_PUB.Eam_Wf_Is_Approval_Required(p_old_wo_rec => NULL,
1214 p_new_wo_rec => NULL,
1215 p_wip_entity_id => x_wip_entity_id,
1216 p_new_system_status => 3,
1217 p_new_wo_status => 3,
1218 p_old_system_status => l_old_system_status,
1219 p_old_wo_status => l_old_eam_status,
1220 x_approval_required => l_approval_required,
1221 x_workflow_name => l_workflow_name,
1222 x_workflow_process => l_workflow_process
1223 );
1224
1225 IF(l_approval_required) THEN
1226 UPDATE EAM_WORK_ORDER_DETAILS
1227 SET user_defined_status_id=3,
1228 pending_flag='Y',
1229 last_update_date=SYSDATE,
1230 last_update_login=FND_GLOBAL.login_id,
1231 last_updated_by=FND_GLOBAL.user_id
1232 WHERE wip_entity_id= x_wip_entity_id;
1233
1234
1235
1236 --Find the total estimated cost of workorder
1237 BEGIN
1238 SELECT NVL((SUM(system_estimated_mat_cost) + SUM(system_estimated_lab_cost) + SUM(system_estimated_eqp_cost)),0)
1239 INTO l_cost_estimate
1240 FROM WIP_EAM_PERIOD_BALANCES
1241 WHERE wip_entity_id =x_wip_entity_id;
1242 EXCEPTION
1243 WHEN NO_DATA_FOUND THEN
1244 l_cost_estimate := 0;
1245 END;
1246
1247
1248 SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
1249 INTO l_wf_event_seq
1250 FROM DUAL;
1251
1252 l_parameter_list := wf_parameter_list_t();
1253 l_event_name := l_status_pending_event;
1254
1255 l_event_key := TO_CHAR(l_wf_event_seq);
1256 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Order Released change event','Building parameter list');
1257
1258
1259 INSERT INTO EAM_WO_WORKFLOWS
1260 (WIP_ENTITY_ID,WF_ITEM_TYPE,WF_ITEM_KEY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
1261 CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)
1262 VALUES
1263 (x_wip_entity_id,l_workflow_name,l_event_key,SYSDATE,FND_GLOBAL.user_id,
1264 SYSDATE,FND_GLOBAL.user_id,FND_GLOBAL.login_id
1265 );
1266
1267
1268 -- Add Parameters
1269 Wf_Event.AddParameterToList(p_name =>'WIP_ENTITY_ID',
1270 p_value => TO_CHAR(x_wip_entity_id),
1271 p_parameterlist => l_parameter_list);
1272 Wf_Event.AddParameterToList(p_name =>'WIP_ENTITY_NAME',
1276 p_value => TO_CHAR(i_org_id),
1273 p_value =>l_wip_entity_name,
1274 p_parameterlist => l_parameter_list);
1275 Wf_Event.AddParameterToList(p_name =>'ORGANIZATION_ID',
1277 p_parameterlist => l_parameter_list);
1278 Wf_Event.AddParameterToList(p_name =>'NEW_WO_STATUS',
1279 p_value =>'3' ,
1280 p_parameterlist => l_parameter_list);
1281 Wf_Event.AddParameterToList(p_name =>'OLD_SYSTEM_STATUS',
1282 p_value => TO_CHAR(l_old_system_status),
1283 p_parameterlist => l_parameter_list);
1284 Wf_Event.AddParameterToList(p_name =>'OLD_WO_STATUS',
1285 p_value => TO_CHAR(l_old_eam_status),
1286 p_parameterlist => l_parameter_list);
1287 Wf_Event.AddParameterToList(p_name =>'NEW_SYSTEM_STATUS',
1288 p_value => '3',
1289 p_parameterlist => l_parameter_list);
1290 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_TYPE',
1291 p_value => TO_CHAR(l_workflow_type),
1292 p_parameterlist => l_parameter_list);
1293 Wf_Event.AddParameterToList(p_name =>'REQUESTOR',
1294 p_value =>FND_GLOBAL.USER_NAME ,
1295 p_parameterlist => l_parameter_list);
1296 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_NAME',
1297 p_value => l_workflow_name,
1298 p_parameterlist => l_parameter_list);
1299 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_PROCESS',
1300 p_value => l_workflow_process,
1301 p_parameterlist => l_parameter_list);
1302 Wf_Event.AddParameterToList(p_name =>'ESTIMATED_COST',
1303 p_value => TO_CHAR(l_cost_estimate),
1304 p_parameterlist => l_parameter_list);
1305 Wf_Core.Context('Enterprise Asset Management...','Work Order Released Event','Raising event');
1306
1307 Wf_Event.Raise( p_event_name => l_event_name,
1308 p_event_key => l_event_key,
1309 p_parameters => l_parameter_list);
1310 l_parameter_list.DELETE;
1311 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Order Released Event','After raising event');
1312
1313
1314
1315 IF(i_maintenance_source_id =1) THEN --update text index for EAM workorders
1316 EAM_TEXT_UTIL.PROCESS_WO_EVENT
1317 (
1318 p_event => 'UPDATE',
1319 p_wip_entity_id =>x_wip_entity_id,
1320 p_organization_id =>i_org_id,
1321 p_last_update_date => SYSDATE,
1322 p_last_updated_by => FND_GLOBAL.user_id,
1323 p_last_update_login =>FND_GLOBAL.login_id
1324 );
1325 END IF;
1326
1327 RETURN;
1328 END IF;
1329 END IF; -- end of check for workflow enabled
1330
1331
1332
1333
1334 -- Check the type of Work Order
1335 if (x_rebuild_jobs = 'Y') then -- Rebuild Work Order
1336
1337 -- Check whether there is material issue or not
1338 /* Bug 3637201 - manual rebuild wo on out of stores serials should also be completed to subinv */
1339
1340 /* Subinventory check is added for bug no :2911698 */
1341 if ((i_manual_rebuild_flag = 'N' and x_inventory_item_info(1).subinventory is not null)
1342 or (I_MANUAL_REBUILD_FLAG <> 'N' and i_parent_wip_entity_id is null and
1343 x_inventory_item_info(1).subinventory is not null)) then -- there is material issue
1344
1345 l_statement := 55;
1346 x_statement := l_statement;
1347
1348 -- return item back to inventory
1349 process_item(s_inventory_item_tbl => x_inventory_item_info,
1350 s_org_id => i_org_id,
1351 s_wip_entity_id => x_wip_entity_id,
1352 s_qa_collection_id => x_qa_collection_id,
1353 s_rebuild_item_id => i_rebuild_item_id,
1354 s_acct_period_id => i_acct_period_id,
1355 s_user_id => x_user_id,
1356 s_transaction_type => x_transaction_type,
1357 s_project_id => i_project_id,
1358 s_task_id => i_task_id,
1359 s_commit => x_commit,
1360 errCode => errCode,
1361 errMsg => errMsg,
1362 x_statement => l_statement);
1363
1364 l_statement := 65;
1365 x_statement := l_statement;
1366
1367 -- Check if there is an error to return item or not
1368 if(errCode <> 0) then
1369 ROLLBACK TO job_comp;
1370 return;
1371 end if; -- end errCode check
1372
1373 end if; -- end material issue check
1374
1375 elsif (x_rebuild_jobs = 'N') then -- Regular EAM Work Order
1376
1377
1378 -- Replaced the above cursor loop and cursor with the following query.
1379 -- for bug #2414513.
1380 begin
1381 SELECT '1'
1382 INTO child_job_var
1383 FROM dual
1384 WHERE EXISTS (SELECT '1'
1385 FROM wip_discrete_jobs wdj, wip_entities we
1386 WHERE wdj.wip_entity_id = we.wip_entity_id
1387 AND wdj.parent_wip_entity_id = x_wip_entity_id
1388 AND wdj.manual_rebuild_flag = 'Y'
1389 AND wdj.status_type NOT IN (WIP_CONSTANTS.COMP_CHRG,
1390 WIP_CONSTANTS.COMP_NOCHRG,WIP_CONSTANTS.CLOSED,
1391 WIP_CONSTANTS.CANCELLED));
1392 if (child_job_var = '1') then
1393 ROLLBACK TO job_comp;
1397 errMsg := fnd_message.get;
1394 fnd_message.set_name('EAM','EAM_CHILD_JOB_NOT_COMPLETE');
1395 FND_MSG_PUB.Add;
1396 errCode := 1;
1398 return;
1399 else
1400 null;
1401 end if;
1402 exception
1403 WHEN OTHERS THEN
1404 null;
1405 end;
1406
1407 else
1408 ROLLBACK TO job_comp;
1409 fnd_message.set_name('EAM','EAM_INVALID_WORK_ORDER_TYPE');
1410 FND_MSG_PUB.Add;
1411 errCode := 1;
1412 errMsg := fnd_message.get;
1413 return;
1414 end if; -- end work order check
1415
1416 /* Bug # 5165813 : Allow shutdown info for rebuild wo also.
1417 Hence moved out of IF block mentioned above */
1418
1419 -- Check whether the user provide Shutdown Information or not
1420 -- If the user provide shutdown information, insert the data to
1421 -- eam_asset_status_history table for history purpose
1422 if(x_shutdown_start_date is not null and
1423 x_shutdown_end_date is not null) then
1424 process_shutdown(s_asset_group_id => i_asset_group_id,
1425 s_organization_id => i_org_id,
1426 s_asset_number => i_asset_number,
1427 s_start_date => x_shutdown_start_date,
1428 s_end_date => x_shutdown_end_date,
1429 s_user_id => x_user_id,
1430 s_maintenance_object_type => i_maintenance_object_type, -- Fix for Bug 3448770
1431 s_maintenance_object_id => i_maintenance_object_id,
1432 s_wip_entity_id => x_wip_entity_id);
1433
1434 end if; -- end shutdown check
1435
1436
1437
1438
1439 -- initial the rest important variable
1440 i_status_type := WIP_CONSTANTS.COMP_CHRG;
1441 i_completion_date := x_actual_end_date;
1442 i_work_request_status := 6; -- Work request complete
1443
1444 elsif (x_transaction_type = 2) then -- Uncomplete Transaction
1445 -- Check the type of Work Order
1446 if (x_rebuild_jobs = 'Y') then -- Rebuild Work Order
1447
1448 -- Check whether there is material issue or not
1449 /* Bug 3637201 */
1450 /* Subinventory check is added for bug no :2911698 */
1451 if ((i_manual_rebuild_flag = 'N' and x_inventory_item_info(1).subinventory is not null) or
1452 (I_MANUAL_REBUILD_FLAG <> 'N' and i_parent_wip_entity_id is null and
1453 x_inventory_item_info(1).subinventory is not null)) then -- there is material issue
1454 l_statement := 50;
1455 -- get item back from inventory
1456 process_item(s_inventory_item_tbl => x_inventory_item_info,
1457 s_org_id => i_org_id,
1458 s_wip_entity_id => x_wip_entity_id,
1459 s_qa_collection_id => x_qa_collection_id,
1460 s_rebuild_item_id => i_rebuild_item_id,
1461 s_acct_period_id => i_acct_period_id,
1462 s_user_id => x_user_id,
1463 s_transaction_type => x_transaction_type,
1464 s_project_id => i_project_id,
1465 s_task_id => i_task_id,
1466 s_commit => x_commit,
1467 errCode => errCode,
1468 errMsg => errMsg,
1469 x_statement => l_statement);
1470
1471 -- Check if there is an error to return item or not
1472 if(errCode <> 0) then
1473 ROLLBACK TO job_comp;
1474 return;
1475 end if; --end errCode check
1476 else
1477 -- get parent work order status
1478 begin -- Handled the exception for bug#2762312
1479 select status_type into i_parent_status_type
1480 from wip_discrete_jobs
1481 where wip_entity_id = i_parent_wip_entity_id;
1482 exception
1483 when NO_DATA_FOUND then
1484 null;
1485 end;
1486
1487 -- Check whether parent job already completed or not
1488 if(i_parent_status_type = WIP_CONSTANTS.COMP_CHRG) then
1489 ROLLBACK TO job_comp;
1490 fnd_message.set_name('EAM','EAM_PARENT_JOB_COMPLETED');
1491 FND_MSG_PUB.Add;
1492 errCode := 1;
1493 errMsg := fnd_message.get;
1494 return;
1495 else
1496 null;
1497 end if; --end parent job check
1498 end if;
1499
1500 elsif (x_rebuild_jobs = 'N') then -- Regular EAM Work Order
1501 null;
1502
1503 else
1504 ROLLBACK TO job_comp;
1505 fnd_message.set_name('EAM','EAM_INVALID_WORK_ORDER_TYPE');
1506 FND_MSG_PUB.Add;
1507 errCode := 1;
1508 errMsg := fnd_message.get;
1509 return;
1510 end if; --end work order check
1511
1512 /* Bug # 5165813 : Allow shutdown info for rebuild wo also.
1513 Hence moved out of IF block mentioned above */
1514 UPDATE eam_asset_status_history
1515 SET enable_flag = 'N'
1516 , last_update_date = SYSDATE
1517 , last_updated_by = FND_GLOBAL.user_id
1518 , last_update_login = FND_GLOBAL.login_id
1519 WHERE organization_id = i_org_id
1520 AND wip_entity_id = x_wip_entity_id
1521 AND operation_seq_num IS NULL
1522 AND (enable_flag = 'Y' OR enable_flag IS null);
1523
1524
1525 -- Update Meter ... Placed here as part of bug #2774571
1526 /*
1527 Last service info needs to be uncompleted for both the work orders.
1528 */
1529 if(i_maintenance_source_id = 1) then -- added to check whether work order is of 'EAM' or 'CRMO'.'EAM=1'
1530 eam_pm_utils.update_pm_when_uncomplete(i_org_id, x_wip_entity_id);
1534 i_status_type := WIP_CONSTANTS.RELEASED;
1531 end if; -- end of source entity check
1532
1533 -- initial the rest important variable
1535 i_completion_date := null;
1536 i_work_request_status := 4; -- Work request in process
1537
1538 else -- Other Transactions
1539 ROLLBACK TO job_comp;
1540 fnd_message.set_name('EAM','EAM_INVALID_TRANSACTION_TYPE');
1541 FND_MSG_PUB.Add;
1542 errCode := 1;
1543 errMsg := fnd_message.get;
1544 return;
1545 end if; -- end transaction type check
1546
1547 l_statement := 75;
1548 x_statement := l_statement;
1549
1550 if (x_inventory_item_info.COUNT = 1) then
1551 l_subinventory := x_inventory_item_info(1).subinventory;
1552 l_locator := x_inventory_item_info(1).locator;
1553 l_lot_number := x_inventory_item_info(1).lot_number;
1554 end if;
1555
1556 -- insert all information to eam_job_completion_txns table for tracking
1557 -- history
1558 -- check for the type of work order to insert the values appropriately
1559 -- The check is added as part of bug #2774571
1560 if(x_rebuild_jobs = 'N') then -- for normal work order
1561 insert into eam_job_completion_txns (transaction_id,
1562 transaction_date,
1563 transaction_type,
1564 wip_entity_id,
1565 organization_id,
1566 parent_wip_entity_id,
1567 reference,
1568 reconciliation_code,
1569 acct_period_id,
1570 qa_collection_id,
1571 asset_group_id,
1572 asset_number,
1573 asset_activity_id,
1574 actual_start_date,
1575 actual_end_date,
1576 actual_duration,
1577 created_by,
1578 creation_date,
1579 last_updated_by,
1580 last_update_date,
1581 last_update_login,
1582 request_id,
1583 program_application_id,
1584 program_id,
1585 program_update_date,
1586 completion_subinventory,
1587 completion_locator_id,
1588 lot_number,
1589 attribute_category,
1590 attribute1,
1591 attribute2,
1592 attribute3,
1593 attribute4,
1594 attribute5,
1595 attribute6,
1596 attribute7,
1597 attribute8,
1598 attribute9,
1599 attribute10,
1600 attribute11,
1601 attribute12,
1602 attribute13,
1603 attribute14,
1604 attribute15
1605 )
1606 values (i_transaction_id,
1607 x_transaction_date,
1608 x_transaction_type,
1609 x_wip_entity_id,
1610 i_org_id,
1611 i_parent_wip_entity_id,
1612 x_reference,
1613 x_reconcil_code,
1614 i_acct_period_id,
1615 x_qa_collection_id,
1616 i_asset_group_id,
1617 i_asset_number,
1618 i_asset_activity_id,
1619 x_actual_start_date,
1620 x_actual_end_date,
1621 x_actual_duration,
1622 x_user_id,
1623 sysdate,
1624 x_user_id,
1625 sysdate,
1626 x_user_id,
1627 x_request_id,
1628 x_application_id,
1629 x_program_id,
1630 i_program_update_date,
1631 l_subinventory,
1632 l_locator,
1633 l_lot_number,
1634 x_attribute_category,
1635 x_attribute1,
1636 x_attribute2,
1637 x_attribute3,
1638 x_attribute4,
1639 x_attribute5,
1640 x_attribute6,
1641 x_attribute7,
1642 x_attribute8,
1643 x_attribute9,
1647 x_attribute13,
1644 x_attribute10,
1645 x_attribute11,
1646 x_attribute12,
1648 x_attribute14,
1649 x_attribute15
1650 );
1651 else -- rebuild work orders.
1652 insert into eam_job_completion_txns (transaction_id,
1653 transaction_date,
1654 transaction_type,
1655 wip_entity_id,
1656 organization_id,
1657 parent_wip_entity_id,
1658 reference,
1659 reconciliation_code,
1660 acct_period_id,
1661 qa_collection_id,
1662 asset_group_id,
1663 asset_number,
1664 asset_activity_id,
1665 actual_start_date,
1666 actual_end_date,
1667 actual_duration,
1668 created_by,
1669 creation_date,
1670 last_updated_by,
1671 last_update_date,
1672 last_update_login,
1673 request_id,
1674 program_application_id,
1675 program_id,
1676 program_update_date,
1677 completion_subinventory,
1678 completion_locator_id,
1679 lot_number,
1680 attribute_category,
1681 attribute1,
1682 attribute2,
1683 attribute3,
1684 attribute4,
1685 attribute5,
1686 attribute6,
1687 attribute7,
1688 attribute8,
1689 attribute9,
1690 attribute10,
1691 attribute11,
1692 attribute12,
1693 attribute13,
1694 attribute14,
1695 attribute15
1696 )
1697 values (i_transaction_id,
1698 x_transaction_date,
1699 x_transaction_type,
1700 x_wip_entity_id,
1701 i_org_id,
1702 i_parent_wip_entity_id,
1703 x_reference,
1704 x_reconcil_code,
1705 i_acct_period_id,
1706 x_qa_collection_id,
1707 i_rebuild_item_id, -- changed from asset_group_id to rebuild_item_id
1708 i_rebuild_serial_number, -- changed from asset_serial_number to rebuild_serial_number
1709 i_asset_activity_id,
1710 x_actual_start_date,
1711 x_actual_end_date,
1712 x_actual_duration,
1713 x_user_id,
1714 sysdate,
1715 x_user_id,
1716 sysdate,
1717 x_user_id,
1718 x_request_id,
1719 x_application_id,
1720 x_program_id,
1721 i_program_update_date,
1722 l_subinventory,
1723 l_locator,
1724 l_lot_number,
1725 x_attribute_category,
1726 x_attribute1,
1727 x_attribute2,
1728 x_attribute3,
1729 x_attribute4,
1730 x_attribute5,
1731 x_attribute6,
1732 x_attribute7,
1733 x_attribute8,
1734 x_attribute9,
1735 x_attribute10,
1736 x_attribute11,
1737 x_attribute12,
1738 x_attribute13,
1739 x_attribute14,
1740 x_attribute15
1741 );
1742 end if; -- end insert check
1743 l_statement := 85;
1744 x_statement := l_statement;
1745
1746 -- Update wip_discrete_jobs table
1747 update wip_discrete_jobs
1748 set last_update_date = sysdate,
1749 last_updated_by = x_user_id,
1753 request_id = x_request_id,
1750 last_update_login = x_user_id,
1751 status_type = i_status_type,
1752 date_completed = i_completion_date,
1754 program_application_id = x_application_id,
1755 program_id = x_program_id,
1756 program_update_date = i_program_update_date
1757 where wip_entity_id = x_wip_entity_id and
1758 organization_id = i_org_id;
1759
1760
1761 --Update Eam_Work_Order_Details with user_defined_status_id
1762 UPDATE EAM_WORK_ORDER_DETAILS
1763 SET last_update_date = sysdate,
1764 last_updated_by = x_user_id,
1765 last_update_login = x_user_id,
1766 user_defined_status_id=i_status_type
1767 WHERE wip_entity_id = x_wip_entity_id;
1768
1769 l_statement := 95;
1770 x_statement := l_statement;
1771
1772 -- Update wip_eam_work_requests table
1773 update wip_eam_work_requests
1774 set work_request_status_id = i_work_request_status,
1775 last_update_date = sysdate,
1776 last_updated_by = x_user_id,
1777 last_update_login = fnd_global.login_id
1778 where wip_entity_id = x_wip_entity_id;
1779
1780 l_statement := 105;
1781 x_statement := l_statement;
1782
1783
1784 --Raise status changed event when a workorder is completed/uncompleted
1785
1786 IF(l_workflow_enabled='Y' AND (WF_EVENT.TEST(l_status_changed_event) <> 'NONE') --if status change event enabled
1787 ) THEN
1788
1789 SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
1790 INTO l_wf_event_seq
1791 FROM DUAL;
1792
1793 l_parameter_list := wf_parameter_list_t();
1794 l_event_name := l_status_changed_event;
1795
1796 l_event_key := TO_CHAR(l_wf_event_seq);
1797 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Order Status change event','Building parameter list');
1798 -- Add Parameters
1799 Wf_Event.AddParameterToList(p_name =>'WIP_ENTITY_ID',
1800 p_value => TO_CHAR(x_wip_entity_id),
1801 p_parameterlist => l_parameter_list);
1802 Wf_Event.AddParameterToList(p_name =>'WIP_ENTITY_NAME',
1803 p_value =>l_wip_entity_name,
1804 p_parameterlist => l_parameter_list);
1805 Wf_Event.AddParameterToList(p_name =>'ORGANIZATION_ID',
1806 p_value => TO_CHAR(i_org_id),
1807 p_parameterlist => l_parameter_list);
1808 Wf_Event.AddParameterToList(p_name =>'NEW_SYSTEM_STATUS',
1809 p_value => TO_CHAR(i_status_type),
1810 p_parameterlist => l_parameter_list);
1811 Wf_Event.AddParameterToList(p_name =>'NEW_WO_STATUS',
1812 p_value => TO_CHAR(i_status_type),
1813 p_parameterlist => l_parameter_list);
1814 Wf_Event.AddParameterToList(p_name =>'OLD_SYSTEM_STATUS',
1815 p_value => TO_CHAR(l_old_system_status),
1816 p_parameterlist => l_parameter_list);
1817 Wf_Event.AddParameterToList(p_name =>'OLD_WO_STATUS',
1818 p_value => TO_CHAR(l_old_eam_status),
1819 p_parameterlist => l_parameter_list);
1820 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_TYPE',
1821 p_value => TO_CHAR(l_workflow_type),
1822 p_parameterlist => l_parameter_list);
1823 Wf_Event.AddParameterToList(p_name =>'REQUESTOR',
1824 p_value =>FND_GLOBAL.USER_NAME ,
1825 p_parameterlist => l_parameter_list);
1826 Wf_Core.Context('Enterprise Asset Management...','Work Order Staus Changed Event','Raising event');
1827
1828 Wf_Event.Raise( p_event_name => l_event_name,
1829 p_event_key => l_event_key,
1830 p_parameters => l_parameter_list);
1831 l_parameter_list.DELETE;
1832 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Order Status Changed Event','After raising event');
1833 END IF; --end of check for status change event
1834
1835
1836 IF(i_maintenance_source_id =1) THEN --update text index for EAM workorders
1837
1838 EAM_TEXT_UTIL.PROCESS_WO_EVENT
1839 (
1840 p_event => 'UPDATE',
1841 p_wip_entity_id =>x_wip_entity_id,
1842 p_organization_id =>i_org_id,
1843 p_last_update_date => SYSDATE,
1844 p_last_updated_by => FND_GLOBAL.user_id,
1845 p_last_update_login =>FND_GLOBAL.login_id
1846 );
1847
1848 END IF; --end of check for EAM workorders
1849
1850
1851 if (x_commit = fnd_api.g_true) then
1852 COMMIT; -- commit all changes
1853 end if;
1854
1855 END complete_work_order_generic;
1856
1857 END eam_completion;