1 PACKAGE BODY EAM_PMDef_Pub AS
2 /* $Header: EAMPPMDB.pls 120.33.12020000.7 2013/03/30 01:01:10 shengywa ship $*/
3 -- Start of comments
4 -- API name : package EAM_PMDef_Pub; API's: instantiate_PM_def, create_PM_def, update_PM_def
5 -- Type : Public
6 -- Function : Copy, create, and update PM definition and associated PM rules.
7 -- Pre-reqs : None.
8 -- Version : Current version: 1.0
9 -- Initial version: 1.0
10 --
11 -- Notes
12 --
13 -- End of comments
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_PMDef_Pub';
16 g_sysdate DATE :=sysdate;
17 /* for de-bugging */
18 PROCEDURE print_log(info varchar2) is
19 PRAGMA AUTONOMOUS_TRANSACTION;
20 l_dummy number;
21 BEGIN
22
23 FND_FILE.PUT_LINE(FND_FILE.LOG, info);
24
25 END;
26
27 -- Given an activity association id of an activity association instance,
28 -- and a pm_schedule_id of a pm template,
29 -- create a new pm definition from the template, where the new pm definition
30 -- is associated to the given activity_association_id.
31
32 PROCEDURE instantiate_PM_def
33 (
34 p_pm_schedule_id IN NUMBER,
35 p_activity_assoc_id IN NUMBER,
36 x_new_pm_schedule_id OUT NOCOPY NUMBER, -- this is the pm_schedule_id of the newly copied pm schedule
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER ,
39 x_msg_data OUT NOCOPY VARCHAR2
40 )
41 IS
42
43 l_pm_schedule_id number;
44 pm_header_row pm_scheduling_rec_type;
45 pm_activity_row pm_activities_grp_rec_type;
46 pm_rule_row pm_rule_rec_type;
47 l_rule_id number;
48 l_validated boolean;
49 l_reason_failed varchar2(30);
50 l_initial_reading number;
51 l_maintenance_object_type number;
52 l_maintenance_object_id number;
53 l_meter_id number;
54 l_return_status varchar2(1);
55 l_msg_count number;
56 l_msg_data varchar2(2000);
57 l_update_failed varchar2(1);
58 l_error_message varchar2(2000);
59 l_prev_pm_schedule_id number;
60 l_last_act number;
61 l_count number;-- for bug 7230256
62
63 cursor pm_rules_csr IS
64 select
65 PM_SCHEDULE_ID,
66 RULE_TYPE,
67 DAY_INTERVAL,
68 METER_ID,
69 RUNTIME_INTERVAL,
70 EFFECTIVE_READING_FROM,
71 EFFECTIVE_READING_TO,
72 EFFECTIVE_DATE_FROM,
73 EFFECTIVE_DATE_TO,
74 LIST_DATE,
75 LIST_DATE_DESC
76 from eam_pm_scheduling_rules
77 where pm_schedule_id = p_pm_schedule_id;
78
79 cursor pm_activities_csr IS
80 select
81 epa.pm_schedule_id,
82 meaa.activity_association_id,
83 epa.interval_multiple,
84 epa.allow_repeat_in_cycle,
85 epa.day_tolerance,
86 epa.next_service_start_date,
87 epa.next_service_end_date
88 from eam_pm_activities epa, mtl_eam_asset_activities meaa
89 where epa.pm_schedule_id=p_pm_schedule_id
90 and epa.activity_association_id =meaa.source_tmpl_id
91 and meaa.maintenance_object_id=l_maintenance_object_id;
92
93
94 BEGIN
95
96 x_return_status:=FND_API.G_RET_STS_SUCCESS;
97
98 -- Check that enough info is supplied to identify which pm schedule to copy from.
99 if (p_pm_schedule_id is null) then
100 FND_MESSAGE.SET_NAME ('EAM', 'EAM_MT_SUPPLY_PARAMS');
101 FND_MSG_PUB.Add;
102 RAISE FND_API.G_EXC_ERROR;
103 end if;
104
105 begin
106 select
107 PM_SCHEDULE_ID,
108 ACTIVITY_ASSOCIATION_ID,
109 NON_SCHEDULED_FLAG,
110 FROM_EFFECTIVE_DATE,
111 TO_EFFECTIVE_DATE,
112 RESCHEDULING_POINT,
113 LEAD_TIME,
114 ATTRIBUTE_CATEGORY,
115 ATTRIBUTE1,
116 ATTRIBUTE2 ,
117 ATTRIBUTE3 ,
118 ATTRIBUTE4 ,
119 ATTRIBUTE5 ,
120 ATTRIBUTE6 ,
121 ATTRIBUTE7 ,
122 ATTRIBUTE8 ,
123 ATTRIBUTE9 ,
124 ATTRIBUTE10 ,
125 ATTRIBUTE11 ,
126 ATTRIBUTE12 ,
127 ATTRIBUTE13 ,
128 ATTRIBUTE14 ,
129 ATTRIBUTE15 ,
130 DAY_TOLERANCE ,
131 SOURCE_CODE ,
132 SOURCE_LINE ,
133 DEFAULT_IMPLEMENT,
134 WHICHEVER_FIRST ,
135 INCLUDE_MANUAL ,
136 SET_NAME_ID ,
137 SCHEDULING_METHOD_CODE,
138 TYPE_CODE,
139 NEXT_SERVICE_START_DATE,
140 NEXT_SERVICE_END_DATE,
141 SOURCE_TMPL_ID ,
142 AUTO_INSTANTIATION_FLAG ,
143 NAME ,
144 TMPL_FLAG ,
145 GENERATE_WO_STATUS ,
146 INTERVAL_PER_CYCLE ,
147 CURRENT_CYCLE ,
148 CURRENT_SEQ ,
149 CURRENT_WO_SEQ ,
150 BASE_DATE ,
151 BASE_READING ,
152 EAM_LAST_CYCLIC_ACT ,
153 MAINTENANCE_OBJECT_ID ,
154 MAINTENANCE_OBJECT_TYPE ,
155 /* added for PM Reviewer -- start-- */
156 LAST_REVIEWED_DATE ,
157 Last_reviewed_by ,
158 /* ---PM reviewer--- end---- */
159 generate_next_work_order,
160 planner_maintenance -- WO defaults ER
161 into pm_header_row
162 from eam_pm_schedulings
163 where pm_schedule_id=p_pm_schedule_id;
164 exception
165 when no_data_found then
166 FND_MESSAGE.SET_NAME ('EAM', 'EAM_MT_SUPPLY_PARAMS');
167 FND_MSG_PUB.Add;
168 RAISE FND_API.G_EXC_ERROR;
169 end;
170
171 select eam_pm_schedulings_s.nextval into l_pm_schedule_id from dual;
172
173 -- If the source pm is not a template, raise error
174 if (not (pm_header_row.tmpl_flag = 'Y')) then
175 FND_MESSAGE.SET_NAME('EAM', 'EAM_PM_INST_NOT_TMPL');
176 FND_MSG_PUB.Add;
177 RAISE FND_API.G_EXC_ERROR;
178 else
179 -- When instantiating, tmpl_flag should be 'N'
180 pm_header_row.tmpl_flag:='N';
181 pm_header_row.source_tmpl_id:=pm_header_row.pm_schedule_id;
182 pm_header_row.auto_instantiation_flag:='N';
183 pm_header_row.name:=pm_header_row.name || '-' || l_pm_schedule_id;
184
185 begin
186
187 select maintenance_object_id, maintenance_object_type
188 into l_maintenance_object_id, l_maintenance_object_type
189 from mtl_eam_asset_activities
190 where activity_association_id=p_activity_assoc_id;
191
192 pm_header_row.maintenance_object_id := l_maintenance_object_id;
193 pm_header_row.maintenance_object_type := 3;
194 pm_header_row.current_cycle := 1;
195 pm_header_row.current_seq := 0;
196
197 exception
198 when no_data_found then
199 FND_MESSAGE.SET_NAME ('EAM', 'EAM_MT_SUPPLY_PARAMS');
200 FND_MSG_PUB.Add;
201 RAISE FND_API.G_EXC_ERROR;
202 end;
203 end if;
204
205
206 l_validated:=validate_pm_header(pm_header_row, l_reason_failed);
207
208
209 if (l_validated) then
210
211 insert into eam_pm_schedulings (
212 PM_SCHEDULE_ID,
213 ACTIVITY_ASSOCIATION_ID,
214 NON_SCHEDULED_FLAG,
215 FROM_EFFECTIVE_DATE,
216 TO_EFFECTIVE_DATE,
217 RESCHEDULING_POINT,
218 LEAD_TIME,
219 ATTRIBUTE_CATEGORY,
220 ATTRIBUTE1,
221 ATTRIBUTE2,
222 ATTRIBUTE3,
223 ATTRIBUTE4,
224 ATTRIBUTE5,
225 ATTRIBUTE6,
226 ATTRIBUTE7,
227 ATTRIBUTE8,
228 ATTRIBUTE9,
229 ATTRIBUTE10,
230 ATTRIBUTE11,
231 ATTRIBUTE12,
232 ATTRIBUTE13,
233 ATTRIBUTE14,
234 ATTRIBUTE15,
235 DAY_TOLERANCE ,
236 SOURCE_CODE ,
237 SOURCE_LINE ,
238 DEFAULT_IMPLEMENT,
239 WHICHEVER_FIRST ,
240 INCLUDE_MANUAL ,
241 SET_NAME_ID,
242 SCHEDULING_METHOD_CODE,
243 TYPE_CODE,
244 NEXT_SERVICE_START_DATE,
245 NEXT_SERVICE_END_DATE,
246 SOURCE_TMPL_ID,
247 AUTO_INSTANTIATION_FLAG,
248 NAME ,
249 TMPL_FLAG ,
250 GENERATE_WO_STATUS ,
251 INTERVAL_PER_CYCLE ,
252 CURRENT_CYCLE ,
253 CURRENT_SEQ ,
254 CURRENT_WO_SEQ ,
255 BASE_DATE ,
256 BASE_READING ,
257 EAM_LAST_CYCLIC_ACT ,
258 MAINTENANCE_OBJECT_ID ,
259 MAINTENANCE_OBJECT_TYPE ,
260 /* added for PM Reviewer -- start-- */
261 LAST_REVIEWED_DATE ,
262 Last_reviewed_by ,
263 /* ---PM reviewer--- end---- */
264 created_by,
265 creation_date,
266 last_update_login,
267 last_updated_by,
268 last_update_date ,
269 generate_next_work_order,
270 planner_maintenance /*WO defaults ER*/ )
271 values (
272 l_pm_schedule_id,
273 pm_header_row.ACTIVITY_ASSOCIATION_ID,
274 pm_header_row.NON_SCHEDULED_FLAG,
275 pm_header_row.FROM_EFFECTIVE_DATE,
276 pm_header_row.TO_EFFECTIVE_DATE,
277 pm_header_row.RESCHEDULING_POINT,
278 pm_header_row.LEAD_TIME,
279 pm_header_row.ATTRIBUTE_CATEGORY,
280 pm_header_row.ATTRIBUTE1,
281 pm_header_row.ATTRIBUTE2 ,
282 pm_header_row.ATTRIBUTE3 ,
283 pm_header_row.ATTRIBUTE4 ,
284 pm_header_row.ATTRIBUTE5 ,
285 pm_header_row.ATTRIBUTE6 ,
286 pm_header_row.ATTRIBUTE7 ,
287 pm_header_row.ATTRIBUTE8 ,
288 pm_header_row.ATTRIBUTE9 ,
289 pm_header_row.ATTRIBUTE10 ,
290 pm_header_row.ATTRIBUTE11 ,
291 pm_header_row.ATTRIBUTE12 ,
292 pm_header_row.ATTRIBUTE13 ,
293 pm_header_row.ATTRIBUTE14 ,
294 pm_header_row.ATTRIBUTE15 ,
295 pm_header_row.DAY_TOLERANCE ,
296 pm_header_row.SOURCE_CODE ,
297 pm_header_row.SOURCE_LINE ,
298 pm_header_row.DEFAULT_IMPLEMENT,
299 pm_header_row.WHICHEVER_FIRST ,
300 pm_header_row.INCLUDE_MANUAL ,
301 pm_header_row.SET_NAME_ID,
302 pm_header_row.SCHEDULING_METHOD_CODE ,
303 pm_header_row.TYPE_CODE,
304 pm_header_row.NEXT_SERVICE_START_DATE,
305 pm_header_row.NEXT_SERVICE_END_DATE,
306 pm_header_row.SOURCE_TMPL_ID,
307 pm_header_row.AUTO_INSTANTIATION_FLAG,
308 pm_header_row.NAME ,
309 pm_header_row.TMPL_FLAG ,
310 pm_header_row.GENERATE_WO_STATUS ,
311 pm_header_row.INTERVAL_PER_CYCLE ,
312 pm_header_row.CURRENT_CYCLE ,
313 pm_header_row.CURRENT_SEQ ,
314 pm_header_row.CURRENT_WO_SEQ ,
315 pm_header_row.BASE_DATE ,
316 pm_header_row.BASE_READING ,
317 pm_header_row.EAM_LAST_CYCLIC_ACT ,
318 pm_header_row.MAINTENANCE_OBJECT_ID ,
319 pm_header_row.MAINTENANCE_OBJECT_TYPE ,
320 /* added for PM Reviewer -- start-- */
321 pm_header_row.LAST_REVIEWED_DATE ,
322 pm_header_row.Last_reviewed_by ,
323 /* ---PM reviewer--- end---- */
324 fnd_global.user_id,
325 sysdate,
326 fnd_global.login_id,
327 fnd_global.user_id,
328 sysdate ,
329 pm_header_row.generate_next_work_order,
330 pm_header_row.planner_maintenance );
331
332 --now start copying the activities.
333
334 for a_pm_activity in pm_activities_csr loop
335
336 --creating an new activity row
337 pm_activity_row.pm_schedule_id := a_pm_activity.pm_schedule_id;
338 pm_activity_row.activity_association_id := a_pm_activity.activity_association_id;
339 pm_activity_row.interval_multiple := a_pm_activity.interval_multiple;
340 pm_activity_row.allow_repeat_in_cycle := a_pm_activity.allow_repeat_in_cycle;
341 pm_activity_row.day_tolerance := a_pm_activity.day_tolerance;
342
343 --validating the activity row
344 l_validated := validate_pm_activity
345 ( pm_activity_row,
346 pm_header_row,
347 l_reason_failed);
348
349 if (l_validated) then
350
351 insert into eam_pm_activities
352 (pm_schedule_id,
353 activity_association_id,
354 interval_multiple,
355 allow_repeat_in_cycle,
356 day_tolerance,
357 created_by,
358 creation_date,
359 last_update_login,
360 last_updated_by,
361 last_update_date )
362 values
363 (l_pm_schedule_id,
364 a_pm_activity.activity_association_id,
365 a_pm_activity.interval_multiple,
366 a_pm_activity.allow_repeat_in_cycle,
367 a_pm_activity.day_tolerance,
368 fnd_global.user_id,
369 sysdate,
370 fnd_global.login_id,
371 fnd_global.user_id,
372 sysdate );
373
374 end if;
375
376 end loop;
377
378 eam_pmdef_pub.update_pm_last_cyclic_act
379 ( p_api_version => 1.0 ,
380 p_init_msg_list => 'F' ,
381 p_commit => 'F' ,
382 p_validation_level => 100 ,
383 x_return_status => l_return_status,
384 x_msg_count => l_msg_count,
385 x_msg_data => l_msg_data,
386 p_pm_schedule_id => l_pm_schedule_id
387 );
388
389
390 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
391 l_update_failed := 'Y';
392 RAISE FND_API.G_EXC_ERROR;
393 end if;
394
395 -- now start copying over the rules
396 for a_pm_rule in pm_rules_csr loop
397
398
399 -- Get maintenance object id and maintenance type
400 select maintenance_object_id, maintenance_object_type
401 into l_maintenance_object_id, l_maintenance_object_type
402 from mtl_eam_asset_activities
403 where activity_association_id=p_activity_assoc_id;
404
405 -- for runtime rule, get the meter id
406 if (a_pm_rule.meter_id is not null) then
407
408 --added for performance issues.
409 select cca.counter_id into l_meter_id
410 from CSI_COUNTER_ASSOCIATIONS cca, CSI_COUNTERS_B ccb
411 where cca.counter_id = ccb.counter_id
412 and ccb.created_from_counter_tmpl_id=a_pm_rule.meter_id
413 and cca.source_object_id= l_maintenance_object_id ;
414
415 end if;
416
417 insert into eam_pm_scheduling_rules
418 ( PM_SCHEDULE_ID,
419 RULE_TYPE,
420 DAY_INTERVAL,
421 METER_ID ,
422 RUNTIME_INTERVAL,
423 CREATED_BY ,
424 CREATION_DATE ,
425 LAST_UPDATE_LOGIN,
426 LAST_UPDATE_DATE,
427 LAST_UPDATED_BY,
428 EFFECTIVE_READING_FROM ,
429 EFFECTIVE_READING_TO ,
430 EFFECTIVE_DATE_FROM ,
431 EFFECTIVE_DATE_TO ,
432 LIST_DATE ,
433 LIST_DATE_DESC,
434 RULE_ID)
435 values
436 (l_pm_schedule_id,
437 a_pm_rule.RULE_TYPE,
438 a_pm_rule.DAY_INTERVAL,
439 l_meter_id,
440 a_pm_rule.RUNTIME_INTERVAL,
441 fnd_global.user_id,
442 sysdate,
443 fnd_global.login_id,
444 sysdate ,
445 fnd_global.user_id,
446 a_pm_rule.EFFECTIVE_READING_FROM ,
447 a_pm_rule.EFFECTIVE_READING_TO ,
448 a_pm_rule.EFFECTIVE_DATE_FROM ,
449 a_pm_rule.EFFECTIVE_DATE_TO ,
450 a_pm_rule.LIST_DATE ,
451 a_pm_rule.LIST_DATE_DESC ,
452 eam_pm_scheduling_rules_s.nextval);
453
454 -- if it's a runtime rule, insert meter initial reading into eam_pm_last_services table
455 if a_pm_rule.meter_id is not null then
456
457 --added for performance issues
458 select initial_reading into l_initial_reading from
459 (select initial_reading from CSI_COUNTERS_B where counter_id = a_pm_rule.meter_id
460 union
461 select initial_reading from CSI_COUNTER_TEMPLATE_B where counter_id = a_pm_rule.meter_id
462 );
463 select EAM_LAST_CYCLIC_ACT into l_last_act from eam_pm_schedulings
464 where pm_schedule_id = l_pm_schedule_id;
465
466 -- for bug 7230256
467 l_count := 0;
468 select count (*) into l_count
469 from eam_pm_last_service
470 where meter_id=l_meter_id and ACTIVITY_ASSOCIATION_ID =l_last_act;
471
472 if l_count = 0 then
473
474 insert into eam_pm_last_service
475 ( METER_ID ,
476 ACTIVITY_ASSOCIATION_ID ,
477 LAST_SERVICE_READING ,
478 PREV_SERVICE_READING ,
479 WIP_ENTITY_ID ,
480 LAST_UPDATE_DATE ,
481 LAST_UPDATED_BY ,
482 CREATION_DATE ,
483 CREATED_BY ,
484 LAST_UPDATE_LOGIN
485 )
486 values
487 (
488 l_meter_id,
489 l_last_act,
490 l_initial_reading,
491 null,
492 null,
493 sysdate,
494 fnd_global.user_id,
495 sysdate,
496 fnd_global.user_id,
497 fnd_global.login_id
498 );
499 end if;
500 end if;
501
502 end loop;
503
504 eam_pmdef_pub.update_pm_last_service_reading
505 ( p_api_version => 1.0 ,
506 p_init_msg_list => 'F' ,
507 p_commit => 'F' ,
508 p_validation_level => 100 ,
509 x_return_status => l_return_status,
510 x_msg_count => l_msg_count,
511 x_msg_data => l_msg_data,
512 p_pm_schedule_id => l_pm_schedule_id
513 );
514
515 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
516 l_update_failed := 'Y';
517 RAISE FND_API.G_EXC_ERROR;
518 end if;
519
520 end if;
521
522 l_prev_pm_schedule_id := p_pm_schedule_id;
523
524
525 EXCEPTION
526 WHEN FND_API.G_EXC_ERROR THEN
527 if l_update_failed = 'Y' then
528 x_return_status := fnd_api.g_ret_sts_error;
529 x_msg_count := 1;
530 x_msg_data := l_msg_data;
531 else
532 FND_MSG_PUB.get
533 ( p_msg_index_out => x_msg_count ,
534 p_data => x_msg_data
535 );
536 x_msg_data := substr(x_msg_data,1,2000);
537 end if;
538
539 when others then
540 l_msg_count := 1;
541 x_return_status := fnd_api.g_ret_sts_error;
542 l_error_message := substrb(sqlerrm,1,512);
543 x_msg_data := l_error_message;
544
545 -- End of API body.
546 END instantiate_PM_def;
547
548
549
550 /* This procedure instantiates a set of PM definitions for all asset_association_id's in the activity_assoc_id_tbl table.
551 */
552
553 PROCEDURE instantiate_PM_Defs
554 (
555 p_api_version IN NUMBER ,
556 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
557 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
558 p_validation_level IN NUMBER :=
559 FND_API.G_VALID_LEVEL_FULL ,
560 x_return_status OUT NOCOPY VARCHAR2 ,
561 x_msg_count OUT NOCOPY NUMBER ,
562 x_msg_data OUT NOCOPY VARCHAR2 ,
563 p_activity_assoc_id_tbl IN EAM_ObjectInstantiation_PUB.Association_Id_Tbl_Type
564 )
565 IS
566 l_api_name CONSTANT VARCHAR2(30) := 'instantiate_PM_defs';
567 l_api_version CONSTANT NUMBER := 1.0;
568 l_activity_assoc_id number;
569 l_return_status varchar2(1);
570 l_msg_count number;
571 l_msg_data varchar2(2000);
572
573 --record and table type added to identify the instantiated pm templates
574 TYPE l_pm_schedule_templ_rec_type is RECORD
575 ( PM_SCHEDULE_ID NUMBER );
576
577 TYPE l_pm_schedule_templ_tbl_type IS TABLE OF l_pm_schedule_templ_rec_type index by binary_integer;
578
579 l_pm_schedule_templ_tbl l_pm_schedule_templ_tbl_type;
580
581 -- This following cursor, given an activity association instance (l_activity_assoc_id), it returns
582 -- all the pm templates that are associated with the SOURCE activity association TEMPLATES
583 -- of l_activity_assoc_id, that have auto_instantiation_flag as "Y"
584
585 cursor pm_template_csr_old IS
586 select pm_schedule_id
587 from eam_pm_schedulings eps, mtl_eam_asset_activities meaa, eam_pm_set_names epsn
588 where eps.activity_association_id=meaa.source_tmpl_id
589 and meaa.activity_association_id=l_activity_assoc_id
590 and eps.tmpl_flag='Y'
591 and eps.auto_instantiation_flag='Y'
592 and eps.set_name_id=epsn.set_name_id
593 and (epsn.end_date is null or (epsn.end_date > sysdate and epsn.end_date > nvl(eps.to_effective_date, sysdate)));
594
595 cursor pm_template_csr IS
596 select distinct eps.pm_schedule_id
597 from eam_pm_schedulings eps, eam_pm_activities epa,mtl_eam_asset_activities meaa, eam_pm_set_names epsn
598 where eps.pm_schedule_id=epa.pm_schedule_id
599 and epa.activity_association_id=meaa.source_tmpl_id
600 and meaa.activity_association_id=l_activity_assoc_id
601 and eps.tmpl_flag='Y'
602 and eps.auto_instantiation_flag='Y'
603 and eps.set_name_id=epsn.set_name_id
604 and (epsn.end_date is null or (epsn.end_date > sysdate and epsn.end_date > nvl(eps.to_effective_date, sysdate)));
605
606 l_new_pm_schedule_id number;
607 l_pm_schedule_id number;
608 l_pm_template_instantiated varchar2(1);
609 k number :=0; -- for bug 7230256
610
611 BEGIN
612 -- Standard Start of API savepoint
613 SAVEPOINT instantiate_PM_Defs_pvt;
614 -- Standard call to check for call compatibility.
615 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
616 p_api_version ,
617 l_api_name ,
618 G_PKG_NAME )
619 THEN
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621 END IF;
622 -- Initialize message list if p_init_msg_list is set to TRUE.
623 IF FND_API.to_Boolean( p_init_msg_list ) THEN
624 FND_MSG_PUB.initialize;
625 END IF;
626 -- Initialize API return status to success
627 x_return_status := FND_API.G_RET_STS_SUCCESS;
628 -- API body
629
630
631 if p_activity_assoc_id_tbl.count>0 then
632
633 for i in p_activity_assoc_id_tbl.first..p_activity_assoc_id_tbl.last loop
634 l_activity_assoc_id:=p_activity_assoc_id_tbl(i);
635
636 for l_pm_row in pm_template_csr loop
637 --defaulting the flag
638 l_pm_template_instantiated := 'N';
639
640 --To check whether the template is already instantiated
641 if l_pm_schedule_templ_tbl.count >0 then
642
643 for jj in l_pm_schedule_templ_tbl.first..l_pm_schedule_templ_tbl.last loop
644
645 if l_pm_schedule_templ_tbl(jj).pm_schedule_id = l_pm_row.pm_schedule_id then
646 l_pm_template_instantiated := 'Y';
647 end if;
648
649 end loop;
650 end if;
651
652 --instantiate the pm def only if the pm template is not earlier instantiated
653 if l_pm_template_instantiated = 'N' then
654 instantiate_PM_def
655 (
656 p_activity_assoc_id => l_activity_assoc_id,
657 p_pm_schedule_id => l_pm_row.pm_schedule_id,
658 x_return_status => l_return_status,
659 x_new_pm_schedule_id => l_new_pm_schedule_id,
660 x_msg_count => l_msg_count,
661 x_msg_data => l_msg_data
662 );
663
664 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
665 x_return_status := l_return_status;
666 x_msg_count := l_msg_count;
667 x_msg_data := l_msg_data;
668 exit;
669 end if;
670
671 l_pm_schedule_templ_tbl(k).pm_schedule_id := l_pm_row.pm_schedule_id; -- replaced i with k for bug 7230256
672 k := k + 1; --for bug 7230256
673
674 end if;
675
676 end loop;
677
678 end loop;
679
680 end if;
681 -- End of API body.
682 -- Standard check of p_commit.
683 IF FND_API.To_Boolean( p_commit ) THEN
684 COMMIT WORK;
685 END IF;
686 -- Standard call to get message count and if count is 1, get message info.
687 FND_MSG_PUB.get
688 ( p_msg_index_out => x_msg_count ,
689 p_data => x_msg_data
690 );
691 x_msg_data := substr(x_msg_data,1,2000);
692 EXCEPTION
693 WHEN FND_API.G_EXC_ERROR THEN
694 ROLLBACK TO instantiate_PM_Defs_pvt;
695 x_return_status := FND_API.G_RET_STS_ERROR ;
696 FND_MSG_PUB.get
697 ( p_msg_index_out => x_msg_count ,
698 p_data => x_msg_data
699 );
700 x_msg_data := substr(x_msg_data,1,2000);
701 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
702 ROLLBACK TO instantiate_PM_Defs_pvt;
703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
704 FND_MSG_PUB.get
705 ( p_msg_index_out => x_msg_count ,
706 p_data => x_msg_data
707 );
708 x_msg_data := substr(x_msg_data,1,2000);
709 WHEN OTHERS THEN
710 ROLLBACK TO instantiate_PM_Defs_pvt;
711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
712 IF FND_MSG_PUB.Check_Msg_Level
713 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
714 THEN
715 FND_MSG_PUB.Add_Exc_Msg
716 ( G_PKG_NAME ,
717 l_api_name
718 );
719 END IF;
720 FND_MSG_PUB.get
721 ( p_msg_index_out => x_msg_count ,
722 p_data => x_msg_data
723 );
724 x_msg_data := substr(x_msg_data,1,2000);
725 END instantiate_PM_Defs;
726
727
728
729
730 PROCEDURE create_PM_def
731 ( p_api_version IN NUMBER ,
732 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
733 ,
734 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
735 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
736 x_return_status OUT NOCOPY VARCHAR2
737 ,
738 x_msg_count OUT NOCOPY NUMBER ,
739 x_msg_data OUT NOCOPY VARCHAR2 ,
740 p_pm_schedule_rec IN pm_scheduling_rec_type,
741 p_pm_activities_tbl IN pm_activities_grp_tbl_type,
742 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type,
743 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
744 p_pm_list_date_rules_tbl IN pm_rule_tbl_type,
745 x_new_pm_schedule_id OUT NOCOPY NUMBER -- this is the pm_schedule_id of the newly created pm schedule
746 ) is
747
748
749 l_api_name CONSTANT VARCHAR2(30) := 'create_PM_def';
750 l_api_version CONSTANT NUMBER := 1.0;
751 l_pm_schedule_id number;
752 i number;
753 l_validated boolean;
754 l_reason_failed varchar2(30);
755 l_rule_id number;
756 x_error_segments NUMBER;
757 x_error_message VARCHAR2(5000);
758 l_act_names varchar2(500);
759 l_message varchar2(30);
760
761 BEGIN
762 -- Standard Start of API savepoint
763 SAVEPOINT create_PM_def_pub;
764 -- Standard call to check for call compatibility.
765 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
766 p_api_version ,
767 l_api_name ,
768 G_PKG_NAME )
769 THEN
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 END IF;
772 -- Initialize message list if p_init_msg_list is set to TRUE.
773 IF FND_API.to_Boolean( p_init_msg_list ) THEN
774 FND_MSG_PUB.initialize;
775 END IF;
776 -- Initialize API return status to success
777 x_return_status := FND_API.G_RET_STS_SUCCESS;
778
779
780 -- API body
781
782
783
784 -- validate header and all rules
785 l_validated:=validate_pm_header_and_rules
786 (
787 p_pm_schedule_rec,
788 p_pm_day_interval_rules_tbl,
789 p_pm_runtime_rules_tbl,
790 p_pm_list_date_rules_tbl,
791 l_reason_failed
792 );
793
794 if (not l_validated) then
795 FND_MESSAGE.SET_NAME ('EAM', l_reason_failed);
796 FND_MSG_PUB.Add;
797 RAISE FND_API.G_EXC_ERROR;
798 end if;
799
800 l_validated := validate_pm_activities
801 (
802 p_pm_activities_tbl,
803 p_pm_runtime_rules_tbl,
804 p_pm_schedule_rec,
805 l_reason_failed,
806 l_message,
807 l_act_names
808 );
809 if (not l_validated) then
810 FND_MESSAGE.SET_NAME ('EAM', l_reason_failed);
811 FND_MSG_PUB.Add;
812 RAISE FND_API.G_EXC_ERROR;
813 end if;
814 /* Validation generate_next_work_order */
815 if ( not (p_pm_schedule_rec.generate_next_work_order = 'Y'
816 OR p_pm_schedule_rec.generate_next_work_order = 'N'
817 OR p_pm_schedule_rec.generate_next_work_order is null)) then
818
819 FND_MESSAGE.SET_NAME ('EAM', 'INVALID_GENERATE_NEXT_WORK_ORDER');
820 FND_MSG_PUB.Add;
821 RAISE FND_API.G_EXC_ERROR;
822
823 end if;
824
825 /* Validating the DFF */
826
827 l_validated := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field (
828 p_app_short_name => 'EAM',
829 p_desc_flex_name => 'EAM_PM_SCHEDULE',
830 p_ATTRIBUTE_CATEGORY => p_pm_schedule_rec.attribute_category ,
831 p_ATTRIBUTE1 => p_pm_schedule_rec.attribute1 ,
832 p_ATTRIBUTE2 => p_pm_schedule_rec.attribute2 ,
833 p_ATTRIBUTE3 => p_pm_schedule_rec.attribute3 ,
834 p_ATTRIBUTE4 => p_pm_schedule_rec.attribute4 ,
835 p_ATTRIBUTE5 => p_pm_schedule_rec.attribute5 ,
836 p_ATTRIBUTE6 => p_pm_schedule_rec.attribute6 ,
837 p_ATTRIBUTE7 => p_pm_schedule_rec.attribute7 ,
838 p_ATTRIBUTE8 => p_pm_schedule_rec.attribute8 ,
839 p_ATTRIBUTE9 => p_pm_schedule_rec.attribute9 ,
840 p_ATTRIBUTE10 => p_pm_schedule_rec.attribute10 ,
841 p_ATTRIBUTE11 => p_pm_schedule_rec.attribute11 ,
842 p_ATTRIBUTE12 => p_pm_schedule_rec.attribute12 ,
843 p_ATTRIBUTE13 => p_pm_schedule_rec.attribute13 ,
844 p_ATTRIBUTE14 => p_pm_schedule_rec.attribute14 ,
845 p_ATTRIBUTE15 => p_pm_schedule_rec.attribute15 ,
846 x_error_segments => x_error_segments ,
847 x_error_message => x_error_message);
848
849 IF (not l_validated) THEN
850 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_DESC_FLEX');
851 FND_MESSAGE.SET_TOKEN('ERROR_MSG', x_error_message);
852 FND_MSG_PUB.Add;
853 RAISE FND_API.G_EXC_ERROR;
854 END IF;
855
856 select eam_pm_schedulings_s.nextval into l_pm_schedule_id from dual;
857
858
859 insert into eam_pm_schedulings (
860 PM_SCHEDULE_ID,
861 ACTIVITY_ASSOCIATION_ID,
862 NON_SCHEDULED_FLAG,
863 FROM_EFFECTIVE_DATE,
864 TO_EFFECTIVE_DATE,
865 RESCHEDULING_POINT,
866 LEAD_TIME,
867 ATTRIBUTE_CATEGORY,
868 ATTRIBUTE1,
869 ATTRIBUTE2 ,
870 ATTRIBUTE3 ,
871 ATTRIBUTE4 ,
872 ATTRIBUTE5 ,
873 ATTRIBUTE6 ,
874 ATTRIBUTE7 ,
875 ATTRIBUTE8 ,
876 ATTRIBUTE9 ,
877 ATTRIBUTE10 ,
878 ATTRIBUTE11 ,
879 ATTRIBUTE12 ,
880 ATTRIBUTE13 ,
881 ATTRIBUTE14 ,
882 ATTRIBUTE15 ,
883 DAY_TOLERANCE ,
884 SOURCE_CODE ,
885 SOURCE_LINE ,
886 DEFAULT_IMPLEMENT,
887 WHICHEVER_FIRST ,
888 INCLUDE_MANUAL ,
889 SET_NAME_ID,
890 SCHEDULING_METHOD_CODE,
891 TYPE_CODE,
892 NEXT_SERVICE_START_DATE,
893 NEXT_SERVICE_END_DATE,
894 SOURCE_TMPL_ID ,
895 AUTO_INSTANTIATION_FLAG,
896 NAME ,
897 TMPL_FLAG ,
898 created_by,
899 creation_date,
900 last_update_login,
901 last_updated_by,
902 last_update_date,
903 GENERATE_WO_STATUS,
904 INTERVAL_PER_CYCLE,
905 CURRENT_CYCLE,
906 CURRENT_SEQ,
907 CURRENT_WO_SEQ,
908 BASE_DATE,
909 BASE_READING,
910 EAM_LAST_CYCLIC_ACT,
911 MAINTENANCE_OBJECT_ID,
912 MAINTENANCE_OBJECT_TYPE,
913 /* added for PM Reviewer -- start-- */
914 LAST_REVIEWED_DATE,
915 Last_reviewed_by
916 /* ---PM reviewer--- end---- */,
917 GENERATE_NEXT_WORK_ORDER,
918 Planner_Maintenance /*WO Defaults ER*/ )
919 values (
920 l_pm_schedule_id,
921 p_pm_schedule_rec.ACTIVITY_ASSOCIATION_ID,
922 p_pm_schedule_rec.NON_SCHEDULED_FLAG,
923 p_pm_schedule_rec.FROM_EFFECTIVE_DATE,
924 p_pm_schedule_rec.TO_EFFECTIVE_DATE,
925 p_pm_schedule_rec.RESCHEDULING_POINT,
926 p_pm_schedule_rec.LEAD_TIME,
927 p_pm_schedule_rec.ATTRIBUTE_CATEGORY,
928 p_pm_schedule_rec.ATTRIBUTE1,
929 p_pm_schedule_rec.ATTRIBUTE2 ,
930 p_pm_schedule_rec.ATTRIBUTE3 ,
931 p_pm_schedule_rec.ATTRIBUTE4 ,
932 p_pm_schedule_rec.ATTRIBUTE5 ,
933 p_pm_schedule_rec.ATTRIBUTE6 ,
934 p_pm_schedule_rec.ATTRIBUTE7 ,
935 p_pm_schedule_rec.ATTRIBUTE8 ,
936 p_pm_schedule_rec.ATTRIBUTE9 ,
937 p_pm_schedule_rec.ATTRIBUTE10 ,
938 p_pm_schedule_rec.ATTRIBUTE11 ,
939 p_pm_schedule_rec.ATTRIBUTE12 ,
940 p_pm_schedule_rec.ATTRIBUTE13 ,
941 p_pm_schedule_rec.ATTRIBUTE14 ,
942 p_pm_schedule_rec.ATTRIBUTE15 ,
943 p_pm_schedule_rec.DAY_TOLERANCE ,
944 p_pm_schedule_rec.SOURCE_CODE ,
945 p_pm_schedule_rec.SOURCE_LINE ,
946 p_pm_schedule_rec.DEFAULT_IMPLEMENT,
947 p_pm_schedule_rec.WHICHEVER_FIRST ,
948 p_pm_schedule_rec.INCLUDE_MANUAL ,
949 p_pm_schedule_rec.SET_NAME_ID,
950 p_pm_schedule_rec.SCHEDULING_METHOD_CODE ,
951 p_pm_schedule_rec.TYPE_CODE,
952 p_pm_schedule_rec.NEXT_SERVICE_START_DATE,
953 p_pm_schedule_rec.NEXT_SERVICE_END_DATE,
954 p_pm_schedule_rec.SOURCE_TMPL_ID ,
955 p_pm_schedule_rec.AUTO_INSTANTIATION_FLAG,
956 p_pm_schedule_rec.NAME ,
957 p_pm_schedule_rec.TMPL_FLAG ,
958 fnd_global.user_id,
959 sysdate,
960 fnd_global.login_id,
961 fnd_global.user_id,
962 sysdate,
963 p_pm_schedule_rec.GENERATE_WO_STATUS ,
964 p_pm_schedule_rec.INTERVAL_PER_CYCLE ,
965 p_pm_schedule_rec.CURRENT_CYCLE ,
966 p_pm_schedule_rec.CURRENT_SEQ ,
967 p_pm_schedule_rec.CURRENT_WO_SEQ ,
968 p_pm_schedule_rec.BASE_DATE ,
969 p_pm_schedule_rec.BASE_READING ,
970 p_pm_schedule_rec.EAM_LAST_CYCLIC_ACT ,
971 p_pm_schedule_rec.MAINTENANCE_OBJECT_ID ,
972 p_pm_schedule_rec.MAINTENANCE_OBJECT_TYPE,
973 /* added for PM Reviewer -- start-- */
974 p_pm_schedule_rec.LAST_REVIEWED_DATE,
975 p_pm_schedule_rec.Last_reviewed_by,
976 /* ---PM reviewer--- end---- */
977 p_pm_schedule_rec.generate_next_work_order,
978 p_pm_schedule_rec.planner_maintenance );
979
980 i:=1;
981 while (p_pm_activities_tbl.exists(i)) loop
982 insert into eam_pm_activities(
983 pm_schedule_id,
984 activity_association_id,
985 interval_multiple,
986 allow_repeat_in_cycle,
987 day_tolerance,
988 created_by,
989 creation_date,
990 last_update_login,
991 last_update_date,
992 last_updated_by
993 )
994 values(
995 l_pm_schedule_id,
996 p_pm_activities_tbl(i).activity_association_id,
997 p_pm_activities_tbl(i).interval_multiple,
998 p_pm_activities_tbl(i).allow_repeat_in_cycle,
999 p_pm_activities_tbl(i).day_tolerance,
1000 fnd_global.user_id,
1001 sysdate,
1002 fnd_global.login_id,
1003 sysdate ,
1004 fnd_global.user_id
1005 );
1006 i:=i+1;
1007 end loop;
1008
1009 /*FP of R12 bug 9744000 start */
1010 eam_pmdef_pub.update_pm_last_cyclic_act
1011 ( p_api_version => 1.0 ,
1012 p_init_msg_list => 'F' ,
1013 p_commit => 'F' ,
1014 p_validation_level => 100 ,
1015 x_return_status => x_return_status,
1016 x_msg_count => x_msg_count,
1017 x_msg_data => x_msg_data ,
1018 p_pm_schedule_id => l_pm_schedule_id
1019 );
1020
1021 if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1022 RAISE FND_API.G_EXC_ERROR;
1023 end if;
1024 /*bug 9744000 end*/
1025
1026
1027 i:=1;
1028 while (p_pm_day_interval_rules_tbl.exists(i)) loop
1029
1030 insert into eam_pm_scheduling_rules
1031 (RULE_ID,
1032 PM_SCHEDULE_ID,
1033 RULE_TYPE,
1034 DAY_INTERVAL,
1035 METER_ID ,
1036 RUNTIME_INTERVAL,
1037 CREATED_BY ,
1038 CREATION_DATE ,
1039 LAST_UPDATE_LOGIN,
1040 LAST_UPDATE_DATE,
1041 LAST_UPDATED_BY,
1042 EFFECTIVE_READING_FROM ,
1043 EFFECTIVE_READING_TO ,
1044 EFFECTIVE_DATE_FROM ,
1045 EFFECTIVE_DATE_TO ,
1046 LIST_DATE ,
1047 LIST_DATE_DESC
1048 )
1049 values
1050 (eam_pm_scheduling_rules_s.nextval,
1051 l_pm_schedule_id,
1052 p_pm_day_interval_rules_tbl(i).RULE_TYPE,
1053 p_pm_day_interval_rules_tbl(i).DAY_INTERVAL,
1054 p_pm_day_interval_rules_tbl(i).METER_ID ,
1055 p_pm_day_interval_rules_tbl(i).RUNTIME_INTERVAL,
1056 fnd_global.user_id,
1057 sysdate,
1058 fnd_global.login_id,
1059 sysdate ,
1060 fnd_global.user_id,
1061 p_pm_day_interval_rules_tbl(i).EFFECTIVE_READING_FROM ,
1062 p_pm_day_interval_rules_tbl(i).EFFECTIVE_READING_TO ,
1063 p_pm_day_interval_rules_tbl(i).EFFECTIVE_DATE_FROM ,
1064 p_pm_day_interval_rules_tbl(i).EFFECTIVE_DATE_TO ,
1065 p_pm_day_interval_rules_tbl(i).LIST_DATE ,
1066 p_pm_day_interval_rules_tbl(i).LIST_DATE_DESC
1067 );
1068
1069 i:=i+1;
1070 end loop;
1071
1072 i:=1;
1073 while (p_pm_runtime_rules_tbl.exists(i)) loop
1074
1075 insert into eam_pm_scheduling_rules
1076 ( RULE_ID,
1077 PM_SCHEDULE_ID,
1078 RULE_TYPE,
1079 DAY_INTERVAL,
1080 METER_ID ,
1081 RUNTIME_INTERVAL,
1082 CREATED_BY ,
1083 CREATION_DATE ,
1084 LAST_UPDATE_LOGIN,
1085 LAST_UPDATE_DATE,
1086 LAST_UPDATED_BY,
1087 EFFECTIVE_READING_FROM ,
1088 EFFECTIVE_READING_TO ,
1089 EFFECTIVE_DATE_FROM ,
1090 EFFECTIVE_DATE_TO ,
1091 LIST_DATE ,
1092 LIST_DATE_DESC
1093 )
1094 values
1095 (eam_pm_scheduling_rules_s.nextval,
1096 l_pm_schedule_id,
1097 p_pm_runtime_rules_tbl(i).RULE_TYPE,
1098 p_pm_runtime_rules_tbl(i).DAY_INTERVAL,
1099 p_pm_runtime_rules_tbl(i).METER_ID ,
1100 p_pm_runtime_rules_tbl(i).RUNTIME_INTERVAL,
1101 fnd_global.user_id,
1102 sysdate,
1103 fnd_global.login_id,
1104 sysdate ,
1105 fnd_global.user_id,
1106 p_pm_runtime_rules_tbl(i).EFFECTIVE_READING_FROM ,
1107 p_pm_runtime_rules_tbl(i).EFFECTIVE_READING_TO ,
1108 p_pm_runtime_rules_tbl(i).EFFECTIVE_DATE_FROM ,
1109 p_pm_runtime_rules_tbl(i).EFFECTIVE_DATE_TO ,
1110 p_pm_runtime_rules_tbl(i).LIST_DATE ,
1111 p_pm_runtime_rules_tbl(i).LIST_DATE_DESC
1112 );
1113 i:=i+1;
1114 end loop;
1115
1116 /*FP of R12 bug 9744000 start*/
1117 if(p_pm_runtime_rules_tbl.count <> 0) then
1118 eam_pmdef_pub.update_pm_last_service_reading
1119 ( p_api_version => 1.0 ,
1120 p_init_msg_list => 'F' ,
1121 p_commit => 'F' ,
1122 p_validation_level => 100 ,
1123 x_return_status => x_return_status,
1124 x_msg_count => x_msg_count,
1125 x_msg_data => x_msg_data,
1126 p_pm_schedule_id => l_pm_schedule_id
1127 );
1128
1129 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1130 RAISE FND_API.G_EXC_ERROR;
1131 end if;
1132 end if;
1133 /*bug 9744000 end*/
1134
1135 i:=1;
1136 while (p_pm_list_date_rules_tbl.exists(i)) loop
1137
1138 insert into eam_pm_scheduling_rules
1139 ( RULE_ID,
1140 PM_SCHEDULE_ID,
1141 RULE_TYPE,
1142 DAY_INTERVAL,
1143 METER_ID ,
1144 RUNTIME_INTERVAL,
1145 CREATED_BY ,
1146 CREATION_DATE ,
1147 LAST_UPDATE_LOGIN,
1148 LAST_UPDATE_DATE,
1149 LAST_UPDATED_BY,
1150 EFFECTIVE_READING_FROM ,
1151 EFFECTIVE_READING_TO ,
1152 EFFECTIVE_DATE_FROM ,
1153 EFFECTIVE_DATE_TO ,
1154 LIST_DATE ,
1155 LIST_DATE_DESC
1156 )
1157 values
1158 (eam_pm_scheduling_rules_s.nextval,
1159 l_pm_schedule_id,
1160 p_pm_list_date_rules_tbl(i).RULE_TYPE,
1161 p_pm_list_date_rules_tbl(i).DAY_INTERVAL,
1162 p_pm_list_date_rules_tbl(i).METER_ID ,
1163 p_pm_list_date_rules_tbl(i).RUNTIME_INTERVAL,
1164 fnd_global.user_id,
1165 sysdate,
1166 fnd_global.login_id,
1167 sysdate ,
1168 fnd_global.user_id,
1169 p_pm_list_date_rules_tbl(i).EFFECTIVE_READING_FROM ,
1170 p_pm_list_date_rules_tbl(i).EFFECTIVE_READING_TO ,
1171 p_pm_list_date_rules_tbl(i).EFFECTIVE_DATE_FROM ,
1172 p_pm_list_date_rules_tbl(i).EFFECTIVE_DATE_TO ,
1173 p_pm_list_date_rules_tbl(i).LIST_DATE ,
1174 p_pm_list_date_rules_tbl(i).LIST_DATE_DESC
1175 );
1176 i:=i+1;
1177 end loop;
1178 x_new_pm_schedule_id:=l_pm_schedule_id;
1179
1180 -- End of API body.
1181
1182
1183 -- Standard check of p_commit.
1184 IF FND_API.To_Boolean( p_commit ) THEN
1185 COMMIT WORK;
1186 END IF;
1187 -- Standard call to get message count and if count is 1, get message info.
1188 FND_MSG_PUB.get
1189 ( p_msg_index_out => x_msg_count ,
1190 p_data => x_msg_data
1191 );
1192 x_msg_data := substr(x_msg_data,1,2000);
1193 EXCEPTION
1194 WHEN FND_API.G_EXC_ERROR THEN
1195 ROLLBACK TO create_PM_def_pub;
1196 x_return_status := FND_API.G_RET_STS_ERROR ;
1197 FND_MSG_PUB.get
1198 ( p_msg_index_out => x_msg_count ,
1199 p_data => x_msg_data
1200 );
1201 x_msg_data := substr(x_msg_data,1,2000);
1202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1203 ROLLBACK TO create_PM_def_pub;
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1205 FND_MSG_PUB.get
1206 ( p_msg_index_out => x_msg_count ,
1207 p_data => x_msg_data
1208 );
1209 x_msg_data := substr(x_msg_data,1,2000);
1210 WHEN OTHERS THEN
1211 ROLLBACK TO create_PM_def_pub;
1212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1213 IF FND_MSG_PUB.Check_Msg_Level
1214 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1215 THEN
1216 FND_MSG_PUB.Add_Exc_Msg
1217 ( G_PKG_NAME ,
1218 l_api_name
1219 );
1220 END IF;
1221 FND_MSG_PUB.get
1222 ( p_msg_index_out => x_msg_count ,
1223 p_data => x_msg_data
1224 );
1225 x_msg_data := substr(x_msg_data,1,2000);
1226 END create_PM_def;
1227
1228
1229
1230 procedure update_pm_def
1231 ( p_api_version IN NUMBER ,
1232 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1233 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1234 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1235 x_return_status OUT NOCOPY VARCHAR2 ,
1236 x_msg_count OUT NOCOPY NUMBER ,
1237 x_msg_data OUT NOCOPY VARCHAR2 ,
1238 p_pm_schedule_rec IN pm_scheduling_rec_type:=null,
1239 p_pm_activities_tbl IN pm_activities_grp_tbl_type,
1240 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type,
1241 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
1242 p_pm_list_date_rules_tbl IN pm_rule_tbl_type
1243 )
1244 is
1245 l_api_name CONSTANT VARCHAR2(30) :='update_pm_def';
1246 l_api_version CONSTANT NUMBER := 1.0;
1247 l_pm_header pm_scheduling_rec_type;
1248 l_day_interval_counter number;
1249 l_runtime_counter number;
1250 l_list_date_counter number;
1251 l_pm_rule pm_rule_rec_type;
1252 l_selected_day_rules_tbl pm_rule_tbl_type;
1253 l_selected_runtime_rules_tbl pm_rule_tbl_type;
1254 l_selected_list_date_rules_tbl pm_rule_tbl_type;
1255 l_merged_day_rules_tbl pm_rule_tbl_type;
1256 l_merged_runtime_rules_tbl pm_rule_tbl_type;
1257 l_merged_list_date_rules_tbl pm_rule_tbl_type;
1258 l_validated boolean;
1259 l_validated_act boolean;
1260 l_reason_failed varchar2(30);
1261 l_current_rules_tbl pm_rule_tbl_type;
1262 n number;
1263 i number;
1264 l_pm_schedule_id number;
1265 l_rule_id number;
1266 l_act_names varchar2(500);
1267 l_message varchar2(30);
1268
1269 x_error_segments NUMBER;
1270 x_error_message VARCHAR2(5000);
1271
1272
1273 BEGIN
1274 -- Standard Start of API savepoint
1275 SAVEPOINT update_pm_def_pub;
1276 -- Standard call to check for call compatibility.
1277 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1278 p_api_version ,
1279 l_api_name ,
1280 G_PKG_NAME )
1281 THEN
1282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283 END IF;
1284 -- Initialize message list if p_init_msg_list is set to TRUE.
1285 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1286 FND_MSG_PUB.initialize;
1287 END IF;
1288 -- Initialize API return status to success
1289 x_return_status := FND_API.G_RET_STS_SUCCESS;
1290 -- API body
1291
1292 if (p_pm_schedule_rec.pm_schedule_id is null) then
1293 FND_MESSAGE.SET_NAME ('EAM', 'EAM_PM_SCHEDULE_ID_MISSING');
1294 FND_MSG_PUB.Add;
1295 RAISE FND_API.G_EXC_ERROR;
1296 else
1297 l_pm_schedule_id:=p_pm_schedule_rec.pm_schedule_id;
1298 end if;
1299
1300
1301 /* Validation generate_next_work_order */
1302 if ( not (p_pm_schedule_rec.generate_next_work_order = 'Y'
1303 OR p_pm_schedule_rec.generate_next_work_order = 'N'
1304 OR p_pm_schedule_rec.generate_next_work_order is null)) then
1305 FND_MESSAGE.SET_NAME ('EAM', 'INVALID_GENERATE_NEXT_WORK_ORDER');
1306 FND_MSG_PUB.Add;
1307 RAISE FND_API.G_EXC_ERROR;
1308
1309 end if;
1310 /* Validating the DFF */
1311
1312 l_validated := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field (
1313 p_app_short_name => 'EAM',
1314 p_desc_flex_name => 'EAM_PM_SCHEDULE',
1315 p_ATTRIBUTE_CATEGORY => p_pm_schedule_rec.attribute_category ,
1316 p_ATTRIBUTE1 => p_pm_schedule_rec.attribute1 ,
1317 p_ATTRIBUTE2 => p_pm_schedule_rec.attribute2 ,
1318 p_ATTRIBUTE3 => p_pm_schedule_rec.attribute3 ,
1319 p_ATTRIBUTE4 => p_pm_schedule_rec.attribute4 ,
1320 p_ATTRIBUTE5 => p_pm_schedule_rec.attribute5 ,
1321 p_ATTRIBUTE6 => p_pm_schedule_rec.attribute6 ,
1322 p_ATTRIBUTE7 => p_pm_schedule_rec.attribute7 ,
1323 p_ATTRIBUTE8 => p_pm_schedule_rec.attribute8 ,
1324 p_ATTRIBUTE9 => p_pm_schedule_rec.attribute9 ,
1325 p_ATTRIBUTE10 => p_pm_schedule_rec.attribute10 ,
1326 p_ATTRIBUTE11 => p_pm_schedule_rec.attribute11 ,
1327 p_ATTRIBUTE12 => p_pm_schedule_rec.attribute12 ,
1328 p_ATTRIBUTE13 => p_pm_schedule_rec.attribute13 ,
1329 p_ATTRIBUTE14 => p_pm_schedule_rec.attribute14 ,
1330 p_ATTRIBUTE15 => p_pm_schedule_rec.attribute15 ,
1331 x_error_segments => x_error_segments ,
1332 x_error_message => x_error_message);
1333 IF (not l_validated) THEN
1334 FND_MESSAGE.SET_NAME ('EAM', x_error_message);
1335 FND_MSG_PUB.Add;
1336 RAISE FND_API.G_EXC_ERROR;
1337 END IF;
1338
1339
1340
1341
1342 l_validated:=validate_pm_header_and_rules(p_pm_schedule_rec,
1343 p_pm_day_interval_rules_tbl,
1344 p_pm_runtime_rules_tbl,
1345 p_pm_list_date_rules_tbl,
1346 l_reason_failed);
1347 l_validated_act := validate_pm_activities
1348 (
1349 p_pm_activities_tbl,
1350 p_pm_runtime_rules_tbl,
1351 p_pm_schedule_rec,
1352 l_reason_failed,
1353 l_message,
1354 l_act_names
1355 );
1356 if (l_validated and l_validated_act) then
1357
1358 update eam_pm_schedulings set
1359 ACTIVITY_ASSOCIATION_ID=p_pm_schedule_rec.ACTIVITY_ASSOCIATION_ID,
1360 NON_SCHEDULED_FLAG=p_pm_schedule_rec.NON_SCHEDULED_FLAG,
1361 FROM_EFFECTIVE_DATE=p_pm_schedule_rec.FROM_EFFECTIVE_DATE,
1362 TO_EFFECTIVE_DATE=p_pm_schedule_rec.TO_EFFECTIVE_DATE,
1363 RESCHEDULING_POINT=p_pm_schedule_rec.RESCHEDULING_POINT,
1364 LEAD_TIME=p_pm_schedule_rec.LEAD_TIME,
1365 ATTRIBUTE_CATEGORY=p_pm_schedule_rec.ATTRIBUTE_CATEGORY,
1366 ATTRIBUTE1=p_pm_schedule_rec.ATTRIBUTE1,
1367 ATTRIBUTE2=p_pm_schedule_rec.ATTRIBUTE2 ,
1368 ATTRIBUTE3=p_pm_schedule_rec.ATTRIBUTE3 ,
1369 ATTRIBUTE4=p_pm_schedule_rec.ATTRIBUTE4 ,
1370 ATTRIBUTE5=p_pm_schedule_rec.ATTRIBUTE5 ,
1371 ATTRIBUTE6=p_pm_schedule_rec.ATTRIBUTE6 ,
1372 ATTRIBUTE7=p_pm_schedule_rec.ATTRIBUTE7 ,
1373 ATTRIBUTE8=p_pm_schedule_rec.ATTRIBUTE8 ,
1374 ATTRIBUTE9=p_pm_schedule_rec.ATTRIBUTE9 ,
1375 ATTRIBUTE10=p_pm_schedule_rec.ATTRIBUTE10 ,
1376 ATTRIBUTE11=p_pm_schedule_rec.ATTRIBUTE11 ,
1377 ATTRIBUTE12=p_pm_schedule_rec.ATTRIBUTE12 ,
1378 ATTRIBUTE13=p_pm_schedule_rec.ATTRIBUTE13 ,
1379 ATTRIBUTE14=p_pm_schedule_rec.ATTRIBUTE14 ,
1380 ATTRIBUTE15=p_pm_schedule_rec.ATTRIBUTE15 ,
1381 DAY_TOLERANCE =p_pm_schedule_rec.DAY_TOLERANCE ,
1382 SOURCE_CODE=p_pm_schedule_rec.SOURCE_CODE ,
1383 SOURCE_LINE= p_pm_schedule_rec.SOURCE_LINE ,
1384 DEFAULT_IMPLEMENT=p_pm_schedule_rec.DEFAULT_IMPLEMENT,
1385 WHICHEVER_FIRST = p_pm_schedule_rec.WHICHEVER_FIRST ,
1386 INCLUDE_MANUAL = p_pm_schedule_rec.INCLUDE_MANUAL ,
1387 SET_NAME_ID=p_pm_schedule_rec.SET_NAME_ID,
1388 SCHEDULING_METHOD_CODE= p_pm_schedule_rec.SCHEDULING_METHOD_CODE ,
1389 TYPE_CODE=p_pm_schedule_rec.TYPE_CODE,
1390 NEXT_SERVICE_START_DATE=p_pm_schedule_rec.NEXT_SERVICE_START_DATE,
1391 NEXT_SERVICE_END_DATE=p_pm_schedule_rec.NEXT_SERVICE_END_DATE,
1392 SOURCE_TMPL_ID = p_pm_schedule_rec.SOURCE_TMPL_ID,
1393 AUTO_INSTANTIATION_FLAG =p_pm_schedule_rec.AUTO_INSTANTIATION_FLAG,
1394 NAME =p_pm_schedule_rec.NAME,
1395 TMPL_FLAG =p_pm_schedule_rec.TMPL_FLAG,
1396 GENERATE_WO_STATUS = p_pm_schedule_rec.GENERATE_WO_STATUS, -- bug 9823755
1397 CURRENT_CYCLE = p_pm_schedule_rec.CURRENT_CYCLE, -- 13263283
1398 INTERVAL_PER_CYCLE = p_pm_schedule_rec.INTERVAL_PER_CYCLE, -- 13263283
1399 CURRENT_SEQ = p_pm_schedule_rec.CURRENT_SEQ, -- added for bug14641909
1400 LAST_REVIEWED_BY = p_pm_schedule_rec.LAST_REVIEWED_BY,
1401 LAST_REVIEWED_DATE = p_pm_schedule_rec.LAST_REVIEWED_DATE ,
1402 --CREATED_BY=fnd_global.user_id,
1403 --CREATION_DATE=sysdate,
1404 LAST_UPDATE_LOGIN=fnd_global.login_id,
1405 LAST_UPDATED_BY=fnd_global.user_id,
1406 LAST_UPDATE_DATE=sysdate ,
1407 generate_next_work_order =p_pm_schedule_rec.generate_next_work_order,
1408 BASE_DATE = p_pm_schedule_rec.BASE_DATE, -- added for bug13906069
1409 BASE_READING = p_pm_schedule_rec.BASE_READING, -- bug 13906069
1410 -- CURRENT_SEQ = p_pm_schedule_rec.CURRENT_SEQ, -- added for bug 14664020 -- removed for bug 16568661
1411 PLANNER_MAINTENANCE = p_pm_schedule_rec.PLANNER_MAINTENANCE -- WO defaults ER
1412 where PM_SCHEDULE_ID=l_pm_schedule_id;
1413
1414 delete from eam_pm_activities
1415 where pm_schedule_id=l_pm_schedule_id;
1416
1417 n:=1;
1418 while (p_pm_activities_tbl.exists(n)) loop /*FP of 7030271*/
1419
1420 insert into eam_pm_activities(
1421 pm_schedule_id,
1422 activity_association_id,
1423 interval_multiple,
1424 allow_repeat_in_cycle,
1425 day_tolerance,
1426 created_by,
1427 creation_date,
1428 last_update_login,
1429 last_update_date,
1430 last_updated_by
1431 )
1432 values(
1433 l_pm_schedule_id,
1434 p_pm_activities_tbl(n).activity_association_id,
1435 p_pm_activities_tbl(n).interval_multiple,
1436 p_pm_activities_tbl(n).allow_repeat_in_cycle,
1437 p_pm_activities_tbl(n).day_tolerance,
1438 fnd_global.user_id,
1439 sysdate,
1440 fnd_global.login_id,
1441 sysdate ,
1442 fnd_global.user_id
1443 );
1444 n:=n+1;
1445
1446 end loop;
1447
1448 /*bug 9744000 start */
1449 eam_pmdef_pub.update_pm_last_cyclic_act
1450 ( p_api_version => 1.0 ,
1451 p_init_msg_list => 'F' ,
1452 p_commit => 'F' ,
1453 p_validation_level => 100 ,
1454 x_return_status => x_return_status,
1455 x_msg_count => x_msg_count,
1456 x_msg_data => x_msg_data ,
1457 p_pm_schedule_id => l_pm_schedule_id
1458 );
1459
1460 if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1461 RAISE FND_API.G_EXC_ERROR;
1462 end if;
1463 /*bug 9744000 end*/
1464
1465 delete from eam_pm_scheduling_rules
1466 where pm_schedule_id=l_pm_schedule_id;
1467
1468 for i in 1..3 loop
1469 if (i=1) then
1470 l_current_rules_tbl:=p_pm_day_interval_rules_tbl;
1471 elsif (i=2) then
1472 l_current_rules_tbl:=p_pm_runtime_rules_tbl;
1473 elsif (i=3) then
1474 l_current_rules_tbl:=p_pm_list_date_rules_tbl;
1475 end if;
1476 n:=1;
1477 while (l_current_rules_tbl.exists(n)) loop
1478
1479 select eam_pm_scheduling_rules_s.nextval into l_rule_id from dual; --Bug#5453536
1480
1481 insert into eam_pm_scheduling_rules
1482 ( rule_id,
1483 PM_SCHEDULE_ID,
1484 RULE_TYPE,
1485 DAY_INTERVAL,
1486 METER_ID ,
1487 RUNTIME_INTERVAL,
1488 CREATED_BY ,
1489 CREATION_DATE ,
1490 LAST_UPDATE_LOGIN,
1491 LAST_UPDATE_DATE,
1492 LAST_UPDATED_BY,
1493 EFFECTIVE_READING_FROM ,
1494 EFFECTIVE_READING_TO ,
1495 EFFECTIVE_DATE_FROM ,
1496 EFFECTIVE_DATE_TO ,
1497 LIST_DATE ,
1498 LIST_DATE_DESC
1499 )
1500 values
1501 (l_rule_id,
1502 l_pm_schedule_id,
1503 l_current_rules_tbl(n).RULE_TYPE,
1504 l_current_rules_tbl(n).DAY_INTERVAL,
1505 l_current_rules_tbl(n).METER_ID ,
1506 l_current_rules_tbl(n).RUNTIME_INTERVAL,
1507 fnd_global.user_id,
1508 sysdate,
1509 fnd_global.login_id,
1510 sysdate ,
1511 fnd_global.user_id,
1512 l_current_rules_tbl(n).EFFECTIVE_READING_FROM ,
1513 l_current_rules_tbl(n).EFFECTIVE_READING_TO ,
1514 l_current_rules_tbl(n).EFFECTIVE_DATE_FROM ,
1515 l_current_rules_tbl(n).EFFECTIVE_DATE_TO ,
1516 l_current_rules_tbl(n).LIST_DATE ,
1517 l_current_rules_tbl(n).LIST_DATE_DESC
1518 );
1519 n:=n+1;
1520 end loop; -- end while loop that loops through each record
1521 end loop; -- end for loop that loop through 3 tables
1522
1523 /*FP of R12 bug 9744000 start*/
1524 if(p_pm_runtime_rules_tbl.count <> 0) then
1525 eam_pmdef_pub.update_pm_last_service_reading
1526 ( p_api_version => 1.0 ,
1527 p_init_msg_list => 'F' ,
1528 p_commit => 'F' ,
1529 p_validation_level => 100 ,
1530 x_return_status => x_return_status,
1531 x_msg_count => x_msg_count,
1532 x_msg_data => x_msg_data,
1533 p_pm_schedule_id => l_pm_schedule_id
1534 );
1535
1536 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1537 RAISE FND_API.G_EXC_ERROR;
1538 end if;
1539 end if;
1540 /*bug 9744000 end*/
1541
1542 else -- rules did not validate
1543 FND_MESSAGE.SET_NAME ('EAM', l_reason_failed);
1544 FND_MSG_PUB.Add;
1545 RAISE FND_API.G_EXC_ERROR;
1546 end if;
1547
1548 -- End of API body.
1549 -- Standard check of p_commit.
1550 IF FND_API.To_Boolean( p_commit ) THEN
1551 COMMIT WORK;
1552 END IF;
1553 -- Standard call to get message count and if count is 1, get message info.
1554 FND_MSG_PUB.get
1555 ( p_msg_index_out => x_msg_count ,
1556 p_data => x_msg_data
1557 );
1558 x_msg_data := substr(x_msg_data,1,2000);
1559 EXCEPTION
1560 WHEN FND_API.G_EXC_ERROR THEN
1561 ROLLBACK TO update_pm_def_pub;
1562 x_return_status := FND_API.G_RET_STS_ERROR ;
1563 FND_MSG_PUB.get
1564 ( p_msg_index_out => x_msg_count ,
1565 p_data => x_msg_data
1566 );
1567 x_msg_data := substr(x_msg_data,1,2000);
1568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1569 ROLLBACK TO update_pm_def_pub;
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1571 FND_MSG_PUB.get
1572 ( p_msg_index_out => x_msg_count ,
1573 p_data => x_msg_data
1574 );
1575 x_msg_data := substr(x_msg_data,1,2000);
1576 WHEN OTHERS THEN
1577 ROLLBACK TO update_pm_def_pub;
1578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1579 IF FND_MSG_PUB.Check_Msg_Level
1580 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1581 THEN
1582 FND_MSG_PUB.Add_Exc_Msg
1583 ( G_PKG_NAME ,
1584 l_api_name
1585 );
1586 END IF;
1587 FND_MSG_PUB.get
1588 ( p_msg_index_out => x_msg_count ,
1589 p_data => x_msg_data
1590 );
1591 x_msg_data := substr(x_msg_data,1,2000);
1592 END update_PM_def;
1593
1594
1595
1596 function validate_pm_header
1597 (
1598 p_pm_schedule_rec IN pm_scheduling_rec_type,
1599 x_reason_failed OUT NOCOPY varchar2
1600 ) return BOOLEAN
1601 is
1602
1603 l_count number;
1604 l_asset_activity_id number;
1605 l_last_service_start_date date;
1606 l_last_service_end_date date;
1607 l_pm_schedule_id number;
1608 l_lookup_type mfg_lookups.lookup_type%type;
1609 l_status_id number;
1610
1611
1612 begin
1613 -- Check all the "not null" fields
1614 if (p_pm_schedule_rec.rescheduling_point is null
1615 or p_pm_schedule_rec.set_name_id is null
1616 or p_pm_schedule_rec.type_code is null
1617 or p_pm_schedule_rec.scheduling_method_code is null
1618 or p_pm_schedule_rec.whichever_first is null
1619 or p_pm_schedule_rec.name is null
1620 or p_pm_schedule_rec.generate_wo_status is null
1621 or p_pm_schedule_rec.interval_per_cycle is null
1622 or p_pm_schedule_rec.maintenance_object_id is null
1623 or p_pm_schedule_rec.maintenance_object_type is null)
1624 then
1625 x_reason_failed:='EAM_PM_HD_NOT_ENOUGH_PARAM';
1626 return false;
1627 end if;
1628
1629
1630 -- default_implement(Y/N), whichever_first(Y/N), include_manual(Y/N), type_code(list_dates, rule_based ) scheduling_method_code(forward, backward)
1631
1632 if (p_pm_schedule_rec.default_implement is not null
1633 and p_pm_schedule_rec.default_implement <> 'Y'
1634 and p_pm_schedule_rec.default_implement <> 'N') then
1635 x_reason_failed:='EAM_PM_HD_INVALID_DEFAULT_IMPL';
1636 return false;
1637 end if;
1638
1639 /* added for PM Reviewer -- start-- */
1640
1641 if( (p_pm_schedule_rec.Last_reviewed_by is null and p_pm_schedule_rec.Last_reviewed_date is not null)
1642 or (p_pm_schedule_rec.Last_reviewed_by is not null and p_pm_schedule_rec.Last_reviewed_date is null)) then
1643 x_reason_failed:='EAM_PM_REVIEW_BY_DATE';
1644 return false;
1645 else
1646 if (p_pm_schedule_rec.Last_reviewed_by is not null and p_pm_schedule_rec.Last_reviewed_date is null) then
1647 if(p_pm_schedule_rec.Last_reviewed_date<>trunc(sysdate)) then
1648 x_reason_failed:='EAM_PM_REVIEW_DATE_BY';
1649 return false;
1650 end if ;
1651 end if;
1652 end if;
1653
1654
1655 /* ---PM reviewer--- end---- */
1656
1657 if (p_pm_schedule_rec.whichever_first is not null
1658 and p_pm_schedule_rec.whichever_first <> 'Y'
1659 and p_pm_schedule_rec.whichever_first <> 'N') then
1660 x_reason_failed:='EAM_PM_HD_INVALID_WHICHEVER';
1661 return false;
1662 end if;
1663
1664 if (p_pm_schedule_rec.include_manual is not null
1665 and p_pm_schedule_rec.include_manual <> 'Y'
1666 and p_pm_schedule_rec.include_manual <> 'N') then
1667 x_reason_failed:='EAM_PM_HD_INVALID_INC_MANUAL';
1668 return false;
1669 end if;
1670
1671
1672 /* Bug # 3840702 : Need to replace literals by bind variable */
1673 l_lookup_type := 'EAM_PM_TYPE';
1674 select count(*) into l_count
1675 from mfg_lookups
1676 where lookup_type = l_lookup_type
1677 and lookup_code=p_pm_schedule_rec.type_code;
1678
1679 if (l_count=0) then
1680 x_reason_failed:='EAM_PM_HD_INVALID_PM_TYPE';
1681 return false;
1682 end if;
1683
1684 l_lookup_type := 'EAM_PM_SCHEDULING_METHOD';
1685 select count(*) into l_count
1686 from mfg_lookups
1687 where lookup_type = l_lookup_type
1688 and lookup_code=p_pm_schedule_rec.scheduling_method_code;
1689
1690 if (l_count=0) then
1691 x_reason_failed:='EAM_PM_HD_INVALID_SCH_METHOD';
1692 return false;
1693 end if;
1694
1695 -- header validation: when it's not a template, auto_instantiation flag must be 'N' or null
1696 if (p_pm_schedule_rec.tmpl_flag='N' and p_pm_schedule_rec.auto_instantiation_flag='Y') then
1697 x_reason_failed:='EAM_PM_HD_NOT_TMPL_INST';
1698 return false;
1699 end if;
1700
1701 -- header validation: effective to > effective from
1702 if (p_pm_schedule_rec.from_effective_date is not null
1703 and p_pm_schedule_rec.to_effective_date is not null
1704 and p_pm_schedule_rec.from_effective_date > p_pm_schedule_rec.to_effective_date)
1705 then
1706 x_reason_failed:='EAM_PM_HD_INVALID_DATES';
1707 return false;
1708 end if;
1709
1710 -- header validation: activity association id is valid
1711 -- header validation: header effective from / to within asset activity effective from / to
1712
1713 -- header validation: Check that the asset activity has last service reading start/end date.
1714 -- header validation : Check that the set name is valid
1715 -- header validation : set name effective end date > sysdate
1716 -- header validation: pm start and end date is within effective dates of set name
1717
1718 select count(*) into l_count
1719 from eam_pm_set_names
1720 where set_name_id = p_pm_schedule_rec.set_name_id;
1721
1722 if (l_count=0) then
1723 x_reason_failed:='EAM_PM_HD_INVALID_SET_NAME';
1724 return false;
1725 end if;
1726
1727
1728
1729 -- set name is unique for this asset activity association and for this type of tmpl_flag
1730 -- (at most one template and one definition for each asset activity association)
1731
1732 /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
1733
1734 -- header validation: there can be only one default
1735 /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
1736 -- header validation: PM Name must be unique
1737 /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
1738 BEGIN
1739 SELECT pm_schedule_id into l_pm_schedule_id
1740 FROM eam_pm_schedulings
1741 WHERE name=p_pm_schedule_rec.name
1742 AND tmpl_flag=p_pm_schedule_rec.tmpl_flag;
1743
1744 if (p_pm_schedule_rec.pm_schedule_id is null) then
1745 x_reason_failed:='EAM_PM_HD_NAME_NOT_UNIQUE';
1746 return false;
1747 else
1748 if (l_pm_schedule_id <> p_pm_schedule_rec.pm_schedule_id) then
1749 x_reason_failed:='EAM_PM_HD_NAME_NOT_UNIQUE';
1750 return false;
1751 end if;
1752 end if;
1753 EXCEPTION
1754 WHEN NO_DATA_FOUND THEN
1755 null;
1756 WHEN TOO_MANY_ROWS THEN
1757 x_reason_failed:='EAM_PM_HD_NAME_NOT_UNIQUE';
1758 return false;
1759 END;
1760
1761 --For list dates pm schedule cyclic attributes should have default values
1762 -- Bug 10334825 . Commented validation for Current Interval Count as per PM comment .
1763 if p_pm_schedule_rec.TYPE_CODE = 20 and
1764 ( -- p_pm_schedule_rec.current_seq > 0 or
1765 p_pm_schedule_rec.interval_per_cycle >1
1766 --or p_pm_schedule_rec.current_cycle > 1 commented for bug 12882041 (12.0.6 bug 12310725)
1767 ) then
1768
1769 x_reason_failed := 'EAM_PM_HD_LIST_DATES_VLD';
1770 return false;
1771
1772 end if;
1773
1774
1775 --New options of Base Date and meter are not applicable for list dates schedule type
1776
1777 if p_pm_schedule_rec.type_code = 20 and p_pm_schedule_rec.rescheduling_point in (5,6) then
1778 x_reason_failed:='EAM_PM_HD_SCHED_TYPE_NA';
1779 return false;
1780 end if;
1781
1782 --validation for current interval count cannot be greater than interval per cycle
1783
1784 if p_pm_schedule_rec.current_seq > p_pm_schedule_rec.interval_per_cycle then
1785 x_reason_failed := 'EAM_PM_HD_CURRENT_SEQ';
1786 return false;
1787 end if;
1788
1789 --validation for generate wo status
1790
1791 begin
1792
1793 SELECT status_id into l_status_id
1794 FROM EAM_WO_STATUSES_V
1795 WHERE ENABLED_FLAG='Y' AND SYSTEM_STATUS IN (1,3,6,17)
1796 and status_id=p_pm_schedule_rec.generate_wo_status;
1797
1798 exception when no_data_found then
1799 x_reason_failed := 'EAM_PM_HD_INVALID_WO_STATUS';
1800 return false;
1801 when too_many_rows then
1802 null;
1803 end;
1804
1805 return true;
1806
1807 end;
1808
1809
1810 function validate_pm_day_interval_rule
1811 (
1812 p_pm_rule_rec IN pm_rule_rec_type,
1813 x_reason_failed OUT NOCOPY varchar2
1814 ) return BOOLEAN
1815 is
1816 begin
1817
1818 -- rule type is day interval rule
1819 if (p_pm_rule_rec.rule_type is null or p_pm_rule_rec.rule_type<>1) then
1820 x_reason_failed:='EAM_PM_DAY_WRONG_RULE_TYPE';
1821 return false;
1822 end if;
1823
1824 -- day interval is not null
1825 if (p_pm_rule_rec.day_interval is null) then
1826 x_reason_failed:='EAM_PM_DAY_NO_DAY_INTERVAL';
1827 return false;
1828 end if;
1829
1830 -- meter id, runtime_interval, effective_reading_from, and effective_reading_to, list_date, and list_date_desc must be null
1831
1832 if (p_pm_rule_rec.meter_id is not null
1833 or p_pm_rule_rec.runtime_interval is not null
1834 or p_pm_rule_rec.effective_reading_from is not null
1835 or p_pm_rule_rec.effective_reading_to is not null
1836 or p_pm_rule_rec.list_date is not null
1837 or p_pm_rule_rec.list_date_desc is not null)
1838 then
1839 x_reason_failed:='EAM_PM_DAY_TOO_MANY_PARAMS';
1840 return false;
1841 end if;
1842
1843 -- effective to > effective from
1844
1845 if (p_pm_rule_rec.effective_date_from is not null
1846 and p_pm_rule_rec.effective_date_to is not null
1847 and p_pm_rule_rec.effective_date_from > p_pm_rule_rec.effective_date_to) then
1848 x_reason_failed:='EAM_PM_DAY_INVALID_DATE';
1849 return false;
1850 end if;
1851
1852 return true;
1853
1854
1855 end;
1856
1857
1858
1859 function validate_pm_runtime_rule
1860 (
1861 p_pm_rule_rec IN pm_rule_rec_type,
1862 x_reason_failed OUT NOCOPY varchar2
1863 ) return BOOLEAN
1864 is
1865 l_meter_id number;
1866 l_value_change_dir varchar2(1);
1867 l_count number;
1868 begin
1869
1870 -- rule type must be run time rule
1871 if (p_pm_rule_rec.rule_type is null or p_pm_rule_rec.rule_type<>2) then
1872 x_reason_failed:='EAM_PM_RUN_WRONG_RULE_TYPE';
1873 return false;
1874 end if;
1875
1876 -- not null: meter_id, runtime_interval not null
1877 if (p_pm_rule_rec.meter_id is null
1878 or p_pm_rule_rec.runtime_interval is null) then
1879 x_reason_failed:='EAM_PM_RUN_MISSING_PARAMS';
1880 return false;
1881 end if;
1882
1883 -- must be null: day_interval, effective_date_from, effective_date_to, list_date, list_date_desc
1884 if (p_pm_rule_rec.day_interval is not null
1885 or p_pm_rule_rec.effective_date_from is not null
1886 or p_pm_rule_rec.effective_date_to is not null
1887 or p_pm_rule_rec.list_date is not null
1888 or p_pm_rule_rec.list_date_desc is not null)
1889 then
1890 x_reason_failed:='EAM_PM_RUN_TOO_MANY_PARAMS';
1891 return false;
1892 end if;
1893
1894
1895 -- validate that the meter id is valid
1896 --added for perofrmance issues
1897 select 1 into l_count from dual where exists
1898 (select COUNTER_ID from CSI_COUNTERS_B where counter_id = p_pm_rule_rec.meter_id
1899 union
1900 select COUNTER_ID from CSI_COUNTER_TEMPLATE_B where counter_id = p_pm_rule_rec.meter_id) ;
1901
1902
1903 if (l_count=0) then
1904 x_reason_failed:='EAM_PM_RUN_INVALID_METER_ID';
1905 return false;
1906 end if;
1907
1908 -- ascending: effective to > effective from, and vice versa
1909
1910 --added for perofrmance issues
1911 select direction into l_value_change_dir from
1912 (select direction from CSI_COUNTERS_B where counter_id = p_pm_rule_rec.meter_id
1913 union
1914 select direction from CSI_COUNTER_TEMPLATE_B where counter_id = p_pm_rule_rec.meter_id);
1915
1916 if ((l_value_change_dir='A' -- ascending
1917 and p_pm_rule_rec.effective_reading_from > p_pm_rule_rec.effective_reading_to)
1918 or
1919 (l_value_change_dir='D' --descending
1920 and p_pm_rule_rec.effective_reading_to > p_pm_rule_rec.effective_reading_from))
1921 then
1922 x_reason_failed:='EAM_PM_RUN_INVALID_FROM_TO';
1923 return false;
1924 end if;
1925
1926 /* Check for whether meter has last service reading has been moved
1927 to validate_pm_header_and_rules */
1928
1929 return true;
1930 end;
1931
1932
1933
1934 function validate_pm_list_date
1935 (
1936 p_pm_rule_rec IN pm_rule_rec_type,
1937 x_reason_failed OUT NOCOPY varchar2
1938 ) return BOOLEAN
1939 is
1940 begin
1941
1942 -- rule_type must be list date
1943 if (p_pm_rule_rec.rule_type is null or p_pm_rule_rec.rule_type<>3) then
1944 x_reason_failed:='EAM_PM_LIST_WRONG_RULE_TYPE';
1945 return false;
1946 end if;
1947
1948 -- not null: list_date, (list_date_desc is not required)
1949 if (p_pm_rule_rec.list_date is null) then
1950 x_reason_failed:='EAM_PM_LIST_NO_DATE';
1951 return false;
1952 end if;
1953
1954 -- null: day_interval, meter_id, runtime_interval, effective_reading_from, effective_reading_to, effective_date_from, effective_date_to???
1955 if (p_pm_rule_rec.day_interval is not null
1956 or p_pm_rule_rec.meter_id is not null
1957 or p_pm_rule_rec.runtime_interval is not null
1958 or p_pm_rule_rec.effective_reading_from is not null
1959 or p_pm_rule_rec.effective_reading_to is not null
1960 or p_pm_rule_rec.effective_date_from is not null
1961 or p_pm_rule_rec.effective_date_to is not null) then
1962 x_reason_failed:='EAM_PM_LIST_TOO_MANY_PARAMS';
1963 return false;
1964 end if;
1965
1966
1967 -- list date greater than sysdate
1968 if (trunc(p_pm_rule_rec.list_date) < trunc(sysdate)) then
1969 x_reason_failed:='EAM_PM_LIST_INVALID_DATE_PAST';
1970 return false;
1971 end if;
1972
1973 return true;
1974
1975 end;
1976
1977
1978
1979 function validate_pm_day_interval_rules
1980 (
1981 p_pm_rules_tbl IN pm_rule_tbl_type,
1982 x_reason_failed OUT NOCOPY varchar2
1983 ) return BOOLEAN
1984 is
1985 i number;
1986 j number;
1987 k number;
1988 l_temp_date_tbl pm_date_tbl_type;
1989 l_sorted_date_tbl pm_date_tbl_type;
1990 l_reason_failed varchar2(30);
1991 l_validated boolean;
1992 l_min_index number;
1993 l_max_index number;
1994 l_num_rules number;
1995 l_num_sortable_rules number;
1996 l_pm_rule_rec pm_rule_rec_type;
1997 begin
1998 -- validate each day interval rule
1999
2000 -- Go through the table one by one,
2001 -- validate each rule individually
2002 -- pick out the one(s) without effective_from or effective_date_to
2003 -- make sure that there can be at most one w/o effective_from_date and at most one w/o effective_date_to,
2004 --and copy the rest to another table.
2005
2006 i:=1; -- counter for all rules
2007 j:=1; -- counter for sortable rules
2008 while (p_pm_rules_tbl.exists(i)) loop
2009 l_pm_rule_rec:=p_pm_rules_tbl(i);
2010 l_validated:=validate_pm_day_interval_rule(l_pm_rule_rec, l_reason_failed);
2011 if (not l_validated) then
2012 x_reason_failed:=l_reason_failed;
2013 return false;
2014 end if;
2015
2016 -- At most one day interval rule can exist such that effective_date_from is null
2017 -- this rule is the first rule after sorting.
2018 if l_pm_rule_rec.effective_date_from is null then
2019 if (l_min_index is not null) then
2020 x_reason_failed:='EAM_PM_DAY_MANY_NO_FROM';
2021 return false;
2022 else
2023 l_min_index:=i;
2024 end if;
2025 end if;
2026
2027 -- At most one day interval rule can exist such that effective_date_to is null
2028 -- this rule is the last rule after sorting.
2029 if l_pm_rule_rec.effective_date_to is null then
2030 if (l_max_index is not null) then
2031 x_reason_failed:='EAM_PM_DAY_MANY_NO_TO';
2032 return false;
2033 else
2034 l_max_index:=i;
2035 end if;
2036 end if;
2037
2038 -- If both effective from and effective to are null, then only one day interval rule can exist
2039 if (l_min_index=l_max_index and i>1) then
2040 x_reason_failed:='EAM_PM_DAY_OVERLAP';
2041 return false;
2042 end if;
2043
2044 -- If the both effective from and effective to are present,
2045 -- then put the index and start date of this record into
2046 -- another temp table for sorting.
2047 if ((l_max_index is null or l_max_index<>i)
2048 and (l_min_index is null or l_min_index<>i)) then
2049 l_temp_date_tbl(j).index1:=i;
2050 l_temp_date_tbl(j).date1:=p_pm_rules_tbl(i).effective_date_from;
2051 j:=j+1;
2052 end if;
2053
2054 i:=i+1;
2055 end loop;
2056 l_num_rules:=i-1;
2057 l_num_sortable_rules:=j-1;
2058
2059
2060 -- validate: no overlap; at most one rule with no effective from, and at most one rule with no effective to.
2061 sort_table_by_date(l_temp_date_tbl, l_num_sortable_rules, l_sorted_date_tbl);
2062
2063 -- If there is a rule w/o effective_to date, then put this rule's index and effective_from date
2064 -- as the last record in the sorted table
2065 if (l_max_index is not null) then
2066 l_sorted_date_tbl(l_num_sortable_rules+1).index1:=l_max_index;
2067 l_sorted_date_tbl(l_num_sortable_rules+1).date1:=p_pm_rules_tbl(l_max_index).effective_date_from;
2068 end if;
2069
2070 -- First, check whether the first record (with no effective_date_from) overlaps with the next record
2071 if (l_min_index is not null and l_num_rules > 1
2072 and p_pm_rules_tbl(l_min_index).effective_date_to > p_pm_rules_tbl(l_sorted_date_tbl(1).index1).effective_date_from)
2073 then
2074 x_reason_failed:='EAM_PM_DAY_OVERLAP';
2075 return false;
2076 end if;
2077
2078
2079 k:=1;
2080 while (l_sorted_date_tbl.exists(k+1)) loop
2081 if (p_pm_rules_tbl(l_sorted_date_tbl(k).index1).effective_date_to > p_pm_rules_tbl(l_sorted_date_tbl(k+1).index1).effective_date_from)
2082 then
2083 x_reason_failed:='EAM_PM_DAY_OVERLAP';
2084 return false;
2085 end if;
2086 k:=k+1;
2087
2088 end loop;
2089 return true;
2090
2091 end;
2092
2093
2094
2095 function validate_pm_runtime_rules
2096 (
2097 p_pm_rules_tbl IN pm_rule_tbl_type,
2098 x_reason_failed OUT NOCOPY varchar2
2099 ) return BOOLEAN
2100 is
2101 i number;
2102 j number;
2103 k number;
2104 l_temp_num_tbl pm_num_tbl_type;
2105 l_sorted_num_tbl pm_num_tbl_type;
2106 l_reason_failed varchar2(30);
2107 l_validated boolean;
2108 l_min_index number;
2109 l_max_index number;
2110 l_num_rules number;
2111 l_num_sortable_rules number;
2112 l_pm_rule_rec pm_rule_rec_type;
2113 l_meter_id_tbl pm_num_tbl_type;
2114 l_num_rules_for_meter pm_num_tbl_type;
2115 l_sorted_meter_id_tbl pm_num_tbl_type;
2116 l_meter_from_tbl pm_num_tbl_type;
2117 l_current_meter_index number;
2118 l_start_rule_index number;
2119 l_end_rule_index number;
2120 l_current_rule_index number;
2121 l_temp_reading_from_tbl pm_num_tbl_type;
2122 l_sorted_meter_from_tbl pm_num_tbl_type;
2123 l_num_meters number;
2124 l_num_rules_counter number;
2125 l_cur_p_tbl_rule_index number;
2126
2127
2128 begin
2129
2130
2131 i:=1; -- counter for all rules
2132
2133 -- First, validate each rule, and put meter id's into a meter id table
2134 while (p_pm_rules_tbl.exists(i)) loop
2135
2136 l_pm_rule_rec:=p_pm_rules_tbl(i);
2137 l_validated:=validate_pm_runtime_rule(l_pm_rule_rec, l_reason_failed);
2138 if (not l_validated) then
2139 x_reason_failed:=l_reason_failed;
2140 return false;
2141 end if;
2142
2143 l_meter_id_tbl(i).index1:=i;
2144 l_meter_id_tbl(i).num1:=p_pm_rules_tbl(i).meter_id;
2145 i:=i+1;
2146 end loop;
2147
2148 l_num_rules:=i-1;
2149
2150 -- Now, l_meter_id_tbl.index is the index of p_pm_rules_tbl, and l_meter_id_tbl.num1
2151 -- is p_pm_rules_tbl.meter_id
2152
2153 -- sort the meter id table by meter id
2154
2155 sort_table_by_number(l_meter_id_tbl, l_num_rules, l_sorted_meter_id_tbl);
2156
2157 -- Go through sorted meter id to determine the number of meters involved
2158 -- and how many rules there are for each meter
2159 -- put different meter rules effective reading from into different sections of
2160 -- l_meter_from_tbl
2161
2162
2163 i:=1;
2164 l_num_meters:=0;
2165
2166 while (l_sorted_meter_id_tbl.exists(i)) loop
2167 if (i=1) then
2168 l_num_meters:=1;
2169 l_num_rules_for_meter(l_num_meters).num1:=1;
2170 elsif (l_sorted_meter_id_tbl(i).num1<> l_sorted_meter_id_tbl(i-1).num1) then
2171 l_num_meters:=l_num_meters+1;
2172 l_num_rules_for_meter(l_num_meters).num1:=1;
2173 else
2174 l_num_rules_for_meter(l_num_meters).num1:=l_num_rules_for_meter(l_num_meters).num1+1;
2175 end if;
2176
2177 -- following two lines put index and reading_from into l_meter_from_tbl
2178 l_meter_from_tbl(i).index1:=l_sorted_meter_id_tbl(i).index1;
2179 l_meter_from_tbl(i).num1:=p_pm_rules_tbl(l_sorted_meter_id_tbl(i).index1).effective_reading_from;
2180 i:=i+1;
2181 end loop;
2182
2183
2184 -- loop through each meter and do cross validation for all rules of each meter
2185
2186 l_num_rules_counter:=0;
2187 for l_current_meter_index in 1..l_num_meters loop
2188 l_start_rule_index:=l_num_rules_counter+1;
2189 l_end_rule_index:=l_num_rules_counter+l_num_rules_for_meter(l_current_meter_index).num1;
2190
2191 l_min_index:=null;
2192 l_max_index:=null;
2193
2194 -- loop through each rule, validate that there is at most one rule w/o effective from,
2195 -- at most one rule w/o effective to, and if one rule has no effective from and no
2196 -- effective to, then there cannot be more than one rules.
2197 -- At the end of the loop, put all the rules with both effective from and effective to
2198 -- into a table for sorting.
2199
2200 l_num_sortable_rules:=0;
2201
2202 -- Following for loop:
2203 -- Go through the section of rule table for the current meter one by one,
2204 -- pick out the one(s) without effective_from or effective_date_to
2205 -- make sure that there can be at most one w/o effective_from_reading and at
2206 -- most one w/o effective_reading_to
2207
2208 for l_current_rule_index in l_start_rule_index .. l_end_rule_index loop
2209 l_cur_p_tbl_rule_index:=l_meter_from_tbl(l_current_rule_index).index1;
2210 l_pm_rule_rec:=p_pm_rules_tbl(l_cur_p_tbl_rule_index);
2211
2212 -- For each meter, at most one runtime rule can exist such that
2213 -- effective_reading_from is null
2214 -- this rule is the first rule after sorting.
2215 if l_pm_rule_rec.effective_reading_from is null then
2216 if (l_min_index is not null) then
2217 x_reason_failed:='EAM_PM_RUN_MANY_NO_FROM';
2218 return false;
2219 else
2220 l_min_index:=l_cur_p_tbl_rule_index;
2221 end if;
2222 end if;
2223
2224 -- for each meter, At most one runtime rule can exist such that
2225 -- effective_reading_to is null
2226 -- this rule is the last rule after sorting.
2227
2228 if l_pm_rule_rec.effective_reading_to is null then
2229 if (l_max_index is not null) then
2230 x_reason_failed:='EAM_PM_RUN_MANY_NO_TO';
2231 return false;
2232 else
2233 l_max_index:=l_cur_p_tbl_rule_index;
2234 end if;
2235 end if;
2236
2237 -- If both effective from and effective to are null, then only one day interval rule can exist
2238 if (l_min_index=l_max_index and l_num_rules_for_meter(l_current_meter_index).num1>1) then
2239 x_reason_failed:='EAM_PM_RUN_OVERLAP';
2240 return false;
2241 end if;
2242
2243 -- If both effective from and effective to are present,
2244 -- then put the index and start reading of this record into
2245 -- another temp table for sorting.
2246
2247
2248 if ((l_max_index is null or l_max_index<>l_cur_p_tbl_rule_index)
2249 and (l_min_index is null or l_min_index<>l_cur_p_tbl_rule_index))
2250 then
2251 l_num_sortable_rules:=l_num_sortable_rules+1;
2252 l_temp_reading_from_tbl(l_num_sortable_rules).index1:=l_meter_from_tbl(l_current_rule_index).index1;
2253 l_temp_reading_from_tbl(l_num_sortable_rules).num1:=p_pm_rules_tbl(l_cur_p_tbl_rule_index).effective_reading_from;
2254 end if;
2255 end loop; -- end for loop that loops through each rule within a meter
2256
2257
2258
2259 -- validate: no overlap; at most one rule with no effective from, and at most one rule with no effective to.
2260
2261 -- following loop for debugging
2262 i:=l_num_rules_counter;
2263
2264 sort_table_by_number(l_temp_reading_from_tbl,
2265 l_num_sortable_rules,
2266 l_sorted_meter_from_tbl);
2267
2268 -- Now, l_sorted_meter_from_tbl is a table of rules for ONE meter.
2269 -- The index of this table starts at 1.
2270
2271 -- If there is a rule w/o effective_to reading,
2272 -- then put this rule's index and effective_from reading
2273 -- as the last record in the sorted table
2274 if (l_max_index is not null) then
2275 l_sorted_meter_from_tbl(l_num_sortable_rules+1).index1:=l_max_index;
2276 l_sorted_meter_from_tbl(l_num_sortable_rules+1).num1:=p_pm_rules_tbl(l_max_index).effective_reading_from;
2277 l_num_sortable_rules:=l_num_sortable_rules+1;
2278 end if;
2279
2280 -- First, check whether the first record (with no effective_reading_from) overlaps with the next record
2281 if (l_min_index is not null
2282 and l_num_sortable_rules >0
2283 and l_min_index <> l_max_index
2284 and p_pm_rules_tbl(l_min_index).effective_reading_to > p_pm_rules_tbl(l_sorted_meter_from_tbl(l_num_rules_counter+1).index1).effective_reading_from)
2285 then
2286 x_reason_failed:='EAM_PM_RUN_OVERLAP';
2287 return false;
2288 end if;
2289
2290
2291
2292
2293 -- This loop goes through each pair of adjacent rules and checks for overlap
2294 for k in 1 .. l_num_sortable_rules-1 loop
2295 if (p_pm_rules_tbl(l_sorted_meter_from_tbl(k).index1).effective_reading_to > p_pm_rules_tbl(l_sorted_meter_from_tbl(k+1).index1).effective_reading_from)
2296 then
2297 x_reason_failed:='EAM_PM_RUN_OVERLAP';
2298 return false;
2299 end if;
2300 end loop; -- end while loop that checks overlap
2301 l_num_rules_counter:=l_num_rules_counter+l_num_rules_for_meter(l_current_meter_index).num1;
2302 end loop; -- end for loop that loops through each meter
2303 return true;
2304
2305 end;
2306
2307
2308
2309
2310
2311 function validate_pm_list_date_rules
2312 (
2313 p_pm_rules_tbl IN pm_rule_tbl_type,
2314 x_reason_failed OUT NOCOPY varchar2
2315 ) return BOOLEAN
2316 is
2317 l_reason_failed varchar2(30);
2318 l_validated boolean;
2319 l_temp_date_tbl pm_date_tbl_type;
2320 l_sorted_date_tbl pm_date_tbl_type;
2321 l_num_rules number;
2322 i number;
2323 begin
2324 -- validate each list date; copy each list date and index into l_temp_date_tbl
2325 i:=1;
2326 while p_pm_rules_tbl.exists(i) loop
2327 l_validated:=validate_pm_list_date(p_pm_rules_tbl(i), l_reason_failed);
2328 if (not l_validated) then
2329 x_reason_failed:=l_reason_failed;
2330 return false;
2331 end if;
2332 l_temp_date_tbl(i).index1:=i;
2333 l_temp_date_tbl(i).date1:=p_pm_rules_tbl(i).list_date;
2334 i:=i+1;
2335 end loop;
2336 l_num_rules:=i-1;
2337
2338 -- no list dates can be the same
2339 sort_table_by_date (l_temp_date_tbl, l_num_rules, l_sorted_date_tbl);
2340 i:=1;
2341 while p_pm_rules_tbl.exists(i+1) loop
2342 if (p_pm_rules_tbl(l_sorted_date_tbl(i).index1).list_date=p_pm_rules_tbl(l_sorted_date_tbl(i+1).index1).list_date) then
2343 x_reason_failed:='EAM_PM_LIST_SAME_DATES';
2344 return false;
2345 end if;
2346 i:=i+1;
2347 end loop;
2348 return true;
2349 end;
2350
2351
2352
2353 function validate_pm_header_and_rules
2354 (
2355 p_pm_schedule_rec IN pm_scheduling_rec_type,
2356 p_pm_day_interval_rules_tbl IN pm_rule_tbl_type,
2357 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
2358 p_pm_list_date_rules_tbl IN pm_rule_tbl_type,
2359 x_reason_failed OUT NOCOPY varchar2
2360 ) return BOOLEAN
2361 is
2362 l_validated boolean;
2363 l_reason_failed varchar2(30);
2364 i number;
2365 l_count number;
2366 l_meter_id number;
2367
2368 begin
2369
2370 -- validate header
2371 l_validated:=validate_pm_header(p_pm_schedule_rec, l_reason_failed);
2372 if (not l_validated) then
2373 x_reason_failed:=l_reason_failed;
2374 return false;
2375 end if;
2376
2377 -- rules tables cannot be all empty
2378 if (p_pm_day_interval_rules_tbl.count=0 and p_pm_runtime_rules_tbl.count=0 and p_pm_list_date_rules_tbl.count=0) then
2379 x_reason_failed:='EAM_PM_EMPTY_HEADER';
2380 return false;
2381 end if;
2382
2383 -- If header is of type rule_based, then list_date rule table should be empty
2384 if (p_pm_schedule_rec.type_code=10 --'Rule Based, meter and day time'
2385 and p_pm_list_date_rules_tbl.count>0) then
2386 x_reason_failed:='EAM_PM_RULE_NOT_MATCH_PM_TYPE';
2387 return false;
2388 end if;
2389
2390 -- If header is of type rule_based, then list_date rule table should be empty
2391 if (p_pm_schedule_rec.type_code=17 --'day time only'
2392 and (p_pm_list_date_rules_tbl.count>0 or p_pm_runtime_rules_tbl.count>0)) then
2393 x_reason_failed:='EAM_PM_RULE_NOT_MATCH_PM_TYPE';
2394 return false;
2395 end if;
2396
2397
2398 -- If header is of type list date, then day interval rules and runtime rules table should be empty
2399 if (p_pm_schedule_rec.type_code=20 -- 'List Dates'
2400 and (p_pm_day_interval_rules_tbl.count>0 or p_pm_runtime_rules_tbl.count>0))
2401 then
2402 x_reason_failed:='EAM_PM_RULE_NOT_MATCH_PM_TYPE';
2403 return false;
2404 end if;
2405
2406
2407 -- validate each type of rules
2408 l_validated:=validate_pm_day_interval_rules(p_pm_day_interval_rules_tbl, l_reason_failed);
2409 if (not l_validated) then
2410 x_reason_failed:=l_reason_failed;
2411 return false;
2412 end if;
2413
2414 l_validated:=validate_pm_runtime_rules(p_pm_runtime_rules_tbl, l_reason_failed);
2415 if (not l_validated) then
2416 x_reason_failed:=l_reason_failed;
2417 return false;
2418 end if;
2419
2420 l_validated:=validate_pm_list_date_rules(p_pm_list_date_rules_tbl, l_reason_failed);
2421 if (not l_validated) then
2422 x_reason_failed:=l_reason_failed;
2423 return false;
2424 end if;
2425
2426
2427 -- Check that effective date from and effective date to in the day interval rules are within the effective dates of the header
2428 i:=1;
2429 while (p_pm_day_interval_rules_tbl.exists(i)) loop
2430 if (p_pm_day_interval_rules_tbl(i).effective_date_from < p_pm_schedule_rec.from_effective_date
2431 or p_pm_day_interval_rules_tbl(i).effective_date_to > p_pm_schedule_rec.to_effective_date)
2432 then
2433 x_reason_failed:='EAM_PM_HD_DAY_INVALID_DATE';
2434 return false;
2435 end if;
2436 i:=i+1;
2437 end loop;
2438
2439 -- Check that list dates in the list date rules are within effective dates of the header
2440 /* i:=1;
2441 while (p_pm_list_date_rules_tbl.exists(i)) loop
2442 if (p_pm_list_date_rules_tbl(i).list_date < p_pm_schedule_rec.from_effective_date
2443 or p_pm_list_date_rules_tbl(i).list_date > p_pm_schedule_rec.to_effective_date)
2444 then
2445 x_reason_failed:='EAM_PM_HD_LIST_INVALID_DATE';
2446 return false;
2447 end if;
2448 i:=i+1;
2449 end loop; relaxing this validation as PM logic checks for end date - bug 11717251*/
2450
2451 return true;
2452 end;
2453
2454 --this is an overloaded method to include logic of meter last service reading
2455
2456 function validate_pm_activity
2457 (
2458 p_pm_activity_grp_rec IN pm_activities_grp_rec_type,
2459 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
2460 p_pm_schedule_rec IN PM_Scheduling_Rec_Type,
2461 x_reason_failed OUT NOCOPY varchar2,
2462 x_message OUT NOCOPY varchar2,
2463 x_activities OUT NOCOPY varchar2
2464 ) return BOOLEAN
2465
2466 is
2467 l_pm_schedule_id number;
2468 l_last_service_start_date date;
2469 l_last_service_end_date date;
2470 i number;
2471 l_count number;
2472 l_lsi_updated varchar2(1);
2473 l_maintenance_object_id number;
2474 l_asset_activity_id number;
2475 l_last_scheduled_start_date date;
2476 l_last_scheduled_end_date date;
2477 l_actual_end_date date;
2478 l_wip_entity_id number;
2479 l_act_name varchar2(240);
2480 l_transaction_id number;
2481 begin
2482
2483 -- activity validation: Check that the asset activity has last service reading start/end date.
2484
2485 if (p_pm_schedule_rec.tmpl_flag is null or p_pm_schedule_rec.tmpl_flag='N') then /* FP of R12 bug 9744000, added null condition*/
2486
2487 select last_service_start_date, last_service_end_date,last_scheduled_start_date,last_scheduled_end_date,
2488 maintenance_object_id,asset_activity_id
2489 into l_last_service_start_date, l_last_service_end_date,l_last_scheduled_start_date,l_last_scheduled_end_date,
2490 l_maintenance_object_id,l_asset_activity_id
2491 from mtl_eam_asset_activities
2492 where activity_association_id=p_pm_activity_grp_rec.activity_association_id;
2493
2494 select distinct msi.concatenated_segments into l_act_name
2495 from mtl_system_items_b_kfv msi
2496 where msi.inventory_item_id=l_asset_activity_id
2497 and msi.eam_item_type=2;
2498
2499
2500 if (l_last_service_start_date is null or l_last_service_end_date is null) then
2501
2502 select max(ejct.actual_start_date),
2503 max(ejct.actual_end_date) into
2504 l_last_service_start_date,
2505 l_last_service_end_date
2506
2507 from eam_job_completion_txns ejct, wip_discrete_jobs wdj
2508 where wdj.wip_entity_id = ejct.wip_entity_id
2509 and wdj.maintenance_object_id=l_maintenance_object_id
2510 and wdj.primary_item_id=l_asset_activity_id
2511 and ejct.transaction_type=1;
2512
2513 if (l_last_service_start_date is null or l_last_service_end_date is null) then
2514
2515 l_last_service_start_date := g_sysdate;
2516 l_last_service_end_date := g_sysdate;
2517 l_lsi_updated := 'Y';
2518 x_activities :=l_act_name;
2519 end if;
2520
2521 UPDATE mtl_eam_asset_activities
2522 SET last_service_start_date = l_last_service_start_date,
2523 last_service_end_date = l_last_service_end_date,
2524 last_update_date=sysdate, last_updated_by=fnd_global.user_id,
2525 last_update_login=fnd_global.login_id
2526 WHERE activity_association_id = p_pm_activity_grp_rec.activity_association_id;
2527
2528
2529 end if;
2530
2531 if (l_last_scheduled_start_date is null or l_last_scheduled_end_date is null) then
2532
2533
2534 begin
2535
2536 select transaction_id into l_transaction_id from
2537 ( select max(actual_end_date),transaction_id from eam_job_completion_txns ejct,
2538 wip_discrete_jobs wdj
2539 where ejct.transaction_type=1 and
2540 wdj.wip_entity_id = ejct.wip_entity_id and
2541 wdj.maintenance_object_id = l_maintenance_object_id and
2542 wdj.primary_item_id = l_asset_activity_id group by ejct.transaction_id order by ejct.transaction_id desc) where rownum = 1;
2543
2544 exception when no_data_found then
2545 l_last_scheduled_start_date := g_sysdate;
2546 l_last_scheduled_end_date := g_sysdate;
2547 l_lsi_updated := 'Y';
2548 x_activities :=l_act_name;
2549 end;
2550
2551 if l_transaction_id is not null then
2552 begin
2553
2554 SELECT wdj.scheduled_start_date,
2555 wdj.scheduled_completion_date,wdj.wip_entity_id
2556 into l_last_scheduled_start_date,l_last_scheduled_end_date,l_wip_entity_id
2557 FROM eam_job_completion_txns ejct, wip_discrete_jobs wdj WHERE wdj.primary_item_id=l_asset_activity_id
2558 and
2559 wdj.maintenance_object_id=l_maintenance_object_id and
2560 ejct.transaction_id=l_transaction_id and ejct.transaction_type=1 and
2561 ejct.wip_entity_id=wdj.wip_entity_id;
2562
2563 exception when no_data_found then
2564 l_last_scheduled_start_date := g_sysdate;
2565 l_last_scheduled_end_date := g_sysdate;
2566 l_lsi_updated := 'Y';
2567 x_activities :=l_act_name;
2568 end;
2569 end if;
2570
2571 UPDATE mtl_eam_asset_activities meaa
2572 SET meaa.last_scheduled_start_date = l_last_scheduled_start_date,
2573 meaa.last_scheduled_end_date = l_last_scheduled_end_date,
2574 meaa.wip_entity_id=l_wip_entity_id,
2575 last_update_date=sysdate, last_updated_by=fnd_global.user_id,
2576 last_update_login=fnd_global.login_id
2577 WHERE (meaa.last_scheduled_start_date is null OR
2578 meaa.last_scheduled_end_date is null) AND
2579 meaa.activity_association_id = p_pm_activity_grp_rec.activity_association_id;
2580
2581 end if;
2582
2583 end if;
2584
2585 --in case of runtime rules check for last service reading.if not insert the same with default values
2586 i:=1;
2587 if (p_pm_schedule_rec.tmpl_flag is null or p_pm_schedule_rec.tmpl_flag='N') then -- FP: bug 9744000, added null condition
2588 while (p_pm_runtime_rules_tbl.exists(i)) loop
2589
2590 select count(*) into l_count
2591 from eam_pm_last_service
2592 where meter_id=p_pm_runtime_rules_tbl(i).meter_id
2593 and activity_association_id=p_pm_activity_grp_rec.activity_association_id;
2594
2595 if (l_count = 0) then
2596
2597 INSERT INTO eam_pm_last_service (activity_association_id, meter_id,
2598 last_service_reading, last_update_date, last_updated_by, creation_date,
2599 last_update_login, created_by)
2600 VALUES (p_pm_activity_grp_rec.activity_association_id,
2601 p_pm_runtime_rules_tbl(i).meter_id,
2602 nvl(p_pm_runtime_rules_tbl(i).last_service_reading, 0),
2603 sysdate,
2604 fnd_global.user_id,
2605 sysdate,
2606 fnd_global.login_id,
2607 fnd_global.user_id);
2608
2609 l_lsi_updated := 'Y';
2610 x_activities :=l_act_name;
2611 end if;
2612 i:=i+1;
2613 end loop;
2614 end if;
2615
2616 -- set name is unique for this asset activity association and for this type of tmpl_flag
2617 -- (at most one template and one definition for each asset activity association)
2618
2619 /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
2620 BEGIN
2621
2622 select eps.pm_schedule_id into l_pm_schedule_id from
2623 eam_pm_schedulings eps,eam_pm_activities epa
2624 where eps.pm_schedule_id=epa.pm_schedule_id
2625 and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
2626 and eps.set_name_id=p_pm_schedule_rec.set_name_id
2627 and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
2628
2629 if (p_pm_schedule_rec.pm_schedule_id is null) then
2630 -- If it's not an update action
2631 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_SET_NAME';
2632 return false;
2633 else
2634 -- it's an update action
2635 if (l_pm_schedule_id <> p_pm_schedule_rec.pm_schedule_id) then
2636 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_SET_NAME';
2637 return false;
2638 end if;
2639
2640 end if;
2641
2642 EXCEPTION
2643 WHEN NO_DATA_FOUND THEN
2644 null;
2645 WHEN TOO_MANY_ROWS THEN
2646 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_SET_NAME';
2647 return false;
2648 END;
2649
2650 -- activity validation: there can be only one default
2651
2652 if (p_pm_schedule_rec.default_implement is not null and p_pm_schedule_rec.default_implement='Y') then
2653
2654 /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
2655 BEGIN
2656
2657 select eps.pm_schedule_id into l_pm_schedule_id from
2658 eam_pm_schedulings eps,eam_pm_activities epa
2659 where eps.pm_schedule_id=epa.pm_schedule_id
2660 and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
2661 and eps.default_implement = 'Y'
2662 and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
2663
2664 -- If there is one default, check if it is the same one
2665 -- as the one that's being validated.
2666 if (p_pm_schedule_rec.pm_schedule_id is null) then
2667 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_DEFAULT';
2668 return false;
2669 else
2670 if (l_pm_schedule_id <> p_pm_schedule_rec.pm_schedule_id) then
2671 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_DEFAULT';
2672 return false;
2673 end if;
2674 end if;
2675
2676 EXCEPTION
2677 WHEN NO_DATA_FOUND THEN
2678 null;
2679 WHEN TOO_MANY_ROWS THEN
2680 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_DEFAULT';
2681 return false;
2682 END;
2683 end if;
2684
2685 --For list dates pm schedule activity cyclic attributes should have default values
2686
2687 if p_pm_schedule_rec.TYPE_CODE = 20 and
2688 (p_pm_activity_grp_rec.interval_multiple > 1 or p_pm_activity_grp_rec.allow_repeat_in_cycle <> 'N') then
2689
2690 x_reason_failed := 'EAM_PM_HD_LIST_DATES_VLD';
2691 return false;
2692
2693 end if;
2694
2695 if l_lsi_updated = 'Y' then
2696 x_message := 'EAM_PM_LAST_SERVICE_DEFAULT';
2697 end if;
2698
2699 return true;
2700
2701 end validate_pm_activity;
2702
2703 --this will be called from instantiation api
2704
2705 function validate_pm_activity
2706 (
2707 p_pm_activity_grp_rec IN pm_activities_grp_rec_type,
2708 p_pm_schedule_rec IN PM_Scheduling_Rec_Type,
2709 x_reason_failed OUT NOCOPY varchar2
2710 ) return BOOLEAN
2711
2712 is
2713 l_pm_schedule_id number;
2714 l_last_service_start_date date;
2715 l_last_service_end_date date;
2716 i number;
2717 begin
2718
2719 -- activity validation: Check that the asset activity has last service reading start/end date.
2720
2721 if (p_pm_schedule_rec.tmpl_flag='N') then
2722
2723 select last_service_start_date, last_service_end_date
2724 into l_last_service_start_date, l_last_service_end_date
2725 from mtl_eam_asset_activities
2726 where activity_association_id=p_pm_activity_grp_rec.activity_association_id;
2727
2728 if (l_last_service_start_date is null or l_last_service_end_date is null) then
2729 x_reason_failed:='EAM_PM_HD_ACT_NO_SERVICE_DATE';
2730 return false;
2731 end if;
2732
2733 end if;
2734
2735 --For list dates pm schedule activity cyclic attributes should have default values
2736
2737 if p_pm_schedule_rec.TYPE_CODE = 20 and
2738 (p_pm_activity_grp_rec.interval_multiple > 1 or p_pm_activity_grp_rec.allow_repeat_in_cycle <> 'N') then
2739
2740 x_reason_failed := 'EAM_PM_HD_LIST_DATES_VLD';
2741 return false;
2742
2743 end if;
2744
2745 -- set name is unique for this asset activity association and for this type of tmpl_flag
2746 -- (at most one template and one definition for each asset activity association)
2747
2748 /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
2749 BEGIN
2750
2751 select eps.pm_schedule_id into l_pm_schedule_id from
2752 eam_pm_schedulings eps,eam_pm_activities epa
2753 where eps.pm_schedule_id=epa.pm_schedule_id
2754 and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
2755 and eps.set_name_id=p_pm_schedule_rec.set_name_id
2756 and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
2757
2758 if (p_pm_schedule_rec.pm_schedule_id is null) then
2759 -- If it's not an update action
2760 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_SET_NAME';
2761 return false;
2762 else
2763 -- it's an update action
2764 if (l_pm_schedule_id <> p_pm_schedule_rec.pm_schedule_id) then
2765 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_SET_NAME';
2766 return false;
2767 end if;
2768
2769 end if;
2770
2771 EXCEPTION
2772 WHEN NO_DATA_FOUND THEN
2773 null;
2774 WHEN TOO_MANY_ROWS THEN
2775 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_SET_NAME';
2776 return false;
2777 END;
2778
2779 -- activity validation: there can be only one default
2780
2781 if (p_pm_schedule_rec.default_implement is not null and p_pm_schedule_rec.default_implement='Y') then
2782
2783 /* Bug # 3890075 : Modified the logic so that the query is executed only once. */
2784 BEGIN
2785
2786 select eps.pm_schedule_id into l_pm_schedule_id from
2787 eam_pm_schedulings eps,eam_pm_activities epa
2788 where eps.pm_schedule_id=epa.pm_schedule_id
2789 and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
2790 and eps.default_implement = 'Y'
2791 and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
2792
2793 -- If there is one default, check if it is the same one
2794 -- as the one that's being validated.
2795 if (p_pm_schedule_rec.pm_schedule_id is null) then
2796 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_DEFAULT';
2797 return false;
2798 else
2799 if (l_pm_schedule_id <> p_pm_schedule_rec.pm_schedule_id) then
2800 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_DEFAULT';
2801 return false;
2802 end if;
2803 end if;
2804
2805 EXCEPTION
2806 WHEN NO_DATA_FOUND THEN
2807 null;
2808 WHEN TOO_MANY_ROWS THEN
2809 x_reason_failed:='EAM_PM_HD_NOT_UNIQUE_DEFAULT';
2810 return false;
2811 END;
2812 end if;
2813
2814 return true;
2815
2816 end validate_pm_activity;
2817
2818 function validate_pm_activities
2819 ( p_pm_activities_grp_tbl IN pm_activities_grp_tbl_type,
2820 p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
2821 p_pm_schedule_rec IN pm_scheduling_rec_type,
2822 x_reason_failed OUT NOCOPY varchar2,
2823 x_message OUT NOCOPY varchar2,
2824 x_activities OUT NOCOPY varchar2
2825 ) return boolean
2826 is
2827 i number;
2828 l_validated boolean;
2829 l_reason_failed varchar2(30);
2830 l_pm_activities_grp_rec pm_activities_grp_rec_type;
2831 l_message varchar2(30);
2832 l_activity varchar2(240);
2833 begin
2834 --added so that the LSI defaulting happens consistenly for all the activities consitently bug:4768304
2835 g_sysdate := sysdate;
2836 i:=1; -- counter for all activities
2837
2838 if ( p_pm_activities_grp_tbl.count = 0 ) then
2839 x_reason_failed:='EAM_PM_NO_ACT';
2840 return false;
2841 end if;
2842
2843 while (p_pm_activities_grp_tbl.exists(i)) loop
2844
2845 -- check for reschedule manual work order option applicable only
2846 --in case of single activity PM
2847
2848 if i > 1 and p_pm_schedule_rec.include_manual='Y' then
2849 x_reason_failed := 'EAM_PM_HD_RESCHED_MAN_NA';
2850 return false;
2851 end if;
2852
2853 l_pm_activities_grp_rec:=p_pm_activities_grp_tbl(i);
2854
2855 l_validated:=validate_pm_activity(l_pm_activities_grp_rec, p_pm_runtime_rules_tbl,p_pm_schedule_rec,l_reason_failed,l_message,l_activity);
2856
2857 if (not l_validated) then
2858 x_reason_failed := l_reason_failed;
2859 return false;
2860 end if;
2861
2862 if (l_validated) and l_message is not null then
2863 x_message := l_message;
2864 if(x_activities is null ) then
2865 x_activities := l_activity;
2866 else
2867 x_activities := x_activities ||' , ' || l_activity;
2868 end if;
2869 end if;
2870
2871 i:= i+1;
2872
2873 end loop;
2874 return true;
2875 end;
2876
2877
2878 procedure sort_table_by_date
2879 (
2880 p_date_table IN pm_date_tbl_type,
2881 p_num_rows IN number,
2882 x_sorted_date_table OUT NOCOPY pm_date_tbl_type
2883 )
2884 is
2885
2886 i number;
2887 j number;
2888 l_min_index number;
2889 l_temp_date_rec pm_date_rec_type;
2890
2891 begin
2892 x_sorted_date_table:=p_date_table;
2893 for i in 1..p_num_rows loop
2894 l_min_index:=i;
2895 for j in i+1..p_num_rows loop
2896 if x_sorted_date_table(j).date1 < x_sorted_date_table(l_min_index).date1 then
2897 l_min_index:=j;
2898 end if;
2899 end loop;
2900 l_temp_date_rec:=x_sorted_date_table(i);
2901 x_sorted_date_table(i):=x_sorted_date_table(l_min_index);
2902 x_sorted_date_table(l_min_index):=l_temp_date_rec;
2903 end loop;
2904 end;
2905
2906
2907 procedure sort_table_by_number
2908 (
2909 p_num_table IN pm_num_tbl_type,
2910 p_num_rows IN number,
2911 x_sorted_num_table OUT NOCOPY pm_num_tbl_type
2912 )
2913 is
2914
2915 i number;
2916 j number;
2917 l_min_index number;
2918 l_temp_num_rec pm_num_rec_type;
2919
2920 begin
2921 x_sorted_num_table:=p_num_table;
2922 for i in 1..p_num_rows loop
2923 l_min_index:=i;
2924 for j in i+1..p_num_rows loop
2925 if x_sorted_num_table(j).num1 < x_sorted_num_table(l_min_index).num1 then
2926 l_min_index:=j;
2927 end if;
2928 end loop;
2929 l_temp_num_rec:=x_sorted_num_table(i);
2930 x_sorted_num_table(i):=x_sorted_num_table(l_min_index);
2931 x_sorted_num_table(l_min_index):=l_temp_num_rec;
2932 end loop;
2933 end;
2934
2935
2936 -- merge two rule tables; p_rules_tbl1 overrides p_rules_tbl2
2937 procedure merge_rules
2938 (p_rules_tbl1 IN pm_rule_tbl_type,
2939 p_rules_tbl2 IN pm_rule_tbl_type,
2940 x_merged_rules_tbl OUT NOCOPY pm_rule_tbl_type)
2941 is
2942 i number;
2943 j number;
2944 l_num_tbl1 pm_num_tbl_type;
2945 l_sorted_num_tbl1 pm_num_tbl_type;
2946 begin
2947 i:=1;
2948 while (p_rules_tbl1.exists(i)) loop
2949 if (p_rules_tbl1(i).rule_id is not null) then
2950 l_num_tbl1(i).index1:=i;
2951 l_num_tbl1(i).num1:=p_rules_tbl1(i).rule_id;
2952 l_num_tbl1(i).other:=1;
2953 end if;
2954 i:=i+1;
2955 end loop;
2956
2957 while (p_rules_tbl2.exists(i)) loop
2958 if (p_rules_tbl2(i).rule_id is not null) then
2959 l_num_tbl1(i).index1:=i;
2960 l_num_tbl1(i).num1:=p_rules_tbl2(i).rule_id;
2961 l_num_tbl1(i).other:=2;
2962 end if;
2963 i:=i+1;
2964 end loop;
2965
2966 sort_table_by_number(l_num_tbl1, i-1, l_sorted_num_tbl1);
2967
2968 i:=1; -- counter for the sorted number table
2969 j:=1; -- counter for the merged rules table; j<=i at any time
2970 while (l_num_tbl1.exists(i)) loop
2971 if (i>1 and l_num_tbl1(i).index1=l_num_tbl1(i-1).index1
2972 and l_num_tbl1(i).other=1) then
2973 x_merged_rules_tbl(j):=p_rules_tbl1(l_num_tbl1(i).index1);
2974 else
2975 j:=j+1;
2976 end if;
2977
2978 if (l_num_tbl1(i).other=1) then
2979 x_merged_rules_tbl(j):=p_rules_tbl1(l_num_tbl1(i).index1);
2980 else -- if l_num_tbl1(i).other=2
2981 x_merged_rules_tbl(j):=p_rules_tbl2(l_num_tbl1(i).index1);
2982 end if;
2983 i:=i+1;
2984 end loop;
2985
2986 end;
2987
2988 --this will be called by pm form and also called from the new api update_pm_last_cyclic_act
2989
2990 procedure get_pm_last_activity
2991 ( p_pm_schedule_id IN NUMBER,
2992 p_activity_association_id OUT NOCOPY NUMBER,
2993 x_return_status OUT NOCOPY VARCHAR2,
2994 x_msg_count OUT NOCOPY NUMBER ,
2995 x_msg_data OUT NOCOPY VARCHAR2
2996 )
2997 is
2998
2999 l_rescheduling_point number;
3000 l_last_service_date date;
3001 l_act_assoc_id number;
3002 l_current_seq number;
3003 l_msg_data varchar2(2000);
3004 l_error_message varchar2(2000);
3005
3006
3007 BEGIN
3008 x_return_status:='S';
3009 -- Check that enough info is supplied to identify which pm schedule to copy from.
3010 if (p_pm_schedule_id is null) then
3011 FND_MESSAGE.SET_NAME ('EAM', 'EAM_MT_SUPPLY_PARAMS');
3012 FND_MSG_PUB.Add;
3013 RAISE FND_API.G_EXC_ERROR;
3014 end if;
3015
3016 --getting the scheduling option for the pm schedule
3017
3018 select rescheduling_point,current_seq into l_rescheduling_point,l_current_seq
3019 from eam_pm_schedulings
3020 where pm_schedule_id = p_pm_schedule_id;
3021
3022 --mandatory checking for rescheduling and current interval values
3023 if (l_rescheduling_point is null) then
3024 FND_MESSAGE.SET_NAME ('EAM', 'EAM_PM_NO_RESCHED');
3025 FND_MSG_PUB.Add;
3026 RAISE FND_API.G_EXC_ERROR;
3027 end if;
3028
3029 if (l_current_seq is null) then
3030 FND_MESSAGE.SET_NAME ('EAM', 'EAM_PM_NO_CURRENTSEQ');
3031 FND_MSG_PUB.Add;
3032 RAISE FND_API.G_EXC_ERROR;
3033 end if;
3034
3035 --based on current sequence retrieve
3036
3037 if l_current_seq > 0 then
3038
3039 --based on scheduling option retrieve the last service date
3040
3041 if (l_rescheduling_point in (1,5,6)) then
3042
3043 select max(meaa.last_service_start_date) into l_last_service_date
3044 from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3045 where epa.activity_association_id=meaa.activity_association_id and
3046 epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
3047 ((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
3048 (epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0))
3049 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate;
3050 /*bug 10381070 checking effectiveness of activity association*/
3051
3052 elsif (l_rescheduling_point = 2) then
3053
3054 select max(meaa.last_service_end_date) into l_last_service_date
3055 from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3056 where epa.activity_association_id=meaa.activity_association_id and
3057 epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
3058 ((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
3059 (epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0))
3060 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate;
3061 /*bug 10381070 checking effectiveness of activity association*/
3062
3063 elsif (l_rescheduling_point = 3) then
3064
3065 select max(meaa.last_scheduled_start_date) into l_last_service_date
3066 from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3067 where epa.activity_association_id=meaa.activity_association_id and
3068 epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
3069 ((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
3070 (epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0))
3071 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate;
3072 /*bug 10381070 checking effectiveness of activity association*/
3073
3074 elsif (l_rescheduling_point = 4) then
3075
3076 select max(meaa.last_scheduled_end_date) into l_last_service_date
3077 from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3078 where epa.activity_association_id=meaa.activity_association_id and
3079 epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
3080 ((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
3081 (epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0))
3082 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate;
3083 /*bug 10381070 checking effectiveness of activity association*/
3084
3085 end if;
3086
3087
3088 elsif l_current_seq = 0 then
3089
3090 if (l_rescheduling_point in (1,5,6)) then
3091
3092 select max(meaa.last_service_start_date) into l_last_service_date
3093 from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3094 where epa.activity_association_id=meaa.activity_association_id and
3095 epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id
3096 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate; /*bug 10381070 */
3097
3098 elsif (l_rescheduling_point = 2) then
3099
3100 select max(meaa.last_service_end_date) into l_last_service_date
3101 from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3102 where epa.activity_association_id=meaa.activity_association_id and
3103 epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id
3104 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate; /*bug 10381070 */
3105
3106 elsif (l_rescheduling_point = 3) then
3107
3108 select max(meaa.last_scheduled_start_date) into l_last_service_date
3109 from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3110 where epa.activity_association_id=meaa.activity_association_id and
3111 epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id
3112 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate; /*bug 10381070 */
3113
3114 elsif (l_rescheduling_point = 4) then
3115
3116 select max(meaa.last_scheduled_end_date) into l_last_service_date
3117 from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
3118 where epa.activity_association_id=meaa.activity_association_id and
3119 epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id
3120 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate; /*bug 10381070 */
3121
3122
3123 end if;
3124
3125 end if;
3126
3127 --based on last service date get the last activity association id of the
3128 --lowest interval multiple activity in case of a tie
3129 -- modified for 9754424, handled no_data_found exception incase of l_last_service_date is null.
3130 Begin
3131 select * into l_act_assoc_id from (
3132 SELECT meaa.activity_association_id FROM mtl_eam_asset_activities meaa, eam_pm_activities epa
3133 WHERE decode(l_rescheduling_point,2,last_service_end_date ,3,last_scheduled_start_date, 4,last_scheduled_end_date ,last_service_start_date)
3134 = l_last_service_date
3135 AND meaa.activity_association_id = epa.activity_association_id AND epa.pm_schedule_id = p_pm_schedule_id
3136 and nvl(meaa.start_date_active, sysdate-1) < sysdate and nvl(meaa.end_date_active, sysdate+1) > sysdate /*bug 10381070 */
3137 order by interval_multiple
3138 )
3139 where rownum=1 ;
3140 EXCEPTION
3141 WHEN no_data_found THEN
3142 NULL;
3143 End;
3144 --assigning the last activity
3145 p_activity_association_id :=nvl(l_act_assoc_id,p_activity_association_id);
3146
3147 EXCEPTION
3148 WHEN FND_API.G_EXC_ERROR THEN
3149 x_return_status := FND_API.G_RET_STS_ERROR ;
3150 FND_MSG_PUB.get
3151 ( p_msg_index_out => x_msg_count ,
3152 p_data => x_msg_data
3153 );
3154 x_msg_data := substr(x_msg_data,1,2000);
3155 WHEN OTHERS THEN
3156 x_msg_count := 1;
3157 x_return_status := fnd_api.g_ret_sts_error;
3158 l_error_message := substrb(sqlerrm,1,512);
3159 x_msg_data := l_error_message;
3160 END get_pm_last_activity;
3161
3162 --this will be called by work order completion
3163
3164
3165 procedure update_pm_last_cyclic_act
3166 ( p_api_version IN NUMBER ,
3167 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
3168 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
3169 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
3170 x_return_status OUT NOCOPY VARCHAR2 ,
3171 x_msg_count OUT NOCOPY NUMBER ,
3172 x_msg_data OUT NOCOPY VARCHAR2 ,
3173 p_pm_schedule_id IN NUMBER
3174 )
3175
3176 is
3177 l_api_name CONSTANT VARCHAR2(30) :='update_pm_last_cyclic_act';
3178 l_api_version CONSTANT NUMBER := 1.0;
3179 x_error_message VARCHAR2(2000);
3180
3181 l_rescheduling_point number;
3182 l_last_service_date date;
3183 l_act_assoc_id number;
3184 l_pm_schedule_id number;
3185 l_return_status varchar2(1);
3186 l_msg_count number;
3187 l_msg_data varchar2(2000);
3188 l_get_failed varchar2(1);
3189
3190 BEGIN
3191
3192 l_pm_schedule_id := p_pm_schedule_id;
3193 -- Standard Start of API savepoint
3194 SAVEPOINT update_pm_last_cyclic_act;
3195
3196 x_return_status := FND_API.G_RET_STS_SUCCESS;
3197
3198 --mandatory checking
3199 if (p_pm_schedule_id is null) then
3200 FND_MESSAGE.SET_NAME ('EAM', 'EAM_PM_SCHEDULE_ID_MISSING');
3201 FND_MSG_PUB.Add;
3202 RAISE FND_API.G_EXC_ERROR;
3203 end if;
3204
3205 --call to get the last activity of the pm schedule
3206
3207 eam_pmdef_pub.get_pm_last_activity(p_pm_schedule_id => l_pm_schedule_id,
3208 p_activity_association_id =>l_act_assoc_id,
3209 x_return_status => l_return_status,
3210 x_msg_count => l_msg_count,
3211 x_msg_data => l_msg_data);
3212
3213 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3214 l_get_failed := 'Y';
3215 RAISE FND_API.G_EXC_ERROR;
3216 end if;
3217
3218 -- updating the eam_last_cyclic_act field
3219 -- modified for 9754424.
3220 if (l_act_assoc_id is not null) or (l_act_assoc_id <>FND_API.G_MISS_NUM) THEN
3221 update eam_pm_schedulings set EAM_LAST_CYCLIC_ACT = l_act_assoc_id,
3222 last_update_date=sysdate,
3223 last_updated_by=fnd_global.user_id,
3224 last_update_login=fnd_global.login_id
3225 where pm_schedule_id = p_pm_schedule_id;
3226 end if;
3227 --standard commit to be checked
3228
3229 IF FND_API.To_Boolean( p_commit ) THEN
3230 COMMIT WORK;
3231 END IF;
3232
3233 EXCEPTION
3234
3235 WHEN FND_API.G_EXC_ERROR THEN
3236 ROLLBACK TO update_pm_last_cyclic_act;
3237 x_return_status := FND_API.G_RET_STS_ERROR ;
3238
3239 if l_get_failed = 'Y' then
3240 x_msg_count := l_msg_count;
3241 x_msg_data := l_msg_data;
3242 else
3243 FND_MSG_PUB.get
3244 ( p_msg_index_out => x_msg_count ,
3245 p_data => x_msg_data
3246 );
3247 x_msg_data := substr(x_msg_data,1,2000);
3248 end if;
3249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3250 ROLLBACK TO update_pm_last_cyclic_act;
3251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3252 FND_MSG_PUB.get
3253 ( p_msg_index_out => x_msg_count ,
3254 p_data => x_msg_data
3255 );
3256 x_msg_data := substr(x_msg_data,1,2000);
3257 WHEN OTHERS THEN
3258 ROLLBACK TO update_pm_last_cyclic_act;
3259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3260 IF FND_MSG_PUB.Check_Msg_Level
3261 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3262 THEN
3263 FND_MSG_PUB.Add_Exc_Msg
3264 ( G_PKG_NAME ,
3265 l_api_name
3266 );
3267 END IF;
3268 FND_MSG_PUB.get
3269 ( p_msg_index_out => x_msg_count ,
3270 p_data => x_msg_data
3271 );
3272 x_msg_data := substr(x_msg_data,1,2000);
3273 END update_pm_last_cyclic_act;
3274
3275 procedure update_pm_last_service_reading
3276 ( p_api_version IN NUMBER ,
3277 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
3278 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
3279 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
3280 x_return_status OUT NOCOPY VARCHAR2 ,
3281 x_msg_count OUT NOCOPY NUMBER ,
3282 x_msg_data OUT NOCOPY VARCHAR2 ,
3283 p_pm_schedule_id IN NUMBER
3284 )
3285 is
3286 l_api_name CONSTANT VARCHAR2(30) :='update_pm_last_service_reading';
3287 l_api_version CONSTANT NUMBER := 1.0;
3288 x_error_message VARCHAR2(2000);
3289
3290 l_rescheduling_point number;
3291 l_last_service_date date;
3292 l_act_assoc_id number;
3293 l_pm_schedule_id number;
3294 l_return_status varchar2(1);
3295 lsr number;
3296 l_msg_count number;
3297 l_msg_data varchar2(2000);
3298 l_get_failed varchar2(1);
3299
3300 cursor c_pm_runtime_rule is
3301 select pm_schedule_id,
3302 meter_id,
3303 rule_id
3304 from eam_pm_scheduling_rules
3305 where pm_schedule_id = l_pm_schedule_id
3306 and rule_type = 2;
3307
3308 BEGIN
3309
3310 l_pm_schedule_id := p_pm_schedule_id;
3311
3312 -- Standard Start of API savepoint
3313 SAVEPOINT update_pm_last_service_reading;
3314
3315 x_return_status := FND_API.G_RET_STS_SUCCESS;
3316
3317 --mandatory checking
3318
3319 if (p_pm_schedule_id is null) then
3320 FND_MESSAGE.SET_NAME ('EAM', 'EAM_PM_SCHEDULE_ID_MISSING');
3321 FND_MSG_PUB.Add;
3322 RAISE FND_API.G_EXC_ERROR;
3323 end if;
3324
3325 --call to get the last activity of the pm schedule
3326
3327 eam_pmdef_pub.get_pm_last_activity(p_pm_schedule_id => l_pm_schedule_id,
3328 p_activity_association_id =>l_act_assoc_id,
3329 x_return_status => l_return_status,
3330 x_msg_count => l_msg_count,
3331 x_msg_data => l_msg_data);
3332
3333 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3334 l_get_failed := 'Y';
3335 RAISE FND_API.G_EXC_ERROR;
3336 end if;
3337
3338 --get the last service reading and update the same in epsr
3339
3340 for runtime_rec in c_pm_runtime_rule loop
3341
3342 select last_service_reading into lsr
3343 from eam_pm_last_service
3344 where meter_id = runtime_rec.meter_id and activity_association_id = l_act_assoc_id;
3345
3346 update eam_pm_scheduling_rules set last_service_reading = lsr,
3347 last_update_date=sysdate,
3348 last_updated_by=fnd_global.user_id,
3349 last_update_login=fnd_global.login_id
3350 where pm_schedule_id=runtime_rec.pm_schedule_id and
3351 meter_id = runtime_rec.meter_id and
3352 rule_id = runtime_rec.rule_id;
3353
3354 end loop;
3355
3356 --standard commit to be checked
3357 IF FND_API.To_Boolean( p_commit ) THEN
3358 COMMIT WORK;
3359 END IF;
3360
3361 EXCEPTION
3362 WHEN FND_API.G_EXC_ERROR THEN
3363 ROLLBACK TO update_pm_last_service_reading;
3364 x_return_status := FND_API.G_RET_STS_ERROR ;
3365 if l_get_failed = 'Y' then
3366 x_msg_count := l_msg_count;
3367 x_msg_data := l_msg_data;
3368 else
3369 FND_MSG_PUB.get
3370 ( p_msg_index_out => x_msg_count ,
3371 p_data => x_msg_data
3372 );
3373 x_msg_data := substr(x_msg_data,1,2000);
3374 end if;
3375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3376 ROLLBACK TO update_pm_last_service_reading;
3377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3378 FND_MSG_PUB.get
3379 ( p_msg_index_out => x_msg_count ,
3380 p_data => x_msg_data
3381 );
3382 x_msg_data := substr(x_msg_data,1,2000);
3383 WHEN OTHERS THEN
3384 ROLLBACK TO update_pm_last_service_reading;
3385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3386 IF FND_MSG_PUB.Check_Msg_Level
3387 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3388 THEN
3389 FND_MSG_PUB.Add_Exc_Msg
3390 ( G_PKG_NAME ,
3391 l_api_name
3392 );
3393 END IF;
3394 FND_MSG_PUB.get
3395 ( p_msg_index_out => x_msg_count ,
3396 p_data => x_msg_data
3397 );
3398 x_msg_data := substr(x_msg_data,1,2000);
3399 END update_pm_last_service_reading;
3400
3401
3402
3403
3404 END;
3405
[Home] [Help]