1 PACKAGE BODY EAM_METERS_UTIL AS
2 /* $Header: EAMETERB.pls 120.27.12020000.4 2012/11/28 09:57:05 srkotika 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 does not (after bug12919405/15917235) 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 /* commented for bug15917235 - child(suppressed) activity assocation's last service reading should not get updated, like date (bug12919405)
379 open C;
380 LOOP
381 fetch C into x_child_aa;
382 EXIT WHEN ( C%NOTFOUND );
383 update_last_service_reading(p_wip_entity_id, x_child_aa, p_meter_id, p_meter_reading);
384 END LOOP;
385 close C;*/
386
387 exception
388 when NO_DATA_FOUND then
389 return;
390 when others then
391 return;
392 END;
393
394
395 /**
396 * This procedure updates the last service reading of the meter for the
397 * asset activity association. It does not (after bug12919405/15917235) recursively updates the meter readings
398 * of the child activity association in the suppression hierarchy.
399 */
400 procedure update_last_service_reading_wo(p_wip_entity_id in number,
401 p_meter_id in number,
402 p_meter_reading in number,
403 p_wo_end_date in date,
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2) IS
407 x_assoc_id number;
408 l_api_name CONSTANT VARCHAR2(30) := 'Meter_Utils';
409 l_api_version CONSTANT NUMBER := 1.0;
410 l_last_service_end_date date;
411 begin
412 SAVEPOINT EAM_METERS_UTIL;
413 x_assoc_id := get_activity_assoc_id(p_wip_entity_id);
414
415 x_return_status := FND_API.G_RET_STS_SUCCESS;
416 --bug 3762560: if x_assoc_id is null, then don't process anything, just return
417 IF x_assoc_id is not null then
418 select last_service_end_date into l_last_service_end_date
419 from mtl_eam_asset_activities
420 where activity_association_id=x_assoc_id;
421
422 if (l_last_service_end_date <= p_wo_end_date and p_meter_reading <> FND_API.G_MISS_NUM ) then --bug# 14549822, prevents corruption of last_service_reading
423 update_last_service_reading(p_wip_entity_id, x_assoc_id, p_meter_id, p_meter_reading);
424 /* Shifted above the if condition as FIX for bug no :2752841 */
425 end if;
426 END IF;
427
428 -- Standard call to get message count and if count is 1, get message info.
429 FND_MSG_PUB.Count_And_Get
430 ( p_count => x_msg_count ,
431 p_data => x_msg_data
432 );
433 EXCEPTION
434 WHEN FND_API.G_EXC_ERROR THEN
435 ROLLBACK TO EAM_METERS_UTIL;
436 x_return_status := FND_API.G_RET_STS_ERROR ;
437 FND_MSG_PUB.Count_And_Get
438 ( p_count => x_msg_count ,
439 p_data => x_msg_data
440 );
441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
442 ROLLBACK TO EAM_METERS_UTIL;
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
444 FND_MSG_PUB.Count_And_Get
445 ( p_count => x_msg_count ,
446 p_data => x_msg_data
447 );
448 WHEN OTHERS THEN
449 ROLLBACK TO EAM_METERS_UTIL;
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
451 IF FND_MSG_PUB.Check_Msg_Level
452 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
453 THEN
454 FND_MSG_PUB.Add_Exc_Msg
455 ( G_PKG_NAME ,
456 l_api_name
457 );
458 END IF;
459 FND_MSG_PUB.Count_And_Get
460 ( p_count => x_msg_count ,
461 p_data => x_msg_data
462 );
463 end;
464
465
466
467
468 /**
469 * This procedure updates the last service start/end date for the
470 * asset activity association. It does not (after bug12919405) recursively updates dates
471 * of the child activity association in the suppression hierarchy.
472 */
473 procedure update_last_service_dates_wo(p_wip_entity_id in number,
474 p_start_date in date,
475 p_end_date in date,
476 x_return_status OUT NOCOPY VARCHAR2,
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2) IS
479 x_assoc_id number;
480 l_api_name CONSTANT VARCHAR2(30) := 'Meter_Utils';
481 l_api_version CONSTANT NUMBER := 1.0;
482 l_pm_schedule_id NUMBER;
483 l_cycle_id NUMBER;
484 l_seq_id NUMBER;
485 --5151820
486 l_pm_cycle_id NUMBER;
487 l_pm_seq_id NUMBER;
488 l_pm_seq number;
489 l_wo_seq number;
490 begin
491 SAVEPOINT EAM_METERS_UTIL;
492
493 x_assoc_id := get_activity_assoc_id(p_wip_entity_id);
494
495 if x_assoc_id is not null then
496
497 update_last_service_dates(p_wip_entity_id, x_assoc_id, p_start_date, p_end_date);
498 x_return_status := FND_API.G_RET_STS_SUCCESS;
499
500 --check if workorder is pm suggested workorder or not
501 BEGIN
502 SELECT wdj.pm_schedule_id,ewod.cycle_id,ewod.seq_id
503 INTO l_pm_schedule_id,l_cycle_id,l_seq_id
504 FROM WIP_DISCRETE_JOBS wdj,EAM_WORK_ORDER_DETAILS ewod
505 WHERE wdj.wip_entity_id = p_wip_entity_id and
506 ewod.wip_entity_id = wdj.wip_entity_id ;
507
508 if ( l_pm_schedule_id is not null) then
509
510 --5151820 added to get pm cycle and seq
511 select current_cycle,current_seq into l_pm_cycle_id,l_pm_seq_id from
512 eam_pm_schedulings where pm_schedule_id =l_pm_schedule_id;
513
514 --Commenting the below code for bug 13414054.This logic does not work if intervals per cycle is greater than 9
515 /* -- 5151820 update pm only when pm cycle and seq are less than or equal to that of work order
516 --concatenating both the attributes and comparing below
517 l_pm_seq := to_number(to_char(l_pm_cycle_id) || to_char(l_pm_seq_id));
518 l_wo_seq := to_number(to_char(l_cycle_id) || to_char(l_seq_id));
519 if l_pm_seq < l_wo_seq then*/
520
521 --Added for bug 13414054
522 if (l_pm_cycle_id < l_cycle_id ) or (l_pm_cycle_id = l_cycle_id and l_pm_seq_id <= l_seq_id)then
523 UPDATE EAM_PM_SCHEDULINGS
524 SET current_cycle = l_cycle_id,
525 current_seq = l_seq_id ,
526 current_wo_seq = l_seq_id,
527 last_update_date=sysdate,
528 last_updated_by=g_last_updated_by,
529 last_update_login=g_last_update_login
530 WHERE pm_schedule_id = l_pm_schedule_id ;
531
532 end if;
533
534 --if pm generate workorder then update last cyclic actviity of the PM
535 EAM_PMDEF_PUB.Update_Pm_Last_Cyclic_Act
536 ( X_Return_Status => x_return_status,
537 p_api_version => 1.0 ,
538 p_commit => FND_API.G_FALSE ,
539 X_msg_count => x_msg_count ,
540 X_msg_data => x_msg_data ,
541 p_pm_schedule_id =>l_pm_schedule_id
542 );
543 end if;
544
545
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 NULL;
549 WHEN OTHERS THEN
550 x_return_status := FND_API.G_RET_STS_ERROR;
551 END;
552
553
554
555 END IF;
556
557 -- Standard call to get message count and if count is 1, get message info.
558 FND_MSG_PUB.Count_And_Get
559 ( p_count => x_msg_count ,
560 p_data => x_msg_data
561 );
562 EXCEPTION
563 WHEN FND_API.G_EXC_ERROR THEN
564 ROLLBACK TO EAM_METERS_UTIL;
565 x_return_status := FND_API.G_RET_STS_ERROR ;
566 FND_MSG_PUB.Count_And_Get
567 ( p_count => x_msg_count ,
568 p_data => x_msg_data
569 );
570 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
571 ROLLBACK TO EAM_METERS_UTIL;
572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
573 FND_MSG_PUB.Count_And_Get
574 ( p_count => x_msg_count ,
575 p_data => x_msg_data
576 );
577 WHEN OTHERS THEN
578 ROLLBACK TO EAM_METERS_UTIL;
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
580 IF FND_MSG_PUB.Check_Msg_Level
581 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
582 THEN
583 FND_MSG_PUB.Add_Exc_Msg
584 ( G_PKG_NAME ,
585 l_api_name
586 );
587 END IF;
588 FND_MSG_PUB.Count_And_Get
589 ( p_count => x_msg_count ,
590 p_data => x_msg_data
591 );
592 end;
593
594
595 /**
596 * This procedure is a wrapper over update_last_service_dates
597 * This is getting called from
598 * EAMPLNWB.fmb -> MASS_COMPLETE block -> Work_Order_Completion
599 * procedure. Do not call this from other locations
600 */
601 procedure updt_last_srvc_dates_wo_wpr (p_wip_entity_id in number,
602 p_start_date in date,
603 p_end_date in date,
604 x_return_status OUT NOCOPY VARCHAR2,
605 x_msg_count OUT NOCOPY NUMBER,
606 x_msg_data OUT NOCOPY VARCHAR2) IS
607 x_assoc_id number;
608 l_api_name CONSTANT VARCHAR2(30) := 'Meter_Utils';
609 l_api_version CONSTANT NUMBER := 1.0;
610 l_pm_schedule_id NUMBER;
611 l_cycle_id NUMBER;
612 l_seq_id NUMBER;
613 begin
614 SAVEPOINT EAM_METERS_UTIL_NEW;
615 x_assoc_id := get_activity_assoc_id(p_wip_entity_id);
616
617 if x_assoc_id is not null then
618 update_last_service_dates(p_wip_entity_id, x_assoc_id, p_start_date, p_end_date);
619 x_return_status := FND_API.G_RET_STS_SUCCESS;
620
621 --check if workorder is pm suggested workorder or not
622 BEGIN
623 SELECT wdj.pm_schedule_id,ewod.cycle_id,ewod.seq_id
624 INTO l_pm_schedule_id,l_cycle_id,l_seq_id
625 FROM WIP_DISCRETE_JOBS wdj,EAM_WORK_ORDER_DETAILS ewod
626 WHERE wdj.wip_entity_id = p_wip_entity_id and
627 ewod.wip_entity_id = wdj.wip_entity_id ;
628
629 if(l_pm_schedule_id is not null) then
630
631 UPDATE EAM_PM_SCHEDULINGS
632 SET current_cycle = l_cycle_id,
633 current_seq = l_seq_id ,
634 current_wo_seq = l_seq_id,
635 last_update_date=sysdate,
636 last_updated_by=g_last_updated_by,
637 last_update_login=g_last_update_login
638 WHERE pm_schedule_id = l_pm_schedule_id ;
639
640 --if pm generate workorder then update last cyclic actviity of the PM
641 EAM_PMDEF_PUB.Update_Pm_Last_Cyclic_Act
642 ( X_Return_Status => x_return_status,
643 p_api_version => 1.0 ,
644 p_commit => FND_API.G_FALSE ,
645 X_msg_count => x_msg_count ,
646 X_msg_data => x_msg_data ,
647 p_pm_schedule_id =>l_pm_schedule_id
648 );
649 end if;
650
651
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 NULL;
655 WHEN OTHERS THEN
656 x_return_status := FND_API.G_RET_STS_ERROR;
657 END;
658
659 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
660 COMMIT;
661 ELSE
662 ROLLBACK TO EAM_METERS_UTIL_NEW;
663 END IF;
664 END IF;
665
666 -- Standard call to get message count and if count is 1, get message info.
667 FND_MSG_PUB.Count_And_Get
668 ( p_count => x_msg_count ,
669 p_data => x_msg_data
670 );
671 EXCEPTION
672 WHEN FND_API.G_EXC_ERROR THEN
673 ROLLBACK TO EAM_METERS_UTIL_NEW;
674 x_return_status := FND_API.G_RET_STS_ERROR ;
675 FND_MSG_PUB.Count_And_Get
676 ( p_count => x_msg_count ,
677 p_data => x_msg_data
678 );
679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
680 ROLLBACK TO EAM_METERS_UTIL_NEW;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
682 FND_MSG_PUB.Count_And_Get
683 ( p_count => x_msg_count ,
684 p_data => x_msg_data
685 );
686 WHEN OTHERS THEN
687 ROLLBACK TO EAM_METERS_UTIL_NEW;
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
689 IF FND_MSG_PUB.Check_Msg_Level
690 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
691 THEN
692 FND_MSG_PUB.Add_Exc_Msg
693 ( G_PKG_NAME ,
694 l_api_name
695 );
696 END IF;
697 FND_MSG_PUB.Count_And_Get
698 ( p_count => x_msg_count ,
699 p_data => x_msg_data
700 );
701 end;
702
703 /**
704 * This procedure updates the last service start/end date for the
705 * asset activity association. It does not (after bug 12919405) recursively updates dates
706 * of the child activity association in the suppression hierarchy.
707 */
708
709 procedure update_last_service_dates( p_wip_entity_id in number,
710 p_activity_assoc_id in number,
711 p_start_date in date,
712 p_end_date in date) IS
713
714 cursor C is
715 select sup.child_association_id
716 from eam_suppression_relations sup
717 where sup.parent_association_id = p_activity_assoc_id;
718
719 cursor pm_schedule(activity_assoc_id NUMBER,l_default VARCHAR2) is
720 select rescheduling_point
721 from eam_pm_schedulings
722 where pm_schedule_id in (SELECT pm_schedule_id
723 FROM eam_pm_activities where activity_association_id=activity_assoc_id)
724 and default_implement=l_default;
725
726
727
728 x_child_aa number;
729 l_schedule_option number:=null;
730 l_default varchar2(1):='Y';
731 l_sch_start_date date;
732 l_sch_end_date date;
733
734 BEGIN
735
736 open pm_schedule(p_activity_assoc_id,l_default);
737 fetch pm_schedule into l_schedule_option;
738 if pm_schedule%NOTFOUND then
739 l_schedule_option:=2;
740 end if;
741 close pm_schedule;
742
743 select wdj.scheduled_start_date
744 ,wdj.scheduled_completion_date
745 into
746 l_sch_start_date
747 ,l_sch_end_date
748 from wip_discrete_jobs wdj
749 where wdj.wip_entity_id=p_wip_entity_id;
750
751 -- backup last date to previous date
752 update mtl_eam_asset_activities
753 set prev_service_start_date=last_service_start_date,
754 prev_service_end_date=last_service_end_date,
755 prev_scheduled_start_date=last_scheduled_start_date,
756 prev_scheduled_end_date=last_scheduled_end_date,
757 PREV_PM_SUGGESTED_START_DATE = LAST_PM_SUGGESTED_START_DATE,
758 PREV_PM_SUGGESTED_END_DATE = LAST_PM_SUGGESTED_END_DATE
759
760 /* Shifted p_start_date,p_end_date for bug #4096193 */
761 where activity_association_id = p_activity_assoc_id
762 and (( decode(l_schedule_option,3,last_scheduled_start_date,
763 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)
764 or ( decode(l_schedule_option,3,last_scheduled_start_date,4,last_scheduled_end_date,
765 1,last_service_start_date,2,last_service_end_date,5,last_service_start_date,6,last_service_start_date) <
766 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)));
767
768 -- copy wdj.scheduled_start/completion_date to meaa.last_scheduled_start/end_date
769 update mtl_eam_asset_activities meaa
770 set (meaa.last_scheduled_start_date,
771 meaa.last_scheduled_end_date,
772 meaa.last_service_start_date, --added for bug #4096193
773 meaa.last_service_end_date,--added for bug #4096193
774 meaa.wip_entity_id,
775 meaa.LAST_PM_SUGGESTED_START_DATE,
776 meaa.LAST_PM_SUGGESTED_END_DATE)
777 = (select wdj.scheduled_start_date,
778 wdj.scheduled_completion_date,
779 p_start_date, --added for bug #4096193
780 p_end_date, --added for bug #4096193
781 wdj.wip_entity_id,
782 ewod.pm_suggested_start_date,
783 ewod.pm_suggested_end_date
784 from wip_discrete_jobs wdj, eam_work_order_details ewod
785 where wdj.wip_entity_id=p_wip_entity_id
786 and wdj.wip_entity_id = ewod.wip_entity_id)
787 where meaa.activity_association_id = p_activity_assoc_id
788 and (( decode(l_schedule_option,3,last_scheduled_start_date,
789 4,last_scheduled_end_date,1,last_service_start_date,
790 2,last_service_end_date,5,last_service_start_date,6,last_service_start_date) is null)
791 or ( decode(l_schedule_option,3,last_scheduled_start_date,4,last_scheduled_end_date,
792 1,last_service_start_date,2,last_service_end_date,5,last_service_start_date,6,last_service_start_date)<
793 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
794
795
796 /* Changed above condition to handle when last_service_end_date is null
797 */
798 /*
799 if (SQL%FOUND) then
800 COMMIT;
801 end if;
802 */
803
804 /* commented for bug12919405 - child(suppressed) activity assocation's last service date should not get updated, checked with PM
805 Open C;
806 LOOP
807 fetch C into x_child_aa;
808 EXIT WHEN ( C%NOTFOUND );
809 update_last_service_dates(p_wip_entity_id, x_child_aa, p_start_date, p_end_date);
810 END LOOP;
811 close C;*/
812
813
814 exception
815 when others then
816 --DBMS_OUTPUT.put_line('association_id: ' || p_activity_assoc_id);
817 return;
818 END;
819
820
821 /**
822 * This procedure should be called when resetting a meter. It updates the corresponding
823 * PM schedule rule data if applicable.
824 */
825 procedure reset_meter(p_meter_id in number,
826 p_current_reading in number,
827 p_last_reading in number,
828 p_change_val in number) is
829 cursor C is
830 select 'X'
831 from csi_counters_b
832 where counter_id = p_meter_id
833 and SYSDATE BETWEEN nvl(start_date_active, SYSDATE-1) AND nvl(end_date_active, SYSDATE+1);
834
835 x_temp number;
836 x_dummy varchar2(1);
837 begin
838 -- make sure meter passed in is a valid one
839 open C;
840 fetch C into x_dummy;
841 if ( C%NOTFOUND ) then
842 close C;
843 return;
844 end if;
845 close C;
846
847 x_temp := p_last_reading + p_change_val + p_current_reading;
848 update eam_pm_scheduling_rules
849 set last_service_reading = x_temp - last_service_reading - runtime_interval
850 where meter_id = p_meter_id
851 and rule_type = 2;
852 end reset_meter;
853
854 /**
855 * This procedure calculates the average of the meter readings for the meter
856 */
857 procedure get_average(p_meter_id in number,
858 p_from_date in date,
859 p_to_date in date,
860 x_average OUT NOCOPY number)
861
862 is
863 invalid_meter exception;
864 l_count number;
865 l_user_defined_rate number ;
866 l_use_past_reading number ;
867
868 BEGIN
869 x_average := 0;
870 l_count := 0 ;
871 l_user_defined_rate := 0 ;
872 l_use_past_reading := 0 ;
873 if NOT EAM_COMMON_UTILITIES_PVT.validate_meter_id(p_meter_id) then
874 raise invalid_meter;
875 end if;
876
877 select
878 ABS(
879 trunc ((SUM(life_to_date_reading * (current_reading_date-sysdate))
880 - SUM (life_to_date_reading) * SUM (current_reading_date-sysdate) / count(rowid))/
881 (SUM((current_reading_date-sysdate) * (current_reading_date-sysdate))
882 - SUM (current_reading_date-sysdate) * SUM (current_reading_date-sysdate) /
883 count(rowid)) , 6)
884 )
885 INTO x_average
886 from
887 eam_meter_readings_v
888 where
889 meter_id = p_meter_id
890 and
891 (disable_flag is null or disable_flag = 'N')
892 and
893 reset_flag <> 'Y'
894 and ( p_from_date is null or (current_reading_date > p_from_date))
895 and ( p_to_date is null or (current_reading_date < p_to_date));
896 if ( x_average IS NULL) then
897 x_average := 0;
898 end if;
899
900 EXCEPTION
901
902 when invalid_meter then
903 x_average := 0;
904
905 when no_data_found then
906 x_average := 0;
907
908 when others then
909 x_average := 0;
910 END get_average;
911
912
913 /**
914 * This is a private function. It resursively iterate through the suppression tree
915 * to see whether the meter is used in the sub tree of the given node.
916 */
917 function mr_mandatory_for_pm(p_activity_assoc_id in number,
918 p_meter_id in number) return boolean is
919 cursor C is
920 select epac.activity_association_id
921 from eam_pm_activities epac,
922 eam_pm_schedulings eps,
923 eam_suppression_relations sup
924 where sup.parent_association_id = p_activity_assoc_id
925 and sup.child_association_id = epac.activity_association_id
926 and eps.pm_schedule_id = epac.pm_schedule_id
927 and nvl(eps.from_effective_date, sysdate-1) < sysdate
928 and nvl(eps.to_effective_date, sysdate+1) > sysdate;
929
930 cursor testmr is
931 select 'X'
932 from eam_pm_scheduling_rules pr,
933 eam_pm_activities epa,
934 csi_counters_b ccb
935 where pr.meter_id = ccb.counter_id
936 and epa.activity_association_id = p_activity_assoc_id
937 and pr.pm_schedule_id = epa.pm_schedule_id
938 and pr.rule_type = 2
939 and pr.meter_id = p_meter_id
940 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1);
941
942 x_child_aa number;
943 x_dummy varchar2(1);
944 begin
945
946 open testmr;
947 fetch testmr into x_dummy;
948 if ( NOT testmr%NOTFOUND ) then
949 close testmr;
950 return true;
951 end if;
952 close testmr;
953 open C;
954 LOOP
955 fetch C into x_child_aa;
956 EXIT WHEN ( C%NOTFOUND );
957 if ( mr_mandatory_for_pm(x_child_aa, p_meter_id) ) then
958 close C;
959 return true;
960 end if;
961 END LOOP;
962
963 close C;
964 return false;
965 end mr_mandatory_for_pm;
966
967
968 /**
969 * This is a private function to resursively iterate through the suppression tree
970 * to see whether any one of them needs meter reading.
971 */
972 function pm_need_meter_reading(p_parent_pm_id in number)
973 return boolean is
974 cursor C is
975 select epac.pm_schedule_id
976 from eam_pm_activities epac,
977 eam_pm_schedulings eps,
978 eam_pm_activities epap,
979 eam_suppression_relations sup
980 where epap.activity_association_id = sup.parent_association_id
981 and epap.pm_schedule_id = p_parent_pm_id
982 and sup.child_association_id = epac.activity_association_id
983 and eps.pm_schedule_id = epac.pm_schedule_id
984 and nvl(eps.from_effective_date, sysdate-1) < sysdate
985 and nvl(eps.to_effective_date, sysdate+1) > sysdate;
986
987
988 x_child_pm number;
989 x_num number;
990 begin
991 select count(*) into x_num
992 from eam_pm_scheduling_rules pr,
993 csi_counters_b ccb
994 where pr.meter_id = ccb.counter_id
995 and pr.pm_schedule_id = p_parent_pm_id
996 and pr.rule_type = 2
997 and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1);
998
999 if ( x_num > 0 ) then
1000 return true;
1001 end if;
1002
1003 open C;
1004 LOOP
1005 fetch C into x_child_pm;
1006 EXIT WHEN ( C%NOTFOUND );
1007 if ( pm_need_meter_reading(x_child_pm) ) then
1008 close C;
1009 return true;
1010 end if;
1011 END LOOP;
1012 close C;
1013
1014 return false;
1015 end pm_need_meter_reading;
1016
1017 /**
1018 * This function checks if a reading date is between a normal meter reading
1019 * and a reset meter reading.
1020 */
1021 /*
1022 * new comment (correcting the original comments for this function)
1023 * this function checks if a reading date is right prior to a reset reading date
1024 */
1025
1026 function cannot_enter_value(p_meter_id in number,
1027 p_reading_date in date)
1028 return boolean is
1029 prev_ceiling_reset_date date;
1030 prev_ceiling_reading_date date;
1031 return_val boolean ;
1032 begin
1033 return_val := false;
1034 -- get min uppper reading that is a reset reading
1035 select min(value_timestamp)
1036 into prev_ceiling_reset_date
1037 from csi_counter_readings
1038 where COUNTER_ID = p_meter_id
1039 and value_timestamp > p_reading_date
1040 and reset_mode = 'SOFT'
1041 and NVL(disabled_flag,'N')<>'Y';
1042
1043 -- get min uppper reading that is not a reset reading
1044 select min(value_timestamp)
1045 into prev_ceiling_reading_date
1046 from csi_counter_readings
1047 where COUNTER_ID = p_meter_id
1048 and value_timestamp > p_reading_date
1049 and (reset_mode <> 'SOFT' or reset_mode is null)
1050 and NVL(disabled_flag,'N')<>'Y';
1051
1052
1053 if prev_ceiling_reset_date is not null then
1054 if prev_ceiling_reading_date is not null then
1055 if (prev_ceiling_reading_date > prev_ceiling_reset_date) then
1056 return_val := true;
1057 end if;
1058 else
1059 -- null;
1060 return_val := true;
1061 end if;
1062 end if;
1063 return return_val ;
1064 end cannot_enter_value;
1065
1066
1067
1068 /**
1069 * This function checks if the reading date is a reset reading date
1070 */
1071 function cannot_update_reset(p_meter_id in number,
1072 p_reading_date in date)
1073 return boolean is
1074 curr_rdg number;
1075 prev_ceiling_reading_date date;
1076 return_val boolean ;
1077 begin
1078 return_val := false;
1079
1080 -- get min uppper reading that is not a reset reading
1081 select min(value_timestamp)
1082 into prev_ceiling_reading_date
1083 from csi_counter_readings
1084 where COUNTER_ID = p_meter_id
1085 and value_timestamp > p_reading_date
1086 and (reset_mode <> 'SOFT' or reset_mode is null)
1087 and NVL(disabled_flag,'N')<>'Y';
1088
1089
1090
1091 if (eam_meters_util.reset_reading_exists(p_meter_id,p_reading_date) = true
1092 and prev_ceiling_reading_date is not null) then
1093 return_val := true;
1094 end if;
1095 return return_val;
1096
1097 exception
1098 when no_data_found then
1099 return false;
1100 when others then
1101 return false;
1102 end cannot_update_reset;
1103
1104
1105
1106 /**
1107 * This function checks if a particular reading is a reset reading
1108 */
1109
1110 function reset_reading_exists (p_meter_id in number,
1111 p_reading_date in date)
1112 return boolean is
1113 return_val boolean;
1114 curr_rdg number;
1115 begin
1116 return_val := false;
1117 select counter_reading
1118 into curr_rdg
1119 from csi_counter_readings
1120 where COUNTER_ID = p_meter_id
1121 and value_timestamp = p_reading_date
1122 and (reset_mode <> 'SOFT' or reset_mode is null)
1123 and NVL(disabled_flag,'N')<>'Y';
1124
1125
1126 if (curr_rdg is not null) then
1127 return_val := true;
1128 end if;
1129 return return_val;
1130 exception
1131
1132 when no_data_found then
1133 return false;
1134 when others then
1135 return false;
1136
1137 end reset_reading_exists;
1138
1139
1140 /**
1141 * This function checks if a particular reading is a normal reading right prior to a reset reading
1142 */
1143
1144 /*
1145 function normal_reading_before_reset ( p_meter_reading_id in number)
1146 return boolean is
1147 l_reset_flag varchar(1);
1148 l_next_reset_flag varchar(1);
1149 l_reading_date date;
1150 l_meter_id number;
1151 l_next_reading_date date;
1152 begin
1153 select current_reading_date, meter_id
1154 into x_reading_date, x_meter_id
1155 from eam_meter_readings where meter_reading_id=p_meter_reading_id;
1156 -- dbms_output.put_line(x_reading_date);
1157 -- dbms_output.put_line(x_meter_id);
1158 select reset_flag into x_reset_flag from eam_meter_readings
1159 where meter_reading_id = p_meter_reading_id;
1160 -- dbms_output.put_line(x_reset_flag);
1161 if (x_reset_flag is not null and x_reset_flag = 'Y') then
1162 return FALSE;
1163 end if;
1164
1165 -- dbms_output.put_line('beforequery');
1166
1167 select min(current_reading_date) into next_reading_date
1168 from eam_meter_readings
1169 where meter_id = x_meter_id
1170 AND current_reading_date > x_reading_date
1171 and (disable_flag is null or disable_flag = 'N');
1172
1173 -- dbms_output.put_line('beforequery2');
1174 if (next_reading_date is not null) then
1175 select reset_flag into next_reset_flag
1176 from eam_meter_readings
1177 where meter_id = x_meter_id
1178 AND current_reading_date =next_reading_date
1179 and (disable_flag is null or disable_flag = 'N');
1180 end if;
1181
1182 if (next_reset_flag is not null and next_reset_flag='Y') then
1183 return TRUE;
1184 else
1185 return FALSE;
1186 end if;
1187 end normal_reading_before_reset;
1188 */
1189
1190
1191 /* following function checks if there exists any readings after the
1192 specific reading date */
1193 function next_reading_exists(p_meter_id in number, p_reading_date in date)
1194 return boolean
1195 is
1196 l_next_reading_date date;
1197 begin
1198 select min(value_timestamp)
1199 into l_next_reading_date
1200 from csi_counter_readings
1201 where COUNTER_ID = p_meter_id
1202 and value_timestamp > p_reading_date
1203 and (disabled_flag <> 'Y');
1204 if (l_next_reading_date is not null) then
1205 return true;
1206 else
1207 return false;
1208 end if;
1209 end next_reading_exists;
1210
1211
1212 /* following function determines whether a non-disabled reading
1213 exists on p_reading_date for meter p_meter_id
1214 */
1215 function reading_exists(p_meter_id IN NUMBER,
1216 p_reading_date IN date)
1217 return boolean
1218 is
1219 return_val boolean;
1220 l_meter_reading_id number;
1221 begin
1222 return_val := false;
1223 select COUNTER_VALUE_ID into l_meter_reading_id
1224 from csi_counter_readings
1225 where
1226 COUNTER_ID=p_meter_id and
1227 value_timestamp = p_reading_date
1228 and NVL(disabled_flag,'N')<>'Y';
1229
1230 if (l_meter_reading_id is not null) then
1231 return_val:=true;
1232 end if;
1233 return return_val;
1234 exception
1235 when no_data_found then
1236 return false;
1237 when others then
1238 return false;
1239 end reading_exists;
1240
1241
1242 /* This function determines whether a new meter reading would
1243 violate the ascending or descending order of the meter.
1244 It compares the current reading with the previous meter reading for this meter,
1245 and if the next meter readign is not a reset reading, it compares the
1246 new reading with the next meter reading for this meter.
1247 If there is violation, "true" is returned; otherwise, "false" is
1248 returned. */
1249
1250
1251 function violate_order(p_meter_id in number,
1252 p_reading_date in date,
1253 p_current_reading in number)
1254 return boolean
1255 is
1256 l_prev_reading_date date;
1257 l_prev_reading number;
1258 l_next_reading_date date;
1259 l_next_reading number;
1260 l_next_reset varchar2(1);
1261 l_meter_type number;
1262 return_val boolean ;
1263
1264 begin
1265 return_val := false;
1266
1267 select max(value_timestamp) into l_prev_reading_date
1268 from csi_counter_readings
1269 where COUNTER_ID = p_meter_id
1270 and value_timestamp < p_reading_date
1271 and NVL(disabled_flag,'N')<>'Y';
1272
1273 if (l_prev_reading_date is not null) then
1274 select counter_reading into l_prev_reading
1275 from csi_counter_readings
1276 where COUNTER_ID = p_meter_id
1277 and value_timestamp = l_prev_reading_date
1278 and NVL(disabled_flag,'N')<>'Y';
1279 end if;
1280
1281 select min(value_timestamp) into l_next_reading_date
1282 from csi_counter_readings
1283 where COUNTER_ID = p_meter_id
1284 and value_timestamp > p_reading_date
1285 and NVL(disabled_flag,'N')<>'Y';
1286
1287 if (l_next_reading_date is not null) then
1288 select counter_reading, decode(reset_mode,'SOFT','Y','N') reset_flag
1289 into l_next_reading, l_next_reset
1290 from csi_counter_readings
1291 where value_timestamp = l_next_reading_date
1292 and COUNTER_ID = p_meter_id
1293 and NVL(disabled_flag,'N')<>'Y';
1294 end if;
1295
1296
1297 if (l_prev_reading is not null or
1298 (l_next_reading is not null and
1299 (l_next_reset is null or l_next_reset='N'))) then
1300 select direction into l_meter_type
1301 from csi_counters_b
1302 where counter_id = p_meter_id;
1303 else
1304 return false;
1305 end if;
1306
1307 if (l_prev_reading is not null) then
1308 if ((l_prev_reading > p_current_reading and l_meter_type = 1)
1309 or (l_prev_reading < p_current_reading and l_meter_type=2))
1310 then
1311 return true;
1312 end if;
1313 end if;
1314
1315 if (l_next_reading is not null and (l_next_reset is null or l_next_reset='N')) then
1316 if ((p_current_reading > l_next_reading and l_meter_type=1)
1317 or (p_current_reading < l_next_reading and l_meter_type=2))
1318 then
1319 return true;
1320 end if;
1321 end if;
1322 return false;
1323 /*
1324 exception
1325 when no_data_found then
1326 return false;
1327 */
1328 end violate_order;
1329
1330 /**
1331 * This procedure updates LTD readings for disabled change meter readings
1332 */
1333 procedure update_change_meter_ltd(p_meter_id in number,
1334 p_meter_reading_id in number) is
1335 l_reading_date DATE;
1336 l_reading_value number;
1337 l_meter_type number;
1338 begin
1339 if p_meter_id is null OR p_meter_reading_id is null then
1340 return;
1341 end if;
1342
1343 select reading_type into l_meter_type
1344 from csi_counters_b
1345 where counter_id = p_meter_id;
1346
1347 if(l_meter_type <> 2) then
1348 return;
1349 end if;
1350
1351 select VALUE_TIMESTAMP, COUNTER_READING
1352 into l_reading_date, l_reading_value
1353 from csi_counter_readings
1354 where COUNTER_VALUE_ID = p_meter_reading_id;
1355
1356 -- Now update the ltd readings of all readings taken after the disabled reading
1357 update csi_counter_readings
1358 set life_to_date_reading = life_to_date_reading - l_reading_value
1359 where value_timestamp > l_reading_date and counter_id = p_meter_id;
1360
1361 exception
1362 when others then
1363 return;
1364 end update_change_meter_ltd;
1365
1366 /* This function calculates the life_to_date reading for a new reading. */
1367
1368 function calculate_ltd (p_meter_id in number,
1369 p_reading_date in date,
1370 p_new_reading in number,
1371 p_meter_type in number)
1372 return number
1373 is
1374 ltd_value number;
1375 l_prev_reading_date date;
1376 l_prev_reading number;
1377 l_prev_ltd_reading number;
1378
1379 begin
1380 select max(value_timestamp) into l_prev_reading_date
1381 from csi_counter_readings
1382 where COUNTER_ID = p_meter_id
1383 and value_timestamp < p_reading_date
1384 and NVL(disabled_flag,'N')<>'Y';
1385
1386 if (l_prev_reading_date is not null) then
1387 select counter_reading, life_to_date_reading
1388 into l_prev_reading, l_prev_ltd_reading
1389 from csi_counter_readings
1390 where value_timestamp = l_prev_reading_date
1391 and COUNTER_ID = p_meter_id
1392 and NVL(disabled_flag,'N')<>'Y';
1393 end if;
1394 if(p_meter_type = 1) then
1395 if (l_prev_reading is not null) then
1396 ltd_value:=p_new_reading-l_prev_reading+l_prev_ltd_reading;
1397 else
1398 ltd_value:=p_new_reading;
1399 end if;
1400 elsif(p_meter_type = 2) then
1401 ltd_value := p_new_reading + l_prev_ltd_reading;
1402 end if;
1403 return ltd_value;
1404
1405 end calculate_ltd;
1406
1407
1408 /* This function verifies that the meter reading meets the follow criteria:
1409 1. meter reading is not a normal reading before a reset reading
1410 2. meter reading is not a reset reading with any readings after it.
1411 */
1412 function can_be_disabled(p_meter_id number,
1413 p_meter_reading_id number,
1414 x_reason_cannot_disable out nocopy number)
1415 return boolean
1416 is
1417 l_current_reading_date date;
1418 l_next_reading_date date;
1419 l_prev_reading_date date;
1420 l_prev_reset varchar2(1);
1421 l_next_reset varchar2(1);
1422 l_reset varchar2(1);
1423
1424 begin
1425 begin
1426 -- get current reading date and reset flag
1427 select decode(reset_mode,'SOFT','Y','N') reset_flag, VALUE_TIMESTAMP
1428 into l_reset, l_current_reading_date
1429 from CSI_COUNTER_READINGS
1430 where COUNTER_VALUE_ID = p_meter_reading_id
1431 and NVL(disabled_flag,'N')<>'Y';
1432 exception
1433 when no_data_found then
1434 x_reason_cannot_disable:=1;
1435 return false;
1436 end;
1437
1438 -- If current reading does not exist, return false
1439 if l_current_reading_date is null then
1440 x_reason_cannot_disable:=1;
1441 return false;
1442 end if;
1443
1444 -- get next reading date
1445 select min(value_timestamp) into l_next_reading_date
1446 from csi_counter_readings
1447 where COUNTER_ID = p_meter_id
1448 and value_timestamp > l_current_reading_date
1449 and NVL(disabled_flag,'N')<>'Y';
1450
1451 -- if next reading date is null (i.e. no more readings after current reading),
1452 -- return true
1453 if (l_next_reading_date is null) then
1454 return true;
1455 end if;
1456
1457 -- The rest of this function will only the executed if the current
1458 -- reading exists and next reading exists
1459
1460 -- if current reading is reset
1461 if (l_reset is not null and l_reset='Y')then
1462 x_reason_cannot_disable:=2;
1463 return false;
1464 end if;
1465
1466 -- if current reading is not reset, get next reset flag
1467 select decode(reset_mode,'SOFT','Y','N') reset_flag into l_next_reset
1468 from csi_counter_readings
1469 where COUNTER_ID = p_meter_id
1470 and value_timestamp = l_next_reading_date
1471 and NVL(disabled_flag,'N')<>'Y';
1472
1473 -- current reading is normal, and next reading is reset
1474 if (l_next_reset is not null and l_next_reset='Y') then
1475 x_reason_cannot_disable:=3;
1476 return false;
1477 end if;
1478
1479 return true;
1480
1481 end can_be_disabled;
1482
1483
1484 PROCEDURE VALIDATE_USED_IN_SCHEDULING(p_meter_id IN NUMBER,
1485 x_return_status OUT NOCOPY VARCHAR2,
1486 x_msg_count OUT NOCOPY NUMBER,
1487 x_msg_data OUT NOCOPY VARCHAR2)
1488 IS
1489 l_exists VARCHAR2(1);
1490 l_api_name CONSTANT VARCHAR2(30) := 'Meter_Utils';
1491 BEGIN
1492
1493 BEGIN
1494
1495 SELECT 'Y'
1496 INTO l_exists
1497 FROM EAM_PM_SCHEDULING_RULES
1498 WHERE meter_id = p_meter_id
1499 AND rownum<=1;
1500
1501 EXCEPTION
1502 WHEN NO_DATA_FOUND THEN
1503 l_exists := 'N';
1504 END;
1505
1506 IF(l_exists='N') THEN
1507 x_return_status := FND_API.G_RET_STS_SUCCESS;
1508 ELSE
1509 x_return_status := FND_API.G_RET_STS_ERROR ;
1510 fnd_message.set_name('EAM', 'EAM_METER_USED_IN_PM');
1511 fnd_msg_pub.add;
1512 END IF;
1513
1514 -- Standard call to get message count and if count is 1, get message info.
1515 FND_MSG_PUB.Count_And_Get
1516 ( p_count => x_msg_count ,
1517 p_data => x_msg_data
1518 );
1519 EXCEPTION
1520 WHEN FND_API.G_EXC_ERROR THEN
1521 x_return_status := FND_API.G_RET_STS_ERROR ;
1522 FND_MSG_PUB.Count_And_Get
1523 ( p_count => x_msg_count ,
1524 p_data => x_msg_data
1525 );
1526 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1528 FND_MSG_PUB.Count_And_Get
1529 ( p_count => x_msg_count ,
1530 p_data => x_msg_data
1531 );
1532 WHEN OTHERS THEN
1533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1534 IF FND_MSG_PUB.Check_Msg_Level
1535 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1536 THEN
1537 FND_MSG_PUB.Add_Exc_Msg
1538 ( G_PKG_NAME ,
1539 l_api_name
1540 );
1541 END IF;
1542 FND_MSG_PUB.Count_And_Get
1543 ( p_count => x_msg_count ,
1544 p_data => x_msg_data
1545 );
1546 END VALIDATE_USED_IN_SCHEDULING;
1547
1548
1549 END eam_meters_util;
1550
1551
[Home] [Help]