[Home] [Help]
PACKAGE BODY: APPS.XLA_DATAFIXES_PUB
Source
1 PACKAGE BODY xla_datafixes_pub AS
2 /* $Header: xlajedfp.pkb 120.14.12020000.2 2012/07/23 13:33:17 vgopiset ship $ */
3
4 --=============================================================================
5 -- *********** Local Trace Routine **********
6 --=============================================================================
7 TYPE t_array_integer IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
8 TYPE t_array_char1 IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
9 TYPE t_array_char30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
10
11 TYPE t_je_ae_header_id IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; --krsankar
12
13 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
14 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
15 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
16 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
18 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
19
20 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
21 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_datafixes_pub';
22
23 --=============================================================================
24 -- *********** Private Global Constant **********
25 --=============================================================================
26 C_COMPLETION_OPTION_DRAFT CONSTANT VARCHAR2(1) := 'D';
27 C_COMPLETION_OPTION_FINAL CONSTANT VARCHAR2(1) := 'F';
28 C_COMPLETION_OPTION_POST CONSTANT VARCHAR2(1) := 'P';
29
30 C_STATUS_FUNDS_RESERVE CONSTANT VARCHAR2(30) := 'FUNDS_RESERVE';
31 C_STATUS_FINAL CONSTANT VARCHAR2(30) := 'FINAL';
32 C_STATUS_DRAFT_CODE CONSTANT VARCHAR2(1) := 'D';
33 C_STATUS_FINAL_CODE CONSTANT VARCHAR2(1) := 'F';
34 C_STATUS_POSTING_CODE CONSTANT VARCHAR2(1) := 'P';
35
36 C_ITEM_HEADER_DESCRIPTION CONSTANT VARCHAR2(20) := 'HEADER_DESCRIPTION';
37 C_ITEM_GL_DATE CONSTANT VARCHAR2(20) := 'GL_DATE';
38 C_ITEM_REFERENCE_DATE CONSTANT VARCHAR2(20) := 'REFERENCE_DATE';
39 C_ITEM_LINE_DESCRIPTION CONSTANT VARCHAR2(20) := 'LINE_DESCRIPTION';
40 C_ITEM_ACCOUNT CONSTANT VARCHAR2(20) := 'ACCOUNT';
41 C_ITEM_ACCOUNTED_DR CONSTANT VARCHAR2(20) := 'ACCOUNTED_DR';
42 C_ITEM_ACCOUNTED_CR CONSTANT VARCHAR2(20) := 'ACCOUNTED_CR';
43 C_ITEM_CURRENCY_CODE CONSTANT VARCHAR2(20) := 'CURRENCY_CODE';
44 C_ITEM_CURR_CONV_TYPE CONSTANT VARCHAR2(20) := 'CURR_CONV_TYPE';
45 C_ITEM_CURR_CONV_RATE CONSTANT VARCHAR2(20) := 'CURR_CONV_RATE';
46 C_ITEM_CURR_CONV_DATE CONSTANT VARCHAR2(20) := 'CURR_CONV_DATE';
47 C_ITEM_ENTERED_DR CONSTANT VARCHAR2(20) := 'ENTERED_DR';
48 C_ITEM_ENTERED_CR CONSTANT VARCHAR2(20) := 'ENTERED_CR';
49 C_ITEM_ACCOUNTING_CLASS CONSTANT VARCHAR2(20) := 'ACCOUNTING_CLASS';
50 C_UNDO_CONTEXT CONSTANT VARCHAR2(20) := 'UNDO'; --added bug 10226301
51
52 g_log_level NUMBER;
53 g_log_enabled BOOLEAN;
54
55 PROCEDURE delete_tb_entries ( p_event_id IN NUMBER
56 ,p_application_id IN NUMBER);
57
58 PROCEDURE trace
59 (p_msg IN VARCHAR2
60 ,p_module IN VARCHAR2
61 ,p_level IN NUMBER) IS
62 BEGIN
63 ----------------------------------------------------------------------------
64 -- Following is for FND log.
65 ----------------------------------------------------------------------------
66 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
67 fnd_log.message(p_level, p_module);
68 ELSIF p_level >= g_log_level THEN
69 fnd_log.string(p_level, p_module, p_msg);
70 END IF;
71
72 EXCEPTION
73 WHEN xla_exceptions_pkg.application_exception THEN
74 RAISE;
75 WHEN OTHERS THEN
76 xla_exceptions_pkg.raise_message
77 (p_location => 'xla_datafixes_pub.trace');
78 END trace;
79
80 --=============================================================================
81 -- *********** public procedures and functions **********
82 --=============================================================================
83
84
85 /*
86 This function returns the transaction number or transaction details
87 depending on the flag p_trans_details_flag.
88 IF p_trans_details_flag = 'N' THEN
89 return the transaction number
90 ELSE
91 return the transaction details based on the entity code passed.
92 END IF;
93 */
94 FUNCTION get_transaction_details ( p_application_id IN INTEGER,
95 p_entity_id IN INTEGER,
96 p_trans_details_flag IN VARCHAR2 DEFAULT 'N',
97 p_entity_code IN VARCHAR2 DEFAULT NULL
98 )
99 RETURN VARCHAR2 IS
100
101 CURSOR c_transaction_number IS
102 SELECT transaction_number
103 FROM xla_transaction_entities
104 WHERE entity_id = p_entity_id
105 AND application_id = p_application_id;
106
107
108 l_transaction_number xla_transaction_entities.transaction_number%TYPE;
109
110 v_refcur SYS_REFCURSOR;
111 l_join_string VARCHAR2(32000);
112
113 l_transaction_entity_sql VARCHAR2(32000) :=
114 'SELECT $transaction_entity_columns$
115 FROM xla_transaction_entities ent
116 WHERE ent.application_id = :1
117 AND ent.entity_id = :2 ';
118
119 l_trx_columns VARCHAR2(4000);
120 l_trx_col1 VARCHAR2(1000);
121 l_trx_col2 VARCHAR2(1000);
122 l_trx_col3 VARCHAR2(1000);
123 l_trx_col4 VARCHAR2(1000);
124
125
126 BEGIN
127
128 IF p_trans_details_flag = 'N' THEN
129 OPEN c_transaction_number;
130 FETCH c_transaction_number INTO l_transaction_number;
131 CLOSE c_transaction_number;
132
133 RETURN l_transaction_number;
134
135 ELSIF ( p_trans_details_flag = 'Y' AND
136 p_entity_code NOT IN ('MANUAL','THIRD_PARTY_MERGE')
137 ) THEN
138
139 FOR i IN
140 ( SELECT xid.transaction_id_col_name_1 trx_col_1
141 ,xid.transaction_id_col_name_2 trx_col_2
142 ,xid.transaction_id_col_name_3 trx_col_3
143 ,xid.transaction_id_col_name_4 trx_col_4
144 ,xid.source_id_col_name_1 src_col_1
145 ,xid.source_id_col_name_2 src_col_2
146 ,xid.source_id_col_name_3 src_col_3
147 ,xid.source_id_col_name_4 src_col_4
148 FROM xla_entity_id_mappings xid
149 WHERE xid.application_id = p_application_id -- input to the procedure
150 AND xid.entity_code = p_entity_code
151 )
152 LOOP
153
154 IF i.trx_col_1 IS NOT NULL THEN
155 l_join_string := l_join_string || ''''|| lower(i.TRX_COL_1) || ': '|| '''' || ' '|| '||' ||
156 'ENT.'|| i.src_col_1 || ' TRX_COL_1' || ',';
157 END IF;
158
159 IF i.trx_col_1 IS NULL THEN
160 l_join_string := l_join_string || 'NULL' || ' TRX_COL_1' || ',';
161 END IF;
162
163
164 IF i.trx_col_2 IS NOT NULL THEN
165 l_join_string := l_join_string || ''''|| lower(i.TRX_COL_2) || ': ' || '''' || ' '|| '||' ||
166 'ENT.'|| i.src_col_2 || ' TRX_COL_2' || ',';
167 END IF;
168
169 IF i.trx_col_2 IS NULL THEN
170 l_join_string := l_join_string || 'NULL' || ' TRX_COL_2' || ',';
171 END IF;
172
173
174 IF i.trx_col_3 IS NOT NULL THEN
175 l_join_string := l_join_string || ''''|| lower(i.TRX_COL_3) || ': ' || '''' ||' '|| '||' ||
176 'ENT.'|| i.src_col_3 || ' TRX_COL_3' || ',';
177 END IF;
178
179 IF i.trx_col_3 IS NULL THEN
180 l_join_string := l_join_string || 'NULL' || ' TRX_COL_3' || ',' ;
181 END IF;
182
183 IF i.trx_col_4 IS NOT NULL THEN
184 l_join_string := l_join_string || ''''|| lower(i.TRX_COL_4) || ': ' || '''' || ' '|| '||' ||
185 'ENT.'|| i.src_col_4 || ' TRX_COL_4' ;
186 END IF;
187
188 IF i.trx_col_4 IS NULL THEN
189 l_join_string := l_join_string || 'NULL' || ' TRX_COL_4' ;
190 END IF;
191
192 END LOOP;
193
194 l_transaction_entity_sql := REPLACE(l_transaction_entity_sql, '$transaction_entity_columns$' , l_join_string);
195
196
197 OPEN v_refcur FOR l_transaction_entity_sql USING p_application_id, p_entity_id ;
198 LOOP
199 FETCH v_refcur INTO l_trx_col1, l_trx_col2,
200 l_trx_col3,l_trx_col4;
201 EXIT WHEN v_refcur%NOTFOUND;
202
203 IF l_trx_col1 IS NOT NULL THEN
204 l_trx_columns := l_trx_columns || l_trx_col1;
205 END IF ;
206
207 IF l_trx_col2 IS NOT NULL THEN
208 l_trx_columns := l_trx_columns || ' '|| l_trx_col2;
209 END IF ;
210
211 IF l_trx_col3 IS NOT NULL THEN
212 l_trx_columns := l_trx_columns || ' ' || l_trx_col3;
213 END IF ;
214
215 IF l_trx_col4 IS NOT NULL THEN
216 l_trx_columns := l_trx_columns || ' ' || l_trx_col4;
217 END IF ;
218
219 END LOOP;
220
221 CLOSE v_refcur;
222
223 RETURN l_trx_columns;
224 ELSE
225 -- indicates that this is a MANUAL or THIRD_PARTY_MERGE entity
226 RETURN NULL;
227
228 END IF;
229
230 END get_transaction_details;
231
232
233 --=============================================================================
234 --
235 -- Following API are used for data fix:
236 --
237 -- 1. delete_journal_entries
238 -- 2. reverse_journal_entries
239 -- 3. redo_accounting
240 -- 4. do_not_transfer_je
241 -- 5. validate_journal_entry
242 --
243 --
244 --=============================================================================
245
246
247 --=============================================================================
248 --
249 --
250 --
251 --=============================================================================
252
253 PROCEDURE delete_journal_entries
254 (p_api_version IN NUMBER
255 ,p_init_msg_list IN VARCHAR2
256 ,p_application_id IN INTEGER
257 ,p_event_id IN INTEGER
258 ,x_return_status OUT NOCOPY VARCHAR2
259 ,x_msg_count OUT NOCOPY NUMBER
260 ,x_msg_data OUT NOCOPY VARCHAR2
261 ) IS
262
263 l_api_name CONSTANT VARCHAR2(30) := 'delete_journal_entries';
264 l_api_version CONSTANT NUMBER := 1.0;
265
266 l_retcode INTEGER;
267 l_log_module VARCHAR2(240);
268 l_gl_transfer_status_code VARCHAR2(10) := NULL;
269 l_count number;
270
271 BEGIN
272 IF g_log_enabled THEN
273 l_log_module := C_DEFAULT_MODULE||'.delete_journal_entries';
274 END IF;
275
276 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
277 trace(p_msg => 'BEGIN of procedure delete_journal_entries',
278 p_module => l_log_module,
279 p_level => C_LEVEL_PROCEDURE);
280 END IF;
281
282 IF (FND_API.to_boolean(p_init_msg_list)) THEN
283 FND_MSG_PUB.initialize;
284 END IF;
285
286 -- Standard call to check for call compatibility.
287 IF (NOT FND_API.compatible_api_call
288 (p_current_version_number => l_api_version
289 ,p_caller_version_number => p_api_version
290 ,p_api_name => l_api_name
291 ,p_pkg_name => C_DEFAULT_MODULE))
292 THEN
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
294 END IF;
295
296 --Bug : 8752657 - Check MO security setting
297 select count(1)
298 into l_count
299 from xla_events xe
300 ,xla_transaction_entities xte
301 where xe.application_id=p_application_id
302 and xe.event_id=p_event_id
303 and xe.entity_id=xte.entity_id
304 and xte.application_id=xe.application_id;
305
306 IF l_count = 0 THEN
307 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
308 trace(p_msg => 'Journal entries cannot be deleted because either MO Security is not set for this session or Entity does not exist for this event',
309 p_module => l_log_module,
310 p_level => C_LEVEL_STATEMENT);
311 END IF;
312 Log_error(p_module => l_log_module
313 ,p_error_msg => 'Journal entries cannot be deleted because either MO Security is not set for this session or Entity does not exist for this event');
314 END IF;--Bug 8752657.
315
316 -- Initialize global variables
317 x_return_status := FND_API.G_RET_STS_SUCCESS;
318
319 -----------------------------------------------------------------------------------
320 -- Validation
321 -----------------------------------------------------------------------------------
322 SELECT MAX(NVL(gl_transfer_status_code,'N')) -- N, NT, S, Y
323 INTO l_gl_transfer_status_code
324 FROM xla_ae_headers xah
325 WHERE application_id = p_application_id
326 AND event_id = p_event_id
327 -- added bug#8344908
328 AND NOT EXISTS
329 ( SELECT 1
330 FROM xla_ae_lines xal, gl_import_references gir
331 WHERE xah.ae_header_id = xal.ae_header_id
332 AND xah.application_id = xal.application_id
333 AND xal.gl_sl_link_id = gir.gl_sl_link_id
334 AND xal.gl_sl_link_table = gir.gl_sl_link_table
335 );
336 -- bug#8344908
337
338 IF l_gl_transfer_status_code IS NULL THEN
339 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
340 trace(p_msg => 'No such journal entry or the journal entries are transferred to gl and cannot be deleted.',
341 p_module => l_log_module,
342 p_level => C_LEVEL_STATEMENT);
343 END IF;
344 Log_error(p_module => l_log_module
345 ,p_error_msg => 'No such journal entry or the journal entries are transferred to gl and cannot be deleted.');
346
347 ELSIF l_gl_transfer_status_code IN ('S','Y') THEN
348 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
349 trace(p_msg => 'l_gl_transfer_status_code='||l_gl_transfer_status_code||
350 'Journal entries cannot be deleted because it has either been Transferred or set to Not Transferred or gl_transfer_status_flag has an incorrect status as the journal entries are not transferred to General Ledger.',
351 p_module => l_log_module,
352 p_level => C_LEVEL_STATEMENT);
353 END IF;
354 Log_error(p_module => l_log_module
355 ,p_error_msg => 'Journal entries cannot be deleted because it has either been Transferred or set to Not Transferred or gl_transfer_status_flag has an incorrect status as the journal entries are not transferred to General Ledger.');
356
357 ELSE
358
359 --------------------------------------------------------
360 -- delete all journal entries for the event
361 -- no impact on trial balance if not transferred
362 --------------------------------------------------------
363 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
364 trace(p_msg => 'Calling xla_journal_entries_pkg.delete_journal_entries.',
365 p_module => l_log_module,
366 p_level => C_LEVEL_STATEMENT);
367 END IF;
368
369 xla_journal_entries_pkg.delete_journal_entries
370 (p_application_id => p_application_id
371 ,p_event_id => p_event_id);
372
373 --------------------------------------------------------
374 -- mark event as un-processed so can be re-processed
375 --------------------------------------------------------
376 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
377 trace(p_msg => 'Update xla_events event_id'||p_event_id||' to Unprocessed.',
378 p_module => l_log_module,
379 p_level => C_LEVEL_STATEMENT);
380 END IF;
381 UPDATE XLA_EVENTS
382 SET EVENT_STATUS_CODE = xla_events_pub_pkg.C_EVENT_UNPROCESSED
383 ,PROCESS_STATUS_CODE = xla_events_pkg.C_INTERNAL_UNPROCESSED
384 WHERE application_id = p_application_id
385 AND event_id = p_event_id;
386
387
388 audit_datafix (p_application_id => p_application_id
389 ,p_event_id => p_event_id);
390
391 END IF;
392
393 FND_MSG_PUB.count_and_get(p_count => x_msg_count
394 ,p_data => x_msg_data);
395
396 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
397 trace(p_msg => 'END of procedure delete_journal_entries',
398 p_module => l_log_module,
399 p_level => C_LEVEL_PROCEDURE);
400 END IF;
401
402 EXCEPTION
403 WHEN FND_API.G_EXC_ERROR THEN
404 ROLLBACK;
405 x_return_status := FND_API.G_RET_STS_ERROR ;
406 FND_MSG_PUB.count_and_get(p_count => x_msg_count
407 ,p_data => x_msg_data);
408
409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
410 ROLLBACK;
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
412 FND_MSG_PUB.count_and_get(p_count => x_msg_count
413 ,p_data => x_msg_data);
414
415 WHEN OTHERS THEN
416 ROLLBACK;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
418 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
419 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
420 END IF;
421 FND_MSG_PUB.count_and_get(p_count => x_msg_count
422 ,p_data => x_msg_data);
423 END delete_journal_entries;
424
425
426
427 /*=== Logic ====================================================================
428 1) find the ae_header_id of the primary ledger (and original parent entry of
429 MPA/Accrual Reversal entry, if exists) for the original p_event_id
430 2) calls Reverse_Journal_Entry with the ae_header_id
431 a) delete the incomplete MPA
432 b) calls Create_Reversal_Entry of the ae_header_id to create the reversal of
433 the original entry, returning the new rev_ae_header_id and rev_event_id
434 i) calls Complete_Journal_Entry with rev_ae_header_id, p_event_id and
435 p_rev_flag = 'Y' to validate the reversal entry rev_ae_header_id and on
436 success,
437 -> calls Create_MRC_Reversal_Entry to create reversal of all other
438 ledgers and entries related to the original entry p_event_id.
439 c) Create a new event and entity, and map the original entry to the new
440 event id and entity id.
441 ==============================================================================*/
442 PROCEDURE reverse_journal_entries
443 (p_api_version IN NUMBER
444 ,p_init_msg_list IN VARCHAR2
445 ,p_application_id IN INTEGER
446 ,p_event_id IN INTEGER
447 ,p_reversal_method IN VARCHAR2
448 ,p_gl_date IN DATE
449 ,p_post_to_gl_flag IN VARCHAR2
450 ,x_return_status OUT NOCOPY VARCHAR2
451 ,x_msg_count OUT NOCOPY NUMBER
452 ,x_msg_data OUT NOCOPY VARCHAR2
453 ,x_rev_ae_header_id OUT NOCOPY INTEGER
454 ,x_rev_event_id OUT NOCOPY INTEGER
455 ,x_rev_entity_id OUT NOCOPY INTEGER
456 ,x_new_event_id OUT NOCOPY INTEGER
457 ,x_new_entity_id OUT NOCOPY INTEGER
458 ) IS
459
460 l_api_name CONSTANT VARCHAR2(30) := 'reverse_journal_entries';
461 l_api_version CONSTANT NUMBER := 1.0;
462
463 ---------------------------------------------------------------
464 -- in order to reverse, they must be FINAL and Transferred.
465 ---------------------------------------------------------------
466 /* Bug 7011889 - Removed ae_header_id out of this CURSOR to fetch through BULK COLLECT */
467 -- Bug#8736946 changed the cursor to pick secondary ledger events which are
468 -- valuation based
469
470 CURSOR c_orig_je IS
471 SELECT /*+ leading(xah) */
472 gl.currency_code, xsu.je_source_name,
473 xah.entity_id, xah.accounting_date,
474 -- commented bug 11883762
475 -- xah.ledger_id,
476 --added bug 11883762
477 xte.ledger_id,
478 xte.legal_entity_id, xah.accrual_reversal_flag,
479 xle.budgetary_control_flag
480 FROM XLA_LEDGER_OPTIONS opt,
481 XLA_LEDGER_RELATIONSHIPS_V rs,
482 xla_gl_ledgers_v gl,
483 xla_ae_headers xah,
484 xla_subledgers xsu,
485 xla_events xle,
486 xla_transaction_entities xte
487 WHERE opt.LEDGER_ID = xah.ledger_id
488 AND opt.APPLICATION_ID = xah.application_id
489 AND xsu.application_id = xah.application_id
490 AND xah.event_id = p_event_id -- input parameters
491 AND xah.application_id = p_application_id -- input parameters
492 AND xah.event_id = xle.event_id
493 AND xah.application_id = xle.application_id
494 AND xah.entity_id = xte.entity_id
495 AND xah.application_id = xte.application_id
496 AND xah.parent_ae_header_id IS NULL
497 AND xah.accounting_entry_status_code = C_STATUS_FINAL_CODE
498 AND opt.ENABLED_FLAG = 'Y'
499 AND rs.LEDGER_ID = opt.LEDGER_ID
500 AND ( rs.LEDGER_CATEGORY_CODE = 'PRIMARY'
501 OR (rs.LEDGER_CATEGORY_CODE = 'SECONDARY'
502 AND xsu.valuation_method_flag = 'Y'
503 AND opt.CAPTURE_EVENT_FLAG = 'Y')
504 --added bug 11883762
505 OR (rs.LEDGER_CATEGORY_CODE = 'SECONDARY' AND rownum=1
506 AND not exists(select 1 from xla_ae_headers xah1 where xah1.entity_id=xah.entity_id
507 AND xah1.application_id=p_application_id
508 AND xah1.event_id=xah.event_id
509 AND xah1.ledger_id=rs.primary_ledger_id)))
510
511 --end bug 11883762
512 AND rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
513 AND rs.ledger_id = gl.ledger_id
514 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL
515 AND NOT EXISTS (SELECT 1
516 FROM xla_ae_headers xah2
517 WHERE xah2.application_id = p_application_id
518 AND xah2.event_id = p_event_id
519 AND xah2.accounting_entry_status_code = C_STATUS_FINAL_CODE
520 AND NVL(xah2.gl_transfer_status_code,'N') IN ('N','NT')) -- can be reversed only if it is transferred
521 --Added bug#8344908
522 AND EXISTS
523 ( SELECT 1
524 FROM xla_ae_lines xal, gl_import_references gir
525 WHERE xah.ae_header_id = xal.ae_header_id
526 AND xah.application_id = xal.application_id
527 AND xal.gl_sl_link_id = gir.gl_sl_link_id
528 AND xal.gl_sl_link_table = gir.gl_sl_link_table
529 );
530 --Added bug#8344908
531
532
533 l_functional_curr xla_gl_ledgers_v.currency_code%TYPE;
534 l_je_source_name xla_subledgers.je_source_name%TYPE;
535 l_entity_id INTEGER;
536 l_pri_ae_header_id INTEGER;
537 l_pri_gl_date DATE;
538 l_ledger_id INTEGER;
539 l_legal_entity_id INTEGER;
540 l_mpa_acc_rev_flag VARCHAR2(1);
541 l_bc_flag VARCHAR2(1);
542 l_transfer_request_id INTEGER;
543
544 l_event_source_info xla_events_pub_pkg.t_event_source_info;
545 l_array_ae_header_id t_array_integer;
546
547 /* Bug 7011889 - Array to hold ae_header_ids from BULK COLLECT in case of Encumbarance events */
548 l_array_je_header_id xla_je_validation_pkg.t_array_int;
549
550 l_retcode INTEGER;
551 l_log_module VARCHAR2(240);
552 l_completion_option VARCHAR2(1);
553 l_completion_retcode VARCHAR2(30);
554
555 --bug#8279661
556 CURSOR c_entity_code(p_orig_entity_id INTEGER) IS
557 SELECT entity_code
558 FROM xla_transaction_entities
559 WHERE application_id = p_application_id
560 AND entity_id = p_orig_entity_id;
561
562 l_orig_entity_code xla_event_types_b.entity_code%TYPE;
563 l_new_description xla_ae_headers.description%TYPE;
564
565 l_count number;--Bug 8752657
566
567 --end bug#8279661
568
569 BEGIN
570 IF g_log_enabled THEN
571 l_log_module := C_DEFAULT_MODULE||'.reverse_journal_entries';
572 END IF;
573
574 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
575 trace(p_msg => 'BEGIN of procedure reverse_journal_entries',
576 p_module => l_log_module,
577 p_level => C_LEVEL_PROCEDURE);
578 END IF;
579
580 IF (FND_API.to_boolean(p_init_msg_list)) THEN
581 FND_MSG_PUB.initialize;
582 END IF;
583
584 -- Bug :8752657 Check MO security setting
585 select count(1)
586 into l_count
587 from xla_events xe
588 ,xla_transaction_entities xte
589 where xe.application_id=p_application_id
590 and xe.event_id=p_event_id
591 and xe.entity_id=xte.entity_id
592 and xte.application_id=xe.application_id;
593
594 IF l_count = 0 THEN
595 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
596 trace(p_msg => 'Journal entries cannot be deleted because either MO Security is not set for this session or Entity does not exist for this event',
597 p_module => l_log_module,
598 p_level => C_LEVEL_STATEMENT);
599 END IF;
600 Log_error(p_module => l_log_module
601 ,p_error_msg => 'Journal entries cannot be deleted because either MO Security is not set for this session or Entity does not exist for this event');
602 END IF;-- Bug 8752657.
603
604 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
605 trace(p_msg => 'Delete entries from xla_trial_balances',
606 p_module => l_log_module,
607 p_level => C_LEVEL_PROCEDURE);
608 END IF;
609
610 -- Standard call to check for call compatibility.
611 IF (NOT FND_API.compatible_api_call
612 (p_current_version_number => l_api_version
613 ,p_caller_version_number => p_api_version
614 ,p_api_name => l_api_name
615 ,p_pkg_name => C_DEFAULT_MODULE))
616 THEN
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
618 END IF;
619
620 -- Initialize global variables
621 x_return_status := FND_API.G_RET_STS_SUCCESS;
622
623 --------------------------------------------------------------
624 -- Call to DELETE TB entries for original event E1
625 --------------------------------------------------------------
626 delete_tb_entries( p_event_id
627 ,p_application_id);
628
629
630 -- Validation -------------------------------------------------------
631 /* Bug 7011889 - Modified from OPEN,FETCH to CURSOR FOR LOOP */
632
633 FOR c_orig_je_rec IN c_orig_je
634 LOOP
635 l_functional_curr := c_orig_je_rec.currency_code;
636 l_je_source_name := c_orig_je_rec.je_source_name;
637 l_entity_id := c_orig_je_rec.entity_id;
638 l_pri_gl_date := c_orig_je_rec.accounting_date;
639 l_ledger_id := c_orig_je_rec.ledger_id;
640 l_legal_entity_id := c_orig_je_rec.legal_entity_id;
641 l_mpa_acc_rev_flag := c_orig_je_rec.accrual_reversal_flag;
642 l_bc_flag := c_orig_je_rec.budgetary_control_flag;
643
644 END LOOP;
645
646
647 --Added bug#8344908 Added the following IF condition to check whether there exists an
648 -- entity id for the event to be reversed. If its null its an indication that the event
649 -- does not exists in gl and throw an error that the event cannot be reversed as its not transferred to gl
650
651 IF l_entity_id IS NOT NULL THEN
652
653
654 /* Bug 7011889 - Bulk collecting header ids into an array */
655 -- Bug#8736946 changed the SELECT to pick secondary ledger events which are
656 -- valuation based
657 SELECT /*+ leading(xah) */
658 xah.ae_header_id
659 BULK COLLECT INTO l_array_je_header_id
660 FROM XLA_LEDGER_OPTIONS opt,
661 XLA_LEDGER_RELATIONSHIPS_V rs,
662 xla_gl_ledgers_v gl,
663 xla_ae_headers xah,
664 xla_subledgers xsu,
665 xla_events xle,
666 xla_transaction_entities xte
667 WHERE opt.LEDGER_ID = xah.ledger_id
668 AND opt.APPLICATION_ID = xah.application_id
669 AND xsu.application_id = xah.application_id
670 AND xah.event_id = p_event_id -- input parameters
671 AND xah.application_id = p_application_id -- input parameters
672 AND xah.event_id = xle.event_id
673 AND xah.application_id = xle.application_id
674 AND xah.entity_id = xte.entity_id
675 AND xah.application_id = xte.application_id
676 AND xah.parent_ae_header_id IS NULL
677 AND xah.accounting_entry_status_code = C_STATUS_FINAL_CODE
678 AND opt.ENABLED_FLAG = 'Y'
679 AND rs.LEDGER_ID = opt.LEDGER_ID
680 AND ( rs.LEDGER_CATEGORY_CODE = 'PRIMARY'
681 OR (rs.LEDGER_CATEGORY_CODE = 'SECONDARY'
682 AND xsu.valuation_method_flag = 'Y'
683 AND opt.CAPTURE_EVENT_FLAG = 'Y')
684 --added bug 11883762
685 OR (rs.LEDGER_CATEGORY_CODE = 'SECONDARY' AND rownum=1
686 AND not exists(select 1 from xla_ae_headers xah1 where xah1.entity_id=xah.entity_id
687 AND xah1.application_id=p_application_id
688 AND xah1.event_id=xah.event_id
689 AND xah1.ledger_id=rs.primary_ledger_id)))
690
691 --end bug 11883762
692 AND rs.RELATIONSHIP_ENABLED_FLAG = 'Y'
693 AND rs.ledger_id = gl.ledger_id
694 AND NOT EXISTS (SELECT 1
695 FROM xla_ae_headers xah2
696 WHERE xah2.application_id = p_application_id
697 AND xah2.event_id = p_event_id
698 AND xah2.accounting_entry_status_code = C_STATUS_FINAL_CODE
699 AND NVL(xah2.gl_transfer_status_code,'N') IN ('N','NT')
700 ) -- can be reversed only if it is transferred
701 AND rs.SLA_ACCOUNTING_METHOD_CODE IS NOT NULL;
702
703
704 --------------------------------------------------------------
705 -- if this is not Accrual Reversal entry, check if it is MPA
706 --------------------------------------------------------------
707 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
708 trace(p_msg => 'l_functional_curr = '||l_functional_curr,
709 p_module => l_log_module,
710 p_level => C_LEVEL_STATEMENT);
711 trace(p_msg => 'l_je_source_name = '||l_je_source_name,
712 p_module => l_log_module,
713 p_level => C_LEVEL_STATEMENT);
714 trace(p_msg => 'l_entity_id = '||l_entity_id,
715 p_module => l_log_module,
716 p_level => C_LEVEL_STATEMENT);
717 trace(p_msg => 'l_pri_gl_date = '||l_pri_gl_date,
718 p_module => l_log_module,
719 p_level => C_LEVEL_STATEMENT);
720 trace(p_msg => 'l_ledger_id = '||l_ledger_id,
721 p_module => l_log_module,
722 p_level => C_LEVEL_STATEMENT);
723 trace(p_msg => 'l_legal_entity_id = '||l_legal_entity_id,
724 p_module => l_log_module,
725 p_level => C_LEVEL_STATEMENT);
726 trace(p_msg => 'l_bc_flag = '||l_bc_flag,
727 p_module => l_log_module,
728 p_level => C_LEVEL_STATEMENT);
729 trace(p_msg => 'Accrual Reversal = '||l_mpa_acc_rev_flag,
730 p_module => l_log_module,
731 p_level => C_LEVEL_STATEMENT);
732 END IF;
733
734 /* Bug 7011889 - Writing to trace file the array of header ids through LOOP */
735
736 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
737 FOR i IN l_array_je_header_id.FIRST..l_array_je_header_id.LAST
738 LOOP
739 trace(p_msg => 'l_pri_ae_header_id = '||l_array_je_header_id(i),
740 p_module => l_log_module,
741 p_level => C_LEVEL_STATEMENT);
742 END LOOP;
743 END IF;
744
745 If l_mpa_acc_rev_flag = 'N' THEN
746
747 /* Bug 7011889 - Modified the SQL to handle multiple header ids */
748
749 SELECT MAX(NVL(MPA_ACCRUAL_ENTRY_FLAG,'N'))
750 INTO l_mpa_acc_rev_flag
751 FROM xla_ae_lines
752 WHERE application_id = p_application_id
753 AND ae_header_id in (SELECT ae_header_id
754 FROM xla_ae_headers
755 WHERE event_id = p_event_id
756 AND application_id = p_application_id);
757
758 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
759 trace(p_msg => 'MPA Accrual = '||l_mpa_acc_rev_flag,
760 p_module => l_log_module,
761 p_level => C_LEVEL_STATEMENT);
762 END IF;
763 END IF;
764
765
766 IF NVl(p_post_to_gl_flag,'N') = 'Y' THEN
767 l_completion_option := C_STATUS_POSTING_CODE; -- if previously posted, then reverse is Final and Post to GL
768 ELSE
769 l_completion_option := C_STATUS_FINAL_CODE; -- if not previously posted, then reverse is only Final
770 END IF;
771 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
772 trace(p_msg => 'l_completion_option = '||l_completion_option,
773 p_module => l_log_module,
774 p_level => C_LEVEL_STATEMENT);
775 END IF;
776
777 -------------------------------------------------------------------------
778 -- delete incomplete MPA here or later :
779 -- less work in subsequent APIs, and anyway rollback if there is error
780 -------------------------------------------------------------------------
781 IF l_mpa_acc_rev_flag = 'Y' THEN
782 FOR i in (SELECT ae_header_id
783 FROM xla_ae_headers
784 WHERE application_id = p_application_id
785 AND event_id = p_event_id
786 AND parent_ae_header_id IS NOT NULL
787 AND accounting_entry_status_code <> C_STATUS_FINAL_CODE ) LOOP
788 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
789 trace(p_msg => 'Delete journal entry = '||i.ae_header_id,
790 p_module => l_log_module,
791 p_level => C_LEVEL_STATEMENT);
792 END IF;
793 -------------------------------------------------
794 -- delete incomplete MPA/Accrual Reversal Entries
795 -------------------------------------------------
796 DELETE xla_ae_lines
797 WHERE application_id = p_application_id
798 AND ae_header_id = i.ae_header_id;
799 --
800 DELETE xla_distribution_links
801 WHERE application_id = p_application_id
802 AND ae_header_id = i.ae_header_id;
803 --
804 DELETE xla_ae_headers
805 WHERE application_id = p_application_id
806 AND ae_header_id = i.ae_header_id;
807 --
808 END LOOP;
809 END IF;
810
811 -----------------------------------------------------------------------------------
812 -- Currently, xla_journal_entries_pkg.reverse_journal_entry only process MANUAL entry
813 -----------------------------------------------------------------------------------
814 update xla_ae_headers
815 set accounting_entry_type_code = 'MANUAL'
816 where application_id = p_application_id
817 and event_id = p_event_id;
818
819 --------------------------------------------------------
820 -- reverse journal entries
821 --------------------------------------------------------
822 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
823 trace(p_msg => 'Calling xla_journal_entries_pkg.reverse_journal_entry.',
824 p_module => l_log_module,
825 p_level => C_LEVEL_STATEMENT);
826 END IF;
827
828 /* Bug 7011889 - Replace call to l_pri_ae_header_id with array of header ids for Encumbarance */
829
830 xla_journal_entries_pkg.reverse_journal_entry(
831 p_array_je_header_id => l_array_je_header_id
832 ,p_application_id => p_application_id
833 ,p_reversal_method => p_reversal_method
834 ,p_gl_date => p_gl_date
835 ,p_context => C_UNDO_CONTEXT --added bug 10226301
836 ,p_completion_option => l_completion_option
837 ,p_functional_curr => l_functional_curr
838 ,p_je_source_name => l_je_source_name
839 ,p_rev_header_id => x_rev_ae_header_id
840 ,p_rev_event_id => x_rev_event_id
841 ,p_completion_retcode => l_completion_retcode -- S,X
842 ,p_transfer_request_id => l_transfer_request_id
843 );
844
845 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
846 trace(p_msg => 'Returned from xla_journal_entries_pkg.reverse_journal_entry.',
847 p_module => l_log_module,
848 p_level => C_LEVEL_STATEMENT);
849 END IF;
850
851 IF l_completion_retcode <> 'S' or x_rev_ae_header_id IS NULL THEN
852 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
853 trace(p_msg => 'Failure in xla_journal_entries_pkg.reverse_journal_entry. Please verify log file.',
854 p_module => l_log_module,
855 p_level => C_LEVEL_STATEMENT);
856 END IF;
857 Log_error(p_module => l_log_module
858 ,p_error_msg => 'Failure in xla_journal_entries_pkg.reverse_journal_entry. Please verify log file.');
859 END IF;
860
861 SELECT entity_id
862 INTO x_rev_entity_id
863 FROM xla_events
864 WHERE application_id = p_application_id
865 AND event_id = x_rev_event_id
866 AND rownum = 1;
867
868 -- Bug 6964268 Begin
869 UPDATE xla_transaction_entities
870 SET (entity_code
871 , source_id_int_1
872 , source_id_char_1
873 , security_id_int_1
874 , security_id_int_2
875 , security_id_int_3
876 , security_id_char_1
877 , security_id_char_2
878 , security_id_char_3
879 , source_id_int_2
880 , source_id_char_2
881 , source_id_int_3
882 , source_id_char_3
883 , source_id_int_4
884 , source_id_char_4
885 , valuation_method
886 , source_application_id
887 , upg_batch_id
888 , upg_source_application_id
889 , upg_valid_flag
890 , transaction_number
891 -- legal_entity_id
892 -- ledger_id
893 , creation_date
894 , created_by
895 , last_update_date
896 , last_updated_by
897 , last_update_login) = (SELECT 'MANUAL' -- entity_code This also prevents transaction to be used in bflow.
898 ,source_id_int_1
899 ,source_id_char_1
900 ,security_id_int_1
901 ,security_id_int_2
902 ,security_id_int_3
903 ,security_id_char_1
904 ,security_id_char_2
905 ,security_id_char_3
906 ,source_id_int_2
907 ,source_id_char_2
908 ,source_id_int_3
909 ,source_id_char_3
910 ,source_id_int_4
911 ,source_id_char_4
912 ,valuation_method
913 ,source_application_id
914 ,upg_batch_id
915 ,upg_source_application_id
916 ,upg_valid_flag
917 ,transaction_number --bug#8279661
918 -- legal_entity_id
919 -- ledger_id
920 ,sysdate
921 ,fnd_global.user_id
922 ,sysdate
923 ,fnd_global.user_id
924 ,fnd_global.user_id
925 FROM xla_transaction_entities
926 WHERE application_id = p_application_id
927 AND entity_id = l_entity_id)
928 WHERE application_id = p_application_id
929 AND entity_id = x_rev_entity_id;
930
931
932 -- Bug 6964268 End
933
934 -----------------------------------------------------------------
935 -- Create new event and entity, same details as original entry
936 -----------------------------------------------------------------
937 l_event_source_info.application_id := p_application_id;
938 l_event_source_info.legal_entity_id := l_legal_entity_id;
939 l_event_source_info.ledger_id := l_ledger_id;
940 l_event_source_info.entity_type_code := 'MANUAL';
941
942 ---------------------------------------------------------------------------------------------
943 -- Currently, xla_events_pkg.validate_event_type_code failes if not MANUAL event type
944 ---------------------------------------------------------------------------------------------
945 -- Currently, xla_events_pkg.validate_event_type_code can only process MANUAL event type
946 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
947 trace(p_msg => 'Calling xla_events_pkg.create_manual_event.',
948 p_module => l_log_module,
949 p_level => C_LEVEL_STATEMENT);
950 END IF;
951 x_new_event_id := xla_events_pkg.create_manual_event
952 (p_event_source_info => l_event_source_info
953 ,p_event_type_code => 'MANUAL'
954 ,p_event_date => l_pri_gl_date
955 ,p_event_status_code => xla_events_pub_pkg.C_EVENT_UNPROCESSED
956 ,p_process_status_code => xla_events_pkg.C_INTERNAL_UNPROCESSED
957 ,p_event_number => 1
958 ,p_budgetary_control_flag => l_bc_flag);
959 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
960 trace(p_msg => 'Returned from xla_events_pkg.create_manual_event = event id '||x_new_event_id,
961 p_module => l_log_module,
962 p_level => C_LEVEL_STATEMENT);
963 END IF;
964
965 -----------------------------------------------------
966 -- update new EVENT_ID and ENTITY_ID
967 -----------------------------------------------------
968 UPDATE xla_events
969 SET event_status_code = xla_events_pub_pkg.C_EVENT_PROCESSED
970 , process_status_code = xla_events_pub_pkg.C_EVENT_PROCESSED
971 ,(event_type_code
972 , event_date
973 , reference_num_1
974 , reference_num_2
975 , reference_num_3
976 , reference_num_4
977 , reference_char_1
978 , reference_char_2
979 , reference_char_3
980 , reference_char_4
981 , reference_date_1
982 , reference_date_2
983 , reference_date_3
984 , reference_date_4
985 , on_hold_flag
986 , upg_batch_id
987 , upg_source_application_id
988 , upg_valid_flag
989 , transaction_date
990 , budgetary_control_flag
991 , merge_event_set_id
992 -- event_number
993 , creation_date
994 , created_by
995 , last_update_date
996 , last_updated_by
997 , last_update_login
998 , program_update_date
999 , program_application_id
1000 , program_id
1001 , request_id) = (SELECT 'MANUAL' -- event_type_code
1002 , event_date
1003 , reference_num_1
1004 , reference_num_2
1005 , reference_num_3
1006 , reference_num_4
1007 , reference_char_1
1008 , reference_char_2
1009 , reference_char_3
1010 , reference_char_4
1011 , reference_date_1
1012 , reference_date_2
1013 , reference_date_3
1014 , reference_date_4
1015 , on_hold_flag
1016 , upg_batch_id
1017 , upg_source_application_id
1018 , upg_valid_flag
1019 , transaction_date
1020 , budgetary_control_flag
1021 , merge_event_set_id
1022 -- event_number
1023 , sysdate
1024 , fnd_global.user_id
1025 , sysdate
1026 , fnd_global.user_id
1027 , fnd_global.user_id
1028 , sysdate
1029 , -1
1030 , -1
1031 , -1
1032 FROM xla_events
1033 WHERE application_id = p_application_id
1034 AND event_id = p_event_id)
1035 WHERE application_id = p_application_id
1036 AND event_id = x_new_event_id
1037 RETURNING entity_id INTO x_new_entity_id;
1038
1039 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1040 trace(p_msg => 'entity id = '||x_new_entity_id,
1041 p_module => l_log_module,
1042 p_level => C_LEVEL_STATEMENT);
1043 END IF;
1044
1045 UPDATE xla_transaction_entities
1046 SET (entity_code
1047 , source_id_int_1
1048 , source_id_char_1
1049 , security_id_int_1
1050 , security_id_int_2
1051 , security_id_int_3
1052 , security_id_char_1
1053 , security_id_char_2
1054 , security_id_char_3
1055 , source_id_int_2
1056 , source_id_char_2
1057 , source_id_int_3
1058 , source_id_char_3
1059 , source_id_int_4
1060 , source_id_char_4
1061 , valuation_method
1062 , source_application_id
1063 , upg_batch_id
1064 , upg_source_application_id
1065 , upg_valid_flag
1066 , transaction_number
1067 -- legal_entity_id
1068 -- ledger_id
1069 , creation_date
1070 , created_by
1071 , last_update_date
1072 , last_updated_by
1073 , last_update_login) = (SELECT 'MANUAL' -- entity_code This also prevents transaction to be used in bflow.
1074 ,source_id_int_1
1075 ,source_id_char_1
1076 ,security_id_int_1
1077 ,security_id_int_2
1078 ,security_id_int_3
1079 ,security_id_char_1
1080 ,security_id_char_2
1081 ,security_id_char_3
1082 ,source_id_int_2
1083 ,source_id_char_2
1084 ,source_id_int_3
1085 ,source_id_char_3
1086 ,source_id_int_4
1087 ,source_id_char_4
1088 ,valuation_method
1089 ,source_application_id
1090 ,upg_batch_id
1091 ,upg_source_application_id
1092 ,upg_valid_flag
1093 ,transaction_number -- bug#8279661
1094 -- legal_entity_id
1095 -- ledger_id
1096 ,sysdate
1097 ,fnd_global.user_id
1098 ,sysdate
1099 ,fnd_global.user_id
1100 ,fnd_global.user_id
1101 FROM xla_transaction_entities
1102 WHERE application_id = p_application_id
1103 AND entity_id = l_entity_id)
1104 WHERE application_id = p_application_id
1105 AND entity_id = x_new_entity_id;
1106
1107
1108 ---------------------------------------------------------
1109 -- audit original event and entries
1110 ---------------------------------------------------------
1111 audit_datafix (p_application_id => p_application_id
1112 ,p_event_id => p_event_id
1113 ,p_audit_all => 'Y');
1114
1115 -------------------------------------------------------------------------------
1116 -- set original entries to link to new event, entity. Also update Description
1117 -------------------------------------------------------------------------------
1118 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1119 trace(p_msg => 'Update xla_ae_headers',
1120 p_module => l_log_module,
1121 p_level => C_LEVEL_STATEMENT);
1122 END IF;
1123
1124
1125 -- bug#8279661
1126 OPEN c_entity_code(l_entity_id);
1127 FETCH c_entity_code INTO l_orig_entity_code;
1128 CLOSE c_entity_code;
1129
1130 /* bug#8279661 Get the entity description details for the event thats reversed */
1131 l_new_description := 'Accounting Error Correction - Initial journal entry for the event_id '||p_event_id || ' For ' ||
1132 get_transaction_details(p_application_id,l_entity_id, 'Y', l_orig_entity_code ); --9813848
1133
1134 UPDATE xla_ae_headers
1135 SET entity_id = x_new_entity_id
1136 ,event_id = x_new_event_id
1137 ,event_type_code = 'MANUAL'
1138 ,description = l_new_description -- 'Data fix entry: event_id of '||p_event_id
1139 WHERE application_id = p_application_id
1140 AND event_id = p_event_id
1141 RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
1142
1143 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1144 trace(p_msg => 'Update xla_ae_lines',
1145 p_module => l_log_module,
1146 p_level => C_LEVEL_STATEMENT);
1147 END IF;
1148 FORALL i in 1..l_array_ae_header_id.COUNT
1149 UPDATE xla_ae_lines
1150 SET description = l_new_description --'Data fix entry: event_id of '||p_event_id
1151 -- business_class_code = NULL -- This is not needed to prevent use by bflow since the entity_code is now 'MANUAL'
1152 WHERE application_id = p_application_id
1153 AND ae_header_id = l_array_ae_header_id(i);
1154
1155 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1156 trace(p_msg => 'Update xla_distribution_links',
1157 p_module => l_log_module,
1158 p_level => C_LEVEL_STATEMENT);
1159 END IF;
1160
1161 FORALL i in 1..l_array_ae_header_id.COUNT
1162 UPDATE xla_distribution_links
1163 SET event_id = x_new_event_id,
1164 temp_line_num = abs(temp_line_num) -- added for RCA bug#8421688
1165 WHERE application_id = p_application_id
1166 AND ae_header_id = l_array_ae_header_id(i);
1167
1168 /*
1169 bug#8421688:
1170 On undoing a cancelled event like invoice cancellation or payment cancellation, the redo of that event
1171 is resulting in accounting error as the NOT EXISTS of the following select fails in xla_ae_lines_pkg
1172 accounting_reversal procedure.
1173 SELECT 1 FROM xla_distribution_links xdl
1174 WHERE ref_ae_header_id = xdl.ae_header_id
1175 AND temp_line_num = xdl.temp_line_num * -1
1176 AND application_id = xdl.application_id
1177 Fix is to make the E3 event temp_line_num +ve for a cancelled event in xla_distribution_links table
1178 using abs(temp_line_num).
1179 */
1180
1181 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1182 trace(p_msg => 'Update xla_events',
1183 p_module => l_log_module,
1184 p_level => C_LEVEL_STATEMENT);
1185 END IF;
1186 ---------------------------------------------------------
1187 -- set original event to Unprocessed
1188 ---------------------------------------------------------
1189 UPDATE XLA_EVENTS
1190 SET EVENT_STATUS_CODE = xla_events_pub_pkg.C_EVENT_UNPROCESSED
1191 ,PROCESS_STATUS_CODE = xla_events_pkg.C_INTERNAL_UNPROCESSED
1192 WHERE application_id = p_application_id
1193 AND event_id = p_event_id;
1194
1195 -------------------------------------------------------------------------------
1196 -- update Description for reverse entries
1197 -------------------------------------------------------------------------------
1198 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1199 trace(p_msg => 'Update descriptions',
1200 p_module => l_log_module,
1201 p_level => C_LEVEL_STATEMENT);
1202 END IF;
1203
1204 /* bug#8279661 Get the entity description details for the event thats reversed */
1205 l_new_description := 'Accounting Error Correction - Offsetting journal entry for the event_id '||x_new_event_id || ' For ' ||
1206 get_transaction_details(p_application_id,l_entity_id, 'Y', l_orig_entity_code ); --9813848
1207
1208 UPDATE xla_ae_headers
1209 SET description = l_new_description --'Data fix reversal entry: event_id of '||x_new_event_id
1210 WHERE application_id = p_application_id
1211 AND event_id = x_rev_event_id
1212 RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
1213
1214 FORALL i in 1..l_array_ae_header_id.COUNT
1215 UPDATE xla_ae_lines
1216 SET description = l_new_description -- 'Data fix reversal entry: event_id of '||x_new_event_id
1217 WHERE application_id = p_application_id
1218 AND ae_header_id = l_array_ae_header_id(i);
1219
1220 ----------------------------------------------------------
1221 -- audit reversed event
1222 ----------------------------------------------------------
1223 audit_datafix (p_application_id => p_application_id
1224 ,p_event_id => x_rev_event_id
1225 ,p_audit_all => 'Y');
1226
1227
1228 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1229 ,p_data => x_msg_data);
1230
1231 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1232 trace(p_msg => 'END of procedure reverse_journal_entries',
1233 p_module => l_log_module,
1234 p_level => C_LEVEL_PROCEDURE);
1235 END IF;
1236
1237 ELSIF l_entity_id IS NULL THEN
1238
1239 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1240 trace(p_msg => 'Journal entry cannot be reversed as its not transferred to General Ledger.',
1241 p_module => l_log_module,
1242 p_level => C_LEVEL_STATEMENT);
1243 END IF;
1244 Log_error(p_module => l_log_module
1245 ,p_error_msg => 'Journal entry cannot be reversed as its not transferred to General Ledger.');
1246 END IF;
1247
1248
1249 EXCEPTION
1250 WHEN FND_API.G_EXC_ERROR THEN
1251 ROLLBACK;
1252 x_return_status := FND_API.G_RET_STS_ERROR ;
1253 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1254 ,p_data => x_msg_data);
1255
1256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1257 ROLLBACK;
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1259 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1260 ,p_data => x_msg_data);
1261
1262 WHEN OTHERS THEN
1263 ROLLBACK;
1264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1265 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1266 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
1267 END IF;
1268 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1269 ,p_data => x_msg_data);
1270 END reverse_journal_entries;
1271
1272
1273
1274 --=============================================================================
1275 --
1276 --
1277 --
1278 --=============================================================================
1279 PROCEDURE redo_accounting
1280 (p_api_version IN NUMBER
1281 ,p_init_msg_list IN VARCHAR2
1282 ,p_application_id IN INTEGER
1283 ,p_event_id IN INTEGER
1284 ,p_gl_posting_flag IN VARCHAR2
1285 ,x_return_status OUT NOCOPY VARCHAR2
1286 ,x_msg_count OUT NOCOPY NUMBER
1287 ,x_msg_data OUT NOCOPY VARCHAR2
1288 ) IS
1289 l_api_name CONSTANT VARCHAR2(30) := 'redo_accounting';
1290 l_api_version CONSTANT NUMBER := 1.0;
1291
1292 l_errbuf VARCHAR2(240);
1293 l_retcode INTEGER;
1294 l_log_module VARCHAR2(240);
1295 l_dummy INTEGER;
1296 l_accounting_mode VARCHAR2(30);
1297 l_process_status VARCHAR2(1);
1298 l_batch_id INTEGER;
1299
1300 BEGIN
1301 IF g_log_enabled THEN
1302 l_log_module := C_DEFAULT_MODULE||'.redo_accounting';
1303 END IF;
1304
1305 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1306 trace(p_msg => 'BEGIN of procedure redo_accounting',
1307 p_module => l_log_module,
1308 p_level => C_LEVEL_PROCEDURE);
1309 END IF;
1310
1311 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1312 FND_MSG_PUB.initialize;
1313 END IF;
1314
1315 -- Standard call to check for call compatibility.
1316 IF (NOT FND_API.compatible_api_call
1317 (p_current_version_number => l_api_version
1318 ,p_caller_version_number => p_api_version
1319 ,p_api_name => l_api_name
1320 ,p_pkg_name => C_DEFAULT_MODULE))
1321 THEN
1322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1323 END IF;
1324
1325 -- Initialize global variables
1326 x_return_status := FND_API.G_RET_STS_SUCCESS;
1327
1328 -- Validation ------------------------------------------------------------------------------------------
1329 SELECT DECODE(NVL(budgetary_control_flag,'N'),'Y', C_STATUS_FUNDS_RESERVE, C_STATUS_FINAL),process_status_code
1330 INTO l_accounting_mode, l_process_status
1331 FROM xla_events
1332 WHERE application_id = p_application_id
1333 AND event_id = p_event_id;
1334
1335 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1336 trace(p_msg => 'l_accounting_mode='||l_accounting_mode||', l_process_status='||l_process_status,
1337 p_module => l_log_module,
1338 p_level => C_LEVEL_STATEMENT);
1339 END IF;
1340 IF l_process_status <> 'U' THEN
1341 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1342 trace(p_msg => 'No such event or event has been processed. Please verify.',
1343 p_module => l_log_module,
1344 p_level => C_LEVEL_STATEMENT);
1345 END IF;
1346 Log_error(p_module => l_log_module
1347 ,p_error_msg => 'No such event or event has been processed. Please verify.');
1348 END IF;
1349 --------------------------------------------------------------------------------------------------------
1350
1351 ---------------------------------------------------------
1352 -- populate a row to be used by accounting_program_events
1353 ---------------------------------------------------------
1354 INSERT INTO xla_acct_prog_events_gt (event_id, ledger_id)
1355 VALUES (p_event_id, null);
1356 --
1357 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1358 trace(p_msg => 'Calling xla_accounting_pub_pkg.accounting_program_events.',
1359 p_module => l_log_module,
1360 p_level => C_LEVEL_STATEMENT);
1361 END IF;
1362 xla_accounting_pub_pkg.accounting_program_events
1363 (p_application_id => p_application_id
1364 ,p_accounting_mode => l_accounting_mode
1365 ,p_gl_posting_flag => p_gl_posting_flag
1366 ,p_accounting_batch_id => l_batch_id
1367 ,p_errbuf => l_errbuf
1368 ,p_retcode => l_retcode);
1369 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1370 trace(p_msg => 'Returned from xla_accounting_pub_pkg.accounting_program_events.',
1371 p_module => l_log_module,
1372 p_level => C_LEVEL_STATEMENT);
1373 END IF;
1374
1375 ----------------------------------------------------------------------------------------------
1376 -- when BC event failed, l_retcode is still 0, do this check to make sure vent is procesed.
1377 ----------------------------------------------------------------------------------------------
1378 SELECT process_status_code
1379 INTO l_process_status
1380 FROM xla_events
1381 WHERE application_id = p_application_id
1382 AND event_id = p_event_id;
1383
1384 IF l_retcode = 0 AND l_process_status = 'P' THEN
1385
1386 audit_datafix (p_application_id => p_application_id
1387 ,p_event_id => p_event_id
1388 ,p_audit_all => 'Y');
1389
1390 ELSE
1391 Log_error(p_module => l_log_module
1392 ,p_error_msg => 'Error in redo accounting. Please check the log file.');
1393 END IF;
1394
1395 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1396 ,p_data => x_msg_data);
1397
1398 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1399 trace(p_msg => 'END of procedure redo_accounting',
1400 p_module => l_log_module,
1401 p_level => C_LEVEL_PROCEDURE);
1402 END IF;
1403
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 ROLLBACK;
1407 x_return_status := FND_API.G_RET_STS_ERROR ;
1408 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1409 ,p_data => x_msg_data);
1410
1411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1412 ROLLBACK;
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1414 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1415 ,p_data => x_msg_data);
1416
1417
1418 WHEN OTHERS THEN
1419 ROLLBACK;
1420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1421 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1422 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
1423 END IF;
1424 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1425 ,p_data => x_msg_data);
1426 END redo_accounting;
1427
1428
1429 --=============================================================================
1430 --Added bug 10226301
1431 --
1432 --
1433 --============================================================================
1434 PROCEDURE reset_global IS
1435 l_log_module VARCHAR2(240);
1436 BEGIN
1437 IF g_log_enabled THEN
1438 l_log_module := C_DEFAULT_MODULE||'.reset_global';
1439 END IF;
1440 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1441 trace
1442 (p_msg => 'BEGIN of procedure RESET GLOBAL'
1443 ,p_level => C_LEVEL_PROCEDURE
1444 ,p_module => l_log_module);
1445 END IF;
1446
1447 global_error_index := 0;
1448 global_accounting_errors.DELETE;
1449
1450 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1451 trace
1452 (p_msg => 'END of procedure RESET GLOBAL'
1453 ,p_level => C_LEVEL_PROCEDURE
1454 ,p_module => l_log_module);
1455 END IF;
1456 EXCEPTION
1457 WHEN xla_exceptions_pkg.application_exception THEN
1458 RAISE;
1459 WHEN OTHERS THEN
1460 xla_exceptions_pkg.raise_message
1461 (p_location => 'xla_accounting_err_pkg.reset_global');
1462 END reset_global; -- end of procedure
1463
1464
1465 --=============================================================================
1466 --
1467 --
1468 --
1469 --=============================================================================
1470
1471 --=============================================================================
1472 --
1473 --
1474 --
1475 --=============================================================================
1476 PROCEDURE do_not_transfer_je
1477 (p_api_version IN NUMBER
1478 ,p_init_msg_list IN VARCHAR2
1479 ,p_application_id IN INTEGER
1480 ,p_ae_header_id IN INTEGER
1481 ,x_return_status OUT NOCOPY VARCHAR2
1482 ,x_msg_count OUT NOCOPY NUMBER
1483 ,x_msg_data OUT NOCOPY VARCHAR2
1484 ) IS
1485 l_api_name CONSTANT VARCHAR2(30) := 'do_not_transfer_je';
1486 l_api_version CONSTANT NUMBER := 1.0;
1487
1488 l_retcode INTEGER;
1489 l_log_module VARCHAR2(240);
1490 l_dummy NUMBER;
1491
1492 BEGIN
1493 IF g_log_enabled THEN
1494 l_log_module := C_DEFAULT_MODULE||'.do_not_transfer_je';
1495 END IF;
1496
1497 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1498 trace(p_msg => 'BEGIN of procedure do_not_transfer_je',
1499 p_module => l_log_module,
1500 p_level => C_LEVEL_PROCEDURE);
1501 END IF;
1502
1503 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1504 FND_MSG_PUB.initialize;
1505 END IF;
1506
1507 -- Standard call to check for call compatibility.
1508 IF (NOT FND_API.compatible_api_call
1509 (p_current_version_number => l_api_version
1510 ,p_caller_version_number => p_api_version
1511 ,p_api_name => l_api_name
1512 ,p_pkg_name => C_DEFAULT_MODULE))
1513 THEN
1514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1515 END IF;
1516
1517 -- Initialize global variables
1518 x_return_status := FND_API.G_RET_STS_SUCCESS;
1519
1520 UPDATE xla_ae_headers
1521 SET gl_transfer_status_code = 'NT'
1522 WHERE application_id = p_application_id
1523 AND ae_header_id = p_ae_header_id
1524 AND accounting_entry_status_code = C_STATUS_FINAL_CODE
1525 AND gl_transfer_status_code = 'N'; -- if already transferred, S or Y, then do not set to NT.
1526
1527 l_dummy := SQL%ROWCOUNT;
1528 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1529 trace(p_msg => 'Rows updated = '||l_dummy,
1530 p_module => l_log_module,
1531 p_level => C_LEVEL_STATEMENT);
1532 END IF;
1533
1534 IF l_dummy = 0 THEN
1535 Log_error(p_module => l_log_module
1536 ,p_error_msg => 'No such entry, or the entry is not in Final mode or it has been transferred. Please verify.');
1537 END IF;
1538
1539 audit_datafix (p_application_id => p_application_id
1540 ,p_ae_header_id => p_ae_header_id);
1541
1542 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1543 ,p_data => x_msg_data);
1544
1545 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1546 trace(p_msg => 'END of procedure do_not_transfer_je',
1547 p_module => l_log_module,
1548 p_level => C_LEVEL_PROCEDURE);
1549 END IF;
1550
1551 EXCEPTION
1552 WHEN FND_API.G_EXC_ERROR THEN
1553 ROLLBACK;
1554 x_return_status := FND_API.G_RET_STS_ERROR ;
1555 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1556 ,p_data => x_msg_data);
1557
1558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1559 ROLLBACK;
1560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1561 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1562 ,p_data => x_msg_data);
1563
1564 WHEN OTHERS THEN
1565 ROLLBACK;
1566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1567 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1568 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
1569 END IF;
1570 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1571 ,p_data => x_msg_data);
1572 END do_not_transfer_je;
1573
1574
1575 --=============================================================================
1576 -- PROCEDURE delete_tb_entries to delete original event E1 entries from
1577 -- TRIAL BALANCES table
1578 --
1579 --=============================================================================
1580
1581 PROCEDURE delete_tb_entries( p_event_id IN NUMBER
1582 ,p_application_id IN NUMBER)
1583 IS
1584 l_log_module VARCHAR2(240);
1585 BEGIN
1586
1587 IF g_log_enabled THEN
1588 l_log_module := C_DEFAULT_MODULE||'.delete_tb_entries';
1589 END IF;
1590
1591 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1592 trace(p_msg => 'BEGIN of procedure delete tb entries',
1593 p_module => l_log_module,
1594 p_level => C_LEVEL_PROCEDURE);
1595 END IF;
1596
1597 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1598 trace(p_msg => 'p_event_id ='||p_event_id,
1599 p_module => l_log_module,
1600 p_level => C_LEVEL_PROCEDURE);
1601 END IF;
1602
1603 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1604 trace(p_msg => 'p_application_id ='||p_application_id,
1605 p_module => l_log_module,
1606 p_level => C_LEVEL_PROCEDURE);
1607 END IF;
1608
1609 FOR i in ( SELECT xah.ae_header_id
1610 ,xah.accounting_date
1611 ,xah.ledger_id
1612 ,xah.entity_id
1613 ,xtb.definition_code
1614 FROM xla_ae_headers xah
1615 ,xla_tb_defn_je_sources xtbje
1616 ,xla_tb_definitions_vl xtb
1617 ,xla_subledgers xsl
1618 WHERE xah.application_id = p_application_id
1619 AND xah.event_id = p_event_id
1620 AND xtb.ledger_id = xah.ledger_id
1621 AND xtb.definition_code = xtbje.definition_code
1622 AND xsl.application_id = xah.application_id
1623 AND xsl.je_source_name = xtbje.je_source_name
1624 AND xtb.enabled_flag = 'Y'
1625 )
1626 LOOP
1627 DELETE FROM xla_trial_balances
1628 WHERE definition_code = i.definition_code
1629 AND ae_header_id = i.ae_header_id
1630 AND gl_date between (i.accounting_date-2) and (i.accounting_date+2)
1631 AND ledger_id = i.ledger_id
1632 AND source_entity_id = i.entity_id
1633 AND source_application_id = p_application_id;
1634 END LOOP;
1635
1636 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1637 trace(p_msg => 'END of procedure delete tb entries',
1638 p_module => l_log_module,
1639 p_level => C_LEVEL_PROCEDURE);
1640 END IF;
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 ROLLBACK;
1644 RAISE;
1645 END;
1646
1647
1648 --=============================================================================
1649 --
1650 --
1651 --
1652 --=============================================================================
1653 PROCEDURE validate_journal_entry
1654 (p_api_version IN NUMBER
1655 ,p_init_msg_list IN VARCHAR2
1656 ,p_application_id IN INTEGER
1657 ,p_ae_header_id IN INTEGER
1658 ,x_return_status OUT NOCOPY VARCHAR2
1659 ,x_msg_count OUT NOCOPY NUMBER
1660 ,x_msg_data OUT NOCOPY VARCHAR2
1661 ) IS
1662 l_api_name CONSTANT VARCHAR2(30) := 'validate_journal_entry';
1663 l_api_version CONSTANT NUMBER := 1.0;
1664
1665 l_retcode INTEGER;
1666 l_log_module VARCHAR2(240);
1667 l_dummy INTEGER;
1668
1669 BEGIN
1670 IF g_log_enabled THEN
1671 l_log_module := C_DEFAULT_MODULE||'.validate_journal_entry';
1672 END IF;
1673
1674 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1675 trace(p_msg => 'BEGIN of procedure validate_journal_entry',
1676 p_module => l_log_module,
1677 p_level => C_LEVEL_PROCEDURE);
1678 END IF;
1679
1680 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1681 FND_MSG_PUB.initialize;
1682 END IF;
1683
1684 -- Standard call to check for call compatibility.
1685 IF (NOT FND_API.compatible_api_call
1686 (p_current_version_number => l_api_version
1687 ,p_caller_version_number => p_api_version
1688 ,p_api_name => l_api_name
1689 ,p_pkg_name => C_DEFAULT_MODULE))
1690 THEN
1691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1692 END IF;
1693
1694 -- Initialize global variables
1695 x_return_status := FND_API.G_RET_STS_SUCCESS;
1696
1697 SELECT count(*)
1698 INTO l_dummy
1699 FROM xla_ae_headers
1700 WHERE application_id = p_application_id
1701 AND ae_header_id = p_ae_header_id
1702 AND accounting_entry_status_code = C_STATUS_FINAL_CODE;
1703
1704 IF l_dummy = 0 THEN
1705 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1706 trace(p_msg => 'No such entry or it is not in Final mode.',
1707 p_module => l_log_module,
1708 p_level => C_LEVEL_STATEMENT);
1709 END IF;
1710 Log_error(p_module => l_log_module
1711 ,p_error_msg => 'No such entry or it is not in Final mode.');
1712 END IF;
1713
1714 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1715 trace(p_msg => 'Calling XLA_UPGRADE_PUB.Validate_Header_Line_Entries.',
1716 p_module => l_log_module,
1717 p_level => C_LEVEL_STATEMENT);
1718 END IF;
1719 XLA_UPGRADE_PUB.Validate_Header_Line_Entries (
1720 p_application_id => p_application_id
1721 ,p_header_id => p_ae_header_id);
1722 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1723 trace(p_msg => 'Returned from XLA_UPGRADE_PUB.Validate_Header_Line_Entries.',
1724 p_module => l_log_module,
1725 p_level => C_LEVEL_STATEMENT);
1726 END IF;
1727
1728
1729 FOR i IN (SELECT error_message_name
1730 FROM xla_upg_errors
1731 WHERE application_id = p_application_id
1732 AND ae_header_id = p_ae_header_id) LOOP
1733 Log_error(p_error_name => i.ERROR_MESSAGE_NAME);
1734 END LOOP;
1735
1736 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1737 ,p_data => x_msg_data);
1738
1739 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1740 trace(p_msg => 'END of procedure validate_journal_entry',
1741 p_module => l_log_module,
1742 p_level => C_LEVEL_PROCEDURE);
1743 END IF;
1744
1745 EXCEPTION
1746 WHEN FND_API.G_EXC_ERROR THEN
1747 ROLLBACK;
1748 x_return_status := FND_API.G_RET_STS_ERROR ;
1749 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1750 ,p_data => x_msg_data);
1751
1752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1753 ROLLBACK;
1754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1755 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1756 ,p_data => x_msg_data);
1757
1758 WHEN OTHERS THEN
1759 ROLLBACK;
1760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1761 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1762 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
1763 END IF;
1764 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1765 ,p_data => x_msg_data);
1766 END validate_journal_entry;
1767
1768
1769
1770 --=============================================================================
1771 --
1772 --
1773 --
1774 --=============================================================================
1775 PROCEDURE audit_datafix
1776 (p_application_id IN INTEGER
1777 ,p_ae_header_id IN INTEGER DEFAULT NULL
1778 ,p_ae_line_num IN INTEGER DEFAULT NULL
1779 ,p_event_id IN INTEGER DEFAULT NULL
1780 ,p_audit_all IN VARCHAR2 DEFAULT 'N'
1781 ) IS
1782
1783 l_log_module VARCHAR2(240);
1784 l_array_ae_header_id t_array_integer;
1785
1786 BEGIN
1787
1788 IF g_log_enabled THEN
1789 l_log_module := C_DEFAULT_MODULE||'.audit_datafix';
1790 END IF;
1791
1792 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1793 trace(p_msg => 'BEGIN of procedure audit_datafix',
1794 p_module => l_log_module,
1795 p_level => C_LEVEL_PROCEDURE);
1796 END IF;
1797
1798 -----------------------------------------------------
1799 -- audit xla_ae_headers
1800 -----------------------------------------------------
1801 IF p_ae_header_id IS NOT NULL THEN
1802 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1803 trace(p_msg => 'Audit xla_ae_headers.',
1804 p_module => l_log_module,
1805 p_level => C_LEVEL_STATEMENT);
1806 END IF;
1807 UPDATE XLA_AE_HEADERS
1808 SET LAST_UPDATE_DATE = sysdate
1809 ,UPG_BATCH_ID = -9999
1810 WHERE application_id = p_application_id
1811 AND ae_header_id = p_ae_header_id;
1812 END IF;
1813
1814 -----------------------------------------------------
1815 -- audit xla_ae_lines
1816 -----------------------------------------------------
1817 IF p_ae_line_num IS NOT NULL THEN
1818 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1819 trace(p_msg => 'Audit xla_ae_lines.',
1820 p_module => l_log_module,
1821 p_level => C_LEVEL_STATEMENT);
1822 END IF;
1823 UPDATE XLA_AE_LINES
1824 SET LAST_UPDATE_DATE = sysdate
1825 ,UPG_BATCH_ID = -9999
1826 WHERE application_id = p_application_id
1827 AND ae_header_id = p_ae_header_id
1828 AND ae_line_num = p_ae_line_num;
1829 END IF;
1830
1831 -----------------------------------------------------
1832 -- audit xla_events and all related entries
1833 -----------------------------------------------------
1834 IF p_event_id IS NOT NULL THEN
1835 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1836 trace(p_msg => 'Audit xla_events.',
1837 p_module => l_log_module,
1838 p_level => C_LEVEL_STATEMENT);
1839 END IF;
1840 UPDATE XLA_EVENTS
1841 SET LAST_UPDATE_DATE = sysdate
1842 ,UPG_BATCH_ID = -9999
1843 WHERE application_id = p_application_id
1844 AND event_id = p_event_id;
1845
1846 IF p_audit_all = 'Y' THEN
1847 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1848 trace(p_msg => 'Audit all details of xla_events.',
1849 p_module => l_log_module,
1850 p_level => C_LEVEL_STATEMENT);
1851 END IF;
1852 UPDATE XLA_AE_HEADERS
1853 SET LAST_UPDATE_DATE = sysdate
1854 ,UPG_BATCH_ID = -9999
1855 WHERE application_id = p_application_id
1856 AND event_id = p_event_id
1857 RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
1858
1859 FORALL i in 1..l_array_ae_header_id.COUNT
1860 UPDATE XLA_AE_LINES
1861 SET LAST_UPDATE_DATE = sysdate
1862 ,UPG_BATCH_ID = -9999
1863 WHERE application_id = p_application_id
1864 AND ae_header_id = l_array_ae_header_id(i);
1865 END IF;
1866
1867 END IF;
1868
1869 END audit_datafix;
1870
1871
1872
1873 --=============================================================================
1874 --
1875 --
1876 --
1877 --=============================================================================
1878 -- Currently there is no token param needed, but can be enhanced if necessary.
1879 PROCEDURE log_error
1880 (p_module IN VARCHAR2 DEFAULT NULL
1881 ,p_error_msg IN VARCHAR2 DEFAULT NULL
1882 ,p_error_name IN VARCHAR2 DEFAULT NULL
1883 ) IS
1884
1885 BEGIN
1886
1887 IF p_error_name IS NULL THEN
1888 -- An internal error occurred. Please inform your system administrator or
1889 -- support representative that:
1890 -- An internal error has occurred in the program LOCATION. ERROR.
1891 --
1892 Xla_exceptions_pkg.raise_message
1893 (p_appli_s_name => 'XLA'
1894 ,p_msg_name => 'XLA_COMMON_ERROR'
1895 ,p_token_1 => 'LOCATION'
1896 ,p_value_1 => p_module
1897 ,p_token_2 => 'ERROR'
1898 ,p_value_2 => p_error_msg
1899 ,p_msg_mode => g_msg_mode);
1900
1901 ELSE
1902 Xla_exceptions_pkg.raise_message
1903 (p_appli_s_name => 'XLA'
1904 ,p_msg_name => p_error_name
1905 ,p_msg_mode => g_msg_mode);
1906
1907 END IF;
1908
1909 Raise FND_API.G_EXC_ERROR;
1910
1911 END log_error;
1912
1913
1914 --=============================================================================
1915 --
1916 -- Following code is executed when the package body is referenced for the first
1917 -- time
1918 --
1919 --=============================================================================
1920 BEGIN
1921
1922 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1923 g_log_enabled := fnd_log.test
1924 (log_level => g_log_level
1925 ,module => C_DEFAULT_MODULE);
1926
1927 IF NOT g_log_enabled THEN
1928 g_log_level := C_LEVEL_LOG_DISABLED;
1929 END IF;
1930
1931 END xla_datafixes_pub;