1 PACKAGE BODY MTH_UDA_PKG AS
2 /*$Header: mthuntbb.pls 120.13.12020000.2 2012/10/18 15:59:21 sasuren 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;
15 e_no_pk_key EXCEPTION;
12 v_master_table VARCHAR2(200);
13 e_tname_not_found EXCEPTION;
14 e_issue_with_data 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 mth_util_pkg.log_msg('UPDATE_TO_PRIMARY_KEY start', mth_util_pkg.G_DBG_PROC_FUN_START);
24 mth_util_pkg.log_msg('P_ENTITY = ' || P_ENTITY , mth_util_pkg.G_DBG_PARAM_VAL);
25
26
27 -- Get the pk key
28 v_pk_column := MTH_UDA_PKG.Get_Mst_Pk_Name(p_entity);
29 IF (v_pk_column IS null) THEN
30 RAISE e_tname_not_found;
31 END IF;
32
33 /*
34 Make a check:
35 1. we should have only one row with db_col as null for each GROUP_ID, which will be the primary key.
36 2. if we have no row with db_col as null, then no primary key info has been provided.
37 */
38
39 FOR v_row IN c_null_check
40 LOOP
41 SELECT COUNT (1) INTO v_temp FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = v_row.GROUP_ID
42 AND DB_COL IS NULL;
43
44 IF (v_temp > 1) THEN -- Case 1
45 RAISE e_issue_with_data;
46 ELSIF (v_temp = 0) THEN -- Case 2
47 RAISE e_no_pk_key;
48 END IF;
49 END LOOP;
50
51 /* Once we have the column name, update those rows
52 in MTH_EXT_ATTR_T_STG, where db_col is null. This is
53 because, we expect only those rows to have db_col as null
54 which consists ATTR_VALUE as the primary key value. For
55 others since, meta data will be configured, db_col should not be
56 null.
57 */
58
59 v_stmt := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_pk_column||''''||' WHERE DB_COL IS NULL';
60 mth_util_pkg.log_msg('v_stmt : '||v_stmt,mth_util_pkg.G_DBG_DYN_SQL);
61 EXECUTE IMMEDIATE v_stmt;
62 COMMIT;
63 mth_util_pkg.log_msg('UPDATE_TO_PRIMARY_KEY end', mth_util_pkg.G_DBG_PROC_FUN_END);
64
65 EXCEPTION
66 WHEN e_tname_not_found THEN
67 mth_util_pkg.log_msg('Exception in UPDATE_TO_PRIMARY_KEY', mth_util_pkg.G_DBG_EXCEPTION);
68 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided');
69 WHEN e_issue_with_data THEN
70 mth_util_pkg.log_msg('Exception in UPDATE_TO_PRIMARY_KEY', mth_util_pkg.G_DBG_EXCEPTION);
71 RAISE_APPLICATION_ERROR(-20002,'There is an issue with data, one or more columns except primary key have NO meta data defined. Please recheck');
72 WHEN e_no_pk_key THEN
73 mth_util_pkg.log_msg('Exception in UPDATE_TO_PRIMARY_KEY', mth_util_pkg.G_DBG_EXCEPTION);
74 RAISE_APPLICATION_ERROR(-20003,'No primary Key column has been provided: A primary key column should not have meta data defined');
75
76 WHEN OTHERS THEN
77 mth_util_pkg.log_msg('Exception in UPDATE_TO_PRIMARY_KEY', mth_util_pkg.G_DBG_EXCEPTION);
78 RAISE_APPLICATION_ERROR(-20006, SQLERRM||v_stmt);
79
80 END;
81 -- End of UPDATE_TO_PRIMARY_KEY;
82
83 PROCEDURE NTB_UPLOAD_STANDARD_WHO(P_EXT_TBL_NAME IN VARCHAR2, P_EXTENSION_ID IN NUMBER, P_IF_ROW_EXISTS IN NUMBER) IS
84 --initialize variables here
85 l_updated_by NUMBER := 15;
86 l_last_update_login NUMBER := 15;
87 v_stmt VARCHAR2(20000);
88 -- main body
89 BEGIN
90 NULL; -- allow compilation
91 /*
92 Check whether we need to insert or update these values
93 by checking p_if_row_exists. if this is 0, we are inserting.
94 */
95
96 IF (p_if_row_exists = 0) THEN
97 /* Row does not exists, assign values to creation_date
98 and created_by*/
99 v_stmt := 'UPDATE '||p_ext_tbl_name||' SET LAST_UPDATE_DATE = '||''''||SYSDATE||''''||', LAST_UPDATED_BY = '||l_updated_by||', LAST_UPDATE_LOGIN = ';
100 v_stmt := v_stmt||l_last_update_login||', CREATED_BY = '||l_updated_by||', CREATION_DATE = '||''''||SYSDATE||''''||' WHERE EXTENSION_ID = '||p_extension_id;
101
102 ELSE
103 /* Row Exists, no need for creation_date and created_by */
104 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;
105 END IF;
106
107 --DBMS_OUTPUT.PUT_LINE (v_stmt);
108
109 EXECUTE IMMEDIATE v_stmt;
110 COMMIT;
111
112
113
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 RAISE_APPLICATION_ERROR(-20001,' in the procedure to update who columns');
118
119 END;
120 -- End of NTB_UPLOAD_STANDARD_WHO;
121
122 PROCEDURE NTB_UPLOADTL(P_ENTITY IN VARCHAR2, P_EXTID IN NUMBER, P_IF_ROW_EXISTS IN NUMBER) IS
123 --initialize variables here
124 v_pk_column VARCHAR2(30);
125 v_tname_b VARCHAR2(30);
126 v_tname_tl VARCHAR2(30);
127 e_tname_not_found EXCEPTION;
128 v_stmt VARCHAR2(30000);
129 -- main body
130 BEGIN
131 NULL; -- allow compilation
132 /*
133 Using the p_entity, get the primary key column name
134 */
135 v_pk_column := MTH_UDA_PKG.Get_Mst_Pk_Name(p_entity);
136 IF (v_pk_column = null) THEN
137 RAISE e_tname_not_found;
138 END IF;
139
140 /*
141 Now get the EXT_TL and EXT_B names
142 */
143 v_tname_b := MTH_UDA_PKG.Get_Ext_Table_Name(p_entity);
144 IF (v_tname_b = NULL) THEN
145 RAISE e_tname_not_found;
146 END IF;
147
148 v_tname_tl := MTH_UDA_PKG.Get_Ext_TL_Table_Name(p_entity);
149 IF (v_tname_tl = NULL) THEN
150 RAISE e_tname_not_found;
151 END IF;
152
153 /*
154 Now, check if it was already existing, if not, we will insert a new row,
155 else call the upload who procedure to update who columns
156 */
157
158 IF (p_if_row_exists = 0) THEN
159 -- INSERT A NEW ROW
160 --DBMS_OUTPUT.PUT_LINE('inserting the rows');
161 v_stmt := 'INSERT INTO '||v_tname_tl||'(EXTENSION_ID, ATTR_GROUP_ID, '||v_pk_column||',
165 EXECUTE IMMEDIATE v_stmt;
162 SOURCE_LANG, LANGUAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE)
163 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;
164 --DBMS_OUTPUT.PUT_LINE(v_stmt);
166 COMMIT;
167
168 ELSE
169 -- CALL to who upload column
170 --DBMS_OUTPUT.PUT_LINE('UPDating the who columns');
171 MTH_UDA_PKG.NTB_Upload_Standard_Who(v_tname_tl,p_extId,p_if_row_exists);
172 END IF;
173
174
175
176
177 EXCEPTION
178 WHEN e_tname_not_found THEN
179 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided at ');
180 WHEN OTHERS THEN
181 RAISE_APPLICATION_ERROR(-20002, SQLERRM);
182
183 END;
184 -- End of NTB_UPLOADTL;
185
186 PROCEDURE NTB_UPLOAD(P_TARGET IN VARCHAR2) IS
187 --initialize variables here
188 v_stmt_no NUMBER;
189 v_if_row_exists NUMBER;
190 v_attr_group NUMBER;
191 v_extId NUMBER;
192 v_cnt_rows NUMBER;
193 v_cnt_existing NUMBER;
194 v_col_name VARCHAR2(20);
195 v_col_val VARCHAR2(255);
196 v_stmt VARCHAR2(20000);
197 v_stmt_var VARCHAR2(20000);
198 v_mrc VARCHAR2(3);
199 v_date_val VARCHAR2(20);
200 e_tname_not_found EXCEPTION;
201 v_entity VARCHAR2(200) := p_target;
202 v_tname VARCHAR2(200);
203
204
205 /*
206 This cursor is used to loop through all the columns to
207 be filled in for a row. This helps to insert as well as
208 update a row in the EXT table.
209 */
210 CURSOR c_row_iterator(R_ID NUMBER, ATTR_GRP NUMBER) IS
211 SELECT STG.ATTR_GROUP_ID,STG.ATTR_NAME, STG.ATTR_VALUE, STG.DB_COL FROM MTH_EXT_ATTR_T_STG STG
212 WHERE STG.GROUP_ID = R_ID AND STG.ATTR_GROUP_ID = ATTR_GRP;
213
214 /*
215 This cursor is used to find all the attribute groups ids having
216 the same row ids and process the same.
217 */
218 CURSOR c_row_iterator1(GROUP_ID1 NUMBER) IS
219 SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;
220
221 /*
222 This cursor first gets all the rows for which DB_COL is null.
223 Essentially, these are going to be the ones which are the primary
224 keys of the table. This helps to locate a particular row and to decide
225 whether we update or insert a new row.
226 */
227 CURSOR c_row_iterator2(GROUP_ID2 NUMBER, AID NUMBER) IS
228 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;
229
230 /*This cursor will get all the NAME VALUE pair for which c_unique_key_flag = 'Y' */
231 CURSOR c_unique_key_flag(GROUP_ID3 NUMBER, AID3 NUMBER) IS
232 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';
233
234 -- main body
235 BEGIN
236 NULL; -- allow compilation
237 mth_util_pkg.log_msg('NTB_UPLOAD start', mth_util_pkg.G_DBG_PROC_FUN_START);
238 mth_util_pkg.log_msg('P_TARGET = ' || P_TARGET , mth_util_pkg.G_DBG_PARAM_VAL);
239 -- Call the procedure to rename columns to the pkey columns
240 MTH_UDA_PKG.Update_To_Primary_Key(v_entity);
241
242
243 -- Code to get find out table name depending on the entity input
244 v_stmt_no := 5;
245 v_tname := MTH_UDA_PKG.Get_Ext_Table_Name(v_entity);
246 IF (v_tname is NULL) THEN
247 RAISE e_tname_not_found;
248 END IF;
249
250 mth_util_pkg.log_msg('The target table is '||v_tname, mth_util_pkg.G_DBG_VAR_VAL);
251 /*
252 Select the different row ids present, each different row id refers to the data for a single
253 row. It is possible to have one to many relationship between row id and attribute group.
254 */
255 v_stmt_no:= 10;
256
257 /*Changed the following select statement
258 SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;
259 due to bug 8349873. Due to the above statement, the logic fails when we have discontinous group ids such as 1,3,5 etc.
260 Changing the statement to select max group_id allows complete iteration through
261 discontinous set.
262 */
263 SELECT NVL(MAX(GROUP_ID),0) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG; --Added NVL for bug 14465600
264 mth_util_pkg.log_msg('v_cnt_rows = ' || v_cnt_rows , mth_util_pkg.G_DBG_VAR_VAL);
265 /* Loop through each row of data. A row of data is identified as having the
266 same row id.
267 */
268 --DBMS_OUTPUT.PUT_LINE('Entering logic to process one set of rows with same row id');
269 FOR VAR IN 1..v_cnt_rows
270 LOOP
271 v_stmt_no := 20;
272
273 /*
274 This loop will help to process data for one row id, with provision
275 to have more than one attribute group id having the same row.
276 */
277 FOR A_ID IN c_row_iterator1(VAR)
278 LOOP
279 v_attr_group:= A_ID.ATTR_GROUP_ID; --Get the attribute group id in a variable
280 mth_util_pkg.log_msg('The attribute group is '||v_attr_group, mth_util_pkg.G_DBG_VAR_VAL);
281 mth_util_pkg.log_msg('Processing Row '||VAR, mth_util_pkg.G_DBG_VAR_VAL);
282 mth_util_pkg.g_debug_indent_level := mth_util_pkg.g_debug_indent_level + 1;
283
284 /*
285 This variable helps to prepare statement to get the EXT ID value
286 for a particular row
287 */
288 v_stmt_var := 'SELECT EXTENSION_ID FROM '||v_tname||' WHERE ATTR_GROUP_ID ='||v_attr_group;
289
290 /*
291 This variable helps to prepare statement to see whether the particular row
292 for which data is being processed is present in the EXT table or not.
293 */
294 v_stmt := 'SELECT COUNT(1) FROM '||v_tname||' WHERE ATTR_GROUP_ID ='||v_attr_group;
295
296 /*
297 This statement helps to prepare the above statements correctly
298 by helping to choose proper WHERE CLAUSES. This is achieved by
299 using the v_cnt_existing varaiable
300 */
301 v_stmt_no := 30;
302 SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
303 WHERE DB_COL IS NULL AND
304 ATTR_GROUP_ID = v_attr_group AND
305 GROUP_ID = VAR;
306 mth_util_pkg.log_msg('No of primary key columns = '||v_cnt_existing, mth_util_pkg.G_DBG_VAR_VAL);
307
308 /*
309 This cursor first gets all the rows for which DB_COL is null.
310 Essentially, these are going to be the ones which are the primary
311 keys of the table. This helps to locate a particular row and to decide
312 whether we update or insert a new row.
313 */
314 --DBMS_OUTPUT.PUT_LINE('Preparing query using pkey columns');
315 FOR VAR2 IN c_row_iterator2(VAR, v_attr_group)
316 LOOP
317 v_col_name := VAR2.ATTR_NAME;
318 v_col_val := VAR2.ATTR_VALUE;
319 v_cnt_existing := v_cnt_existing -1;
320
321 /*
322 If the pkey columns happen to be date columns, we need
323 to add the logic to process the data by converting it
324 to date. This has been done in the loops which follow
325 */
326 v_stmt := v_stmt||' AND '||v_col_name||'='||v_col_val;
327 v_stmt_var := v_stmt_var||' AND '||v_col_name||'='||v_col_val;
328
329 END LOOP;
330
331
332 /* Now add where clause to check for unique keys. For a multi row
333 attribute group, distinction between attribute group id and pkeys would
334 not suffice. For such a case, we designate few attribute columns as unique.
335 These attributes allow us to distinguish between multi row data.
336 Skip this check if the attribute group is not multi row.
337 */
338 --DBMS_OUTPUT.PUT_LINE('Now check logic for MULTI ROW');
339 v_stmt_no := 40;
340 SELECT MULTI_ROW_CODE INTO v_mrc FROM EGO_ATTR_GROUPS_V
341 WHERE ATTR_GROUP_ID = v_attr_group;
342
343 IF v_mrc = 'Y' THEN
344 mth_util_pkg.log_msg('Attribute group is MULTI ROW',mth_util_pkg.G_DBG_OTH);
345 /* LOGIC FOR multi row attribute groups */
346 SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
347 WHERE UNIQUE_KEY_FLAG='Y' AND
348 ATTR_GROUP_ID = v_attr_group AND
349 GROUP_ID = VAR;
350 mth_util_pkg.log_msg('No of unique columns = '||v_cnt_existing, mth_util_pkg.G_DBG_VAR_VAL);
351
352 FOR C IN c_unique_key_flag(VAR, v_attr_group)
353 LOOP
354 -- DBMS_OUTPUT.PUT_LINE('Adding MULTI ROW column where clause to the earlier query');
355 v_col_name := C.DB_COL;
356 v_col_val := C.ATTR_VALUE;
357 v_cnt_existing := v_cnt_existing-1;
358 v_stmt_no := 50;
359
360 /*
361 Check for proper where clause. This is done to
362 get the correct timestamp for date columns.
363 */
364 -- DBMS_OUTPUT.PUT_LINE('Statement so far '||STMT);
365
366 IF SUBSTR(v_col_name,1,1)= 'D' THEN
367 -- DBMS_OUTPUT.PUT_LINE('We have a unique date column');
368 v_date_val := TO_CHAR(TO_DATE(v_col_val,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
369 v_stmt := v_stmt||' AND '||v_col_name||'='||'TO_DATE('||''''||v_date_val||''''||',''MM/DD/YYYY HH24:MI:SS'')';
370 v_stmt_var := v_stmt_var||' AND '||v_col_name||'='||'TO_DATE('||''''||v_date_val||''''||',''MM/DD/YYYY HH24:MI:SS'')';
371 ELSE
372 v_stmt := v_stmt||' AND '||v_col_name||'='||''''||v_col_val||'''';
373 v_stmt_var := v_stmt_var||' AND '||v_col_name||'='||''''||v_col_val||'''';
374 END IF;
375
376 END LOOP;
377 ELSE
378 null;
379 --DBMS_OUTPUT.PUT_LINE('The A Group is single row');
380 END IF;
381
382 -- DBMS_OUTPUT.PUT_LINE('The updated statements after unique key check are ');
383 -- DBMS_OUTPUT.PUT_LINE(v_stmt);
384 -- DBMS_OUTPUT.PUT_LINE(v_stmt_var);
385
386 v_stmt_no := 60;
387 /*
388 Get the count of the row in the variable v_if_row_exists
389 If the count is 0, it means the row with these values
390 of pkeys are not present. So proceed with inserting a
391 new surrogate key value
392 */
393 mth_util_pkg.log_msg(v_stmt,mth_util_pkg.G_DBG_DYN_SQL);
394 EXECUTE IMMEDIATE v_stmt INTO v_if_row_exists ;
395
396 IF v_if_row_exists = 0 THEN
397 --DBMS_OUTPUT.PUT_LINE('This row is not present in the EXT table');
398 --DBMS_OUTPUT.PUT_LINE('INSERT THE NEW ROW');
399
400
401 v_stmt_no := 70;
402 v_stmt := 'SELECT EGO_EXTFWK_S.NEXTVAL FROM DUAL';
403 EXECUTE IMMEDIATE v_stmt INTO v_extId;
404
405 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||''''||' )';
406 --DBMS_OUTPUT.PUT_LINE('The new EXT ID is'||v_extId);
407 --DBMS_OUTPUT.PUT_LINE('The new EXT ID is'||v_stmt);
408 v_stmt_no := 80;
409 mth_util_pkg.log_msg(v_stmt,mth_util_pkg.G_DBG_DYN_SQL);
410 EXECUTE IMMEDIATE v_stmt USING v_extId, v_attr_group ;
411 --COMMIT;
412
413 ELSE
417 mth_util_pkg.log_msg(v_stmt_var,mth_util_pkg.G_DBG_DYN_SQL);
414 -- DBMS_OUTPUT.PUT_LINE('This data is already present in the EXT table');
415 -- DBMS_OUTPUT.PUT_LINE('UPDATE THE DATA');
416 v_stmt_no := 90;
418 EXECUTE IMMEDIATE v_stmt_var INTO v_extId;
419 --DBMS_OUTPUT.PUT_LINE('The EXT ID for this data is '||v_extId);
420 END IF;
421
422 /*
423 Iterate over all the name value pair for the row id
424 to insert/update in the EXT Table
425 */
426 --DBMS_OUTPUT.PUT_LINE('Iterate over all the columns to insert/update');
427 FOR EXT_VAL IN c_row_iterator(VAR,v_attr_group)
428 LOOP
429 /*
430 Check whether this is a pkey value,
431 in such cases, DB_COL will be null
432 */
433 IF EXT_VAL.DB_COL IS NULL THEN
434 v_col_name := EXT_VAL.ATTR_NAME;
435 ELSE
436 v_col_name := EXT_VAL.DB_COL;
437 END IF;
438
439 --DBMS_OUTPUT.PUT_LINE('The column name to be updated/inserted '||v_col_name);
440
441 /*
442 Check for a date column to use the appropiate TO_DATE FUNCTION
443 */
444 IF SUBSTR(v_col_name,1,1) = 'D' THEN
445 -- DBMS_OUTPUT.PUT_LINE('A DATE COLUMN');
446
447 -- DBMS_OUTPUT.PUT_LINE('The TIMESTAMP is ');
448 -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(TO_DATE(EXT_VAL.ATTR_VALUE,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS'));
449 v_date_val := TO_CHAR(TO_DATE(EXT_VAL.ATTR_VALUE,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
450 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;
451 ELSE
452 v_col_val := EXT_VAL.ATTR_VALUE;
453 --DBMS_OUTPUT.PUT_LINE('The data is '||v_col_val);
454 v_stmt := 'UPDATE '||v_tname||' SET '||v_col_name||' = '||''''||v_col_val||''''||' WHERE EXTENSION_ID = '||v_extId;
455 END IF;
456
457 --DBMS_OUTPUT.PUT_LINE('The statement to be executed is '||v_stmt);
458
459 v_stmt_no := 100;
460 mth_util_pkg.log_msg(v_stmt,mth_util_pkg.G_DBG_DYN_SQL);
461 EXECUTE IMMEDIATE v_stmt;
462
463 END LOOP; -- Completing insertion or updating a single row
464
465 --Commit after all the attributes for one group id is
466 --updated
467 COMMIT;
468 mth_util_pkg.g_debug_indent_level := mth_util_pkg.g_debug_indent_level - 1;
469 mth_util_pkg.log_msg('Processed Row '||VAR, mth_util_pkg.G_DBG_VAR_VAL);
470
471 /*
472 call procedure to update standard who columns
473 */
474 --DBMS_OUTPUT.PUT_LINE('calling who procedure');
475 MTH_UDA_PKG.NTB_Upload_Standard_Who(v_tname,v_extId, v_if_row_exists);
476
477 /*
478 Call the procedure to update TL Table
479 */
480 MTH_UDA_PKG.NTB_UploadTL(v_entity,v_extId,v_if_row_exists);
481
482 END LOOP;
483 END LOOP;
484 mth_util_pkg.log_msg('NTB_UPLOAD end', mth_util_pkg.G_DBG_PROC_FUN_END);
485
486
487
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490 mth_util_pkg.log_msg('Exception in NTB_UPLOAD', mth_util_pkg.G_DBG_EXCEPTION);
491 RAISE_APPLICATION_ERROR(-20002,'No data found at line number '||v_stmt_no);
492
493 WHEN e_tname_not_found THEN
494 mth_util_pkg.log_msg('Exception in NTB_UPLOAD', mth_util_pkg.G_DBG_EXCEPTION);
495 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided at '||v_stmt_no);
496
497 WHEN OTHERS THEN
498 mth_util_pkg.log_msg('Exception in NTB_UPLOAD', mth_util_pkg.G_DBG_EXCEPTION);
499 RAISE_APPLICATION_ERROR(-20003,SQLERRM||' at '||v_stmt_no);
500 ROLLBACK;
501 END;
502 -- End of NTB_UPLOAD;
503
504 FUNCTION GET_MST_TABLE_NAME(P_ENTITY IN VARCHAR2) RETURN VARCHAR2 IS
505 --initialize variables here
506 v_entity_code NUMBER;
507 v_mst_tbl_name VARCHAR2(50) DEFAULT NULL;
508 -- main body
509 BEGIN
510 NULL; -- allow compilation
511
512 -- Get the code for the entity
513 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
514
515 -- Check if v_entity_code is -1, if so, return NULL
516 IF (v_entity_code = -1) THEN
517 RETURN NULL;
518 END IF;
519
520 CASE
521 WHEN v_entity_code = 1 THEN v_mst_tbl_name := 'MTH_EQUIPMENTS_D';
522 WHEN v_entity_code = 2 THEN v_mst_tbl_name := 'MTH_ITEMS_D';
523 WHEN v_entity_code = 3 THEN v_mst_tbl_name := 'MTH_OTHERS_D';
524 WHEN v_entity_code = 4 THEN v_mst_tbl_name := 'MTH_PRODUCTION_SCHEDULES_F';
525 WHEN v_entity_code = 5 THEN v_mst_tbl_name := 'MTH_PRODUCTION_SEGMENTS_F';
526 WHEN v_entity_code = 6 THEN v_mst_tbl_name := 'MTH_ALL_ENTITIES_V';
527
528 END CASE;
529
530 RETURN v_mst_tbl_name;
531
532 END;
533 -- End of GET_MST_TABLE_NAME;
534
535 FUNCTION GET_MST_PK_NAME(P_ENTITY IN VARCHAR2) RETURN VARCHAR2 IS
536 --initialize variables here
537 v_entity_code NUMBER;
538 v_mst_pk_name VARCHAR2(50) DEFAULT NULL;
539 -- main body
540 BEGIN
541 NULL; -- allow compilation
542 -- Get the code for the entity
543 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
544
545 -- Check if v_entity_code is -1, if so, return NULL
546 IF (v_entity_code = -1) THEN
547 RETURN NULL;
548 END IF;
549
550 CASE
551 WHEN v_entity_code = 1 THEN v_mst_pk_name := 'EQUIPMENT_PK_KEY';
552 WHEN v_entity_code = 2 THEN v_mst_pk_name := 'ITEM_PK_KEY';
553 WHEN v_entity_code = 3 THEN v_mst_pk_name := 'OTHER_PK_KEY';
554 WHEN v_entity_code = 4 THEN v_mst_pk_name := 'WORKORDER_PK_KEY';
555 WHEN v_entity_code = 5 THEN v_mst_pk_name := 'SEGMENT_PK_KEY';
556 WHEN v_entity_code = 6 THEN v_mst_pk_name := 'ENTITY_PK_KEY';
557 END CASE;
558
559 RETURN v_mst_pk_name;
560 END;
561 -- End of GET_MST_PK_NAME;
562
563 FUNCTION GET_EXT_TL_TABLE_NAME(P_ENTITY IN VARCHAR2) RETURN VARCHAR2 IS
567 -- main body
564 --initialize variables here
565 v_entity_code NUMBER;
566 v_ext_tbl_name VARCHAR2(50) DEFAULT NULL;
568 BEGIN
569 NULL; -- allow compilation
570
571 -- Get the code for the entity
572 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
573
574 -- Check if v_entity_code is -1, if so, return NULL
575 IF (v_entity_code = -1) THEN
576 RETURN NULL;
577 END IF;
578
579 CASE
580 WHEN v_entity_code = 1 THEN v_ext_tbl_name := 'MTH_EQUIPMENTS_EXT_TL';
581 WHEN v_entity_code = 2 THEN v_ext_tbl_name := 'MTH_ITEMS_EXT_TL';
582 WHEN v_entity_code = 3 THEN v_ext_tbl_name := 'MTH_OTHERS_EXT_TL';
583 WHEN v_entity_code = 4 THEN v_ext_tbl_name := 'MTH_PRODUCTION_SCHEDULE_EXT_TL';
584 WHEN v_entity_code = 5 THEN v_ext_tbl_name := 'MTH_PRODUCTION_SEGMENTS_EXT_TL';
585 WHEN v_entity_code = 6 THEN v_ext_tbl_name := 'MTH_USER_ENTITIES_EXT_TL';
586 END CASE;
587
588 RETURN v_ext_tbl_name;
589 END;
590 -- End of GET_EXT_TL_TABLE_NAME;
591
592 FUNCTION GET_EXT_TABLE_NAME(P_ENTITY IN VARCHAR2) RETURN VARCHAR2 IS
593 --initialize variables here
594 v_entity_code NUMBER;
595 v_ext_tbl_name VARCHAR2(50) DEFAULT NULL;
596 -- main body
597 BEGIN
598 NULL; -- allow compilation
599 -- Get the code for the entity
600 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
601
602 -- Check if v_entity_code is -1, if so, return NULL
603 IF (v_entity_code = -1) THEN
604 RETURN NULL;
605 END IF;
606
607 CASE
608 WHEN v_entity_code = 1 THEN v_ext_tbl_name := 'MTH_EQUIPMENTS_EXT_B';
609 WHEN v_entity_code = 2 THEN v_ext_tbl_name := 'MTH_ITEMS_EXT_B';
610 WHEN v_entity_code = 3 THEN v_ext_tbl_name := 'MTH_OTHERS_EXT_B';
611 WHEN v_entity_code = 4 THEN v_ext_tbl_name := 'MTH_PRODUCTION_SCHEDULES_EXT_B';
612 WHEN v_entity_code = 5 THEN v_ext_tbl_name := 'MTH_PRODUCTION_SEGMENTS_EXT_B';
613 WHEN v_entity_code = 6 THEN v_ext_tbl_name := 'MTH_USER_ENTITIES_EXT_B';
614
615 END CASE;
616
617 RETURN v_ext_tbl_name;
618
619 END;
620 -- End of GET_EXT_TABLE_NAME;
621
622 FUNCTION GET_ENTITY_CODE(P_ENTITY IN VARCHAR2) RETURN NUMBER IS
623 --initialize variables here
624 v_entity_code NUMBER;
625 v_entity VARCHAR2(50);
626 -- main body
627 BEGIN
628 NULL; -- allow compilation
629 -- First make it case insensitive
630 v_entity := UPPER(p_entity);
631
632 CASE
633 WHEN v_entity = 'EQUIPMENTS' THEN v_entity_code := 1;
634 WHEN v_entity = 'ITEMS' THEN v_entity_code := 2;
635 WHEN v_entity = 'OTHERS' THEN v_entity_code := 3;
636 WHEN v_entity = 'PRODUCTION_SCHEDULES' THEN v_entity_code := 4;
637 WHEN v_entity = 'PRODUCTION_SEGMENTS' THEN v_entity_code := 5;
638 WHEN v_entity = 'USER_ENTITIES' THEN v_entity_code := 6;
639
640 ELSE v_entity_code := -1;
641 END CASE;
642 RETURN v_entity_code;
643
644 END;
645 -- End of GET_ENTITY_CODE;
646
647
648
649 PROCEDURE DEVICE_POST_LOG(P_TARGET IN VARCHAR2) IS
650 -- Intialize variables
651 e_duplicate_run EXCEPTION;
652 e_not_allowed EXCEPTION;
653 v_l_stmt NUMBER; -- To track the line where the error has occcured
654 v_count NUMBER;
655 v_to_date DATE;
656 v_fact_table VARCHAR2(50) := UPPER(p_target); -- FACT Table Name
657 v_last_update_date DATE; -- Standard WHO column
658 v_last_update_system_id NUMBER; -- Standard WHO column
659 v_stmt VARCHAR2(500);
660
661
662 -- main body
663 BEGIN
664
665 /*Make a check against v_fact_table, it should be MTH_EQUIPMENTS_EXT_B only and nothing else*/
666 v_l_stmt := 10;
667 IF v_fact_table <> 'MTH_EQUIPMENTS_EXT_B' THEN
668 RAISE e_not_allowed;
669 END IF;
670
671 -- Get the sysdate
672 v_l_stmt := 20;
673 SELECT SYSDATE INTO v_last_update_date FROM DUAL;
674
675 -- get the unassigned value for system id
676 v_l_stmt := 30;
677 SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() INTO v_last_update_system_id FROM DUAL;
678
679
680 -- Check whether the pre map operation has been run or not
681 v_l_stmt := 40;
682 SELECT COUNT(FACT_TABLE) INTO v_count
683 FROM MTH_RUN_LOG
684 WHERE FACT_TABLE = v_fact_table;
685
686 -- DBMS_OUTPUT.PUT_LINE('THE COUNT IS '||v_count);
687
688 IF v_count <> 0 THEN
689
690 -- Another check
691 v_l_stmt := 45;
692 SELECT TO_DATE INTO v_to_date
693 FROM MTH_RUN_LOG
694 WHERE FACT_TABLE = v_fact_table;
695
696 -- DBMS_OUTPUT.PUT_LINE('THE TO_DATE IS '||v_to_date);
697
698 END IF;
699
700 v_l_stmt := 50;
701 IF (v_count = 0)OR(v_to_date IS NULL) THEN
702 -- Log is being run for the first time
703 RAISE e_duplicate_run;
704 ELSE
705 -- Update TO_DATE to SYSDATE, LAST_UPDATE_DATE, and LAST_UPDATE_SYSTEM_ID
706
707
708 v_stmt := 'UPDATE MTH_RUN_LOG SET FROM_DATE = TO_DATE, LAST_UPDATE_DATE = :1, LAST_UPDATE_SYSTEM_ID =:2 WHERE
709 FACT_TABLE =:3';
710
711 -- DBMS_OUTPUT.PUT_LINE('UPDATING THE LOG TABLE '||v_stmt);
712
713 v_l_stmt := 60;
714 EXECUTE IMMEDIATE v_stmt USING v_last_update_date, v_last_update_system_id, v_fact_table;
715 v_l_stmt := 65;
716 COMMIT;
717
718 v_l_stmt := 70;
719 v_stmt := 'UPDATE MTH_RUN_LOG SET TO_DATE = NULL WHERE
720 FACT_TABLE =:1';
721
722 -- DBMS_OUTPUT.PUT_LINE('UPDATING THE LOG TABLE '||v_stmt);
723
724 EXECUTE IMMEDIATE v_stmt USING v_fact_table;
725 v_l_stmt := 75;
726 COMMIT;
727 END IF;
728
729 EXCEPTION
730 WHEN e_not_allowed THEN
734 WHEN OTHERS THEN
731 RAISE_APPLICATION_ERROR(-20201,'This fact CANNOT BE logged using the procedure at line '||v_l_stmt);
732 WHEN e_duplicate_run THEN
733 RAISE_APPLICATION_ERROR (-20201,'Pre Map logging not available, run the load first, ABORTING');
735 RAISE_APPLICATION_ERROR(-20203,SQLERRM||'at line '||v_l_stmt);
736 END;
737 -- End of DEVICE_POST_LOG;
738
739 PROCEDURE DEVICE_PRE_LOG(P_TARGET IN VARCHAR2) IS
740 -- Intialize variables
741 v_l_stmt NUMBER; -- To track the line where the error has occcured
742 v_to_date DATE; -- To Date
743 v_from_date DATE := TO_DATE('01/01/1900','MM-DD-YYYY HH:MIAM'); -- From Date # Bug fix: need to specify the date format
744 v_cnt NUMBER;
745 v_fact_table VARCHAR2(50) := UPPER(p_target); -- The instance name in the ETL
746 /*
747 CREATION_DATE will be same as v_last_update_date if the load is run for the first time,
748 else if the load is being run again, CREATION_DATE would already be populated, so there is no
749 need for this variable
750 */
751 v_last_update_date DATE; -- Standard WHO column
752 /*
753 CREATION_SYSTEM_ID will be same as LAST_UPDATE_SYSTEM_ID if the load is run for the first time,
754 else if the load is being run again, CREATION_SYSTEM_ID would already be populated, so there is no
755 need for this variable
756 */
757 v_last_update_system_id NUMBER; -- Standard WHO column
758 v_stmt VARCHAR2(500);
759 e_not_allowed EXCEPTION;
760
761
762 -- main body
763 BEGIN
764
765 /*Make a check against v_fact_table, it should be MTH_EQUIPMENTS_EXT_B only and nothing else*/
766 v_l_stmt := 5;
767 IF v_fact_table <> 'MTH_EQUIPMENTS_EXT_B' THEN
768 RAISE e_not_allowed;
769 END IF;
770
771 -- get the system date
772 v_l_stmt := 10;
773 SELECT SYSDATE INTO v_to_date FROM DUAL;
774
775 v_last_update_date := v_to_date;
776
777 -- get the unassigned value for system id
778 v_l_stmt := 15;
779 SELECT MTH_UTIL_PKG.MTH_UA_GET_VAL() INTO v_last_update_system_id FROM DUAL;
780
781 -- Check whether the load is being run for the first time or not
782 v_l_stmt := 20;
783 SELECT COUNT(FACT_TABLE) INTO v_cnt
784 FROM MTH_RUN_LOG
785 WHERE FACT_TABLE = v_fact_table;
786
787 -- DBMS_OUTPUT.PUT_LINE('Count of the fact table '||v_cnt);
788
789 v_l_stmt := 30;
790 IF v_cnt = 0 THEN
791 -- Log is being run for the first time
792
793 v_stmt := 'INSERT INTO MTH_RUN_LOG (FACT_TABLE, FROM_DATE, TO_DATE, CREATION_DATE,
794 LAST_UPDATE_DATE, CREATION_SYSTEM_ID, LAST_UPDATE_SYSTEM_ID) VALUES (:1, :2, :3, :4, :5, :6, :7)';
795 v_l_stmt := 40;
796 -- DBMS_OUTPUT.PUT_LINE('Inserting'||v_stmt);
797 EXECUTE IMMEDIATE v_stmt USING v_fact_table, v_from_date, v_to_date, v_last_update_date,
798 v_last_update_date, v_last_update_system_id, v_last_update_system_id;
799 v_l_stmt := 45;
800 COMMIT;
801 ELSE
802 -- Update TO_DATE to SYSDATE, LAST_UPDATE_DATE, and LAST_UPDATE_SYSTEM_ID
803
804 v_stmt := 'UPDATE MTH_RUN_LOG SET TO_DATE = :1, LAST_UPDATE_DATE = :2, LAST_UPDATE_SYSTEM_ID =:3 WHERE
805 FACT_TABLE =:4';
806 v_l_stmt := 50;
807 -- DBMS_OUTPUT.PUT_LINE('Updating'||v_stmt);
808 EXECUTE IMMEDIATE v_stmt USING v_to_date, v_last_update_date, v_last_update_system_id, v_fact_table;
809 v_l_stmt := 55;
810 COMMIT;
811 END IF;
812
813 EXCEPTION
814 WHEN e_not_allowed THEN
815 RAISE_APPLICATION_ERROR(-20201,'This fact CANNOT BE logged using the procedure at line '||v_l_stmt);
816 WHEN OTHERS THEN
817 RAISE_APPLICATION_ERROR(-20203,SQLERRM||'at '||v_l_stmt);
818 END;
819 -- End of DEVICE_PRE_LOG;
820
821 PROCEDURE TB_UPLOAD IS
822 v_colname VARCHAR2(30);
823 v_tl_colname VARCHAR2(30);
824 v_stmt VARCHAR2(32767);
825 v_stmt_no NUMBER;
826 CURSOR DISTINCT_COLUMN IS
827 SELECT DISTINCT DB_COL FROM MTH_TAG_READINGS_T_STG;
828
829
830 BEGIN
831
832 v_stmt_no := 5;
833 FOR DBCOL IN DISTINCT_COLUMN
834 LOOP
835 v_colname := DBCOL.DB_COL;
836 v_stmt_no := 10;
837 v_stmt := 'MERGE INTO MTH_EQUIPMENTS_EXT_B ED
838 USING (
839 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
840 WHERE DB_COL = '||''''||v_colname||''''||') TS
841 ON (';
842
843 v_stmt := v_stmt||'ED.EQUIPMENT_PK_KEY = TS.EQUIPMENT_FK_KEY AND
844 ED.READ_TIME = TS.READ_TIME)
845 WHEN MATCHED THEN
846 UPDATE
847 SET ED.'||v_colname||' = TS.TAG_DATA,
848 ED.LAST_UPDATE_DATE = ''''||SYSDATE||'''',
849 ED.LAST_UPDATED_BY = TS.l_updated_by,';
850
851 v_stmt := v_stmt||'ED.LAST_UPDATE_LOGIN = TS.l_last_update_login
852 WHEN NOT MATCHED THEN
853 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,';
854
855 v_stmt:=
856 v_stmt||'LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE,RECIPE_NUM,RECIPE_VERSION)
857 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,';
858
859 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)';
860
861 --DBMS_OUTPUT.PUT_LINE(v_stmt);
862 v_stmt_no := 20;
863 EXECUTE IMMEDIATE v_stmt;
864 COMMIT;
865
866 END LOOP;
867
868
869
870 EXCEPTION
871 WHEN INVALID_NUMBER THEN
872 RAISE_APPLICATION_ERROR(-20008,'The Tag Data you are tyring to insert is of Character Data Type. A number is expected instead.');
873 WHEN OTHERS THEN
874 RAISE_APPLICATION_ERROR(-20008,SQLERRM||' at '||v_stmt_no);
875
876 END;
877 -- End of TB_UPLOAD;
878
879 /* Support Composite Pk Key */
880
881 PROCEDURE GET_MST_COMPOSITE_PK_NAME(P_ENTITY IN VARCHAR2,v_mst_pk_name OUT NOCOPY v_mst_pk_key_columns,
882 v_entity_code OUT NUMBER, v_csv_columns OUT NOCOPY v_csv_column_names)
883 IS
884 --initialize variables here
885 --v_entity_code NUMBER;
886 --v_mst_pk_name1 VARCHAR2(50) DEFAULT NULL;
887 --v_mst_pk_name2 VARCHAR2(30) DEFAULT NULL;
888
889 -- main body
890 BEGIN
891 NULL; -- allow compilation
892 -- Get the code for the entity
893 v_entity_code := MTH_UDA_PKG.Get_Entity_Code(p_entity);
894
895 -- Check if v_entity_code is -1, if so, return NULL
896 --IF (v_entity_code = -1) THEN
897 -- RETURN NULL;
898 --END IF;
899
900 CASE
901 WHEN v_entity_code = 6 THEN
902 v_mst_pk_name := v_mst_pk_key_columns('ENTITY_PK_KEY', 'ENTITY_TYPE');
903 v_csv_columns := v_csv_column_names('USER_ENTITY','ENTITY_TYPE');
904 END CASE;
905
906 END; -- End GET_MST_COMPOSITE_PK_NAME
907
908 PROCEDURE UPDATE_COMPOSITE_PRIMARY_KEY(P_ENTITY IN VARCHAR2) IS
909 --initialize variables here
910 v_entity_code NUMBER;
911 v_mst_pk_column v_mst_pk_key_columns;
912 v_csv_cols v_csv_column_names;
913 v_stmt1 VARCHAR2(1000);
914 v_stmt2 VARCHAR2(200);
915 v_temp NUMBER;
916 v_i_num NUMBER;
917 v_t_num NUMBER;
918 v_master_table VARCHAR2(200);
919 e_tname_not_found EXCEPTION;
920 e_issue_with_data EXCEPTION;
921 e_no_pk_key EXCEPTION;
922 v_pk_column1 VARCHAR2(40);
923 v_pk_column2 VARCHAR2(30);
924 ctr_csv_col NUMBER;
925 v_csv_ctr NUMBER;
926
927
928
929 CURSOR c_null_check IS
930 SELECT DISTINCT GROUP_ID FROM MTH_EXT_ATTR_T_STG;
931 -- main body
932 BEGIN
933 --NULL; -- allow compilation
934
935
936
937 -- Get the pk key
938 --EXECUTE MTH_UDA_PKG.GET_MST_COMPOSITE_PK_NAME(p_entity,v_pk_column1,v_pk_column2);
939 --EXECUTE IMMEDIATE
940 MTH_UDA_PKG.GET_MST_COMPOSITE_PK_NAME(p_entity,v_mst_pk_column,v_entity_code, v_csv_cols);
941 --IF (v_pk_column IS NULL OR v_pk_column2 IS NULL ) THEN
942 IF (v_mst_pk_column IS NULL OR v_entity_code = -1) THEN
943 RAISE e_tname_not_found;
944 END IF;
945
946 /*
947 Make a check:
948 1. if we have no row with db_col as null, then no primary key info has been provided.
949 */
950
951 FOR v_row IN c_null_check
952 LOOP
953 SELECT COUNT (1) INTO v_temp FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID = v_row.GROUP_ID
954 AND DB_COL IS NULL;
955
956 IF (v_temp > v_mst_pk_column.count) THEN -- Case 1
957 RAISE e_issue_with_data;
958 ELSIF (v_temp = 0) THEN -- Case 2
959 RAISE e_no_pk_key;
960 END IF;
961 END LOOP;
962
963 /* Once we have the column name, update those rows
964 in MTH_EXT_ATTR_T_STG, where db_col is null. This is
965 because, we expect only those rows to have db_col as null
966 which consists ATTR_VALUE as the primary key value. For
967 others since, meta data will be configured, db_col should not be
968 null.
969 */
970
971 --The assumption is the csv columns are in the same order as the primary key columns.
972 v_csv_ctr := v_csv_cols.FIRST;
973 For ctr in v_mst_pk_column.FIRST..v_mst_pk_column.LAST
974 LOOP
975 v_stmt1 := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_mst_pk_column(ctr)||''''||' WHERE DB_COL IS NULL AND ATTR_NAME = ' || ''''|| v_csv_cols(v_csv_ctr) || '''';
976 --DBMS_OUTPUT.PUT_LINE(v_stmt1);
977 -- v_stmt2 := 'UPDATE MTH_EXT_ATTR_T_STG SET ATTR_NAME = '||''''||v_pk_column(ctr)||''''||' WHERE DB_COL IS NULL ' AND ATTR_NAME = 'ENTITY_TYPE';
978 EXECUTE IMMEDIATE v_stmt1;
979 -- EXECUTE IMMEDIATE v_stmt2;
980 v_csv_ctr := v_csv_cols.NEXT(v_csv_ctr);
981
982 END LOOP;
983 COMMIT;
984
985 EXCEPTION
986 WHEN e_tname_not_found THEN
987 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided');
988 WHEN e_issue_with_data THEN
989 RAISE_APPLICATION_ERROR(-20002,'There is an issue with data, one or more columns except primary key have NO meta data defined. Please recheck');
990 WHEN e_no_pk_key THEN
991 RAISE_APPLICATION_ERROR(-20003,'No primary Key column has been provided: A primary key column should not have meta data defined');
992 WHEN OTHERS THEN
993 RAISE_APPLICATION_ERROR(-20006, SQLERRM||v_stmt1);
994
995 END; -- End of UPDATE_TO_COMPOSITE_PRIMARY_KEY;
996
997 ---------
998
999
1000 ----------
1001 PROCEDURE NTB_UPLOAD_COMPOSITETL(P_ENTITY IN VARCHAR2, P_EXTID IN NUMBER, P_IF_ROW_EXISTS IN NUMBER) IS
1002 --initialize variables here
1003 v_pk_column v_mst_pk_key_columns;
1004 v_csv_column v_csv_column_names;
1005 v_entity_code NUMBER;
1006 v_tname_b VARCHAR2(30);
1007 v_tname_tl VARCHAR2(30);
1008 e_tname_not_found EXCEPTION;
1009 v_stmt VARCHAR2(30000);
1010 v_concat_pk_key VARCHAR2(3000);
1011 -- main body
1012 BEGIN
1013 NULL; -- allow compilation
1014 /*
1015 Using the p_entity, get the primary key column name
1016 */
1017 MTH_UDA_PKG.GET_MST_COMPOSITE_PK_NAME(p_entity,v_pk_column, v_entity_code, v_csv_column);
1018 --IF (v_pk_column1 IS NULL OR v_pk_column2 IS NULL OR (v_pk_column1 IS NULL AND v_pk_column2 IS NULL)) THEN
1019 IF (v_pk_column IS NULL OR v_entity_code = -1) THEN
1020 RAISE e_tname_not_found;
1021 END IF;
1022
1023 /*
1024 Now get the EXT_TL and EXT_B names
1025 */
1026 v_tname_b := MTH_UDA_PKG.Get_Ext_Table_Name(p_entity);
1027 IF (v_tname_b = NULL) THEN
1028 RAISE e_tname_not_found;
1029 END IF;
1030
1031 v_tname_tl := MTH_UDA_PKG.Get_Ext_TL_Table_Name(p_entity);
1032 IF (v_tname_tl = NULL) THEN
1033 RAISE e_tname_not_found;
1034 END IF;
1035
1036 /*
1037 Now, check if it was already existing, if not, we will insert a new row,
1038 else call the upload who procedure to update who columns
1039 */
1040
1041 IF (p_if_row_exists = 0) THEN
1042 -- INSERT A NEW ROW
1043 --DBMS_OUTPUT.PUT_LINE('inserting the rows');
1044 FOR ctr IN v_pk_column.FIRST..v_pk_column.LAST
1045 LOOP
1046 v_concat_pk_key := v_concat_pk_key || v_pk_column(ctr) || ', ';
1047
1048 END LOOP;
1049
1050 v_stmt := 'INSERT INTO '||v_tname_tl||'(EXTENSION_ID, ATTR_GROUP_ID, ' || v_concat_pk_key || 'SOURCE_LANG, LANGUAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE) SELECT EXTENSION_ID, ATTR_GROUP_ID, ';
1051 v_stmt := v_stmt ||v_concat_pk_key || ' ''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;
1052 --DBMS_OUTPUT.PUT_LINE(v_stmt);
1053 EXECUTE IMMEDIATE v_stmt;
1054 COMMIT;
1055
1056 ELSE
1057 -- CALL to who upload column
1058 --DBMS_OUTPUT.PUT_LINE('UPDating the who columns');
1059 MTH_UDA_PKG.NTB_Upload_Standard_Who(v_tname_tl,p_extId,p_if_row_exists);
1060 END IF;
1061
1062
1063
1064
1065 EXCEPTION
1066 WHEN e_tname_not_found THEN
1067 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided at ');
1068 WHEN OTHERS THEN
1069 RAISE_APPLICATION_ERROR(-20002, SQLERRM);
1070
1071 END;
1072 -- End of NTB_UPLOADCOMPOSITETL;
1073
1074
1075 /* Support Composite Pk Key */
1076
1077 PROCEDURE NTB_UPLOAD_COMPOSITE_PK(P_TARGET IN VARCHAR2) IS
1078 --initialize variables here
1079 v_stmt_no NUMBER;
1080 v_if_row_exists NUMBER;
1081 v_attr_group NUMBER;
1082 v_extId NUMBER;
1083 v_cnt_rows NUMBER;
1084 v_cnt_existing NUMBER;
1085 v_col_name VARCHAR2(20);
1086 v_col_val VARCHAR2(255);
1087 v_stmt VARCHAR2(20000);
1088 v_stmt_var VARCHAR2(20000);
1089 v_mrc VARCHAR2(3);
1090 v_date_val VARCHAR2(20);
1091 e_tname_not_found EXCEPTION;
1092 v_entity VARCHAR2(200) := p_target;
1093 v_tname VARCHAR2(200);
1094
1095
1096 /*
1097 This cursor is used to loop through all the columns to
1098 be filled in for a row. This helps to insert as well as
1099 update a row in the EXT table.
1100 */
1101 CURSOR c_row_iterator(R_ID NUMBER, ATTR_GRP NUMBER) IS
1102 SELECT STG.ATTR_GROUP_ID,STG.ATTR_NAME, STG.ATTR_VALUE, STG.DB_COL FROM MTH_EXT_ATTR_T_STG STG
1103 WHERE STG.GROUP_ID = R_ID AND STG.ATTR_GROUP_ID = ATTR_GRP;
1104
1105 /*
1106 This cursor is used to find all the attribute groups ids having
1107 the same row ids and process the same.
1108 */
1109 CURSOR c_row_iterator1(GROUP_ID1 NUMBER) IS
1110 SELECT DISTINCT ATTR_GROUP_ID FROM MTH_EXT_ATTR_T_STG WHERE GROUP_ID= GROUP_ID1;
1111
1112 /*
1113 This cursor first gets all the rows for which DB_COL is null.
1114 Essentially, these are going to be the ones which are the primary
1115 keys of the table. This helps to locate a particular row and to decide
1116 whether we update or insert a new row.
1117 */
1121 /*This cursor will get all the NAME VALUE pair for which c_unique_key_flag = 'Y' */
1118 CURSOR c_row_iterator2(GROUP_ID2 NUMBER, AID NUMBER) IS
1119 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;
1120
1122 CURSOR c_unique_key_flag(GROUP_ID3 NUMBER, AID3 NUMBER) IS
1123 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';
1124
1125 -- main body
1126 BEGIN
1127 NULL; -- allow compilation
1128 -- Call the procedure to rename columns to the pkey columns
1129 MTH_UDA_PKG.UPDATE_COMPOSITE_PRIMARY_KEY(v_entity);
1130
1131
1132 -- Code to get find out table name depending on the entity input
1133 v_stmt_no := 5;
1134 v_tname := MTH_UDA_PKG.Get_Ext_Table_Name(v_entity);
1135 IF (v_tname is NULL) THEN
1136 RAISE e_tname_not_found;
1137 END IF;
1138
1139 --DBMS_OUTPUT.PUT_LINE('The target table is '||v_tname);
1140 /*
1141 Select the different row ids present, each different row id refers to the data for a single
1142 row. It is possible to have one to many relationship between row id and attribute group.
1143 */
1144 v_stmt_no:= 10;
1145
1146 /*Changed the following select statement
1147 SELECT COUNT(DISTINCT GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;
1148 due to bug 8349873. Due to the above statement, the logic fails when we have discontinous group ids such as 1,3,5 etc.
1149 Changing the statement to select max group_id allows complete iteration through
1150 discontinous set.
1151 */
1152 SELECT MAX(GROUP_ID) INTO v_cnt_rows FROM MTH_EXT_ATTR_T_STG;
1153
1154 /* Loop through each row of data. A row of data is identified as having the
1155 same row id.
1156 */
1157 --DBMS_OUTPUT.PUT_LINE('Entering logic to process one set of rows with same row id');
1158 FOR VAR IN 1..v_cnt_rows
1159 LOOP
1160 v_stmt_no := 20;
1161
1162 /*
1163 This loop will help to process data for one row id, with provision
1164 to have more than one attribute group id having the same row.
1165 */
1166 FOR A_ID IN c_row_iterator1(VAR)
1167 LOOP
1168 v_attr_group:= A_ID.ATTR_GROUP_ID; --Get the attribute group id in a variable
1169 --DBMS_OUTPUT.PUT_LINE('The attribute group is '||v_attr_group);
1170 --DBMS_OUTPUT.PUT_LINE('Processing Row '||VAR);
1171
1172 /*
1173 This variable helps to prepare statement to get the EXT ID value
1174 for a particular row
1175 */
1176 v_stmt_var := 'SELECT EXTENSION_ID FROM '||v_tname||' WHERE ATTR_GROUP_ID ='||v_attr_group;
1177
1178 /*
1179 This variable helps to prepare statement to see whether the particular row
1180 for which data is being processed is present in the EXT table or not.
1181 */
1182 v_stmt := 'SELECT COUNT(1) FROM '||v_tname||' WHERE ATTR_GROUP_ID ='||v_attr_group;
1183
1184 /*
1185 This statement helps to prepare the above statements correctly
1186 by helping to choose proper WHERE CLAUSES. This is achieved by
1187 using the v_cnt_existing varaiable
1188 */
1189 v_stmt_no := 30;
1190 SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
1191 WHERE DB_COL IS NULL AND
1192 ATTR_GROUP_ID = v_attr_group AND
1193 GROUP_ID = VAR;
1194 --DBMS_OUTPUT.PUT_LINE('No of primary key columns = '||v_cnt_existing);
1195
1196 /*
1197 This cursor first gets all the rows for which DB_COL is null.
1198 Essentially, these are going to be the ones which are the primary
1199 keys of the table. This helps to locate a particular row and to decide
1200 whether we update or insert a new row.
1201 */
1202 --DBMS_OUTPUT.PUT_LINE('Preparing query using pkey columns');
1203 FOR VAR2 IN c_row_iterator2(VAR, v_attr_group)
1204 LOOP
1205 v_col_name := VAR2.ATTR_NAME;
1206 v_col_val := VAR2.ATTR_VALUE;
1207 v_cnt_existing := v_cnt_existing -1;
1208
1209 /*
1210 If the pkey columns happen to be date columns, we need
1211 to add the logic to process the data by converting it
1212 to date. This has been done in the loops which follow
1213 */
1214 v_stmt := v_stmt||' AND TO_CHAR('||v_col_name||') = ''' || v_col_val || '''';
1215 v_stmt_var := v_stmt_var||' AND TO_CHAR('||v_col_name||') = '''||v_col_val || '''';
1216
1217 END LOOP;
1218
1219 --DBMS_OUTPUT.PUT_LINE('The queries for pkeys are as follows:');
1220 --DBMS_OUTPUT.PUT_LINE(v_stmt);
1221 --DBMS_OUTPUT.PUT_LINE(v_stmt_var);
1222
1223 /* Now add where clause to check for unique keys. For a multi row
1224 attribute group, distinction between attribute group id and pkeys would
1225 not suffice. For such a case, we designate few attribute columns as unique.
1226 These attributes allow us to distinguish between multi row data.
1227 Skip this check if the attribute group is not multi row.
1228 */
1229 --DBMS_OUTPUT.PUT_LINE('Now check logic for MULTI ROW');
1230 v_stmt_no := 40;
1231 SELECT MULTI_ROW_CODE INTO v_mrc FROM EGO_ATTR_GROUPS_V
1232 WHERE ATTR_GROUP_ID = v_attr_group;
1233
1234 IF v_mrc = 'Y' THEN
1235 --DBMS_OUTPUT.PUT_LINE('Attribute group is MULTI ROW');
1236 /* LOGIC FOR multi row attribute groups */
1237 SELECT COUNT(1) INTO v_cnt_existing FROM MTH_EXT_ATTR_T_STG
1238 WHERE UNIQUE_KEY_FLAG='Y' AND
1239 ATTR_GROUP_ID = v_attr_group AND
1240 GROUP_ID = VAR;
1241 -- DBMS_OUTPUT.PUT_LINE('No of unique columns = '||v_cnt_existing);
1242
1243 FOR C IN c_unique_key_flag(VAR, v_attr_group)
1244 LOOP
1245 -- DBMS_OUTPUT.PUT_LINE('Adding MULTI ROW column where clause to the earlier query');
1246 v_col_name := C.DB_COL;
1247 v_col_val := C.ATTR_VALUE;
1248 v_cnt_existing := v_cnt_existing-1;
1249 v_stmt_no := 50;
1250
1251 /*
1252 Check for proper where clause. This is done to
1253 get the correct timestamp for date columns.
1254 */
1255 -- DBMS_OUTPUT.PUT_LINE('Statement so far '||STMT);
1256
1257 IF SUBSTR(v_col_name,1,1)= 'D' THEN
1258 -- DBMS_OUTPUT.PUT_LINE('We have a unique date column');
1259 v_date_val := TO_CHAR(TO_DATE(v_col_val,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
1260 v_stmt := v_stmt||' AND '||v_col_name||'='||'TO_DATE('||''''||v_date_val||''''||',''MM/DD/YYYY HH24:MI:SS'')';
1261 v_stmt_var := v_stmt_var||' AND '||v_col_name||'='||'TO_DATE('||''''||v_date_val||''''||',''MM/DD/YYYY HH24:MI:SS'')';
1262 ELSE
1263 v_stmt := v_stmt||' AND '||v_col_name||'='||''''||v_col_val||'''';
1264 v_stmt_var := v_stmt_var||' AND '||v_col_name||'='||''''||v_col_val||'''';
1265 END IF;
1266
1267 END LOOP;
1268 ELSE
1269 null;
1270 --DBMS_OUTPUT.PUT_LINE('The A Group is single row');
1271 END IF;
1272
1273 --DBMS_OUTPUT.PUT_LINE('The updated statements after unique key check are ');
1274 --DBMS_OUTPUT.PUT_LINE(v_stmt);
1275 -- DBMS_OUTPUT.PUT_LINE(v_stmt_var);
1276
1277 v_stmt_no := 60;
1278 /*
1279 Get the count of the row in the variable v_if_row_exists
1280 If the count is 0, it means the row with these values
1281 of pkeys are not present. So proceed with inserting a
1282 new surrogate key value
1283 */
1284 EXECUTE IMMEDIATE v_stmt INTO v_if_row_exists ;
1285
1286 IF v_if_row_exists = 0 THEN
1287 --DBMS_OUTPUT.PUT_LINE('This row is not present in the EXT table');
1288 --DBMS_OUTPUT.PUT_LINE('INSERT THE NEW ROW');
1289
1290
1291 v_stmt_no := 70;
1292 v_stmt := 'SELECT EGO_EXTFWK_S.NEXTVAL FROM DUAL';
1293 EXECUTE IMMEDIATE v_stmt INTO v_extId;
1294
1295 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||''''||' )';
1296 --DBMS_OUTPUT.PUT_LINE('The new EXT ID is'||v_extId);
1297 --DBMS_OUTPUT.PUT_LINE('The new EXT ID is'||v_stmt);
1298 v_stmt_no := 80;
1299 EXECUTE IMMEDIATE v_stmt USING v_extId, v_attr_group ;
1300 --COMMIT;
1301
1302 ELSE
1303 -- DBMS_OUTPUT.PUT_LINE('This data is already present in the EXT table');
1304 -- DBMS_OUTPUT.PUT_LINE('UPDATE THE DATA');
1305 v_stmt_no := 90;
1306 EXECUTE IMMEDIATE v_stmt_var INTO v_extId;
1307 --DBMS_OUTPUT.PUT_LINE('The EXT ID for this data is '||v_extId);
1308 END IF;
1309
1310 /*
1311 Iterate over all the name value pair for the row id
1312 to insert/update in the EXT Table
1313 */
1314 --DBMS_OUTPUT.PUT_LINE('Iterate over all the columns to insert/update');
1315 FOR EXT_VAL IN c_row_iterator(VAR,v_attr_group)
1316 LOOP
1317 /*
1318 Check whether this is a pkey value,
1319 in such cases, DB_COL will be null
1320 */
1321 IF EXT_VAL.DB_COL IS NULL THEN
1322 v_col_name := EXT_VAL.ATTR_NAME;
1323 ELSE
1324 v_col_name := EXT_VAL.DB_COL;
1325 END IF;
1326
1327 --DBMS_OUTPUT.PUT_LINE('The column name to be updated/inserted '||v_col_name);
1328
1329 /*
1330 Check for a date column to use the appropiate TO_DATE FUNCTION
1331 */
1332 IF SUBSTR(v_col_name,1,1) = 'D' THEN
1333 -- DBMS_OUTPUT.PUT_LINE('A DATE COLUMN');
1334
1335 -- DBMS_OUTPUT.PUT_LINE('The TIMESTAMP is ');
1336 -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(TO_DATE(EXT_VAL.ATTR_VALUE,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS'));
1337 v_date_val := TO_CHAR(TO_DATE(EXT_VAL.ATTR_VALUE,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS');
1338 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;
1339 ELSE
1340 v_col_val := EXT_VAL.ATTR_VALUE;
1341 --DBMS_OUTPUT.PUT_LINE('The data is '||v_col_val);
1342 v_stmt := 'UPDATE '||v_tname||' SET '||v_col_name||' = '||''''||v_col_val||''''||' WHERE EXTENSION_ID = '||v_extId;
1343 END IF;
1344
1345 --DBMS_OUTPUT.PUT_LINE('The statement to be executed is '||v_stmt);
1346
1347 v_stmt_no := 100;
1348 EXECUTE IMMEDIATE v_stmt;
1349
1350 END LOOP; -- Completing insertion or updating a single row
1351
1352 --Commit after all the attributes for one group id is
1353 --updated
1354 COMMIT;
1355 /*
1356 call procedure to update standard who columns
1357 */
1358 --DBMS_OUTPUT.PUT_LINE('calling who procedure');
1359 MTH_UDA_PKG.NTB_Upload_Standard_Who(v_tname,v_extId, v_if_row_exists);
1360
1361 /*
1362 Call the procedure to update TL Table
1363 */
1364 MTH_UDA_PKG.NTB_Upload_COMPOSITETL(v_entity,v_extId,v_if_row_exists);
1365
1366 END LOOP;
1367 END LOOP;
1368
1369 EXCEPTION
1370 WHEN NO_DATA_FOUND THEN
1371 RAISE_APPLICATION_ERROR(-20002,'No data found at line number '||v_stmt_no);
1372
1373 WHEN e_tname_not_found THEN
1374 RAISE_APPLICATION_ERROR(-20001,'Incorrect Entity provided at '||v_stmt_no);
1375
1376 WHEN OTHERS THEN
1377 RAISE_APPLICATION_ERROR(-20003,SQLERRM||' at '||v_stmt_no);
1378 ROLLBACK;
1379
1380 END; --End NTB_UPLOAD_COMPOSITE_PK
1381
1382 END MTH_UDA_PKG;
[Home] [Help]