[Home] [Help]
PACKAGE BODY: APPS.MTH_PROCESS_UDA_PKG
Source
1 PACKAGE BODY MTH_PROCESS_UDA_PKG AS
2 /*$Header: mthudapb.pls 120.4.12020000.3 2012/10/18 15:58:54 sasuren noship $*/
3
4 PROCEDURE TB_UPLOAD_NO_COMMIT;
5 /*******************************************************************************
6 * Procedure :MAIN *
7 * Description :This procedure is the main procedure for output *
8 * File Name :MTHUDAPB.PLS *
9 * Visibility :Public *
10 * Parameters : p_mode : INIT,INCR,RECALC *
11 * p_recal_from_date : Recalculation from date *
12 * p_recal_to_date : Recalculation to date *
13 * p_equipment_pk_key : Equipment to recalculate *
14 * p_plant_fk_key : Plant *
15 * p_err_buff : Error message *
16 * p_retcode : Error Code *
17 * Modification log : *
18 * Author Date Change *
19 * Srikanth Lakshmanan 17-Oct-2011 Initial Creation *
20 *******************************************************************************/
21
22 PROCEDURE PROCESS_UDA( p_mode IN VARCHAR2, --INIT, INCR, RECALC
23 p_recal_from_date IN DATE DEFAULT NULL, --Recalculation from date
24 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
25 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
26 p_plant_fk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
27 p_retcode OUT NOCOPY NUMBER
28 ) IS
29 l_retcode NUMBER;
30 l_errbuf VARCHAR2(100);
31
32 BEGIN
33
34 mth_util_pkg.log_msg('PROCESS_UDA start', mth_util_pkg.G_DBG_PROC_FUN_START);
35 mth_util_pkg.log_msg('p_mode = ' || p_mode , mth_util_pkg.G_DBG_PARAM_VAL);
36 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);
37 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);
38 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key , mth_util_pkg.G_DBG_PARAM_VAL);
39 mth_util_pkg.log_msg('p_plant_fk_key = ' || p_plant_fk_key , mth_util_pkg.G_DBG_PARAM_VAL);
40
41 IF p_mode IN ('INCR','INIT') THEN
42 PROCESS_EQUIPUDA_IN_INCR_MODE(l_retcode,l_errbuf);
43 /* ELSE IF p_mode = 'INIT'
44 PROCESS_EQUIPMENTS_UDA_IN_INIT_MODE(p_equipment_pk_key,
45 p_plant_fk_key); */
46 ELSE
47 REPROCES_EQUIPMENTS_UDA('RECAL',p_recal_from_date,
48 p_recal_to_date,
49 p_equipment_pk_key,
50 p_plant_fk_key,
51 l_retcode,
52 l_errbuf
53 );
54 END IF;
55
56 mth_util_pkg.log_msg('PROCESS_UDA end', mth_util_pkg.G_DBG_PROC_FUN_END);
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 mth_util_pkg.log_msg('Exception OTHERS in PROCESS_UDA', mth_util_pkg.G_DBG_EXCEPTION);
61 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
62 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
63 p_retcode := 2;
64 RAISE;
65
66 END PROCESS_UDA;
67
68 /*******************************************************************************
69 * Procedure :VALIDATE *
70 * Description :This procedure is the validate procedure *
71 * File Name :MTHUDAPB.PLS *
72 * Visibility :Public *
73 * Parameters : p_mode : INIT,INCR,RECALC *
74 * p_recal_from_date : Recalculation from date *
75 * p_recal_to_date : Recalculation to date *
76 * p_err_buff : Error message *
77 * p_retcode : Error Code *
78 * Modification log : *
79 * Author Date Change *
80 * Srikanth Lakshmanan 31-Oct-2011 Initial Creation *
81 *******************************************************************************/
82 PROCEDURE VALIDATE( p_mode IN VARCHAR2, --INIT, INCR, RECALC
83 p_recal_from_date IN DATE DEFAULT NULL, --Recalculation from date
84 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
85 p_retcode OUT NOCOPY NUMBER,
86 p_err_buff OUT NOCOPY VARCHAR2
87 ) IS
88
89 NULL_DATES EXCEPTION;
90 DATE_VAL EXCEPTION;
91 v_tag_reading_count NUMBER;
92
93
94 BEGIN
95
96 IF( p_mode = 'RECAL') THEN
97
98 -- Validate the parameters passed
99 IF ( (p_recal_from_date IS NULL) OR (p_recal_to_date IS NULL) ) THEN
100 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
101 p_retcode := 2;
102 RAISE NULL_DATES;
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)
112 OR TAG.last_update_date >= RUN_LOG.FROM_DATE;
113 IF v_tag_reading_count > 0 THEN
114 mth_util_pkg.log_msg('All data from tag reading is still not populated to output table.',mth_util_pkg.G_DBG_EXCEPTION);p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
115 p_retcode := 2;
116 RAISE DATE_VAL;
117 END IF;
118 END IF;
119
120 EXCEPTION
121 WHEN NULL_DATES THEN
122 --Call logging API and then throw exception
123 mth_util_pkg.log_msg('Exception NULL_DATES in VALIDATION', mth_util_pkg.G_DBG_EXCEPTION);
124 mth_util_pkg.log_msg('Recalculation from date passed is null. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
125 mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
126 p_retcode := 2;
127 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
128 RAISE;
129 WHEN DATE_VAL THEN
130 --Call logging API and then throw exception
131 mth_util_pkg.log_msg('Exception DATE_VAL in VALIDATION', mth_util_pkg.G_DBG_EXCEPTION);
132 mth_util_pkg.log_msg('Recalculation from date is greater than recalculation to date. Please correct and re-run the program.', mth_util_pkg.G_DBG_EXCEPTION);
133 mth_util_pkg.log_msg(-20002, mth_util_pkg.G_DBG_EXCEPTION);
134 p_retcode := 2;
135 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
136 RAISE;
137 WHEN OTHERS THEN
138 mth_util_pkg.log_msg('Exception OTHERS in VALIDATE', mth_util_pkg.G_DBG_EXCEPTION);
139 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
140 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
141 p_retcode := 2;
142 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
143 RAISE;
144
145 END VALIDATE;
146
147 /*******************************************************************************
148 * Procedure :PROCESS_EQUIPUDA_IN_INCR_MODE *
149 * Description :This procedure is the main procedure for output *
150 * File Name :MTHUDAPB.PLS *
151 * Visibility :Public *
152 * Parameters : *
153 * Modification log : *
154 * Author Date Change *
155 * Srikanth Lakshmanan 17-Oct-2011 Initial Creation *
156 *******************************************************************************/
157
158 PROCEDURE PROCESS_EQUIPUDA_IN_INCR_MODE(
159 p_retcode OUT NOCOPY NUMBER,
160 p_err_buff OUT NOCOPY VARCHAR2
161 ) IS
162 v_log_from_date DATE;
163 v_log_to_date DATE;
164 v_unassigned_val VARCHAR2(30);
165 BEGIN
166
167 mth_util_pkg.log_msg('PROCESS_EQUIPUDA_IN_INCR_MODE start', mth_util_pkg.G_DBG_PROC_FUN_START);
168
169 -- Initialize default parameters
170 v_log_to_date := sysdate;
171 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
172
173 -- Call mth_run_log_pre_load
174 mth_util_pkg.mth_run_log_pre_load('MTH_EQUIPMENTS_EXT_B',v_unassigned_val,'INCR',NULL,0,v_log_to_date);
175
176 -- Call GET_RUN_LOG_DATES
177 mth_util_pkg.GET_RUN_LOG_DATES('MTH_EQUIPMENTS_EXT_B',NULL,NULL,NULL,v_log_from_date,v_log_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,
187 HOUR_FK_KEY,
188 ITEM_FK_KEY,
189 RECIPE_VERSION,
190 RECIPE_NUM,
191 READ_TIME,
192 TAG_DATA,
193 DB_COL,
194 ATTR_GROUP_ID)
195 (SELECT DISTINCT TAG.EQUIPMENT_FK_KEY,
196 TAG.WORKORDER_FK_KEY,
197 TAG.SEGMENT_FK_KEY,
198 TAG.SHIFT_WORKDAY_FK_KEY,
199 TAG.HOUR_FK_KEY,
200 TAG.ITEM_FK_KEY,
201 TAG.RECIPE_VERSION,
202 TAG.RECIPE_NUM,
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'
212 AND TAG.creation_date >= RUN_LOG.from_date
213 AND TAG.creation_date <= Nvl(RUN_LOG.To_Date,SYSDATE)
214 AND TAG.MTH_ENTITY IS NULL
215 AND EGO.ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP'
216 AND EGO_ATTR_GRP.ATTR_GROUP_ID = TAG.ATTRIBUTE_GROUP
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
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);
232
233
234 TB_UPLOAD_NO_COMMIT();
235 p_retcode := 0;
236
237 ----Call mth_run_log_post_load
238 mth_util_pkg.mth_run_log_post_load('MTH_EQUIPMENTS_EXT_B',v_unassigned_val);
239
240 mth_util_pkg.log_msg('PROCESS_EQUIPUDA_IN_INCR_MODE end', mth_util_pkg.G_DBG_PROC_FUN_START);
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 mth_util_pkg.log_msg('Exception in PROCESS_EQUIPUDA_IN_INCR_MODE.', mth_util_pkg.G_DBG_EXCEPTION);
245 mth_util_pkg.log_msg('Unknown Exception occured while processing INCR mode');
246 mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
247 p_retcode := 2;
248 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
249 RAISE;
250
251 END PROCESS_EQUIPUDA_IN_INCR_MODE;
252
253 /*******************************************************************************
254 * Procedure :PROCESS_EQUIPMENTS_UDA_IN_INIT_MODE *
255 * Description :This procedure is the main procedure for output *
256 * File Name :MTHUDAPB.PLS *
257 * Visibility :Public *
258 * Parameters : p_equipment_pk_key : Equipment *
259 * p_err_buff : Error message *
260 * p_retcode : Error Code *
261 * Modification log : *
262 * Author Date Change *
263 * Srikanth Lakshmanan 17-Oct-2011 Initial Creation *
264 *******************************************************************************/
265 /*
266 PROCEDURE PROCESS_EQUIPMENTS_UDA_IN_INIT_MODE(
267 p_equipment_pk_key IN NUMBER DEFAULT NULL --Equipment to recalculate
268 p_retcode OUT NOCOPY NUMBER,
269 p_err_buff OUT NOCOPY VARCHAR2
270 ) IS
271
272 BEGIN
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,
282 HOUR_FK_KEY,
283 ITEM_FK_KEY,
284 RECIPE_VERSION,
285 RECIPE_NUM,
286 READ_TIME,
287 TAG_DATA,
288 DB_COL,
289 ATTR_GROUP_ID)
290 (SELECT TAG.EQUIPMENT_FK_KEY,
291 TAG.WORKORDER_FK_KEY,
292 TAG.SEGMENT_FK_KEY,
293 TAG.SHIFT_WORKDAY_FK_KEY,
294 TAG.HOUR_FK_KEY,
295 TAG.ITEM_FK_KEY,
296 TAG.RECIPE_VERSION,
297 TAG.RECIPE_NUM,
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'
307 AND TAG.EQUIPMENT_FK_KEY = NVL(p_equipment_pk_key, TAG.EQUIPMENT_FK_KEY)
308 AND TAG.CREATION_DATE >= RUN_LOG.FROM_DATE
309 AND TAG.CREATION_DATE < RUN_LOG.TO_DATE
310 AND EGO.ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP'
311 AND EGO_ATTR_GRP.ATTR_GROUP_ID = TAG.GROUP_ID
312 AND EGO.ATTR_GROUP_NAME = EGO_ATTR_GRP.ATTR_GROUP_NAME
313 AND EGO.ATTR_ID = TAG.ATTRIBUTE);
314
315 TB_UPLOAD_NO_COMMIT();
316 p_retcode := 0;
317
318 EXCEPTION
319
320 mth_util_pkg.log_msg('Exception in PROCESS_EQUIPUDA_IN_INCR_MODE.', mth_util_pkg.G_DBG_EXCEPTION);
321 mth_util_pkg.log_msg('Unknown Exception occured while processing Incremental mode');
322 p_retcode := 2;
323 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
324 mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
325 RAISE;
326
327 END PROCESS_EQUIPMENTS_UDA_IN_INIT_MODE;
328 */
329
330 /*******************************************************************************
331 * Procedure :REPROCES_EQUIPMENTS_UDA *
332 * Description :This procedure is the main procedure for output *
333 * File Name :MTHUDAPB.PLS *
334 * Visibility :Public *
335 * Parameters : p_recal_from_date : Recalculation from date *
336 * p_recal_to_date : Recalculation to date *
337 * p_equipment_pk_key : Equipment to recalculate *
338 * p_plant_fk_key : Plant to recalculate *
339 * p_err_buff : Error message *
340 * p_retcode : Error Code *
341 * Modification log : *
342 * Author Date Change *
343 * Srikanth Lakshmanan 17-Oct-2011 Initial Creation *
344 *******************************************************************************/
345
346 PROCEDURE REPROCES_EQUIPMENTS_UDA( p_mode IN VARCHAR2, --INIT, INCR, RECALC
347 p_recal_from_date IN DATE DEFAULT NULL, --Recalculation from date
348 p_recal_to_date IN DATE DEFAULT NULL, --Recalculation to date
349 p_equipment_pk_key IN NUMBER DEFAULT NULL, --Equipment to recalculate
350 p_plant IN NUMBER DEFAULT NULL, --Equipment to recalculate
351 p_retcode OUT NOCOPY NUMBER,
352 p_err_buff OUT NOCOPY VARCHAR2
353 ) IS
354
355 BEGIN
356
357 mth_util_pkg.log_msg('REPROCES_EQUIPMENTS_UDA start', mth_util_pkg.G_DBG_PROC_FUN_START);
358 mth_util_pkg.log_msg('p_equipment_pk_key = ' || p_equipment_pk_key, mth_util_pkg.G_DBG_PARAM_VAL);
359 mth_util_pkg.log_msg('p_plant = ' || p_plant, mth_util_pkg.G_DBG_PARAM_VAL);
360
361 DELETE FROM MTH_EQUIPMENTS_EXT_B EQEXT
362 WHERE EQEXT.READ_TIME > p_recal_from_date
363 AND EQEXT.READ_TIME <= p_recal_to_date
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,
373 HOUR_FK_KEY,
374 ITEM_FK_KEY,
375 RECIPE_VERSION,
376 RECIPE_NUM,
377 READ_TIME,
378 TAG_DATA,
379 DB_COL,
380 ATTR_GROUP_ID)
381 SELECT TAG.EQUIPMENT_FK_KEY,
382 TAG.WORKORDER_FK_KEY,
383 TAG.SEGMENT_FK_KEY,
384 TAG.SHIFT_WORKDAY_FK_KEY,
385 TAG.HOUR_FK_KEY,
386 TAG.ITEM_FK_KEY,
387 TAG.RECIPE_VERSION,
388 TAG.RECIPE_NUM,
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
398 AND EGO.ATTR_GROUP_TYPE = 'MTH_EQUIPMENTS_GROUP'
399 AND EGO_ATTR_GRP.ATTR_GROUP_ID = TAG.ATTRIBUTE_GROUP
400 AND TAG.MTH_ENTITY IS NULL
401 AND EGO.ATTR_GROUP_NAME = EGO_ATTR_GRP.ATTR_GROUP_NAME
402 AND EGO.ATTR_ID = TAG.ATTRIBUTE
403 AND(( p_equipment_pk_key IS NOT NULL AND TAG.EQUIPMENT_FK_KEY = p_equipment_pk_key)
404 OR (EQUIP.PLANT_FK_KEY = NVL( p_plant, EQUIP.PLANT_FK_KEY)))
405 AND TAG.EQUIPMENT_FK_KEY = EQUIP.EQUIPMENT_PK_KEY
406 AND TAG.EQUIPMENT_FK_KEY = NVL(p_equipment_pk_key, TAG.EQUIPMENT_FK_KEY)
407 AND EQUIP.PLANT_FK_KEY = NVL(p_plant, EQUIP.PLANT_FK_KEY);
408
409 TB_UPLOAD_NO_COMMIT();
410 p_retcode := 0;
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 mth_util_pkg.log_msg('Exception in REPROCES_EQUIPMENTS_UDA.', mth_util_pkg.G_DBG_EXCEPTION);
415 mth_util_pkg.log_msg('Unknown Exception occured while processing Recalculation');
416 mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
417 p_retcode := 2;
418 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
419 RAISE;
420
421 END REPROCES_EQUIPMENTS_UDA;
422
423
424 PROCEDURE TB_UPLOAD_NO_COMMIT IS
425
426 v_colname VARCHAR2(30);
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
436 v_stmt_no := 5;
437 FOR DBCOL IN DISTINCT_COLUMN
438 LOOP
439 v_colname := DBCOL.DB_COL;
440 v_stmt_no := 10;
441 v_stmt := 'MERGE INTO MTH_EQUIPMENTS_EXT_B ED
442 USING (
443 SELECT TAG_DATA,
444 EQUIPMENT_FK_KEY,
445 WORKORDER_FK_KEY,
446 SEGMENT_FK_KEY,
447 SHIFT_WORKDAY_FK_KEY,
448 HOUR_FK_KEY,
449 ITEM_FK_KEY,
450 READ_TIME,
451 ATTR_GROUP_ID,
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
461 ED.READ_TIME = TS.READ_TIME)
462 WHEN MATCHED THEN
463 UPDATE
464 SET ED.'||v_colname||' = TS.TAG_DATA,
465 ED.LAST_UPDATED_BY = TS.l_updated_by,';
466
467 v_stmt := v_stmt||'ED.LAST_UPDATE_LOGIN = TS.l_last_update_login
468 WHEN NOT MATCHED THEN
469 INSERT ('||v_colname||',EXTENSION_ID, EQUIPMENT_PK_KEY,WORKORDER_FK_KEY,SEGMENT_FK_KEY,SHIFT_WORKDAY_FK_KEY, HOUR_FK_KEY, ITEM_FK_KEY, READ_TIME, ATTR_GROUP_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,';
470
471 v_stmt:=
472 v_stmt||'LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE,RECIPE_NUM,RECIPE_VERSION)
473 VALUES (TS.TAG_DATA,EGO_EXTFWK_S.NEXTVAL, TS.EQUIPMENT_FK_KEY, TS.WORKORDER_FK_KEY, TS.SEGMENT_FK_KEY,TS.SHIFT_WORKDAY_FK_KEY,TS.HOUR_FK_KEY, TS.ITEM_FK_KEY, TS.READ_TIME,';
474
475 v_stmt := v_stmt||'TS.ATTR_GROUP_ID,'||''''||SYSDATE||''''||',TS.l_updated_by,TS.l_last_update_login,TS.l_updated_by,'||''''||SYSDATE||''''||',TS.RECIPE_NUM, TS.RECIPE_VERSION)';
476
477 mth_util_pkg.log_msg('v_stmt : '||v_stmt,mth_util_pkg.G_DBG_DYN_SQL);
478
479 --DBMS_OUTPUT.PUT_LINE(v_stmt);
480 v_stmt_no := 20;
481 EXECUTE IMMEDIATE v_stmt;
482 --COMMIT;
483
484 END LOOP;
485
486
487 EXCEPTION
488 WHEN INVALID_NUMBER THEN
489 RAISE_APPLICATION_ERROR(-20008,'The Tag Data you are tyring to insert is of Character Data Type. A number is expected instead.');
490 WHEN OTHERS THEN
491 RAISE_APPLICATION_ERROR(-20008,SQLERRM||' at '||v_stmt_no);
492
493 END;
494 -- End of TB_UPLOAD;
495
496 /*******************************************************************************
497 * Procedure :PROCESS_INTERFACE_TO_STAGING *
498 * Description :This procedure is the main procedure for output *
499 * File Name :MTHUDAPB.PLS *
500 * Visibility :Public *
501 * Parameters : p_entity *
502 * *
503 * Modification log : *
504 * Author Date Change *
505 * Akanksha verma 30-March-2012 Initial Creation *
506 *******************************************************************************/
507
508 PROCEDURE PROCESS_INTERFACE_TO_STAGING (P_ENTITY IN VARCHAR2) IS
509
510 BEGIN
511 mth_util_pkg.log_msg('PROCESS_INTERFACE_TO_STAGING start', mth_util_pkg.G_DBG_PROC_FUN_START);
512 mth_util_pkg.log_msg('p_entity = ' || p_entity , mth_util_pkg.G_DBG_PARAM_VAL);
513
514 INSERT INTO MTH_EXT_ATTR_ERR
515 (
516 ATTR_GRP_NAME,
517 ATTR_NAME,
518 ATTR_VALUE,
519 GROUP_ID
520 )
521 SELECT stg_egv.attr_grp_name,
522 Nvl(Decode(stg_egv.attr_group_type,
523 'MTH_USER_ENTITIES_GROUP',stg_egv.ATTR_NAME,
524 eav.ATTR_NAME),stg_egv.ATTR_NAME) attr_name,
525 stg_egv.attr_value,
526 stg_egv.group_id
527 FROM ego_attrs_v eav,
528 (
529 SELECT stg.*,
530 egv.attr_group_name,
531 egv.attr_group_id,
532 egv.attr_group_type
533 FROM ego_attr_groups_v egv,
534 mth_ext_attr_i_stg stg
535 WHERE egv.APPLICATION_ID(+) = 9001
536 AND egv.ATTR_GROUP_TYPE(+) = Decode(P_ENTITY,
537 'EQUIPMENTS','MTH_EQUIPMENTS_GROUP',
538 'ITEMS','MTH_ITEMS_GROUP',
539 'OTHERS','MTH_OTHERS_GROUP',
540 'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP',
541 'PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
542 'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')
543 AND stg.attr_grp_name = egv.attr_group_name(+)) stg_egv
544 WHERE eav.APPLICATION_ID(+) = 9001
545 AND eav.ATTR_GROUP_TYPE(+) = Decode(P_ENTITY,
546 'EQUIPMENTS','MTH_EQUIPMENTS_GROUP',
547 'ITEMS','MTH_ITEMS_GROUP',
548 'OTHERS','MTH_OTHERS_GROUP',
549 'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP',
550 'PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
551 'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')
552 AND stg_egv.attr_group_name = eav.attr_group_name(+)
553 AND stg_egv.attr_name = eav.attr_name(+)
554 AND (
555 (eav.DATABASE_COLUMN IS NULL
556 AND NOT (stg_egv.ATTR_NAME IN ('EQUIPMENT','ITEM','OTHER','WORKORDER','SEGMENT','USER_ENTITY','ENTITY_TYPE'))
557 )
558 OR (stg_egv.ATTR_GROUP_ID IS NULL)
559 OR (eav.REQUIRED_FLAG ='Y' AND stg_egv.ATTR_VALUE IS NULL)
560 OR (P_ENTITY='EQUIPMENTS' AND stg_egv.ATTR_NAME='EQUIPMENT' AND stg_egv.ATTR_VALUE NOT IN (SELECT EQUIPMENT_PK_KEY FROM MTH_EQUIPMENTS_D))
561 OR (P_ENTITY='ITEMS' AND stg_egv.ATTR_NAME='ITEM' AND stg_egv.ATTR_VALUE NOT IN (SELECT ITEM_PK_KEY FROM MTH_ITEMS_EXT_B))
562 OR (P_ENTITY='OTHERS' AND stg_egv.ATTR_NAME='OTHER' AND stg_egv.ATTR_VALUE NOT IN (SELECT OTHER_PK_KEY FROM MTH_OTHERS_EXT_B))
563 OR (P_ENTITY='PRODUCTION_SCHEDULES' AND stg_egv.ATTR_NAME='WORKORDER' AND stg_egv.ATTR_VALUE NOT IN (SELECT WORKORDER_PK_KEY FROM MTH_PRODUCTION_SCHEDULES_EXT_B))
564 OR (P_ENTITY='PRODUCTION_SEGMENTS' AND stg_egv.ATTR_NAME='SEGMENT' AND stg_egv.ATTR_VALUE NOT IN (SELECT SEGMENT_PK_KEY FROM MTH_PRODUCTION_SEGMENTS_EXT_B))
565 OR (P_ENTITY='USER_ENTITIES' AND stg_egv.ATTR_NAME='USER_ENTITY' AND stg_egv.ATTR_VALUE NOT IN
566 (
567 SELECT e.ENTITY_PK_KEY
568 FROM MTH_USER_ENTITIES_EXT_B e,
569 mth_ext_attr_i_stg s
570 WHERE stg_egv.group_id = s.group_id
571 AND s.ATTR_NAME = 'ENTITY_TYPE'
572 AND s.ATTR_VALUE = e.ENTITY_TYPE
573 )
574 )
575 );
576
577 mth_util_pkg.log_msg('Number of rows inserted in MTH_EXT_ATTR_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
578
579
580
581
582 INSERT INTO MTH_EXT_ATTR_T_STG
583 (
584 ATTR_GROUP_ID,
585 ATTR_NAME,
586 ATTR_VALUE,
587 GROUP_ID,
588 DB_COL,
589 UNIQUE_KEY_FLAG
590 )
591 SELECT stg_egv.attr_group_id,
592 Nvl(Decode(stg_egv.attr_group_type,
593 'MTH_USER_ENTITIES_GROUP',stg_egv.ATTR_NAME,
594 eav.ATTR_NAME),stg_egv.ATTR_NAME) attr_name,
595 stg_egv.attr_value,
596 stg_egv.group_id,
597 eav.database_column,
598 eav.unique_key_flag
599 FROM ego_attrs_v eav,
600 (
601 SELECT stg.*,
602 egv.attr_group_name,
603 egv.attr_group_id,
604 egv.attr_group_type
605 FROM ego_attr_groups_v egv,
606 mth_ext_attr_i_stg stg
607 WHERE egv.APPLICATION_ID(+) = 9001
608 AND egv.ATTR_GROUP_TYPE(+) = Decode(P_ENTITY,
609 'EQUIPMENTS','MTH_EQUIPMENTS_GROUP',
610 'ITEMS','MTH_ITEMS_GROUP',
611 'OTHERS','MTH_OTHERS_GROUP',
612 'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP',
613 'PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
614 'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')
615 AND stg.attr_grp_name = egv.attr_group_name(+)) stg_egv
616 WHERE eav.APPLICATION_ID(+) = 9001
617 AND eav.ATTR_GROUP_TYPE(+) = Decode(P_ENTITY,
618 'EQUIPMENTS','MTH_EQUIPMENTS_GROUP',
619 'ITEMS','MTH_ITEMS_GROUP',
620 'OTHERS','MTH_OTHERS_GROUP',
621 'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP',
622 'PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
623 'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')
624 AND stg_egv.attr_group_name = eav.attr_group_name(+)
625 AND stg_egv.attr_name = eav.attr_name(+)
626 AND NOT(
627 (
628 (eav.DATABASE_COLUMN IS NULL
629 AND NOT (stg_egv.ATTR_NAME IN ('EQUIPMENT','ITEM','OTHER','WORKORDER','SEGMENT','USER_ENTITY','ENTITY_TYPE'))
630 )
631 OR (stg_egv.ATTR_GROUP_ID IS NULL)
632 OR (eav.REQUIRED_FLAG ='Y' AND stg_egv.ATTR_VALUE IS NULL)
633 OR (P_ENTITY='EQUIPMENTS' AND stg_egv.ATTR_NAME='EQUIPMENT' AND stg_egv.ATTR_VALUE NOT IN (SELECT EQUIPMENT_PK_KEY FROM MTH_EQUIPMENTS_D))
634 OR (P_ENTITY='ITEMS' AND stg_egv.ATTR_NAME='ITEM' AND stg_egv.ATTR_VALUE NOT IN (SELECT ITEM_PK_KEY FROM MTH_ITEMS_EXT_B))
635 OR (P_ENTITY='OTHERS' AND stg_egv.ATTR_NAME='OTHER' AND stg_egv.ATTR_VALUE NOT IN (SELECT OTHER_PK_KEY FROM MTH_OTHERS_EXT_B))
636 OR (P_ENTITY='PRODUCTION_SCHEDULES' AND stg_egv.ATTR_NAME='WORKORDER' AND stg_egv.ATTR_VALUE NOT IN (SELECT WORKORDER_PK_KEY FROM MTH_PRODUCTION_SCHEDULES_EXT_B))
637 OR (P_ENTITY='PRODUCTION_SEGMENTS' AND stg_egv.ATTR_NAME='SEGMENT' AND stg_egv.ATTR_VALUE NOT IN (SELECT SEGMENT_PK_KEY FROM MTH_PRODUCTION_SEGMENTS_EXT_B))
638 OR (P_ENTITY='USER_ENTITIES' AND stg_egv.ATTR_NAME='USER_ENTITY' AND stg_egv.ATTR_VALUE NOT IN
639 (
640 SELECT e.ENTITY_PK_KEY
641 FROM MTH_USER_ENTITIES_EXT_B e,
642 mth_ext_attr_i_stg s
643 WHERE stg_egv.group_id = s.group_id
644 AND s.ATTR_NAME = 'ENTITY_TYPE'
645 AND s.ATTR_VALUE = e.ENTITY_TYPE
646 )
647 )
648 )
649 );
650
651 mth_util_pkg.log_msg('Number of rows inserted in MTH_EXT_ATTR_T_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
652
653
654
655
656 INSERT
657 INTO
658 MTH_EXT_ATTR_ERR
659 (ATTR_GRP_NAME,
660 ATTR_NAME,
661 ATTR_VALUE,
662 GROUP_ID)
663 (SELECT
664 egv.attr_group_name,
665 stg.ATTR_NAME,
666 stg.ATTR_VALUE,
667 stg.attr_group_id
668 FROM
669 ego_attr_groups_v egv,
670 MTH_EXT_ATTR_ERR err,
671 MTH_EXT_ATTR_T_STG stg
672 WHERE
673 ( stg.ATTR_GROUP_ID = egv.ATTR_GROUP_ID) AND
674 (stg.GROUP_ID = err.GROUP_ID) AND
675 ( egv.ATTR_GROUP_NAME = err.ATTR_GRP_NAME)
676 );
677
678 mth_util_pkg.log_msg('Number of rows inserted in MTH_EXT_ATTR_ERR - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
679
680
681 DELETE
682 FROM
683 MTH_EXT_ATTR_T_STG
684 WHERE EXISTS
685 ( SELECT 1 FROM
686 (SELECT
687 egv.ATTR_GROUP_ID,
688 err.ATTR_NAME,
689 err.ATTR_VALUE,
690 err.GROUP_ID
691 FROM
692 ego_attr_groups_v egv,
693 MTH_EXT_ATTR_ERR err
694 WHERE
695 (egv.APPLICATION_ID = 9001 ) AND
696 (egv.ATTR_GROUP_TYPE = Decode(P_ENTITY,'EQUIPMENTS','MTH_EQUIPMENTS_GROUP','ITEMS','MTH_ITEMS_GROUP','OTHERS','MTH_OTHERS_GROUP',
697 'PRODUCTION_SCHEDULES','MTH_PROD_SCH_GROUP','PRODUCTION_SEGMENTS','MTH_PRODUCTION_SEGMENTS_GROUP',
698 'USER_ENTITIES','MTH_USER_ENTITIES_GROUP')) AND
699 (err.ATTR_GRP_NAME = egv.ATTR_GROUP_NAME)
700 ));
701 mth_util_pkg.log_msg('Number of rows deleted from MTH_EXT_ATTR_T_STG - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
702 mth_util_pkg.log_msg('PROCESS_INTERFACE_TO_STAGING end', mth_util_pkg.G_DBG_PROC_FUN_END);
703
704 END PROCESS_INTERFACE_TO_STAGING;
705
706 /*******************************************************************************
707 * Procedure :PROCESS_UDA_STATIC_DATA *
708 * Description :This procedure is the main procedure for output *
709 * File Name :MTHUDAPB.PLS *
710 * Visibility :Public *
711 * Parameters : p_entity *
712 * *
713 * Modification log : *
714 * Author Date Change *
715 * Akanksha verma 3-April-2012 Initial Creation *
716 *******************************************************************************/
717
718 Procedure PROCESS_UDA_STATIC_DATA (p_err_buff OUT NOCOPY VARCHAR2,
719 p_retcode OUT NOCOPY NUMBER,
720 p_entity IN VARCHAR2
721 ) IS
722 --EQUIPMENTS
723 --ITEMS
724 --OTHERS
725 --PRODUCTION_SEGMENTS
726 --PRODUCTION_SCHEDULES
727 --USER_ENTITIES
728 Begin
729 --MTH_EXT_ATTR_EQ,IT,OT,PSG,PS,UE_PF--
730 mth_util_pkg.log_msg('PROCESS_UDA_STATIC_DATA start', mth_util_pkg.G_DBG_PROC_FUN_START);
731 mth_util_pkg.log_msg('p_entity = ' || p_entity , mth_util_pkg.G_DBG_PARAM_VAL);
732
733 MTH_UTIL_PKG.MTH_TRUNCATE_TABLE('MTH_EXT_ATTR_ERR');
734 MTH_PROCESS_UDA_PKG.PROCESS_INTERFACE_TO_STAGING(p_entity);
735 IF p_entity = 'USER_ENTITIES' THEN
736 MTH_COMMON_PKG.CALL_NTB_UPLOAD_COMPOSITE_PK(p_entity);
737 ELSE
738 MTH_UDA_PKG.NTB_UPLOAD(p_entity);
739 END IF;
740 MTH_UTIL_PKG.MTH_TRUNCATE_TABLE('MTH_EXT_ATTR_I_STG');
741 MTH_UTIL_PKG.MTH_TRUNCATE_TABLE('MTH_EXT_ATTR_T_STG');
742 p_retcode := 0;
743
744 mth_util_pkg.log_msg('PROCESS_UDA_STATIC_DATA end', mth_util_pkg.G_DBG_PROC_FUN_END);
745
746 EXCEPTION
747 WHEN OTHERS THEN
748 mth_util_pkg.log_msg('Exception in PROCESS_UDA_STATIC_DATA', mth_util_pkg.G_DBG_EXCEPTION);
749 mth_util_pkg.log_msg('Unknown Exception occured while processing');
750 mth_util_pkg.log_msg(-20001, mth_util_pkg.G_DBG_EXCEPTION);
751 p_retcode := 2;
752 p_err_buff := mth_util_pkg.G_DBG_EXCEPTION;
753 RAISE;
754
755
756
757 END PROCESS_UDA_STATIC_DATA;
758
759 END MTH_PROCESS_UDA_PKG;