[Home] [Help]
PACKAGE BODY: APPS.DDR_ETL_UTIL_PKG
Source
1 PACKAGE BODY ddr_etl_util_pkg AS
2 /* $Header: ddruetlb.pls 120.10.12020000.4 2012/08/11 05:15:35 gglover ship $ */
3
4 g_delimeter_char VARCHAR2(1) := ' '; /* Tab as delimeter */
5 -- g_delimeter_char VARCHAR2(1) := '~';
6 -- g_delimeter_char VARCHAR2(1) := CHR(9); /* Tab as delimeter */
7 g_owner_name VARCHAR2(30) := 'DDR';
8 g_max_col_value_size INTEGER := 1000;
9 g_max_linesize INTEGER := 32767;
10 g_owner_name_real VARCHAR2(30);
11
12 PROCEDURE set_owner
13 IS
14 v_owner_sql VARCHAR2(1000);
15 v_user VARCHAR2(100);
16 BEGIN
17 v_owner_sql := 'select user from dual';
18 EXECUTE IMMEDIATE v_owner_sql
19 INTO v_user;
20
21 g_owner_name_real := v_user;
22
23 IF v_user = 'APPS' THEN
24 g_owner_name := 'DDR';
25 ELSE
26 g_owner_name := v_user;
27 END IF;
28
29 END set_owner;
30
31
32 PROCEDURE Raise_Error (p_error_text IN VARCHAR2)
33 IS
34 l_error_text VARCHAR2(240);
35 BEGIN
36 l_error_text := p_error_text;
37 Raise_Application_Error(-20001,l_error_text);
38 END Raise_Error;
39
40 PROCEDURE Validate_Parameters (p_table_name IN VARCHAR2,p_file_name IN VARCHAR2)
41 IS
42 CURSOR cur_tab (p_table_name IN VARCHAR2) IS
43 SELECT 1
44 FROM ALL_TABLES
45 WHERE table_name = p_table_name
46 AND owner = g_owner_name;
47
48 l_dummy INTEGER;
49 BEGIN
50 set_owner;
51
52 IF p_table_name IS NULL
53 THEN
54 Raise_Error('Table Name must be specified');
55 END IF;
56
57 IF p_file_name IS NULL
58 THEN
59 Raise_Error('File Name must be specified');
60 END IF;
61
62 /* Validate Table Name */
63 OPEN cur_tab (UPPER(p_table_name));
64 FETCH cur_tab INTO l_dummy;
65 IF cur_tab%NOTFOUND
66 THEN
67 Raise_Error('Invalid table name: ' || p_table_name);
68 END IF;
69 CLOSE cur_tab;
70 END Validate_Parameters;
71
72 FUNCTION is_string (p_column_type IN VARCHAR2)
73 RETURN BOOLEAN
74 IS
75 BEGIN
76 RETURN (p_column_type IN ('CHAR', 'VARCHAR2'));
77 END;
78
79 FUNCTION is_string (p_column_type IN string_tab,p_row_idx IN INTEGER)
80 RETURN BOOLEAN
81 IS
82 BEGIN
83 RETURN (p_column_type(p_row_idx) IN ('CHAR', 'VARCHAR2'));
84 END;
85
86 FUNCTION is_number (p_column_type IN VARCHAR2)
87 RETURN BOOLEAN
88 IS
89 BEGIN
90 RETURN (p_column_type IN ('FLOAT', 'INTEGER', 'NUMBER'));
91 END;
92
93 FUNCTION is_number (p_column_type IN string_tab,p_row_idx IN INTEGER)
94 RETURN BOOLEAN
95 IS
96 BEGIN
97 RETURN (p_column_type(p_row_idx) IN ('FLOAT', 'INTEGER', 'NUMBER'));
98 END;
99
100 FUNCTION is_date (p_column_type IN VARCHAR2)
101 RETURN BOOLEAN
102 IS
103 BEGIN
104 RETURN (p_column_type = 'DATE');
105 END;
106
107 FUNCTION is_date (p_column_type IN string_tab,p_row_idx IN INTEGER)
108 RETURN BOOLEAN
109 IS
110 BEGIN
111 RETURN (p_column_type(p_row_idx) = 'DATE');
112 END;
113
114 FUNCTION Get_Directory_Name RETURN VARCHAR2
115 IS
116 l_dirname VARCHAR2(100);
117 BEGIN
118 -- l_dirname := 'E:\Biswajit\BI-Projects\DSM\Praxis-Work\ETL\Scripts\Error\';
119 l_dirname := 'DDR_ERROR_DIR';
120 RETURN l_dirname;
121 END Get_Directory_Name;
122
123 PROCEDURE Get_Column_Details (
124 p_table_name IN VARCHAR2,
125 p_column_name IN OUT NOCOPY string_tab,
126 p_column_type IN OUT NOCOPY string_tab,
127 p_column_count IN OUT NOCOPY NUMBER
128 )
129 IS
130 CURSOR cur_col(c_table_name IN VARCHAR2) IS
131 SELECT column_name, data_type, data_length, data_precision, data_scale
132 FROM ALL_TAB_COLUMNS
133 WHERE table_name = c_table_name
134 AND owner = g_owner_name
135 ORDER BY column_id;
136 BEGIN
137 set_owner;
138
139 p_column_count := 0;
140 FOR rec_col IN cur_col (p_table_name)
141 LOOP
142 p_column_count := p_column_count + 1;
143 p_column_name(p_column_count) := rec_col.column_name;
144 p_column_type(p_column_count) := rec_col.data_type;
145 END LOOP;
146 END Get_Column_Details;
147
148 PROCEDURE Get_File_Column_Details (
149 p_line IN VARCHAR2,
150 p_file_column_name IN OUT NOCOPY string_tab,
151 p_file_column_count IN OUT NOCOPY NUMBER
152 )
153 IS
154 l_token VARCHAR2(4000);
155 BEGIN
156 p_file_column_count := LENGTH(p_line) - LENGTH(REPLACE(p_line,g_delimeter_char,'')) + 1;
157
158 FOR idx IN 0 .. (p_file_column_count-1)
159 LOOP
160 IF (idx=0)
161 THEN
162 IF INSTR(p_line,g_delimeter_char,1,1) <> 0
163 THEN
164 l_token := SUBSTR(p_line,1,INSTR(p_line,g_delimeter_char,1,1)-1);
165 ELSE
166 l_token := p_line;
167 END IF;
168 ELSE
169 IF INSTR(p_line,g_delimeter_char,1,idx+1) <> 0
170 THEN
171 l_token := SUBSTR(p_line,INSTR(p_line,g_delimeter_char,1,idx)+1,
172 INSTR(p_line,g_delimeter_char,1,idx+1)-INSTR(p_line,g_delimeter_char,1,idx)-1);
173 ELSE
174 l_token := SUBSTR(p_line,INSTR(p_line,g_delimeter_char,1,idx)+1);
175 END IF;
176 END IF;
177
178 p_file_column_name(idx+1) := l_token;
179 END LOOP;
180
181 END Get_File_Column_Details;
182
183 FUNCTION Column_Exists (p_column_name_array IN string_tab,p_column_name IN VARCHAR2)
184 RETURN BOOLEAN
185 IS
186 l_return_value BOOLEAN := FALSE;
187 BEGIN
188 FOR indx IN 1 .. p_column_name_array.COUNT
189 LOOP
190 IF p_column_name_array(indx) = p_column_name
191 THEN
192 l_return_value := TRUE;
193 EXIT;
194 END IF;
195 END LOOP;
196 RETURN l_return_value;
197 END Column_Exists;
198
199 PROCEDURE Export_Error (
200 p_table_name IN VARCHAR2,
201 p_load_id IN NUMBER DEFAULT NULL,
202 p_file_name IN VARCHAR2 DEFAULT NULL
203 )
204 AS
205 l_where_clause VARCHAR2(500);
206 l_file_name VARCHAR2(100);
207 BEGIN
208 l_where_clause := 'WHERE ACTION_FLAG = ''N'' ';
209 IF p_load_id IS NOT NULL
210 THEN
211 l_where_clause := l_where_clause || ' AND LOAD_ID = ' || p_load_id;
212 END IF;
213
214 l_file_name := p_file_name;
215 IF l_file_name IS NULL
216 THEN
217 IF p_load_id IS NOT NULL
218 THEN
219 l_file_name := p_table_name || '_' || TO_CHAR(p_load_id) || '.err';
220 ELSE
221 l_file_name := p_table_name || '.err';
222 END IF;
223 END IF;
224
225 Export_Data(p_table_name,l_where_clause,NVL(p_file_name,l_file_name));
226 END Export_Error;
227
228 PROCEDURE Export_Data (
229 p_table_name IN VARCHAR2,
230 p_where_clause IN VARCHAR2 DEFAULT NULL,
231 p_file_name IN VARCHAR2 DEFAULT NULL
232 )
233 AS
234 l_dir_name VARCHAR2(100);
235 l_table_name VARCHAR2(30);
236 l_where_clause VARCHAR2(500);
237 l_file UTL_FILE.FILE_TYPE;
238 l_column_name string_tab;
239 l_column_type string_tab;
240 l_column_count INTEGER;
241 l_column_list VARCHAR2(10000);
242 l_SQL_stmt VARCHAR2(10000);
243 cur_err INTEGER;
244 l_string_value VARCHAR2(1000);
245 l_number_value NUMBER;
246 l_date_value DATE;
247 l_return_value INTEGER;
248 l_line VARCHAR2(10000);
249 l_hdr_line VARCHAR2(10000);
250 BEGIN
251 l_table_name := UPPER(p_table_name);
252 l_where_clause := LTRIM(UPPER(p_where_clause));
253
254 Validate_Parameters(l_table_name,p_file_name);
255 Get_Column_Details(l_table_name,l_column_name,l_column_type,l_column_count);
256
257 /* Build the SELECT statement to be executed */
258 FOR indx IN 1 .. l_column_count
259 LOOP
260 IF indx = 1
261 THEN
262 l_column_list := l_column_name(indx);
263 l_hdr_line := l_column_name(indx);
264 ELSE
265 l_column_list := l_column_list || ', ' || l_column_name(indx);
266 l_hdr_line := l_hdr_line || g_delimeter_char || l_column_name(indx);
267 END IF;
268 END LOOP;
269
270 l_SQL_stmt := 'SELECT ' || l_column_list;
271 l_SQL_stmt := l_SQL_stmt || ' FROM ' || l_table_name;
272 IF l_where_clause IS NOT NULL
273 THEN
274 IF (l_where_clause NOT LIKE 'GROUP BY%' AND l_where_clause NOT LIKE 'ORDER BY%')
275 THEN
276 l_where_clause := ' WHERE ' || LTRIM (l_where_clause, 'WHERE');
277 END IF;
278 END IF;
279 l_SQL_stmt := l_SQL_stmt || l_where_clause;
280
281 l_dir_name := Get_Directory_Name;
282 l_file := UTL_FILE.FOPEN(l_dir_name,p_file_name,'W',g_max_linesize);
283
284 /* Retrieve the records */
285 cur_err := DBMS_SQL.OPEN_CURSOR;
286 DBMS_SQL.PARSE(cur_err,l_SQL_stmt,DBMS_SQL.NATIVE);
287 FOR col_indx IN 1 .. l_column_count
288 LOOP
289 IF is_string(l_column_type,col_indx)
290 THEN
291 DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_string_value,g_max_col_value_size);
292 ELSIF is_number(l_column_type,col_indx)
293 THEN
294 DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_number_value);
295 ELSIF is_date (l_column_type,col_indx)
296 THEN
297 DBMS_SQL.DEFINE_COLUMN (cur_err,col_indx,l_date_value);
298 END IF;
299 END LOOP;
300 l_return_value := DBMS_SQL.EXECUTE(cur_err);
301
302 LOOP
303 l_return_value := DBMS_SQL.FETCH_ROWS(cur_err);
304 EXIT WHEN l_return_value = 0;
305
306 IF DBMS_SQL.last_row_count = 1
307 THEN
308 /* Write the column header line in file */
309 UTL_FILE.PUT_LINE(l_file,l_hdr_line);
310 END IF;
311
312 l_line := NULL;
313 FOR col_indx IN 1 .. l_column_count
314 LOOP
315 IF is_string(l_column_type,col_indx)
316 THEN
317 DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_string_value);
318 ELSIF is_number(l_column_type,col_indx)
319 THEN
320 DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_number_value);
321 l_string_value := TO_CHAR (l_number_value);
322 ELSIF is_date(l_column_type,col_indx)
323 THEN
324 DBMS_SQL.COLUMN_VALUE(cur_err,col_indx,l_date_value);
325 l_string_value := TO_CHAR(l_date_value,'YYYY/MM/DD');
326 END IF;
327
328 l_line := l_line || g_delimeter_char || l_string_value;
329 END LOOP;
330 l_line := SUBSTR(l_line,2);
331
332 /* Write the line to the file */
333 UTL_FILE.PUT_LINE(l_file,l_line);
334 END LOOP;
335 DBMS_SQL.CLOSE_CURSOR(cur_err);
336
337 UTL_FILE.FCLOSE(l_file);
338 EXCEPTION
339 WHEN OTHERS
340 THEN
341 IF UTL_FILE.IS_OPEN(l_file)
342 THEN
343 UTL_FILE.FCLOSE(l_file);
344 END IF;
345 IF DBMS_SQL.IS_OPEN(cur_err)
346 THEN
347 DBMS_SQL.CLOSE_CURSOR(cur_err);
348 END IF;
349 Raise_Error(SQLERRM);
350 END Export_Data;
351
352 PROCEDURE Import_Error (
353 p_table_name IN VARCHAR2,
354 p_file_name IN VARCHAR2 DEFAULT NULL,
355 p_tgt_table_type IN VARCHAR2
356 )
357 AS
358 l_dir_name VARCHAR2(100);
359 l_table_name VARCHAR2(30);
360 l_file_name VARCHAR2(100);
364 l_column_count INTEGER;
361 l_file UTL_FILE.FILE_TYPE;
362 l_column_name string_tab;
363 l_column_type string_tab;
365 l_update_stmt VARCHAR2(10000);
366 l_insert_stmt VARCHAR2(10000);
367 l_update_clause VARCHAR2(10000);
368 l_insert_clause VARCHAR2(10000);
369 l_values_clause VARCHAR2(10000);
370 cur_err_upd INTEGER;
371 cur_err_ins INTEGER;
372 l_string_value VARCHAR2(4000);
373 l_number_value NUMBER;
374 l_date_value DATE;
375 l_return_value INTEGER;
376 l_line VARCHAR2(10000);
377 l_col_type_by_name string_index_by_char_tab;
378 l_file_column_name string_tab;
379 l_file_column_count INTEGER;
380 l_rec_count INTEGER;
381 l_count INTEGER;
382 l_file_column_value string_tab;
383 l_update_count INTEGER;
384 l_action_column_indx INTEGER;
385 l_src_idnt_column_indx INTEGER;
386 l_src_idnt_value VARCHAR2(1);
387 l_tgt_table_type VARCHAR2(1);
388 BEGIN
389 l_table_name := UPPER(p_table_name);
390 l_file_name := NVL(p_file_name,l_table_name || '.err');
391 l_tgt_table_type := p_tgt_table_type;
392
393 Validate_Parameters(l_table_name,l_file_name);
394 Get_Column_Details(l_table_name,l_column_name,l_column_type,l_column_count);
395
396 FOR indx IN 1 .. l_column_count
397 LOOP
398 l_col_type_by_name(l_column_name(indx)) := l_column_type(indx);
399 END LOOP;
400
401 l_dir_name := Get_Directory_Name;
402 l_file := UTL_FILE.FOPEN(l_dir_name,l_file_name,'R',g_max_linesize);
403
404 /* Read and Process all records from the file */
405 cur_err_upd := DBMS_SQL.OPEN_CURSOR;
406 cur_err_ins := DBMS_SQL.OPEN_CURSOR;
407 l_rec_count := 0;
408 LOOP
409 BEGIN
410 UTL_FILE.GET_LINE(l_file,l_line,g_max_linesize);
411 l_rec_count := l_rec_count + 1;
412
413 IF l_rec_count = 1 /* Assuming that the first line is the File Column Header Line */
414 THEN
415 /* Build l_file_column_name array based on the read line */
416 Get_File_Column_Details(l_line,l_file_column_name,l_file_column_count);
417
418 /* Build the Update and Insert Statements and Parse them */
419 l_update_clause := 'UPDATE ' || l_table_name || ' SET ';
420 l_insert_clause := 'INSERT INTO ' || l_table_name ||'(';
421 l_values_clause := 'VALUES (';
422 l_action_column_indx := 0;
423 FOR indx IN 1 .. l_file_column_count
424 LOOP
425 IF l_file_column_name(indx) <> 'REC_ID'
426 THEN
427 l_update_clause := l_update_clause || ' ' || l_file_column_name(indx)
428 || '=:' || l_file_column_name(indx) || ',';
429 END IF;
430 l_insert_clause := l_insert_clause || l_file_column_name(indx) || ',';
431 l_values_clause := l_values_clause || ':' || l_file_column_name(indx) || ',';
432
433 IF l_file_column_name(indx) = 'ACTION_FLAG'
434 THEN
435 l_action_column_indx := indx;
436 /* BUG# 13809784 - Import based on target table type -Start*/
437 ELSIF l_file_column_name(indx) = 'SRC_IDNT_FLAG' THEN
438 l_src_idnt_column_indx := indx;
439 /* BUG# 13809784 -End */
440 END IF;
441 END LOOP;
442 l_update_clause := SUBSTR(l_update_clause,1,LENGTH(l_update_clause)-1);
443 l_insert_clause := SUBSTR(l_insert_clause,1,LENGTH(l_insert_clause)-1);
444 l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
445 l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
446 l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
447
448 DBMS_SQL.PARSE(cur_err_upd,l_update_stmt,DBMS_SQL.NATIVE);
449 DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
450 ELSE
451 Get_File_Column_Details(l_line,l_file_column_value,l_count);
452
453 /* BUG# 13809784 - Import based on target table type -Start*/
454 IF l_src_idnt_column_indx IS NULL THEN
455 l_src_idnt_value := 'R';
456 ELSIF l_file_column_value(l_src_idnt_column_indx) = l_tgt_table_type THEN
457 l_src_idnt_value := 'Y';
458 ELSE
459 l_src_idnt_value := 'N'; --not matching target table type
460 END IF;
461 /* BUG# 13809784 -End */
462
463 IF l_file_column_value(l_action_column_indx) IN ('Y','D') /* i.e. ACTION_FLAG IN ('Y','D') */
464 THEN
465 /* BUG# 13809784 - Import based on target table type * -Start*/
466 IF l_src_idnt_value IN ('R','Y') THEN
467
468 /* Update the record into table */
469 FOR indx IN 1 .. l_count
470 LOOP
471 IF is_string(l_col_type_by_name(l_file_column_name(indx)))
472 THEN
473 l_string_value := l_file_column_value(indx);
474 DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_string_value);
475 ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
476 THEN
477 l_number_value := TO_NUMBER(l_file_column_value(indx));
478 DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_number_value);
479 ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
480 THEN
481 l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
482 DBMS_SQL.BIND_VARIABLE(cur_err_upd,l_file_column_name(indx),l_date_value);
483 END IF;
484 END LOOP;
485 l_update_count := DBMS_SQL.EXECUTE(cur_err_upd);
486
487 /* Insert the record into table if Update fails */
488 IF l_update_count = 0
489 THEN
490 FOR indx IN 1 .. l_count
491 LOOP
492 IF is_string(l_col_type_by_name(l_file_column_name(indx)))
493 THEN
494 l_string_value := l_file_column_value(indx);
495 DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
496 ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
497 THEN
498 l_number_value := TO_NUMBER(l_file_column_value(indx));
499 DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
500 ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
501 THEN
502 l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
503 DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
504 END IF;
505 END LOOP;
506 l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
507 END IF;
508 END IF;
509 END IF;
510
511 END IF;
512
513 EXCEPTION
514 WHEN NO_DATA_FOUND
515 THEN EXIT;
516 END;
517 END LOOP;
518
519 DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
520 DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
521 UTL_FILE.FCLOSE(l_file);
522
523 COMMIT;
524 EXCEPTION
525 WHEN OTHERS
526 THEN
527 IF UTL_FILE.IS_OPEN(l_file)
528 THEN
529 UTL_FILE.FCLOSE(l_file);
530 END IF;
531 IF DBMS_SQL.IS_OPEN(cur_err_upd)
532 THEN
533 DBMS_SQL.CLOSE_CURSOR(cur_err_upd);
534 END IF;
535 IF DBMS_SQL.IS_OPEN(cur_err_ins)
536 THEN
537 DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
538 END IF;
539 Raise_Error(SQLERRM);
540 END Import_Error;
541
542 PROCEDURE Import_Error (
543 p_table_name IN VARCHAR2,
544 p_file_name IN VARCHAR2 DEFAULT NULL,
545 p_err_table_name IN VARCHAR2,
546 p_load_id IN NUMBER DEFAULT NULL,
547 p_tgt_table_type IN VARCHAR2 DEFAULT 'I'
548 )
549 AS
550 l_err_table_name VARCHAR2(30);
551 BEGIN
552 IF p_err_table_name IS NULL
553 THEN
554 l_err_table_name := 'DDR_E_' || SUBSTR(p_table_name,7);
555 ELSE
556 l_err_table_name := p_err_table_name;
557 END IF;
558 Import_Error(p_table_name=>l_err_table_name
559 ,p_file_name=>p_file_name
560 ,p_tgt_table_type=>p_tgt_table_type);
561 Transfer_Data(l_err_table_name,p_table_name,p_load_id,p_tgt_table_type);
565 p_table_name IN VARCHAR2,
562 END Import_Error;
563
564 PROCEDURE Import_Data (
566 p_file_name IN VARCHAR2 DEFAULT NULL
567 )
568 AS
569 l_dir_name VARCHAR2(100);
570 l_table_name VARCHAR2(30);
571 l_file_name VARCHAR2(100);
572 l_file UTL_FILE.FILE_TYPE;
573 l_column_name string_tab;
574 l_column_type string_tab;
575 l_column_count INTEGER;
576 l_insert_stmt VARCHAR2(10000);
577 l_insert_clause VARCHAR2(10000);
578 l_values_clause VARCHAR2(10000);
579 cur_err_ins INTEGER;
580 l_string_value VARCHAR2(4000);
581 l_number_value NUMBER;
582 l_date_value DATE;
583 l_return_value INTEGER;
584 l_line VARCHAR2(10000);
585 l_col_type_by_name string_index_by_char_tab;
586 l_file_column_name string_tab;
587 l_file_column_count INTEGER;
588 l_rec_count INTEGER;
589 l_count INTEGER;
590 l_file_column_value string_tab;
591 l_update_count INTEGER;
592 BEGIN
593 l_table_name := UPPER(p_table_name);
594 l_file_name := NVL(p_file_name,l_table_name || '.txt');
595
596 Validate_Parameters(l_table_name,l_file_name);
597 Get_Column_Details(l_table_name,l_column_name,l_column_type,l_column_count);
598
599 FOR indx IN 1 .. l_column_count
600 LOOP
601 l_col_type_by_name(l_column_name(indx)) := l_column_type(indx);
602 END LOOP;
603
604 l_dir_name := Get_Directory_Name;
605 l_file := UTL_FILE.FOPEN(l_dir_name,l_file_name,'R',g_max_linesize);
606
607 /* Read and Process all records from the file */
608 cur_err_ins := DBMS_SQL.OPEN_CURSOR;
609 l_rec_count := 0;
610 LOOP
611 BEGIN
612 UTL_FILE.GET_LINE(l_file,l_line,g_max_linesize);
613 l_rec_count := l_rec_count + 1;
614
615 IF l_rec_count = 1 /* Assuming that the first line is the File Column Header Line */
616 THEN
617 /* Build l_file_column_name array based on the read line */
618 Get_File_Column_Details(l_line,l_file_column_name,l_file_column_count);
619
620 /* Build the Insert Statement and Parse them */
621 l_insert_clause := 'INSERT INTO ' || l_table_name ||'(';
622 l_values_clause := 'VALUES (';
623 FOR indx IN 1 .. l_file_column_count
624 LOOP
625 l_insert_clause := l_insert_clause || l_file_column_name(indx) || ',';
626 l_values_clause := l_values_clause || ':' || l_file_column_name(indx) || ',';
627 END LOOP;
628 l_insert_clause := SUBSTR(l_insert_clause,1,LENGTH(l_insert_clause)-1);
629 l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
630 l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
631
632 DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
633 ELSE
634 Get_File_Column_Details(l_line,l_file_column_value,l_count);
635
636 /* Insert the record into table */
637 FOR indx IN 1 .. l_count
638 LOOP
639 IF is_string(l_col_type_by_name(l_file_column_name(indx)))
640 THEN
641 l_string_value := l_file_column_value(indx);
642 DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_string_value);
643 ELSIF is_number(l_col_type_by_name(l_file_column_name(indx)))
644 THEN
645 l_number_value := TO_NUMBER(l_file_column_value(indx));
646 DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_number_value);
647 ELSIF is_date(l_col_type_by_name(l_file_column_name(indx)))
648 THEN
649 l_date_value := TO_DATE(l_file_column_value(indx),'YYYY/MM/DD');
650 DBMS_SQL.BIND_VARIABLE(cur_err_ins,l_file_column_name(indx),l_date_value);
651 END IF;
652 END LOOP;
653 l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
654
655 END IF;
656
657 EXCEPTION
658 WHEN NO_DATA_FOUND
659 THEN EXIT;
660 END;
661 END LOOP;
662
663 DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
664 UTL_FILE.FCLOSE(l_file);
665
666 COMMIT;
667 EXCEPTION
668 WHEN OTHERS
669 THEN
670 IF UTL_FILE.IS_OPEN(l_file)
671 THEN
672 UTL_FILE.FCLOSE(l_file);
673 END IF;
674 IF DBMS_SQL.IS_OPEN(cur_err_ins)
675 THEN
676 DBMS_SQL.CLOSE_CURSOR(cur_err_ins);
677 END IF;
678 Raise_Error(SQLERRM);
679 END Import_Data;
680
681 PROCEDURE Transfer_Data (
682 p_src_table_name IN VARCHAR2,
683 p_tgt_table_name IN VARCHAR2,
684 p_load_id IN NUMBER DEFAULT NULL,
685 p_tgt_table_type IN VARCHAR2 DEFAULT 'I'
686 )
687 AS
688 l_src_table_name VARCHAR2(30);
689 l_tgt_table_name VARCHAR2(30);
690 l_tgt_table_type VARCHAR2(30);
691 l_src_column_name string_tab;
692 l_src_column_type string_tab;
693 l_src_column_count INTEGER;
694 l_src_column_list VARCHAR2(10000);
695 l_select_stmt VARCHAR2(10000);
696 l_where_clause VARCHAR2(500);
697 l_tgt_column_name string_tab;
698 l_tgt_column_type string_tab;
699 l_tgt_column_count INTEGER;
700 l_tgt_col_type_by_name string_index_by_char_tab;
701 l_update_stmt VARCHAR2(10000);
702 l_insert_stmt VARCHAR2(10000);
703 l_update_clause VARCHAR2(10000);
704 l_insert_clause VARCHAR2(10000);
705 l_values_clause VARCHAR2(10000);
706 l_delete_stmt VARCHAR2(1000);
707 l_rest_where_clause VARCHAR2(500);
708 cur_src INTEGER;
709 cur_tgt_upd INTEGER;
710 cur_tgt_ins INTEGER;
711 l_string_value VARCHAR2(1000);
712 l_number_value NUMBER;
713 l_date_value DATE;
714 l_return_value INTEGER;
715 l_update_count INTEGER;
716 BEGIN
717 l_src_table_name := UPPER(p_src_table_name);
718 l_tgt_table_name := UPPER(p_tgt_table_name);
719 l_tgt_table_type := NVL(p_tgt_table_type,'I');
720
721 Validate_Parameters(l_src_table_name,'DUMMY');
722 Validate_Parameters(l_tgt_table_name,'DUMMY');
723 IF p_tgt_table_type NOT IN ('I','S')
724 THEN
725 Raise_Error('Valid values for Target Table Type is ''I'' and ''S'' only');
726 END IF;
727
728 /* Get Target Table Column details */
729 Get_Column_Details(l_tgt_table_name,l_tgt_column_name,l_tgt_column_type,l_tgt_column_count);
730 FOR indx IN 1 .. l_tgt_column_count
731 LOOP
732 l_tgt_col_type_by_name(l_tgt_column_name(indx)) := l_tgt_column_type(indx);
733 END LOOP;
734
735 /* Get Source Table Column details */
736 Get_Column_Details(l_src_table_name,l_src_column_name,l_src_column_type,l_src_column_count);
737
738 /* Build the SELECT statement to be executed */
739 FOR indx IN 1 .. l_src_column_count
740 LOOP
741 IF indx = 1
742 THEN
743 l_src_column_list := l_src_column_name(indx);
744 ELSE
745 l_src_column_list := l_src_column_list || ', ' || l_src_column_name(indx);
746 END IF;
747 END LOOP;
748
749 l_select_stmt := 'SELECT ' || l_src_column_list;
750 l_select_stmt := l_select_stmt || ' FROM ' || l_src_table_name;
751 l_where_clause := ' WHERE ACTION_FLAG = ''Y'' ';
752 l_rest_where_clause := null;
753 IF p_load_id IS NOT NULL
754 THEN
755 l_where_clause := l_where_clause || ' AND LOAD_ID = ' || p_load_id;
756 l_rest_where_clause := l_rest_where_clause || ' AND LOAD_ID = ' || p_load_id;
757 END IF;
758 IF Column_Exists(l_src_column_name,'SRC_IDNT_FLAG')
759 THEN
760 l_where_clause := l_where_clause || ' AND SRC_IDNT_FLAG = ''' || l_tgt_table_type || '''';
761 l_rest_where_clause := l_rest_where_clause || ' AND SRC_IDNT_FLAG = ''' || l_tgt_table_type || '''';
762 END IF;
763 l_select_stmt := l_select_stmt || l_where_clause;
764
765 cur_tgt_upd := DBMS_SQL.OPEN_CURSOR;
766 cur_tgt_ins := DBMS_SQL.OPEN_CURSOR;
767
768 /* Build the Update and Insert Statements and Parse them */
769 l_update_clause := 'UPDATE ' || l_tgt_table_name || ' SET ';
770 l_insert_clause := 'INSERT INTO ' || l_tgt_table_name ||'(';
771 l_values_clause := 'VALUES (';
772 FOR indx IN 1 .. l_tgt_column_count
773 LOOP
774 /* Here check for the corresponding column existance in source table */
775 IF Column_Exists(l_src_column_name,l_tgt_column_name(indx))
776 THEN
777 IF l_tgt_column_name(indx) <> 'REC_ID'
778 THEN
779 l_update_clause := l_update_clause || ' ' || l_tgt_column_name(indx)
780 || '=:' || l_tgt_column_name(indx) || ',';
781 END IF;
782 l_insert_clause := l_insert_clause || l_tgt_column_name(indx) || ',';
783 l_values_clause := l_values_clause || ':' || l_tgt_column_name(indx) || ',';
784 END IF;
785 END LOOP;
786 l_update_clause := SUBSTR(l_update_clause,1,LENGTH(l_update_clause)-1);
787 l_insert_clause := SUBSTR(l_insert_clause,1,LENGTH(l_insert_clause)-1);
788 l_values_clause := SUBSTR(l_values_clause,1,LENGTH(l_values_clause)-1);
789 l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
790 l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
791
792 DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);
793 DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
794
795 /* Retrieve the records from Source table */
796 cur_src := DBMS_SQL.OPEN_CURSOR;
797 DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);
798 FOR col_indx IN 1 .. l_src_column_count
799 LOOP
800 IF is_string(l_src_column_type,col_indx)
801 THEN
802 DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_string_value,g_max_col_value_size);
806 ELSIF is_date (l_src_column_type,col_indx)
803 ELSIF is_number(l_src_column_type,col_indx)
804 THEN
805 DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_number_value);
807 THEN
808 DBMS_SQL.DEFINE_COLUMN (cur_src,col_indx,l_date_value);
809 END IF;
810 END LOOP;
811
812 l_return_value := DBMS_SQL.EXECUTE(cur_src);
813 LOOP
814 l_return_value := DBMS_SQL.FETCH_ROWS(cur_src);
815 EXIT WHEN l_return_value = 0;
816
817 FOR col_indx IN 1 .. l_src_column_count
818 LOOP
819 IF Column_Exists(l_tgt_column_name,l_src_column_name(col_indx))
820 THEN
821 IF is_string(l_src_column_type,col_indx)
822 THEN
823 DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_string_value);
824 DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_string_value);
825 DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_string_value);
826 ELSIF is_number(l_src_column_type,col_indx)
827 THEN
828 DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_number_value);
829 DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_number_value);
830 DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_number_value);
831 ELSIF is_date(l_src_column_type,col_indx)
832 THEN
833 DBMS_SQL.COLUMN_VALUE(cur_src,col_indx,l_date_value);
834 DBMS_SQL.BIND_VARIABLE(cur_tgt_upd,l_src_column_name(col_indx),l_date_value);
835 DBMS_SQL.BIND_VARIABLE(cur_tgt_ins,l_src_column_name(col_indx),l_date_value);
836 END IF;
837 END IF;
838 END LOOP;
839
840 /* Execute the DML statement against the Target table */
841 l_update_count := DBMS_SQL.EXECUTE(cur_tgt_upd);
842
843 /* Insert the record into table if Update fails */
844 IF l_update_count = 0
845 THEN
846 l_update_count := DBMS_SQL.EXECUTE(cur_tgt_ins);
847 END IF;
848 END LOOP;
849
850 DBMS_SQL.CLOSE_CURSOR(cur_src);
851 DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
852 DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
853
854 /* Delete Records from Target table for records marked with "ACTION_FLAG = 'D'" in Source Error table */
855 l_delete_stmt := 'DELETE FROM ' || l_tgt_table_name || ' WHERE REC_ID IN (SELECT REC_ID FROM ' || l_src_table_name;
856 l_delete_stmt := l_delete_stmt || ' WHERE ACTION_FLAG = ''D'' ' || l_rest_where_clause || ')';
857 EXECUTE IMMEDIATE l_delete_stmt;
858
859 /* Delete Transfered Records (i.e. ACTION_FLAG = 'Y') as well as records marked with "ACTION_FLAG = 'D'"
860 from Source Error table */
861 l_delete_stmt := 'DELETE FROM ' || l_src_table_name || ' WHERE ACTION_FLAG IN (''Y'',''D'') ' || l_rest_where_clause;
862 EXECUTE IMMEDIATE l_delete_stmt;
863
864 COMMIT;
865 EXCEPTION
866 WHEN OTHERS
867 THEN
868 IF DBMS_SQL.IS_OPEN(cur_src)
869 THEN
870 DBMS_SQL.CLOSE_CURSOR(cur_src);
871 END IF;
872 IF DBMS_SQL.IS_OPEN(cur_tgt_upd)
873 THEN
874 DBMS_SQL.CLOSE_CURSOR(cur_tgt_upd);
875 END IF;
876 IF DBMS_SQL.IS_OPEN(cur_tgt_ins)
877 THEN
878 DBMS_SQL.CLOSE_CURSOR(cur_tgt_ins);
879 END IF;
880 Raise_Error(SQLERRM);
881 END Transfer_Data;
882
883 PROCEDURE refresh_mv (
884 p_list IN VARCHAR2,
885 p_method IN VARCHAR2 DEFAULT NULL,
886 p_rollback_seg IN VARCHAR2 DEFAULT NULL,
887 p_push_deferred_rpc IN BOOLEAN DEFAULT FALSE,
888 p_refresh_after_errors IN BOOLEAN DEFAULT FALSE,
889 p_purge_option IN BINARY_INTEGER DEFAULT 1,
890 p_parallelism IN BINARY_INTEGER DEFAULT 0,
891 p_heap_size IN BINARY_INTEGER DEFAULT 0,
892 p_atomic_refresh IN BOOLEAN DEFAULT FALSE,
893 p_job_id IN VARCHAR2 DEFAULT NULL,
894 p_refreshed_by IN VARCHAR2 DEFAULT NULL,
895 x_out OUT NOCOPY VARCHAR2,
896 x_message OUT NOCOPY VARCHAR2
897 ) IS
898 v_seq NUMBER;
899 BEGIN
900
901 SELECT ddr_u_mv_rfrsh_seq.nextval INTO v_seq FROM DUAL;
902 INSERT INTO ddr_u_mv_rfrsh_log(refresh_job_id
903 ,refresh_sequence
904 ,mv_name
905 ,refresh_method
906 ,error_message
907 ,refreshed_by
908 ,start_date
909 ,end_date)
910 VALUES(p_job_id
911 ,v_seq
912 ,p_list
913 ,p_method
914 ,NULL
915 ,p_refreshed_by
916 ,SYSDATE
917 ,NULL);
918 COMMIT;
919
920 DBMS_MVIEW.REFRESH(p_list, p_method, p_rollback_seg, p_push_deferred_rpc,
921 p_refresh_after_errors, p_purge_option,
922 p_parallelism, p_heap_size, p_atomic_refresh);
923
924 UPDATE ddr_u_mv_rfrsh_log
925 SET end_date = SYSDATE
926 WHERE refresh_job_id = p_job_id
927 AND refresh_sequence = v_seq
928 AND mv_name = p_list;
929 COMMIT;
930
931 x_out := 'S';
932 x_message := NULL;
933 EXCEPTION
934 WHEN OTHERS THEN
935 x_out := 'F';
936 x_message := SQLERRM;
937
938 UPDATE ddr_u_mv_rfrsh_log
939 SET end_date = SYSDATE,
940 error_message = x_message
941 WHERE refresh_job_id = p_job_id
942 AND refresh_sequence = v_seq
943 AND mv_name = p_list;
944 COMMIT;
945
946 END refresh_mv;
947
948 FUNCTION get_mv_refresh_job_id RETURN VARCHAR2 IS
949 BEGIN
950 RETURN TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS');
951 END get_mv_refresh_job_id;
952
953 PROCEDURE truncate_mv_log(
954 p_mv_log_name IN VARCHAR2,
955 p_job_id IN VARCHAR2 DEFAULT NULL,
956 p_refreshed_by IN VARCHAR2 DEFAULT NULL,
957 x_out OUT NOCOPY VARCHAR2,
958 x_message OUT NOCOPY VARCHAR2
959 ) IS
960 v_seq NUMBER;
961 BEGIN
962
963 SELECT ddr_u_mv_rfrsh_seq.nextval INTO v_seq FROM DUAL;
964 INSERT INTO ddr_u_mv_rfrsh_log(refresh_job_id
965 ,refresh_sequence
966 ,mv_name
967 ,refresh_method
968 ,error_message
969 ,refreshed_by
970 ,start_date
971 ,end_date)
972 VALUES(p_job_id
973 ,v_seq
974 ,p_mv_log_name
975 ,'TRUNCATE'
976 ,p_refreshed_by
977 ,NULL
978 ,SYSDATE
979 ,NULL);
980 COMMIT;
981
982 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||p_mv_log_name||' DROP STORAGE';
983
984 UPDATE ddr_u_mv_rfrsh_log
985 SET end_date = SYSDATE
986 WHERE refresh_job_id = p_job_id
987 AND refresh_sequence = v_seq
988 AND mv_name = p_mv_log_name;
989 COMMIT;
990
991 x_out := 'S';
992 x_message := NULL;
993 EXCEPTION
994 WHEN OTHERS THEN
995 x_out := 'F';
996 x_message := SQLERRM;
997 UPDATE ddr_u_mv_rfrsh_log
998 SET end_date = SYSDATE,
999 error_message = x_message
1000 WHERE refresh_job_id = p_job_id
1001 AND refresh_sequence = v_seq
1002 AND mv_name = p_mv_log_name;
1003 COMMIT;
1004
1005 END truncate_mv_log;
1006
1007
1008 -- p_mv_name => name of MV to be refreshed
1009 -- p_refresh_type => 'F' for fast and 'C' for complete
1010 -- p_refresh_dependents => 'Y' refreshes all the MV that this MV depends on
1011 -- 'N' refreshes only this MV
1012 -- p_exclude_list => comma separated list of dependent MV to exclude from refresh
1013 -- p_exclude_like_list => comma separated list of 'LIKE' syntax to match dependent MVs to exclude from refresh
1014
1015 -- This recursive logic will in-efficiently take care of case where a MV A
1016 -- dependes on MV B and MV C, and MV B also depends on MV C.
1017 -- In this scenario MV C will get refreshed twice
1018 -- We dont have this kind of setup now but to take care of this setup the logic
1019 -- would be to first list all the MVs once in order of their dependency in
1020 -- array and then refersh them in that order
1021
1022 PROCEDURE refresh_mv_odi (p_mv_name VARCHAR2 DEFAULT NULL
1023 ,p_refresh_type VARCHAR2
1024 ,p_refresh_dependents VARCHAR2 DEFAULT 'Y'
1025 ,p_exclude_1 VARCHAR2 DEFAULT NULL
1026 ,p_exclude_2 VARCHAR2 DEFAULT NULL
1027 ,p_exclude_3 VARCHAR2 DEFAULT NULL
1028 ,p_exclude_like_1 VARCHAR2 DEFAULT NULL
1029 ,p_exclude_like_2 VARCHAR2 DEFAULT NULL
1030 ,p_exclude_like_3 VARCHAR2 DEFAULT NULL
1031 ,p_include_like_1 VARCHAR2 DEFAULT NULL
1032 ,p_parent VARCHAR2 DEFAULT 'Y'
1033 ,p_mv_run_id VARCHAR2 DEFAULT NULL
1034 ,p_refreshed_by VARCHAR2 DEFAULT NULL
1035 ) IS
1036 X_OUT VARCHAR2(1000);
1037 X_MESSAGE varchar2(1000);
1038 v_schema VARCHAR2(30) := 'APPS';
1039
1040 CURSOR c1(p_mv_name VARCHAR2) IS
1041 SELECT dep.name, dep.referenced_name
1042 FROM dba_dependencies dep
1043 WHERE 1=1
1044 AND ((dep.referenced_name<> p_exclude_1 OR p_exclude_1 IS NULL) AND
1045 (dep.referenced_name <> p_exclude_2 OR p_exclude_2 IS NULL) AND
1046 (dep.referenced_name <> p_exclude_3 OR p_exclude_3 IS NULL)
1047 )
1048 AND ((dep.referenced_name NOT LIKE p_exclude_like_1 OR p_exclude_like_1 IS NULL)
1049 AND
1050 (dep.referenced_name NOT LIKE p_exclude_like_2 OR p_exclude_like_2 IS NULL)
1051 AND
1052 (dep.referenced_name NOT LIKE p_exclude_like_3 OR p_exclude_like_3 IS NULL)
1053 )
1054 AND ((dep.name LIKE p_include_like_1 AND p_mv_name IS NULL) OR (p_include_like_1 IS NULL AND
1055 dep.name = p_mv_name)
1056 )
1057 AND dep.type = 'MATERIALIZED VIEW'
1058 AND dep.referenced_type = 'MATERIALIZED VIEW'
1059 AND dep.owner = v_schema;
1060
1061 BEGIN
1062
1063 IF p_refresh_dependents <> 'Y' THEN --{
1064 refresh_mv(P_LIST => P_MV_NAME,
1065 P_METHOD => P_REFRESH_TYPE,
1066 P_JOB_ID => P_MV_RUN_ID,
1067 p_refreshed_by => p_refreshed_by,
1068 X_OUT => X_OUT,
1069 x_message => x_message);
1070 NULL;
1071 ELSE
1072 FOR rec IN c1(p_mv_name) LOOP --{
1073 refresh_mv_odi (p_mv_name => rec.referenced_name
1074 ,p_refresh_type => p_refresh_type
1075 ,p_refresh_dependents => p_refresh_dependents
1076 ,p_exclude_1 => p_exclude_1
1077 ,p_exclude_2 => p_exclude_2
1078 ,p_exclude_3 => p_exclude_3
1079 ,p_exclude_like_1 => p_exclude_like_1
1080 ,p_exclude_like_2 => p_exclude_like_2
1081 ,p_exclude_like_3 => p_exclude_like_3
1082 ,p_include_like_1 => NULL
1083 ,p_parent => NULL
1084 );
1085
1086 refresh_mv( P_LIST => rec.referenced_name,
1087 P_METHOD => p_refresh_type,
1088 P_JOB_ID => p_mv_run_id,
1089 p_refreshed_by => p_refreshed_by,
1090 X_OUT => x_out,
1091 x_message => x_message);
1092
1093 IF p_parent IS NOT NULL THEN --{
1094 REFRESH_MV(P_LIST =>REC.NAME,
1095 P_METHOD => P_REFRESH_TYPE,
1096 P_JOB_ID => P_MV_RUN_ID,
1097 p_refreshed_by => p_refreshed_by,
1098 X_OUT => X_OUT,
1099 x_message => x_message);
1100 END IF; --}
1101 END LOOP; --}
1102 END IF; --}
1103 END refresh_mv_odi;
1104
1105
1106 PROCEDURE truncate_mv_log_wrp (p_mv_log_name VARCHAR2,
1107 p_job_id VARCHAR2 DEFAULT NULL,
1108 p_refreshed_by VARCHAR2 DEFAULT NULL
1109 ) IS
1110
1111 X_OUT VARCHAR2(10);
1112 X_MESSAGE VARCHAR2(1000);
1113 v_mv_log_name VARCHAR2(100);
1114 v_apps_user VARCHAR2(30) := 'AP';
1115 BEGIN
1116 set_owner;
1117 v_apps_user := v_apps_user || 'PS';
1118
1119 v_mv_log_name := p_mv_log_name;
1120
1121 IF g_owner_name_real <> 'APPS' THEN --{
1122 v_mv_log_name := REPLACE(v_mv_log_name,v_apps_user||'.D','D');
1123 v_mv_log_name := REPLACE(v_mv_log_name,v_apps_user||'.M','M');
1124 v_mv_log_name := REPLACE(v_mv_log_name,'DDR.M','M');
1125 END IF; --}
1126
1127 DDR_ETL_UTIL_PKG.TRUNCATE_MV_LOG(P_MV_LOG_NAME => V_MV_LOG_NAME,
1128 P_JOB_ID => P_JOB_ID,
1129 P_REFRESHED_BY => P_REFRESHED_BY,
1130 X_OUT => X_OUT,
1131 X_MESSAGE => X_MESSAGE
1132 );
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 X_OUT := 'F';
1136 X_MESSAGE := SQLERRM;
1137 END;
1138
1139
1140 END ddr_etl_util_pkg;