[Home] [Help]
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:
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:
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:
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
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);
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);
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:
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
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
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;
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;
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:
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;
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
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;
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;
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:
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;
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:
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;
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:
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);
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,
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,
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 (
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:
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
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);
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;
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:
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: /*******************************************************************************
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;
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);
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:
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:
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:
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
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:
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
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);
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;
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:
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: /*******************************************************************************
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:
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
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;
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:
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
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,
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
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);
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;
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:
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:
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;
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,
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';
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:
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);
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
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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,
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,
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,
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);
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
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);
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;
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:
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: /*******************************************************************************
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;
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,
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';
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:
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
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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,
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
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
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);
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
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);
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;
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:
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: /*******************************************************************************
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(*)
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)
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)
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
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
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
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
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
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
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
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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 '
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,
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;
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)||','||
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||','||
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;
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:
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,
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
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);
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
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
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);
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
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
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);
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
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
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);
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
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
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);
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;
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:
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: /*******************************************************************************
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);
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:
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
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();
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
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;
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;
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:
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: /*******************************************************************************
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:
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:
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:
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:
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
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:
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
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);
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;
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:
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: /*******************************************************************************
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;
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:
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:
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
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:
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
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
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;
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:
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: /*******************************************************************************
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:
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
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;
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:
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
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
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:
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
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
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:
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:
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
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;
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:
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;