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