[Home] [Help]
63: AND mrl.fact_table = 'MTH_EQUIP_OUTPUT'
64: AND mtr.last_update_date > mrl.from_date;
65:
66: IF v_tag_reading_count > 0 THEN
67: mth_util_pkg.log_msg('All data from tag reading is still not populated to output table.',mth_util_pkg.G_DBG_EXCEPTION);
68: v_retcode := 2;
69: END IF;
70: END IF;
71:
78: AND meo.last_update_date > mrl.from_date;
79:
80: IF v_output_count > 0 THEN
81: v_msg:=fnd_message.get_string('MTH','MTH_OUTPUT_NOT_IN_SUMMARY');
82: mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_EXCEPTION);
83:
84: v_retcode := 2;
85: END IF;
86: END IF;
99: LOOP
100:
101: FND_MESSAGE.SET_TOKEN('MOC_MV_TOKEN',v_compile_state(i));
102: v_msg:=fnd_message.get_string('MTH','MTH_MV_NOT_REFRESHED');
103: mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_EXCEPTION);
104: v_retcode := 2;
105: END LOOP;
106: END IF;
107:
136: ) IS
137: l_ret_code NUMBER;
138: BEGIN
139: l_ret_code := 0;
140: mth_util_pkg.log_msg('PROCESS_OUTPUT start', mth_util_pkg.G_DBG_PROC_FUN_START);
141: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
142: mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_PARAM_VAL);
143:
144: IF(p_mode = 'RECAL') THEN
137: l_ret_code NUMBER;
138: BEGIN
139: l_ret_code := 0;
140: mth_util_pkg.log_msg('PROCESS_OUTPUT start', mth_util_pkg.G_DBG_PROC_FUN_START);
141: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
142: mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_PARAM_VAL);
143:
144: IF(p_mode = 'RECAL') THEN
145: 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: BEGIN
139: l_ret_code := 0;
140: mth_util_pkg.log_msg('PROCESS_OUTPUT start', mth_util_pkg.G_DBG_PROC_FUN_START);
141: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
142: mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_PARAM_VAL);
143:
144: IF(p_mode = 'RECAL') THEN
145: 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);
146: 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);
141: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
142: mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_PARAM_VAL);
143:
144: IF(p_mode = 'RECAL') THEN
145: 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);
146: 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);
147: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
148: mth_util_pkg.log_msg('p_plant_pk_key = ' || p_plant_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
149: END IF;
142: mth_util_pkg.log_msg('p_source = ' || p_source , mth_util_pkg.G_DBG_PARAM_VAL);
143:
144: IF(p_mode = 'RECAL') THEN
145: 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);
146: 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);
147: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
148: mth_util_pkg.log_msg('p_plant_pk_key = ' || p_plant_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
149: END IF;
150:
143:
144: IF(p_mode = 'RECAL') THEN
145: 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);
146: 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);
147: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
148: mth_util_pkg.log_msg('p_plant_pk_key = ' || p_plant_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
149: END IF;
150:
151: IF p_source = 'TAG' THEN
144: IF(p_mode = 'RECAL') THEN
145: 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);
146: 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);
147: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
148: mth_util_pkg.log_msg('p_plant_pk_key = ' || p_plant_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
149: END IF;
150:
151: IF p_source = 'TAG' THEN
152: IF p_mode = 'INIT' THEN
174: p_plant_pk_key );
175: EXCEPTION
176: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
177: l_ret_code := 1;
178: mth_util_pkg.log_msg('Exception VALIDATION_ERR in PROCESS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
179: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
180: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
181: END;
182: END IF;
175: EXCEPTION
176: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
177: l_ret_code := 1;
178: mth_util_pkg.log_msg('Exception VALIDATION_ERR in PROCESS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
179: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
180: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
181: END;
182: END IF;
183: END IF;
176: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
177: l_ret_code := 1;
178: mth_util_pkg.log_msg('Exception VALIDATION_ERR in PROCESS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
179: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
180: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
181: END;
182: END IF;
183: END IF;
184: p_ret_code := l_ret_code;
181: END;
182: END IF;
183: END IF;
184: p_ret_code := l_ret_code;
185: mth_util_pkg.log_msg('PROCESS_OUTPUT end', mth_util_pkg.G_DBG_PROC_FUN_END);
186:
187: EXCEPTION
188: WHEN OTHERS THEN
189: l_ret_code := 2;
187: EXCEPTION
188: WHEN OTHERS THEN
189: l_ret_code := 2;
190: p_ret_code := l_ret_code;
191: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
192: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
193: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
194: RAISE;
195:
188: WHEN OTHERS THEN
189: l_ret_code := 2;
190: p_ret_code := l_ret_code;
191: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
192: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
193: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
194: RAISE;
195:
196: END PROCESS_OUTPUT;
189: l_ret_code := 2;
190: p_ret_code := l_ret_code;
191: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT', mth_util_pkg.G_DBG_EXCEPTION);
192: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
193: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
194: RAISE;
195:
196: END PROCESS_OUTPUT;
197:
210: PROCEDURE INIT_OUTPUT_FROM_READING IS
211: v_log_date DATE;
212: v_unassigned_val VARCHAR2(30);
213: BEGIN
214: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
215:
216: -- Initialize default parameters
217: v_log_date := sysdate;
218: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
214: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
215:
216: -- Initialize default parameters
217: v_log_date := sysdate;
218: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
219:
220: -- Call mth_run_log_pre_load
221: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INITIAL',NULL,0,v_log_date);
222:
217: v_log_date := sysdate;
218: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
219:
220: -- Call mth_run_log_pre_load
221: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INITIAL',NULL,0,v_log_date);
222:
223: --delete output table
224: DELETE FROM MTH_EQUIP_OUTPUT;
225: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
221: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INITIAL',NULL,0,v_log_date);
222:
223: --delete output table
224: DELETE FROM MTH_EQUIP_OUTPUT;
225: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
226:
227: INSERT INTO MTH_EQUIP_OUTPUT (
228: EQUIPMENT_FK_KEY,
229: ITEM_FK_KEY,
285: a.RECIPE_NUM,
286: a.RECIPE_VERSION,
287: a.SEGMENT_FK_KEY,
288: a.HOUR_FK_KEY;
289: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
290:
291: --update processed flag in readings table
292: update MTH_TAG_READINGS t
293: set PROCESSED_FLAG = 1,
304: AND t.ITEM_FK_KEY IS NOT NULL
305: AND t.HOUR_FK_KEY IS NOT NULL
306: AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
307: AND t.LAST_UPDATE_DATE <= v_log_date);
308: mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
309:
310: ----Call mth_run_log_post_load
311: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);
312:
307: AND t.LAST_UPDATE_DATE <= v_log_date);
308: mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
309:
310: ----Call mth_run_log_post_load
311: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);
312:
313:
314: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
315: EXCEPTION
310: ----Call mth_run_log_post_load
311: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);
312:
313:
314: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
315: EXCEPTION
316: WHEN OTHERS THEN
317: mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
318: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
313:
314: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
315: EXCEPTION
316: WHEN OTHERS THEN
317: mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
318: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
319: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
320: RAISE;
321: END INIT_OUTPUT_FROM_READING;
314: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
315: EXCEPTION
316: WHEN OTHERS THEN
317: mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
318: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
319: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
320: RAISE;
321: END INIT_OUTPUT_FROM_READING;
322:
315: EXCEPTION
316: WHEN OTHERS THEN
317: mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
318: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
319: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
320: RAISE;
321: END INIT_OUTPUT_FROM_READING;
322:
323: /*******************************************************************************
336: v_log_from_date DATE;
337: v_log_to_date DATE;
338: v_unassigned_val VARCHAR2(30);
339: BEGIN
340: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
341:
342: -- Initialize default parameters
343: v_log_to_date := sysdate;
344: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
340: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
341:
342: -- Initialize default parameters
343: v_log_to_date := sysdate;
344: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
345:
346: -- Call mth_run_log_pre_load
347: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
348:
343: v_log_to_date := sysdate;
344: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
345:
346: -- Call mth_run_log_pre_load
347: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
348:
349: -- Call GET_RUN_LOG_DATES
350: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
351:
346: -- Call mth_run_log_pre_load
347: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
348:
349: -- Call GET_RUN_LOG_DATES
350: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
351:
352:
353: MERGE INTO MTH_EQUIP_OUTPUT o USING
354: (SELECT a.EQUIPMENT_FK_KEY,
453: tr.RECIPE_NUM,
454: tr.RECIPE_VERSION,
455: tr.SEGMENT_FK_KEY
456: );
457: mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
458:
459: --update processed flag in readings table
460: update MTH_TAG_READINGS t
461: set PROCESSED_FLAG = 1,
472: AND t.ITEM_FK_KEY IS NOT NULL
473: AND t.HOUR_FK_KEY IS NOT NULL
474: AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
475: AND t.LAST_UPDATE_DATE BETWEEN v_log_from_date and v_log_to_date);
476: mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
477:
478: ----Call mth_run_log_post_load
479: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);
480:
475: AND t.LAST_UPDATE_DATE BETWEEN v_log_from_date and v_log_to_date);
476: mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
477:
478: ----Call mth_run_log_post_load
479: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);
480:
481:
482: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
483: EXCEPTION
478: ----Call mth_run_log_post_load
479: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);
480:
481:
482: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
483: EXCEPTION
484: WHEN OTHERS THEN
485: mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
486: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
481:
482: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
483: EXCEPTION
484: WHEN OTHERS THEN
485: mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
486: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
487: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
488: RAISE;
489: END INCR_OUTPUT_FROM_READING;
482: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
483: EXCEPTION
484: WHEN OTHERS THEN
485: mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
486: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
487: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
488: RAISE;
489: END INCR_OUTPUT_FROM_READING;
490:
483: EXCEPTION
484: WHEN OTHERS THEN
485: mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
486: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
487: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
488: RAISE;
489: END INCR_OUTPUT_FROM_READING;
490:
491: /*******************************************************************************
507: p_equipment_pk_key IN NUMBER DEFAULT NULL --Equipment to recalculate
508: ) IS
509: v_unassigned_val VARCHAR2(30);
510: BEGIN
511: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
512: 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);
513: 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);
514: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
515:
508: ) IS
509: v_unassigned_val VARCHAR2(30);
510: BEGIN
511: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
512: 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);
513: 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);
514: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
515:
516: -- Initialize default parameters
509: v_unassigned_val VARCHAR2(30);
510: BEGIN
511: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
512: 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);
513: 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);
514: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
515:
516: -- Initialize default parameters
517: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
510: BEGIN
511: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING start', mth_util_pkg.G_DBG_PROC_FUN_START);
512: 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);
513: 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);
514: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
515:
516: -- Initialize default parameters
517: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
518:
513: 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);
514: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
515:
516: -- Initialize default parameters
517: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
518:
519: --delete output table
520: DELETE FROM MTH_EQUIP_OUTPUT o
521: WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)
519: --delete output table
520: DELETE FROM MTH_EQUIP_OUTPUT o
521: WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)
522: AND o.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,o.READING_TIME);
523: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
524:
525: INSERT INTO MTH_EQUIP_OUTPUT (
526: EQUIPMENT_FK_KEY,
527: ITEM_FK_KEY,
584: a.RECIPE_NUM,
585: a.RECIPE_VERSION,
586: a.SEGMENT_FK_KEY,
587: a.HOUR_FK_KEY;
588: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
589:
590: --update processed flag in readings table
591: update MTH_TAG_READINGS t
592: set PROCESSED_FLAG = 1,
603: AND t.ITEM_FK_KEY IS NOT NULL
604: AND t.HOUR_FK_KEY IS NOT NULL
605: AND m.MTH_ALIAS IN ('Completed Quantity', 'Output Quantity', 'Rejected Quantity', 'Scrap Quantity', 'Rework Quantity')
606: AND t.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,t.READING_TIME));
607: mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
608:
609:
610: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
611: EXCEPTION
606: AND t.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,t.READING_TIME));
607: mth_util_pkg.log_msg('Number of rows updated in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
608:
609:
610: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
611: EXCEPTION
612: WHEN OTHERS THEN
613: mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
614: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
609:
610: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
611: EXCEPTION
612: WHEN OTHERS THEN
613: mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
614: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
615: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
616: RAISE;
617: END RECAL_OUTPUT_FROM_READING;
610: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_READING end', mth_util_pkg.G_DBG_PROC_FUN_END);
611: EXCEPTION
612: WHEN OTHERS THEN
613: mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
614: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
615: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
616: RAISE;
617: END RECAL_OUTPUT_FROM_READING;
618:
611: EXCEPTION
612: WHEN OTHERS THEN
613: mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_READING', mth_util_pkg.G_DBG_EXCEPTION);
614: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
615: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
616: RAISE;
617: END RECAL_OUTPUT_FROM_READING;
618:
619: /*******************************************************************************
628: v_log_date DATE;
629: v_unassigned_val VARCHAR2(30);
630: v_processing_flag NUMBER;
631: BEGIN
632: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
633:
634: -- Initialize default parameters
635: v_log_date := sysdate;
636: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
632: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
633:
634: -- Initialize default parameters
635: v_log_date := sysdate;
636: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
637:
638:
639: --delete data from the output table
640: DELETE FROM MTH_EQUIP_OUTPUT;
637:
638:
639: --delete data from the output table
640: DELETE FROM MTH_EQUIP_OUTPUT;
641: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
642:
643: --Ensuring the functionality of ES map is introduced before the staging to fact is executed.
644: INSERT INTO mth_equip_output_stg(equipment_fk,
645: item_fk,
706: user_measure5,
707: scrap_reason_code
708: FROM mth_equip_output_err
709: WHERE reprocess_ready_yn = 'Y');
710: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
711:
712: --delete data from the output error table
713: DELETE FROM MTH_EQUIP_OUTPUT_ERR
714: WHERE REPROCESS_READY_YN = 'Y';
711:
712: --delete data from the output error table
713: DELETE FROM MTH_EQUIP_OUTPUT_ERR
714: WHERE REPROCESS_READY_YN = 'Y';
715: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
716:
717: mth_util_pkg.switch_column_default_value('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
718:
719: --Execute all validations on csv records
713: DELETE FROM MTH_EQUIP_OUTPUT_ERR
714: WHERE REPROCESS_READY_YN = 'Y';
715: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
716:
717: mth_util_pkg.switch_column_default_value('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
718:
719: --Execute all validations on csv records
720:
721: -- Validation for Invalid Item
726: mth_equip_output_stg stg
727: WHERE mid.item_pk = stg.item_fk) itm
728: WHERE itm.item_pk = stg.item_fk
729: AND stg.processing_flag = v_processing_flag );
730: mth_util_pkg.log_msg('Number of rows with ITM in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
731:
732: -- Validation for Invalid Segment
733: UPDATE mth_equip_output_stg stg
734: SET stg.err_code = stg.err_code || 'SEG '
737: mth_equip_output_stg stg
738: WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
739: WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk)
740: AND stg.processing_flag = v_processing_flag );
741: mth_util_pkg.log_msg('Number of rows with SEG in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
742:
743: -- Validation for invalid work order
744: UPDATE mth_equip_output_stg stg
745: SET stg.err_code = stg.err_code || 'WKO '
750: WHERE stg.workorder_fk = mps.workorder_pk(+)
751: AND stg.workorder_fk IS NOT NULL) wko
752: WHERE wko.workorder_pk = stg.workorder_fk
753: AND stg.processing_flag = v_processing_flag);
754: mth_util_pkg.log_msg('Number of rows with WKO in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
755:
756: -- Validation for Invalid Equipment
757: UPDATE mth_equip_output_stg stg
758: SET stg.err_code = stg.err_code || 'EQP '
761: mth_equip_output_stg stg
762: WHERE med.equipment_pk = stg.equipment_fk) eqp
763: WHERE eqp.equipment_pk = stg.equipment_fk
764: AND stg.processing_flag = v_processing_flag );
765: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
766:
767: -- Validation for Inactive Equipment
768: UPDATE mth_equip_output_stg stg
769: SET stg.err_code = stg.err_code || 'IEQ '
773: WHERE med.equipment_pk = stg.equipment_fk
774: AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
775: WHERE eqp.equipment_pk = stg.equipment_fk
776: AND stg.processing_flag = v_processing_flag );
777: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
778:
779: -- Validation for Duplicate record
780: UPDATE mth_equip_output_stg stg
781: SET stg.err_code = stg.err_code || 'DUP '
789: AND dup.item_fk = stg.item_fk
790: AND dup.workorder_fk = stg.workorder_fk
791: AND dup.segment_fk = stg.segment_fk
792: AND stg.processing_flag = v_processing_flag );
793: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
794:
795: -- Validation for invalid shift
796: UPDATE mth_equip_output_stg stg
797: SET stg.err_code = stg.err_code || 'WDS '
802: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
803: AND stg.shift_workday_fk IS NOT NULL) wds
804: WHERE wds.shift_workday_pk = stg.shift_workday_fk
805: AND stg.processing_flag = v_processing_flag);
806: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
807:
808: -- Validation for Null Work Day Shift
809: UPDATE mth_equip_output_stg stg
810: SET stg.err_code = stg.err_code || 'NWDS '
809: UPDATE mth_equip_output_stg stg
810: SET stg.err_code = stg.err_code || 'NWDS '
811: WHERE stg.shift_workday_fk IS NULL
812: AND stg.processing_flag = v_processing_flag;
813: mth_util_pkg.log_msg('Number of rows with NWDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
814:
815: -- Validation for user dimension 1
816: UPDATE mth_equip_output_stg stg
817: SET stg.err_code = stg.err_code || 'UD1 '
823: AND stg.user_dim1_fk IS NOT NULL) ud1
824: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
825: AND ud1.entity_pk IS NULL
826: AND stg.processing_flag = v_processing_flag);
827: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
828:
829: -- Validation for user dimension 2
830: UPDATE mth_equip_output_stg stg
831: SET stg.err_code = stg.err_code || 'UD2 '
837: AND stg.user_dim2_fk IS NOT NULL) ud2
838: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
839: AND ud2.entity_pk IS NULL
840: AND stg.processing_flag = v_processing_flag);
841: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
842:
843: -- Validation for user dimension 3
844: UPDATE mth_equip_output_stg stg
845: SET stg.err_code = stg.err_code || 'UD3 '
851: AND stg.user_dim3_fk IS NOT NULL) ud3
852: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
853: AND ud3.entity_pk IS NULL
854: AND stg.processing_flag = v_processing_flag);
855: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
856:
857: -- Validation for user dimension 4
858: UPDATE mth_equip_output_stg stg
859: SET stg.err_code = stg.err_code || 'UD4 '
865: AND stg.user_dim4_fk IS NOT NULL) ud4
866: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
867: AND stg.processing_flag = v_processing_flag
868: AND ud4.entity_pk IS NULL);
869: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
870:
871: -- Validation for user dimension 5
872: UPDATE mth_equip_output_stg stg
873: SET stg.err_code = stg.err_code || 'UD5 '
879: AND stg.user_dim5_fk IS NOT NULL) ud5
880: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
881: AND stg.processing_flag = v_processing_flag
882: AND ud5.entity_pk IS NULL);
883: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
884:
885: -- Validation for SPR
886: UPDATE mth_equip_output_stg stg
887: SET stg.err_code = stg.err_code || 'SPR '
896: AND dtr.reading_time = stg.reading_time
897: AND dtr.qty_scrap = stg.qty_scrap
898: AND dtr.scrap_reason_code = stg.scrap_reason_code
899: AND stg.processing_flag = v_processing_flag);
900: mth_util_pkg.log_msg('Number of rows with SPR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
901:
902: -- Validation for Future Time Date
903: UPDATE mth_equip_output_stg stg
904: SET stg.err_code = stg.err_code || 'FTD '
903: UPDATE mth_equip_output_stg stg
904: SET stg.err_code = stg.err_code || 'FTD '
905: WHERE stg.reading_time > SYSDATE
906: AND stg.processing_flag = v_processing_flag;
907: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
908:
909: -- Validation for ITR
910: UPDATE mth_equip_output_stg stg
911: SET stg.err_code = stg.err_code || 'ITR '
924: AND itr.equipment_pk = stg.equipment_fk
925: AND stg.reading_time >= itr.from_date
926: AND stg.reading_time <= itr.to_date
927: AND stg.processing_flag = v_processing_flag);
928: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
929:
930: -- Validation for Duplicate Output
931:
932: UPDATE mth_equip_output_stg stg
944: WHERE dop.reading_time = stg.reading_time
945: AND dop.equipment_pk = stg.equipment_fk
946: AND dop.shift_workday_pk = stg.shift_workday_fk
947: AND stg.processing_flag = v_processing_flag );
948: mth_util_pkg.log_msg('Number of rows with DOP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
949:
950: --Insert records into mth_equip_output_err
951: INSERT INTO mth_equip_output_err(equipment_fk,
952: item_fk,
1017: 'N',
1018: err_code
1019: FROM mth_equip_output_stg
1020: WHERE err_code IS NOT NULL);
1021: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1022:
1023: --Insert records into MTH_EQUIP_OUTPUT table
1024: INSERT INTO mth_equip_output( equipment_fk_key,
1025: item_fk_key,
1128: AND lkp.LOOKUP_TYPE (+) = 'MTH_SCRAP_REASON'
1129: AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
1130: AND stg.err_code IS NULL
1131: AND stg.processing_flag = v_processing_flag);
1132: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1133:
1134: -- Insert into tag reason readings
1135: INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
1136: EQUIPMENT_FK_KEY,
1165: AND stg.processing_flag = v_processing_flag
1166: AND stg.ERR_CODE IS NULL
1167: AND stg.qty_scrap IS NOT NULL);
1168:
1169: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1170:
1171: mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1172:
1173: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1167: AND stg.qty_scrap IS NOT NULL);
1168:
1169: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1170:
1171: mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1172:
1173: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1174:
1175: EXCEPTION
1169: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1170:
1171: mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1172:
1173: mth_util_pkg.log_msg('INIT_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1174:
1175: EXCEPTION
1176: WHEN OTHERS THEN
1177: --Call logging API and then throw exception
1174:
1175: EXCEPTION
1176: WHEN OTHERS THEN
1177: --Call logging API and then throw exception
1178: mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1179: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1180: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1181: RAISE;
1182: END;
1175: EXCEPTION
1176: WHEN OTHERS THEN
1177: --Call logging API and then throw exception
1178: mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1179: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1180: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1181: RAISE;
1182: END;
1183:
1176: WHEN OTHERS THEN
1177: --Call logging API and then throw exception
1178: mth_util_pkg.log_msg('Exception OTHERS in INIT_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1179: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1180: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1181: RAISE;
1182: END;
1183:
1184: /*******************************************************************************
1193: v_log_date DATE;
1194: v_unassigned_val VARCHAR2(30);
1195: v_processing_flag NUMBER;
1196: BEGIN
1197: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
1198:
1199: -- Initialize default parameters
1200: v_log_date := sysdate;
1201: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1197: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
1198:
1199: -- Initialize default parameters
1200: v_log_date := sysdate;
1201: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1202:
1203: --Ensuring the functionality of ES map is introduced before the staging to fact is executed.
1204: INSERT INTO mth_equip_output_stg(equipment_fk,
1205: item_fk,
1266: user_measure5,
1267: scrap_reason_code
1268: FROM mth_equip_output_err
1269: WHERE reprocess_ready_yn = 'Y');
1270: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1271:
1272: --delete data from the output error table
1273: DELETE FROM MTH_EQUIP_OUTPUT_ERR
1274: WHERE REPROCESS_READY_YN = 'Y';
1271:
1272: --delete data from the output error table
1273: DELETE FROM MTH_EQUIP_OUTPUT_ERR
1274: WHERE REPROCESS_READY_YN = 'Y';
1275: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1276:
1277: mth_util_pkg.switch_column_default_value('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1278:
1279: --Execute all validations on csv records
1273: DELETE FROM MTH_EQUIP_OUTPUT_ERR
1274: WHERE REPROCESS_READY_YN = 'Y';
1275: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1276:
1277: mth_util_pkg.switch_column_default_value('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1278:
1279: --Execute all validations on csv records
1280:
1281: -- Validation for Invalid Item
1286: mth_equip_output_stg stg
1287: WHERE mid.item_pk = stg.item_fk) itm
1288: WHERE itm.item_pk = stg.item_fk
1289: AND stg.processing_flag = v_processing_flag );
1290: mth_util_pkg.log_msg('Number of rows with ITM in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1291:
1292: -- Validation for Invalid Segment
1293: UPDATE mth_equip_output_stg stg
1294: SET stg.err_code = stg.err_code || 'SEG '
1297: mth_equip_output_stg stg
1298: WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
1299: WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk)
1300: AND stg.processing_flag = v_processing_flag );
1301: mth_util_pkg.log_msg('Number of rows with SEG in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1302:
1303: -- Validation for invalid work order
1304: UPDATE mth_equip_output_stg stg
1305: SET stg.err_code = stg.err_code || 'WKO '
1310: WHERE stg.workorder_fk = mps.workorder_pk(+)
1311: AND stg.workorder_fk IS NOT NULL) wko
1312: WHERE wko.workorder_pk = stg.workorder_fk
1313: AND stg.processing_flag = v_processing_flag);
1314: mth_util_pkg.log_msg('Number of rows with WKO in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1315:
1316: -- Validation for Invalid Equipment
1317: UPDATE mth_equip_output_stg stg
1318: SET stg.err_code = stg.err_code || 'EQP '
1321: mth_equip_output_stg stg
1322: WHERE med.equipment_pk = stg.equipment_fk) eqp
1323: WHERE eqp.equipment_pk = stg.equipment_fk
1324: AND stg.processing_flag = v_processing_flag );
1325: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1326:
1327: -- Validation for Inactive Equipment
1328: UPDATE mth_equip_output_stg stg
1329: SET stg.err_code = stg.err_code || 'IEQ '
1333: WHERE med.equipment_pk = stg.equipment_fk
1334: AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
1335: WHERE eqp.equipment_pk = stg.equipment_fk
1336: AND stg.processing_flag = v_processing_flag );
1337: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1338:
1339: -- Validation for Duplicate record
1340: UPDATE mth_equip_output_stg stg
1341: SET stg.err_code = stg.err_code || 'DUP '
1349: AND dup.item_fk = stg.item_fk
1350: AND dup.workorder_fk = stg.workorder_fk
1351: AND dup.segment_fk = stg.segment_fk
1352: AND stg.processing_flag = v_processing_flag );
1353: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1354:
1355: -- Validation for invalid shift
1356: UPDATE mth_equip_output_stg stg
1357: SET stg.err_code = stg.err_code || 'WDS '
1362: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1363: AND stg.shift_workday_fk IS NOT NULL) wds
1364: WHERE wds.shift_workday_pk = stg.shift_workday_fk
1365: AND stg.processing_flag = v_processing_flag);
1366: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1367:
1368: -- Validation for Null Work Day Shift
1369: UPDATE mth_equip_output_stg stg
1370: SET stg.err_code = stg.err_code || 'NWDS '
1369: UPDATE mth_equip_output_stg stg
1370: SET stg.err_code = stg.err_code || 'NWDS '
1371: WHERE stg.shift_workday_fk IS NULL
1372: AND stg.processing_flag = v_processing_flag;
1373: mth_util_pkg.log_msg('Number of rows with NWDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1374:
1375: -- Validation for user dimension 1
1376: UPDATE mth_equip_output_stg stg
1377: SET stg.err_code = stg.err_code || 'UD1 '
1383: AND stg.user_dim1_fk IS NOT NULL) ud1
1384: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
1385: AND ud1.entity_pk IS NULL
1386: AND stg.processing_flag = v_processing_flag);
1387: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1388:
1389: -- Validation for user dimension 2
1390: UPDATE mth_equip_output_stg stg
1391: SET stg.err_code = stg.err_code || 'UD2 '
1397: AND stg.user_dim2_fk IS NOT NULL) ud2
1398: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
1399: AND ud2.entity_pk IS NULL
1400: AND stg.processing_flag = v_processing_flag);
1401: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1402:
1403: -- Validation for user dimension 3
1404: UPDATE mth_equip_output_stg stg
1405: SET stg.err_code = stg.err_code || 'UD3 '
1411: AND stg.user_dim3_fk IS NOT NULL) ud3
1412: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
1413: AND ud3.entity_pk IS NULL
1414: AND stg.processing_flag = v_processing_flag);
1415: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1416:
1417: -- Validation for user dimension 4
1418: UPDATE mth_equip_output_stg stg
1419: SET stg.err_code = stg.err_code || 'UD4 '
1425: AND stg.user_dim4_fk IS NOT NULL) ud4
1426: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
1427: AND stg.processing_flag = v_processing_flag
1428: AND ud4.entity_pk IS NULL);
1429: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1430:
1431: -- Validation for user dimension 5
1432: UPDATE mth_equip_output_stg stg
1433: SET stg.err_code = stg.err_code || 'UD5 '
1439: AND stg.user_dim5_fk IS NOT NULL) ud5
1440: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
1441: AND stg.processing_flag = v_processing_flag
1442: AND ud5.entity_pk IS NULL);
1443: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1444:
1445: -- Validation for SPR
1446: UPDATE mth_equip_output_stg stg
1447: SET stg.err_code = stg.err_code || 'SPR '
1456: AND dtr.reading_time = stg.reading_time
1457: AND dtr.qty_scrap = stg.qty_scrap
1458: AND dtr.scrap_reason_code = stg.scrap_reason_code
1459: AND stg.processing_flag = v_processing_flag);
1460: mth_util_pkg.log_msg('Number of rows with SPR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1461:
1462: -- Validation for Future Time Date
1463: UPDATE mth_equip_output_stg stg
1464: SET stg.err_code = stg.err_code || 'FTD '
1463: UPDATE mth_equip_output_stg stg
1464: SET stg.err_code = stg.err_code || 'FTD '
1465: WHERE stg.reading_time > SYSDATE
1466: AND stg.processing_flag = v_processing_flag;
1467: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1468:
1469: -- Validation for ITR
1470: UPDATE mth_equip_output_stg stg
1471: SET stg.err_code = stg.err_code || 'ITR '
1484: AND itr.equipment_pk = stg.equipment_fk
1485: AND stg.reading_time >= itr.from_date
1486: AND stg.reading_time <= itr.to_date
1487: AND stg.processing_flag = v_processing_flag);
1488: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1489:
1490: -- Validation for Duplicate Output
1491:
1492: UPDATE mth_equip_output_stg stg
1504: WHERE dop.reading_time = stg.reading_time
1505: AND dop.equipment_pk = stg.equipment_fk
1506: AND dop.shift_workday_pk = stg.shift_workday_fk
1507: AND stg.processing_flag = v_processing_flag );
1508: mth_util_pkg.log_msg('Number of rows with DOP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1509:
1510: --Insert records into mth_equip_output_err
1511: INSERT INTO mth_equip_output_err(equipment_fk,
1512: item_fk,
1577: 'N',
1578: err_code
1579: FROM mth_equip_output_stg
1580: WHERE err_code IS NOT NULL);
1581: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1582:
1583:
1584: --Insert records into mth_equip_output
1585:
1691: AND lkp.LOOKUP_TYPE (+) = 'MTH_SCRAP_REASON'
1692: AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
1693: AND stg.err_code IS NULL
1694: AND stg.processing_flag = v_processing_flag);
1695: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1696:
1697: -- Insert into tag reason readings
1698: INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
1699: EQUIPMENT_FK_KEY,
1728: AND stg.processing_flag = v_processing_flag
1729: AND stg.ERR_CODE IS NULL
1730: AND stg.qty_scrap IS NOT NULL);
1731:
1732: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1733:
1734: mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1735:
1736: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1730: AND stg.qty_scrap IS NOT NULL);
1731:
1732: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1733:
1734: mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1735:
1736: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1737: EXCEPTION
1738: WHEN OTHERS THEN
1732: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1733:
1734: mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);
1735:
1736: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1737: EXCEPTION
1738: WHEN OTHERS THEN
1739: --Call logging API and then throw exception
1740: mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1736: mth_util_pkg.log_msg('INCR_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1737: EXCEPTION
1738: WHEN OTHERS THEN
1739: --Call logging API and then throw exception
1740: mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1741: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1742: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1743: RAISE;
1744: END;
1737: EXCEPTION
1738: WHEN OTHERS THEN
1739: --Call logging API and then throw exception
1740: mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1741: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1742: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1743: RAISE;
1744: END;
1745:
1738: WHEN OTHERS THEN
1739: --Call logging API and then throw exception
1740: mth_util_pkg.log_msg('Exception OTHERS in INCR_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
1741: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1742: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1743: RAISE;
1744: END;
1745:
1746: /*******************************************************************************
1798: TYPE fetch_err_rows IS TABLE OF c_error_rows%ROWTYPE;
1799: l_err_rows fetch_err_rows;
1800:
1801: BEGIN
1802: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
1803:
1804: -- Initialize default parameters
1805: v_log_date := sysdate;
1806: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1802: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_CSV start', mth_util_pkg.G_DBG_PROC_FUN_START);
1803:
1804: -- Initialize default parameters
1805: v_log_date := sysdate;
1806: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1807:
1808: IF (p_plant_pk_key IS NOT NULL)
1809: THEN
1810: SELECT Count(*)
1815:
1816: IF l_count < 1
1817: THEN
1818: v_msg:=fnd_message.get_string('MTH','MTH_SITE_MIS_ERR');
1819: mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
1820: RAISE SITE_MIS_ERR;
1821: END IF;
1822:
1823:
1834:
1835: IF l_count < 1
1836: THEN
1837: v_msg:=fnd_message.get_string('MTH','MTH_EQUIP_MIS_ERR');
1838: mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
1839: RAISE EQUIP_MIS_ERR;
1840: END IF;
1841:
1842:
1857:
1858: IF(p_min_reading_time_csv < p_recal_from_date OR p_max_reading_time_csv > p_recal_to_date)
1859: THEN
1860: v_msg:=fnd_message.get_string('MTH','MTH_GAP_IN_RECAL_CSV');
1861: mth_util_pkg.log_msg(v_msg,mth_util_pkg.G_DBG_USER_ERROR);
1862: RAISE GAP_IN_RECAL_CSV;
1863: END IF;
1864:
1865: --Delete records from output table for the recalculation time range
1907: FROM mth_items_d mid,
1908: mth_equip_output_stg stg
1909: WHERE mid.item_pk = stg.item_fk) itm
1910: WHERE itm.item_pk = stg.item_fk );
1911: mth_util_pkg.log_msg('Number of rows with ITM in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1912:
1913: -- Validation for Invalid Segment
1914: UPDATE mth_equip_output_stg stg
1915: SET stg.err_code = stg.err_code || 'SEG '
1917: FROM mth_production_segments_f msf,
1918: mth_equip_output_stg stg
1919: WHERE msf.segment_pk = Nvl(stg.segment_fk,msf.segment_pk)) seg
1920: WHERE seg.segment_pk = Nvl(stg.segment_fk,seg.segment_pk));
1921: mth_util_pkg.log_msg('Number of rows with SEG in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1922:
1923: -- Validation for invalid work order
1924: UPDATE mth_equip_output_stg stg
1925: SET stg.err_code = stg.err_code || 'WKO '
1929: mth_equip_output_stg stg
1930: WHERE stg.workorder_fk = mps.workorder_pk(+)
1931: AND stg.workorder_fk IS NOT NULL) wko
1932: WHERE wko.workorder_pk = stg.workorder_fk);
1933: mth_util_pkg.log_msg('Number of rows with WKO in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1934:
1935: -- Validation for Invalid Equipment
1936: UPDATE mth_equip_output_stg stg
1937: SET stg.err_code = stg.err_code || 'EQP '
1939: FROM mth_equipments_d med,
1940: mth_equip_output_stg stg
1941: WHERE med.equipment_pk = stg.equipment_fk) eqp
1942: WHERE eqp.equipment_pk = stg.equipment_fk);
1943: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1944:
1945: -- Validation for Inactive Equipment
1946: UPDATE mth_equip_output_stg stg
1947: SET stg.err_code = stg.err_code || 'IEQ '
1950: mth_equip_output_stg stg
1951: WHERE med.equipment_pk = stg.equipment_fk
1952: AND Nvl(med.status,'NULL') <> 'ACTIVE') eqp
1953: WHERE eqp.equipment_pk = stg.equipment_fk );
1954: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1955:
1956: -- Validation for Duplicate record
1957: UPDATE mth_equip_output_stg stg
1958: SET stg.err_code = stg.err_code || 'DUP '
1965: AND dup.reading_time = stg.reading_time
1966: AND dup.item_fk = stg.item_fk
1967: AND dup.workorder_fk = stg.workorder_fk
1968: AND dup.segment_fk = stg.segment_fk );
1969: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1970:
1971: -- Validation for invalid shift
1972: UPDATE mth_equip_output_stg stg
1973: SET stg.err_code = stg.err_code || 'WDS '
1977: mth_equip_output_stg stg
1978: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1979: AND stg.shift_workday_fk IS NOT NULL) wds
1980: WHERE wds.shift_workday_pk = stg.shift_workday_fk);
1981: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1982:
1983: -- Validation for Null Work Day Shift
1984: UPDATE mth_equip_output_stg stg
1985: SET stg.err_code = stg.err_code || 'NWDS '
1983: -- Validation for Null Work Day Shift
1984: UPDATE mth_equip_output_stg stg
1985: SET stg.err_code = stg.err_code || 'NWDS '
1986: WHERE stg.shift_workday_fk IS NULL;
1987: mth_util_pkg.log_msg('Number of rows with NWDS in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1988:
1989: -- Validation for user dimension 1
1990: UPDATE mth_equip_output_stg stg
1991: SET stg.err_code = stg.err_code || 'UD1 '
1996: WHERE stg.user_dim1_fk = mue.entity_pk (+)
1997: AND stg.user_dim1_fk IS NOT NULL) ud1
1998: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
1999: AND ud1.entity_pk IS NULL);
2000: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2001:
2002: -- Validation for user dimension 2
2003: UPDATE mth_equip_output_stg stg
2004: SET stg.err_code = stg.err_code || 'UD2 '
2009: WHERE stg.user_dim2_fk = mue.entity_pk (+)
2010: AND stg.user_dim2_fk IS NOT NULL) ud2
2011: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
2012: AND ud2.entity_pk IS NULL);
2013: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2014:
2015: -- Validation for user dimension 3
2016: UPDATE mth_equip_output_stg stg
2017: SET stg.err_code = stg.err_code || 'UD3 '
2022: WHERE stg.user_dim3_fk = mue.entity_pk (+)
2023: AND stg.user_dim3_fk IS NOT NULL) ud3
2024: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
2025: AND ud3.entity_pk IS NULL);
2026: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2027:
2028: -- Validation for user dimension 4
2029: UPDATE mth_equip_output_stg stg
2030: SET stg.err_code = stg.err_code || 'UD4 '
2035: WHERE stg.user_dim4_fk = mue.entity_pk (+)
2036: AND stg.user_dim4_fk IS NOT NULL) ud4
2037: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
2038: AND ud4.entity_pk IS NULL);
2039: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2040:
2041: -- Validation for user dimension 5
2042: UPDATE mth_equip_output_stg stg
2043: SET stg.err_code = stg.err_code || 'UD5 '
2048: WHERE stg.user_dim5_fk = mue.entity_pk (+)
2049: AND stg.user_dim5_fk IS NOT NULL) ud5
2050: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
2051: AND ud5.entity_pk IS NULL);
2052: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2053:
2054: -- Validation for SPR
2055: UPDATE mth_equip_output_stg stg
2056: SET stg.err_code = stg.err_code || 'SPR '
2064: stg.scrap_reason_code <> dtr.lookup_code)
2065: AND dtr.reading_time = stg.reading_time
2066: AND dtr.qty_scrap = stg.qty_scrap
2067: AND dtr.scrap_reason_code = stg.scrap_reason_code);
2068: mth_util_pkg.log_msg('Number of rows with SPR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2069:
2070: -- Validation for Future Time Date
2071: UPDATE mth_equip_output_stg stg
2072: SET stg.err_code = stg.err_code || 'FTD '
2070: -- Validation for Future Time Date
2071: UPDATE mth_equip_output_stg stg
2072: SET stg.err_code = stg.err_code || 'FTD '
2073: WHERE stg.reading_time > SYSDATE;
2074: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2075:
2076: -- Validation for ITR
2077: UPDATE mth_equip_output_stg stg
2078: SET stg.err_code = stg.err_code || 'ITR '
2090: WHERE itr.shift_workday_pk = stg.shift_workday_fk
2091: AND itr.equipment_pk = stg.equipment_fk
2092: AND stg.reading_time >= itr.from_date
2093: AND stg.reading_time <= itr.to_date);
2094: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2095:
2096: -- Validation for Duplicate Output
2097:
2098: UPDATE mth_equip_output_stg stg
2109: AND meo.reading_time = stg.reading_time) dop
2110: WHERE dop.reading_time = stg.reading_time
2111: AND dop.equipment_pk = stg.equipment_fk
2112: AND dop.shift_workday_pk = stg.shift_workday_fk );
2113: mth_util_pkg.log_msg('Number of rows with DOP in MTH_EQUIP_OUTPUT_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2114:
2115:
2116: OPEN c_error_rows;
2117: LOOP
2121: CLOSE c_error_rows;
2122:
2123: IF l_err_rows.COUNT > 0
2124: THEN
2125: mth_util_pkg.log_msg('Following are the errored records in OUTPUT CSV Recalculation',mth_util_pkg.G_DBG_USER_ERROR);
2126: mth_util_pkg.log_msg('EQUIPMENT_FK,ITEM_FK,SHIFT_WORKDAY_FK,WORKORDER_FK,READING_TIME,QTY_COMPLETED,QTY_SCRAP,QTY_REJECTED,QTY_REWORK,QTY_UOM,QTY_GOOD,' ||
2127: 'QTY_OUTPUT,SEGMENT_FK,USER_DIM1_FK,USER_DIM2_FK,USER_DIM3_FK,USER_DIM4_FK,USER_DIM5_FK,' ||
2128: 'SCRAP_REASON_CODE,ERR_CODE',mth_util_pkg.G_DBG_USER_ERROR);
2129:
2122:
2123: IF l_err_rows.COUNT > 0
2124: THEN
2125: mth_util_pkg.log_msg('Following are the errored records in OUTPUT CSV Recalculation',mth_util_pkg.G_DBG_USER_ERROR);
2126: mth_util_pkg.log_msg('EQUIPMENT_FK,ITEM_FK,SHIFT_WORKDAY_FK,WORKORDER_FK,READING_TIME,QTY_COMPLETED,QTY_SCRAP,QTY_REJECTED,QTY_REWORK,QTY_UOM,QTY_GOOD,' ||
2127: 'QTY_OUTPUT,SEGMENT_FK,USER_DIM1_FK,USER_DIM2_FK,USER_DIM3_FK,USER_DIM4_FK,USER_DIM5_FK,' ||
2128: 'SCRAP_REASON_CODE,ERR_CODE',mth_util_pkg.G_DBG_USER_ERROR);
2129:
2130: FOR i IN l_err_rows.FIRST..l_err_rows.LAST
2124: THEN
2125: mth_util_pkg.log_msg('Following are the errored records in OUTPUT CSV Recalculation',mth_util_pkg.G_DBG_USER_ERROR);
2126: mth_util_pkg.log_msg('EQUIPMENT_FK,ITEM_FK,SHIFT_WORKDAY_FK,WORKORDER_FK,READING_TIME,QTY_COMPLETED,QTY_SCRAP,QTY_REJECTED,QTY_REWORK,QTY_UOM,QTY_GOOD,' ||
2127: 'QTY_OUTPUT,SEGMENT_FK,USER_DIM1_FK,USER_DIM2_FK,USER_DIM3_FK,USER_DIM4_FK,USER_DIM5_FK,' ||
2128: 'SCRAP_REASON_CODE,ERR_CODE',mth_util_pkg.G_DBG_USER_ERROR);
2129:
2130: FOR i IN l_err_rows.FIRST..l_err_rows.LAST
2131: LOOP
2132: mth_util_pkg.log_msg(l_err_rows(i).equipment_fk||','||l_err_rows(i).item_fk||','||l_err_rows(i).shift_workday_fk||','||l_err_rows(i).workorder_fk||','||fnd_date.date_to_canonical(l_err_rows(i).reading_time)||','||
2128: 'SCRAP_REASON_CODE,ERR_CODE',mth_util_pkg.G_DBG_USER_ERROR);
2129:
2130: FOR i IN l_err_rows.FIRST..l_err_rows.LAST
2131: LOOP
2132: mth_util_pkg.log_msg(l_err_rows(i).equipment_fk||','||l_err_rows(i).item_fk||','||l_err_rows(i).shift_workday_fk||','||l_err_rows(i).workorder_fk||','||fnd_date.date_to_canonical(l_err_rows(i).reading_time)||','||
2133: l_err_rows(i).qty_completed||','||l_err_rows(i).qty_scrap||','||l_err_rows(i).qty_rejected||','||
2134: l_err_rows(i).qty_rework||','||l_err_rows(i).qty_uom||','||l_err_rows(i).qty_good||','||l_err_rows(i).qty_output||','||
2135: l_err_rows(i).segment_fk||','||l_err_rows(i).user_dim1_fk||','||l_err_rows(i).user_dim2_fk||','||l_err_rows(i).user_dim3_fk||','||
2136: l_err_rows(i).user_dim4_fk||','||l_err_rows(i).user_dim5_fk||','||l_err_rows(i).scrap_reason_code||','||l_err_rows(i).err_code
2133: l_err_rows(i).qty_completed||','||l_err_rows(i).qty_scrap||','||l_err_rows(i).qty_rejected||','||
2134: l_err_rows(i).qty_rework||','||l_err_rows(i).qty_uom||','||l_err_rows(i).qty_good||','||l_err_rows(i).qty_output||','||
2135: l_err_rows(i).segment_fk||','||l_err_rows(i).user_dim1_fk||','||l_err_rows(i).user_dim2_fk||','||l_err_rows(i).user_dim3_fk||','||
2136: l_err_rows(i).user_dim4_fk||','||l_err_rows(i).user_dim5_fk||','||l_err_rows(i).scrap_reason_code||','||l_err_rows(i).err_code
2137: ,mth_util_pkg.G_DBG_USER_ERROR);
2138: END LOOP;
2139: RAISE MTH_PROCESS_TXN_PKG.VALIDATION_ERR;
2140: END IF;
2141:
2249: AND stg.USER_DIM5_FK = mue5.ENTITY_PK (+)
2250: AND lkp.LOOKUP_TYPE (+) = 'MTH_SCRAP_REASON'
2251: AND stg.SCRAP_REASON_CODE = lkp.LOOKUP_CODE (+)
2252: AND stg.err_code IS NULL);
2253: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2254: v_count := SQL%ROWCOUNT;
2255:
2256: -- Insert into tag reason readings
2257: INSERT INTO MTH_TAG_REASON_READINGS (REASON_TYPE,
2286: AND med.status = 'ACTIVE'
2287: AND stg.ERR_CODE IS NULL
2288: AND stg.qty_scrap IS NOT NULL);
2289:
2290: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2291:
2292: IF (v_count > 0) THEN
2293: DELETE FROM MTH_EQUIP_OUTPUT_STG;
2294: END IF;
2292: IF (v_count > 0) THEN
2293: DELETE FROM MTH_EQUIP_OUTPUT_STG;
2294: END IF;
2295:
2296: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
2297: EXCEPTION
2298: WHEN EQUIP_MIS_ERR THEN
2299: --Call logging API and then throw exception
2300: mth_util_pkg.log_msg('Exception EQUIP_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2296: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
2297: EXCEPTION
2298: WHEN EQUIP_MIS_ERR THEN
2299: --Call logging API and then throw exception
2300: mth_util_pkg.log_msg('Exception EQUIP_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2301: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2302: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2303: RAISE;
2304: WHEN SITE_MIS_ERR THEN
2297: EXCEPTION
2298: WHEN EQUIP_MIS_ERR THEN
2299: --Call logging API and then throw exception
2300: mth_util_pkg.log_msg('Exception EQUIP_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2301: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2302: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2303: RAISE;
2304: WHEN SITE_MIS_ERR THEN
2305: --Call logging API and then throw exception
2298: WHEN EQUIP_MIS_ERR THEN
2299: --Call logging API and then throw exception
2300: mth_util_pkg.log_msg('Exception EQUIP_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2301: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2302: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2303: RAISE;
2304: WHEN SITE_MIS_ERR THEN
2305: --Call logging API and then throw exception
2306: mth_util_pkg.log_msg('Exception SITE_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2302: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2303: RAISE;
2304: WHEN SITE_MIS_ERR THEN
2305: --Call logging API and then throw exception
2306: mth_util_pkg.log_msg('Exception SITE_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2307: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2308: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2309: RAISE;
2310: WHEN GAP_IN_RECAL_CSV THEN
2303: RAISE;
2304: WHEN SITE_MIS_ERR THEN
2305: --Call logging API and then throw exception
2306: mth_util_pkg.log_msg('Exception SITE_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2307: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2308: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2309: RAISE;
2310: WHEN GAP_IN_RECAL_CSV THEN
2311: --Call logging API and then throw exception
2304: WHEN SITE_MIS_ERR THEN
2305: --Call logging API and then throw exception
2306: mth_util_pkg.log_msg('Exception SITE_MIS_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2307: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2308: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2309: RAISE;
2310: WHEN GAP_IN_RECAL_CSV THEN
2311: --Call logging API and then throw exception
2312: mth_util_pkg.log_msg('Exception GAP_IN_RECAL_CSV in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2308: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2309: RAISE;
2310: WHEN GAP_IN_RECAL_CSV THEN
2311: --Call logging API and then throw exception
2312: mth_util_pkg.log_msg('Exception GAP_IN_RECAL_CSV in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2313: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2314: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2315: RAISE;
2316: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
2309: RAISE;
2310: WHEN GAP_IN_RECAL_CSV THEN
2311: --Call logging API and then throw exception
2312: mth_util_pkg.log_msg('Exception GAP_IN_RECAL_CSV in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2313: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2314: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2315: RAISE;
2316: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
2317: --Call logging API and then throw exception
2310: WHEN GAP_IN_RECAL_CSV THEN
2311: --Call logging API and then throw exception
2312: mth_util_pkg.log_msg('Exception GAP_IN_RECAL_CSV in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2313: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2314: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2315: RAISE;
2316: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
2317: --Call logging API and then throw exception
2318: mth_util_pkg.log_msg('Exception VALIDATION_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2314: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2315: RAISE;
2316: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
2317: --Call logging API and then throw exception
2318: mth_util_pkg.log_msg('Exception VALIDATION_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2319: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2320: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2321: RAISE;
2322: WHEN OTHERS THEN
2315: RAISE;
2316: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
2317: --Call logging API and then throw exception
2318: mth_util_pkg.log_msg('Exception VALIDATION_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2319: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2320: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2321: RAISE;
2322: WHEN OTHERS THEN
2323: --Call logging API and then throw exception
2316: WHEN MTH_PROCESS_TXN_PKG.VALIDATION_ERR THEN
2317: --Call logging API and then throw exception
2318: mth_util_pkg.log_msg('Exception VALIDATION_ERR in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2319: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2320: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2321: RAISE;
2322: WHEN OTHERS THEN
2323: --Call logging API and then throw exception
2324: mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2320: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2321: RAISE;
2322: WHEN OTHERS THEN
2323: --Call logging API and then throw exception
2324: mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2325: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2326: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2327: RAISE;
2328: END;
2321: RAISE;
2322: WHEN OTHERS THEN
2323: --Call logging API and then throw exception
2324: mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2325: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2326: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2327: RAISE;
2328: END;
2329:
2322: WHEN OTHERS THEN
2323: --Call logging API and then throw exception
2324: mth_util_pkg.log_msg('Exception OTHERS in RECAL_OUTPUT_FROM_CSV', mth_util_pkg.G_DBG_EXCEPTION);
2325: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2326: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2327: RAISE;
2328: END;
2329:
2330:
2351: IS
2352: l_ret_code NUMBER;
2353: BEGIN
2354: l_ret_code := 0;
2355: mth_util_pkg.log_msg('PROCESS_OUTPUT_SUMMARY start', mth_util_pkg.G_DBG_PROC_FUN_START);
2356: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
2357: 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);
2358: 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);
2359: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
2352: l_ret_code NUMBER;
2353: BEGIN
2354: l_ret_code := 0;
2355: mth_util_pkg.log_msg('PROCESS_OUTPUT_SUMMARY start', mth_util_pkg.G_DBG_PROC_FUN_START);
2356: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
2357: 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);
2358: 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);
2359: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
2360:
2353: BEGIN
2354: l_ret_code := 0;
2355: mth_util_pkg.log_msg('PROCESS_OUTPUT_SUMMARY start', mth_util_pkg.G_DBG_PROC_FUN_START);
2356: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
2357: 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);
2358: 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);
2359: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
2360:
2361: IF p_mode = 'INIT' THEN
2354: l_ret_code := 0;
2355: mth_util_pkg.log_msg('PROCESS_OUTPUT_SUMMARY start', mth_util_pkg.G_DBG_PROC_FUN_START);
2356: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
2357: 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);
2358: 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);
2359: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
2360:
2361: IF p_mode = 'INIT' THEN
2362: PROCESS_OUTPUT_SMMRY_INIT();
2355: mth_util_pkg.log_msg('PROCESS_OUTPUT_SUMMARY start', mth_util_pkg.G_DBG_PROC_FUN_START);
2356: mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
2357: 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);
2358: 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);
2359: mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
2360:
2361: IF p_mode = 'INIT' THEN
2362: PROCESS_OUTPUT_SMMRY_INIT();
2363: ELSIF p_mode = 'INCR' THEN
2371: );
2372: END IF;
2373:
2374: p_ret_code := l_ret_code;
2375: mth_util_pkg.log_msg('PROCESS_OUTPUT_SUMMARY end', mth_util_pkg.G_DBG_PROC_FUN_END);
2376:
2377: EXCEPTION
2378: WHEN OTHERS THEN
2379: l_ret_code := 2;
2377: EXCEPTION
2378: WHEN OTHERS THEN
2379: l_ret_code := 2;
2380: p_ret_code := l_ret_code;
2381: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SUMMARY', mth_util_pkg.G_DBG_EXCEPTION);
2382: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2383: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2384: RAISE;
2385: END PROCESS_OUTPUT_SUMMARY;
2378: WHEN OTHERS THEN
2379: l_ret_code := 2;
2380: p_ret_code := l_ret_code;
2381: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SUMMARY', mth_util_pkg.G_DBG_EXCEPTION);
2382: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2383: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2384: RAISE;
2385: END PROCESS_OUTPUT_SUMMARY;
2386:
2379: l_ret_code := 2;
2380: p_ret_code := l_ret_code;
2381: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SUMMARY', mth_util_pkg.G_DBG_EXCEPTION);
2382: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2383: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2384: RAISE;
2385: END PROCESS_OUTPUT_SUMMARY;
2386:
2387: /*******************************************************************************
2401: v_ua_val VARCHAR2(30);
2402: v_run_log_to_date DATE;
2403: v_run_log_from_date DATE;
2404: BEGIN
2405: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INIT start', mth_util_pkg.G_DBG_PROC_FUN_START);
2406: -- Initialize default parameters
2407: v_log_date := sysdate;
2408: v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2409:
2404: BEGIN
2405: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INIT start', mth_util_pkg.G_DBG_PROC_FUN_START);
2406: -- Initialize default parameters
2407: v_log_date := sysdate;
2408: v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2409:
2410: -- Call mth_run_log_pre_load
2411: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);
2412:
2407: v_log_date := sysdate;
2408: v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2409:
2410: -- Call mth_run_log_pre_load
2411: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);
2412:
2413: -- Call delete output summary to delete all data from MTH_EQUIP_OUTPUT_SUMMARY table as well update status summary table
2414: UPDATE MTH_EQUIP_STATUS_SUMMARY
2415: SET wo_item_count = null,
2413: -- Call delete output summary to delete all data from MTH_EQUIP_OUTPUT_SUMMARY table as well update status summary table
2414: UPDATE MTH_EQUIP_STATUS_SUMMARY
2415: SET wo_item_count = null,
2416: required_hours = null;
2417: mth_util_pkg.log_msg('Number of rows updated in status summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2418:
2419: DELETE FROM MTH_EQUIP_OUTPUT_SUMMARY;
2420: mth_util_pkg.log_msg('Number of rows deleted from output summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2421:
2416: required_hours = null;
2417: mth_util_pkg.log_msg('Number of rows updated in status summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2418:
2419: DELETE FROM MTH_EQUIP_OUTPUT_SUMMARY;
2420: mth_util_pkg.log_msg('Number of rows deleted from output summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2421:
2422: -- Get to and from date from run log
2423: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT_SUMMARY',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
2424:
2419: DELETE FROM MTH_EQUIP_OUTPUT_SUMMARY;
2420: mth_util_pkg.log_msg('Number of rows deleted from output summary - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2421:
2422: -- Get to and from date from run log
2423: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT_SUMMARY',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
2424:
2425: -- Process data from output to be inserted to output summary
2426: INSERT
2427: INTO
2502: meo.segment_fk_key,
2503: meo.hour_fk_key );
2504:
2505: -- Call the logging API to log the number of rows inserted
2506: mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2507:
2508: UPDATE
2509: MTH_EQUIP_STATUS_SUMMARY mes
2510: SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
2565: AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
2566: AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
2567:
2568: -- Call the logging API to log the number of rows updated
2569: mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2570:
2571: -- Call mth_run_log_post_load
2572: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
2573:
2568: -- Call the logging API to log the number of rows updated
2569: mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2570:
2571: -- Call mth_run_log_post_load
2572: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
2573:
2574: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INIT end', mth_util_pkg.G_DBG_PROC_FUN_END);
2575: EXCEPTION
2576: WHEN OTHERS THEN
2570:
2571: -- Call mth_run_log_post_load
2572: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
2573:
2574: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INIT end', mth_util_pkg.G_DBG_PROC_FUN_END);
2575: EXCEPTION
2576: WHEN OTHERS THEN
2577: --Call logging API and then throw exception
2578: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INIT', mth_util_pkg.G_DBG_EXCEPTION);
2574: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INIT end', mth_util_pkg.G_DBG_PROC_FUN_END);
2575: EXCEPTION
2576: WHEN OTHERS THEN
2577: --Call logging API and then throw exception
2578: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INIT', mth_util_pkg.G_DBG_EXCEPTION);
2579: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2580: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2581: RAISE;
2582: END PROCESS_OUTPUT_SMMRY_INIT;
2575: EXCEPTION
2576: WHEN OTHERS THEN
2577: --Call logging API and then throw exception
2578: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INIT', mth_util_pkg.G_DBG_EXCEPTION);
2579: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2580: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2581: RAISE;
2582: END PROCESS_OUTPUT_SMMRY_INIT;
2583:
2576: WHEN OTHERS THEN
2577: --Call logging API and then throw exception
2578: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INIT', mth_util_pkg.G_DBG_EXCEPTION);
2579: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2580: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2581: RAISE;
2582: END PROCESS_OUTPUT_SMMRY_INIT;
2583:
2584: /*******************************************************************************
2598: v_ua_val VARCHAR2(30);
2599: v_run_log_to_date DATE;
2600: v_run_log_from_date DATE;
2601: BEGIN
2602: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INCR start', mth_util_pkg.G_DBG_PROC_FUN_START);
2603: -- Initialize default parameters
2604: v_log_date := sysdate;
2605: v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2606:
2601: BEGIN
2602: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INCR start', mth_util_pkg.G_DBG_PROC_FUN_START);
2603: -- Initialize default parameters
2604: v_log_date := sysdate;
2605: v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2606:
2607: -- Call mth_run_log_pre_load
2608: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INCR',NULL,0,v_log_date);
2609:
2604: v_log_date := sysdate;
2605: v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2606:
2607: -- Call mth_run_log_pre_load
2608: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INCR',NULL,0,v_log_date);
2609:
2610: -- Get to and from date from run log
2611: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT_SUMMARY',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
2612:
2607: -- Call mth_run_log_pre_load
2608: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INCR',NULL,0,v_log_date);
2609:
2610: -- Get to and from date from run log
2611: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT_SUMMARY',NULL,NULL,NULL,v_run_log_from_date,v_run_log_to_date);
2612:
2613: UPDATE
2614: MTH_EQUIP_STATUS_SUMMARY mes
2615: SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
2754: AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
2755: AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
2756:
2757: -- Call the logging API to log the number of rows updated
2758: mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2759:
2760: -- Process data from output to be updated/inserted to output summary
2761: MERGE
2762: INTO
2895: subquery.sq_level9_level_key,
2896: subquery.sq_resource_cost);
2897:
2898: -- Call the logging API to log the number of rows merged
2899: mth_util_pkg.log_msg('Rows merged in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2900:
2901:
2902: -- Call mth_run_log_pre_load
2903: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
2899: mth_util_pkg.log_msg('Rows merged in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
2900:
2901:
2902: -- Call mth_run_log_pre_load
2903: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
2904: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INCR end', mth_util_pkg.G_DBG_PROC_FUN_END);
2905:
2906: EXCEPTION
2907: WHEN OTHERS THEN
2900:
2901:
2902: -- Call mth_run_log_pre_load
2903: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
2904: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_INCR end', mth_util_pkg.G_DBG_PROC_FUN_END);
2905:
2906: EXCEPTION
2907: WHEN OTHERS THEN
2908: --Call logging API and then throw exception
2905:
2906: EXCEPTION
2907: WHEN OTHERS THEN
2908: --Call logging API and then throw exception
2909: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INCR', mth_util_pkg.G_DBG_EXCEPTION);
2910: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2911: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2912: RAISE;
2913: END PROCESS_OUTPUT_SMMRY_INCR;
2906: EXCEPTION
2907: WHEN OTHERS THEN
2908: --Call logging API and then throw exception
2909: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INCR', mth_util_pkg.G_DBG_EXCEPTION);
2910: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2911: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2912: RAISE;
2913: END PROCESS_OUTPUT_SMMRY_INCR;
2914:
2907: WHEN OTHERS THEN
2908: --Call logging API and then throw exception
2909: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_INCR', mth_util_pkg.G_DBG_EXCEPTION);
2910: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2911: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2912: RAISE;
2913: END PROCESS_OUTPUT_SMMRY_INCR;
2914:
2915: /*******************************************************************************
2959: FROM MTH_EQUIP_OUTPUT;
2960:
2961:
2962: BEGIN
2963: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL start', mth_util_pkg.G_DBG_PROC_FUN_START);
2964: 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);
2965: 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);
2966: mth_util_pkg.log_msg('p_recalc_equip_key = ' || p_recalc_equip_key, mth_util_pkg.G_DBG_PARAM_VAL);
2967:
2960:
2961:
2962: BEGIN
2963: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL start', mth_util_pkg.G_DBG_PROC_FUN_START);
2964: 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);
2965: 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);
2966: mth_util_pkg.log_msg('p_recalc_equip_key = ' || p_recalc_equip_key, mth_util_pkg.G_DBG_PARAM_VAL);
2967:
2968: -- Validate the parameters passed
2961:
2962: BEGIN
2963: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL start', mth_util_pkg.G_DBG_PROC_FUN_START);
2964: 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);
2965: 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);
2966: mth_util_pkg.log_msg('p_recalc_equip_key = ' || p_recalc_equip_key, mth_util_pkg.G_DBG_PARAM_VAL);
2967:
2968: -- Validate the parameters passed
2969: IF (p_recalc_from_date IS NULL)
2962: BEGIN
2963: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL start', mth_util_pkg.G_DBG_PROC_FUN_START);
2964: 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);
2965: 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);
2966: mth_util_pkg.log_msg('p_recalc_equip_key = ' || p_recalc_equip_key, mth_util_pkg.G_DBG_PARAM_VAL);
2967:
2968: -- Validate the parameters passed
2969: IF (p_recalc_from_date IS NULL)
2970: THEN
2980: -- Fetch from date for the p_recalc_from_date
2981: OPEN c_fetch_from_date;
2982:
2983: FETCH c_fetch_from_date INTO p_n_recalc_from_date;
2984: 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);
2985:
2986: CLOSE c_fetch_from_date;
2987:
2988: IF p_recalc_to_date IS NULL
2990: -- Fetch to date for the p_recalc_to_date
2991: OPEN c_fetch_recalc_to_date;
2992:
2993: FETCH c_fetch_recalc_to_date INTO v_recalc_to_date;
2994: mth_util_pkg.log_msg('Max of reading time for output when the recalculation to date passed is null : '||v_recalc_to_date, mth_util_pkg.G_DBG_PARAM_VAL);
2995:
2996: CLOSE c_fetch_recalc_to_date;
2997: ELSE
2998: v_recalc_to_date := p_recalc_to_date;
3001: -- Fetch to date for the p_recalc_to_date
3002: OPEN c_fetch_to_date;
3003:
3004: FETCH c_fetch_to_date INTO p_n_recalc_to_date;
3005: 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);
3006:
3007: CLOSE c_fetch_to_date;
3008:
3009: -- Initialize default parameters
3007: CLOSE c_fetch_to_date;
3008:
3009: -- Initialize default parameters
3010: v_log_date := sysdate;
3011: v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3012:
3013: -- Call mth_run_log_pre_load
3014: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);
3015:
3010: v_log_date := sysdate;
3011: v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3012:
3013: -- Call mth_run_log_pre_load
3014: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);
3015:
3016: -- Call delete output summary to delete all data from MTH_EQUIP_OUTPUT_SUMMARY table as well update status summary table
3017: -- for particular hour fk keys that contains the recalculation from and to dates
3018: IF (p_recalc_plant_key IS NULL ) THEN
3038: WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key));
3039: END IF;
3040:
3041: -- Call the logging API to log the number of rows inserted
3042: mth_util_pkg.log_msg('Rows deleted from MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3043: IF (p_recalc_plant_key IS NULL ) THEN
3044: DELETE
3045: FROM MTH_EQUIP_OUTPUT_SUMMARY
3046: WHERE hour_fk_key IN (SELECT hour_pk_key
3061: WHERE plant_fk_key = Nvl(p_recalc_plant_key,plant_fk_key));
3062: END IF;
3063:
3064: -- Call the logging API to log the number of rows inserted
3065: mth_util_pkg.log_msg('Rows deleted from MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3066:
3067: -- Process data from output to be inserted to output summary
3068: INSERT
3069: INTO
3151: meo.segment_fk_key,
3152: meo.hour_fk_key );
3153:
3154: -- Call the logging API to log the number of rows inserted
3155: mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3156:
3157: UPDATE
3158: MTH_EQUIP_STATUS_SUMMARY mes
3159: SET (mes.WO_ITEM_COUNT,mes.REQUIRED_HOURS,
3230: AND mes.SHIFT_WORKDAY_FK_KEY = meos.SHIFT_WORKDAY_FK_KEY
3231: AND mes.HOUR_FK_KEY = meos.HOUR_FK_KEY);
3232:
3233: -- Call the logging API to log the number of rows updated
3234: mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3235: -- Call mth_run_log_pre_load
3236: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
3237: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL end', mth_util_pkg.G_DBG_PROC_FUN_END);
3238:
3232:
3233: -- Call the logging API to log the number of rows updated
3234: mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3235: -- Call mth_run_log_pre_load
3236: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
3237: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL end', mth_util_pkg.G_DBG_PROC_FUN_END);
3238:
3239: EXCEPTION
3240: WHEN NULL_DATES THEN
3233: -- Call the logging API to log the number of rows updated
3234: mth_util_pkg.log_msg('Rows updated in MTH_EQUIP_STATUS_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);
3235: -- Call mth_run_log_pre_load
3236: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);
3237: mth_util_pkg.log_msg('PROCESS_OUTPUT_SMMRY_RECAL end', mth_util_pkg.G_DBG_PROC_FUN_END);
3238:
3239: EXCEPTION
3240: WHEN NULL_DATES THEN
3241: --Call logging API and then throw exception
3238:
3239: EXCEPTION
3240: WHEN NULL_DATES THEN
3241: --Call logging API and then throw exception
3242: mth_util_pkg.log_msg('Exception NULL_DATES in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3243: mth_util_pkg.log_msg('Recalculation from date passed is null. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
3244: mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
3245: RAISE;
3246: WHEN DATE_VAL THEN
3239: EXCEPTION
3240: WHEN NULL_DATES THEN
3241: --Call logging API and then throw exception
3242: mth_util_pkg.log_msg('Exception NULL_DATES in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3243: mth_util_pkg.log_msg('Recalculation from date passed is null. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
3244: mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
3245: RAISE;
3246: WHEN DATE_VAL THEN
3247: --Call logging API and then throw exception
3240: WHEN NULL_DATES THEN
3241: --Call logging API and then throw exception
3242: mth_util_pkg.log_msg('Exception NULL_DATES in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3243: mth_util_pkg.log_msg('Recalculation from date passed is null. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
3244: mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
3245: RAISE;
3246: WHEN DATE_VAL THEN
3247: --Call logging API and then throw exception
3248: mth_util_pkg.log_msg('Exception DATE_VAL in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3244: mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
3245: RAISE;
3246: WHEN DATE_VAL THEN
3247: --Call logging API and then throw exception
3248: mth_util_pkg.log_msg('Exception DATE_VAL in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3249: mth_util_pkg.log_msg('Recalculation from date is greater than recalculation to date. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
3250: mth_util_pkg.log_msg(-20002, mth_util_pkg.G_DBG_EXCEPTION);
3251: RAISE;
3252: WHEN OTHERS THEN
3245: RAISE;
3246: WHEN DATE_VAL THEN
3247: --Call logging API and then throw exception
3248: mth_util_pkg.log_msg('Exception DATE_VAL in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3249: mth_util_pkg.log_msg('Recalculation from date is greater than recalculation to date. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
3250: mth_util_pkg.log_msg(-20002, mth_util_pkg.G_DBG_EXCEPTION);
3251: RAISE;
3252: WHEN OTHERS THEN
3253: --Call logging API and then throw exception
3246: WHEN DATE_VAL THEN
3247: --Call logging API and then throw exception
3248: mth_util_pkg.log_msg('Exception DATE_VAL in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3249: mth_util_pkg.log_msg('Recalculation from date is greater than recalculation to date. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
3250: mth_util_pkg.log_msg(-20002, mth_util_pkg.G_DBG_EXCEPTION);
3251: RAISE;
3252: WHEN OTHERS THEN
3253: --Call logging API and then throw exception
3254: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3250: mth_util_pkg.log_msg(-20002, mth_util_pkg.G_DBG_EXCEPTION);
3251: RAISE;
3252: WHEN OTHERS THEN
3253: --Call logging API and then throw exception
3254: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3255: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3256: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3257: RAISE;
3258: END PROCESS_OUTPUT_SMMRY_RECAL;
3251: RAISE;
3252: WHEN OTHERS THEN
3253: --Call logging API and then throw exception
3254: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3255: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3256: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3257: RAISE;
3258: END PROCESS_OUTPUT_SMMRY_RECAL;
3259:
3252: WHEN OTHERS THEN
3253: --Call logging API and then throw exception
3254: mth_util_pkg.log_msg('Exception OTHERS in PROCESS_OUTPUT_SMMRY_RECAL', mth_util_pkg.G_DBG_EXCEPTION);
3255: mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
3256: mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
3257: RAISE;
3258: END PROCESS_OUTPUT_SMMRY_RECAL;
3259:
3260: END MTH_PROCESS_OUTPUT_PKG;