eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MTH_PROCESS_OUTPUT_PKG dependencies on MTH_EQUIP_OUTPUT_STG

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