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_STATUS_PKG dependencies on MTH_EQUIP_STATUSES_STG

Line 841: INSERT INTO mth_equip_statuses_stg(equipment_fk,

837: v_log_date := sysdate;
838: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
839:
840: --Ensuring the functionality of ES map is introduced before the staging to fact is executed.
841: INSERT INTO mth_equip_statuses_stg(equipment_fk,
842: shift_workday_fk,
843: from_date,
844: To_Date,
845: status,

Line 887: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

883: user_measure5,
884: downtime_reason_code
885: FROM mth_equip_statuses_err
886: WHERE reprocess_ready_yn = 'Y');
887: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
888:
889: --delete status table
890: DELETE FROM MTH_EQUIP_STATUSES_ERR
891: WHERE REPROCESS_READY_YN = 'Y';

Line 895: mth_util_pkg.switch_column_default_value('MTH_EQUIP_STATUSES_STG',v_processing_flag);

891: WHERE REPROCESS_READY_YN = 'Y';
892: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
893:
894:
895: mth_util_pkg.switch_column_default_value('MTH_EQUIP_STATUSES_STG',v_processing_flag);
896:
897: --delete status table
898: DELETE FROM MTH_EQUIP_STATUSES;
899: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 904: UPDATE mth_equip_statuses_stg stg

900:
901:
902: --Execute all validations on csv records
903: -- Validation for Duplicate record
904: UPDATE mth_equip_statuses_stg stg
905: SET stg.err_code = stg.err_code || 'DUP '
906: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
907: FROM mth_equip_statuses_stg
908: GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup

Line 907: FROM mth_equip_statuses_stg

903: -- Validation for Duplicate record
904: UPDATE mth_equip_statuses_stg stg
905: SET stg.err_code = stg.err_code || 'DUP '
906: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
907: FROM mth_equip_statuses_stg
908: GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
909: WHERE dup.cnt>1
910: AND dup.equipment_fk = stg.equipment_fk
911: AND dup.shift_workday_fk = stg.shift_workday_fk

Line 915: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

911: AND dup.shift_workday_fk = stg.shift_workday_fk
912: AND dup.from_date = stg.from_date
913: AND dup.To_Date = stg.To_Date
914: AND stg.processing_flag = v_processing_flag);
915: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
916:
917: -- Validation for invalid equipment
918: UPDATE mth_equip_statuses_stg stg
919: SET stg.err_code = stg.err_code || 'EQP '

Line 918: UPDATE mth_equip_statuses_stg stg

914: AND stg.processing_flag = v_processing_flag);
915: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
916:
917: -- Validation for invalid equipment
918: UPDATE mth_equip_statuses_stg stg
919: SET stg.err_code = stg.err_code || 'EQP '
920: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
921: FROM mth_equipments_d med,
922: mth_equip_statuses_stg stg

Line 922: mth_equip_statuses_stg stg

918: UPDATE mth_equip_statuses_stg stg
919: SET stg.err_code = stg.err_code || 'EQP '
920: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
921: FROM mth_equipments_d med,
922: mth_equip_statuses_stg stg
923: WHERE med.equipment_pk = stg.equipment_fk) eqp
924: WHERE eqp.equipment_pk = stg.equipment_fk
925: AND stg.processing_flag = v_processing_flag);
926: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 926: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

922: mth_equip_statuses_stg stg
923: WHERE med.equipment_pk = stg.equipment_fk) eqp
924: WHERE eqp.equipment_pk = stg.equipment_fk
925: AND stg.processing_flag = v_processing_flag);
926: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
927:
928: -- Validation for Inactive equipment
929: UPDATE mth_equip_statuses_stg stg
930: SET stg.err_code = stg.err_code || 'IEQ '

Line 929: UPDATE mth_equip_statuses_stg stg

925: AND stg.processing_flag = v_processing_flag);
926: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
927:
928: -- Validation for Inactive equipment
929: UPDATE mth_equip_statuses_stg stg
930: SET stg.err_code = stg.err_code || 'IEQ '
931: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
932: FROM mth_equipments_d med,
933: mth_equip_statuses_stg stg

Line 933: mth_equip_statuses_stg stg

929: UPDATE mth_equip_statuses_stg stg
930: SET stg.err_code = stg.err_code || 'IEQ '
931: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
932: FROM mth_equipments_d med,
933: mth_equip_statuses_stg stg
934: WHERE med.equipment_pk = stg.equipment_fk
935: AND med.status <> 'ACTIVE') eqp
936: WHERE eqp.equipment_pk = stg.equipment_fk
937: AND stg.processing_flag = v_processing_flag);

Line 938: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

934: WHERE med.equipment_pk = stg.equipment_fk
935: AND med.status <> 'ACTIVE') eqp
936: WHERE eqp.equipment_pk = stg.equipment_fk
937: AND stg.processing_flag = v_processing_flag);
938: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
939:
940: -- Validation for invalid shift
941: UPDATE mth_equip_statuses_stg stg
942: SET stg.err_code = stg.err_code || 'WDS '

Line 941: UPDATE mth_equip_statuses_stg stg

937: AND stg.processing_flag = v_processing_flag);
938: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
939:
940: -- Validation for invalid shift
941: UPDATE mth_equip_statuses_stg stg
942: SET stg.err_code = stg.err_code || 'WDS '
943: WHERE stg.shift_workday_fk IS NOT NULL
944: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
945: FROM mth_workday_shifts_d mds,

Line 946: mth_equip_statuses_stg stg

942: SET stg.err_code = stg.err_code || 'WDS '
943: WHERE stg.shift_workday_fk IS NOT NULL
944: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
945: FROM mth_workday_shifts_d mds,
946: mth_equip_statuses_stg stg
947: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
948: AND stg.shift_workday_fk IS NOT NULL) wds
949: WHERE wds.shift_workday_pk = stg.shift_workday_fk
950: AND stg.processing_flag = v_processing_flag);

Line 951: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

947: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
948: AND stg.shift_workday_fk IS NOT NULL) wds
949: WHERE wds.shift_workday_pk = stg.shift_workday_fk
950: AND stg.processing_flag = v_processing_flag);
951: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
952:
953: -- Validation for invalid equipment shift combination
954: UPDATE mth_equip_statuses_stg stg
955: SET stg.err_code = stg.err_code || 'ESD '

Line 954: UPDATE mth_equip_statuses_stg stg

950: AND stg.processing_flag = v_processing_flag);
951: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
952:
953: -- Validation for invalid equipment shift combination
954: UPDATE mth_equip_statuses_stg stg
955: SET stg.err_code = stg.err_code || 'ESD '
956: WHERE NOT EXISTS ( SELECT * FROM
957: ( SELECT mee.equipment_pk, mws.shift_workday_pk
958: FROM mth_equipments_d mee,

Line 970: mth_equip_statuses_stg stg

966: AND stg.processing_flag = v_processing_flag)
967: AND EXISTS ( SELECT * FROM
968: ( SELECT mds.shift_workday_pk
969: FROM mth_workday_shifts_d mds,
970: mth_equip_statuses_stg stg
971: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
972: AND stg.shift_workday_fk IS NOT NULL) wds
973: WHERE wds.shift_workday_pk = stg.shift_workday_fk
974: AND stg.processing_flag = v_processing_flag)

Line 977: mth_equip_statuses_stg stg

973: WHERE wds.shift_workday_pk = stg.shift_workday_fk
974: AND stg.processing_flag = v_processing_flag)
975: AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
976: FROM mth_equipments_d med,
977: mth_equip_statuses_stg stg
978: WHERE med.equipment_pk = stg.equipment_fk) eqp
979: WHERE eqp.equipment_pk = stg.equipment_fk
980: AND stg.processing_flag = v_processing_flag);
981: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 981: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

977: mth_equip_statuses_stg stg
978: WHERE med.equipment_pk = stg.equipment_fk) eqp
979: WHERE eqp.equipment_pk = stg.equipment_fk
980: AND stg.processing_flag = v_processing_flag);
981: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
982:
983: -- Validation for user dimension 1
984: UPDATE mth_equip_statuses_stg stg
985: SET stg.err_code = stg.err_code || 'UD1 '

Line 984: UPDATE mth_equip_statuses_stg stg

980: AND stg.processing_flag = v_processing_flag);
981: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
982:
983: -- Validation for user dimension 1
984: UPDATE mth_equip_statuses_stg stg
985: SET stg.err_code = stg.err_code || 'UD1 '
986: WHERE stg.user_dim1_fk IS NOT NULL
987: AND EXISTS (SELECT *
988: FROM

Line 991: mth_equip_statuses_stg stg

987: AND EXISTS (SELECT *
988: FROM
989: (SELECT mue.entity_pk, stg.user_dim1_fk
990: FROM mth_user_dim_entities_mst mue,
991: mth_equip_statuses_stg stg
992: WHERE stg.user_dim1_fk = mue.entity_pk (+)
993: AND stg.user_dim1_fk IS NOT NULL) ud1
994: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
995: AND stg.processing_flag = v_processing_flag

Line 997: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

993: AND stg.user_dim1_fk IS NOT NULL) ud1
994: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
995: AND stg.processing_flag = v_processing_flag
996: AND ud1.entity_pk IS NULL);
997: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
998:
999: -- Validation for user dimension 2
1000: UPDATE mth_equip_statuses_stg stg
1001: SET stg.err_code = stg.err_code || 'UD2 '

Line 1000: UPDATE mth_equip_statuses_stg stg

996: AND ud1.entity_pk IS NULL);
997: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
998:
999: -- Validation for user dimension 2
1000: UPDATE mth_equip_statuses_stg stg
1001: SET stg.err_code = stg.err_code || 'UD2 '
1002: WHERE stg.user_dim2_fk IS NOT NULL
1003: AND EXISTS (SELECT *
1004: FROM

Line 1007: mth_equip_statuses_stg stg

1003: AND EXISTS (SELECT *
1004: FROM
1005: (SELECT mue.entity_pk, stg.user_dim2_fk
1006: FROM mth_user_dim_entities_mst mue,
1007: mth_equip_statuses_stg stg
1008: WHERE stg.user_dim2_fk = mue.entity_pk (+)
1009: AND stg.user_dim2_fk IS NOT NULL) ud2
1010: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
1011: AND stg.processing_flag = v_processing_flag

Line 1013: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1009: AND stg.user_dim2_fk IS NOT NULL) ud2
1010: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
1011: AND stg.processing_flag = v_processing_flag
1012: AND ud2.entity_pk IS NULL);
1013: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1014:
1015: -- Validation for user dimension 3
1016: UPDATE mth_equip_statuses_stg stg
1017: SET stg.err_code = stg.err_code || 'UD3 '

Line 1016: UPDATE mth_equip_statuses_stg stg

1012: AND ud2.entity_pk IS NULL);
1013: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1014:
1015: -- Validation for user dimension 3
1016: UPDATE mth_equip_statuses_stg stg
1017: SET stg.err_code = stg.err_code || 'UD3 '
1018: WHERE stg.user_dim3_fk IS NOT NULL
1019: AND EXISTS (SELECT *
1020: FROM

Line 1023: mth_equip_statuses_stg stg

1019: AND EXISTS (SELECT *
1020: FROM
1021: (SELECT mue.entity_pk, stg.user_dim3_fk
1022: FROM mth_user_dim_entities_mst mue,
1023: mth_equip_statuses_stg stg
1024: WHERE stg.user_dim3_fk = mue.entity_pk (+)
1025: AND stg.user_dim3_fk IS NOT NULL) ud3
1026: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
1027: AND stg.processing_flag = v_processing_flag

Line 1029: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1025: AND stg.user_dim3_fk IS NOT NULL) ud3
1026: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
1027: AND stg.processing_flag = v_processing_flag
1028: AND ud3.entity_pk IS NULL);
1029: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1030:
1031: -- Validation for user dimension 4
1032: UPDATE mth_equip_statuses_stg stg
1033: SET stg.err_code = stg.err_code || 'UD4 '

Line 1032: UPDATE mth_equip_statuses_stg stg

1028: AND ud3.entity_pk IS NULL);
1029: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1030:
1031: -- Validation for user dimension 4
1032: UPDATE mth_equip_statuses_stg stg
1033: SET stg.err_code = stg.err_code || 'UD4 '
1034: WHERE stg.user_dim4_fk IS NOT NULL
1035: AND EXISTS (SELECT *
1036: FROM

Line 1039: mth_equip_statuses_stg stg

1035: AND EXISTS (SELECT *
1036: FROM
1037: (SELECT mue.entity_pk, stg.user_dim4_fk
1038: FROM mth_user_dim_entities_mst mue,
1039: mth_equip_statuses_stg stg
1040: WHERE stg.user_dim4_fk = mue.entity_pk (+)
1041: AND stg.user_dim4_fk IS NOT NULL) ud4
1042: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
1043: AND stg.processing_flag = v_processing_flag

Line 1045: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1041: AND stg.user_dim4_fk IS NOT NULL) ud4
1042: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
1043: AND stg.processing_flag = v_processing_flag
1044: AND ud4.entity_pk IS NULL);
1045: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1046:
1047: -- Validation for user dimension 5
1048: UPDATE mth_equip_statuses_stg stg
1049: SET stg.err_code = stg.err_code || 'UD5 '

Line 1048: UPDATE mth_equip_statuses_stg stg

1044: AND ud4.entity_pk IS NULL);
1045: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1046:
1047: -- Validation for user dimension 5
1048: UPDATE mth_equip_statuses_stg stg
1049: SET stg.err_code = stg.err_code || 'UD5 '
1050: WHERE stg.user_dim5_fk IS NOT NULL
1051: AND EXISTS (SELECT *
1052: FROM

Line 1055: mth_equip_statuses_stg stg

1051: AND EXISTS (SELECT *
1052: FROM
1053: (SELECT mue.entity_pk, stg.user_dim5_fk
1054: FROM mth_user_dim_entities_mst mue,
1055: mth_equip_statuses_stg stg
1056: WHERE stg.user_dim5_fk = mue.entity_pk (+)
1057: AND stg.user_dim5_fk IS NOT NULL) ud5
1058: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
1059: AND stg.processing_flag = v_processing_flag

Line 1061: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1057: AND stg.user_dim5_fk IS NOT NULL) ud5
1058: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
1059: AND stg.processing_flag = v_processing_flag
1060: AND ud5.entity_pk IS NULL);
1061: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1062:
1063: -- Validation for 'GAP' error code
1064: UPDATE mth_equip_statuses_stg stg
1065: SET stg.err_code = stg.err_code || 'GAP '

Line 1064: UPDATE mth_equip_statuses_stg stg

1060: AND ud5.entity_pk IS NULL);
1061: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1062:
1063: -- Validation for 'GAP' error code
1064: UPDATE mth_equip_statuses_stg stg
1065: SET stg.err_code = stg.err_code || 'GAP '
1066: WHERE EXISTS (SELECT *
1067: FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
1068: over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code

Line 1071: FROM mth_equip_statuses_stg a WHERE a.processing_flag = v_processing_flag) b) c

1067: FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
1068: over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
1069: FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
1070: (Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
1071: FROM mth_equip_statuses_stg a WHERE a.processing_flag = v_processing_flag) b) c
1072: WHERE c.Count >= 1
1073: AND stg.from_date = c.from_date
1074: AND stg.equipment_fk = c.equipment_fk
1075: AND stg.processing_flag = v_processing_flag);

Line 1076: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1072: WHERE c.Count >= 1
1073: AND stg.from_date = c.from_date
1074: AND stg.equipment_fk = c.equipment_fk
1075: AND stg.processing_flag = v_processing_flag);
1076: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1077:
1078: -- Validation for FTD
1079: UPDATE mth_equip_statuses_stg stg
1080: SET stg.err_code = stg.err_code || 'FTD '

Line 1079: UPDATE mth_equip_statuses_stg stg

1075: AND stg.processing_flag = v_processing_flag);
1076: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1077:
1078: -- Validation for FTD
1079: UPDATE mth_equip_statuses_stg stg
1080: SET stg.err_code = stg.err_code || 'FTD '
1081: WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE
1082: AND stg.processing_flag = v_processing_flag;
1083: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 1083: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1079: UPDATE mth_equip_statuses_stg stg
1080: SET stg.err_code = stg.err_code || 'FTD '
1081: WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE
1082: AND stg.processing_flag = v_processing_flag;
1083: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1084:
1085: -- Validation for DTR
1086: UPDATE mth_equip_statuses_stg stg
1087: SET stg.err_code = stg.err_code || 'DTR '

Line 1086: UPDATE mth_equip_statuses_stg stg

1082: AND stg.processing_flag = v_processing_flag;
1083: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1084:
1085: -- Validation for DTR
1086: UPDATE mth_equip_statuses_stg stg
1087: SET stg.err_code = stg.err_code || 'DTR '
1088: WHERE EXISTS (SELECT *
1089: FROM
1090: (SELECT flk.lookup_code,

Line 1097: mth_equip_statuses_stg stg

1093: stg.to_date,
1094: stg.downtime_reason_code,
1095: stg.status
1096: FROM fnd_lookups flk,
1097: mth_equip_statuses_stg stg
1098: WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
1099: AND stg.downtime_reason_code = flk.lookup_code (+)
1100: AND stg.status = 3) dtr
1101: WHERE ((stg.downtime_reason_code = dtr.downtime_reason_code

Line 1108: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1104: AND dtr.from_date = stg.from_date
1105: AND dtr.To_Date = stg.To_Date)
1106: OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL))
1107: AND stg.processing_flag = v_processing_flag);
1108: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1109:
1110: -- Validation for IDR
1111: UPDATE mth_equip_statuses_stg stg
1112: SET stg.err_code = stg.err_code || 'IDR '

Line 1111: UPDATE mth_equip_statuses_stg stg

1107: AND stg.processing_flag = v_processing_flag);
1108: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1109:
1110: -- Validation for IDR
1111: UPDATE mth_equip_statuses_stg stg
1112: SET stg.err_code = stg.err_code || 'IDR '
1113: WHERE EXISTS (SELECT *
1114: FROM
1115: ( SELECT flk.lookup_code,

Line 1122: mth_equip_statuses_stg stg

1118: stg.to_date,
1119: stg.downtime_reason_code,
1120: stg.status
1121: FROM fnd_lookups flk,
1122: mth_equip_statuses_stg stg
1123: WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
1124: AND stg.downtime_reason_code = flk.lookup_code (+)
1125: AND stg.status = 2) dtr
1126: WHERE stg.downtime_reason_code = dtr.downtime_reason_code

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

1128: AND dtr.lookup_code IS NULL
1129: AND dtr.from_date = stg.from_date
1130: AND dtr.To_Date = stg.To_Date
1131: AND stg.processing_flag = v_processing_flag);
1132: mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1133:
1134: -- Validation for ITR
1135: UPDATE mth_equip_statuses_stg stg
1136: SET stg.err_code = stg.err_code || 'ITR '

Line 1135: UPDATE mth_equip_statuses_stg stg

1131: AND stg.processing_flag = v_processing_flag);
1132: mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1133:
1134: -- Validation for ITR
1135: UPDATE mth_equip_statuses_stg stg
1136: SET stg.err_code = stg.err_code || 'ITR '
1137: WHERE EXISTS ( SELECT * FROM (
1138: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1139: FROM mth_workday_shifts_d mds,

Line 1140: mth_equip_statuses_stg stg,

1136: SET stg.err_code = stg.err_code || 'ITR '
1137: WHERE EXISTS ( SELECT * FROM (
1138: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1139: FROM mth_workday_shifts_d mds,
1140: mth_equip_statuses_stg stg,
1141: mth_equipment_shifts_d mes,
1142: mth_equipments_d med
1143: WHERE stg.shift_workday_fk = mds.shift_workday_pk
1144: AND stg.equipment_fk = med.equipment_pk

Line 1156: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1152: AND itr.equipment_pk = stg.equipment_fk
1153: AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
1154: AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date)
1155: AND stg.processing_flag = v_processing_flag);
1156: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1157:
1158: -- Validation for MSF
1159: UPDATE mth_equip_statuses_stg stg
1160: SET stg.err_code = stg.err_code || 'MSF '

Line 1159: UPDATE mth_equip_statuses_stg stg

1155: AND stg.processing_flag = v_processing_flag);
1156: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1157:
1158: -- Validation for MSF
1159: UPDATE mth_equip_statuses_stg stg
1160: SET stg.err_code = stg.err_code || 'MSF '
1161: WHERE EXISTS ( SELECT * FROM (
1162: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1163: FROM mth_workday_shifts_d mds,

Line 1164: mth_equip_statuses_stg stg,

1160: SET stg.err_code = stg.err_code || 'MSF '
1161: WHERE EXISTS ( SELECT * FROM (
1162: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1163: FROM mth_workday_shifts_d mds,
1164: mth_equip_statuses_stg stg,
1165: mth_equipment_shifts_d mes,
1166: mth_equipments_d med
1167: WHERE stg.shift_workday_fk = mds.shift_workday_pk
1168: AND stg.equipment_fk = med.equipment_pk

Line 1184: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1180: AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
1181: OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
1182: AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date)))
1183: AND stg.processing_flag = v_processing_flag);
1184: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1185:
1186: --Validation for OCSV
1187: UPDATE mth_equip_statuses_stg stag
1188: SET stag.err_code = stag.err_code || 'OCSV '

Line 1187: UPDATE mth_equip_statuses_stg stag

1183: AND stg.processing_flag = v_processing_flag);
1184: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1185:
1186: --Validation for OCSV
1187: UPDATE mth_equip_statuses_stg stag
1188: SET stag.err_code = stag.err_code || 'OCSV '
1189: WHERE EXISTS (SELECT *
1190: FROM
1191: (SELECT CASE

Line 1206: FROM mth_equip_statuses_stg stg,

1202: THEN 1 END overlap,
1203: med.equipment_pk,
1204: stg.from_date,
1205: stg.to_date
1206: FROM mth_equip_statuses_stg stg,
1207: mth_equipments_d med
1208: WHERE stg.equipment_fk = med.equipment_pk
1209: AND stg.processing_flag = v_processing_flag) ovp
1210: WHERE ovp.overlap = 1

Line 1215: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1211: AND stag.equipment_fk = ovp.equipment_pk
1212: AND stag.from_date = ovp.from_date
1213: AND stag.To_Date = ovp.To_Date
1214: AND stag.processing_flag = v_processing_flag);
1215: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1216:
1217: --validation for OSTS
1218: UPDATE mth_equip_statuses_stg stag
1219: SET stag.err_code = stag.err_code || 'OSTS '

Line 1218: UPDATE mth_equip_statuses_stg stag

1214: AND stag.processing_flag = v_processing_flag);
1215: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1216:
1217: --validation for OSTS
1218: UPDATE mth_equip_statuses_stg stag
1219: SET stag.err_code = stag.err_code || 'OSTS '
1220: WHERE EXISTS (SELECT *
1221: FROM ( SELECT CASE
1222: WHEN (stg.FROM_DATE >= sts.FROM_DATE

Line 1233: FROM mth_equip_statuses_stg stg,

1229: med.equipment_pk,
1230: wds.shift_workday_pk,
1231: stg.from_date,
1232: stg.to_date
1233: FROM mth_equip_statuses_stg stg,
1234: mth_equip_statuses sts,
1235: mth_equipments_d med,
1236: mth_workday_shifts_d wds
1237: WHERE stg.equipment_fk = med.equipment_pk

Line 1247: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1243: AND stag.equipment_fk = osts.equipment_pk
1244: AND stag.shift_workday_fk = osts.shift_workday_pk
1245: AND stag.processing_flag = v_processing_flag
1246: AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
1247: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1248:
1249: --validation for WRC
1250: UPDATE mth_equip_statuses_stg stg
1251: SET stg.err_code = stg.err_code ||'WRC '

Line 1250: UPDATE mth_equip_statuses_stg stg

1246: AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
1247: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1248:
1249: --validation for WRC
1250: UPDATE mth_equip_statuses_stg stg
1251: SET stg.err_code = stg.err_code ||'WRC '
1252: WHERE NOT EXISTS ( SELECT *
1253: FROM (SELECT stg.*
1254: FROM mth_equip_statuses_stg stg,

Line 1254: FROM mth_equip_statuses_stg stg,

1250: UPDATE mth_equip_statuses_stg stg
1251: SET stg.err_code = stg.err_code ||'WRC '
1252: WHERE NOT EXISTS ( SELECT *
1253: FROM (SELECT stg.*
1254: FROM mth_equip_statuses_stg stg,
1255: MTH_EQUIPMENT_REASON_SETUP mer,
1256: mth_equipments_d med
1257: WHERE stg.equipment_fk = med.equipment_pk
1258: AND med.equipment_pk_key = mer.equipment_fk_key

Line 1271: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1267: AND stg.processing_flag = v_processing_flag
1268: )
1269: AND stg.status IN (3,2)
1270: AND stg.downtime_reason_code IS NOT NULL;
1271: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1272:
1273: --validation for STS
1274: UPDATE mth_equip_statuses_stg stg
1275: SET stg.err_code = stg.err_code ||'STS '

Line 1274: UPDATE mth_equip_statuses_stg stg

1270: AND stg.downtime_reason_code IS NOT NULL;
1271: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1272:
1273: --validation for STS
1274: UPDATE mth_equip_statuses_stg stg
1275: SET stg.err_code = stg.err_code ||'STS '
1276: WHERE stg.processing_flag = v_processing_flag
1277: AND stg.status NOT IN (1,2,3,4);
1278: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 1278: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1274: UPDATE mth_equip_statuses_stg stg
1275: SET stg.err_code = stg.err_code ||'STS '
1276: WHERE stg.processing_flag = v_processing_flag
1277: AND stg.status NOT IN (1,2,3,4);
1278: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1279:
1280: --Insert records into mth_equip_statuses_err
1281: INSERT INTO mth_equip_statuses_err(equipment_fk,
1282: shift_workday_fk,

Line 1329: FROM mth_equip_statuses_stg

1325: user_measure4,
1326: user_measure5,
1327: err_code,
1328: downtime_reason_code
1329: FROM mth_equip_statuses_stg
1330: WHERE err_code IS NOT NULL
1331: AND processing_flag = v_processing_flag);
1332: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1333:

Line 1399: FROM mth_equip_statuses_stg stg,

1395: null,
1396: null,
1397: mhd.HOUR_PK_KEY,
1398: stg.from_date
1399: FROM mth_equip_statuses_stg stg,
1400: mth_equipments_d med,
1401: mth_workday_shifts_d wds,
1402: mth_systems_setup mss,
1403: mth_user_dim_entities_mst mue1,

Line 1457: FROM mth_equip_statuses_stg stg,

1453: null,
1454: null,
1455: mhd.HOUR_PK_KEY,
1456: stg.from_date
1457: FROM mth_equip_statuses_stg stg,
1458: mth_equipments_d med,
1459: mth_workday_shifts_d wds,
1460: mth_systems_setup mss,
1461: mth_user_dim_entities_mst mue1,

Line 1512: FROM mth_equip_statuses_stg stg,

1508: NULL,
1509: null,
1510: sts.reading_time,
1511: sts.hour_fk_key
1512: FROM mth_equip_statuses_stg stg,
1513: mth_equip_statuses sts
1514: WHERE stg.from_date = sts.reading_time
1515: AND sts.status = stg.status
1516: AND stg.status = 3

Line 1537: FROM mth_equip_statuses_stg stg,

1533: NULL,
1534: null,
1535: sts.reading_time,
1536: sts.hour_fk_key
1537: FROM mth_equip_statuses_stg stg,
1538: mth_equip_statuses sts
1539: WHERE stg.from_date = sts.reading_time
1540: AND sts.status = stg.status
1541: AND stg.status = 2

Line 1551: mth_util_pkg.truncate_table_partition('MTH_EQUIP_STATUSES_STG',v_processing_flag);

1547: );
1548:
1549: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1550:
1551: mth_util_pkg.truncate_table_partition('MTH_EQUIP_STATUSES_STG',v_processing_flag);
1552:
1553: mth_util_pkg.log_msg('INIT_STATUS_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
1554: EXCEPTION
1555: WHEN OTHERS THEN

Line 1583: INSERT INTO mth_equip_statuses_stg(equipment_fk,

1579: v_log_date := sysdate;
1580: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1581:
1582: --Ensuring the functionality of ES map is introduced before the staging to fact is executed.
1583: INSERT INTO mth_equip_statuses_stg(equipment_fk,
1584: shift_workday_fk,
1585: from_date,
1586: To_Date,
1587: status,

Line 1631: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1627: downtime_reason_code,
1628: v_processing_flag
1629: FROM mth_equip_statuses_err
1630: WHERE reprocess_ready_yn = 'Y');
1631: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_STG from error table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1632:
1633: --delete status table
1634: DELETE FROM MTH_EQUIP_STATUSES_ERR
1635: WHERE REPROCESS_READY_YN = 'Y';

Line 1639: mth_util_pkg.switch_column_default_value('MTH_EQUIP_STATUSES_STG',v_processing_flag);

1635: WHERE REPROCESS_READY_YN = 'Y';
1636: mth_util_pkg.log_msg('Number of rows deleted in MTH_EQUIP_STATUSES - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1637:
1638:
1639: mth_util_pkg.switch_column_default_value('MTH_EQUIP_STATUSES_STG',v_processing_flag);
1640:
1641:
1642: --Execute all validations on csv records
1643: -- Validation for Duplicate record

Line 1644: UPDATE mth_equip_statuses_stg stg

1640:
1641:
1642: --Execute all validations on csv records
1643: -- Validation for Duplicate record
1644: UPDATE mth_equip_statuses_stg stg
1645: SET stg.err_code = stg.err_code || 'DUP '
1646: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
1647: FROM mth_equip_statuses_stg
1648: GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup

Line 1647: FROM mth_equip_statuses_stg

1643: -- Validation for Duplicate record
1644: UPDATE mth_equip_statuses_stg stg
1645: SET stg.err_code = stg.err_code || 'DUP '
1646: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
1647: FROM mth_equip_statuses_stg
1648: GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
1649: WHERE dup.cnt>1
1650: AND dup.equipment_fk = stg.equipment_fk
1651: AND dup.shift_workday_fk = stg.shift_workday_fk

Line 1655: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1651: AND dup.shift_workday_fk = stg.shift_workday_fk
1652: AND dup.from_date = stg.from_date
1653: AND dup.To_Date = stg.To_Date
1654: AND stg.processing_flag = v_processing_flag);
1655: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1656:
1657: -- Validation for invalid equipment
1658: UPDATE mth_equip_statuses_stg stg
1659: SET stg.err_code = stg.err_code || 'EQP '

Line 1658: UPDATE mth_equip_statuses_stg stg

1654: AND stg.processing_flag = v_processing_flag);
1655: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1656:
1657: -- Validation for invalid equipment
1658: UPDATE mth_equip_statuses_stg stg
1659: SET stg.err_code = stg.err_code || 'EQP '
1660: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1661: FROM mth_equipments_d med,
1662: mth_equip_statuses_stg stg

Line 1662: mth_equip_statuses_stg stg

1658: UPDATE mth_equip_statuses_stg stg
1659: SET stg.err_code = stg.err_code || 'EQP '
1660: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1661: FROM mth_equipments_d med,
1662: mth_equip_statuses_stg stg
1663: WHERE med.equipment_pk = stg.equipment_fk) eqp
1664: WHERE eqp.equipment_pk = stg.equipment_fk
1665: AND stg.processing_flag = v_processing_flag);
1666: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 1666: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1662: mth_equip_statuses_stg stg
1663: WHERE med.equipment_pk = stg.equipment_fk) eqp
1664: WHERE eqp.equipment_pk = stg.equipment_fk
1665: AND stg.processing_flag = v_processing_flag);
1666: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1667:
1668: -- Validation for Inactive equipment
1669: UPDATE mth_equip_statuses_stg stg
1670: SET stg.err_code = stg.err_code || 'IEQ '

Line 1669: UPDATE mth_equip_statuses_stg stg

1665: AND stg.processing_flag = v_processing_flag);
1666: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1667:
1668: -- Validation for Inactive equipment
1669: UPDATE mth_equip_statuses_stg stg
1670: SET stg.err_code = stg.err_code || 'IEQ '
1671: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1672: FROM mth_equipments_d med,
1673: mth_equip_statuses_stg stg

Line 1673: mth_equip_statuses_stg stg

1669: UPDATE mth_equip_statuses_stg stg
1670: SET stg.err_code = stg.err_code || 'IEQ '
1671: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1672: FROM mth_equipments_d med,
1673: mth_equip_statuses_stg stg
1674: WHERE med.equipment_pk = stg.equipment_fk
1675: AND med.status <> 'ACTIVE') eqp
1676: WHERE eqp.equipment_pk = stg.equipment_fk
1677: AND stg.processing_flag = v_processing_flag);

Line 1678: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1674: WHERE med.equipment_pk = stg.equipment_fk
1675: AND med.status <> 'ACTIVE') eqp
1676: WHERE eqp.equipment_pk = stg.equipment_fk
1677: AND stg.processing_flag = v_processing_flag);
1678: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1679:
1680: -- Validation for invalid shift
1681: UPDATE mth_equip_statuses_stg stg
1682: SET stg.err_code = stg.err_code || 'WDS '

Line 1681: UPDATE mth_equip_statuses_stg stg

1677: AND stg.processing_flag = v_processing_flag);
1678: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1679:
1680: -- Validation for invalid shift
1681: UPDATE mth_equip_statuses_stg stg
1682: SET stg.err_code = stg.err_code || 'WDS '
1683: WHERE stg.shift_workday_fk IS NOT NULL
1684: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1685: FROM mth_workday_shifts_d mds,

Line 1686: mth_equip_statuses_stg stg

1682: SET stg.err_code = stg.err_code || 'WDS '
1683: WHERE stg.shift_workday_fk IS NOT NULL
1684: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
1685: FROM mth_workday_shifts_d mds,
1686: mth_equip_statuses_stg stg
1687: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1688: AND stg.shift_workday_fk IS NOT NULL) wds
1689: WHERE wds.shift_workday_pk = stg.shift_workday_fk
1690: AND stg.processing_flag = v_processing_flag);

Line 1691: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1687: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1688: AND stg.shift_workday_fk IS NOT NULL) wds
1689: WHERE wds.shift_workday_pk = stg.shift_workday_fk
1690: AND stg.processing_flag = v_processing_flag);
1691: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1692:
1693: -- Valildation for invalid equipment shift combination
1694: UPDATE mth_equip_statuses_stg stg
1695: SET stg.err_code = stg.err_code || 'ESD '

Line 1694: UPDATE mth_equip_statuses_stg stg

1690: AND stg.processing_flag = v_processing_flag);
1691: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1692:
1693: -- Valildation for invalid equipment shift combination
1694: UPDATE mth_equip_statuses_stg stg
1695: SET stg.err_code = stg.err_code || 'ESD '
1696: WHERE NOT EXISTS ( SELECT * FROM
1697: ( SELECT mee.equipment_pk, mws.shift_workday_pk
1698: FROM mth_equipments_d mee,

Line 1710: mth_equip_statuses_stg stg

1706: AND stg.processing_flag = v_processing_flag)
1707: AND EXISTS ( SELECT * FROM
1708: ( SELECT mds.shift_workday_pk
1709: FROM mth_workday_shifts_d mds,
1710: mth_equip_statuses_stg stg
1711: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
1712: AND stg.shift_workday_fk IS NOT NULL
1713: AND stg.processing_flag = v_processing_flag) wds
1714: WHERE wds.shift_workday_pk = stg.shift_workday_fk )

Line 1717: mth_equip_statuses_stg stg

1713: AND stg.processing_flag = v_processing_flag) wds
1714: WHERE wds.shift_workday_pk = stg.shift_workday_fk )
1715: AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
1716: FROM mth_equipments_d med,
1717: mth_equip_statuses_stg stg
1718: WHERE med.equipment_pk = stg.equipment_fk) eqp
1719: WHERE eqp.equipment_pk = stg.equipment_fk
1720: AND stg.processing_flag = v_processing_flag);
1721: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 1721: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1717: mth_equip_statuses_stg stg
1718: WHERE med.equipment_pk = stg.equipment_fk) eqp
1719: WHERE eqp.equipment_pk = stg.equipment_fk
1720: AND stg.processing_flag = v_processing_flag);
1721: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1722:
1723: -- Validation for user dimension 1
1724: UPDATE mth_equip_statuses_stg stg
1725: SET stg.err_code = stg.err_code || 'UD1 '

Line 1724: UPDATE mth_equip_statuses_stg stg

1720: AND stg.processing_flag = v_processing_flag);
1721: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1722:
1723: -- Validation for user dimension 1
1724: UPDATE mth_equip_statuses_stg stg
1725: SET stg.err_code = stg.err_code || 'UD1 '
1726: WHERE stg.user_dim1_fk IS NOT NULL
1727: AND EXISTS (SELECT *
1728: FROM

Line 1731: mth_equip_statuses_stg stg

1727: AND EXISTS (SELECT *
1728: FROM
1729: (SELECT mue.entity_pk, stg.user_dim1_fk
1730: FROM mth_user_dim_entities_mst mue,
1731: mth_equip_statuses_stg stg
1732: WHERE stg.user_dim1_fk = mue.entity_pk (+)
1733: AND stg.user_dim1_fk IS NOT NULL) ud1
1734: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
1735: AND stg.processing_flag = v_processing_flag

Line 1737: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1733: AND stg.user_dim1_fk IS NOT NULL) ud1
1734: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
1735: AND stg.processing_flag = v_processing_flag
1736: AND ud1.entity_pk IS NULL);
1737: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1738:
1739: -- Validation for user dimension 2
1740: UPDATE mth_equip_statuses_stg stg
1741: SET stg.err_code = stg.err_code || 'UD2 '

Line 1740: UPDATE mth_equip_statuses_stg stg

1736: AND ud1.entity_pk IS NULL);
1737: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1738:
1739: -- Validation for user dimension 2
1740: UPDATE mth_equip_statuses_stg stg
1741: SET stg.err_code = stg.err_code || 'UD2 '
1742: WHERE stg.user_dim2_fk IS NOT NULL
1743: AND EXISTS (SELECT *
1744: FROM

Line 1747: mth_equip_statuses_stg stg

1743: AND EXISTS (SELECT *
1744: FROM
1745: (SELECT mue.entity_pk, stg.user_dim2_fk
1746: FROM mth_user_dim_entities_mst mue,
1747: mth_equip_statuses_stg stg
1748: WHERE stg.user_dim2_fk = mue.entity_pk (+)
1749: AND stg.user_dim2_fk IS NOT NULL) ud2
1750: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
1751: AND stg.processing_flag = v_processing_flag

Line 1753: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1749: AND stg.user_dim2_fk IS NOT NULL) ud2
1750: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
1751: AND stg.processing_flag = v_processing_flag
1752: AND ud2.entity_pk IS NULL);
1753: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1754:
1755: -- Validation for user dimension 3
1756: UPDATE mth_equip_statuses_stg stg
1757: SET stg.err_code = stg.err_code || 'UD3 '

Line 1756: UPDATE mth_equip_statuses_stg stg

1752: AND ud2.entity_pk IS NULL);
1753: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1754:
1755: -- Validation for user dimension 3
1756: UPDATE mth_equip_statuses_stg stg
1757: SET stg.err_code = stg.err_code || 'UD3 '
1758: WHERE stg.user_dim3_fk IS NOT NULL
1759: AND EXISTS (SELECT *
1760: FROM

Line 1763: mth_equip_statuses_stg stg

1759: AND EXISTS (SELECT *
1760: FROM
1761: (SELECT mue.entity_pk, stg.user_dim3_fk
1762: FROM mth_user_dim_entities_mst mue,
1763: mth_equip_statuses_stg stg
1764: WHERE stg.user_dim3_fk = mue.entity_pk (+)
1765: AND stg.user_dim3_fk IS NOT NULL) ud3
1766: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
1767: AND stg.processing_flag = v_processing_flag

Line 1769: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1765: AND stg.user_dim3_fk IS NOT NULL) ud3
1766: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
1767: AND stg.processing_flag = v_processing_flag
1768: AND ud3.entity_pk IS NULL);
1769: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1770:
1771: -- Validation for user dimension 4
1772: UPDATE mth_equip_statuses_stg stg
1773: SET stg.err_code = stg.err_code || 'UD4 '

Line 1772: UPDATE mth_equip_statuses_stg stg

1768: AND ud3.entity_pk IS NULL);
1769: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1770:
1771: -- Validation for user dimension 4
1772: UPDATE mth_equip_statuses_stg stg
1773: SET stg.err_code = stg.err_code || 'UD4 '
1774: WHERE stg.user_dim4_fk IS NOT NULL
1775: AND EXISTS (SELECT *
1776: FROM

Line 1779: mth_equip_statuses_stg stg

1775: AND EXISTS (SELECT *
1776: FROM
1777: (SELECT mue.entity_pk, stg.user_dim4_fk
1778: FROM mth_user_dim_entities_mst mue,
1779: mth_equip_statuses_stg stg
1780: WHERE stg.user_dim4_fk = mue.entity_pk (+)
1781: AND stg.user_dim4_fk IS NOT NULL) ud4
1782: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
1783: AND stg.processing_flag = v_processing_flag

Line 1785: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1781: AND stg.user_dim4_fk IS NOT NULL) ud4
1782: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
1783: AND stg.processing_flag = v_processing_flag
1784: AND ud4.entity_pk IS NULL);
1785: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1786:
1787: -- Validation for user dimension 5
1788: UPDATE mth_equip_statuses_stg stg
1789: SET stg.err_code = stg.err_code || 'UD5 '

Line 1788: UPDATE mth_equip_statuses_stg stg

1784: AND ud4.entity_pk IS NULL);
1785: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1786:
1787: -- Validation for user dimension 5
1788: UPDATE mth_equip_statuses_stg stg
1789: SET stg.err_code = stg.err_code || 'UD5 '
1790: WHERE stg.user_dim5_fk IS NOT NULL
1791: AND EXISTS (SELECT *
1792: FROM

Line 1795: mth_equip_statuses_stg stg

1791: AND EXISTS (SELECT *
1792: FROM
1793: (SELECT mue.entity_pk, stg.user_dim5_fk
1794: FROM mth_user_dim_entities_mst mue,
1795: mth_equip_statuses_stg stg
1796: WHERE stg.user_dim5_fk = mue.entity_pk (+)
1797: AND stg.user_dim5_fk IS NOT NULL) ud5
1798: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
1799: AND stg.processing_flag = v_processing_flag

Line 1801: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1797: AND stg.user_dim5_fk IS NOT NULL) ud5
1798: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
1799: AND stg.processing_flag = v_processing_flag
1800: AND ud5.entity_pk IS NULL);
1801: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1802:
1803: -- Validation for 'GAP' error code
1804: UPDATE mth_equip_statuses_stg stg
1805: SET stg.err_code = stg.err_code || 'GAP '

Line 1804: UPDATE mth_equip_statuses_stg stg

1800: AND ud5.entity_pk IS NULL);
1801: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1802:
1803: -- Validation for 'GAP' error code
1804: UPDATE mth_equip_statuses_stg stg
1805: SET stg.err_code = stg.err_code || 'GAP '
1806: WHERE EXISTS (SELECT *
1807: FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
1808: over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code

Line 1811: FROM mth_equip_statuses_stg a WHERE a.processing_flag = v_processing_flag) b) c

1807: FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
1808: over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
1809: FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
1810: (Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
1811: FROM mth_equip_statuses_stg a WHERE a.processing_flag = v_processing_flag) b) c
1812: WHERE c.Count >= 1
1813: AND stg.from_date = c.from_date
1814: AND stg.equipment_fk = c.equipment_fk
1815: AND stg.processing_flag = v_processing_flag);

Line 1816: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1812: WHERE c.Count >= 1
1813: AND stg.from_date = c.from_date
1814: AND stg.equipment_fk = c.equipment_fk
1815: AND stg.processing_flag = v_processing_flag);
1816: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1817:
1818: -- Validation for FTD
1819: UPDATE mth_equip_statuses_stg stg
1820: SET stg.err_code = stg.err_code || 'FTD '

Line 1819: UPDATE mth_equip_statuses_stg stg

1815: AND stg.processing_flag = v_processing_flag);
1816: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1817:
1818: -- Validation for FTD
1819: UPDATE mth_equip_statuses_stg stg
1820: SET stg.err_code = stg.err_code || 'FTD '
1821: WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE
1822: AND stg.processing_flag = v_processing_flag;
1823: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 1823: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1819: UPDATE mth_equip_statuses_stg stg
1820: SET stg.err_code = stg.err_code || 'FTD '
1821: WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE
1822: AND stg.processing_flag = v_processing_flag;
1823: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1824:
1825: -- Validation for DTR
1826: UPDATE mth_equip_statuses_stg stg
1827: SET stg.err_code = stg.err_code || 'DTR '

Line 1826: UPDATE mth_equip_statuses_stg stg

1822: AND stg.processing_flag = v_processing_flag;
1823: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1824:
1825: -- Validation for DTR
1826: UPDATE mth_equip_statuses_stg stg
1827: SET stg.err_code = stg.err_code || 'DTR '
1828: WHERE EXISTS (SELECT *
1829: FROM
1830: (SELECT flk.lookup_code,

Line 1837: mth_equip_statuses_stg stg

1833: stg.to_date,
1834: stg.downtime_reason_code,
1835: stg.status
1836: FROM fnd_lookups flk,
1837: mth_equip_statuses_stg stg
1838: WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
1839: AND stg.downtime_reason_code = flk.lookup_code (+)
1840: AND stg.status = 3) dtr
1841: WHERE ((stg.downtime_reason_code = dtr.downtime_reason_code

Line 1848: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1844: AND dtr.from_date = stg.from_date
1845: AND dtr.To_Date = stg.To_Date)
1846: OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL))
1847: AND stg.processing_flag = v_processing_flag);
1848: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1849:
1850: -- Validation for IDR
1851: UPDATE mth_equip_statuses_stg stg
1852: SET stg.err_code = stg.err_code || 'IDR '

Line 1851: UPDATE mth_equip_statuses_stg stg

1847: AND stg.processing_flag = v_processing_flag);
1848: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1849:
1850: -- Validation for IDR
1851: UPDATE mth_equip_statuses_stg stg
1852: SET stg.err_code = stg.err_code || 'IDR '
1853: WHERE EXISTS (SELECT *
1854: FROM
1855: ( SELECT flk.lookup_code,

Line 1862: mth_equip_statuses_stg stg

1858: stg.to_date,
1859: stg.downtime_reason_code,
1860: stg.status
1861: FROM fnd_lookups flk,
1862: mth_equip_statuses_stg stg
1863: WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
1864: AND stg.downtime_reason_code = flk.lookup_code (+)
1865: AND stg.status = 2) dtr
1866: WHERE stg.downtime_reason_code = dtr.downtime_reason_code

Line 1872: mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1868: AND dtr.lookup_code IS NULL
1869: AND dtr.from_date = stg.from_date
1870: AND dtr.To_Date = stg.To_Date
1871: AND stg.processing_flag = v_processing_flag);
1872: mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1873:
1874: -- Validation for ITR
1875: UPDATE mth_equip_statuses_stg stg
1876: SET stg.err_code = stg.err_code || 'ITR '

Line 1875: UPDATE mth_equip_statuses_stg stg

1871: AND stg.processing_flag = v_processing_flag);
1872: mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1873:
1874: -- Validation for ITR
1875: UPDATE mth_equip_statuses_stg stg
1876: SET stg.err_code = stg.err_code || 'ITR '
1877: WHERE EXISTS ( SELECT * FROM (
1878: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1879: FROM mth_workday_shifts_d mds,

Line 1880: mth_equip_statuses_stg stg,

1876: SET stg.err_code = stg.err_code || 'ITR '
1877: WHERE EXISTS ( SELECT * FROM (
1878: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1879: FROM mth_workday_shifts_d mds,
1880: mth_equip_statuses_stg stg,
1881: mth_equipment_shifts_d mes,
1882: mth_equipments_d med
1883: WHERE stg.shift_workday_fk = mds.shift_workday_pk
1884: AND stg.equipment_fk = med.equipment_pk

Line 1896: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1892: AND itr.equipment_pk = stg.equipment_fk
1893: AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
1894: AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date)
1895: AND stg.processing_flag = v_processing_flag);
1896: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1897:
1898: -- Validation for MSF
1899: UPDATE mth_equip_statuses_stg stg
1900: SET stg.err_code = stg.err_code || 'MSF '

Line 1899: UPDATE mth_equip_statuses_stg stg

1895: AND stg.processing_flag = v_processing_flag);
1896: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1897:
1898: -- Validation for MSF
1899: UPDATE mth_equip_statuses_stg stg
1900: SET stg.err_code = stg.err_code || 'MSF '
1901: WHERE EXISTS ( SELECT * FROM (
1902: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1903: FROM mth_workday_shifts_d mds,

Line 1904: mth_equip_statuses_stg stg,

1900: SET stg.err_code = stg.err_code || 'MSF '
1901: WHERE EXISTS ( SELECT * FROM (
1902: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
1903: FROM mth_workday_shifts_d mds,
1904: mth_equip_statuses_stg stg,
1905: mth_equipment_shifts_d mes,
1906: mth_equipments_d med
1907: WHERE stg.shift_workday_fk = mds.shift_workday_pk
1908: AND stg.equipment_fk = med.equipment_pk

Line 1924: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1920: AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
1921: OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
1922: AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date)))
1923: AND stg.processing_flag = v_processing_flag);
1924: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1925:
1926: --Validation for OCSV
1927: UPDATE mth_equip_statuses_stg stag
1928: SET stag.err_code = stag.err_code || 'OCSV '

Line 1927: UPDATE mth_equip_statuses_stg stag

1923: AND stg.processing_flag = v_processing_flag);
1924: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1925:
1926: --Validation for OCSV
1927: UPDATE mth_equip_statuses_stg stag
1928: SET stag.err_code = stag.err_code || 'OCSV '
1929: WHERE EXISTS (SELECT *
1930: FROM
1931: (SELECT CASE

Line 1946: FROM mth_equip_statuses_stg stg,

1942: THEN 1 END overlap,
1943: med.equipment_pk,
1944: stg.from_date,
1945: stg.to_date
1946: FROM mth_equip_statuses_stg stg,
1947: mth_equipments_d med
1948: WHERE stg.equipment_fk = med.equipment_pk
1949: AND stg.processing_flag = v_processing_flag) ovp
1950: WHERE ovp.overlap = 1

Line 1955: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1951: AND stag.equipment_fk = ovp.equipment_pk
1952: AND stag.from_date = ovp.from_date
1953: AND stag.To_Date = ovp.To_Date
1954: AND stag.processing_flag = v_processing_flag);
1955: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1956:
1957: --validation for OSTS
1958: UPDATE mth_equip_statuses_stg stag
1959: SET stag.err_code = stag.err_code || 'OSTS '

Line 1958: UPDATE mth_equip_statuses_stg stag

1954: AND stag.processing_flag = v_processing_flag);
1955: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1956:
1957: --validation for OSTS
1958: UPDATE mth_equip_statuses_stg stag
1959: SET stag.err_code = stag.err_code || 'OSTS '
1960: WHERE EXISTS (SELECT *
1961: FROM ( SELECT CASE
1962: WHEN (stg.FROM_DATE >= sts.FROM_DATE

Line 1973: FROM mth_equip_statuses_stg stg,

1969: med.equipment_pk,
1970: wds.shift_workday_pk,
1971: stg.from_date,
1972: stg.to_date
1973: FROM mth_equip_statuses_stg stg,
1974: mth_equip_statuses sts,
1975: mth_equipments_d med,
1976: mth_workday_shifts_d wds
1977: WHERE stg.equipment_fk = med.equipment_pk

Line 1987: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1983: AND stag.equipment_fk = osts.equipment_pk
1984: AND stag.shift_workday_fk = osts.shift_workday_pk
1985: AND stag.processing_flag = v_processing_flag
1986: AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
1987: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1988:
1989: --validation for WRC
1990: UPDATE mth_equip_statuses_stg stg
1991: SET stg.err_code = stg.err_code ||'WRC '

Line 1990: UPDATE mth_equip_statuses_stg stg

1986: AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
1987: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1988:
1989: --validation for WRC
1990: UPDATE mth_equip_statuses_stg stg
1991: SET stg.err_code = stg.err_code ||'WRC '
1992: WHERE NOT EXISTS ( SELECT *
1993: FROM (SELECT stg.*
1994: FROM mth_equip_statuses_stg stg,

Line 1994: FROM mth_equip_statuses_stg stg,

1990: UPDATE mth_equip_statuses_stg stg
1991: SET stg.err_code = stg.err_code ||'WRC '
1992: WHERE NOT EXISTS ( SELECT *
1993: FROM (SELECT stg.*
1994: FROM mth_equip_statuses_stg stg,
1995: MTH_EQUIPMENT_REASON_SETUP mer,
1996: mth_equipments_d med
1997: WHERE stg.equipment_fk = med.equipment_pk
1998: AND med.equipment_pk_key = mer.equipment_fk_key

Line 2011: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2007: AND stg.processing_flag = v_processing_flag
2008: )
2009: AND stg.status IN (3,2)
2010: AND stg.downtime_reason_code IS NOT NULL;
2011: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2012:
2013: --validation for STS
2014: UPDATE mth_equip_statuses_stg stg
2015: SET stg.err_code = stg.err_code ||'STS '

Line 2014: UPDATE mth_equip_statuses_stg stg

2010: AND stg.downtime_reason_code IS NOT NULL;
2011: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2012:
2013: --validation for STS
2014: UPDATE mth_equip_statuses_stg stg
2015: SET stg.err_code = stg.err_code ||'STS '
2016: WHERE stg.processing_flag = v_processing_flag
2017: AND stg.status NOT IN (1,2,3,4);
2018: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 2018: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2014: UPDATE mth_equip_statuses_stg stg
2015: SET stg.err_code = stg.err_code ||'STS '
2016: WHERE stg.processing_flag = v_processing_flag
2017: AND stg.status NOT IN (1,2,3,4);
2018: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2019:
2020: --Insert records into mth_equip_statuses_err
2021: INSERT INTO mth_equip_statuses_err(equipment_fk,
2022: shift_workday_fk,

Line 2069: FROM mth_equip_statuses_stg

2065: user_measure4,
2066: user_measure5,
2067: err_code,
2068: downtime_reason_code
2069: FROM mth_equip_statuses_stg
2070: WHERE err_code IS NOT NULL
2071: AND processing_flag = v_processing_flag);
2072: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2073:

Line 2103: FROM mth_equip_statuses_stg stg,

2099: v_log_date log_date,
2100: v_unassigned_val unassigned_val,
2101: mhd.HOUR_PK_KEY hour_fk_key,
2102: stg.from_date reading_time
2103: FROM mth_equip_statuses_stg stg,
2104: mth_equipments_d med,
2105: mth_workday_shifts_d wds,
2106: mth_systems_setup mss,
2107: mth_user_dim_entities_mst mue1,

Line 2154: FROM mth_equip_statuses_stg stg,

2150: v_log_date log_date,
2151: v_unassigned_val unassigned_val,
2152: mhd.HOUR_PK_KEY hour_fk_key,
2153: stg.from_date reading_time
2154: FROM mth_equip_statuses_stg stg,
2155: mth_equipments_d med,
2156: mth_workday_shifts_d wds,
2157: mth_systems_setup mss,
2158: mth_user_dim_entities_mst mue1,

Line 2286: FROM mth_equip_statuses_stg stg,

2282: v_unassigned_val ua_val,
2283: NULL oth_col,
2284: sts.reading_time reading_time,
2285: sts.hour_fk_key hour_fk_key
2286: FROM mth_equip_statuses_stg stg,
2287: mth_equip_statuses sts
2288: WHERE stg.from_date = sts.reading_time
2289: AND sts.status = stg.status
2290: AND stg.status = 3

Line 2307: FROM mth_equip_statuses_stg stg,

2303: v_unassigned_val ua_val,
2304: NULL oth_col,
2305: sts.reading_time reading_time,
2306: sts.hour_fk_key hour_fk_key
2307: FROM mth_equip_statuses_stg stg,
2308: mth_equip_statuses sts
2309: WHERE stg.from_date = sts.reading_time
2310: AND sts.status = stg.status
2311: AND stg.status = 2

Line 2358: mth_util_pkg.truncate_table_partition('MTH_EQUIP_STATUSES_STG',v_processing_flag);

2354: subquery.hour_fk_key);
2355:
2356: mth_util_pkg.log_msg('Number of rows merged in MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2357:
2358: mth_util_pkg.truncate_table_partition('MTH_EQUIP_STATUSES_STG',v_processing_flag);
2359:
2360: mth_util_pkg.log_msg('INCR_STATUS_FROM_CSV end', mth_util_pkg.G_DBG_PROC_FUN_END);
2361: EXCEPTION
2362: WHEN OTHERS THEN

Line 2410: FROM mth_equip_statuses_stg

2406: user_dim4_fk,
2407: user_dim5_fk,
2408: downtime_reason_code,
2409: err_code
2410: FROM mth_equip_statuses_stg
2411: WHERE err_code IS NOT NULL;
2412:
2413: TYPE fetch_err_rows IS TABLE OF c_error_rows%ROWTYPE;
2414: l_err_rows fetch_err_rows;

Line 2441: mth_equip_statuses_stg stg

2437: THEN
2438: SELECT Count(*)
2439: INTO l_count
2440: FROM mth_equipments_d med,
2441: mth_equip_statuses_stg stg
2442: WHERE med.equipment_pk = stg.equipment_fk
2443: AND med.equipment_pk_key = p_equipment_pk_key
2444: AND med.plant_fk_key = Nvl(p_plant_pk_key,med.plant_fk_key);
2445: END IF;

Line 2456: FROM mth_equip_statuses_stg stg

2452: END IF;
2453:
2454: SELECT Min(from_date)
2455: INTO p_min_from_date_csv
2456: FROM mth_equip_statuses_stg stg
2457: WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
2458: FROM mth_equipments_d
2459: WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
2460:

Line 2463: FROM mth_equip_statuses_stg stg

2459: WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
2460:
2461: SELECT Max(to_date)
2462: INTO p_max_to_date_csv
2463: FROM mth_equip_statuses_stg stg
2464: WHERE stg.equipment_fk = Nvl((SELECT equipment_pk
2465: FROM mth_equipments_d
2466: WHERE equipment_pk_key = p_equipment_pk_key),stg.equipment_fk);
2467:

Line 2648: UPDATE mth_equip_statuses_stg stg

2644: mth_util_pkg.log_msg('Number of rows deleted from MTH_TAG_REASON_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2645:
2646: --Execute all validations on csv records
2647: -- Validation for Duplicate record
2648: UPDATE mth_equip_statuses_stg stg
2649: SET stg.err_code = stg.err_code || 'DUP '
2650: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
2651: FROM mth_equip_statuses_stg
2652: GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup

Line 2651: FROM mth_equip_statuses_stg

2647: -- Validation for Duplicate record
2648: UPDATE mth_equip_statuses_stg stg
2649: SET stg.err_code = stg.err_code || 'DUP '
2650: WHERE EXISTS ( SELECT * FROM ( SELECT equipment_fk,shift_workday_fk,from_date,To_Date,Count(equipment_fk) cnt
2651: FROM mth_equip_statuses_stg
2652: GROUP BY equipment_fk,shift_workday_fk,from_date,To_Date) dup
2653: WHERE dup.cnt>1
2654: AND dup.equipment_fk = stg.equipment_fk
2655: AND dup.shift_workday_fk = stg.shift_workday_fk

Line 2658: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2654: AND dup.equipment_fk = stg.equipment_fk
2655: AND dup.shift_workday_fk = stg.shift_workday_fk
2656: AND dup.from_date = stg.from_date
2657: AND dup.To_Date = stg.To_Date );
2658: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2659:
2660: -- Validation for invalid equipment
2661: UPDATE mth_equip_statuses_stg stg
2662: SET stg.err_code = stg.err_code || 'EQP '

Line 2661: UPDATE mth_equip_statuses_stg stg

2657: AND dup.To_Date = stg.To_Date );
2658: mth_util_pkg.log_msg('Number of rows with DUP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2659:
2660: -- Validation for invalid equipment
2661: UPDATE mth_equip_statuses_stg stg
2662: SET stg.err_code = stg.err_code || 'EQP '
2663: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
2664: FROM mth_equipments_d med,
2665: mth_equip_statuses_stg stg

Line 2665: mth_equip_statuses_stg stg

2661: UPDATE mth_equip_statuses_stg stg
2662: SET stg.err_code = stg.err_code || 'EQP '
2663: WHERE NOT EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
2664: FROM mth_equipments_d med,
2665: mth_equip_statuses_stg stg
2666: WHERE med.equipment_pk = stg.equipment_fk) eqp
2667: WHERE eqp.equipment_pk = stg.equipment_fk );
2668: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2669:

Line 2668: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2664: FROM mth_equipments_d med,
2665: mth_equip_statuses_stg stg
2666: WHERE med.equipment_pk = stg.equipment_fk) eqp
2667: WHERE eqp.equipment_pk = stg.equipment_fk );
2668: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2669:
2670: -- Validation for Inactive equipment
2671: UPDATE mth_equip_statuses_stg stg
2672: SET stg.err_code = stg.err_code || 'IEQ '

Line 2671: UPDATE mth_equip_statuses_stg stg

2667: WHERE eqp.equipment_pk = stg.equipment_fk );
2668: mth_util_pkg.log_msg('Number of rows with EQP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2669:
2670: -- Validation for Inactive equipment
2671: UPDATE mth_equip_statuses_stg stg
2672: SET stg.err_code = stg.err_code || 'IEQ '
2673: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
2674: FROM mth_equipments_d med,
2675: mth_equip_statuses_stg stg

Line 2675: mth_equip_statuses_stg stg

2671: UPDATE mth_equip_statuses_stg stg
2672: SET stg.err_code = stg.err_code || 'IEQ '
2673: WHERE EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
2674: FROM mth_equipments_d med,
2675: mth_equip_statuses_stg stg
2676: WHERE med.equipment_pk = stg.equipment_fk
2677: AND med.status <> 'ACTIVE') eqp
2678: WHERE eqp.equipment_pk = stg.equipment_fk );
2679: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 2679: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2675: mth_equip_statuses_stg stg
2676: WHERE med.equipment_pk = stg.equipment_fk
2677: AND med.status <> 'ACTIVE') eqp
2678: WHERE eqp.equipment_pk = stg.equipment_fk );
2679: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2680:
2681: -- Validation for invalid shift
2682: UPDATE mth_equip_statuses_stg stg
2683: SET stg.err_code = stg.err_code || 'WDS '

Line 2682: UPDATE mth_equip_statuses_stg stg

2678: WHERE eqp.equipment_pk = stg.equipment_fk );
2679: mth_util_pkg.log_msg('Number of rows with IEQ in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2680:
2681: -- Validation for invalid shift
2682: UPDATE mth_equip_statuses_stg stg
2683: SET stg.err_code = stg.err_code || 'WDS '
2684: WHERE stg.shift_workday_fk IS NOT NULL
2685: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
2686: FROM mth_workday_shifts_d mds,

Line 2687: mth_equip_statuses_stg stg

2683: SET stg.err_code = stg.err_code || 'WDS '
2684: WHERE stg.shift_workday_fk IS NOT NULL
2685: AND NOT EXISTS ( SELECT * FROM ( SELECT mds.shift_workday_pk
2686: FROM mth_workday_shifts_d mds,
2687: mth_equip_statuses_stg stg
2688: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
2689: AND stg.shift_workday_fk IS NOT NULL) wds
2690: WHERE wds.shift_workday_pk = stg.shift_workday_fk);
2691: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 2691: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2687: mth_equip_statuses_stg stg
2688: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
2689: AND stg.shift_workday_fk IS NOT NULL) wds
2690: WHERE wds.shift_workday_pk = stg.shift_workday_fk);
2691: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2692:
2693: -- Valildation for invalid equipment shift combination
2694: UPDATE mth_equip_statuses_stg stg
2695: SET stg.err_code = stg.err_code || 'ESD '

Line 2694: UPDATE mth_equip_statuses_stg stg

2690: WHERE wds.shift_workday_pk = stg.shift_workday_fk);
2691: mth_util_pkg.log_msg('Number of rows with WDS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2692:
2693: -- Valildation for invalid equipment shift combination
2694: UPDATE mth_equip_statuses_stg stg
2695: SET stg.err_code = stg.err_code || 'ESD '
2696: WHERE NOT EXISTS ( SELECT * FROM
2697: ( SELECT mee.equipment_pk, mws.shift_workday_pk
2698: FROM mth_equipments_d mee,

Line 2709: mth_equip_statuses_stg stg

2705: AND stg.shift_workday_fk = esd.shift_workday_pk)
2706: AND EXISTS ( SELECT * FROM
2707: ( SELECT mds.shift_workday_pk
2708: FROM mth_workday_shifts_d mds,
2709: mth_equip_statuses_stg stg
2710: WHERE stg.shift_workday_fk = mds.shift_workday_pk(+)
2711: AND stg.shift_workday_fk IS NOT NULL) wds
2712: WHERE wds.shift_workday_pk = stg.shift_workday_fk )
2713: AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk

Line 2715: mth_equip_statuses_stg stg

2711: AND stg.shift_workday_fk IS NOT NULL) wds
2712: WHERE wds.shift_workday_pk = stg.shift_workday_fk )
2713: AND EXISTS ( SELECT * FROM ( SELECT med.equipment_pk_key, med.equipment_pk
2714: FROM mth_equipments_d med,
2715: mth_equip_statuses_stg stg
2716: WHERE med.equipment_pk = stg.equipment_fk) eqp
2717: WHERE eqp.equipment_pk = stg.equipment_fk );
2718: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2719:

Line 2718: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2714: FROM mth_equipments_d med,
2715: mth_equip_statuses_stg stg
2716: WHERE med.equipment_pk = stg.equipment_fk) eqp
2717: WHERE eqp.equipment_pk = stg.equipment_fk );
2718: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2719:
2720: -- Validation for user dimension 1
2721: UPDATE mth_equip_statuses_stg stg
2722: SET stg.err_code = stg.err_code || 'UD1 '

Line 2721: UPDATE mth_equip_statuses_stg stg

2717: WHERE eqp.equipment_pk = stg.equipment_fk );
2718: mth_util_pkg.log_msg('Number of rows with ESD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2719:
2720: -- Validation for user dimension 1
2721: UPDATE mth_equip_statuses_stg stg
2722: SET stg.err_code = stg.err_code || 'UD1 '
2723: WHERE stg.user_dim1_fk IS NOT NULL
2724: AND EXISTS (SELECT *
2725: FROM

Line 2728: mth_equip_statuses_stg stg

2724: AND EXISTS (SELECT *
2725: FROM
2726: (SELECT mue.entity_pk, stg.user_dim1_fk
2727: FROM mth_user_dim_entities_mst mue,
2728: mth_equip_statuses_stg stg
2729: WHERE stg.user_dim1_fk = mue.entity_pk (+)
2730: AND stg.user_dim1_fk IS NOT NULL) ud1
2731: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
2732: AND ud1.entity_pk IS NULL);

Line 2733: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2729: WHERE stg.user_dim1_fk = mue.entity_pk (+)
2730: AND stg.user_dim1_fk IS NOT NULL) ud1
2731: WHERE ud1.user_dim1_fk = stg.user_dim1_fk
2732: AND ud1.entity_pk IS NULL);
2733: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2734:
2735: -- Validation for user dimension 2
2736: UPDATE mth_equip_statuses_stg stg
2737: SET stg.err_code = stg.err_code || 'UD2 '

Line 2736: UPDATE mth_equip_statuses_stg stg

2732: AND ud1.entity_pk IS NULL);
2733: mth_util_pkg.log_msg('Number of rows with UD1 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2734:
2735: -- Validation for user dimension 2
2736: UPDATE mth_equip_statuses_stg stg
2737: SET stg.err_code = stg.err_code || 'UD2 '
2738: WHERE stg.user_dim2_fk IS NOT NULL
2739: AND EXISTS (SELECT *
2740: FROM

Line 2743: mth_equip_statuses_stg stg

2739: AND EXISTS (SELECT *
2740: FROM
2741: (SELECT mue.entity_pk, stg.user_dim2_fk
2742: FROM mth_user_dim_entities_mst mue,
2743: mth_equip_statuses_stg stg
2744: WHERE stg.user_dim2_fk = mue.entity_pk (+)
2745: AND stg.user_dim2_fk IS NOT NULL) ud2
2746: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
2747: AND ud2.entity_pk IS NULL);

Line 2748: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2744: WHERE stg.user_dim2_fk = mue.entity_pk (+)
2745: AND stg.user_dim2_fk IS NOT NULL) ud2
2746: WHERE ud2.user_dim2_fk = stg.user_dim2_fk
2747: AND ud2.entity_pk IS NULL);
2748: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2749:
2750: -- Validation for user dimension 3
2751: UPDATE mth_equip_statuses_stg stg
2752: SET stg.err_code = stg.err_code || 'UD3 '

Line 2751: UPDATE mth_equip_statuses_stg stg

2747: AND ud2.entity_pk IS NULL);
2748: mth_util_pkg.log_msg('Number of rows with UD2 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2749:
2750: -- Validation for user dimension 3
2751: UPDATE mth_equip_statuses_stg stg
2752: SET stg.err_code = stg.err_code || 'UD3 '
2753: WHERE stg.user_dim3_fk IS NOT NULL
2754: AND EXISTS (SELECT *
2755: FROM

Line 2758: mth_equip_statuses_stg stg

2754: AND EXISTS (SELECT *
2755: FROM
2756: (SELECT mue.entity_pk, stg.user_dim3_fk
2757: FROM mth_user_dim_entities_mst mue,
2758: mth_equip_statuses_stg stg
2759: WHERE stg.user_dim3_fk = mue.entity_pk (+)
2760: AND stg.user_dim3_fk IS NOT NULL) ud3
2761: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
2762: AND ud3.entity_pk IS NULL);

Line 2763: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2759: WHERE stg.user_dim3_fk = mue.entity_pk (+)
2760: AND stg.user_dim3_fk IS NOT NULL) ud3
2761: WHERE ud3.user_dim3_fk = stg.user_dim3_fk
2762: AND ud3.entity_pk IS NULL);
2763: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2764:
2765: -- Validation for user dimension 4
2766: UPDATE mth_equip_statuses_stg stg
2767: SET stg.err_code = stg.err_code || 'UD4 '

Line 2766: UPDATE mth_equip_statuses_stg stg

2762: AND ud3.entity_pk IS NULL);
2763: mth_util_pkg.log_msg('Number of rows with UD3 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2764:
2765: -- Validation for user dimension 4
2766: UPDATE mth_equip_statuses_stg stg
2767: SET stg.err_code = stg.err_code || 'UD4 '
2768: WHERE stg.user_dim4_fk IS NOT NULL
2769: AND EXISTS (SELECT *
2770: FROM

Line 2773: mth_equip_statuses_stg stg

2769: AND EXISTS (SELECT *
2770: FROM
2771: (SELECT mue.entity_pk, stg.user_dim4_fk
2772: FROM mth_user_dim_entities_mst mue,
2773: mth_equip_statuses_stg stg
2774: WHERE stg.user_dim4_fk = mue.entity_pk (+)
2775: AND stg.user_dim4_fk IS NOT NULL) ud4
2776: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
2777: AND ud4.entity_pk IS NULL);

Line 2778: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2774: WHERE stg.user_dim4_fk = mue.entity_pk (+)
2775: AND stg.user_dim4_fk IS NOT NULL) ud4
2776: WHERE ud4.user_dim4_fk = stg.user_dim4_fk
2777: AND ud4.entity_pk IS NULL);
2778: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2779:
2780: -- Validation for user dimension 5
2781: UPDATE mth_equip_statuses_stg stg
2782: SET stg.err_code = stg.err_code || 'UD5 '

Line 2781: UPDATE mth_equip_statuses_stg stg

2777: AND ud4.entity_pk IS NULL);
2778: mth_util_pkg.log_msg('Number of rows with UD4 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2779:
2780: -- Validation for user dimension 5
2781: UPDATE mth_equip_statuses_stg stg
2782: SET stg.err_code = stg.err_code || 'UD5 '
2783: WHERE stg.user_dim5_fk IS NOT NULL
2784: AND EXISTS (SELECT *
2785: FROM

Line 2788: mth_equip_statuses_stg stg

2784: AND EXISTS (SELECT *
2785: FROM
2786: (SELECT mue.entity_pk, stg.user_dim5_fk
2787: FROM mth_user_dim_entities_mst mue,
2788: mth_equip_statuses_stg stg
2789: WHERE stg.user_dim5_fk = mue.entity_pk (+)
2790: AND stg.user_dim5_fk IS NOT NULL) ud5
2791: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
2792: AND ud5.entity_pk IS NULL);

Line 2793: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2789: WHERE stg.user_dim5_fk = mue.entity_pk (+)
2790: AND stg.user_dim5_fk IS NOT NULL) ud5
2791: WHERE ud5.user_dim5_fk = stg.user_dim5_fk
2792: AND ud5.entity_pk IS NULL);
2793: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2794:
2795: -- Validation for 'GAP' error code
2796: UPDATE mth_equip_statuses_stg stg
2797: SET stg.err_code = stg.err_code || 'GAP '

Line 2796: UPDATE mth_equip_statuses_stg stg

2792: AND ud5.entity_pk IS NULL);
2793: mth_util_pkg.log_msg('Number of rows with UD5 in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2794:
2795: -- Validation for 'GAP' error code
2796: UPDATE mth_equip_statuses_stg stg
2797: SET stg.err_code = stg.err_code || 'GAP '
2798: WHERE EXISTS (SELECT *
2799: FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
2800: over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code

Line 2803: FROM mth_equip_statuses_stg a) b) c

2799: FROM (SELECT sum(case when ((b.from_date = (b.prev_to_date + (1 / 86400)) AND b.err_code IS NULL) or (b.prev_to_date IS NULL AND b.err_code IS NULL) AND b.prev_err_code IS NULL) then 0 else 1 end)
2800: over (partition by b.equipment_fk order by b.from_date ) count, b.from_date, b.equipment_fk, b.err_code
2801: FROM (SELECT (Lag (a.To_Date) over (partition by a.equipment_fk order by a.from_date )) prev_to_date, a.from_date, a.equipment_fk, a.err_code,
2802: (Lag (a.err_code) over (partition by a.equipment_fk ORDER BY a.from_date)) prev_err_code
2803: FROM mth_equip_statuses_stg a) b) c
2804: WHERE c.Count >= 1
2805: AND stg.from_date = c.from_date
2806: AND stg.equipment_fk = c.equipment_fk);
2807: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 2807: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2803: FROM mth_equip_statuses_stg a) b) c
2804: WHERE c.Count >= 1
2805: AND stg.from_date = c.from_date
2806: AND stg.equipment_fk = c.equipment_fk);
2807: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2808:
2809: -- Validation for FTD
2810: UPDATE mth_equip_statuses_stg stg
2811: SET stg.err_code = stg.err_code || 'FTD '

Line 2810: UPDATE mth_equip_statuses_stg stg

2806: AND stg.equipment_fk = c.equipment_fk);
2807: mth_util_pkg.log_msg('Number of rows with GAP in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2808:
2809: -- Validation for FTD
2810: UPDATE mth_equip_statuses_stg stg
2811: SET stg.err_code = stg.err_code || 'FTD '
2812: WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE;
2813: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2814:

Line 2813: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2809: -- Validation for FTD
2810: UPDATE mth_equip_statuses_stg stg
2811: SET stg.err_code = stg.err_code || 'FTD '
2812: WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE;
2813: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2814:
2815: -- Validation for DTR
2816: UPDATE mth_equip_statuses_stg stg
2817: SET stg.err_code = stg.err_code || 'DTR '

Line 2816: UPDATE mth_equip_statuses_stg stg

2812: WHERE stg.from_date > SYSDATE OR stg.to_date > SYSDATE;
2813: mth_util_pkg.log_msg('Number of rows with FTD in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2814:
2815: -- Validation for DTR
2816: UPDATE mth_equip_statuses_stg stg
2817: SET stg.err_code = stg.err_code || 'DTR '
2818: WHERE EXISTS (SELECT *
2819: FROM
2820: (SELECT flk.lookup_code,

Line 2827: mth_equip_statuses_stg stg

2823: stg.to_date,
2824: stg.downtime_reason_code,
2825: stg.status
2826: FROM fnd_lookups flk,
2827: mth_equip_statuses_stg stg
2828: WHERE flk.lookup_type (+) = 'MTH_EQUIP_DOWNTIME_REASON'
2829: AND stg.downtime_reason_code = flk.lookup_code (+)
2830: AND stg.status = 3) dtr
2831: WHERE (stg.downtime_reason_code = dtr.downtime_reason_code

Line 2837: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2833: AND dtr.lookup_code IS NULL
2834: AND dtr.from_date = stg.from_date
2835: AND dtr.To_Date = stg.To_Date)
2836: OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL));
2837: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2838:
2839: -- Validation for IDR
2840: UPDATE mth_equip_statuses_stg stg
2841: SET stg.err_code = stg.err_code || 'IDR '

Line 2840: UPDATE mth_equip_statuses_stg stg

2836: OR (stg.status NOT IN ('3','2') AND stg.downtime_reason_code IS NOT NULL));
2837: mth_util_pkg.log_msg('Number of rows with DTR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2838:
2839: -- Validation for IDR
2840: UPDATE mth_equip_statuses_stg stg
2841: SET stg.err_code = stg.err_code || 'IDR '
2842: WHERE EXISTS (SELECT *
2843: FROM
2844: ( SELECT flk.lookup_code,

Line 2851: mth_equip_statuses_stg stg

2847: stg.to_date,
2848: stg.downtime_reason_code,
2849: stg.status
2850: FROM fnd_lookups flk,
2851: mth_equip_statuses_stg stg
2852: WHERE flk.lookup_type (+) = 'MTH_EQUIP_IDLE_REASON'
2853: AND stg.downtime_reason_code = flk.lookup_code (+)
2854: AND stg.status = 2) dtr
2855: WHERE stg.downtime_reason_code = dtr.downtime_reason_code

Line 2860: mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2856: AND stg.status = dtr.status
2857: AND dtr.lookup_code IS NULL
2858: AND dtr.from_date = stg.from_date
2859: AND dtr.To_Date = stg.To_Date);
2860: mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2861:
2862: -- Validation for ITR
2863: UPDATE mth_equip_statuses_stg stg
2864: SET stg.err_code = stg.err_code || 'ITR '

Line 2863: UPDATE mth_equip_statuses_stg stg

2859: AND dtr.To_Date = stg.To_Date);
2860: mth_util_pkg.log_msg('Number of rows with IDR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2861:
2862: -- Validation for ITR
2863: UPDATE mth_equip_statuses_stg stg
2864: SET stg.err_code = stg.err_code || 'ITR '
2865: WHERE EXISTS ( SELECT * FROM (
2866: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
2867: FROM mth_workday_shifts_d mds,

Line 2868: mth_equip_statuses_stg stg,

2864: SET stg.err_code = stg.err_code || 'ITR '
2865: WHERE EXISTS ( SELECT * FROM (
2866: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
2867: FROM mth_workday_shifts_d mds,
2868: mth_equip_statuses_stg stg,
2869: mth_equipment_shifts_d mes,
2870: mth_equipments_d med
2871: WHERE stg.shift_workday_fk = mds.shift_workday_pk
2872: AND stg.equipment_fk = med.equipment_pk

Line 2882: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2878: WHERE itr.shift_workday_pk = stg.shift_workday_fk
2879: AND itr.equipment_pk = stg.equipment_fk
2880: AND (stg.from_date NOT BETWEEN itr.from_date AND itr.To_Date)
2881: AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date));
2882: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2883:
2884: -- Validation for MSF
2885: UPDATE mth_equip_statuses_stg stg
2886: SET stg.err_code = stg.err_code || 'MSF '

Line 2885: UPDATE mth_equip_statuses_stg stg

2881: AND (stg.to_date NOT BETWEEN itr.from_date AND itr.To_Date));
2882: mth_util_pkg.log_msg('Number of rows with ITR in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2883:
2884: -- Validation for MSF
2885: UPDATE mth_equip_statuses_stg stg
2886: SET stg.err_code = stg.err_code || 'MSF '
2887: WHERE EXISTS ( SELECT * FROM (
2888: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
2889: FROM mth_workday_shifts_d mds,

Line 2890: mth_equip_statuses_stg stg,

2886: SET stg.err_code = stg.err_code || 'MSF '
2887: WHERE EXISTS ( SELECT * FROM (
2888: SELECT mds.shift_workday_pk,med.equipment_pk,mes.from_date,mes.To_Date
2889: FROM mth_workday_shifts_d mds,
2890: mth_equip_statuses_stg stg,
2891: mth_equipment_shifts_d mes,
2892: mth_equipments_d med
2893: WHERE stg.shift_workday_fk = mds.shift_workday_pk
2894: AND stg.equipment_fk = med.equipment_pk

Line 2908: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2904: AND (((stg.from_date BETWEEN msf.from_date AND msf.To_Date)
2905: AND (stg.to_date NOT BETWEEN msf.from_date AND msf.To_Date))
2906: OR ((stg.from_date NOT BETWEEN msf.from_date AND msf.To_Date)
2907: AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date))));
2908: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2909:
2910: --Validation for OCSV
2911: UPDATE mth_equip_statuses_stg stag
2912: SET stag.err_code = stag.err_code || 'OCSV '

Line 2911: UPDATE mth_equip_statuses_stg stag

2907: AND (stg.to_date BETWEEN msf.from_date AND msf.To_Date))));
2908: mth_util_pkg.log_msg('Number of rows with MSF in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2909:
2910: --Validation for OCSV
2911: UPDATE mth_equip_statuses_stg stag
2912: SET stag.err_code = stag.err_code || 'OCSV '
2913: WHERE EXISTS (SELECT *
2914: FROM
2915: (SELECT CASE

Line 2930: FROM mth_equip_statuses_stg stg,

2926: THEN 1 END overlap,
2927: med.equipment_pk,
2928: stg.from_date,
2929: stg.to_date
2930: FROM mth_equip_statuses_stg stg,
2931: mth_equipments_d med
2932: WHERE stg.equipment_fk = med.equipment_pk ) ovp
2933: WHERE ovp.overlap = 1
2934: AND stag.equipment_fk = ovp.equipment_pk

Line 2937: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2933: WHERE ovp.overlap = 1
2934: AND stag.equipment_fk = ovp.equipment_pk
2935: AND stag.from_date = ovp.from_date
2936: AND stag.To_Date = ovp.To_Date);
2937: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2938:
2939: --validation for OSTS
2940: UPDATE mth_equip_statuses_stg stag
2941: SET stag.err_code = stag.err_code || 'OSTS '

Line 2940: UPDATE mth_equip_statuses_stg stag

2936: AND stag.To_Date = ovp.To_Date);
2937: mth_util_pkg.log_msg('Number of rows with OCSV in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2938:
2939: --validation for OSTS
2940: UPDATE mth_equip_statuses_stg stag
2941: SET stag.err_code = stag.err_code || 'OSTS '
2942: WHERE EXISTS (SELECT *
2943: FROM ( SELECT CASE
2944: WHEN (stg.FROM_DATE >= sts.FROM_DATE

Line 2955: FROM mth_equip_statuses_stg stg,

2951: med.equipment_pk,
2952: wds.shift_workday_pk,
2953: stg.from_date,
2954: stg.to_date
2955: FROM mth_equip_statuses_stg stg,
2956: mth_equip_statuses sts,
2957: mth_equipments_d med,
2958: mth_workday_shifts_d wds
2959: WHERE stg.equipment_fk = med.equipment_pk

Line 2967: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2963: WHERE osts.overlap = 1
2964: AND stag.equipment_fk = osts.equipment_pk
2965: AND stag.shift_workday_fk = osts.shift_workday_pk
2966: AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
2967: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2968:
2969: --validation for WRC
2970: UPDATE mth_equip_statuses_stg stg
2971: SET stg.err_code = stg.err_code ||'WRC '

Line 2970: UPDATE mth_equip_statuses_stg stg

2966: AND ((stag.from_date BETWEEN osts.from_date AND osts.To_Date ) OR (stag.To_Date BETWEEN osts.from_date AND osts.To_Date)));
2967: mth_util_pkg.log_msg('Number of rows with OSTS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2968:
2969: --validation for WRC
2970: UPDATE mth_equip_statuses_stg stg
2971: SET stg.err_code = stg.err_code ||'WRC '
2972: WHERE NOT EXISTS ( SELECT *
2973: FROM (SELECT stg.*
2974: FROM mth_equip_statuses_stg stg,

Line 2974: FROM mth_equip_statuses_stg stg,

2970: UPDATE mth_equip_statuses_stg stg
2971: SET stg.err_code = stg.err_code ||'WRC '
2972: WHERE NOT EXISTS ( SELECT *
2973: FROM (SELECT stg.*
2974: FROM mth_equip_statuses_stg stg,
2975: MTH_EQUIPMENT_REASON_SETUP mer,
2976: mth_equipments_d med
2977: WHERE stg.equipment_fk = med.equipment_pk
2978: AND med.equipment_pk_key = mer.equipment_fk_key

Line 2990: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2986: AND ers.shift_workday_fk = stg.shift_workday_fk
2987: )
2988: AND stg.status IN (3,2)
2989: AND stg.downtime_reason_code IS NOT NULL;
2990: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2991:
2992: --validation for STS
2993: UPDATE mth_equip_statuses_stg stg
2994: SET stg.err_code = stg.err_code ||'STS '

Line 2993: UPDATE mth_equip_statuses_stg stg

2989: AND stg.downtime_reason_code IS NOT NULL;
2990: mth_util_pkg.log_msg('Number of rows with WRC in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2991:
2992: --validation for STS
2993: UPDATE mth_equip_statuses_stg stg
2994: SET stg.err_code = stg.err_code ||'STS '
2995: WHERE stg.status NOT IN (1,2,3,4);
2996: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2997:

Line 2996: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

2992: --validation for STS
2993: UPDATE mth_equip_statuses_stg stg
2994: SET stg.err_code = stg.err_code ||'STS '
2995: WHERE stg.status NOT IN (1,2,3,4);
2996: mth_util_pkg.log_msg('Number of rows with STS in MTH_EQUIP_STATUSES_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2997:
2998: /*--Insert records into mth_equip_statuses_err
2999: INSERT INTO mth_equip_statuses_err(equipment_fk,
3000: shift_workday_fk,

Line 3047: FROM mth_equip_statuses_stg

3043: user_measure4,
3044: user_measure5,
3045: err_code,
3046: downtime_reason_code
3047: FROM mth_equip_statuses_stg
3048: WHERE err_code IS NOT NULL);
3049: mth_util_pkg.log_msg('Number of rows inserted in MTH_EQUIP_STATUSES_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);*/
3050:
3051: OPEN c_error_rows;

Line 3137: FROM mth_equip_statuses_stg stg,

3133: null,
3134: null,
3135: mhd.HOUR_PK_KEY,
3136: stg.from_date
3137: FROM mth_equip_statuses_stg stg,
3138: mth_equipments_d med,
3139: mth_workday_shifts_d wds,
3140: mth_systems_setup mss,
3141: mth_user_dim_entities_mst mue1,

Line 3194: FROM mth_equip_statuses_stg stg,

3190: null,
3191: null,
3192: mhd.HOUR_PK_KEY,
3193: stg.from_date
3194: FROM mth_equip_statuses_stg stg,
3195: mth_equipments_d med,
3196: mth_workday_shifts_d wds,
3197: mth_systems_setup mss,
3198: mth_user_dim_entities_mst mue1,

Line 3249: FROM mth_equip_statuses_stg stg,

3245: NULL,
3246: null,
3247: sts.reading_time,
3248: sts.hour_fk_key
3249: FROM mth_equip_statuses_stg stg,
3250: mth_equip_statuses sts
3251: WHERE stg.from_date = sts.reading_time
3252: AND sts.status = stg.status
3253: AND stg.status = 3

Line 3273: FROM mth_equip_statuses_stg stg,

3269: NULL,
3270: null,
3271: sts.reading_time,
3272: sts.hour_fk_key
3273: FROM mth_equip_statuses_stg stg,
3274: mth_equip_statuses sts
3275: WHERE stg.from_date = sts.reading_time
3276: AND sts.status = stg.status
3277: AND stg.status = 2

Line 3641: FROM mth_equip_statuses_stg stg,

3637: IS
3638: SELECT Min(from_time)
3639: FROM mth_hour_d
3640: WHERE (SELECT Min(from_date) reading_time
3641: FROM mth_equip_statuses_stg stg,
3642: mth_equipments_d eq
3643: WHERE stg.equipment_fk = eq.equipment_pk
3644: AND eq.equipment_pk_key = nvl(p_recalc_equip_key,eq.equipment_pk_key)
3645: AND eq.equipment_pk_key IN (SELECT equipment_pk_key

Line 3665: FROM mth_equip_statuses_stg stg,

3661: IS
3662: SELECT Max(to_time)
3663: FROM mth_hour_d
3664: WHERE (SELECT Max(to_date) reading_time
3665: FROM mth_equip_statuses_stg stg,
3666: mth_equipments_d eq
3667: WHERE stg.equipment_fk = eq.equipment_pk
3668: AND eq.equipment_pk_key = nvl(p_recalc_equip_key,eq.equipment_pk_key)
3669: AND eq.equipment_pk_key IN (SELECT equipment_pk_key

Line 3813: DELETE FROM MTH_EQUIP_STATUSES_STG;

3809: -- Call mth_run_log_post_load
3810: mth_util_pkg.mth_run_log_post_load('MTH_EQUIP_STATUS_SUMMARY',v_ua_val);
3811:
3812:
3813: DELETE FROM MTH_EQUIP_STATUSES_STG;
3814:
3815:
3816: mth_util_pkg.log_msg('PROCESS_STATUS_SMMRY_RECAL end', mth_util_pkg.G_DBG_PROC_FUN_END);
3817: