APPS.MTH_PROCESS_OUTPUT_PKG dependencies on MTH_EQUIP_OUTPUT

Line 63: AND mrl.fact_table = 'MTH_EQUIP_OUTPUT'

59: 'Rejected Quantity',
60: 'Output Quantity',
61: 'Rework Quantity',
62: 'Completed Quantity')
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);

Line 75: FROM mth_equip_output meo,

71:
72: IF( p_mode = 'RECAL' ) THEN
73: SELECT Count(1)
74: INTO v_output_count
75: FROM mth_equip_output meo,
76: mth_run_log mrl
77: WHERE mrl.fact_table = 'MTH_EQUIP_OUTPUT_SUMMARY'
78: AND meo.last_update_date > mrl.from_date;
79:

Line 77: WHERE mrl.fact_table = 'MTH_EQUIP_OUTPUT_SUMMARY'

73: SELECT Count(1)
74: INTO v_output_count
75: FROM mth_equip_output meo,
76: mth_run_log mrl
77: WHERE mrl.fact_table = 'MTH_EQUIP_OUTPUT_SUMMARY'
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');

Line 221: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INITIAL',NULL,0,v_log_date);

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);

Line 224: DELETE FROM MTH_EQUIP_OUTPUT;

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);
226:
227: INSERT INTO MTH_EQUIP_OUTPUT (
228: EQUIPMENT_FK_KEY,

Line 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,

Line 227: INSERT INTO MTH_EQUIP_OUTPUT (

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,
230: SHIFT_WORKDAY_FK_KEY,
231: WORKORDER_FK_KEY,

Line 289: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 311: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);

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

Line 347: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT',v_unassigned_val,'INCR',NULL,0,v_log_to_date);

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:

Line 350: mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIP_OUTPUT',NULL,NULL,NULL,v_log_from_date,v_log_to_date);

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,

Line 353: MERGE INTO MTH_EQUIP_OUTPUT o USING

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,
355: a.ITEM_FK_KEY,
356: a.SHIFT_WORKDAY_FK_KEY,
357: a.WORKORDER_FK_KEY,

Line 457: mth_util_pkg.log_msg('Number of rows merged in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 479: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT',v_unassigned_val);

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

Line 520: DELETE FROM MTH_EQUIP_OUTPUT o

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)
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:

Line 523: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 525: INSERT INTO MTH_EQUIP_OUTPUT (

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,
528: SHIFT_WORKDAY_FK_KEY,
529: WORKORDER_FK_KEY,

Line 588: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 640: DELETE FROM MTH_EQUIP_OUTPUT;

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;
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,

Line 641: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 644: INSERT INTO mth_equip_output_stg(equipment_fk,

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,
646: shift_workday_fk,
647: workorder_fk,
648: reading_time,

Line 708: FROM mth_equip_output_err

704: user_measure3,
705: user_measure4,
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

Line 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);

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';

Line 713: DELETE 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';
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);

Line 715: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || 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';
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

Line 717: mth_util_pkg.switch_column_default_value('MTH_EQUIP_OUTPUT_STG',v_processing_flag);

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

Line 722: UPDATE mth_equip_output_stg stg

718:
719: --Execute all validations on csv records
720:
721: -- Validation for Invalid Item
722: UPDATE mth_equip_output_stg stg
723: SET stg.err_code = stg.err_code || 'ITM '
724: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
725: FROM mth_items_d mid,
726: mth_equip_output_stg stg

Line 726: mth_equip_output_stg stg

722: UPDATE mth_equip_output_stg stg
723: SET stg.err_code = stg.err_code || 'ITM '
724: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
725: FROM mth_items_d mid,
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);

Line 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);

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 '

Line 733: UPDATE mth_equip_output_stg stg

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 '
735: WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
736: FROM mth_production_segments_f msf,
737: mth_equip_output_stg stg

Line 737: mth_equip_output_stg stg

733: UPDATE mth_equip_output_stg stg
734: SET stg.err_code = stg.err_code || 'SEG '
735: WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
736: FROM mth_production_segments_f msf,
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);

Line 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);

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 '

Line 744: UPDATE mth_equip_output_stg stg

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 '
746: WHERE stg.workorder_fk IS NOT NULL
747: AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
748: FROM mth_production_schedules_f mps,

Line 749: mth_equip_output_stg stg

745: SET stg.err_code = stg.err_code || 'WKO '
746: WHERE stg.workorder_fk IS NOT NULL
747: AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
748: FROM mth_production_schedules_f mps,
749: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 757: UPDATE mth_equip_output_stg stg

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 '
759: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
760: FROM mth_equipments_d med,
761: mth_equip_output_stg stg

Line 761: mth_equip_output_stg stg

757: UPDATE mth_equip_output_stg stg
758: SET stg.err_code = stg.err_code || 'EQP '
759: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
760: FROM mth_equipments_d med,
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);

Line 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);

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 '

Line 768: UPDATE mth_equip_output_stg stg

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 '
770: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
771: FROM mth_equipments_d med,
772: mth_equip_output_stg stg

Line 772: mth_equip_output_stg stg

768: UPDATE mth_equip_output_stg stg
769: SET stg.err_code = stg.err_code || 'IEQ '
770: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
771: FROM mth_equipments_d med,
772: mth_equip_output_stg stg
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 );

Line 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);

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 '

Line 780: UPDATE mth_equip_output_stg stg

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 '
782: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
783: FROM mth_equip_output_stg
784: GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup

Line 783: FROM mth_equip_output_stg

779: -- Validation for Duplicate record
780: UPDATE mth_equip_output_stg stg
781: SET stg.err_code = stg.err_code || 'DUP '
782: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
783: FROM mth_equip_output_stg
784: GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
785: WHERE dup.cnt>1
786: AND dup.equipment_fk = stg.equipment_fk
787: AND dup.shift_workday_fk = stg.shift_workday_fk

Line 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);

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 '

Line 796: UPDATE mth_equip_output_stg stg

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 '
798: WHERE stg.shift_workday_fk IS NOT NULL
799: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
800: FROM mth_workday_shifts_d mds,

Line 801: mth_equip_output_stg stg

797: SET stg.err_code = stg.err_code || 'WDS '
798: WHERE stg.shift_workday_fk IS NOT NULL
799: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
800: FROM mth_workday_shifts_d mds,
801: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 809: UPDATE mth_equip_output_stg stg

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 '
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);

Line 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);

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 '

Line 816: UPDATE mth_equip_output_stg stg

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 '
818: WHERE stg.user_dim1_fk IS NOT NULL
819: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
820: FROM mth_user_dim_entities_mst mue,

Line 821: mth_equip_output_stg stg

817: SET stg.err_code = stg.err_code || 'UD1 '
818: WHERE stg.user_dim1_fk IS NOT NULL
819: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
820: FROM mth_user_dim_entities_mst mue,
821: mth_equip_output_stg stg
822: WHERE stg.user_dim1_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 830: UPDATE mth_equip_output_stg stg

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 '
832: WHERE stg.user_dim2_fk IS NOT NULL
833: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
834: FROM mth_user_dim_entities_mst mue,

Line 835: mth_equip_output_stg stg

831: SET stg.err_code = stg.err_code || 'UD2 '
832: WHERE stg.user_dim2_fk IS NOT NULL
833: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
834: FROM mth_user_dim_entities_mst mue,
835: mth_equip_output_stg stg
836: WHERE stg.user_dim2_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 844: UPDATE mth_equip_output_stg stg

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 '
846: WHERE stg.user_dim3_fk IS NOT NULL
847: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
848: FROM mth_user_dim_entities_mst mue,

Line 849: mth_equip_output_stg stg

845: SET stg.err_code = stg.err_code || 'UD3 '
846: WHERE stg.user_dim3_fk IS NOT NULL
847: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
848: FROM mth_user_dim_entities_mst mue,
849: mth_equip_output_stg stg
850: WHERE stg.user_dim3_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 858: UPDATE mth_equip_output_stg stg

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 '
860: WHERE stg.user_dim4_fk IS NOT NULL
861: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
862: FROM mth_user_dim_entities_mst mue,

Line 863: mth_equip_output_stg stg

859: SET stg.err_code = stg.err_code || 'UD4 '
860: WHERE stg.user_dim4_fk IS NOT NULL
861: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
862: FROM mth_user_dim_entities_mst mue,
863: mth_equip_output_stg stg
864: WHERE stg.user_dim4_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 872: UPDATE mth_equip_output_stg stg

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 '
874: WHERE stg.user_dim5_fk IS NOT NULL
875: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
876: FROM mth_user_dim_entities_mst mue,

Line 877: mth_equip_output_stg stg

873: SET stg.err_code = stg.err_code || 'UD5 '
874: WHERE stg.user_dim5_fk IS NOT NULL
875: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
876: FROM mth_user_dim_entities_mst mue,
877: mth_equip_output_stg stg
878: WHERE stg.user_dim5_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 886: UPDATE mth_equip_output_stg stg

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 '
888: WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
889: FROM fnd_lookups flk,
890: mth_equip_output_stg stg

Line 890: mth_equip_output_stg stg

886: UPDATE mth_equip_output_stg stg
887: SET stg.err_code = stg.err_code || 'SPR '
888: WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
889: FROM fnd_lookups flk,
890: mth_equip_output_stg stg
891: WHERE stg.scrap_reason_code = flk.lookup_code (+)
892: AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
893: WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
894: ( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR

Line 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);

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 '

Line 903: UPDATE mth_equip_output_stg stg

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 '
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);

Line 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);

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 '

Line 910: UPDATE mth_equip_output_stg stg

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 '
912: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
913: FROM mth_workday_shifts_d mds,
914: mth_equip_output_stg stg,

Line 914: mth_equip_output_stg stg,

910: UPDATE mth_equip_output_stg stg
911: SET stg.err_code = stg.err_code || 'ITR '
912: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
913: FROM mth_workday_shifts_d mds,
914: mth_equip_output_stg stg,
915: mth_equipment_shifts_d mes,
916: mth_equipments_d med
917: WHERE stg.shift_workday_fk = mds.shift_workday_pk
918: AND stg.equipment_fk = med.equipment_pk

Line 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);

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

Line 932: UPDATE mth_equip_output_stg stg

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
933: SET stg.err_code = stg.err_code || 'DOP '
934: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
935: FROM mth_equip_output meo,
936: mth_equip_output_stg stg,

Line 935: FROM mth_equip_output meo,

931:
932: UPDATE mth_equip_output_stg stg
933: SET stg.err_code = stg.err_code || 'DOP '
934: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
935: FROM mth_equip_output meo,
936: mth_equip_output_stg stg,
937: mth_equipments_d med,
938: mth_workday_shifts_d wds
939: WHERE med.equipment_pk_key = meo.equipment_fk_key

Line 936: mth_equip_output_stg stg,

932: UPDATE mth_equip_output_stg stg
933: SET stg.err_code = stg.err_code || 'DOP '
934: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
935: FROM mth_equip_output meo,
936: mth_equip_output_stg stg,
937: mth_equipments_d med,
938: mth_workday_shifts_d wds
939: WHERE med.equipment_pk_key = meo.equipment_fk_key
940: AND wds.shift_workday_pk_key = meo.shift_workday_fk_key

Line 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);

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,

Line 950: --Insert records into mth_equip_output_err

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,
953: shift_workday_fk,
954: workorder_fk,

Line 951: INSERT INTO mth_equip_output_err(equipment_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,
953: shift_workday_fk,
954: workorder_fk,
955: reading_time,

Line 1019: FROM mth_equip_output_stg

1015: user_measure5,
1016: scrap_reason_code,
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

Line 1021: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 1023: --Insert records into MTH_EQUIP_OUTPUT table

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,
1026: shift_workday_fk_key,
1027: workorder_fk_key,

Line 1024: INSERT INTO mth_equip_output( equipment_fk_key,

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,
1026: shift_workday_fk_key,
1027: workorder_fk_key,
1028: reading_time,

Line 1102: FROM mth_equip_output_stg stg,

1098: null,
1099: null,
1100: null,
1101: mhd.hour_pk_key
1102: FROM mth_equip_output_stg stg,
1103: mth_equipments_d med,
1104: mth_workday_shifts_d wds,
1105: mth_items_d mid,
1106: mth_production_segments_f msf,

Line 1132: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 1161: FROM mth_equip_output_stg stg,

1157: NULL,
1158: NULL,
1159: NULL,
1160: stg.reading_time
1161: FROM mth_equip_output_stg stg,
1162: mth_equipments_d med
1163: WHERE med.equipment_pk = stg.equipment_fk
1164: AND med.status = 'ACTIVE'
1165: AND stg.processing_flag = v_processing_flag

Line 1171: mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);

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

Line 1204: INSERT INTO mth_equip_output_stg(equipment_fk,

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,
1206: shift_workday_fk,
1207: workorder_fk,
1208: reading_time,

Line 1268: FROM mth_equip_output_err

1264: user_measure3,
1265: user_measure4,
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

Line 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);

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';

Line 1273: DELETE 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';
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);

Line 1275: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_OUTPUT_ERR - ' || 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';
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

Line 1277: mth_util_pkg.switch_column_default_value('MTH_EQUIP_OUTPUT_STG',v_processing_flag);

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

Line 1282: UPDATE mth_equip_output_stg stg

1278:
1279: --Execute all validations on csv records
1280:
1281: -- Validation for Invalid Item
1282: UPDATE mth_equip_output_stg stg
1283: SET stg.err_code = stg.err_code || 'ITM '
1284: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
1285: FROM mth_items_d mid,
1286: mth_equip_output_stg stg

Line 1286: mth_equip_output_stg stg

1282: UPDATE mth_equip_output_stg stg
1283: SET stg.err_code = stg.err_code || 'ITM '
1284: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
1285: FROM mth_items_d mid,
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);

Line 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);

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 '

Line 1293: UPDATE mth_equip_output_stg stg

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 '
1295: WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
1296: FROM mth_production_segments_f msf,
1297: mth_equip_output_stg stg

Line 1297: mth_equip_output_stg stg

1293: UPDATE mth_equip_output_stg stg
1294: SET stg.err_code = stg.err_code || 'SEG '
1295: WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
1296: FROM mth_production_segments_f msf,
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);

Line 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);

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 '

Line 1304: UPDATE mth_equip_output_stg stg

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 '
1306: WHERE stg.workorder_fk IS NOT NULL
1307: AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
1308: FROM mth_production_schedules_f mps,

Line 1309: mth_equip_output_stg stg

1305: SET stg.err_code = stg.err_code || 'WKO '
1306: WHERE stg.workorder_fk IS NOT NULL
1307: AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
1308: FROM mth_production_schedules_f mps,
1309: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 1317: UPDATE mth_equip_output_stg stg

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 '
1319: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1320: FROM mth_equipments_d med,
1321: mth_equip_output_stg stg

Line 1321: mth_equip_output_stg stg

1317: UPDATE mth_equip_output_stg stg
1318: SET stg.err_code = stg.err_code || 'EQP '
1319: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1320: FROM mth_equipments_d med,
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);

Line 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);

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 '

Line 1328: UPDATE mth_equip_output_stg stg

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 '
1330: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1331: FROM mth_equipments_d med,
1332: mth_equip_output_stg stg

Line 1332: mth_equip_output_stg stg

1328: UPDATE mth_equip_output_stg stg
1329: SET stg.err_code = stg.err_code || 'IEQ '
1330: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1331: FROM mth_equipments_d med,
1332: mth_equip_output_stg stg
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 );

Line 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);

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 '

Line 1340: UPDATE mth_equip_output_stg stg

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 '
1342: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
1343: FROM mth_equip_output_stg
1344: GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup

Line 1343: FROM mth_equip_output_stg

1339: -- Validation for Duplicate record
1340: UPDATE mth_equip_output_stg stg
1341: SET stg.err_code = stg.err_code || 'DUP '
1342: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
1343: FROM mth_equip_output_stg
1344: GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
1345: WHERE dup.cnt>1
1346: AND dup.equipment_fk = stg.equipment_fk
1347: AND dup.shift_workday_fk = stg.shift_workday_fk

Line 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);

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 '

Line 1356: UPDATE mth_equip_output_stg stg

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 '
1358: WHERE stg.shift_workday_fk IS NOT NULL
1359: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1360: FROM mth_workday_shifts_d mds,

Line 1361: mth_equip_output_stg stg

1357: SET stg.err_code = stg.err_code || 'WDS '
1358: WHERE stg.shift_workday_fk IS NOT NULL
1359: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1360: FROM mth_workday_shifts_d mds,
1361: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 1369: UPDATE mth_equip_output_stg stg

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 '
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);

Line 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);

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 '

Line 1376: UPDATE mth_equip_output_stg stg

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 '
1378: WHERE stg.user_dim1_fk IS NOT NULL
1379: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
1380: FROM mth_user_dim_entities_mst mue,

Line 1381: mth_equip_output_stg stg

1377: SET stg.err_code = stg.err_code || 'UD1 '
1378: WHERE stg.user_dim1_fk IS NOT NULL
1379: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
1380: FROM mth_user_dim_entities_mst mue,
1381: mth_equip_output_stg stg
1382: WHERE stg.user_dim1_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 1390: UPDATE mth_equip_output_stg stg

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 '
1392: WHERE stg.user_dim2_fk IS NOT NULL
1393: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
1394: FROM mth_user_dim_entities_mst mue,

Line 1395: mth_equip_output_stg stg

1391: SET stg.err_code = stg.err_code || 'UD2 '
1392: WHERE stg.user_dim2_fk IS NOT NULL
1393: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
1394: FROM mth_user_dim_entities_mst mue,
1395: mth_equip_output_stg stg
1396: WHERE stg.user_dim2_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 1404: UPDATE mth_equip_output_stg stg

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 '
1406: WHERE stg.user_dim3_fk IS NOT NULL
1407: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
1408: FROM mth_user_dim_entities_mst mue,

Line 1409: mth_equip_output_stg stg

1405: SET stg.err_code = stg.err_code || 'UD3 '
1406: WHERE stg.user_dim3_fk IS NOT NULL
1407: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
1408: FROM mth_user_dim_entities_mst mue,
1409: mth_equip_output_stg stg
1410: WHERE stg.user_dim3_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 1418: UPDATE mth_equip_output_stg stg

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 '
1420: WHERE stg.user_dim4_fk IS NOT NULL
1421: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
1422: FROM mth_user_dim_entities_mst mue,

Line 1423: mth_equip_output_stg stg

1419: SET stg.err_code = stg.err_code || 'UD4 '
1420: WHERE stg.user_dim4_fk IS NOT NULL
1421: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
1422: FROM mth_user_dim_entities_mst mue,
1423: mth_equip_output_stg stg
1424: WHERE stg.user_dim4_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 1432: UPDATE mth_equip_output_stg stg

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 '
1434: WHERE stg.user_dim5_fk IS NOT NULL
1435: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
1436: FROM mth_user_dim_entities_mst mue,

Line 1437: mth_equip_output_stg stg

1433: SET stg.err_code = stg.err_code || 'UD5 '
1434: WHERE stg.user_dim5_fk IS NOT NULL
1435: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
1436: FROM mth_user_dim_entities_mst mue,
1437: mth_equip_output_stg stg
1438: WHERE stg.user_dim5_fk = mue.entity_pk (+)
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

Line 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);

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 '

Line 1446: UPDATE mth_equip_output_stg stg

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 '
1448: WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
1449: FROM fnd_lookups flk,
1450: mth_equip_output_stg stg

Line 1450: mth_equip_output_stg stg

1446: UPDATE mth_equip_output_stg stg
1447: SET stg.err_code = stg.err_code || 'SPR '
1448: WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
1449: FROM fnd_lookups flk,
1450: mth_equip_output_stg stg
1451: WHERE stg.scrap_reason_code = flk.lookup_code (+)
1452: AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
1453: WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
1454: ( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR

Line 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);

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 '

Line 1463: UPDATE mth_equip_output_stg stg

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 '
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);

Line 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);

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 '

Line 1470: UPDATE mth_equip_output_stg stg

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 '
1472: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
1473: FROM mth_workday_shifts_d mds,
1474: mth_equip_output_stg stg,

Line 1474: mth_equip_output_stg stg,

1470: UPDATE mth_equip_output_stg stg
1471: SET stg.err_code = stg.err_code || 'ITR '
1472: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
1473: FROM mth_workday_shifts_d mds,
1474: mth_equip_output_stg stg,
1475: mth_equipment_shifts_d mes,
1476: mth_equipments_d med
1477: WHERE stg.shift_workday_fk = mds.shift_workday_pk
1478: AND stg.equipment_fk = med.equipment_pk

Line 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);

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

Line 1492: UPDATE mth_equip_output_stg stg

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
1493: SET stg.err_code = stg.err_code || 'DOP '
1494: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
1495: FROM mth_equip_output meo,
1496: mth_equip_output_stg stg,

Line 1495: FROM mth_equip_output meo,

1491:
1492: UPDATE mth_equip_output_stg stg
1493: SET stg.err_code = stg.err_code || 'DOP '
1494: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
1495: FROM mth_equip_output meo,
1496: mth_equip_output_stg stg,
1497: mth_equipments_d med,
1498: mth_workday_shifts_d wds
1499: WHERE med.equipment_pk_key = meo.equipment_fk_key

Line 1496: mth_equip_output_stg stg,

1492: UPDATE mth_equip_output_stg stg
1493: SET stg.err_code = stg.err_code || 'DOP '
1494: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
1495: FROM mth_equip_output meo,
1496: mth_equip_output_stg stg,
1497: mth_equipments_d med,
1498: mth_workday_shifts_d wds
1499: WHERE med.equipment_pk_key = meo.equipment_fk_key
1500: AND wds.shift_workday_pk_key = meo.shift_workday_fk_key

Line 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);

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,

Line 1510: --Insert records into mth_equip_output_err

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,
1513: shift_workday_fk,
1514: workorder_fk,

Line 1511: INSERT INTO mth_equip_output_err(equipment_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,
1513: shift_workday_fk,
1514: workorder_fk,
1515: reading_time,

Line 1579: FROM mth_equip_output_stg

1575: user_measure5,
1576: scrap_reason_code,
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:

Line 1581: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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:

Line 1584: --Insert records into mth_equip_output

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:
1586: --Insert records into MTH_EQUIP_OUTPUT table
1587: INSERT INTO mth_equip_output( equipment_fk_key,
1588: item_fk_key,

Line 1586: --Insert records into MTH_EQUIP_OUTPUT table

1582:
1583:
1584: --Insert records into mth_equip_output
1585:
1586: --Insert records into MTH_EQUIP_OUTPUT table
1587: INSERT INTO mth_equip_output( equipment_fk_key,
1588: item_fk_key,
1589: shift_workday_fk_key,
1590: workorder_fk_key,

Line 1587: INSERT INTO mth_equip_output( equipment_fk_key,

1583:
1584: --Insert records into mth_equip_output
1585:
1586: --Insert records into MTH_EQUIP_OUTPUT table
1587: INSERT INTO mth_equip_output( equipment_fk_key,
1588: item_fk_key,
1589: shift_workday_fk_key,
1590: workorder_fk_key,
1591: reading_time,

Line 1665: FROM mth_equip_output_stg stg,

1661: null,
1662: null,
1663: null,
1664: mhd.hour_pk_key
1665: FROM mth_equip_output_stg stg,
1666: mth_equipments_d med,
1667: mth_workday_shifts_d wds,
1668: mth_items_d mid,
1669: mth_production_segments_f msf,

Line 1695: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 1724: FROM mth_equip_output_stg stg,

1720: NULL,
1721: NULL,
1722: NULL,
1723: stg.reading_time
1724: FROM mth_equip_output_stg stg,
1725: mth_equipments_d med
1726: WHERE med.equipment_pk = stg.equipment_fk
1727: AND med.status = 'ACTIVE'
1728: AND stg.processing_flag = v_processing_flag

Line 1734: mth_util_pkg.truncate_table_partition('MTH_EQUIP_OUTPUT_STG',v_processing_flag);

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

Line 1795: FROM mth_equip_output_stg

1791: user_dim4_fk,
1792: user_dim5_fk,
1793: scrap_reason_code,
1794: err_code
1795: FROM mth_equip_output_stg
1796: WHERE err_code IS NOT NULL;
1797:
1798: TYPE fetch_err_rows IS TABLE OF c_error_rows%ROWTYPE;
1799: l_err_rows fetch_err_rows;

Line 1829: MTH_equip_output_stg stg

1825: THEN
1826: SELECT Count(*)
1827: INTO l_count
1828: FROM mth_equipments_d med,
1829: MTH_equip_output_stg stg
1830: WHERE med.equipment_pk = stg.equipment_fk
1831: AND med.equipment_pk_key = p_equipment_pk_key
1832: AND med.plant_fk_key = Nvl(p_plant_pk_key,med.plant_fk_key);
1833: END IF;

Line 1845: FROM MTH_equip_output_stg stg

1841:
1842:
1843: SELECT Min(reading_time)
1844: INTO p_min_reading_time_csv
1845: FROM MTH_equip_output_stg stg
1846: WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
1847: FROM mth_equipments_d
1848: WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
1849:

Line 1852: FROM MTH_equip_output_stg stg

1848: WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
1849:
1850: SELECT Max(reading_time)
1851: INTO p_max_reading_time_csv
1852: FROM MTH_equip_output_stg stg
1853: WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
1854: FROM mth_equipments_d
1855: WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
1856:

Line 1868: FROM mth_equip_output

1864:
1865: --Delete records from output table for the recalculation time range
1866: IF (p_equipment_pk_key IS NULL AND p_plant_pk_key IS NOT NULL) THEN
1867: DELETE
1868: FROM mth_equip_output
1869: WHERE reading_time >= p_recal_from_date
1870: AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
1871: AND equipment_fk_key IN ( SELECT equipment_pk_key
1872: FROM mth_equipments_d

Line 1876: FROM mth_equip_output

1872: FROM mth_equipments_d
1873: WHERE plant_fk_key = p_plant_pk_key);
1874: ELSE
1875: DELETE
1876: FROM mth_equip_output
1877: WHERE reading_time >= p_recal_from_date
1878: AND reading_time <= Nvl(p_recal_to_date,SYSDATE)
1879: AND equipment_fk_key = NVL(p_equipment_pk_key,equipment_fk_key);
1880: END IF;

Line 1904: UPDATE mth_equip_output_stg stg

1900:
1901: --Execute all validations on csv records
1902:
1903: -- Validation for Invalid Item
1904: UPDATE mth_equip_output_stg stg
1905: SET stg.err_code = stg.err_code || 'ITM '
1906: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
1907: FROM mth_items_d mid,
1908: mth_equip_output_stg stg

Line 1908: mth_equip_output_stg stg

1904: UPDATE mth_equip_output_stg stg
1905: SET stg.err_code = stg.err_code || 'ITM '
1906: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mid.item_pk_key, mid.item_pk
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:

Line 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);

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 '

Line 1914: UPDATE mth_equip_output_stg stg

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 '
1916: WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
1917: FROM mth_production_segments_f msf,
1918: mth_equip_output_stg stg

Line 1918: mth_equip_output_stg stg

1914: UPDATE mth_equip_output_stg stg
1915: SET stg.err_code = stg.err_code || 'SEG '
1916: WHERE NOT EXISTS ( SELECT * FROM ( SELECT msf.segment_pk_key, msf.segment_pk
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:

Line 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);

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 '

Line 1924: UPDATE mth_equip_output_stg stg

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 '
1926: WHERE stg.workorder_fk IS NOT NULL
1927: AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
1928: FROM mth_production_schedules_f mps,

Line 1929: mth_equip_output_stg stg

1925: SET stg.err_code = stg.err_code || 'WKO '
1926: WHERE stg.workorder_fk IS NOT NULL
1927: AND NOT EXISTS ( SELECT * FROM ( SELECT mps.workorder_pk,mps.workorder_pk_key
1928: FROM mth_production_schedules_f mps,
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);

Line 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);

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 '

Line 1936: UPDATE mth_equip_output_stg stg

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 '
1938: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1939: FROM mth_equipments_d med,
1940: mth_equip_output_stg stg

Line 1940: mth_equip_output_stg stg

1936: UPDATE mth_equip_output_stg stg
1937: SET stg.err_code = stg.err_code || 'EQP '
1938: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
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:

Line 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);

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 '

Line 1946: UPDATE mth_equip_output_stg stg

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 '
1948: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1949: FROM mth_equipments_d med,
1950: mth_equip_output_stg stg

Line 1950: mth_equip_output_stg stg

1946: UPDATE mth_equip_output_stg stg
1947: SET stg.err_code = stg.err_code || 'IEQ '
1948: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1949: FROM mth_equipments_d med,
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);

Line 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);

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 '

Line 1957: UPDATE mth_equip_output_stg stg

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 '
1959: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
1960: FROM mth_equip_output_stg
1961: GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup

Line 1960: FROM mth_equip_output_stg

1956: -- Validation for Duplicate record
1957: UPDATE mth_equip_output_stg stg
1958: SET stg.err_code = stg.err_code || 'DUP '
1959: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk,Count(equipment_fk) cnt
1960: FROM mth_equip_output_stg
1961: GROUP BY equipment_fk,shift_workday_fk,reading_time,item_fk,workorder_fk,segment_fk) dup
1962: WHERE dup.cnt>1
1963: AND dup.equipment_fk = stg.equipment_fk
1964: AND dup.shift_workday_fk = stg.shift_workday_fk

Line 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);

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 '

Line 1972: UPDATE mth_equip_output_stg stg

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 '
1974: WHERE stg.shift_workday_fk IS NOT NULL
1975: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1976: FROM mth_workday_shifts_d mds,

Line 1977: mth_equip_output_stg stg

1973: SET stg.err_code = stg.err_code || 'WDS '
1974: WHERE stg.shift_workday_fk IS NOT NULL
1975: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1976: FROM mth_workday_shifts_d mds,
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);

Line 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);

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 '

Line 1984: UPDATE mth_equip_output_stg stg

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 '
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:

Line 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);

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 '

Line 1990: UPDATE mth_equip_output_stg stg

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 '
1992: WHERE stg.user_dim1_fk IS NOT NULL
1993: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
1994: FROM mth_user_dim_entities_mst mue,

Line 1995: mth_equip_output_stg stg

1991: SET stg.err_code = stg.err_code || 'UD1 '
1992: WHERE stg.user_dim1_fk IS NOT NULL
1993: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim1_fk
1994: FROM mth_user_dim_entities_mst mue,
1995: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 2003: UPDATE mth_equip_output_stg stg

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 '
2005: WHERE stg.user_dim2_fk IS NOT NULL
2006: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
2007: FROM mth_user_dim_entities_mst mue,

Line 2008: mth_equip_output_stg stg

2004: SET stg.err_code = stg.err_code || 'UD2 '
2005: WHERE stg.user_dim2_fk IS NOT NULL
2006: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim2_fk
2007: FROM mth_user_dim_entities_mst mue,
2008: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 2016: UPDATE mth_equip_output_stg stg

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 '
2018: WHERE stg.user_dim3_fk IS NOT NULL
2019: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
2020: FROM mth_user_dim_entities_mst mue,

Line 2021: mth_equip_output_stg stg

2017: SET stg.err_code = stg.err_code || 'UD3 '
2018: WHERE stg.user_dim3_fk IS NOT NULL
2019: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim3_fk
2020: FROM mth_user_dim_entities_mst mue,
2021: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 2029: UPDATE mth_equip_output_stg stg

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 '
2031: WHERE stg.user_dim4_fk IS NOT NULL
2032: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
2033: FROM mth_user_dim_entities_mst mue,

Line 2034: mth_equip_output_stg stg

2030: SET stg.err_code = stg.err_code || 'UD4 '
2031: WHERE stg.user_dim4_fk IS NOT NULL
2032: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim4_fk
2033: FROM mth_user_dim_entities_mst mue,
2034: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 2042: UPDATE mth_equip_output_stg stg

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 '
2044: WHERE stg.user_dim5_fk IS NOT NULL
2045: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
2046: FROM mth_user_dim_entities_mst mue,

Line 2047: mth_equip_output_stg stg

2043: SET stg.err_code = stg.err_code || 'UD5 '
2044: WHERE stg.user_dim5_fk IS NOT NULL
2045: AND EXISTS (SELECT * FROM (SELECT mue.entity_pk, stg.user_dim5_fk
2046: FROM mth_user_dim_entities_mst mue,
2047: mth_equip_output_stg stg
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);

Line 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);

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 '

Line 2055: UPDATE mth_equip_output_stg stg

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 '
2057: WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
2058: FROM fnd_lookups flk,
2059: mth_equip_output_stg stg

Line 2059: mth_equip_output_stg stg

2055: UPDATE mth_equip_output_stg stg
2056: SET stg.err_code = stg.err_code || 'SPR '
2057: WHERE EXISTS (SELECT * FROM (SELECT flk.lookup_code, flk.lookup_type,stg.reading_time,stg.qty_scrap,stg.scrap_reason_code
2058: FROM fnd_lookups flk,
2059: mth_equip_output_stg stg
2060: WHERE stg.scrap_reason_code = flk.lookup_code (+)
2061: AND flk.lookup_type(+) = 'MTH_SCRAP_REASON') dtr
2062: WHERE ((stg.qty_scrap IS NULL OR stg.qty_scrap <= 0) AND stg.scrap_reason_code IS NOT NULL) OR
2063: ( dtr.lookup_code IS NULL AND stg.scrap_reason_code IS NOT NULL OR

Line 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);

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 '

Line 2071: UPDATE mth_equip_output_stg stg

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 '
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:

Line 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);

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 '

Line 2077: UPDATE mth_equip_output_stg stg

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 '
2079: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
2080: FROM mth_workday_shifts_d mds,
2081: mth_equip_output_stg stg,

Line 2081: mth_equip_output_stg stg,

2077: UPDATE mth_equip_output_stg stg
2078: SET stg.err_code = stg.err_code || 'ITR '
2079: WHERE NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.to_date
2080: FROM mth_workday_shifts_d mds,
2081: mth_equip_output_stg stg,
2082: mth_equipment_shifts_d mes,
2083: mth_equipments_d med
2084: WHERE stg.shift_workday_fk = mds.shift_workday_pk
2085: AND stg.equipment_fk = med.equipment_pk

Line 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);

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

Line 2098: UPDATE mth_equip_output_stg stg

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
2099: SET stg.err_code = stg.err_code || 'DOP '
2100: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
2101: FROM mth_equip_output meo,
2102: mth_equip_output_stg stg,

Line 2101: FROM mth_equip_output meo,

2097:
2098: UPDATE mth_equip_output_stg stg
2099: SET stg.err_code = stg.err_code || 'DOP '
2100: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
2101: FROM mth_equip_output meo,
2102: mth_equip_output_stg stg,
2103: mth_equipments_d med,
2104: mth_workday_shifts_d wds
2105: WHERE med.equipment_pk_key = meo.equipment_fk_key

Line 2102: mth_equip_output_stg stg,

2098: UPDATE mth_equip_output_stg stg
2099: SET stg.err_code = stg.err_code || 'DOP '
2100: WHERE EXISTS (SELECT * FROM (SELECT meo.reading_time,med.equipment_pk,wds.shift_workday_pk
2101: FROM mth_equip_output meo,
2102: mth_equip_output_stg stg,
2103: mth_equipments_d med,
2104: mth_workday_shifts_d wds
2105: WHERE med.equipment_pk_key = meo.equipment_fk_key
2106: AND wds.shift_workday_pk_key = meo.shift_workday_fk_key

Line 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);

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

Line 2143: --Insert records into mth_equip_output

2139: RAISE MTH_PROCESS_TXN_PKG.VALIDATION_ERR;
2140: END IF;
2141:
2142:
2143: --Insert records into mth_equip_output
2144:
2145: --Insert records into MTH_EQUIP_OUTPUT table
2146: INSERT INTO mth_equip_output( equipment_fk_key,
2147: item_fk_key,

Line 2145: --Insert records into MTH_EQUIP_OUTPUT table

2141:
2142:
2143: --Insert records into mth_equip_output
2144:
2145: --Insert records into MTH_EQUIP_OUTPUT table
2146: INSERT INTO mth_equip_output( equipment_fk_key,
2147: item_fk_key,
2148: shift_workday_fk_key,
2149: workorder_fk_key,

Line 2146: INSERT INTO mth_equip_output( equipment_fk_key,

2142:
2143: --Insert records into mth_equip_output
2144:
2145: --Insert records into MTH_EQUIP_OUTPUT table
2146: INSERT INTO mth_equip_output( equipment_fk_key,
2147: item_fk_key,
2148: shift_workday_fk_key,
2149: workorder_fk_key,
2150: reading_time,

Line 2224: FROM mth_equip_output_stg stg,

2220: null,
2221: null,
2222: null,
2223: mhd.hour_pk_key
2224: FROM mth_equip_output_stg stg,
2225: mth_equipments_d med,
2226: mth_workday_shifts_d wds,
2227: mth_items_d mid,
2228: mth_production_segments_f msf,

Line 2253: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_OUTPUT - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 2283: FROM mth_equip_output_stg stg,

2279: NULL,
2280: NULL,
2281: NULL,
2282: stg.reading_time
2283: FROM mth_equip_output_stg stg,
2284: mth_equipments_d med
2285: WHERE med.equipment_pk = stg.equipment_fk
2286: AND med.status = 'ACTIVE'
2287: AND stg.ERR_CODE IS NULL

Line 2293: DELETE FROM MTH_EQUIP_OUTPUT_STG;

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;
2295:
2296: mth_util_pkg.log_msg('RECAL_OUTPUT_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
2297: EXCEPTION

Line 2411: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);

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,

Line 2413: -- Call delete output summary to delete all data from MTH_EQUIP_OUTPUT_SUMMARY table as well update status summary table

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,
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);

Line 2419: DELETE FROM MTH_EQUIP_OUTPUT_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:
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);

Line 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);

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

Line 2428: MTH_EQUIP_OUTPUT_SUMMARY( EQUIPMENT_FK_KEY,

2424:
2425: -- Process data from output to be inserted to output summary
2426: INSERT
2427: INTO
2428: MTH_EQUIP_OUTPUT_SUMMARY( EQUIPMENT_FK_KEY,
2429: ITEM_FK_KEY,
2430: WORKORDER_FK_KEY,
2431: SHIFT_WORKDAY_FK_KEY,
2432: HOUR_FK_KEY,

Line 2483: mth_equip_output meo

2479: mth_resource_cost_mv mrc,
2480: mth_workday_shifts_d msg,
2481: mth_equipment_denorm_d med,
2482: mth_equip_standard_rates_f srf,
2483: mth_equip_output meo
2484: WHERE med.equipment_hierarchy_key = -2
2485: AND med.equipment_fk_key is not null
2486: AND msg.from_date between med.equipment_effective_date
2487: and nvl(med.equipment_expiration_date , msg.from_date)

Line 2506: mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 2532: FROM MTH_EQUIP_OUTPUT_SUMMARY meos

2528: v_log_date last_update_date,
2529: v_ua_val last_update_system_id,
2530: v_ua_val last_update_login,
2531: v_ua_val last_updated_by
2532: FROM MTH_EQUIP_OUTPUT_SUMMARY meos
2533: WHERE meos.standard_rate_1 is NOT NULL
2534: AND meos.standard_rate_1 <> 0
2535: GROUP BY meos.equipment_fk_key,
2536: meos.shift_workday_fk_key,

Line 2558: FROM MTH_EQUIP_OUTPUT_SUMMARY meos

2554: v_log_date last_update_date,
2555: v_ua_val last_update_system_id,
2556: v_ua_val last_update_login,
2557: v_ua_val last_updated_by
2558: FROM MTH_EQUIP_OUTPUT_SUMMARY meos
2559: WHERE meos.standard_rate_1 is NOT NULL
2560: AND meos.standard_rate_1 <> 0
2561: GROUP BY meos.equipment_fk_key,
2562: meos.shift_workday_fk_key,

Line 2572: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);

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

Line 2608: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INCR',NULL,0,v_log_date);

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:

Line 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);

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,

Line 2644: FROM mth_equip_output_summary eos,

2640: end) ) required_time,
2641: Sum((CASE WHEN eos.equipment_fk_key IS NULL THEN 1 ELSE 0 END)) wo_item_count,
2642: null,
2643: null
2644: FROM mth_equip_output_summary eos,
2645: mth_equip_standard_rates_f srf,
2646: (SELECT meo.equipment_fk_key,
2647: meo.item_fk_key,
2648: Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,

Line 2659: FROM MTH_EQUIP_OUTPUT meo

2655: Nvl(meo.recipe_version,v_ua_val) recipe_version,
2656: Nvl(meo.recipe_num,v_ua_val) recipe_num,
2657: meo.segment_fk_key segment_fk_key,
2658: meo.hour_fk_key
2659: FROM MTH_EQUIP_OUTPUT meo
2660: WHERE meo.last_update_date > v_run_log_from_date AND meo.last_update_date <= v_run_log_to_date
2661: GROUP BY meo.equipment_fk_key,
2662: meo.item_fk_key,
2663: meo.workorder_fk_key ,

Line 2712: FROM mth_equip_output_summary eos,

2708: end) ) required_time,
2709: Sum((case when eos.equipment_fk_key is null then 1 else 0 end)) wo_item_count,
2710: null,
2711: null
2712: FROM mth_equip_output_summary eos,
2713: mth_equip_standard_rates_f srf,
2714: (SELECT meo.equipment_fk_key,
2715: meo.item_fk_key,
2716: Nvl(meo.workorder_fk_key,v_ua_val) workorder_fk_key,

Line 2727: FROM MTH_EQUIP_OUTPUT meo

2723: Nvl(meo.recipe_version,v_ua_val) recipe_version,
2724: Nvl(meo.recipe_num,v_ua_val) recipe_num,
2725: meo.segment_fk_key segment_fk_key,
2726: meo.hour_fk_key
2727: FROM MTH_EQUIP_OUTPUT meo
2728: WHERE meo.last_update_date > v_run_log_from_date AND meo.last_update_date <= v_run_log_to_date
2729: GROUP BY meo.equipment_fk_key,
2730: meo.item_fk_key,
2731: meo.workorder_fk_key ,

Line 2763: MTH_EQUIP_OUTPUT_SUMMARY

2759:
2760: -- Process data from output to be updated/inserted to output summary
2761: MERGE
2762: INTO
2763: MTH_EQUIP_OUTPUT_SUMMARY
2764: USING
2765: ( SELECT meo.equipment_fk_key sq_equipment_fk_key,
2766: meo.item_fk_key sq_item_fk_key,
2767: Nvl(meo.workorder_fk_key,v_ua_val) sq_workorder_fk_key,

Line 2790: mth_equip_output meo

2786: mth_resource_cost_mv mrc,
2787: mth_workday_shifts_d msg,
2788: mth_equipment_denorm_d med,
2789: mth_equip_standard_rates_f srf,
2790: mth_equip_output meo
2791: WHERE med.equipment_hierarchy_key = -2
2792: AND med.equipment_fk_key is not null
2793: AND msg.from_date between med.equipment_effective_date
2794: and nvl(med.equipment_expiration_date , msg.from_date)

Line 2803: FROM mth_equip_output

2799: AND meo.equipment_fk_key = srf.equipment_fk_key(+)
2800: AND meo.item_fk_key = srf.item_fk_key(+)
2801: AND meo.shift_workday_fk_key = srf.shift_workday_fk_key(+)
2802: AND meo.hour_fk_key IN ( SELECT hour_fk_key
2803: FROM mth_equip_output
2804: WHERE last_update_date > v_run_log_from_date
2805: AND last_update_date <= v_run_log_to_date )
2806: GROUP BY meo.equipment_fk_key,
2807: meo.item_fk_key,

Line 2816: mth_equip_output_summary.equipment_fk_key = subquery.sq_equipment_fk_key

2812: meo.segment_fk_key,
2813: meo.hour_fk_key ) subquery
2814: ON
2815: (
2816: mth_equip_output_summary.equipment_fk_key = subquery.sq_equipment_fk_key
2817: AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key
2818: AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key

Line 2817: AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key

2813: meo.hour_fk_key ) subquery
2814: ON
2815: (
2816: mth_equip_output_summary.equipment_fk_key = subquery.sq_equipment_fk_key
2817: AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key
2818: AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
2821: AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num

Line 2818: AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key

2814: ON
2815: (
2816: mth_equip_output_summary.equipment_fk_key = subquery.sq_equipment_fk_key
2817: AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key
2818: AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
2821: AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num
2822: AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version

Line 2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key

2815: (
2816: mth_equip_output_summary.equipment_fk_key = subquery.sq_equipment_fk_key
2817: AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key
2818: AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
2821: AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num
2822: AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version
2823: AND mth_equip_output_summary.segment_fk_key = subquery.segment

Line 2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key

2816: mth_equip_output_summary.equipment_fk_key = subquery.sq_equipment_fk_key
2817: AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key
2818: AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
2821: AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num
2822: AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version
2823: AND mth_equip_output_summary.segment_fk_key = subquery.segment
2824: )

Line 2821: AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num

2817: AND mth_equip_output_summary.item_fk_key = subquery.sq_item_fk_key
2818: AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
2821: AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num
2822: AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version
2823: AND mth_equip_output_summary.segment_fk_key = subquery.segment
2824: )
2825: WHEN MATCHED THEN

Line 2822: AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version

2818: AND mth_equip_output_summary.workorder_fk_key = subquery.sq_workorder_fk_key
2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
2821: AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num
2822: AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version
2823: AND mth_equip_output_summary.segment_fk_key = subquery.segment
2824: )
2825: WHEN MATCHED THEN
2826: UPDATE

Line 2823: AND mth_equip_output_summary.segment_fk_key = subquery.segment

2819: AND mth_equip_output_summary.shift_workday_fk_key = subquery.sq_shift_workday_fk_key
2820: AND mth_equip_output_summary.hour_fk_key = subquery.sq_hour_fk_key
2821: AND mth_equip_output_summary.recipe_num = subquery.sq_recipe_num
2822: AND mth_equip_output_summary.recipe_version = subquery.sq_recipe_version
2823: AND mth_equip_output_summary.segment_fk_key = subquery.segment
2824: )
2825: WHEN MATCHED THEN
2826: UPDATE
2827: SET

Line 2846: (mth_equip_output_summary.equipment_fk_key,

2842: resource_cost = subquery.sq_resource_cost
2843:
2844: WHEN NOT MATCHED THEN
2845: INSERT
2846: (mth_equip_output_summary.equipment_fk_key,
2847: mth_equip_output_summary.item_fk_key,
2848: mth_equip_output_summary.workorder_fk_key,
2849: mth_equip_output_summary.shift_workday_fk_key,
2850: mth_equip_output_summary.hour_fk_key,

Line 2847: mth_equip_output_summary.item_fk_key,

2843:
2844: WHEN NOT MATCHED THEN
2845: INSERT
2846: (mth_equip_output_summary.equipment_fk_key,
2847: mth_equip_output_summary.item_fk_key,
2848: mth_equip_output_summary.workorder_fk_key,
2849: mth_equip_output_summary.shift_workday_fk_key,
2850: mth_equip_output_summary.hour_fk_key,
2851: mth_equip_output_summary.qty_completed,

Line 2848: mth_equip_output_summary.workorder_fk_key,

2844: WHEN NOT MATCHED THEN
2845: INSERT
2846: (mth_equip_output_summary.equipment_fk_key,
2847: mth_equip_output_summary.item_fk_key,
2848: mth_equip_output_summary.workorder_fk_key,
2849: mth_equip_output_summary.shift_workday_fk_key,
2850: mth_equip_output_summary.hour_fk_key,
2851: mth_equip_output_summary.qty_completed,
2852: mth_equip_output_summary.qty_scrap,

Line 2849: mth_equip_output_summary.shift_workday_fk_key,

2845: INSERT
2846: (mth_equip_output_summary.equipment_fk_key,
2847: mth_equip_output_summary.item_fk_key,
2848: mth_equip_output_summary.workorder_fk_key,
2849: mth_equip_output_summary.shift_workday_fk_key,
2850: mth_equip_output_summary.hour_fk_key,
2851: mth_equip_output_summary.qty_completed,
2852: mth_equip_output_summary.qty_scrap,
2853: mth_equip_output_summary.qty_rejected,

Line 2850: mth_equip_output_summary.hour_fk_key,

2846: (mth_equip_output_summary.equipment_fk_key,
2847: mth_equip_output_summary.item_fk_key,
2848: mth_equip_output_summary.workorder_fk_key,
2849: mth_equip_output_summary.shift_workday_fk_key,
2850: mth_equip_output_summary.hour_fk_key,
2851: mth_equip_output_summary.qty_completed,
2852: mth_equip_output_summary.qty_scrap,
2853: mth_equip_output_summary.qty_rejected,
2854: mth_equip_output_summary.qty_rework,

Line 2851: mth_equip_output_summary.qty_completed,

2847: mth_equip_output_summary.item_fk_key,
2848: mth_equip_output_summary.workorder_fk_key,
2849: mth_equip_output_summary.shift_workday_fk_key,
2850: mth_equip_output_summary.hour_fk_key,
2851: mth_equip_output_summary.qty_completed,
2852: mth_equip_output_summary.qty_scrap,
2853: mth_equip_output_summary.qty_rejected,
2854: mth_equip_output_summary.qty_rework,
2855: mth_equip_output_summary.qty_good,

Line 2852: mth_equip_output_summary.qty_scrap,

2848: mth_equip_output_summary.workorder_fk_key,
2849: mth_equip_output_summary.shift_workday_fk_key,
2850: mth_equip_output_summary.hour_fk_key,
2851: mth_equip_output_summary.qty_completed,
2852: mth_equip_output_summary.qty_scrap,
2853: mth_equip_output_summary.qty_rejected,
2854: mth_equip_output_summary.qty_rework,
2855: mth_equip_output_summary.qty_good,
2856: mth_equip_output_summary.qty_output,

Line 2853: mth_equip_output_summary.qty_rejected,

2849: mth_equip_output_summary.shift_workday_fk_key,
2850: mth_equip_output_summary.hour_fk_key,
2851: mth_equip_output_summary.qty_completed,
2852: mth_equip_output_summary.qty_scrap,
2853: mth_equip_output_summary.qty_rejected,
2854: mth_equip_output_summary.qty_rework,
2855: mth_equip_output_summary.qty_good,
2856: mth_equip_output_summary.qty_output,
2857: mth_equip_output_summary.system_fk_key,

Line 2854: mth_equip_output_summary.qty_rework,

2850: mth_equip_output_summary.hour_fk_key,
2851: mth_equip_output_summary.qty_completed,
2852: mth_equip_output_summary.qty_scrap,
2853: mth_equip_output_summary.qty_rejected,
2854: mth_equip_output_summary.qty_rework,
2855: mth_equip_output_summary.qty_good,
2856: mth_equip_output_summary.qty_output,
2857: mth_equip_output_summary.system_fk_key,
2858: mth_equip_output_summary.creation_date,

Line 2855: mth_equip_output_summary.qty_good,

2851: mth_equip_output_summary.qty_completed,
2852: mth_equip_output_summary.qty_scrap,
2853: mth_equip_output_summary.qty_rejected,
2854: mth_equip_output_summary.qty_rework,
2855: mth_equip_output_summary.qty_good,
2856: mth_equip_output_summary.qty_output,
2857: mth_equip_output_summary.system_fk_key,
2858: mth_equip_output_summary.creation_date,
2859: mth_equip_output_summary.last_update_date,

Line 2856: mth_equip_output_summary.qty_output,

2852: mth_equip_output_summary.qty_scrap,
2853: mth_equip_output_summary.qty_rejected,
2854: mth_equip_output_summary.qty_rework,
2855: mth_equip_output_summary.qty_good,
2856: mth_equip_output_summary.qty_output,
2857: mth_equip_output_summary.system_fk_key,
2858: mth_equip_output_summary.creation_date,
2859: mth_equip_output_summary.last_update_date,
2860: mth_equip_output_summary.creation_system_id,

Line 2857: mth_equip_output_summary.system_fk_key,

2853: mth_equip_output_summary.qty_rejected,
2854: mth_equip_output_summary.qty_rework,
2855: mth_equip_output_summary.qty_good,
2856: mth_equip_output_summary.qty_output,
2857: mth_equip_output_summary.system_fk_key,
2858: mth_equip_output_summary.creation_date,
2859: mth_equip_output_summary.last_update_date,
2860: mth_equip_output_summary.creation_system_id,
2861: mth_equip_output_summary.last_update_system_id,

Line 2858: mth_equip_output_summary.creation_date,

2854: mth_equip_output_summary.qty_rework,
2855: mth_equip_output_summary.qty_good,
2856: mth_equip_output_summary.qty_output,
2857: mth_equip_output_summary.system_fk_key,
2858: mth_equip_output_summary.creation_date,
2859: mth_equip_output_summary.last_update_date,
2860: mth_equip_output_summary.creation_system_id,
2861: mth_equip_output_summary.last_update_system_id,
2862: mth_equip_output_summary.last_update_login,

Line 2859: mth_equip_output_summary.last_update_date,

2855: mth_equip_output_summary.qty_good,
2856: mth_equip_output_summary.qty_output,
2857: mth_equip_output_summary.system_fk_key,
2858: mth_equip_output_summary.creation_date,
2859: mth_equip_output_summary.last_update_date,
2860: mth_equip_output_summary.creation_system_id,
2861: mth_equip_output_summary.last_update_system_id,
2862: mth_equip_output_summary.last_update_login,
2863: mth_equip_output_summary.last_updated_by,

Line 2860: mth_equip_output_summary.creation_system_id,

2856: mth_equip_output_summary.qty_output,
2857: mth_equip_output_summary.system_fk_key,
2858: mth_equip_output_summary.creation_date,
2859: mth_equip_output_summary.last_update_date,
2860: mth_equip_output_summary.creation_system_id,
2861: mth_equip_output_summary.last_update_system_id,
2862: mth_equip_output_summary.last_update_login,
2863: mth_equip_output_summary.last_updated_by,
2864: mth_equip_output_summary.recipe_num,

Line 2861: mth_equip_output_summary.last_update_system_id,

2857: mth_equip_output_summary.system_fk_key,
2858: mth_equip_output_summary.creation_date,
2859: mth_equip_output_summary.last_update_date,
2860: mth_equip_output_summary.creation_system_id,
2861: mth_equip_output_summary.last_update_system_id,
2862: mth_equip_output_summary.last_update_login,
2863: mth_equip_output_summary.last_updated_by,
2864: mth_equip_output_summary.recipe_num,
2865: mth_equip_output_summary.recipe_version,

Line 2862: mth_equip_output_summary.last_update_login,

2858: mth_equip_output_summary.creation_date,
2859: mth_equip_output_summary.last_update_date,
2860: mth_equip_output_summary.creation_system_id,
2861: mth_equip_output_summary.last_update_system_id,
2862: mth_equip_output_summary.last_update_login,
2863: mth_equip_output_summary.last_updated_by,
2864: mth_equip_output_summary.recipe_num,
2865: mth_equip_output_summary.recipe_version,
2866: mth_equip_output_summary.segment_fk_key,

Line 2863: mth_equip_output_summary.last_updated_by,

2859: mth_equip_output_summary.last_update_date,
2860: mth_equip_output_summary.creation_system_id,
2861: mth_equip_output_summary.last_update_system_id,
2862: mth_equip_output_summary.last_update_login,
2863: mth_equip_output_summary.last_updated_by,
2864: mth_equip_output_summary.recipe_num,
2865: mth_equip_output_summary.recipe_version,
2866: mth_equip_output_summary.segment_fk_key,
2867: mth_equip_output_summary.standard_rate_1,

Line 2864: mth_equip_output_summary.recipe_num,

2860: mth_equip_output_summary.creation_system_id,
2861: mth_equip_output_summary.last_update_system_id,
2862: mth_equip_output_summary.last_update_login,
2863: mth_equip_output_summary.last_updated_by,
2864: mth_equip_output_summary.recipe_num,
2865: mth_equip_output_summary.recipe_version,
2866: mth_equip_output_summary.segment_fk_key,
2867: mth_equip_output_summary.standard_rate_1,
2868: mth_equip_output_summary.item_cost,

Line 2865: mth_equip_output_summary.recipe_version,

2861: mth_equip_output_summary.last_update_system_id,
2862: mth_equip_output_summary.last_update_login,
2863: mth_equip_output_summary.last_updated_by,
2864: mth_equip_output_summary.recipe_num,
2865: mth_equip_output_summary.recipe_version,
2866: mth_equip_output_summary.segment_fk_key,
2867: mth_equip_output_summary.standard_rate_1,
2868: mth_equip_output_summary.item_cost,
2869: mth_equip_output_summary.resource_fk_key,

Line 2866: mth_equip_output_summary.segment_fk_key,

2862: mth_equip_output_summary.last_update_login,
2863: mth_equip_output_summary.last_updated_by,
2864: mth_equip_output_summary.recipe_num,
2865: mth_equip_output_summary.recipe_version,
2866: mth_equip_output_summary.segment_fk_key,
2867: mth_equip_output_summary.standard_rate_1,
2868: mth_equip_output_summary.item_cost,
2869: mth_equip_output_summary.resource_fk_key,
2870: mth_equip_output_summary.resource_cost)

Line 2867: mth_equip_output_summary.standard_rate_1,

2863: mth_equip_output_summary.last_updated_by,
2864: mth_equip_output_summary.recipe_num,
2865: mth_equip_output_summary.recipe_version,
2866: mth_equip_output_summary.segment_fk_key,
2867: mth_equip_output_summary.standard_rate_1,
2868: mth_equip_output_summary.item_cost,
2869: mth_equip_output_summary.resource_fk_key,
2870: mth_equip_output_summary.resource_cost)
2871: VALUES

Line 2868: mth_equip_output_summary.item_cost,

2864: mth_equip_output_summary.recipe_num,
2865: mth_equip_output_summary.recipe_version,
2866: mth_equip_output_summary.segment_fk_key,
2867: mth_equip_output_summary.standard_rate_1,
2868: mth_equip_output_summary.item_cost,
2869: mth_equip_output_summary.resource_fk_key,
2870: mth_equip_output_summary.resource_cost)
2871: VALUES
2872: (subquery.sq_equipment_fk_key,

Line 2869: mth_equip_output_summary.resource_fk_key,

2865: mth_equip_output_summary.recipe_version,
2866: mth_equip_output_summary.segment_fk_key,
2867: mth_equip_output_summary.standard_rate_1,
2868: mth_equip_output_summary.item_cost,
2869: mth_equip_output_summary.resource_fk_key,
2870: mth_equip_output_summary.resource_cost)
2871: VALUES
2872: (subquery.sq_equipment_fk_key,
2873: subquery.sq_item_fk_key,

Line 2870: mth_equip_output_summary.resource_cost)

2866: mth_equip_output_summary.segment_fk_key,
2867: mth_equip_output_summary.standard_rate_1,
2868: mth_equip_output_summary.item_cost,
2869: mth_equip_output_summary.resource_fk_key,
2870: mth_equip_output_summary.resource_cost)
2871: VALUES
2872: (subquery.sq_equipment_fk_key,
2873: subquery.sq_item_fk_key,
2874: subquery.sq_workorder_fk_key,

Line 2899: mth_util_pkg.log_msg('Rows merged in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);

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);

Line 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

Line 2959: FROM MTH_EQUIP_OUTPUT;

2955:
2956: CURSOR c_fetch_recalc_to_date
2957: IS
2958: SELECT Max(reading_time)
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);

Line 3014: mth_util_pkg.mth_run_log_pre_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val,'INITIAL',NULL,0,v_log_date);

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

Line 3016: -- Call delete output summary to delete all data from MTH_EQUIP_OUTPUT_SUMMARY table as well update status summary table

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
3019: UPDATE MTH_EQUIP_STATUS_SUMMARY
3020: SET wo_item_count = null,

Line 3045: FROM MTH_EQUIP_OUTPUT_SUMMARY

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
3047: FROM mth_hour_d
3048: WHERE from_time >= p_n_recalc_from_date
3049: AND to_time <= p_n_recalc_to_date)

Line 3053: FROM MTH_EQUIP_OUTPUT_SUMMARY

3049: AND to_time <= p_n_recalc_to_date)
3050: AND equipment_fk_key = nvl(p_recalc_equip_key,equipment_fk_key);
3051: ELSE
3052: DELETE
3053: FROM MTH_EQUIP_OUTPUT_SUMMARY
3054: WHERE hour_fk_key IN (SELECT hour_pk_key
3055: FROM mth_hour_d
3056: WHERE from_time >= p_n_recalc_from_date
3057: AND to_time <= p_n_recalc_to_date)

Line 3065: mth_util_pkg.log_msg('Rows deleted from MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);

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

Line 3070: MTH_EQUIP_OUTPUT_SUMMARY( EQUIPMENT_FK_KEY,

3066:
3067: -- Process data from output to be inserted to output summary
3068: INSERT
3069: INTO
3070: MTH_EQUIP_OUTPUT_SUMMARY( EQUIPMENT_FK_KEY,
3071: ITEM_FK_KEY,
3072: WORKORDER_FK_KEY,
3073: SHIFT_WORKDAY_FK_KEY,
3074: HOUR_FK_KEY,

Line 3125: mth_equip_output meo

3121: mth_resource_cost_mv mrc,
3122: mth_workday_shifts_d msg,
3123: mth_equipment_denorm_d med,
3124: mth_equip_standard_rates_f srf,
3125: mth_equip_output meo
3126: WHERE med.equipment_hierarchy_key = -2
3127: AND med.equipment_fk_key is not null
3128: AND msg.from_date between med.equipment_effective_date
3129: and nvl(med.equipment_expiration_date , msg.from_date)

Line 3155: mth_util_pkg.log_msg('Rows inserted in MTH_EQUIP_OUTPUT_SUMMARY : '||SQL%ROWCOUNT,mth_util_pkg.G_DBG_ROW_CNT);

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,

Line 3181: FROM MTH_EQUIP_OUTPUT_SUMMARY meos

3177: v_log_date last_update_date,
3178: v_ua_val last_update_system_id,
3179: v_ua_val last_update_login,
3180: v_ua_val last_updated_by
3181: FROM MTH_EQUIP_OUTPUT_SUMMARY meos
3182: WHERE meos.standard_rate_1 is NOT NULL
3183: AND meos.standard_rate_1 <> 0
3184: AND meos.hour_fk_key IN (SELECT hour_pk_key
3185: FROM mth_hour_d

Line 3215: FROM MTH_EQUIP_OUTPUT_SUMMARY meos

3211: v_log_date last_update_date,
3212: v_ua_val last_update_system_id,
3213: v_ua_val last_update_login,
3214: v_ua_val last_updated_by
3215: FROM MTH_EQUIP_OUTPUT_SUMMARY meos
3216: WHERE meos.standard_rate_1 is NOT NULL
3217: AND meos.standard_rate_1 <> 0
3218: AND meos.hour_fk_key IN (SELECT hour_pk_key
3219: FROM mth_hour_d

Line 3236: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_OUTPUT_SUMMARY',v_ua_val);

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