[Home] [Help]
PACKAGE BODY: APPS.AP_ACCTG_DATA_FIX_PKG
Source
1 PACKAGE BODY AP_Acctg_Data_Fix_PKG AS
2 /* $Header: apgdfalb.pls 120.1 2008/03/10 09:49:15 serabell noship $ */
3
4 /* Procedure to open the log files on the instance where the datafix
5 script is being run. The log file contains the log messages
6 and the report outputs written by the data fix scripts.
7 The file location is the environment's 'utl_file_dir' parameter. */
8
9 PROCEDURE Open_Log_Out_Files
10 (P_Bug_Number IN varchar2,
11 P_File_Location OUT NOCOPY VARCHAR2) IS
12
13 l_log_file VARCHAR2(30);
14 l_out_file VARCHAR2(30);
15 l_file_location v$parameter.value%type;
16 No_Utl_Dir EXCEPTION;
17 l_date VARCHAR2(30);
18 l_message VARCHAR2(500);
19
20 BEGIN
21 SELECT TO_CHAR(SYSDATE, '-HH24:MI:SS')
22 INTO l_date
23 FROM DUAL;
24
25 l_log_file := p_bug_number||l_date||'.html';
26 l_out_file := p_bug_number||'.out';
27
28 SELECT decode(instr(value,','),0,value,
29 SUBSTR (value,1,instr(value,',') - 1))
30 INTO l_file_location
31 FROM v$parameter
32 WHERE name = 'utl_file_dir';
33
34 IF l_file_location IS NULL THEN
35 RAISE No_Utl_Dir;
36 END IF;
37
38 p_file_location:=l_file_location||'/'||l_log_file;
39
40 FND_FILE.PUT_NAMES(l_log_file,
41 l_out_file,
42 l_file_location);
43 EXCEPTION
44 WHEN OTHERS THEN
45 l_message := 'Exception :: '||SQLERRM||'<p>';
46 FND_File.Put_Line(fnd_file.log,l_message);
47
48 l_message := 'in side AP_ACCTG_DATA_FIX_PKG.Open_Log_Out_Files '||'<p>';
49 FND_File.Put_Line(fnd_file.log,l_message);
50 APP_EXCEPTION.RAISE_EXCEPTION;
51 END Open_Log_Out_Files;
52
53
54
55 /* Procedure to close the log files on the instance once all the log
56 messages are written to it. */
57
58 PROCEDURE Close_Log_Out_Files IS
59 BEGIN
60 FND_File.Close;
61 END Close_Log_Out_Files;
62
63
64 /* Procedure to create temproary backup tables for the accounting */
65
66 PROCEDURE Create_Temp_Acctg_Tables
67 (P_Bug_Number IN NUMBER) IS
68 First_time number :=0;
69 l_calling_sequence VARCHAR2(2000);
70 l_message VARCHAR2(500);
71 Begin
72 l_calling_sequence :=
73 'AP_Acctg_Data_Fix_PKG.Create_Temp_Acctg_Tables';
74
75 Execute Immediate
76 'create table '||'Events_'||P_Bug_Number||
77 ' as select * from XLA_EVENTS where rownum<1 ';
78
79 Execute Immediate
80 'alter table '||'Events_'||P_Bug_Number||
81 ' add datafix_update_date date default sysdate';
82
83 Execute Immediate
84 'create table '||'HEADERS_'||P_Bug_Number||
85 ' as select * from XLA_AE_HEADERS where rownum<1 ';
86
87 Execute Immediate
88 'alter table '||'HEADERS_'||P_Bug_Number||
89 ' add datafix_update_date date default sysdate';
90
91 Execute Immediate
92 'create table '||'LINES_'||P_Bug_Number||
93 ' as select * from XLA_AE_LINES where rownum<1 ';
94
95 Execute Immediate
96 'alter table '||'LINES_'||P_Bug_Number||
97 ' add datafix_update_date date default sysdate';
98
99 Execute Immediate
100 'create table '||'DISTRIB_LINKS_'||P_Bug_Number||
101 ' as select * from XLA_DISTRIBUTION_LINKS where rownum<1 ';
102
103 Execute Immediate
104 'alter table '||'DISTRIB_LINKS_'||P_Bug_Number||
105 ' add datafix_update_date date default sysdate';
106
107 Execute Immediate
108 'create table '||'TRANS_ENTITIES_'||P_Bug_Number||
109 ' as select * from XLA_TRANSACTION_ENTITIES where rownum<1 ';
110
111 Execute Immediate
112 'alter table '||'TRANS_ENTITIES_'||P_Bug_Number||
113 ' add datafix_update_date date default sysdate';
114
115 EXCEPTION
116
117 WHEN OTHERS THEN
118 l_message := 'Exception :: '||SQLERRM||'<p>';
119 FND_File.Put_Line(fnd_file.log,l_message);
120
121 l_message := 'in side '||l_calling_sequence||'<p>';
122 FND_File.Put_Line(fnd_file.log,l_message);
123
124 APP_EXCEPTION.RAISE_EXCEPTION;
125 End Create_Temp_Acctg_Tables;
126
127
128 /* Procedure to get all the columns for a particular table.
129 This procedure gets called from Back_Up_Acctg procedure. */
130
131 PROCEDURE Get_Cols(tab_name in varchar2,ret_str out NOCOPY varchar2) is
132 TYPE sqlCurTyp IS REF CURSOR;
133 cur sqlCurTyp;
134 stmt_str VARCHAR2(500);
135 col_name varchar2(100);
136 l_all_tab_columns varchar2(100) := 'ALL_TAB_COLUMNS';
137 l_calling_sequence VARCHAR2(2000);
138 l_message varchar2(500);
139
140 begin
141
142 l_calling_sequence :=
143 'AP_Acctg_Data_Fix_PKG.Get_Cols<-' ;
144 stmt_str := 'select column_name from '|| l_all_tab_columns ||
145 ' where table_name=:1 and column_name<>''DATAFIX_UPDATE_DATE''';
146 OPEN cur FOR stmt_str USING TAB_NAME;
147 LOOP
148 FETCH cur INTO COL_NAME;
149 EXIT WHEN cur%NOTFOUND;
150 ret_str:=ret_str||','||col_name;
151 END LOOP;
152 CLOSE cur;
153
154 ret_str:= SUBSTR(ret_str,2,LENGTH(ret_str));
155 EXCEPTION
156
157 WHEN OTHERS THEN
158 l_message := 'Exception :: '||SQLERRM||'<p>';
159 FND_File.Put_Line(fnd_file.log,l_message);
160
161 l_message := 'in side '||l_calling_sequence||'<p>';
162 FND_File.Put_Line(fnd_file.log,l_message);
163 APP_EXCEPTION.RAISE_EXCEPTION;
164 end Get_Cols;
165
166
167 /* Procedure to get the backup of all the Accounting (XLA) tables. */
168
169 Procedure Back_Up_Acctg(P_Bug_Number in number) is
170 sql_liab_stat varchar2(5000);
171 col_str1 varchar2(5000);
172 col_str2 varchar2(5000);
173 col_str3 varchar2(5000);
174 col_str4 varchar2(5000);
175 col_str5 varchar2(5000);
176 bkp_tables_exists number:=0;
177 l_message varchar2(500);
178 TYPE sqlCurTyp IS REF CURSOR;
179 cur sqlCurTyp;
180 l_tables varchar2(100) := 'ALL_TABLES';
181 l_calling_sequence VARCHAR2(2000);
182 BEGIN
183
184 l_calling_sequence :=
185 'AP_Acctg_Data_Fix_PKG.Back_Up_Acctg<-' ;
186
187 sql_liab_stat := 'select count(*) from '|| l_tables ||
188 ' where table_name='||''''||'HEADERS_'||P_Bug_number||'''';
189 OPEN cur FOR sql_liab_stat;
190 fetch cur into bkp_tables_exists;
191 CLOSE cur;
192
193 AP_Acctg_Data_Fix_PKG.Print('_______________________________________'||
194 '_______________________________________');
195
196 if (bkp_tables_exists=0) then
197 l_message := 'Before creating accounting backup tables <p>';
198
199 AP_Acctg_Data_Fix_PKG.Create_Temp_Acctg_Tables(p_bug_number);
200
201 l_message := 'After creating accounting backup tables <p>';
202
203 end if;
204
205 AP_Acctg_Data_Fix_PKG.get_cols('EVENTS_'||P_Bug_Number,col_str5);
206
207 AP_Acctg_Data_Fix_PKG.get_cols('HEADERS_'||P_Bug_Number,col_str1);
208
209 AP_Acctg_Data_Fix_PKG.get_cols('LINES_'||P_Bug_Number,col_str2);
210
211 AP_Acctg_Data_Fix_PKG.get_cols('DISTRIB_LINKS_'||P_Bug_Number,col_str3);
212
213 /* AP_Acctg_Data_Fix_PKG.get_cols('TRANS_ENTITIES_'||P_Bug_Number,col_str4); */
214
215 l_message := 'Before creating backup for Accounting tables <p>';
216 FND_File.Put_Line(fnd_file.log,l_message);
217
218 sql_liab_stat := 'insert into events_'||P_Bug_Number||'('||col_str5||') '||
219 ' select '||col_str5||' from xla_events '||
220 ' where event_id in '||
221 ' (select event_id from ap_temp_data_driver_'||P_Bug_Number||
222 ' Where process_flag=''Y'')';
223
224 EXECUTE IMMEDIATE sql_liab_stat ;
225
226 sql_liab_stat := 'insert into headers_'||P_Bug_Number||'('||col_str1||') '||
227 ' select '||col_str1||' from xla_ae_headers '||
228 ' where event_id in '||
229 ' (select event_id from ap_temp_data_driver_'||P_Bug_Number||
230 ' Where process_flag=''Y'')';
231
232 EXECUTE IMMEDIATE sql_liab_stat ;
233
234 sql_liab_stat := 'insert into lines_'||P_Bug_Number||'('||col_str2||') '||
235 ' select '||col_str2||' from xla_ae_lines '||
236 ' where ae_header_id in '||
237 ' (select ae_header_id from headers_'||P_Bug_Number||') ';
238
239 EXECUTE IMMEDIATE sql_liab_stat;
240
241 sql_liab_stat := 'insert into distrib_links_'||P_Bug_Number||'('||col_str3||') '||
242 ' select '||col_str3||' from xla_distribution_links '||
243 ' where ae_header_id in '||
244 ' (select ae_header_id from headers_'||P_Bug_Number||') ';
245
246 EXECUTE IMMEDIATE sql_liab_stat;
247
248 /* sql_liab_stat := 'insert into trans_entities_'||P_Bug_Number||'('||col_str4||') '||
249 ' select '||col_str4||' from xla_transaction_entities '||
250 ' where entity_id in '||
251 ' (select entity_id from ap_temp_data_driver_'||P_Bug_Number||') ';
252
253 EXECUTE IMMEDIATE sql_liab_stat; */
254
255 l_message := 'After creating backup for Accounting tables <p>';
256 FND_File.Put_Line(fnd_file.log,l_message);
257
258 EXCEPTION
259
260 WHEN OTHERS THEN
261 APP_EXCEPTION.RAISE_EXCEPTION;
262 l_message := 'Exception :: '||SQLERRM||'<p>';
263 FND_File.Put_Line(fnd_file.log,l_message);
264
265 l_message := 'in side '||l_calling_sequence||'<p>';
266 FND_File.Put_Line(fnd_file.log,l_message);
267 END Back_Up_Acctg;
268
269 /* Procedure to print messages in the Log file */
270 PROCEDURE Print
271 (p_message IN VARCHAR2,
272 P_calling_sequence IN VARCHAR2) IS
273 l_message varchar2(500);
274 l_calling_sequence varchar2(500);
275 Begin
276 l_calling_sequence:='AP_Acctg_Data_Fix_PKG.print <- '||p_calling_sequence;
277
278 FND_File.Put_Line(fnd_file.log,p_message||'<p>');
279 Exception
280 WHEN OTHERS THEN
281 l_message := 'Exception :: '||SQLERRM||'<p>';
282 FND_File.Put_Line(fnd_file.log,l_message);
283
284 l_message := 'in side '||l_calling_sequence||'<p>';
285 FND_File.Put_Line(fnd_file.log,l_message);
286 APP_EXCEPTION.RAISE_EXCEPTION;
287 End Print;
288
289
290 /* Procedure to print the values in the table and column list
291 passed as parameters, in HTML table format, into the Log file. */
292
293 Procedure Print_Html_Table
294 (p_select_list in VARCHAR2,
295 p_table_in in VARCHAR2,
296 p_where_in in VARCHAR2,
297 P_calling_sequence in VARCHAR2) IS
298
299 l_calling_sequence varchar2(500);
300 select_list1 varchar2(2000):=P_SELECT_LIST;
301
302 TYPE string_tab IS TABLE OF VARCHAR2(100)
303 INDEX BY BINARY_INTEGER;
304
305 TYPE integer_tab IS TABLE OF NUMBER
306 INDEX BY BINARY_INTEGER;
307
308 colname string_tab;
309 coltype string_tab;
310 collen integer_tab;
311
312 owner_nm VARCHAR2(100) := USER;
313 table_nm VARCHAR2(100) := UPPER (p_table_in);
314 where_clause VARCHAR2(1000) := LTRIM (UPPER (p_where_in));
315
316 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
317 fdbk INTEGER := 0;
318
319 string_value VARCHAR2(2000);
320 number_value NUMBER;
321 date_value DATE;
322
323 dot_loc INTEGER;
324 cur_pos INTEGER:=1;
325
326 col_count INTEGER := 0;
327 col_line VARCHAR2(2000);
328 col_list VARCHAR2(2000);
329 l_message VARCHAR2(2000):='<table border="5"><tr>';
330
331 BEGIN
332 l_calling_sequence:='AP_Acctg_Data_Fix_PKG.Print_Html_Table <- '||
333 p_calling_sequence;
334 dot_loc := INSTR (table_nm, '.');
335 IF dot_loc > 0
336 THEN
337 owner_nm := SUBSTR (table_nm, 1, dot_loc-1);
338 table_nm := SUBSTR (table_nm, dot_loc+1);
339 END IF;
340 --print('stg1');
341 loop
342 dot_loc := INSTR(select_list1,',');
343
344 IF (DOT_LOC<=0) THEN
345 --print('stg2');
346 col_list := col_list || ', ' || select_list1;
347 col_count := col_count + 1;
348 colname (col_count) := select_list1;
349 l_message:=l_message||'<th>'||colname (col_count)||'</th></tr>';
350 --print('stg3');
351 ELSE
352 --print('stg4');
353 col_list := col_list || ', ' || SUBSTR (select_list1, 1, dot_loc-1);
354 col_count := col_count + 1;
355 colname (col_count) := SUBSTR (select_list1, 1, dot_loc-1);
356 cur_pos:=dot_loc+1;
357 select_list1:=SUBSTR (select_list1, dot_loc+1);
358
359 l_message:=l_message||'<th>'||colname (col_count)||'</th>';
360 --print('st5');
361 end if;
362
363 --print('stg66');
364 --print(col_count);
365 --print(colname (col_count));
366
367 SELECT data_type,DATA_LENGTH
368 INTO coltype (col_count) ,collen(col_count)
369 FROM all_tab_columns
370 WHERE owner = owner_nm
371 AND table_name = table_nm
372 AND column_name=colname (col_count);
373 --print('stg7');
374
375 EXIT WHEN (DOT_LOC<=0);
376
377 end loop;
378 --print('stg8');
379 col_list := RTRIM (LTRIM (col_list, ', '), ', ');
380 --print('stg9');
381 Print(l_message);
382 --print('stg10');
383
384 IF where_clause IS NOT NULL
385 THEN
386 IF (where_clause NOT LIKE 'GROUP BY%' AND
387 where_clause NOT LIKE 'ORDER BY%')
388 THEN
389 where_clause :=
390 'WHERE ' || LTRIM (where_clause, 'WHERE');
391 END IF;
392 END IF;
393 --print('stg11');
394
395 DBMS_SQL.PARSE
396 (cur,
397 'SELECT ' || col_list ||
398 ' FROM ' || p_table_in || ' ' || where_clause,
399 1);
400
401 --print('stg12');
402 FOR col_ind IN 1 .. col_count
403 LOOP
404 IF (coltype(col_ind) IN ('CHAR', 'VARCHAR2'))
405 THEN
406 DBMS_SQL.DEFINE_COLUMN
407 (cur, col_ind, string_value, collen (col_ind));
408 ELSIF (coltype(col_ind) = 'NUMBER')
409 THEN
410 DBMS_SQL.DEFINE_COLUMN (cur, col_ind, number_value);
411
412 ELSIF (coltype(col_ind) = 'DATE')
413 THEN
414 DBMS_SQL.DEFINE_COLUMN (cur, col_ind, date_value);
415 END IF;
416 END LOOP;
417 --print('stg13');
418
419 fdbk := DBMS_SQL.EXECUTE (cur);
420 LOOP
421 fdbk := DBMS_SQL.FETCH_ROWS (cur);
422 EXIT WHEN fdbk = 0;
423
424 col_line := NULL;
425 l_message:='<tr>';
426 FOR col_ind IN 1 .. col_count
427 LOOP
428 IF (coltype(col_ind) IN ('CHAR', 'VARCHAR2'))
429 THEN
430 DBMS_SQL.COLUMN_VALUE
431 (cur, col_ind, string_value);
432
433 ELSIF (coltype(col_ind) = 'NUMBER')
434 THEN
435 DBMS_SQL.COLUMN_VALUE
436 (cur, col_ind, number_value);
437 string_value := TO_CHAR (number_value);
438
439 ELSIF (coltype(col_ind) = 'DATE')
440 THEN
441 DBMS_SQL.COLUMN_VALUE
442 (cur, col_ind, date_value);
443 string_value := date_value;
444 END IF;
445
446 col_line :=
447 col_line || ' ' ||
448 RPAD (NVL (string_value, ' '), collen (col_ind));
449 l_message:=l_message||'<td>'||NVL (string_value, ' ')||'</td>';
450 END LOOP;
451 l_message:=l_message||'</tr>';
452 Print(l_message);
453 END LOOP;
454 print('</table>');
455
456 Exception
457 WHEN OTHERS THEN
458 l_message := 'SELECT ' || col_list ||
459 ' FROM ' || p_table_in || ' ' || where_clause||'<p>';
460 FND_File.Put_Line(fnd_file.log,l_message);
461
462 l_message := 'Exception :: '||SQLERRM||'<p>';
463 FND_File.Put_Line(fnd_file.log,l_message);
464
465 l_message := 'in side '||l_calling_sequence||'<p>';
466 FND_File.Put_Line(fnd_file.log,l_message);
467 APP_EXCEPTION.RAISE_EXCEPTION;
468 END Print_Html_Table;
469
470
471 /* Procedure to backup the data from the source table to destination
472 table. It also takes in as input SELECT LIST which determine
473 the list of columns which will be backed up. The additional
477 (p_source_table in VARCHAR2,
474 WHERE caluse can also be passed in as input. */
475
476 Procedure Backup_data
478 p_destination_table in VARCHAR2,
479 p_select_list in VARCHAR2,
480 p_where_clause in VARCHAR2,
481 P_calling_sequence in VARCHAR2) is
482
483 l_calling_sequence varchar2(500);
484 l_message varchar2(500);
485 TYPE sqlCurTyp IS REF CURSOR;
486 cur sqlCurTyp;
487 l_tables varchar2(100) := 'ALL_TABLES';
488 l_bkp_tables_exists number:=0;
489 sql_stmt varchar2(10000);
490 col_str1 varchar2(5000);
491
492 Begin
493 l_calling_sequence:='AP_Acctg_Data_Fix_PKG.Backup_data <- '||
494 p_calling_sequence;
495
496 sql_stmt := 'select count(*) from '|| l_tables ||
497 ' where table_name='||''''||p_destination_table||'''';
498
499 OPEN cur FOR sql_stmt;
500 FETCH cur into l_bkp_tables_exists;
501 CLOSE cur;
502
503 if (l_bkp_tables_exists=0) then
504 Print('Before creating backup table '||p_destination_table);
505 Execute Immediate
506 'Create table '||p_destination_table||
507 ' as select '||p_select_list||' from '||
508 p_source_table||' where rownum<1 ';
509
510 Execute Immediate
511 'alter table '||p_destination_table||
512 ' add datafix_update_date date default sysdate';
513 Print('Created table '||p_destination_table);
514
515 end if;
516 sql_stmt := 'insert into '||p_destination_table||
517 '('||P_SELECT_LIST||') '||' select '||P_SELECT_LIST||
518 ' from '||P_SOURCE_TABLE||' '||P_WHERE_CLAUSE;
519 l_message:=sql_stmt;
520 EXECUTE IMMEDIATE sql_stmt;
521
522 Exception
523 WHEN OTHERS THEN
524 l_message := 'Exception :: '||SQLERRM;
525 print(fnd_file.log,l_message);
526
527 l_message := 'in side '||l_calling_sequence;
528 print(fnd_file.log,l_message);
529 APP_EXCEPTION.RAISE_EXCEPTION;
530 End Backup_data;
531
532 END AP_Acctg_Data_Fix_PKG;