1 PACKAGE BODY EAM_METR_VALIDATOR AS
2 /* $Header: EAMETRVB.pls 115.10 2004/02/21 00:34:53 lllin ship $ */
3
4 procedure validate(p_current_rowid in rowid,
5 p_interface_id in number) is
6 l_disable_flag varchar2(1);
7 begin
8 select disable_flag into l_disable_flag
9 from eam_meter_readings_interface
10 where rowid=p_current_rowid;
11
12 if (l_disable_flag is null or l_disable_flag='N') then
13 populate_reading(p_current_rowid, p_interface_id);
14 reading_date(p_current_rowid, p_interface_id);
15 reading_values(p_current_rowid, p_interface_id);
16 end if;
17
18 last_updated_by_name(p_current_rowid, p_interface_id);
19 created_by_name(p_current_rowid, p_interface_id);
20 populate_who(p_current_rowid, p_interface_id);
21 last_updated_by(p_current_rowid, p_interface_id);
22 created_by(p_current_rowid, p_interface_id);
23
24 organization_code(p_current_rowid, p_interface_id);
25 organization_id(p_current_rowid, p_interface_id);
26 work_order_name_id(p_current_rowid, p_interface_id);
27 reset_flag(p_current_rowid, p_interface_id);
28
29 end validate;
30
31 procedure populate_reading(p_current_rowid in rowid,
32 p_interface_id in number) is
33 l_current_reading number;
34 l_reading_change number;
35 l_meter_id number;
36 l_last_reading number;
37 l_retcode varchar2(1);
38 l_reading_date date;
39 l_last_reading_date date;
40 begin
41 meter_name(p_current_rowid => p_current_rowid,
42 p_interface_id => p_interface_id,
43 p_retcode => l_retcode);
44 if (l_retcode='2') then
45 FND_Message.set_name('EAM', 'EAM_METINT_MISSING_MT_INFO');
46 eam_int_utils.record_error(p_interface_id, FND_Message.get, FALSE);
47 end if;
48
49 select reading_value, reading_change, meter_id, reading_date
50 into l_current_reading, l_reading_change, l_meter_id, l_reading_date
51 from eam_meter_readings_interface
52 where rowid=p_current_rowid;
53
54 if (l_current_reading is null) then
55 if (l_reading_change is null) then
56 FND_Message.set_name('EAM', 'EAM_METINT_MISSING_READING');
57 eam_int_utils.record_error(p_interface_id, FND_Message.get, FALSE);
58 else
59 select max(current_reading_date) into l_last_reading_date
60 from eam_meter_readings
61 where meter_id=l_meter_id
62 and current_reading_date < l_reading_date
63 and (disable_flag is null or disable_flag = 'N');
64
65 if (l_last_reading_date is not null) then
66 select current_reading into l_last_reading
67 from eam_meter_readings
68 where meter_id = l_meter_id
69 and current_reading_date=l_last_reading_date
70 and (disable_flag is null or disable_flag = 'N');
71 else
72 l_last_reading:=0;
73 end if;
74
75 l_current_reading:=l_last_reading+l_reading_change;
76
77 update eam_meter_readings_interface
78 set reading_value=l_current_reading
79 where rowid=p_current_rowid;
80 end if;
81 end if;
82 exception
83 when no_data_found then
84 FND_Message.set_name('EAM', 'EAM_METINT_MISSING_READING');
85 eam_int_utils.record_error(p_interface_id, FND_Message.get, FALSE);
86 end populate_reading;
87
88 procedure populate_who(p_current_rowid in rowid,
89 p_interface_id in number) is
90 l_user_id number;
91 l_created_by number;
92 l_last_updated_by number;
93 l_creation_date date;
94 l_last_update_date date;
95 begin
96 l_user_id:=fnd_global.user_id;
97 select created_by, last_updated_by, creation_date, last_update_date
98 into l_created_by, l_last_updated_by, l_creation_date, l_last_update_date
99 from eam_meter_readings_interface
100 where rowid=p_current_rowid;
101
102 IF (l_created_by IS NOT NULL
103 AND l_last_updated_by IS NOT NULL
104 AND l_creation_date IS NOT NULL
105 AND l_last_update_date IS NOT NULL)
106 THEN
107 RETURN;
108 END IF;
109
110 if (l_created_by is null) then
111 l_created_by:=fnd_global.user_id;
112 end if;
113
114 if (l_last_updated_by is null) then
115 l_last_updated_by:=fnd_global.user_id;
116 end if;
117
118 if (l_creation_date is null) then
119 l_creation_date:=sysdate;
120 end if;
121
122 if (l_last_update_date is null) then
123 l_last_update_date:=sysdate;
124 end if;
125
126 update eam_meter_readings_interface
127 set created_by=l_created_by,
128 last_updated_by=l_last_updated_by,
129 creation_date=l_creation_date,
130 last_update_date=l_last_update_date
131 WHERE rowid = p_current_rowid;
132 end populate_who;
133
134
135 procedure life_to_date_reading(p_current_rowid in rowid,
136 p_interface_id in number);
137
138
139
140 procedure last_updated_by_name(p_current_rowid in rowid,
141 p_interface_id in number) is
142 begin
143 eam_int_utils.derive_id_from_code(
144 p_current_rowid,
145 p_interface_id,
146 'eam_meter_readings_interface mri',
147 'LAST_UPDATED_BY',
148 'LAST_UPDATED_BY_NAME',
149 '(SELECT USER_ID
150 FROM FND_USER
151 WHERE USER_NAME = mri.LAST_UPDATED_BY_NAME)'
152 );
153 end last_updated_by_name;
154
155
156 procedure last_updated_by(p_current_rowid in rowid,
157 p_interface_id in number) is
158 begin
159 eam_mri_utils.error_if(
160 p_current_rowid,
161 p_interface_id,
162 '((LAST_UPDATED_BY IS NULL AND LAST_UPDATED_BY_NAME IS NULL)
163 OR NOT EXISTS
164 (SELECT 1
165 FROM FND_USER FU
166 WHERE USER_ID = mri.LAST_UPDATED_BY
167 AND SYSDATE BETWEEN FU.START_DATE AND
168 NVL(FU.END_DATE,SYSDATE+1)))',
169 'WIP',
170 'WIP_ML_LAST_UPDATED_BY');
171 end last_updated_by;
172
173
174 procedure created_by_name(p_current_rowid in rowid,
175 p_interface_id in number) is
176 begin
177 eam_int_utils.derive_id_from_code(
178 p_current_rowid,
179 p_interface_id,
180 'eam_meter_readings_interface mri',
181 'CREATED_BY',
182 'CREATED_BY_NAME',
183 '(SELECT USER_ID
184 FROM FND_USER
185 WHERE USER_NAME = mri.LAST_UPDATED_BY_NAME)'
186 );
187 end created_by_name;
188
189
190 procedure created_by(p_current_rowid in rowid,
191 p_interface_id in number) is
192 begin
193 eam_mri_utils.error_if(
194 p_current_rowid,
195 p_interface_id,
196 '((CREATED_BY IS NULL AND CREATED_BY_NAME IS NULL)
197 OR NOT EXISTS
198 (SELECT 1
199 FROM FND_USER FU
200 WHERE USER_ID = mri.CREATED_BY
201 AND SYSDATE BETWEEN FU.START_DATE AND
202 NVL(FU.END_DATE,SYSDATE+1)))',
203 'WIP',
204 'WIP_ML_CREATED_BY');
205 end created_by;
206
207
208 procedure organization_code(p_current_rowid in rowid,
209 p_interface_id in number) is
210 x_org_code varchar2(3);
211 x_eam_enabled varchar2(1) := null;
212 begin
213 select organization_code into x_org_code
214 from eam_meter_readings_interface
215 where rowid = p_current_rowid;
216
217 if ( x_org_code is null ) then
218 return;
219 end if;
220
221 -- check if the org is EAM enabled
222 select eam_enabled_flag
223 into x_eam_enabled
224 from mtl_parameters
225 where organization_code = x_org_code;
226
227 if (nvl(x_eam_enabled, 'N') = 'N') then
228 fnd_message.set_name('EAM', 'EAM_ORG_EAM_ENABLED');
229 eam_int_utils.record_error(p_interface_id,
230 fnd_message.get,
231 FALSE);
232 end if;
233
234 eam_int_utils.derive_id_from_code(
235 p_current_rowid,
236 p_interface_id,
237 'eam_meter_readings_interface mri',
238 'ORGANIZATION_ID',
239 'ORGANIZATION_CODE',
240 '(SELECT ORGANIZATION_ID
241 FROM ORG_ORGANIZATION_DEFINITIONS
242 WHERE ORGANIZATION_CODE = MRI.ORGANIZATION_CODE)',
243 FALSE
244 );
245 exception
246 when others then
247 fnd_message.set_name('EAM', 'EAM_MR_ORG_INFO_MISSING');
248 eam_int_utils.record_error(p_interface_id,
249 fnd_message.get,
250 FALSE);
251 end organization_code;
252
253
254 procedure organization_id(p_current_rowid in rowid,
255 p_interface_id in number) is
256 x_org_id number := null;
257 x_eam_enabled varchar2(1) := null;
258 begin
259 select organization_id into x_org_id
260 from eam_meter_readings_interface
261 where rowid = p_current_rowid;
262 if ( x_org_id is null ) then
263 return;
264 end if;
265
266 -- check if the org is EAM enabled
267 select eam_enabled_flag
268 into x_eam_enabled
269 from mtl_parameters
270 where organization_id = x_org_id;
271
272 if (nvl(x_eam_enabled, 'N') = 'N') then
273 fnd_message.set_name('EAM', 'EAM_ORG_EAM_ENABLED');
274 eam_int_utils.record_error(p_interface_id,
275 fnd_message.get,
276 FALSE);
277 end if;
278
279
280 eam_mri_utils.error_if(
281 p_current_rowid,
282 p_interface_id,
283 '(NOT EXISTS (SELECT 1
284 FROM ORG_ORGANIZATION_DEFINITIONS
285 WHERE ORGANIZATION_ID= MRI.ORGANIZATION_ID)
286 OR TRUNC(SYSDATE) > (SELECT NVL(DISABLE_DATE, SYSDATE + 1)
287 FROM ORG_ORGANIZATION_DEFINITIONS
288 WHERE ORGANIZATION_ID = MRI.ORGANIZATION_ID)
289 )',
290 'WIP',
291 'WIP_ML_ORGANIZATION_ID');
292 exception
293 when others then
294 fnd_message.set_name('EAM', 'EAM_MR_ORG_INFO_MISSING');
295 eam_int_utils.record_error(p_interface_id,
296 fnd_message.get,
297 FALSE);
298 end organization_id;
299
300
301 procedure work_order_name_id(p_current_rowid in rowid,
302 p_interface_id in number) is
303 x_org_id number := null;
304 x_work_order_name varchar2(240) := null;
305 x_wip_entity_id number := null;
306 begin
307 select organization_id,
308 work_order_name,
309 wip_entity_id
310 into x_org_id,
311 x_work_order_name,
312 x_wip_entity_id
313 from eam_meter_readings_interface
314 where rowid = p_current_rowid;
315
316 if ( x_work_order_name is not null AND
317 x_wip_entity_id is null AND
318 x_org_id is null ) then
319 fnd_message.set_name('EAM', 'EAM_MR_ORG_INFO_MISSING');
320 eam_int_utils.record_error(p_interface_id,
321 fnd_message.get,
322 FALSE);
323 end if;
324
325 eam_int_utils.derive_id_from_code(
326 p_current_rowid,
327 p_interface_id,
328 'eam_meter_readings_interface mri',
329 'WIP_ENTITY_ID',
330 'WORK_ORDER_NAME',
331 '(SELECT WIP_ENTITY_ID
332 FROM WIP_ENTITIES
333 WHERE ORGANIZATION_ID = MRI.ORGANIZATION_ID
334 AND WIP_ENTITY_NAME = MRI.WORK_ORDER_NAME)',
335 FALSE
336 );
337
338 -- now make sure that wip entity id is valid if not null
339 x_wip_entity_id := null;
340 select wip_entity_id
341 into x_wip_entity_id
342 from eam_meter_readings_interface
343 where rowid = p_current_rowid;
344 if ( x_wip_entity_id is null ) then
345 return;
346 end if;
347
348 if ( NOT eam_int_utils.request_matches_condition(
349 p_current_rowid,
350 p_interface_id,
351 'eam_meter_readings_interface mri',
352 'exists (select 1
353 from wip_discrete_jobs wdj, eam_asset_meters eam, wip_entities we
354 where wdj.wip_entity_id = mri.wip_entity_id
355 and wdj.organization_id = mri.organization_id
356 and we.organization_id = mri.organization_id
357 and eam.organization_id = mri.organization_id
358 and eam.asset_number = wdj.asset_number
359 and eam.asset_group_id = wdj.asset_group_id
360 and eam.meter_id = mri.meter_id
361 and we.wip_entity_id = wdj.wip_entity_id)') ) then
362 eam_int_utils.record_invalid_column_error(
363 p_interface_id,
364 'WIP_ENTITY_ID');
365 end if;
366
367 end work_order_name_id;
368
369
370 procedure meter_name(p_current_rowid in rowid,
371 p_interface_id in number,
372 p_retcode out NOCOPY varchar2) is
373 begin
374 p_retcode := '0';
375 eam_int_utils.derive_id_from_code(
376 p_current_rowid,
377 p_interface_id,
378 'eam_meter_readings_interface mri',
379 'METER_ID',
380 'METER_NAME',
381 '(SELECT METER_ID
382 FROM EAM_METERS
383 WHERE METER_NAME = MRI.METER_NAME
384 AND MRI.READING_DATE BETWEEN
385 NVL(FROM_EFFECTIVE_DATE, MRI.READING_DATE-1)
386 AND NVL(TO_EFFECTIVE_DATE, MRI.READING_DATE+1))'
387 );
388
389 eam_int_utils.derive_code_from_id(
390 p_current_rowid,
391 p_interface_id,
392 'eam_meter_readings_interface mri',
393 'METER_ID',
394 'METER_NAME',
395 '(SELECT METER_NAME
396 FROM EAM_METERS
397 WHERE METER_ID = MRI.METER_ID
398 AND MRI.READING_DATE BETWEEN
399 NVL(FROM_EFFECTIVE_DATE, MRI.READING_DATE-1)
400 AND NVL(TO_EFFECTIVE_DATE, MRI.READING_DATE+1))'
401 );
402
403 if ( eam_int_utils.has_errors ) then
404 eam_int_utils.load_errors('eam_meter_readings_interface');
405 update eam_meter_readings_interface
406 set process_status = WIP_CONSTANTS.ERROR,
407 process_phase = WIP_CONSTANTS.ML_VALIDATION
408 where rowid = p_current_rowid;
409 p_retcode := '2';
410 end if;
411 end meter_name;
412
413
414 procedure meter_id(p_current_rowid in rowid,
415 p_interface_id in number,
416 p_retcode out NOCOPY varchar2) is
417 x_meter_name varchar2(30) := null;
418 begin
419 p_retcode := '0';
420
421 select meter_name into x_meter_name
422 from eam_meter_readings_interface
423 where rowid = p_current_rowid;
424 -- if meter name is already specified, then we don't need to
425 -- validate it again.
426 if ( x_meter_name is not null ) then
427 p_retcode := '#';
428 return;
429 end if;
430
431 eam_mri_utils.error_if(
432 p_current_rowid,
433 p_interface_id,
434 '(NOT EXISTS
435 (SELECT METER_ID
436 FROM EAM_METERS
437 WHERE METER_NAME = MRI.METER_NAME
438 AND MRI.READING_DATE BETWEEN
439 NVL(FROM_EFFECTIVE_DATE, MRI.READING_DATE-1)
440 AND NVL(TO_EFFECTIVE_DATE, MRI.READING_DATE+1)))',
441 'EAM',
442 'EAM_MR_INVALID_METER');
443
444 if ( eam_int_utils.has_errors ) then
445 eam_int_utils.load_errors('eam_meter_readings_interface');
446 update eam_meter_readings_interface
447 set process_status = WIP_CONSTANTS.ERROR,
448 process_phase = WIP_CONSTANTS.ML_VALIDATION
449 where rowid = p_current_rowid;
450 p_retcode := '2';
451 end if;
452
453 end meter_id;
454
455
456 procedure reading_date(p_current_rowid in rowid,
457 p_interface_id in number) is
458 begin
459 eam_mri_utils.error_if(
460 p_current_rowid,
461 p_interface_id,
462 '(MRI.READING_DATE > SYSDATE
463 OR NOT EXISTS
464 (SELECT 1
465 FROM EAM_METERS EM
466 WHERE EM.METER_ID = MRI.METER_ID
467 AND MRI.READING_DATE BETWEEN
468 NVL(EM.FROM_EFFECTIVE_DATE, MRI.READING_DATE-1)
469 AND NVL(EM.TO_EFFECTIVE_DATE, MRI.READING_DATE+1)))',
470 'EAM',
471 'EAM_MR_INVALID_READING_DATE'
472 );
473 eam_mri_utils.error_if(
474 p_current_rowid,
475 p_interface_id,
476 '(EXISTS (SELECT 1
477 FROM EAM_METER_READINGS EM
478 WHERE EM.METER_ID = MRI.METER_ID
479 AND EM.CURRENT_READING_DATE = MRI.READING_DATE
480 AND (EM.DISABLE_FLAG IS NULL OR DISABLE_FLAG = ''N'')))',
481 'EAM',
482 'EAM_SAME_READING_DATE_EXIST'
483 );
484 end reading_date;
485
486
487 procedure reset_flag(p_current_rowid in rowid,
488 p_interface_id in number) is
489 x_meter_id number;
490 x_reading_date date;
491 x_ltd number := null;
492 x_reset_flag varchar2(1);
493 begin
494 -- if it is reset and there is any meter reading data in the history table
495 -- that are after the reset, then we error out.
496 eam_mri_utils.error_if(
497 p_current_rowid,
498 p_interface_id,
499 '((RESET_FLAG = ''Y''
500 AND EXISTS
501 (SELECT 1
502 FROM EAM_METER_READINGS MR
503 WHERE MR.METER_ID = MRI.METER_ID
504 AND MR.CURRENT_READING_DATE > MRI.READING_DATE
505 AND (MR.DISABLE_FLAG IS NULL OR MR.DISABLE_FLAG=''N''))))',
506 'EAM',
507 'EAM_MR_RESET_NOT_ALLOWED'
508 );
509
510 eam_int_utils.warn_irrelevant_column(
511 p_current_rowid,
512 p_interface_id,
513 'eam_meter_readings_interface mri',
514 'LIFE_TO_DATE_READING',
515 'mri.reset_flag = ''Y''');
516
517 -- now default the life to date reading to the previous entry
518 begin
519 select meter_id,
520 reading_date,
521 reset_flag
522 into x_meter_id,
523 x_reading_date,
524 x_reset_flag
525 from eam_meter_readings_interface
526 where rowid = p_current_rowid;
527
528 if ( x_reset_flag is null OR x_reset_flag <> 'Y' ) then
529 return;
530 end if;
531
532 select life_to_date_reading
533 into x_ltd
534 from eam_meter_readings
535 where meter_id = x_meter_id
536 and current_reading_date =
537 (select max(current_reading_date)
538 from eam_meter_readings
539 where meter_id = x_meter_id
540 and current_reading_date < x_reading_date
541 and (disable_flag is null or disable_flag='N'))
542 and (disable_flag is null or disable_flag='N');
543 exception when others then
544 x_ltd := null;
545 end;
546
547 if ( x_ltd is null ) then
548 fnd_message.set_name('EAM', 'EAM_NO_PREV_READING_LTD');
549 eam_int_utils.record_error(p_interface_id,
550 fnd_message.get,
551 FALSE);
552 else
553 update eam_meter_readings_interface
554 set life_to_date_reading = x_ltd
555 where rowid = p_current_rowid;
556 end if;
557 end reset_flag;
558
559
560 /**
561 * This method do the validation and set the corresponding ltd reading if null.
562 */
563 procedure reading_values(p_current_rowid in rowid,
564 p_interface_id in number) is
565 x_meter_id number := null;
566 x_meter_type number := 1;
567 l_ltd number;
568 x_reading_date date := null;
569 x_reading_value number := null;
570 x_life_to_date_reading number := null;
571 x_value_change_dir number := null;
572 x_pre_reading number := null;
573 x_next_reading number := null;
574 x_rule_broken boolean := false;
575 x_pre_ltd number := null;
576 x_pre_rowid rowid;
577 x_next_rowid rowid;
578 x_next_ltd number;
579 x_ltd_defaulted boolean := false;
580 begin
581 -- if both life to date reading and reading value are provided even if
582 -- it is not resetting, we ignore the reading value field. Normally, this
583 -- would rarely happen.
584 eam_int_utils.warn_redundant_column(
585 p_current_rowid,
586 p_interface_id,
587 'eam_meter_readings_interface',
588 'life_to_date_reading',
589 'reading_value',
590 'reset_flag <> ''Y''');
591
592 select mri.reading_value,
593 mri.life_to_date_reading,
594 mri.meter_id,
595 nvl(em.meter_type, 1),
596 mri.reading_date,
597 em.value_change_dir
598 into x_reading_value,
599 x_life_to_date_reading,
600 x_meter_id,
601 x_meter_type,
602 x_reading_date,
603 x_value_change_dir
604 from eam_meter_readings_interface mri,
605 eam_meters em
606 where mri.meter_id = em.meter_id
607 and mri.rowid = p_current_rowid;
608
609
610
611 -- if x_life_to_date_reading is not null, then we use it instead of
612 -- reading value provided. The validation will be done there.
613
614 -- commented due to bug 2156306
615 --if ( x_life_to_date_reading is null ) then
616 begin
617 select rowid
618 into x_pre_rowid
619 from eam_meter_readings
620 where meter_id = x_meter_id and
621 current_reading_date =
622 (select max(current_reading_date)
623 from eam_meter_readings
624 where meter_id = x_meter_id
625 and current_reading_date < x_reading_date
626 and (disable_flag is null or disable_flag='N'));
627
628 exception when others then
629 --dbms_output.put_line('some exception');
630 x_pre_rowid := null;
631 end;
632
633 if ( x_pre_rowid is not null ) then
634 select current_reading, life_to_date_reading
635 into x_pre_reading, x_pre_ltd
636 from eam_meter_readings
637 where rowid = x_pre_rowid;
638 else
639 x_pre_reading := null;
640 --dbms_output.put_line('null???');
641 end if;
642
643 begin
644 select rowid
645 into x_next_rowid
646 from eam_meter_readings
647 where current_reading_date =
648 (select min(current_reading_date)
649 from eam_meter_readings
650 where meter_id = x_meter_id
651 and current_reading_date > x_reading_date
652 and (disable_flag is null or disable_flag = 'N'));
653
654 exception when others then
655 x_next_rowid := null;
656 end;
657
658 if ( x_next_rowid is not null ) then
659 select current_reading, life_to_date_reading
660 into x_next_reading, x_next_ltd
661 from eam_meter_readings
662 where rowid = x_next_rowid;
663 else
664 x_next_reading := null;
665 end if;
666
667 -- dbms_output.put_line('here');
668 l_ltd := eam_meters_util.calculate_ltd(x_meter_id,
669 x_reading_date, x_reading_value, x_meter_type);
670 if ( x_pre_reading is not null ) then
671 -- dbms_output.put_line('here 1');
672 if(x_meter_type = 1) then
673 if ( (x_value_change_dir = 1 AND x_pre_reading > x_reading_value)
674 OR (x_value_change_dir = 2 AND x_pre_reading < x_reading_value) ) then
675 x_rule_broken := true;
676 end if;
677 elsif (x_meter_type = 2) then
678 if ( (x_value_change_dir = 1 AND x_reading_value < 0)
679 OR (x_value_change_dir = 2 AND x_reading_value > 0) ) then
680 x_rule_broken := true;
681 end if;
682
683 end if;
684 -- default the ltd reading
685
686
687 update eam_meter_readings_interface
688 -- set life_to_date_reading = x_pre_ltd + x_reading_value - x_pre_reading
689 set life_to_date_reading = l_ltd
690 where rowid = p_current_rowid;
691 x_ltd_defaulted := true;
692 end if;
693
694 if ( x_next_reading is not null ) then
695 if(x_meter_type = 1) then
696 if ( (x_value_change_dir = 1 AND x_next_reading < x_reading_value)
697 OR (x_value_change_dir = 2 AND x_next_reading > x_reading_value) ) then
698 x_rule_broken := true;
699 end if;
700 elsif (x_meter_type = 2) then
701 if ( (x_value_change_dir = 1 AND x_reading_value < 0)
702 OR (x_value_change_dir = 2 AND x_reading_value > 0) ) then
703 x_rule_broken := true;
704 end if;
705
706 end if;
707
708 -- default the ltd reading
709 if ( NOT x_ltd_defaulted ) then
710
711 update eam_meter_readings_interface
712 --set life_to_date_reading = x_next_ltd + x_reading_value - x_next_reading
713 set life_to_date_reading = l_ltd
714 where rowid = p_current_rowid;
715 x_ltd_defaulted := true;
716 end if;
717 end if;
718
719 if ( NOT x_ltd_defaulted ) then
720 update eam_meter_readings_interface
721 -- set life_to_date_reading = x_reading_value
722 set life_to_date_reading = l_ltd
723 where rowid = p_current_rowid;
724 end if;
725
726 if ( x_rule_broken ) then
727 fnd_message.set_name('EAM', 'EAM_MR_VALUE_BREAK_PATTERN');
728 eam_int_utils.record_error(p_interface_id,
729 fnd_message.get,
730 true);
731
732 /*
733 eam_int_utils.record_error(p_interface_id,
734 fnd_message.get,
735 false);
736 */
737 end if;
738
739 -- commented due to bug 2156306
740 -- end if;
741
742 end reading_values;
743
744
745 /**
746 * This method will be called only when ltd column is not null.
747 * This method is deprecated. It's not being called anywhere
748 */
749 procedure life_to_date_reading(p_current_rowid in rowid,
750 p_interface_id in number) is
751 x_reset_flag varchar2(1) := null;
752 x_ltd_reading number := null;
753 x_reading_date date := null;
754 x_pre_ltd number := null;
755 x_pre_reading number := null;
756 x_pre_rowid rowid;
757 x_next_ltd number := null;
758 x_next_reading number := null;
759 x_next_rowid rowid;
760 x_meter_id number := null;
761 x_value_change_dir number := null;
762 x_rule_broken boolean := false;
763 x_defaulted boolean := false;
764 begin
765 select mri.life_to_date_reading,
766 mri.reading_date,
767 mri.reset_flag,
768 mri.meter_id,
769 em.value_change_dir
770 into x_ltd_reading,
771 x_reading_date,
772 x_reset_flag,
773 x_meter_id,
774 x_value_change_dir
775 from eam_meter_readings_interface mri,
776 eam_meters em
777 where mri.meter_id = em.meter_id
778 and mri.rowid = p_current_rowid;
779
780 begin
781 select rowid
782 into x_pre_rowid
783 from eam_meter_readings
784 where current_reading_date =
785 (select max(current_reading_date)
786 from eam_meter_readings
787 where meter_id = x_meter_id
788 and current_reading_date < x_reading_date
789 and (disable_flag is null or disable_flag='N'));
790
791 exception when others then
792 x_pre_rowid := null;
793 end;
794
795 if ( x_pre_rowid is not null ) then
796 select current_reading, life_to_date_reading
797 into x_pre_reading, x_pre_ltd
798 from eam_meter_readings
799 where rowid = x_pre_rowid;
800 else
801 x_pre_ltd := null;
802 end if;
803
804
805 begin
806 select rowid
807 into x_next_rowid
808 from eam_meter_readings
809 where current_reading_date =
810 (select min(current_reading_date)
811 from eam_meter_readings
812 where meter_id = x_meter_id
813 and current_reading_date > x_reading_date
814 and (disable_flag is null or disable_flag='N'));
815
816 exception when others then
817 x_next_rowid := null;
818 end;
819
820 if ( x_next_rowid is not null ) then
821 select life_to_date_reading, current_reading
822 into x_next_ltd, x_next_reading
823 from eam_meter_readings
824 where rowid = x_next_rowid;
825 else
826 x_next_ltd := null;
827 end if;
828
829 if ( x_pre_ltd is not null ) then
830 if ( (x_value_change_dir = 1 AND x_pre_ltd > x_ltd_reading)
831 OR (x_value_change_dir = 2 AND x_pre_reading < x_ltd_reading) ) then
832 x_rule_broken := true;
833 end if;
834 if ( x_reset_flag <> 'Y' ) then
835 update eam_meter_readings_interface
836 set reading_value = x_pre_reading + x_ltd_reading - x_pre_ltd
837 where rowid = p_current_rowid;
838 x_defaulted := true;
839 end if;
840 end if;
841
842 if ( x_next_ltd is not null ) then
843 if ( (x_value_change_dir = 1 AND x_next_ltd < x_ltd_reading)
844 OR (x_value_change_dir = 2 AND x_next_ltd > x_ltd_reading) ) then
845 x_rule_broken := true;
846 end if;
847 if ( x_reset_flag <> 'Y' AND (NOT x_defaulted) ) then
848 update eam_meter_readings_interface
849 set reading_value = x_next_reading + x_ltd_reading - x_next_ltd
850 where rowid = p_current_rowid;
851 end if;
852 end if;
853
854 if ( x_rule_broken ) then
855 fnd_message.set_name('EAM', 'EAM_MR_LTD_BREAK_PATTERN');
856 eam_int_utils.record_error(p_interface_id,
857 fnd_message.get,
858 true);
859 end if;
860
861 end life_to_date_reading;
862
863 END eam_metr_validator;
864
[Home] [Help]