[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.45.12020000.5 2012/10/16 11:45:48 vinerao ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(50) :='AP.PLSQL.AP_ACCTG_DATA_FIX_PKG.';
12
13
14
15 /* Procedure to open the log files on the instance where the datafix
16 script is being run. The log file contains the log messages
17 and the report outputs written by the data fix scripts.
18 The file location is the environment's 'utl_file_dir' parameter. */
19
20 PROCEDURE Open_Log_Out_Files
21 (P_Bug_Number IN varchar2,
22 P_File_Location OUT NOCOPY VARCHAR2) IS
23
24 l_log_file VARCHAR2(30);
25 l_out_file VARCHAR2(30);
26 l_file_location v$parameter.value%type;
27 No_Utl_Dir EXCEPTION;
28 l_date VARCHAR2(30);
29 l_message VARCHAR2(500);
30
31 BEGIN
32 SELECT TO_CHAR(SYSDATE, '-HH24:MI:SS')
33 INTO l_date
34 FROM DUAL;
35
36 l_log_file := p_bug_number||l_date||'.html';
37 l_out_file := p_bug_number||'.out';
38
39 SELECT decode(instr(value,','),0,value,
40 SUBSTR (value,1,instr(value,',') - 1))
41 INTO l_file_location
42 FROM v$parameter
43 WHERE name = 'utl_file_dir';
44
45 IF l_file_location IS NULL THEN
46 RAISE No_Utl_Dir;
47 END IF;
48
49 p_file_location:=l_file_location||'/'||l_log_file;
50
51 FND_FILE.PUT_NAMES(l_log_file,
52 l_out_file,
53 l_file_location);
54 EXCEPTION
55 WHEN OTHERS THEN
56 l_message := 'Exception :: '||SQLERRM||'<p>';
57 FND_File.Put_Line(fnd_file.log,l_message);
58
59 l_message := 'in side AP_ACCTG_DATA_FIX_PKG.Open_Log_Out_Files '||'<p>';
60 FND_File.Put_Line(fnd_file.log,l_message);
61 APP_EXCEPTION.RAISE_EXCEPTION;
62 END Open_Log_Out_Files;
63
64
65
66 /* Procedure to close the log files on the instance once all the log
67 messages are written to it. */
68
69 PROCEDURE Close_Log_Out_Files IS
70 BEGIN
71 FND_File.Close;
72 END Close_Log_Out_Files;
73
74
75 /* Procedure to create temproary backup tables for the accounting */
76
77 PROCEDURE Create_Temp_Acctg_Tables
78 (P_Bug_Number IN NUMBER) IS
79 First_time number :=0;
80 l_calling_sequence VARCHAR2(2000);
81 l_message VARCHAR2(500);
82 Begin
83 l_calling_sequence :=
84 'AP_Acctg_Data_Fix_PKG.Create_Temp_Acctg_Tables';
85
86 Execute Immediate
87 'create table '||'Events_'||P_Bug_Number||
88 ' as select * from XLA_EVENTS where rownum<1 ';
89
90 Execute Immediate
91 'alter table '||'Events_'||P_Bug_Number||
92 ' add datafix_update_date date default sysdate';
93
94 Execute Immediate
95 'create table '||'HEADERS_'||P_Bug_Number||
96 ' as select * from XLA_AE_HEADERS where rownum<1 ';
97
98 Execute Immediate
99 'alter table '||'HEADERS_'||P_Bug_Number||
100 ' add datafix_update_date date default sysdate';
101
102 Execute Immediate
103 'create table '||'LINES_'||P_Bug_Number||
104 ' as select * from XLA_AE_LINES where rownum<1 ';
105
106 Execute Immediate
107 'alter table '||'LINES_'||P_Bug_Number||
108 ' add datafix_update_date date default sysdate';
109
110 Execute Immediate
111 'create table '||'DISTRIB_LINKS_'||P_Bug_Number||
112 ' as select * from XLA_DISTRIBUTION_LINKS where rownum<1 ';
113
114 Execute Immediate
115 'alter table '||'DISTRIB_LINKS_'||P_Bug_Number||
116 ' add datafix_update_date date default sysdate';
117
118 Execute Immediate
119 'create table '||'TRANS_ENTITIES_'||P_Bug_Number||
120 ' as select * from XLA_TRANSACTION_ENTITIES where rownum<1 ';
121
122 Execute Immediate
123 'alter table '||'TRANS_ENTITIES_'||P_Bug_Number||
124 ' add datafix_update_date date default sysdate';
125
126 EXCEPTION
127
128 WHEN OTHERS THEN
129 l_message := 'Exception :: '||SQLERRM||'<p>';
130 FND_File.Put_Line(fnd_file.log,l_message);
131
132 l_message := 'in side '||l_calling_sequence||'<p>';
133 FND_File.Put_Line(fnd_file.log,l_message);
134
135 APP_EXCEPTION.RAISE_EXCEPTION;
136 End Create_Temp_Acctg_Tables;
137
138
139 /* Procedure to get all the columns for a particular table.
140 This procedure gets called from Back_Up_Acctg procedure. */
141
142 PROCEDURE Get_Cols(tab_name in varchar2,ret_str out NOCOPY varchar2) is
143 TYPE sqlCurTyp IS REF CURSOR;
144 cur sqlCurTyp;
145 stmt_str VARCHAR2(500);
146 col_name varchar2(100);
147 l_all_tab_columns varchar2(100) := 'ALL_TAB_COLUMNS';
148 l_calling_sequence VARCHAR2(2000);
149 l_message varchar2(500);
150
151 begin
152
153 l_calling_sequence :=
154 'AP_Acctg_Data_Fix_PKG.Get_Cols<-' ;
155 stmt_str := 'select column_name from '|| l_all_tab_columns ||
156 ' where table_name=:1 and column_name<>''DATAFIX_UPDATE_DATE''';
157 OPEN cur FOR stmt_str USING TAB_NAME;
158 LOOP
159 FETCH cur INTO COL_NAME;
160 EXIT WHEN cur%NOTFOUND;
161 ret_str:=ret_str||','||col_name;
162 END LOOP;
163 CLOSE cur;
164
165 ret_str:= SUBSTR(ret_str,2,LENGTH(ret_str));
166 EXCEPTION
167
168 WHEN OTHERS THEN
169 l_message := 'Exception :: '||SQLERRM||'<p>';
170 FND_File.Put_Line(fnd_file.log,l_message);
171
172 l_message := 'in side '||l_calling_sequence||'<p>';
173 FND_File.Put_Line(fnd_file.log,l_message);
174 APP_EXCEPTION.RAISE_EXCEPTION;
175 end Get_Cols;
176
177 /* Overload the Get_Cols procedure to handle the case where there are two tables with the same name
178 in different schemas. For example, you can find ap_invoices_all in both the ap and bifin schemas.
179 Without this the procedure will end with ORA-00957: duplicate column name
180 */
181
182 PROCEDURE Get_Cols(tab_name in varchar2, schema_name in varchar2, ret_str out NOCOPY varchar2) is
183 TYPE sqlCurTyp IS REF CURSOR;
184 cur sqlCurTyp;
185 stmt_str VARCHAR2(500);
186 col_name varchar2(100);
187 l_all_tab_columns varchar2(100) := 'ALL_TAB_COLUMNS';
188 l_calling_sequence VARCHAR2(2000);
189 l_message varchar2(500);
190
191 begin
192
193 l_calling_sequence :=
194 'AP_Acctg_Data_Fix_PKG.Get_Cols<-' ;
195 stmt_str := 'select column_name from '|| l_all_tab_columns ||
196 ' where table_name=:1 and owner =:2 and column_name<>''DATAFIX_UPDATE_DATE''';
197 OPEN cur FOR stmt_str USING TAB_NAME, SCHEMA_NAME;
198 LOOP
199 FETCH cur INTO COL_NAME;
200 EXIT WHEN cur%NOTFOUND;
201 ret_str:=ret_str||','||col_name;
202 END LOOP;
203 CLOSE cur;
204
205 ret_str:= SUBSTR(ret_str,2,LENGTH(ret_str));
206 EXCEPTION
207
208 WHEN OTHERS THEN
209 l_message := 'Exception :: '||SQLERRM||'<p>';
210 FND_File.Put_Line(fnd_file.log,l_message);
211
212 l_message := 'in side '||l_calling_sequence||'<p>';
213 FND_File.Put_Line(fnd_file.log,l_message);
214 APP_EXCEPTION.RAISE_EXCEPTION;
215 end Get_Cols;
216
217
218 /* Procedure to get the backup of all the Accounting (XLA) tables. */
219
220 Procedure Back_Up_Acctg(P_Bug_Number in number,
221 P_Driver_Table in VARCHAR2 DEFAULT NULL,
222 P_Calling_Sequence in VARCHAR2 DEFAULT NULL) is
223
224 l_driver_table ALL_TABLES.TABLE_NAME%TYPE;
225 l_debug_info VARCHAR2(4000);
226 sql_liab_stat varchar2(5000);
227 col_str1 varchar2(5000);
228 col_str2 varchar2(5000);
229 col_str3 varchar2(5000);
230 col_str4 varchar2(5000);
231 col_str5 varchar2(5000);
232 bkp_tables_exists number:=0;
233 l_message varchar2(500);
234 TYPE sqlCurTyp IS REF CURSOR;
235 cur sqlCurTyp;
236 l_tables varchar2(100) := 'ALL_TABLES';
237 l_calling_sequence VARCHAR2(2000);
238 BEGIN
239
240 l_calling_sequence :=
241 'AP_Acctg_Data_Fix_PKG.Back_Up_Acctg<-'||P_calling_Sequence ;
242
243 l_debug_info := 'Setting the driver table name';
244 IF P_Driver_Table IS NULL THEN
245 l_driver_table := 'AP_TEMP_DATA_DRIVER_'||P_Bug_number;
246 ELSE
247 l_driver_table := upper(P_Driver_Table);
248 END IF;
249
250 l_debug_info := 'Checking if the backup Tables exist';
251 sql_liab_stat := 'select count(*) from '|| l_tables ||
252 ' where table_name='||''''||'HEADERS_'||P_Bug_number||'''';
253 OPEN cur FOR sql_liab_stat;
254 fetch cur into bkp_tables_exists;
255 CLOSE cur;
256
257 AP_Acctg_Data_Fix_PKG.Print('_______________________________________'||
258 '_______________________________________');
259
260 if (bkp_tables_exists=0) then
261 l_message := 'Backup tables do not Exist: Before creating accounting backup tables <p>';
262 Print(l_message);
263
264 AP_Acctg_Data_Fix_PKG.Create_Temp_Acctg_Tables(p_bug_number);
265
266 l_message := 'After creating accounting backup tables <p>';
267 Print(l_message);
268
269 end if;
270
271 l_debug_info := 'Before getting the cols for the Backup Tables';
272
273 AP_Acctg_Data_Fix_PKG.get_cols('EVENTS_'||P_Bug_Number,col_str5);
274
275 AP_Acctg_Data_Fix_PKG.get_cols('HEADERS_'||P_Bug_Number,col_str1);
276
277 AP_Acctg_Data_Fix_PKG.get_cols('LINES_'||P_Bug_Number,col_str2);
278
279 AP_Acctg_Data_Fix_PKG.get_cols('DISTRIB_LINKS_'||P_Bug_Number,col_str3);
280
281 l_message := 'Before creating backup for Accounting tables <p>';
282 FND_File.Put_Line(fnd_file.log,l_message);
283
284 l_debug_info := 'Before backing the events';
285 sql_liab_stat := 'insert into events_'||P_Bug_Number||'('||col_str5||') '||
286 ' select '||col_str5||' from xla_events '||
287 ' where event_id in '||
288 ' (select event_id from '||l_driver_table||
289 ' Where process_flag=''Y'')';
290
291 EXECUTE IMMEDIATE sql_liab_stat ;
292
293 l_debug_info := 'Before backing the headers';
294 sql_liab_stat := 'insert into headers_'||P_Bug_Number||'('||col_str1||') '||
295 ' select '||col_str1||' from xla_ae_headers '||
296 ' where event_id in '||
297 ' (select event_id from '||l_driver_table||
298 ' Where process_flag=''Y'')';
299
300 EXECUTE IMMEDIATE sql_liab_stat ;
301
302 l_debug_info := 'Before backing the lines';
303 sql_liab_stat := 'insert into lines_'||P_Bug_Number||'('||col_str2||') '||
304 ' select '||col_str2||' from xla_ae_lines '||
305 ' where ae_header_id in '||
306 ' (select xah.ae_header_id '||
307 ' from headers_'||P_Bug_Number||' xah, '||
308 l_driver_table||' dr '||
309 ' where dr.event_id = xah.event_id '||
310 ' and dr.process_flag = ''Y'' '||
311 ' ) ';
312
313 EXECUTE IMMEDIATE sql_liab_stat;
314
315 l_debug_info := 'Before backing the dist links';
316 sql_liab_stat := 'insert into distrib_links_'||P_Bug_Number||'('||col_str3||') '||
317 ' select '||col_str3||' from xla_distribution_links '||
318 ' where ae_header_id in '||
319 ' (select xah.ae_header_id '||
320 ' from headers_'||P_Bug_Number||' xah, '||
321 l_driver_table||' dr '||
322 ' where dr.event_id = xah.event_id '||
323 ' and dr.process_flag = ''Y'' '||
324 ' ) ';
325
326 EXECUTE IMMEDIATE sql_liab_stat;
327
328 l_message := 'After creating backup for Accounting tables <p>';
329 FND_File.Put_Line(fnd_file.log,l_message);
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 l_message := 'Exception :: '||SQLERRM||'<p>';
334 FND_File.Put_Line(fnd_file.log,l_message);
335
336 l_message := 'in side '||l_calling_sequence||
337 ' while performing '||l_debug_info||'<p>';
338 FND_File.Put_Line(fnd_file.log,l_message);
339 APP_EXCEPTION.RAISE_EXCEPTION();
340 END Back_Up_Acctg;
341
342 /* Procedure to print messages in the Log file */
343 PROCEDURE Print
344 (p_message IN VARCHAR2,
345 P_calling_sequence IN VARCHAR2) IS
346 l_message varchar2(500);
347 l_calling_sequence varchar2(500);
348 Begin
349 l_calling_sequence:='AP_Acctg_Data_Fix_PKG.print <- '||p_calling_sequence;
350
351 FND_File.Put_Line(fnd_file.log,p_message||'<p>');
352
353 Exception
354 WHEN OTHERS THEN
355 l_message := 'Exception :: '||SQLERRM||'<p>';
356 FND_File.Put_Line(fnd_file.log,l_message);
357
358 l_message := 'in side '||l_calling_sequence||'<p>';
359 FND_File.Put_Line(fnd_file.log,l_message);
360 APP_EXCEPTION.RAISE_EXCEPTION;
361 End Print;
362
363
364 /* Procedure to print the values in the table and column list
365 passed as parameters, in HTML table format, into the Log file. */
366 /* GSI Bug 9490277 : Added parameter p_print_in_output
367 If this is passed as TRUE then table will be printed in output file instead of being printed in log file */
368
369 Procedure Print_Html_Table
370 (p_select_list in VARCHAR2,
371 p_table_in in VARCHAR2,
372 p_where_in in VARCHAR2,
373 P_calling_sequence in VARCHAR2,
374 p_print_in_output IN BOOLEAN /* GSI Bug 9490277 */) IS
375
376 l_calling_sequence varchar2(500);
377 select_list1 varchar2(2000):=P_SELECT_LIST;
378
379 TYPE string_tab IS TABLE OF VARCHAR2(100)
380 INDEX BY BINARY_INTEGER;
381
382 TYPE integer_tab IS TABLE OF NUMBER
383 INDEX BY BINARY_INTEGER;
384
385 colname string_tab;
386 coltype string_tab;
387 collen integer_tab;
388
389 owner_nm VARCHAR2(100) := USER;
390 table_nm VARCHAR2(100) := UPPER (p_table_in);
391 where_clause VARCHAR2(1000) := LTRIM (UPPER (p_where_in));
392
393 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
394 fdbk INTEGER := 0;
395
396 string_value VARCHAR2(2000);
397 number_value NUMBER;
398 date_value DATE;
399
400 dot_loc INTEGER;
401 cur_pos INTEGER:=1;
402
403 col_count INTEGER := 0;
404 col_line LONG;
405 col_list VARCHAR2(2000);
406 l_message VARCHAR2(2000):='<table border="5"><tr>';
407
408 BEGIN
409 l_calling_sequence:='AP_Acctg_Data_Fix_PKG.Print_Html_Table <- '||
410 p_calling_sequence;
411 dot_loc := INSTR (table_nm, '.');
412 IF dot_loc > 0
413 THEN
414 owner_nm := SUBSTR (table_nm, 1, dot_loc-1);
415 table_nm := SUBSTR (table_nm, dot_loc+1);
416 END IF;
417 loop
418 dot_loc := INSTR(select_list1,',');
419
420 IF (DOT_LOC<=0) THEN
421 col_list := col_list || ', ' || select_list1;
422 col_count := col_count + 1;
423 colname (col_count) := select_list1;
424 l_message:=l_message||'<th>'||colname (col_count)||'</th></tr>';
425 ELSE
426 col_list := col_list || ', ' || SUBSTR (select_list1, 1, dot_loc-1);
427 col_count := col_count + 1;
428 colname (col_count) := SUBSTR (select_list1, 1, dot_loc-1);
429 cur_pos:=dot_loc+1;
430 select_list1:=SUBSTR (select_list1, dot_loc+1);
431
432 l_message:=l_message||'<th>'||colname (col_count)||'</th>';
433 end if;
434
435 SELECT data_type,DATA_LENGTH
436 INTO coltype (col_count) ,collen(col_count)
437 FROM all_tab_columns
438 WHERE owner = owner_nm
439 AND table_name = table_nm
440 AND column_name=colname (col_count);
441
442 EXIT WHEN (DOT_LOC<=0);
443
444 end loop;
445 col_list := RTRIM (LTRIM (col_list, ', '), ', ');
446
447 IF p_print_in_output THEN -- Bug 9490277
448 FND_FILE.Put_Line( fnd_file.output, l_message);
449 ELSE
450 print(l_message);
451 END IF ;
452
453 IF where_clause IS NOT NULL
454 THEN
455 IF (where_clause NOT LIKE 'GROUP BY%' AND
456 where_clause NOT LIKE 'ORDER BY%')
457 THEN
458 where_clause :=
459 'WHERE ' || LTRIM (where_clause, 'WHERE');
460 END IF;
461 END IF;
462
463 DBMS_SQL.PARSE
464 (cur,
465 'SELECT ' || col_list ||
466 ' FROM ' || p_table_in || ' ' || where_clause,
467 1);
468
469 FOR col_ind IN 1 .. col_count
470 LOOP
471 IF (coltype(col_ind) IN ('CHAR', 'VARCHAR2'))
472 THEN
473 DBMS_SQL.DEFINE_COLUMN
474 (cur, col_ind, string_value, collen (col_ind));
475 ELSIF (coltype(col_ind) = 'NUMBER')
476 THEN
477 DBMS_SQL.DEFINE_COLUMN (cur, col_ind, number_value);
478
479 ELSIF (coltype(col_ind) = 'DATE')
480 THEN
481 DBMS_SQL.DEFINE_COLUMN (cur, col_ind, date_value);
482 END IF;
483 END LOOP;
484
485 fdbk := DBMS_SQL.EXECUTE (cur);
486 LOOP
487 fdbk := DBMS_SQL.FETCH_ROWS (cur);
488 EXIT WHEN fdbk = 0;
489
490 col_line := NULL;
491 l_message:='<tr>';
492 FOR col_ind IN 1 .. col_count
493 LOOP
494 IF (coltype(col_ind) IN ('CHAR', 'VARCHAR2'))
495 THEN
496
497 DBMS_SQL.COLUMN_VALUE
498 (cur, col_ind, string_value);
499
500 ELSIF (coltype(col_ind) = 'NUMBER')
501 THEN
502
503 DBMS_SQL.COLUMN_VALUE
504 (cur, col_ind, number_value);
505 string_value := TO_CHAR (number_value);
506
507 ELSIF (coltype(col_ind) = 'DATE')
508 THEN
509
510 DBMS_SQL.COLUMN_VALUE
511 (cur, col_ind, date_value);
512 string_value := date_value;
513 END IF;
514
515 col_line :=
516 col_line || ' ' ||
517 RPAD (NVL (string_value, ' '), collen (col_ind));
518 l_message:=l_message||'<td>'||NVL (string_value, ' ')||'</td>';
519
520 END LOOP;
521 l_message:=l_message||'</tr>';
522
523 IF p_print_in_output THEN -- Bug 9490277
524 FND_FILE.Put_Line( fnd_file.output, l_message);
525 ELSE
526 Print(l_message);
527 END IF ;
528
529 END LOOP;
530 IF p_print_in_output THEN -- Bug 9490277
531 FND_FILE.Put_Line( fnd_file.output, '</table>');
532 FND_FILE.Put_Line( fnd_file.output, '<p>');
533 ELSE
534 print('</table>');
535 END IF ;
536
537 Exception
538 WHEN OTHERS THEN
539 l_message := 'SELECT ' || col_list ||
540 ' FROM ' || p_table_in || ' ' || where_clause||'<p>';
541 FND_File.Put_Line(fnd_file.log,l_message);
542
543 l_message := 'Exception :: '||SQLERRM||'<p>';
544 FND_File.Put_Line(fnd_file.log,l_message);
545
546 l_message := 'in side '||l_calling_sequence||'<p>';
547 FND_File.Put_Line(fnd_file.log,l_message);
548 APP_EXCEPTION.RAISE_EXCEPTION;
549 END Print_Html_Table;
550
551 /* Procedure to backup the data from the source table to destination
552 table. It also takes in as input SELECT LIST which determine
553 the list of columns which will be backed up. The additional
554 WHERE caluse can also be passed in as input. */
555
556 Procedure Backup_data
557 (p_source_table in VARCHAR2,
558 p_destination_table in VARCHAR2,
559 p_select_list in VARCHAR2,
560 p_where_clause in VARCHAR2,
561 P_calling_sequence in VARCHAR2) is
562
563 l_calling_sequence varchar2(4000);
564 l_message LONG;
565 TYPE sqlCurTyp IS REF CURSOR;
566 cur sqlCurTyp;
567 l_tables varchar2(100) := 'ALL_TABLES';
568 l_bkp_tables_exists number:=0;
569 sql_stmt LONG;
570 col_str1 LONG;
571 l_sql_stmt LONG;
572 --bug12833171
573 l_col_name VARCHAR2(35);
574 l_col_type VARCHAR2(35);
575
576 Begin
577 l_calling_sequence:='AP_Acctg_Data_Fix_PKG.Backup_data <- '||
578 p_calling_sequence;
579
580 sql_stmt := 'select count(*) from '|| l_tables ||
581 ' where table_name='||''''||p_destination_table||'''';
582
583 OPEN cur FOR sql_stmt;
584 FETCH cur into l_bkp_tables_exists;
585 CLOSE cur;
586
587 if (l_bkp_tables_exists=0) then
588 Print('Before creating backup table '||p_destination_table);
589 l_sql_stmt :=
590 'Create table '||p_destination_table||
591 ' as select '||p_select_list||' from '||
592 p_source_table||' where rownum<1 ';
593
594 execute immediate l_sql_stmt;
595
596 Execute Immediate
597 'alter table '||p_destination_table||
598 ' add datafix_update_date date default sysdate';
599 Print('Created table '||p_destination_table);
600
601 end if;
602
603 --bug12833171
604 sql_stmt := 'SELECT column_name,
605 data_type|| decode(data_type, ''DATE'', '''', ''(''||data_length||''''||nvl2(data_precision, '',''||data_precision||'')'', '')'')) column_type
606 FROM all_tab_columns
607 WHERE table_name IN ('''||p_source_table||''')
608 MINUS
609 SELECT column_name,
610 data_type|| decode(data_type, ''DATE'', '''', ''(''||data_length||''''||nvl2(data_precision, '',''||data_precision||'')'', '')'')) column_type
611 FROM all_tab_columns
612 WHERE table_name IN ('''||p_destination_table||''')';
613
614 OPEN cur FOR sql_stmt;
615 LOOP
616 FETCH cur INTO l_col_name, l_col_type;
617 EXIT WHEN cur%NOTFOUND;
618
619 BEGIN
620 sql_stmt := 'alter table '||p_destination_table||' add '||l_col_name||' '||l_col_type;
621 EXECUTE IMMEDIATE sql_stmt;
622 EXCEPTION
623 WHEN OTHERS THEN
624 IF SQLCODE <> -1430 THEN
625 print('Exception while trying to add column: '||l_col_name||' '||sqlerrm);
626 print('query : '||sql_stmt);
627 END IF;
628 END;
629 END LOOP;
630
631 sql_stmt := 'insert into '||p_destination_table||
632 '('||P_SELECT_LIST||') '||' select '||P_SELECT_LIST||
633 ' from '||P_SOURCE_TABLE||' '||P_WHERE_CLAUSE;
634 l_message:=sql_stmt;
635 EXECUTE IMMEDIATE sql_stmt;
636
637 Exception
638 WHEN OTHERS THEN
639 l_message := 'Exception :: '||SQLERRM;
640 print(l_message);
641
642 l_message := 'in side '||l_calling_sequence;
643 print(l_message);
644 APP_EXCEPTION.RAISE_EXCEPTION;
645 End Backup_data;
646
647 PROCEDURE apps_initialize
648 (p_user_name IN FND_USER.USER_NAME%TYPE,
649 p_resp_name IN FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE,
650 p_calling_sequence IN VARCHAR2) IS
651
652 l_user_id NUMBER;
653 l_resp_id NUMBER;
654 l_application_id NUMBER := 200;
655 l_debug_info VARCHAR2(4000);
656 l_error_log LONG;
657 l_calling_sequence VARCHAR2(4000);
658 BEGIN
659
660 l_calling_sequence := 'AP_ACCTG_DATA_FIX_PKG.apps_initialize <-'||p_calling_sequence;
661
662 l_debug_info := 'Before fetching the User Details ';
663 BEGIN
664 SELECT fu.user_id
665 INTO l_user_id
666 FROM fnd_user fu
667 WHERE fu.user_name = p_user_name;
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 print('User '||p_user_name||' Not Found');
672 APP_EXCEPTION.RAISE_EXCEPTION();
673 END;
674
675 l_debug_info := 'Before fetching the responsibility details';
676 BEGIN
677 SELECT fr.responsibility_id
678 INTO l_resp_id
679 FROM fnd_responsibility_tl fr
680 WHERE fr.responsibility_name = p_resp_name
681 AND rownum = 1;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 print('Responsibility '||p_resp_name||' Not Found');
686 APP_EXCEPTION.RAISE_EXCEPTION();
687
688 END;
689
690 l_debug_info := 'Before Initializing the Application';
691 FND_GLOBAL.apps_initialize
692 (l_user_id,
693 l_resp_id,
694 l_application_id);
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 IF SQLCODE <> -20001 THEN
699 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
700 ' in '||l_calling_sequence||' while performing '||l_debug_info;
701 Print(l_error_log);
702 END IF;
703 APP_EXCEPTION.RAISE_EXCEPTION();
704 END;
705
706
707 PROCEDURE Del_Nonfinal_xla_entries
708 (p_event_id IN NUMBER,
709 p_delete_event IN VARCHAR2,
710 p_commit_flag IN VARCHAR2,
711 p_calling_sequence IN VARCHAR2) IS
712
713 l_event_status_code XLA_EVENTS.EVENT_STATUS_CODE%TYPE;
714 l_gl_transfer_status_code XLA_AE_HEADERS.GL_TRANSFER_STATUS_CODE%TYPE;
715 l_debug_info VARCHAR2(4000);
716 l_error_log LONG;
717 l_calling_sequence VARCHAR2(4000);
718
719 /* Bug 11674553 */
720 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
721 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
722
723 l_legal_entity_id NUMBER(15);
724 l_ledger_id NUMBER(15);
725 l_transaction_date AP_INVOICES_ALL.invoice_date%TYPE;
726 l_transation_id number(20);
727 l_entity_code varchar2(20);
728
729 l_api_Version NUMBER;
730 l_InIt_msg_List VARCHAR2(300);
731 l_Application_Id INTEGER;
732 l_Return_Status VARCHAR2(300);
733 x_msg_Count NUMBER;
734 x_msg_Data VARCHAR2(4000);
735
736 l_journals_exist VARCHAR2(1);
737 l_result NUMBER(10);
738 l_accounting_event_id NUMBER(10);
739 l_entity_id NUMBER(20);
740 l_orgid NUMBER(10);
741 l_dummy NUMBER;
742
743 l_procedure_name CONSTANT VARCHAR2(30) := 'del_nonfinal_xla_entries';
744 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
745
746
747 BEGIN
748 l_calling_sequence := 'AP_ACCTG_DATA_FIX_PKG.Del_Nonfinal_xla_entries <- '||p_calling_sequence;
749
750 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
751
752 l_log_msg := 'Begin of procedure '|| l_procedure_name;
753 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
754 FND_LOG.STRING(G_LEVEL_PROCEDURE,
755 G_MODULE_NAME||l_procedure_name||'.begin',
756 l_log_msg);
757 END IF;
758
759 l_log_msg := 'p_event_id: '||p_event_id ;
760 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
761 FND_LOG.STRING(G_LEVEL_PROCEDURE,
762 G_MODULE_NAME||l_procedure_name,
763 l_log_msg);
764 END IF;
765
766 l_log_msg := 'p_delete_event '||p_delete_event ;
767 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
768 FND_LOG.STRING(G_LEVEL_PROCEDURE,
769 G_MODULE_NAME||l_procedure_name,
770 l_log_msg);
771 END IF;
772
773
774 l_debug_info := 'Check if the event exists';
775 BEGIN
776
777 l_dummy := 0;
778
779 SELECT 1
780 INTO l_dummy
781 FROM dual
782 WHERE EXISTS
783 (SELECT 1
784 FROM xla_events xe
785 WHERE xe.application_id = 200
786 AND xe.event_id = p_event_id);
787
788 EXCEPTION
789 WHEN OTHERS THEN
790 l_dummy := 0;
791 END;
792
793 IF l_dummy = 0 THEN
794
795 l_log_msg := 'Event_ID '||p_event_ID||' does not exist, returning';
796 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
797 FND_LOG.STRING(G_LEVEL_PROCEDURE,
798 G_MODULE_NAME||l_procedure_name,
799 l_log_msg);
800 END IF;
801
802 RETURN;
803 END IF;
804
805 l_debug_info := 'Initializing the Variables';
806
807 l_event_status_code := 'U';
808 l_gl_transfer_status_code := 'N';
809 l_api_Version := 1.0;
810 l_InIt_msg_List := fnd_aPi.g_True;
811 l_Application_Id := 200;
812 l_journals_exist := 'N';
813 l_Return_Status := 'S';
814
815 BEGIN
816 l_debug_info := 'Fetching event_status_code..';
817 SELECT xe.event_status_code,
818 xe.entity_id
819 INTO l_event_status_code,
820 l_entity_id
821 FROM xla_events xe
822 WHERE xe.application_id = 200
823 AND xe.event_id = p_event_id;
824
825 SELECT xah.gl_transfer_status_code
826 INTO l_gl_transfer_status_code
827 FROM xla_ae_headers xah
828 WHERE xah.application_id = 200
829 AND xah.gl_transfer_status_code = 'Y'
830 AND xah.event_id = p_event_id
831 AND rownum = 1;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 NULL;
836 END;
837
838 IF l_event_status_code = 'P' OR
839 l_gl_transfer_status_code = 'Y' THEN
840
841 l_log_msg := 'The event is processed or has a header which has been '||
842 'transferred to GL, returning ';
843 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
844 FND_LOG.STRING(G_LEVEL_PROCEDURE,
845 G_MODULE_NAME||l_procedure_name,
846 l_log_msg);
847 END IF;
848
849
850 RETURN;
851 END IF;
852
853 /* Bug 11674553
854 Code changes are done in ap_acctg_data_fix_pkg.del_nonfinal_xla_entries procedure
855 for deleting the journals, event and entity by calling api instead of directly
856 deleting from xla tables.
857
858 While calling delete entity, if l_result is 0 then entity deleted
859 else entity not deleted as there are events associated to the entity
860 */
861
862 BEGIN
863 l_debug_info := ' Fetching security context..';
864 SELECT security_id_int_1,
865 legal_entity_id,
866 ledger_id,
867 entity_code,
868 source_id_int_1,
869 transaction_number,
870 application_id
871 INTO l_event_security_context.security_id_int_1,
872 l_event_source_info.legal_entity_id,
873 l_event_source_info.ledger_id,
874 l_event_source_info.entity_type_code,
875 l_event_source_info.source_id_int_1,
876 l_event_source_info.transaction_number,
877 l_event_source_info.application_id
878 FROM xla_transaction_entities_upg xte
879 WHERE xte.application_id = 200
880 AND xte.entity_id = l_entity_id;
881
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 l_error_log := ' No entity exists for the given entity_id '||l_entity_id||
885 ' Exception in fetching security context';
886 Print(l_error_log);
887
888 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
889 FND_LOG.STRING(G_LEVEL_PROCEDURE,
890 G_MODULE_NAME||l_procedure_name,
891 l_error_log);
892 END IF;
893
894
895 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
896 ' in '||l_calling_sequence||' while performing '||l_debug_info;
897 Print(l_error_log);
898 RAISE;
899
900 WHEN OTHERS THEN
901
902 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
903 ' in '||l_calling_sequence||' while performing '||l_debug_info;
904 Print(l_error_log);
905
906
907 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
908 FND_LOG.STRING(G_LEVEL_PROCEDURE,
909 G_MODULE_NAME||l_procedure_name,
910 l_error_log);
911 END IF;
912
913 RAISE;
914 END;
915
916 l_debug_info := 'Setting org context..';
917 l_orgid := l_event_security_context.security_id_int_1;
918 MO_GLOBAL.SET_POLICY_CONTEXT('S', l_orgid);
919
920 BEGIN
921
922 l_debug_info := 'Checking if journals exist..';
923
924 SELECT 'Y'
925 INTO l_journals_exist
926 FROM dual
927 WHERE EXISTS
928 (SELECT 'JOURNALS EXISTS'
929 FROM xla_ae_headers
930 WHERE application_id =200
931 AND event_id = p_event_id);
932 EXCEPTION
933
934 WHEN NO_DATA_FOUND THEN
935 l_journals_exist := 'N';
936
937 END;
938
939 l_log_msg := 'l_journals_exist: '||l_journals_exist;
940 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
941 FND_LOG.STRING(G_LEVEL_PROCEDURE,
942 G_MODULE_NAME||l_procedure_name,
943 l_log_msg);
944 END IF;
945
946
947
948 IF (l_journals_exist ='Y') THEN
949 l_debug_info := 'Deleting data from xla tables..';
950
951 xla_datafixes_pub.delete_journal_entries
952 (p_api_version => l_api_Version,
953 p_init_msg_list => l_InIt_msg_List,
954 p_application_id => l_Application_Id,
955 p_event_id => p_event_id,
956 x_return_status => l_Return_Status,
957 x_msg_count => x_msg_Count,
958 x_msg_data => x_msg_Data);
959
960 IF (x_msg_Count > 0 OR l_Return_Status = 'U') THEN
961 l_error_log := 'Error in xla_DataFixes_Pub.delete_journal_entries:'|| x_msg_Data;
962 Print(l_error_log);
963
964 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
965 FND_LOG.STRING(G_LEVEL_PROCEDURE,
966 G_MODULE_NAME||l_procedure_name,
967 l_error_log);
968 END IF;
969
970 APP_EXCEPTION.RAISE_EXCEPTION();
971 END IF;
972 ELSE
973 l_Return_Status := 'S';
974 END IF;
975
976 IF (p_delete_event = 'Y' and l_Return_Status = 'S' ) THEN
977
978 BEGIN
979 l_debug_info := ' Calling delete event..';
980 AP_XLA_EVENTS_PKG.delete_event
981 ( p_event_source_info => l_event_source_info,
982 p_event_id => p_event_id,
983 p_valuation_method => NULL,
984 p_security_context => l_event_security_context,
985 p_calling_sequence => l_calling_sequence
986 );
987 EXCEPTION
988 WHEN OTHERS THEN
989 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
990 ' in '||l_calling_sequence||' while performing '||l_debug_info;
991 Print(l_error_log);
992
993
994 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
995 FND_LOG.STRING(G_LEVEL_PROCEDURE,
996 G_MODULE_NAME||l_procedure_name,
997 l_error_log);
998 END IF;
999 RAISE;
1000 END;
1001
1002
1003 l_debug_info := 'Checking if entity exists';
1004 BEGIN
1005
1006 l_dummy := 0;
1007
1008 SELECT 1
1009 INTO l_dummy
1010 FROM dual
1011 WHERE EXISTS
1012 (SELECT 1
1013 FROM xla_transaction_entities_upg xte
1014 WHERE xte.application_id = 200
1015 AND xte.entity_id = l_entity_id);
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 l_dummy := 0;
1020 END;
1021
1022 IF l_dummy = 1 THEN
1023
1024 l_debug_info := 'Calling delete entity..';
1025
1026 BEGIN
1027 l_result := xla_events_pub_pkg.delete_entity (
1028 p_source_info => l_event_source_info,
1029 p_valuation_method => NULL,
1030 p_security_context => l_event_security_context );
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 NULL;
1034 END;
1035
1036 END IF;
1037
1038 END IF;
1039
1040 IF p_commit_flag = 'Y' THEN
1041 COMMIT;
1042 END IF;
1043
1044 l_log_msg := 'End of procedure '|| l_procedure_name;
1045 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1046 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1047 G_MODULE_NAME||l_procedure_name||'.end',
1048 l_log_msg);
1049 END IF;
1050
1051
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
1055 ' in '||l_calling_sequence||' while performing '||l_debug_info;
1056 Print(l_error_log);
1057
1058 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1059 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1060 G_MODULE_NAME||l_procedure_name,
1061 l_error_log);
1062 END IF;
1063
1064 APP_EXCEPTION.RAISE_EXCEPTION();
1065 END;
1066
1067
1068 -- bug9342663, modified the procedure to check for the
1069 -- closing status of AP periods only when it belongs
1070 -- to the Primary Ledger, since AP does not have any
1071 -- UI or controls for the secondary or ALC ledger
1072 -- periods, nor are they used in any AP flow
1073 --
1074
1075 PROCEDURE check_period
1076 (p_bug_no IN NUMBER,
1077 p_driver_table IN VARCHAR2,
1078 p_check_event_date IN VARCHAR2 DEFAULT 'Y',
1079 p_check_sysdate IN VARCHAR2 DEFAULT 'N',
1080 p_chk_proposed_undo_date IN VARCHAR2 DEFAULT 'N',
1081 p_update_process_flag IN VARCHAR2,
1082 P_calc_undo_date IN VARCHAR2,
1083 P_commit_flag IN VARCHAR2 DEFAULT 'N',
1084 p_calling_sequence IN VARCHAR2) IS
1085
1086 l_sql_stmt LONG;
1087 l_bug_no VARCHAR2(100);
1088 l_driver_table ALL_TABLES.TABLE_NAME%TYPE;
1089 l_debug_info VARCHAR2(4000);
1090 l_error_log LONG;
1091 l_date_string VARCHAR2(100);
1092 l_dummy NUMBER;
1093 l_check_process_flag VARCHAR2(1) := 'N';
1094 l_check_proposed_col VARCHAR2(1) := 'N';
1095 l_update_process_flag VARCHAR2(1);
1096 l_message VARCHAR2(4000);
1097 l_calling_sequence VARCHAR2(4000);
1098
1099 TYPE refcurtyp IS REF CURSOR;
1100 closed_period_trx REFCURTYP;
1101
1102 TYPE event_id_t IS TABLE OF XLA_AE_HEADERS.EVENT_ID%TYPE INDEX BY BINARY_INTEGER;
1103 TYPE event_type_code_t IS TABLE OF XLA_AE_HEADERS.EVENT_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
1104 TYPE ae_header_id_t IS TABLE OF XLA_AE_HEADERS.AE_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
1105 TYPE accounting_date_t IS TABLE OF XLA_AE_HEADERS.ACCOUNTING_DATE%TYPE INDEX BY BINARY_INTEGER;
1106 TYPE source_type_t IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
1107 TYPE source_id_int_1_t IS TABLE OF XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1%TYPE INDEX BY BINARY_INTEGER;
1108 TYPE transaction_number_t IS TABLE OF XLA_TRANSACTION_ENTITIES.TRANSACTION_NUMBER%TYPE INDEX BY BINARY_INTEGER;
1109 TYPE security_id_int_1_t IS TABLE OF XLA_TRANSACTION_ENTITIES.SECURITY_ID_INT_1%TYPE INDEX BY BINARY_INTEGER;
1110 TYPE period_name_t IS TABLE OF GL_PERIOD_STATUSES.PERIOD_NAME%TYPE INDEX BY BINARY_INTEGER;
1111 TYPE closing_status_t IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
1112 TYPE ledger_name_t IS TABLE OF GL_LEDGERS.NAME%TYPE INDEX BY BINARY_INTEGER;
1113
1114 TYPE period_close_rec_typ IS RECORD
1115 (event_id_l event_id_t,
1116 event_type_code_l event_type_code_t,
1117 ae_header_id_l ae_header_id_t,
1118 accounting_date_l accounting_date_t,
1119 source_type_l source_type_t,
1120 source_id_int_1_l source_id_int_1_t,
1121 transaction_number_l transaction_number_t,
1122 security_id_int_1_l security_id_int_1_t,
1123 period_name_l period_name_t,
1124 closing_status_l closing_status_t,
1125 ledger_name_l ledger_name_t);
1126
1127 period_close_list PERIOD_CLOSE_REC_TYP;
1128
1129 BEGIN
1130
1131 l_calling_sequence := 'AP_ACCTG_DATA_FIX_PKG.Check_Period <- '||p_calling_sequence;
1132 l_bug_no := p_bug_no;
1133
1134 l_debug_info := 'Constructing the name of the driver table';
1135 IF p_driver_table IS NOT NULL THEN
1136 l_driver_table := upper(p_driver_table);
1137 ELSE
1138 l_driver_table := 'AP_TEMP_DATA_DRIVER_'||l_bug_no;
1139 END IF;
1140
1141 l_debug_info := 'Verifying the input parameters for the API, wrt dates';
1142 IF nvl(p_check_sysdate, 'N') = 'N' AND
1143 nvl(p_check_event_date, 'N') = 'N' AND
1144 nvl(p_chk_proposed_undo_date, 'N') = 'N' THEN
1145 l_error_log := ' Period check needs to be performed either on event_date '||
1146 ' or for a specific date ';
1147 Print(l_error_log);
1148 APP_EXCEPTION.RAISE_EXCEPTION();
1149 END IF;
1150
1151 l_debug_info := 'Checking for the presence of the event_id column in the '||
1152 'driver ';
1153 BEGIN
1154 SELECT 1
1155 INTO l_dummy
1156 FROM sys.all_tab_columns
1157 WHERE table_name = l_driver_table
1158 AND column_name = 'EVENT_ID';
1159
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 l_error_log := 'Could not find the column event_id in the driver table '||
1163 l_driver_table||' : aborting';
1164 Print(l_error_log);
1165 APP_EXCEPTION.RAISE_EXCEPTION();
1166 END;
1167
1168 l_debug_info := 'Check for the presence of the process_flag on the driver table ';
1169 BEGIN
1170 SELECT 'Y'
1171 INTO l_check_process_flag
1172 FROM sys.all_tab_columns
1173 WHERE table_name = l_driver_table
1174 AND column_name = 'PROCESS_FLAG';
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 l_debug_info := 'The column process_flag is not found in the table';
1179 l_check_process_flag := 'N';
1180 END;
1181
1182 l_debug_info := 'The column process flag does not exist, proceeding to add it for '||
1183 'further reporting and calculations ';
1184 IF l_check_process_flag <> 'Y' THEN
1185
1186 l_sql_stmt := 'ALTER TABLE '||l_driver_table||' ADD (process_flag VARCHAR2(1) DEFAULT ''Y'') ';
1187 EXECUTE IMMEDIATE l_sql_stmt;
1188
1189 END IF;
1190
1191 l_debug_info := 'If the api has been called to calculate the Undo Date, process_flag '||
1192 'must get updated';
1193 l_update_process_flag := p_update_process_flag;
1194 IF P_calc_undo_date = 'Y' THEN
1195 l_update_process_flag := 'Y';
1196 END IF;
1197
1198 l_debug_info := 'Checking the events having a user proposed undo date';
1199 BEGIN
1200 SELECT 'Y'
1201 INTO l_check_proposed_col
1202 FROM sys.all_tab_columns
1203 WHERE table_name = l_driver_table
1204 AND column_name = 'PROPOSED_UNDO_DATE';
1205
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 l_check_proposed_col := 'N';
1209 END;
1210
1211 l_debug_info := 'Proceeding to display all of the transactions which have undo '||
1212 'Date entered by the user which is in a closed period ';
1213 IF p_chk_proposed_undo_date = 'Y' AND l_check_proposed_col = 'Y' THEN
1214 l_debug_info := 'Constructing the dynamic sql for checking the undo date';
1215 l_sql_stmt :=
1216 ' SELECT DISTINCT '||
1217 ' xah.event_id, '||
1218 ' xah.event_type_code, '||
1219 ' xah.ae_header_id, '||
1220 ' xah.accounting_date, '||
1221 ' DECODE(xte.entity_code, '||
1222 ' ''AP_INVOICES'', ''Invoice'', '||
1223 ' ''AP_PAYMENTS'', ''Payments''), '||
1224 ' xte.source_id_int_1, '||
1225 ' xte.transaction_number, '||
1226 ' xte.security_id_int_1, '||
1227 ' glps.period_name, '||
1228 ' DECODE(glps.closing_status, '||
1229 ' ''C'', ''Closed'', '||
1230 ' ''N'', ''Never Opened'', '||
1231 ' ''Not-Open''), '||
1232 ' gl.name '||
1233 ' FROM xla_events xe, '||
1234 ' xla_ae_headers xah, '||
1235 l_driver_table||' dr, '||
1236 ' xla_transaction_entities_upg xte, '||
1237 ' gl_period_statuses glps, '||
1238 ' gl_ledgers gl '||
1239 ' WHERE xe.application_id = 200 '||
1240 ' AND xah.application_id =200 '||
1241 ' AND xte.application_id =200 '||
1242 ' AND xe.event_id = dr.event_id '||
1243 ' AND xe.entity_id = xte.entity_id '||
1244 ' AND xe.event_id = xah.event_id '||
1245 ' AND xah.entity_id = xte.entity_id '||
1246 ' AND xe.event_status_code = ''P'' '||
1247 ' AND xah.accounting_entry_status_code = ''F'' '||
1248 ' AND xah.event_type_code <> ''MANUAL'' '||
1249 ' AND (glps.application_id = 101 OR '||
1250 ' (glps.application_id = 200 AND '||
1251 ' xah.ledger_id = xte.ledger_id)) '||
1252 ' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
1253 ' AND glps.set_of_books_id = xah.ledger_id '||
1254 ' AND glps.closing_status NOT IN (''O'',''F'') '||
1255 ' AND dr.proposed_undo_date IS NOT NULL '||
1256 ' AND dr.process_flag = ''Y'' '|| /*Bug 9727543*/
1257 ' AND dr.proposed_undo_date BETWEEN glps.start_date AND glps.end_date '||
1258 ' AND xah.ledger_id = gl.ledger_id ';
1259
1260 l_message := '<b><u>Following Events would not be Un-Accounted because the '||
1261 'Date provided by the User is in a closed Period</u></b>';
1262 Print(l_message);
1263
1264 l_message := '<table border="5">'||
1265 '<th>EVENT_ID</th>'||
1266 '<th>EVENT_TYPE_CODE</th>'||
1267 '<th>AE_HEADER_ID</th>'||
1268 '<th>ACCOUNTING_DATE</th>'||
1269 '<th>SOURCE_TYPE</th>'||
1270 '<th>SOURCE_ID_INT_1</th>'||
1271 '<th>TRANSACTION_NUMBER</th>'||
1272 '<th>SECURITY_ID_INT_1</th>'||
1273 '<th>PROPOSED_PERIOD_NAME</th>'||
1274 '<th>CLOSING_STATUS</th>'||
1275 '<th>LEDGER_NAME</th></tr>';
1276
1277 print(l_message);
1278
1279 OPEN closed_period_trx FOR l_sql_stmt;
1280 LOOP
1281 FETCH closed_period_trx
1282 BULK COLLECT INTO period_close_list.event_id_l,
1283 period_close_list.event_type_code_l,
1284 period_close_list.ae_header_id_l,
1285 period_close_list.accounting_date_l,
1286 period_close_list.source_type_l,
1287 period_close_list.source_id_int_1_l,
1288 period_close_list.transaction_number_l,
1289 period_close_list.security_id_int_1_l,
1290 period_close_list.period_name_l,
1291 period_close_list.closing_status_l,
1292 period_close_list.ledger_name_l LIMIT 1000;
1293
1294 IF period_close_list.event_id_l.COUNT > 0 THEN
1295 FOR i IN period_close_list.event_id_l.FIRST..period_close_list.event_id_l.LAST LOOP
1296 l_message :=
1297 '<tr><td>'||
1298 to_char(period_close_list.event_id_l(i))||'</td><td>'||
1299 period_close_list.event_type_code_l(i)||'</td><td>'||
1300 to_char(period_close_list.ae_header_id_l(i))||'</td><td>'||
1301 to_char(period_close_list.accounting_date_l(i), 'DD-MON-YYYY')||'</td><td>'||
1302 period_close_list.source_type_l(i)||'</td><td>'||
1303 to_char(period_close_list.source_id_int_1_l(i))||'</td><td>'||
1304 period_close_list.transaction_number_l(i)||'</td><td>'||
1305 to_char(period_close_list.security_id_int_1_l(i))||'</td><td>'||
1306 period_close_list.period_name_l(i)||'</td><td>'||
1307 period_close_list.closing_status_l(i)||'</td><td>'||
1308 period_close_list.ledger_name_l(i)||'</td></tr>';
1309
1310 print(l_message);
1311 END LOOP;
1312 END IF;
1313
1314 IF (l_update_process_flag = 'Y' AND period_close_list.event_id_l.COUNT > 0) THEN
1315
1316 l_debug_info := 'The column process_flag has been found, proceeding to update';
1317 FOR i IN period_close_list.event_id_l.FIRST..period_close_list.event_id_l.LAST LOOP
1318 l_sql_stmt :=
1319 ' UPDATE '||l_driver_table||
1320 ' SET process_flag = ''E'' '|| /*Bug 9727543*/
1321 ' WHERE event_id = '||period_close_list.event_id_l(i);
1322
1323 l_debug_info := 'Proceeding to update the process flag for the event '||
1324 period_close_list.event_id_l(i);
1325 EXECUTE IMMEDIATE l_sql_stmt;
1326 END LOOP;
1327
1328 END IF;
1329
1330 EXIT WHEN closed_period_trx%NOTFOUND;
1331 END LOOP;
1332
1333 l_message := '</table>';
1334 print(l_message);
1335 END IF;
1336
1337 -- Find out all the events for which there exists at least one of the secondary
1338 -- ledgers, for which the period is not OPEN in AP or GL for the event
1339 -- date AND for the parameter date, depending on options passed:
1340
1341 IF (nvl(p_check_sysdate, 'N') <> 'N' OR
1342 nvl(p_check_event_date, 'N') <> 'N') THEN
1343
1344 IF p_check_sysdate = 'Y' THEN
1345 l_date_string := ' trunc(sysdate) ';
1346 ELSE
1347 l_date_string := ' XE.event_date ';
1348 END IF;
1349
1350 l_debug_info := 'Pos 2 Constructing the statement for fetching the period info';
1351 l_sql_stmt :=
1352 ' SELECT DISTINCT '||
1353 ' xah.event_id, '||
1354 ' xah.event_type_code, '||
1355 ' xah.ae_header_id, '||
1356 ' xah.accounting_date, '||
1357 ' DECODE(xte.entity_code, '||
1358 ' ''AP_INVOICES'', ''Invoice'', '||
1359 ' ''AP_PAYMENTS'', ''Payments''), '||
1360 ' xte.source_id_int_1, '||
1361 ' xte.transaction_number, '||
1362 ' xte.security_id_int_1, '||
1363 ' glps.period_name, '||
1364 ' DECODE(glps.closing_status, '||
1365 ' ''C'', ''Closed'', '||
1366 ' ''N'', ''Never Opened'', '||
1367 ' ''Not-Open''), '||
1368 ' gl.name '||
1369 ' FROM xla_events xe, '||
1370 ' xla_ae_headers xah, '||
1371 l_driver_table||' dr, '||
1372 ' xla_transaction_entities_upg xte, '||
1373 ' gl_period_statuses glps, '||
1374 ' gl_ledgers gl '||
1375 ' WHERE xe.application_id = 200 '||
1376 ' AND xah.application_id =200 '||
1377 ' AND xte.application_id =200 '||
1378 ' AND xe.event_id = dr.event_id '||
1379 ' AND dr.process_flag = ''Y'' '|| /*Bug 9727543*/
1380 ' AND xe.entity_id = xte.entity_id '||
1381 ' AND xe.event_id = xah.event_id '||
1382 ' AND xah.entity_id = xte.entity_id '||
1383 ' AND xe.event_status_code = ''P'' '||
1384 ' AND xah.accounting_entry_status_code = ''F'' '||
1385 ' AND xah.event_type_code <> ''MANUAL'' '||
1386 ' AND (glps.application_id = 101 OR '||
1387 ' (glps.application_id = 200 AND '||
1388 ' xah.ledger_id = xte.ledger_id)) '||
1389 ' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
1390 ' AND glps.set_of_books_id = xah.ledger_id '||
1391 ' AND glps.closing_status NOT IN (''O'',''F'') '||
1392 ' AND xah.ledger_id = gl.ledger_id '||
1393 ' AND '||l_date_string||' BETWEEN glps.start_date AND glps.end_date ';
1394
1395 IF p_chk_proposed_undo_date = 'Y' AND l_check_proposed_col = 'Y' THEN
1396 l_sql_stmt := l_sql_stmt||
1397 ' AND dr.proposed_undo_date IS NULL ';
1398 END IF;
1399
1400 IF p_check_sysdate = 'Y' AND p_check_event_date = 'Y' THEN
1401 l_sql_stmt := l_sql_stmt||
1402 ' AND EXISTS '||
1403 ' (SELECT 1 '||
1404 ' FROM gl_period_statuses glpse, '||
1405 ' xla_ae_headers xahe '||
1406 ' WHERE xahe.application_id = 200 '||
1407 ' AND (glpse.application_id = 101 OR '||
1408 ' (glpse.application_id = 200 AND '||
1409 ' xahe.ledger_id = xte.ledger_id)) '||
1410 ' AND xahe.event_id = xe.event_id '||
1411 ' AND nvl(glpse.adjustment_period_flag, ''N'') = ''N'' '||
1412 ' AND glpse.set_of_books_id = xahe.ledger_id '||
1413 ' AND glpse.closing_status NOT IN (''O'',''F'') '||
1414 ' AND xe.event_date BETWEEN glpse.start_date AND glpse.end_date) ';
1415 END IF;
1416
1417 --Print(l_sql_stmt);
1418
1419 l_message := '<b><u>The Following events cannot be Unaccounted';
1420 IF p_check_event_date = 'Y' AND nvl(p_check_sysdate, 'N') = 'N' THEN
1421 l_message := l_message||' On the same Date as the Original Event Date because the Original Period is Closed</u></b> ';
1422 ELSIF nvl(p_check_event_date, 'N') = 'N' AND p_check_sysdate = 'Y' THEN
1423 l_message := l_message||' On the SYSDATE because the current Period is Closed</u></b>';
1424 ELSIF p_check_event_date = 'Y' AND p_check_sysdate = 'Y' THEN
1425 l_message := l_message||' On the same Date as the Original Event Date or the Sysdate because both Original '||
1426 ' and Current Periods are Closed</u></b>';
1427 END IF;
1428
1429 Print(l_message);
1430
1431 l_message := '<table border="5">'||
1432 '<th>EVENT_ID</th>'||
1433 '<th>EVENT_TYPE_CODE</th>'||
1434 '<th>AE_HEADER_ID</th>'||
1435 '<th>ACCOUNTING_DATE</th>'||
1436 '<th>SOURCE_TYPE</th>'||
1437 '<th>SOURCE_ID_INT_1</th>'||
1438 '<th>TRANSACTION_NUMBER</th>'||
1439 '<th>SECURITY_ID_INT_1</th>'||
1440 '<th>PERIOD_NAME</th>'||
1441 '<th>CLOSING_STATUS</th>'||
1442 '<th>LEDGER_NAME</th></tr>';
1443
1444 print(l_message);
1445
1446 OPEN closed_period_trx FOR l_sql_stmt;
1447 LOOP
1448 FETCH closed_period_trx
1449 BULK COLLECT INTO period_close_list.event_id_l,
1450 period_close_list.event_type_code_l,
1451 period_close_list.ae_header_id_l,
1452 period_close_list.accounting_date_l,
1453 period_close_list.source_type_l,
1454 period_close_list.source_id_int_1_l,
1455 period_close_list.transaction_number_l,
1456 period_close_list.security_id_int_1_l,
1457 period_close_list.period_name_l,
1458 period_close_list.closing_status_l,
1459 period_close_list.ledger_name_l LIMIT 1000;
1460
1461 IF period_close_list.event_id_l.COUNT > 0 THEN
1462 FOR i IN period_close_list.event_id_l.FIRST..period_close_list.event_id_l.LAST LOOP
1463 l_message :=
1464 '<tr><td>'||
1465 to_char(period_close_list.event_id_l(i))||'</td><td>'||
1466 period_close_list.event_type_code_l(i)||'</td><td>'||
1467 to_char(period_close_list.ae_header_id_l(i))||'</td><td>'||
1468 to_char(period_close_list.accounting_date_l(i), 'DD-MON-YYYY')||'</td><td>'||
1469 period_close_list.source_type_l(i)||'</td><td>'||
1470 to_char(period_close_list.source_id_int_1_l(i))||'</td><td>'||
1471 period_close_list.transaction_number_l(i)||'</td><td>'||
1472 to_char(period_close_list.security_id_int_1_l(i))||'</td><td>'||
1473 period_close_list.period_name_l(i)||'</td><td>'||
1474 period_close_list.closing_status_l(i)||'</td><td>'||
1475 period_close_list.ledger_name_l(i)||'</td></tr>';
1476
1477 print(l_message);
1478 END LOOP;
1479 END IF;
1480
1481 IF (l_update_process_flag = 'Y' AND period_close_list.event_id_l.COUNT > 0) THEN
1482 l_debug_info := 'The column process_flag has been found, proceeding to update';
1483 FOR i IN period_close_list.event_id_l.FIRST..period_close_list.event_id_l.LAST LOOP
1484 l_sql_stmt :=
1485 ' UPDATE '||l_driver_table||
1486 ' SET process_flag = ''E'' '|| /*Bug 9727543*/
1487 ' WHERE event_id = '||period_close_list.event_id_l(i);
1488
1489 l_debug_info := 'Proceeding to update the process flag for the event '||
1490 period_close_list.event_id_l(i);
1491 EXECUTE IMMEDIATE l_sql_stmt;
1492 END LOOP;
1493 END IF;
1494 EXIT WHEN closed_period_trx%NOTFOUND;
1495
1496 END LOOP;
1497 l_message := '</table>';
1498 print(l_message);
1499 END IF;
1500
1501 IF p_calc_undo_date = 'Y' THEN
1502 l_debug_info := 'Check for the presence of columns for calculated undo dates and periods';
1503 BEGIN
1504 SELECT 1
1505 INTO l_dummy
1506 FROM sys.all_tab_columns
1507 WHERE table_name = l_driver_table
1508 AND column_name = 'CALCULATED_UNDO_DATE';
1509
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 l_sql_stmt := ' ALTER TABLE '||l_driver_table||' ADD '||
1513 ' (CALCULATED_UNDO_DATE DATE, CALCULATED_UNDO_PERIOD VARCHAR2(100)) ';
1514
1515 EXECUTE IMMEDIATE l_sql_stmt;
1516 END;
1517
1518 --Bug 9436697 changed the update to update dr.proposed_undo_date onto dr.calculated_undo_date
1519
1520 IF p_chk_proposed_undo_date = 'Y' AND l_check_proposed_col = 'Y' THEN
1521
1522 l_sql_stmt :=
1523 ' UPDATE '||l_driver_table ||' dr '||
1524 ' SET (dr.calculated_undo_date, '||
1525 ' dr.calculated_undo_period) = '||
1526 ' (SELECT dr.proposed_undo_date, glps.period_name '||
1527 ' FROM xla_events xe, '||
1528 ' gl_period_statuses glps, '||
1529 ' xla_transaction_entities_upg xte '||
1530 ' WHERE xe.application_id = 200 '||
1531 ' AND glps.application_id = 200 '||
1532 ' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
1533 ' AND glps.set_of_books_id = xte.ledger_id '||
1534 ' AND xte.application_id =200 '||
1535 ' AND xte.entity_id = xe.entity_id '||
1536 ' AND dr.event_id = xe.event_id '||
1537 ' AND dr.proposed_undo_date BETWEEN glps.start_date '||
1538 ' AND glps.end_date) '||
1539 ' WHERE 1=1 ';
1540
1541 IF p_chk_proposed_undo_date = 'Y' AND l_check_proposed_col = 'Y' THEN
1542 l_sql_stmt := l_sql_stmt||
1543 ' AND dr.proposed_undo_date IS NOT NULL ';
1544 END IF;
1545
1546 l_sql_stmt := l_sql_stmt||
1547 ' AND dr.process_flag = ''Y'' '||
1548 ' AND dr.calculated_undo_date IS NULL '||
1549 ' AND NOT EXISTS '||
1550 ' (SELECT 1 '||
1551 ' FROM gl_period_statuses glps, '||
1552 ' xla_transaction_entities_upg xte, '||
1553 ' xla_ae_headers xah '||
1554 ' WHERE (glps.application_id = 101 OR '||
1555 ' (glps.application_id = 200 AND '||
1556 ' xah.ledger_id = xte.ledger_id)) '||
1557 ' AND dr.event_id = xah.event_id '||
1558 ' AND xah.application_id =200 '||
1559 ' AND xte.application_id =200 '||
1560 ' AND xah.entity_id = xte.entity_id '||
1561 ' AND xah.ledger_id = glps.set_of_books_id '||
1562 ' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
1563 ' AND dr.proposed_undo_date BETWEEN glps.start_date '||
1564 ' AND glps.end_date '||
1565 ' AND glps.closing_status NOT IN (''O'',''F'')) ';
1566
1567 l_debug_info := 'Before updating the calculated values where the event date is open';
1568 EXECUTE IMMEDIATE l_sql_stmt;
1569
1570 END IF;
1571
1572 IF p_check_event_date = 'Y' THEN
1573 l_debug_info := ' Updating calculated date and period for all records where event date '||
1574 ' is in an open period ';
1575
1576 l_sql_stmt :=
1577 ' UPDATE '||l_driver_table ||' dr '||
1578 ' SET (dr.calculated_undo_date, '||
1579 ' dr.calculated_undo_period) = '||
1580 ' (SELECT xe.event_date, glps.period_name '||
1581 ' FROM xla_events xe, '||
1582 ' gl_period_statuses glps, '||
1583 ' xla_transaction_entities_upg xte '||
1584 ' WHERE xe.application_id = 200 '||
1585 ' AND glps.application_id = 200 '||
1586 ' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
1587 ' AND glps.set_of_books_id = xte.ledger_id '||
1588 ' AND xte.application_id =200 '||
1589 ' AND xte.entity_id = xe.entity_id '||
1590 ' AND dr.event_id = xe.event_id '||
1591 ' AND xe.event_date BETWEEN glps.start_date '||
1592 ' AND glps.end_date) '||
1593 ' WHERE 1=1 ';
1594
1595 IF p_chk_proposed_undo_date = 'Y' AND l_check_proposed_col = 'Y' THEN
1596 l_sql_stmt := l_sql_stmt||
1597 ' AND dr.proposed_undo_date IS NULL ';
1598 END IF;
1599
1600 l_sql_stmt := l_sql_stmt||
1601 ' AND dr.process_flag = ''Y'' '||
1602 ' AND dr.calculated_undo_date IS NULL '||
1603 ' AND NOT EXISTS '||
1604 ' (SELECT 1 '||
1605 ' FROM gl_period_statuses glps, '||
1606 ' xla_transaction_entities_upg xte, '||
1607 ' xla_ae_headers xah '||
1608 ' WHERE (glps.application_id = 101 OR '||
1609 ' (glps.application_id = 200 AND '||
1610 ' xah.ledger_id = xte.ledger_id)) '||
1611 ' AND dr.event_id = xah.event_id '||
1612 ' AND xah.application_id =200 '||
1613 ' AND xte.application_id =200 '||
1614 ' AND xah.entity_id = xte.entity_id '||
1615 ' AND xah.ledger_id = glps.set_of_books_id '||
1616 ' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
1617 ' AND xah.accounting_date BETWEEN glps.start_date '||
1618 ' AND glps.end_date '||
1619 ' AND glps.closing_status NOT IN (''O'',''F'')) ';
1620
1621 l_debug_info := 'Before updating the calculated values where the event date is open';
1622 EXECUTE IMMEDIATE l_sql_stmt;
1623 END IF;
1624
1625 IF p_check_sysdate = 'Y' THEN
1626 l_debug_info := ' Updating calculated date and period for all records where sysdate '||
1627 ' is in an open period ';
1628 l_sql_stmt :=
1629 ' UPDATE '||l_driver_table ||' dr '||
1630 ' SET (dr.calculated_undo_date, '||
1631 ' dr.calculated_undo_period) = '||
1632 ' (SELECT trunc(sysdate), glps.period_name '||
1633 ' FROM xla_events xe, '||
1634 ' gl_period_statuses glps, '||
1635 ' xla_transaction_entities_upg xte '||
1636 ' WHERE xe.application_id = 200 '||
1637 ' AND glps.application_id = 200 '||
1638 ' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
1639 ' AND glps.set_of_books_id = xte.ledger_id '||
1640 ' AND xte.application_id =200 '||
1641 ' AND xte.entity_id = xe.entity_id '||
1642 ' AND dr.event_id = xe.event_id '||
1643 ' AND trunc(sysdate) BETWEEN glps.start_date '||
1644 ' AND glps.end_date) '||
1645 ' WHERE 1=1 ';
1646
1647 IF p_chk_proposed_undo_date = 'Y' AND l_check_proposed_col = 'Y' THEN
1648 l_sql_stmt := l_sql_stmt||
1649 ' AND dr.proposed_undo_date IS NULL ';
1650 END IF;
1651
1652 l_sql_stmt := l_sql_stmt||
1653 ' AND dr.process_flag = ''Y'' '||
1654 ' AND dr.calculated_undo_date IS NULL '||
1655 ' AND NOT EXISTS '||
1656 ' (SELECT 1 '||
1657 ' FROM gl_period_statuses glps, '||
1658 ' xla_transaction_entities_upg xte, '||
1659 ' xla_ae_headers xah '||
1660 ' WHERE (glps.application_id = 101 OR '||
1661 ' (glps.application_id = 200 AND '||
1662 ' xah.ledger_id = xte.ledger_id)) '||
1663 ' AND dr.event_id = xah.event_id '||
1664 ' AND xah.application_id =200 '||
1665 ' AND xte.application_id =200 '||
1666 ' AND xah.entity_id = xte.entity_id '||
1667 ' AND xah.ledger_id = glps.set_of_books_id '||
1668 ' AND nvl(glps.adjustment_period_flag, ''N'') = ''N'' '||
1669 ' AND trunc(sysdate) BETWEEN glps.start_date '||
1670 ' AND glps.end_date '||
1671 ' AND glps.closing_status NOT IN (''O'',''F'')) ';
1672
1673 l_debug_info := 'Before updating the calculated values for events where event date is closed, '||
1674 'and there is no proposed date';
1675 EXECUTE IMMEDIATE l_sql_stmt;
1676 END IF;
1677
1678 END IF;
1679
1680 IF p_commit_flag = 'Y' THEN
1681 COMMIT;
1682 END IF;
1683
1684 EXCEPTION
1685 WHEN OTHERS THEN
1686 IF SQLCODE <> -20001 THEN
1687 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
1688 ' in '||l_calling_sequence||' while performing '||l_debug_info;
1689 Print(l_error_log);
1690 END IF;
1691 APP_EXCEPTION.RAISE_EXCEPTION();
1692 END;
1693
1694 PROCEDURE check_ccid
1695 (p_bug_no IN NUMBER,
1696 p_driver_table IN VARCHAR2,
1697 p_update_process_flag IN VARCHAR2,
1698 p_commit_flag IN VARCHAR2 DEFAULT 'N',
1699 p_calling_sequence IN VARCHAR2) IS
1700
1701 l_sql_stmt LONG;
1702 l_bug_no VARCHAR2(100);
1703 l_driver_table ALL_TABLES.TABLE_NAME%TYPE;
1704 l_debug_info VARCHAR2(4000);
1705 l_error_log LONG;
1706 l_date VARCHAR2(100);
1707 l_dummy NUMBER;
1708 l_check_process_flag VARCHAR2(1) := 'N';
1709 l_message VARCHAR2(4000);
1710 l_calling_sequence VARCHAR2(4000);
1711
1712 TYPE event_id_t IS TABLE OF XLA_AE_HEADERS.EVENT_ID%TYPE INDEX BY BINARY_INTEGER;
1713 TYPE event_type_code_t IS TABLE OF XLA_AE_HEADERS.EVENT_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
1714 TYPE ae_header_id_t IS TABLE OF XLA_AE_HEADERS.AE_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
1715 TYPE accounting_date_t IS TABLE OF XLA_AE_HEADERS.ACCOUNTING_DATE%TYPE INDEX BY BINARY_INTEGER;
1716 TYPE ae_line_num_t IS TABLE OF XLA_AE_LINES.AE_LINE_NUM%TYPE INDEX BY BINARY_INTEGER;
1717 TYPE accounting_class_code_t IS TABLE OF XLA_AE_LINES.ACCOUNTING_CLASS_CODE%TYPE INDEX BY BINARY_INTEGER;
1718 TYPE source_type_t IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
1719 TYPE source_id_int_1_t IS TABLE OF XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1%TYPE INDEX BY BINARY_INTEGER;
1720 TYPE transaction_number_t IS TABLE OF XLA_TRANSACTION_ENTITIES.TRANSACTION_NUMBER%TYPE INDEX BY BINARY_INTEGER;
1721 TYPE security_id_int_1_t IS TABLE OF XLA_TRANSACTION_ENTITIES.SECURITY_ID_INT_1%TYPE INDEX BY BINARY_INTEGER;
1722 TYPE code_combination_id_t IS TABLE OF GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE INDEX BY BINARY_INTEGER;
1723 TYPE account_t IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
1724 TYPE enabled_flag_t IS TABLE OF GL_CODE_COMBINATIONS.ENABLED_FLAG%TYPE INDEX BY BINARY_INTEGER;
1725 TYPE end_date_active_t IS TABLE OF GL_CODE_COMBINATIONS.END_DATE_ACTIVE%TYPE INDEX BY BINARY_INTEGER;
1726 TYPE ledger_name_t IS TABLE OF GL_LEDGERS.NAME%TYPE INDEX BY BINARY_INTEGER;
1727
1728 TYPE invalid_ccid_rec_typ IS RECORD
1729 (event_id_l event_id_t,
1730 event_type_code_l event_type_code_t,
1731 ae_header_id_l ae_header_id_t,
1732 accounting_date_l accounting_date_t,
1733 ae_line_num_l ae_line_num_t,
1734 accounting_class_code_l accounting_class_code_t,
1735 source_type_l source_type_t,
1736 source_id_int_1_l source_id_int_1_t,
1737 transaction_number_l transaction_number_t,
1738 security_id_int_1_l security_id_int_1_t,
1739 code_combination_id_l code_combination_id_t,
1740 account_l account_t,
1741 enabled_flag_l enabled_flag_t,
1742 end_date_active_l end_date_active_t,
1743 ledger_name_l ledger_name_t);
1744
1745 invalid_ccid_list INVALID_CCID_REC_TYP;
1746
1747 TYPE refcurtyp IS REF CURSOR;
1748 invalid_ccid REFCURTYP;
1749
1750 BEGIN
1751
1752 l_calling_sequence := 'AP_ACCTG_DATA_FIX_PKG.check_ccid <- '||p_calling_sequence;
1753 l_bug_no := p_bug_no;
1754
1755 l_debug_info := 'Constructing the driver table name';
1756 IF p_driver_table IS NOT NULL THEN
1757 l_driver_table := upper(p_driver_table);
1758 ELSE
1759 l_driver_table := 'AP_TEMP_DATA_DRIVER_'||p_bug_no;
1760 END IF;
1761
1762 l_debug_info := 'Constructing the sql statement ';
1763 l_sql_stmt :=
1764 ' SELECT DISTINCT '||
1765 ' xah.event_id, '||
1766 ' xah.event_type_code, '||
1767 ' xah.ae_header_id, '||
1768 ' xah.accounting_date, '||
1769 ' xal.ae_line_num, '||
1770 ' xal.accounting_class_code, '||
1771 ' DECODE(xte.entity_code, '||
1772 ' ''AP_INVOICES'', ''Invoice'', '||
1773 ' ''AP_PAYMENTS'', ''Payments''), '||
1774 ' xte.source_id_int_1, '||
1775 ' xte.transaction_number, '||
1776 ' xte.security_id_int_1, '||
1777 ' glcc.code_combination_id, '||
1778 ' glcc.padded_concatenated_segments, '||
1779 ' glcc.enabled_flag, '||
1780 ' glcc.end_date_active, '||
1781 ' gl.name '||
1782 ' FROM xla_events xe, '||
1783 ' xla_ae_headers xah, '||
1784 ' xla_ae_lines xal, '||
1785 l_driver_table||' dr, '||
1786 ' xla_transaction_entities_upg xte, '||
1787 ' gl_code_combinations_kfv glcc, '||
1788 ' gl_ledgers gl '||
1789 ' WHERE xe.application_id = 200 '||
1790 ' AND xah.application_id = 200 '||
1791 ' AND xal.application_id = 200 '||
1792 ' AND xte.application_id =200 '||
1793 ' AND xe.event_id = dr.event_id '||
1794 ' and DR.PROCESS_FLAG = ''Y'' '|| /*Bug 9727543*/
1795 ' AND xe.entity_id = xte.entity_id '||
1796 ' AND xe.event_id = xah.event_id '||
1797 ' AND xah.entity_id = xte.entity_id '||
1798 ' AND xe.event_status_code = ''P'' '||
1799 ' AND xah.accounting_entry_status_code = ''F'' '||
1800 ' AND xah.event_type_code <> ''MANUAL'' '||
1801 ' AND xah.ae_header_id = xal.ae_header_id '||
1802 ' AND xal.code_combination_id = glcc.code_combination_id '||
1803 ' AND (glcc.enabled_flag = ''N'' OR glcc.end_date_active IS NOT NULL) '||
1804 ' AND xah.ledger_id = gl.ledger_id ';
1805
1806 --Print(l_sql_stmt);
1807
1808 l_message := '<b><u>The following events cannot be Unaccounted because the Code Combination Id is not Enabled</u></b>';
1809 Print(l_message);
1810
1811 l_message := '<table border="5">'||
1812 '<th>EVENT_ID</th>'||
1813 '<th>EVENT_TYPE_CODE</th>'||
1814 '<th>AE_HEADER_ID</th>'||
1815 '<th>ACCOUNTING_DATE</th>'||
1816 '<th>AE_LINE_NUM</th>'||
1817 '<th>ACCOUNTING_CLASS_CODE</th>'||
1818 '<th>SOURCE_TYPE</th>'||
1819 '<th>SOURCE_ID_INT_1</th>'||
1820 '<th>TRANSACTION_NUMBER</th>'||
1821 '<th>SECURITY_ID_INT_1</th>'||
1822 '<th>CODE_COMBINATION_ID</th>'||
1823 '<th>ACCOUNT</th>'||
1824 '<th>ENABLED_FLAG</th>'||
1825 '<th>END_DATE_ACTIVE</th>'||
1826 '<th>LEDGER_NAME</tr>';
1827 print(l_message);
1828
1829 OPEN invalid_ccid FOR l_sql_stmt;
1830 LOOP
1831 FETCH invalid_ccid
1832 BULK COLLECT INTO invalid_ccid_list.event_id_l,
1833 invalid_ccid_list.event_type_code_l,
1834 invalid_ccid_list.ae_header_id_l,
1835 invalid_ccid_list.accounting_date_l,
1836 invalid_ccid_list.ae_line_num_l,
1837 invalid_ccid_list.accounting_class_code_l,
1838 invalid_ccid_list.source_type_l,
1839 invalid_ccid_list.source_id_int_1_l,
1840 invalid_ccid_list.transaction_number_l,
1841 invalid_ccid_list.security_id_int_1_l,
1842 invalid_ccid_list.code_combination_id_l,
1843 invalid_ccid_list.account_l,
1844 invalid_ccid_list.enabled_flag_l,
1845 invalid_ccid_list.end_date_active_l,
1846 invalid_ccid_list.ledger_name_l LIMIT 1000;
1847
1848 IF invalid_ccid_list.event_id_l.COUNT > 0 THEN
1849 FOR i IN invalid_ccid_list.event_id_l.FIRST..invalid_ccid_list.event_id_l.LAST LOOP
1850 l_message :=
1851 '<tr><td>'||
1852 to_char(invalid_ccid_list.event_id_l(i))||'</td><td>'||
1853 invalid_ccid_list.event_type_code_l(i)||'</td><td>'||
1854 to_char(invalid_ccid_list.ae_header_id_l(i))||'</td><td>'||
1855 to_char(invalid_ccid_list.accounting_date_l(i), 'DD-MON-YYYY')||'</td><td>'||
1856 to_char(invalid_ccid_list.ae_line_num_l(i))||'</td><td>'||
1857 invalid_ccid_list.accounting_class_code_l(i)||'</td><td>'||
1858 invalid_ccid_list.source_type_l(i)||'</td><td>'||
1859 to_char(invalid_ccid_list.source_id_int_1_l(i))||'</td><td>'||
1860 invalid_ccid_list.transaction_number_l(i)||'</td><td>'||
1861 to_char(invalid_ccid_list.security_id_int_1_l(i))||'</td><td>'||
1862 to_char(invalid_ccid_list.code_combination_id_l(i))||'</td><td>'||
1863 invalid_ccid_list.account_l(i)||'</td><td>'||
1864 invalid_ccid_list.enabled_flag_l(i)||'</td><td>'||
1865 to_char(invalid_ccid_list.end_date_active_l(i), 'DD-MON-YYYY')||'</td><td>'||
1866 invalid_ccid_list.ledger_name_l(i)||'</td><tr>';
1867
1868 print(l_message);
1869 END LOOP;
1870 END IF;
1871
1872 BEGIN
1873 SELECT 'Y'
1874 INTO l_check_process_flag
1875 FROM sys.all_tab_columns
1876 WHERE table_name = l_driver_table
1877 AND column_name = 'PROCESS_FLAG';
1878
1879 EXCEPTION
1880 WHEN OTHERS THEN
1881 l_debug_info := 'The column process_flag is not found in the table';
1882 l_check_process_flag := 'N';
1883
1884 END;
1885
1886 IF (l_check_process_flag = 'Y' AND p_update_process_flag = 'Y') THEN
1887
1888 l_debug_info := 'The column process_flag has been found, proceeding to update';
1889
1890 IF invalid_ccid_list.event_id_l.COUNT > 0 THEN
1891 FOR i IN invalid_ccid_list.event_id_l.FIRST..invalid_ccid_list.event_id_l.LAST LOOP
1892 l_sql_stmt :=
1893 ' UPDATE '||l_driver_table||
1894 ' SET process_flag = ''E'' '||
1895 ' WHERE event_id = '||invalid_ccid_list.event_id_l(i);
1896
1897 l_debug_info := 'Proceeding to update the process flag for the event '||
1898 invalid_ccid_list.event_id_l(i);
1899 EXECUTE IMMEDIATE l_sql_stmt;
1900 END LOOP;
1901 END IF;
1902
1903 END IF;
1904
1905 EXIT WHEN invalid_ccid%NOTFOUND;
1906 END LOOP;
1907
1908 l_message := '</table>';
1909 print(l_message);
1910
1911 IF p_commit_flag = 'Y' THEN
1912 COMMIT;
1913 END IF;
1914
1915 EXCEPTION
1916 WHEN OTHERS THEN
1917 IF SQLCODE <> -20001 THEN
1918 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
1919 ' in '||l_calling_sequence||' while performing '||l_debug_info;
1920 Print(l_error_log);
1921 END IF;
1922 APP_EXCEPTION.RAISE_EXCEPTION();
1923 END;
1924
1925 PROCEDURE Undo_Accounting
1926 (p_Source_Table IN VARCHAR2,
1927 p_Source_Id IN NUMBER,
1928 p_Event_Id IN NUMBER DEFAULT NULL,
1929 p_skip_date_calc IN VARCHAR2 DEFAULT 'N',
1930 p_undo_date IN DATE,
1931 p_undo_period IN VARCHAR2,
1932 p_bug_id IN NUMBER DEFAULT NULL,
1933 p_Gl_Date IN DATE DEFAULT NULL, --Bug#8471406
1934 p_rev_event_id OUT NOCOPY NUMBER,
1935 p_new_event_id OUT NOCOPY NUMBER,
1936 p_return_code OUT NOCOPY VARCHAR2,
1937 p_calling_sequence IN VARCHAR2
1938 )
1939 IS
1940 l_aPi_Version NUMBER;
1941 l_InIt_msg_List VARCHAR2(300);
1942 l_Application_Id INTEGER;
1943 l_Reversal_Method VARCHAR2(300);
1944 l_gl_Date DATE;
1945 l_Post_To_gl_Flag VARCHAR2(3);
1946 x_msg_Count NUMBER;
1947 x_msg_Data VARCHAR2(4000);
1948 x_Rev_ae_Header_Id INTEGER;
1949 x_Rev_Event_Id INTEGER;
1950 x_Rev_Entity_Id INTEGER;
1951 x_New_Event_Id INTEGER;
1952 x_New_Entity_Id INTEGER;
1953 l_Rev_Event_Id INTEGER;
1954 l_New_Event_Id INTEGER;
1955 l_Source_Id NUMBER;
1956 l_Return_Status VARCHAR2(300);
1957 l_event_status_code XLA_EVENTS.EVENT_STATUS_CODE%TYPE;
1958 Debug_Info VARCHAR2(50) := 'Undo_Accounting';
1959 l_calling_sequence VARCHAR2(4000);
1960
1961 --Cursor modified to improve performance 7655892
1962
1963 CURSOR Events_to_Process(p_Check_Or_Invoice_Id NUMBER) IS
1964 SELECT /*LEADING(ASP, XTE)*/ DISTINCT xe.event_id,
1965 security_id_int_1 cur_org_id,
1966 decode(xte.entity_code, 'AP_PAYMENTS', 'CHECKS', 'AP_INVOICES', 'INVOICES') check_or_invoice,
1967 xe.event_date gl_date,
1968 MAX(DECODE(NVL(gl_transfer_status_code,'N'), 'Y', 'Y', 'N')) gl_transfer_status_code /*bug 13911650*/
1969 FROM xla_transaction_entities_upg xte,
1970 xla_events xe,
1971 xla_ae_headers xah,
1972 ap_system_parameters_all asp
1973 WHERE xte.entity_id = xe.entity_id
1974 AND xe.application_id = 200
1975 AND xte.entity_code = p_Source_Table
1976 AND nvl(source_id_int_1,-99) = p_Check_Or_Invoice_Id --nvl added by bug 7655892
1977 AND xe.event_status_code = 'P'
1978 AND xe.process_status_code = 'P'
1979 AND xah.event_id = xe.event_id
1980 AND nvl(xe.event_id, xe.event_id) = nvl(p_Event_Id, xe.event_id) --nvl added to both sides in 7655892
1981 AND xah.application_id = 200
1982 AND xte.application_id = 200
1983 /* AND xah.ledger_id = xte.ledger_id Bug 13900488 */
1984 AND xte.ledger_id = asp.set_of_books_id --extra join condition added by 7655892
1985 AND xte.security_id_int_1 = asp.org_id
1986 AND nvl(xe.budgetary_control_flag, 'N') = 'N'
1987 GROUP BY xe.event_id,
1988 security_id_int_1,
1989 decode(xte.entity_code, 'AP_PAYMENTS', 'CHECKS', 'AP_INVOICES', 'INVOICES'),
1990 xe.event_date; /* bug 13911650, 7627438*/
1991
1992 TYPE Events_to_Process_tab_type IS TABLE of Events_to_Process%ROWTYPE;
1993 Events_to_Process_tab Events_to_Process_tab_type;
1994
1995 CURSOR Check_period_Status(p_Date DATE,
1996 p_org_id NUMBER) IS
1997 SELECT DISTINCT gps.Period_Name
1998 FROM gl_Period_Statuses gps,
1999 ap_System_Parameters_All Asp
2000 WHERE gps.Application_Id = 200
2001 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
2002 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
2003 AND p_Date BETWEEN Trunc(gps.Start_Date)
2004 AND Trunc(gps.End_Date)
2005 AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
2006 AND gps.closing_Status in ('O', 'F')
2007 INTERSECT
2008 SELECT DISTINCT gps.Period_Name
2009 FROM gl_Period_Statuses gps,
2010 ap_System_Parameters_All Asp
2011 WHERE gps.Application_Id = 101
2012 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
2013 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
2014 AND p_Date BETWEEN Trunc(gps.Start_Date)
2015 AND Trunc(gps.End_Date)
2016 AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
2017 AND gps.closing_Status in ('O', 'F');
2018
2019 /* bug # 7688339. if the event date period and
2020 sysdate period is not open. it will place
2021 the accounting reversals for earliest open period.
2022 the cursor check_open_period will check the
2023 latest open period for the org_id
2024 */
2025 --Bug#8471406 start
2026 CURSOR Check_Open_Period(p_org_id NUMBER) IS
2027 SELECT Period_Name, End_Date
2028 FROM (
2029 SELECT DISTINCT gps.Period_Name, trunc(gps.End_Date) End_date
2030 FROM gl_Period_Statuses gps,
2031 ap_System_Parameters_All Asp
2032 WHERE gps.Application_Id = 200
2033 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
2034 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
2035 AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
2036 AND gps.closing_Status in ('O', 'F')
2037 INTERSECT
2038 SELECT DISTINCT gps.Period_Name, trunc(gps.End_Date) End_date
2039 FROM gl_Period_Statuses gps,
2040 ap_System_Parameters_All Asp
2041 WHERE gps.Application_Id = 101
2042 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
2043 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
2044 AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
2045 AND gps.closing_Status in ('O', 'F')
2046 order by end_date
2047 )
2048 WHERE rownum < 2;
2049
2050 CURSOR Check_Entered_Gl_date(p_org_id NUMBER) IS
2051 SELECT DISTINCT gps.Period_Name
2052 FROM gl_Period_Statuses gps,
2053 ap_System_Parameters_All Asp
2054 WHERE gps.Application_Id = 200
2055 AND p_Gl_Date between trunc(gps.start_date) and trunc(gps.end_date)
2056 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
2057 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
2058 AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
2059 AND gps.closing_Status in ('O', 'F')
2060 INTERSECT
2061 SELECT DISTINCT gps.Period_Name
2062 FROM gl_Period_Statuses gps,
2063 ap_System_Parameters_All Asp
2064 WHERE gps.Application_Id = 101
2065 AND p_Gl_Date between trunc(gps.start_date) and trunc(gps.end_date)
2066 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
2067 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
2068 AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
2069 AND gps.closing_Status in ('O', 'F');
2070
2071
2072 l_entered_date DATE;
2073 l_entered_Period VARCHAR2(15);
2074 --Bug#8471406 end
2075
2076 l_Period_Name VARCHAR2(15);
2077 l_cur_Period_Name VARCHAR2(15);
2078 NULL_VALUE NUMBER := null;
2079 l_table_name VARCHAR2(20) := 'ALL_TABLES';
2080 ins_AP_undo_event_log_stmt VARCHAR2(200) := 'INSERT INTO AP_undo_event_log('
2081 ||'EVENT_ID,E2,E3,STATUS,INVOICE_ID,CHECK_ID, BUG_ID) '||
2082 'VALUES(:1, :2, :3, :4, :5, :6, :7)';
2083 log_table_exists_stmt VARCHAR2(200) := 'select count(*) '||
2084 'from '||l_table_name ||
2085 ' where table_name = ''AP_UNDO_EVENT_LOG'' ';
2086 log_table_exists NUMBER ;
2087 -- Logging:
2088 l_procedure_name CONSTANT VARCHAR2(30) := 'Undo_Accounting';
2089 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2090
2091 BEGIN
2092
2093 l_calling_sequence := 'AP_ACCTG_DATA_FIX_PKG <- '||p_calling_sequence;
2094
2095 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2096
2097 l_log_msg := 'Begin of procedure '|| l_procedure_name;
2098 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2099 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2100 G_MODULE_NAME||l_procedure_name,
2101 l_log_msg);
2102 END IF;
2103
2104 l_aPi_Version := 1.0;
2105 l_InIt_msg_List := fnd_aPi.g_True;
2106 l_Application_Id := 200;
2107 l_Reversal_Method := 'SIDE';
2108 l_Post_To_gl_Flag := 'N';
2109 l_Source_Id := p_Source_Id;
2110
2111 EXECUTE IMMEDIATE log_table_exists_stmt into log_table_exists;
2112
2113 IF ( log_table_exists = 0) THEN
2114
2115 l_log_msg := 'Before creating Table AP_undo_event_log';
2116 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2117 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2118 G_MODULE_NAME||l_procedure_name,
2119 l_log_msg);
2120 END IF;
2121
2122 BEGIN
2123 EXECUTE IMMEDIATE 'CREATE TABLE AP_undo_event_log
2124 (
2125 EVENT_ID NUMBER,
2126 E2 integer,E3 integer,STATUS varchar2(300),
2127 INVOICE_ID NUMBER ,CHECK_ID NUMBER, BUG_ID NUMBER)';
2128
2129 l_log_msg := 'Created table AP_undo_event_log';
2130 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2131 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2132 G_MODULE_NAME||l_procedure_name,
2133 l_log_msg);
2134 END IF;
2135
2136 EXCEPTION
2137 WHEN OTHERS THEN
2138
2139 l_log_msg := 'Could not create table AP_undo_event_log';
2140 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2141 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2142 G_MODULE_NAME||l_procedure_name,
2143 l_log_msg);
2144 END IF;
2145
2146 END;
2147 END IF;
2148
2149 OPEN Events_to_Process(l_Source_Id);
2150 FETCH Events_to_Process BULK COLLECT INTO Events_to_Process_tab;
2151 CLOSE Events_to_Process;
2152
2153 IF(Events_to_Process_tab.COUNT =0) THEN
2154 l_log_msg := 'No events exist for the parameters passed : '||p_Source_Table
2155 || ' , '||p_Source_Id;
2156 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2157 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2158 G_MODULE_NAME||l_procedure_name,
2159 l_log_msg);
2160 END IF;
2161 END IF;
2162 FOR i in 1..Events_to_Process_tab.COUNT LOOP
2163
2164 IF (p_skip_date_calc = 'Y' AND
2165 p_undo_date IS NOT NULL AND
2166 p_undo_period IS NOT NULL) THEN
2167
2168 l_gl_date := p_undo_date;
2169 l_period_name := p_undo_period;
2170 ELSE
2171
2172 l_gl_Date := Events_to_Process_tab(i).gl_Date;
2173 OPEN Check_period_Status(l_gl_Date,
2174 Events_to_Process_tab(i).Cur_Org_Id);
2175 FETCH Check_period_Status INTO l_Period_Name;
2176 IF(Check_period_Status%NOTFOUND ) THEN
2177 l_Period_Name := null;
2178 END IF;
2179 CLOSE Check_period_Status;
2180
2181 --Bug#8471406 start
2182 if (l_Period_Name is Null) Then
2183 if ( p_Gl_Date is not Null) then
2184 Open Check_Entered_Gl_date(Events_to_Process_tab(i).Cur_Org_Id);
2185 Fetch Check_Entered_Gl_date into l_entered_period;
2186 IF(Check_Entered_Gl_date%NOTFOUND ) THEN
2187 l_log_msg := 'The entered date did not have any period, please recheck '||
2188 ' exiting.....';
2189 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2190 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2191 G_MODULE_NAME||l_procedure_name,
2192 l_log_msg);
2193 END IF;
2194 RETURN; -- exit undo accounting program
2195 END IF;
2196 Close Check_Entered_Gl_date;
2197 l_gl_Date := p_Gl_Date;
2198 l_Period_Name := l_entered_period;
2199 Else -- p_Gl_Date is null
2200
2201 FOR Check_Open_Period_rec IN Check_Open_Period(Events_to_Process_tab(i).Cur_Org_Id)
2202 LOOP
2203 l_gl_Date := Check_Open_Period_rec.End_Date;
2204 l_Period_Name := Check_Open_Period_rec.Period_Name;
2205
2206 END LOOP;
2207
2208 End if; -- p_Gl_Date is null
2209 End If; -- transaction date in closed period
2210 END IF;
2211
2212 mo_Global.Set_Policy_Context('S',Events_to_Process_tab(i).Cur_Org_Id);
2213
2214 l_log_msg := 'Set Org context to '||Events_to_Process_tab(i).Cur_Org_Id;
2215 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2216 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2217 G_MODULE_NAME||l_procedure_name,
2218 l_log_msg);
2219 END IF;
2220
2221 l_return_status := NULL;
2222 BEGIN
2223
2224 IF (Events_to_Process_tab(i).gl_Transfer_Status_Code = 'Y') THEN
2225 Debug_Info := 'xla_DataFixes_Pub.Reverse_Journal_entries';
2226
2227 l_log_msg := 'Calling xla_DataFixes_Pub.Reverse_Journal_entries';
2228 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2229 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2230 G_MODULE_NAME||l_procedure_name,
2231 l_log_msg);
2232 END IF;
2233
2234 xla_DataFixes_Pub.Reverse_Journal_entries
2235 (p_aPi_Version => l_aPi_Version,
2236 p_InIt_msg_List => l_InIt_msg_List,
2237 p_Application_Id => l_Application_Id,
2238 p_event_id => Events_to_Process_tab(i).Event_Id,
2239 p_Reversal_Method => l_Reversal_Method,
2240 p_gl_Date => l_gl_Date,
2241 p_Post_To_gl_Flag => l_Post_To_gl_Flag,
2242 x_Return_Status => l_Return_Status,
2243 x_msg_Count => x_msg_Count,
2244 x_msg_Data => x_msg_Data,
2245 x_Rev_ae_Header_Id => x_Rev_ae_Header_Id,
2246 x_Rev_Event_Id => x_Rev_Event_Id,
2247 x_Rev_Entity_Id => x_Rev_Entity_Id,
2248 x_New_Event_Id => x_New_Event_Id,
2249 x_New_Entity_Id => x_New_Entity_Id);
2250
2251 l_Rev_Event_Id := x_Rev_Event_Id;
2252 l_New_Event_Id := x_New_Event_Id;
2253 p_rev_event_id := x_Rev_Event_Id;
2254 p_new_event_id := x_New_Event_Id;
2255
2256 l_log_msg := 'l_Return_Status='||l_Return_Status;
2257 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2258 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2259 G_MODULE_NAME||l_procedure_name,
2260 l_log_msg);
2261 END IF;
2262
2263 If(x_msg_Count > 0 OR l_Return_Status = 'U') Then
2264
2265 l_log_msg := 'Undo_Accounting : Error in xla_DataFixes_Pub.Reverse_Journal_entries
2266 :'|| x_msg_Data;
2267 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2268 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2269 G_MODULE_NAME||l_procedure_name,
2270 l_log_msg);
2271 END IF;
2272 p_return_code := 'XLA_ERROR';
2273
2274 End If;
2275
2276 ELSIF(Events_to_Process_tab(i).gl_Transfer_Status_Code = 'N') THEN
2277 Debug_Info := 'xla_DataFixes_Pub.delete_journal_entries';
2278
2279 l_log_msg := 'Calling xla_datafixes_pub.delete_journal_entries';
2280 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2281 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2282 G_MODULE_NAME||l_procedure_name,
2283 l_log_msg);
2284 END IF;
2285
2286 xla_datafixes_pub.delete_journal_entries
2287 (p_api_version => l_aPi_Version,
2288 p_init_msg_list => l_InIt_msg_List,
2289 p_application_id => l_Application_Id,
2290 p_event_id => Events_to_Process_tab(i).Event_Id,
2291 x_return_status => l_Return_Status,
2292 x_msg_count => x_msg_Count,
2293 x_msg_data => x_msg_Data);
2294
2295 l_log_msg := 'l_Return_Status='||l_Return_Status;
2296 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2297 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2298 G_MODULE_NAME||l_procedure_name,
2299 l_log_msg);
2300 END IF;
2301
2302 If(x_msg_Count > 0 OR l_Return_Status = 'U') Then
2303
2304 l_log_msg := 'Undo_Accounting : Error in xla_DataFixes_Pub.delete_journal_entries
2305 :'|| x_msg_Data;
2306 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2307 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2308 G_MODULE_NAME||l_procedure_name,
2309 l_log_msg);
2310 END IF;
2311
2312 p_return_code := 'XLA_ERROR';
2313
2314 End If;
2315 END IF;
2316
2317 EXCEPTION
2318 WHEN OTHERS THEN
2319 l_return_status := 'U';
2320 p_return_code := 'XLA_EXCEPTION';
2321 END;
2322
2323 IF (l_Return_Status = 'S') THEN
2324 BEGIN
2325 SELECT event_status_code
2326 INTO l_event_status_code
2327 FROM xla_events xe
2328 WHERE xe.application_id = 200
2329 AND xe.event_id = Events_to_Process_tab(i).Event_Id;
2330
2331 IF l_event_status_code = 'P' THEN
2332 p_return_code := 'XLA_NO_WORK';
2333 END IF;
2334
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 l_event_status_code := 'X';
2338 p_return_code := 'XLA_NO_WORK';
2339 END;
2340
2341 IF (Events_to_Process_tab(i).Check_Or_Invoice = 'CHECKS' AND
2342 l_event_status_code = 'U') THEN
2343
2344 BEGIN
2345
2346 UPDATE ap_Payment_History_All aph
2347 SET Accounting_Date = l_gl_Date,
2348 Posted_Flag = 'N',
2349 Last_Updated_By = fnd_Global.User_Id
2350 WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
2351 AND Check_Id = p_Source_Id;
2352
2353
2354 UPDATE ap_Invoice_Payments_All aip
2355 SET Accounting_Date = l_gl_Date,
2356 Posted_Flag = 'N',
2357 Accrual_Posted_Flag = 'N',
2358 Last_Updated_By = fnd_Global.User_Id,
2359 Period_Name = l_Period_Name
2360 WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
2361 AND Check_Id = p_Source_Id;
2362
2363 --8306966
2364 UPDATE ap_Invoice_distributions_All aid
2365 SET Accounting_Date = l_gl_Date,
2366 Posted_Flag = 'N',
2367 Accrual_Posted_Flag = 'N',
2368 Last_Updated_By = fnd_Global.User_Id,
2369 Period_Name = l_Period_Name
2370 WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
2371 AND line_type_lookup_code = 'AWT' ;
2372 --end of 8306966
2373
2374
2375 UPDATE xla_Events
2376 SET Event_Date = l_gl_Date
2377 WHERE Event_Id = Events_to_Process_tab(i).Event_Id;
2378
2379 EXECUTE IMMEDIATE ins_AP_undo_event_log_stmt USING
2380 Events_to_Process_tab(i).Event_Id,l_rev_event_id
2381 ,l_new_event_id,l_return_status,NULL_VALUE
2382 ,l_Source_Id,p_bug_id;
2383
2384 DELETE
2385 FROM ap_Payment_Hist_dIsts
2386 WHERE Payment_History_Id IN
2387 (SELECT Payment_History_Id
2388 FROM ap_Payment_History_All
2389 WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
2390 AND Check_Id = l_Source_Id);
2391
2392 p_return_code := 'SUCCESS';
2393
2394 l_log_msg :='Updated Transaction tables for Payments';
2395 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2396 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2397 G_MODULE_NAME||l_procedure_name,
2398 l_log_msg);
2399 END IF;
2400
2401 EXCEPTION
2402 WHEN OTHERS THEN
2403 p_return_code := 'AP_PAYMENT_ERROR';
2404 END;
2405
2406 ELSIF (Events_to_Process_tab(i).Check_Or_Invoice = 'INVOICES' AND
2407 l_event_status_code = 'U') THEN
2408
2409 BEGIN
2410
2411 UPDATE ap_Invoice_Distributions_All Aid
2412 SET Accounting_Date = l_gl_Date,
2413 Posted_Flag = 'N',
2414 Accrual_Posted_Flag = 'N',
2415 Last_Updated_By = fnd_Global.User_Id,
2416 Period_Name =l_Period_Name
2417 WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
2418 AND Invoice_Id = l_Source_Id;
2419
2420 UPDATE ap_self_assessed_tax_dist_all asatd
2421 SET Accounting_Date = l_gl_Date,
2422 Posted_Flag = 'N',
2423 Accrual_Posted_Flag = 'N',
2424 Last_Updated_By = fnd_Global.User_Id,
2425 Period_Name = l_Period_Name
2426 WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
2427 AND Invoice_Id = l_Source_Id;
2428
2429
2430 UPDATE xla_Events
2431 SET Event_Date = l_gl_Date
2432 WHERE Event_Id = Events_to_Process_tab(i).Event_Id;
2433
2434 UPDATE ap_prepay_history_all aph
2435 SET Accounting_Date = l_gl_Date,
2436 Posted_Flag = 'N',
2437 Last_Updated_By = fnd_Global.User_Id
2438 WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
2439 AND Invoice_Id = l_Source_Id;
2440
2441 DELETE
2442 FROM ap_prepay_app_dists
2443 WHERE PREPAY_HISTORY_ID IN
2444 (SELECT PREPAY_HISTORY_ID
2445 FROM ap_prepay_history_all
2446 WHERE Accounting_Event_Id = Events_to_Process_tab(i).Event_Id
2447 AND transaction_type = 'PREPAYMENT APPLICATION ADJ' --7502473
2448 AND Invoice_Id = l_Source_Id);
2449
2450
2451 EXECUTE IMMEDIATE ins_AP_undo_event_log_stmt USING
2452 Events_to_Process_tab(i).Event_Id,l_rev_event_id,
2453 l_new_event_id,l_return_status,l_Source_Id,
2454 NULL_VALUE,p_bug_id;
2455
2456
2457 p_return_code := 'SUCCESS';
2458
2459 l_log_msg :='Updated Transaction tables for Invoice';
2460 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2461 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2462 G_MODULE_NAME||l_procedure_name,
2463 l_log_msg);
2464 END IF;
2465
2466 EXCEPTION
2467 WHEN OTHERS THEN
2468 p_return_code := 'AP_INVOICE_ERROR';
2469 END;
2470 END IF;
2471 ELSE
2472
2473 IF p_return_code IS NULL THEN
2474 p_return_code := 'XLA_ERROR';
2475 END IF;
2476
2477 IF (Events_to_Process_tab(i).Check_Or_Invoice = 'INVOICES') THEN
2478 Print('Undo Accounting Unsuccessful for Invoice id ' ||l_Source_Id
2479 ||' event id ' ||Events_to_Process_tab(i).Event_Id);
2480
2481 l_log_msg :='Undo Accounting Unsuccessful for Invoice id ' ||l_Source_Id
2482 ||' event id ' ||Events_to_Process_tab(i).Event_Id;
2483 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2484 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2485 G_MODULE_NAME||l_procedure_name,
2486 l_log_msg);
2487 END IF;
2488
2489 ELSIF (Events_to_Process_tab(i).Check_Or_Invoice = 'CHECKS') THEN
2490 Print('Undo Accounting Unsuccessful for Check id ' ||l_Source_Id
2491 ||' event id ' ||Events_to_Process_tab(i).Event_Id);
2492
2493 l_log_msg :='Undo Accounting Unsuccessful for Check id ' ||l_Source_Id
2494 ||' event id ' ||Events_to_Process_tab(i).Event_Id;
2495 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2496 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2497 G_MODULE_NAME||l_procedure_name,
2498 l_log_msg);
2499 END IF;
2500
2501 END IF;
2502 END IF;
2503 END LOOP;
2504 EXCEPTION
2505 WHEN OTHERS THEN
2506 l_log_msg :='Exception in Undo_accounting ';
2507 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2508 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2509 G_MODULE_NAME||l_procedure_name,
2510 l_log_msg);
2511 END IF;
2512 IF (SQLCODE <> - 20001) THEN
2513 fnd_Message.Set_Name('SQLAP','AP_DEBUG');
2514 fnd_Message.Set_Token('ERROR',SQLERRM);
2515 fnd_Message.Set_Token('CALLING_SEQUENCE',l_Calling_Sequence);
2516 fnd_Message.Set_Token('PARAMETERS','p_source_id = '
2517 ||p_Source_Id
2518 ||', p_Source_Table = '
2519 ||p_Source_Table);
2520 fnd_Message.Set_Token('DEBUG_INFO',Debug_Info);
2521 END IF;
2522
2523 app_Exception.Raise_Exception;
2524
2525 END Undo_Accounting;
2526
2527 PROCEDURE Undo_Accounting
2528 (p_source_table IN VARCHAR2,
2529 p_source_id IN NUMBER,
2530 p_Event_id IN NUMBER DEFAULT NULL,
2531 p_calling_sequence IN VARCHAR2 DEFAULT NULL,
2532 p_bug_id IN NUMBER DEFAULT NULL,
2533 p_GL_Date IN DATE DEFAULT NULL) IS
2534
2535 l_source_table XLA_TRANSACTION_ENTITIES.ENTITY_CODE%TYPE;
2536 l_source_id XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1%TYPE;
2537 l_event_id XLA_EVENTS.EVENT_ID%TYPE;
2538 l_calling_sequence VARCHAR2(4000);
2539 l_bug_id NUMBER;
2540 l_rev_event_id NUMBER;
2541 l_new_event_id NUMBER;
2542 l_return_code VARCHAR2(4000);
2543 l_log_msg FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2544 l_procedure_name VARCHAR2(1000);
2545 debug_info VARCHAR2(4000);
2546 l_gl_date DATE;
2547
2548 BEGIN
2549
2550 l_calling_sequence := 'Overloaded Undo_Accounting api <- '||p_calling_sequence;
2551
2552 debug_info := 'Setting the Variables that need to be passed';
2553 l_source_table := p_source_table;
2554 l_source_id := p_source_id;
2555 l_event_id := p_event_id;
2556 l_bug_id := p_bug_id;
2557 l_gl_date := p_GL_Date;
2558 l_procedure_name := 'AP_ACCTG_DATA_FIX_PKG.undo_accounting';
2559
2560 debug_info := 'Calling the Undo_Accounting with higher number of Arguments';
2561 Undo_Accounting
2562 (p_Source_Table => l_source_table,
2563 p_Source_Id => l_source_id,
2564 p_Event_Id => l_event_id,
2565 p_skip_date_calc => 'N',
2566 p_undo_date => NULL,
2567 p_undo_period => NULL,
2568 p_bug_id => l_bug_id,
2569 p_Gl_Date => l_gl_date,
2570 p_rev_event_id => l_rev_event_id,
2571 p_new_event_id => l_new_event_id,
2572 p_return_code => l_return_code,
2573 p_calling_sequence => l_calling_sequence);
2574
2575 EXCEPTION
2576 WHEN OTHERS THEN
2577 l_log_msg :='Exception in Undo_accounting ';
2578 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2579 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2580 G_MODULE_NAME||l_procedure_name,
2581 l_log_msg);
2582 END IF;
2583 IF (SQLCODE <> - 20001) THEN
2584 fnd_Message.Set_Name('SQLAP','AP_DEBUG');
2585 fnd_Message.Set_Token('ERROR',SQLERRM);
2586 fnd_Message.Set_Token('CALLING_SEQUENCE',p_Calling_Sequence);
2587 fnd_Message.Set_Token('PARAMETERS','p_source_id = '
2588 ||p_Source_Id
2589 ||', p_Source_Table = '
2590 ||p_Source_Table);
2591 fnd_Message.Set_Token('DEBUG_INFO',Debug_Info);
2592 END IF;
2593 app_Exception.Raise_Exception;
2594 END;
2595
2596 PROCEDURE undo_acctg_entries
2597 (p_bug_no IN NUMBER,
2598 p_driver_table IN VARCHAR2,
2599 p_calling_sequence IN VARCHAR2) IS
2600
2601 l_sql_stmt LONG;
2602 l_bug_no VARCHAR2(100);
2603 l_driver_table ALL_TABLES.TABLE_NAME%TYPE;
2604 l_debug_info VARCHAR2(4000);
2605 l_error_log LONG;
2606 l_prev_org_id NUMBER := -99;
2607 l_org_id NUMBER := -99;
2608 l_dummy NUMBER;
2609 l_rev_event_id NUMBER;
2610 l_new_event_id NUMBER;
2611 l_return_code VARCHAR2(1000);
2612 l_event_status_code XLA_EVENTS.EVENT_STATUS_CODE%TYPE;
2613 l_message VARCHAR2(4000);
2614 l_calling_sequence VARCHAR2(4000);
2615
2616 TYPE refcurtyp IS REF CURSOR;
2617 undo_events_cur REFCURTYP;
2618 undo_journals_details REFCURTYP;
2619 undo_failures REFCURTYP;
2620
2621 TYPE kind_t IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
2622 TYPE event_id_t IS TABLE OF XLA_AE_HEADERS.EVENT_ID%TYPE INDEX BY BINARY_INTEGER;
2623 TYPE accounting_class_code_t IS TABLE OF XLA_AE_LINES.ACCOUNTING_CLASS_CODE%TYPE INDEX BY BINARY_INTEGER;
2624 TYPE event_type_code_t IS TABLE OF XLA_AE_HEADERS.EVENT_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
2625 TYPE event_date_t IS TABLE OF XLA_EVENTS.EVENT_DATE%TYPE INDEX BY BINARY_INTEGER;
2626 TYPE ae_header_id_t IS TABLE OF XLA_AE_HEADERS.AE_HEADER_ID%TYPE INDEX BY BINARY_INTEGER;
2627 TYPE balance_type_code_t IS TABLE OF XLA_AE_HEADERS.BALANCE_TYPE_CODE%TYPE INDEX BY BINARY_INTEGER;
2628 TYPE source_id_int_1_t IS TABLE OF XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1%TYPE INDEX BY BINARY_INTEGER;
2629 TYPE org_id_t IS TABLE OF XLA_TRANSACTION_ENTITIES.SECURITY_ID_INT_1%TYPE INDEX BY BINARY_INTEGER;
2630 TYPE transaction_number_t IS TABLE OF XLA_TRANSACTION_ENTITIES.TRANSACTION_NUMBER%TYPE INDEX BY BINARY_INTEGER;
2631 TYPE entity_code_t IS TABLE OF XLA_TRANSACTION_ENTITIES.ENTITY_CODE%TYPE INDEX BY BINARY_INTEGER;
2632 TYPE ae_line_num_t IS TABLE OF XLA_AE_LINES.AE_LINE_NUM%TYPE INDEX BY BINARY_INTEGER;
2633 TYPE padded_concatenated_segments_t IS TABLE OF GL_CODE_COMBINATIONS_KFV.PADDED_CONCATENATED_SEGMENTS%TYPE INDEX BY BINARY_INTEGER;
2634 TYPE entered_dr_t IS TABLE OF XLA_AE_LINES.ENTERED_DR%TYPE INDEX BY BINARY_INTEGER;
2635 TYPE entered_cr_t IS TABLE OF XLA_AE_LINES.ENTERED_CR%TYPE INDEX BY BINARY_INTEGER;
2636 TYPE accounted_dr_t IS TABLE OF XLA_AE_LINES.ACCOUNTED_DR%TYPE INDEX BY BINARY_INTEGER;
2637 TYPE accounted_cr_t IS TABLE OF XLA_AE_LINES.ACCOUNTED_CR%TYPE INDEX BY BINARY_INTEGER;
2638 TYPE description_t IS TABLE OF XLA_AE_LINES.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
2639 TYPE name_t IS TABLE OF GL_LEDGERS.NAME%TYPE INDEX BY BINARY_INTEGER;
2640 TYPE calc_undo_date_t IS TABLE OF XLA_EVENTS.EVENT_DATE%TYPE INDEX BY BINARY_INTEGER;
2641 TYPE calc_undo_period_t IS TABLE OF GL_PERIOD_STATUSES.PERIOD_NAME%TYPE INDEX BY BINARY_INTEGER;
2642 TYPE error_reason_t IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
2643 TYPE budgetary_control_flag_t IS TABLE OF XLA_EVENTS.budgetary_control_flag%TYPE INDEX BY BINARY_INTEGER; /*Bug 10072990*/
2644
2645 TYPE undo_rec_typ IS RECORD
2646 (event_id_l event_id_t,
2647 source_type_l entity_code_t,
2648 source_id_l source_id_int_1_t,
2649 org_id_l org_id_t,
2650 calc_undo_date_l calc_undo_date_t,
2651 calc_undo_period_l calc_undo_period_t,
2652 budgetary_control_flag_l budgetary_control_flag_t);
2653
2654 undo_events_list UNDO_REC_TYP;
2655
2656 TYPE undo_journal_details IS RECORD
2657 (kind_l kind_t,
2658 accounting_class_code_l accounting_class_code_t,
2659 event_type_code_l event_type_code_t,
2660 event_id_l event_id_t,
2661 event_date_l event_date_t,
2662 ae_header_id_l ae_header_id_t,
2663 balance_type_code_l balance_type_code_t,
2664 source_id_int_1_l source_id_int_1_t,
2665 transaction_number_l transaction_number_t,
2666 entity_code_l entity_code_t,
2667 ae_line_num_l ae_line_num_t,
2668 padded_concatenated_segments_l padded_concatenated_segments_t,
2669 entered_dr_l entered_dr_t,
2670 entered_cr_l entered_cr_t,
2671 accounted_dr_l accounted_dr_t,
2672 accounted_cr_l accounted_cr_t,
2673 description_l description_t,
2674 name_l name_t);
2675
2676 undo_journal_dtls_l UNDO_JOURNAL_DETAILS;
2677
2678 TYPE undo_failure_details IS RECORD
2679 (entity_code_l entity_code_t,
2680 source_id_int_1_l source_id_int_1_t,
2681 transaction_number_l transaction_number_t,
2682 event_id_l event_id_t,
2683 calc_undo_date_l calc_undo_date_t,
2684 calc_undo_period_l calc_undo_period_t,
2685 error_reason_l error_reason_t
2686 );
2687
2688 undo_failures_l UNDO_FAILURE_DETAILS;
2689
2690
2691 BEGIN
2692
2693 l_bug_no := p_bug_no;
2694 l_calling_sequence := 'Undo_acctg_entries <- '||p_calling_sequence;
2695
2696 l_debug_info := 'Constructing the name of the driver table';
2697 IF p_driver_table IS NOT NULL THEN
2698 l_driver_table := upper(p_driver_table);
2699 ELSE
2700 l_driver_table := 'AP_TEMP_DATA_DRIVER_'||l_bug_no;
2701 END IF;
2702
2703 l_debug_info := 'Before calling the check_period API';
2704 check_period
2705 (p_bug_no => l_bug_no,
2706 p_driver_table => l_driver_table,
2707 p_check_sysdate => 'Y',
2708 p_check_event_date => 'Y',
2709 p_chk_proposed_undo_date => 'Y',
2710 p_update_process_flag => 'Y',
2711 p_calc_undo_date => 'Y',
2712 p_commit_flag => 'Y',
2713 p_calling_sequence => l_calling_sequence);
2714
2715 l_debug_info := 'Before calling the check_ccid api';
2716 check_ccid
2717 (p_bug_no => l_bug_no,
2718 p_driver_table => l_driver_table,
2719 p_update_process_flag => 'Y',
2720 p_commit_flag => 'Y',
2721 p_calling_sequence => l_calling_sequence);
2722
2723 l_debug_info := 'Checking the presence of the O/P values on the '||
2724 'temp data driver';
2725 BEGIN
2726 SELECT 1
2727 INTO l_dummy
2728 FROM sys.all_tab_columns
2729 WHERE table_name = l_driver_table
2730 AND column_name = 'REVERSAL_EVENT_ID';
2731
2732 EXCEPTION
2733 WHEN OTHERS THEN
2734 l_dummy := 0;
2735 END;
2736
2737 IF l_dummy = 0 THEN
2738 l_sql_stmt := ' ALTER TABLE '||l_driver_table||
2739 ' ADD (REVERSAL_EVENT_ID NUMBER, '||
2740 ' NEW_EVENT_ID NUMBER, '||
2741 ' RETURN_STATUS VARCHAR2(1000)) ';
2742 EXECUTE IMMEDIATE l_sql_stmt;
2743 END IF;
2744
2745 l_debug_info := 'Constructing the dynamic sql statement ';
2746 l_sql_stmt := ' SELECT DISTINCT '||
2747 ' xte.entity_code, '||
2748 ' xte.source_id_int_1, '||
2749 ' xe.event_id, '||
2750 ' xte.security_id_int_1, '||
2751 ' dr.calculated_undo_date, '||
2752 ' dr.calculated_undo_period, '||
2753 ' xe.budgetary_control_flag '|| --Bug 10072990
2754 ' FROM xla_transaction_entities_upg xte, '||
2755 ' xla_events xe, '||
2756 l_driver_table||' dr '||
2757 ' WHERE xte.application_id = 200 '||
2758 ' AND xe.application_id = 200 '||
2759 ' AND dr.event_id = xe.event_id '||
2760 ' AND xe.entity_id = xte.entity_id '||
2761 ' AND dr.process_flag = ''Y'' '||
2762 ' AND xe.event_status_code = ''P'' '||
2763 ' AND xe.event_type_code <> ''MANUAL'' '||
2764 ' ORDER BY decode(nvl(xe.budgetary_control_flag,''N''),''N'',1,2) ' ||
2765 ' , xte.security_id_int_1 ' ; --Bug 10072990
2766
2767 l_debug_info := 'After the l_sql_stmt, before opening the cursor';
2768 OPEN undo_events_cur FOR l_sql_stmt;
2769 LOOP
2770 FETCH undo_events_cur
2771 BULK COLLECT INTO undo_events_list.source_type_l,
2772 undo_events_list.source_id_l,
2773 undo_events_list.event_id_l,
2774 undo_events_list.org_id_l,
2775 undo_events_list.calc_undo_date_l,
2776 undo_events_list.calc_undo_period_l,
2777 undo_events_list.budgetary_control_flag_l LIMIT 1000; --Bug 10072990
2778
2779 l_debug_info := 'After the fetch, before looping for the batch of 1000';
2780 IF undo_events_list.event_id_l.COUNT > 0 THEN
2781 FOR i IN undo_events_list.event_id_l.FIRST..undo_events_list.event_id_l.LAST LOOP
2782
2783 SAVEPOINT BEFORE_UNDO;
2784 BEGIN
2785
2786 l_debug_info := 'Fetching the org_id';
2787 l_org_id := undo_events_list.org_id_l(i);
2788
2789 IF l_org_id <> l_prev_org_id THEN
2790 l_debug_info := 'Setting the org context to org_id '||l_org_id;
2791 MO_GLOBAL.set_policy_context('S', l_org_id);
2792 END IF;
2793
2794 l_debug_info := 'Before calling the undo_accounting api';
2795 IF nvl(undo_events_list.budgetary_control_flag_l(i), 'N') <> 'Y' THEN --Bug 10072990, Bug12407622
2796 Undo_Accounting
2797 (p_Source_Table => undo_events_list.source_type_l(i),
2798 p_Source_Id => undo_events_list.source_id_l(i),
2799 p_Event_Id => undo_events_list.event_id_l(i),
2800 p_Skip_Date_Calc => 'Y',
2801 p_undo_date => undo_events_list.calc_undo_date_l(i),
2802 p_undo_period => undo_events_list.calc_undo_period_l(i),
2803 p_bug_id => l_bug_no,
2804 p_Gl_Date => NULL,
2805 P_rev_event_id => l_rev_event_id,
2806 P_new_event_id => l_new_event_id,
2807 P_return_code => l_return_code,
2808 P_calling_sequence => l_calling_sequence);
2809 ELSE --Bug 10072990
2810 undo_inv_accounting
2811 (p_source_table => undo_events_list.source_type_l(i),
2812 p_source_id => undo_events_list.source_id_l(i),
2813 p_event_id => undo_events_list.event_id_l(i),
2814 p_skip_date_calc => 'Y',
2815 p_undo_date => undo_events_list.calc_undo_date_l(i),
2816 p_undo_period => undo_events_list.calc_undo_period_l(i),
2817 p_bug_id => l_bug_no,
2818 p_gl_date => NULL,
2819 p_rev_event_id => l_rev_event_id,
2820 p_new_event_id => l_new_event_id,
2821 p_return_code => l_return_code,
2822 p_calling_sequence => l_calling_sequence);
2823
2824 END IF;
2825
2826 l_debug_info := 'Resetting the prev org context ';
2827 l_prev_org_id := l_org_id;
2828
2829 EXCEPTION
2830 WHEN OTHERS THEN
2831 l_prev_org_id := -99;
2832 l_message := 'Event_id '||undo_events_list.event_id_l(i)||' Could not be processed '||
2833 'because of unexpected error ';
2834 ROLLBACK TO BEFORE_UNDO;
2835 END;
2836
2837 l_sql_stmt :=
2838 ' UPDATE '||l_driver_table||
2839 ' SET reversal_event_id = :b1, '||
2840 ' new_event_id = :b2, '||
2841 ' return_status = :b3 '||
2842 ' WHERE event_id = :b4 ';
2843
2844 EXECUTE IMMEDIATE l_sql_stmt USING l_rev_event_id,
2845 l_new_event_id,
2846 l_return_code,
2847 undo_events_list.event_id_l(i);
2848 -- A commit is required here, if not committed, then in case there
2849 -- is an XLA exception while undo, XLA issues a blind Rollback, which
2850 -- causes all the events which were successfully undone to be rolled
2851 -- back
2852 --
2853 COMMIT;
2854
2855 END LOOP;
2856 END IF;
2857 EXIT WHEN undo_events_cur%NOTFOUND;
2858 END LOOP;
2859
2860 l_message := '<b><u>Following are the details of the Original and the Reversal '||
2861 'Entries for the Events successfully Unaccounted</u></b>';
2862 Print(l_message);
2863
2864 l_debug_info := ' Printing the details of the Original and the reversal '||
2865 ' Journal Entries ';
2866 l_message := '<table border="5">'||
2867 '<th>LEDGER_NAME</th>'||
2868 '<th>KIND</th>'||
2869 '<th>ACCOUNTING_CLASS_CODE</th>'||
2870 '<th>EVENT_TYPE_CODE</th>'||
2871 '<th>EVENT_ID</th>'||
2872 '<th>EVENT_DATE</th>'||
2873 '<th>AE_HEADER_ID</th>'||
2874 '<th>BALANCE_TYPE_CODE</th>'||
2875 '<th>SOURCE_ID_INT_1</th>'||
2876 '<th>TRANSACTION_NUMBER</th>'||
2877 '<th>ENTITY_CODE</th>'||
2878 '<th>AE_LINE_NUM</th>'||
2879 '<th>PADDED_CONCATENATED_SEGMENTS</th>'||
2880 '<th>ENTERED_DR</th>'||
2881 '<th>ENTERED_CR</th>'||
2882 '<th>ACCOUNTED_DR</th>'||
2883 '<th>ACCOUNTED_CR</th>'||
2884 '<th>DESCRIPTION</th>';
2885
2886 print(l_message);
2887 l_sql_stmt :=
2888 ' SELECT v1.kind, '||
2889 ' v1.accounting_class_code, '||
2890 ' v1.event_type_code, '||
2891 ' v1.event_id, '||
2892 ' v1.event_date, '||
2893 ' v1.ae_header_id, '||
2894 ' v1.balance_type_code, '||
2895 ' v1.source_id_int_1, '||
2896 ' v1.transaction_number, '||
2897 ' v1.entity_code, '||
2898 ' v1.ae_line_num, '||
2899 ' v1.padded_concatenated_segments, '||
2900 ' v1.entered_dr, '||
2901 ' v1.entered_cr, '||
2902 ' v1.accounted_dr, '||
2903 ' v1.accounted_cr, '||
2904 ' v1.description, '||
2905 ' v1.name '||
2906 ' FROM '||
2907 ' ( '||
2908 ' SELECT ''OLD'' KIND, '||
2909 ' xal.accounting_class_code, '||
2910 ' xah.event_type_code, '||
2911 ' xah.event_id, '||
2912 ' xe.event_date, '||
2913 ' xah.ae_header_id, '||
2914 ' xah.balance_type_code, '||
2915 ' xah.accounting_date, '||
2916 ' xte.source_id_int_1, '||
2917 ' xte.transaction_number, '||
2918 ' xte.entity_code, '||
2919 ' xal.ae_line_num, '||
2920 ' gcc.padded_concatenated_segments, '||
2921 ' xal.entered_dr, '||
2922 ' xal.entered_cr, '||
2923 ' xal.accounted_dr, '||
2924 ' xal.accounted_cr, '||
2925 ' xal.description, '||
2926 ' gl.name '||
2927 ' FROM xla_events xe, '||
2928 ' xla_ae_headers xah, '||
2929 ' xla_ae_lines xal, '||
2930 ' xla_transaction_entities_upg xte, '||
2931 ' gl_code_combinations_kfv gcc, '||
2932 ' gl_ledgers gl '||
2933 ' WHERE xe.application_id = 200 '||
2934 ' AND xah.application_id = 200 '||
2935 ' AND xal.application_id = 200 '||
2936 ' AND xte.application_id = 200 '||
2937 ' AND xe.event_id = xah.event_id '||
2938 ' AND xe.entity_id = xte.entity_id '||
2939 ' AND xah.ae_header_id = xal.ae_header_id '||
2940 ' AND xah.ledger_id = gl.ledger_id '||
2941 ' AND xal.code_combination_id = gcc.code_combination_id '||
2942 ' AND xah.event_id IN '||
2943 ' (SELECT DISTINCT dr.new_event_id '||
2944 ' FROM '||l_driver_table||' dr, '||
2945 ' xla_events xe '||
2946 ' WHERE xe.application_id = 200 '||
2947 ' AND dr.event_id = xe.event_id '||
2948 ' AND xe.event_status_code <> ''MANUAL'' '||
2949 ' AND dr.process_flag = ''Y'' '||
2950 ' AND xe.event_status_code <> ''P'' '||
2951 ' ) '||
2952 ' UNION ALL '||
2953 ' SELECT ''REVERSAL'' KIND, '||
2954 ' xal.accounting_class_code, '||
2955 ' xah.event_type_code, '||
2956 ' xah.event_id, '||
2957 ' xe.event_date, '||
2958 ' xah.ae_header_id, '||
2959 ' xah.balance_type_code, '||
2960 ' xah.accounting_date, '||
2961 ' xte.source_id_int_1, '||
2962 ' xte.transaction_number, '||
2963 ' xte.entity_code, '||
2964 ' xal.ae_line_num, '||
2965 ' gcc.padded_concatenated_segments, '||
2966 ' xal.entered_dr, '||
2967 ' xal.entered_cr, '||
2968 ' xal.accounted_dr, '||
2969 ' xal.accounted_cr, '||
2970 ' xal.description, '||
2971 ' gl.name '||
2972 ' FROM xla_events xe, '||
2973 ' xla_ae_headers xah, '||
2974 ' xla_ae_lines xal, '||
2975 ' xla_transaction_entities_upg xte, '||
2976 ' gl_code_combinations_kfv gcc, '||
2977 ' gl_ledgers gl '||
2978 ' WHERE xe.application_id = 200 '||
2979 ' AND xah.application_id = 200 '||
2980 ' AND xal.application_id = 200 '||
2981 ' AND xte.application_id = 200 '||
2982 ' AND xe.event_id = xah.event_id '||
2983 ' AND xah.ledger_id = gl.ledger_id '||
2984 ' AND xe.entity_id = xte.entity_id '||
2985 ' AND xah.ae_header_id = xal.ae_header_id '||
2986 ' AND xe.event_type_code = ''MANUAL'' '||
2987 ' AND xal.code_combination_id = gcc.code_combination_id '||
2988 ' AND xe.event_id IN '||
2989 ' (SELECT DISTINCT dr.reversal_event_id '||
2990 ' FROM '||l_driver_table||' dr, '||
2991 ' xla_events xe '||
2992 ' WHERE xe.application_id = 200 '||
2993 ' AND dr.event_id = xe.event_id '||
2994 ' AND xe.event_status_code <> ''MANUAL'' '||
2995 ' AND dr.process_flag = ''Y'' '||
2996 ' AND xe.event_status_code <> ''P'' '||
2997 ' ) '||
2998 ' ) v1 '||
2999 ' ORDER BY v1.entity_code, '||
3000 ' v1.source_id_int_1, '||
3001 ' v1.KIND, '||
3002 ' v1.event_type_code, '||
3003 ' v1.ae_header_id, '||
3004 ' v1.balance_type_code, '||
3005 ' v1.ae_line_num ';
3006
3007 l_debug_info := 'Before Opening the cursor for Printing the details '||
3008 'of the Original and Reversal Entries ';
3009 OPEN undo_journals_details FOR l_sql_stmt;
3010 LOOP
3011
3012 l_debug_info := 'Before fetch for a batchsize for Printing details of Original and Rev';
3013 FETCH undo_journals_details
3014 BULK COLLECT INTO undo_journal_dtls_l.kind_l,
3015 undo_journal_dtls_l.accounting_class_code_l,
3016 undo_journal_dtls_l.event_type_code_l,
3017 undo_journal_dtls_l.event_id_l,
3018 undo_journal_dtls_l.event_date_l,
3019 undo_journal_dtls_l.ae_header_id_l,
3020 undo_journal_dtls_l.balance_type_code_l,
3021 undo_journal_dtls_l.source_id_int_1_l,
3022 undo_journal_dtls_l.transaction_number_l,
3023 undo_journal_dtls_l.entity_code_l,
3024 undo_journal_dtls_l.ae_line_num_l,
3025 undo_journal_dtls_l.padded_concatenated_segments_l,
3026 undo_journal_dtls_l.entered_dr_l,
3027 undo_journal_dtls_l.entered_cr_l,
3028 undo_journal_dtls_l.accounted_dr_l,
3029 undo_journal_dtls_l.accounted_cr_l,
3030 undo_journal_dtls_l.description_l,
3031 undo_journal_dtls_l.name_l LIMIT 1000;
3032
3033 l_debug_info := 'Before looping for the batchsize';
3034 IF undo_journal_dtls_l.event_id_l.COUNT > 0 THEN
3035 FOR i IN undo_journal_dtls_l.event_id_l.FIRST..undo_journal_dtls_l.event_id_l.LAST LOOP
3036 l_message :=
3037 '<tr><td>'||undo_journal_dtls_l.name_l(i)||'</td><td>'||
3038 undo_journal_dtls_l.kind_l(i)||'</td><td>'||
3039 undo_journal_dtls_l.accounting_class_code_l(i)||'</td><td>'||
3040 undo_journal_dtls_l.event_type_code_l(i)||'</td><td>'||
3041 to_char(undo_journal_dtls_l.event_id_l(i))||'</td><td>'||
3042 to_char(undo_journal_dtls_l.event_date_l(i), 'DD-MON-YYYY')||'</td><td>'||
3043 to_char(undo_journal_dtls_l.ae_header_id_l(i))||'</td><td>'||
3044 undo_journal_dtls_l.balance_type_code_l(i)||'</td><td>'||
3045 to_char(undo_journal_dtls_l.source_id_int_1_l(i))||'</td><td>'||
3046 undo_journal_dtls_l.transaction_number_l(i)||'</td><td>'||
3047 undo_journal_dtls_l.entity_code_l(i)||'</td><td>'||
3048 to_char(undo_journal_dtls_l.ae_line_num_l(i))||'</td><td>'||
3049 undo_journal_dtls_l.padded_concatenated_segments_l(i)||'</td><td>'||
3050 to_char(undo_journal_dtls_l.entered_dr_l(i))||'</td><td>'||
3051 to_char(undo_journal_dtls_l.entered_cr_l(i))||'</td><td>'||
3052 to_char(undo_journal_dtls_l.accounted_dr_l(i))||'</td><td>'||
3053 to_char(undo_journal_dtls_l.accounted_cr_l(i))||'</td><td>'||
3054 undo_journal_dtls_l.description_l(i)||'</td><td>';
3055 print(l_message);
3056 END LOOP;
3057 END IF;
3058 EXIT WHEN undo_journals_details%NOTFOUND;
3059 END LOOP;
3060
3061 l_debug_info := 'After Printing the details of the Original and the Reversal Entries';
3062
3063 l_message := '</table>';
3064 print(l_message);
3065
3066
3067 l_message := '<b><u>Following are the details of the XLA events for which '||
3068 'there was an error while Unaccounting</u></b>';
3069 Print(l_message);
3070
3071 l_debug_info := ' Printing the details of the transactions for which the Undo '||
3072 ' was not successful, and the reasons for the same ';
3073 l_sql_stmt := ' SELECT DISTINCT '||
3074 ' xte.entity_code, '||
3075 ' xte.source_id_int_1, '||
3076 ' xte.transaction_number, '||
3077 ' xe.event_id, '||
3078 ' dr.calculated_undo_date, '||
3079 ' dr.calculated_undo_period, '||
3080 ' decode(dr.return_status, '||
3081 ' ''XLA_ERROR'', ''XLA Undo API Error'', '||
3082 ' ''XLA_EXCEPTION'', ''XLA Undo API throws Exception'', '||
3083 ' ''XLA_NO_WORK'', ''XLA API did not Work'', '||
3084 ' ''AP_PAYMENT_ERROR'', ''Exception while updating AP Payments'', '||
3085 ' ''AP_INVOICE_ERROR'', ''Exception while updating AP Invoices'', '||
3086 ' ''UNEXPECTED_EXCEPTION'', ''Unexpected Exception Occurred'', '||
3087 ' ''SUCCESS'', ''Success'', '||
3088 ' ''Relevant Error Not Found '' '||
3089 ' ) '||
3090 ' FROM xla_transaction_entities_upg xte, '||
3091 ' xla_events xe, '||
3092 l_driver_table||' dr '||
3093 ' WHERE xte.application_id = 200 '||
3094 ' AND xe.application_id = 200 '||
3095 ' AND dr.event_id = xe.event_id '||
3096 ' AND xe.entity_id = xte.entity_id '||
3097 ' AND dr.process_flag = ''Y'' '||
3098 ' AND xe.event_status_code = ''P'' '||
3099 ' AND xe.event_type_code <> ''MANUAL'' ';
3100
3101 l_message := '<table border="5">'||
3102 '<th>SOURCE_TYPE</th>'||
3103 '<th>TRANSACTION_ID</th>'||
3104 '<th>TRANSACTION_NUMBER</th>'||
3105 '<th>EVENT_ID</th>'||
3106 '<th>UNDO_DATE</th>'||
3107 '<th>UNDO_PERIOD</th>'||
3108 '<th>ERROR_REASON</th>';
3109 Print(l_message);
3110
3111 l_debug_info := 'Before Opening the cursor for Printing the failures';
3112 OPEN undo_failures FOR l_sql_stmt;
3113 LOOP
3114 l_debug_info := 'Inside the Loop before fetching the batchsize';
3115 FETCH undo_failures
3116 BULK COLLECT INTO undo_failures_l.entity_code_l,
3117 undo_failures_l.source_id_int_1_l,
3118 undo_failures_l.transaction_number_l,
3119 undo_failures_l.event_id_l,
3120 undo_failures_l.calc_undo_date_l,
3121 undo_failures_l.calc_undo_period_l,
3122 undo_failures_l.error_reason_l LIMIT 1000;
3123
3124 l_debug_info := 'After the fetch before looping for the batchsize';
3125 IF undo_failures_l.event_id_l.COUNT > 0 THEN
3126 FOR i IN undo_failures_l.event_id_l.FIRST..undo_failures_l.event_id_l.LAST LOOP
3127 l_debug_info := 'Constructing the failure message for the event_id '||undo_failures_l.event_id_l(i);
3128 l_message :=
3129 '<tr><td>'||undo_failures_l.entity_code_l(i)||'</td><td>'||
3130 to_char(undo_failures_l.source_id_int_1_l(i))||'</td><td>'||
3131 undo_failures_l.transaction_number_l(i)||'</td><td>'||
3132 to_char(undo_failures_l.event_id_l(i))||'</td><td>'||
3133 to_char(undo_failures_l.calc_undo_date_l(i), 'DD-MON-YYYY')||'</td><td>'||
3134 undo_failures_l.calc_undo_period_l(i)||'</td><td>'||
3135 undo_failures_l.error_reason_l(i)||'</td><td>';
3136 Print(l_message);
3137 END LOOP;
3138 END IF;
3139 EXIT WHEN undo_failures%NOTFOUND;
3140 l_debug_info := 'After Processing One batch';
3141 END LOOP;
3142 l_debug_info := 'After processing the failures';
3143
3144 l_debug_info := 'Marking all the events for which Undo has been successful to D ';
3145 --Bug 10072990
3146 l_sql_stmt :=
3147 'UPDATE '||l_driver_table||' dr '||
3148 ' SET dr.process_flag = ''D'' '||
3149 ' WHERE dr.process_flag = ''Y'' '||
3150 ' AND (dr.event_id IN '||
3151 ' (SELECT xe.event_id '||
3152 ' FROM xla_events xe, '||
3153 l_driver_table||' dr1 '||
3154 ' WHERE xe.application_id = 200 '||
3155 ' AND xe.event_status_code <> ''P'' '||
3156 ' AND xe.event_id = dr1.event_id) ' ||
3157 ' OR NOT EXISTS ' ||
3158 ' (SELECT 1 ' ||
3159 ' FROM xla_events xe ' ||
3160 ' WHERE xe.event_id = dr.event_id ' ||
3161 ' AND xe.application_id =200 ))';
3162
3163 EXECUTE IMMEDIATE l_sql_stmt;
3164 COMMIT;
3165
3166 l_message := '</table>';
3167 print(l_message);
3168
3169 EXCEPTION
3170 WHEN OTHERS THEN
3171 IF SQLCODE <> -20001 THEN
3172 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
3173 ' in '||l_calling_sequence||' while performing '||l_debug_info;
3174 Print(l_error_log);
3175 END IF;
3176 APP_EXCEPTION.RAISE_EXCEPTION();
3177 END;
3178
3179 PROCEDURE push_error(p_error_code IN VARCHAR2,
3180 p_error_stack IN OUT NOCOPY Rejection_List_Tab_Typ) IS
3181
3182 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3183 l_procedure_name CONSTANT VARCHAR2(30) := 'Push_error';
3184 l_index NUMBER;
3185
3186 BEGIN
3187
3188 l_index := p_error_stack.COUNT;
3189
3190 l_log_msg := ' Current Stack Count : '||l_index||
3191 ' Pushing the error :'||p_error_code||
3192 ' in the stack';
3193
3194 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3195 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3196 G_MODULE_NAME||l_procedure_name,
3197 l_log_msg);
3198 END IF;
3199
3200 p_error_stack(l_index + 1) := p_error_code;
3201
3202 END;
3203
3204
3205 PROCEDURE final_pay_round_dfix
3206 (p_invoice_id IN NUMBER,
3207 p_op_event_id OUT NOCOPY NUMBER,
3208 p_op_event_type OUT NOCOPY VARCHAR2,
3209 p_return_status OUT NOCOPY BOOLEAN,
3210 p_rejection_tab OUT NOCOPY Rejection_List_Tab_Typ,
3211 p_rej_count OUT NOCOPY NUMBER,
3212 p_error_msg OUT NOCOPY VARCHAR2,
3213 p_pay_dist_tab OUT NOCOPY Pay_Dist_Tab_Typ,
3214 p_prepay_dist_tab OUT NOCOPY Prepay_Dist_Tab_Typ,
3215 p_commit_flag IN VARCHAR2,
3216 p_calling_sequence IN VARCHAR2) IS
3217
3218 l_dummy NUMBER;
3219 l_org_id AP_INVOICES_ALL.org_id%TYPE;
3220 l_count_hist_pay NUMBER;
3221 l_cnt_unacc_inv_evnts NUMBER;
3222 l_cnt_unacc_pay_evnts NUMBER;
3223 l_cnt_untrx_inv_evnts NUMBER;
3224 l_cnt_untrx_pay_evnts NUMBER;
3225 l_invoice_id NUMBER;
3226 l_check_id NUMBER;
3227 l_is_final_pay BOOLEAN;
3228 l_rej_count NUMBER;
3229 l_reject_code VARCHAR2(100);
3230 l_index NUMBER := 0;
3231 l_max_event_id XLA_EVENTS.event_id%TYPE;
3232 l_op_event_type XLA_EVENTS.EVENT_TYPE_CODE%TYPE;
3233 l_max_pay_dist_id NUMBER;
3234 l_max_prepay_dist_id NUMBER;
3235 l_return_status BOOLEAN;
3236 l_validation_status VARCHAR2(100);
3237 l_error_code VARCHAR2(100);
3238 l_error_log LONG;
3239
3240 l_inv_rec ap_accounting_pay_pkg.r_invoices_info;
3241 l_xla_event_rec ap_accounting_pay_pkg.r_xla_event_info;
3242 l_pay_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
3243 l_clr_hist_rec ap_accounting_pay_pkg.r_pay_hist_info;
3244 l_inv_pay_rec ap_acctg_pay_dist_pkg.r_inv_pay_info;
3245 l_prepay_inv_rec ap_accounting_pay_pkg.r_invoices_info;
3246 l_prepay_hist_rec AP_ACCTG_PREPAY_DIST_PKG.r_prepay_hist_info;
3247 l_prepay_dist_rec AP_ACCTG_PREPAY_DIST_PKG.r_prepay_dist_info;
3248
3249 l_debug_info VARCHAR2(4000);
3250 l_calling_sequence VARCHAR2(4000);
3251
3252 l_pay_dist_tab Pay_Dist_Tab_Typ;
3253 l_prepay_dist_tab Prepay_Dist_Tab_Typ;
3254 l_rejection_tab Rejection_List_Tab_Typ;
3255
3256 l_procedure_name CONSTANT VARCHAR2(30) := 'Final_Pay_Round_Dfix';
3257 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3258
3259 -- bug9716573, added budgetary control flag to the cursor
3260 -- bug11772495, removed budgetary control flag from the cursor
3261 --
3262 CURSOR xla_evnt_dtls(p_event_id NUMBER) IS
3263 SELECT XE.Event_ID,
3264 XE.Event_Type_Code,
3265 XE.Event_Date,
3266 XE.Event_Number,
3267 XE.Event_Status_Code,
3268 XTE.Entity_Code,
3269 XTE.Source_ID_Int_1
3270 FROM xla_events XE,
3271 xla_transaction_entities_upg XTE
3272 WHERE XE.application_id = 200
3273 AND XTE.application_id = 200
3274 AND XE.entity_id = XTE.entity_id
3275 AND XE.event_id = p_event_id;
3276
3277 CURSOR Invoice_Payments
3278 (P_Invoice_id NUMBER,
3279 P_Check_id NUMBER,
3280 P_Event_id NUMBER) IS
3281 SELECT AIP.Invoice_ID,
3282 AIP.Invoice_Payment_ID,
3283 AIP.Amount,
3284 AIP.Discount_Taken,
3285 AIP.Payment_Base_Amount,
3286 AIP.Invoice_Base_Amount,
3287 AIP.Exchange_Rate_Type,
3288 AIP.Exchange_Date,
3289 AIP.Exchange_Rate,
3290 NVL(AIP.Reversal_Flag,'N'),
3291 AIP.Reversal_Inv_Pmt_ID
3292 FROM Ap_Invoice_Payments_All AIP
3293 WHERE AIP.invoice_id = P_Invoice_id
3294 AND AIP.accounting_event_id = nvl(P_Event_id, AIP.accounting_event_id)
3295 AND AIP.check_id = nvl(P_Check_id, AIP.check_id)
3296 AND nvl(AIP.reversal_flag, 'N') <> 'Y';
3297
3298 CURSOR Prepay_History
3299 (P_Invoice_ID NUMBER,
3300 P_event_id NUMBER
3301 ) IS
3302 SELECT APH.Prepay_History_ID,
3303 APH.Prepay_Invoice_ID,
3304 APH.Invoice_ID,
3305 APH.Invoice_Line_Number,
3306 APH.Transaction_Type,
3307 APH.Accounting_Date,
3308 APH.Invoice_Adjustment_Event_ID,
3309 APH.Related_Prepay_App_Event_ID
3310 FROM AP_Prepay_History_All APH
3311 WHERE APH.Invoice_ID = P_Invoice_ID
3312 AND APH.accounting_event_id = P_event_id
3313 ORDER BY transaction_type;
3314
3315 CURSOR Prepay_Dists
3316 (P_Invoice_ID NUMBER,
3317 P_event_id NUMBER
3318 ) IS
3319 SELECT AID.Invoice_ID,
3320 AID.Invoice_Distribution_ID Invoice_Distribution_ID,
3321 AID.Line_Type_Lookup_Code,
3322 AID.Amount,
3323 AID.Base_Amount,
3324 AID.Accounting_Event_ID,
3325 AID.Prepay_Distribution_ID,
3326 AID.Prepay_Tax_Diff_Amount,
3327 AID.Parent_Reversal_ID
3328 FROM AP_Invoice_Distributions_All AID
3329 WHERE Invoice_ID = P_Invoice_ID
3330 AND Line_Type_Lookup_Code = 'PREPAY'
3331 AND Accounting_Event_ID = P_event_id
3332 ORDER BY abs(AID.amount) DESC;
3333
3334 CURSOR new_round_pay_dists(p_event_id NUMBER,
3335 p_max_pay_dist_id NUMBER)
3336 IS
3337 SELECT aphd.payment_hist_dist_id,
3338 aphd.accounting_event_id,
3339 aphd.pay_dist_lookup_code,
3340 aphd.invoice_distribution_id,
3341 aphd.amount,
3342 aphd.payment_history_id,
3343 aphd.invoice_payment_id,
3344 aphd.bank_curr_amount,
3345 aphd.cleared_base_amount,
3346 aphd.historical_flag,
3347 aphd.invoice_dist_amount,
3348 aphd.invoice_dist_base_amount,
3349 aphd.invoice_adjustment_event_id,
3350 aphd.matured_base_amount,
3351 aphd.paid_base_amount,
3352 aphd.rounding_amt,
3353 aphd.reversal_flag,
3354 aphd.reversed_pay_hist_dist_id,
3355 aphd.created_by,
3356 aphd.creation_date,
3357 aphd.last_update_date,
3358 aphd.last_updated_by,
3359 aphd.last_update_login,
3360 aphd.program_application_id,
3361 aphd.program_id,
3362 aphd.program_login_id,
3363 aphd.program_update_date,
3364 aphd.request_id,
3365 aphd.awt_related_id,
3366 aphd.release_inv_dist_derived_from,
3367 aphd.pa_addition_flag,
3368 aphd.amount_variance,
3369 aphd.invoice_base_amt_variance,
3370 aphd.quantity_variance,
3371 aphd.invoice_base_qty_variance,
3372 DECODE(asp.automatic_offsets_flag,
3373 'Y',DECODE(asp.liability_post_lookup_code,
3374 'ACCOUNT_SEGMENT_VALUE', aid.dist_code_combination_id,
3375 asp.rounding_error_ccid),
3376 asp.rounding_error_ccid
3377 ) write_off_code_combination
3378 FROM ap_payment_hist_dists aphd,
3379 ap_payment_history_all aph,
3380 ap_system_parameters_all asp,
3381 ap_invoice_distributions_all aid
3382 WHERE aphd.accounting_event_id = p_event_id
3383 AND aph.payment_history_id = aphd.payment_history_id
3384 AND aph.org_id = asp.org_id
3385 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
3386 AND aphd.payment_hist_dist_id > p_max_pay_dist_id;
3387
3388 CURSOR new_round_prepay_dists(p_event_id NUMBER,
3389 p_max_prepay_dist_id NUMBER)
3390 IS
3391 SELECT apad.prepay_app_dist_id,
3392 apad.prepay_dist_lookup_code,
3393 apad.invoice_distribution_id,
3394 apad.prepay_app_distribution_id,
3395 apad.accounting_event_id,
3396 apad.prepay_history_id,
3397 apad.prepay_exchange_date,
3398 apad.prepay_pay_exchange_date,
3399 apad.prepay_clr_exchange_date,
3400 apad.prepay_exchange_rate,
3401 apad.prepay_pay_exchange_rate,
3402 apad.prepay_clr_exchange_rate,
3403 apad.prepay_exchange_rate_type,
3404 apad.prepay_pay_exchange_rate_type,
3405 apad.prepay_clr_exchange_rate_type,
3406 apad.reversed_prepay_app_dist_id,
3407 apad.amount,
3408 apad.base_amt_at_prepay_xrate,
3409 apad.base_amt_at_prepay_pay_xrate,
3410 apad.base_amount,
3411 apad.base_amt_at_prepay_clr_xrate,
3412 apad.rounding_amt,
3413 apad.round_amt_at_prepay_xrate,
3414 apad.round_amt_at_prepay_pay_xrate,
3415 apad.round_amt_at_prepay_clr_xrate,
3416 apad.last_updated_by,
3417 apad.last_update_date,
3418 apad.last_update_login,
3419 apad.created_by,
3420 apad.creation_date,
3421 apad.program_application_id,
3422 apad.program_id,
3423 apad.program_update_date,
3424 apad.request_id,
3425 apad.awt_related_id,
3426 apad.release_inv_dist_derived_from,
3427 apad.pa_addition_flag,
3428 apad.bc_event_id,
3429 apad.amount_variance,
3430 apad.invoice_base_amt_variance,
3431 apad.quantity_variance,
3432 apad.invoice_base_qty_variance,
3433 DECODE(asp.automatic_offsets_flag,
3434 'Y',DECODE(asp.liability_post_lookup_code,
3435 'ACCOUNT_SEGMENT_VALUE', aid.dist_code_combination_id,
3436 asp.rounding_error_ccid),
3437 asp.rounding_error_ccid
3438 ) write_off_code_combination
3439 FROM ap_prepay_app_dists apad,
3440 ap_prepay_history_all apph,
3441 ap_system_parameters_all asp,
3442 ap_invoice_distributions_all aid
3443 WHERE apad.accounting_event_id = P_Event_Id
3444 AND apad.prepay_history_id = apph.prepay_history_id
3445 AND apph.org_id = asp.org_id
3446 AND apad.invoice_distribution_id = aid.invoice_distribution_id
3447 AND apad.prepay_app_dist_id > P_Max_Prepay_Dist_id;
3448
3449
3450 BEGIN
3451
3452
3453 l_calling_sequence := 'Final_Pay_Round_Dfix <- '||P_Calling_Sequence;
3454 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3455
3456 l_log_msg := 'Procedure Begins';
3457
3458 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3459 FND_LOG.STRING(G_LEVEL_PROCEDURE,
3460 G_MODULE_NAME||l_procedure_name,
3461 l_log_msg);
3462 END IF;
3463
3464 l_debug_info := 'Before Verifying if the Invoice is Valid';
3465 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3466
3467 FND_LOG.STRING(G_LEVEL_STATEMENT,
3468 G_MODULE_NAME||l_procedure_name,
3469 l_debug_info);
3470 END IF;
3471
3472 l_invoice_id := p_invoice_id;
3473
3474 BEGIN
3475
3476 SELECT 1
3477 INTO l_dummy
3478 FROM ap_invoices_all
3479 WHERE invoice_id = l_invoice_id;
3480
3481 EXCEPTION
3482 WHEN OTHERS THEN
3483 l_reject_code := 'INVALID INVOICE';
3484 Push_Error(p_error_code => l_reject_code,
3485 p_error_stack => p_rejection_tab);
3486
3487 p_return_status := FALSE;
3488 p_rej_count := p_rejection_tab.COUNT;
3489
3490 RETURN;
3491 END;
3492
3493
3494 l_debug_info := 'Before getting the operating unit for the Invoice';
3495 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3496 FND_LOG.STRING(G_LEVEL_STATEMENT,
3497 G_MODULE_NAME||l_procedure_name,
3498 l_debug_info);
3499 END IF;
3500
3501 SELECT org_id
3502 INTO l_org_id
3503 FROM ap_invoices_all
3504 WHERE invoice_id = l_invoice_id;
3505
3506 l_debug_info := 'Before setting the org context for the Invoice';
3507 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3508 FND_LOG.STRING(G_LEVEL_STATEMENT,
3509 G_MODULE_NAME||l_procedure_name,
3510 l_debug_info);
3511 END IF;
3512
3513 MO_GLOBAL.set_policy_context('S', l_org_id);
3514
3515
3516 -- Proceed with the remainder of the Validations
3517 -- All the Validations would be carried out, and the
3518 -- error messages would be pushed into the stack
3519 --
3520
3521 l_debug_info := 'Before checking for the Validation status of the Invoice';
3522 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3523 FND_LOG.STRING(G_LEVEL_STATEMENT,
3524 G_MODULE_NAME||l_procedure_name,
3525 l_debug_info);
3526 END IF;
3527
3528 SELECT ap_invoices_utility_pkg.Get_Approval_Status
3529 (ai.invoice_id,
3530 ai.invoice_amount,
3531 ai.payment_status_flag,
3532 ai.invoice_type_lookup_code)
3533 INTO l_validation_status
3534 FROM ap_invoices_all ai
3535 WHERE ai.invoice_id = l_invoice_id;
3536
3537 IF l_validation_status NOT IN ('APPROVED','UNPAID','FULL','PERMANENT','AVAILABLE') THEN
3538 Push_Error(p_error_code => 'UNAPPROVED INVOICE : '||l_validation_status,
3539 p_error_stack => p_rejection_tab);
3540 END IF;
3541
3542
3543 l_debug_info := 'Check if there is any of the events accruing liability '||
3544 'have been Accounted in 11i';
3545 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3546 FND_LOG.STRING(G_LEVEL_STATEMENT,
3547 G_MODULE_NAME||l_procedure_name,
3548 l_debug_info);
3549 END IF;
3550
3551
3552 SELECT count(*)
3553 INTO l_count_hist_pay
3554 FROM ap_invoice_payments_all aip,
3555 xla_ae_headers xah,
3556 xla_transaction_entities_upg xte,
3557 xla_ae_lines xal
3558 WHERE xah.application_id = 200
3559 AND aip.check_id = nvl(xte.source_id_int_1, -99)
3560 AND aip.set_of_books_id = xte.ledger_id
3561 AND xte.entity_code = 'AP_PAYMENTS'
3562 AND aip.invoice_id = l_invoice_id
3563 AND xah.upg_batch_id IS NOT NULL
3564 AND xah.upg_batch_id <> -9999
3565 AND xte.entity_id = xah.entity_id
3566 AND xah.ae_header_id = xal.ae_header_id
3567 AND xal.accounting_class_code = 'LIABILITY'
3568 AND rownum = 1;
3569
3570 IF l_count_hist_pay > 0 THEN
3571 Push_Error(p_error_code => 'HISTORICAL PAYMENT',
3572 p_error_stack => p_rejection_tab);
3573 END IF;
3574
3575
3576 l_debug_info := 'Check if any of the Invoice events (Including '||
3577 'Prepayment Applications) have not been '||
3578 'Accounted ';
3579 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3580 FND_LOG.STRING(G_LEVEL_STATEMENT,
3581 G_MODULE_NAME||l_procedure_name,
3582 l_debug_info);
3583 END IF;
3584
3585
3586 SELECT count(*)
3587 INTO l_cnt_unacc_inv_evnts
3588 FROM xla_events xe,
3589 xla_transaction_entities_upg xte,
3590 ap_invoices_all ai
3591 WHERE xe.application_id = 200
3592 AND xte.application_id = 200
3593 AND xe.entity_id = xte.entity_id
3594 AND xte.entity_code = 'AP_INVOICES'
3595 AND xe.event_status_code NOT IN ('P', 'N', 'Z')
3596 AND nvl(xte.source_id_int_1, -99) = ai.invoice_id
3597 AND xte.ledger_id = ai.set_of_books_id
3598 AND ai.invoice_id = l_invoice_id
3599 AND rownum = 1;
3600
3601 IF l_cnt_unacc_inv_evnts > 0 THEN
3602 Push_Error(p_error_code => 'UNACCOUNTED INVOICE',
3603 p_error_stack => p_rejection_tab);
3604 END IF;
3605
3606
3607 l_debug_info := 'Check if any of the Payment events pertaining '||
3608 'to the Invoice have been accounted in 11i ';
3609 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3610 FND_LOG.STRING(G_LEVEL_STATEMENT,
3611 G_MODULE_NAME||l_procedure_name,
3612 l_debug_info);
3613 END IF;
3614
3615
3616 SELECT count(*)
3617 INTO l_cnt_unacc_pay_evnts
3618 FROM xla_events xe,
3619 xla_transaction_entities_upg xte,
3620 ap_invoice_payments_all aip
3621 WHERE xe.application_id = 200
3622 AND xte.application_id = 200
3623 AND xte.entity_code = 'AP_PAYMENTS'
3624 AND xe.entity_id = xte.entity_id
3625 AND aip.invoice_id = l_invoice_id
3626 AND nvl(xte.source_id_int_1, -99) = aip.check_id
3627 AND xte.ledger_id = aip.set_of_books_id
3628 AND xe.event_status_code NOT IN ('P', 'N', 'Z')
3629 AND rownum = 1;
3630
3631 IF l_cnt_unacc_pay_evnts > 0 THEN
3632 Push_Error(p_error_code => 'UNACCOUNTED PAYMENT',
3633 p_error_stack => p_rejection_tab);
3634 END IF;
3635
3636
3637 IF l_cnt_unacc_inv_evnts = 0 AND
3638 l_cnt_unacc_pay_evnts = 0 THEN
3639
3640 l_debug_info := 'Checking if any of the Invoice events is not transferred '||
3641 'to GL';
3642 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3643 FND_LOG.STRING(G_LEVEL_STATEMENT,
3644 G_MODULE_NAME||l_procedure_name,
3645 l_debug_info);
3646 END IF;
3647
3648 SELECT count(*)
3649 INTO l_cnt_untrx_inv_evnts
3650 FROM xla_events xe,
3651 xla_ae_headers xah,
3652 xla_transaction_entities_upg xte,
3653 ap_invoices_all ai
3654 WHERE xe.application_id = 200
3655 AND xte.application_id = 200
3656 AND xah.application_id = 200
3657 AND xe.entity_id = xte.entity_id
3658 AND xte.entity_code = 'AP_INVOICES'
3659 AND xe.event_status_code = 'P'
3660 AND xah.event_id = xe.event_id
3661 AND xah.accounting_entry_status_code = 'F'
3662 AND xah.gl_transfer_status_code <> 'Y'
3663 AND nvl(xte.source_id_int_1, -99) = ai.invoice_id
3664 AND xte.ledger_id = ai.set_of_books_id
3665 AND ai.invoice_id = l_invoice_id
3666 AND rownum = 1;
3667
3668 IF l_cnt_untrx_inv_evnts > 0 THEN
3669 Push_Error(p_error_code => 'UNTRANSFERRED INVOICE',
3670 p_error_stack => p_rejection_tab);
3671 END IF;
3672
3673 l_debug_info := 'Checking if any of the Payment Events is not transferred '||
3674 'to GL';
3675 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3676 FND_LOG.STRING(G_LEVEL_STATEMENT,
3677 G_MODULE_NAME||l_procedure_name,
3678 l_debug_info);
3679 END IF;
3680
3681 SELECT count(*)
3682 INTO l_cnt_untrx_pay_evnts
3683 FROM xla_events xe,
3684 xla_ae_headers xah,
3685 xla_transaction_entities_upg xte,
3686 ap_invoice_payments_all aip
3687 WHERE xe.application_id = 200
3688 AND xte.application_id = 200
3689 AND xte.entity_code = 'AP_PAYMENTS'
3690 AND xe.entity_id = xte.entity_id
3691 AND aip.invoice_id = l_invoice_id
3692 AND nvl(xte.source_id_int_1, -99) = aip.check_id
3693 AND xte.ledger_id = aip.set_of_books_id
3694 AND xe.event_status_code = 'P'
3695 AND xah.event_id = xe.event_id
3696 AND xah.accounting_entry_status_code = 'F'
3697 AND xah.gl_transfer_status_code <> 'Y'
3698 AND rownum = 1;
3699
3700 IF l_cnt_untrx_pay_evnts > 0 THEN
3701 Push_Error(p_error_code => 'UNTRANSFERRED PAYMENT',
3702 p_error_stack => p_rejection_tab);
3703 END IF;
3704
3705 END IF;
3706
3707 -- Find the event on which we need to create the final
3708 -- Payment Rounding
3709
3710 l_debug_info := 'fetch the event for which we need to round';
3711 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3712 FND_LOG.STRING(G_LEVEL_STATEMENT,
3713 G_MODULE_NAME||l_procedure_name,
3714 l_debug_info);
3715 END IF;
3716
3717 SELECT max(xe.event_id)
3718 INTO l_max_event_id
3719 FROM xla_events xe
3720 WHERE xe.application_id = 200
3721 AND xe.event_status_code = 'P'
3722 AND xe.event_id IN
3723 (SELECT aph.accounting_event_id
3724 FROM ap_payment_history_all aph,
3725 ap_invoice_payments_all aip
3726 WHERE aip.check_id = aph.check_id
3727 AND aip.invoice_id = l_invoice_id
3728 AND aph.rev_pmt_hist_id IS NULL
3729 AND aph.transaction_type IN ('PAYMENT CREATED',
3730 'REFUND RECORDED',
3731 'PAYMENT ADJUSTED',
3732 'MANUAL PAYMENT ADJUSTED',
3733 'PAYMENT CLEARING',
3734 'PAYMENT CLEARING ADJUSTED'
3735 )
3736 AND NOT EXISTS
3737 (SELECT 1
3738 FROM ap_payment_history_all aph_rev
3739 WHERE aph_rev.check_id = aph.check_id
3740 AND nvl(aph_rev.related_event_id, aph_rev.accounting_event_id)
3741 = nvl(aph.related_event_id, aph.accounting_event_id)
3742 AND aph_rev.rev_pmt_hist_id IS NOT NULL
3743 )
3744 UNION
3745 SELECT aid.accounting_event_id
3746 FROM ap_invoice_distributions_all aid
3747 WHERE aid.invoice_id = l_invoice_id
3748 AND aid.line_type_lookup_code = 'PREPAY'
3749 AND nvl(aid.reversal_flag, 'N') <> 'Y')
3750 AND EXISTS
3751 (SELECT 1
3752 FROM xla_ae_headers xah,
3753 xla_ae_lines xal
3754 WHERE xah.application_id = 200
3755 AND xal.application_id = 200
3756 AND xah.event_id = xe.event_id
3757 AND xah.ae_header_id = xal.ae_header_id
3758 AND xal.accounting_class_code = 'LIABILITY');
3759
3760 p_op_event_id := l_max_event_id;
3761 l_debug_info := 'Check if ane event has been suitably determined for '||
3762 'rounding';
3763 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3764 FND_LOG.STRING(G_LEVEL_STATEMENT,
3765 G_MODULE_NAME||l_procedure_name,
3766 l_debug_info);
3767 END IF;
3768
3769 IF l_max_event_id IS NULL THEN
3770 Push_Error(p_error_code => 'NO AVAILABLE EVENT',
3771 p_error_stack => p_rejection_tab);
3772 P_rej_count := p_rejection_tab.COUNT;
3773 P_return_status := FALSE;
3774
3775 l_debug_info := 'Cannot proceed without an available event,'||
3776 'bailing out';
3777 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3778 FND_LOG.STRING(G_LEVEL_STATEMENT,
3779 G_MODULE_NAME||l_procedure_name,
3780 l_debug_info);
3781 END IF;
3782
3783 RETURN;
3784
3785 END IF;
3786
3787 l_debug_info := 'For the event type determined set the event '||
3788 'type O/P flag appropriately ';
3789 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3790 FND_LOG.STRING(G_LEVEL_STATEMENT,
3791 G_MODULE_NAME||l_procedure_name,
3792 l_debug_info);
3793 END IF;
3794
3795 SELECT xe.event_type_code
3796 INTO p_op_event_type
3797 FROM xla_events xe
3798 WHERE xe.application_id = 200
3799 AND xe.event_id = l_max_event_id;
3800
3801
3802 l_debug_info := ' Check if the Invoice is fully paid ';
3803 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3804 FND_LOG.STRING(G_LEVEL_STATEMENT,
3805 G_MODULE_NAME||l_procedure_name,
3806 l_debug_info);
3807 END IF;
3808
3809
3810 OPEN Ap_Acctg_Pay_Dist_Pkg.Invoice_Header(l_invoice_id);
3811 FETCH Ap_Acctg_Pay_Dist_Pkg.Invoice_Header INTO l_inv_rec;
3812 CLOSE Ap_Acctg_Pay_Dist_Pkg.Invoice_Header;
3813
3814 l_debug_info := 'Before the is_final_payment api call';
3815 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3816 FND_LOG.STRING(G_LEVEL_STATEMENT,
3817 G_MODULE_NAME||l_procedure_name,
3818 l_debug_info);
3819 END IF;
3820
3821
3822 l_is_final_pay := Ap_Accounting_Pay_Pkg.Is_Final_Payment
3823 (P_Inv_Rec => l_Inv_Rec,
3824 P_Payment_Amount => 0,
3825 P_Discount_Amount => 0,
3826 P_Prepay_Amount => 0,
3827 P_Transaction_Type => p_op_event_type,
3828 P_calling_sequence => l_calling_sequence);
3829
3830 IF NOT l_is_final_pay THEN
3831 Push_Error(p_error_code => 'NOT FINAL PAYMENT',
3832 p_error_stack => p_rejection_tab);
3833 END IF;
3834
3835 -- Check the count of the Rejections in the stack, if
3836 -- Rejections have been incurrent, do not proceed further
3837 -- and hence return
3838 p_rej_count := p_rejection_tab.COUNT;
3839
3840 l_debug_info := 'The final rejection count after evaluating all the '||
3841 'factors for final_pay rounding is '||p_rej_count;
3842 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3843 FND_LOG.STRING(G_LEVEL_STATEMENT,
3844 G_MODULE_NAME||l_procedure_name,
3845 l_debug_info);
3846 END IF;
3847
3848
3849 IF p_rej_count > 0 THEN
3850 l_debug_info := 'Since there are rejections, setting the return status '||
3851 'to false, and returning';
3852 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3853 FND_LOG.STRING(G_LEVEL_STATEMENT,
3854 G_MODULE_NAME||l_procedure_name,
3855 l_debug_info);
3856 END IF;
3857
3858 p_return_status := FALSE;
3859 RETURN;
3860 END IF;
3861
3862 l_debug_info := 'Determine the max id for the distribution '||
3863 'pertaining to the Payment/Prepayment distribution ';
3864 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3865 FND_LOG.STRING(G_LEVEL_STATEMENT,
3866 G_MODULE_NAME||l_procedure_name,
3867 l_debug_info);
3868 END IF;
3869
3870
3871 IF p_op_event_type NOT LIKE 'PREPAY%' THEN
3872
3873 SELECT max(aphd.payment_hist_dist_id)
3874 INTO l_max_pay_dist_id
3875 FROM ap_payment_hist_dists aphd
3876 WHERE aphd.accounting_event_id = l_max_event_id;
3877
3878 ELSE
3879
3880 SELECT max(apad.prepay_app_dist_id)
3881 INTO l_max_prepay_dist_id
3882 FROM ap_prepay_app_dists apad
3883 WHERE apad.accounting_event_id = l_max_event_id;
3884
3885 END IF;
3886
3887 -- Call the final Payment rounding api, for the
3888 -- specific event
3889
3890 l_debug_info := 'Fetching the event record';
3891 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3892 FND_LOG.STRING(G_LEVEL_STATEMENT,
3893 G_MODULE_NAME||l_procedure_name,
3894 l_debug_info);
3895 END IF;
3896
3897
3898 OPEN xla_evnt_dtls(l_max_event_id);
3899 FETCH xla_evnt_dtls INTO l_xla_event_rec;
3900 CLOSE xla_evnt_dtls;
3901
3902 IF p_op_event_type NOT LIKE 'PREPAY%' THEN
3903
3904 l_debug_info := 'fetching the Payment History Record';
3905 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3906 FND_LOG.STRING(G_LEVEL_STATEMENT,
3907 G_MODULE_NAME||l_procedure_name,
3908 l_debug_info);
3909 END IF;
3910
3911 OPEN Ap_Acctg_Pay_Dist_Pkg.Payment_History(l_max_event_id);
3912 FETCH Ap_Acctg_Pay_Dist_Pkg.Payment_History INTO l_pay_hist_rec;
3913 CLOSE Ap_Acctg_Pay_Dist_Pkg.Payment_History;
3914
3915 l_debug_info := 'Fetching the Invoice Payment Record';
3916 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3917 FND_LOG.STRING(G_LEVEL_STATEMENT,
3918 G_MODULE_NAME||l_procedure_name,
3919 l_debug_info);
3920 END IF;
3921
3922 -- We need to fetch the Invoice Payment record depending on the type
3923 -- of the event, in case it is present on Invoice Payments, one
3924 -- of the Invoice Payment Records can be used on which the event is
3925 -- stamped
3926 --
3927 -- In case it is a clearing event, or it is an Adjustment event, we
3928 -- would be fetching one of the Invoice Payments corresponding to the
3929 -- check, to which the event belongs
3930 --
3931
3932 l_debug_info := 'before fetching the check_id';
3933 SELECT xte.source_id_int_1
3934 INTO l_check_id
3935 FROM xla_transaction_entities_upg xte,
3936 xla_events xe
3937 WHERE xe.application_id = 200
3938 AND xte.application_id = 200
3939 AND xe.entity_id = xte.entity_id
3940 AND xte.entity_code = 'AP_PAYMENTS'
3941 AND xe.event_id = l_max_event_id;
3942
3943 l_debug_info := 'Check_id :'||l_check_id||' fetched for the event ';
3944 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3945 FND_LOG.STRING(G_LEVEL_STATEMENT,
3946 G_MODULE_NAME||l_procedure_name,
3947 l_debug_info);
3948 END IF;
3949
3950 IF p_op_event_type NOT IN ('PAYMENT ADJUSTED','PAYMENT CLEARED', 'PAYMENT CLEARING ADJUSTED') THEN
3951
3952 l_debug_info := ' Event type :'||p_op_event_type||' would be present on Invoice payments '||
3953 ' Cursor Invoice_Payments opened with :'||
3954 ' Invoice_id:'||l_invoice_id||' Check_id:'||l_check_id||
3955 ' Event_id:'||l_max_event_id;
3956 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3957 FND_LOG.STRING(G_LEVEL_STATEMENT,
3958 G_MODULE_NAME||l_procedure_name,
3959 l_debug_info);
3960 END IF;
3961
3962 OPEN Invoice_Payments(l_invoice_id, l_check_id, l_max_event_id);
3963 FETCH Invoice_Payments INTO l_inv_pay_rec;
3964 CLOSE Invoice_Payments;
3965
3966 ELSE
3967
3968 l_debug_info := ' Event type :'||p_op_event_type||' would not be present on Invoice payments '||
3969 ' Cursor Invoice_Payments opened with :'||
3970 ' Invoice_id:'||l_invoice_id||' Check_id:'||l_check_id||
3971 ' Event_id: null';
3972 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3973 FND_LOG.STRING(G_LEVEL_STATEMENT,
3974 G_MODULE_NAME||l_procedure_name,
3975 l_debug_info);
3976 END IF;
3977
3978
3979 OPEN Invoice_Payments(l_invoice_id, l_check_id, null);
3980 FETCH Invoice_Payments INTO l_inv_pay_rec;
3981 CLOSE Invoice_Payments;
3982
3983 END IF;
3984
3985 ELSE
3986
3987 l_debug_info := 'Fetching the Prepay History Record';
3988 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3989 FND_LOG.STRING(G_LEVEL_STATEMENT,
3990 G_MODULE_NAME||l_procedure_name,
3991 l_debug_info);
3992 END IF;
3993
3994 OPEN Prepay_History(l_invoice_id, l_max_event_id);
3995 FETCH Prepay_History INTO l_prepay_hist_rec;
3996 CLOSE Prepay_History;
3997
3998 l_debug_info := 'Fetching the Prepay Distribution record';
3999 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4000 FND_LOG.STRING(G_LEVEL_STATEMENT,
4001 G_MODULE_NAME||l_procedure_name,
4002 l_debug_info);
4003 END IF;
4004
4005 OPEN Prepay_Dists(l_invoice_id, l_max_event_id);
4006 FETCH Prepay_Dists INTO l_prepay_dist_rec;
4007 CLOSE Prepay_Dists;
4008
4009 END IF;
4010
4011 l_debug_info := 'Before the call to the Final Pay api';
4012 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4013 FND_LOG.STRING(G_LEVEL_STATEMENT,
4014 G_MODULE_NAME||l_procedure_name,
4015 l_debug_info);
4016 END IF;
4017
4018
4019 Ap_Acctg_Pay_Round_Pkg.Final_Pay
4020 (p_xla_event_rec => l_xla_event_rec,
4021 p_pay_hist_rec => l_pay_hist_rec,
4022 p_clr_hist_rec => l_clr_hist_rec,
4023 p_inv_rec => l_inv_rec,
4024 p_inv_pay_rec => l_inv_pay_rec,
4025 p_prepay_inv_rec => l_prepay_inv_rec,
4026 p_prepay_hist_rec => l_prepay_hist_rec,
4027 p_prepay_dist_rec => l_prepay_dist_rec,
4028 p_calling_sequence => l_calling_sequence);
4029
4030 -- depending on the type of the event query the
4031 -- appropriate table, for the specific event for all
4032 -- the ids greater than the max id stored
4033
4034
4035 IF p_op_event_type NOT LIKE 'PREPAY%' THEN
4036
4037 l_debug_info := 'Fetching the details of the Payment Hist dists created';
4038 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4039 FND_LOG.STRING(G_LEVEL_STATEMENT,
4040 G_MODULE_NAME||l_procedure_name,
4041 l_debug_info);
4042 END IF;
4043
4044 OPEN new_round_pay_dists(l_max_event_id, l_max_pay_dist_id);
4045 FETCH new_round_pay_dists BULK COLLECT INTO p_pay_dist_tab;
4046 CLOSE new_round_pay_dists;
4047
4048 IF p_pay_dist_tab.COUNT = 0 THEN
4049
4050 Push_Error(p_error_code => 'NO PAY DIST CREATED',
4051 p_error_stack => p_rejection_tab);
4052
4053 p_return_status := FALSE;
4054 p_rej_count := p_rejection_tab.COUNT;
4055
4056 ELSE
4057 p_return_status := TRUE;
4058 END IF;
4059
4060 ELSE
4061
4062 l_debug_info := 'Fetching the details of the Prepay Hist Dists Created';
4063 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4064 FND_LOG.STRING(G_LEVEL_STATEMENT,
4065 G_MODULE_NAME||l_procedure_name,
4066 l_debug_info);
4067 END IF;
4068
4069 OPEN new_round_prepay_dists(l_max_event_id, l_max_prepay_dist_id);
4070 FETCH new_round_prepay_dists BULK COLLECT INTO p_prepay_dist_tab;
4071 CLOSE new_round_prepay_dists;
4072
4073 IF p_prepay_dist_tab.COUNT = 0 THEN
4074
4075 Push_Error(p_error_code => 'NO PREPAY DIST CREATED',
4076 p_error_stack => p_rejection_tab);
4077
4078 p_return_status := FALSE;
4079 p_rej_count := p_rejection_tab.COUNT;
4080
4081 ELSE
4082 p_return_status := TRUE;
4083 END IF;
4084
4085
4086 END IF;
4087
4088 l_debug_info := 'Committing if asked to';
4089 IF nvl(p_commit_flag, 'N') = 'Y' THEN
4090 COMMIT;
4091 END IF;
4092
4093 l_log_msg := 'Procedure Ends';
4094 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4095 FND_LOG.STRING(G_LEVEL_PROCEDURE,
4096 G_MODULE_NAME||l_procedure_name,
4097 l_log_msg);
4098 END IF;
4099
4100
4101
4102 EXCEPTION
4103 WHEN OTHERS THEN
4104 IF SQLCODE <> -20001 THEN
4105 p_error_msg := ' Encountered an Exception '||SQLERRM||
4106 ' at : '||l_calling_sequence||
4107 ' while performing : '||l_debug_info||
4108 ' for Invoice_id : '||l_invoice_id;
4109
4110 p_return_status := FALSE;
4111
4112 Push_Error(p_error_code => 'UNEXPECTED EXCEPTION',
4113 p_error_stack => p_rejection_tab);
4114
4115 p_rej_count := p_rejection_tab.COUNT;
4116
4117
4118 END IF;
4119 END;
4120
4121
4122 Function Is_period_open(P_Date IN date,
4123 P_Org_Id IN number default mo_global.get_current_org_id)
4124 return varchar2
4125 IS
4126 l_period_name Varchar2(15) Default Null;
4127 Begin
4128 SELECT period_name
4129 Into l_period_name
4130 FROM gl_period_statuses GLPS,
4131 ap_system_parameters_all SP
4132 WHERE application_id = 200
4133 AND sp.org_id = P_Org_Id
4134 AND GLPS.set_of_books_id = SP.set_of_books_id
4135 AND trunc(P_Date) BETWEEN start_date AND end_date
4136 AND closing_status in ('O', 'F')
4137 AND NVL(adjustment_period_flag, 'N') = 'N';
4138
4139
4140 return (l_period_name);
4141 Exception
4142 when others then
4143 return (NULL);
4144 End;
4145
4146 Function get_open_period_start_date(P_Org_Id IN number)
4147 return date
4148 Is
4149 l_start_date Date;
4150
4151 Begin
4152 SELECT Start_Date
4153 Into l_start_date
4154 FROM (
4155 SELECT DISTINCT gps.Period_Name, trunc(gps.Start_Date) Start_date
4156 FROM gl_Period_Statuses gps,
4157 ap_System_Parameters_All Asp
4158 WHERE gps.Application_Id = 200
4159 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
4160 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
4161 AND Nvl(Asp.Org_Id,- 99) = Nvl(p_org_id,- 99)
4162 AND gps.closing_Status in ('O', 'F')
4163 INTERSECT
4164 SELECT DISTINCT gps.Period_Name, trunc(gps.start_Date) start_date
4165 FROM gl_Period_Statuses gps,
4166 ap_System_Parameters_All Asp
4167 WHERE gps.Application_Id = 101
4168 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
4169 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
4170 AND Nvl(Asp.Org_Id,- 99) = Nvl(P_Org_Id,- 99)
4171 AND gps.closing_Status in ('O', 'F')
4172 order by Start_date
4173 )
4174 WHERE rownum < 2;
4175
4176 return (l_start_date);
4177 Exception
4178 when others then
4179 return (NULL);
4180 End;
4181
4182 /*
4183 USE :
4184 Public api to delete prepay appl/payment cascade adjustment events
4185
4186 INPUT :
4187 p_source_type - 'AP_INVOICES' -- for prepay appl
4188 'AP_PAYMENTS' -- for payment
4189 p_source_id - invoice_id -- when p_source_type is 'AP_INVOICES'
4190 check_id -- when p_source_type is 'AP_PAYMENTS'
4191 p_related_event_id - related event id of cascade adjustment.
4192 This is added to handle single event only
4193 for ex: in case when single event id undone..etc
4194
4195 NOTES :
4196 1. org context needs to be set prior to call the api
4197 2. commit is to be handled by the calling api
4198
4199 */
4200 FUNCTION delete_cascade_adjustments
4201 (p_source_type IN VARCHAR2,
4202 p_source_id IN NUMBER,
4203 p_related_event_id IN NUMBER DEFAULT NULL)
4204 RETURN BOOLEAN IS
4205
4206 l_procedure_name CONSTANT VARCHAR2(30) := 'delete_cascade_adjustments()';
4207 l_debug_info VARCHAR2(1000);
4208
4209 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
4210 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
4211 l_orgid NUMBER(10);
4212
4213 CURSOR payment_cascade_adj_cur IS
4214 SELECT DISTINCT aph_adj.accounting_event_id
4215 FROM ap_payment_history_all aph
4216 , xla_events xe
4217 , ap_payment_history_all aph_adj
4218 , xla_events xe_adj
4219 WHERE aph.check_id = p_source_id
4220 AND aph.accounting_event_id = NVL(p_related_event_id
4221 , aph.accounting_event_id)
4222 AND aph.transaction_type IN('PAYMENT CREATED',
4223 'PAYMENT MATURITY',
4224 'PAYMENT CLEARING',
4225 'REFUND RECORDED')
4226 AND aph.posted_flag <> 'Y'
4227 AND xe.event_id = aph.accounting_event_id
4228 AND xe.event_status_code <> 'P'
4229 AND aph_adj.check_id = aph.check_id
4230 AND aph_adj.related_event_id <> aph_adj.accounting_event_id
4231 AND aph_adj.related_event_id = aph.accounting_event_id
4232 AND aph_adj.transaction_type IN('PAYMENT ADJUSTED',
4233 'PAYMENT MATURITY ADJUSTED',
4234 'PAYMENT CLEARING ADJUSTED',
4235 'REFUND ADJUSTED')
4236 AND aph_adj.posted_flag <> 'Y'
4237 AND xe_adj.event_id = aph_adj.accounting_event_id
4238 AND xe_adj.event_status_code <> 'P';
4239
4240 CURSOR prepay_appl_cascade_adj_cur IS
4241 SELECT DISTINCT apph_adj.accounting_event_id
4242 FROM ap_invoices_all ai
4243 , ap_prepay_history_all apph
4244 , xla_events xe
4245 , ap_prepay_history_all apph_adj
4246 , xla_events xe_adj
4247 WHERE 1=1
4248 AND ai.invoice_id = p_source_id
4249 AND ap_invoices_utility_pkg.get_approval_status
4250 (ai.invoice_id,
4251 ai.invoice_amount,
4252 ai.payment_status_flag,
4253 ai.invoice_type_lookup_code)
4254 IN ('NEEDS REAPPROVAL',
4255 'NEVER APPROVED',
4256 'UNAPPROVED')
4257 AND apph.invoice_id = ai.invoice_id
4258 AND apph.accounting_event_id = NVL(p_related_event_id,
4259 apph.accounting_event_id)
4260 AND apph.transaction_type IN('PREPAYMENT APPLIED')
4261 AND apph.posted_flag <> 'Y'
4262 AND NOT EXISTS
4263 (
4264 SELECT 'encumbered'
4265 FROM ap_invoice_distributions_all aid
4266 WHERE aid.accounting_event_id = apph.accounting_event_id
4267 AND NVL(aid.encumbered_flag, 'N') = 'Y' --bug11880177,bug12407622
4268 )
4269 AND xe.event_id = apph.accounting_event_id
4270 AND xe.event_status_code <> 'P'
4271 AND apph_adj.invoice_id = apph.invoice_id
4272 AND apph_adj.related_prepay_app_event_id <> apph_adj.accounting_event_id
4273 AND apph_adj.related_prepay_app_event_id = apph.accounting_event_id
4274 AND apph_adj.transaction_type IN('PREPAYMENT APPLICATION ADJ')
4275 AND apph_adj.posted_flag <> 'Y'
4276 AND xe_adj.event_id = apph_adj.accounting_event_id
4277 AND xe_adj.event_status_code <> 'P';
4278
4279 BEGIN
4280
4281 l_debug_info := 'Begin : function '||l_procedure_name;
4282 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4283 FND_LOG.STRING(G_LEVEL_STATEMENT,
4284 G_MODULE_NAME||l_procedure_name,
4285 l_debug_info);
4286 END IF;
4287
4288 l_debug_info := 'input : p_source_type = '||p_source_type
4289 ||', p_source_id = '||p_source_id;
4290 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4291 FND_LOG.STRING(G_LEVEL_STATEMENT,
4292 G_MODULE_NAME||l_procedure_name,
4293 l_debug_info);
4294 END IF;
4295
4296 /* validating and getting the EVENT SOURCE information */
4297 BEGIN
4298
4299 SELECT security_id_int_1,
4300 legal_entity_id,
4301 ledger_id,
4302 entity_code,
4303 source_id_int_1,
4304 transaction_number,
4305 application_id
4306 INTO l_event_security_context.security_id_int_1,
4307 l_event_source_info.legal_entity_id,
4308 l_event_source_info.ledger_id,
4309 l_event_source_info.entity_type_code,
4310 l_event_source_info.source_id_int_1,
4311 l_event_source_info.transaction_number,
4312 l_event_source_info.application_id
4313 FROM xla_transaction_entities_upg xte
4314 WHERE NVL(xte.source_id_int_1, -99) = p_source_id
4315 AND xte.entity_code = p_source_type
4316 AND xte.application_id = 200;
4317
4318 EXCEPTION
4319 WHEN NO_DATA_FOUND THEN
4320 l_debug_info := 'source information is INVALID';
4321 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4322 FND_LOG.STRING(G_LEVEL_STATEMENT,
4323 G_MODULE_NAME||l_procedure_name,
4324 l_debug_info);
4325 END IF;
4326 RETURN FALSE;
4327 END;
4328
4329 --bug 12434758
4330 l_debug_info := 'Setting org context..';
4331 l_orgid := l_event_security_context.security_id_int_1;
4332 MO_GLOBAL.SET_POLICY_CONTEXT('S', l_orgid);
4333
4334 IF p_source_type = 'AP_INVOICES' THEN
4335
4336 /* delete PREPAY APPL cascade adjustments */
4337 FOR i IN prepay_appl_cascade_adj_cur
4338 LOOP
4339
4340 AP_XLA_EVENTS_PKG.delete_event
4341 ( p_event_source_info => l_event_source_info,
4342 p_event_id => i.accounting_event_id,
4343 p_valuation_method => NULL,
4344 p_security_context => l_event_security_context,
4345 p_calling_sequence => l_procedure_name
4346 );
4347
4348 DELETE ap_prepay_app_dists apad
4349 WHERE apad.accounting_event_id = i.accounting_event_id
4350 AND NOT EXISTS
4351 (
4352 SELECT 1
4353 FROM xla_events xe
4354 WHERE xe.event_id = apad.accounting_event_id
4355 AND xe.application_id = 200
4356 );
4357
4358 DELETE ap_prepay_history_all apph
4359 WHERE apph.accounting_event_id = i.accounting_event_id
4360 AND NOT EXISTS
4361 (
4362 SELECT 1
4363 FROM xla_events xe
4364 WHERE xe.event_id = apph.accounting_event_id
4365 AND xe.application_id = 200
4366 );
4367
4368 END LOOP;
4369
4370 ELSIF p_source_type = 'AP_PAYMENTS' THEN
4371
4372 /* delete PAYMENT cascade adjustments */
4373 FOR i IN payment_cascade_adj_cur
4374 LOOP
4375
4376 AP_XLA_EVENTS_PKG.delete_event
4377 ( p_event_source_info => l_event_source_info,
4378 p_event_id => i.accounting_event_id,
4379 p_valuation_method => NULL,
4380 p_security_context => l_event_security_context,
4381 p_calling_sequence => l_procedure_name
4382 );
4383
4384 DELETE ap_payment_hist_dists aphd
4385 WHERE aphd.accounting_event_id = i.accounting_event_id
4386 AND NOT EXISTS
4387 (
4388 SELECT 1
4389 FROM xla_events xe
4390 WHERE xe.event_id = aphd.accounting_event_id
4391 AND xe.application_id = 200
4392 );
4393
4394 DELETE ap_payment_history_all aph
4395 WHERE aph.accounting_event_id = i.accounting_event_id
4396 AND NOT EXISTS
4397 (
4398 SELECT 1
4399 FROM xla_events xe
4400 WHERE xe.event_id = aph.accounting_event_id
4401 AND xe.application_id = 200
4402 );
4403
4404 END LOOP;
4405
4406 END IF; -- p_source_type = 'AP_INVOICES'
4407
4408 RETURN TRUE;
4409
4410 EXCEPTION
4411 WHEN OTHERS THEN
4412 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4413 FND_LOG.STRING(G_LEVEL_STATEMENT,
4414 G_MODULE_NAME||l_procedure_name,
4415 SQLERRM);
4416 END IF;
4417
4418 RETURN FALSE;
4419 END delete_cascade_adjustments;
4420
4421 PROCEDURE repop_prepay_dists
4422 (p_bug_no IN NUMBER,
4423 p_driver_table IN VARCHAR2,
4424 p_calling_sequence IN VARCHAR2) IS
4425
4426 l_check_invoice_col VARCHAR2(1) := 'N';
4427 l_check_event_col VARCHAR2(1) := 'N';
4428 l_check_ret_stat_col VARCHAR2(1) := 'N';
4429 l_check_process_col VARCHAR2(1) := 'N';
4430 l_sql_stmt LONG;
4431 l_sql_errors NUMBER;
4432 TYPE Tab_Number IS TABLE OF AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE INDEX BY BINARY_INTEGER;
4433 l_inv_dist_id_tab TAB_NUMBER;
4434 l_inv_line_no_tab TAB_NUMBER;
4435 l_acct_event_id_tab TAB_NUMBER;
4436 l_bc_event_id_tab TAB_NUMBER;
4437 TYPE Tab_Char IS TABLE OF AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG%TYPE INDEX BY BINARY_INTEGER;
4438 l_match_status_flg_tab TAB_CHAR;
4439 l_bug_no VARCHAR2(100);
4440 l_driver_table ALL_TABLES.TABLE_NAME%TYPE;
4441 l_debug_info VARCHAR2(4000);
4442 l_error_log LONG;
4443 l_prev_org_id NUMBER := -99;
4444 l_org_id NUMBER := -99;
4445 l_message VARCHAR2(4000);
4446 l_calling_sequence VARCHAR2(4000);
4447 l_procedure_name CONSTANT VARCHAR2(30) := 'repop_prepay_dists';
4448
4449 TYPE refcurtyp IS REF CURSOR;
4450 drv_invoices_cur REFCURTYP;
4451 drv_status_cur REFCURTYP;
4452
4453 TYPE source_id_int_1_t IS TABLE OF XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1%TYPE INDEX BY BINARY_INTEGER;
4454 TYPE invoice_num_t IS TABLE OF AP_INVOICES_ALL.INVOICE_NUM%TYPE INDEX BY BINARY_INTEGER;
4455 TYPE invoice_type_code_t IS TABLE OF AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE%TYPE INDEX BY BINARY_INTEGER;
4456 TYPE invoice_amount_t IS TABLE OF AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE INDEX BY BINARY_INTEGER;
4457 TYPE vendor_name_t IS TABLE OF AP_SUPPLIERS.vendor_name%TYPE INDEX BY BINARY_INTEGER;
4458 TYPE vendor_site_code_t IS TABLE OF AP_SUPPLIER_SITES_ALL.VENDOR_SITE_CODE%TYPE INDEX BY BINARY_INTEGER;
4459 TYPE org_id_t IS TABLE OF AP_INVOICES_ALL.ORG_ID%TYPE INDEX BY BINARY_INTEGER;
4460 TYPE set_of_books_id_t IS TABLE OF AP_INVOICES_ALL.SET_OF_BOOKS_ID%TYPE INDEX BY BINARY_INTEGER;
4461 TYPE process_flag_t IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
4462 TYPE error_reason_t IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
4463
4464 TYPE repop_inv_rec_type IS RECORD
4465 (source_id_l source_id_int_1_t,
4466 process_flag_l process_flag_t
4467 );
4468
4469 repop_invoices_list REPOP_INV_REC_TYPE;
4470
4471 TYPE repop_status_details IS RECORD
4472 (source_id_l source_id_int_1_t,
4473 invoice_num_l invoice_num_t,
4474 invoice_type_code_l invoice_type_code_t,
4475 invoice_amount_l invoice_amount_t,
4476 vendor_name_l vendor_name_t,
4477 vendor_site_code_l vendor_site_code_t,
4478 org_id_l org_id_t,
4479 set_of_books_id_l set_of_books_id_t,
4480 process_flag_l process_flag_t,
4481 error_reason_l error_reason_t
4482 );
4483
4484 repop_status_list REPOP_STATUS_DETAILS;
4485
4486 -- bug10056653, started to Fetch the invoice_line_number
4487 -- and also improved checks to ensure that the prepay
4488 -- distributions are not encumbered
4489 --
4490 CURSOR distributions_cur(P_Invoice_ID NUMBER) IS
4491 SELECT aid.invoice_distribution_id,
4492 aid.invoice_line_number,
4493 aid.accounting_event_id,
4494 aid.bc_event_id,
4495 aid.match_status_flag
4496 FROM ap_invoice_distributions_all aid,
4497 xla_events xe
4498 WHERE xe.application_id = 200
4499 AND aid.accounting_event_id = xe.event_id
4500 AND aid.invoice_id = P_Invoice_ID
4501 AND xe.event_status_code IN ('I','U')
4502 AND xe.event_type_code IN ('PREPAYMENT APPLIED','PREPAYMENT UNAPPLIED')
4503 AND aid.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
4504 AND aid.prepay_distribution_id IS NOT NULL
4505 AND NVL(encumbered_flag, 'N') <> 'Y'
4506 AND aid.posted_flag <> 'Y'
4507 AND NOT EXISTS
4508 (SELECT 1
4509 FROM xla_events xe_bc,
4510 financials_system_params_all fsp
4511 WHERE fsp.org_id = aid.org_id
4512 AND fsp.purch_encumbrance_flag = 'Y'
4513 AND xe_bc.application_id = 200
4514 AND xe_bc.budgetary_control_flag = 'Y'
4515 AND xe_bc.event_id = aid.bc_event_id
4516 AND xe_bc.event_status_code = 'P'
4517 )
4518 ORDER BY aid.invoice_id,
4519 xe.event_type_code ;
4520
4521 BEGIN
4522
4523 l_bug_no := p_bug_no;
4524 l_calling_sequence := 'Repopulate_Prepay_Distributions <- '||p_calling_sequence;
4525
4526 l_debug_info := 'Constructing the name of the driver table';
4527 IF p_driver_table IS NOT NULL THEN
4528 l_driver_table := upper(p_driver_table);
4529 ELSE
4530 l_driver_table := 'AP_TEMP_DATA_DRIVER_'||l_bug_no;
4531 END IF;
4532
4533 BEGIN
4534 l_debug_info := 'Checking if the invoice_id column is there in the driver table';
4535 SELECT 'Y'
4536 INTO l_check_invoice_col
4537 FROM sys.all_tab_columns
4538 WHERE table_name = l_driver_table
4539 AND column_name = 'INVOICE_ID';
4540
4541 EXCEPTION
4542 WHEN OTHERS THEN
4543 l_check_invoice_col := 'N';
4544 END;
4545
4546 BEGIN
4547 l_debug_info := 'Checking if the return_status column is there in the driver table';
4548 SELECT 'Y'
4549 INTO l_check_ret_stat_col
4550 FROM sys.all_tab_columns
4551 WHERE table_name = l_driver_table
4552 AND column_name = 'RETURN_STATUS';
4553
4554 EXCEPTION
4555 WHEN OTHERS THEN
4556 l_check_ret_stat_col := 'N';
4557 END;
4558
4559 BEGIN
4560 l_debug_info := 'Checking if the process_flag column is there in the driver table';
4561 SELECT 'Y'
4562 INTO l_check_process_col
4563 FROM sys.all_tab_columns
4564 WHERE table_name = l_driver_table
4565 AND column_name = 'PROCESS_FLAG';
4566
4567 EXCEPTION
4568 WHEN OTHERS THEN
4569 l_debug_info := 'Adding process_flag column in the driver table if not present';
4570 l_sql_stmt := ' ALTER TABLE '||l_driver_table||' ADD '||
4571 ' (PROCESS_FLAG VARCHAR2(1)) ';
4572 EXECUTE IMMEDIATE l_sql_stmt;
4573
4574 l_debug_info := 'Updating the driver tables process_flag column to ''Y'' as default value';
4575 l_sql_stmt := ' UPDATE '||l_driver_table||
4576 ' SET process_flag = ''Y''';
4577 EXECUTE IMMEDIATE l_sql_stmt;
4578
4579 END;
4580
4581 IF l_check_invoice_col = 'N' THEN
4582 BEGIN
4583 l_debug_info := 'Checking if the event_id column is there in the driver table';
4584 SELECT 'Y'
4585 INTO l_check_event_col
4586 FROM sys.all_tab_columns
4587 WHERE table_name = l_driver_table
4588 AND column_name = 'EVENT_ID';
4589
4590 EXCEPTION
4591 WHEN OTHERS THEN
4592 l_check_event_col := 'N';
4593 END;
4594
4595 IF l_check_event_col = 'Y' THEN
4596 l_debug_info := 'Adding invoice_id column to the driver table if it doesnot exist';
4597 l_sql_stmt := ' ALTER TABLE '||l_driver_table||' ADD '||
4598 ' (INVOICE_ID NUMBER(15,0)) ';
4599 EXECUTE IMMEDIATE l_sql_stmt;
4600
4601 l_debug_info := 'Populating the invoice_id column in the driver table';
4602 l_sql_stmt := 'UPDATE '||l_driver_table||' dr '||
4603 ' SET invoice_id = (SELECT DISTINCT invoice_id '||
4604 ' FROM ap_invoice_distributions_all '||
4605 ' WHERE accounting_event_id = nvl(dr.event_id,-99)) ';
4606 EXECUTE IMMEDIATE l_sql_stmt;
4607 ELSE
4608 l_debug_info := 'Either the invoice_id or the event_id column needs to be present in driver table '
4609 ||l_driver_table;
4610 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4611 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name,
4612 l_debug_info);
4613 END IF;
4614 print(l_debug_info);
4615 APP_EXCEPTION.RAISE_EXCEPTION();
4616 END IF;
4617 END IF;
4618
4619 IF l_check_ret_stat_col = 'N' THEN
4620 l_debug_info := 'Adding Return status column in the driver table';
4621 l_sql_stmt := ' ALTER TABLE '||l_driver_table||' ADD '||
4622 ' (RETURN_STATUS VARCHAR2(1000)) ';
4623 EXECUTE IMMEDIATE l_sql_stmt;
4624 END IF;
4625
4626 /*Bug 10638465*/
4627 l_debug_info := 'Constructing the dynamic sql statement ';
4628 l_sql_stmt := ' SELECT DISTINCT dr.invoice_id, dr.process_flag '||
4629 ' FROM '||l_driver_table||' dr '||
4630 ' WHERE dr.process_flag NOT IN (''N'',''E'') '|| --bug10019343
4631 ' AND EXISTS '||
4632 ' (SELECT ''Prepay Invoice'''||
4633 ' FROM ap_invoice_distributions_all '||
4634 ' WHERE invoice_id = dr.invoice_id '||
4635 ' AND line_type_lookup_code = ''PREPAY'')';
4636
4637 l_debug_info := 'After the l_sql_stmt, before opening the cursor';
4638 OPEN drv_invoices_cur FOR l_sql_stmt;
4639 LOOP
4640 FETCH drv_invoices_cur
4641 BULK COLLECT INTO repop_invoices_list.source_id_l,
4642 repop_invoices_list.process_flag_l LIMIT 1000;
4643
4644 l_debug_info := 'After the fetch, before looping for the batch of 1000';
4645 IF repop_invoices_list.source_id_l.COUNT > 0 THEN
4646
4647 FOR i IN repop_invoices_list.source_id_l.FIRST..repop_invoices_list.source_id_l.LAST LOOP
4648
4649 SAVEPOINT BEFORE_REPOPULTION;
4650 IF repop_invoices_list.process_flag_l(i) <> 'N' THEN --bug10019343
4651
4652 BEGIN
4653 l_debug_info := 'Fetching the org_id';
4654 SELECT NVL(org_id,-99)
4655 INTO l_org_id
4656 FROM ap_invoices_all
4657 WHERE invoice_id = repop_invoices_list.source_id_l(i);
4658
4659 IF l_org_id <> l_prev_org_id THEN
4660 l_debug_info := 'Setting the org context to org_id '||l_org_id;
4661 MO_GLOBAL.set_policy_context('S', l_org_id);
4662 END IF;
4663
4664 l_debug_info := 'After the l_sql_stmt, before opening the cursor';
4665 OPEN distributions_cur(repop_invoices_list.source_id_l(i));
4666 FETCH distributions_cur
4667 BULK COLLECT INTO l_inv_dist_id_tab,
4668 l_inv_line_no_tab,
4669 l_acct_event_id_tab,
4670 l_bc_event_id_tab,
4671 l_match_status_flg_tab;
4672 CLOSE distributions_cur;
4673
4674 l_debug_info := 'Updating ap_invoice_distributions';
4675 FORALL j IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
4676 UPDATE ap_invoice_distributions_all aid
4677 SET aid.accounting_event_id = NULL,
4678 aid.bc_event_id = NULL,
4679 aid.posted_flag = DECODE(aid.posted_flag, 'S', 'N', aid.posted_flag),
4680 aid.match_status_flag = 'S'
4681 WHERE aid.invoice_distribution_id = l_inv_dist_id_tab(j);
4682
4683
4684 l_debug_info := 'Deleting from ap_prepay_app_dists';
4685 -- bug10056653, updated the delete to exclude the PREPAY APP
4686 -- DISTS pertaining to ADJ events
4687 --
4688 FORALL j IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
4689 DELETE FROM ap_prepay_app_dists apad
4690 WHERE (apad.accounting_event_id = l_acct_event_id_tab(j) OR
4691 apad.bc_event_id = l_bc_event_id_tab(j) OR
4692 apad.prepay_app_distribution_id = l_inv_dist_id_tab(j))
4693 AND NOT EXISTS
4694 (SELECT 1
4695 FROM ap_prepay_history_all apph
4696 WHERE apph.prepay_history_id = apad.prepay_history_id
4697 AND apph.transaction_type = 'PREPAYMENT APPLICATION ADJ');
4698
4699 l_debug_info := 'Deleting from ap_prepay_history_all';
4700 -- bug10056653, updated the delete to exclude the PREPAY
4701 -- HISTORY pertaining to ADJ events, and also ensure that
4702 -- there are no posted or encumbered Invoice dists under
4703 -- the prepay History
4704 --
4705 FORALL j IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
4706 DELETE FROM ap_prepay_history_all apph
4707 WHERE apph.invoice_id = repop_invoices_list.source_id_l(i)
4708 AND apph.transaction_type <> 'PREPAYMENT APPLICATION ADJ'
4709 AND apph.invoice_line_number = l_inv_line_no_tab(j)
4710 AND NOT EXISTS
4711 (SELECT 1
4712 FROM ap_prepay_app_dists apad,
4713 ap_invoice_distributions_all aid
4714 WHERE apad.prepay_history_id = apph.prepay_history_id
4715 AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
4716 AND (aid.posted_flag = 'Y' OR
4717 aid.encumbered_flag = 'Y')
4718 )
4719 AND (apph.accounting_event_id = l_acct_event_id_tab(j) OR
4720 apph.bc_event_id = l_bc_event_id_tab(j) OR
4721 (apph.accounting_event_id IS NULL AND
4722 apph.bc_event_id IS NULL)
4723 );
4724
4725
4726 l_debug_info := 'Before the call to the prepay_dist_appl routine for Invoice_id: ' ||
4727 repop_invoices_list.source_id_l(i);
4728
4729 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4730 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name,
4731 l_debug_info);
4732 END IF;
4733
4734 AP_ACCTG_PREPAY_DIST_PKG.prepay_dist_appl
4735 (repop_invoices_list.source_id_l(i),l_calling_sequence);
4736
4737 l_debug_info := 'API call successful for Invoice_Id :'||
4738 repop_invoices_list.source_id_l(i);
4739
4740 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4741 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name,
4742 l_debug_info);
4743 END IF;
4744
4745 l_debug_info := 'Updating ap_invoice_distributions';
4746 FORALL j IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
4747 UPDATE ap_invoice_distributions_all aid
4748 SET aid.accounting_event_id = l_acct_event_id_tab(j),
4749 aid.bc_event_id = l_bc_event_id_tab(j),
4750 aid.match_status_flag = l_match_status_flg_tab(j)
4751 WHERE aid.invoice_distribution_id = l_inv_dist_id_tab(j);
4752
4753 l_debug_info := 'Updating ap_prepay_app_dists';
4754 -- bug10056653, excluded prepay history pertaining to the
4755 -- cascade ADJ events
4756 --
4757 FORALL j IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
4758 UPDATE ap_prepay_app_dists apad
4759 SET apad.accounting_event_id = l_acct_event_id_tab(j),
4760 apad.bc_event_id = l_bc_event_id_tab(j)
4761 WHERE apad.prepay_app_distribution_id = l_inv_dist_id_tab(j)
4762 AND NOT EXISTS
4763 (SELECT 1
4764 FROM ap_prepay_history_all apph
4765 WHERE apph.prepay_history_id = apad.prepay_history_id
4766 AND apph.transaction_type = 'PREPAYMENT APPLICATION ADJ');
4767
4768 l_debug_info := 'Updating ap_prepay_history_all';
4769 -- bug10056653, excluded prepay app dists pertaining to the
4770 -- cascade ADJ events
4771 --
4772 FORALL j IN l_inv_dist_id_tab.FIRST..l_inv_dist_id_tab.LAST
4773 UPDATE ap_prepay_history_all apph
4774 SET apph.accounting_event_id = l_acct_event_id_tab(j),
4775 apph.bc_event_id = l_bc_event_id_tab(j)
4776 WHERE apph.transaction_type <> 'PREPAYMENT APPLICATION ADJ'
4777 AND EXISTS
4778 (SELECT 1
4779 FROM ap_prepay_app_dists apad
4780 WHERE apad.prepay_history_id = apph.prepay_history_id
4781 AND apad.prepay_app_distribution_id = l_inv_dist_id_tab(j));
4782
4783 l_prev_org_id := l_org_id;
4784 COMMIT;
4785 EXCEPTION
4786 WHEN OTHERS THEN
4787 l_prev_org_id := -99;
4788
4789 l_error_log := ' Skipping the Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
4790 ' in '||l_calling_sequence||' while performing '||l_debug_info||
4791 ' for Invoice_ID :'||repop_invoices_list.source_id_l(i);
4792
4793 -- bug10056653, printing exception in the LOG
4794 Print(l_error_log);
4795
4796 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
4797 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name, l_error_log);
4798 END IF;
4799
4800
4801
4802 ROLLBACK TO BEFORE_REPOPULTION;
4803 l_sql_stmt := ' UPDATE '||l_driver_table||' dr '||
4804 ' SET return_status = '||SQLERRM||' , '||
4805 ' process_flag = ''E'''||
4806 ' WHERE dr.invoice_id = repop_invoices_list.source_id_l(i)';
4807
4808 EXECUTE IMMEDIATE l_sql_stmt;
4809 END;
4810 END IF;
4811 END LOOP;
4812 END IF;
4813 EXIT WHEN drv_invoices_cur%NOTFOUND;
4814 END LOOP;
4815
4816 -- bug10056653, changed the display message
4817 --
4818 l_message := '<b><u>Following are the details of the invoices for which '||
4819 'repopulation of Prepay History(AP_PREPAY_HISTORY_ALL) and '||
4820 'Prepay App Dists(AP_PREPAY_APP_DISTS) was successful </u></b>';
4821 Print(l_message);
4822
4823 l_debug_info := ' Printing the details of the repopulated prepayment ';
4824 l_message := '<table border="5">'||
4825 '<th>INVOICE_ID</th>'||
4826 '<th>INVOICE_NUM</th>'||
4827 '<th>INVOICE_TYPE_LOOKUP_CODE</th>'||
4828 '<th>INVOICE_AMOUNT</th>'||
4829 '<th>VENDOR_NAME</th>'||
4830 '<th>VENDOR_SITE</th>'||
4831 '<th>ORG_ID</th>'||
4832 '<th>LEDGER_ID</th>'||
4833 '<th>PROCESS_FLAG</th>'||
4834 '<th>RETURN_STATUS</th>';
4835
4836 print(l_message);
4837
4838 /*Bug 10638465*/
4839 l_sql_stmt :=
4840 ' SELECT distinct dr.invoice_id, '||
4841 ' ai.invoice_num, '||
4842 ' ai.invoice_type_lookup_code, '||
4843 ' ai.invoice_amount, '||
4844 ' av.vendor_name, '||
4845 ' avs.vendor_site_code, '||
4846 ' ai.org_id, '||
4847 ' aps.set_of_books_id, '||
4848 ' dr.process_flag, '||
4849 ' NVL(dr.return_status,''Data Processed'') '||
4850 ' FROM '||l_driver_table||' dr, '||
4851 ' ap_invoices_all ai, '||
4852 ' ap_suppliers av, '||
4853 ' ap_supplier_sites_all avs, '||
4854 ' ap_system_parameters_all aps '||
4855 ' WHERE ai.invoice_id = dr.invoice_id'||
4856 ' AND ai.org_id = aps.org_id'||
4857 ' AND ai.vendor_id = av.vendor_id'||
4858 ' AND ai.vendor_site_id = avs.vendor_site_id'||
4859 ' AND dr.process_flag NOT IN (''N'',''E'') '|| --bug10019343
4860 ' ORDER BY dr.invoice_id';
4861
4862 l_debug_info := 'Before Opening the cursor for Printing the details '||
4863 'of the Processed Entries ';
4864 OPEN drv_status_cur FOR l_sql_stmt;
4865 LOOP
4866
4867 l_debug_info := 'Before fetch for a batchsize for Printing details of Original and Rev';
4868 FETCH drv_status_cur
4869 BULK COLLECT INTO repop_status_list.source_id_l,
4870 repop_status_list.invoice_num_l,
4871 repop_status_list.invoice_type_code_l,
4872 repop_status_list.invoice_amount_l,
4873 repop_status_list.vendor_name_l,
4874 repop_status_list.vendor_site_code_l,
4875 repop_status_list.org_id_l,
4876 repop_status_list.set_of_books_id_l,
4877 repop_status_list.process_flag_l,
4878 repop_status_list.error_reason_l LIMIT 1000;
4879
4880 l_debug_info := 'Before looping for the batchsize';
4881 IF repop_status_list.source_id_l.COUNT > 0 THEN
4882 FOR i IN repop_status_list.source_id_l.FIRST..repop_status_list.source_id_l.LAST LOOP
4883 l_message :=
4884 '<tr><td>'||to_char(repop_status_list.source_id_l(i))||'</td><td>'||
4885 repop_status_list.invoice_num_l(i)||'</td><td>'||
4886 repop_status_list.invoice_type_code_l(i)||'</td><td>'||
4887 repop_status_list.invoice_amount_l(i)||'</td><td>'||
4888 repop_status_list.vendor_name_l(i)||'</td><td>'||
4889 repop_status_list.vendor_site_code_l(i)||'</td><td>'||
4890 repop_status_list.org_id_l(i)||'</td><td>'||
4891 repop_status_list.set_of_books_id_l(i)||'</td><td>'||
4892 repop_status_list.process_flag_l(i)||'</td><td>'||
4893 repop_status_list.error_reason_l(i)||'</td>';
4894 print(l_message);
4895 END LOOP;
4896 END IF;
4897 EXIT WHEN drv_status_cur%NOTFOUND;
4898 END LOOP;
4899
4900 l_debug_info := 'After Printing the details of the status of the effected Entries';
4901
4902 l_message := '</table>';
4903 print(l_message);
4904
4905 -- bug10056653, changed the display message
4906 --
4907 l_message := '<b><u>Following are the details of the invoices for which '||
4908 'repopulation of Prepay History(AP_PREPAY_HISTORY_ALL) and '||
4909 'Prepay App Dists(AP_PREPAY_APP_DISTS) failed </u></b>';
4910 Print(l_message);
4911
4912 l_debug_info := ' Printing the details of the repopulated prepayment ';
4913 l_message := '<table border="5">'||
4914 '<th>INVOICE_ID</th>'||
4915 '<th>INVOICE_NUM</th>'||
4916 '<th>INVOICE_TYPE_LOOKUP_CODE</th>'||
4917 '<th>INVOICE_AMOUNT</th>'||
4918 '<th>VENDOR_NAME</th>'||
4919 '<th>VENDOR_SITE</th>'||
4920 '<th>ORG_ID</th>'||
4921 '<th>LEDGER_ID</th>'||
4922 '<th>PROCESS_FLAG</th>'||
4923 '<th>RETURN_STATUS</th>';
4924
4925 print(l_message);
4926
4927 /*Bug 10638465*/
4928 l_sql_stmt :=
4929 ' SELECT distinct dr.invoice_id, '||
4930 ' ai.invoice_num, '||
4931 ' ai.invoice_type_lookup_code, '||
4932 ' ai.invoice_amount, '||
4933 ' av.vendor_name, '||
4934 ' avs.vendor_site_code, '||
4935 ' ai.org_id, '||
4936 ' aps.set_of_books_id, '||
4937 ' dr.process_flag, '||
4938 ' NVL(dr.return_status,''Data Processed'') '||
4939 ' FROM '||l_driver_table||' dr, '||
4940 ' ap_invoices_all ai, '||
4941 ' ap_suppliers av, '||
4942 ' ap_supplier_sites_all avs, '||
4943 ' ap_system_parameters_all aps '||
4944 ' WHERE ai.invoice_id = dr.invoice_id'||
4945 ' AND ai.org_id = aps.org_id'||
4946 ' AND ai.vendor_id = av.vendor_id'||
4947 ' AND ai.vendor_site_id = avs.vendor_site_id'||
4948 ' AND dr.process_flag = ''E'''||
4949 ' ORDER BY dr.invoice_id';
4950
4951 l_debug_info := 'Before Opening the cursor for Printing the details '||
4952 'of the failed Entries ';
4953 OPEN drv_status_cur FOR l_sql_stmt;
4954 LOOP
4955
4956 l_debug_info := 'Before fetch for a batchsize for Printing details of Original and Rev';
4957 FETCH drv_status_cur
4958 BULK COLLECT INTO repop_status_list.source_id_l,
4959 repop_status_list.invoice_num_l,
4960 repop_status_list.invoice_type_code_l,
4961 repop_status_list.invoice_amount_l,
4962 repop_status_list.vendor_name_l,
4963 repop_status_list.vendor_site_code_l,
4964 repop_status_list.org_id_l,
4965 repop_status_list.set_of_books_id_l,
4966 repop_status_list.process_flag_l,
4967 repop_status_list.error_reason_l LIMIT 1000;
4968
4969 l_debug_info := 'Before looping for the batchsize';
4970 IF repop_status_list.source_id_l.COUNT > 0 THEN
4971 FOR i IN repop_status_list.source_id_l.FIRST..repop_status_list.source_id_l.LAST LOOP
4972 l_message :=
4973 '<tr><td>'||to_char(repop_status_list.source_id_l(i))||'</td><td>'||
4974 repop_status_list.invoice_num_l(i)||'</td><td>'||
4975 repop_status_list.invoice_type_code_l(i)||'</td><td>'||
4976 repop_status_list.invoice_amount_l(i)||'</td><td>'||
4977 repop_status_list.vendor_name_l(i)||'</td><td>'||
4978 repop_status_list.vendor_site_code_l(i)||'</td><td>'||
4979 repop_status_list.org_id_l(i)||'</td><td>'||
4980 repop_status_list.set_of_books_id_l(i)||'</td><td>'||
4981 repop_status_list.process_flag_l(i)||'</td><td>'||
4982 repop_status_list.error_reason_l(i)||'</td>';
4983 print(l_message);
4984 END LOOP;
4985 END IF;
4986 EXIT WHEN drv_status_cur%NOTFOUND;
4987 END LOOP;
4988
4989 l_debug_info := 'After Printing the details of the status of the affected Entries';
4990
4991 l_message := '</table>';
4992 print(l_message);
4993
4994 EXCEPTION
4995 WHEN OTHERS THEN
4996 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
4997 ' in '||l_calling_sequence||' while performing '||l_debug_info;
4998
4999 -- Bug10056653, Printing exception in the log
5000 --
5001 Print(l_error_log);
5002
5003 -- Bug10056653, printing end of table as well
5004 --
5005 l_message := '</table>';
5006 print(l_message);
5007
5008
5009 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5010 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name,
5011 l_error_log);
5012 END IF;
5013
5014
5015 APP_EXCEPTION.RAISE_EXCEPTION();
5016 END repop_prepay_dists;
5017
5018 PROCEDURE del_cascade_adj_entries
5019 (p_bug_no IN NUMBER,
5020 p_driver_table IN VARCHAR2,
5021 p_calling_sequence IN VARCHAR2) IS
5022
5023 l_check_invoice_col VARCHAR2(1) := 'N';
5024 l_check_event_col VARCHAR2(1) := 'N';
5025 l_check_ret_stat_col VARCHAR2(1) := 'N';
5026 l_check_process_col VARCHAR2(1) := 'N';
5027 l_sql_stmt LONG;
5028 l_sql_errors NUMBER;
5029 l_rowcount NUMBER;
5030 l_bug_no VARCHAR2(100);
5031 l_driver_table ALL_TABLES.TABLE_NAME%TYPE;
5032 l_debug_info VARCHAR2(4000);
5033 l_error_log LONG;
5034 l_message VARCHAR2(4000);
5035 l_calling_sequence VARCHAR2(4000);
5036 l_procedure_name CONSTANT VARCHAR2(30) := 'del_cascade_adj_entries';
5037 TYPE refcurtyp IS REF CURSOR;
5038 drv_invoices_cur REFCURTYP;
5039 drv_status_cur REFCURTYP;
5040
5041 TYPE source_id_int_1_t IS TABLE OF XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1%TYPE INDEX BY BINARY_INTEGER;
5042 TYPE invoice_num_t IS TABLE OF AP_INVOICES_ALL.INVOICE_NUM%TYPE INDEX BY BINARY_INTEGER;
5043 TYPE event_id_t IS TABLE OF AP_INVOICES_ALL.INVOICE_NUM%TYPE INDEX BY BINARY_INTEGER;
5044 TYPE process_flag_t IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
5045 TYPE error_reason_t IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
5046 l_check_canc_dt_col VARCHAR2(1) := 'N'; /*Bug 13602149*/
5047 TYPE cancelled_date_t IS TABLE OF AP_INVOICES_ALL.CANCELLED_DATE%TYPE INDEX BY BINARY_INTEGER; /*Bug 13602149*/
5048
5049 TYPE del_cascade_rec_type IS RECORD
5050 (source_id_l source_id_int_1_t,
5051 event_id_l event_id_t,
5052 cancelled_date_l cancelled_date_t,
5053 process_flag_l process_flag_t
5054 );
5055
5056 del_cascade_inv_list DEL_CASCADE_REC_TYPE;
5057
5058 TYPE del_cascade_status_details IS RECORD
5059 (source_id_l source_id_int_1_t,
5060 event_id_l event_id_t,
5061 invoice_num_l invoice_num_t,
5062 process_flag_l process_flag_t,
5063 error_reason_l error_reason_t
5064 );
5065
5066 del_cascade_status_list DEL_CASCADE_STATUS_DETAILS;
5067
5068 BEGIN
5069
5070 l_bug_no := p_bug_no;
5071 l_calling_sequence := 'del_cascade_adj_entries <- '||p_calling_sequence;
5072
5073 l_debug_info := 'Constructing the name of the driver table';
5074 IF p_driver_table IS NOT NULL THEN
5075 l_driver_table := upper(p_driver_table);
5076 ELSE
5077 l_driver_table := 'AP_TEMP_DATA_DRIVER_'||l_bug_no;
5078 END IF;
5079
5080 l_debug_info := 'Checking the presence of the O/P values on the '||
5081 'temp data driver';
5082 BEGIN
5083 l_debug_info := 'Checking if the invoice_id column is there in the driver table';
5084 SELECT 'Y'
5085 INTO l_check_invoice_col
5086 FROM sys.all_tab_columns
5087 WHERE table_name = l_driver_table
5088 AND column_name = 'INVOICE_ID';
5089
5090 EXCEPTION
5091 WHEN OTHERS THEN
5092 l_check_invoice_col := 'N';
5093 END;
5094
5095 BEGIN
5096 l_debug_info := 'Checking if the del_cas_rt_sts column is there in the driver table';
5097
5098 SELECT 'Y'
5099 INTO l_check_ret_stat_col
5100 FROM sys.all_tab_columns
5101 WHERE table_name = l_driver_table
5102 AND column_name = 'DEL_CAS_RT_STS'; --bug10056653, changed column name to CAPS
5103
5104 EXCEPTION
5105 WHEN OTHERS THEN
5106 l_check_ret_stat_col := 'N';
5107 END;
5108
5109 BEGIN
5110 l_debug_info := 'Checking if the process_flag column is there in the driver table';
5111 SELECT 'Y'
5112 INTO l_check_process_col
5113 FROM sys.all_tab_columns
5114 WHERE table_name = l_driver_table
5115 AND column_name = 'PROCESS_FLAG';
5116
5117 EXCEPTION
5118 WHEN OTHERS THEN
5119 l_debug_info := 'Adding process_flag column in the driver table if not present';
5120 l_sql_stmt := ' ALTER TABLE '||l_driver_table||' ADD '||
5121 ' (PROCESS_FLAG VARCHAR2(1)) ';
5122 EXECUTE IMMEDIATE l_sql_stmt;
5123
5124 l_debug_info := 'Updating the driver tables process_flag column to ''Y'' as default value';
5125 l_sql_stmt := ' UPDATE '||l_driver_table||
5126 ' SET process_flag = ''Y''';
5127 EXECUTE IMMEDIATE l_sql_stmt;
5128
5129 END;
5130
5131 BEGIN
5132 l_debug_info := 'Checking if the event_id column is there in the driver table';
5133 SELECT 'Y'
5134 INTO l_check_event_col
5135 FROM sys.all_tab_columns
5136 WHERE table_name = l_driver_table
5137 AND column_name = 'EVENT_ID';
5138
5139 EXCEPTION
5140 WHEN OTHERS THEN
5141 l_check_event_col := 'N';
5142 END;
5143
5144 IF l_check_event_col = 'Y' AND
5145 l_check_invoice_col = 'N' THEN
5146
5147 l_debug_info := 'Adding invoice_id column to the driver table if it doesnot exist';
5148 l_sql_stmt := ' ALTER TABLE '||l_driver_table||' ADD '||
5149 ' (INVOICE_ID NUMBER(15,0)) ';
5150 EXECUTE IMMEDIATE l_sql_stmt;
5151
5152 l_debug_info := 'Populating the invoice_id column in the driver table';
5153 l_sql_stmt := 'UPDATE '||l_driver_table||' dr '||
5154 ' SET invoice_id = (SELECT DISTINCT invoice_id '||
5155 ' FROM ap_invoice_distributions_all '||
5156 ' WHERE accounting_event_id = nvl(dr.event_id,-99))';
5157 EXECUTE IMMEDIATE l_sql_stmt;
5158
5159 END IF;
5160
5161 IF l_check_event_col = 'N' AND
5162 l_check_invoice_col = 'N' THEN
5163
5164 l_debug_info := 'Either the invoice_id or the event_id column needs to be present in driver table '
5165 ||l_driver_table||', exiting Cascade deletion';
5166
5167 -- bug10056653, printing the reason for error in the GDF log
5168 --
5169 Print(l_debug_info);
5170
5171 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5172 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name,
5173 l_debug_info);
5174 END IF;
5175
5176 APP_EXCEPTION.RAISE_EXCEPTION();
5177
5178 END IF;
5179
5180 IF l_check_ret_stat_col = 'N' THEN
5181 l_debug_info := 'Adding Return status column in the driver table';
5182 l_sql_stmt := ' ALTER TABLE '||l_driver_table||' ADD '||
5183 ' (del_cas_rt_sts VARCHAR2(1000)) ';
5184 EXECUTE IMMEDIATE l_sql_stmt;
5185 END IF;
5186
5187 /*Bug 13602149*/
5188 BEGIN
5189 l_debug_info := 'Checking if cancelled_date column is there in the driver table';
5190
5191 SELECT 'Y'
5192 INTO l_check_canc_dt_col
5193 FROM sys.all_tab_columns
5194 WHERE table_name = l_driver_table
5195 AND column_name = 'CANCELLED_DATE';
5196
5197 EXCEPTION
5198 WHEN OTHERS THEN
5199 l_check_canc_dt_col := 'N';
5200 END;
5201
5202 IF l_check_canc_dt_col = 'N' THEN
5203
5204 l_debug_info := 'Adding cancelled_date column to the driver table if it does not exist';
5205
5206 l_sql_stmt := ' ALTER TABLE '||l_driver_table||' ADD '||
5207 ' (CANCELLED_DATE DATE) ';
5208 EXECUTE IMMEDIATE l_sql_stmt;
5209
5210 l_debug_info := 'Populating the cancelled_date column in the driver table';
5211 print(l_debug_info);
5212 l_sql_stmt := 'UPDATE '||l_driver_table||' dr '||
5213 ' SET cancelled_date = (SELECT cancelled_date '||
5214 ' FROM ap_invoices_all '||
5215 ' WHERE invoice_id = dr.invoice_id)';
5216 EXECUTE IMMEDIATE l_sql_stmt;
5217
5218 END IF;
5219
5220 /*Bug 10638465*/
5221 l_debug_info := 'Constructing the dynamic sql statement ';
5222 l_sql_stmt := ' SELECT DISTINCT dr.invoice_id, dr.event_id, dr.cancelled_date, dr.process_flag '||
5223 ' FROM '||l_driver_table||' dr '||
5224 ' WHERE dr.process_flag NOT IN (''N'',''E'') ';
5225
5226 l_debug_info := 'After the l_sql_stmt, before opening the cursor';
5227 OPEN drv_invoices_cur FOR l_sql_stmt;
5228 LOOP
5229 FETCH drv_invoices_cur
5230 BULK COLLECT INTO del_cascade_inv_list.source_id_l,
5231 del_cascade_inv_list.event_id_l,
5232 del_cascade_inv_list.cancelled_date_l,
5233 del_cascade_inv_list.process_flag_l LIMIT 1000;
5234
5235 l_debug_info := 'After the fetch, before looping for the batch of 1000';
5236 IF del_cascade_inv_list.source_id_l.COUNT > 0 THEN
5237 FOR i IN del_cascade_inv_list.source_id_l.FIRST..del_cascade_inv_list.source_id_l.LAST LOOP
5238 /*Bug 13602149*/
5239 l_sql_stmt := ' UPDATE ap_invoices_all '||
5240 ' SET force_revalidation_flag = ''Y'','||
5241 ' cancelled_date = NULL'||
5242 ' WHERE invoice_id = '||del_cascade_inv_list.source_id_l(i)||
5243 ' AND force_revalidation_flag != ''Y''';
5244 EXECUTE IMMEDIATE l_sql_stmt;
5245 l_rowcount := SQL%ROWCOUNT;
5246
5247 SAVEPOINT BEFORE_DELETION;
5248 IF delete_cascade_adjustments
5249 ('AP_INVOICES',
5250 del_cascade_inv_list.source_id_l(i),
5251 del_cascade_inv_list.event_id_l(i)
5252 ) THEN
5253
5254 l_debug_info := 'After calling delete_cascade_adjustments..';
5255
5256
5257 COMMIT;
5258
5259 ELSE
5260 l_debug_info := 'Failed in deleting the data';
5261 l_sql_stmt := ' UPDATE '||l_driver_table||' dr '||
5262 ' SET del_cas_rt_sts = '||''''||l_debug_info||''''|| /*bug:12764043*/
5263 ' WHERE dr.invoice_id ='|| del_cascade_inv_list.source_id_l(i); /*bug:11660129*/
5264
5265 EXECUTE IMMEDIATE l_sql_stmt;
5266 ROLLBACK TO BEFORE_DELETION;
5267 END IF;
5268 IF l_rowcount <> 0 THEN
5269 /*Bug 13602149*/
5270 l_sql_stmt := ' UPDATE ap_invoices_all '||
5271 ' SET force_revalidation_flag = ''N'','||
5272 ' cancelled_date = NVL('||''''||del_cascade_inv_list.cancelled_date_l(i)||''''||',NULL)'||
5273 ' WHERE invoice_id = '||del_cascade_inv_list.source_id_l(i);
5274 EXECUTE IMMEDIATE l_sql_stmt;
5275 END IF;
5276 l_rowcount := 0;
5277 END LOOP;
5278 END IF;
5279 EXIT WHEN drv_invoices_cur%NOTFOUND;
5280 END LOOP;
5281
5282 l_message := '<b><u>Following are the details of the invoices for which '||
5283 'cascade events, if any, are deleted </u></b>';
5284 Print(l_message);
5285
5286 l_debug_info := ' Printing the details of the repopulated prepayment ';
5287
5288 -- bug10056653, changed the table header to show RETURN_STATUS instead of
5289 -- del_cas_rt_sts.
5290 --
5291 l_message := '<table border="5">'||
5292 '<th>INVOICE_ID</th>'||
5293 '<th>EVENT_ID</th>'||
5294 '<th>INVOICE_NUM</th>'||
5295 '<th>PROCESS_FLAG</th>'||
5296 '<th>RETURN_STATUS</th>';
5297
5298 print(l_message);
5299
5300 /*Bug 10638465*/
5301 l_sql_stmt :=
5302 ' SELECT distinct dr.invoice_id, '||
5303 ' dr.event_id, '||
5304 ' ai.invoice_num, '||
5305 ' dr.process_flag, '||
5306 ' NVL(dr.del_cas_rt_sts,''Data Processed'') '||
5307 ' FROM '||l_driver_table||' dr, '||
5308 ' ap_invoices_all ai'||
5309 ' WHERE ai.invoice_id = dr.invoice_id'||
5310 ' AND dr.process_flag NOT IN (''N'',''E'') '||
5311 ' ORDER BY dr.invoice_id';
5312
5313 l_debug_info := 'Before Opening the cursor for Printing the details '||
5314 'of the Processed Entries ';
5315 OPEN drv_status_cur FOR l_sql_stmt;
5316 LOOP
5317
5318 l_debug_info := 'Before fetch for a batchsize for Printing details of Original and Rev';
5319 FETCH drv_status_cur
5320 BULK COLLECT INTO del_cascade_status_list.source_id_l,
5321 del_cascade_status_list.event_id_l,
5322 del_cascade_status_list.invoice_num_l,
5323 del_cascade_status_list.process_flag_l,
5324 del_cascade_status_list.error_reason_l LIMIT 1000;
5325
5326 l_debug_info := 'Before looping for the batchsize';
5327 IF del_cascade_status_list.source_id_l.COUNT > 0 THEN
5328 FOR i IN del_cascade_status_list.source_id_l.FIRST..del_cascade_status_list.source_id_l.LAST LOOP
5329 l_message :=
5330 '<tr><td>'||to_char(del_cascade_status_list.source_id_l(i))||'</td><td>'||
5331 del_cascade_status_list.event_id_l(i)||'</td><td>'||
5332 del_cascade_status_list.invoice_num_l(i)||'</td><td>'||
5333 del_cascade_status_list.process_flag_l(i)||'</td><td>'||
5334 del_cascade_status_list.error_reason_l(i)||'</td>';
5335 print(l_message);
5336 END LOOP;
5337 END IF;
5338 EXIT WHEN drv_status_cur%NOTFOUND;
5339 END LOOP;
5340
5341 l_debug_info := 'After Printing the details of the status of the affected Entries';
5342
5343 l_message := '</table>';
5344 print(l_message);
5345
5346 EXCEPTION
5347 WHEN OTHERS THEN
5348 l_error_log := ' Encountered an Unhandled Exception, '||SQLCODE||'-'||SQLERRM||
5349 ' in '||l_calling_sequence||' while performing '||l_debug_info;
5350
5351 -- Bug10056653, Printing exception in the log
5352 --
5353 Print(l_error_log);
5354
5355 -- Bug10056653, Printing table closing tag
5356 --
5357 l_message := '</table>';
5358 print(l_message);
5359
5360 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
5361 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_procedure_name,
5362 l_error_log);
5363 END IF;
5364
5365 APP_EXCEPTION.RAISE_EXCEPTION();
5366
5367 END del_cascade_adj_entries;
5368
5369 /*Added Function UOM_CONVERT for Bug9756279*/
5370 FUNCTION UOM_CONVERT
5371 (from_unit in varchar2
5372 ,to_unit in varchar2
5373 ,item_id in number)
5374 RETURN NUMBER
5375 IS
5376 l_rate NUMBER;
5377 BEGIN
5378 l_rate := po_uom_s.po_uom_convert(from_unit,to_unit,item_id);
5379 return l_rate;
5380
5381 EXCEPTION
5382 WHEN OTHERS THEN
5383 return null;
5384 END UOM_CONVERT;
5385
5386
5387 --Bug 10072990 start
5388 PROCEDURE undo_inv_accounting(
5389 p_source_table IN VARCHAR2,
5390 p_source_id IN NUMBER,
5391 p_event_id IN NUMBER DEFAULT NULL,
5392 p_skip_date_calc IN VARCHAR2 DEFAULT 'N',
5393 p_undo_date IN DATE,
5394 p_undo_period IN VARCHAR2,
5395 p_bug_id IN NUMBER DEFAULT NULL,
5396 p_gl_date IN DATE DEFAULT NULL,
5397 p_rev_event_id OUT NOCOPY NUMBER,
5398 p_new_event_id OUT NOCOPY NUMBER,
5399 p_return_code OUT NOCOPY VARCHAR2,
5400 p_calling_sequence IN VARCHAR2 ) IS
5401
5402 CURSOR events_to_process( p_invoice_id NUMBER )
5403 IS
5404 SELECT /*LEADING(ASP, XTE)*/
5405 DISTINCT xe.event_id
5406 , xe.event_type_code --bug12833171
5407 , security_id_int_1 cur_org_id
5408 , xe.event_date gl_date
5409 , nvl(xe.budgetary_control_flag, 'N') budgetary_control_flag /*Bug 12975723*/
5410 , MAX(DECODE(NVL(gl_transfer_status_code,'N'), 'Y', 'Y', 'N')) gl_transfer_status_code /* bug 13911650*/
5411 FROM xla_transaction_entities_upg xte
5412 , xla_events xe
5413 , xla_ae_headers xah
5414 , ap_system_parameters_all asp
5415 WHERE xte.entity_id = xe.entity_id
5416 AND xe.application_id = 200
5417 AND xte.entity_code = 'AP_INVOICES'
5418 AND NVL( source_id_int_1, - 99 ) = p_invoice_id
5419 AND xe.event_status_code = 'P'
5420 AND xe.process_status_code = 'P'
5421 AND xah.event_id = xe.event_id
5422 AND xe.event_id = NVL( p_event_id, xe.event_id )
5423 AND xah.application_id = 200
5424 AND xte.application_id = 200
5425 /* AND xah.ledger_id = xte.ledger_id Bug 13900488 */
5426 AND xte.ledger_id = asp.set_of_books_id
5427 AND xte.security_id_int_1 = asp.org_id
5428 GROUP BY xe.event_id
5429 , xe.event_type_code --bug12833171
5430 , security_id_int_1
5431 , xe.event_date
5432 , nvl(xe.budgetary_control_flag, 'N') /* bug 13911650, 12975723*/
5433 ORDER BY decode(nvl(xe.budgetary_control_flag,'N'),'N',1,2);
5434
5435 type events_to_process_tab_type IS TABLE OF events_to_process%rowtype;
5436 events_to_process_tab events_to_process_tab_type;
5437
5438 CURSOR check_period_status( p_date DATE, p_org_id NUMBER )
5439 IS
5440 SELECT DISTINCT gps.period_name
5441 FROM gl_period_statuses gps
5442 , ap_system_parameters_all asp
5443 WHERE gps.application_id = 200
5444 AND gps.set_of_books_id = asp.set_of_books_id
5445 AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
5446 AND p_date BETWEEN TRUNC( gps.start_date ) AND TRUNC( gps.end_date )
5447 AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
5448 AND gps.closing_status IN( 'O', 'F' )
5449 INTERSECT
5450 SELECT DISTINCT gps.period_name
5451 FROM gl_period_statuses gps
5452 , ap_system_parameters_all asp
5453 WHERE gps.application_id = 101
5454 AND gps.set_of_books_id = asp.set_of_books_id
5455 AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
5456 AND p_date BETWEEN TRUNC( gps.start_date ) AND TRUNC( gps.end_date )
5457 AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
5458 AND gps.closing_status IN( 'O', 'F' );
5459
5460 CURSOR check_open_period( p_org_id NUMBER )
5461 IS
5462 SELECT period_name
5463 , end_date
5464 FROM
5465 ( SELECT DISTINCT gps.period_name
5466 , TRUNC( gps.end_date ) end_date
5467 FROM gl_period_statuses gps
5468 , ap_system_parameters_all asp
5469 WHERE gps.application_id = 200
5470 AND gps.set_of_books_id = asp.set_of_books_id
5471 AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
5472 AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
5473 AND gps.closing_status IN( 'O', 'F' )
5474 INTERSECT
5475 SELECT DISTINCT gps.period_name
5476 , TRUNC( gps.end_date ) end_date
5477 FROM gl_period_statuses gps
5478 , ap_system_parameters_all asp
5479 WHERE gps.application_id = 101
5480 AND gps.set_of_books_id = asp.set_of_books_id
5481 AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
5482 AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
5483 AND gps.closing_status IN( 'O', 'F' )
5484 ORDER BY end_date
5485 )
5486 WHERE rownum < 2;
5487
5488
5489 CURSOR check_entered_gl_date( p_org_id NUMBER )
5490 IS
5491 SELECT DISTINCT gps.period_name
5492 FROM gl_period_statuses gps
5493 , ap_system_parameters_all asp
5494 WHERE gps.application_id = 200
5495 AND p_gl_date BETWEEN TRUNC( gps.start_date ) AND TRUNC( gps.end_date )
5496 AND gps.set_of_books_id = asp.set_of_books_id
5497 AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
5498 AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
5499 AND gps.closing_status IN( 'O', 'F' )
5500 INTERSECT
5501 SELECT DISTINCT gps.period_name
5502 FROM gl_period_statuses gps
5503 , ap_system_parameters_all asp
5504 WHERE gps.application_id = 101
5505 AND p_gl_date BETWEEN TRUNC( gps.start_date ) AND TRUNC( gps.end_date )
5506 AND gps.set_of_books_id = asp.set_of_books_id
5507 AND NVL( gps.adjustment_period_flag, 'N' ) = 'N'
5508 AND NVL( asp.org_id, - 99 ) = NVL( p_org_id, - 99 )
5509 AND gps.closing_status IN( 'O', 'F' );
5510
5511 l_debug_info VARCHAR2(4000);
5512 l_api_version NUMBER;
5513 l_init_msg_list VARCHAR2(300);
5514 l_application_id INTEGER;
5515 l_reversal_method VARCHAR2(300);
5516 l_gl_date DATE;
5517 l_post_to_gl_flag VARCHAR2(3);
5518 x_msg_count NUMBER;
5519 x_msg_data VARCHAR2(4000);
5520 x_rev_ae_header_id INTEGER;
5521 x_rev_event_id INTEGER;
5522 x_rev_entity_id INTEGER;
5523 x_new_event_id INTEGER;
5524 x_new_entity_id INTEGER;
5525 l_rev_event_id INTEGER;
5526 l_new_event_id INTEGER;
5527 l_source_id NUMBER;
5528 l_return_status VARCHAR2(300);
5529 l_event_status_code XLA_EVENTS.EVENT_STATUS_CODE%TYPE;
5530 debug_info VARCHAR2(50) := 'Undo_Inv_Accounting';
5531 l_calling_sequence VARCHAR2(4000);
5532 l_rel_act_acct_event_cnt NUMBER;
5533
5534
5535 l_entered_date DATE;
5536 l_entered_Period VARCHAR2(15);
5537 l_Period_Name VARCHAR2(15);
5538 l_cur_Period_Name VARCHAR2(15);
5539 NULL_VALUE NUMBER := NULL;
5540 l_table_name VARCHAR2(20) := 'ALL_TABLES';
5541 ins_ap_undo_event_log_stmt VARCHAR2(200) := 'INSERT INTO AP_undo_event_log('
5542 ||'EVENT_ID,E2,E3,STATUS,INVOICE_ID,CHECK_ID, BUG_ID) '
5543 ||'VALUES(:1, :2, :3, :4, :5, :6, :7)';
5544 log_table_exists_stmt VARCHAR2(200) := 'select count(*) from '||l_table_name
5545 ||' where table_name = ''AP_UNDO_EVENT_LOG'' ';
5546 log_table_exists NUMBER ;
5547 l_procedure_name CONSTANT VARCHAR2(30) := 'Undo_Accounting';
5548 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5549
5550 BEGIN
5551
5552 l_calling_sequence := 'AP_ACCTG_DATA_FIX_PKG.undo_Inv_accounting <- '||p_calling_sequence;
5553
5554 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5555
5556 l_log_msg := 'Begin of procedure '|| l_procedure_name;
5557
5558 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5559 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin',l_log_msg);
5560 END IF;
5561
5562 l_api_version := 1.0;
5563 l_init_msg_list := fnd_api.g_true;
5564 l_application_id := 200;
5565 l_reversal_method := 'SIDE';
5566 l_post_to_gl_flag := 'N';
5567 l_source_id := p_source_id;
5568
5569 EXECUTE IMMEDIATE log_table_exists_stmt into log_table_exists;
5570
5571 IF ( log_table_exists = 0) THEN
5572
5573 l_log_msg := 'Before creating Table AP_undo_event_log';
5574 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5575 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name,l_log_msg);
5576 END IF;
5577
5578 BEGIN
5579 EXECUTE IMMEDIATE 'CREATE TABLE AP_undo_event_log
5580 (EVENT_ID NUMBER
5581 ,E2 integer
5582 ,E3 integer
5583 ,STATUS varchar2(300)
5584 ,INVOICE_ID NUMBER
5585 ,CHECK_ID NUMBER
5586 ,BUG_ID NUMBER)';
5587
5588 l_log_msg := 'Created table AP_undo_event_log';
5589
5590 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5591 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_procedure_name,l_log_msg);
5592 END IF;
5593
5594 EXCEPTION
5595 WHEN OTHERS THEN
5596
5597 l_log_msg := 'Could not create table AP_undo_event_log';
5598 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5599 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_procedure_name,l_log_msg);
5600 END IF;
5601
5602 END;
5603 END IF;
5604
5605 OPEN Events_to_Process(l_source_id);
5606 FETCH Events_to_Process BULK COLLECT INTO Events_to_Process_tab;
5607 CLOSE Events_to_Process;
5608
5609 IF( Events_to_Process_tab.COUNT = 0 ) THEN
5610 l_log_msg := 'No events exist for the parameters passed : '||p_Source_Table || ' , '||p_Source_Id;
5611 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5612 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5613 END IF;
5614 END IF;
5615
5616 FOR i IN 1..Events_to_Process_tab.COUNT
5617 LOOP
5618 IF( p_skip_date_calc = 'Y' AND p_undo_date IS NOT NULL AND p_undo_period IS NOT NULL ) THEN
5619 l_gl_date := p_undo_date;
5620 l_period_name := p_undo_period;
5621 ELSE
5622 l_gl_Date := Events_to_Process_tab( i ).gl_Date;
5623 OPEN Check_period_Status( l_gl_Date, Events_to_Process_tab( i ).Cur_Org_Id );
5624 FETCH Check_period_Status INTO l_Period_Name;
5625 IF( Check_period_Status%NOTFOUND ) THEN
5626 l_Period_Name := NULL;
5627 END IF;
5628
5629 CLOSE Check_period_Status;
5630
5631 IF( l_Period_Name IS NULL ) THEN
5632 IF( p_Gl_Date IS NOT NULL ) THEN
5633 OPEN Check_Entered_Gl_date( Events_to_Process_tab( i ).Cur_Org_Id );
5634 FETCH Check_Entered_Gl_date INTO l_entered_period;
5635 IF( Check_Entered_Gl_date%NOTFOUND ) THEN
5636 l_log_msg := 'The entered date did not have any period, please recheck '||' exiting.....';
5637 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5638 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5639 END IF;
5640 RETURN;
5641 END IF;
5642 CLOSE Check_Entered_Gl_date;
5643 l_gl_Date := p_Gl_Date;
5644 l_Period_Name := l_entered_period;
5645 ELSE -- p_Gl_Date is null
5646 FOR Check_Open_Period_rec IN Check_Open_Period( Events_to_Process_tab( i ).Cur_Org_Id )
5647 LOOP
5648 l_gl_Date := Check_Open_Period_rec.End_Date;
5649 l_Period_Name := Check_Open_Period_rec.Period_Name;
5650 END LOOP;
5651 END IF; -- p_Gl_Date is null
5652
5653 END IF; -- transaction date in closed period
5654 END IF;
5655 mo_Global.Set_Policy_Context( 'S', Events_to_Process_tab( i ).Cur_Org_Id );
5656 l_log_msg := 'Set Org context to '||Events_to_Process_tab( i ).Cur_Org_Id;
5657
5658 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5659 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5660 END IF;
5661
5662 l_return_status := NULL;
5663 BEGIN
5664 IF( Events_to_Process_tab( i ).budgetary_control_flag = 'Y' ) THEN
5665 SELECT COUNT( 1 )
5666 INTO l_rel_act_acct_event_cnt
5667 FROM ap_invoice_distributions_all aid
5668 , xla_events xe
5669 WHERE aid.invoice_id = p_source_id
5670 AND aid.bc_event_id = Events_to_Process_tab( i ).event_id
5671 AND xe.event_id = aid.accounting_event_id
5672 AND xe.event_status_code = 'P'
5673 AND xe.application_id = 200;
5674 ELSE
5675 l_rel_act_acct_event_cnt := 0;
5676 END IF;
5677
5678 IF( Events_to_Process_tab( i ).gl_Transfer_Status_Code = 'Y' AND l_rel_act_acct_event_cnt = 0 ) THEN
5679 Debug_Info := 'xla_DataFixes_Pub.Reverse_Journal_entries';
5680 l_log_msg := 'Calling xla_DataFixes_Pub.Reverse_Journal_entries';
5681 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5682 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5683 END IF;
5684
5685 xla_datafixes_pub.reverse_journal_entries(
5686 p_api_version => l_api_version
5687 , p_init_msg_list => l_init_msg_list
5688 , p_application_id => l_application_id
5689 , p_event_id => events_to_process_tab( i ).event_id
5690 , p_reversal_method => l_reversal_method
5691 , p_gl_date => l_gl_date
5692 , p_post_to_gl_flag => l_post_to_gl_flag
5693 , x_return_status => l_return_status
5694 , x_msg_count => x_msg_count
5695 , x_msg_data => x_msg_data
5696 , x_rev_ae_header_id => x_rev_ae_header_id
5697 , x_rev_event_id => x_rev_event_id
5698 , x_rev_entity_id => x_rev_entity_id
5699 , x_new_event_id => x_new_event_id
5700 , x_new_entity_id => x_new_entity_id );
5701 l_Rev_Event_Id := x_Rev_Event_Id;
5702 l_New_Event_Id := x_New_Event_Id;
5703 p_rev_event_id := x_Rev_Event_Id;
5704 p_new_event_id := x_New_Event_Id;
5705 l_log_msg := 'l_Return_Status='||l_Return_Status;
5706
5707 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5708 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5709 END IF;
5710
5711 IF( x_msg_Count > 0 OR l_Return_Status = 'U' ) THEN
5712 l_log_msg := 'Undo_Accounting : Error in xla_DataFixes_Pub.Reverse_Journal_entries:'|| x_msg_Data;
5713
5714 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5715 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5716 END IF;
5717 p_return_code := 'XLA_ERROR';
5718 END IF;
5719 ELSIF( Events_to_Process_tab( i ).gl_Transfer_Status_Code = 'N' ) THEN
5720 Debug_Info := 'xla_DataFixes_Pub.delete_journal_entries';
5721 l_log_msg := 'Calling xla_datafixes_pub.delete_journal_entries';
5722 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5723 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5724 END IF;
5725
5726 xla_datafixes_pub.delete_journal_entries
5727 ( p_api_version => l_aPi_Version
5728 , p_init_msg_list => l_InIt_msg_List
5729 , p_application_id => l_Application_Id
5730 , p_event_id => Events_to_Process_tab( i ).Event_Id
5731 , x_return_status => l_Return_Status
5732 , x_msg_count => x_msg_Count
5733 , x_msg_data => x_msg_Data );
5734
5735 l_log_msg := 'l_Return_Status='||l_Return_Status;
5736 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5737 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5738 END IF;
5739
5740 IF( x_msg_Count > 0 OR l_Return_Status = 'U' ) THEN
5741 l_log_msg := 'Undo_Accounting : Error in xla_DataFixes_Pub.delete_journal_entries:'|| x_msg_Data;
5742 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5743 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5744 END IF;
5745 p_return_code := 'XLA_ERROR';
5746 END IF;
5747 ELSIF (l_rel_act_acct_event_cnt <> 0 ) THEN
5748 p_return_code := 'AP_BC_REL_EVENT_ACCOUNTED';
5749 l_Return_Status := 'U';
5750 END IF;
5751
5752 EXCEPTION WHEN OTHERS THEN
5753 l_return_status := 'U';
5754 p_return_code := 'XLA_EXCEPTION';
5755 END;
5756
5757 IF( l_Return_Status = 'S' ) THEN
5758 BEGIN
5759 SELECT event_status_code
5760 INTO l_event_status_code
5761 FROM xla_events xe
5762 WHERE xe.application_id = 200
5763 AND xe.event_id = Events_to_Process_tab( i ).Event_Id;
5764 IF l_event_status_code = 'P' THEN
5765 p_return_code := 'XLA_NO_WORK';
5766 END IF;
5767 EXCEPTION WHEN OTHERS THEN
5768 l_event_status_code := 'X';
5769 p_return_code := 'XLA_NO_WORK';
5770 END;
5771
5772 IF( l_event_status_code = 'U' AND Events_to_Process_tab(i).budgetary_control_flag <> 'Y') THEN
5773 BEGIN
5774 UPDATE ap_Invoice_Distributions_All Aid
5775 SET Accounting_Date = l_gl_Date
5776 , Posted_Flag = 'N'
5777 , Accrual_Posted_Flag = 'N'
5778 , Last_Updated_By = fnd_Global.User_Id
5779 , Period_Name = l_Period_Name
5780 WHERE Accounting_Event_Id = Events_to_Process_tab( i ).Event_Id
5781 AND Invoice_Id = l_Source_Id;
5782
5783 UPDATE ap_self_assessed_tax_dist_all asatd
5784 SET Accounting_Date = l_gl_Date
5785 , Posted_Flag = 'N'
5786 , Accrual_Posted_Flag = 'N'
5787 , Last_Updated_By = fnd_Global.User_Id
5788 , Period_Name = l_Period_Name
5789 WHERE Accounting_Event_Id = Events_to_Process_tab( i ).Event_Id
5790 AND Invoice_Id = l_Source_Id;
5791
5792 UPDATE xla_Events
5793 SET Event_Date = l_gl_Date
5794 WHERE Event_Id = Events_to_Process_tab( i ).Event_Id
5795 AND application_id = 200;
5796
5797 UPDATE ap_prepay_history_all aph
5798 SET Accounting_Date = l_gl_Date
5799 , Posted_Flag = 'N'
5800 , Last_Updated_By = fnd_Global.User_Id
5801 WHERE Accounting_Event_Id = Events_to_Process_tab( i ).Event_Id
5802 AND Invoice_Id = l_Source_Id;
5803
5804 DELETE FROM ap_prepay_app_dists
5805 WHERE PREPAY_HISTORY_ID IN
5806 (SELECT PREPAY_HISTORY_ID
5807 FROM ap_prepay_history_all
5808 WHERE Accounting_Event_Id = Events_to_Process_tab( i ).Event_Id
5809 AND transaction_type = 'PREPAYMENT APPLICATION ADJ'
5810 AND Invoice_Id = l_Source_Id);
5811
5812 EXECUTE IMMEDIATE ins_AP_undo_event_log_stmt
5813 USING Events_to_Process_tab( i ).Event_Id
5814 , l_rev_event_id
5815 , l_new_event_id
5816 , l_return_status
5817 , l_Source_Id
5818 , NULL_VALUE
5819 , p_bug_id;
5820
5821 p_return_code := 'SUCCESS';
5822 l_log_msg := 'Updated Transaction tables for Invoice';
5823
5824 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5825 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5826 END IF;
5827 EXCEPTION WHEN OTHERS THEN
5828 p_return_code := 'AP_INVOICE_ERROR';
5829 END;
5830 ELSIF ( l_event_status_code = 'U' AND Events_to_Process_tab(i).budgetary_control_flag = 'Y') THEN
5831 BEGIN
5832 DELETE gl_bc_packets
5833 WHERE event_id = events_to_process_tab(i).event_id;
5834
5835 UPDATE gms_award_distributions
5836 SET fc_status = 'N'
5837 , last_updated_by = fnd_Global.User_Id
5838 WHERE invoice_distribution_id IN
5839 (SELECT aid.invoice_distribution_id
5840 FROM ap_invoice_distributions_all aid
5841 WHERE aid.bc_event_id = events_to_process_tab(i).event_id
5842 AND aid.invoice_id = p_source_id)
5843 AND fc_status = 'A';
5844
5845 UPDATE ap_invoice_distributions_all aid
5846 SET encumbered_flag = 'N'
5847 , bc_event_id = NULL
5848 , match_status_flag = 'T'
5849 , last_updated_by = fnd_Global.User_Id
5850 WHERE aid.bc_event_id = events_to_process_tab(i).event_id
5851 AND aid.invoice_id = p_source_id;
5852
5853 UPDATE ap_self_assessed_tax_dist_all aid
5854 SET encumbered_flag = 'N'
5855 , bc_event_id = NULL
5856 , match_status_flag = 'T'
5857 , last_updated_by = fnd_Global.User_Id
5858 WHERE aid.bc_event_id = events_to_process_tab(i).event_id
5859 AND aid.invoice_id = p_source_id;
5860
5861 UPDATE ap_prepay_history_all apph
5862 SET bc_event_id = NULL
5863 , last_updated_by = fnd_Global.User_Id
5864 WHERE apph.bc_event_id = events_to_process_tab(i).event_id
5865 AND apph.invoice_id = p_source_id;
5866
5867 UPDATE ap_prepay_app_dists
5868 SET bc_event_id = NULL
5869 , last_updated_by = fnd_Global.User_Id
5870 WHERE bc_event_id = events_to_process_tab(i).event_id
5871 AND prepay_history_id IN
5872 (SELECT apph.prepay_history_id
5873 FROM ap_prepay_history_all apph
5874 WHERE apph.invoice_id = p_source_id);
5875
5876 --bug12833171
5877 UPDATE ap_invoices_all
5878 SET cancelled_date = NULL
5879 , last_updated_by = fnd_Global.User_Id
5880 WHERE invoice_id = p_source_id
5881 AND cancelled_date IS NOT NULL
5882 AND events_to_process_tab(i).event_type_code IN
5883 ('INVOICE CANCELLED', 'PREPAYMENT CANCELLED',
5884 'DEBIT MEMO CANCELLED', 'CREDIT MEMO CANCELLED');
5885
5886 del_nonfinal_xla_entries(events_to_process_tab(i).event_id ,'Y','N', l_calling_sequence);
5887
5888 ap_accounting_events_pkg.update_invoice_events_status(p_source_id ,l_calling_sequence );
5889
5890 EXECUTE IMMEDIATE ins_AP_undo_event_log_stmt
5891 USING Events_to_Process_tab( i ).Event_Id
5892 , l_rev_event_id
5893 , l_new_event_id
5894 , l_return_status
5895 , l_Source_Id
5896 , NULL_VALUE
5897 , p_bug_id;
5898
5899 p_return_code := 'SUCCESS';
5900 l_log_msg := 'Updated Transaction tables for Invoice';
5901
5902 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5903 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5904 END IF;
5905
5906 EXCEPTION WHEN OTHERS THEN
5907 p_return_code := 'AP_INVOICE_ERROR';
5908 END;
5909 END IF;
5910 ELSE
5911 IF p_return_code IS NULL THEN
5912 p_return_code := 'XLA_ERROR';
5913 END IF;
5914
5915 PRINT( 'Undo Accounting Unsuccessful for Invoice id ' ||l_Source_Id ||' event id ' ||Events_to_Process_tab( i ).Event_Id );
5916 l_log_msg := 'Undo Accounting Unsuccessful for Invoice id ' ||l_Source_Id ||' event id ' ||Events_to_Process_tab( i ).Event_Id;
5917
5918 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5919 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5920 END IF;
5921 END IF;
5922 END LOOP;
5923
5924 EXCEPTION
5925 WHEN OTHERS THEN
5926 l_log_msg := 'Exception in undo_Inv_accounting with higher number of Arguments';
5927
5928 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5929 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
5930 END IF;
5931
5932 IF( SQLCODE <> - 20001 ) THEN
5933 fnd_Message.Set_Name ( 'SQLAP', 'AP_DEBUG' );
5934 fnd_Message.Set_Token( 'ERROR', SQLERRM );
5935 fnd_Message.Set_Token( 'CALLING_SEQUENCE', p_Calling_Sequence );
5936 fnd_Message.Set_Token( 'PARAMETERS', 'p_source_id = ' ||p_Source_Id ||', p_event_id = ' ||p_event_id|| ', p_gl_date = ' ||p_gl_date);
5937 fnd_Message.Set_Token( 'DEBUG_INFO', l_debug_info );
5938 END IF;
5939 app_Exception.Raise_Exception;
5940 END undo_inv_accounting;
5941
5942 /*=============================================================================
5943 Procedure to do undo Invoice accounting.
5944
5945 a. Input parameters to be accepted -
5946 1. p_source_id - Mandatory
5947 2. p_event_id (this is optional, if p_event_id is not null
5948 given then undo only for the event)
5949 3. p_gl_date - optional [refer Point#2]
5950 4. p_bug_id
5951 5. p_calling_sequence.
5952
5953 b. It should have derivation logic for gl_date if input is
5954 null, in the order of
5955 1. Default
5956 transaction accounting date
5957 2. if transaction accounting date is in closed period
5958 p_gl_date
5959 3. if transaction accounting date and p_gl_date are
5960 in closed period
5961 Oldest open period
5962 c. Throw an error
5963 1. p_gl_date is in closed period
5964 2. p_gl_date is null AND event_date is in closed period
5965 AND no oldest open period.
5966
5967 d. If event_id is null this API should undo the all the
5968 actual events and encumbrance events of given invoice.
5969
5970 e. If event_id is passed it will do undo account the event
5971 only.
5972 1. Event_id can be actual or bc event id
5973 2. event_id should be Invoice event.
5974 3. If it is bc_event_id for that bc_event_id all
5975 related actual accounting entries should be in unaccounted
5976 status.
5977 *============================================================================*/
5978
5979 PROCEDURE undo_inv_accounting(
5980 p_source_id IN NUMBER,
5981 p_event_id IN NUMBER DEFAULT NULL,
5982 p_calling_sequence IN VARCHAR2 DEFAULT NULL,
5983 p_bug_id IN NUMBER DEFAULT NULL,
5984 p_gl_date IN DATE DEFAULT NULL) IS
5985
5986 l_source_table XLA_TRANSACTION_ENTITIES.ENTITY_CODE%TYPE;
5987 l_source_id XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1%TYPE;
5988 l_event_id XLA_EVENTS.EVENT_ID%TYPE;
5989 l_calling_sequence VARCHAR2(4000);
5990 l_bug_id NUMBER;
5991 l_rev_event_id NUMBER;
5992 l_new_event_id NUMBER;
5993 l_return_code VARCHAR2(4000);
5994 l_log_msg FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
5995 l_procedure_name VARCHAR2(1000);
5996 l_debug_info VARCHAR2(4000);
5997 l_gl_date DATE;
5998 l_is_inv_event NUMBER;
5999
6000 BEGIN
6001 l_calling_sequence := 'Overloaded undo_inv_accounting api <- '||p_calling_sequence;
6002 l_debug_info := 'Setting the variables that need to be passed';
6003 l_source_table := 'AP_INVOICES';
6004 l_source_id := p_source_id;
6005 l_event_id := p_event_id;
6006 l_bug_id := p_bug_id;
6007 l_gl_date := p_gl_date;
6008 l_procedure_name := 'AP_ACCTG_DATA_FIX_PKG.undo_inv_accounting';
6009
6010 l_debug_info := 'Calling the undo_inv_accounting with higher number of arguments';
6011 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6012 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
6013 END IF;
6014
6015 undo_inv_accounting
6016 (p_source_table => l_source_table,
6017 p_source_id => l_source_id,
6018 p_event_id => l_event_id,
6019 p_skip_date_calc => 'N',
6020 p_undo_date => NULL,
6021 p_undo_period => NULL,
6022 p_bug_id => l_bug_id,
6023 p_gl_date => l_gl_date,
6024 p_rev_event_id => l_rev_event_id,
6025 p_new_event_id => l_new_event_id,
6026 p_return_code => l_return_code,
6027 p_calling_sequence => l_calling_sequence);
6028
6029 l_debug_info := 'After undo_inv_accounting, l_return_code = '||l_return_code;
6030 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6031 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_debug_info);
6032 END IF;
6033
6034 EXCEPTION
6035 WHEN OTHERS THEN
6036 l_log_msg := 'Exception in undo_inv_accounting';
6037
6038 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6039 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name, l_log_msg );
6040 END IF;
6041
6042 IF( SQLCODE <> - 20001 ) THEN
6043 fnd_message.set_name ( 'SQLAP', 'AP_DEBUG' );
6044 fnd_message.set_token( 'ERROR', SQLERRM );
6045 fnd_message.set_token( 'CALLING_SEQUENCE', p_calling_sequence );
6046 fnd_message.set_token( 'PARAMETERS', 'p_source_id = ' ||p_source_id ||', p_event_id = ' ||p_event_id|| ', p_gl_date = '||p_gl_date );
6047 fnd_message.set_token( 'DEBUG_INFO', l_debug_info );
6048 END IF;
6049 app_exception.raise_exception;
6050 END undo_inv_accounting;
6051 -- Bug 10072990 end
6052
6053 -- Bug11673966
6054 FUNCTION get_net_prepay_app(p_event_id NUMBER)
6055 RETURN NUMBER IS
6056 l_total_prepay_amt NUMBER := -99;
6057 BEGIN
6058 SELECT NVL((SUM(AID.Amount)), 0) --bug12764043, removed ABS
6059 INTO l_total_prepay_amt
6060 FROM ap_invoice_distributions_all aid
6061 WHERE aid.line_type_lookup_code IN ('PREPAY','REC_TAX','NONREC_TAX')
6062 AND aid.prepay_distribution_id IS NOT NULL
6063 AND aid.accounting_event_id = P_Event_ID;
6064
6065 RETURN(l_total_prepay_amt);
6066
6067 EXCEPTION
6068 WHEN OTHERS THEN
6069 RETURN -99;
6070 END;
6071
6072
6073 -- Bug11673966
6074 FUNCTION get_net_prepay_acct(P_Event_ID NUMBER,
6075 P_Ledger_ID NUMBER)
6076 RETURN NUMBER IS
6077 l_total_prepay_acct NUMBER := -100;
6078 BEGIN
6079 SELECT NVL((sum(nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0))), 0) --bug12764043, removed ABS
6080 into l_total_prepay_acct
6081 FROM xla_ae_lines xal,
6082 xla_ae_headers xah
6083 WHERE xal.application_id = 200
6084 AND xah.application_id = 200
6085 AND xah.balance_type_code = 'A' --bug12654609
6086 AND xah.ae_header_id = xal.ae_header_id
6087 AND xal.accounting_class_code = 'PREPAID_EXPENSE'
6088 AND xah.event_id = P_Event_ID
6089 AND xah.ledger_id = P_Ledger_ID;
6090
6091 RETURN (l_total_prepay_acct);
6092
6093
6094 EXCEPTION
6095 WHEN OTHERS THEN
6096 RETURN -100;
6097
6098 END;
6099
6100 -- Bug11673966
6101 FUNCTION get_net_adj_acct(P_Event_ID NUMBER,
6102 P_Ledger_ID NUMBER)
6103 RETURN NUMBER IS
6104 l_total_prepay_acct NUMBER := -101;
6105 BEGIN
6106
6107
6108 SELECT NVL((sum(nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0))), 0) --bug12764043, removed ABS
6109 into l_total_prepay_acct
6110 FROM xla_ae_lines xal,
6111 xla_ae_headers xah
6112 WHERE xal.application_id = 200
6113 AND xah.application_id = 200
6114 AND xah.balance_type_code = 'A' --bug12654609
6115 AND xah.ae_header_id = xal.ae_header_id
6116 AND xal.accounting_class_code = 'PREPAID_EXPENSE'
6117 AND xah.event_id IN
6118 (SELECT /*+ push_subq */
6119 apph.accounting_event_id
6120 FROM ap_prepay_history_all apph
6121 WHERE apph.related_prepay_app_event_id = p_event_id
6122 AND apph.transaction_type = 'PREPAYMENT APPLICATION ADJ')
6123 AND xah.ledger_id = P_Ledger_ID;
6124
6125 RETURN(l_total_prepay_acct);
6126
6127 EXCEPTION
6128 WHEN OTHERS THEN
6129 RETURN -101;
6130
6131 END;
6132 -- Bug 10635312 start
6133 /*=============================================================================
6134 --DESCRIPTION : this fuction will replace the new vendor contact id in place of
6135 old vendor contact id in
6136 -- po_headers_all
6137 -- po_headers_archive_all
6138 -- po_rfq_vendors
6139 -- po_vendor_list_entries
6140 -- po_requisition_lines_all
6141 -- po_reqexpress_lines_all
6142 -- This function returns a 1 if it is successful. It returns a 0
6143 -- if there are exceptions.
6144 --PARAMETERS : new_vendor_contact_id - this is the new vendor contact id that
6145 -- will be used to replace the old one
6146 -- old_vendor_contact_id - this is the old vendor contact id that
6147 -- will be replaced.
6148 =============================================================================*/
6149 FUNCTION po_vendor_contact_merge(new_vendor_contact_id NUMBER,
6150 old_vendor_contact_id NUMBER)
6151 RETURN NUMBER
6152 IS
6153 l_debug_info VARCHAR2(4000);
6154 l_calling_sequence VARCHAR2(4000);
6155 l_log_msg VARCHAR2(4000);
6156 l_error_msg VARCHAR2(4000);
6157 BEGIN
6158 l_calling_sequence := 'po_vendor_contact_merge';
6159 l_debug_info := 'Entering into '||l_calling_sequence;
6160
6161 l_log_msg := l_debug_info;
6162
6163 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6164 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_calling_sequence, l_log_msg );
6165 END IF;
6166
6167 l_debug_info := 'Updating po_headers_all';
6168 UPDATE po_headers_all
6169 SET vendor_contact_id = new_vendor_contact_id
6170 WHERE vendor_contact_id = old_vendor_contact_id;
6171
6172 l_debug_info := 'Updating po_headers_archive_all';
6173 UPDATE po_headers_archive_all
6174 SET vendor_contact_id = new_vendor_contact_id
6175 WHERE vendor_contact_id = old_vendor_contact_id;
6176
6177 l_debug_info := 'Updating po_rfq_vendors';
6178 UPDATE po_rfq_vendors
6179 SET vendor_contact_id = new_vendor_contact_id
6180 WHERE vendor_contact_id = old_vendor_contact_id;
6181
6182 l_debug_info := 'Updating po_vendor_list_entries';
6183 UPDATE po_vendor_list_entries
6184 SET vendor_contact_id = new_vendor_contact_id
6185 WHERE vendor_contact_id = old_vendor_contact_id;
6186
6187 l_debug_info := 'Updating po_requisition_lines_all';
6188 UPDATE po_requisition_lines_all
6189 SET vendor_contact_id = new_vendor_contact_id
6190 WHERE vendor_contact_id = old_vendor_contact_id;
6191
6192 l_debug_info := 'Updating po_reqexpress_lines_all';
6193 UPDATE po_reqexpress_lines_all
6194 SET suggested_vendor_contact_id = new_vendor_contact_id
6195 WHERE suggested_vendor_contact_id = old_vendor_contact_id;
6196
6197 RETURN 1;
6198 EXCEPTION
6199 WHEN OTHERS THEN
6200 l_error_msg := ' Encountered an Exception '||SQLERRM|| ' at : '
6201 || l_calling_sequence|| ' while performing : '
6202 ||l_debug_info || ' for new_vendor_contact_id='||new_vendor_contact_id
6203 ||',old_vendor_contact_id='||old_vendor_contact_id ;
6204 dbms_output.put_line(l_error_msg);
6205 PRINT(l_error_msg);
6206 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6207 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_calling_sequence, l_error_msg );
6208 END IF;
6209 RETURN 0;
6210 END;
6211 -- Bug 10635312 end
6212
6213 /*=============================================================================
6214 --DESCRIPTION : this fuction will replace the new vendor contact id in place of
6215 old vendor contact id in
6216 -- po_headers_all
6217 -- po_headers_archive_all
6218 -- po_rfq_vendors
6219 -- po_vendor_list_entries
6220 -- po_requisition_lines_all
6221 -- po_reqexpress_lines_all
6222 -- This function returns a 1 if it is successful. It returns a 0
6223 -- if there are exceptions.
6224 --PARAMETERS : p_bug_no - GDF bug that is calling the fix
6225 -- p_driver_table - GDF driver table that stores the dupciate
6226 -- contacts
6227 -- p_calling_sequence - script calling this API
6228 =============================================================================*/
6229
6230 -- Bug 13405297 start
6231 FUNCTION po_vendor_contact_merge(p_driver_table IN VARCHAR2,
6232 p_calling_sequence IN VARCHAR2)
6233 RETURN NUMBER
6234 IS
6235 l_debug_info VARCHAR2(4000);
6236 l_calling_sequence VARCHAR2(4000);
6237 l_log_msg VARCHAR2(4000);
6238 l_error_msg VARCHAR2(4000);
6239 BEGIN
6240 l_calling_sequence := 'po_vendor_contact_merge';
6241 l_debug_info := 'Entering into '||l_calling_sequence;
6242
6243 l_log_msg := l_debug_info;
6244
6245 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6246 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_calling_sequence, l_log_msg );
6247 END IF;
6248
6249 l_debug_info := 'Updating po_headers_all';
6250
6251 EXECUTE IMMEDIATE
6252 'MERGE INTO po_headers_all poh
6253 USING (SELECT DISTINCT poh.po_header_id,
6254 tmp.new_vendor_contact_id,
6255 tmp.current_vendor_contact_id
6256 FROM '|| p_driver_table || ' tmp,'||
6257 'po_headers_all poh
6258 WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
6259 and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
6260 and tmp.process_flag = ''Y''
6261 ) a
6262 on (poh.po_header_id = a.po_header_id)
6263 WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
6264
6265 l_debug_info := 'Updating po_headers_archive_all';
6266
6267 EXECUTE IMMEDIATE
6268 'MERGE INTO po_headers_archive_all poh
6269 USING (SELECT DISTINCT poh.po_header_id,
6270 poh.revision_num,
6271 tmp.new_vendor_contact_id,
6272 tmp.current_vendor_contact_id
6273 FROM '|| p_driver_table || ' tmp,'||
6274 'po_headers_archive_all poh
6275 WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
6276 and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
6277 and tmp.process_flag = ''Y''
6278 ) a
6279 on (poh.po_header_id = a.po_header_id and poh.revision_num = a.revision_num)
6280 WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
6281
6282 l_debug_info := 'Updating po_rfq_vendors';
6283
6284 EXECUTE IMMEDIATE
6285 'MERGE INTO po_rfq_vendors poh
6286 USING (SELECT DISTINCT poh.po_header_id,
6287 poh.sequence_num,
6288 tmp.new_vendor_contact_id,
6289 tmp.current_vendor_contact_id
6290 FROM '|| p_driver_table || ' tmp,'||
6291 'po_rfq_vendors poh
6292 WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
6293 and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
6294 AND tmp.process_flag = ''Y''
6295 ) a
6296 on (poh.po_header_id = a.po_header_id and poh.sequence_num = a.sequence_num)
6297 WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
6298
6299 l_debug_info := 'Updating po_vendor_list_entries';
6300
6301 EXECUTE IMMEDIATE
6302 'MERGE INTO po_vendor_list_entries poh
6303 USING (SELECT DISTINCT poh.vendor_list_entry_id,
6304 tmp.new_vendor_contact_id,
6305 tmp.current_vendor_contact_id
6306 FROM '|| p_driver_table || ' tmp,'||
6307 'po_vendor_list_entries poh
6308 WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
6309 and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
6310 AND tmp.process_flag = ''Y''
6311 ) a
6312 on (poh.vendor_list_entry_id = a.vendor_list_entry_id)
6313 WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
6314
6315 l_debug_info := 'Updating po_requisition_lines_all';
6316
6317 EXECUTE IMMEDIATE
6318 'MERGE INTO po_requisition_lines_all poh
6319 USING (SELECT DISTINCT poh.requisition_line_id,
6320 tmp.new_vendor_contact_id,
6321 tmp.current_vendor_contact_id
6322 FROM '|| p_driver_table || ' tmp,'||
6323 'po_requisition_lines_all poh
6324 WHERE poh.vendor_contact_id = tmp.current_vendor_contact_id
6325 and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
6326 AND tmp.process_flag = ''Y''
6327 ) a
6328 on (poh.requisition_line_id = a.requisition_line_id)
6329 WHEN MATCHED THEN UPDATE SET poh.vendor_contact_id = a.new_vendor_contact_id';
6330
6331 l_debug_info := 'Updating po_reqexpress_lines_all';
6332
6333 EXECUTE IMMEDIATE
6334 'MERGE INTO po_reqexpress_lines_all poh
6335 USING (SELECT distinct
6336 poh.rowid row_id, --bug13863902
6337 poh.express_name,
6338 poh.sequence_num,
6339 tmp.new_vendor_contact_id,
6340 tmp.current_vendor_contact_id
6341 FROM '|| p_driver_table || ' tmp,'||
6342 'po_reqexpress_lines_all poh
6343 WHERE poh.suggested_vendor_contact_id = tmp.current_vendor_contact_id
6344 and tmp.current_vendor_contact_id <> tmp.new_vendor_contact_id --bug13863902
6345 AND tmp.process_flag = ''Y''
6346 ) a
6347 on (poh.express_name = a.express_name and poh.sequence_num = a.sequence_num and poh.rowid = a.row_id)
6348 WHEN MATCHED THEN UPDATE SET poh.suggested_vendor_contact_id = a.new_vendor_contact_id';
6349
6350 RETURN 1;
6351
6352 EXCEPTION
6353 WHEN OTHERS THEN
6354 l_error_msg := ' Encountered an Exception '||SQLERRM|| ' at : '
6355 || l_calling_sequence|| ' while performing : '
6356 ||l_debug_info ;
6357 dbms_output.put_line(l_error_msg);
6358 PRINT(l_error_msg);
6359 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
6360 FND_LOG.STRING( G_LEVEL_PROCEDURE, G_MODULE_NAME||l_calling_sequence, l_error_msg );
6361 END IF;
6362 RETURN 0;
6363 END;
6364 -- Bug 13405297 end
6365
6366 -- Bug 14731725 start
6367 /*=============================================================================
6368 --DESCRIPTION : Using this function,the log file generated after applying the
6369 -- data fix is mailed to the user as an attachment taking
6370 -- email id as input from the user.
6371 --PARAMETERS : v_recipient - this is the email id to which the log is sent
6372 -- will be used to replace the old one
6373 -- v_file_location - this is the complete path of the
6374 -- log file generated.
6375 =============================================================================*/
6376
6377 FUNCTION send_mail_clob_impl(
6378 v_recipient VARCHAR2,
6379 v_file_location VARCHAR2)
6380 RETURN BOOLEAN IS
6381
6382 vInHandle_read UTL_FILE.FILE_TYPE;
6383 v_dir_name VARCHAR2(255); -- variable to hold directory name
6384 v_file_name VARCHAR2(255); -- variable to hold file name
6385 vNewLine VARCHAR2(32767);
6386 vMessage VARCHAR2(32767);
6387 blnEOF BOOLEAN;
6388 v_attachment CLOB; -- Text that will be sent as an attached file
6389 v_offset NUMBER;
6390 v_amount NUMBER;
6391 -- variable to hold the smtp server connection
6392 v_smtp_connection utl_smtp.connection;
6393
6394 -- variable to hold the smtp host name
6395 v_smtp_host VARCHAR2(100) DEFAULT 'localhost';
6396
6397 -- variable to hold the smtp port
6398 v_smtp_port NUMBER DEFAULT 25;
6399
6400 -- variable to hold the sender, from field
6401 v_sender VARCHAR2(100) DEFAULT '[email protected]'; --sender email id
6402 v_cc VARCHAR2(100);
6403 v_subject VARCHAR2(255); -- Subject of the email
6404 v_body VARCHAR2(255); -- Body of the email
6405
6406 BEGIN
6407 v_dir_name := substr(v_file_location,1,(instr(v_file_location,'/',-1)-1));
6408 v_file_name := substr(v_file_location,(instr(v_file_location,'/',-1)+1));
6409 v_subject := 'GDF Log File'||' '||v_file_name;
6410 v_body := 'Please find attached the log file';
6411 utl_file.fcopy(v_dir_name,v_file_name,v_dir_name, 'gdftemp.html');
6412 vInHandle_read := utl_file.fopen(v_dir_name,'gdftemp.html', 'r',32767);
6413 BEGIN
6414 LOOP
6415 utl_file.get_line(vInHandle_read,vNewLine);
6416 vNewLine := vNewLine||fnd_global.local_chr(10);
6417 v_attachment := v_attachment||vNewLine;
6418 END LOOP;
6419 EXCEPTION
6420 WHEN NO_DATA_FOUND THEN
6421 utl_file.fclose(vInHandle_read);
6422 WHEN OTHERS THEN
6423 Dbms_Output.put_line(SQLERRM);
6424 END;
6425 -- establish the connection to the smtp server
6426 v_smtp_connection := utl_smtp.open_connection(v_smtp_host, v_smtp_port);
6427
6428 -- perform a handshake with the smtp server
6429 utl_smtp.helo(v_smtp_connection, v_smtp_host);
6430
6431 -- set the 'from' address of the message
6432 utl_smtp.mail(v_smtp_connection, v_sender);
6433
6434 -- add the recipient to the message
6435 utl_smtp.rcpt(v_smtp_connection, v_recipient);
6436
6437 utl_smtp.open_data (v_smtp_connection);
6438
6439
6440 --writing headers of the message
6441 utl_smtp.write_data(v_smtp_connection,'Date: ' || to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') || utl_tcp.crlf);
6442 utl_smtp.write_data(v_smtp_connection,'From: ' || v_sender || utl_tcp.crlf);
6443 utl_smtp.write_data(v_smtp_connection,'Subject: '|| v_subject || utl_tcp.crlf);
6444 utl_smtp.write_data(v_smtp_connection,'To: ' || v_recipient || utl_tcp.crlf);
6445 utl_smtp.write_data(v_smtp_connection,'Cc: ' || v_cc || utl_tcp.crlf);
6446 utl_smtp.write_data(v_smtp_connection,'MIME-Version: 1.0'|| utl_tcp.crlf); --adding MIME type
6447 utl_smtp.write_data(v_smtp_connection,'Content-Type: multipart/mixed;' || utl_tcp.crlf);
6448 utl_smtp.write_data(v_smtp_connection,' boundary="-----SECBOUND"'|| utl_tcp.crlf);
6449 utl_smtp.write_data(v_smtp_connection,utl_tcp.crlf);
6450 utl_smtp.write_data(v_smtp_connection, '-------SECBOUND'|| utl_tcp.crlf);
6451 utl_smtp.write_data(v_smtp_connection,'Content-Type: text/html;'|| utl_tcp.crlf);
6452 utl_smtp.write_data(v_smtp_connection,'Content-Transfer_Encoding: 8bit'|| utl_tcp.crlf);
6453 utl_smtp.write_data(v_smtp_connection,utl_tcp.crlf);
6454 utl_smtp.write_data(v_smtp_connection,v_body || utl_tcp.crlf);
6455 utl_smtp.write_data(v_smtp_connection,'-------SECBOUND'|| utl_tcp.crlf);
6456 utl_smtp.write_data(v_smtp_connection,'Content-Type: text/html;' || utl_tcp.crlf);
6457 utl_smtp.write_data(v_smtp_connection,' name="log.html"'|| utl_tcp.crlf); -- file name that will hold the attached text
6458 utl_smtp.write_data(v_smtp_connection,'Content-Transfer_Encoding: 8bit'|| utl_tcp.crlf);
6459 utl_smtp.write_data(v_smtp_connection,'Content-Disposition: attachment;'|| utl_tcp.crlf);
6460 utl_smtp.write_data(v_smtp_connection,' filename="log.html"'|| utl_tcp.crlf);
6461 utl_smtp.write_data(v_smtp_connection,utl_tcp.crlf);
6462
6463 -- writing data from clob
6464 BEGIN
6465 v_offset := 1;
6466 v_amount := 1900;
6467 WHILE v_offset < dbms_lob.getlength(v_attachment)
6468 LOOP
6469 utl_smtp.write_data(v_smtp_connection,
6470 dbms_lob.substr(v_attachment,v_amount,v_offset));
6471 v_offset := v_offset + v_amount ;
6472 v_amount := least(1900,dbms_lob.getlength(v_attachment) - v_amount);
6473 END LOOP;
6474 vNewLine := utl_tcp.crlf ||utl_tcp.crlf ||'-------SECBOUND--';
6475 utl_smtp.close_data(v_smtp_connection);
6476 utl_smtp.quit(v_smtp_connection);
6477 dbms_lob.freetemporary(v_attachment);
6478 END;
6479 RETURN TRUE;
6480
6481 EXCEPTION
6482 WHEN utl_smtp.invalid_operation THEN
6483 dbms_output.put_line('Invalid Operation in Mail attempt using UTL_SMTP.');
6484 RETURN FALSE;
6485 WHEN utl_smtp.transient_error THEN
6486 dbms_output.put_line(' Temporary e-mail issue - try again');
6487 RETURN FALSE;
6488 WHEN utl_smtp.permanent_error THEN
6489 dbms_output.put_line(' Permanent Error Encountered.');
6490 RETURN FALSE;
6491
6492 END send_mail_clob_impl;
6493
6494 -- Bug 14731725 end
6495
6496 END AP_Acctg_Data_Fix_PKG;