[Home] [Help]
PACKAGE BODY: APPS.MTH_PROCESS_TXN_PKG
Source
1 Package Body MTH_PROCESS_TXN_PKG AS
2 /*$Header: mthptxnb.pls 120.5.12020000.1 2012/07/24 16:11:47 sasuren noship $ */
3
4
5 /* ****************************************************************************
6 * Procedure :process_transactions *
7 * Description :... *
8 * File Name :MTHPTXNB.PLS *
9 * Visibility :Public *
10 * Parameters : *
11 ******************************************************************************/
12
13 PROCEDURE process_transactions(
14 p_err_buff OUT NOCOPY VARCHAR2,
15 p_retcode OUT NOCOPY NUMBER,
16 p_mode IN VARCHAR2, --INIT, INCR, RECAL
17 p_source IN VARCHAR2, --TAG, CSVS, CSVO, CSVSO
18 p_from_tz IN VARCHAR2, --From time zone (for tag)
19 p_to_tz IN VARCHAR2, --To time zone (for tag)
20 p_plant_pk_key IN NUMBER DEFAULT NULL, --Plant for which recalculation has to be done
21 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
22 p_recal_from_date_str IN VARCHAR2 DEFAULT NULL, --Recalculation from date
23 p_recal_to_date_str IN VARCHAR2 DEFAULT NULL --Recalculation to date
24 ) IS
25 l_execution_id NUMBER;
26 l_retcode NUMBER DEFAULT 0;
27 l_ret_code NUMBER DEFAULT 0;
28 l_err_buff VARCHAR2(1000);
29 l_recal_from_date DATE;
30 l_recal_to_date DATE;
31 l_valid NUMBER;
32 l_valid_temp NUMBER;
33 l_err_msg VARCHAR2(1000);
34 TYPE factTable IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
35 l_fact_table factTable;
36 TYPE fromDate IS TABLE OF DATE INDEX BY BINARY_INTEGER;
37 l_from_date fromDate;
38 l_sts_fromdate fromDate;
39 l_op_fromdate fromDate;
40 l_reading_time fromDate;
41 i NUMBER;
42 v_msg VARCHAR2(300);
43 l_count NUMBER;
44 BEGIN
45
46 mth_util_pkg.initialize_debug('Concurrent Program "Process/Reprocess Transactions"', l_execution_id);
47 mth_util_pkg.log_msg('Process/Reprocess Transactions start', mth_util_pkg.G_DBG_PROC_FUN_START);
48 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_MAIN_PARAM);
49 mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_MAIN_PARAM);
50 mth_util_pkg.log_msg('p_from_tz = ' || p_from_tz , mth_util_pkg.G_DBG_MAIN_PARAM);
51 mth_util_pkg.log_msg('p_to_tz = ' || p_to_tz , mth_util_pkg.G_DBG_MAIN_PARAM);
52 mth_util_pkg.log_msg('p_plant_pk_key = ' || p_plant_pk_key , mth_util_pkg.G_DBG_MAIN_PARAM);
53 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key , mth_util_pkg.G_DBG_MAIN_PARAM);
54 mth_util_pkg.log_msg('p_recal_from_date_str = ' || p_recal_from_date_str, mth_util_pkg.G_DBG_MAIN_PARAM);
55 mth_util_pkg.log_msg('p_recal_to_date_str = ' || p_recal_to_date_str, mth_util_pkg.G_DBG_MAIN_PARAM);
56
57 IF (p_mode = 'RECAL') THEN
58
59 l_recal_from_date := fnd_date.canonical_to_date(p_recal_from_date_str);
60 l_recal_to_date := nvl(fnd_date.canonical_to_date(p_recal_to_date_str),sysdate);
61
62 mth_util_pkg.log_msg('l_recal_from_date = ' || fnd_date.date_to_canonical(l_recal_from_date), mth_util_pkg.G_DBG_MAIN_PARAM);
63 mth_util_pkg.log_msg('l_recal_to_date = ' || fnd_date.date_to_canonical(l_recal_to_date), mth_util_pkg.G_DBG_MAIN_PARAM);
64
65 END IF;
66
67 --Validations
68 IF (p_mode NOT IN ('INIT','INCR','RECAL') ) THEN
69 v_msg:=fnd_message.get_string('MTH','MTH_INCORRECT_MODE');
70 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
71 p_retcode := 2;
72 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
73 END IF;
74
75 IF( p_source NOT IN ('CSVO','CSVS','CSVSO','TAG') ) THEN
76 v_msg:=fnd_message.get_string('MTH','MTH_INCORRECT_SOURCE');
77 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
78 p_retcode := 2;
79 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
80 END IF;
81
82
83 IF (p_mode = 'RECAL') THEN
84
85
86 IF (p_plant_pk_key IS NOT NULL) THEN
87 SELECT Count(*)
88 INTO l_count
89 FROM mth_plants_d mpd
90 WHERE mpd.plant_pk_key = p_plant_pk_key;
91 END IF;
92
93 IF l_count < 1 THEN
94 v_msg:=fnd_message.get_string('MTH','MTH_SITE_MIS_ERR');
95 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
96 RAISE MTH_PROCESS_TXN_PKG.RAISE_ERROR;
97 END IF;
98
99
100 IF (p_equipment_pk_key is not null) THEN
101 SELECT Count(*)
102 INTO l_count
103 FROM mth_equipments_d med
104 WHERE med.equipment_pk_key = p_equipment_pk_key
105 AND med.plant_fk_key = Nvl(p_plant_pk_key,med.plant_fk_key);
106 END IF;
107
108 IF l_count < 1 THEN
109 v_msg:=fnd_message.get_string('MTH','MTH_EQUIP_MIS_ERR');
110 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
111 RAISE MTH_PROCESS_TXN_PKG.RAISE_ERROR;
112 END IF;
113
114 IF (l_recal_from_date IS NULL) THEN
115 v_msg:=fnd_message.get_string('MTH','MTH_NULL_RECAL_FROM_DATE');
116 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
117 p_retcode := 2;
118 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
119 END IF;
120
121 IF (l_recal_from_date > l_recal_to_date) THEN
122 v_msg:=fnd_message.get_string('MTH','MTH_RECAL_FROM_DATE');
123 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
124 p_retcode := 2;
125 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
126 END IF;
127
128 IF (p_source = 'TAG') THEN
129 v_msg:=fnd_message.get_string('MTH','MTH_ROUTE_IN_RECAL_MODE');
130 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
131 p_retcode := 2;
132 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
133 END IF;
134
135 IF (p_source IN ('CSVS','CSVSO') ) THEN
136
137 SELECT from_date
138 BULK COLLECT
139 INTO l_from_date
140 FROM mth_equip_statuses_stg
141 FOR UPDATE OF from_date;
142
143 MTH_PROCESS_STATUS_PKG.VALIDATE(p_mode,
144 p_source,
145 l_retcode,
146 l_err_buff
147 );
148
149 END IF;
150
151 IF (l_retcode = 2) THEN
152 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
153 END IF;
154
155 IF (l_retcode = 1) THEN
156 l_ret_code := l_retcode;
157 END IF;
158
159 IF (p_source IN ('CSVO','CSVSO') ) THEN
160 SELECT reading_time
161 BULK COLLECT
162 INTO l_reading_time
163 FROM mth_equip_output_stg
164 FOR UPDATE OF reading_time;
165
166 MTH_PROCESS_OUTPUT_PKG.VALIDATE(p_mode,
167 p_source,
168 l_retcode,
169 l_err_buff
170 );
171
172 END IF;
173
174 IF (l_retcode = 2) THEN
175 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
176 END IF;
177
178 IF (p_source = 'TAG') THEN
179 mth_util_pkg.log_msg('Call for tag route recalculation to be added later',mth_util_pkg.G_DBG_USER_ERROR);
180 ELSE
181 IF (p_source IN ('CSVS','CSVSO') ) THEN
182
183 MTH_PROCESS_STATUS_PKG.PROCESS_STATUS( p_mode,
184 p_source,
185 l_recal_from_date,
186 l_recal_to_date,
187 p_equipment_pk_key,
188 p_plant_pk_key,
189 l_retcode
190 );
191 IF(l_retcode = 2) THEN
192 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
193 END IF;
194
195 IF(l_retcode = 1) THEN
196 l_ret_code := l_retcode;
197 END IF;
198 END IF;
199
200 IF (p_source IN ('CSVO','CSVSO') ) THEN
201 MTH_PROCESS_OUTPUT_PKG.PROCESS_OUTPUT( p_mode,
202 p_source,
203 l_recal_from_date,
204 l_recal_to_date,
205 p_equipment_pk_key,
206 p_plant_pk_key,
207 l_retcode
208 );
209 IF(l_retcode = 2) THEN
210 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
211 END IF;
212
213 IF(l_retcode = 1) THEN
214 l_ret_code := l_retcode;
215 END IF;
216
217 END IF;
218
219 IF (l_ret_code = 1) THEN
220 RAISE MTH_PROCESS_TXN_PKG.VALIDATION_ERR;
221 END IF;
222
223 IF (p_source IN ('CSVS','CSVSO') ) THEN
224 SELECT from_date
225 BULK COLLECT
226 INTO l_sts_fromdate
227 FROM mth_run_log
228 WHERE fact_table = 'MTH_EQUIP_STATUS_SUMMARY'
229 FOR UPDATE OF from_date;
230
231 MTH_PROCESS_STATUS_PKG.PROCESS_STATUS_SUMMARY( p_mode,
232 l_recal_from_date,
233 l_recal_to_date,
234 p_equipment_pk_key,
235 p_plant_pk_key,
236 l_retcode
237 );
238 IF(l_retcode = 2) THEN
239 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
240 END IF;
241
242 IF(l_retcode = 1) THEN
243 l_ret_code := l_retcode;
244 END IF;
245 END IF;
246
247 IF (p_source IN ('CSVO','CSVSO') ) THEN
248 SELECT from_date
249 BULK COLLECT
250 INTO l_sts_fromdate
251 FROM mth_run_log
252 WHERE fact_table = 'MTH_EQUIP_OUTPUT_SUMMARY'
253 FOR UPDATE OF from_date;
254
255 MTH_PROCESS_OUTPUT_PKG.PROCESS_OUTPUT_SUMMARY( p_mode,
256 l_recal_from_date,
257 l_recal_to_date,
258 p_equipment_pk_key,
259 p_plant_pk_key,
260 l_retcode
261 );
262 IF(l_retcode = 2) THEN
263 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
264 END IF;
265
266 IF(l_retcode = 1) THEN
267 l_ret_code := l_retcode;
268 END IF;
269 END IF;
270 END IF;
271
272 COMMIT;
273 ELSE
274 IF (p_source = 'TAG') THEN
275
276 MTH_LOAD_TAG_READINGS_PKG.PROCESS_READINGS( p_mode,
277 l_recal_from_date,
278 l_recal_to_date,
279 p_equipment_pk_key,
280 p_from_tz,
281 p_to_tz,
282 l_retcode
283 );
284 IF(l_retcode = 2) THEN
285 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
286 END IF;
287
288 IF(l_retcode = 1) THEN
289 l_ret_code := l_retcode;
290 END IF;
291
292 MTH_PROCESS_STATUS_PKG.PROCESS_STATUS( p_mode,
293 p_source,
294 l_recal_from_date,
295 l_recal_to_date,
296 p_equipment_pk_key,
297 p_plant_pk_key,
298 l_retcode
299 );
300 IF(l_retcode = 2) THEN
301 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
302 END IF;
303
304 IF(l_retcode = 1) THEN
305 l_ret_code := l_retcode;
306 END IF;
307
308 MTH_PROCESS_OUTPUT_PKG.PROCESS_OUTPUT( p_mode,
309 p_source,
310 l_recal_from_date,
311 l_recal_to_date,
312 p_equipment_pk_key,
313 p_plant_pk_key,
314 l_retcode
315 );
316 IF(l_retcode = 2) THEN
317 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
318 END IF;
319
320 IF(l_retcode = 1) THEN
321 l_ret_code := l_retcode;
322 END IF;
323
324
325 MTH_PROCESS_STATUS_PKG.PROCESS_STATUS_SUMMARY( p_mode,
326 l_recal_from_date,
327 l_recal_to_date,
328 p_equipment_pk_key,
329 p_plant_pk_key,
330 l_retcode
331 );
332 IF(l_retcode = 2) THEN
333 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
334 END IF;
335
336 IF(l_retcode = 1) THEN
337 l_ret_code := l_retcode;
338 END IF;
339
340
341
342 MTH_PROCESS_OUTPUT_PKG.PROCESS_OUTPUT_SUMMARY( p_mode,
343 l_recal_from_date,
344 l_recal_to_date,
345 p_equipment_pk_key,
346 p_plant_pk_key,
347 l_retcode
348 );
349 IF(l_retcode = 2) THEN
350 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
351 END IF;
352
353 IF(l_retcode = 1) THEN
354 l_ret_code := l_retcode;
355 END IF;
356
357
358 MTH_PROCESS_REASON_PKG.PROCESS_REASONS( p_mode,
359 p_source,
360 l_recal_from_date,
361 l_recal_to_date,
362 p_equipment_pk_key,
363 p_plant_pk_key,
364 l_retcode
365 );
366 IF(l_retcode = 2) THEN
367 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
368 END IF;
369
370 IF(l_retcode = 1) THEN
371 l_ret_code := l_retcode;
372 END IF;
373
374 MTH_PROCESS_UDA_PKG.PROCESS_UDA( p_mode,
375 l_recal_from_date,
376 l_recal_to_date,
377 p_equipment_pk_key,
378 p_plant_pk_key,
379 l_retcode
380 );
381 IF(l_retcode = 2) THEN
382 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
383 END IF;
384
385 IF(l_retcode = 1) THEN
386 l_ret_code := l_retcode;
387 END IF;
388
389 MTH_EVENT_GEN_PKG.MTH_GEN_HDL_EVTS_FROM_STS_OUT (p_mode,
390 l_retcode
391 );
392 IF(l_retcode = 2) THEN
393 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
394 END IF;
395
396 IF(l_retcode = 1) THEN
397 l_ret_code := l_retcode;
398 END IF;
399
400 ELSE
401 IF (p_source IN ('CSVS','CSVSO') ) THEN
402 MTH_PROCESS_STATUS_PKG.PROCESS_STATUS( p_mode,
403 p_source,
404 l_recal_from_date,
405 l_recal_to_date,
406 p_equipment_pk_key,
407 p_plant_pk_key,
408 l_retcode
409 );
410 IF(l_retcode = 2) THEN
411 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
412 END IF;
413
414 IF(l_retcode = 1) THEN
415 l_ret_code := l_retcode;
416 END IF;
417 END IF;
418
419 IF (p_source IN ('CSVO','CSVSO') ) THEN
420 MTH_PROCESS_OUTPUT_PKG.PROCESS_OUTPUT( p_mode,
421 p_source,
422 l_recal_from_date,
423 l_recal_to_date,
424 p_equipment_pk_key,
425 p_plant_pk_key,
426 l_retcode
427 );
428 IF(l_retcode = 2) THEN
429 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
430 END IF;
431
432 IF(l_retcode = 1) THEN
433 l_ret_code := l_retcode;
434 END IF;
435 END IF;
436
437 IF (p_source IN ('CSVS','CSVSO') ) THEN
438 MTH_PROCESS_STATUS_PKG.PROCESS_STATUS_SUMMARY( p_mode,
439 l_recal_from_date,
440 l_recal_to_date,
441 p_equipment_pk_key,
442 p_plant_pk_key,
443 l_retcode
444 );
445 IF(l_retcode = 2) THEN
446 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
447 END IF;
448
449 IF(l_retcode = 1) THEN
450 l_ret_code := l_retcode;
451 END IF;
452 END IF;
453
454 IF (p_source IN ('CSVO','CSVSO') ) THEN
455 MTH_PROCESS_OUTPUT_PKG.PROCESS_OUTPUT_SUMMARY( p_mode,
456 l_recal_from_date,
457 l_recal_to_date,
458 p_equipment_pk_key,
459 p_plant_pk_key,
460 l_retcode
461 );
462 IF(l_retcode = 2) THEN
463 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
464 END IF;
465
466 IF(l_retcode = 1) THEN
467 l_ret_code := l_retcode;
468 END IF;
469 END IF;
470 IF (p_source IN ('CSVS','CSVO','CSVSO') ) THEN
471
472 MTH_EVENT_GEN_PKG.MTH_GEN_HDL_EVTS_FROM_STS_OUT (p_mode,
473 l_retcode
474 );
475 IF(l_retcode = 2) THEN
476 raise MTH_PROCESS_TXN_PKG.RAISE_ERROR;
477 END IF;
478
479 IF(l_retcode = 1) THEN
480 l_ret_code := l_retcode;
481 END IF;
482 END IF;
483
484 COMMIT;
485 END IF;
486 END IF;
487
488 l_valid := 0;
489
490 /*
491 1. RECAL and TAG are not supported.
492 2. Messages need to be translated.
493 if recal then
494 if p_source in CSVS , CSVSO then
495 take lock on status stg
496 validate status
497 end if
498 if p_source in CSVO , CSVSO then
499 take lock on output stg
500 validate output
501 end if
502 if p_source = TAG then
503 take lock on run log and readings latest
504 validate raw to readings
505 validate status
506 validate output
507 validate UDA
508 validate reasons
509 end if
510 if p_source = TAG then
511 process readings (raw to readings)
512 process status
513 process output
514 process reasons
515 prcess UDA
516 else
517 if p_source in CSVS , CSVSO then
518 process status
519 end if
520 if p_source in CSVO , CSVSO then
521 process output
522 end if
523 end if
524 process status summary
525 process output summary
526 if p_source = TAG then
527 update run log set from date = sysdate, to date = null;
528 end if
529 commit
530 else
531 if p_source = TAG then
532 process readings (raw to readings)
533 process status
534 process output
535 process reasons
536 prcess UDA
537 else
538 if p_source in CSVS , CSVSO then
539 process status
540 end if
541 if p_source in CSVO , CSVSO then
542 process output
543 end if
544 end if
545 process status summary
546 process output summary
547 commit
548 end if
549 refresh MVs?
550
551
552 */
553 IF(l_ret_code = 1) THEN
554 p_retcode := l_ret_code;
555 ELSE
556 p_retcode := l_retcode;
557 END IF;
558
559 mth_util_pkg.log_msg('process_transactions end', mth_util_pkg.G_DBG_PROC_FUN_END);
560
561 EXCEPTION
562 WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
563 ROLLBACK;
564 p_retcode := 1;
565 mth_util_pkg.log_msg('Exception VALIDATION_ERR in process_transactions', mth_util_pkg.G_DBG_EXCEPTION);
566
567 WHEN MTH_PROCESS_TXN_PKG.RAISE_ERROR THEN
568 ROLLBACK;
569 p_retcode := 2;
570 mth_util_pkg.log_msg('Exception RAISE_ERROR in process_transactions', mth_util_pkg.G_DBG_EXCEPTION);
571
572
573 WHEN OTHERS THEN
574 ROLLBACK;
575 p_retcode := 2;
576 mth_util_pkg.log_msg('Exception OTHERS in process_transactions', mth_util_pkg.G_DBG_EXCEPTION);
577 mth_util_pkg.log_msg('An unexpected error occurred. Given below is the associated technical information.', mth_util_pkg.G_DBG_USER_ERROR);
578 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_USER_ERROR);
579 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_USER_ERROR);
580 p_err_buff := substr(sqlerrm,1,240);
581
582 END process_transactions;
583
584
585
586 END MTH_PROCESS_TXN_PKG;