1 PACKAGE BODY MTH_UDA_PKG AS
2 /*$Header: mthuntbb.pls 120.6.12010000.2 2008/09/09 00:52:16 tkan ship $*/
3
4 PROCEDURE UPDATE_TO_PRIMARY_KEY(P_ENTITY IN VARCHAR2) IS
5 --initialize variables here
6 v_entity_code NUMBER;
7 v_pk_column VARCHAR2(40);
8 v_stmt VARCHAR2(200);
9 v_temp NUMBER;
10 v_i_num NUMBER;
11 v_t_num NUMBER;
12 v_master_table VARCHAR2(200);
13 e_tname_not_found EXCEPTION;
14 e_issue_with_data EXCEPTION;
15 e_no_pk_key EXCEPTION;
16
17 CURSOR c_null_check IS
18 SELECT DISTINCT GROUP_ID FROM MTH_EXT_ATTR_T_STG;
19 -- main body
20 BEGIN
21 NULL; -- allow compilation
22
23
24
25 -- Get the pk key
26 v_pk_column := MTH_UDA_PKG.Get_Mst_Pk_Name(p_entity);
27 IF (v_pk_column IS null) THEN
28 RAISE e_tname_not_found;
29 END IF;
30
31 /*
32 Make a check:
33 1. we should have only one row with db_col as null for each GROUP_ID, which will be the primary key.
34 2. if we have no row with db_col as null, then no primary key info has been provided.
35 */
36
37 FOR v_row IN c_null_check
38 LOOP
39 SELECT COUNT (1) INTO v_temp FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = v_row.GROUP_ID
40 AND DB_COL IS NULL;
41
42 IF (v_temp > 1) THEN -- Case 1
43 RAISE e_issue_with_data;
44 ELSIF (v_temp = 0) THEN -- Case 2
45 RAISE e_no_pk_key;
46 END IF;
47 END LOOP;
48
49 /* Once we have the column name, update those rows
50 in MTH_EXT_ATTR_T_STG, where db_col is null. This is
51 because, we expect only those rows to have db_col as null
52 which consists ATTR_VALUE as the primary key value. For
53 others since, meta data will be configured, db_col should not be
54 null.
55 */
56
57 v_stmt := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_pk_column||''''||' WHERE DB_COL IS NULL';
58 EXECUTE IMMEDIATE v_stmt;
59 COMMIT;
60
61 EXCEPTION
62 WHEN e_tname_not_found THEN
63 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided');
64 WHEN e_issue_with_data THEN
65 RAISE_APPLICATION_ERROR(-20002,'There is an issue with data, one or more columns except primary key have NO meta data defined. Please recheck');
66 WHEN e_no_pk_key THEN
67
68 RAISE_APPLICATION_ERROR(-20003,'No primary Key column has been provided: A primary key column should not have meta data defined');
69
70 WHEN OTHERS THEN
71 RAISE_APPLICATION_ERROR(-20006, SQLERRM||v_stmt);
72
73 END;
74 -- End of UPDATE_TO_PRIMARY_KEY;
75
76 PROCEDURE NTB_UPLOAD_STANDARD_WHO(P_EXT_TBL_NAME IN VARCHAR2, P_EXTENSION_ID IN NUMBER, P_IF_ROW_EXISTS IN NUMBER) IS
77 --initialize variables here
78 l_updated_by NUMBER := 15;
79 l_last_update_login NUMBER := 15;
80 v_stmt VARCHAR2(20000);
81 -- main body
82 BEGIN
83 NULL; -- allow compilation
84 /*
85 Check whether we need to insert or update these values
86 by checking p_if_row_exists. if this is 0, we are inserting.
87 */
88
89 IF (p_if_row_exists = 0) THEN
90 /* Row does not exists, assign values to creation_date
91 and created_by*/
92 v_stmt := 'UPDATE '||p_ext_tbl_name||' SET LAST_UPDATE_DATE = '||''''||SYSDATE||''''||', LAST_UPDATED_BY = '||l_updated_by||', LAST_UPDATE_LOGIN = ';
93 v_stmt := v_stmt||l_last_update_login||', CREATED_BY = '||l_updated_by||', CREATION_DATE = '||''''||SYSDATE||''''||' WHERE EXTENSION_ID = '||p_extension_id;
94
95 ELSE
96 /* Row Exists, no need for creation_date and created_by */
97 v_stmt := 'UPDATE '||p_ext_tbl_name||' SET LAST_UPDATE_DATE = '||''''||SYSDATE||''''||', LAST_UPDATED_BY = '||l_updated_by||', LAST_UPDATE_LOGIN = '||l_last_update_login||' WHERE EXTENSION_ID = '||p_extension_id;
98 END IF;
99
100 --DBMS_OUTPUT.PUT_LINE (v_stmt);
101
102 EXECUTE IMMEDIATE v_stmt;
103 COMMIT;
104
105
106
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 RAISE_APPLICATION_ERROR(-20001,' in the procedure to update who columns');
111
112 END;
113 -- End of NTB_UPLOAD_STANDARD_WHO;
114
115 PROCEDURE NTB_UPLOADTL(P_ENTITY IN VARCHAR2, P_EXTID IN NUMBER, P_IF_ROW_EXISTS IN NUMBER) IS
116 --initialize variables here
117 v_pk_column VARCHAR2(30);
118 v_tname_b VARCHAR2(30);
119 v_tname_tl VARCHAR2(30);
120 e_tname_not_found EXCEPTION;
121 v_stmt VARCHAR2(30000);
122 -- main body
123 BEGIN
124 NULL; -- allow compilation
125 /*
126 Using the p_entity, get the primary key column name
127 */
128 v_pk_column := MTH_UDA_PKG.Get_Mst_Pk_Name(p_entity);
129 IF (v_pk_column = null) THEN
130 RAISE e_tname_not_found;
131 END IF;
132
133 /*
134 Now get the EXT_TL and EXT_B names
135 */
136 v_tname_b := MTH_UDA_PKG.Get_Ext_Table_Name(p_entity);
137 IF (v_tname_b = NULL) THEN
138 RAISE e_tname_not_found;
139 END IF;
140
141 v_tname_tl := MTH_UDA_PKG.Get_Ext_TL_Table_Name(p_entity);
142 IF (v_tname_tl = NULL) THEN
143 RAISE e_tname_not_found;
144 END IF;
145
146 /*
147 Now, check if it was already existing, if not, we will insert a new row,
148 else call the upload who procedure to update who columns
149 */
150
151 IF (p_if_row_exists = 0) THEN
152 -- INSERT A NEW ROW
153 --DBMS_OUTPUT.PUT_LINE('inserting the rows');
154 v_stmt := 'INSERT INTO '||v_tname_tl||'(EXTENSION_ID, ATTR_GROUP_ID, '||v_pk_column||',
155 SOURCE_LANG, LANGUAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE)
156 SELECT EXTENSION_ID, ATTR_GROUP_ID, '||v_pk_column||', ''US''SOURCE_LANG, ''US'' LANGUAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN, CREATED_BY,CREATION_DATE FROM '||v_tname_b||' WHERE EXTENSION_ID = '||p_extId;
157 --DBMS_OUTPUT.PUT_LINE(v_stmt);
158 EXECUTE IMMEDIATE v_stmt;
159 COMMIT;
160
161 ELSE
162 -- CALL to who upload column
163 --DBMS_OUTPUT.PUT_LINE('UPDating the who columns');
164 MTH_UDA_PKG.NTB_Upload_Standard_Who(v_tname_tl,p_extId,p_if_row_exists);
165 END IF;
166
167
168
169
170 EXCEPTION
171 WHEN e_tname_not_found THEN
172 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided at ');
173 WHEN OTHERS THEN
174 RAISE_APPLICATION_ERROR(-20002, SQLERRM);
175
176 END;
177 -- End of NTB_UPLOADTL;
178
179 PROCEDURE NTB_UPLOAD(P_TARGET IN VARCHAR2) IS
180 --initialize variables here
181 v_stmt_no NUMBER;
182 v_if_row_exists NUMBER;
183 v_attr_group NUMBER;
184 v_extId NUMBER;
185 v_cnt_rows NUMBER;
186 v_cnt_existing NUMBER;
187 v_col_name VARCHAR2(20);
188 v_col_val VARCHAR2(20);
189 v_stmt VARCHAR2(20000);
190 v_stmt_var VARCHAR2(20000);
191 v_mrc VARCHAR2(3);
192 v_date_val VARCHAR2(20);
193 e_tname_not_found EXCEPTION;
194 v_entity VARCHAR2(200) := p_target;
195 v_tname VARCHAR2(200);
196
197
198 /*
199 This cursor is used to loop through all the columns to
200 be filled in for a row. This helps to insert as well as
201 update a row in the EXT table.
202 */
203 CURSOR c_row_iterator(R_ID NUMBER, ATTR_GRP NUMBER) IS
204 SELECT STG.ATTR_GROUP_ID,STG.ATTR_NAME, STG.ATTR_VALUE, STG.DB_COL FROM MTH_EXT_ATTR_T_STG STG
205 WHERE STG.GROUP_ID = R_ID AND STG.ATTR_GROUP_ID = ATTR_GRP;
206
207 /*
208 This cursor is used to find all the attribute groups ids having
209 the same row ids and process the same.
210 */
211 CURSOR c_row_iterator1(GROUP_ID1 NUMBER) IS
212 SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;
213
214 /*
215 This cursor first gets all the rows for which DB_COL is null.
216 Essentially, these are going to be the ones which are the primary
217 keys of the table. This helps to locate a particular row and to decide
218 whether we update or insert a new row.
219 */
220 CURSOR c_row_iterator2(GROUP_ID2 NUMBER, AID NUMBER) IS
221 SELECT ATTR_NAME, ATTR_VALUE FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = GROUP_ID2 AND DB_COL IS NULL AND ATTR_GROUP_ID = AID;
222
223 /*This cursor will get all the NAME VALUE pair for which c_unique_key_flag = 'Y' */
224 CURSOR c_unique_key_flag(GROUP_ID3 NUMBER, AID3 NUMBER) IS
225 SELECT DB_COL, ATTR_VALUE FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = GROUP_ID3 AND ATTR_GROUP_ID = AID3 AND UNIQUE_KEY_FLAG='Y';
226
227 -- main body
228 BEGIN
229 NULL; -- allow compilation
230 -- Call the procedure to rename columns to the pkey columns
231 MTH_UDA_PKG.Update_To_Primary_Key(v_entity);
232
233
234 -- Code to get find out table name depending on the entity input
235 v_stmt_no := 5;
236 v_tname := MTH_UDA_PKG.Get_Ext_Table_Name(v_entity);
237 IF (v_tname is NULL) THEN
238 RAISE e_tname_not_found;
239 END IF;
240
241 -- DBMS_OUTPUT.PUT_LINE('The target table is '||TNAME);
242 /*
243 Select the different row ids present, each different row id refers to the data for a single
244 row. It is possible to have one to many relationship between row id and attribute group.
245 */
246 v_stmt_no:= 10;
247 SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;
248
249 /* Loop through each row of data. A row of data is identified as having the
250 same row id.
251 */
252 --DBMS_OUTPUT.PUT_LINE('Entering logic to process one set of rows with same row id');
253 FOR VAR IN 1..v_cnt_rows
254 LOOP
255 v_stmt_no := 20;
256
257 /*
258 This loop will help to process data for one row id, with provision
259 to have more than one attribute group id having the same row.
260 */
261 FOR A_ID IN c_row_iterator1(VAR)
262 LOOP
263 v_attr_group:= A_ID.ATTR_GROUP_ID; --Get the attribute group id in a variable
264 --DBMS_OUTPUT.PUT_LINE('The attribute group is '||v_attr_group);
265 --DBMS_OUTPUT.PUT_LINE('Processing Row '||VAR);
266
267 /*
268 This variable helps to prepare statement to get the EXT ID value
269 for a particular row
270 */
271 v_stmt_var := 'SELECT EXTENSION_ID FROM '||v_tname||' WHERE ATTR_GROUP_ID ='||v_attr_group;
272
273 /*
274 This variable helps to prepare statement to see whether the particular row
275 for which data is being processed is present in the EXT table or not.
276 */
277 v_stmt := 'SELECT COUNT(1) FROM '||v_tname||' WHERE ATTR_GROUP_ID ='||v_attr_group;
278
279 /*
280 This statement helps to prepare the above statements correctly
281 by helping to choose proper WHERE CLAUSES. This is achieved by
282 using the v_cnt_existing varaiable
283 */
284 v_stmt_no := 30;
285 SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
286 WHERE DB_COL IS NULL AND
287 ATTR_GROUP_ID = v_attr_group AND
288 GROUP_ID = VAR;
289 -- DBMS_OUTPUT.PUT_LINE('No of primary key columns = '||v_cnt_existing);
290
291 /*
292 This cursor first gets all the rows for which DB_COL is null.
293 Essentially, these are going to be the ones which are the primary
294 keys of the table. This helps to locate a particular row and to decide
295 whether we update or insert a new row.
296 */
297 --DBMS_OUTPUT.PUT_LINE('Preparing query using pkey columns');
298 FOR VAR2 IN c_row_iterator2(VAR, v_attr_group)
299 LOOP
300 v_col_name := VAR2.ATTR_NAME;
301 v_col_val := VAR2.ATTR_VALUE;
302 v_cnt_existing := v_cnt_existing -1;
303
304 /*
305 If the pkey columns happen to be date columns, we need
306 to add the logic to process the data by converting it
307 to date. This has been done in the loops which follow
308 */
309 v_stmt := v_stmt||' AND '||v_col_name||'='||v_col_val;
310 v_stmt_var := v_stmt_var||' AND '||v_col_name||'='||v_col_val;
311
312 END LOOP;
313
314 -- DBMS_OUTPUT.PUT_LINE('The queries for pkeys are as follows:');
315 -- DBMS_OUTPUT.PUT_LINE(v_stmt);
316 -- DBMS_OUTPUT.PUT_LINE(v_stmt_var);
317
318 /* Now add where clause to check for unique keys. For a multi row
319 attribute group, distinction between attribute group id and pkeys would
320 not suffice. For such a case, we designate few attribute columns as unique.
321 These attributes allow us to distinguish between multi row data.
322 Skip this check if the attribute group is not multi row.
323 */
324 --DBMS_OUTPUT.PUT_LINE('Now check logic for MULTI ROW');
325 v_stmt_no := 40;
326 SELECT MULTI_ROW_CODE INTO v_mrc FROM EGO_ATTR_GROUPS_V
327 WHERE ATTR_GROUP_ID = v_attr_group;
328
329 IF v_mrc = 'Y' THEN
330 --DBMS_OUTPUT.PUT_LINE('Attribute group is MULTI ROW');
331 /* LOGIC FOR multi row attribute groups */
332 SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
333 WHERE UNIQUE_KEY_FLAG='Y' AND
334 ATTR_GROUP_ID = v_attr_group AND
335 GROUP_ID = VAR;
336 -- DBMS_OUTPUT.PUT_LINE('No of unique columns = '||v_cnt_existing);
337
338 FOR C IN c_unique_key_flag(VAR, v_attr_group)
339 LOOP
340 -- DBMS_OUTPUT.PUT_LINE('Adding MULTI ROW column where clause to the earlier query');
341 v_col_name := C.DB_COL;
342 v_col_val := C.ATTR_VALUE;
343 v_cnt_existing := v_cnt_existing-1;
344 v_stmt_no := 50;
345
346 /*
347 Check for proper where clause. This is done to
348 get the correct timestamp for date columns.
349 */
350 -- DBMS_OUTPUT.PUT_LINE('Statement so far '||STMT);
351
352 IF SUBSTR(v_col_name,1,1)= 'D' THEN
353 -- DBMS_OUTPUT.PUT_LINE('We have a unique date column');
354 v_date_val := TO_CHAR(TO_DATE(v_col_val,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
355 v_stmt := v_stmt||' AND '||v_col_name||'='||'TO_DATE('||''''||v_date_val||''''||',''MM/DD/YYYY HH24:MI:SS'')';
356 v_stmt_var := v_stmt_var||' AND '||v_col_name||'='||'TO_DATE('||''''||v_date_val||''''||',''MM/DD/YYYY HH24:MI:SS'')';
357 ELSE
361
358 v_stmt := v_stmt||' AND '||v_col_name||'='||''''||v_col_val||'''';
359 v_stmt_var := v_stmt_var||' AND '||v_col_name||'='||''''||v_col_val||'''';
360 END IF;
362 END LOOP;
363 ELSE
364 null;
365 --DBMS_OUTPUT.PUT_LINE('The A Group is single row');
366 END IF;
367
368 -- DBMS_OUTPUT.PUT_LINE('The updated statements after unique key check are ');
369 -- DBMS_OUTPUT.PUT_LINE(v_stmt);
370 -- DBMS_OUTPUT.PUT_LINE(v_stmt_var);
371
372 v_stmt_no := 60;
373 /*
374 Get the count of the row in the variable v_if_row_exists
375 If the count is 0, it means the row with these values
376 of pkeys are not present. So proceed with inserting a
377 new surrogate key value
378 */
379 EXECUTE IMMEDIATE v_stmt INTO v_if_row_exists ;
380
381 IF v_if_row_exists = 0 THEN
382 --DBMS_OUTPUT.PUT_LINE('This row is not present in the EXT table');
383 --DBMS_OUTPUT.PUT_LINE('INSERT THE NEW ROW');
384
385
386 v_stmt_no := 70;
387 v_stmt := 'SELECT EGO_EXTFWK_S.NEXTVAL FROM DUAL';
388 EXECUTE IMMEDIATE v_stmt INTO v_extId;
389
390 v_stmt := 'INSERT INTO '||v_tname||' (EXTENSION_ID, ATTR_GROUP_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE) VALUES (:1, :2, '||''''||SYSDATE||''''||', -1, -1,'||''''||SYSDATE||''''||' )';
391 --DBMS_OUTPUT.PUT_LINE('The new EXT ID is'||v_extId);
392 --DBMS_OUTPUT.PUT_LINE('The new EXT ID is'||v_stmt);
393 v_stmt_no := 80;
394 EXECUTE IMMEDIATE v_stmt USING v_extId, v_attr_group ;
395 COMMIT;
396
397 ELSE
398 -- DBMS_OUTPUT.PUT_LINE('This data is already present in the EXT table');
399 -- DBMS_OUTPUT.PUT_LINE('UPDATE THE DATA');
400 v_stmt_no := 90;
401 EXECUTE IMMEDIATE v_stmt_var INTO v_extId;
402 --DBMS_OUTPUT.PUT_LINE('The EXT ID for this data is '||v_extId);
403 END IF;
404
405 /*
406 Iterate over all the name value pair for the row id
407 to insert/update in the EXT Table
408 */
409 --DBMS_OUTPUT.PUT_LINE('Iterate over all the columns to insert/update');
410 FOR EXT_VAL IN c_row_iterator(VAR,v_attr_group)
411 LOOP
412 /*
413 Check whether this is a pkey value,
414 in such cases, DB_COL will be null
415 */
416 IF EXT_VAL.DB_COL IS NULL THEN
417 v_col_name := EXT_VAL.ATTR_NAME;
418 ELSE
419 v_col_name := EXT_VAL.DB_COL;
420 END IF;
421
422 --DBMS_OUTPUT.PUT_LINE('The column name to be updated/inserted '||v_col_name);
423
424 /*
425 Check for a date column to use the appropiate TO_DATE FUNCTION
426 */
427 IF SUBSTR(v_col_name,1,1) = 'D' THEN
428 --DBMS_OUTPUT.PUT_LINE('A DATE COLUMN');
429
430 --DBMS_OUTPUT.PUT_LINE('The TIMESTAMP is ');
431 --DBMS_OUTPUT.PUT_LINE(TO_CHAR(TO_DATE(EXT_VAL.ATTR_VALUE,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS'));
432 v_date_val := TO_CHAR(TO_DATE(EXT_VAL.ATTR_VALUE,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
433 v_stmt := 'UPDATE '||v_tname||' SET '||v_col_name||' = '||'TO_DATE('||''''||v_date_val||''''||',''MM/DD/YYYY HH24:MI:SS'')'||' WHERE EXTENSION_ID = '||v_extId;
434 ELSE
435 v_col_val := EXT_VAL.ATTR_VALUE;
436 --DBMS_OUTPUT.PUT_LINE('The data is '||v_col_val);
437 v_stmt := 'UPDATE '||v_tname||' SET '||v_col_name||' = '||''''||v_col_val||''''||' WHERE EXTENSION_ID = '||v_extId;
438 END IF;
439
440 --DBMS_OUTPUT.PUT_LINE('The statement to be executed is '||v_stmt);
441
442 v_stmt_no := 100;
443 EXECUTE IMMEDIATE v_stmt;
444 COMMIT;
445
446 END LOOP; -- Completing insertion or updating a single row
447 /*
448 call procedure to update standard who columns
449 */
450 --DBMS_OUTPUT.PUT_LINE('calling who procedure');
451 MTH_UDA_PKG.NTB_Upload_Standard_Who(v_tname,v_extId, v_if_row_exists);
452
453 /*
454 Call the procedure to update TL Table
455 */
456 MTH_UDA_PKG.NTB_UploadTL(v_entity,v_extId,v_if_row_exists);
457
458 END LOOP;
459 END LOOP;
460
461
462
463
464 EXCEPTION
465 WHEN NO_DATA_FOUND THEN
466 RAISE_APPLICATION_ERROR(-20002,'No data found at line number '||v_stmt_no);
467
468 WHEN e_tname_not_found THEN
469 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided at '||v_stmt_no);
470
471 WHEN OTHERS THEN
472 RAISE_APPLICATION_ERROR(-20003,SQLERRM||' at '||v_stmt_no);
473 END;
474 -- End of NTB_UPLOAD;
475
476 FUNCTION GET_MST_TABLE_NAME(P_ENTITY IN VARCHAR2) RETURN VARCHAR2 IS
477 --initialize variables here
478 v_entity_code NUMBER;
479 v_mst_tbl_name VARCHAR2(50) DEFAULT NULL;
480 -- main body
481 BEGIN
482 NULL; -- allow compilation
483
484 -- Get the code for the entity
485 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
486
487 -- Check if v_entity_code is -1, if so, return NULL
488 IF (v_entity_code = -1) THEN
489 RETURN NULL;
490 END IF;
491
492 CASE
493 WHEN v_entity_code = 1 THEN v_mst_tbl_name := 'MTH_EQUIPMENTS_D';
494 WHEN v_entity_code = 2 THEN v_mst_tbl_name := 'MTH_ITEMS_D';
495 WHEN v_entity_code = 3 THEN v_mst_tbl_name := 'MTH_OTHERS_D';
496 WHEN v_entity_code = 4 THEN v_mst_tbl_name := 'MTH_PRODUCTION_SCHEDULES_F';
497 END CASE;
498
499 RETURN v_mst_tbl_name;
500
504 FUNCTION GET_MST_PK_NAME(P_ENTITY IN VARCHAR2) RETURN VARCHAR2 IS
501 END;
502 -- End of GET_MST_TABLE_NAME;
503
505 --initialize variables here
506 v_entity_code NUMBER;
507 v_mst_pk_name VARCHAR2(50) DEFAULT NULL;
508 -- main body
509 BEGIN
510 NULL; -- allow compilation
511 -- Get the code for the entity
512 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
513
514 -- Check if v_entity_code is -1, if so, return NULL
515 IF (v_entity_code = -1) THEN
516 RETURN NULL;
517 END IF;
518
519 CASE
520 WHEN v_entity_code = 1 THEN v_mst_pk_name := 'EQUIPMENT_PK_KEY';
521 WHEN v_entity_code = 2 THEN v_mst_pk_name := 'ITEM_PK_KEY';
522 WHEN v_entity_code = 3 THEN v_mst_pk_name := 'OTHER_PK_KEY';
523 WHEN v_entity_code = 4 THEN v_mst_pk_name := 'WORKORDER_PK_KEY';
524 END CASE;
525
526 RETURN v_mst_pk_name;
527 END;
528 -- End of GET_MST_PK_NAME;
529
530 FUNCTION GET_EXT_TL_TABLE_NAME(P_ENTITY IN VARCHAR2) RETURN VARCHAR2 IS
531 --initialize variables here
532 v_entity_code NUMBER;
533 v_ext_tbl_name VARCHAR2(50) DEFAULT NULL;
534 -- main body
535 BEGIN
536 NULL; -- allow compilation
537
538 -- Get the code for the entity
539 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
540
541 -- Check if v_entity_code is -1, if so, return NULL
542 IF (v_entity_code = -1) THEN
543 RETURN NULL;
544 END IF;
545
546 CASE
547 WHEN v_entity_code = 1 THEN v_ext_tbl_name := 'MTH_EQUIPMENTS_EXT_TL';
548 WHEN v_entity_code = 2 THEN v_ext_tbl_name := 'MTH_ITEMS_EXT_TL';
549 WHEN v_entity_code = 3 THEN v_ext_tbl_name := 'MTH_OTHERS_EXT_TL';
550 WHEN v_entity_code = 4 THEN v_ext_tbl_name := 'MTH_PRODUCTION_SCHEDULE_EXT_TL';
551 END CASE;
552
553 RETURN v_ext_tbl_name;
554 END;
555 -- End of GET_EXT_TL_TABLE_NAME;
556
557 FUNCTION GET_EXT_TABLE_NAME(P_ENTITY IN VARCHAR2) RETURN VARCHAR2 IS
558 --initialize variables here
559 v_entity_code NUMBER;
560 v_ext_tbl_name VARCHAR2(50) DEFAULT NULL;
561 -- main body
562 BEGIN
563 NULL; -- allow compilation
564 -- Get the code for the entity
565 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
566
567 -- Check if v_entity_code is -1, if so, return NULL
568 IF (v_entity_code = -1) THEN
569 RETURN NULL;
570 END IF;
571
572 CASE
573 WHEN v_entity_code = 1 THEN v_ext_tbl_name := 'MTH_EQUIPMENTS_EXT_B';
574 WHEN v_entity_code = 2 THEN v_ext_tbl_name := 'MTH_ITEMS_EXT_B';
575 WHEN v_entity_code = 3 THEN v_ext_tbl_name := 'MTH_OTHERS_EXT_B';
576 WHEN v_entity_code = 4 THEN v_ext_tbl_name := 'MTH_PRODUCTION_SCHEDULES_EXT_B';
577 END CASE;
578
579 RETURN v_ext_tbl_name;
580
581 END;
582 -- End of GET_EXT_TABLE_NAME;
583
584 FUNCTION GET_ENTITY_CODE(P_ENTITY IN VARCHAR2) RETURN NUMBER IS
585 --initialize variables here
586 v_entity_code NUMBER;
587 v_entity VARCHAR2(50);
588 -- main body
589 BEGIN
590 NULL; -- allow compilation
591 -- First make it case insensitive
592 v_entity := UPPER(p_entity);
593
594 CASE
595 WHEN v_entity = 'EQUIPMENTS' THEN v_entity_code := 1;
596 WHEN v_entity = 'ITEMS' THEN v_entity_code := 2;
597 WHEN v_entity = 'OTHERS' THEN v_entity_code := 3;
598 WHEN v_entity = 'PRODUCTION_SCHEDULES' THEN v_entity_code := 4;
599 ELSE v_entity_code := -1;
600 END CASE;
601 RETURN v_entity_code;
602
603 END;
604 -- End of GET_ENTITY_CODE;
605
606
607
608 PROCEDURE DEVICE_POST_LOG(P_TARGET IN VARCHAR2) IS
609 -- Intialize variables
610 e_duplicate_run EXCEPTION;
611 e_not_allowed EXCEPTION;
612 v_l_stmt NUMBER; -- To track the line where the error has occcured
613 v_count NUMBER;
614 v_to_date DATE;
615 v_fact_table VARCHAR2(50) := UPPER(p_target); -- FACT Table Name
616 v_last_update_date DATE; -- Standard WHO column
617 v_last_update_system_id NUMBER; -- Standard WHO column
618 v_stmt VARCHAR2(500);
619
620
621 -- main body
622 BEGIN
623
624 /*Make a check against v_fact_table, it should be MTH_EQUIPMENTS_EXT_B only and nothing else*/
625 v_l_stmt := 10;
626 IF v_fact_table <> 'MTH_EQUIPMENTS_EXT_B' THEN
627 RAISE e_not_allowed;
628 END IF;
629
630 -- Get the sysdate
631 v_l_stmt := 20;
632 SELECT SYSDATE INTO v_last_update_date FROM DUAL;
633
634 -- get the unassigned value for system id
635 v_l_stmt := 30;
636 SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() INTO v_last_update_system_id FROM DUAL;
637
638
639 -- Check whether the pre map operation has been run or not
640 v_l_stmt := 40;
641 SELECT COUNT(FACT_TABLE) INTO v_count
642 FROM MTH_RUN_LOG
643 WHERE FACT_TABLE = v_fact_table;
644
645 -- DBMS_OUTPUT.PUT_LINE('THE COUNT IS '||v_count);
646
647 IF v_count <> 0 THEN
648
649 -- Another check
650 v_l_stmt := 45;
651 SELECT TO_DATE INTO v_to_date
652 FROM MTH_RUN_LOG
653 WHERE FACT_TABLE = v_fact_table;
654
655 -- DBMS_OUTPUT.PUT_LINE('THE TO_DATE IS '||v_to_date);
656
657 END IF;
658
659 v_l_stmt := 50;
660 IF (v_count = 0)OR(v_to_date IS NULL) THEN
664 -- Update TO_DATE to SYSDATE, LAST_UPDATE_DATE, and LAST_UPDATE_SYSTEM_ID
661 -- Log is being run for the first time
662 RAISE e_duplicate_run;
663 ELSE
665
666
667 v_stmt := 'UPDATE MTH_RUN_LOG SET FROM_DATE = TO_DATE, LAST_UPDATE_DATE = :1, LAST_UPDATE_SYSTEM_ID =:2 WHERE
668 FACT_TABLE =:3';
669
670 -- DBMS_OUTPUT.PUT_LINE('UPDATING THE LOG TABLE '||v_stmt);
671
672 v_l_stmt := 60;
673 EXECUTE IMMEDIATE v_stmt USING v_last_update_date, v_last_update_system_id, v_fact_table;
674 v_l_stmt := 65;
675 COMMIT;
676
677 v_l_stmt := 70;
678 v_stmt := 'UPDATE MTH_RUN_LOG SET TO_DATE = NULL WHERE
679 FACT_TABLE =:1';
680
681 -- DBMS_OUTPUT.PUT_LINE('UPDATING THE LOG TABLE '||v_stmt);
682
683 EXECUTE IMMEDIATE v_stmt USING v_fact_table;
684 v_l_stmt := 75;
685 COMMIT;
686 END IF;
687
688 EXCEPTION
689 WHEN e_not_allowed THEN
690 RAISE_APPLICATION_ERROR(-20201,'This fact CANNOT BE logged using the procedure at line '||v_l_stmt);
691 WHEN e_duplicate_run THEN
692 RAISE_APPLICATION_ERROR (-20201,'Pre Map logging not available, run the load first, ABORTING');
693 WHEN OTHERS THEN
694 RAISE_APPLICATION_ERROR(-20203,SQLERRM||'at line '||v_l_stmt);
695 END;
696 -- End of DEVICE_POST_LOG;
697
698 PROCEDURE DEVICE_PRE_LOG(P_TARGET IN VARCHAR2) IS
699 -- Intialize variables
700 v_l_stmt NUMBER; -- To track the line where the error has occcured
701 v_to_date DATE; -- To Date
702 v_from_date DATE := TO_DATE('01/01/1900','MM-DD-YYYY HH:MIAM'); -- From Date # Bug fix: need to specify the date format
703 v_cnt NUMBER;
704 v_fact_table VARCHAR2(50) := UPPER(p_target); -- The instance name in the ETL
705 /*
706 CREATION_DATE will be same as v_last_update_date if the load is run for the first time,
707 else if the load is being run again, CREATION_DATE would already be populated, so there is no
708 need for this variable
709 */
710 v_last_update_date DATE; -- Standard WHO column
711 /*
712 CREATION_SYSTEM_ID will be same as LAST_UPDATE_SYSTEM_ID if the load is run for the first time,
713 else if the load is being run again, CREATION_SYSTEM_ID would already be populated, so there is no
714 need for this variable
715 */
716 v_last_update_system_id NUMBER; -- Standard WHO column
717 v_stmt VARCHAR2(500);
718 e_not_allowed EXCEPTION;
719
720
721 -- main body
722 BEGIN
723
724 /*Make a check against v_fact_table, it should be MTH_EQUIPMENTS_EXT_B only and nothing else*/
725 v_l_stmt := 5;
726 IF v_fact_table <> 'MTH_EQUIPMENTS_EXT_B' THEN
727 RAISE e_not_allowed;
728 END IF;
729
730 -- get the system date
731 v_l_stmt := 10;
732 SELECT SYSDATE INTO v_to_date FROM DUAL;
733
734 v_last_update_date := v_to_date;
735
736 -- get the unassigned value for system id
737 v_l_stmt := 15;
738 SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() INTO v_last_update_system_id FROM DUAL;
739
740 -- Check whether the load is being run for the first time or not
741 v_l_stmt := 20;
742 SELECT COUNT(FACT_TABLE) INTO v_cnt
743 FROM MTH_RUN_LOG
744 WHERE FACT_TABLE = v_fact_table;
745
746 -- DBMS_OUTPUT.PUT_LINE('Count of the fact table '||v_cnt);
747
748 v_l_stmt := 30;
749 IF v_cnt = 0 THEN
750 -- Log is being run for the first time
751
752 v_stmt := 'INSERT INTO MTH_RUN_LOG (FACT_TABLE, FROM_DATE, TO_DATE, CREATION_DATE,
753 LAST_UPDATE_DATE, CREATION_SYSTEM_ID, LAST_UPDATE_SYSTEM_ID) VALUES (:1, :2, :3, :4, :5, :6, :7)';
754 v_l_stmt := 40;
755 -- DBMS_OUTPUT.PUT_LINE('Inserting'||v_stmt);
756 EXECUTE IMMEDIATE v_stmt USING v_fact_table, v_from_date, v_to_date, v_last_update_date,
757 v_last_update_date, v_last_update_system_id, v_last_update_system_id;
758 v_l_stmt := 45;
759 COMMIT;
760 ELSE
761 -- Update TO_DATE to SYSDATE, LAST_UPDATE_DATE, and LAST_UPDATE_SYSTEM_ID
762
763 v_stmt := 'UPDATE MTH_RUN_LOG SET TO_DATE = :1, LAST_UPDATE_DATE = :2, LAST_UPDATE_SYSTEM_ID =:3 WHERE
764 FACT_TABLE =:4';
765 v_l_stmt := 50;
766 -- DBMS_OUTPUT.PUT_LINE('Updating'||v_stmt);
767 EXECUTE IMMEDIATE v_stmt USING v_to_date, v_last_update_date, v_last_update_system_id, v_fact_table;
768 v_l_stmt := 55;
769 COMMIT;
770 END IF;
771
772 EXCEPTION
773 WHEN e_not_allowed THEN
774 RAISE_APPLICATION_ERROR(-20201,'This fact CANNOT BE logged using the procedure at line '||v_l_stmt);
775 WHEN OTHERS THEN
776 RAISE_APPLICATION_ERROR(-20203,SQLERRM||'at '||v_l_stmt);
777 END;
778 -- End of DEVICE_PRE_LOG;
779
780 PROCEDURE TB_UPLOAD IS
781 v_colname VARCHAR2(30);
782 v_tl_colname VARCHAR2(30);
783 v_stmt VARCHAR2(32767);
784 v_stmt_no NUMBER;
785 CURSOR DISTINCT_COLUMN IS
786 SELECT DISTINCT DB_COL FROM MTH_TAG_READINGS_T_STG;
787
788
789 BEGIN
790
791 v_stmt_no := 5;
792 FOR DBCOL IN DISTINCT_COLUMN
793 LOOP
794 v_colname := DBCOL.DB_COL;
795 v_stmt_no := 10;
796 v_stmt := 'MERGE INTO MTH_EQUIPMENTS_EXT_B ED
797 USING (
798 SELECT * FROM MTH_TAG_READINGS_T_STG,(SELECT NVL(FND_GLOBAL.User_Id,-1)l_updated_by,NVL(FND_GLOBAL.Login_Id,-1)l_last_update_login FROM DUAL )D
799 WHERE DB_COL = '||''''||v_colname||''''||') TS
800 ON (';
801
802 v_stmt := v_stmt||'ED.EQUIPMENT_PK_KEY = TS.EQUIPMENT_FK_KEY AND
803 ED.READ_TIME = TS.READ_TIME)
804 WHEN MATCHED THEN
805 UPDATE
806 SET ED.'||v_colname||' = TS.TAG_DATA,
807 ED.LAST_UPDATE_DATE = ''''||SYSDATE||'''',
808 ED.LAST_UPDATED_BY = TS.l_updated_by,';
809
810 v_stmt := v_stmt||'ED.LAST_UPDATE_LOGIN = TS.l_last_update_login
811 WHEN NOT MATCHED THEN
812 INSERT ('||v_colname||',EXTENSION_ID, EQUIPMENT_PK_KEY,WORKORDER_FK_KEY,SEGMENT_FK_KEY,SHIFT_WORKDAY_FK_KEY, ITEM_FK_KEY, READ_TIME, ATTR_GROUP_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,';
813
814 v_stmt:=
815 v_stmt||'LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE,RECIPE_NUM,RECIPE_VERSION)
816 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.ITEM_FK_KEY, TS.READ_TIME,';
817
818 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)';
819
820 --DBMS_OUTPUT.PUT_LINE(v_stmt);
821 v_stmt_no := 20;
822 EXECUTE IMMEDIATE v_stmt;
823 COMMIT;
824
825 END LOOP;
826
827
828
829 EXCEPTION
830 WHEN INVALID_NUMBER THEN
831 RAISE_APPLICATION_ERROR(-20008,'The Tag Data you are tyring to insert is of Character Data Type. A number is expected instead.');
832 WHEN OTHERS THEN
833 RAISE_APPLICATION_ERROR(-20008,SQLERRM||' at '||v_stmt_no);
834
835 END;
836 -- End of TB_UPLOAD;
837
838 END MTH_UDA_PKG;
[Home] [Help]