[Home] [Help]
103: END IF;
104:
105: SELECT Count(1)
106: INTO v_tag_reading_count
107: FROM MTH_TAG_READINGS TAG,
108: MTH_RUN_LOG RUN_LOG
109: WHERE
110: (TAG.CREATION_DATE < p_recal_from_date
111: AND TAG.CREATION_DATE > p_recal_to_date)
178: mth_util_pkg.log_msg('v_log_from_date : '||v_log_from_date, mth_util_pkg.G_DBG_PROC_FUN_START);
179: mth_util_pkg.log_msg('v_log_to_date : '||v_log_to_date, mth_util_pkg.G_DBG_PROC_FUN_START);
180:
181:
182: INSERT INTO MTH_TAG_READINGS_T_STG
183: (EQUIPMENT_FK_KEY,
184: WORKORDER_FK_KEY,
185: SEGMENT_FK_KEY,
186: SHIFT_WORKDAY_FK_KEY,
203: TAG.READING_TIME,
204: TAG.TAG_DATA,
205: EGO.DATABASE_COLUMN,
206: TAG.ATTRIBUTE_GROUP
207: FROM MTH_TAG_READINGS TAG,
208: MTH_RUN_LOG RUN_LOG,
209: EGO_ATTRS_V EGO,
210: EGO_ATTR_GROUPS_V EGO_ATTR_GRP
211: WHERE RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENTS_EXT_B'
217: AND EGO.ATTR_GROUP_NAME = EGO_ATTR_GRP.ATTR_GROUP_NAME
218: AND EGO.ATTR_ID = TAG.ATTRIBUTE
219: AND TAG.processed_flag = 0);
220:
221: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_T_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
222:
223:
224: UPDATE MTH_TAG_READINGS
225: SET processed_flag = 1
220:
221: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS_T_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
222:
223:
224: UPDATE MTH_TAG_READINGS
225: SET processed_flag = 1
226: WHERE reading_time IN (SELECT read_time
227: FROM MTH_TAG_READINGS_T_STG)
228: AND processed_flag=0
223:
224: UPDATE MTH_TAG_READINGS
225: SET processed_flag = 1
226: WHERE reading_time IN (SELECT read_time
227: FROM MTH_TAG_READINGS_T_STG)
228: AND processed_flag=0
229: AND mth_entity IS NULL;
230:
231: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
227: FROM MTH_TAG_READINGS_T_STG)
228: AND processed_flag=0
229: AND mth_entity IS NULL;
230:
231: mth_util_pkg.log_msg('Number of rows inserted in MTH_TAG_READINGS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
232:
233:
234: TB_UPLOAD_NO_COMMIT();
235: p_retcode := 0;
273:
274: mth_util_pkg.log_msg('PROCESS_EQUIPMENTS_UDA_IN_INIT_MODE start', mth_util_pkg.G_DBG_PROC_FUN_START);
275: mth_util_pkg.log_msg('p_equipment_pk_key = ' || , mth_util_pkg.G_DBG_PARAM_VAL);
276:
277: INSERT INTO MTH_TAG_READINGS_T_STG
278: (EQUIPMENT_FK_KEY,
279: WORKORDER_FK_KEY,
280: SEGMENT_FK_KEY,
281: SHIFT_WORKDAY_FK_KEY,
298: TAG.READING_TIME,
299: TAG.TAG_DATA,
300: EGO.DATABASE_COLUMN,
301: TAG.GROUP_ID
302: FROM MTH_TAG_READINGS TAG,
303: MTH_RUN_LOG RUN_LOG,
304: EGO_ATTRS_V EGO,
305: EGO_ATTR_GROUPS_V EGO_ATTR_GRP
306: WHERE RUN_LOG.FACT_TABLE = 'MTH_EQUIPMENTS_EXT_B'
364: AND NVL(p_equipment_pk_key,EQEXT.EQUIPMENT_PK_KEY) in
365: (SELECT distinct EQUIPMENT_PK_KEY from MTH_EQUIPMENTS_D
366: WHERE PLANT_FK_KEY = p_plant);
367:
368: INSERT INTO MTH_TAG_READINGS_T_STG
369: (EQUIPMENT_FK_KEY,
370: WORKORDER_FK_KEY,
371: SEGMENT_FK_KEY,
372: SHIFT_WORKDAY_FK_KEY,
389: TAG.READING_TIME,
390: TAG.TAG_DATA,
391: EGO.DATABASE_COLUMN,
392: TAG.ATTRIBUTE_GROUP
393: FROM MTH_TAG_READINGS TAG,
394: EGO_ATTRS_V EGO,
395: EGO_ATTR_GROUPS_V EGO_ATTR_GRP,
396: MTH_EQUIPMENTS_D EQUIP
397: WHERE TAG.CREATION_DATE <= p_recal_to_date
427: v_tl_colname VARCHAR2(30);
428: v_stmt VARCHAR2(32767);
429: v_stmt_no NUMBER;
430: CURSOR DISTINCT_COLUMN IS
431: SELECT DISTINCT DB_COL FROM MTH_TAG_READINGS_T_STG;
432:
433:
434: BEGIN
435:
452: RECIPE_NUM,
453: RECIPE_VERSION,
454: NVL(FND_GLOBAL.User_Id,-1)l_updated_by,
455: NVL(FND_GLOBAL.Login_Id,-1)l_last_update_login
456: FROM MTH_TAG_READINGS_T_STG
457: WHERE DB_COL = '||''''||v_colname||''''||') TS
458: ON (';
459:
460: v_stmt := v_stmt||'ED.EQUIPMENT_PK_KEY = TS.EQUIPMENT_FK_KEY AND