[Home] [Help]
PACKAGE BODY: APPS.EAM_PM_UTILS
Source
4 /**
1 PACKAGE BODY eam_pm_utils AS
2 /* $Header: EAMPMUTB.pls 120.8.12020000.2 2012/11/28 10:01:53 srkotika ship $ */
3
5 * This is a private procedure to be called to move one particular pm
6 * suggestion to the wip mass load interface table.
7 */
8 procedure move_pm_to_wdj(p_group_id in number,
9 p_pm_id in number,
10 p_assoc_id in number);
11
12 /**
13 * this is a private procedure to be called to update all the pms
14 * under the root of the given pm.
15 */
16 procedure update_all_pm_uncomplete(p_wip_entity_id in number, p_activity_association_id in number);
17
18
19 /**
20 * This is a private function.
21 * This function returns false if no pm related data should be updated. It returns true
22 * otherwise. When it returns true, it populates the return parameters with the right
23 * information.
24 */
25 function check_applicable_pm(p_org_id in number,
26 p_wip_entity_id in number,
27 p_pm_schedule_id out NOCOPY number,
28 p_resched_point out NOCOPY number) return boolean;
29
30
31
32 /**
33 * This procedure should be called when completing the work order. It will update
34 * the related PM rule data if applicable.
35 **** This procedure is now obsolete and is no longer called,
36 **** thus the body is null.
37 */
38 procedure update_pm_when_complete(p_org_id in number,
39 p_wip_entity_id in number,
40 p_completion_date in date) is
41 x_pm_id number := null;
42 x_resched_point number;
43 x_applicable boolean;
44 x_new_base_date date;
45 begin
46 null;
47 end update_pm_when_complete;
48
49 /**
50 * This procedure should be called when uncompleting the work order. It will update
51 * the related PM rule data if applicable.
52 */
53
54 procedure update_pm_when_uncomplete(p_org_id in number,
55 p_wip_entity_id in number)
56 is
57 l_is_last_wo boolean;
58 l_activity_association_id number;
59 l_service_reading number;
60 l_meter_reading_id number;
61 l_last_service_end_date DATE;
62
63 /* Bug # 5154927 : Corrected the query for both correctness and performance */
64 CURSOR METER_READING_IDS(P_WIP_ENTITY_ID NUMBER) IS
65 select ccr.counter_value_id meter_reading_id
66 from csi_counter_readings ccr, csi_transactions cct,
67 csi_counter_associations cca, wip_discrete_jobs wdj
68 where ccr.transaction_id = cct.transaction_id
69 and cct.transaction_type_id = 92
70 and cct.source_header_ref_id = wdj.wip_entity_id
71 and wdj.wip_entity_id = p_wip_entity_id
72 and wdj.maintenance_object_type = 3
73 and wdj.maintenance_object_id = cca.source_object_id
74 and cca.source_object_code = 'CP'
75 and cca.counter_id = ccr.counter_id;
76
77 L_MSG_DATA VARCHAR2(10000);
78 L_MSG_COUNT NUMBER;
79 L_RETURN_STATUS VARCHAR2(1);
80
81 begin
82
83 l_is_last_wo := check_is_last_wo(p_org_id, p_wip_entity_id,l_last_service_end_date);
84 if (l_is_last_wo=false) then
85 return;
86 end if;
87
88 -- Code added to take care of BUG # 4653925 , 5154927
89 OPEN meter_reading_ids(p_wip_entity_id);
90 LOOP
91 fetch meter_reading_ids into l_meter_reading_id;
92 Exit when meter_reading_ids%NOTFOUND;
93 EAM_MeterReading_PUB.disable_meter_reading(
94 p_api_version => 1.0,
95 p_init_msg_list => null,
96 p_commit => FND_API.G_FALSE,
97 x_return_status => l_return_status,
98 x_msg_count => l_msg_count,
99 x_msg_data => l_msg_data,
103
100 p_meter_reading_id => l_meter_reading_id,
101 p_meter_id => null,
102 p_meter_reading_date => null);
104 END LOOP;
105
106 -- reverse the last service reading in eam_pm_last_service table.
107 update eam_pm_last_service
108 set
109 last_service_reading=prev_service_reading,
110 wip_entity_id=null,
111 last_update_date=sysdate,
112 last_updated_by=fnd_global.user_id
113 where
114 wip_entity_id=p_wip_entity_id
115 and prev_service_reading is not null;
116
117
118 -- Get the activity_association_id for this work order
119
120 select meaa.activity_association_id into l_activity_association_id
121 from wip_discrete_jobs wdj, mtl_eam_asset_activities meaa
122 where
123 wdj.wip_entity_id=p_wip_entity_id and
124 wdj.maintenance_object_id=meaa.maintenance_object_id and
125 wdj.maintenance_object_type=meaa.maintenance_object_type and
126 wdj.primary_item_id=meaa.asset_activity_id;
127
128 -- do not(after bug15917235) recursively reverse the last service date for all activities
129 -- in the suppression tree with l_activity_association_id as
130 -- root.
131 update_all_pm_uncomplete(p_wip_entity_id, l_activity_association_id);
132 exception
133 when no_data_found then
134 return;
135 end;
136
137 /*
138 * Following function, given an organization id and wip_entity_number,
139 * determines whether this is the latest completed work order for
140 * its asset/activity association.
141 */
142 function check_is_last_wo(p_org_id number,
143 p_wip_entity_id number,
144 p_last_service_end_date OUT NOCOPY DATE )
145 return boolean
146 is
147 l_last_actual_end_date date;
148 l_last_wip_entity_id number;
149 l_transaction_type number;
150 begin
151 -- Find the latest date of any wo transaction for this asset / act assoc.
152
153 select max(ejct.actual_end_date)
154 into l_last_actual_end_date
155 from
156 eam_job_completion_txns ejct
157 where
158 transaction_type=1
159 and transaction_id in
160 (select max(ejct.transaction_id) from
161 eam_job_completion_txns ejct,
162 wip_discrete_jobs wdj1,
163 wip_discrete_jobs wdj2
164 where wdj1.wip_entity_id=p_wip_entity_id
165 and wdj1.maintenance_object_type = wdj2.maintenance_object_type
166 and wdj1.maintenance_object_id = wdj2.maintenance_object_id
167 and nvl(wdj1.primary_item_id, -99) = nvl(wdj2.primary_item_id, -99)
168 and wdj1.organization_id = wdj2.organization_id
172 --Retruning actual end date to get the counter reading id.
169 and ejct.wip_entity_id = wdj2.wip_entity_id
170 group by ejct.wip_entity_id);
171
176 -- for this asset / act assoc and see if it matches
173 p_last_service_end_date := l_last_actual_end_date;
174
175 -- Find the wo transaction on this l_last_actual_end_date
177 -- p_wip_entity_id and if it is a "completion" transaction.
178
179 select wip_entity_id, transaction_type
180 into l_last_wip_entity_id, l_transaction_type
181 from eam_job_completion_txns
182 where actual_end_date=l_last_actual_end_date
183 and transaction_id=
184 (select max(transaction_id)
185 from eam_job_completion_txns
186 where wip_entity_id=p_wip_entity_id
187 and transaction_type = 1);
188
189 if (p_wip_entity_id <> l_last_wip_entity_id)
190 then
191 return false;
192 else
193 return true;
194 end if;
195
196 exception
197 when no_data_found then
198 return false;
199 end;
200
201
202 procedure update_all_pm_uncomplete(p_wip_entity_id in number, p_activity_association_id in number) is
203 cursor C is
204 select sup.child_association_id
205 from eam_suppression_relations sup
206 where
207 sup.parent_association_id = p_activity_association_id;
208
209 l_prev_service_start_date date;
210 l_prev_service_end_date date;
211 l_prev_scheduled_start_date date;
212 l_prev_scheduled_end_date date;
213 l_prev_pm_suggested_start_date date;
214 l_prev_pm_suggested_end_date date;
215
216 x_child_aa number;
217 begin
218
219
220 -- First, update the last service date in meaa table
221 select prev_service_start_date, prev_service_end_date,
222 prev_scheduled_start_date, prev_scheduled_end_date,
223 prev_pm_suggested_start_date, prev_pm_suggested_end_date
224 into l_prev_service_start_date, l_prev_service_end_date,
225 l_prev_scheduled_start_date, l_prev_scheduled_end_date,
226 l_prev_pm_suggested_start_date,l_prev_pm_suggested_end_date
227 from mtl_eam_asset_activities
228 where activity_association_id=p_activity_association_id;
229
230 if (l_prev_service_start_date is not null and l_prev_service_end_date is not null and
231 l_prev_scheduled_start_date is not null and l_prev_scheduled_end_date is not null) then
232 update mtl_eam_asset_activities
233 set last_service_start_date = l_prev_service_start_date,
234 last_service_end_date = l_prev_service_end_date,
235 last_scheduled_start_date = l_prev_scheduled_start_date,
236 last_scheduled_end_date = l_prev_scheduled_end_date,
237 last_pm_suggested_start_date = l_prev_pm_suggested_start_date,
238 last_pm_suggested_end_date = l_prev_pm_suggested_end_date
239 where activity_association_id=p_activity_association_id;
240
241 update mtl_eam_asset_activities
242 set prev_service_start_date = null,
243 prev_service_end_date = null,
244 prev_scheduled_start_date = null,
245 prev_scheduled_end_date = null,
246 prev_pm_suggested_start_date = null,
247 prev_pm_suggested_end_date = null,
248 wip_entity_id = p_wip_entity_id
249 where activity_association_id=p_activity_association_id;
250
251 end if;
252 /* commented for bug15917235 - suppressed activity association's last service date should not get reversed as after bug12919405 we do not
253 update LSD recursively on WO complete (bug12919405 checked with PM)
254 open C;
255 LOOP
256 fetch C into x_child_aa;
257 EXIT WHEN ( C%NOTFOUND );
258 update_all_pm_uncomplete(p_wip_entity_id, x_child_aa);
259 END LOOP;
260 close C;*/
261
262 end update_all_pm_uncomplete;
263
264
265 /**
266 * This function returns false if no pm related data should be updated. It returns true
267 * otherwise. When it returns true, it populates the return parameters with the right
268 * information.
269 */
270 function check_applicable_pm(p_org_id in number,
271 p_wip_entity_id in number,
272 p_pm_schedule_id out NOCOPY number,
273 p_resched_point out NOCOPY number) return boolean
274 is
275 cursor C(p_asset_group_id number,
276 p_asset_number varchar2,
277 p_asset_activity_id number) is
278 select pms.pm_schedule_id
279 from eam_pm_schedulings pms,
280 mtl_eam_asset_activities eaa
281 where pms.activity_association_id = eaa.activity_association_id
282 and eaa.organization_id = p_org_id
283 and eaa.inventory_item_id = p_asset_group_id
284 and eaa.serial_number = p_asset_number
285 and eaa.asset_activity_id = p_asset_activity_id
286 and nvl(eaa.start_date_active, sysdate-1) < sysdate
287 and nvl(eaa.end_date_active, sysdate+1) > sysdate
288 and nvl(pms.from_effective_date, sysdate-1) < sysdate
289 and nvl(pms.to_effective_date, sysdate+1) > sysdate;
290
291 x_pm_id number := null;
292 x_asset_group_id number := null;
293 x_asset_number varchar2(30) := null;
294 x_asset_activity_id number := null;
295 x_rebuild_id number := null;
296 x_generated_by_pm boolean := false;
297 x_resched_point number;
298 begin
299 select pm_schedule_id,
300 asset_group_id,
301 asset_number,
302 primary_item_id,
303 rebuild_item_id
304 into x_pm_id,
305 x_asset_group_id,
306 x_asset_number,
307 x_asset_activity_id,
308 x_rebuild_id
309 from wip_discrete_jobs
310 where wip_entity_id = p_wip_entity_id;
311
312 -- do nothing for rebuildable work order
313 if ( x_rebuild_id is not null ) then
314 return false;
318 if ( x_pm_id is null ) then
315 end if;
316
317 -- find the corresponding valid pm schedule id if there is one
319 open C(x_asset_group_id, x_asset_number, x_asset_activity_id);
320 fetch C into x_pm_id;
321 if ( C%NOTFOUND ) then
322 close C;
323 return false;
324 end if;
325 close C;
326 else
327 x_generated_by_pm := true;
328 end if;
329
330 select rescheduling_point into x_resched_point
331 from eam_pm_schedulings
332 where pm_schedule_id = x_pm_id;
333
334 -- if the rescheduling point is start date, then we ignore the manually created
335 -- work orders.
336 if ( x_resched_point = 1 AND x_generated_by_pm = false ) then
337 return false;
338 end if;
339
340 p_pm_schedule_id := x_pm_id;
341 p_resched_point := x_resched_point;
342 return true;
343 end check_applicable_pm;
344
345
346 /**
347 * This procedure is called to move the forecasted work order suggestions from
348 * forecast table to wip_job_schedule_interface to be uploaded. It removes the
349 * records from forecast table then.
350 */
351 procedure transfer_to_wdj(p_group_id number) is
352 cursor pms is
353 select distinct pm_schedule_id, activity_association_id
354 from eam_forecasted_work_orders
355 where group_id = p_group_id
356 and process_flag = 'Y';
357
358 l_pm_id number;
359 l_assoc_id number;
360 begin
361 open pms;
362 fetch pms into l_pm_id, l_assoc_id;
363 if ( pms%NOTFOUND ) then
364 close pms;
365 return;
366 end if;
367
368 LOOP
372 END LOOP;
369 move_pm_to_wdj(p_group_id, l_pm_id, l_assoc_id);
370 fetch pms into l_pm_id, l_assoc_id;
371 EXIT WHEN ( pms%NOTFOUND );
373 close pms;
374
375 -- delete the records that has been moved to the mass load interface table
376 delete from eam_forecasted_work_orders
377 where group_id = p_group_id
378 and process_flag = 'Y';
379 end transfer_to_wdj;
380
381
382 procedure move_pm_to_wdj(p_group_id in number,
383 p_pm_id in number,
384 p_assoc_id in number) is
385 cursor pmwo is
386 select scheduled_start_date,
387 scheduled_completion_date,
388 wip_entity_id,
389 action_type,
390 created_by,
391 creation_date,
392 last_update_login,
393 last_update_date,
394 last_updated_by,
395 attribute_category,
396 attribute1,
397 attribute2,
398 attribute3,
399 attribute4,
400 attribute5,
401 attribute6,
402 attribute7,
403 attribute8,
404 attribute9,
405 attribute10,
406 attribute11,
407 attribute12,
408 attribute13,
409 attribute14,
410 attribute15
411 from eam_forecasted_work_orders
412 where pm_schedule_id = p_pm_id
413 and group_id = p_group_id
414 and process_flag = 'Y';
415
416 recinfo pmwo%ROWTYPE;
417 l_org_id number;
418 l_inventory_item_id number;
419 l_serial_number varchar2(30);
420 l_asset_activity_id number;
421 l_owning_department number;
422 l_description varchar2(240);
423 -- l_routing_reference_id number;
424 -- l_bom_reference_id number;
425 l_priority_code varchar2(30);
426 l_activity_cause varchar2(30);
427 l_activity_type varchar2(30);
428 l_activity_source varchar2(30);
429 l_tagging_required_flag varchar2(1);
430 l_shutdown_type_code varchar2(30);
431 l_class_code varchar2(10);
432 l_eam_item_type number;
433 l_maintenance_object_id number;
434 begin
435 select organization_id,
436 inventory_item_id,
437 serial_number,
438 asset_activity_id,
439 owning_department_id,
440 activity_cause_code,
441 activity_type_code,
442 activity_source_code,
443 tagging_required_flag,
444 shutdown_type_code,
445 priority_code
446 into l_org_id,
447 l_inventory_item_id,
448 l_serial_number,
449 l_asset_activity_id,
450 l_owning_department,
451 l_activity_cause,
452 l_activity_type,
453 l_activity_source,
454 l_tagging_required_flag,
455 l_shutdown_type_code,
456 l_priority_code
457 from mtl_eam_asset_activities
458 where activity_association_id = p_assoc_id;
459
460 if (l_serial_number is not null) then
461 select wip_accounting_class_code, gen_object_id
462 into l_class_code, l_maintenance_object_id
463 from mtl_serial_numbers
464 where current_organization_id = l_org_id
465 and inventory_item_id = l_inventory_item_id
466 and serial_number = l_serial_number;
467 end if;
468
469 select eam_item_type
470 into l_eam_item_type
471 from mtl_system_items
472 where organization_id = l_org_id
473 and inventory_item_id = l_inventory_item_id;
474
475 select description
476 into l_description
477 /* from mtl_system_items_b Commented for bug#4878157 */
478 from mtl_system_items_vl /* Added for bug#4878157 */
479 where inventory_item_id = l_asset_activity_id
480 and organization_id = l_org_id;
481
482 open pmwo;
483 fetch pmwo into recinfo;
484 if ( pmwo%NOTFOUND ) then
485 close pmwo;
486 return;
487 end if;
488
489 LOOP
490 if ( recinfo.action_type = 1 ) then
491 -- Create
492 if ( l_eam_item_type = 1 ) then
493 -- for Asset Group and number
494 insert into wip_job_schedule_interface(
495 group_id,
496 last_update_date,
497 last_updated_by,
498 creation_date,
499 created_by,
500 last_update_login,
501 organization_id,
502 load_type,
503 first_unit_start_date,
504 last_unit_completion_date,
505 asset_group_id,
506 description,
507 routing_reference_id,
508 bom_reference_id,
509 asset_number,
510 primary_item_id,
511 pm_schedule_id,
512 process_phase,
513 process_status,
514 owning_department,
515 activity_type,
516 activity_cause,
517 activity_source,
518 tagout_required,
519 shutdown_type,
520 priority,
521 plan_maintenance,
522 class_code,
523 attribute_category,
524 attribute1,
525 attribute2,
529 attribute6,
526 attribute3,
527 attribute4,
528 attribute5,
530 attribute7,
531 attribute8,
532 attribute9,
533 attribute10,
534 attribute11,
535 attribute12,
536 attribute13,
537 attribute14,
538 attribute15,
539 maintenance_object_id,
540 maintenance_object_type,
541 maintenance_object_source
542 )values(
543 p_group_id,
544 sysdate,
545 recinfo.last_updated_by,
546 sysdate,
547 recinfo.created_by,
548 recinfo.last_update_login,
549 l_org_id,
550 7,
551 recinfo.scheduled_start_date,
552 recinfo.scheduled_completion_date,
553 l_inventory_item_id,
554 l_description,
555 l_asset_activity_id,
556 l_asset_activity_id,
557 l_serial_number,
558 l_asset_activity_id,
559 p_pm_id,
560 2,
561 1,
562 l_owning_department,
563 l_activity_type,
564 l_activity_cause,
565 l_activity_source,
566 l_tagging_required_flag,
567 l_shutdown_type_code,
568 to_number(l_priority_code),
569 'Y',
570 l_class_code,
571 recinfo.attribute_category,
572 recinfo.attribute1,
573 recinfo.attribute2,
574 recinfo.attribute3,
575 recinfo.attribute4,
576 recinfo.attribute5,
577 recinfo.attribute6,
578 recinfo.attribute7,
579 recinfo.attribute8,
580 recinfo.attribute9,
581 recinfo.attribute10,
582 recinfo.attribute11,
583 recinfo.attribute12,
584 recinfo.attribute13,
585 recinfo.attribute14,
586 recinfo.attribute15,
587 l_maintenance_object_id,
588 1,
589 1
590 );
591 elsif (l_eam_item_type = 3) then
592 -- for rebuildables
593 insert into wip_job_schedule_interface(
594 group_id,
595 last_update_date,
596 last_updated_by,
597 creation_date,
598 created_by,
599 last_update_login,
600 organization_id,
601 load_type,
602 first_unit_start_date,
603 last_unit_completion_date,
604 rebuild_item_id,
605 description,
606 routing_reference_id,
607 bom_reference_id,
608 rebuild_serial_number,
609 primary_item_id,
610 pm_schedule_id,
611 process_phase,
612 process_status,
613 owning_department,
614 activity_type,
615 activity_cause,
616 activity_source,
617 tagout_required,
618 shutdown_type,
619 priority,
620 plan_maintenance,
621 class_code,
622 attribute_category,
623 attribute1,
624 attribute2,
625 attribute3,
626 attribute4,
627 attribute5,
628 attribute6,
629 attribute7,
630 attribute8,
631 attribute9,
632 attribute10,
633 attribute11,
634 attribute12,
635 attribute13,
636 attribute14,
637 attribute15,
638 manual_rebuild_flag,
639 maintenance_object_id,
640 maintenance_object_type,
641 maintenance_object_source
642 )values(
643 p_group_id,
644 sysdate,
645 recinfo.last_updated_by,
646 sysdate,
647 recinfo.created_by,
648 recinfo.last_update_login,
649 l_org_id,
650 7,
651 recinfo.scheduled_start_date,
652 recinfo.scheduled_completion_date,
653 l_inventory_item_id,
654 l_description,
655 l_asset_activity_id,
656 l_asset_activity_id,
657 l_serial_number,
658 l_asset_activity_id,
659 p_pm_id,
660 2,
661 1,
662 l_owning_department,
663 l_activity_type,
664 l_activity_cause,
665 l_activity_source,
666 l_tagging_required_flag,
667 l_shutdown_type_code,
668 to_number(l_priority_code),
669 'Y',
670 l_class_code,
671 recinfo.attribute_category,
672 recinfo.attribute1,
673 recinfo.attribute2,
674 recinfo.attribute3,
675 recinfo.attribute4,
676 recinfo.attribute5,
677 recinfo.attribute6,
678 recinfo.attribute7,
679 recinfo.attribute8,
680 recinfo.attribute9,
681 recinfo.attribute10,
682 recinfo.attribute11,
683 recinfo.attribute12,
684 recinfo.attribute13,
685 recinfo.attribute14,
686 recinfo.attribute15,
687 'Y',
688 nvl(l_maintenance_object_id, l_inventory_item_id),
689 decode(l_maintenance_object_id, null, 2, 1),
690 1
691 );
692 end if;
693
697 group_id,
694 elsif ( recinfo.action_type = 2 ) then
695 -- Reschdule
696 insert into wip_job_schedule_interface(
698 last_update_date,
699 last_updated_by,
700 creation_date,
701 created_by,
702 last_update_login,
703 organization_id,
704 load_type,
705 wip_entity_id,
706 first_unit_start_date,
707 last_unit_completion_date,
708 process_phase,
709 process_status,
710 maintenance_object_id,
711 maintenance_object_type,
712 maintenance_object_source
713 )values(
714 p_group_id,
715 sysdate,
716 recinfo.last_updated_by,
717 sysdate,
718 recinfo.created_by,
719 recinfo.last_update_login,
720 l_org_id,
721 8,
722 recinfo.wip_entity_id,
723 recinfo.scheduled_start_date,
724 recinfo.scheduled_completion_date,
725 2,
726 1,
727 nvl(l_maintenance_object_id, l_inventory_item_id),
728 decode(l_maintenance_object_id, null, 2, 1),
729 1
730 );
731 elsif ( recinfo.action_type = 3 ) then
732 -- Cancel
733 insert into wip_job_schedule_interface(
734 group_id,
735 last_update_date,
736 last_updated_by,
737 creation_date,
738 created_by,
739 last_update_login,
740 organization_id,
741 load_type,
742 wip_entity_id,
743 status_type,
744 process_phase,
745 process_status,
746 maintenance_object_id,
747 maintenance_object_type,
748 maintenance_object_source
749 )values(
750 p_group_id,
751 sysdate,
752 recinfo.last_updated_by,
753 sysdate,
754 recinfo.created_by,
755 recinfo.last_update_login,
756 l_org_id,
757 8,
758 recinfo.wip_entity_id,
759 7,
760 2,
761 1,
762 nvl(l_maintenance_object_id, l_inventory_item_id),
763 decode(l_maintenance_object_id, null, 2, 1),
764 1
765 );
766 end if;
767
768 fetch pmwo into recinfo;
769 EXIT WHEN ( pmwo%NOTFOUND );
770 END LOOP;
771 close pmwo;
772 end move_pm_to_wdj;
773
774 END eam_pm_utils;