[Home] [Help]
PACKAGE BODY: APPS.MTH_LOAD_TAG_READINGS_PKG
Source
1 PACKAGE BODY MTH_LOAD_TAG_READINGS_PKG AS
2 /*$Header: mthtagb.pls 120.8.12020000.5 2012/10/17 08:00:56 aksachde noship $*/
3
4 PROCEDURE INIT_READINGS_FROM_RAW(
5 p_from_tz IN VARCHAR2 DEFAULT NULL,
6 p_to_tz IN VARCHAR2 DEFAULT NULL,
7 p_ret_code OUT NOCOPY NUMBER
8 );
9
10 PROCEDURE INCR_READINGS_FROM_RAW(
11 p_from_tz IN VARCHAR2 DEFAULT NULL,
12 p_to_tz IN VARCHAR2 DEFAULT NULL,
13 p_ret_code OUT NOCOPY NUMBER
14 );
15
16 PROCEDURE RECAL_READINGS_FROM_RAW(p_recal_from_date IN DATE, --Recalculation from date
17 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
18 p_equipment_pk_key IN NUMBER DEFAULT NULL,
19 p_from_tz IN VARCHAR2 DEFAULT NULL,
20 p_to_tz IN VARCHAR2 DEFAULT NULL,
21 p_ret_code OUT NOCOPY NUMBER
22 );
23
24
25 /*******************************************************************************
29 * Visibility :Public *
26 * Procedure :PROCESS_READINGS *
27 * Description :This procedure is the main procedure for readings *
28 * File Name :MTHTAGB.PLS *
30 * Parameters : p_mode : INIT,INCR,RECAL *
31 * p_recal_from_date : Recalculation from date *
32 * p_recal_to_date : Recalculation to date *
33 * p_equipment_pk_key : Equipment to recalculate
34 * p_from_tz *
35 * p_to_tz *
36 * p_ret_code *
37 *******************************************************************************/
38
39 PROCEDURE PROCESS_READINGS( p_mode IN VARCHAR2, --INIT, INCR, RECAL
40 p_recal_from_date IN DATE DEFAULT NULL, --Recalculation from date
41 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
42 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
43 p_from_tz IN VARCHAR2 DEFAULT NULL,
44 p_to_tz IN VARCHAR2 DEFAULT NULL ,
45 p_ret_code OUT NOCOPY NUMBER
46 ) IS
47
48 BEGIN
49 p_ret_code := 0;
50 mth_util_pkg.log_msg('PROCESS_READINGS start', mth_util_pkg.G_DBG_PROC_FUN_START);
51 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
52 mth_util_pkg.log_msg('p_recal_from_date = ' || to_char(p_recal_from_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
53 mth_util_pkg.log_msg('p_recal_to_date = ' || to_char(p_recal_to_date,'DD-MON-YYYY HH24:MI:SS') , mth_util_pkg.G_DBG_PARAM_VAL);
54 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
55
56 IF p_mode = 'INIT' THEN
57 INIT_READINGS_FROM_RAW(
58 p_from_tz,
59 p_to_tz,
60 p_ret_code
61 );
62 ELSIF p_mode = 'INCR' THEN
63 INCR_READINGS_FROM_RAW(
64 p_from_tz,
65 p_to_tz,
66 p_ret_code
67 );
68 ELSE
69 RECAL_READINGS_FROM_RAW(p_recal_from_date,
70 p_recal_to_date,
71 p_equipment_pk_key,
72 p_from_tz,
73 p_to_tz,
74 p_ret_code
75 );
76 END IF;
77 mth_util_pkg.log_msg('PROCESS_READINGS end', mth_util_pkg.G_DBG_PROC_FUN_END);
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_READINGS', mth_util_pkg.G_DBG_EXCEPTION);
82 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
83 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
84 p_ret_code :=2;
85
86 END PROCESS_READINGS;
87 /*******************************************************************************
88 * Function :MTH_TRANSFORM_TAG_DATA_TO_STG *
89 * Description :This function is used to push the data from raw proceseed
90 to tag readings staging or staging error *
91 * File Name :MTHTAGB.PLS *
92 * Visibility :Private *
93 * Parameters : *
94 *******************************************************************************/
95 FUNCTION MTH_TRANSFORM_TAG_DATA_TO_STG RETURN NUMBER
96 IS
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,
106 USER_ATTR1,
107 USER_ATTR2,
108 USER_ATTR3,
109 USER_ATTR4,
110 USER_ATTR5,
111 USER_MEASURE1,
112 USER_MEASURE2,
113 USER_MEASURE3,
114 USER_MEASURE4,
115 USER_MEASURE5,
116 QUALITY_FLAG,
117 CREATION_DATE,
118 ERR_CODE)
119 (SELECT MAX(RP.GROUP_ID) GROUP_ID,
120 RP.READING_TIME,
121 RP.TAG_CODE,
122 MAX(RP.TAG_DATA) TAG_DATA,
123 MAX(RP.USER_ATTR1) USER_ATTR1,
124 MAX(RP.USER_ATTR2) USER_ATTR2,
125 MAX(RP.USER_ATTR3) USER_ATTR3,
126 MAX(RP.USER_ATTR4) USER_ATTR4,
127 MAX(RP.USER_ATTR5) USER_ATTR5,
128 MAX(RP.USER_MEASURE1) USER_MEASURE1,
129 MAX(RP.USER_MEASURE2) USER_MEASURE2,
130 MAX(RP.USER_MEASURE3) USER_MEASURE3,
131 MAX(RP.USER_MEASURE4) USER_MEASURE4,
132 MAX(RP.USER_MEASURE5) USER_MEASURE5,
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);
142
146 INTO
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
147 MTH_TAG_READINGS_STG_ERR
148 (GROUP_ID,
149 READING_TIME,
150 TAG_CODE,
151 TAG_DATA,
152 EQUIPMENT_FK_KEY,
153 PLANT_FK_KEY,
154 USER_ATTR1,
155 USER_ATTR2,
156 USER_ATTR3,
157 USER_ATTR4,
158 USER_ATTR5,
159 USER_MEASURE1,
160 USER_MEASURE2,
161 USER_MEASURE3,
162 USER_MEASURE4,
163 USER_MEASURE5,
164 QUALITY_FLAG)
165 (SELECT
166 RAW_DATA.GROUP_ID GROUP_ID,
167 RAW_DATA.READING_TIME READING_TIME,
168 RAW_DATA.TAG_CODE TAG_CODE,
169 RAW_DATA.TAG_DATA TAG_DATA,
170 TDM.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
171 EQP.PLANT_FK_KEY PLANT_FK_KEY,
172 RAW_DATA.USER_ATTR1 USER_ATTR1,
173 RAW_DATA.USER_ATTR2 USER_ATTR2,
174 RAW_DATA.USER_ATTR3 USER_ATTR3,
175 RAW_DATA.USER_ATTR4 USER_ATTR4,
176 RAW_DATA.USER_ATTR5 USER_ATTR5,
177 RAW_DATA.USER_MEASURE1 USER_MEASURE1,
178 RAW_DATA.USER_MEASURE2 USER_MEASURE2,
179 RAW_DATA.USER_MEASURE3 USER_MEASURE3,
180 RAW_DATA.USER_MEASURE4 USER_MEASURE4,
181 RAW_DATA.USER_MEASURE5 USER_MEASURE5,
182 RAW_DATA.QUALITY_FLAG QUALITY_FLAG
183 FROM
184 ( SELECT
185 RP.READING_TIME,
186 RP.TAG_CODE,
187 MAX(RP.GROUP_ID) GROUP_ID,
188 MAX(RP.TAG_DATA) TAG_DATA,
189 MAX(RP.USER_ATTR1) USER_ATTR1,
190 MAX(RP.USER_ATTR2) USER_ATTR2,
191 MAX(RP.USER_ATTR3) USER_ATTR3,
192 MAX(RP.USER_ATTR4) USER_ATTR4,
193 MAX(RP.USER_ATTR5) USER_ATTR5,
194 MAX(RP.USER_MEASURE1) USER_MEASURE1,
195 MAX(RP.USER_MEASURE2) USER_MEASURE2,
196 MAX(RP.USER_MEASURE3) USER_MEASURE3,
197 MAX(RP.USER_MEASURE4) USER_MEASURE4,
198 MAX(RP.USER_MEASURE5) USER_MEASURE5,
199 MAX(RP.QUALITY_FLAG) QUALITY_FLAG,
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
209 AND TM.TAG_CODE = TDM.TAG_CODE (+)
210 AND TDM.EQUIPMENT_FK_KEY = EQP.EQUIPMENT_PK_KEY(+)
211 AND(( TDM.EQUIPMENT_FK_KEY IS NULL OR
212 EQP.PLANT_FK_KEY is NULL OR
213 TM.STATUS is NULL OR
214 TM.STATUS <> 'ACTIVE' OR
215 (TDM.MTH_ENTITY=5 AND RAW_DATA.TAG_DATA NOT IN (1,2,3,4) )
216 OR (TDM.MTH_ENTITY=8 AND RAW_DATA.TAG_DATA NOT IN (SELECT lookup_code FROM fnd_lookups WHERE lookup_type='MTH_EQUIP_DOWNTIME_REASON' ))
217 OR (TDM.MTH_ENTITY=17 AND RAW_DATA.TAG_DATA NOT IN (SELECT lookup_code FROM fnd_lookups WHERE lookup_type='MTH_EQUIP_IDLE_REASON' ))
218 OR (TDM.MTH_ENTITY=13 AND RAW_DATA.TAG_DATA NOT IN (SELECT lookup_code FROM fnd_lookups WHERE lookup_type='MTH_SCRAP_REASON' )) ) OR
219 (TDM.EQUIPMENT_FK_KEY IS NOT NULL
220 AND EQP.PLANT_FK_KEY is NOT NULL
221 AND TM.STATUS is NOT NULL
222 AND TM.STATUS = 'ACTIVE'
223 AND TDM.MTH_ENTITY IS NULL
224 AND TDM.ATTRIBUTE_GROUP IS NULL
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
234 MTH_TAG_READINGS_STG
235 (GROUP_ID,
236 READING_TIME,
237 TAG_CODE,
238 TAG_DATA,
239 MTH_ENTITY,
240 ATTRIBUTE_GROUP,
241 ATTRIBUTE,
242 EQUIPMENT_FK_KEY,
243 PLANT_FK_KEY,
244 USER_ATTR1,
245 USER_ATTR2,
246 USER_ATTR3,
247 USER_ATTR4,
248 USER_ATTR5,
249 USER_MEASURE1,
250 USER_MEASURE2,
251 USER_MEASURE3,
252 USER_MEASURE4,
253 USER_MEASURE5,
254 QUALITY_FLAG,
255 CREATION_DATE)
256 (SELECT
257 RAW_DATA.GROUP_ID GROUP_ID,
258 RAW_DATA.READING_TIME READING_TIME,
259 RAW_DATA.TAG_CODE TAG_CODE,
260 RAW_DATA.TAG_DATA TAG_DATA,
261 TDM.MTH_ENTITY MTH_ENTITY,
262 TDM.ATTRIBUTE_GROUP ATTRIBUTE_GROUP,
263 TDM.ATTRIBUTE ATTRIBUTE,
264 TDM.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY,
265 EQP.PLANT_FK_KEY PLANT_FK_KEY,
266 RAW_DATA.USER_ATTR1 USER_ATTR1,
267 RAW_DATA.USER_ATTR2 USER_ATTR2,
268 RAW_DATA.USER_ATTR3 USER_ATTR3,
269 RAW_DATA.USER_ATTR4 USER_ATTR4,
270 RAW_DATA.USER_ATTR5 USER_ATTR5,
271 RAW_DATA.USER_MEASURE1 USER_MEASURE1,
272 RAW_DATA.USER_MEASURE2 USER_MEASURE2,
273 RAW_DATA.USER_MEASURE3 USER_MEASURE3,
274 RAW_DATA.USER_MEASURE4 USER_MEASURE4,
275 RAW_DATA.USER_MEASURE5 USER_MEASURE5,
276 RAW_DATA.QUALITY_FLAG QUALITY_FLAG,
277 SYSDATE - 0.002 CREATION_DATE
278 FROM
279 (SELECT
280 RP.READING_TIME,
281 RP.TAG_CODE,
282 MAX(RP.GROUP_ID) GROUP_ID,
283 MAX(RP.TAG_DATA) TAG_DATA,
284 MAX(RP.USER_ATTR1) USER_ATTR1,
285 MAX(RP.USER_ATTR2) USER_ATTR2,
286 MAX(RP.USER_ATTR3) USER_ATTR3,
287 MAX(RP.USER_ATTR4) USER_ATTR4,
288 MAX(RP.USER_ATTR5) USER_ATTR5,
289 MAX(RP.USER_MEASURE1) USER_MEASURE1,
290 MAX(RP.USER_MEASURE2) USER_MEASURE2,
291 MAX(RP.USER_MEASURE3) USER_MEASURE3,
292 MAX(RP.USER_MEASURE4) USER_MEASURE4,
293 MAX(RP.USER_MEASURE5) USER_MEASURE5,
294 MAX(RP.QUALITY_FLAG) QUALITY_FLAG,
298 MTH_TAG_READINGS_RAW_PROCESSED RP
295 MAX(RP.CREATION_DATE) CREATION_DATE,
296 COUNT(RP.TAG_CODE) DUP_COUNT
297 FROM
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
303 RAW_DATA.TAG_CODE = TM.TAG_CODE
304 AND TM.TAG_CODE = TDM.TAG_CODE (+)
305 AND TDM.EQUIPMENT_FK_KEY = EQP.EQUIPMENT_PK_KEY(+)
306 AND TDM.EQUIPMENT_FK_KEY IS NOT NULL
307 AND EQP.PLANT_FK_KEY is NOT NULL
308 AND TM.STATUS is NOT NULL
309 AND TM.STATUS = 'ACTIVE'
310 AND (((TDM.MTH_ENTITY<>5 OR RAW_DATA.TAG_DATA IN (1,2,3,4) )
311 AND (TDM.MTH_ENTITY<>8 OR RAW_DATA.TAG_DATA IN (SELECT lookup_code FROM fnd_lookups WHERE lookup_type='MTH_EQUIP_DOWNTIME_REASON' ))
312 AND (TDM.MTH_ENTITY<>17 OR RAW_DATA.TAG_DATA IN (SELECT lookup_code FROM fnd_lookups WHERE lookup_type='MTH_EQUIP_IDLE_REASON' ))
313 AND (TDM.MTH_ENTITY<>13 OR RAW_DATA.TAG_DATA IN (SELECT lookup_code FROM fnd_lookups WHERE lookup_type='MTH_SCRAP_REASON' )) )
314 OR TDM.MTH_ENTITY IS NULL)
315
316 AND NOT (TDM.MTH_ENTITY IS NULL And
317 TDM.ATTRIBUTE_GROUP IS NULL And
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
327 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_READINGS', mth_util_pkg.G_DBG_EXCEPTION);
328 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
329 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
330 v_ret_code :=2;
331 RETURN v_ret_code;
332
333 END MTH_TRANSFORM_TAG_DATA_TO_STG;
334 /*******************************************************************************
335 * Function :MTH_PROCESS_READINGS_ERRORS_TO_STG *
336 * Description :This function is used to push the data from readings error
337 to readings or error or staging table *
338 * File Name :MTHTAGB.PLS *
339 * Visibility :Private *
340 * Parameters : *
341 *******************************************************************************/
342 FUNCTION MTH_READINGS_ERRORS_TO_STG RETURN NUMBER
343 IS
344 v_ret_code NUMBER;
345 BEGIN
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,
355 MTH_ENTITY,
356 ATTRIBUTE_GROUP,
357 ATTRIBUTE,
358 EQUIPMENT_FK_KEY,
359 WORKORDER_FK_KEY,
360 SEGMENT_FK_KEY,
361 SHIFT_WORKDAY_FK_KEY,
362 ITEM_FK_KEY,
363 HOUR_FK_KEY,
364 PROCESSED_FLAG,
365 CREATION_DATE,
366 LAST_UPDATE_DATE,
367 CREATION_SYSTEM_ID,
368 LAST_UPDATE_SYSTEM_ID,
369 PLANT_FK_KEY,
370 USER_ATTR1,
371 USER_ATTR2,
372 USER_ATTR3,
373 USER_ATTR4,
374 USER_ATTR5,
375 USER_MEASURE1,
376 USER_MEASURE2,
377 USER_MEASURE3,
378 USER_MEASURE4,
379 USER_MEASURE5,
380 QUALITY_FLAG,
381 RECIPE_NUM,
382 RECIPE_VERSION)
383 (SELECT
384 TRE.GROUP_ID,
385 TRE.READING_TIME,
386 TRE.TAG_CODE,
387 TRE.TAG_DATA,
388 TRE.MTH_ENTITY,
389 TRE.ATTRIBUTE_GROUP,
390 TRE.ATTRIBUTE,
391 TRE.EQUIPMENT_FK_KEY,
392 NVL( TRE.WORKORDER_FK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ) ,
393 NVL( TRE.SEGMENT_FK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
394 TRE.SHIFT_WORKDAY_FK_KEY,
395 NVL( TRE.ITEM_FK_KEY , MTH_UTIL_PKG.MTH_UA_GET_VAL() ),
396 TRE.HOUR_FK_KEY,
397 TRE.PROCESSED_FLAG,
398 SYSDATE - 0.0001,
399 SYSDATE - 0.0001,
400 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
401 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
402 TRE.PLANT_FK_KEY,
403 TRE.USER_ATTR1,
404 TRE.USER_ATTR2,
405 TRE.USER_ATTR3,
406 TRE.USER_ATTR4,
407 TRE.USER_ATTR5,
408 TRE.USER_MEASURE1,
409 TRE.USER_MEASURE2,
410 TRE.USER_MEASURE3,
411 TRE.USER_MEASURE4,
412 TRE.USER_MEASURE5,
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 (+)
422 AND (MCM.CONTEXTUALIZE_METHOD = 1 Or MCM.CONTEXTUALIZE_METHOD = 3)
423 AND TRE.SHIFT_WORKDAY_FK_KEY IS NOT NULL AND
424 TRE.HOUR_FK_KEY IS NOT NULL AND
425 TRE.EQUIPMENT_STATUS = 'ACTIVE' AND
426 ( MCR.WO_REQUIRED <> 'Y' Or
427 TRE.WORKORDER_FK_KEY IS NOT NULL And TRE.WORKORDER_FK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL() ) AND
428 ( MCR.WO_SEGMENT_REQUIRED <> 'Y' Or
429 TRE.SEGMENT_FK_KEY IS NOT NULL And TRE.SEGMENT_FK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL()) AND
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
439 MTH_TAG_READINGS_STG
436 -- Inserting scheduled based contextualization records in tag readings staging table for the equipment whose staus is active
437 INSERT
438 INTO
440 (GROUP_ID,
441 READING_TIME,
442 TAG_CODE,
443 TAG_DATA,
444 MTH_ENTITY,
445 ATTRIBUTE_GROUP,
446 ATTRIBUTE,
447 EQUIPMENT_FK_KEY,
448 PLANT_FK_KEY,
449 USER_ATTR1,
450 USER_ATTR2,
451 USER_ATTR3,
452 USER_ATTR4,
453 USER_ATTR5,
454 USER_MEASURE1,
455 USER_MEASURE2,
456 USER_MEASURE3,
457 USER_MEASURE4,
458 USER_MEASURE5,
459 QUALITY_FLAG,
460 PROCESSED_FLAG,
461 CREATION_DATE)
462 (SELECT
463 TRE.GROUP_ID,
464 TRE.READING_TIME,
465 TRE.TAG_CODE,
466 TRE.TAG_DATA,
467 TRE.MTH_ENTITY,
468 TRE.ATTRIBUTE_GROUP,
469 TRE.ATTRIBUTE,
470 TRE.EQUIPMENT_FK_KEY,
471 TRE.PLANT_FK_KEY,
472 TRE.USER_ATTR1,
473 TRE.USER_ATTR2,
474 TRE.USER_ATTR3,
475 TRE.USER_ATTR4,
476 TRE.USER_ATTR5,
477 TRE.USER_MEASURE1,
478 TRE.USER_MEASURE2,
479 TRE.USER_MEASURE3,
480 TRE.USER_MEASURE4,
481 TRE.USER_MEASURE5,
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 (+)
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
500 MTH_TAG_READINGS_ERR
501 (GROUP_ID,
502 READING_TIME,
503 TAG_CODE,
504 TAG_DATA,
505 MTH_ENTITY,
506 ATTRIBUTE_GROUP,
507 ATTRIBUTE,
508 EQUIPMENT_FK_KEY,
509 WORKORDER_FK_KEY,
510 SEGMENT_FK_KEY,
511 SHIFT_WORKDAY_FK_KEY,
512 ITEM_FK_KEY,
513 HOUR_FK_KEY,
514 WO_TAG_CODE,
515 WO_TAG_DATA,
516 SEGMENT_TAG_CODE,
517 SEGMENT_TAG_DATA,
518 ITEM_TAG_CODE,
519 ITEM_TAG_DATA,
520 RE_PROCESS_FLAG,
521 PLANT_FK_KEY,
522 USER_ATTR1,
523 USER_ATTR2,
524 USER_ATTR3,
525 USER_ATTR4,
526 USER_ATTR5,
527 USER_MEASURE1,
528 USER_MEASURE2,
529 USER_MEASURE3,
530 USER_MEASURE4,
531 USER_MEASURE5,
532 QUALITY_FLAG,
533 PROCESSED_FLAG,
534 RECIPE_VERSION_TAG_CODE,
535 RECIPE_NUM_TAG_CODE,
536 RECIPE_VERSION,
537 RECIPE_NUM,
538 EQUIPMENT_STATUS)
539 (SELECT
540 TRE.GROUP_ID,
541 TRE.READING_TIME,
542 TRE.TAG_CODE,
543 TRE.TAG_DATA,
544 TRE.MTH_ENTITY,
545 TRE.ATTRIBUTE_GROUP,
546 TRE.ATTRIBUTE,
547 TRE.EQUIPMENT_FK_KEY,
548 TRE.WORKORDER_FK_KEY,
549 TRE.SEGMENT_FK_KEY,
550 TRE.SHIFT_WORKDAY_FK_KEY,
551 TRE.ITEM_FK_KEY,
552 TRE.HOUR_FK_KEY,
553 TRE.WO_TAG_CODE,
554 TRE.WO_TAG_DATA,
555 TRE.SEGMENT_TAG_CODE,
556 TRE.SEGMENT_TAG_DATA,
557 TRE.ITEM_TAG_CODE,
558 TRE.ITEM_TAG_DATA,
559 'N',
560 TRE.PLANT_FK_KEY,
561 TRE.USER_ATTR1,
562 TRE.USER_ATTR2,
563 TRE.USER_ATTR3,
564 TRE.USER_ATTR4,
565 TRE.USER_ATTR5,
566 TRE.USER_MEASURE1,
567 TRE.USER_MEASURE2,
568 TRE.USER_MEASURE3,
569 TRE.USER_MEASURE4,
570 TRE.USER_MEASURE5,
571 TRE.QUALITY_FLAG,
572 TRE.PROCESSED_FLAG,
573 TRE.RECIPE_VERSION_TAG_CODE,
574 TRE.RECIPE_NUM_TAG_CODE,
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
584 NOT ((MCM.CONTEXTUALIZE_METHOD = 2 AND TRE.EQUIPMENT_STATUS = 'ACTIVE')
585 OR (( MCM.CONTEXTUALIZE_METHOD = 1 Or MCM.CONTEXTUALIZE_METHOD = 3) And
586 TRE.SHIFT_WORKDAY_FK_KEY IS NOT NULL And
587 TRE.HOUR_FK_KEY IS NOT NULL And
588 TRE.EQUIPMENT_STATUS = 'ACTIVE' And
589 (MCR.WO_REQUIRED <> 'Y' Or
590 TRE.WORKORDER_FK_KEY IS NOT NULL And TRE.WORKORDER_FK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL() ) And
591 (MCR.WO_SEGMENT_REQUIRED <> 'Y' Or
592 TRE.SEGMENT_FK_KEY IS NOT NULL And TRE.SEGMENT_FK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL() ) And
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
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
610 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_READINGS', mth_util_pkg.G_DBG_EXCEPTION);
614 RETURN v_ret_code;
611 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
612 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
613 v_ret_code:=2;
615 END MTH_READINGS_ERRORS_TO_STG;
616
617 /*******************************************************************************
618 * Function :MTH_TAG_BASED_CONTEXTUALIZE *
619 * Description :This function is used to push the data from readings staging
620 to readings or error table *
621 * File Name :MTHTAGB.PLS *
622 * Visibility :Private *
623 * Parameters : *
624 *******************************************************************************/
625 FUNCTION MTH_TAG_BASED_CONTEXTUALIZE RETURN NUMBER
626 IS
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,
636 MTH_ENTITY,
637 ATTRIBUTE_GROUP,
638 ATTRIBUTE,
639 EQUIPMENT_FK_KEY,
640 RE_PROCESS_FLAG,
641 PLANT_FK_KEY,
642 USER_ATTR1,
643 USER_ATTR2,
644 USER_ATTR3,
645 USER_ATTR4,
646 USER_ATTR5,
647 USER_MEASURE1,
648 USER_MEASURE2,
649 USER_MEASURE3,
650 USER_MEASURE4,
651 USER_MEASURE5,
652 QUALITY_FLAG,
653 PROCESSED_FLAG)
654 (SELECT
655 STG.GROUP_ID,
656 STG.READING_TIME,
657 STG.TAG_CODE,
658 STG.TAG_DATA,
659 STG.MTH_ENTITY,
660 STG.ATTRIBUTE_GROUP,
661 STG.ATTRIBUTE,
662 STG.EQUIPMENT_FK_KEY,
663 'N',
664 STG.PLANT_FK_KEY,
665 STG.USER_ATTR1,
666 STG.USER_ATTR2,
667 STG.USER_ATTR3,
668 STG.USER_ATTR4,
669 STG.USER_ATTR5,
670 STG.USER_MEASURE1,
671 STG.USER_MEASURE2,
672 STG.USER_MEASURE3,
673 STG.USER_MEASURE4,
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 )
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
689 MTH_TAG_READINGS_ERR
690 (GROUP_ID,
691 READING_TIME,
692 TAG_CODE,
693 TAG_DATA,
694 MTH_ENTITY,
695 ATTRIBUTE_GROUP,
696 ATTRIBUTE,
697 EQUIPMENT_FK_KEY,
698 WORKORDER_FK_KEY,
699 SEGMENT_FK_KEY,
700 SHIFT_WORKDAY_FK_KEY,
701 ITEM_FK_KEY,
702 HOUR_FK_KEY,
703 WO_TAG_CODE,
704 WO_TAG_DATA,
705 SEGMENT_TAG_CODE,
706 SEGMENT_TAG_DATA,
707 ITEM_TAG_CODE,
708 ITEM_TAG_DATA,
709 RE_PROCESS_FLAG,
710 PLANT_FK_KEY,
711 USER_ATTR1,
712 USER_ATTR2,
713 USER_ATTR3,
714 USER_ATTR4,
715 USER_ATTR5,
716 USER_MEASURE1,
717 USER_MEASURE2,
718 USER_MEASURE3,
719 USER_MEASURE4,
720 USER_MEASURE5,
721 QUALITY_FLAG,
722 PROCESSED_FLAG,
723 RECIPE_VERSION_TAG_CODE,
724 RECIPE_NUM_TAG_CODE,
725 RECIPE_VERSION,
726 RECIPE_NUM,
727 EQUIPMENT_STATUS)
728 ( SELECT
729 TAG_DATA1.GROUP_ID ,
730 TAG_DATA1.READING_TIME,
731 TAG_DATA1.TAG_CODE,
732 TAG_DATA1.TAG_DATA,
733 TAG_DATA1.MTH_ENTITY,
734 TAG_DATA1.ATTRIBUTE_GROUP,
735 TAG_DATA1.ATTRIBUTE,
736 TAG_DATA1.EQUIPMENT_FK_KEY,
737 PROD_SCHD.WORKORDER_PK_KEY,
738 PROD_SEG.SEGMENT_PK_KEY,
739 TAG_DATA1.SHIFT_WORKDAY_FK_KEY,
740 ITEMS.ITEM_PK_KEY,
741 TAG_DATA1.HOUR_FK_KEY,
742 TAG_DATA1.WORKORDER_TAG_CODE,
743 TAG_DATA1.WORKORDER,
744 TAG_DATA1.SEGMENT_TAG_CODE,
745 TAG_DATA1.SEGMENT1,
746 TAG_DATA1.ITEM_TAG_CODE,
747 TAG_DATA1.ITEM,
748 'N',
749 TAG_DATA1.PLANT_FK_KEY,
750 TAG_DATA1.USER_ATTR1,
751 TAG_DATA1.USER_ATTR2,
752 TAG_DATA1.USER_ATTR3,
753 TAG_DATA1.USER_ATTR4,
754 TAG_DATA1.USER_ATTR5,
755 TAG_DATA1.USER_MEASURE1,
756 TAG_DATA1.USER_MEASURE2,
757 TAG_DATA1.USER_MEASURE3,
758 TAG_DATA1.USER_MEASURE4,
759 TAG_DATA1.USER_MEASURE5,
760 TAG_DATA1.QUALITY_FLAG,
761 TAG_DATA1.PROCESSED_FLAG,
762 TAG_DATA1.RECIPE_VERSION_TAG_CODE,
763 TAG_DATA1.RECIPE_NUM_TAG_CODE,
764 TAG_DATA1.RECIPE_VERSION,
765 TAG_DATA1.RECIPE_NUM,
766 TAG_DATA1.STATUS
767 FROM
768 ( SELECT
769 case when BUS_CONTEXT.WORKORDER is null
770 then TO_CHAR( MTH_UTIL_PKG.MTH_UA_GET_VAL() )
771 else BUS_CONTEXT.WORKORDER || '-' || PLANTS.PLANT_PK
772 END WORKORDER_PK,
773 case when BUS_CONTEXT.ITEM is null
774 then TO_CHAR( MTH_UTIL_PKG.MTH_UA_GET_VAL() )
775 else BUS_CONTEXT.ITEM || '-' || PLANTS.PLANT_PK
776 END ITEM_PK,
777 case when (BUS_CONTEXT.SEGMENT1 is null or BUS_CONTEXT.WORKORDER is null)
778 then TO_CHAR( MTH_UTIL_PKG.MTH_UA_GET_VAL() )
782 TAG_DATA.READING_TIME,
779 else BUS_CONTEXT.SEGMENT1 || '-' || BUS_CONTEXT.WORKORDER || '-' || PLANTS.PLANT_PK
780 END SEGMENT_PK,
781 TAG_DATA.GROUP_ID,
783 TAG_DATA.TAG_CODE,
784 TAG_DATA.MTH_ENTITY,
785 TAG_DATA.ATTRIBUTE_GROUP,
786 TAG_DATA.ATTRIBUTE,
787 TAG_DATA.EQUIPMENT_FK_KEY,
788 TAG_DATA.TAG_DATA,
789 EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY,
790 BUS_CONTEXT.WORKORDER_TAG_CODE,
791 BUS_CONTEXT.SEGMENT_TAG_CODE,
792 BUS_CONTEXT.ITEM_TAG_CODE,
793 BUS_CONTEXT.WORKORDER,
794 BUS_CONTEXT.SEGMENT1,
795 BUS_CONTEXT.ITEM,
796 TAG_DATA.CONTEXTUALIZE_METHOD,
797 TAG_DATA.PLANT_FK_KEY,
798 TAG_DATA.PROCESSED_FLAG,
799 TAG_DATA.USER_ATTR1,
800 TAG_DATA.USER_ATTR2,
801 TAG_DATA.USER_ATTR3,
802 TAG_DATA.USER_ATTR4,
803 TAG_DATA.USER_ATTR5,
804 TAG_DATA.USER_MEASURE1,
805 TAG_DATA.USER_MEASURE2,
806 TAG_DATA.USER_MEASURE3,
807 TAG_DATA.USER_MEASURE4,
808 TAG_DATA.USER_MEASURE5,
809 TAG_DATA.QUALITY_FLAG,
810 BUS_CONTEXT.RECIPE_NUM,
811 BUS_CONTEXT.RECIPE_VERSION,
812 BUS_CONTEXT.RECIPE_NUM_TAG_CODE,
813 BUS_CONTEXT.RECIPE_VERSION_TAG_CODE,
814 HOURS.HOUR_PK_KEY HOUR_FK_KEY,
815 EQUIPMENTS.STATUS
816 FROM
817 ( SELECT
818 STG.GROUP_ID,
819 STG.READING_TIME,
820 STG.TAG_CODE,
821 STG.TAG_DATA,
822 STG.MTH_ENTITY,
823 STG.ATTRIBUTE_GROUP,
824 STG.ATTRIBUTE,
825 STG.EQUIPMENT_FK_KEY,
826 MCM.CONTEXTUALIZE_METHOD,
827 STG.PLANT_FK_KEY,
828 NVL(STG.PROCESSED_FLAG , 0)PROCESSED_FLAG,
829 STG.USER_ATTR1,
830 STG.USER_ATTR2,
831 STG.USER_ATTR3,
832 STG.USER_ATTR4,
833 STG.USER_ATTR5,
834 STG.USER_MEASURE1,
835 STG.USER_MEASURE2,
836 STG.USER_MEASURE3,
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 ) )
846 ) TAG_DATA, ( SELECT
847 MIN(CASE WHEN BUS.MTH_ENTITY = 2 THEN BUS.READING_TIME ELSE NULL END) READING_TIME,
848 EQUIPMENT_FK_KEY,
849 MIN(CASE WHEN BUS.MTH_ENTITY = 2 THEN BUS.TAG_DATA ELSE NULL END) WORKORDER,
850 MIN(CASE WHEN BUS.MTH_ENTITY = 3 THEN BUS.TAG_DATA ELSE NULL END) SEGMENT1,
851 MIN(CASE WHEN BUS.MTH_ENTITY = 4 THEN BUS.TAG_DATA ELSE NULL END) ITEM,
852 MIN(CASE WHEN BUS.MTH_ENTITY = 2 THEN BUS.TAG_CODE ELSE NULL END) WORKORDER_TAG_CODE,
853 MIN(CASE WHEN BUS.MTH_ENTITY = 3 THEN BUS.TAG_CODE ELSE NULL END) SEGMENT_TAG_CODE,
854 MIN(CASE WHEN BUS.MTH_ENTITY = 4 THEN BUS.TAG_CODE ELSE NULL END) ITEM_TAG_CODE,
855 MIN(CASE WHEN BUS.MTH_ENTITY = 14 THEN BUS.TAG_DATA ELSE NULL END) RECIPE_NUM,
856 MIN(CASE WHEN BUS.MTH_ENTITY = 15 THEN BUS.TAG_DATA ELSE NULL END) RECIPE_VERSION,
857 MIN(CASE WHEN BUS.MTH_ENTITY = 14 THEN BUS.TAG_CODE ELSE NULL END) RECIPE_NUM_TAG_CODE,
858 MIN(CASE WHEN BUS.MTH_ENTITY = 15 THEN BUS.TAG_CODE ELSE NULL END) RECIPE_VERSION_TAG_CODE,
859 BUS.GROUP_ID
860 FROM
861 (SELECT
862 STG.GROUP_ID,
863 STG.READING_TIME,
864 STG.TAG_CODE,
865 STG.TAG_DATA,
866 STG.MTH_ENTITY,
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
876 STG.MTH_ENTITY IN ( 2 , 3 , 4 , 14 , 15 ) ) BUS
877 GROUP BY
878 BUS.GROUP_ID, BUS.EQUIPMENT_FK_KEY ) BUS_CONTEXT ,MTH_PLANTS_D PLANTS , MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS , MTH_HOUR_D HOURS ,MTH_EQUIPMENTS_D EQUIPMENTS
879 WHERE
880 TAG_DATA.GROUP_ID = BUS_CONTEXT.GROUP_ID (+) And
881 TAG_DATA.EQUIPMENT_FK_KEY = BUS_CONTEXT.EQUIPMENT_FK_KEY (+) And
882 TAG_DATA.PLANT_FK_KEY = PLANTS.PLANT_PK_KEY (+) And
883 TAG_DATA.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY (+) AND
884 TAG_DATA.READING_TIME >= EQP_SHIFTS.FROM_DATE (+)
885 AND TAG_DATA.READING_TIME <= EQP_SHIFTS.TO_DATE (+)
886 AND TAG_DATA.READING_TIME >= HOURS.FROM_TIME(+)
887 AND TAG_DATA.READING_TIME <= HOURS.TO_TIME(+)
888
889 /*( TAG_DATA.READING_TIME BETWEEN EQP_SHIFTS.FROM_DATE and
890 EQP_SHIFTS.TO_DATE and
891 EQP_SHIFTS.FROM_DATE is not null and
892 EQP_SHIFTS.TO_DATE is not null
893 or
894 EQP_SHIFTS.FROM_DATE is null and
895 EQP_SHIFTS.TO_DATE is null ) And
896 ( TAG_DATA.READING_TIME BETWEEN HOURS.FROM_TIME (+) and
897 HOURS.TO_TIME (+))*/ AND
898 TAG_DATA.EQUIPMENT_FK_KEY = EQUIPMENTS.EQUIPMENT_PK_KEY (+)
899 --AND EQP_SHIFTS.FROM_DATE IS NOT NULL AND
900 -- EQP_SHIFTS.TO_DATE IS NOT NULL AND
901 -- EQP_SHIFTS.FROM_DATE != EQP_SHIFTS.TO_DATE
902 )TAG_DATA1,MTH_PRODUCTION_SCHEDULES_F PROD_SCHD,
903 MTH_PRODUCTION_SEGMENTS_F PROD_SEG, MTH_ITEMS_D ITEMS , MTH_CONTEXTUALIZATION_REQ MCR
904 WHERE
905 TAG_DATA1.WORKORDER_PK = PROD_SCHD.WORKORDER_PK (+) And
906 TAG_DATA1.SEGMENT_PK = PROD_SEG.SEGMENT_PK (+) And
907 TAG_DATA1.ITEM_PK = ITEMS.ITEM_PK (+) AND
908 TAG_DATA1.EQUIPMENT_FK_KEY = MCR.EQUIPMENT_FK_KEY (+)
909 AND( TAG_DATA1.SHIFT_WORKDAY_FK_KEY IS NULL Or
910 TAG_DATA1.HOUR_FK_KEY IS NULL Or
911 TAG_DATA1.STATUS IS NULL Or
912 TAG_DATA1.STATUS <> 'ACTIVE' Or
913 PROD_SCHD.WORKORDER_PK_KEY IS NULL Or
914 PROD_SEG.SEGMENT_PK_KEY IS NULL Or
915 ITEMS.ITEM_PK_KEY IS NULL Or
916 (MCR.WO_REQUIRED = 'Y' AND
917 TO_CHAR(PROD_SCHD.WORKORDER_PK_KEY) =TO_CHAR(MTH_UTIL_PKG.MTH_UA_GET_VAL())) Or
918 (MCR.WO_SEGMENT_REQUIRED = 'Y' AND
922 )
919 TO_CHAR(PROD_SEG.SEGMENT_PK_KEY) = TO_CHAR(MTH_UTIL_PKG.MTH_UA_GET_VAL())) Or
920 (MCR.ITEM_REQUIRED = 'Y' AND
921 TO_CHAR(ITEMS.ITEM_PK_KEY) = TO_CHAR(MTH_UTIL_PKG.MTH_UA_GET_VAL()))
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
930 INTO
931 MTH_TAG_READINGS
932 (GROUP_ID,
933 READING_TIME,
934 TAG_CODE,
935 TAG_DATA,
936 MTH_ENTITY,
937 ATTRIBUTE_GROUP,
938 ATTRIBUTE,
939 EQUIPMENT_FK_KEY,
940 WORKORDER_FK_KEY,
941 SEGMENT_FK_KEY,
942 SHIFT_WORKDAY_FK_KEY,
943 ITEM_FK_KEY,
944 HOUR_FK_KEY,
945 PROCESSED_FLAG,
946 CREATION_DATE,
947 LAST_UPDATE_DATE,
948 CREATION_SYSTEM_ID,
949 LAST_UPDATE_SYSTEM_ID,
950 PLANT_FK_KEY,
951 USER_ATTR1,
952 USER_ATTR2,
953 USER_ATTR3,
954 USER_ATTR4,
955 USER_ATTR5,
956 USER_MEASURE1,
957 USER_MEASURE2,
958 USER_MEASURE3,
959 USER_MEASURE4,
960 USER_MEASURE5,
961 QUALITY_FLAG,
962 RECIPE_NUM,
963 RECIPE_VERSION)
964 ( SELECT
965 TAG_DATA1.GROUP_ID ,
966 TAG_DATA1.READING_TIME,
967 TAG_DATA1.TAG_CODE,
968 TAG_DATA1.TAG_DATA,
969 TAG_DATA1.MTH_ENTITY,
970 TAG_DATA1.ATTRIBUTE_GROUP,
971 TAG_DATA1.ATTRIBUTE,
972 TAG_DATA1.EQUIPMENT_FK_KEY,
973 PROD_SCHD.WORKORDER_PK_KEY,
974 PROD_SEG.SEGMENT_PK_KEY,
975 TAG_DATA1.SHIFT_WORKDAY_FK_KEY,
976 ITEMS.ITEM_PK_KEY,
977 TAG_DATA1.HOUR_FK_KEY,
978 TAG_DATA1.PROCESSED_FLAG,
979 SYSDATE,
980 SYSDATE,
981 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
982 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
983 TAG_DATA1.PLANT_FK_KEY,
984 TAG_DATA1.USER_ATTR1,
985 TAG_DATA1.USER_ATTR2,
986 TAG_DATA1.USER_ATTR3,
987 TAG_DATA1.USER_ATTR4,
988 TAG_DATA1.USER_ATTR5,
989 TAG_DATA1.USER_MEASURE1,
990 TAG_DATA1.USER_MEASURE2,
991 TAG_DATA1.USER_MEASURE3,
992 TAG_DATA1.USER_MEASURE4,
993 TAG_DATA1.USER_MEASURE5,
994 TAG_DATA1.QUALITY_FLAG,
995 TAG_DATA1.RECIPE_NUM,
996 TAG_DATA1.RECIPE_VERSION
997 FROM
998 (SELECT
999 case when BUS_CONTEXT.WORKORDER is null
1000 then TO_CHAR( MTH_UTIL_PKG.MTH_UA_GET_VAL() )
1001 else BUS_CONTEXT.WORKORDER || '-' || PLANTS.PLANT_PK
1002 END WORKORDER_PK,
1003 case when BUS_CONTEXT.ITEM is null
1004 then TO_CHAR( MTH_UTIL_PKG.MTH_UA_GET_VAL() )
1005 else BUS_CONTEXT.ITEM || '-' || PLANTS.PLANT_PK
1006 END ITEM_PK,
1007 case when (BUS_CONTEXT.SEGMENT1 is null or BUS_CONTEXT.WORKORDER is null)
1008 then TO_CHAR( MTH_UTIL_PKG.MTH_UA_GET_VAL() )
1009 else BUS_CONTEXT.SEGMENT1 || '-' || BUS_CONTEXT.WORKORDER || '-' || PLANTS.PLANT_PK
1010 END SEGMENT_PK,
1011 TAG_DATA.GROUP_ID,
1012 TAG_DATA.READING_TIME,
1013 TAG_DATA.TAG_CODE,
1014 TAG_DATA.MTH_ENTITY,
1015 TAG_DATA.ATTRIBUTE_GROUP,
1016 TAG_DATA.ATTRIBUTE,
1017 TAG_DATA.EQUIPMENT_FK_KEY,
1018 TAG_DATA.TAG_DATA,
1019 EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY,
1020 BUS_CONTEXT.WORKORDER_TAG_CODE,
1021 BUS_CONTEXT.SEGMENT_TAG_CODE,
1022 BUS_CONTEXT.ITEM_TAG_CODE,
1023 BUS_CONTEXT.WORKORDER,
1024 BUS_CONTEXT.SEGMENT1,
1025 BUS_CONTEXT.ITEM,
1026 TAG_DATA.CONTEXTUALIZE_METHOD,
1027 TAG_DATA.PLANT_FK_KEY,
1028 TAG_DATA.PROCESSED_FLAG,
1029 TAG_DATA.USER_ATTR1,
1030 TAG_DATA.USER_ATTR2,
1031 TAG_DATA.USER_ATTR3,
1032 TAG_DATA.USER_ATTR4,
1033 TAG_DATA.USER_ATTR5,
1034 TAG_DATA.USER_MEASURE1,
1035 TAG_DATA.USER_MEASURE2,
1036 TAG_DATA.USER_MEASURE3,
1037 TAG_DATA.USER_MEASURE4,
1038 TAG_DATA.USER_MEASURE5,
1039 TAG_DATA.QUALITY_FLAG,
1040 BUS_CONTEXT.RECIPE_NUM,
1041 BUS_CONTEXT.RECIPE_VERSION,
1042 BUS_CONTEXT.RECIPE_NUM_TAG_CODE,
1043 BUS_CONTEXT.RECIPE_VERSION_TAG_CODE,
1044 HOURS.HOUR_PK_KEY HOUR_FK_KEY,
1045 EQUIPMENTS.STATUS
1046 FROM
1047 ( SELECT
1048 STG.GROUP_ID,
1049 STG.READING_TIME,
1050 STG.TAG_CODE,
1051 STG.TAG_DATA,
1052 STG.MTH_ENTITY,
1053 STG.ATTRIBUTE_GROUP,
1054 STG.ATTRIBUTE,
1055 STG.EQUIPMENT_FK_KEY,
1056 MCM.CONTEXTUALIZE_METHOD,
1057 STG.PLANT_FK_KEY,
1058 NVL(STG.PROCESSED_FLAG , 0)PROCESSED_FLAG,
1059 STG.USER_ATTR1,
1060 STG.USER_ATTR2,
1061 STG.USER_ATTR3,
1062 STG.USER_ATTR4,
1063 STG.USER_ATTR5,
1064 STG.USER_MEASURE1,
1065 STG.USER_MEASURE2,
1066 STG.USER_MEASURE3,
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 ) )
1076 ) TAG_DATA, ( SELECT
1077 MIN(CASE WHEN BUS.MTH_ENTITY = 2 THEN BUS.READING_TIME ELSE NULL END) READING_TIME,
1078 EQUIPMENT_FK_KEY,
1079 MIN(CASE WHEN BUS.MTH_ENTITY = 2 THEN BUS.TAG_DATA ELSE NULL END) WORKORDER,
1080 MIN(CASE WHEN BUS.MTH_ENTITY = 3 THEN BUS.TAG_DATA ELSE NULL END) SEGMENT1,
1081 MIN(CASE WHEN BUS.MTH_ENTITY = 4 THEN BUS.TAG_DATA ELSE NULL END) ITEM,
1082 MIN(CASE WHEN BUS.MTH_ENTITY = 2 THEN BUS.TAG_CODE ELSE NULL END) WORKORDER_TAG_CODE,
1083 MIN(CASE WHEN BUS.MTH_ENTITY = 3 THEN BUS.TAG_CODE ELSE NULL END) SEGMENT_TAG_CODE,
1087 MIN(CASE WHEN BUS.MTH_ENTITY = 14 THEN BUS.TAG_CODE ELSE NULL END) RECIPE_NUM_TAG_CODE,
1084 MIN(CASE WHEN BUS.MTH_ENTITY = 4 THEN BUS.TAG_CODE ELSE NULL END) ITEM_TAG_CODE,
1085 MIN(CASE WHEN BUS.MTH_ENTITY = 14 THEN BUS.TAG_DATA ELSE NULL END) RECIPE_NUM,
1086 MIN(CASE WHEN BUS.MTH_ENTITY = 15 THEN BUS.TAG_DATA ELSE NULL END) RECIPE_VERSION,
1088 NULL RECIPE_VERSION_TAG_CODE,
1089 BUS.GROUP_ID
1090 FROM
1091 (SELECT
1092 STG.GROUP_ID,
1093 STG.READING_TIME,
1094 STG.TAG_CODE,
1095 STG.TAG_DATA,
1096 STG.MTH_ENTITY,
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
1106 STG.MTH_ENTITY IN ( 2 , 3 , 4 , 14 , 15 ) ) BUS
1107 GROUP BY
1108 BUS.GROUP_ID, BUS.EQUIPMENT_FK_KEY ) BUS_CONTEXT ,MTH_PLANTS_D PLANTS , MTH_EQUIPMENT_SHIFTS_D EQP_SHIFTS , MTH_HOUR_D HOURS ,MTH_EQUIPMENTS_D EQUIPMENTS
1109 WHERE
1110 TAG_DATA.GROUP_ID = BUS_CONTEXT.GROUP_ID (+) And
1111 TAG_DATA.EQUIPMENT_FK_KEY = BUS_CONTEXT.EQUIPMENT_FK_KEY (+) And
1112 TAG_DATA.PLANT_FK_KEY = PLANTS.PLANT_PK_KEY (+) And
1113 TAG_DATA.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY (+) And
1114 ( TAG_DATA.READING_TIME BETWEEN EQP_SHIFTS.FROM_DATE and
1115 EQP_SHIFTS.TO_DATE and
1116 EQP_SHIFTS.FROM_DATE is not null and
1117 EQP_SHIFTS.TO_DATE is not null
1118 or
1119 EQP_SHIFTS.FROM_DATE is null and
1120 EQP_SHIFTS.TO_DATE is null ) And
1121 ( TAG_DATA.READING_TIME BETWEEN HOURS.FROM_TIME (+) and
1122 HOURS.TO_TIME (+)) AND
1123 TAG_DATA.EQUIPMENT_FK_KEY = EQUIPMENTS.EQUIPMENT_PK_KEY (+)
1124 AND EQP_SHIFTS.FROM_DATE IS NOT NULL AND
1125 EQP_SHIFTS.TO_DATE IS NOT NULL AND
1126 EQP_SHIFTS.FROM_DATE <> EQP_SHIFTS.TO_DATE )TAG_DATA1,MTH_PRODUCTION_SCHEDULES_F PROD_SCHD,
1127 MTH_PRODUCTION_SEGMENTS_F PROD_SEG, MTH_ITEMS_D ITEMS , MTH_CONTEXTUALIZATION_REQ MCR
1128 WHERE
1129 TAG_DATA1.WORKORDER_PK = PROD_SCHD.WORKORDER_PK (+) And
1130 TAG_DATA1.SEGMENT_PK = PROD_SEG.SEGMENT_PK (+) And
1131 TAG_DATA1.ITEM_PK = ITEMS.ITEM_PK (+) AND
1132 TAG_DATA1.EQUIPMENT_FK_KEY = MCR.EQUIPMENT_FK_KEY (+)
1133 AND NOT( TAG_DATA1.SHIFT_WORKDAY_FK_KEY IS NULL Or
1134 TAG_DATA1.HOUR_FK_KEY IS NULL Or
1135 TAG_DATA1.STATUS IS NULL Or
1136 TAG_DATA1.STATUS <> 'ACTIVE' Or
1137 PROD_SCHD.WORKORDER_PK_KEY IS NULL Or
1138 PROD_SEG.SEGMENT_PK_KEY IS NULL Or
1139 ITEMS.ITEM_PK_KEY IS NULL Or
1140 (MCR.WO_REQUIRED = 'Y' AND
1141 TO_CHAR(PROD_SCHD.WORKORDER_PK_KEY) =TO_CHAR(MTH_UTIL_PKG.MTH_UA_GET_VAL())) Or
1142 (MCR.WO_SEGMENT_REQUIRED = 'Y' AND
1143 TO_CHAR(PROD_SEG.SEGMENT_PK_KEY) = TO_CHAR(MTH_UTIL_PKG.MTH_UA_GET_VAL())) Or
1144 (MCR.ITEM_REQUIRED = 'Y' AND
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
1154 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_READINGS', mth_util_pkg.G_DBG_EXCEPTION);
1155 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1156 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1157 v_ret_code:=2;
1158 RETURN v_ret_code;
1159
1160
1161 END MTH_TAG_BASED_CONTEXTUALIZE;
1162 /*******************************************************************************
1163 * Function :MTH_SCHD_BASED_CONTEXTUALIZE *
1164 * Description :This function is used to push the data from readings staging
1165 to readings or error table *
1166 * File Name :MTHTAGB.PLS *
1167 * Visibility :Private *
1168 * Parameters : *
1169 *******************************************************************************/
1170 FUNCTION MTH_SCHD_BASED_CONTEXTUALIZE RETURN NUMBER
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,
1180 ATTRIBUTE_GROUP,
1181 ATTRIBUTE,
1182 EQUIPMENT_FK_KEY,
1183 WORKORDER_FK_KEY,
1184 SEGMENT_FK_KEY,
1185 SHIFT_WORKDAY_FK_KEY,
1186 ITEM_FK_KEY,
1187 HOUR_FK_KEY,
1188 RE_PROCESS_FLAG,
1189 PLANT_FK_KEY,
1190 USER_ATTR1,
1191 USER_ATTR2,
1192 USER_ATTR3,
1193 USER_ATTR4,
1194 USER_ATTR5,
1195 USER_MEASURE1,
1196 USER_MEASURE2,
1197 USER_MEASURE3,
1198 USER_MEASURE4,
1199 USER_MEASURE5,
1200 QUALITY_FLAG,
1201 PROCESSED_FLAG,
1202 EQUIPMENT_STATUS)
1206 STG.TAG_DATA,
1203 (SELECT STG.GROUP_ID,
1204 STG.READING_TIME,
1205 STG.TAG_CODE,
1207 STG.MTH_ENTITY,
1208 STG.ATTRIBUTE_GROUP,
1209 STG.ATTRIBUTE,
1210 STG.EQUIPMENT_FK_KEY,
1211 EQUIP_PROD_SCHEDULE.WORKORDER_FK_KEY,
1212 EQUIP_PROD_SCHEDULE.SEGMENT_FK_KEY,
1213 EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY,
1214 EQUIP_PROD_SCHEDULE.ITEM_FK_KEY,
1215 HOURS.HOUR_PK_KEY,
1216 'N',
1217 STG.PLANT_FK_KEY,
1218 STG.USER_ATTR1,
1219 STG.USER_ATTR2,
1220 STG.USER_ATTR3,
1221 STG.USER_ATTR4,
1222 STG.USER_ATTR5,
1223 STG.USER_MEASURE1,
1224 STG.USER_MEASURE2,
1225 STG.USER_MEASURE3,
1226 STG.USER_MEASURE4,
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 (+)
1236 -- AND EQP_SHIFTS.FROM_DATE IS NOT NULL
1237 -- AND EQP_SHIFTS.TO_DATE IS NOT NULL
1238 -- AND EQP_SHIFTS.FROM_DATE != EQP_SHIFTS.TO_DATE
1239 AND STG.EQUIPMENT_FK_KEY = EQUIP_PROD_SCHEDULE.EQUIPMENT_FK_KEY (+)
1240 AND STG.EQUIPMENT_FK_KEY = EQUIPMENTS.EQUIPMENT_PK_KEY (+)
1241 -- AND (STG.READING_TIME between EQUIP_PROD_SCHEDULE.SCHEDULE_FROM_DATE AND EQUIP_PROD_SCHEDULE.SCHEDULE_TO_DATE
1242 -- AND EQUIP_PROD_SCHEDULE.SCHEDULE_FROM_DATE is not null
1243 -- AND EQUIP_PROD_SCHEDULE.SCHEDULE_TO_DATE is not null
1244 -- OR ( EQUIP_PROD_SCHEDULE.SCHEDULE_FROM_DATE is null AND EQUIP_PROD_SCHEDULE.SCHEDULE_TO_DATE is null ))
1245 AND STG.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY (+)
1246 /* AND (STG.READING_TIME between EQP_SHIFTS.FROM_DATE AND EQP_SHIFTS.TO_DATE
1247 AND EQP_SHIFTS.FROM_DATE is not null
1248 AND EQP_SHIFTS.TO_DATE is not null
1249 OR (EQP_SHIFTS.FROM_DATE is null AND EQP_SHIFTS.TO_DATE is null) )
1250 AND ( STG.READING_TIME between HOURS.FROM_TIME (+) AND HOURS.TO_TIME (+) ) */
1251 AND STG.READING_TIME >= EQUIP_PROD_SCHEDULE.SCHEDULE_FROM_DATE (+)
1252 AND STG.READING_TIME <= EQUIP_PROD_SCHEDULE.SCHEDULE_TO_DATE (+)
1253
1254
1255 AND STG.READING_TIME >= EQP_SHIFTS.FROM_DATE (+)
1256 AND STG.READING_TIME <= EQP_SHIFTS.TO_DATE (+)
1257 AND STG.READING_TIME >= HOURS.FROM_TIME(+)
1258 AND STG.READING_TIME <= HOURS.TO_TIME(+)
1259
1260 AND EQUIP_PROD_SCHEDULE.WORKORDER_FK_KEY = PROD_SCHD.WORKORDER_PK_KEY(+)
1261 AND STG.EQUIPMENT_FK_KEY = MCR.EQUIPMENT_FK_KEY (+)
1262 AND ( NOT (( EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY IS NOT NULL
1263 AND HOURS.HOUR_PK_KEY IS NOT NULL
1264 AND (EQUIP_PROD_SCHEDULE.WORKORDER_FK_KEY IS NOT NULL Or UPPER(MCR.WO_REQUIRED) = 'N' )
1265 AND (EQUIP_PROD_SCHEDULE.SEGMENT_FK_KEY IS NOT NULL Or UPPER(MCR.WO_SEGMENT_REQUIRED ) = 'N' )
1266 AND (EQUIP_PROD_SCHEDULE.ITEM_FK_KEY IS NOT NULL Or UPPER( MCR.ITEM_REQUIRED) = 'N')
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
1276 INTO MTH_TAG_READINGS
1277 (GROUP_ID,
1278 READING_TIME,
1279 TAG_CODE,
1280 TAG_DATA,
1281 MTH_ENTITY,
1282 ATTRIBUTE_GROUP,
1283 ATTRIBUTE,
1284 EQUIPMENT_FK_KEY,
1285 WORKORDER_FK_KEY,
1286 SEGMENT_FK_KEY,
1287 SHIFT_WORKDAY_FK_KEY,
1288 ITEM_FK_KEY,
1289 HOUR_FK_KEY,
1290 PROCESSED_FLAG,
1294 LAST_UPDATE_SYSTEM_ID,
1291 CREATION_DATE,
1292 LAST_UPDATE_DATE,
1293 CREATION_SYSTEM_ID,
1295 PLANT_FK_KEY,
1296 USER_ATTR1,
1297 USER_ATTR2,
1298 USER_ATTR3,
1299 USER_ATTR4,
1300 USER_ATTR5,
1301 USER_MEASURE1,
1302 USER_MEASURE2,
1303 USER_MEASURE3,
1304 USER_MEASURE4,
1305 USER_MEASURE5,
1306 QUALITY_FLAG,
1307 RECIPE_NUM,
1308 RECIPE_VERSION)
1309 (SELECT STG.GROUP_ID,
1310 STG.READING_TIME,
1311 STG.TAG_CODE,
1312 STG.TAG_DATA,
1313 STG.MTH_ENTITY,
1314 STG.ATTRIBUTE_GROUP,
1315 STG.ATTRIBUTE,
1316 STG.EQUIPMENT_FK_KEY,
1317 EQUIP_PROD_SCHEDULE.WORKORDER_FK_KEY,
1318 EQUIP_PROD_SCHEDULE.SEGMENT_FK_KEY,
1319 EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY,
1320 EQUIP_PROD_SCHEDULE.ITEM_FK_KEY,
1321 HOURS.HOUR_PK_KEY,
1322 NVL(STG.PROCESSED_FLAG , 0),
1323 SYSDATE,
1324 SYSDATE,
1325 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1326 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1327 STG.PLANT_FK_KEY,
1328 STG.USER_ATTR1,
1329 STG.USER_ATTR2,
1330 STG.USER_ATTR3,
1331 STG.USER_ATTR4,
1332 STG.USER_ATTR5,
1333 STG.USER_MEASURE1,
1334 STG.USER_MEASURE2,
1335 STG.USER_MEASURE3,
1336 STG.USER_MEASURE4,
1337 STG.USER_MEASURE5,
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 (+)
1347 AND EQP_SHIFTS.FROM_DATE IS NOT NULL
1348 AND EQP_SHIFTS.TO_DATE IS NOT NULL
1349 AND EQP_SHIFTS.FROM_DATE <> EQP_SHIFTS.TO_DATE
1350 AND STG.EQUIPMENT_FK_KEY = EQUIP_PROD_SCHEDULE.EQUIPMENT_FK_KEY (+)
1351 AND STG.EQUIPMENT_FK_KEY = EQUIPMENTS.EQUIPMENT_PK_KEY (+)
1352 AND (STG.READING_TIME between EQUIP_PROD_SCHEDULE.SCHEDULE_FROM_DATE AND EQUIP_PROD_SCHEDULE.SCHEDULE_TO_DATE
1353 AND EQUIP_PROD_SCHEDULE.SCHEDULE_FROM_DATE is not null
1354 AND EQUIP_PROD_SCHEDULE.SCHEDULE_TO_DATE is not null
1355 OR ( EQUIP_PROD_SCHEDULE.SCHEDULE_FROM_DATE is null AND EQUIP_PROD_SCHEDULE.SCHEDULE_TO_DATE is null ))
1356 AND STG.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY (+)
1357 AND (STG.READING_TIME between EQP_SHIFTS.FROM_DATE AND EQP_SHIFTS.TO_DATE
1358 AND EQP_SHIFTS.FROM_DATE is not null
1359 AND EQP_SHIFTS.TO_DATE is not null
1360 OR (EQP_SHIFTS.FROM_DATE is null AND EQP_SHIFTS.TO_DATE is null) )
1361 AND ( STG.READING_TIME between HOURS.FROM_TIME (+) AND HOURS.TO_TIME (+) )
1362 AND EQUIP_PROD_SCHEDULE.WORKORDER_FK_KEY = PROD_SCHD.WORKORDER_PK_KEY(+)
1363 AND STG.EQUIPMENT_FK_KEY = MCR.EQUIPMENT_FK_KEY (+)
1364 AND EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY IS NOT NULL
1365 AND HOURS.HOUR_PK_KEY IS NOT NULL
1366 AND (EQUIP_PROD_SCHEDULE.WORKORDER_FK_KEY IS NOT NULL Or UPPER(MCR.WO_REQUIRED) = 'N' )
1367 AND (EQUIP_PROD_SCHEDULE.SEGMENT_FK_KEY IS NOT NULL Or UPPER(MCR.WO_SEGMENT_REQUIRED ) = 'N' )
1368 AND (EQUIP_PROD_SCHEDULE.ITEM_FK_KEY IS NOT NULL Or UPPER( MCR.ITEM_REQUIRED) = 'N')
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
1378 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_READINGS', mth_util_pkg.G_DBG_EXCEPTION);
1379 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1380 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1381 v_ret_code:=2;
1382 RETURN v_ret_code;
1383
1384
1385 END MTH_SCHD_BASED_CONTEXTUALIZE;
1386
1387 /*******************************************************************************
1388 * Function : MTH_MANUAL_BASED_CONTEXTUALIZE *
1392 * Visibility :Private *
1389 * Description :This function is used to push the data from readings staging
1390 to readings or error table *
1391 * File Name :MTHTAGB.PLS *
1393 * Parameters : *
1394 *******************************************************************************/
1395 FUNCTION MTH_MANUAL_BASED_CONTEXTUALIZE RETURN NUMBER
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,
1405 MTH_ENTITY,
1406 ATTRIBUTE_GROUP,
1407 ATTRIBUTE,
1408 EQUIPMENT_FK_KEY,
1409 WORKORDER_FK_KEY,
1410 SEGMENT_FK_KEY,
1411 SHIFT_WORKDAY_FK_KEY,
1412 ITEM_FK_KEY,
1413 HOUR_FK_KEY,
1414 PROCESSED_FLAG,
1415 CREATION_DATE,
1416 LAST_UPDATE_DATE,
1417 CREATION_SYSTEM_ID,
1418 LAST_UPDATE_SYSTEM_ID,
1419 PLANT_FK_KEY,
1420 USER_ATTR1,
1421 USER_ATTR2,
1422 USER_ATTR3,
1423 USER_ATTR4,
1424 USER_ATTR5,
1425 USER_MEASURE1,
1426 USER_MEASURE2,
1427 USER_MEASURE3,
1428 USER_MEASURE4,
1429 USER_MEASURE5,
1430 QUALITY_FLAG)
1431 (SELECT STG.GROUP_ID,
1432 STG.READING_TIME,
1433 STG.TAG_CODE,
1434 STG.TAG_DATA,
1435 STG.MTH_ENTITY,
1436 STG.ATTRIBUTE_GROUP,
1437 STG.ATTRIBUTE,
1438 STG.EQUIPMENT_FK_KEY,
1439 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1440 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1441 EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY,
1442 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1443 HOURS.HOUR_PK_KEY,
1444 NVL(STG.PROCESSED_FLAG , 0),
1445 SYSDATE,
1446 SYSDATE,
1447 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1448 MTH_UTIL_PKG.MTH_UA_GET_VAL(),
1449 STG.PLANT_FK_KEY,
1450 STG.USER_ATTR1,
1451 STG.USER_ATTR2,
1452 STG.USER_ATTR3,
1453 STG.USER_ATTR4,
1454 STG.USER_ATTR5,
1455 STG.USER_MEASURE1,
1456 STG.USER_MEASURE2,
1457 STG.USER_MEASURE3,
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
1467 AND EQP_SHIFTS.FROM_DATE IS NOT NULL
1468 AND EQP_SHIFTS.TO_DATE IS NOT NULL
1469 AND EQP_SHIFTS.FROM_DATE <> EQP_SHIFTS.To_Date
1470 AND STG.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY (+)
1471 AND STG.READING_TIME >= EQP_SHIFTS.FROM_DATE (+)
1472 AND STG.READING_TIME <= EQP_SHIFTS.TO_DATE (+)
1473 AND STG.READING_TIME >= HOURS.FROM_TIME(+)
1474 AND STG.READING_TIME <= HOURS.TO_TIME(+)
1475 AND STG.EQUIPMENT_FK_KEY = CONTEXT_REQ.EQUIPMENT_FK_KEY (+)
1476 AND STG.EQUIPMENT_FK_KEY = EQUIPMENTS.EQUIPMENT_PK_KEY (+)
1477 AND (NOT (( EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY IS NULL
1478 OR HOURS.HOUR_PK_KEY IS NULL
1479 OR CONTEXT_REQ.WO_REQUIRED = 'Y'
1480 OR CONTEXT_REQ.WO_SEGMENT_REQUIRED = 'Y'
1481 OR CONTEXT_REQ.ITEM_REQUIRED = 'Y'
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
1491 (GROUP_ID,
1492 READING_TIME,
1493 TAG_CODE,
1494 TAG_DATA,
1495 MTH_ENTITY,
1496 ATTRIBUTE_GROUP,
1497 ATTRIBUTE,
1498 EQUIPMENT_FK_KEY,
1499 SHIFT_WORKDAY_FK_KEY,
1500 HOUR_FK_KEY,
1501 RE_PROCESS_FLAG,
1502 PLANT_FK_KEY,
1503 USER_ATTR1,
1504 USER_ATTR2,
1505 USER_ATTR3,
1506 USER_ATTR4,
1510 USER_MEASURE3,
1507 USER_ATTR5,
1508 USER_MEASURE1,
1509 USER_MEASURE2,
1511 USER_MEASURE4,
1512 USER_MEASURE5,
1513 QUALITY_FLAG,
1514 PROCESSED_FLAG,
1515 EQUIPMENT_STATUS)
1516 (SELECT STG.GROUP_ID,
1517 STG.READING_TIME,
1518 STG.TAG_CODE,
1519 STG.TAG_DATA,
1520 STG.MTH_ENTITY,
1521 STG.ATTRIBUTE_GROUP,
1522 STG.ATTRIBUTE,
1523 STG.EQUIPMENT_FK_KEY,
1524 EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY,
1525 HOURS.HOUR_PK_KEY,
1526 'N',
1527 STG.PLANT_FK_KEY,
1528 STG.USER_ATTR1,
1529 STG.USER_ATTR2,
1530 STG.USER_ATTR3,
1531 STG.USER_ATTR4,
1532 STG.USER_ATTR5,
1533 STG.USER_MEASURE1,
1534 STG.USER_MEASURE2,
1535 STG.USER_MEASURE3,
1536 STG.USER_MEASURE4,
1537 STG.USER_MEASURE5,
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
1547 -- AND EQP_SHIFTS.FROM_DATE IS NOT NULL
1548 -- AND EQP_SHIFTS.TO_DATE IS NOT NULL
1549 -- AND EQP_SHIFTS.FROM_DATE != EQP_SHIFTS.To_Date
1550 AND STG.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY (+)
1551 AND STG.READING_TIME >= EQP_SHIFTS.FROM_DATE (+)
1552 AND STG.READING_TIME <= EQP_SHIFTS.TO_DATE (+)
1553 AND STG.READING_TIME >= HOURS.FROM_TIME(+)
1554 AND STG.READING_TIME <= HOURS.TO_TIME(+)
1555 AND STG.EQUIPMENT_FK_KEY = CONTEXT_REQ.EQUIPMENT_FK_KEY (+)
1556 AND STG.EQUIPMENT_FK_KEY = EQUIPMENTS.EQUIPMENT_PK_KEY (+)
1557 AND ( EQP_SHIFTS.SHIFT_WORKDAY_FK_KEY IS NULL
1558 OR HOURS.HOUR_PK_KEY IS NULL
1559 OR CONTEXT_REQ.WO_REQUIRED = 'Y'
1560 OR CONTEXT_REQ.WO_SEGMENT_REQUIRED = 'Y'
1561 OR CONTEXT_REQ.ITEM_REQUIRED = 'Y'
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
1571 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_READINGS', mth_util_pkg.G_DBG_EXCEPTION);
1572 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1573 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1574 v_ret_code:=2;
1575 RETURN v_ret_code;
1576
1577
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 *
1587 * :p_from_tz (value for from time zone ) *
1588 * :p_to_tz (value for to time zone ) *
1589 **************************************************************************** */
1590
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,
1600 TAG_DATA,
1601 CREATION_DATE,
1602 USER_ATTR1,
1603 USER_ATTR2,
1604 USER_ATTR3,
1605 USER_ATTR4,
1606 USER_ATTR5,
1607 USER_MEASURE1,
1608 USER_MEASURE2,
1609 USER_MEASURE3,
1610 USER_MEASURE4,
1611 USER_MEASURE5,
1612 QUALITY_FLAG)
1616 r.TAG_DATA,
1613 ( SELECT r.GROUP_ID,
1614 r.READING_TIME,
1615 r.TAG_CODE,
1617 r.CREATION_DATE,
1618 r.USER_ATTR1,
1619 r.USER_ATTR2,
1620 r.USER_ATTR3,
1621 r.USER_ATTR4,
1622 r.USER_ATTR5,
1623 r.USER_MEASURE1,
1624 r.USER_MEASURE2,
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
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<a.reading_time
1641 AND latest.tag_code=a.tag_code
1642 ) ;
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 ,
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
1657 FROM mth_tag_readings_latest l
1658 WHERE rraw.tag_code = l.tag_code)
1659
1660 ) ;
1661 p_ret_code := 0;
1662
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 mth_util_pkg.log_msg('Exception OTHERS in INIT_READINGS_FROM_RAW', mth_util_pkg.G_DBG_EXCEPTION);
1666 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1667 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1668 p_ret_code := 2;
1669
1670
1671
1672
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 **************************************************************************** */
1682
1683 PROCEDURE MTH_GEN_HDL_EQP_DOWN_EVENT IS
1684 v_previous_tag_reading_data VARCHAR2(255) ;
1685 cursor c_readings is select readings.*,eqp_shifts.shift_workday_fk_key
1686 from MTH_EQUIPMENT_SHIFTS_D eqp_shifts,
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
1696 READINGS.TAG_DATA = '3' And
1697 READINGS.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY And
1698 READINGS.READING_TIME BETWEEN EQP_SHIFTS.FROM_DATE AND EQP_SHIFTS.TO_DATE And
1699 EQP_SHIFTS.AVAILABILITY_FLAG = 'Y' and
1700 (prev_status IS NULL OR prev_status <> '3');
1701
1702 r_readings c_readings%ROWTYPE ;
1703
1704 BEGIN
1705 open c_readings;
1706 loop
1707 fetch c_readings into r_readings ;
1708 exit when c_readings%NOTFOUND ;
1709 IF (r_readings.PREV_STATUS IS NULL) THEN
1710 v_previous_tag_reading_data := MTH_UTIL_PKG.get_prev_tag_reading(r_readings.TAG_CODE,r_readings.READING_TIME);
1711 IF (v_previous_tag_reading_data <>'3' OR v_previous_tag_reading_data IS NULL) THEN
1712 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'DOWN',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),'Equipment is Down');
1713 END IF;
1714 ELSE
1715 IF (r_readings.PREV_STATUS <> '3') THEN
1716 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'DOWN',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),'Equipment is Down');
1717 END IF;
1718 END IF;
1719 end loop;
1720 close c_readings ;
1721 END ;
1722
1723 /* ****************************************************************************
1724 * Procedure : MTH_HDL_EQP_OUT_OF_CTL_EVT *
1728 * Visibility :Private *
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 *
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;
1738 v_lower_limit NUMBER NULL;
1739 cursor c_readings is SELECT
1740 eagrps.ATTR_GROUP_ID,
1741 eagrps.APPLICATION_ID "EGO_ATTR.APPLICATION_ID" ,
1742 eagrps.ATTR_GROUP_TYPE "EGO_ATTR.ATTR_GROUP_TYPE",
1743 eagrps.ATTR_GROUP_NAME "EGO_ATTR.ATTR_GROUP_NAME",
1744 eattrs.ATTR_ID,
1745 eattrs.APPLICATION_ID,
1746 eattrs.ATTR_GROUP_TYPE,
1747 eattrs.ATTR_GROUP_NAME,
1748 eattrs.ATTR_NAME,
1749 stg.GROUP_ID,
1750 stg.READING_TIME,
1751 stg.TAG_CODE,
1752 stg.TAG_DATA,
1753 stg.MTH_ENTITY,
1754 stg.ATTRIBUTE_GROUP,
1755 stg.ATTRIBUTE,
1756 stg.EQUIPMENT_FK_KEY,
1757 stg.PLANT_FK_KEY,
1758 stg.QUALITY_FLAG,
1759 stg.PROCESSED_FLAG,
1760 tag_mstr.TAG_CODE "TAG_CODE_1",
1761 tag_mstr.EQUIPMENT_FK_KEY "EQUIPMENT_FK_KEY_1",
1762 tag_mstr.UOM
1763 FROM
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 )) )
1773 WHERE
1774 ( eagrps.APPLICATION_ID = 9001 ) AND
1775 ( LENGTH ( TRIM ( TRANSLATE ( stg.TAG_DATA , ' +-.0123456789' , ' ' ) ) ) IS NULL ) AND
1776 eattrs.ATTR_ID IS NOT NULL And
1777 eagrps.ATTR_GROUP_ID IS NOT NULL
1778 ORDER BY equipment_fk_key, tag_code, reading_time;
1779
1780 r_readings c_readings%ROWTYPE ;
1781
1782 BEGIN
1783 open c_readings;
1784 loop
1785 fetch c_readings into r_readings ;
1786 exit when c_readings%NOTFOUND ;
1787
1788 MTH_UTIL_PKG.get_upper_lower_limits(r_readings.EQUIPMENT_FK_KEY,r_readings.ATTR_NAME,'SPECIFICATIONS','LLIMIT','ULIMIT',v_lower_limit,v_upper_limit);
1789 MTH_UTIL_PKG.get_prev_tag_reading_info(r_readings.TAG_CODE,r_readings.READING_TIME,NULL,v_previous_tag_data,v_pre_reading_time,v_pre_eqp_avail);
1790
1791 IF((TO_NUMBER(r_readings.TAG_DATA) < v_lower_limit AND (TO_NUMBER(v_previous_tag_data) >= v_lower_limit OR v_previous_tag_data IS NULL))
1792 OR (TO_NUMBER(r_readings.TAG_DATA) > v_upper_limit AND ( v_previous_tag_data <= v_upper_limit OR v_previous_tag_data IS NULL)))THEN
1793 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'OUT_CTL',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),CASE WHEN TO_NUMBER( r_readings.TAG_DATA ) > v_upper_limit THEN
1794 'Equipment Parameter value for "' ||
1795 r_readings.ATTRIBUTE ||
1796 '" parameter is above Upper Control Limit, ' ||
1797 r_readings.TAG_CODE || ' = ' || r_readings.TAG_DATA || ', UCL = ' ||
1798 v_upper_limit
1799 WHEN TO_NUMBER( r_readings.TAG_DATA ) < v_lower_limit THEN
1800 'Equipment Parameter value for "' ||
1801 r_readings.ATTRIBUTE ||
1802 '" parameter is below Lower Control Limit, ' ||
1803 r_readings.TAG_CODE || ' = ' || r_readings.TAG_DATA || ', LCL = ' ||
1804 v_lower_limit
1805 ELSE NULL
1806 END);
1807 END IF;
1808
1809 v_previous_tag_code := r_readings.TAG_CODE ;
1810 v_previous_tag_data := r_readings.TAG_DATA ;
1811 end loop;
1812 close c_readings ;
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 **************************************************************************** */
1822
1823
1824 PROCEDURE MTH_GEN_HDL_CTL_LMT_WRNG_EVT IS
1825 v_verify_tag_data_trend NUMBER ;
1826 cursor c_readings is SELECT
1827 eagrps.ATTR_GROUP_ID,
1828 eagrps.APPLICATION_ID "EGO_ATTR.APPLICATION_ID" ,
1829 eagrps.ATTR_GROUP_TYPE "EGO_ATTR.ATTR_GROUP_TYPE",
1830 eagrps.ATTR_GROUP_NAME "EGO_ATTR.ATTR_GROUP_NAME",
1831 eattrs.ATTR_ID,
1832 eattrs.APPLICATION_ID,
1833 eattrs.ATTR_GROUP_TYPE,
1834 eattrs.ATTR_GROUP_NAME,
1835 eattrs.ATTR_NAME,
1836 stg.GROUP_ID,
1837 stg.READING_TIME,
1838 stg.TAG_CODE,
1839 stg.TAG_DATA,
1840 stg.MTH_ENTITY,
1841 stg.ATTRIBUTE_GROUP,
1842 stg.ATTRIBUTE,
1843 stg.EQUIPMENT_FK_KEY,
1844 stg.PLANT_FK_KEY,
1845 stg.QUALITY_FLAG,
1846 stg.PROCESSED_FLAG,
1847 tag_mstr.TAG_CODE "TAG_CODE_1",
1848 tag_mstr.EQUIPMENT_FK_KEY "EQUIPMENT_FK_KEY_1",
1849 tag_mstr.UOM
1850 FROM
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 )) )
1858 -- AND
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 ))
1859 (( stg.TAG_CODE = tag_mstr.TAG_CODE )) )
1860 WHERE
1861 ( eagrps.APPLICATION_ID = 9001 ) AND
1862 ( LENGTH ( TRIM ( TRANSLATE ( stg.TAG_DATA , ' +-.0123456789' , ' ' ) ) ) IS NULL )
1863 ORDER BY equipment_fk_key, tag_code, reading_time;
1864
1865 r_readings c_readings%ROWTYPE ;
1866 BEGIN
1867 open c_readings;
1868 loop
1869 fetch c_readings into r_readings ;
1870 exit when c_readings%NOTFOUND ;
1871 v_verify_tag_data_trend := MTH_UTIL_PKG.verify_tag_data_trend(r_readings.TAG_CODE,r_readings.TAG_DATA,r_readings.READING_TIME,'SPECIFICATIONS','MEAN',v_ctl_limit_threshold,NULL);
1872 IF(v_verify_tag_data_trend >0)THEN
1873 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'CTL_WG',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),CASE WHEN v_verify_tag_data_trend = 1 THEN
1874 v_ctl_limit_threshold || ' consecutive values of Equipment Parameter value for ' ||
1875 r_readings.attribute|| ' parameter is above mean value'
1876 WHEN v_verify_tag_data_trend = 2 THEN
1877 v_ctl_limit_threshold ||' consecutive values of Equipment Parameter value for ' ||
1878 r_readings.attribute || ' parameter is below mean value'
1879 ELSE NULL
1880 END);
1881 END IF ;
1882 end loop;
1883 close c_readings ;
1884 END ;
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 **************************************************************************** */
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
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,
1906 INGRP2.ATTRIBUTE_GROUP ATTRIBUTE_GROUP2, INGRP2.ATTRIBUTE ATTRIBUTE2, INGRP2.TAG_CODE TAG_CODE2,
1907 INGRP2.TAG_DATA TAG_DATA2, INGRP2.ATTR_NAME ATTR_NAME2,INGRP2.READING_TIME READING_TIME2 ,INGRP2.UOM UOM2
1908 from
1909 (SELECT EAV.ATTR_NAME ATTR_NAME,
1910 TRS.GROUP_ID GROUP_ID,
1911 TRS.TAG_CODE TAG_CODE,
1912 TRS.TAG_DATA TAG_DATA,
1913 TRS.ATTRIBUTE_GROUP ATTRIBUTE_GROUP,
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
1923 AND TRS.ATTRIBUTE = EAV.ATTR_ID
1924 --AND TRS.EQUIPMENT_FK_KEY = TM.EQUIPMENT_FK_KEY
1925 AND TRS.TAG_CODE = TM.TAG_CODE
1926 AND EAGV.APPLICATION_ID = 9001
1927 AND ( LENGTH ( TRIM ( TRANSLATE ( TRS.TAG_DATA , ' +-.0123456789' , ' ' ) ) ) IS NULL )
1928 AND EAV.ATTR_NAME = v_attr1) INGRP1,
1929 (SELECT EAV.ATTR_NAME ATTR_NAME,
1930 TRS.GROUP_ID GROUP_ID,
1931 TRS.TAG_CODE,
1932 TRS.TAG_DATA,
1933 TRS.ATTRIBUTE_GROUP,
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
1943 AND TRS.ATTRIBUTE = EAV.ATTR_ID
1944 --AND TRS.EQUIPMENT_FK_KEY = TM.EQUIPMENT_FK_KEY
1945 AND TRS.TAG_CODE = TM.TAG_CODE
1946 AND EAGV.APPLICATION_ID = 9001
1947 AND ( LENGTH ( TRIM ( TRANSLATE ( TRS.TAG_DATA , ' +-.0123456789' , ' ' ) ) ) IS NULL )
1948 AND EAV.ATTR_NAME = v_attr2) INGRP2
1949 where INGRP1.GROUP_ID = INGRP2.GROUP_ID
1950 AND INGRP1.ATTRIBUTE IS NOT NULL
1951 AND INGRP1.ATTRIBUTE_GROUP IS NOT NULL
1952 AND INGRP2.ATTRIBUTE IS NOT NULL
1953 AND INGRP2.ATTRIBUTE_GROUP IS NOT NULL
1954 AND INGRP1.EQUIPMENT_FK_KEY = INGRP2.EQUIPMENT_FK_KEY
1955 AND INGRP1.ATTRIBUTE <> INGRP2.ATTRIBUTE ;
1956
1957 r_readings c_readings%ROWTYPE ;
1958
1959 BEGIN
1960 open c_readings;
1961 loop
1962 fetch c_readings into r_readings ;
1963 exit when c_readings%NOTFOUND ;
1964 IF ((v_previous_tag_code1 IS NULL AND v_previous_tag_data1 IS NULL) AND (v_previous_tag_code2 IS NULL AND v_previous_tag_data2 IS NULL)AND (r_readings.TAG_DATA1> v_unstab_threshold1 AND r_readings.TAG_DATA2> v_unstab_threshold2 ) ) THEN
1965 MTH_UTIL_PKG.get_prev_tag_reading_set(r_readings.TAG_CODE1,r_readings.READING_TIME1,r_readings.TAG_CODE2,r_readings.READING_TIME2,NULL,p_pre_tag_data1,p_pre_tag_data2);
1969 r_readings.TAG_CODE1 || ' = ' || r_readings.TAG_DATA1 || ' ' ||
1966 IF( ((p_pre_tag_data1 <= v_unstab_threshold1 AND p_pre_tag_data2 <=v_unstab_threshold2) OR (p_pre_tag_data1 IS NULL AND p_pre_tag_data2 IS NULL)) AND(r_readings.TAG_DATA1> v_unstab_threshold1 AND r_readings.TAG_DATA2> v_unstab_threshold2 ))THEN
1967 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'UNSTAB',NULL,NULL,CASE WHEN r_readings.READING_TIME1 >= r_readings.READING_TIME2
1968 THEN r_readings.READING_TIME1 ELSE r_readings.READING_TIME2 END, NULL,NULL,'Equipment is unstable due to combination of parameter readings beyond limits, ' ||
1970 NVL( r_readings.UOM1 , '') || ', ' ||
1971 r_readings.TAG_CODE2 || ' = ' || r_readings.TAG_DATA2 || ' ' ||
1972 NVL( r_readings.UOM2 , '') );
1973 END IF;
1974 END IF;
1975 v_previous_tag_code1 := r_readings.TAG_CODE1 ;
1976 v_previous_tag_data1 :=r_readings.TAG_DATA1 ;
1977 v_previous_tag_code2 := r_readings.TAG_CODE2 ;
1978 v_previous_tag_data2 :=r_readings.TAG_DATA2 ;
1979 end loop;
1980 close c_readings ;
1981 END ;
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 **************************************************************************** */
1991
1992 PROCEDURE MTH_GEN_HDL_EQP_FAULT_EVENT IS
1993 v_previous_tag_reading_data VARCHAR2(255) ;
1994 cursor c_readings is select readings.*
1995 from MTH_EQUIPMENT_SHIFTS_D eqp_shifts,
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
2005 READINGS.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY And
2006 READINGS.READING_TIME BETWEEN EQP_SHIFTS.FROM_DATE AND EQP_SHIFTS.TO_DATE And
2007 EQP_SHIFTS.AVAILABILITY_FLAG = 'Y'
2008 AND (prev_tag_data IS NULL OR prev_tag_data <> v_EQP_FAULT_THRESHOLD) ;
2009 r_readings c_readings%ROWTYPE ;
2010
2011 BEGIN
2012 open c_readings;
2013 loop
2014 fetch c_readings into r_readings ;
2015 exit when c_readings%NOTFOUND ;
2016 IF (r_readings.PREV_TAG_DATA IS NULL) THEN
2017 v_previous_tag_reading_data := MTH_UTIL_PKG.get_prev_tag_reading(r_readings.TAG_CODE,r_readings.READING_TIME);
2018 IF (v_previous_tag_reading_data <> v_EQP_FAULT_THRESHOLD OR v_previous_tag_reading_data IS NULL) THEN
2019 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'FAULT',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),'Equipment fault. ' || r_readings.TAG_CODE || ' = ' || r_readings.TAG_DATA);
2020 END IF;
2021 ELSE
2022 IF (r_readings.PREV_TAG_DATA <> v_EQP_FAULT_THRESHOLD) THEN
2023 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'FAULT',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),'Equipment fault. ' || r_readings.TAG_CODE || ' = ' || r_readings.TAG_DATA);END IF;
2024 END IF;
2025 end loop;
2026 close c_readings ;
2027 END ;
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 **************************************************************************** */
2037
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
2047 EQP_SHIFTS.AVAILABILITY_FLAG = 'Y'And
2048 READINGS.TAG_DATA IS NOT NULL and
2049 CASE WHEN ( LENGTH(TRIM(TRANSLATE( READINGS.TAG_DATA , ' +-.0123456789', ' '))) IS NULL )
2050 THEN To_Number( READINGS.TAG_DATA ) ELSE NULL END > 0;
2051 r_readings c_readings%ROWTYPE ;
2052 BEGIN
2053 open c_readings;
2054 loop
2055 fetch c_readings into r_readings ;
2056 exit when c_readings%NOTFOUND ;
2057 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'SCRP',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),'Scrap Quantity = ' || r_readings.tag_data);
2058 end loop;
2059 close c_readings ;
2060 END ;
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 **************************************************************************** */
2070
2071 PROCEDURE MTH_GEN_HDL_RECALIBRATION_EVT IS
2072 v_previous_tag_reading_data VARCHAR2(255) ;
2073 cursor c_readings is SELECT Reading.* FROM (select readings.* ,lag( readings.tag_data)
2074 over (partition by readings.equipment_fk_key, readings.tag_code
2078 MTH_TAG_READINGS_STG readings,
2075 order by readings.reading_time ) prev_tag_data
2076
2077 from MTH_EQUIPMENT_SHIFTS_D eqp_shifts,
2079 MTH_ENTITIES entities
2080 WHERE
2081 READINGS.MTH_ENTITY = 18 And
2082 READINGS.EQUIPMENT_FK_KEY = EQP_SHIFTS.EQUIPMENT_FK_KEY And
2083 READINGS.READING_TIME BETWEEN EQP_SHIFTS.FROM_DATE AND EQP_SHIFTS.TO_DATE And
2084 EQP_SHIFTS.AVAILABILITY_FLAG = 'Y' and
2085 ENTITIES.id = READINGS.mth_entity and
2086 ENTITIES.mth_alias = 'Equipment Cycles' And
2087 CASE WHEN( LENGTH ( TRIM ( TRANSLATE ( READINGS.TAG_DATA , ' +-.0123456789' , ' ' ) ) ) IS NULL ) THEN To_Number( readings.TAG_DATA ) ELSE NULL END >0 ) Reading
2088 WHERE (Reading.prev_tag_data IS NULL OR Reading.prev_tag_data <> v_recal_threshold) ;
2089
2090 r_readings c_readings%ROWTYPE ;
2091
2092 BEGIN
2093 open c_readings;
2094 loop
2095 fetch c_readings into r_readings ;
2096 exit when c_readings%NOTFOUND ;
2097 IF (r_readings.TAG_DATA >= v_recal_threshold)THEN
2098 v_previous_tag_reading_data := MTH_UTIL_PKG.get_prev_tag_reading(r_readings.TAG_CODE,r_readings.READING_TIME);
2099 IF (v_previous_tag_reading_data < v_recal_threshold OR v_previous_tag_reading_data IS NULL ) THEN
2100 MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'RECAL',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),'Equipment ready for recalibration. ' || r_readings.TAG_CODE ||
2101 ' = ' || r_readings.TAG_DATA);
2102 END IF;
2103 END IF;
2104 --IF (r_readings.TAG_DATA = v_recal_threshold) THEN
2105 --MTH_EVENT_PKG.HANDLE_EVENT (r_readings.equipment_fk_key,'RECAL',NULL,NULL,r_readings.READING_TIME,NULL,TO_NUMBER(r_readings.tag_data),'Equipment ready for recalibration. ' || r_readings.TAG_CODE ||
2106 -- ' = ' || r_readings.TAG_DATA);
2107 end loop;
2108 close c_readings ;
2109 END ;
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 **************************************************************************** */
2119
2120
2121 PROCEDURE MTH_GEN_HDL_EVENTS_READINGS IS
2122 v_count1 number;
2123 v_desc1 VARCHAR2(100);
2124 l_sql_stmt1 VARCHAR2(1000);
2125 BEGIN
2126 MTH_GEN_HDL_EQP_DOWN_EVENT ();
2127 MTH_HDL_EQP_OUT_OF_CTL_EVT();
2128 MTH_GEN_HDL_CTL_LMT_WRNG_EVT();
2129 MTH_HDL_EQP_UNSTAB_EVT();
2130 MTH_GEN_HDL_EQP_FAULT_EVENT ();
2131 MTH_GEN_HDL_SCRAP_EVENT();
2132 MTH_GEN_HDL_RECALIBRATION_EVT();
2133 SELECT Count(*) INTO v_count1 FROM FND_LOOKUPS B WHERE B.LOOKUP_TYPE IN ('MTH_EVENT_GEN_API_READINGS');
2134 IF (v_count1>0) THEN
2135 SELECT B.DESCRIPTION INTO v_desc1 FROM FND_LOOKUPS B WHERE B.LOOKUP_TYPE IN ('MTH_EVENT_GEN_API_READINGS');
2136 l_sql_stmt1:= 'BEGIN ' || v_desc1 || '; END;';
2137 BEGIN
2138 IF(v_desc1 IS NOT NULL )THEN
2139 EXECUTE IMMEDIATE l_sql_stmt1;
2140 END IF;
2141 EXCEPTION
2142 WHEN OTHERS THEN
2143 mth_util_pkg.log_msg('Exception while calling custom hook - ' || v_desc1, mth_util_pkg.G_DBG_EXCEPTION);
2144 END;
2145 END IF;
2146
2147 END MTH_GEN_HDL_EVENTS_READINGS;
2148
2149
2150
2151
2152
2153
2154
2155 /*******************************************************************************
2156 * Procedure :INIT_READINGS_FROM_RAW *
2157 * Description :This procedure is used for calculating the readings *
2158 * in INIT mode *
2159 * File Name :MTHTAGB.PLS *
2160 * Visibility :Private *
2161 * Parameters : *
2162 *******************************************************************************/
2163 PROCEDURE INIT_READINGS_FROM_RAW(
2164 p_from_tz IN VARCHAR2 DEFAULT NULL,
2165 p_to_tz IN VARCHAR2 DEFAULT NULL ,
2166 p_ret_code OUT NOCOPY NUMBER
2167 ) IS
2168 v_log_date DATE;
2169 v_unassigned_val VARCHAR2(30);
2170 v_current_processing_flag NUMBER;
2171 v_desc VARCHAR2(100);
2172 l_sql_stmt VARCHAR2(1000);
2173 v_ret_code NUMBER ;
2174 v_reprocess_flag number;
2175 v_count number;
2176
2177
2178 BEGIN
2179 mth_util_pkg.log_msg('INIT_READINGS_FROM_RAW start', mth_util_pkg.G_DBG_PROC_FUN_START);
2180
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;
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
2201 DELETE FROM MTH_TAG_READINGS;
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
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
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
2213 DELETE FROM MTH_EVENT_ACTIONS;
2214 mth_util_pkg.log_msg('Number of rows deleted in MTH_EVENT_ACTIONS - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
2215
2216
2217 IF (v_current_processing_flag=1 OR v_current_processing_flag=2 OR v_reprocess_flag>0 ) THEN
2218
2219 mth_util_pkg.mth_load_tag_raw_to_processed(v_current_processing_flag, p_from_tz , p_to_tz); --commit to be removed from procedure
2220
2221 p_ret_code := MTH_LOAD_TAG_READINGS_PKG.MTH_TRANSFORM_TAG_DATA_TO_STG();
2222
2223 IF (p_ret_code <> 2) THEN
2224
2225 SELECT Count(*)
2226 INTO v_count
2227 FROM FND_LOOKUPS B
2228 WHERE B.LOOKUP_TYPE IN ('MTH_TAG_BUS_RULES_PLSQL_API');
2229
2230 IF (v_count>0) THEN
2231
2232 SELECT B.DESCRIPTION
2233 INTO v_desc
2234 FROM FND_LOOKUPS B
2235 WHERE B.LOOKUP_TYPE IN ('MTH_TAG_BUS_RULES_PLSQL_API');
2236
2237 l_sql_stmt:= 'BEGIN ' || v_desc || '(:1); END;';
2238
2239 BEGIN
2240 IF(v_desc IS NOT NULL )THEN
2241 EXECUTE IMMEDIATE l_sql_stmt USING OUT p_ret_code;
2242 ELSE
2243 RAISE NO_DATA_FOUND;
2244 END IF;
2245
2246 EXCEPTION
2247 WHEN NO_DATA_FOUND THEN
2248 p_ret_code := 0;
2249
2250 WHEN OTHERS THEN
2251 mth_util_pkg.log_msg('Exception while calling custom hook - ' || v_desc, mth_util_pkg.G_DBG_EXCEPTION);
2252 p_ret_code := 2;
2253 END;
2254 ELSE
2255 p_ret_code := 0;
2256
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);
2266 p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_TAG_BASED_CONTEXTUALIZE();
2267 IF(p_ret_code <> 2) THEN
2268 mth_util_pkg.log_msg('MTH_TAG_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2269 p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_SCHD_BASED_CONTEXTUALIZE();
2270 IF(p_ret_code <> 2) THEN
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;
2280 COMMIT;
2281 END IF;
2282 END IF;
2283 END IF;
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;
2293
2294 END IF;
2295 END IF;
2296 END IF;
2297
2298 mth_util_pkg.log_msg('INIT_READINGS_FROM_RAW end', mth_util_pkg.G_DBG_PROC_FUN_END);
2299
2300
2301 EXCEPTION
2302 WHEN OTHERS THEN
2303 mth_util_pkg.log_msg('Exception OTHERS in INIT_READINGS_FROM_RAW', mth_util_pkg.G_DBG_EXCEPTION);
2304 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2305 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2306 p_ret_code := 2;
2307 ROLLBACK;
2308
2309 END INIT_READINGS_FROM_RAW;
2310
2311 /*******************************************************************************
2312 * Procedure :INCR_READINGS_FROM_RAW *
2313 * Description :This procedure is used for calculating the readings *
2314 * in INCR mode *
2315 * File Name :MTHTAGB.PLS *
2316 * Visibility :Private *
2317 * Parameters : *
2318 *******************************************************************************/
2319 PROCEDURE INCR_READINGS_FROM_RAW(
2323 ) IS
2320 p_from_tz IN VARCHAR2 DEFAULT NULL,
2321 p_to_tz IN VARCHAR2 DEFAULT NULL ,
2322 p_ret_code OUT NOCOPY NUMBER
2324 v_log_date DATE;
2325 v_unassigned_val VARCHAR2(30);
2326 v_current_processing_flag NUMBER;
2327 v_desc VARCHAR2(100);
2328 l_sql_stmt VARCHAR2(1000);
2329 v_ret_code NUMBER ;
2330 v_reprocess_flag number;
2331 v_count number;
2332
2333
2334 BEGIN
2335 mth_util_pkg.log_msg('INCR_READINGS_FROM_RAW start', mth_util_pkg.G_DBG_PROC_FUN_START);
2336
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 );
2346 p_ret_code := MTH_LOAD_TAG_READINGS_PKG.MTH_TRANSFORM_TAG_DATA_TO_STG();
2347 IF(p_ret_code <> 2)
2348 THEN
2349 -- Dbms_Output.put_line('p_ret_code1' || p_ret_code);
2350 SELECT Count(*)
2351 INTO v_count
2352 FROM FND_LOOKUPS B
2353 WHERE B.LOOKUP_TYPE IN ('MTH_TAG_BUS_RULES_PLSQL_API');
2354
2355 IF (v_count>0) THEN
2356
2357 SELECT B.DESCRIPTION
2358 INTO v_desc
2359 FROM FND_LOOKUPS B
2360 WHERE B.LOOKUP_TYPE IN ('MTH_TAG_BUS_RULES_PLSQL_API');
2361 -- Dbms_Output.put_line('v_desc' || v_desc);
2362
2363 l_sql_stmt:= 'BEGIN ' || v_desc || '(:1); END;';
2364
2365 BEGIN
2366 IF(v_desc IS NOT NULL )THEN
2367 -- Dbms_Output.put_line('p_ret_code4' || p_ret_code);
2368 -- Dbms_Output.put_line('l_sql_stmt' || l_sql_stmt);
2369
2370 EXECUTE IMMEDIATE l_sql_stmt USING OUT p_ret_code;
2371
2372 -- Dbms_Output.put_line('p_ret_code2' || p_ret_code);
2373
2374 ELSE
2375 RAISE NO_DATA_FOUND;
2376 END IF;
2377
2378 EXCEPTION
2379 WHEN NO_DATA_FOUND THEN
2380 p_ret_code := 0;
2381
2382 WHEN OTHERS THEN
2383 -- Dbms_Output.put_line('p_ret_code5' || p_ret_code);
2384
2385 mth_util_pkg.log_msg('Exception while calling custom hook - ' || v_desc, mth_util_pkg.G_DBG_EXCEPTION);
2386 p_ret_code := 2;
2387 END;
2388 ELSE
2389 p_ret_code := 0;
2390
2391 END IF;
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);
2401 p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_TAG_BASED_CONTEXTUALIZE();
2402 IF(p_ret_code <> 2) THEN
2403 mth_util_pkg.log_msg('MTH_TAG_BASED_CONTEXTUALIZE completeled succesfully', mth_util_pkg.G_DBG_PROC_FUN_END);
2404 p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_SCHD_BASED_CONTEXTUALIZE();
2405 IF(p_ret_code <> 2) THEN
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);
2415
2416 END IF;
2417 COMMIT;
2418 END IF;
2419 END IF;
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;
2429 END IF;
2430 END IF;
2431 END IF;
2432
2433
2434 mth_util_pkg.log_msg('INCR_READINGS_FROM_RAW end', mth_util_pkg.G_DBG_PROC_FUN_END);
2435
2436
2437 EXCEPTION
2438 WHEN OTHERS THEN
2439 mth_util_pkg.log_msg('Exception OTHERS in INCR_READINGS_FROM_RAW', mth_util_pkg.G_DBG_EXCEPTION);
2440 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2441 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2442 p_ret_code := 2;
2443 ROLLBACK;
2444
2445
2446 END INCR_READINGS_FROM_RAW;
2447
2448
2449 /*******************************************************************************
2450 * Procedure :RECAL_READINGS_FROM_RAW *
2451 * Description :This procedure is used for calculating the readings *
2452 * in RECAL mode *
2453 * File Name :MTHTAGB.PLS *
2454 * Visibility :Private *
2455 * Parameters : *
2456 *******************************************************************************/
2457 PROCEDURE RECAL_READINGS_FROM_RAW(p_recal_from_date IN DATE, --Recalculation from date
2458 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
2459 p_equipment_pk_key IN NUMBER DEFAULT NULL,
2460 p_from_tz IN VARCHAR2 DEFAULT NULL,
2461 p_to_tz IN VARCHAR2 DEFAULT NULL ,
2462 p_ret_code OUT NOCOPY NUMBER
2463 ) IS
2464 v_log_date DATE;
2465 v_unassigned_val VARCHAR2(30);
2466 v_current_processing_flag NUMBER;
2467 v_desc VARCHAR2(100);
2468 l_sql_stmt VARCHAR2(1000);
2469 v_ret_code NUMBER ;
2470
2471
2472 BEGIN
2473 mth_util_pkg.log_msg('RECAL_READINGS_FROM_RAW start', mth_util_pkg.G_DBG_PROC_FUN_START);
2474
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)
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
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
2496 WHERE B.LOOKUP_TYPE IN ('MTH_TAG_BUS_RULES_PLSQL_API');
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();
2506 IF(p_ret_code <> 2)
2507 THEN
2508 p_ret_code :=MTH_LOAD_TAG_READINGS_PKG.MTH_SCHD_BASED_CONTEXTUALIZE();
2509 IF(p_ret_code <> 2)
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;
2519 END IF;
2520 END IF;
2521 END IF;
2522 END IF;
2523
2524
2525 mth_util_pkg.log_msg('RECAL_READINGS_FROM_RAW end', mth_util_pkg.G_DBG_PROC_FUN_END);
2526
2527 EXCEPTION
2528 WHEN OTHERS THEN
2529 mth_util_pkg.log_msg('Exception OTHERS in IRECAL_READINGS_FROM_RAW', mth_util_pkg.G_DBG_EXCEPTION);
2530 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
2531 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
2532 p_ret_code := 2;
2533
2534
2535 END RECAL_READINGS_FROM_RAW;
2536
2537
2538
2539
2540 END MTH_LOAD_TAG_READINGS_PKG;