1 PACKAGE BODY EAM_METERS_UTIL AS
2 /* $Header: EAMETERB.pls 120.25 2006/05/15 05:58:26 sshahid ship $ */
3
4 -- Standard who
5 g_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
6 g_created_by NUMBER(15) := FND_GLOBAL.USER_ID;
7 g_last_update_login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
8 g_request_id NUMBER(15) := FND_GLOBAL.CONC_REQUEST_ID;
9 g_program_application_id NUMBER(15) := FND_GLOBAL.PROG_APPL_ID;
10 g_program_id NUMBER(15) := FND_GLOBAL.CONC_PROGRAM_ID;
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_METERS_UTIL';
13
14
15 /**
16 * This is a private helper function that retrieves the activity association id
17 * given the wip entity id.
18 */
19 function get_activity_assoc_id(p_wip_entity_id number)
20 return number IS
21 x_pm_id number;
22 x_activity_assoc_id number;
23 begin
24
25 select activity_association_id into x_activity_assoc_id
26 from mtl_eam_asset_activities meaa, wip_discrete_jobs wdj
27 where meaa.asset_activity_id = wdj.primary_item_id
28 and meaa.maintenance_object_id = wdj.maintenance_object_id
29 and meaa.maintenance_object_type = wdj.maintenance_object_type
30 and wdj.wip_entity_id = p_wip_entity_id;
31
32 return x_activity_assoc_id;
33
34 exception
35 when no_data_found then
36 return null;
37 when others then
38 return null;
39 end ;
40
41
42 /**
43 * This function is used to calcuate the meter usage rate. The algorithm it
44 * uses put equal weight on each individual meter reading.
45 */
46 function get_meter_usage_rate(p_meter_id in number,
47 p_user_defined_rate in number,
48 p_use_past_reading in number)
49 return number IS
50
51 l_count number;
52 x_average number;
53
54 x_first_reading number;
55 x_first_reading_date date ;
56 x_last_reading number;
57 x_last_reading_date date ;
58
59 cursor C is
60 select * from (
61 select life_to_date_reading,
62 VALUE_TIMESTAMP
63 from csi_counter_readings
64 where counter_id = p_meter_id
65 and (reset_mode <> 'SOFT' or reset_mode is null )
66 and NVL(disabled_flag,'N')<>'Y'
67 order by VALUE_TIMESTAMP desc) where rownum <3;
68
69 BEGIN
70 l_count := 0;
71 x_average := 0;
72 select count (*) INTO l_count
73 from csi_counter_readings
74 where counter_id = p_meter_id
75 and (reset_mode <> 'SOFT' or reset_mode is null )
76 and NVL(disabled_flag,'N')<>'Y'
77 order by VALUE_TIMESTAMP desc;
78
79
80 if ( l_count = 0 ) then
81 return p_user_defined_rate;
82 end if;
83
84 if ( trunc(p_use_past_reading) >= 1 ) then
85 if ( l_count < p_use_past_reading OR (p_use_past_reading = 1 and l_count = 1)) then
86 return p_user_defined_rate;
87 end if;
88
89 if p_use_past_reading = 1 then
90
91 open C;
92 fetch C into x_first_reading, x_first_reading_date;
93 fetch C into x_last_reading, x_last_reading_date;
94 close C;
95
96 x_average := trunc (( x_first_reading - x_last_reading) / (x_first_reading_date - x_last_reading_date) , 6);
97 return ABS (x_average);
98
99 end if;
100
101 select
102 trunc ((SUM(life_to_date_reading * (current_reading_date-sysdate))
103 - SUM (life_to_date_reading) * SUM (current_reading_date-sysdate) / count(row_id))/
104 (SUM((current_reading_date-sysdate) * (current_reading_date-sysdate))
105 - SUM (current_reading_date-sysdate) * SUM (current_reading_date-sysdate) /
106 count(row_id)) , 6)
107 INTO x_average
108 from
109 (
110 select ccr.value_timestamp current_reading_date,
111 ccr.life_to_date_reading,
112 rownum row_id
113 from
114 csi_counter_readings ccr
115 where
116 ccr.counter_id = p_meter_id and
117 (reset_mode <> 'SOFT' or reset_mode is null ) and
118 NVL(disabled_flag,'N')<>'Y'
119 order by VALUE_TIMESTAMP desc
120 )
121 where rownum <= p_use_past_reading + 1;
122
123 if ( x_average IS NULL OR x_average = 0) then
124 return p_user_defined_rate;
125 end if;
126
127 return ABS (x_average);
128 else
129 return p_user_defined_rate;
130 end if;
131
132 EXCEPTION
133 when others then
134 return p_user_defined_rate;
135 END get_meter_usage_rate;
136
137 /**
138 This function uses above function to get usage rate.
139 */
140 function get_meter_usage_rate(p_meter_id in number)
141 return number IS
142 l_user_defined_rate number;
143 l_use_past_reading number;
144 begin
145 select default_usage_rate,use_past_reading
146 into l_user_defined_rate, l_use_past_reading
147 from csi_counters_b
148 where counter_id = p_meter_id;
149
150 return get_meter_usage_rate(p_meter_id,l_user_defined_rate,l_use_past_reading);
151
152 end get_meter_usage_rate;
153 /**
154 * This function is used to tell whether there is any mandatory meter reading
155 * or not for completing the given work order.
156 */
157 function has_mandatory_meter_reading(p_wip_entity_id in number)
158 return boolean is
159 x_pm_id number ;
160 x_maintenance_object_id number;
161
162 cursor meters_csr(pp_maintenance_object_id number) is
163 select counter_id meter_id from csi_counter_associations
164 where source_object_id = pp_maintenance_object_id;
165
166 begin
167
168 select maintenance_object_id into x_maintenance_object_id
169 from wip_discrete_jobs
170 where wip_entity_id = p_wip_entity_id;
171
172 for a_meter in meters_csr(x_maintenance_object_id) LOOP
173 if (is_meter_reading_mandatory(p_wip_entity_id,
174 a_meter.meter_id)) then
175 return true;
176
177 end if;
178 END LOOP;
179
180 return false;
181
182 exception
183 when no_data_found then
184 return false;
185 when others then
186 return false;
187
188 end has_mandatory_meter_reading;
189
190
191
192 /**
193 * This function is used to determine whether the meter reading is mandatory or not
194 * for the given wo. It should be called in the meter reading form when
195 * referenced by the completion page. It will return false if any one of the passed
196 * parameter is invalid.
197 */
198 function is_meter_reading_mandatory(p_wip_entity_id in number,
199 p_meter_id in number)
200 return boolean IS
201
202 x_activity_assoc_id number;
203 x_result boolean;
204 l_required_flag varchar2(1);
205 l_required_flag_b boolean;
206
207 begin
208
209 begin
210 select eam_required_flag
211 into l_required_flag
212 from csi_counters_b
213 where counter_id = p_meter_id;
214
215 if nvl(l_required_flag, 'N') = 'Y' then
216 l_required_flag_b := true;
217 else
218 l_required_flag_b := false;
219 end if;
220 exception
221 when no_data_found then
222 l_required_flag_b := false;
223 when others then
224 l_required_flag_b := false;
225 end;
226
227 x_activity_assoc_id := get_activity_assoc_id(p_wip_entity_id);
228
229 if (x_activity_assoc_id is null) then
230 if (l_required_flag_b) then
231 return true;
232 else
233 return false;
234 end if;
235 else
236 x_result := is_meter_reading_required(x_activity_assoc_id, p_meter_id);
237 return (x_result or l_required_flag_b) ;
238 end if;
239
240 end is_meter_reading_mandatory;
241
242
243 /**
244 * This function is used to determine whether the meter reading is mandatory
245 * for the given wo or not. It should be called in the meter reading form when
246 * referenced by the completion page.
247 */
248 function is_meter_reading_mandatory_v(p_wip_entity_id in number,
249 p_meter_id in number)
250 return varchar2 IS
251 BEGIN
252 IF(is_meter_reading_mandatory(p_wip_entity_id, p_meter_id)) then
253 return 'Y';
254 else
255 return 'N';
256 END IF;
257 END;
258
259 /* This function determines if the Last Service Reading of the meter for
260 ** the asset activity association is mandatory by checking if the meter
261 * is used in any of the PM defined for the association.
262 */
263 function is_meter_reading_required(p_activity_assoc_id in number,
264 p_meter_id in number)
265 return boolean IS
266
267 cursor C is
268 select used_in_scheduling
269 from csi_counters_b
270 where counter_id = p_meter_id
271 and SYSDATE BETWEEN nvl(start_date_active, SYSDATE-1) AND nvl(end_date_active, SYSDATE+1);
272
273 x_used_in_scheduling varchar2(1);
274 x_pm_id number;
275 begin
276 x_used_in_scheduling := 'N';
277 open C;
278 fetch C into x_used_in_scheduling;
279 if ( C%NOTFOUND ) then
280 close C;
281 return false;
282 end if;
283 close C;
284
285 if ( x_used_in_scheduling <> 'Y' ) then
286 return false;
287 end if;
288 return mr_mandatory_for_pm(p_activity_assoc_id, p_meter_id);
289
290 exception
291 when no_data_found then
292 return false;
293 when others then
294 return false;
295 END;
296
297
298 /**
299 * This procedure determines if the Last Service Reading of the meter for
300 * the asset activity association is mandatory by checking if the meter
301 * is used in any of the PM defined for the association. If it is required,
302 * then the function returns 'Y', otherwise 'N'.
303 */
304 function is_meter_reading_required_v(p_activity_assoc_id in number,
305 p_meter_id in number)
306 return varchar2 IS
307 BEGIN
308 IF(is_meter_reading_required(p_activity_assoc_id, p_meter_id)) then
309 return 'Y';
310 else
311 return 'N';
312 END IF;
313 return 'N';
314 END;
315
316
317 /**
318 * This procedure updates the last service reading of the meter for the
319 * asset activity association. It also recursively updates the meter readings
320 * of the child activity association in the suppression hierarchy.
321 */
322
323 procedure update_last_service_reading(p_wip_entity_id in number,
324 p_activity_assoc_id in number,
325 p_meter_id in number,
326 p_meter_reading in number) IS
327 cursor C is
328 select sup.child_association_id
329 from eam_suppression_relations sup
330 where sup.parent_association_id = p_activity_assoc_id;
331
332
333 x_child_aa number;
334 x_count number;
335 BEGIN
336 -- populate the previous service reading field with the old last service reading value
337 /* Following select and if condition are
338 Added for bug no : 2756121 */
339 select count(*) into x_count from eam_pm_last_service
340 where
341 meter_id = p_meter_id and
342 activity_association_id = p_activity_assoc_id;
343
344 if(x_count = 0) then
345 insert into eam_pm_last_service(
346 meter_id,
347 activity_association_id,
348 last_service_reading,
349 wip_entity_id,
350 creation_date,
351 created_by,
352 last_update_login,
353 last_updated_by,
354 last_update_date)
355 values(
356 p_meter_id,
357 p_activity_assoc_id,
358 p_meter_reading,
359 p_wip_entity_id,
360 SYSDATE,
361 g_created_by,
362 g_last_update_login,
363 g_last_updated_by,
364 SYSDATE
365 );
366 else
367 update eam_pm_last_service
368 set prev_service_reading = last_service_reading
369 where meter_id = p_meter_id
370 and activity_association_id = p_activity_assoc_id;
371
372 update eam_pm_last_service
373 set last_service_reading = p_meter_reading,
374 wip_entity_id = p_wip_entity_id
375 where meter_id = p_meter_id
376 and activity_association_id = p_activity_assoc_id;
377 end if;
378 open C;
379 LOOP
380 fetch C into x_child_aa;
381 EXIT WHEN ( C%NOTFOUND );
382 update_last_service_reading(p_wip_entity_id, x_child_aa, p_meter_id, p_meter_reading);
383 END LOOP;
384 close C;
385
386 exception
387 when NO_DATA_FOUND then
388 return;
389 when others then
390 return;
391 END;
392
393
394 /**
395 * This procedure updates the last service reading of the meter for the
396 * asset activity association. It also recursively updates the meter readings
397 * of the child activity association in the suppression hierarchy.
398 */
399 procedure update_last_service_reading_wo(p_wip_entity_id in number,
400 p_meter_id in number,
401 p_meter_reading in number,
402 p_wo_end_date in date,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_data OUT NOCOPY VARCHAR2) IS
406 x_assoc_id number;
407 l_api_name CONSTANT VARCHAR2(30) := 'Meter_Utils';
408 l_api_version CONSTANT NUMBER := 1.0;
409 l_last_service_end_date date;
410 begin
411 SAVEPOINT EAM_METERS_UTIL;
412 x_assoc_id := get_activity_assoc_id(p_wip_entity_id);
413
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415 --bug 3762560: if x_assoc_id is null, then don't process anything, just return
416 IF x_assoc_id is not null then
417 select last_service_end_date into l_last_service_end_date
418 from mtl_eam_asset_activities
419 where activity_association_id=x_assoc_id;
420
421 if (l_last_service_end_date <= p_wo_end_date) then
422 update_last_service_reading(p_wip_entity_id, x_assoc_id, p_meter_id, p_meter_reading);
423 /* Shifted above the if condition as FIX for bug no :2752841 */
424 end if;
425 END IF;
426
427 -- Standard call to get message count and if count is 1, get message info.
428 FND_MSG_PUB.Count_And_Get
429 ( p_count => x_msg_count ,
430 p_data => x_msg_data
431 );
432 EXCEPTION
433 WHEN FND_API.G_EXC_ERROR THEN
434 ROLLBACK TO EAM_METERS_UTIL;
435 x_return_status := FND_API.G_RET_STS_ERROR ;
436 FND_MSG_PUB.Count_And_Get
437 ( p_count => x_msg_count ,
438 p_data => x_msg_data
439 );
440 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
441 ROLLBACK TO EAM_METERS_UTIL;
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
443 FND_MSG_PUB.Count_And_Get
444 ( p_count => x_msg_count ,
445 p_data => x_msg_data
446 );
447 WHEN OTHERS THEN
448 ROLLBACK TO EAM_METERS_UTIL;
449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
450 IF FND_MSG_PUB.Check_Msg_Level
451 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
452 THEN
453 FND_MSG_PUB.Add_Exc_Msg
454 ( G_PKG_NAME ,
455 l_api_name
456 );
457 END IF;
458 FND_MSG_PUB.Count_And_Get
459 ( p_count => x_msg_count ,
460 p_data => x_msg_data
461 );
462 end;
463
464
465
466
467 /**
468 * This procedure updates the last service start/end date for the
469 * asset activity association. It also recursively updates dates
470 * of the child activity association in the suppression hierarchy.
471 */
472 procedure update_last_service_dates_wo(p_wip_entity_id in number,
473 p_start_date in date,
474 p_end_date in date,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2) IS
478 x_assoc_id number;
479 l_api_name CONSTANT VARCHAR2(30) := 'Meter_Utils';
480 l_api_version CONSTANT NUMBER := 1.0;
481 l_pm_schedule_id NUMBER;
482 l_cycle_id NUMBER;
483 l_seq_id NUMBER;
484 --5151820
485 l_pm_cycle_id NUMBER;
486 l_pm_seq_id NUMBER;
487 l_pm_seq number;
488 l_wo_seq number;
489 begin
490 SAVEPOINT EAM_METERS_UTIL;
491
492 x_assoc_id := get_activity_assoc_id(p_wip_entity_id);
493
494 if x_assoc_id is not null then
495
496 update_last_service_dates(p_wip_entity_id, x_assoc_id, p_start_date, p_end_date);
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498
499 --check if workorder is pm suggested workorder or not
500 BEGIN
501 SELECT wdj.pm_schedule_id,ewod.cycle_id,ewod.seq_id
502 INTO l_pm_schedule_id,l_cycle_id,l_seq_id
503 FROM WIP_DISCRETE_JOBS wdj,EAM_WORK_ORDER_DETAILS ewod
504 WHERE wdj.wip_entity_id = p_wip_entity_id and
505 ewod.wip_entity_id = wdj.wip_entity_id ;
506
507 if ( l_pm_schedule_id is not null) then
508
509 --5151820 added to get pm cycle and seq
510 select current_cycle,current_seq into l_pm_cycle_id,l_pm_seq_id from
511 eam_pm_schedulings where pm_schedule_id =l_pm_schedule_id;
512
513 -- 5151820 update pm only when pm cycle and seq are less than or equal to that of work order
514 --concatenating both the attributes and comparing below
515 l_pm_seq := to_number(to_char(l_pm_cycle_id) || to_char(l_pm_seq_id));
516 l_wo_seq := to_number(to_char(l_cycle_id) || to_char(l_seq_id));
517
518 if l_pm_seq < l_wo_seq then
519
520 UPDATE EAM_PM_SCHEDULINGS
521 SET current_cycle = l_cycle_id,
522 current_seq = l_seq_id ,
523 current_wo_seq = l_seq_id,
524 last_update_date=sysdate,
525 last_updated_by=g_last_updated_by,
526 last_update_login=g_last_update_login
527 WHERE pm_schedule_id = l_pm_schedule_id ;
528
529 end if;
530
531 --if pm generate workorder then update last cyclic actviity of the PM
532 EAM_PMDEF_PUB.Update_Pm_Last_Cyclic_Act
533 ( X_Return_Status => x_return_status,
534 p_api_version => 1.0 ,
535 p_commit => FND_API.G_FALSE ,
536 X_msg_count => x_msg_count ,
537 X_msg_data => x_msg_data ,
538 p_pm_schedule_id =>l_pm_schedule_id
539 );
540 end if;
541
542
543 EXCEPTION
544 WHEN NO_DATA_FOUND THEN
545 NULL;
546 WHEN OTHERS THEN
547 x_return_status := FND_API.G_RET_STS_ERROR;
548 END;
549
550
551
552 END IF;
553
554 -- Standard call to get message count and if count is 1, get message info.
555 FND_MSG_PUB.Count_And_Get
556 ( p_count => x_msg_count ,
557 p_data => x_msg_data
558 );
559 EXCEPTION
560 WHEN FND_API.G_EXC_ERROR THEN
561 ROLLBACK TO EAM_METERS_UTIL;
562 x_return_status := FND_API.G_RET_STS_ERROR ;
563 FND_MSG_PUB.Count_And_Get
564 ( p_count => x_msg_count ,
565 p_data => x_msg_data
566 );
567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
568 ROLLBACK TO EAM_METERS_UTIL;
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
570 FND_MSG_PUB.Count_And_Get
571 ( p_count => x_msg_count ,
572 p_data => x_msg_data
573 );
574 WHEN OTHERS THEN
575 ROLLBACK TO EAM_METERS_UTIL;
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
577 IF FND_MSG_PUB.Check_Msg_Level
578 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
579 THEN
580 FND_MSG_PUB.Add_Exc_Msg
581 ( G_PKG_NAME ,
582 l_api_name
583 );
584 END IF;
585 FND_MSG_PUB.Count_And_Get
586 ( p_count => x_msg_count ,
587 p_data => x_msg_data
588 );
589 end;
590
591
592 /**
593 * This procedure is a wrapper over update_last_service_dates
594 * This is getting called from
595 * EAMPLNWB.fmb -> MASS_COMPLETE block -> Work_Order_Completion
596 * procedure. Do not call this from other locations
597 */
598 procedure updt_last_srvc_dates_wo_wpr (p_wip_entity_id in number,
599 p_start_date in date,
600 p_end_date in date,
601 x_return_status OUT NOCOPY VARCHAR2,
602 x_msg_count OUT NOCOPY NUMBER,
603 x_msg_data OUT NOCOPY VARCHAR2) IS
604 x_assoc_id number;
605 l_api_name CONSTANT VARCHAR2(30) := 'Meter_Utils';
606 l_api_version CONSTANT NUMBER := 1.0;
607 l_pm_schedule_id NUMBER;
608 l_cycle_id NUMBER;
609 l_seq_id NUMBER;
610 begin
611 SAVEPOINT EAM_METERS_UTIL_NEW;
612 x_assoc_id := get_activity_assoc_id(p_wip_entity_id);
613
614 if x_assoc_id is not null then
615 update_last_service_dates(p_wip_entity_id, x_assoc_id, p_start_date, p_end_date);
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617
618 --check if workorder is pm suggested workorder or not
619 BEGIN
620 SELECT wdj.pm_schedule_id,ewod.cycle_id,ewod.seq_id
621 INTO l_pm_schedule_id,l_cycle_id,l_seq_id
622 FROM WIP_DISCRETE_JOBS wdj,EAM_WORK_ORDER_DETAILS ewod
623 WHERE wdj.wip_entity_id = p_wip_entity_id and
624 ewod.wip_entity_id = wdj.wip_entity_id ;
625
626 if(l_pm_schedule_id is not null) then
627
628 UPDATE EAM_PM_SCHEDULINGS
629 SET current_cycle = l_cycle_id,
630 current_seq = l_seq_id ,
631 current_wo_seq = l_seq_id,
632 last_update_date=sysdate,
633 last_updated_by=g_last_updated_by,
634 last_update_login=g_last_update_login
635 WHERE pm_schedule_id = l_pm_schedule_id ;
636
637 --if pm generate workorder then update last cyclic actviity of the PM
638 EAM_PMDEF_PUB.Update_Pm_Last_Cyclic_Act
639 ( X_Return_Status => x_return_status,
640 p_api_version => 1.0 ,
641 p_commit => FND_API.G_FALSE ,
642 X_msg_count => x_msg_count ,
643 X_msg_data => x_msg_data ,
644 p_pm_schedule_id =>l_pm_schedule_id
645 );
646 end if;
647
648
649 EXCEPTION
650 WHEN NO_DATA_FOUND THEN
651 NULL;
652 WHEN OTHERS THEN
653 x_return_status := FND_API.G_RET_STS_ERROR;
654 END;
655
656 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
657 COMMIT;
658 ELSE
659 ROLLBACK TO EAM_METERS_UTIL_NEW;
660 END IF;
661 END IF;
662
663 -- Standard call to get message count and if count is 1, get message info.
664 FND_MSG_PUB.Count_And_Get
665 ( p_count => x_msg_count ,
666 p_data => x_msg_data
667 );
668 EXCEPTION
669 WHEN FND_API.G_EXC_ERROR THEN
670 ROLLBACK TO EAM_METERS_UTIL_NEW;
671 x_return_status := FND_API.G_RET_STS_ERROR ;
672 FND_MSG_PUB.Count_And_Get
673 ( p_count => x_msg_count ,
674 p_data => x_msg_data
675 );
676 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
677 ROLLBACK TO EAM_METERS_UTIL_NEW;
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
679 FND_MSG_PUB.Count_And_Get
680 ( p_count => x_msg_count ,
681 p_data => x_msg_data
682 );
683 WHEN OTHERS THEN
684 ROLLBACK TO EAM_METERS_UTIL_NEW;
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
686 IF FND_MSG_PUB.Check_Msg_Level
687 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
688 THEN
689 FND_MSG_PUB.Add_Exc_Msg
690 ( G_PKG_NAME ,
691 l_api_name
692 );
693 END IF;
694 FND_MSG_PUB.Count_And_Get
695 ( p_count => x_msg_count ,
696 p_data => x_msg_data
697 );
698 end;
699
700 /**
701 * This procedure updates the last service start/end date for the
702 * asset activity association. It also recursively updates dates
703 * of the child activity association in the suppression hierarchy.
704 */
705
706 procedure update_last_service_dates( p_wip_entity_id in number,
707 p_activity_assoc_id in number,
708 p_start_date in date,
709 p_end_date in date) IS
710
711 cursor C is
712 select sup.child_association_id
713 from eam_suppression_relations sup
714 where sup.parent_association_id = p_activity_assoc_id;
715
716 cursor pm_schedule(activity_assoc_id NUMBER,l_default VARCHAR2) is
717 select rescheduling_point
718 from eam_pm_schedulings
719 where pm_schedule_id in (SELECT pm_schedule_id
720 FROM eam_pm_activities where activity_association_id=activity_assoc_id)
721 and default_implement=l_default;
722
723
724
725 x_child_aa number;
726 l_schedule_option number:=null;
727 l_default varchar2(1):='Y';
728 l_sch_start_date date;
729 l_sch_end_date date;
730
731 BEGIN
732
733 open pm_schedule(p_activity_assoc_id,l_default);
734 fetch pm_schedule into l_schedule_option;
735 if pm_schedule%NOTFOUND then
736 l_schedule_option:=2;
737 end if;
738 close pm_schedule;
739
740 select wdj.scheduled_start_date
741 ,wdj.scheduled_completion_date
742 into
743 l_sch_start_date
744 ,l_sch_end_date
745 from wip_discrete_jobs wdj
746 where wdj.wip_entity_id=p_wip_entity_id;
747
748 -- backup last date to previous date
749 update mtl_eam_asset_activities
750 set prev_service_start_date=last_service_start_date,
751 prev_service_end_date=last_service_end_date,
752 prev_scheduled_start_date=last_scheduled_start_date,
753 prev_scheduled_end_date=last_scheduled_end_date,
754 PREV_PM_SUGGESTED_START_DATE = LAST_PM_SUGGESTED_START_DATE,
755 PREV_PM_SUGGESTED_END_DATE = LAST_PM_SUGGESTED_END_DATE
756
757 /* Shifted p_start_date,p_end_date for bug #4096193 */
758 where activity_association_id = p_activity_assoc_id
759 and (( decode(l_schedule_option,3,last_scheduled_start_date,
760 4,last_scheduled_end_date,1,last_service_start_date,2,last_service_end_date,5,last_service_start_date,6,last_service_start_date) is null)
761 or ( decode(l_schedule_option,3,last_scheduled_start_date,4,last_scheduled_end_date,
762 1,last_service_start_date,2,last_service_end_date,5,last_service_start_date,6,last_service_start_date) <
763 decode(l_schedule_option,3,l_sch_start_date,4,l_sch_end_date,1,p_start_date,2,p_end_date,5,p_start_date,6,p_start_date)));
764
765 -- copy wdj.scheduled_start/completion_date to meaa.last_scheduled_start/end_date
766 update mtl_eam_asset_activities meaa
767 set (meaa.last_scheduled_start_date,
768 meaa.last_scheduled_end_date,
769 meaa.last_service_start_date, --added for bug #4096193
770 meaa.last_service_end_date,--added for bug #4096193
771 meaa.wip_entity_id,
772 meaa.LAST_PM_SUGGESTED_START_DATE,
773 meaa.LAST_PM_SUGGESTED_END_DATE)
774 = (select wdj.scheduled_start_date,
775 wdj.scheduled_completion_date,
776 p_start_date, --added for bug #4096193
777 p_end_date, --added for bug #4096193
778 wdj.wip_entity_id,
779 ewod.pm_suggested_start_date,
780 ewod.pm_suggested_end_date
781 from wip_discrete_jobs wdj, eam_work_order_details ewod
782 where wdj.wip_entity_id=p_wip_entity_id
783 and wdj.wip_entity_id = ewod.wip_entity_id)
784 where meaa.activity_association_id = p_activity_assoc_id
785 and (( decode(l_schedule_option,3,last_scheduled_start_date,
786 4,last_scheduled_end_date,1,last_service_start_date,
787 2,last_service_end_date,5,last_service_start_date,6,last_service_start_date) is null)
788 or ( decode(l_schedule_option,3,last_scheduled_start_date,4,last_scheduled_end_date,
789 1,last_service_start_date,2,last_service_end_date,5,last_service_start_date,6,last_service_start_date)<
790 decode(l_schedule_option,3,l_sch_start_date,4,l_sch_end_date,1,p_start_date,2,p_end_date,5,p_start_date,6,p_start_date))); --added for bug #4096193
791
792
793 /* Changed above condition to handle when last_service_end_date is null
794 */
795 /*
796 if (SQL%FOUND) then
797 COMMIT;
798 end if;
799 */
800
801 open C;
802 LOOP
803 fetch C into x_child_aa;
804 EXIT WHEN ( C%NOTFOUND );
805 update_last_service_dates(p_wip_entity_id, x_child_aa, p_start_date, p_end_date);
806 END LOOP;
807 close C;
808
809
810 exception
811 when others then
812 --DBMS_OUTPUT.put_line('association_id: ' || p_activity_assoc_id);
813 return;
814 END;
815
816
817 /**
818 * This procedure should be called when resetting a meter. It updates the corresponding
819 * PM schedule rule data if applicable.
820 */
821 procedure reset_meter(p_meter_id in number,
822 p_current_reading in number,
823 p_last_reading in number,
824 p_change_val in number) is
825 cursor C is
826 select 'X'
827 from csi_counters_b
828 where counter_id = p_meter_id
829 and SYSDATE BETWEEN nvl(start_date_active, SYSDATE-1) AND nvl(end_date_active, SYSDATE+1);
830
831 x_temp number;
832 x_dummy varchar2(1);
833 begin
834 -- make sure meter passed in is a valid one
835 open C;
836 fetch C into x_dummy;
837 if ( C%NOTFOUND ) then
838 close C;
839 return;
840 end if;
841 close C;
842
843 x_temp := p_last_reading + p_change_val + p_current_reading;
844 update eam_pm_scheduling_rules
845 set last_service_reading = x_temp - last_service_reading - runtime_interval
846 where meter_id = p_meter_id
847 and rule_type = 2;
848 end reset_meter;
849
850 /**
851 * This procedure calculates the average of the meter readings for the meter
852 */
853 procedure get_average(p_meter_id in number,
854 p_from_date in date,
855 p_to_date in date,
856 x_average OUT NOCOPY number)
857
858 is
859 invalid_meter exception;
860 l_count number;
861 l_user_defined_rate number ;
862 l_use_past_reading number ;
863
864 BEGIN
865 x_average := 0;
866 l_count := 0 ;
867 l_user_defined_rate := 0 ;
868 l_use_past_reading := 0 ;
869 if NOT EAM_COMMON_UTILITIES_PVT.validate_meter_id(p_meter_id) then
870 raise invalid_meter;
871 end if;
872
873 select
874 ABS(
875 trunc ((SUM(life_to_date_reading * (current_reading_date-sysdate))
876 - SUM (life_to_date_reading) * SUM (current_reading_date-sysdate) / count(rowid))/
877 (SUM((current_reading_date-sysdate) * (current_reading_date-sysdate))
878 - SUM (current_reading_date-sysdate) * SUM (current_reading_date-sysdate) /
879 count(rowid)) , 6)
880 )
881 INTO x_average
882 from
883 eam_meter_readings_v
884 where
885 meter_id = p_meter_id
886 and
887 (disable_flag is null or disable_flag = 'N')
888 and
889 reset_flag <> 'Y'
890 and ( p_from_date is null or (current_reading_date > p_from_date))
891 and ( p_to_date is null or (current_reading_date < p_to_date));
892 if ( x_average IS NULL) then
893 x_average := 0;
894 end if;
895
896 EXCEPTION
897
898 when invalid_meter then
899 x_average := 0;
900
901 when no_data_found then
902 x_average := 0;
903
904 when others then
905 x_average := 0;
906 END get_average;
907
908
909 /**
910 * This is a private function. It resursively iterate through the suppression tree
911 * to see whether the meter is used in the sub tree of the given node.
912 */
913 function mr_mandatory_for_pm(p_activity_assoc_id in number,
914 p_meter_id in number) return boolean is
915 cursor C is
916 select epac.activity_association_id
917 from eam_pm_activities epac,
918 eam_pm_schedulings eps,
919 eam_suppression_relations sup
920 where sup.parent_association_id = p_activity_assoc_id
921 and sup.child_association_id = epac.activity_association_id
922 and eps.pm_schedule_id = epac.pm_schedule_id
923 and nvl(eps.from_effective_date, sysdate-1) < sysdate
924 and nvl(eps.to_effective_date, sysdate+1) > sysdate;
925
926 cursor testmr is
927 select 'X'
928 from eam_pm_scheduling_rules pr,
929 eam_pm_activities epa,
930 csi_counters_b ccb
931 where pr.meter_id = ccb.counter_id
932 and epa.activity_association_id = p_activity_assoc_id
933 and pr.pm_schedule_id = epa.pm_schedule_id
934 and pr.rule_type = 2
935 and pr.meter_id = p_meter_id
936 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1);
937
938 x_child_aa number;
939 x_dummy varchar2(1);
940 begin
941
942 open testmr;
943 fetch testmr into x_dummy;
944 if ( NOT testmr%NOTFOUND ) then
945 close testmr;
946 return true;
947 end if;
948 close testmr;
949 open C;
950 LOOP
951 fetch C into x_child_aa;
952 EXIT WHEN ( C%NOTFOUND );
953 if ( mr_mandatory_for_pm(x_child_aa, p_meter_id) ) then
954 close C;
955 return true;
956 end if;
957 END LOOP;
958
959 close C;
960 return false;
961 end mr_mandatory_for_pm;
962
963
964 /**
965 * This is a private function to resursively iterate through the suppression tree
966 * to see whether any one of them needs meter reading.
967 */
968 function pm_need_meter_reading(p_parent_pm_id in number)
969 return boolean is
970 cursor C is
971 select epac.pm_schedule_id
972 from eam_pm_activities epac,
973 eam_pm_schedulings eps,
974 eam_pm_activities epap,
975 eam_suppression_relations sup
976 where epap.activity_association_id = sup.parent_association_id
977 and epap.pm_schedule_id = p_parent_pm_id
978 and sup.child_association_id = epac.activity_association_id
979 and eps.pm_schedule_id = epac.pm_schedule_id
980 and nvl(eps.from_effective_date, sysdate-1) < sysdate
981 and nvl(eps.to_effective_date, sysdate+1) > sysdate;
982
983
984 x_child_pm number;
985 x_num number;
986 begin
987 select count(*) into x_num
988 from eam_pm_scheduling_rules pr,
989 csi_counters_b ccb
990 where pr.meter_id = ccb.counter_id
991 and pr.pm_schedule_id = p_parent_pm_id
992 and pr.rule_type = 2
993 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1);
994
995 if ( x_num > 0 ) then
996 return true;
997 end if;
998
999 open C;
1000 LOOP
1001 fetch C into x_child_pm;
1002 EXIT WHEN ( C%NOTFOUND );
1003 if ( pm_need_meter_reading(x_child_pm) ) then
1004 close C;
1005 return true;
1006 end if;
1007 END LOOP;
1008 close C;
1009
1010 return false;
1011 end pm_need_meter_reading;
1012
1013 /**
1014 * This function checks if a reading date is between a normal meter reading
1015 * and a reset meter reading.
1016 */
1017 /*
1018 * new comment (correcting the original comments for this function)
1019 * this function checks if a reading date is right prior to a reset reading date
1020 */
1021
1022 function cannot_enter_value(p_meter_id in number,
1023 p_reading_date in date)
1024 return boolean is
1025 prev_ceiling_reset_date date;
1026 prev_ceiling_reading_date date;
1027 return_val boolean ;
1028 begin
1029 return_val := false;
1030 -- get min uppper reading that is a reset reading
1031 select min(value_timestamp)
1032 into prev_ceiling_reset_date
1033 from csi_counter_readings
1034 where COUNTER_ID = p_meter_id
1035 and value_timestamp > p_reading_date
1036 and reset_mode = 'SOFT'
1037 and NVL(disabled_flag,'N')<>'Y';
1038
1039 -- get min uppper reading that is not a reset reading
1040 select min(value_timestamp)
1041 into prev_ceiling_reading_date
1042 from csi_counter_readings
1043 where COUNTER_ID = p_meter_id
1044 and value_timestamp > p_reading_date
1045 and (reset_mode <> 'SOFT' or reset_mode is null)
1046 and NVL(disabled_flag,'N')<>'Y';
1047
1048
1049 if prev_ceiling_reset_date is not null then
1050 if prev_ceiling_reading_date is not null then
1051 if (prev_ceiling_reading_date > prev_ceiling_reset_date) then
1052 return_val := true;
1053 end if;
1054 else
1055 -- null;
1056 return_val := true;
1057 end if;
1058 end if;
1059 return return_val ;
1060 end cannot_enter_value;
1061
1062
1063
1064 /**
1065 * This function checks if the reading date is a reset reading date
1066 */
1067 function cannot_update_reset(p_meter_id in number,
1068 p_reading_date in date)
1069 return boolean is
1070 curr_rdg number;
1071 prev_ceiling_reading_date date;
1072 return_val boolean ;
1073 begin
1074 return_val := false;
1075
1076 -- get min uppper reading that is not a reset reading
1077 select min(value_timestamp)
1078 into prev_ceiling_reading_date
1079 from csi_counter_readings
1080 where COUNTER_ID = p_meter_id
1081 and value_timestamp > p_reading_date
1082 and (reset_mode <> 'SOFT' or reset_mode is null)
1083 and NVL(disabled_flag,'N')<>'Y';
1084
1085
1086
1087 if (eam_meters_util.reset_reading_exists(p_meter_id,p_reading_date) = true
1088 and prev_ceiling_reading_date is not null) then
1089 return_val := true;
1090 end if;
1091 return return_val;
1092
1093 exception
1094 when no_data_found then
1095 return false;
1096 when others then
1097 return false;
1098 end cannot_update_reset;
1099
1100
1101
1102 /**
1103 * This function checks if a particular reading is a reset reading
1104 */
1105
1106 function reset_reading_exists (p_meter_id in number,
1107 p_reading_date in date)
1108 return boolean is
1109 return_val boolean;
1110 curr_rdg number;
1111 begin
1112 return_val := false;
1113 select counter_reading
1114 into curr_rdg
1115 from csi_counter_readings
1116 where COUNTER_ID = p_meter_id
1117 and value_timestamp = p_reading_date
1118 and (reset_mode <> 'SOFT' or reset_mode is null)
1119 and NVL(disabled_flag,'N')<>'Y';
1120
1121
1122 if (curr_rdg is not null) then
1123 return_val := true;
1124 end if;
1125 return return_val;
1126 exception
1127
1128 when no_data_found then
1129 return false;
1130 when others then
1131 return false;
1132
1133 end reset_reading_exists;
1134
1135
1136 /**
1137 * This function checks if a particular reading is a normal reading right prior to a reset reading
1138 */
1139
1140 /*
1141 function normal_reading_before_reset ( p_meter_reading_id in number)
1142 return boolean is
1143 l_reset_flag varchar(1);
1144 l_next_reset_flag varchar(1);
1145 l_reading_date date;
1146 l_meter_id number;
1147 l_next_reading_date date;
1148 begin
1149 select current_reading_date, meter_id
1150 into x_reading_date, x_meter_id
1151 from eam_meter_readings where meter_reading_id=p_meter_reading_id;
1152 -- dbms_output.put_line(x_reading_date);
1153 -- dbms_output.put_line(x_meter_id);
1154 select reset_flag into x_reset_flag from eam_meter_readings
1155 where meter_reading_id = p_meter_reading_id;
1156 -- dbms_output.put_line(x_reset_flag);
1157 if (x_reset_flag is not null and x_reset_flag = 'Y') then
1158 return FALSE;
1159 end if;
1160
1161 -- dbms_output.put_line('beforequery');
1162
1163 select min(current_reading_date) into next_reading_date
1164 from eam_meter_readings
1165 where meter_id = x_meter_id
1166 AND current_reading_date > x_reading_date
1167 and (disable_flag is null or disable_flag = 'N');
1168
1169 -- dbms_output.put_line('beforequery2');
1170 if (next_reading_date is not null) then
1171 select reset_flag into next_reset_flag
1172 from eam_meter_readings
1173 where meter_id = x_meter_id
1174 AND current_reading_date =next_reading_date
1175 and (disable_flag is null or disable_flag = 'N');
1176 end if;
1177
1178 if (next_reset_flag is not null and next_reset_flag='Y') then
1179 return TRUE;
1180 else
1181 return FALSE;
1182 end if;
1183 end normal_reading_before_reset;
1184 */
1185
1186
1187 /* following function checks if there exists any readings after the
1188 specific reading date */
1189 function next_reading_exists(p_meter_id in number, p_reading_date in date)
1190 return boolean
1191 is
1192 l_next_reading_date date;
1193 begin
1194 select min(value_timestamp)
1195 into l_next_reading_date
1196 from csi_counter_readings
1197 where COUNTER_ID = p_meter_id
1198 and value_timestamp > p_reading_date
1199 and (disabled_flag <> 'Y');
1200 if (l_next_reading_date is not null) then
1201 return true;
1202 else
1203 return false;
1204 end if;
1205 end next_reading_exists;
1206
1207
1208 /* following function determines whether a non-disabled reading
1209 exists on p_reading_date for meter p_meter_id
1210 */
1211 function reading_exists(p_meter_id IN NUMBER,
1212 p_reading_date IN date)
1213 return boolean
1214 is
1215 return_val boolean;
1216 l_meter_reading_id number;
1217 begin
1218 return_val := false;
1219 select COUNTER_VALUE_ID into l_meter_reading_id
1220 from csi_counter_readings
1221 where
1222 COUNTER_ID=p_meter_id and
1223 value_timestamp = p_reading_date
1224 and NVL(disabled_flag,'N')<>'Y';
1225
1226 if (l_meter_reading_id is not null) then
1227 return_val:=true;
1228 end if;
1229 return return_val;
1230 exception
1231 when no_data_found then
1232 return false;
1233 when others then
1234 return false;
1235 end reading_exists;
1236
1237
1238 /* This function determines whether a new meter reading would
1239 violate the ascending or descending order of the meter.
1240 It compares the current reading with the previous meter reading for this meter,
1241 and if the next meter readign is not a reset reading, it compares the
1242 new reading with the next meter reading for this meter.
1243 If there is violation, "true" is returned; otherwise, "false" is
1244 returned. */
1245
1246
1247 function violate_order(p_meter_id in number,
1248 p_reading_date in date,
1249 p_current_reading in number)
1250 return boolean
1251 is
1252 l_prev_reading_date date;
1253 l_prev_reading number;
1254 l_next_reading_date date;
1255 l_next_reading number;
1256 l_next_reset varchar2(1);
1257 l_meter_type number;
1258 return_val boolean ;
1259
1260 begin
1261 return_val := false;
1262
1263 select max(value_timestamp) into l_prev_reading_date
1264 from csi_counter_readings
1265 where COUNTER_ID = p_meter_id
1266 and value_timestamp < p_reading_date
1267 and NVL(disabled_flag,'N')<>'Y';
1268
1269 if (l_prev_reading_date is not null) then
1270 select counter_reading into l_prev_reading
1271 from csi_counter_readings
1272 where COUNTER_ID = p_meter_id
1273 and value_timestamp = l_prev_reading_date
1274 and NVL(disabled_flag,'N')<>'Y';
1275 end if;
1276
1277 select min(value_timestamp) into l_next_reading_date
1278 from csi_counter_readings
1279 where COUNTER_ID = p_meter_id
1280 and value_timestamp > p_reading_date
1281 and NVL(disabled_flag,'N')<>'Y';
1282
1283 if (l_next_reading_date is not null) then
1284 select counter_reading, decode(reset_mode,'SOFT','Y','N') reset_flag
1285 into l_next_reading, l_next_reset
1286 from csi_counter_readings
1287 where value_timestamp = l_next_reading_date
1288 and COUNTER_ID = p_meter_id
1289 and NVL(disabled_flag,'N')<>'Y';
1290 end if;
1291
1292
1293 if (l_prev_reading is not null or
1294 (l_next_reading is not null and
1295 (l_next_reset is null or l_next_reset='N'))) then
1296 select direction into l_meter_type
1297 from csi_counters_b
1298 where counter_id = p_meter_id;
1299 else
1300 return false;
1301 end if;
1302
1303 if (l_prev_reading is not null) then
1304 if ((l_prev_reading > p_current_reading and l_meter_type = 1)
1305 or (l_prev_reading < p_current_reading and l_meter_type=2))
1306 then
1307 return true;
1308 end if;
1309 end if;
1310
1311 if (l_next_reading is not null and (l_next_reset is null or l_next_reset='N')) then
1312 if ((p_current_reading > l_next_reading and l_meter_type=1)
1313 or (p_current_reading < l_next_reading and l_meter_type=2))
1314 then
1315 return true;
1316 end if;
1317 end if;
1318 return false;
1319 /*
1320 exception
1321 when no_data_found then
1322 return false;
1323 */
1324 end violate_order;
1325
1326 /**
1327 * This procedure updates LTD readings for disabled change meter readings
1328 */
1329 procedure update_change_meter_ltd(p_meter_id in number,
1330 p_meter_reading_id in number) is
1331 l_reading_date DATE;
1332 l_reading_value number;
1333 l_meter_type number;
1334 begin
1335 if p_meter_id is null OR p_meter_reading_id is null then
1336 return;
1337 end if;
1338
1339 select reading_type into l_meter_type
1340 from csi_counters_b
1341 where counter_id = p_meter_id;
1342
1343 if(l_meter_type <> 2) then
1344 return;
1345 end if;
1346
1347 select VALUE_TIMESTAMP, COUNTER_READING
1348 into l_reading_date, l_reading_value
1349 from csi_counter_readings
1350 where COUNTER_VALUE_ID = p_meter_reading_id;
1351
1352 -- Now update the ltd readings of all readings taken after the disabled reading
1353 update csi_counter_readings
1354 set life_to_date_reading = life_to_date_reading - l_reading_value
1355 where value_timestamp > l_reading_date and counter_id = p_meter_id;
1356
1357 exception
1358 when others then
1359 return;
1360 end update_change_meter_ltd;
1361
1362 /* This function calculates the life_to_date reading for a new reading. */
1363
1364 function calculate_ltd (p_meter_id in number,
1365 p_reading_date in date,
1366 p_new_reading in number,
1367 p_meter_type in number)
1368 return number
1369 is
1370 ltd_value number;
1371 l_prev_reading_date date;
1372 l_prev_reading number;
1373 l_prev_ltd_reading number;
1374
1375 begin
1376 select max(value_timestamp) into l_prev_reading_date
1377 from csi_counter_readings
1378 where COUNTER_ID = p_meter_id
1379 and value_timestamp < p_reading_date
1380 and NVL(disabled_flag,'N')<>'Y';
1381
1382 if (l_prev_reading_date is not null) then
1383 select counter_reading, life_to_date_reading
1384 into l_prev_reading, l_prev_ltd_reading
1385 from csi_counter_readings
1386 where value_timestamp = l_prev_reading_date
1387 and COUNTER_ID = p_meter_id
1388 and NVL(disabled_flag,'N')<>'Y';
1389 end if;
1390 if(p_meter_type = 1) then
1391 if (l_prev_reading is not null) then
1392 ltd_value:=p_new_reading-l_prev_reading+l_prev_ltd_reading;
1393 else
1394 ltd_value:=p_new_reading;
1395 end if;
1396 elsif(p_meter_type = 2) then
1397 ltd_value := p_new_reading + l_prev_ltd_reading;
1398 end if;
1399 return ltd_value;
1400
1401 end calculate_ltd;
1402
1403
1404 /* This function verifies that the meter reading meets the follow criteria:
1405 1. meter reading is not a normal reading before a reset reading
1406 2. meter reading is not a reset reading with any readings after it.
1407 */
1408 function can_be_disabled(p_meter_id number,
1409 p_meter_reading_id number,
1410 x_reason_cannot_disable out nocopy number)
1411 return boolean
1412 is
1413 l_current_reading_date date;
1414 l_next_reading_date date;
1415 l_prev_reading_date date;
1416 l_prev_reset varchar2(1);
1417 l_next_reset varchar2(1);
1418 l_reset varchar2(1);
1419
1420 begin
1421 begin
1422 -- get current reading date and reset flag
1423 select decode(reset_mode,'SOFT','Y','N') reset_flag, VALUE_TIMESTAMP
1424 into l_reset, l_current_reading_date
1425 from CSI_COUNTER_READINGS
1426 where COUNTER_VALUE_ID = p_meter_reading_id
1427 and NVL(disabled_flag,'N')<>'Y';
1428 exception
1429 when no_data_found then
1430 x_reason_cannot_disable:=1;
1431 return false;
1432 end;
1433
1434 -- If current reading does not exist, return false
1435 if l_current_reading_date is null then
1436 x_reason_cannot_disable:=1;
1437 return false;
1438 end if;
1439
1440 -- get next reading date
1441 select min(value_timestamp) into l_next_reading_date
1442 from csi_counter_readings
1443 where COUNTER_ID = p_meter_id
1444 and value_timestamp > l_current_reading_date
1445 and NVL(disabled_flag,'N')<>'Y';
1446
1447 -- if next reading date is null (i.e. no more readings after current reading),
1448 -- return true
1449 if (l_next_reading_date is null) then
1450 return true;
1451 end if;
1452
1453 -- The rest of this function will only the executed if the current
1454 -- reading exists and next reading exists
1455
1456 -- if current reading is reset
1457 if (l_reset is not null and l_reset='Y')then
1458 x_reason_cannot_disable:=2;
1459 return false;
1460 end if;
1461
1462 -- if current reading is not reset, get next reset flag
1463 select decode(reset_mode,'SOFT','Y','N') reset_flag into l_next_reset
1464 from csi_counter_readings
1465 where COUNTER_ID = p_meter_id
1466 and value_timestamp = l_next_reading_date
1467 and NVL(disabled_flag,'N')<>'Y';
1468
1469 -- current reading is normal, and next reading is reset
1470 if (l_next_reset is not null and l_next_reset='Y') then
1471 x_reason_cannot_disable:=3;
1472 return false;
1473 end if;
1474
1475 return true;
1476
1477 end can_be_disabled;
1478
1479
1480 PROCEDURE VALIDATE_USED_IN_SCHEDULING(p_meter_id IN NUMBER,
1481 x_return_status OUT NOCOPY VARCHAR2,
1482 x_msg_count OUT NOCOPY NUMBER,
1483 x_msg_data OUT NOCOPY VARCHAR2)
1484 IS
1485 l_exists VARCHAR2(1);
1486 l_api_name CONSTANT VARCHAR2(30) := 'Meter_Utils';
1487 BEGIN
1488
1489 BEGIN
1490
1491 SELECT 'Y'
1492 INTO l_exists
1493 FROM EAM_PM_SCHEDULING_RULES
1494 WHERE meter_id = p_meter_id
1495 AND rownum<=1;
1496
1497 EXCEPTION
1498 WHEN NO_DATA_FOUND THEN
1499 l_exists := 'N';
1500 END;
1501
1502 IF(l_exists='N') THEN
1503 x_return_status := FND_API.G_RET_STS_SUCCESS;
1504 ELSE
1505 x_return_status := FND_API.G_RET_STS_ERROR ;
1506 fnd_message.set_name('EAM', 'EAM_METER_USED_IN_PM');
1507 fnd_msg_pub.add;
1508 END IF;
1509
1510 -- Standard call to get message count and if count is 1, get message info.
1511 FND_MSG_PUB.Count_And_Get
1512 ( p_count => x_msg_count ,
1513 p_data => x_msg_data
1514 );
1515 EXCEPTION
1516 WHEN FND_API.G_EXC_ERROR THEN
1517 x_return_status := FND_API.G_RET_STS_ERROR ;
1518 FND_MSG_PUB.Count_And_Get
1519 ( p_count => x_msg_count ,
1520 p_data => x_msg_data
1521 );
1522 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1524 FND_MSG_PUB.Count_And_Get
1525 ( p_count => x_msg_count ,
1526 p_data => x_msg_data
1527 );
1528 WHEN OTHERS THEN
1529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1530 IF FND_MSG_PUB.Check_Msg_Level
1531 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1532 THEN
1533 FND_MSG_PUB.Add_Exc_Msg
1534 ( G_PKG_NAME ,
1535 l_api_name
1536 );
1537 END IF;
1538 FND_MSG_PUB.Count_And_Get
1539 ( p_count => x_msg_count ,
1540 p_data => x_msg_data
1541 );
1542 END VALIDATE_USED_IN_SCHEDULING;
1543
1544
1545 END eam_meters_util;
1546
1547
[Home] [Help]