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_LOAD_TAG_READINGS_PKG dependencies on MTH_TAG_READINGS

Line 101: MTH_TAG_READINGS_RAW_ERR

97: v_ret_code NUMBER;
98: BEGIN
99: INSERT
100: INTO
101: MTH_TAG_READINGS_RAW_ERR
102: (GROUP_ID,
103: READING_TIME,
104: TAG_CODE,
105: TAG_DATA,

Line 137: FROM MTH_TAG_READINGS_RAW_PROCESSED RP

133: MAX(RP.QUALITY_FLAG) QUALITY_FLAG,
134: MAX(RP.CREATION_DATE) CREATION_DATE ,
135: -- COUNT(RP.TAG_CODE) DUP_COUNT,
136: 'DUP' ERROR_CODE
137: FROM MTH_TAG_READINGS_RAW_PROCESSED RP
138: GROUP BY RP.READING_TIME , RP.TAG_CODE
139: HAVING COUNT(RP.TAG_CODE) > 1
140: ) ;
141: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_RAW_ERR while proceesing the data from tag readings raw processed table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 141: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_RAW_ERR while proceesing the data from tag readings raw processed table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

137: FROM MTH_TAG_READINGS_RAW_PROCESSED RP
138: GROUP BY RP.READING_TIME , RP.TAG_CODE
139: HAVING COUNT(RP.TAG_CODE) > 1
140: ) ;
141: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_RAW_ERR while proceesing the data from tag readings raw processed table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
142:
143: ---Inserting records missing equipment or plant or incorrect status information or missing mth entity,attribute group and attribute information in tag readings staging error table
144:
145: INSERT

Line 147: MTH_TAG_READINGS_STG_ERR

143: ---Inserting records missing equipment or plant or incorrect status information or missing mth entity,attribute group and attribute information in tag readings staging error table
144:
145: INSERT
146: INTO
147: MTH_TAG_READINGS_STG_ERR
148: (GROUP_ID,
149: READING_TIME,
150: TAG_CODE,
151: TAG_DATA,

Line 204: MTH_TAG_READINGS_RAW_PROCESSED RP

200: MAX(RP.CREATION_DATE) CREATION_DATE,
201: COUNT(RP.TAG_CODE) DUP_COUNT
202: FROM
203:
204: MTH_TAG_READINGS_RAW_PROCESSED RP
205: GROUP BY
206: RP.READING_TIME , RP.TAG_CODE ) RAW_DATA ,MTH_TAG_MASTER TM ,MTH_TAG_DESTINATION_MAP TDM ,MTH_EQUIPMENTS_D EQP
207: WHERE
208: RAW_DATA.TAG_CODE = TM.TAG_CODE

Line 229: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_STG_ERR while proceesing the data from tag readings raw processed table- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

225: AND TDM.ATTRIBUTE IS NULL )
226: )
227: )
228: ;
229: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_STG_ERR while proceesing the data from tag readings raw processed table- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
230:
231: -- Inserting records in tag readings staging table
232: INSERT
233: INTO

Line 234: MTH_TAG_READINGS_STG

230:
231: -- Inserting records in tag readings staging table
232: INSERT
233: INTO
234: MTH_TAG_READINGS_STG
235: (GROUP_ID,
236: READING_TIME,
237: TAG_CODE,
238: TAG_DATA,

Line 298: MTH_TAG_READINGS_RAW_PROCESSED RP

294: MAX(RP.QUALITY_FLAG) QUALITY_FLAG,
295: MAX(RP.CREATION_DATE) CREATION_DATE,
296: COUNT(RP.TAG_CODE) DUP_COUNT
297: FROM
298: MTH_TAG_READINGS_RAW_PROCESSED RP
299: --WHERE (r.CREATION_DATE >= v_log_from_date and r.CREATION_DATE < v_log_to_date )
300: GROUP BY
301: RP.READING_TIME , RP.TAG_CODE ) RAW_DATA ,MTH_TAG_MASTER TM ,MTH_TAG_DESTINATION_MAP TDM ,MTH_EQUIPMENTS_D EQP
302: WHERE

Line 322: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_STG while proceesing the data from tag readings raw processed table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

318: TDM.ATTRIBUTE IS NULL)
319: )
320:
321: ;
322: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_STG while proceesing the data from tag readings raw processed table - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
323: v_ret_code:=0;
324: RETURN v_ret_code;
325: EXCEPTION
326: WHEN OTHERS THEN

Line 350: MTH_TAG_READINGS

346:
347: -- Inserting records in tag readings table for manual and tag based contextualization which has all the required Context
348: INSERT
349: INTO
350: MTH_TAG_READINGS
351: (GROUP_ID,
352: READING_TIME,
353: TAG_CODE,
354: TAG_DATA,

Line 417: MTH_TAG_READINGS_ERR TRE, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_CONTEXTUALIZATION_REQ MCR

413: TRE.QUALITY_FLAG,
414: TRE.RECIPE_NUM,
415: TRE.RECIPE_VERSION
416: FROM
417: MTH_TAG_READINGS_ERR TRE, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_CONTEXTUALIZATION_REQ MCR
418: WHERE
419: TRE.RE_PROCESS_FLAG = 'Y' And
420: TRE.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+) And
421: TRE.EQUIPMENT_FK_KEY = MCR.EQUIPMENT_FK_KEY (+)

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

430: ( MCR.ITEM_REQUIRED <> 'Y' Or
431: TRE.ITEM_FK_KEY IS NOT NULL And TRE.ITEM_FK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL() )
432: )
433: ;
434: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS from tag readings error table- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
435:
436: -- Inserting scheduled based contextualization records in tag readings staging table for the equipment whose staus is active
437: INSERT
438: INTO

Line 439: MTH_TAG_READINGS_STG

435:
436: -- Inserting scheduled based contextualization records in tag readings staging table for the equipment whose staus is active
437: INSERT
438: INTO
439: MTH_TAG_READINGS_STG
440: (GROUP_ID,
441: READING_TIME,
442: TAG_CODE,
443: TAG_DATA,

Line 486: MTH_TAG_READINGS_ERR TRE, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_CONTEXTUALIZATION_REQ MCR

482: TRE.QUALITY_FLAG,
483: TRE.PROCESSED_FLAG,
484: SYSDATE - 0.0001
485: FROM
486: MTH_TAG_READINGS_ERR TRE, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_CONTEXTUALIZATION_REQ MCR
487: WHERE
488: TRE.RE_PROCESS_FLAG = 'Y'
489: AND TRE.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+)
490: AND TRE.EQUIPMENT_FK_KEY = MCR.EQUIPMENT_FK_KEY (+)

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

491: AND MCM.CONTEXTUALIZE_METHOD = 2
492: AND TRE.EQUIPMENT_STATUS = 'ACTIVE'
493: )
494: ;
495: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_STG from tag readings error table- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
496:
497: -- Inserting Remaining records in tag readings error table
498: INSERT
499: INTO

Line 500: MTH_TAG_READINGS_ERR

496:
497: -- Inserting Remaining records in tag readings error table
498: INSERT
499: INTO
500: MTH_TAG_READINGS_ERR
501: (GROUP_ID,
502: READING_TIME,
503: TAG_CODE,
504: TAG_DATA,

Line 579: MTH_TAG_READINGS_ERR TRE, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_CONTEXTUALIZATION_REQ MCR

575: TRE.RECIPE_VERSION,
576: TRE.RECIPE_NUM,
577: TRE.EQUIPMENT_STATUS
578: FROM
579: MTH_TAG_READINGS_ERR TRE, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_CONTEXTUALIZATION_REQ MCR
580: WHERE
581: TRE.RE_PROCESS_FLAG = 'Y' And
582: TRE.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+) And
583: TRE.EQUIPMENT_FK_KEY = MCR.EQUIPMENT_FK_KEY (+) AND

Line 597: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR from tag readings error table which have re_process_flag as Y- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

593: (MCR.ITEM_REQUIRED <> 'Y' Or
594: TRE.ITEM_FK_KEY IS NOT NULL And TRE.ITEM_FK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL())))
595: )
596: ;
597: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR from tag readings error table which have re_process_flag as Y- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
598:
599: -- Deleting from tag readings error table all the records where re_process_flag is Y
600: DELETE
601: FROM

Line 602: MTH_TAG_READINGS_ERR

598:
599: -- Deleting from tag readings error table all the records where re_process_flag is Y
600: DELETE
601: FROM
602: MTH_TAG_READINGS_ERR
603: WHERE RE_PROCESS_FLAG = 'Y' ;
604:
605: mth_util_pkg.log_msg('Number of rows deleted from MTH_TAG_READINGS_ERR which have re_process_flag as Y- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
606: v_ret_code:=0;

Line 605: mth_util_pkg.log_msg('Number of rows deleted from MTH_TAG_READINGS_ERR which have re_process_flag as Y- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

601: FROM
602: MTH_TAG_READINGS_ERR
603: WHERE RE_PROCESS_FLAG = 'Y' ;
604:
605: mth_util_pkg.log_msg('Number of rows deleted from MTH_TAG_READINGS_ERR which have re_process_flag as Y- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
606: v_ret_code:=0;
607: RETURN v_ret_code;
608: EXCEPTION
609: WHEN OTHERS THEN

Line 631: MTH_TAG_READINGS_ERR

627: v_ret_code NUMBER;
628: BEGIN
629: INSERT
630: INTO
631: MTH_TAG_READINGS_ERR
632: (GROUP_ID,
633: READING_TIME,
634: TAG_CODE,
635: TAG_DATA,

Line 678: MTH_TAG_READINGS_STG STG , MTH_CONTEXTUALIZATION_METHOD CM

674: STG.USER_MEASURE5,
675: STG.QUALITY_FLAG,
676: NVL( STG.PROCESSED_FLAG , 0)
677: FROM
678: MTH_TAG_READINGS_STG STG , MTH_CONTEXTUALIZATION_METHOD CM
679: WHERE
680: STG.EQUIPMENT_FK_KEY = CM.EQUIPMENT_FK_KEY (+)
681: AND CM.CONTEXTUALIZE_METHOD NOT IN ( 1 , 2 , 3 )
682: )

Line 684: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR which has contextualization method other than 1,2 or 3 - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

680: STG.EQUIPMENT_FK_KEY = CM.EQUIPMENT_FK_KEY (+)
681: AND CM.CONTEXTUALIZE_METHOD NOT IN ( 1 , 2 , 3 )
682: )
683: ;
684: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR which has contextualization method other than 1,2 or 3 - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
685:
686: -- Inserting records in tag readings error table for tag based contextualization missing/incorrect required context information
687: INSERT
688: INTO

Line 689: MTH_TAG_READINGS_ERR

685:
686: -- Inserting records in tag readings error table for tag based contextualization missing/incorrect required context information
687: INSERT
688: INTO
689: MTH_TAG_READINGS_ERR
690: (GROUP_ID,
691: READING_TIME,
692: TAG_CODE,
693: TAG_DATA,

Line 841: MTH_TAG_READINGS_STG STG, MTH_CONTEXTUALIZATION_METHOD MCM

837: STG.USER_MEASURE4,
838: STG.USER_MEASURE5,
839: STG.QUALITY_FLAG
840: FROM
841: MTH_TAG_READINGS_STG STG, MTH_CONTEXTUALIZATION_METHOD MCM
842: WHERE
843: STG.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+)
844: AND MCM.CONTEXTUALIZE_METHOD = 1 AND
845: ( STG.MTH_ENTITY IS NULL Or STG.MTH_ENTITY IS NOT NULL And NOT STG.MTH_ENTITY IN ( 2 , 3 , 4 , 14 , 15 ) )

Line 871: MTH_TAG_READINGS_STG STG ,MTH_CONTEXTUALIZATION_METHOD MCM

867: STG.ATTRIBUTE_GROUP,
868: STG.ATTRIBUTE,
869: STG.EQUIPMENT_FK_KEY
870: FROM
871: MTH_TAG_READINGS_STG STG ,MTH_CONTEXTUALIZATION_METHOD MCM
872: WHERE
873: STG.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+) AND
874: MCM.CONTEXTUALIZE_METHOD = 1 AND
875: STG.MTH_ENTITY IS NOT NULL AND

Line 925: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR for tag based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

921: TO_CHAR(ITEMS.ITEM_PK_KEY) = TO_CHAR(MTH_UTIL_PKG.MTH_UA_GET_VAL()))
922: )
923:
924: );
925: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR for tag based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
926:
927: ---
928:
929: INSERT

Line 931: MTH_TAG_READINGS

927: ---
928:
929: INSERT
930: INTO
931: MTH_TAG_READINGS
932: (GROUP_ID,
933: READING_TIME,
934: TAG_CODE,
935: TAG_DATA,

Line 1071: MTH_TAG_READINGS_STG STG, MTH_CONTEXTUALIZATION_METHOD MCM

1067: STG.USER_MEASURE4,
1068: STG.USER_MEASURE5,
1069: STG.QUALITY_FLAG
1070: FROM
1071: MTH_TAG_READINGS_STG STG, MTH_CONTEXTUALIZATION_METHOD MCM
1072: WHERE
1073: STG.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+)
1074: AND MCM.CONTEXTUALIZE_METHOD = 1 AND
1075: ( STG.MTH_ENTITY IS NULL Or STG.MTH_ENTITY IS NOT NULL And NOT STG.MTH_ENTITY IN ( 2 , 3 , 4 , 14 , 15 ) )

Line 1101: MTH_TAG_READINGS_STG STG ,MTH_CONTEXTUALIZATION_METHOD MCM

1097: STG.ATTRIBUTE_GROUP,
1098: STG.ATTRIBUTE,
1099: STG.EQUIPMENT_FK_KEY
1100: FROM
1101: MTH_TAG_READINGS_STG STG ,MTH_CONTEXTUALIZATION_METHOD MCM
1102: WHERE
1103: STG.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+) AND
1104: MCM.CONTEXTUALIZE_METHOD = 1 AND
1105: STG.MTH_ENTITY IS NOT NULL AND

Line 1149: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS for tag based contextualziation-- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1145: TO_CHAR(ITEMS.ITEM_PK_KEY) = TO_CHAR(MTH_UTIL_PKG.MTH_UA_GET_VAL()))
1146: )
1147: )
1148: ;
1149: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS for tag based contextualziation-- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1150: v_ret_code:=0;
1151: RETURN v_ret_code;
1152: EXCEPTION
1153: WHEN OTHERS THEN

Line 1175: MTH_TAG_READINGS_ERR (GROUP_ID,

1171: IS
1172: v_ret_code NUMBER;
1173: BEGIN
1174: INSERT INTO
1175: MTH_TAG_READINGS_ERR (GROUP_ID,
1176: READING_TIME,
1177: TAG_CODE,
1178: TAG_DATA,
1179: MTH_ENTITY,

Line 1231: FROM MTH_TAG_READINGS_STG STG, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_EQUIP_PROD_SCHEDULE_F EQUIP_PROD_SCHEDULE,

1227: STG.USER_MEASURE5,
1228: STG.QUALITY_FLAG,
1229: NVL(STG.PROCESSED_FLAG , 0),
1230: EQUIPMENTS.STATUS
1231: FROM MTH_TAG_READINGS_STG STG, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_EQUIP_PROD_SCHEDULE_F EQUIP_PROD_SCHEDULE,
1232: MTH_EQUIPMENTS_D EQUIPMENTS, MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS,MTH_HOUR_D HOURS ,MTH_PRODUCTION_SCHEDULES_F PROD_SCHD ,
1233: MTH_CONTEXTUALIZATION_REQ MCR
1234: WHERE MCM.CONTEXTUALIZE_METHOD = 2
1235: AND STG.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+)

Line 1271: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR for schedule based contextualziation-- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1267: AND EQUIPMENTS.STATUS IS NOT NULL
1268: AND EQUIPMENTS.STATUS = 'ACTIVE')))
1269: )
1270: ;
1271: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR for schedule based contextualziation-- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1272:
1273:
1274: ---
1275: INSERT

Line 1276: INTO MTH_TAG_READINGS

1272:
1273:
1274: ---
1275: INSERT
1276: INTO MTH_TAG_READINGS
1277: (GROUP_ID,
1278: READING_TIME,
1279: TAG_CODE,
1280: TAG_DATA,

Line 1342: MTH_TAG_READINGS_STG STG, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_EQUIP_PROD_SCHEDULE_F EQUIP_PROD_SCHEDULE,

1338: STG.QUALITY_FLAG,
1339: PROD_SCHD.RECIPE_NUM,
1340: PROD_SCHD.RECIPE_VERSION
1341: FROM
1342: MTH_TAG_READINGS_STG STG, MTH_CONTEXTUALIZATION_METHOD MCM ,MTH_EQUIP_PROD_SCHEDULE_F EQUIP_PROD_SCHEDULE,
1343: MTH_EQUIPMENTS_D EQUIPMENTS, MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS,MTH_HOUR_D HOURS ,MTH_PRODUCTION_SCHEDULES_F PROD_SCHD ,
1344: MTH_CONTEXTUALIZATION_REQ MCR
1345: WHERE MCM.CONTEXTUALIZE_METHOD = 2
1346: AND STG.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+)

Line 1373: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS for schedule based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1369: AND EQUIPMENTS.STATUS IS NOT NULL
1370: AND EQUIPMENTS.STATUS = 'ACTIVE'
1371: )
1372: ;
1373: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS for schedule based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1374: v_ret_code:=0;
1375: RETURN v_ret_code;
1376: EXCEPTION
1377: WHEN OTHERS THEN

Line 1400: INTO MTH_TAG_READINGS

1396: IS
1397: v_ret_code NUMBER;
1398: BEGIN
1399: INSERT
1400: INTO MTH_TAG_READINGS
1401: (GROUP_ID,
1402: READING_TIME,
1403: TAG_CODE,
1404: TAG_DATA,

Line 1462: MTH_TAG_READINGS_STG STG ,MTH_CONTEXTUALIZATION_METHOD MCM , MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS ,MTH_CONTEXTUALIZATION_REQ CONTEXT_REQ,

1458: STG.USER_MEASURE4,
1459: STG.USER_MEASURE5,
1460: STG.QUALITY_FLAG
1461: FROM
1462: MTH_TAG_READINGS_STG STG ,MTH_CONTEXTUALIZATION_METHOD MCM , MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS ,MTH_CONTEXTUALIZATION_REQ CONTEXT_REQ,
1463: MTH_HOUR_D HOURS ,MTH_EQUIPMENTS_D EQUIPMENTS
1464: WHERE
1465: STG.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+)
1466: AND MCM.CONTEXTUALIZE_METHOD = 3

Line 1486: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS for manual based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1482: OR EQUIPMENTS.STATUS IS NULL
1483: OR EQUIPMENTS.STATUS <> 'ACTIVE')))
1484: )
1485: ;
1486: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS for manual based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1487:
1488: -----
1489: INSERT
1490: INTO MTH_TAG_READINGS_ERR

Line 1490: INTO MTH_TAG_READINGS_ERR

1486: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS for manual based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1487:
1488: -----
1489: INSERT
1490: INTO MTH_TAG_READINGS_ERR
1491: (GROUP_ID,
1492: READING_TIME,
1493: TAG_CODE,
1494: TAG_DATA,

Line 1542: MTH_TAG_READINGS_STG STG ,MTH_CONTEXTUALIZATION_METHOD MCM , MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS ,MTH_CONTEXTUALIZATION_REQ CONTEXT_REQ,

1538: STG.QUALITY_FLAG,
1539: NVL(STG.PROCESSED_FLAG , 0),
1540: EQUIPMENTS.STATUS
1541: FROM
1542: MTH_TAG_READINGS_STG STG ,MTH_CONTEXTUALIZATION_METHOD MCM , MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS ,MTH_CONTEXTUALIZATION_REQ CONTEXT_REQ,
1543: MTH_HOUR_D HOURS ,MTH_EQUIPMENTS_D EQUIPMENTS
1544: WHERE
1545: STG.EQUIPMENT_FK_KEY = MCM.EQUIPMENT_FK_KEY (+)
1546: AND MCM.CONTEXTUALIZE_METHOD = 3

Line 1566: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR for manual based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

1562: OR EQUIPMENTS.STATUS IS NULL
1563: OR EQUIPMENTS.STATUS <> 'ACTIVE')
1564: )
1565: ;
1566: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_ERR for manual based contextualziation- ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
1567: v_ret_code:=0;
1568: RETURN v_ret_code;
1569: EXCEPTION
1570: WHEN OTHERS THEN

Line 1582: * Description :Load data from the table MTH_TAG_READINGS_RAW *

1578: END MTH_MANUAL_BASED_CONTEXTUALIZE;
1579:
1580: /* ****************************************************************************
1581: * Procedure : mth_recal_tag_raw_to_processed *
1582: * Description :Load data from the table MTH_TAG_READINGS_RAW *
1583: * into meter readings table MTH_TAG_READINGS_RAW_PROCESSED *
1584: * File Name :MTHTAGB.PLS *
1585: * Visibility :Private *
1586: * Parameters :p_curr_partition (value of the partition column *

Line 1583: * into meter readings table MTH_TAG_READINGS_RAW_PROCESSED *

1579:
1580: /* ****************************************************************************
1581: * Procedure : mth_recal_tag_raw_to_processed *
1582: * Description :Load data from the table MTH_TAG_READINGS_RAW *
1583: * into meter readings table MTH_TAG_READINGS_RAW_PROCESSED *
1584: * File Name :MTHTAGB.PLS *
1585: * Visibility :Private *
1586: * Parameters :p_curr_partition (value of the partition column *
1587: * :p_from_tz (value for from time zone ) *

Line 1595: -- Insert the data into the mth_tag_readings_processed table

1591: PROCEDURE mth_recal_tag_raw_to_processed(p_curr_partition IN NUMBER, p_from_tz IN
1592: VARCHAR2, p_to_tz IN VARCHAR2,p_ret_code OUT NOCOPY NUMBER)
1593: IS
1594: BEGIN
1595: -- Insert the data into the mth_tag_readings_processed table
1596: INSERT INTO MTH_TAG_READINGS_RAW_PROCESSED
1597: ( GROUP_ID,
1598: READING_TIME ,
1599: TAG_CODE,

Line 1596: INSERT INTO MTH_TAG_READINGS_RAW_PROCESSED

1592: VARCHAR2, p_to_tz IN VARCHAR2,p_ret_code OUT NOCOPY NUMBER)
1593: IS
1594: BEGIN
1595: -- Insert the data into the mth_tag_readings_processed table
1596: INSERT INTO MTH_TAG_READINGS_RAW_PROCESSED
1597: ( GROUP_ID,
1598: READING_TIME ,
1599: TAG_CODE,
1600: TAG_DATA,

Line 1629: FROM MTH_TAG_READINGS_RAW r);

1625: r.USER_MEASURE3,
1626: r.USER_MEASURE4,
1627: r.USER_MEASURE5,
1628: r.QUALITY_FLAG
1629: FROM MTH_TAG_READINGS_RAW r);
1630:
1631: -- Update/Create entry in MTH_TAG_READINGS_LATEST
1632:
1633: UPDATE MTH_TAG_READINGS_LATEST latest

Line 1631: -- Update/Create entry in MTH_TAG_READINGS_LATEST

1627: r.USER_MEASURE5,
1628: r.QUALITY_FLAG
1629: FROM MTH_TAG_READINGS_RAW r);
1630:
1631: -- Update/Create entry in MTH_TAG_READINGS_LATEST
1632:
1633: UPDATE MTH_TAG_READINGS_LATEST latest
1634: SET (latest.reading_time,latest.tag_value)= ( SELECT a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,
1635: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r

Line 1633: UPDATE MTH_TAG_READINGS_LATEST latest

1629: FROM MTH_TAG_READINGS_RAW r);
1630:
1631: -- Update/Create entry in MTH_TAG_READINGS_LATEST
1632:
1633: UPDATE MTH_TAG_READINGS_LATEST latest
1634: SET (latest.reading_time,latest.tag_value)= ( SELECT a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,
1635: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r
1636: GROUP BY r.tag_code)a ,mth_tag_readings_latest l
1637: WHERE rraw.reading_time=a.reading_time

Line 1634: SET (latest.reading_time,latest.tag_value)= ( SELECT a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,

1630:
1631: -- Update/Create entry in MTH_TAG_READINGS_LATEST
1632:
1633: UPDATE MTH_TAG_READINGS_LATEST latest
1634: SET (latest.reading_time,latest.tag_value)= ( SELECT a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,
1635: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r
1636: GROUP BY r.tag_code)a ,mth_tag_readings_latest l
1637: WHERE rraw.reading_time=a.reading_time
1638: AND rraw.tag_code=a.tag_code

Line 1635: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r

1631: -- Update/Create entry in MTH_TAG_READINGS_LATEST
1632:
1633: UPDATE MTH_TAG_READINGS_LATEST latest
1634: SET (latest.reading_time,latest.tag_value)= ( SELECT a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,
1635: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r
1636: GROUP BY r.tag_code)a ,mth_tag_readings_latest l
1637: WHERE rraw.reading_time=a.reading_time
1638: AND rraw.tag_code=a.tag_code
1639: AND a.tag_code IN l.tag_code

Line 1636: GROUP BY r.tag_code)a ,mth_tag_readings_latest l

1632:
1633: UPDATE MTH_TAG_READINGS_LATEST latest
1634: SET (latest.reading_time,latest.tag_value)= ( SELECT a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,
1635: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r
1636: GROUP BY r.tag_code)a ,mth_tag_readings_latest l
1637: WHERE rraw.reading_time=a.reading_time
1638: AND rraw.tag_code=a.tag_code
1639: AND a.tag_code IN l.tag_code
1640: AND l.reading_time

Line 1647: INSERT INTO MTH_TAG_READINGS_LATEST

1643:
1644:
1645:
1646:
1647: INSERT INTO MTH_TAG_READINGS_LATEST
1648: ( TAG_CODE,
1649: READING_TIME,
1650: TAG_VALUE)
1651: ( SELECT a.tag_code,a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,

Line 1651: ( SELECT a.tag_code,a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,

1647: INSERT INTO MTH_TAG_READINGS_LATEST
1648: ( TAG_CODE,
1649: READING_TIME,
1650: TAG_VALUE)
1651: ( SELECT a.tag_code,a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,
1652: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r
1653: GROUP BY r.tag_code)a
1654: WHERE rraw.reading_time=a.reading_time
1655: AND rraw.tag_code=a.tag_code

Line 1652: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r

1648: ( TAG_CODE,
1649: READING_TIME,
1650: TAG_VALUE)
1651: ( SELECT a.tag_code,a.reading_time,rraw.tag_data FROM mth_tag_readings_raw rraw ,
1652: ( SELECT r.tag_code,Max(r.reading_time) reading_time FROM mth_tag_readings_raw r
1653: GROUP BY r.tag_code)a
1654: WHERE rraw.reading_time=a.reading_time
1655: AND rraw.tag_code=a.tag_code
1656: AND NOT EXISTS (SELECT tag_code

Line 1657: FROM mth_tag_readings_latest l

1653: GROUP BY r.tag_code)a
1654: WHERE rraw.reading_time=a.reading_time
1655: AND rraw.tag_code=a.tag_code
1656: AND NOT EXISTS (SELECT tag_code
1657: FROM mth_tag_readings_latest l
1658: WHERE rraw.tag_code = l.tag_code)
1659:
1660: ) ;
1661: p_ret_code := 0;

Line 1677: and handles equpment down events from data in MTH_TAG_READINGS_STG table *

1673: END mth_recal_tag_raw_to_processed;
1674: /* ****************************************************************************
1675: * Procedure : MTH_GEN_HDL_EQP_DOWN_EVENT *
1676: * Description : The new procedure MTH_GEN_HDL_EQP_DOWN_EVENT generates *
1677: and handles equpment down events from data in MTH_TAG_READINGS_STG table *
1678: * File Name :MTHTAGB.PLS *
1679: * Visibility :Private *
1680: * Parameters : *
1681: **************************************************************************** */

Line 1691: from mth_tag_readings_stg

1687: (select MTH_ENTITY, tag_data, equipment_fk_key, reading_time,
1688: tag_code, lag( tag_data)
1689: over (partition by equipment_fk_key, tag_code
1690: order by reading_time ) prev_status
1691: from mth_tag_readings_stg
1692: --where MTH_ENTITY = 'Status'
1693: ) READINGS
1694: where
1695: READINGS.MTH_ENTITY = 5 And

Line 1726: and handles equpment out of control events from MTH_TAG_READINGS_STG table *

1722:
1723: /* ****************************************************************************
1724: * Procedure : MTH_HDL_EQP_OUT_OF_CTL_EVT *
1725: * Description : The new procedure MTH_HDL_EQP_OUT_OF_CTL_EVT generates *
1726: and handles equpment out of control events from MTH_TAG_READINGS_STG table *
1727: * File Name :MTHTAGB.PLS *
1728: * Visibility :Private *
1729: * Parameters : *
1730: **************************************************************************** */

Line 1733: v_previous_tag_data MTH_TAG_READINGS.TAG_DATA%TYPE NULL;

1729: * Parameters : *
1730: **************************************************************************** */
1731:
1732: PROCEDURE MTH_HDL_EQP_OUT_OF_CTL_EVT IS
1733: v_previous_tag_data MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1734: v_previous_tag_code MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1735: v_pre_reading_time MTH_TAG_READINGS.READING_TIME%TYPE NULL;
1736: v_pre_eqp_avail VARCHAR2(30);
1737: v_upper_limit NUMBER NULL;

Line 1734: v_previous_tag_code MTH_TAG_READINGS.TAG_CODE%TYPE NULL;

1730: **************************************************************************** */
1731:
1732: PROCEDURE MTH_HDL_EQP_OUT_OF_CTL_EVT IS
1733: v_previous_tag_data MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1734: v_previous_tag_code MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1735: v_pre_reading_time MTH_TAG_READINGS.READING_TIME%TYPE NULL;
1736: v_pre_eqp_avail VARCHAR2(30);
1737: v_upper_limit NUMBER NULL;
1738: v_lower_limit NUMBER NULL;

Line 1735: v_pre_reading_time MTH_TAG_READINGS.READING_TIME%TYPE NULL;

1731:
1732: PROCEDURE MTH_HDL_EQP_OUT_OF_CTL_EVT IS
1733: v_previous_tag_data MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1734: v_previous_tag_code MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1735: v_pre_reading_time MTH_TAG_READINGS.READING_TIME%TYPE NULL;
1736: v_pre_eqp_avail VARCHAR2(30);
1737: v_upper_limit NUMBER NULL;
1738: v_lower_limit NUMBER NULL;
1739: cursor c_readings is SELECT

Line 1768: JOIN MTH_TAG_READINGS_STG stg ON ( (( eagrps.ATTR_GROUP_ID = stg.ATTRIBUTE_GROUP ))

1764: EGO_ATTR_GROUPS_V eagrps
1765: JOIN EGO_ATTRS_V eattrs ON ( (( eagrps.ATTR_GROUP_TYPE = eattrs.ATTR_GROUP_TYPE ))
1766: AND (( eagrps.APPLICATION_ID = eattrs.APPLICATION_ID ))
1767: AND (( eattrs.ATTR_GROUP_NAME = eagrps.ATTR_GROUP_NAME )) )
1768: JOIN MTH_TAG_READINGS_STG stg ON ( (( eagrps.ATTR_GROUP_ID = stg.ATTRIBUTE_GROUP ))
1769: AND (( stg.ATTRIBUTE = eattrs.ATTR_ID )) )
1770: JOIN MTH_TAG_MASTER tag_mstr ON ( --(( stg.EQUIPMENT_FK_KEY = tag_mstr.EQUIPMENT_FK_KEY ))
1771: --AND
1772: (( stg.TAG_CODE = tag_mstr.TAG_CODE )) )

Line 1817: generates and handles equpment Control Limit warning events from MTH_TAG_READINGS_STG table*

1813: END ;
1814: /* ****************************************************************************
1815: * Procedure : MTH_GEN_HDL_CTL_LMT_WRNG_EVT *
1816: * Description :The new procedure MTH_GEN_HDL_CTL_LMT_WRNG_EVT *
1817: generates and handles equpment Control Limit warning events from MTH_TAG_READINGS_STG table*
1818: * File Name :MTHTAGB.PLS *
1819: * Visibility :Private *
1820: * Parameters : *
1821: **************************************************************************** */

Line 1855: JOIN MTH_TAG_READINGS_STG stg ON ( (( eagrps.ATTR_GROUP_ID = stg.ATTRIBUTE_GROUP ))

1851: EGO_ATTR_GROUPS_V eagrps
1852: JOIN EGO_ATTRS_V eattrs ON ( (( eagrps.ATTR_GROUP_TYPE = eattrs.ATTR_GROUP_TYPE ))
1853: AND (( eagrps.APPLICATION_ID = eattrs.APPLICATION_ID ))
1854: AND (( eattrs.ATTR_GROUP_NAME = eagrps.ATTR_GROUP_NAME )) )
1855: JOIN MTH_TAG_READINGS_STG stg ON ( (( eagrps.ATTR_GROUP_ID = stg.ATTRIBUTE_GROUP ))
1856: AND (( stg.ATTRIBUTE = eattrs.ATTR_ID )) )
1857: JOIN MTH_TAG_MASTER tag_mstr ON (-- (( stg.EQUIPMENT_FK_KEY = tag_mstr.EQUIPMENT_FK_KEY ))
1858: -- AND
1859: (( stg.TAG_CODE = tag_mstr.TAG_CODE )) )

Line 1889: generates and handles Equipment unstable events from MTH_TAG_READINGS_STG table*

1885:
1886: /* ****************************************************************************
1887: * Procedure : MTH_HDL_EQP_UNSTAB_EVT *
1888: * Description :The new procedure MTH_HDL_EQP_UNSTAB_EVT *
1889: generates and handles Equipment unstable events from MTH_TAG_READINGS_STG table*
1890: * File Name :MTHTAGB.PLS *
1891: * Visibility :Private *
1892: * Parameters : *
1893: **************************************************************************** */

Line 1896: v_previous_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;

1892: * Parameters : *
1893: **************************************************************************** */
1894:
1895: PROCEDURE MTH_HDL_EQP_UNSTAB_EVT IS
1896: v_previous_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1897: v_previous_tag_code1 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1898: v_previous_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1899: v_previous_tag_code2 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1900: p_pre_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;

Line 1897: v_previous_tag_code1 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;

1893: **************************************************************************** */
1894:
1895: PROCEDURE MTH_HDL_EQP_UNSTAB_EVT IS
1896: v_previous_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1897: v_previous_tag_code1 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1898: v_previous_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1899: v_previous_tag_code2 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1900: p_pre_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1901: p_pre_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;

Line 1898: v_previous_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;

1894:
1895: PROCEDURE MTH_HDL_EQP_UNSTAB_EVT IS
1896: v_previous_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1897: v_previous_tag_code1 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1898: v_previous_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1899: v_previous_tag_code2 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1900: p_pre_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1901: p_pre_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1902:

Line 1899: v_previous_tag_code2 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;

1895: PROCEDURE MTH_HDL_EQP_UNSTAB_EVT IS
1896: v_previous_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1897: v_previous_tag_code1 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1898: v_previous_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1899: v_previous_tag_code2 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1900: p_pre_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1901: p_pre_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1902:
1903:

Line 1900: p_pre_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;

1896: v_previous_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1897: v_previous_tag_code1 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1898: v_previous_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1899: v_previous_tag_code2 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1900: p_pre_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1901: p_pre_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1902:
1903:
1904: cursor c_readings is select INGRP1.GROUP_ID , INGRP1.ATTRIBUTE_GROUP ATTRIBUTE_GROUP1, INGRP1.ATTRIBUTE ATTRIBUTE1, INGRP1.TAG_CODE TAG_CODE1,

Line 1901: p_pre_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;

1897: v_previous_tag_code1 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1898: v_previous_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1899: v_previous_tag_code2 MTH_TAG_READINGS.TAG_CODE%TYPE NULL;
1900: p_pre_tag_data1 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1901: p_pre_tag_data2 MTH_TAG_READINGS.TAG_DATA%TYPE NULL;
1902:
1903:
1904: cursor c_readings is select INGRP1.GROUP_ID , INGRP1.ATTRIBUTE_GROUP ATTRIBUTE_GROUP1, INGRP1.ATTRIBUTE ATTRIBUTE1, INGRP1.TAG_CODE TAG_CODE1,
1905: INGRP1.TAG_DATA TAG_DATA1, INGRP1.ATTR_NAME ATTR_NAME1, INGRP1.EQUIPMENT_FK_KEY,INGRP1.READING_TIME READING_TIME1, INGRP1.UOM UOM1,

Line 1918: FROM EGO_ATTR_GROUPS_V EAGV ,EGO_ATTRS_V EAV, MTH_TAG_READINGS_STG TRS , MTH_TAG_MASTER TM

1914: TRS.ATTRIBUTE ATTRIBUTE,
1915: TRS.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY ,
1916: TM.UOM ,
1917: TRS.READING_TIME
1918: FROM EGO_ATTR_GROUPS_V EAGV ,EGO_ATTRS_V EAV, MTH_TAG_READINGS_STG TRS , MTH_TAG_MASTER TM
1919: WHERE EAGV.ATTR_GROUP_TYPE = EAV.ATTR_GROUP_TYPE
1920: AND EAGV.APPLICATION_ID = EAV.APPLICATION_ID
1921: AND EAV.ATTR_GROUP_NAME = EAGV.ATTR_GROUP_NAME
1922: AND EAGV.ATTR_GROUP_ID = TRS.ATTRIBUTE_GROUP

Line 1938: FROM EGO_ATTR_GROUPS_V EAGV ,EGO_ATTRS_V EAV, MTH_TAG_READINGS_STG TRS , MTH_TAG_MASTER TM

1934: TRS.ATTRIBUTE,
1935: TRS.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
1936: TM.UOM ,
1937: TRS.READING_TIME
1938: FROM EGO_ATTR_GROUPS_V EAGV ,EGO_ATTRS_V EAV, MTH_TAG_READINGS_STG TRS , MTH_TAG_MASTER TM
1939: WHERE EAGV.ATTR_GROUP_TYPE = EAV.ATTR_GROUP_TYPE
1940: AND EAGV.APPLICATION_ID = EAV.APPLICATION_ID
1941: AND EAV.ATTR_GROUP_NAME = EAGV.ATTR_GROUP_NAME
1942: AND EAGV.ATTR_GROUP_ID = TRS.ATTRIBUTE_GROUP

Line 1986: generates and handles equpment fault events from MTH_TAG_READINGS_STG table*

1982:
1983: /* ****************************************************************************
1984: * Procedure : MTH_GEN_HDL_EQP_FAULT_EVENT *
1985: * Description :The new procedure MTH_GEN_HDL_EQP_FAULT_EVENT *
1986: generates and handles equpment fault events from MTH_TAG_READINGS_STG table*
1987: * File Name :MTHTAGB.PLS *
1988: * Visibility :Private *
1989: * Parameters : *
1990: **************************************************************************** */

Line 2000: from mth_tag_readings_stg

1996: (select MTH_ENTITY, tag_data, equipment_fk_key, reading_time,
1997: tag_code, lag( tag_data)
1998: over (partition by equipment_fk_key, tag_code
1999: order by reading_time ) prev_tag_data
2000: from mth_tag_readings_stg
2001: where MTH_ENTITY =19
2002: ) READINGS
2003: where
2004: READINGS.TAG_DATA = v_EQP_FAULT_THRESHOLD And

Line 2032: generates and handles scrap events from MTH_TAG_READINGS_STG table*

2028:
2029: /* ****************************************************************************
2030: * Procedure : MTH_GEN_HDL_SCRAP_EVENT *
2031: * Description :The new procedure MTH_GEN_HDL_SCRAP_EVENT *
2032: generates and handles scrap events from MTH_TAG_READINGS_STG table*
2033: * File Name :MTHTAGB.PLS *
2034: * Visibility :Private *
2035: * Parameters : *
2036: **************************************************************************** */

Line 2042: MTH_TAG_READINGS_STG READINGS

2038: PROCEDURE MTH_GEN_HDL_SCRAP_EVENT IS
2039: cursor c_readings IS
2040: select readings.*
2041: from MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS,
2042: MTH_TAG_READINGS_STG READINGS
2043: where
2044: READINGS.MTH_ENTITY = 12 And
2045: READINGS.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY And
2046: READINGS.READING_TIME BETWEEN EQP_SHIFTS.FROM_DATE AND EQP_SHIFTS.TO_DATE And

Line 2065: generates and handles Recalibration events from MTH_TAG_READINGS_STG table*

2061:
2062: /* ****************************************************************************
2063: * Procedure : MTH_GEN_HDL_RECALIBRATION_EVT *
2064: * Description :The new procedure MTH_GEN_HDL_RECALIBRATION_EVT *
2065: generates and handles Recalibration events from MTH_TAG_READINGS_STG table*
2066: * File Name :MTHTAGB.PLS *
2067: * Visibility :Private *
2068: * Parameters : *
2069: **************************************************************************** */

Line 2078: MTH_TAG_READINGS_STG readings,

2074: over (partition by readings.equipment_fk_key, readings.tag_code
2075: order by readings.reading_time ) prev_tag_data
2076:
2077: from MTH_EQUIPMENT_SHIFTS_D eqp_shifts,
2078: MTH_TAG_READINGS_STG readings,
2079: MTH_ENTITIES entities
2080: WHERE
2081: READINGS.MTH_ENTITY = 18 And
2082: READINGS.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY And

Line 2114: generates and handles events from the data in mth_tag_readings_stg table*

2110:
2111: /* ****************************************************************************
2112: * Procedure : MTH_GEN_HDL_EVENTS_READINGS *
2113: * Description :The new procedure MTH_GEN_HDL_EVENTS_READINGS *
2114: generates and handles events from the data in mth_tag_readings_stg table*
2115: * File Name :MTHTAGB.PLS *
2116: * Visibility :Private *
2117: * Parameters : *
2118: **************************************************************************** */

Line 2185: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);

2181: -- Initialize default parameters
2182: v_log_date := sysdate;
2183: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2184:
2185: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2186: SELECT Count(*) INTO v_reprocess_flag FROM MTH_TAG_READINGS_ERR WHERE RE_PROCESS_FLAG='Y';
2187:
2188: --delete all the tables
2189: DELETE FROM MTH_TAG_READINGS_LATEST;

Line 2186: SELECT Count(*) INTO v_reprocess_flag FROM MTH_TAG_READINGS_ERR WHERE RE_PROCESS_FLAG='Y';

2182: v_log_date := sysdate;
2183: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2184:
2185: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2186: SELECT Count(*) INTO v_reprocess_flag FROM MTH_TAG_READINGS_ERR WHERE RE_PROCESS_FLAG='Y';
2187:
2188: --delete all the tables
2189: DELETE FROM MTH_TAG_READINGS_LATEST;
2190: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINSG_LATEST - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 2189: DELETE FROM MTH_TAG_READINGS_LATEST;

2185: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2186: SELECT Count(*) INTO v_reprocess_flag FROM MTH_TAG_READINGS_ERR WHERE RE_PROCESS_FLAG='Y';
2187:
2188: --delete all the tables
2189: DELETE FROM MTH_TAG_READINGS_LATEST;
2190: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINSG_LATEST - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2191:
2192: DELETE FROM MTH_TAG_READINGS_UNPROCESS_ERR;
2193: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_UNPROCESS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

Line 2192: DELETE FROM MTH_TAG_READINGS_UNPROCESS_ERR;

2188: --delete all the tables
2189: DELETE FROM MTH_TAG_READINGS_LATEST;
2190: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINSG_LATEST - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2191:
2192: DELETE FROM MTH_TAG_READINGS_UNPROCESS_ERR;
2193: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_UNPROCESS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2194:
2195: DELETE FROM MTH_TAG_READINGS_RAW_ERR;
2196: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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

2189: DELETE FROM MTH_TAG_READINGS_LATEST;
2190: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINSG_LATEST - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2191:
2192: DELETE FROM MTH_TAG_READINGS_UNPROCESS_ERR;
2193: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_UNPROCESS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2194:
2195: DELETE FROM MTH_TAG_READINGS_RAW_ERR;
2196: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2197:

Line 2195: DELETE FROM MTH_TAG_READINGS_RAW_ERR;

2191:
2192: DELETE FROM MTH_TAG_READINGS_UNPROCESS_ERR;
2193: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_UNPROCESS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2194:
2195: DELETE FROM MTH_TAG_READINGS_RAW_ERR;
2196: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2197:
2198: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;
2199: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_PROCESSED - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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

2192: DELETE FROM MTH_TAG_READINGS_UNPROCESS_ERR;
2193: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_UNPROCESS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2194:
2195: DELETE FROM MTH_TAG_READINGS_RAW_ERR;
2196: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2197:
2198: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;
2199: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_PROCESSED - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2200:

Line 2198: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;

2194:
2195: DELETE FROM MTH_TAG_READINGS_RAW_ERR;
2196: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2197:
2198: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;
2199: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_PROCESSED - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2200:
2201: DELETE FROM MTH_TAG_READINGS;
2202: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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

2195: DELETE FROM MTH_TAG_READINGS_RAW_ERR;
2196: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2197:
2198: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;
2199: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_PROCESSED - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2200:
2201: DELETE FROM MTH_TAG_READINGS;
2202: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2203:

Line 2201: DELETE FROM MTH_TAG_READINGS;

2197:
2198: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;
2199: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_PROCESSED - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2200:
2201: DELETE FROM MTH_TAG_READINGS;
2202: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2203:
2204: DELETE FROM MTH_TAG_READINGS_STG_ERR;
2205: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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

2198: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;
2199: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_RAW_PROCESSED - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2200:
2201: DELETE FROM MTH_TAG_READINGS;
2202: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2203:
2204: DELETE FROM MTH_TAG_READINGS_STG_ERR;
2205: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2206:

Line 2204: DELETE FROM MTH_TAG_READINGS_STG_ERR;

2200:
2201: DELETE FROM MTH_TAG_READINGS;
2202: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2203:
2204: DELETE FROM MTH_TAG_READINGS_STG_ERR;
2205: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2206:
2207: DELETE FROM MTH_TAG_READINGS_ERR;
2208: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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

2201: DELETE FROM MTH_TAG_READINGS;
2202: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2203:
2204: DELETE FROM MTH_TAG_READINGS_STG_ERR;
2205: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2206:
2207: DELETE FROM MTH_TAG_READINGS_ERR;
2208: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2209:

Line 2207: DELETE FROM MTH_TAG_READINGS_ERR;

2203:
2204: DELETE FROM MTH_TAG_READINGS_STG_ERR;
2205: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2206:
2207: DELETE FROM MTH_TAG_READINGS_ERR;
2208: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2209:
2210: DELETE FROM MTH_EVENTS;
2211: mth_util_pkg.log_msg('Number of rows deleted in MTH_EVENTS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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

2204: DELETE FROM MTH_TAG_READINGS_STG_ERR;
2205: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2206:
2207: DELETE FROM MTH_TAG_READINGS_ERR;
2208: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2209:
2210: DELETE FROM MTH_EVENTS;
2211: mth_util_pkg.log_msg('Number of rows deleted in MTH_EVENTS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2212:

Line 2261: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;

2257: END IF;
2258:
2259: IF(p_ret_code <> 2) THEN
2260:
2261: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;
2262: MTH_GEN_HDL_EVENTS_READINGS();
2263: p_ret_code := MTH_LOAD_TAG_READINGS_PKG.MTH_READINGS_ERRORS_TO_STG();
2264: IF(p_ret_code <> 2) THEN
2265: mth_util_pkg.log_msg('MTH_READINGS_ERRORS_TO_STG completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);

Line 2275: DELETE FROM MTH_TAG_READINGS_STG;

2271: mth_util_pkg.log_msg('MTH_SCHD_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2272: p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_MANUAL_BASED_CONTEXTUALIZE();
2273: IF(p_ret_code <> 2) THEN
2274: mth_util_pkg.log_msg('MTH_MANUAL_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2275: DELETE FROM MTH_TAG_READINGS_STG;
2276: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2277: IF(v_current_processing_flag=1 OR v_current_processing_flag=2 ) THEN
2278: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);
2279: END IF;

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

2272: p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_MANUAL_BASED_CONTEXTUALIZE();
2273: IF(p_ret_code <> 2) THEN
2274: mth_util_pkg.log_msg('MTH_MANUAL_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2275: DELETE FROM MTH_TAG_READINGS_STG;
2276: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2277: IF(v_current_processing_flag=1 OR v_current_processing_flag=2 ) THEN
2278: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);
2279: END IF;
2280: COMMIT;

Line 2278: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);

2274: mth_util_pkg.log_msg('MTH_MANUAL_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2275: DELETE FROM MTH_TAG_READINGS_STG;
2276: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2277: IF(v_current_processing_flag=1 OR v_current_processing_flag=2 ) THEN
2278: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);
2279: END IF;
2280: COMMIT;
2281: END IF;
2282: END IF;

Line 2288: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);

2284: END IF;
2285:
2286: IF(p_ret_code = 2) THEN
2287: ROLLBACK;
2288: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2289: UPDATE MTH_TAG_READINGS_RAW
2290: SET processing_flag = Decode(v_current_processing_flag,1,2,1)
2291: WHERE processing_flag = v_current_processing_flag;
2292: END IF;

Line 2289: UPDATE MTH_TAG_READINGS_RAW

2285:
2286: IF(p_ret_code = 2) THEN
2287: ROLLBACK;
2288: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2289: UPDATE MTH_TAG_READINGS_RAW
2290: SET processing_flag = Decode(v_current_processing_flag,1,2,1)
2291: WHERE processing_flag = v_current_processing_flag;
2292: END IF;
2293:

Line 2341: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);

2337: -- Initialize default parameters
2338: v_log_date := sysdate;
2339: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2340:
2341: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2342: SELECT Count(*) INTO v_reprocess_flag FROM MTH_TAG_READINGS_ERR WHERE RE_PROCESS_FLAG='Y';
2343:
2344: IF(v_current_processing_flag=1 OR v_current_processing_flag=2 OR v_reprocess_flag>0 ) THEN
2345: mth_util_pkg.mth_load_tag_raw_to_processed(v_current_processing_flag, p_from_tz , p_to_tz );

Line 2342: SELECT Count(*) INTO v_reprocess_flag FROM MTH_TAG_READINGS_ERR WHERE RE_PROCESS_FLAG='Y';

2338: v_log_date := sysdate;
2339: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2340:
2341: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2342: SELECT Count(*) INTO v_reprocess_flag FROM MTH_TAG_READINGS_ERR WHERE RE_PROCESS_FLAG='Y';
2343:
2344: IF(v_current_processing_flag=1 OR v_current_processing_flag=2 OR v_reprocess_flag>0 ) THEN
2345: mth_util_pkg.mth_load_tag_raw_to_processed(v_current_processing_flag, p_from_tz , p_to_tz );
2346: p_ret_code := MTH_LOAD_TAG_READINGS_PKG.MTH_TRANSFORM_TAG_DATA_TO_STG();

Line 2396: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;

2392: --Dbms_Output.put_line('p_ret_code3' || p_ret_code);
2393:
2394: IF(p_ret_code <> 2) THEN
2395:
2396: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED;
2397: MTH_GEN_HDL_EVENTS_READINGS();
2398: p_ret_code := MTH_LOAD_TAG_READINGS_PKG.MTH_READINGS_ERRORS_TO_STG();
2399: IF(p_ret_code <> 2) THEN
2400: mth_util_pkg.log_msg('MTH_READINGS_ERRORS_TO_STG completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);

Line 2410: DELETE FROM MTH_TAG_READINGS_STG;

2406: mth_util_pkg.log_msg('MTH_SCHD_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2407: p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_MANUAL_BASED_CONTEXTUALIZE();
2408: IF(p_ret_code <> 2) THEN
2409: mth_util_pkg.log_msg('MTH_MANUAL_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2410: DELETE FROM MTH_TAG_READINGS_STG;
2411: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2412: IF(v_current_processing_flag=1 OR v_current_processing_flag=2 ) THEN
2413:
2414: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);

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

2407: p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_MANUAL_BASED_CONTEXTUALIZE();
2408: IF(p_ret_code <> 2) THEN
2409: mth_util_pkg.log_msg('MTH_MANUAL_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2410: DELETE FROM MTH_TAG_READINGS_STG;
2411: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2412: IF(v_current_processing_flag=1 OR v_current_processing_flag=2 ) THEN
2413:
2414: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);
2415:

Line 2414: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);

2410: DELETE FROM MTH_TAG_READINGS_STG;
2411: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2412: IF(v_current_processing_flag=1 OR v_current_processing_flag=2 ) THEN
2413:
2414: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);
2415:
2416: END IF;
2417: COMMIT;
2418: END IF;

Line 2424: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);

2420: END IF;
2421: END IF;
2422: IF(p_ret_code = 2) THEN
2423: ROLLBACK;
2424: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2425: UPDATE MTH_TAG_READINGS_RAW
2426: SET processing_flag = Decode(v_current_processing_flag,1,2,1)
2427: WHERE processing_flag = v_current_processing_flag;
2428: END IF;

Line 2425: UPDATE MTH_TAG_READINGS_RAW

2421: END IF;
2422: IF(p_ret_code = 2) THEN
2423: ROLLBACK;
2424: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2425: UPDATE MTH_TAG_READINGS_RAW
2426: SET processing_flag = Decode(v_current_processing_flag,1,2,1)
2427: WHERE processing_flag = v_current_processing_flag;
2428: END IF;
2429: END IF;

Line 2479: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);

2475: -- Initialize default parameters
2476: v_log_date := sysdate;
2477: v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2478:
2479: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2480:
2481: IF(v_current_processing_flag=1 OR v_current_processing_flag=2) THEN
2482: DELETE FROM MTH_TAG_READINGS o
2483: WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)

Line 2482: DELETE FROM MTH_TAG_READINGS o

2478:
2479: mth_util_pkg.switch_column_default_value ( 'MTH_TAG_READINGS_RAW', v_current_processing_flag);
2480:
2481: IF(v_current_processing_flag=1 OR v_current_processing_flag=2) THEN
2482: DELETE FROM MTH_TAG_READINGS o
2483: WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)
2484: AND o.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,o.READING_TIME);
2485:
2486: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);

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

2482: DELETE FROM MTH_TAG_READINGS o
2483: WHERE o.EQUIPMENT_FK_KEY = nvl(p_equipment_pk_key,o.EQUIPMENT_FK_KEY)
2484: AND o.READING_TIME BETWEEN p_recal_from_date AND nvl(p_recal_to_date,o.READING_TIME);
2485:
2486: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2487:
2488: MTH_LOAD_TAG_READINGS_PKG.mth_recal_tag_raw_to_processed(v_current_processing_flag, p_from_tz , p_to_tz,p_ret_code);
2489: IF(p_ret_code <> 2)
2490: THEN

Line 2491: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);

2487:
2488: MTH_LOAD_TAG_READINGS_PKG.mth_recal_tag_raw_to_processed(v_current_processing_flag, p_from_tz , p_to_tz,p_ret_code);
2489: IF(p_ret_code <> 2)
2490: THEN
2491: mth_util_pkg.truncate_table_partition('MTH_TAG_READINGS_RAW' ,v_current_processing_flag);
2492: p_ret_code := MTH_LOAD_TAG_READINGS_PKG.MTH_TRANSFORM_TAG_DATA_TO_STG();
2493: IF(p_ret_code <> 2)
2494: THEN
2495: SELECT B.DESCRIPTION INTO v_desc FROM FND_LOOKUPS B

Line 2501: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED ;

2497: l_sql_stmt:= 'BEGIN ' || ' v_desc' || ' :1; ' || 'END;';
2498: EXECUTE IMMEDIATE l_sql_stmt USING OUT v_ret_code;
2499: IF(v_ret_code <> 2)
2500: THEN
2501: DELETE FROM MTH_TAG_READINGS_RAW_PROCESSED ;
2502: /* p_ret_code := MTH_LOAD_TAG_READINGS_PKG.MTH_READINGS_ERRORS_TO_STG();
2503: IF(p_ret_code <> 2)
2504: THEN */
2505: p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_TAG_BASED_CONTEXTUALIZE();

Line 2514: DELETE FROM MTH_TAG_READINGS_STG;

2510: THEN
2511: p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_MANUAL_BASED_CONTEXTUALIZE();
2512: IF(p_ret_code <> 2)
2513: THEN
2514: DELETE FROM MTH_TAG_READINGS_STG;
2515: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2516: END IF;
2517: END IF;
2518: END IF;

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

2511: p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_MANUAL_BASED_CONTEXTUALIZE();
2512: IF(p_ret_code <> 2)
2513: THEN
2514: DELETE FROM MTH_TAG_READINGS_STG;
2515: mth_util_pkg.log_msg('Number of rows deleted in MTH_TAG_READINGS_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2516: END IF;
2517: END IF;
2518: END IF;
2519: END IF;