[Home] [Help]
PACKAGE BODY: APPS.MTH_PROCESS_STATUS_PKG
Source
1 PACKAGE BODY MTH_PROCESS_STATUS_PKG AS
2 /*$Header: mtheqstb.pls 120.12.12020000.3 2013/05/22 05:27:45 mgijare noship $*/
3
4 PROCEDURE INIT_STATUS_FROM_READING;
5 PROCEDURE INCR_STATUS_FROM_READING;
6 PROCEDURE RECAL_STATUS_FROM_READING(p_recal_from_date IN DATE, --Recalculation from date
7 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
8 p_equipment_pk_key IN NUMBER DEFAULT NULL); --Equipment to recalculate
9
10 PROCEDURE INIT_STATUS_FROM_CSV;
11 PROCEDURE INCR_STATUS_FROM_CSV;
12 PROCEDURE RECAL_STATUS_FROM_CSV(p_recal_from_date IN DATE, --Recalculation from date
13 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
14 p_equipment_pk_key IN NUMBER DEFAULT NULL,--Equipment to recalculate
15 p_plant_pk_key IN NUMBER DEFAULT NULL);--Site for which recalcution to be done
16
17 PROCEDURE PROCESS_STATUS_SMMRY_INIT;
18 PROCEDURE PROCESS_STATUS_SMMRY_INCR;
19 PROCEDURE PROCESS_STATUS_SMMRY_RECAL(p_recalc_from_date IN TIMESTAMP, --Recalculation from date
20 p_recalc_to_date IN TIMESTAMP DEFAULT NULL, --Recalculation to date
21 p_recalc_equip_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
22 p_recalc_plant_key IN NUMBER DEFAULT NULL); --Plant to recalculate
23
24 /*******************************************************************************
25 * Procedure :VALIDATE *
26 * Description :This procedure is the validate procedure *
27 * File Name :MTHEQOPB.PLS *
28 * Visibility :Public *
29 * Parameters : p_err_buff : Error message *
30 * p_retcode : Error Code *
31 * Modification log : *
32 * Author Date Change *
33 * Mandar Gijare 20-Sep-2011 Initial Creation *
34 *******************************************************************************/
35 PROCEDURE VALIDATE(
36 p_mode IN VARCHAR2,
37 p_source IN VARCHAR2,
38 p_retcode OUT NOCOPY NUMBER,
39 p_err_buff OUT NOCOPY VARCHAR2
40 ) IS
41 v_retcode NUMBER DEFAULT 0;
42 v_tag_reading_count NUMBER;
43 v_output_count NUMBER;
44 TYPE compileState IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
45 v_compile_state compileState;
46 v_msg VARCHAR2(300);
47 i NUMBER;
48 BEGIN
49
50 IF( (p_mode = 'RECAL') AND (p_source = 'TAG') ) THEN
51 SELECT Count(1)
52 INTO v_tag_reading_count
53 FROM mth_tag_readings mtr,
54 mth_entities mte,
55 mth_run_log mrl
56 WHERE mtr.mth_entity = mte.id
57 AND mte.mth_alias = 'Status'
58 AND mrl.fact_table = 'MTH_EQUIP_STATUSES'
59 AND mtr.last_update_date > mrl.from_date;
60
61 IF v_tag_reading_count > 0 THEN
62 mth_util_pkg.log_msg('All data from tag reading is still not populated to status table.',mth_util_pkg.G_DBG_EXCEPTION);
63 v_retcode := 2;
64 END IF;
65 END IF;
66
67 IF( p_mode = 'RECAL' ) THEN
68 SELECT Count(1)
69 INTO v_output_count
70 FROM mth_equip_statuses meo,
71 mth_run_log mrl
72 WHERE mrl.fact_table = 'MTH_EQUIP_STATUS_SUMMARY'
73 AND meo.last_update_date > mrl.from_date;
74
75 IF v_output_count > 0 THEN
76 v_msg:=fnd_message.get_string('MTH','MTH_STATUS_NOT_IN_SUMMARY');
77
78 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_EXCEPTION);
79 v_retcode := 2;
80 END IF;
81 END IF;
82
83
84 SELECT mview_name
85 BULK COLLECT
86 INTO v_compile_state
87 FROM dba_mviews
88 WHERE mview_name IN ('MTH_RESOURCE_COST_MV')
89 AND owner = sys_context('USERENV','CURRENT_SCHEMA')
90 AND compile_state <> 'VALID' ;
91
92 IF v_compile_state.Count > 0
93 THEN
94 FOR i IN v_compile_state.FIRST .. v_compile_state.LAST
95 LOOP
96 FND_MESSAGE.SET_TOKEN('MOC_MV_TOKEN',v_compile_state(i));
97 v_msg:=fnd_message.get_string('MTH','MTH_MV_NOT_REFRESHED');
98
99 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_EXCEPTION);
100 v_retcode := 2;
101 END LOOP;
102 END IF;
103
104 p_retcode := v_retcode;
105 p_err_buff := 'Error in validate procedure of MTH_PROCESS_STATUS_PKG.';
106
107 END VALIDATE;
108
109 /*******************************************************************************
110 * Procedure :PROCESS_STATUS *
111 * Description :This procedure is the main procedure for status *
112 * File Name :MTHEQSTB.PLS *
113 * Visibility :Public *
114 * Parameters : p_mode : INIT,INCR,RECAL *
115 * p_source : TAG,CSV *
116 * p_recal_from_date : Recalculation from date *
117 * p_recal_to_date : Recalculation to date *
118 * p_equipment_pk_key : Equipment to recalculate *
119 *******************************************************************************/
120
121 PROCEDURE PROCESS_STATUS( p_mode IN VARCHAR2, --INIT, INCR, RECAL
122 p_source IN VARCHAR2, --TAG, CSV
123 p_recal_from_date IN DATE DEFAULT NULL, --Recalculation from date
124 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
125 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
126 p_plant_pk_key IN NUMBER DEFAULT NULL, --Site for which recalcution to be done
127 p_ret_code OUT NOCOPY NUMBER
128 ) IS
129 l_ret_code NUMBER;
130 BEGIN
131 l_ret_code := 0;
132 mth_util_pkg.log_msg('PROCESS_STATUS start', mth_util_pkg.G_DBG_PROC_FUN_START);
133 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
134 mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_PARAM_VAL);
135
136 IF(p_mode = 'RECAL') THEN
137 mth_util_pkg.log_msg('p_recal_from_date = ' || to_char(p_recal_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
138 mth_util_pkg.log_msg('p_recal_to_date = ' || to_char(p_recal_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
139 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
140 END IF;
141
142 IF p_source = 'TAG' THEN
143 IF p_mode = 'INIT' THEN
144 INIT_STATUS_FROM_READING();
145 ELSIF p_mode = 'INCR' THEN
146 INCR_STATUS_FROM_READING();
147 ELSE
148 RECAL_STATUS_FROM_READING(p_recal_from_date,
149 p_recal_to_date,
150 p_equipment_pk_key
151 );
152 END IF;
153 END IF;
154
155 IF p_source IN ('CSVS','CSVSO') THEN
156 IF p_mode = 'INIT' THEN
157 INIT_STATUS_FROM_CSV();
158 ELSIF p_mode = 'INCR' THEN
159 INCR_STATUS_FROM_CSV();
160 ELSE
161 BEGIN
162 RECAL_STATUS_FROM_CSV(p_recal_from_date,
163 p_recal_to_date,
164 p_equipment_pk_key,
165 p_plant_pk_key );
166 EXCEPTION
167 WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
168 l_ret_code := 1;
169 mth_util_pkg.log_msg('Exception VALIDATION_ERR in PROCESS_STATUS', mth_util_pkg.G_DBG_EXCEPTION);
170 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
171 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
172 END;
173 END IF;
174 END IF;
175
176 p_ret_code := l_ret_code;
177 mth_util_pkg.log_msg('PROCESS_STATUS end', mth_util_pkg.G_DBG_PROC_FUN_END);
178
179 EXCEPTION
180 WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
181 l_ret_code := 1;
182 p_ret_code := l_ret_code;
183 mth_util_pkg.log_msg('Exception VALIDATION_ERR in PROCESS_STATUS', mth_util_pkg.G_DBG_EXCEPTION);
184 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
185 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
186 RAISE;
187 WHEN OTHERS THEN
188 l_ret_code := 2;
189 p_ret_code := l_ret_code;
190 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_STATUS', mth_util_pkg.G_DBG_EXCEPTION);
191 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
192 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
193 RAISE;
194
195 END PROCESS_STATUS;
196
197 /*******************************************************************************
198 * Procedure :INIT_STATUS_FROM_READING *
199 * Description :This procedure is used for calculating the status *
200 * in INIT mode *
201 * File Name :MTHEQSTB.PLS *
202 * Visibility :Private *
203 * Parameters : *
204 *******************************************************************************/
205 PROCEDURE INIT_STATUS_FROM_READING IS
206 v_log_date DATE;
207 v_unassigned_val VARCHAR2(30);
208 BEGIN
209 mth_util_pkg.log_msg('INIT_STATUS_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
210
211 -- Initialize default parameters
212 v_log_date := sysdate;
213 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
214
215 -- Call mth_run_log_pre_load
216 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_STATUSES',v_unassigned_val,'INITIAL',NULL,0,v_log_date);
217
218 --delete status table
219 DELETE FROM MTH_EQUIP_STATUSES;
220 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
221
222 INSERT INTO MTH_EQUIP_STATUSES (
223 EQUIPMENT_FK_KEY,
224 SHIFT_WORKDAY_FK_KEY,
225 HOUR_FK_KEY,
226 FROM_DATE,
227 TO_DATE,
228 STATUS,
229 SYSTEM_FK_KEY,
230 USER_ATTR1,
231 USER_ATTR2,
232 USER_ATTR3,
233 USER_ATTR4,
234 USER_ATTR5,
235 USER_MEASURE1,
236 USER_MEASURE2,
237 USER_MEASURE3,
238 USER_MEASURE4,
239 USER_MEASURE5,
240 CREATION_DATE,
241 LAST_UPDATE_DATE,
242 CREATION_SYSTEM_ID,
243 LAST_UPDATE_SYSTEM_ID,
244 READING_TIME
245 )
246
247 SELECT r.EQUIPMENT_FK_KEY,
248 s.SHIFT_WORKDAY_FK_KEY,
249 s.HOUR_PK_KEY HOUR_FK_KEY,
250 Greatest(s.FROM_DATE,r.FROM_DATE) AS FROM_DATE,
251 Least(s.TO_DATE,r.TO_DATE) AS TO_DATE,
252 r.TAG_DATA STATUS,
253 v_unassigned_val as SYSTEM_FK_KEY,
254 r.USER_ATTR1,
255 r.USER_ATTR2,
256 r.USER_ATTR3,
257 r.USER_ATTR4,
258 r.USER_ATTR5,
259 r.USER_MEASURE1,
260 r.USER_MEASURE2,
261 r.USER_MEASURE3,
262 r.USER_MEASURE4,
263 r.USER_MEASURE5,
264 SYSDATE CREATION_DATE,
265 SYSDATE LAST_UPDATE_DATE,
266 v_unassigned_val as CREATION_SYSTEM_ID,
267 v_unassigned_val as LAST_UPDATE_SYSTEM_ID,
268 r.FROM_DATE
269 FROM (SELECT r.READING_TIME FROM_DATE,
270 Lead(r.READING_TIME) over (PARTITION BY r.EQUIPMENT_FK_KEY ORDER BY r.READING_TIME) - 1/(24*60*60) AS To_Date,
271 r.EQUIPMENT_FK_KEY,
272 r.TAG_DATA,
273 r.USER_ATTR1,
274 r.USER_ATTR2,
275 r.USER_ATTR3,
276 r.USER_ATTR4,
277 r.USER_ATTR5,
278 r.USER_MEASURE1,
279 r.USER_MEASURE2,
280 r.USER_MEASURE3,
281 r.USER_MEASURE4,
282 r.USER_MEASURE5
283 FROM (SELECT readings.READING_TIME,
284 readings.EQUIPMENT_FK_KEY,
285 readings.TAG_DATA,
286 readings.USER_ATTR1,
287 readings.USER_ATTR2,
288 readings.USER_ATTR3,
289 readings.USER_ATTR4,
290 readings.USER_ATTR5,
291 readings.USER_MEASURE1,
292 readings.USER_MEASURE2,
293 readings.USER_MEASURE3,
294 readings.USER_MEASURE4,
295 readings.USER_MEASURE5 ,
296 readings.mth_entity
297 FROM mth_tag_readings readings
298 WHERE readings.EQUIPMENT_FK_KEY IS NOT NULL AND
299 readings.HOUR_FK_KEY IS NOT NULL AND
300 -- readings.PROCESSED_FLAG = 0 AND
301 readings.LAST_UPDATE_DATE <= v_log_date AND
302 readings.TAG_DATA IS NOT NULL
303
304 UNION
305 SELECT err.READING_TIME,
306 err.EQUIPMENT_FK_KEY,
307 err.TAG_DATA,
308 err.USER_ATTR1,
309 err.USER_ATTR2,
310 err.USER_ATTR3,
311 err.USER_ATTR4,
312 err.USER_ATTR5,
313 err.USER_MEASURE1,
314 err.USER_MEASURE2,
315 err.USER_MEASURE3,
316 err.USER_MEASURE4,
317 err.USER_MEASURE5 ,
318 err.mth_entity
319 FROM mth_tag_readings_err err
320 WHERE err.EQUIPMENT_FK_KEY IS NOT NULL AND
321 err.HOUR_FK_KEY IS NOT NULL AND
322 --err.PROCESSED_FLAG = 0 AND
323 err.EQUIPMENT_STATUS = 'ACTIVE' AND
324 err.TAG_DATA IS NOT NULL )r,
325 MTH_ENTITIES e
326 WHERE e.mth_alias = 'Status'
327 AND e.id = r.mth_entity
328 AND r.TAG_DATA IS NOT NULL
329 ) r,
330 MTH_EQUIP_SHIFT_HR_V s
331 WHERE ( ( r.FROM_DATE BETWEEN s.from_date AND s.to_date )
332 OR ( s.from_date BETWEEN r.FROM_DATE AND nvl(r.To_Date,r.from_date) ) )
333 AND r.EQUIPMENT_FK_KEY = s.EQUIPMENT_FK_KEY;
334 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
335
336 --update processed flag in readings table
337 update MTH_TAG_READINGS t
338 set PROCESSED_FLAG = 1,
339 last_update_date=sysdate
340 where exists (
341 select 1
342 from mth_entities m
343 where t.MTH_ENTITY = m.ID
344 --AND t.PROCESSED_FLAG = 0
345 AND t.EQUIPMENT_FK_KEY IS NOT NULL
346 AND t.HOUR_FK_KEY IS NOT NULL
347 AND m.MTH_ALIAS = 'Status'
348 AND t.LAST_UPDATE_DATE <= v_log_date
349 AND t.TAG_DATA IS NOT NULL
350 );
351 mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
352 --update processed flag in readings error table
353 update MTH_TAG_READINGS_ERR t
354 set PROCESSED_FLAG = 1
355 where exists (
356 select 1
357 from mth_entities m
358 where t.MTH_ENTITY = m.ID
359 -- AND t.PROCESSED_FLAG = 0
360 AND t.EQUIPMENT_FK_KEY IS NOT NULL
361 AND t.HOUR_FK_KEY IS NOT NULL
362 AND m.MTH_ALIAS = 'Status'
363 AND t.EQUIPMENT_STATUS = 'ACTIVE'
364 AND t.TAG_DATA IS NOT NULL
365 );
366 mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
367
368 ----Call mth_run_log_post_load
369 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_STATUSES',v_unassigned_val);
370
371
372 mth_util_pkg.log_msg('INIT_STATUS_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
373 EXCEPTION
374 WHEN OTHERS THEN
375 mth_util_pkg.log_msg('Exception OTHERS in INIT_STATUS_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
376 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
377 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
378 RAISE;
379 END INIT_STATUS_FROM_READING;
380
381 /*******************************************************************************
382 * Procedure :INCR_STATUS_FROM_READING *
383 * Description :This procedure is used for calculating the status *
384 * in INCR mode *
385 * File Name :MTHEQSTB.PLS *
386 * Visibility :Private *
387 * Parameters : *
388 *******************************************************************************/
389 PROCEDURE INCR_STATUS_FROM_READING IS
390 v_log_from_date DATE;
391 v_log_to_date DATE;
392 v_unassigned_val VARCHAR2(30);
393
394 BEGIN
395 mth_util_pkg.log_msg('INCR_STATUS_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
396
397 -- Initialize default parameters
398 v_log_to_date := sysdate;
399 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
400
401
402 -- Call mth_run_log_pre_load
403 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_STATUSES',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
404
405 -- Call GET_RUN_LOG_DATES
406 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_STATUSES',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
407
408
409
410
411 MERGE INTO MTH_EQUIP_STATUSES o USING
412 ( SELECT r.EQUIPMENT_FK_KEY,
413 s.SHIFT_WORKDAY_FK_KEY,
414 s.HOUR_PK_KEY HOUR_FK_KEY,
415 Greatest(s.FROM_DATE,r.FROM_DATE) AS FROM_DATE,
416 Least(s.TO_DATE,r.TO_DATE) AS TO_DATE,
417 r.TAG_DATA STATUS,
418 v_unassigned_val as SYSTEM_FK_KEY,
419 r.USER_ATTR1,
420 r.USER_ATTR2,
421 r.USER_ATTR3,
422 r.USER_ATTR4,
423 r.USER_ATTR5,
424 r.USER_MEASURE1,
425 r.USER_MEASURE2,
426 r.USER_MEASURE3,
427 r.USER_MEASURE4,
428 r.USER_MEASURE5,
429 r.FROM_DATE Reading_time
430 FROM (SELECT r.READING_TIME FROM_DATE,
431 Lead(r.READING_TIME) over (PARTITION BY r.EQUIPMENT_FK_KEY ORDER BY r.READING_TIME) - 1/(24*60*60) AS To_Date,
432 r.EQUIPMENT_FK_KEY,
433 r.TAG_DATA,
434 r.USER_ATTR1,
435 r.USER_ATTR2,
436 r.USER_ATTR3,
437 r.USER_ATTR4,
438 r.USER_ATTR5,
439 r.USER_MEASURE1,
440 r.USER_MEASURE2,
441 r.USER_MEASURE3,
442 r.USER_MEASURE4,
443 r.USER_MEASURE5
444 FROM (SELECT readings.READING_TIME,
445 readings.EQUIPMENT_FK_KEY,
446 readings.TAG_DATA,
447 readings.USER_ATTR1,
448 readings.USER_ATTR2,
449 readings.USER_ATTR3,
450 readings.USER_ATTR4,
451 readings.USER_ATTR5,
452 readings.USER_MEASURE1,
453 readings.USER_MEASURE2,
454 readings.USER_MEASURE3,
455 readings.USER_MEASURE4,
456 readings.USER_MEASURE5,
457 readings.mth_entity
458 FROM mth_tag_readings readings
459 WHERE readings.EQUIPMENT_FK_KEY IS NOT NULL AND
460 readings.HOUR_FK_KEY IS NOT NULL AND
461 ( ( readings.PROCESSED_FLAG = 0 AND readings.LAST_UPDATE_DATE > v_log_from_date and readings.LAST_UPDATE_DATE<=v_log_to_date )
462 OR readings.reading_time IN (SELECT i.FROM_DATE
463 FROM mth_equip_statuses i
464 WHERE i.To_Date IS NULL
465 AND i.equipment_fk_key =readings.equipment_fk_key
466 )) AND
467 readings.TAG_DATA IS NOT NULL
468
469 UNION
470 SELECT err.READING_TIME,
471 err.EQUIPMENT_FK_KEY,
472 err.TAG_DATA,
473 err.USER_ATTR1,
474 err.USER_ATTR2,
475 err.USER_ATTR3,
476 err.USER_ATTR4,
477 err.USER_ATTR5,
478 err.USER_MEASURE1,
479 err.USER_MEASURE2,
480 err.USER_MEASURE3,
481 err.USER_MEASURE4,
482 err.USER_MEASURE5 ,
483 err.mth_entity
484 FROM mth_tag_readings_err err
485 WHERE err.EQUIPMENT_FK_KEY IS NOT NULL AND
486 err.HOUR_FK_KEY IS NOT NULL AND
487 ( err.PROCESSED_FLAG = 0
488 OR err.reading_time IN (SELECT i.FROM_DATE
489 FROM mth_equip_statuses i
490 WHERE i.To_Date IS NULL
491 AND i.equipment_fk_key =err.equipment_fk_key
492 )) AND
493 err.EQUIPMENT_STATUS = 'ACTIVE' AND
494 err.TAG_DATA IS NOT NULL )r,
495 MTH_ENTITIES e
496 WHERE e.mth_alias = 'Status'
497 AND e.id = r.mth_entity
498 AND r.TAG_DATA IS NOT NULL
499 ORDER BY r.equipment_fk_key, r.reading_time
500 ) r,
501 MTH_EQUIP_SHIFT_HR_V s
502 WHERE ( ( r.FROM_DATE BETWEEN s.from_date AND s.to_date )
503 OR ( s.from_date BETWEEN r.FROM_DATE AND nvl(r.To_Date,r.from_date) ) )
504 AND r.EQUIPMENT_FK_KEY = s.EQUIPMENT_FK_KEY
505 )tr
506
507 ON (o.EQUIPMENT_FK_KEY = tr.EQUIPMENT_FK_KEY
508 AND o.SHIFT_WORKDAY_FK_KEY = tr.SHIFT_WORKDAY_FK_KEY
509 AND o.HOUR_FK_KEY = tr.HOUR_FK_KEY
510 AND o.FROM_DATE = tr.FROM_DATE)
511 WHEN MATCHED THEN
512 UPDATE SET
513 o.TO_DATE = tr.TO_DATE,
514 o.STATUS = tr.STATUS,
515 o.USER_ATTR1 = tr.USER_ATTR1,
516 o.USER_ATTR2 = tr.USER_ATTR2,
517 o.USER_ATTR3 = tr.USER_ATTR3,
518 o.USER_ATTR4 = tr.USER_ATTR4,
519 o.USER_ATTR5 = tr.USER_ATTR5,
520 o.USER_MEASURE1 = tr.USER_MEASURE1,
521 o.USER_MEASURE2 = tr.USER_MEASURE2,
522 o.USER_MEASURE3 = tr.USER_MEASURE3,
523 o.USER_MEASURE4 = tr.USER_MEASURE4,
524 o.USER_MEASURE5 = tr.USER_MEASURE5,
525 o.LAST_UPDATE_DATE =SYSDATE,
526 o.LAST_UPDATE_SYSTEM_ID = v_unassigned_val
527
528 WHEN NOT MATCHED THEN
529 INSERT (
530 o.EQUIPMENT_FK_KEY,
531 o.SHIFT_WORKDAY_FK_KEY,
532 o.HOUR_FK_KEY,
533 o.FROM_DATE,
534 o.TO_DATE,
535 o.STATUS,
536 o.SYSTEM_FK_KEY,
537 o.USER_ATTR1,
538 o.USER_ATTR2,
539 o.USER_ATTR3,
540 o.USER_ATTR4,
541 o.USER_ATTR5,
542 o.USER_MEASURE1,
543 o.USER_MEASURE2,
544 o.USER_MEASURE3,
545 o.USER_MEASURE4,
546 o.USER_MEASURE5,
547 o.CREATION_DATE,
548 o.LAST_UPDATE_DATE,
549 o.CREATION_SYSTEM_ID,
550 o.LAST_UPDATE_SYSTEM_ID ,
551 o.READING_TIME
552
553 )
554 VALUES
555 (
556 tr.EQUIPMENT_FK_KEY,
557 tr.SHIFT_WORKDAY_FK_KEY,
558 tr.HOUR_FK_KEY,
559 tr.FROM_DATE,
560 tr.TO_DATE,
561 tr.STATUS,
562 v_unassigned_val,
563 tr.USER_ATTR1,
564 tr.USER_ATTR2,
565 tr.USER_ATTR3,
566 tr.USER_ATTR4,
567 tr.USER_ATTR5,
568 tr.USER_MEASURE1,
569 tr.USER_MEASURE2,
570 tr.USER_MEASURE3,
571 tr.USER_MEASURE4,
572 tr.USER_MEASURE5,
573 SYSDATE,
574 SYSDATE,
575 v_unassigned_val,
576 v_unassigned_val,
577 tr.Reading_time
578 );
579
580
581 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
582
583 --update processed flag in readings table
584
585
586 update MTH_TAG_READINGS t
587 set PROCESSED_FLAG = 1,
588 last_update_date=sysdate
589 where exists (
590 select 1
591 from mth_entities m
592 where t.MTH_ENTITY = m.ID
593 AND t.PROCESSED_FLAG = 0
594 AND t.EQUIPMENT_FK_KEY IS NOT NULL
595 AND t.HOUR_FK_KEY IS NOT NULL
596 AND t.TAG_DATA IS NOT NULL
597 AND m.MTH_ALIAS = 'Status'
598 AND t.LAST_UPDATE_DATE BETWEEN v_log_from_date and v_log_to_date);
599
600 mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
601
602
603 --update processed flag in readings error table
604 update MTH_TAG_READINGS_ERR t
605 set PROCESSED_FLAG = 1
606 where exists (
607 select 1
608 from mth_entities m
609 where t.MTH_ENTITY = m.ID
610 AND t.PROCESSED_FLAG = 0
611 AND t.EQUIPMENT_FK_KEY IS NOT NULL
612 AND t.HOUR_FK_KEY IS NOT NULL
613 AND m.MTH_ALIAS = 'Status'
614 AND t.EQUIPMENT_STATUS = 'ACTIVE'
615 AND t.TAG_DATA IS NOT NULL
616 );
617 mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
618
619 ----Call mth_run_log_post_load
620 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_STATUSES',v_unassigned_val);
621
622
623 mth_util_pkg.log_msg('INCR_STATUS_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
624 EXCEPTION
625 WHEN OTHERS THEN
626 mth_util_pkg.log_msg('Exception OTHERS in INCR_STATUS_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
627 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
628 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
629 RAISE;
630 END INCR_STATUS_FROM_READING;
631
632 /*******************************************************************************
633 * Procedure :RECAL_STATUS_FROM_READING *
634 * Description :This procedure is used for calculating the status *
635 * in RECAL mode *
636 * File Name :MTHEQSTB.PLS *
637 * Visibility :Private *
638 * Parameters : p_recal_from_date : Recalculation from date *
639 * p_recal_to_date : Recalculation to date *
640 * p_equipment_pk_key : Equipment to recalculate *
641 *******************************************************************************/
642
643 PROCEDURE RECAL_STATUS_FROM_READING(p_recal_from_date IN DATE, --Recalculation from date
644 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
645 p_equipment_pk_key IN NUMBER DEFAULT NULL --Equipment to recalculate
646 ) IS
647 v_unassigned_val VARCHAR2(30);
648 BEGIN
649 mth_util_pkg.log_msg('RECAL_STATUS_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
650 mth_util_pkg.log_msg('p_recal_from_date = ' || to_char(p_recal_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
651 mth_util_pkg.log_msg('p_recal_to_date = ' || to_char(p_recal_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
652 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
653
654 -- Initialize default parameters
655 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
656
657 --delete status table
658 DELETE FROM MTH_EQUIP_STATUSES o
659 WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)
660 AND ( ( o.FROM_DATE BETWEEN p_recal_from_date AND nvl(p_recal_to_date,o.FROM_DATE) )
661 OR ( p_recal_from_date BETWEEN o.FROM_DATE AND nvl(o.To_Date,p_recal_from_date) ) ) ;
662
663
664 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
665
666
667
668 MERGE INTO MTH_EQUIP_STATUSES o USING
669 ( SELECT r.EQUIPMENT_FK_KEY,
670 s.SHIFT_WORKDAY_FK_KEY,
671 s.HOUR_PK_KEY HOUR_FK_KEY,
672 Greatest(s.FROM_DATE,r.FROM_DATE) AS FROM_DATE,
673 Least(s.TO_DATE,r.TO_DATE) AS TO_DATE,
674 r.TAG_DATA STATUS,
675 v_unassigned_val as SYSTEM_FK_KEY,
676 r.USER_ATTR1,
677 r.USER_ATTR2,
678 r.USER_ATTR3,
679 r.USER_ATTR4,
680 r.USER_ATTR5,
681 r.USER_MEASURE1,
682 r.USER_MEASURE2,
683 r.USER_MEASURE3,
684 r.USER_MEASURE4,
685 r.USER_MEASURE5
686 FROM (SELECT r.READING_TIME FROM_DATE,
687 Lead(r.READING_TIME) over (PARTITION BY r.EQUIPMENT_FK_KEY ORDER BY r.READING_TIME) - 1/(24*60*60) AS To_Date,
688 r.EQUIPMENT_FK_KEY,
689 r.TAG_DATA,
690 r.USER_ATTR1,
691 r.USER_ATTR2,
692 r.USER_ATTR3,
693 r.USER_ATTR4,
694 r.USER_ATTR5,
695 r.USER_MEASURE1,
696 r.USER_MEASURE2,
697 r.USER_MEASURE3,
698 r.USER_MEASURE4,
699 r.USER_MEASURE5
700 FROM mth_tag_readings r,
701 MTH_ENTITIES e
702 WHERE e.mth_alias = 'Status'
703 AND e.id = r.mth_entity
704 AND r.EQUIPMENT_FK_KEY IS NOT NULL
705 AND r.HOUR_FK_KEY IS NOT NULL
706 AND r.TAG_DATA IS NOT NULL
707 AND r.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key, r.EQUIPMENT_FK_KEY)
708 AND ( ( r.READING_TIME BETWEEN p_recal_from_date AND p_recal_to_date
709
710 OR r.READING_TIME IN ((SELECT Max(i.READING_TIME)
711 FROM mth_tag_readings i , MTH_ENTITIES e
712 WHERE e.mth_alias = 'Status'
713 AND e.id = i.mth_entity
714 AND i.READING_TIME < p_recal_from_date
715 AND i.equipment_fk_key = r.equipment_fk_key)
716 , (SELECT Min(i.READING_TIME)
717 FROM mth_tag_readings i, MTH_ENTITIES e
718 WHERE e.mth_alias = 'Status'
719 AND e.id = i.mth_entity
720 AND i.READING_TIME > p_recal_to_date
721 AND i.equipment_fk_key = r.equipment_fk_key ) ) )
722 )
723 ORDER BY r.equipment_fk_key, r.reading_time
724 ) r,
725 MTH_EQUIP_SHIFT_HR_V s
726 WHERE ( ( r.FROM_DATE BETWEEN s.from_date AND s.to_date )
727 OR ( s.from_date BETWEEN r.FROM_DATE AND nvl(r.To_Date,r.from_date) ) )
728 AND r.EQUIPMENT_FK_KEY = s.EQUIPMENT_FK_KEY
729 )tr
730
731 ON (o.EQUIPMENT_FK_KEY = tr.EQUIPMENT_FK_KEY
732 AND o.SHIFT_WORKDAY_FK_KEY = tr.SHIFT_WORKDAY_FK_KEY
733 AND o.HOUR_FK_KEY = tr.HOUR_FK_KEY
734 AND o.FROM_DATE = tr.FROM_DATE)
735 WHEN MATCHED THEN
736 UPDATE SET
737
738 o.STATUS = tr.STATUS,
739 o.LAST_UPDATE_DATE =SYSDATE,
740 o.LAST_UPDATE_SYSTEM_ID = v_unassigned_val
741 WHEN NOT MATCHED THEN
742 INSERT (
743 o.EQUIPMENT_FK_KEY,
744 o.SHIFT_WORKDAY_FK_KEY,
745 o.HOUR_FK_KEY,
746 o.FROM_DATE,
747 o.TO_DATE,
748 o.STATUS,
749 o.SYSTEM_FK_KEY,
750 o.USER_ATTR1,
751 o.USER_ATTR2,
752 o.USER_ATTR3,
753 o.USER_ATTR4,
754 o.USER_ATTR5,
755 o.USER_MEASURE1,
756 o.USER_MEASURE2,
757 o.USER_MEASURE3,
758 o.USER_MEASURE4,
759 o.USER_MEASURE5,
760 o.CREATION_DATE,
761 o.LAST_UPDATE_DATE,
762 o.CREATION_SYSTEM_ID,
763 o.LAST_UPDATE_SYSTEM_ID
764
765 )
766 VALUES
767 (
768 tr.EQUIPMENT_FK_KEY,
769 tr.SHIFT_WORKDAY_FK_KEY,
770 tr.HOUR_FK_KEY,
771 tr.FROM_DATE,
772 tr.TO_DATE,
773 tr.STATUS,
774 v_unassigned_val,
775 tr.USER_ATTR1,
776 tr.USER_ATTR2,
777 tr.USER_ATTR3,
778 tr.USER_ATTR4,
779 tr.USER_ATTR5,
780 tr.USER_MEASURE1,
781 tr.USER_MEASURE2,
782 tr.USER_MEASURE3,
783 tr.USER_MEASURE4,
784 tr.USER_MEASURE5,
785 SYSDATE,
786 SYSDATE,
787 v_unassigned_val,
788 v_unassigned_val
789 );
790
791 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
792
793 --update processed flag in readings table
794 update MTH_TAG_READINGS t
795 set PROCESSED_FLAG = 1,
796 last_update_date=sysdate
797 where exists (
798 select 1
799 from mth_entities m
800 where t.MTH_ENTITY = m.ID
801 AND t.PROCESSED_FLAG = 0
802 AND t.EQUIPMENT_FK_KEY IS NOT NULL
803 AND t.HOUR_FK_KEY IS NOT NULL
804 AND t.TAG_DATA IS NOT NULL
805 AND m.MTH_ALIAS = 'Status'
806 AND t.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,t.READING_TIME));
807
808 mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
809
810
811 mth_util_pkg.log_msg('RECAL_STATUS_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
812 EXCEPTION
813 WHEN OTHERS THEN
814 mth_util_pkg.log_msg('Exception OTHERS in RECAL_STATUS_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
815 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
816 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
817 RAISE;
818 END RECAL_STATUS_FROM_READING;
819
820
821 /*******************************************************************************
822 * Procedure :INIT_STATUS_FROM_CSV *
823 * Description :This procedure is used for calculating the status *
824 * in INIT mode from CSV *
825 * File Name :MTHEQSTB.PLS *
826 * Visibility :Private *
827 * Parameters : *
828 *******************************************************************************/
829 PROCEDURE INIT_STATUS_FROM_CSV IS
830 v_log_date DATE;
831 v_unassigned_val VARCHAR2(30);
832 v_processing_flag NUMBER;
833 BEGIN
834 mth_util_pkg.log_msg('INIT_STATUS_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
835
836 -- Initialize default parameters
837 v_log_date := sysdate;
838 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
839
840 --Ensuring the functionality of ES map is introduced before the staging to fact is executed.
841 INSERT INTO mth_equip_statuses_stg(equipment_fk,
842 shift_workday_fk,
843 from_date,
844 To_Date,
845 status,
846 system_fk,
847 user_dim1_fk,
848 user_dim2_fk,
849 user_dim3_fk,
850 user_dim4_fk,
851 user_dim5_fk,
852 user_attr1,
853 user_attr2,
854 user_attr3,
855 user_attr4,
856 user_attr5,
857 user_measure1,
858 user_measure2,
859 user_measure3,
860 user_measure4,
861 user_measure5,
862 downtime_reason_code)
863 (SELECT equipment_fk,
864 shift_workday_fk,
865 from_date,
866 To_Date,
867 status,
868 system_fk,
869 user_dim1_fk,
870 user_dim2_fk,
871 user_dim3_fk,
872 user_dim4_fk,
873 user_dim5_fk,
874 user_attr1,
875 user_attr2,
876 user_attr3,
877 user_attr4,
878 user_attr5,
879 user_measure1,
880 user_measure2,
881 user_measure3,
882 user_measure4,
883 user_measure5,
884 downtime_reason_code
885 FROM mth_equip_statuses_err
886 WHERE reprocess_ready_yn = 'Y');
887 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
888
889 --delete status table
890 DELETE FROM MTH_EQUIP_STATUSES_ERR
891 WHERE REPROCESS_READY_YN = 'Y';
892 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
893
894
895 mth_util_pkg.switch_column_default_value('MTH_EQUIP_STATUSES_STG',v_processing_flag);
896
897 --delete status table
898 DELETE FROM MTH_EQUIP_STATUSES;
899 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
900
901
902 --Execute all validations on csv records
903 -- Validation for Duplicate record
904 UPDATE mth_equip_statuses_stg stg
905 SET stg.err_code = stg.err_code || 'DUP '
906 WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
907 FROM mth_equip_statuses_stg
908 GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
909 WHERE dup.cnt>1
910 AND dup.equipment_fk = stg.equipment_fk
911 AND dup.shift_workday_fk = stg.shift_workday_fk
912 AND dup.from_date = stg.from_date
913 AND dup.To_Date = stg.To_Date
914 AND stg.processing_flag = v_processing_flag);
915 mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
916
917 -- Validation for invalid equipment
918 UPDATE mth_equip_statuses_stg stg
919 SET stg.err_code = stg.err_code || 'EQP '
920 WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
921 FROM mth_equipments_d med,
922 mth_equip_statuses_stg stg
923 WHERE med.equipment_pk = stg.equipment_fk) eqp
924 WHERE eqp.equipment_pk = stg.equipment_fk
925 AND stg.processing_flag = v_processing_flag);
926 mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
927
928 -- Validation for Inactive equipment
929 UPDATE mth_equip_statuses_stg stg
930 SET stg.err_code = stg.err_code || 'IEQ '
931 WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
932 FROM mth_equipments_d med,
933 mth_equip_statuses_stg stg
934 WHERE med.equipment_pk = stg.equipment_fk
935 AND med.status <> 'ACTIVE') eqp
936 WHERE eqp.equipment_pk = stg.equipment_fk
937 AND stg.processing_flag = v_processing_flag);
938 mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
939
940 -- Validation for invalid shift
941 UPDATE mth_equip_statuses_stg stg
942 SET stg.err_code = stg.err_code || 'WDS '
943 WHERE stg.shift_workday_fk IS NOT NULL
944 AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
945 FROM mth_workday_shifts_d mds,
946 mth_equip_statuses_stg stg
947 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
948 AND stg.shift_workday_fk IS NOT NULL) wds
949 WHERE wds.shift_workday_pk = stg.shift_workday_fk
950 AND stg.processing_flag = v_processing_flag);
951 mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
952
953 -- Validation for invalid equipment shift combination
954 UPDATE mth_equip_statuses_stg stg
955 SET stg.err_code = stg.err_code || 'ESD '
956 WHERE NOT EXISTS ( SELECT * FROM
957 ( SELECT mee.equipment_pk, mws.shift_workday_pk
958 FROM mth_equipments_d mee,
959 mth_equipment_shifts_d med,
960 mth_workday_shifts_d mws
961 WHERE mee.equipment_pk_key = med.equipment_fk_key
962 AND med.shift_workday_fk_key = mws.shift_workday_pk_key
963 AND med.entity_type = 'EQUIPMENT') esd
964 WHERE stg.equipment_fk = esd.equipment_pk
965 AND stg.shift_workday_fk = esd.shift_workday_pk
966 AND stg.processing_flag = v_processing_flag)
967 AND EXISTS ( SELECT * FROM
968 ( SELECT mds.shift_workday_pk
969 FROM mth_workday_shifts_d mds,
970 mth_equip_statuses_stg stg
971 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
972 AND stg.shift_workday_fk IS NOT NULL) wds
973 WHERE wds.shift_workday_pk = stg.shift_workday_fk
974 AND stg.processing_flag = v_processing_flag)
975 AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
976 FROM mth_equipments_d med,
977 mth_equip_statuses_stg stg
978 WHERE med.equipment_pk = stg.equipment_fk) eqp
979 WHERE eqp.equipment_pk = stg.equipment_fk
980 AND stg.processing_flag = v_processing_flag);
981 mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
982
983 -- Validation for user dimension 1
984 UPDATE mth_equip_statuses_stg stg
985 SET stg.err_code = stg.err_code || 'UD1 '
986 WHERE stg.user_dim1_fk IS NOT NULL
987 AND EXISTS (SELECT *
988 FROM
989 (SELECT mue.entity_pk, stg.user_dim1_fk
990 FROM mth_user_dim_entities_mst mue,
991 mth_equip_statuses_stg stg
992 WHERE stg.user_dim1_fk = mue.entity_pk (+)
993 AND stg.user_dim1_fk IS NOT NULL) ud1
994 WHERE ud1.user_dim1_fk = stg.user_dim1_fk
995 AND stg.processing_flag = v_processing_flag
996 AND ud1.entity_pk IS NULL);
997 mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
998
999 -- Validation for user dimension 2
1000 UPDATE mth_equip_statuses_stg stg
1001 SET stg.err_code = stg.err_code || 'UD2 '
1002 WHERE stg.user_dim2_fk IS NOT NULL
1003 AND EXISTS (SELECT *
1004 FROM
1005 (SELECT mue.entity_pk, stg.user_dim2_fk
1006 FROM mth_user_dim_entities_mst mue,
1007 mth_equip_statuses_stg stg
1008 WHERE stg.user_dim2_fk = mue.entity_pk (+)
1009 AND stg.user_dim2_fk IS NOT NULL) ud2
1010 WHERE ud2.user_dim2_fk = stg.user_dim2_fk
1011 AND stg.processing_flag = v_processing_flag
1012 AND ud2.entity_pk IS NULL);
1013 mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1014
1015 -- Validation for user dimension 3
1016 UPDATE mth_equip_statuses_stg stg
1017 SET stg.err_code = stg.err_code || 'UD3 '
1018 WHERE stg.user_dim3_fk IS NOT NULL
1019 AND EXISTS (SELECT *
1020 FROM
1021 (SELECT mue.entity_pk, stg.user_dim3_fk
1022 FROM mth_user_dim_entities_mst mue,
1023 mth_equip_statuses_stg stg
1024 WHERE stg.user_dim3_fk = mue.entity_pk (+)
1025 AND stg.user_dim3_fk IS NOT NULL) ud3
1026 WHERE ud3.user_dim3_fk = stg.user_dim3_fk
1027 AND stg.processing_flag = v_processing_flag
1028 AND ud3.entity_pk IS NULL);
1029 mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1030
1031 -- Validation for user dimension 4
1032 UPDATE mth_equip_statuses_stg stg
1033 SET stg.err_code = stg.err_code || 'UD4 '
1034 WHERE stg.user_dim4_fk IS NOT NULL
1035 AND EXISTS (SELECT *
1036 FROM
1037 (SELECT mue.entity_pk, stg.user_dim4_fk
1038 FROM mth_user_dim_entities_mst mue,
1039 mth_equip_statuses_stg stg
1040 WHERE stg.user_dim4_fk = mue.entity_pk (+)
1041 AND stg.user_dim4_fk IS NOT NULL) ud4
1042 WHERE ud4.user_dim4_fk = stg.user_dim4_fk
1043 AND stg.processing_flag = v_processing_flag
1044 AND ud4.entity_pk IS NULL);
1045 mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1046
1047 -- Validation for user dimension 5
1048 UPDATE mth_equip_statuses_stg stg
1049 SET stg.err_code = stg.err_code || 'UD5 '
1050 WHERE stg.user_dim5_fk IS NOT NULL
1051 AND EXISTS (SELECT *
1052 FROM
1053 (SELECT mue.entity_pk, stg.user_dim5_fk
1054 FROM mth_user_dim_entities_mst mue,
1055 mth_equip_statuses_stg stg
1056 WHERE stg.user_dim5_fk = mue.entity_pk (+)
1057 AND stg.user_dim5_fk IS NOT NULL) ud5
1058 WHERE ud5.user_dim5_fk = stg.user_dim5_fk
1059 AND stg.processing_flag = v_processing_flag
1060 AND ud5.entity_pk IS NULL);
1061 mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1062
1063 -- Validation for 'GAP' error code
1064 UPDATE mth_equip_statuses_stg stg
1065 SET stg.err_code = stg.err_code || 'GAP '
1066 WHERE EXISTS (SELECT *
1067 FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
1068 over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
1069 FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
1070 (Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
1071 FROM mth_equip_statuses_stg a WHERE a.processing_flag = v_processing_flag) b) c
1072 WHERE c.Count >= 1
1073 AND stg.from_date = c.from_date
1074 AND stg.equipment_fk = c.equipment_fk
1075 AND stg.processing_flag = v_processing_flag);
1076 mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1077
1078 -- Validation for FTD
1079 UPDATE mth_equip_statuses_stg stg
1080 SET stg.err_code = stg.err_code || 'FTD '
1081 WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE
1082 AND stg.processing_flag = v_processing_flag;
1083 mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1084
1085 -- Validation for DTR
1086 UPDATE mth_equip_statuses_stg stg
1087 SET stg.err_code = stg.err_code || 'DTR '
1088 WHERE EXISTS (SELECT *
1089 FROM
1090 (SELECT flk.lookup_code,
1091 flk.lookup_type,
1092 stg.FROM_date,
1093 stg.to_date,
1094 stg.downtime_reason_code,
1095 stg.status
1096 FROM fnd_lookups flk,
1097 mth_equip_statuses_stg stg
1098 WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
1099 AND stg.downtime_reason_code = flk.lookup_code (+)
1100 AND stg.status = 3) dtr
1101 WHERE ((stg.downtime_reason_code = dtr.downtime_reason_code
1102 AND stg.status = dtr.status
1103 AND dtr.lookup_code IS NULL
1104 AND dtr.from_date = stg.from_date
1105 AND dtr.To_Date = stg.To_Date)
1106 OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL))
1107 AND stg.processing_flag = v_processing_flag);
1108 mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1109
1110 -- Validation for IDR
1111 UPDATE mth_equip_statuses_stg stg
1112 SET stg.err_code = stg.err_code || 'IDR '
1113 WHERE EXISTS (SELECT *
1114 FROM
1115 ( SELECT flk.lookup_code,
1116 flk.lookup_type,
1117 stg.FROM_date,
1118 stg.to_date,
1119 stg.downtime_reason_code,
1120 stg.status
1121 FROM fnd_lookups flk,
1122 mth_equip_statuses_stg stg
1123 WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
1124 AND stg.downtime_reason_code = flk.lookup_code (+)
1125 AND stg.status = 2) dtr
1126 WHERE stg.downtime_reason_code = dtr.downtime_reason_code
1127 AND stg.status = dtr.status
1128 AND dtr.lookup_code IS NULL
1129 AND dtr.from_date = stg.from_date
1130 AND dtr.To_Date = stg.To_Date
1131 AND stg.processing_flag = v_processing_flag);
1132 mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1133
1134 -- Validation for ITR
1135 UPDATE mth_equip_statuses_stg stg
1136 SET stg.err_code = stg.err_code || 'ITR '
1137 WHERE EXISTS ( SELECT * FROM (
1138 SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1139 FROM mth_workday_shifts_d mds,
1140 mth_equip_statuses_stg stg,
1141 mth_equipment_shifts_d mes,
1142 mth_equipments_d med
1143 WHERE stg.shift_workday_fk = mds.shift_workday_pk
1144 AND stg.equipment_fk = med.equipment_pk
1145 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
1146 AND med.equipment_pk_key = mes.equipment_fk_key
1147 AND (stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
1148 AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date)
1149 AND Upper(mes.entity_type) = 'EQUIPMENT'
1150 AND stg.processing_flag = v_processing_flag) itr
1151 WHERE itr.shift_workday_pk = stg.shift_workday_fk
1152 AND itr.equipment_pk = stg.equipment_fk
1153 AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
1154 AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date)
1155 AND stg.processing_flag = v_processing_flag);
1156 mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1157
1158 -- Validation for MSF
1159 UPDATE mth_equip_statuses_stg stg
1160 SET stg.err_code = stg.err_code || 'MSF '
1161 WHERE EXISTS ( SELECT * FROM (
1162 SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1163 FROM mth_workday_shifts_d mds,
1164 mth_equip_statuses_stg stg,
1165 mth_equipment_shifts_d mes,
1166 mth_equipments_d med
1167 WHERE stg.shift_workday_fk = mds.shift_workday_pk
1168 AND stg.equipment_fk = med.equipment_pk
1169 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
1170 AND med.equipment_pk_key = mes.equipment_fk_key
1171 AND (((stg.from_date BETWEEN mes.from_date AND mes.To_Date)
1172 AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date))
1173 OR ((stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
1174 AND (stg.to_date BETWEEN mes.from_date AND mes.To_Date)))
1175 AND Upper(mes.entity_type) = 'EQUIPMENT'
1176 AND stg.processing_flag = v_processing_flag) msf
1177 WHERE msf.shift_workday_pk = stg.shift_workday_fk
1178 AND msf.equipment_pk = stg.equipment_fk
1179 AND (((stg.from_date BETWEEN msf.from_date AND msf.To_Date)
1180 AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
1181 OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
1182 AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date)))
1183 AND stg.processing_flag = v_processing_flag);
1184 mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1185
1186 --Validation for OCSV
1187 UPDATE mth_equip_statuses_stg stag
1188 SET stag.err_code = stag.err_code || 'OCSV '
1189 WHERE EXISTS (SELECT *
1190 FROM
1191 (SELECT CASE
1192 WHEN (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
1193 AND (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
1194 AND ((stg.from_date >= (LAG(stg.from_date) OVER ( PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
1195 AND
1196 stg.from_date <= (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)))
1197 OR
1198 (stg.to_date >= (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
1199 AND
1200 stg.to_date <= (LAG(stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date )))
1201 )
1202 THEN 1 END overlap,
1203 med.equipment_pk,
1204 stg.from_date,
1205 stg.to_date
1206 FROM mth_equip_statuses_stg stg,
1207 mth_equipments_d med
1208 WHERE stg.equipment_fk = med.equipment_pk
1209 AND stg.processing_flag = v_processing_flag) ovp
1210 WHERE ovp.overlap = 1
1211 AND stag.equipment_fk = ovp.equipment_pk
1212 AND stag.from_date = ovp.from_date
1213 AND stag.To_Date = ovp.To_Date
1214 AND stag.processing_flag = v_processing_flag);
1215 mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1216
1217 --validation for OSTS
1218 UPDATE mth_equip_statuses_stg stag
1219 SET stag.err_code = stag.err_code || 'OSTS '
1220 WHERE EXISTS (SELECT *
1221 FROM ( SELECT CASE
1222 WHEN (stg.FROM_DATE >= sts.FROM_DATE
1223 AND stg.FROM_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
1224 OR (stg.TO_DATE >= sts.FROM_DATE
1225 AND stg.TO_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
1226 OR (stg.FROM_DATE < sts.FROM_DATE
1227 AND stg.TO_DATE > NVL(sts.TO_DATE,stg.TO_DATE+1/86400))
1228 THEN 1 END overlap ,
1229 med.equipment_pk,
1230 wds.shift_workday_pk,
1231 stg.from_date,
1232 stg.to_date
1233 FROM mth_equip_statuses_stg stg,
1234 mth_equip_statuses sts,
1235 mth_equipments_d med,
1236 mth_workday_shifts_d wds
1237 WHERE stg.equipment_fk = med.equipment_pk
1238 AND stg.shift_workday_fk = wds.shift_workday_pk
1239 AND med.equipment_pk_key = sts.equipment_fk_key
1240 AND wds.shift_workday_pk_key = sts.shift_workday_fk_key
1241 AND stg.processing_flag = v_processing_flag) osts
1242 WHERE osts.overlap = 1
1243 AND stag.equipment_fk = osts.equipment_pk
1244 AND stag.shift_workday_fk = osts.shift_workday_pk
1245 AND stag.processing_flag = v_processing_flag
1246 AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
1247 mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1248
1249 --validation for WRC
1250 UPDATE mth_equip_statuses_stg stg
1251 SET stg.err_code = stg.err_code ||'WRC '
1252 WHERE NOT EXISTS ( SELECT *
1253 FROM (SELECT stg.*
1254 FROM mth_equip_statuses_stg stg,
1255 MTH_EQUIPMENT_REASON_SETUP mer,
1256 mth_equipments_d med
1257 WHERE stg.equipment_fk = med.equipment_pk
1258 AND med.equipment_pk_key = mer.equipment_fk_key
1259 AND stg.downtime_reason_code = mer.reason_code
1260 AND stg.status IN (3,2)
1261 AND stg.downtime_reason_code IS NOT NULL) ers
1262 WHERE ers.equipment_fk = stg.equipment_fk
1263 AND ers.downtime_reason_code = stg.downtime_reason_code
1264 AND ers.from_date = stg.from_date
1265 AND Nvl(ers.To_Date,SYSDATE) = Nvl(stg.To_Date,SYSDATE)
1266 AND ers.shift_workday_fk = stg.shift_workday_fk
1267 AND stg.processing_flag = v_processing_flag
1268 )
1269 AND stg.status IN (3,2)
1270 AND stg.downtime_reason_code IS NOT NULL;
1271 mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1272
1273 --validation for STS
1274 UPDATE mth_equip_statuses_stg stg
1275 SET stg.err_code = stg.err_code ||'STS '
1276 WHERE stg.processing_flag = v_processing_flag
1277 AND stg.status NOT IN (1,2,3,4);
1278 mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1279
1280 --Insert records into mth_equip_statuses_err
1281 INSERT INTO mth_equip_statuses_err(equipment_fk,
1282 shift_workday_fk,
1283 from_date,
1284 To_Date,
1285 status,
1286 system_fk,
1287 reprocess_ready_yn,
1288 user_dim1_fk,
1289 user_dim2_fk,
1290 user_dim3_fk,
1291 user_dim4_fk,
1292 user_dim5_fk,
1293 user_attr1,
1294 user_attr2,
1295 user_attr3,
1296 user_attr4,
1297 user_attr5,
1298 user_measure1,
1299 user_measure2,
1300 user_measure3,
1301 user_measure4,
1302 user_measure5,
1303 err_code,
1304 downtime_reason_code)
1305 (SELECT equipment_fk,
1306 shift_workday_fk,
1307 from_date,
1308 To_Date,
1309 status,
1310 system_fk,
1311 'N',
1312 user_dim1_fk,
1313 user_dim2_fk,
1314 user_dim3_fk,
1315 user_dim4_fk,
1316 user_dim5_fk,
1317 user_attr1,
1318 user_attr2,
1319 user_attr3,
1320 user_attr4,
1321 user_attr5,
1322 user_measure1,
1323 user_measure2,
1324 user_measure3,
1325 user_measure4,
1326 user_measure5,
1327 err_code,
1328 downtime_reason_code
1329 FROM mth_equip_statuses_stg
1330 WHERE err_code IS NOT NULL
1331 AND processing_flag = v_processing_flag);
1332 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1333
1334 --Insert records into mth_equip_statuses table
1335 INSERT INTO mth_equip_statuses( equipment_fk_key,
1336 shift_workday_fk_key,
1337 from_date,
1338 To_Date,
1339 status,
1340 system_fk_key,
1341 user_dim1_fk_key,
1342 user_dim2_fk_key,
1343 user_dim3_fk_key,
1344 user_dim4_fk_key,
1345 user_dim5_fk_key,
1346 user_attr1 ,
1347 user_attr2 ,
1348 user_attr3 ,
1349 user_attr4 ,
1350 user_attr5 ,
1351 user_measure1 ,
1352 user_measure2 ,
1353 user_measure3 ,
1354 user_measure4 ,
1355 user_measure5 ,
1356 creation_date,
1357 last_update_date,
1358 creation_system_id,
1359 last_update_system_id,
1360 created_by,
1361 last_updated_by,
1362 last_update_login,
1363 expected_up_time,
1364 status_type,
1365 hour_fk_key,
1366 reading_time )
1367 (SELECT med.EQUIPMENT_PK_KEY ,
1368 wds.SHIFT_WORKDAY_PK_KEY ,
1369 Greatest(stg.from_date,mhd.from_time),
1370 Least(stg.To_Date,mhd.to_time) ,
1371 stg.STATUS ,
1372 Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) ,
1373 mue1.ENTITY_PK_KEY ,
1374 mue2.ENTITY_PK_KEY ,
1375 mue3.ENTITY_PK_KEY ,
1376 mue4.ENTITY_PK_KEY ,
1377 mue5.ENTITY_PK_KEY ,
1378 stg.USER_ATTR1 ,
1379 stg.USER_ATTR2 ,
1380 stg.USER_ATTR3 ,
1381 stg.USER_ATTR4 ,
1382 stg.USER_ATTR5 ,
1383 stg.USER_MEASURE1 ,
1384 stg.USER_MEASURE2 ,
1385 stg.USER_MEASURE3 ,
1386 stg.USER_MEASURE4 ,
1387 stg.USER_MEASURE5 ,
1388 v_log_date,
1389 v_log_date,
1390 v_unassigned_val,
1391 v_unassigned_val,
1392 null,
1393 null,
1394 null,
1395 null,
1396 null,
1397 mhd.HOUR_PK_KEY,
1398 stg.from_date
1399 FROM mth_equip_statuses_stg stg,
1400 mth_equipments_d med,
1401 mth_workday_shifts_d wds,
1402 mth_systems_setup mss,
1403 mth_user_dim_entities_mst mue1,
1404 mth_user_dim_entities_mst mue2,
1405 mth_user_dim_entities_mst mue3,
1406 mth_user_dim_entities_mst mue4,
1407 mth_user_dim_entities_mst mue5,
1408 fnd_lookups lkp,
1409 mth_hour_d mhd
1410 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
1411 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
1412 AND mhd.to_time >= stg.from_date
1413 AND mhd.from_time <= stg.to_date
1414 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
1415 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
1416 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
1417 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
1418 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
1419 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
1420 AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
1421 AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+)
1422 AND stg.err_code IS NULL
1423 AND stg.processing_flag = v_processing_flag
1424 UNION ALL
1425 SELECT med.EQUIPMENT_PK_KEY ,
1426 wds.SHIFT_WORKDAY_PK_KEY ,
1427 Greatest(stg.from_date,mhd.from_time),
1428 stg.To_Date,
1429 stg.STATUS ,
1430 Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) ,
1431 mue1.ENTITY_PK_KEY ,
1432 mue2.ENTITY_PK_KEY ,
1433 mue3.ENTITY_PK_KEY ,
1434 mue4.ENTITY_PK_KEY ,
1435 mue5.ENTITY_PK_KEY ,
1436 stg.USER_ATTR1 ,
1437 stg.USER_ATTR2 ,
1438 stg.USER_ATTR3 ,
1439 stg.USER_ATTR4 ,
1440 stg.USER_ATTR5 ,
1441 stg.USER_MEASURE1 ,
1442 stg.USER_MEASURE2 ,
1443 stg.USER_MEASURE3 ,
1444 stg.USER_MEASURE4 ,
1445 stg.USER_MEASURE5 ,
1446 v_log_date,
1447 v_log_date,
1448 v_unassigned_val,
1449 v_unassigned_val,
1450 null,
1451 null,
1452 null,
1453 null,
1454 null,
1455 mhd.HOUR_PK_KEY,
1456 stg.from_date
1457 FROM mth_equip_statuses_stg stg,
1458 mth_equipments_d med,
1459 mth_workday_shifts_d wds,
1460 mth_systems_setup mss,
1461 mth_user_dim_entities_mst mue1,
1462 mth_user_dim_entities_mst mue2,
1463 mth_user_dim_entities_mst mue3,
1464 mth_user_dim_entities_mst mue4,
1465 mth_user_dim_entities_mst mue5,
1466 fnd_lookups lkp,
1467 mth_hour_d mhd
1468 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
1469 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
1470 AND stg.from_date BETWEEN mhd.from_time AND mhd.to_time
1471 AND stg.To_Date IS NULL
1472 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
1473 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
1474 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
1475 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
1476 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
1477 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
1478 AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
1479 AND stg.processing_flag = v_processing_flag
1480 AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+) AND stg.err_code IS NULL );
1481 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1482
1483 --Insertion of records in tag reason readings
1484 INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
1485 EQUIPMENT_FK_KEY,
1486 FROM_DATE,
1487 To_Date,
1488 REASON_CODE,
1489 CREATION_DATE,
1490 LAST_UPDATE_DATE,
1491 CREATION_SYSTEM_ID,
1492 LAST_UPDATE_SYSTEM_ID,
1493 CREATED_BY,
1494 LAST_UPDATE_LOGIN,
1495 LAST_UPDATED_BY,
1496 reading_time,
1497 hour_fk_key)
1498 (SELECT 1 reason_type,
1499 sts.equipment_fk_key,
1500 sts.from_date,
1501 sts.To_Date,
1502 stg.downtime_reason_code,
1503 v_log_date,
1504 v_log_date,
1505 v_unassigned_val,
1506 v_unassigned_val,
1507 NULL,
1508 NULL,
1509 null,
1510 sts.reading_time,
1511 sts.hour_fk_key
1512 FROM mth_equip_statuses_stg stg,
1513 mth_equip_statuses sts
1514 WHERE stg.from_date = sts.reading_time
1515 AND sts.status = stg.status
1516 AND stg.status = 3
1517 AND stg.processing_flag = v_processing_flag
1518 AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
1519 FROM mth_equipments_d
1520 WHERE equipment_pk = stg.equipment_fk )
1521 AND stg.err_code IS NULL
1522 UNION
1523 SELECT 3 reason_type,
1524 sts.equipment_fk_key,
1525 sts.from_date,
1526 sts.To_Date,
1527 stg.downtime_reason_code,
1528 v_log_date,
1529 v_log_date,
1530 v_unassigned_val,
1531 v_unassigned_val,
1532 NULL,
1533 NULL,
1534 null,
1535 sts.reading_time,
1536 sts.hour_fk_key
1537 FROM mth_equip_statuses_stg stg,
1538 mth_equip_statuses sts
1539 WHERE stg.from_date = sts.reading_time
1540 AND sts.status = stg.status
1541 AND stg.status = 2
1542 AND stg.processing_flag = v_processing_flag
1543 AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
1544 FROM mth_equipments_d
1545 WHERE equipment_pk = stg.equipment_fk )
1546 AND stg.err_code IS NULL
1547 );
1548
1549 mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1550
1551 mth_util_pkg.truncate_table_partition('MTH_EQUIP_STATUSES_STG',v_processing_flag);
1552
1553 mth_util_pkg.log_msg('INIT_STATUS_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 --Call logging API and then throw exception
1557 mth_util_pkg.log_msg('Exception OTHERS in INIT_STATUS_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1558 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1559 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1560 RAISE;
1561 END;
1562
1563 /*******************************************************************************
1564 * Procedure :INCR_STATUS_FROM_CSV *
1565 * Description :This procedure is used for calculating the status *
1566 * in INCR mode from CSV *
1567 * File Name :MTHEQSTB.PLS *
1568 * Visibility :Private *
1569 * Parameters : *
1570 *******************************************************************************/
1571 PROCEDURE INCR_STATUS_FROM_CSV IS
1572 v_log_date DATE;
1573 v_unassigned_val VARCHAR2(30);
1574 v_processing_flag NUMBER;
1575 BEGIN
1576 mth_util_pkg.log_msg('INCR_STATUS_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
1577
1578 -- Initialize default parameters
1579 v_log_date := sysdate;
1580 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1581
1582 --Ensuring the functionality of ES map is introduced before the staging to fact is executed.
1583 INSERT INTO mth_equip_statuses_stg(equipment_fk,
1584 shift_workday_fk,
1585 from_date,
1586 To_Date,
1587 status,
1588 system_fk,
1589 user_dim1_fk,
1590 user_dim2_fk,
1591 user_dim3_fk,
1592 user_dim4_fk,
1593 user_dim5_fk,
1594 user_attr1,
1595 user_attr2,
1596 user_attr3,
1597 user_attr4,
1598 user_attr5,
1599 user_measure1,
1600 user_measure2,
1601 user_measure3,
1602 user_measure4,
1603 user_measure5,
1604 downtime_reason_code,
1605 processing_flag)
1606 (SELECT equipment_fk,
1607 shift_workday_fk,
1608 from_date,
1609 To_Date,
1610 status,
1611 system_fk,
1612 user_dim1_fk,
1613 user_dim2_fk,
1614 user_dim3_fk,
1615 user_dim4_fk,
1616 user_dim5_fk,
1617 user_attr1,
1618 user_attr2,
1619 user_attr3,
1620 user_attr4,
1621 user_attr5,
1622 user_measure1,
1623 user_measure2,
1624 user_measure3,
1625 user_measure4,
1626 user_measure5,
1627 downtime_reason_code,
1628 v_processing_flag
1629 FROM mth_equip_statuses_err
1630 WHERE reprocess_ready_yn = 'Y');
1631 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1632
1633 --delete status table
1634 DELETE FROM MTH_EQUIP_STATUSES_ERR
1635 WHERE REPROCESS_READY_YN = 'Y';
1636 mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1637
1638
1639 mth_util_pkg.switch_column_default_value('MTH_EQUIP_STATUSES_STG',v_processing_flag);
1640
1641
1642 --Execute all validations on csv records
1643 -- Validation for Duplicate record
1644 UPDATE mth_equip_statuses_stg stg
1645 SET stg.err_code = stg.err_code || 'DUP '
1646 WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
1647 FROM mth_equip_statuses_stg
1648 GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
1649 WHERE dup.cnt>1
1650 AND dup.equipment_fk = stg.equipment_fk
1651 AND dup.shift_workday_fk = stg.shift_workday_fk
1652 AND dup.from_date = stg.from_date
1653 AND dup.To_Date = stg.To_Date
1654 AND stg.processing_flag = v_processing_flag);
1655 mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1656
1657 -- Validation for invalid equipment
1658 UPDATE mth_equip_statuses_stg stg
1659 SET stg.err_code = stg.err_code || 'EQP '
1660 WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1661 FROM mth_equipments_d med,
1662 mth_equip_statuses_stg stg
1663 WHERE med.equipment_pk = stg.equipment_fk) eqp
1664 WHERE eqp.equipment_pk = stg.equipment_fk
1665 AND stg.processing_flag = v_processing_flag);
1666 mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1667
1668 -- Validation for Inactive equipment
1669 UPDATE mth_equip_statuses_stg stg
1670 SET stg.err_code = stg.err_code || 'IEQ '
1671 WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1672 FROM mth_equipments_d med,
1673 mth_equip_statuses_stg stg
1674 WHERE med.equipment_pk = stg.equipment_fk
1675 AND med.status <> 'ACTIVE') eqp
1676 WHERE eqp.equipment_pk = stg.equipment_fk
1677 AND stg.processing_flag = v_processing_flag);
1678 mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1679
1680 -- Validation for invalid shift
1681 UPDATE mth_equip_statuses_stg stg
1682 SET stg.err_code = stg.err_code || 'WDS '
1683 WHERE stg.shift_workday_fk IS NOT NULL
1684 AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1685 FROM mth_workday_shifts_d mds,
1686 mth_equip_statuses_stg stg
1687 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1688 AND stg.shift_workday_fk IS NOT NULL) wds
1689 WHERE wds.shift_workday_pk = stg.shift_workday_fk
1690 AND stg.processing_flag = v_processing_flag);
1691 mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1692
1693 -- Valildation for invalid equipment shift combination
1694 UPDATE mth_equip_statuses_stg stg
1695 SET stg.err_code = stg.err_code || 'ESD '
1696 WHERE NOT EXISTS ( SELECT * FROM
1697 ( SELECT mee.equipment_pk, mws.shift_workday_pk
1698 FROM mth_equipments_d mee,
1699 mth_equipment_shifts_d med,
1700 mth_workday_shifts_d mws
1701 WHERE mee.equipment_pk_key = med.equipment_fk_key
1702 AND med.shift_workday_fk_key = mws.shift_workday_pk_key
1703 AND med.entity_type = 'EQUIPMENT') esd
1704 WHERE stg.equipment_fk = esd.equipment_pk
1705 AND stg.shift_workday_fk = esd.shift_workday_pk
1706 AND stg.processing_flag = v_processing_flag)
1707 AND EXISTS ( SELECT * FROM
1708 ( SELECT mds.shift_workday_pk
1709 FROM mth_workday_shifts_d mds,
1710 mth_equip_statuses_stg stg
1711 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1712 AND stg.shift_workday_fk IS NOT NULL
1713 AND stg.processing_flag = v_processing_flag) wds
1714 WHERE wds.shift_workday_pk = stg.shift_workday_fk )
1715 AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1716 FROM mth_equipments_d med,
1717 mth_equip_statuses_stg stg
1718 WHERE med.equipment_pk = stg.equipment_fk) eqp
1719 WHERE eqp.equipment_pk = stg.equipment_fk
1720 AND stg.processing_flag = v_processing_flag);
1721 mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1722
1723 -- Validation for user dimension 1
1724 UPDATE mth_equip_statuses_stg stg
1725 SET stg.err_code = stg.err_code || 'UD1 '
1726 WHERE stg.user_dim1_fk IS NOT NULL
1727 AND EXISTS (SELECT *
1728 FROM
1729 (SELECT mue.entity_pk, stg.user_dim1_fk
1730 FROM mth_user_dim_entities_mst mue,
1731 mth_equip_statuses_stg stg
1732 WHERE stg.user_dim1_fk = mue.entity_pk (+)
1733 AND stg.user_dim1_fk IS NOT NULL) ud1
1734 WHERE ud1.user_dim1_fk = stg.user_dim1_fk
1735 AND stg.processing_flag = v_processing_flag
1736 AND ud1.entity_pk IS NULL);
1737 mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1738
1739 -- Validation for user dimension 2
1740 UPDATE mth_equip_statuses_stg stg
1741 SET stg.err_code = stg.err_code || 'UD2 '
1742 WHERE stg.user_dim2_fk IS NOT NULL
1743 AND EXISTS (SELECT *
1744 FROM
1745 (SELECT mue.entity_pk, stg.user_dim2_fk
1746 FROM mth_user_dim_entities_mst mue,
1747 mth_equip_statuses_stg stg
1748 WHERE stg.user_dim2_fk = mue.entity_pk (+)
1749 AND stg.user_dim2_fk IS NOT NULL) ud2
1750 WHERE ud2.user_dim2_fk = stg.user_dim2_fk
1751 AND stg.processing_flag = v_processing_flag
1752 AND ud2.entity_pk IS NULL);
1753 mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1754
1755 -- Validation for user dimension 3
1756 UPDATE mth_equip_statuses_stg stg
1757 SET stg.err_code = stg.err_code || 'UD3 '
1758 WHERE stg.user_dim3_fk IS NOT NULL
1759 AND EXISTS (SELECT *
1760 FROM
1761 (SELECT mue.entity_pk, stg.user_dim3_fk
1762 FROM mth_user_dim_entities_mst mue,
1763 mth_equip_statuses_stg stg
1764 WHERE stg.user_dim3_fk = mue.entity_pk (+)
1765 AND stg.user_dim3_fk IS NOT NULL) ud3
1766 WHERE ud3.user_dim3_fk = stg.user_dim3_fk
1767 AND stg.processing_flag = v_processing_flag
1768 AND ud3.entity_pk IS NULL);
1769 mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1770
1771 -- Validation for user dimension 4
1772 UPDATE mth_equip_statuses_stg stg
1773 SET stg.err_code = stg.err_code || 'UD4 '
1774 WHERE stg.user_dim4_fk IS NOT NULL
1775 AND EXISTS (SELECT *
1776 FROM
1777 (SELECT mue.entity_pk, stg.user_dim4_fk
1778 FROM mth_user_dim_entities_mst mue,
1779 mth_equip_statuses_stg stg
1780 WHERE stg.user_dim4_fk = mue.entity_pk (+)
1781 AND stg.user_dim4_fk IS NOT NULL) ud4
1782 WHERE ud4.user_dim4_fk = stg.user_dim4_fk
1783 AND stg.processing_flag = v_processing_flag
1784 AND ud4.entity_pk IS NULL);
1785 mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1786
1787 -- Validation for user dimension 5
1788 UPDATE mth_equip_statuses_stg stg
1789 SET stg.err_code = stg.err_code || 'UD5 '
1790 WHERE stg.user_dim5_fk IS NOT NULL
1791 AND EXISTS (SELECT *
1792 FROM
1793 (SELECT mue.entity_pk, stg.user_dim5_fk
1794 FROM mth_user_dim_entities_mst mue,
1795 mth_equip_statuses_stg stg
1796 WHERE stg.user_dim5_fk = mue.entity_pk (+)
1797 AND stg.user_dim5_fk IS NOT NULL) ud5
1798 WHERE ud5.user_dim5_fk = stg.user_dim5_fk
1799 AND stg.processing_flag = v_processing_flag
1800 AND ud5.entity_pk IS NULL);
1801 mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1802
1803 -- Validation for 'GAP' error code
1804 UPDATE mth_equip_statuses_stg stg
1805 SET stg.err_code = stg.err_code || 'GAP '
1806 WHERE EXISTS (SELECT *
1807 FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
1808 over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
1809 FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
1810 (Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
1811 FROM mth_equip_statuses_stg a WHERE a.processing_flag = v_processing_flag) b) c
1812 WHERE c.Count >= 1
1813 AND stg.from_date = c.from_date
1814 AND stg.equipment_fk = c.equipment_fk
1815 AND stg.processing_flag = v_processing_flag);
1816 mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1817
1818 -- Validation for FTD
1819 UPDATE mth_equip_statuses_stg stg
1820 SET stg.err_code = stg.err_code || 'FTD '
1821 WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE
1822 AND stg.processing_flag = v_processing_flag;
1823 mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1824
1825 -- Validation for DTR
1826 UPDATE mth_equip_statuses_stg stg
1827 SET stg.err_code = stg.err_code || 'DTR '
1828 WHERE EXISTS (SELECT *
1829 FROM
1830 (SELECT flk.lookup_code,
1831 flk.lookup_type,
1832 stg.FROM_date,
1833 stg.to_date,
1834 stg.downtime_reason_code,
1835 stg.status
1836 FROM fnd_lookups flk,
1837 mth_equip_statuses_stg stg
1838 WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
1839 AND stg.downtime_reason_code = flk.lookup_code (+)
1840 AND stg.status = 3) dtr
1841 WHERE ((stg.downtime_reason_code = dtr.downtime_reason_code
1842 AND stg.status = dtr.status
1843 AND dtr.lookup_code IS NULL
1844 AND dtr.from_date = stg.from_date
1845 AND dtr.To_Date = stg.To_Date)
1846 OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL))
1847 AND stg.processing_flag = v_processing_flag);
1848 mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1849
1850 -- Validation for IDR
1851 UPDATE mth_equip_statuses_stg stg
1852 SET stg.err_code = stg.err_code || 'IDR '
1853 WHERE EXISTS (SELECT *
1854 FROM
1855 ( SELECT flk.lookup_code,
1856 flk.lookup_type,
1857 stg.FROM_date,
1858 stg.to_date,
1859 stg.downtime_reason_code,
1860 stg.status
1861 FROM fnd_lookups flk,
1862 mth_equip_statuses_stg stg
1863 WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
1864 AND stg.downtime_reason_code = flk.lookup_code (+)
1865 AND stg.status = 2) dtr
1866 WHERE stg.downtime_reason_code = dtr.downtime_reason_code
1867 AND stg.status = dtr.status
1868 AND dtr.lookup_code IS NULL
1869 AND dtr.from_date = stg.from_date
1870 AND dtr.To_Date = stg.To_Date
1871 AND stg.processing_flag = v_processing_flag);
1872 mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1873
1874 -- Validation for ITR
1875 UPDATE mth_equip_statuses_stg stg
1876 SET stg.err_code = stg.err_code || 'ITR '
1877 WHERE EXISTS ( SELECT * FROM (
1878 SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1879 FROM mth_workday_shifts_d mds,
1880 mth_equip_statuses_stg stg,
1881 mth_equipment_shifts_d mes,
1882 mth_equipments_d med
1883 WHERE stg.shift_workday_fk = mds.shift_workday_pk
1884 AND stg.equipment_fk = med.equipment_pk
1885 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
1886 AND med.equipment_pk_key = mes.equipment_fk_key
1887 AND (stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
1888 AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date)
1889 AND Upper(mes.entity_type) = 'EQUIPMENT'
1890 AND stg.processing_flag = v_processing_flag) itr
1891 WHERE itr.shift_workday_pk = stg.shift_workday_fk
1892 AND itr.equipment_pk = stg.equipment_fk
1893 AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
1894 AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date)
1895 AND stg.processing_flag = v_processing_flag);
1896 mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1897
1898 -- Validation for MSF
1899 UPDATE mth_equip_statuses_stg stg
1900 SET stg.err_code = stg.err_code || 'MSF '
1901 WHERE EXISTS ( SELECT * FROM (
1902 SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1903 FROM mth_workday_shifts_d mds,
1904 mth_equip_statuses_stg stg,
1905 mth_equipment_shifts_d mes,
1906 mth_equipments_d med
1907 WHERE stg.shift_workday_fk = mds.shift_workday_pk
1908 AND stg.equipment_fk = med.equipment_pk
1909 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
1910 AND med.equipment_pk_key = mes.equipment_fk_key
1911 AND (((stg.from_date BETWEEN mes.from_date AND mes.To_Date)
1912 AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date))
1913 OR ((stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
1914 AND (stg.to_date BETWEEN mes.from_date AND mes.To_Date)))
1915 AND Upper(mes.entity_type) = 'EQUIPMENT'
1916 AND stg.processing_flag = v_processing_flag) msf
1917 WHERE msf.shift_workday_pk = stg.shift_workday_fk
1918 AND msf.equipment_pk = stg.equipment_fk
1919 AND (((stg.from_date BETWEEN msf.from_date AND msf.To_Date)
1920 AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
1921 OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
1922 AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date)))
1923 AND stg.processing_flag = v_processing_flag);
1924 mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1925
1926 --Validation for OCSV
1927 UPDATE mth_equip_statuses_stg stag
1928 SET stag.err_code = stag.err_code || 'OCSV '
1929 WHERE EXISTS (SELECT *
1930 FROM
1931 (SELECT CASE
1932 WHEN (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
1933 AND (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
1934 AND ((stg.from_date >= (LAG(stg.from_date) OVER ( PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
1935 AND
1936 stg.from_date <= (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)))
1937 OR
1938 (stg.to_date >= (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
1939 AND
1940 stg.to_date <= (LAG(stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date )))
1941 )
1942 THEN 1 END overlap,
1943 med.equipment_pk,
1944 stg.from_date,
1945 stg.to_date
1946 FROM mth_equip_statuses_stg stg,
1947 mth_equipments_d med
1948 WHERE stg.equipment_fk = med.equipment_pk
1949 AND stg.processing_flag = v_processing_flag) ovp
1950 WHERE ovp.overlap = 1
1951 AND stag.equipment_fk = ovp.equipment_pk
1952 AND stag.from_date = ovp.from_date
1953 AND stag.To_Date = ovp.To_Date
1954 AND stag.processing_flag = v_processing_flag);
1955 mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1956
1957 --validation for OSTS
1958 UPDATE mth_equip_statuses_stg stag
1959 SET stag.err_code = stag.err_code || 'OSTS '
1960 WHERE EXISTS (SELECT *
1961 FROM ( SELECT CASE
1962 WHEN (stg.FROM_DATE >= sts.FROM_DATE
1963 AND stg.FROM_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
1964 OR (stg.TO_DATE >= sts.FROM_DATE
1965 AND stg.TO_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
1966 OR (stg.FROM_DATE < sts.FROM_DATE
1967 AND stg.TO_DATE > NVL(sts.TO_DATE,stg.TO_DATE+1/86400))
1968 THEN 1 END overlap ,
1969 med.equipment_pk,
1970 wds.shift_workday_pk,
1971 stg.from_date,
1972 stg.to_date
1973 FROM mth_equip_statuses_stg stg,
1974 mth_equip_statuses sts,
1975 mth_equipments_d med,
1976 mth_workday_shifts_d wds
1977 WHERE stg.equipment_fk = med.equipment_pk
1978 AND stg.shift_workday_fk = wds.shift_workday_pk
1979 AND med.equipment_pk_key = sts.equipment_fk_key
1980 AND wds.shift_workday_pk_key = sts.shift_workday_fk_key
1981 AND stg.processing_flag = v_processing_flag) osts
1982 WHERE osts.overlap = 1
1983 AND stag.equipment_fk = osts.equipment_pk
1984 AND stag.shift_workday_fk = osts.shift_workday_pk
1985 AND stag.processing_flag = v_processing_flag
1986 AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
1987 mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1988
1989 --validation for WRC
1990 UPDATE mth_equip_statuses_stg stg
1991 SET stg.err_code = stg.err_code ||'WRC '
1992 WHERE NOT EXISTS ( SELECT *
1993 FROM (SELECT stg.*
1994 FROM mth_equip_statuses_stg stg,
1995 MTH_EQUIPMENT_REASON_SETUP mer,
1996 mth_equipments_d med
1997 WHERE stg.equipment_fk = med.equipment_pk
1998 AND med.equipment_pk_key = mer.equipment_fk_key
1999 AND stg.downtime_reason_code = mer.reason_code
2000 AND stg.status IN (3,2)
2001 AND stg.downtime_reason_code IS NOT NULL) ers
2002 WHERE ers.equipment_fk = stg.equipment_fk
2003 AND ers.downtime_reason_code = stg.downtime_reason_code
2004 AND ers.from_date = stg.from_date
2005 AND Nvl(ers.To_Date,SYSDATE) = Nvl(stg.To_Date,SYSDATE)
2006 AND ers.shift_workday_fk = stg.shift_workday_fk
2007 AND stg.processing_flag = v_processing_flag
2008 )
2009 AND stg.status IN (3,2)
2010 AND stg.downtime_reason_code IS NOT NULL;
2011 mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2012
2013 --validation for STS
2014 UPDATE mth_equip_statuses_stg stg
2015 SET stg.err_code = stg.err_code ||'STS '
2016 WHERE stg.processing_flag = v_processing_flag
2017 AND stg.status NOT IN (1,2,3,4);
2018 mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2019
2020 --Insert records into mth_equip_statuses_err
2021 INSERT INTO mth_equip_statuses_err(equipment_fk,
2022 shift_workday_fk,
2023 from_date,
2024 To_Date,
2025 status,
2026 system_fk,
2027 reprocess_ready_yn,
2028 user_dim1_fk,
2029 user_dim2_fk,
2030 user_dim3_fk,
2031 user_dim4_fk,
2032 user_dim5_fk,
2033 user_attr1,
2034 user_attr2,
2035 user_attr3,
2036 user_attr4,
2037 user_attr5,
2038 user_measure1,
2039 user_measure2,
2040 user_measure3,
2041 user_measure4,
2042 user_measure5,
2043 err_code,
2044 downtime_reason_code)
2045 (SELECT equipment_fk,
2046 shift_workday_fk,
2047 from_date,
2048 To_Date,
2049 status,
2050 system_fk,
2051 'N',
2052 user_dim1_fk,
2053 user_dim2_fk,
2054 user_dim3_fk,
2055 user_dim4_fk,
2056 user_dim5_fk,
2057 user_attr1,
2058 user_attr2,
2059 user_attr3,
2060 user_attr4,
2061 user_attr5,
2062 user_measure1,
2063 user_measure2,
2064 user_measure3,
2065 user_measure4,
2066 user_measure5,
2067 err_code,
2068 downtime_reason_code
2069 FROM mth_equip_statuses_stg
2070 WHERE err_code IS NOT NULL
2071 AND processing_flag = v_processing_flag);
2072 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2073
2074 MERGE
2075 INTO
2076 MTH_EQUIP_STATUSES stat
2077 USING
2078 (SELECT med.EQUIPMENT_PK_KEY equipment_fk_key,
2079 wds.SHIFT_WORKDAY_PK_KEY shift_workday_fk_key,
2080 Greatest(stg.from_date,mhd.from_time) from_date,
2081 Least(stg.To_Date,mhd.to_time) to_date,
2082 stg.STATUS status,
2083 Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) system_fk_key,
2084 mue1.ENTITY_PK_KEY USER_DIM1_FK_KEY,
2085 mue2.ENTITY_PK_KEY USER_DIM2_FK_KEY,
2086 mue3.ENTITY_PK_KEY USER_DIM3_FK_KEY,
2087 mue4.ENTITY_PK_KEY USER_DIM4_FK_KEY,
2088 mue5.ENTITY_PK_KEY USER_DIM5_FK_KEY,
2089 stg.USER_ATTR1 USER_ATTR1,
2090 stg.USER_ATTR2 USER_ATTR2,
2091 stg.USER_ATTR3 USER_ATTR3,
2092 stg.USER_ATTR4 USER_ATTR4,
2093 stg.USER_ATTR5 USER_ATTR5,
2094 stg.USER_MEASURE1 USER_MEASURE1,
2095 stg.USER_MEASURE2 USER_MEASURE2,
2096 stg.USER_MEASURE3 USER_MEASURE3,
2097 stg.USER_MEASURE4 USER_MEASURE4,
2098 stg.USER_MEASURE5 USER_MEASURE5,
2099 v_log_date log_date,
2100 v_unassigned_val unassigned_val,
2101 mhd.HOUR_PK_KEY hour_fk_key,
2102 stg.from_date reading_time
2103 FROM mth_equip_statuses_stg stg,
2104 mth_equipments_d med,
2105 mth_workday_shifts_d wds,
2106 mth_systems_setup mss,
2107 mth_user_dim_entities_mst mue1,
2108 mth_user_dim_entities_mst mue2,
2109 mth_user_dim_entities_mst mue3,
2110 mth_user_dim_entities_mst mue4,
2111 mth_user_dim_entities_mst mue5,
2112 fnd_lookups lkp,
2113 mth_hour_d mhd
2114 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
2115 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
2116 AND mhd.to_time >= stg.from_date
2117 AND mhd.from_time <= stg.to_date
2118 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
2119 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
2120 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
2121 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
2122 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
2123 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
2124 AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
2125 AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+)
2126 AND stg.processing_flag = v_processing_flag
2127 AND err_code IS NULL
2128 UNION ALL
2129 SELECT med.EQUIPMENT_PK_KEY equipment_fk_key,
2130 wds.SHIFT_WORKDAY_PK_KEY shift_workday_fk_key,
2131 Greatest(stg.from_date,mhd.from_time) from_date,
2132 stg.To_Date to_date,
2133 stg.STATUS status,
2134 Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) system_fk_key,
2135 mue1.ENTITY_PK_KEY USER_DIM1_FK_KEY,
2136 mue2.ENTITY_PK_KEY USER_DIM2_FK_KEY,
2137 mue3.ENTITY_PK_KEY USER_DIM3_FK_KEY,
2138 mue4.ENTITY_PK_KEY USER_DIM4_FK_KEY,
2139 mue5.ENTITY_PK_KEY USER_DIM5_FK_KEY,
2140 stg.USER_ATTR1 USER_ATTR1,
2141 stg.USER_ATTR2 USER_ATTR2,
2142 stg.USER_ATTR3 USER_ATTR3,
2143 stg.USER_ATTR4 USER_ATTR4,
2144 stg.USER_ATTR5 USER_ATTR5,
2145 stg.USER_MEASURE1 USER_MEASURE1,
2146 stg.USER_MEASURE2 USER_MEASURE2,
2147 stg.USER_MEASURE3 USER_MEASURE3,
2148 stg.USER_MEASURE4 USER_MEASURE4,
2149 stg.USER_MEASURE5 USER_MEASURE5,
2150 v_log_date log_date,
2151 v_unassigned_val unassigned_val,
2152 mhd.HOUR_PK_KEY hour_fk_key,
2153 stg.from_date reading_time
2154 FROM mth_equip_statuses_stg stg,
2155 mth_equipments_d med,
2156 mth_workday_shifts_d wds,
2157 mth_systems_setup mss,
2158 mth_user_dim_entities_mst mue1,
2159 mth_user_dim_entities_mst mue2,
2160 mth_user_dim_entities_mst mue3,
2161 mth_user_dim_entities_mst mue4,
2162 mth_user_dim_entities_mst mue5,
2163 fnd_lookups lkp,
2164 mth_hour_d mhd
2165 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
2166 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
2167 AND stg.from_date BETWEEN mhd.from_time AND mhd.to_time
2168 AND stg.To_Date IS NULL
2169 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
2170 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
2171 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
2172 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
2173 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
2174 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
2175 AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
2176 AND stg.processing_flag = v_processing_flag
2177 AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+) AND stg.err_code IS NULL ) subquery
2178 ON ( stat.equipment_fk_key = subquery.equipment_fk_key
2179 AND stat.shift_workday_fk_key = subquery.shift_workday_fk_key
2180 AND stat.hour_fk_key = subquery.hour_fk_key
2181 AND stat.from_date = subquery.from_date )
2182 WHEN MATCHED THEN
2183 UPDATE SET stat.To_Date = subquery.to_date,
2184 stat.status = subquery.status,
2185 stat.system_fk_key = subquery.system_fk_key,
2186 stat.user_dim1_fk_key = subquery.USER_DIM1_FK_KEY,
2187 stat.user_dim2_fk_key = subquery.USER_DIM2_FK_KEY,
2188 stat.user_dim3_fk_key = subquery.USER_DIM3_FK_KEY,
2189 stat.user_dim4_fk_key = subquery.USER_DIM4_FK_KEY,
2190 stat.user_dim5_fk_key = subquery.USER_DIM5_FK_KEY,
2191 stat.user_attr1 = subquery.USER_ATTR1,
2192 stat.user_attr2 = subquery.USER_ATTR2,
2193 stat.user_attr3 = subquery.USER_ATTR3,
2194 stat.user_attr4 = subquery.USER_ATTR4,
2195 stat.user_attr5 = subquery.USER_ATTR5,
2196 stat.user_measure1 = subquery.USER_MEASURE1,
2197 stat.user_measure2 = subquery.USER_MEASURE2,
2198 stat.user_measure3 = subquery.USER_MEASURE3,
2199 stat.user_measure4 = subquery.USER_MEASURE4,
2200 stat.user_measure5 = subquery.USER_MEASURE5,
2201 stat.last_update_date = subquery.log_date,
2202 stat.last_update_system_id = subquery.unassigned_val
2203 WHEN NOT MATCHED THEN
2204 INSERT ( stat.equipment_fk_key,
2205 stat.shift_workday_fk_key,
2206 stat.from_date,
2207 stat.To_Date,
2208 stat.status,
2209 stat.system_fk_key,
2210 stat.user_dim1_fk_key,
2211 stat.user_dim2_fk_key,
2212 stat.user_dim3_fk_key,
2213 stat.user_dim4_fk_key,
2214 stat.user_dim5_fk_key,
2215 stat.user_attr1 ,
2216 stat.user_attr2 ,
2217 stat.user_attr3 ,
2218 stat.user_attr4 ,
2219 stat.user_attr5 ,
2220 stat.user_measure1 ,
2221 stat.user_measure2 ,
2222 stat.user_measure3 ,
2223 stat.user_measure4 ,
2224 stat.user_measure5 ,
2225 stat.creation_date,
2226 stat.last_update_date,
2227 stat.creation_system_id,
2228 stat.last_update_system_id,
2229 stat.created_by,
2230 stat.last_updated_by,
2231 stat.last_update_login,
2232 stat.expected_up_time,
2233 stat.status_type,
2234 stat.hour_fk_key,
2235 stat.reading_time )
2236 VALUES
2237 ( subquery.equipment_fk_key,
2238 subquery.shift_workday_fk_key,
2239 subquery.from_date,
2240 subquery.To_Date,
2241 subquery.status,
2242 subquery.system_fk_key,
2243 subquery.user_dim1_fk_key,
2244 subquery.user_dim2_fk_key,
2245 subquery.user_dim3_fk_key,
2246 subquery.user_dim4_fk_key,
2247 subquery.user_dim5_fk_key,
2248 subquery.user_attr1 ,
2249 subquery.user_attr2 ,
2250 subquery.user_attr3 ,
2251 subquery.user_attr4 ,
2252 subquery.user_attr5 ,
2253 subquery.user_measure1 ,
2254 subquery.user_measure2 ,
2255 subquery.user_measure3 ,
2256 subquery.user_measure4 ,
2257 subquery.user_measure5 ,
2258 subquery.log_date,
2259 subquery.log_date,
2260 subquery.unassigned_val,
2261 subquery.unassigned_val,
2262 NULL,
2263 NULL,
2264 NULL,
2265 NULL,
2266 NULL,
2267 subquery.hour_fk_key,
2268 subquery.reading_time );
2269
2270 mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2271
2272 MERGE
2273 INTO
2274 MTH_TAG_REASON_READINGS trr
2275 USING
2276 (SELECT 1 reason_type,
2277 sts.equipment_fk_key equipment_fk_key,
2278 sts.from_date from_date,
2279 sts.To_Date to_date,
2280 stg.downtime_reason_code reason_code,
2281 v_log_date log_date,
2282 v_unassigned_val ua_val,
2283 NULL oth_col,
2284 sts.reading_time reading_time,
2285 sts.hour_fk_key hour_fk_key
2286 FROM mth_equip_statuses_stg stg,
2287 mth_equip_statuses sts
2288 WHERE stg.from_date = sts.reading_time
2289 AND sts.status = stg.status
2290 AND stg.status = 3
2291 AND stg.processing_flag = v_processing_flag
2292 AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
2293 FROM mth_equipments_d
2294 WHERE equipment_pk = stg.equipment_fk )
2295 AND stg.err_code IS NULL
2296 UNION
2297 SELECT 3 reason_type,
2298 sts.equipment_fk_key equipment_fk_key,
2299 sts.from_date from_date,
2300 sts.To_Date to_date,
2301 stg.downtime_reason_code reason_code,
2302 v_log_date log_date,
2303 v_unassigned_val ua_val,
2304 NULL oth_col,
2305 sts.reading_time reading_time,
2306 sts.hour_fk_key hour_fk_key
2307 FROM mth_equip_statuses_stg stg,
2308 mth_equip_statuses sts
2309 WHERE stg.from_date = sts.reading_time
2310 AND sts.status = stg.status
2311 AND stg.status = 2
2312 AND stg.processing_flag = v_processing_flag
2313 AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
2314 FROM mth_equipments_d
2315 WHERE equipment_pk = stg.equipment_fk )
2316 AND stg.err_code IS NULL
2317 )subquery
2318 ON ( trr.equipment_fk_key = subquery.equipment_fk_key
2319 AND trr.from_date = subquery.from_date
2320 )
2321 WHEN MATCHED THEN
2322 UPDATE SET trr.to_date = subquery.to_date,
2323 trr.last_update_date = subquery.log_date,
2324 trr.last_update_login = subquery.oth_col,
2325 trr.last_updated_by = subquery.oth_col
2326 WHEN NOT MATCHED THEN
2327 INSERT (trr.REASON_TYPE,
2328 trr.EQUIPMENT_FK_KEY,
2329 trr.FROM_DATE,
2330 trr.To_Date,
2331 trr.REASON_CODE,
2332 trr.CREATION_DATE,
2333 trr.LAST_UPDATE_DATE,
2334 trr.CREATION_SYSTEM_ID,
2335 trr.LAST_UPDATE_SYSTEM_ID,
2336 trr.CREATED_BY,
2337 trr.LAST_UPDATE_LOGIN,
2338 trr.LAST_UPDATED_BY,
2339 trr.READING_TIME,
2340 trr.HOUR_FK_KEY)
2341 VALUES (subquery.reason_type,
2342 subquery.equipment_fk_key,
2343 subquery.from_date,
2344 subquery.to_date,
2345 subquery.reason_code,
2346 subquery.log_date,
2347 subquery.log_date,
2348 subquery.ua_val,
2349 subquery.ua_val,
2350 subquery.oth_col,
2351 subquery.oth_col,
2352 subquery.oth_col,
2353 subquery.reading_time,
2354 subquery.hour_fk_key);
2355
2356 mth_util_pkg.log_msg('Number of rows merged in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2357
2358 mth_util_pkg.truncate_table_partition('MTH_EQUIP_STATUSES_STG',v_processing_flag);
2359
2360 mth_util_pkg.log_msg('INCR_STATUS_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
2361 EXCEPTION
2362 WHEN OTHERS THEN
2363 --Call logging API and then throw exception
2364 mth_util_pkg.log_msg('Exception OTHERS in INCR_STATUS_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2365 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2366 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2367 RAISE;
2368 END;
2369
2370 /*******************************************************************************
2371 * Procedure :RECAL_STATUS_FROM_CSV *
2372 * Description :This procedure is used for calculating the status *
2373 * in RECAL mode *
2374 * File Name :MTHEQSTB.PLS *
2375 * Visibility :Private *
2376 * Parameters : p_recal_from_date : Recalculation from date *
2377 * p_recal_to_date : Recalculation to date *
2378 * p_equipment_pk_key : Equipment to recalculate *
2379 *******************************************************************************/
2380 PROCEDURE RECAL_STATUS_FROM_CSV(p_recal_from_date IN DATE, --Recalculation from date
2381 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
2382 p_equipment_pk_key IN NUMBER DEFAULT NULL,--Equipment to recalculate
2383 p_plant_pk_key IN NUMBER DEFAULT NULL)--Site for which recalcution to be done
2384 IS
2385 GAP_IN_RECAL_CSV EXCEPTION;
2386 EQUIP_MIS_ERR EXCEPTION;
2387 SITE_MIS_ERR EXCEPTION;
2388 --VALIDATION_ERR EXCEPTION;
2389 l_count NUMBER;
2390 p_min_from_date_csv DATE;
2391 p_max_to_date_csv DATE;
2392 v_log_date DATE;
2393 v_unassigned_val VARCHAR2(30);
2394 v_count NUMBER;
2395 v_msg VARCHAR2(300);
2396
2397 CURSOR c_error_rows IS
2398 SELECT equipment_fk,
2399 shift_workday_fk,
2400 from_date,
2401 To_Date,
2402 status,
2403 user_dim1_fk,
2404 user_dim2_fk,
2405 user_dim3_fk,
2406 user_dim4_fk,
2407 user_dim5_fk,
2408 downtime_reason_code,
2409 err_code
2410 FROM mth_equip_statuses_stg
2411 WHERE err_code IS NOT NULL;
2412
2413 TYPE fetch_err_rows IS TABLE OF c_error_rows%ROWTYPE;
2414 l_err_rows fetch_err_rows;
2415
2416 BEGIN
2417 --initialize parameters
2418 v_log_date := SYSDATE;
2419 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2420
2421 IF (p_plant_pk_key IS NOT NULL)
2422 THEN
2423 SELECT Count(*)
2424 INTO l_count
2425 FROM mth_plants_d mpd
2426 WHERE mpd.plant_pk_key = p_plant_pk_key;
2427 END IF;
2428
2429 IF l_count < 1
2430 THEN
2431 v_msg:=fnd_message.get_string('MTH','MTH_SITE_MIS_ERR');
2432 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
2433 RAISE SITE_MIS_ERR;
2434 END IF;
2435
2436 IF (p_equipment_pk_key is not null)
2437 THEN
2438 SELECT Count(*)
2439 INTO l_count
2440 FROM mth_equipments_d med,
2441 mth_equip_statuses_stg stg
2442 WHERE med.equipment_pk = stg.equipment_fk
2443 AND med.equipment_pk_key = p_equipment_pk_key
2444 AND med.plant_fk_key = Nvl(p_plant_pk_key,med.plant_fk_key);
2445 END IF;
2446
2447 IF l_count < 1
2448 THEN
2449 v_msg:=fnd_message.get_string('MTH','MTH_EQUIP_MIS_ERR');
2450 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
2451 RAISE EQUIP_MIS_ERR;
2452 END IF;
2453
2454 SELECT Min(from_date)
2455 INTO p_min_from_date_csv
2456 FROM mth_equip_statuses_stg stg
2457 WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
2458 FROM mth_equipments_d
2459 WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
2460
2461 SELECT Max(to_date)
2462 INTO p_max_to_date_csv
2463 FROM mth_equip_statuses_stg stg
2464 WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
2465 FROM mth_equipments_d
2466 WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
2467
2468 IF(p_min_from_date_csv > p_recal_from_date OR p_max_to_date_csv < p_recal_to_date)
2469 THEN
2470 v_msg:=fnd_message.get_string('MTH','MTH_GAP_IN_RECAL_CSV');
2471 mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
2472 RAISE GAP_IN_RECAL_CSV;
2473 END IF;
2474
2475 --Update the status before deleting records for recalculation
2476 UPDATE mth_equip_statuses sts
2477 SET sts.To_Date = (SELECT recal.new_to_date
2478 FROM (SELECT equipment_fk_key,shift_workday_fk_key, from_date,To_Date,
2479 CASE WHEN (from_date < p_recal_from_date AND To_Date >= p_recal_from_date
2480 --AND p_recal_from_date < (To_Date - 1/86400)
2481 )
2482 THEN (p_recal_from_date - 1/86400)
2483 END new_To_Date
2484 FROM mth_equip_statuses)recal
2485 WHERE sts.equipment_fk_key = recal.equipment_fk_key
2486 AND sts.shift_workday_fk_key = recal.shift_workday_fk_key
2487 AND sts.from_date = recal.FROM_date
2488 AND sts.To_Date = recal.To_Date
2489 AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
2490 AND recal.new_to_date IS NOT NULL)
2491 WHERE EXISTS (SELECT 1
2492 FROM (SELECT equipment_fk_key,shift_workday_fk_key, from_date,To_Date,
2493 CASE WHEN (from_date < p_recal_from_date AND To_Date >= p_recal_from_date
2494 --AND p_recal_from_date < (To_Date - 1/86400)
2495 )
2496 THEN (p_recal_from_date - 1/86400)
2497 END new_To_Date
2498 FROM mth_equip_statuses)rcl
2499 WHERE sts.equipment_fk_key = rcl.equipment_fk_key
2500 AND sts.shift_workday_fk_key = rcl.shift_workday_fk_key
2501 AND sts.from_date = rcl.FROM_date
2502 AND sts.To_Date = rcl.To_Date
2503 AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
2504 AND rcl.new_to_date IS NOT NULL);
2505
2506 mth_util_pkg.log_msg('Number of rows updated for from_date MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2507
2508 IF (p_recal_to_date IS NOT NULL) THEN
2509
2510 UPDATE mth_equip_statuses sts
2511 SET sts.from_date = (SELECT recal.new_from_date
2512 FROM (SELECT equipment_fk_key,shift_workday_fk_key, from_date,To_Date,
2513 CASE WHEN (from_date <= p_recal_to_date AND To_Date > p_recal_to_date
2514 --AND p_recal_to_date < (To_Date + 1/86400)
2515 )
2516 THEN (p_recal_to_date + 1/86400)
2517 END new_from_date
2518 FROM mth_equip_statuses)recal
2519 WHERE sts.equipment_fk_key = recal.equipment_fk_key
2520 AND sts.shift_workday_fk_key = recal.shift_workday_fk_key
2521 AND sts.from_date = recal.FROM_date
2522 AND sts.To_Date = recal.To_Date
2523 AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
2524 AND recal.new_from_date IS NOT NULL)
2525 WHERE EXISTS (SELECT 1
2526 FROM ( SELECT equipment_fk_key,shift_workday_fk_key, from_date,To_Date,
2527 CASE WHEN (from_date <= p_recal_to_date AND To_Date > p_recal_to_date
2528 --AND p_recal_to_date < (To_Date + 1/86400)
2529 )
2530 THEN (p_recal_to_date + 1/86400)
2531 END new_from_date
2532 FROM mth_equip_statuses)rcl
2533 WHERE sts.equipment_fk_key = rcl.equipment_fk_key
2534 AND sts.shift_workday_fk_key = rcl.shift_workday_fk_key
2535 AND sts.from_date = rcl.FROM_date
2536 AND sts.To_Date = rcl.To_Date
2537 AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
2538 AND rcl.new_from_date IS NOT NULL);
2539
2540 mth_util_pkg.log_msg('Number of rows updated for to_date MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2541 END IF;
2542
2543 -- Update the tag reason readings before deleting records for recalculation
2544 UPDATE mth_tag_reason_readings sts
2545 SET sts.To_Date = (SELECT recal.new_to_date
2546 FROM (SELECT equipment_fk_key,from_date,To_Date,
2547 CASE WHEN (from_date < p_recal_from_date AND To_Date >= p_recal_from_date
2548 --AND p_recal_from_date < (To_Date - 1/86400)
2549 )
2550 THEN (p_recal_from_date - 1/86400)
2551 END new_To_Date
2552 FROM mth_tag_reason_readings)recal
2553 WHERE sts.equipment_fk_key = recal.equipment_fk_key
2554 AND sts.from_date = recal.FROM_date
2555 AND sts.To_Date = recal.To_Date
2556 AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
2557 AND recal.new_to_date IS NOT NULL)
2558 WHERE EXISTS (SELECT 1
2559 FROM (SELECT equipment_fk_key,from_date,To_Date,
2560 CASE WHEN (from_date < p_recal_from_date AND To_Date >= p_recal_from_date
2561 --AND p_recal_from_date < (To_Date - 1/86400)
2562 )
2563 THEN (p_recal_from_date - 1/86400)
2564 END new_To_Date
2565 FROM mth_tag_reason_readings)rcl
2566 WHERE sts.equipment_fk_key = rcl.equipment_fk_key
2567 AND sts.from_date = rcl.FROM_date
2568 AND sts.To_Date = rcl.To_Date
2569 AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
2570 AND rcl.new_to_date IS NOT NULL);
2571
2572 mth_util_pkg.log_msg('Number of rows updated for from_date MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2573
2574 IF (p_recal_to_date IS NOT NULL) THEN
2575 UPDATE mth_tag_reason_readings sts
2576 SET sts.from_date = (SELECT recal.new_from_date
2577 FROM (SELECT equipment_fk_key, from_date,To_Date,
2578 CASE WHEN (from_date <= p_recal_to_date AND To_Date > p_recal_to_date
2579 --AND p_recal_to_date < (To_Date + 1/86400)
2580 )
2581 THEN (p_recal_to_date + 1/86400)
2582 END new_from_date
2583 FROM mth_tag_reason_readings)recal
2584 WHERE sts.equipment_fk_key = recal.equipment_fk_key
2585 AND sts.from_date = recal.FROM_date
2586 AND sts.To_Date = recal.To_Date
2587 AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
2588 AND recal.new_from_date IS NOT NULL)
2589 WHERE EXISTS (SELECT 1
2590 FROM ( SELECT equipment_fk_key, from_date,To_Date,
2591 CASE WHEN (from_date <= p_recal_to_date AND To_Date > p_recal_to_date
2592 --AND p_recal_to_date < (To_Date + 1/86400)
2593 )
2594 THEN (p_recal_to_date + 1/86400)
2595 END new_from_date
2596 FROM mth_tag_reason_readings)rcl
2597 WHERE sts.equipment_fk_key = rcl.equipment_fk_key
2598 AND sts.from_date = rcl.FROM_date
2599 AND sts.To_Date = rcl.To_Date
2600 AND sts.equipment_fk_key = Nvl(p_equipment_pk_key,sts.equipment_fk_key)
2601 AND rcl.new_from_date IS NOT NULL);
2602
2603 mth_util_pkg.log_msg('Number of rows updated for to_date MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2604 END IF;
2605
2606 --Delete records from status table for the recalculation time range
2607 IF (p_equipment_pk_key IS NULL AND p_plant_pk_key IS NOT NULL) THEN
2608 DELETE
2609 FROM mth_equip_statuses
2610 WHERE from_date >= p_recal_from_date
2611 AND nvl(To_Date,sysdate) <= nvl(p_recal_to_date,nvl(To_Date,sysdate))
2612 AND equipment_fk_key IN ( SELECT equipment_pk_key
2613 FROM mth_equipments_d
2614 WHERE plant_fk_key = p_plant_pk_key);
2615 ELSE
2616 DELETE
2617 FROM mth_equip_statuses
2618 WHERE from_date >= p_recal_from_date
2619 AND nvl(To_Date,sysdate) <= nvl(p_recal_to_date,nvl(To_Date,sysdate))
2620 AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
2621 END IF;
2622
2623 mth_util_pkg.log_msg('Number of rows deleted from MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2624
2625 --Delete records from tag reason readings table for the recalculation time range
2626 IF (p_equipment_pk_key IS NULL AND p_plant_pk_key IS NOT NULL) THEN
2627 DELETE
2628 FROM mth_tag_reason_readings
2629 WHERE from_date >= p_recal_from_date
2630 AND nvl(To_Date,sysdate) <= nvl(p_recal_to_date,nvl(To_Date,sysdate))
2631 AND reason_type IN (1,3)
2632 AND equipment_fk_key IN ( SELECT equipment_pk_key
2633 FROM mth_equipments_d
2634 WHERE plant_fk_key = p_plant_pk_key);
2635 ELSE
2636 DELETE
2637 FROM mth_tag_reason_readings
2638 WHERE from_date >= p_recal_from_date
2639 AND nvl(To_Date,sysdate) <= nvl(p_recal_to_date,nvl(To_Date,sysdate))
2640 AND reason_type IN (1,3)
2641 AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
2642 END IF;
2643
2644 mth_util_pkg.log_msg('Number of rows deleted from MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2645
2646 --Execute all validations on csv records
2647 -- Validation for Duplicate record
2648 UPDATE mth_equip_statuses_stg stg
2649 SET stg.err_code = stg.err_code || 'DUP '
2650 WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
2651 FROM mth_equip_statuses_stg
2652 GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
2653 WHERE dup.cnt>1
2654 AND dup.equipment_fk = stg.equipment_fk
2655 AND dup.shift_workday_fk = stg.shift_workday_fk
2656 AND dup.from_date = stg.from_date
2657 AND dup.To_Date = stg.To_Date );
2658 mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2659
2660 -- Validation for invalid equipment
2661 UPDATE mth_equip_statuses_stg stg
2662 SET stg.err_code = stg.err_code || 'EQP '
2663 WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
2664 FROM mth_equipments_d med,
2665 mth_equip_statuses_stg stg
2666 WHERE med.equipment_pk = stg.equipment_fk) eqp
2667 WHERE eqp.equipment_pk = stg.equipment_fk );
2668 mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2669
2670 -- Validation for Inactive equipment
2671 UPDATE mth_equip_statuses_stg stg
2672 SET stg.err_code = stg.err_code || 'IEQ '
2673 WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
2674 FROM mth_equipments_d med,
2675 mth_equip_statuses_stg stg
2676 WHERE med.equipment_pk = stg.equipment_fk
2677 AND med.status <> 'ACTIVE') eqp
2678 WHERE eqp.equipment_pk = stg.equipment_fk );
2679 mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2680
2681 -- Validation for invalid shift
2682 UPDATE mth_equip_statuses_stg stg
2683 SET stg.err_code = stg.err_code || 'WDS '
2684 WHERE stg.shift_workday_fk IS NOT NULL
2685 AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
2686 FROM mth_workday_shifts_d mds,
2687 mth_equip_statuses_stg stg
2688 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
2689 AND stg.shift_workday_fk IS NOT NULL) wds
2690 WHERE wds.shift_workday_pk = stg.shift_workday_fk);
2691 mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2692
2693 -- Valildation for invalid equipment shift combination
2694 UPDATE mth_equip_statuses_stg stg
2695 SET stg.err_code = stg.err_code || 'ESD '
2696 WHERE NOT EXISTS ( SELECT * FROM
2697 ( SELECT mee.equipment_pk, mws.shift_workday_pk
2698 FROM mth_equipments_d mee,
2699 mth_equipment_shifts_d med,
2700 mth_workday_shifts_d mws
2701 WHERE mee.equipment_pk_key = med.equipment_fk_key
2702 AND med.shift_workday_fk_key = mws.shift_workday_pk_key
2703 AND med.entity_type = 'EQUIPMENT') esd
2704 WHERE stg.equipment_fk = esd.equipment_pk
2705 AND stg.shift_workday_fk = esd.shift_workday_pk)
2706 AND EXISTS ( SELECT * FROM
2707 ( SELECT mds.shift_workday_pk
2708 FROM mth_workday_shifts_d mds,
2709 mth_equip_statuses_stg stg
2710 WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
2711 AND stg.shift_workday_fk IS NOT NULL) wds
2712 WHERE wds.shift_workday_pk = stg.shift_workday_fk )
2713 AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
2714 FROM mth_equipments_d med,
2715 mth_equip_statuses_stg stg
2716 WHERE med.equipment_pk = stg.equipment_fk) eqp
2717 WHERE eqp.equipment_pk = stg.equipment_fk );
2718 mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2719
2720 -- Validation for user dimension 1
2721 UPDATE mth_equip_statuses_stg stg
2722 SET stg.err_code = stg.err_code || 'UD1 '
2723 WHERE stg.user_dim1_fk IS NOT NULL
2724 AND EXISTS (SELECT *
2725 FROM
2726 (SELECT mue.entity_pk, stg.user_dim1_fk
2727 FROM mth_user_dim_entities_mst mue,
2728 mth_equip_statuses_stg stg
2729 WHERE stg.user_dim1_fk = mue.entity_pk (+)
2730 AND stg.user_dim1_fk IS NOT NULL) ud1
2731 WHERE ud1.user_dim1_fk = stg.user_dim1_fk
2732 AND ud1.entity_pk IS NULL);
2733 mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2734
2735 -- Validation for user dimension 2
2736 UPDATE mth_equip_statuses_stg stg
2737 SET stg.err_code = stg.err_code || 'UD2 '
2738 WHERE stg.user_dim2_fk IS NOT NULL
2739 AND EXISTS (SELECT *
2740 FROM
2741 (SELECT mue.entity_pk, stg.user_dim2_fk
2742 FROM mth_user_dim_entities_mst mue,
2743 mth_equip_statuses_stg stg
2744 WHERE stg.user_dim2_fk = mue.entity_pk (+)
2745 AND stg.user_dim2_fk IS NOT NULL) ud2
2746 WHERE ud2.user_dim2_fk = stg.user_dim2_fk
2747 AND ud2.entity_pk IS NULL);
2748 mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2749
2750 -- Validation for user dimension 3
2751 UPDATE mth_equip_statuses_stg stg
2752 SET stg.err_code = stg.err_code || 'UD3 '
2753 WHERE stg.user_dim3_fk IS NOT NULL
2754 AND EXISTS (SELECT *
2755 FROM
2756 (SELECT mue.entity_pk, stg.user_dim3_fk
2757 FROM mth_user_dim_entities_mst mue,
2758 mth_equip_statuses_stg stg
2759 WHERE stg.user_dim3_fk = mue.entity_pk (+)
2760 AND stg.user_dim3_fk IS NOT NULL) ud3
2761 WHERE ud3.user_dim3_fk = stg.user_dim3_fk
2762 AND ud3.entity_pk IS NULL);
2763 mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2764
2765 -- Validation for user dimension 4
2766 UPDATE mth_equip_statuses_stg stg
2767 SET stg.err_code = stg.err_code || 'UD4 '
2768 WHERE stg.user_dim4_fk IS NOT NULL
2769 AND EXISTS (SELECT *
2770 FROM
2771 (SELECT mue.entity_pk, stg.user_dim4_fk
2772 FROM mth_user_dim_entities_mst mue,
2773 mth_equip_statuses_stg stg
2774 WHERE stg.user_dim4_fk = mue.entity_pk (+)
2775 AND stg.user_dim4_fk IS NOT NULL) ud4
2776 WHERE ud4.user_dim4_fk = stg.user_dim4_fk
2777 AND ud4.entity_pk IS NULL);
2778 mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2779
2780 -- Validation for user dimension 5
2781 UPDATE mth_equip_statuses_stg stg
2782 SET stg.err_code = stg.err_code || 'UD5 '
2783 WHERE stg.user_dim5_fk IS NOT NULL
2784 AND EXISTS (SELECT *
2785 FROM
2786 (SELECT mue.entity_pk, stg.user_dim5_fk
2787 FROM mth_user_dim_entities_mst mue,
2788 mth_equip_statuses_stg stg
2789 WHERE stg.user_dim5_fk = mue.entity_pk (+)
2790 AND stg.user_dim5_fk IS NOT NULL) ud5
2791 WHERE ud5.user_dim5_fk = stg.user_dim5_fk
2792 AND ud5.entity_pk IS NULL);
2793 mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2794
2795 -- Validation for 'GAP' error code
2796 UPDATE mth_equip_statuses_stg stg
2797 SET stg.err_code = stg.err_code || 'GAP '
2798 WHERE EXISTS (SELECT *
2799 FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
2800 over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
2801 FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
2802 (Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
2803 FROM mth_equip_statuses_stg a) b) c
2804 WHERE c.Count >= 1
2805 AND stg.from_date = c.from_date
2806 AND stg.equipment_fk = c.equipment_fk);
2807 mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2808
2809 -- Validation for FTD
2810 UPDATE mth_equip_statuses_stg stg
2811 SET stg.err_code = stg.err_code || 'FTD '
2812 WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE;
2813 mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2814
2815 -- Validation for DTR
2816 UPDATE mth_equip_statuses_stg stg
2817 SET stg.err_code = stg.err_code || 'DTR '
2818 WHERE EXISTS (SELECT *
2819 FROM
2820 (SELECT flk.lookup_code,
2821 flk.lookup_type,
2822 stg.FROM_date,
2823 stg.to_date,
2824 stg.downtime_reason_code,
2825 stg.status
2826 FROM fnd_lookups flk,
2827 mth_equip_statuses_stg stg
2828 WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
2829 AND stg.downtime_reason_code = flk.lookup_code (+)
2830 AND stg.status = 3) dtr
2831 WHERE (stg.downtime_reason_code = dtr.downtime_reason_code
2832 AND stg.status = dtr.status
2833 AND dtr.lookup_code IS NULL
2834 AND dtr.from_date = stg.from_date
2835 AND dtr.To_Date = stg.To_Date)
2836 OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL));
2837 mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2838
2839 -- Validation for IDR
2840 UPDATE mth_equip_statuses_stg stg
2841 SET stg.err_code = stg.err_code || 'IDR '
2842 WHERE EXISTS (SELECT *
2843 FROM
2844 ( SELECT flk.lookup_code,
2845 flk.lookup_type,
2846 stg.FROM_date,
2847 stg.to_date,
2848 stg.downtime_reason_code,
2849 stg.status
2850 FROM fnd_lookups flk,
2851 mth_equip_statuses_stg stg
2852 WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
2853 AND stg.downtime_reason_code = flk.lookup_code (+)
2854 AND stg.status = 2) dtr
2855 WHERE stg.downtime_reason_code = dtr.downtime_reason_code
2856 AND stg.status = dtr.status
2857 AND dtr.lookup_code IS NULL
2858 AND dtr.from_date = stg.from_date
2859 AND dtr.To_Date = stg.To_Date);
2860 mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2861
2862 -- Validation for ITR
2863 UPDATE mth_equip_statuses_stg stg
2864 SET stg.err_code = stg.err_code || 'ITR '
2865 WHERE EXISTS ( SELECT * FROM (
2866 SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
2867 FROM mth_workday_shifts_d mds,
2868 mth_equip_statuses_stg stg,
2869 mth_equipment_shifts_d mes,
2870 mth_equipments_d med
2871 WHERE stg.shift_workday_fk = mds.shift_workday_pk
2872 AND stg.equipment_fk = med.equipment_pk
2873 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
2874 AND med.equipment_pk_key = mes.equipment_fk_key
2875 AND (stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
2876 AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date)
2877 AND Upper(mes.entity_type) = 'EQUIPMENT') itr
2878 WHERE itr.shift_workday_pk = stg.shift_workday_fk
2879 AND itr.equipment_pk = stg.equipment_fk
2880 AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
2881 AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date));
2882 mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2883
2884 -- Validation for MSF
2885 UPDATE mth_equip_statuses_stg stg
2886 SET stg.err_code = stg.err_code || 'MSF '
2887 WHERE EXISTS ( SELECT * FROM (
2888 SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
2889 FROM mth_workday_shifts_d mds,
2890 mth_equip_statuses_stg stg,
2891 mth_equipment_shifts_d mes,
2892 mth_equipments_d med
2893 WHERE stg.shift_workday_fk = mds.shift_workday_pk
2894 AND stg.equipment_fk = med.equipment_pk
2895 AND mds.shift_workday_pk_key = mes.shift_workday_fk_key
2896 AND med.equipment_pk_key = mes.equipment_fk_key
2897 AND (((stg.from_date BETWEEN mes.from_date AND mes.To_Date)
2898 AND (stg.to_date NOT BETWEEN mes.from_date AND mes.To_Date))
2899 OR ((stg.from_date NOT BETWEEN mes.from_date AND mes.To_Date)
2900 AND (stg.to_date BETWEEN mes.from_date AND mes.To_Date)))
2901 AND Upper(mes.entity_type) = 'EQUIPMENT') msf
2902 WHERE msf.shift_workday_pk = stg.shift_workday_fk
2903 AND msf.equipment_pk = stg.equipment_fk
2904 AND (((stg.from_date BETWEEN msf.from_date AND msf.To_Date)
2905 AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
2906 OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
2907 AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date))));
2908 mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2909
2910 --Validation for OCSV
2911 UPDATE mth_equip_statuses_stg stag
2912 SET stag.err_code = stag.err_code || 'OCSV '
2913 WHERE EXISTS (SELECT *
2914 FROM
2915 (SELECT CASE
2916 WHEN (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
2917 AND (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)) IS NOT NULL
2918 AND ((stg.from_date >= (LAG(stg.from_date) OVER ( PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
2919 AND
2920 stg.from_date <= (LAG (stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date)))
2921 OR
2922 (stg.to_date >= (LAG(stg.from_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date))
2923 AND
2924 stg.to_date <= (LAG(stg.to_date) OVER (PARTITION BY med.equipment_pk_key ORDER BY stg.from_date )))
2925 )
2926 THEN 1 END overlap,
2927 med.equipment_pk,
2928 stg.from_date,
2929 stg.to_date
2930 FROM mth_equip_statuses_stg stg,
2931 mth_equipments_d med
2932 WHERE stg.equipment_fk = med.equipment_pk ) ovp
2933 WHERE ovp.overlap = 1
2934 AND stag.equipment_fk = ovp.equipment_pk
2935 AND stag.from_date = ovp.from_date
2936 AND stag.To_Date = ovp.To_Date);
2937 mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2938
2939 --validation for OSTS
2940 UPDATE mth_equip_statuses_stg stag
2941 SET stag.err_code = stag.err_code || 'OSTS '
2942 WHERE EXISTS (SELECT *
2943 FROM ( SELECT CASE
2944 WHEN (stg.FROM_DATE >= sts.FROM_DATE
2945 AND stg.FROM_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
2946 OR (stg.TO_DATE >= sts.FROM_DATE
2947 AND stg.TO_DATE <= NVL(sts.TO_DATE,stg.FROM_DATE-1/86400))
2948 OR (stg.FROM_DATE < sts.FROM_DATE
2949 AND stg.TO_DATE > NVL(sts.TO_DATE,stg.TO_DATE+1/86400))
2950 THEN 1 END overlap ,
2951 med.equipment_pk,
2952 wds.shift_workday_pk,
2953 stg.from_date,
2954 stg.to_date
2955 FROM mth_equip_statuses_stg stg,
2956 mth_equip_statuses sts,
2957 mth_equipments_d med,
2958 mth_workday_shifts_d wds
2959 WHERE stg.equipment_fk = med.equipment_pk
2960 AND stg.shift_workday_fk = wds.shift_workday_pk
2961 AND med.equipment_pk_key = sts.equipment_fk_key
2962 AND wds.shift_workday_pk_key = sts.shift_workday_fk_key) osts
2963 WHERE osts.overlap = 1
2964 AND stag.equipment_fk = osts.equipment_pk
2965 AND stag.shift_workday_fk = osts.shift_workday_pk
2966 AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
2967 mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2968
2969 --validation for WRC
2970 UPDATE mth_equip_statuses_stg stg
2971 SET stg.err_code = stg.err_code ||'WRC '
2972 WHERE NOT EXISTS ( SELECT *
2973 FROM (SELECT stg.*
2974 FROM mth_equip_statuses_stg stg,
2975 MTH_EQUIPMENT_REASON_SETUP mer,
2976 mth_equipments_d med
2977 WHERE stg.equipment_fk = med.equipment_pk
2978 AND med.equipment_pk_key = mer.equipment_fk_key
2979 AND stg.downtime_reason_code = mer.reason_code
2980 AND stg.status IN (3,2)
2981 AND stg.downtime_reason_code IS NOT NULL) ers
2982 WHERE ers.equipment_fk = stg.equipment_fk
2983 AND ers.downtime_reason_code = stg.downtime_reason_code
2984 AND ers.from_date = stg.from_date
2985 AND Nvl(ers.To_Date,SYSDATE) = Nvl(stg.To_Date,SYSDATE)
2986 AND ers.shift_workday_fk = stg.shift_workday_fk
2987 )
2988 AND stg.status IN (3,2)
2989 AND stg.downtime_reason_code IS NOT NULL;
2990 mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2991
2992 --validation for STS
2993 UPDATE mth_equip_statuses_stg stg
2994 SET stg.err_code = stg.err_code ||'STS '
2995 WHERE stg.status NOT IN (1,2,3,4);
2996 mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2997
2998 /*--Insert records into mth_equip_statuses_err
2999 INSERT INTO mth_equip_statuses_err(equipment_fk,
3000 shift_workday_fk,
3001 from_date,
3002 To_Date,
3003 status,
3004 system_fk,
3005 reprocess_ready_yn,
3006 user_dim1_fk,
3007 user_dim2_fk,
3008 user_dim3_fk,
3009 user_dim4_fk,
3010 user_dim5_fk,
3011 user_attr1,
3012 user_attr2,
3013 user_attr3,
3014 user_attr4,
3015 user_attr5,
3016 user_measure1,
3017 user_measure2,
3018 user_measure3,
3019 user_measure4,
3020 user_measure5,
3021 err_code,
3022 downtime_reason_code)
3023 (SELECT equipment_fk,
3024 shift_workday_fk,
3025 from_date,
3026 To_Date,
3027 status,
3028 system_fk,
3029 'N',
3030 user_dim1_fk,
3031 user_dim2_fk,
3032 user_dim3_fk,
3033 user_dim4_fk,
3034 user_dim5_fk,
3035 user_attr1,
3036 user_attr2,
3037 user_attr3,
3038 user_attr4,
3039 user_attr5,
3040 user_measure1,
3041 user_measure2,
3042 user_measure3,
3043 user_measure4,
3044 user_measure5,
3045 err_code,
3046 downtime_reason_code
3047 FROM mth_equip_statuses_stg
3048 WHERE err_code IS NOT NULL);
3049 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);*/
3050
3051 OPEN c_error_rows;
3052 LOOP
3053 FETCH c_error_rows BULK COLLECT INTO l_err_rows;
3054 EXIT WHEN c_error_rows%NOTFOUND;
3055 END LOOP;
3056 CLOSE c_error_rows;
3057
3058 IF l_err_rows.COUNT > 0
3059 THEN
3060 mth_util_pkg.log_msg('Following are the errored records in Status CSV Recalculation',mth_util_pkg.G_DBG_USER_ERROR);
3061 mth_util_pkg.log_msg('EQUIPMENT_FK,SHIFT_WORKDAY_FK,FROM_DATE,TO_DATE,STATUS,USER_DIM1_FK,USER_DIM2_FK,USER_DIM3_FK,USER_DIM4_FK,USER_DIM5_FK,DOWNTIME_REASON_CODE,ERR_CODE',mth_util_pkg.G_DBG_USER_ERROR);
3062 FOR i IN l_err_rows.FIRST..l_err_rows.LAST
3063 LOOP
3064 mth_util_pkg.log_msg(l_err_rows(i).equipment_fk||','||l_err_rows(i).shift_workday_fk||','||fnd_date.date_to_canonical(l_err_rows(i).from_date)||','||
3065 fnd_date.date_to_canonical(l_err_rows(i).to_date)||','||l_err_rows(i).status||','||l_err_rows(i).user_dim1_fk||','||
3066 l_err_rows(i).user_dim2_fk||','||l_err_rows(i).user_dim3_fk||','||l_err_rows(i).user_dim4_fk||','||
3067 l_err_rows(i).user_dim5_fk||','||l_err_rows(i).downtime_reason_code||','||l_err_rows(i).err_code,mth_util_pkg.G_DBG_USER_ERROR);
3068 END LOOP;
3069 RAISE MTH_PROCESS_TXN_PKG.VALIDATION_ERR;
3070 END IF;
3071
3072 --Insert records into mth_equip_statuses table
3073 INSERT INTO mth_equip_statuses( equipment_fk_key,
3074 shift_workday_fk_key,
3075 from_date,
3076 To_Date,
3077 status,
3078 system_fk_key,
3079 user_dim1_fk_key,
3080 user_dim2_fk_key,
3081 user_dim3_fk_key,
3082 user_dim4_fk_key,
3083 user_dim5_fk_key,
3084 user_attr1 ,
3085 user_attr2 ,
3086 user_attr3 ,
3087 user_attr4 ,
3088 user_attr5 ,
3089 user_measure1 ,
3090 user_measure2 ,
3091 user_measure3 ,
3092 user_measure4 ,
3093 user_measure5 ,
3094 creation_date,
3095 last_update_date,
3096 creation_system_id,
3097 last_update_system_id,
3098 created_by,
3099 last_updated_by,
3100 last_update_login,
3101 expected_up_time,
3102 status_type,
3103 hour_fk_key,
3104 reading_time )
3105 (SELECT med.EQUIPMENT_PK_KEY ,
3106 wds.SHIFT_WORKDAY_PK_KEY ,
3107 Greatest(stg.from_date,mhd.from_time),
3108 Least(stg.To_Date,mhd.to_time) ,
3109 stg.STATUS ,
3110 Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) ,
3111 mue1.ENTITY_PK_KEY ,
3112 mue2.ENTITY_PK_KEY ,
3113 mue3.ENTITY_PK_KEY ,
3114 mue4.ENTITY_PK_KEY ,
3115 mue5.ENTITY_PK_KEY ,
3116 stg.USER_ATTR1 ,
3117 stg.USER_ATTR2 ,
3118 stg.USER_ATTR3 ,
3119 stg.USER_ATTR4 ,
3120 stg.USER_ATTR5 ,
3121 stg.USER_MEASURE1 ,
3122 stg.USER_MEASURE2 ,
3123 stg.USER_MEASURE3 ,
3124 stg.USER_MEASURE4 ,
3125 stg.USER_MEASURE5 ,
3126 v_log_date,
3127 v_log_date,
3128 v_unassigned_val,
3129 v_unassigned_val,
3130 null,
3131 null,
3132 null,
3133 null,
3134 null,
3135 mhd.HOUR_PK_KEY,
3136 stg.from_date
3137 FROM mth_equip_statuses_stg stg,
3138 mth_equipments_d med,
3139 mth_workday_shifts_d wds,
3140 mth_systems_setup mss,
3141 mth_user_dim_entities_mst mue1,
3142 mth_user_dim_entities_mst mue2,
3143 mth_user_dim_entities_mst mue3,
3144 mth_user_dim_entities_mst mue4,
3145 mth_user_dim_entities_mst mue5,
3146 fnd_lookups lkp,
3147 mth_hour_d mhd
3148 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
3149 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
3150 AND mhd.to_time >= stg.from_date
3151 AND mhd.from_time <= stg.to_date
3152 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
3153 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
3154 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
3155 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
3156 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
3157 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
3158 AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
3159 AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+)
3160 AND err_code IS NULL
3161 UNION ALL
3162 SELECT med.EQUIPMENT_PK_KEY ,
3163 wds.SHIFT_WORKDAY_PK_KEY ,
3164 Greatest(stg.from_date,mhd.from_time),
3165 stg.To_Date,
3166 stg.STATUS ,
3167 Nvl(mss.SYSTEM_PK_KEY,v_unassigned_val) ,
3168 mue1.ENTITY_PK_KEY ,
3169 mue2.ENTITY_PK_KEY ,
3170 mue3.ENTITY_PK_KEY ,
3171 mue4.ENTITY_PK_KEY ,
3172 mue5.ENTITY_PK_KEY ,
3173 stg.USER_ATTR1 ,
3174 stg.USER_ATTR2 ,
3175 stg.USER_ATTR3 ,
3176 stg.USER_ATTR4 ,
3177 stg.USER_ATTR5 ,
3178 stg.USER_MEASURE1 ,
3179 stg.USER_MEASURE2 ,
3180 stg.USER_MEASURE3 ,
3181 stg.USER_MEASURE4 ,
3182 stg.USER_MEASURE5 ,
3183 v_log_date,
3184 v_log_date,
3185 v_unassigned_val,
3186 v_unassigned_val,
3187 null,
3188 null,
3189 null,
3190 null,
3191 null,
3192 mhd.HOUR_PK_KEY,
3193 stg.from_date
3194 FROM mth_equip_statuses_stg stg,
3195 mth_equipments_d med,
3196 mth_workday_shifts_d wds,
3197 mth_systems_setup mss,
3198 mth_user_dim_entities_mst mue1,
3199 mth_user_dim_entities_mst mue2,
3200 mth_user_dim_entities_mst mue3,
3201 mth_user_dim_entities_mst mue4,
3202 mth_user_dim_entities_mst mue5,
3203 fnd_lookups lkp,
3204 mth_hour_d mhd
3205 WHERE stg.EQUIPMENT_FK = med.EQUIPMENT_PK (+)
3206 AND stg.SHIFT_WORKDAY_FK = wds.SHIFT_WORKDAY_PK (+)
3207 AND stg.from_date BETWEEN mhd.from_time AND mhd.to_time
3208 AND stg.To_Date IS NULL
3209 AND NVL (stg.SYSTEM_FK , v_unassigned_val) = mss.SYSTEM_PK (+)
3210 AND stg.USER_DIM1_FK = mue1.ENTITY_PK (+)
3211 AND stg.USER_DIM2_FK = mue2.ENTITY_PK (+)
3212 AND stg.USER_DIM3_FK = mue3.ENTITY_PK (+)
3213 AND stg.USER_DIM4_FK = mue4.ENTITY_PK (+)
3214 AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
3215 AND lkp.LOOKUP_TYPE (+) = 'MTH_EQUIP_DOWNTIME_REASON'
3216 AND stg.DOWNTIME_REASON_CODE = lkp.LOOKUP_CODE (+) AND stg.err_code IS NULL );
3217 mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
3218 v_count := SQL%ROWCOUNT;
3219
3220 --Insertion of records in tag reason readings
3221 INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
3222 EQUIPMENT_FK_KEY,
3223 FROM_DATE,
3224 To_Date,
3225 REASON_CODE,
3226 CREATION_DATE,
3227 LAST_UPDATE_DATE,
3228 CREATION_SYSTEM_ID,
3229 LAST_UPDATE_SYSTEM_ID,
3230 CREATED_BY,
3231 LAST_UPDATE_LOGIN,
3232 LAST_UPDATED_BY,
3233 READING_TIME,
3234 HOUR_FK_KEY)
3235 (SELECT 1 reason_type,
3236 sts.equipment_fk_key,
3237 sts.from_date,
3238 sts.To_Date,
3239 stg.downtime_reason_code,
3240 v_log_date,
3241 v_log_date,
3242 v_unassigned_val,
3243 v_unassigned_val,
3244 NULL,
3245 NULL,
3246 null,
3247 sts.reading_time,
3248 sts.hour_fk_key
3249 FROM mth_equip_statuses_stg stg,
3250 mth_equip_statuses sts
3251 WHERE stg.from_date = sts.reading_time
3252 AND sts.status = stg.status
3253 AND stg.status = 3
3254 AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
3255 FROM mth_equipments_d
3256 WHERE equipment_pk = stg.equipment_fk )
3257 AND stg.err_code IS NULL
3258 UNION
3259 SELECT 3 reason_type,
3260 sts.equipment_fk_key,
3261 sts.from_date,
3262 sts.To_Date,
3263 stg.downtime_reason_code,
3264 v_log_date,
3265 v_log_date,
3266 v_unassigned_val,
3267 v_unassigned_val,
3268 NULL,
3269 NULL,
3270 null,
3271 sts.reading_time,
3272 sts.hour_fk_key
3273 FROM mth_equip_statuses_stg stg,
3274 mth_equip_statuses sts
3275 WHERE stg.from_date = sts.reading_time
3276 AND sts.status = stg.status
3277 AND stg.status = 2
3278 AND sts.equipment_fk_key IN ( SELECT equipment_pk_key
3279 FROM mth_equipments_d
3280 WHERE equipment_pk = stg.equipment_fk )
3281 AND stg.err_code IS NULL
3282 );
3283 mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
3284
3285 mth_util_pkg.log_msg('RECAL_STATUS_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
3286 EXCEPTION
3287 WHEN SITE_MIS_ERR THEN
3288 --Call logging API and then throw exception
3289 mth_util_pkg.log_msg('Exception SITE_MIS_ERR in RECAL_STATUS_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
3290 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3291 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3292 RAISE;
3293 WHEN EQUIP_MIS_ERR THEN
3294 --Call logging API and then throw exception
3295 mth_util_pkg.log_msg('Exception EQUIP_MIS_ERR in RECAL_STATUS_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
3296 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3297 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3298 RAISE;
3299 WHEN GAP_IN_RECAL_CSV THEN
3300 --Call logging API and then throw exception
3301 mth_util_pkg.log_msg('Exception GAP_IN_RECAL_CSV in RECAL_STATUS_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
3302 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3303 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3304 RAISE;
3305 WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
3306 --Call logging API and then throw exception
3307 mth_util_pkg.log_msg('Exception VALIDATION_ERR in RECAL_STATUS_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
3308 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3309 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3310 RAISE;
3311 WHEN OTHERS THEN
3312 --Call logging API and then throw exception
3313 mth_util_pkg.log_msg('Exception OTHERS in RECAL_STATUS_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
3314 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3315 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3316 RAISE;
3317 END;
3318
3319 /*******************************************************************************
3320 * Procedure :PROCESS_STATUS_SUMMARY *
3321 * Description :This procedure is the main procedure for status *
3322 * summary
3323 * File Name :MTHEQSTB.PLS *
3324 * Visibility :Public *
3325 * Parameters : p_fact_name : name of the fact table *
3326 * p_from_date : from_date for the run *
3327 * p_to_date : to_date for the run *
3328 *******************************************************************************/
3329 PROCEDURE PROCESS_STATUS_SUMMARY(p_mode IN VARCHAR2, --INIT,INCR,RECAL
3330 p_recal_from_date IN TIMESTAMP , --Recalculation from date
3331 p_recal_to_date IN TIMESTAMP DEFAULT NULL, --Recalculation to date
3332 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
3333 p_plant_pk_key IN NUMBER DEFAULT NULL , --Plant to recalculate
3334 p_ret_code OUT NOCOPY NUMBER
3335 )
3336 IS
3337 l_ret_code NUMBER;
3338 BEGIN
3339 l_ret_code := 0;
3340 mth_util_pkg.log_msg('PROCESS_STATUS_SUMMARY start', mth_util_pkg.G_DBG_PROC_FUN_START);
3341 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
3342 mth_util_pkg.log_msg('p_recal_from_date = ' || to_char(p_recal_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
3343 mth_util_pkg.log_msg('p_recal_to_date = ' || to_char(p_recal_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
3344 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
3345
3346 IF p_mode = 'INIT' THEN
3347 PROCESS_STATUS_SMMRY_INIT();
3348 ELSIF p_mode = 'INCR' THEN
3349 PROCESS_STATUS_SMMRY_INCR();
3350 ELSE
3351 PROCESS_STATUS_SMMRY_RECAL(
3352 p_recal_from_date,
3353 p_recal_to_date,
3354 p_equipment_pk_key,
3355 p_plant_pk_key
3356 );
3357 END IF;
3358
3359 p_ret_code := l_ret_code;
3360 mth_util_pkg.log_msg('PROCESS_STATUS_SUMMARY end', mth_util_pkg.G_DBG_PROC_FUN_END);
3361
3362 EXCEPTION
3363 WHEN OTHERS THEN
3364 l_ret_code := 2;
3365 p_ret_code := l_ret_code;
3366 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_STATUS_SUMMARY', mth_util_pkg.G_DBG_EXCEPTION);
3367 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3368 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3369 RAISE;
3370 END PROCESS_STATUS_SUMMARY;
3371
3372 /*******************************************************************************
3373 * Procedure :PROCESS_STATUS_SMMRY_INIT *
3374 * Description :This procedure is used for calculating the status *
3375 * summary from status in INIT mode *
3376 * File Name :MTHEQSTB.PLS *
3377 * Visibility :Private *
3378 * Parameters : *
3379 *******************************************************************************/
3380 PROCEDURE PROCESS_STATUS_SMMRY_INIT IS
3381 v_log_date DATE;
3382 v_ua_val VARCHAR2(30);
3383 v_run_log_to_date DATE;
3384 v_run_log_from_date DATE;
3385 BEGIN
3386 mth_util_pkg.log_msg('PROCESS_STATUS_SMMRY_INIT start', mth_util_pkg.G_DBG_PROC_FUN_START);
3387 -- Initialize default parameters
3388 v_log_date := sysdate;
3389 v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3390
3391 -- Call mth_run_log_pre_load
3392 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_STATUS_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);
3393
3394 DELETE FROM MTH_EQUIP_STATUS_SUMMARY;
3395 mth_util_pkg.log_msg('Number of rows deleted from status summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
3396
3397 -- Get to and from date from run log
3398 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_STATUS_SUMMARY',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
3399
3400 -- Process data from status to be inserted to status summary
3401 INSERT
3402 INTO
3403 MTH_EQUIP_STATUS_SUMMARY( EQUIPMENT_FK_KEY,
3404 SHIFT_WORKDAY_FK_KEY,
3405 HOUR_FK_KEY,
3406 RUN_HOURS,
3407 IDLE_HOURS,
3408 DOWN_HOURS,
3409 UP_HOURS,
3410 OFF_HOURS,
3411 SYSTEM_FK_KEY,
3412 CREATION_DATE,
3413 LAST_UPDATE_DATE,
3414 CREATION_SYSTEM_ID,
3415 LAST_UPDATE_SYSTEM_ID,
3416 RESOURCE_FK_KEY,
3417 RESOURCE_COST)
3418 (SELECT eqsts.equipment_fk_key,
3419 eqsts.shift_workday_fk_key,
3420 eqsts.hour_fk_key,
3421 Sum(Decode (eqsts.status,1,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) run_hours,
3422 Sum(Decode (eqsts.status,2,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) idle_hours,
3423 Sum(Decode (eqsts.status,3,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) down_hours,
3424 Sum((CASE WHEN (eqsts.status = 1 OR eqsts.status = 2) THEN ((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600)
3425 ELSE 0 END)) up_hours,
3426 Sum(Decode (eqsts.status,4,((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) off_hours,
3427 v_ua_val,
3428 v_log_date,
3429 v_log_date,
3430 v_ua_val,
3431 v_ua_val,
3432 Min(med.level9_level_key) resource_fk_key,
3433 Min(mrc.cost) resource_cost
3434 FROM mth_equip_statuses eqsts,
3435 mth_hour_d msg,
3436 mth_equipment_denorm_d med,
3437 mth_resource_cost_mv mrc
3438 WHERE med.equipment_hierarchy_key = -2
3439 AND med.equipment_fk_key IS NOT NULL
3440 AND msg.from_time BETWEEN med.equipment_effective_date
3441 AND Nvl(med.equipment_expiration_date , msg.from_time)
3442 AND med.equipment_fk_key = eqsts.equipment_fk_key
3443 AND eqsts.hour_fk_key = msg.hour_pk_key
3444 AND med.level9_level_key = mrc.resource_fk_key(+)
3445 AND eqsts.last_update_date <= v_run_log_to_date
3446 GROUP BY eqsts.equipment_fk_key,
3447 eqsts.shift_workday_fk_key,
3448 eqsts.hour_fk_key);
3449
3450 -- Call the logging API to log the number of rows inserted
3451 mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3452
3453 -- Call mth_run_log_post_load
3454 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_STATUS_SUMMARY',v_ua_val);
3455
3456 mth_util_pkg.log_msg('PROCESS_STATUS_SMMRY_INIT end', mth_util_pkg.G_DBG_PROC_FUN_END);
3457 EXCEPTION
3458 WHEN OTHERS THEN
3459 --Call logging API and then throw exception
3460 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_STATUS_SMMRY_INIT', mth_util_pkg.G_DBG_EXCEPTION);
3461 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3462 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3463 RAISE;
3464 END;
3465
3466 /*******************************************************************************
3467 * Procedure :PROCESS_STATUS_SMMRY_INCR *
3468 * Description :This procedure is used for calculating the status *
3469 * summary from status in INCR mode *
3470 * File Name :MTHEQSTB.PLS *
3471 * Visibility :Private *
3472 * Parameters : *
3473 *******************************************************************************/
3474 PROCEDURE PROCESS_STATUS_SMMRY_INCR IS
3475 v_log_date DATE;
3476 v_ua_val VARCHAR2(30);
3477 v_run_log_to_date DATE;
3478 v_run_log_from_date DATE;
3479 BEGIN
3480 mth_util_pkg.log_msg('PROCESS_STATUS_SMMRY_INCR start', mth_util_pkg.G_DBG_PROC_FUN_START);
3481
3482 -- Initialize default parameters
3483 v_log_date := sysdate;
3484 v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3485
3486 -- Call mth_run_log_pre_load
3487 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_STATUS_SUMMARY',v_ua_val,'INCR',NULL,0,v_log_date);
3488
3489 -- Get to and from date from run log
3490 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_STATUS_SUMMARY',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
3491
3492 MERGE
3493 INTO
3494 MTH_EQUIP_STATUS_SUMMARY mes
3495 USING
3496 ( SELECT eqsts.equipment_fk_key,
3497 eqsts.shift_workday_fk_key,
3498 eqsts.hour_fk_key,
3499 Sum(Decode (eqsts.status,1,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) run_hours,
3500 Sum(Decode (eqsts.status,2,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) idle_hours,
3501 Sum(Decode (eqsts.status,3,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) down_hours,
3502 Sum((CASE WHEN (eqsts.status = 1 OR eqsts.status = 2) THEN ((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600)
3503 ELSE 0 END)) up_hours,
3504 Sum(Decode (eqsts.status,4,((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) off_hours,
3505 v_ua_val system_fk_key,
3506 v_log_date creation_date,
3507 v_log_date last_update_date,
3508 v_ua_val creation_system_id,
3509 v_ua_val last_update_system_id,
3510 Min(med.level9_level_key) resource_fk_key,
3511 Min(mrc.cost) resource_cost
3512 FROM mth_equip_statuses eqsts,
3513 mth_hour_d msg,
3514 mth_equipment_denorm_d med,
3515 mth_resource_cost_mv mrc
3516 WHERE med.equipment_hierarchy_key = -2
3517 AND med.equipment_fk_key IS NOT NULL
3518 AND msg.from_time BETWEEN med.equipment_effective_date
3519 AND Nvl(med.equipment_expiration_date , msg.from_time)
3520 AND med.equipment_fk_key = eqsts.equipment_fk_key
3521 AND eqsts.hour_fk_key = msg.hour_pk_key
3522 AND med.level9_level_key = mrc.resource_fk_key(+)
3523 AND eqsts.hour_fk_key IN ( SELECT hour_fk_key
3524 FROM mth_equip_statuses
3525 WHERE last_update_date > v_run_log_from_date
3526 AND last_update_date <= v_run_log_to_date )
3527 GROUP BY eqsts.equipment_fk_key,
3528 eqsts.shift_workday_fk_key,
3529 eqsts.hour_fk_key) statrec
3530 ON (
3531 mes.equipment_fk_key = statrec.equipment_fk_key
3532 AND mes.shift_workday_fk_key = statrec.shift_workday_fk_key
3533 AND mes.hour_fk_key = statrec.hour_fk_key
3534 )
3535
3536 WHEN MATCHED THEN
3537 UPDATE
3538 SET
3539 mes.up_hours = statrec.up_hours,
3540 mes.down_hours = statrec.down_hours,
3541 mes.run_hours = statrec.run_hours,
3542 mes.idle_hours = statrec.idle_hours,
3543 mes.off_hours = statrec.off_hours,
3544 mes.system_fk_key = statrec.system_fk_key,
3545 mes.last_update_date = statrec.last_update_date,
3546 mes.last_update_system_id = statrec.last_update_system_id,
3547 mes.resource_fk_key = statrec.resource_fk_key,
3548 mes.resource_cost = statrec.resource_cost
3549
3550
3551 WHEN NOT MATCHED THEN
3552 INSERT
3553 (mes.equipment_fk_key,
3554 mes.shift_workday_fk_key,
3555 mes.hour_fk_key,
3556 mes.up_hours,
3557 mes.down_hours,
3558 mes.run_hours,
3559 mes.idle_hours,
3560 mes.off_hours,
3561 mes.system_fk_key,
3562 mes.creation_date,
3563 mes.last_update_date,
3564 mes.creation_system_id,
3565 mes.last_update_system_id,
3566 mes.resource_fk_key,
3567 mes.resource_cost)
3568 VALUES
3569 (statrec.equipment_fk_key,
3570 statrec.shift_workday_fk_key,
3571 statrec.hour_fk_key,
3572 statrec.up_hours,
3573 statrec.down_hours,
3574 statrec.run_hours,
3575 statrec.idle_hours,
3576 statrec.off_hours,
3577 statrec.system_fk_key,
3578 statrec.creation_date,
3579 statrec.last_update_date,
3580 statrec.creation_system_id,
3581 statrec.last_update_system_id,
3582 statrec.resource_fk_key,
3583 statrec.resource_cost)
3584 ;
3585 -- Call the logging API to log the number of rows merged
3586 mth_util_pkg.log_msg('Rows merged in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3587
3588 -- Call mth_run_log_post_load
3589 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_STATUS_SUMMARY',v_ua_val);
3590
3591 mth_util_pkg.log_msg('PROCESS_STATUS_SMMRY_INCR end', mth_util_pkg.G_DBG_PROC_FUN_END);
3592
3593 EXCEPTION
3594 WHEN OTHERS THEN
3595 --Call logging API and then throw exception
3596 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_STATUS_SMMRY_INCR', mth_util_pkg.G_DBG_EXCEPTION);
3597 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3598 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3599 RAISE;
3600 END;
3601
3602 /*******************************************************************************
3603 * Procedure :PROCESS_STATUS_SMMRY_RECAL *
3604 * Description :This procedure is used for calculating the status *
3605 * summary from status in RECAL mode *
3606 * File Name :MTHEQSTB.PLS *
3607 * Visibility :Private *
3608 * Parameters : p_recal_from_date : Recalculation from date *
3609 * p_recal_to_date : Recalculation to date *
3610 * p_equipment_pk_key : Equipment to recalculate *
3611 *******************************************************************************/
3612 PROCEDURE PROCESS_STATUS_SMMRY_RECAL(p_recalc_from_date IN TIMESTAMP,
3613 p_recalc_to_date IN TIMESTAMP,
3614 p_recalc_equip_key IN NUMBER,
3615 p_recalc_plant_key IN NUMBER)
3616 IS
3617 v_log_date DATE;
3618 v_ua_val VARCHAR2(30);
3619 v_run_log_to_date TIMESTAMP;
3620 v_run_log_from_date TIMESTAMP;
3621 p_n_recalc_from_date TIMESTAMP;
3622 p_n_recalc_to_date TIMESTAMP;
3623 v_recalc_to_date TIMESTAMP;
3624 v_status_from_date TIMESTAMP;
3625 v_status_to_date TIMESTAMP;
3626
3627 /*CURSOR c_fetch_from_date
3628 IS
3629 SELECT Min(from_time)
3630 FROM mth_hour_d
3631 WHERE (SELECT Min(Max(reading_time))
3632 FROM mth_tag_readings
3633 WHERE reading_time < p_recalc_from_date
3634 GROUP BY equipment_fk_key) BETWEEN from_time AND to_time;*/
3635
3636 CURSOR c_fetch_from_date
3637 IS
3638 SELECT Min(from_time)
3639 FROM mth_hour_d
3640 WHERE (SELECT Min(from_date) reading_time
3641 FROM mth_equip_statuses_stg stg,
3642 mth_equipments_d eq
3643 WHERE stg.equipment_fk = eq.equipment_pk
3644 AND eq.equipment_pk_key = nvl(p_recalc_equip_key,eq.equipment_pk_key)
3645 AND eq.equipment_pk_key IN (SELECT equipment_pk_key
3646 FROM mth_equipments_d
3647 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
3648 GROUP BY eq.equipment_pk_key )
3649 BETWEEN from_time AND to_time;
3650
3651 /*CURSOR c_fetch_to_date
3652 IS
3653 SELECT Max(to_time)
3654 FROM mth_hour_d
3655 WHERE (SELECT Max(Min(reading_time))
3656 FROM mth_tag_readings
3657 WHERE reading_time > v_recalc_to_date
3658 GROUP BY equipment_fk_key) BETWEEN from_time AND to_time;*/
3659
3660 CURSOR c_fetch_to_date
3661 IS
3662 SELECT Max(to_time)
3663 FROM mth_hour_d
3664 WHERE (SELECT Max(to_date) reading_time
3665 FROM mth_equip_statuses_stg stg,
3666 mth_equipments_d eq
3667 WHERE stg.equipment_fk = eq.equipment_pk
3668 AND eq.equipment_pk_key = nvl(p_recalc_equip_key,eq.equipment_pk_key)
3669 AND eq.equipment_pk_key IN (SELECT equipment_pk_key
3670 FROM mth_equipments_d
3671 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
3672 GROUP BY eq.equipment_pk_key )
3673 BETWEEN from_time AND to_time;
3674
3675 CURSOR c_fetch_recalc_to_date(v_recalc_equip_key IN NUMBER)
3676 IS
3677 SELECT Max(from_date),
3678 Max(To_Date)
3679 FROM MTH_EQUIP_STATUSES
3680 WHERE equipment_fk_key = nvl(v_recalc_equip_key,equipment_fk_key);
3681
3682
3683 BEGIN
3684 mth_util_pkg.log_msg('PROCESS_STATUS_SMMRY_RECAL start', mth_util_pkg.G_DBG_PROC_FUN_START);
3685 mth_util_pkg.log_msg('p_recalc_from_date = ' || to_char(p_recalc_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
3686 mth_util_pkg.log_msg('p_recalc_to_date = ' || to_char(p_recalc_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
3687 mth_util_pkg.log_msg('p_recalc_equip_key = ' || p_recalc_equip_key, mth_util_pkg.G_DBG_PARAM_VAL);
3688
3689 -- Fetch from date for the p_recalc_from_date
3690 OPEN c_fetch_from_date;
3691
3692 FETCH c_fetch_from_date INTO p_n_recalc_from_date;
3693 mth_util_pkg.log_msg('Hour from which recalculation has to be done p_n_recalc_from_date : '||p_n_recalc_from_date, mth_util_pkg.G_DBG_PARAM_VAL);
3694
3695 CLOSE c_fetch_from_date;
3696
3697 IF p_recalc_to_date IS NULL
3698 THEN
3699 -- Fetch to date for the p_recalc_to_date
3700 OPEN c_fetch_recalc_to_date(p_recalc_equip_key);
3701
3702 FETCH c_fetch_recalc_to_date INTO v_status_from_date,v_status_to_date;
3703 v_recalc_to_date := Greatest(v_status_from_date,v_status_to_date);
3704
3705 CLOSE c_fetch_recalc_to_date;
3706 ELSE
3707 v_recalc_to_date := p_recalc_to_date;
3708 END IF;
3709
3710 -- Fetch to date for the p_recalc_to_date
3711 OPEN c_fetch_to_date;
3712
3713 FETCH c_fetch_to_date INTO p_n_recalc_to_date;
3714 mth_util_pkg.log_msg('Hour till which recalculation has to be done p_n_recalc_to_date : '||p_n_recalc_to_date, mth_util_pkg.G_DBG_PARAM_VAL);
3715
3716 CLOSE c_fetch_to_date;
3717
3718 -- Initialize default parameters
3719 v_log_date := sysdate;
3720 v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3721
3722 -- Call mth_run_log_pre_load
3723 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_STATUS_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);
3724
3725 -- Call delete status summary to delete all data from MTH_EQUIP_STATUS_SUMMARY table as well update status summary table
3726 -- for particular hour fk keys that contains the recalculation from and to dates
3727 IF (p_recalc_plant_key IS NULL ) THEN
3728 DELETE MTH_EQUIP_STATUS_SUMMARY
3729 WHERE hour_fk_key IN (SELECT hour_pk_key
3730 FROM mth_hour_d
3731 WHERE from_time >= p_n_recalc_from_date
3732 AND to_time <= p_n_recalc_to_date)
3733 AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key);
3734 ELSE
3735 DELETE MTH_EQUIP_STATUS_SUMMARY
3736 WHERE hour_fk_key IN (SELECT hour_pk_key
3737 FROM mth_hour_d
3738 WHERE from_time >= p_n_recalc_from_date
3739 AND to_time <= p_n_recalc_to_date)
3740 AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key)
3741 AND equipment_fk_key IN (SELECT equipment_pk_key
3742 FROM mth_equipments_d
3743 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key));
3744 END IF;
3745
3746 -- Call the logging API to log the number of rows inserted
3747 mth_util_pkg.log_msg('Rows deleted from MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3748
3749 -- Process data from status to be recalculated and inserted to status summary
3750 INSERT
3751 INTO
3752 MTH_EQUIP_STATUS_SUMMARY( EQUIPMENT_FK_KEY,
3753 SHIFT_WORKDAY_FK_KEY,
3754 HOUR_FK_KEY,
3755 RUN_HOURS,
3756 IDLE_HOURS,
3757 DOWN_HOURS,
3758 UP_HOURS,
3759 OFF_HOURS,
3760 SYSTEM_FK_KEY,
3761 CREATION_DATE,
3762 LAST_UPDATE_DATE,
3763 CREATION_SYSTEM_ID,
3764 LAST_UPDATE_SYSTEM_ID,
3765 RESOURCE_FK_KEY,
3766 RESOURCE_COST)
3767 (SELECT eqsts.equipment_fk_key,
3768 eqsts.shift_workday_fk_key,
3769 eqsts.hour_fk_key,
3770 Sum(Decode (eqsts.status,1,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) run_hours,
3771 Sum(Decode (eqsts.status,2,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) idle_hours,
3772 Sum(Decode (eqsts.status,3,((Least(Nvl(eqsts.to_date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) down_hours,
3773 Sum((CASE WHEN (eqsts.status = 1 OR eqsts.status = 2) THEN ((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600)
3774 ELSE 0 END)) up_hours,
3775 Sum(Decode (eqsts.status,4,((Least(Nvl(eqsts.To_Date,SYSDATE),msg.to_time) - eqsts.from_date)*24)+(1/3600),0)) off_hours,
3776 v_ua_val,
3777 v_log_date,
3778 v_log_date,
3779 v_ua_val,
3780 v_ua_val,
3781 Min(med.level9_level_key) resource_fk_key,
3782 Min(mrc.cost) resource_cost
3783 FROM mth_equip_statuses eqsts,
3784 mth_hour_d msg,
3785 mth_equipment_denorm_d med,
3786 mth_resource_cost_mv mrc
3787 WHERE med.equipment_hierarchy_key = -2
3788 AND med.equipment_fk_key IS NOT NULL
3789 AND msg.from_time BETWEEN med.equipment_effective_date
3790 AND Nvl(med.equipment_expiration_date , msg.from_time)
3791 AND med.equipment_fk_key = eqsts.equipment_fk_key
3792 AND eqsts.hour_fk_key = msg.hour_pk_key
3793 AND eqsts.hour_fk_key IN (SELECT hour_pk_key
3794 FROM mth_hour_d
3795 WHERE from_time >= p_n_recalc_from_date
3796 AND to_time <= p_n_recalc_to_date)
3797 AND eqsts.equipment_fk_key = nvl(p_recalc_equip_key,eqsts.equipment_fk_key)
3798 AND eqsts.equipment_fk_key IN (SELECT equipment_pk_key
3799 FROM mth_equipments_d
3800 WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key))
3801 AND med.level9_level_key = mrc.resource_fk_key(+)
3802 GROUP BY eqsts.equipment_fk_key,
3803 eqsts.shift_workday_fk_key,
3804 eqsts.hour_fk_key);
3805
3806 -- Call the logging API to log the number of rows inserted
3807 mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3808
3809 -- Call mth_run_log_post_load
3810 mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_STATUS_SUMMARY',v_ua_val);
3811
3812
3813 DELETE FROM MTH_EQUIP_STATUSES_STG;
3814
3815
3816 mth_util_pkg.log_msg('PROCESS_STATUS_SMMRY_RECAL end', mth_util_pkg.G_DBG_PROC_FUN_END);
3817
3818 EXCEPTION
3819 WHEN OTHERS THEN
3820 --Call logging API and then throw exception
3821 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_STATUS_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3822 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3823 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3824 RAISE;
3825 END;
3826
3827 END MTH_PROCESS_STATUS_PKG;