[Home] [Help]
PACKAGE BODY: APPS.EAM_PM_UTILS
Source
1 PACKAGE BODY eam_pm_utils AS
2 /* $Header: EAMPMUTB.pls 120.8 2006/04/20 04:10:26 yjhabak ship $ */
3
4 /**
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,
100 p_meter_reading_id => l_meter_reading_id,
101 p_meter_id => null,
102 p_meter_reading_date => null);
103
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 -- 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
169 and ejct.wip_entity_id = wdj2.wip_entity_id
170 group by ejct.wip_entity_id);
171
172 --Retruning actual end date to get the counter reading id.
173 p_last_service_end_date := l_last_actual_end_date;
174
175 -- Find the wo transaction on this l_last_actual_end_date
176 -- for this asset / act assoc and see if it matches
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
253 open C;
254 LOOP
255 fetch C into x_child_aa;
256 EXIT WHEN ( C%NOTFOUND );
257 update_all_pm_uncomplete(p_wip_entity_id, x_child_aa);
258 END LOOP;
259 close C;
260
261 end update_all_pm_uncomplete;
262
263
264 /**
265 * This function returns false if no pm related data should be updated. It returns true
266 * otherwise. When it returns true, it populates the return parameters with the right
267 * information.
268 */
269 function check_applicable_pm(p_org_id in number,
270 p_wip_entity_id in number,
271 p_pm_schedule_id out NOCOPY number,
272 p_resched_point out NOCOPY number) return boolean
273 is
274 cursor C(p_asset_group_id number,
275 p_asset_number varchar2,
276 p_asset_activity_id number) is
277 select pms.pm_schedule_id
278 from eam_pm_schedulings pms,
279 mtl_eam_asset_activities eaa
280 where pms.activity_association_id = eaa.activity_association_id
281 and eaa.organization_id = p_org_id
282 and eaa.inventory_item_id = p_asset_group_id
283 and eaa.serial_number = p_asset_number
284 and eaa.asset_activity_id = p_asset_activity_id
285 and nvl(eaa.start_date_active, sysdate-1) < sysdate
286 and nvl(eaa.end_date_active, sysdate+1) > sysdate
287 and nvl(pms.from_effective_date, sysdate-1) < sysdate
288 and nvl(pms.to_effective_date, sysdate+1) > sysdate;
289
290 x_pm_id number := null;
291 x_asset_group_id number := null;
292 x_asset_number varchar2(30) := null;
293 x_asset_activity_id number := null;
294 x_rebuild_id number := null;
295 x_generated_by_pm boolean := false;
296 x_resched_point number;
297 begin
298 select pm_schedule_id,
299 asset_group_id,
300 asset_number,
301 primary_item_id,
302 rebuild_item_id
303 into x_pm_id,
304 x_asset_group_id,
305 x_asset_number,
306 x_asset_activity_id,
307 x_rebuild_id
308 from wip_discrete_jobs
309 where wip_entity_id = p_wip_entity_id;
310
311 -- do nothing for rebuildable work order
312 if ( x_rebuild_id is not null ) then
313 return false;
314 end if;
315
316 -- find the corresponding valid pm schedule id if there is one
317 if ( x_pm_id is null ) then
318 open C(x_asset_group_id, x_asset_number, x_asset_activity_id);
319 fetch C into x_pm_id;
320 if ( C%NOTFOUND ) then
321 close C;
322 return false;
323 end if;
324 close C;
325 else
326 x_generated_by_pm := true;
327 end if;
328
329 select rescheduling_point into x_resched_point
330 from eam_pm_schedulings
331 where pm_schedule_id = x_pm_id;
332
333 -- if the rescheduling point is start date, then we ignore the manually created
334 -- work orders.
335 if ( x_resched_point = 1 AND x_generated_by_pm = false ) then
336 return false;
337 end if;
338
339 p_pm_schedule_id := x_pm_id;
340 p_resched_point := x_resched_point;
341 return true;
342 end check_applicable_pm;
343
344
345 /**
346 * This procedure is called to move the forecasted work order suggestions from
347 * forecast table to wip_job_schedule_interface to be uploaded. It removes the
348 * records from forecast table then.
349 */
350 procedure transfer_to_wdj(p_group_id number) is
351 cursor pms is
352 select distinct pm_schedule_id, activity_association_id
353 from eam_forecasted_work_orders
354 where group_id = p_group_id
355 and process_flag = 'Y';
356
357 l_pm_id number;
358 l_assoc_id number;
359 begin
360 open pms;
361 fetch pms into l_pm_id, l_assoc_id;
362 if ( pms%NOTFOUND ) then
363 close pms;
364 return;
365 end if;
366
367 LOOP
368 move_pm_to_wdj(p_group_id, l_pm_id, l_assoc_id);
369 fetch pms into l_pm_id, l_assoc_id;
370 EXIT WHEN ( pms%NOTFOUND );
371 END LOOP;
372 close pms;
373
374 -- delete the records that has been moved to the mass load interface table
375 delete from eam_forecasted_work_orders
376 where group_id = p_group_id
377 and process_flag = 'Y';
378 end transfer_to_wdj;
379
380
381 procedure move_pm_to_wdj(p_group_id in number,
382 p_pm_id in number,
383 p_assoc_id in number) is
384 cursor pmwo is
385 select scheduled_start_date,
386 scheduled_completion_date,
387 wip_entity_id,
388 action_type,
389 created_by,
390 creation_date,
391 last_update_login,
392 last_update_date,
393 last_updated_by,
394 attribute_category,
395 attribute1,
396 attribute2,
397 attribute3,
398 attribute4,
399 attribute5,
400 attribute6,
401 attribute7,
402 attribute8,
403 attribute9,
404 attribute10,
405 attribute11,
406 attribute12,
407 attribute13,
408 attribute14,
409 attribute15
410 from eam_forecasted_work_orders
411 where pm_schedule_id = p_pm_id
412 and group_id = p_group_id
413 and process_flag = 'Y';
414
415 recinfo pmwo%ROWTYPE;
416 l_org_id number;
417 l_inventory_item_id number;
418 l_serial_number varchar2(30);
419 l_asset_activity_id number;
420 l_owning_department number;
421 l_description varchar2(240);
422 -- l_routing_reference_id number;
423 -- l_bom_reference_id number;
424 l_priority_code varchar2(30);
425 l_activity_cause varchar2(30);
426 l_activity_type varchar2(30);
427 l_activity_source varchar2(30);
428 l_tagging_required_flag varchar2(1);
429 l_shutdown_type_code varchar2(30);
430 l_class_code varchar2(10);
431 l_eam_item_type number;
432 l_maintenance_object_id number;
433 begin
434 select organization_id,
435 inventory_item_id,
436 serial_number,
437 asset_activity_id,
438 owning_department_id,
439 activity_cause_code,
440 activity_type_code,
441 activity_source_code,
442 tagging_required_flag,
443 shutdown_type_code,
444 priority_code
445 into l_org_id,
446 l_inventory_item_id,
447 l_serial_number,
448 l_asset_activity_id,
449 l_owning_department,
450 l_activity_cause,
451 l_activity_type,
452 l_activity_source,
453 l_tagging_required_flag,
454 l_shutdown_type_code,
455 l_priority_code
456 from mtl_eam_asset_activities
457 where activity_association_id = p_assoc_id;
458
459 if (l_serial_number is not null) then
460 select wip_accounting_class_code, gen_object_id
461 into l_class_code, l_maintenance_object_id
462 from mtl_serial_numbers
463 where current_organization_id = l_org_id
464 and inventory_item_id = l_inventory_item_id
465 and serial_number = l_serial_number;
466 end if;
467
468 select eam_item_type
469 into l_eam_item_type
470 from mtl_system_items
471 where organization_id = l_org_id
472 and inventory_item_id = l_inventory_item_id;
473
474 select description
475 into l_description
476 /* from mtl_system_items_b Commented for bug#4878157 */
477 from mtl_system_items_vl /* Added for bug#4878157 */
478 where inventory_item_id = l_asset_activity_id
479 and organization_id = l_org_id;
480
481 open pmwo;
482 fetch pmwo into recinfo;
483 if ( pmwo%NOTFOUND ) then
484 close pmwo;
485 return;
486 end if;
487
488 LOOP
489 if ( recinfo.action_type = 1 ) then
490 -- Create
491 if ( l_eam_item_type = 1 ) then
492 -- for Asset Group and number
493 insert into wip_job_schedule_interface(
494 group_id,
495 last_update_date,
496 last_updated_by,
497 creation_date,
498 created_by,
499 last_update_login,
500 organization_id,
501 load_type,
502 first_unit_start_date,
503 last_unit_completion_date,
504 asset_group_id,
505 description,
506 routing_reference_id,
507 bom_reference_id,
508 asset_number,
509 primary_item_id,
510 pm_schedule_id,
511 process_phase,
512 process_status,
513 owning_department,
514 activity_type,
515 activity_cause,
516 activity_source,
517 tagout_required,
518 shutdown_type,
519 priority,
520 plan_maintenance,
521 class_code,
522 attribute_category,
523 attribute1,
524 attribute2,
525 attribute3,
526 attribute4,
527 attribute5,
528 attribute6,
529 attribute7,
530 attribute8,
531 attribute9,
532 attribute10,
533 attribute11,
534 attribute12,
535 attribute13,
536 attribute14,
537 attribute15,
538 maintenance_object_id,
539 maintenance_object_type,
540 maintenance_object_source
541 )values(
542 p_group_id,
543 sysdate,
544 recinfo.last_updated_by,
545 sysdate,
546 recinfo.created_by,
547 recinfo.last_update_login,
548 l_org_id,
549 7,
550 recinfo.scheduled_start_date,
551 recinfo.scheduled_completion_date,
552 l_inventory_item_id,
553 l_description,
554 l_asset_activity_id,
555 l_asset_activity_id,
556 l_serial_number,
557 l_asset_activity_id,
558 p_pm_id,
559 2,
560 1,
561 l_owning_department,
562 l_activity_type,
563 l_activity_cause,
564 l_activity_source,
565 l_tagging_required_flag,
566 l_shutdown_type_code,
567 to_number(l_priority_code),
568 'Y',
569 l_class_code,
570 recinfo.attribute_category,
571 recinfo.attribute1,
572 recinfo.attribute2,
573 recinfo.attribute3,
574 recinfo.attribute4,
575 recinfo.attribute5,
576 recinfo.attribute6,
577 recinfo.attribute7,
578 recinfo.attribute8,
579 recinfo.attribute9,
580 recinfo.attribute10,
581 recinfo.attribute11,
582 recinfo.attribute12,
583 recinfo.attribute13,
584 recinfo.attribute14,
585 recinfo.attribute15,
586 l_maintenance_object_id,
587 1,
588 1
589 );
590 elsif (l_eam_item_type = 3) then
591 -- for rebuildables
592 insert into wip_job_schedule_interface(
593 group_id,
594 last_update_date,
595 last_updated_by,
596 creation_date,
597 created_by,
598 last_update_login,
599 organization_id,
600 load_type,
601 first_unit_start_date,
602 last_unit_completion_date,
603 rebuild_item_id,
604 description,
605 routing_reference_id,
606 bom_reference_id,
607 rebuild_serial_number,
608 primary_item_id,
609 pm_schedule_id,
610 process_phase,
611 process_status,
612 owning_department,
613 activity_type,
614 activity_cause,
615 activity_source,
616 tagout_required,
617 shutdown_type,
618 priority,
619 plan_maintenance,
620 class_code,
621 attribute_category,
622 attribute1,
623 attribute2,
624 attribute3,
625 attribute4,
626 attribute5,
627 attribute6,
628 attribute7,
629 attribute8,
630 attribute9,
631 attribute10,
632 attribute11,
633 attribute12,
634 attribute13,
635 attribute14,
636 attribute15,
637 manual_rebuild_flag,
638 maintenance_object_id,
639 maintenance_object_type,
640 maintenance_object_source
641 )values(
642 p_group_id,
643 sysdate,
644 recinfo.last_updated_by,
645 sysdate,
646 recinfo.created_by,
647 recinfo.last_update_login,
648 l_org_id,
649 7,
650 recinfo.scheduled_start_date,
651 recinfo.scheduled_completion_date,
652 l_inventory_item_id,
653 l_description,
654 l_asset_activity_id,
655 l_asset_activity_id,
656 l_serial_number,
657 l_asset_activity_id,
658 p_pm_id,
659 2,
660 1,
661 l_owning_department,
662 l_activity_type,
663 l_activity_cause,
664 l_activity_source,
665 l_tagging_required_flag,
666 l_shutdown_type_code,
667 to_number(l_priority_code),
668 'Y',
669 l_class_code,
670 recinfo.attribute_category,
671 recinfo.attribute1,
672 recinfo.attribute2,
673 recinfo.attribute3,
674 recinfo.attribute4,
675 recinfo.attribute5,
676 recinfo.attribute6,
677 recinfo.attribute7,
678 recinfo.attribute8,
679 recinfo.attribute9,
680 recinfo.attribute10,
681 recinfo.attribute11,
682 recinfo.attribute12,
683 recinfo.attribute13,
684 recinfo.attribute14,
685 recinfo.attribute15,
686 'Y',
687 nvl(l_maintenance_object_id, l_inventory_item_id),
688 decode(l_maintenance_object_id, null, 2, 1),
689 1
690 );
691 end if;
692
693 elsif ( recinfo.action_type = 2 ) then
694 -- Reschdule
695 insert into wip_job_schedule_interface(
696 group_id,
697 last_update_date,
698 last_updated_by,
699 creation_date,
700 created_by,
701 last_update_login,
702 organization_id,
703 load_type,
704 wip_entity_id,
705 first_unit_start_date,
706 last_unit_completion_date,
707 process_phase,
708 process_status,
709 maintenance_object_id,
710 maintenance_object_type,
711 maintenance_object_source
712 )values(
713 p_group_id,
714 sysdate,
715 recinfo.last_updated_by,
716 sysdate,
717 recinfo.created_by,
718 recinfo.last_update_login,
719 l_org_id,
720 8,
721 recinfo.wip_entity_id,
722 recinfo.scheduled_start_date,
723 recinfo.scheduled_completion_date,
724 2,
725 1,
726 nvl(l_maintenance_object_id, l_inventory_item_id),
727 decode(l_maintenance_object_id, null, 2, 1),
728 1
729 );
730 elsif ( recinfo.action_type = 3 ) then
731 -- Cancel
732 insert into wip_job_schedule_interface(
733 group_id,
734 last_update_date,
735 last_updated_by,
736 creation_date,
737 created_by,
738 last_update_login,
739 organization_id,
740 load_type,
741 wip_entity_id,
742 status_type,
743 process_phase,
744 process_status,
745 maintenance_object_id,
746 maintenance_object_type,
747 maintenance_object_source
748 )values(
749 p_group_id,
750 sysdate,
751 recinfo.last_updated_by,
752 sysdate,
753 recinfo.created_by,
754 recinfo.last_update_login,
755 l_org_id,
756 8,
757 recinfo.wip_entity_id,
758 7,
759 2,
760 1,
761 nvl(l_maintenance_object_id, l_inventory_item_id),
762 decode(l_maintenance_object_id, null, 2, 1),
763 1
764 );
765 end if;
766
767 fetch pmwo into recinfo;
768 EXIT WHEN ( pmwo%NOTFOUND );
769 END LOOP;
770 close pmwo;
771 end move_pm_to_wdj;
772
773 END eam_pm_utils;