[Home] [Help]
PACKAGE BODY: APPS.XLA_DATAFIXES_PUB
Source
1 PACKAGE BODY xla_datafixes_pub AS
2 /* $Header: xlajedfp.pkb 120.1.12010000.2 2008/08/06 21:16:58 sbhaskar 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
51
52 g_log_level NUMBER;
53 g_log_enabled BOOLEAN;
54
55 PROCEDURE trace
56 (p_msg IN VARCHAR2
57 ,p_module IN VARCHAR2
58 ,p_level IN NUMBER) IS
59 BEGIN
60 ----------------------------------------------------------------------------
61 -- Following is for FND log.
62 ----------------------------------------------------------------------------
63 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
64 fnd_log.message(p_level, p_module);
65 ELSIF p_level >= g_log_level THEN
66 fnd_log.string(p_level, p_module, p_msg);
67 END IF;
68
69 EXCEPTION
70 WHEN xla_exceptions_pkg.application_exception THEN
71 RAISE;
72 WHEN OTHERS THEN
73 xla_exceptions_pkg.raise_message
74 (p_location => 'xla_datafixes_pub.trace');
75 END trace;
76
77 --=============================================================================
78 -- *********** public procedures and functions **********
79 --=============================================================================
80
81
82 --=============================================================================
83 --
84 -- Following API are used for data fix:
85 --
86 -- 1. delete_journal_entries
87 -- 2. reverse_journal_entries
88 -- 3. redo_accounting
89 -- 4. do_not_transfer_je
90 -- 5. validate_journal_entry
91 --
92 --
93 --=============================================================================
94
95
96 --=============================================================================
97 --
98 --
99 --
100 --=============================================================================
101
102 PROCEDURE delete_journal_entries
103 (p_api_version IN NUMBER
104 ,p_init_msg_list IN VARCHAR2
105 ,p_application_id IN INTEGER
106 ,p_event_id IN INTEGER
107 ,x_return_status OUT NOCOPY VARCHAR2
108 ,x_msg_count OUT NOCOPY NUMBER
109 ,x_msg_data OUT NOCOPY VARCHAR2
110 ) IS
111
112 l_api_name CONSTANT VARCHAR2(30) := 'delete_journal_entries';
113 l_api_version CONSTANT NUMBER := 1.0;
114
115 l_retcode INTEGER;
116 l_log_module VARCHAR2(240);
117 l_gl_transfer_status_code VARCHAR2(10) := NULL;
118
119 BEGIN
120 IF g_log_enabled THEN
121 l_log_module := C_DEFAULT_MODULE||'.delete_journal_entries';
122 END IF;
123
124 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
125 trace(p_msg => 'BEGIN of procedure delete_journal_entries',
126 p_module => l_log_module,
127 p_level => C_LEVEL_PROCEDURE);
128 END IF;
129
130 IF (FND_API.to_boolean(p_init_msg_list)) THEN
131 FND_MSG_PUB.initialize;
132 END IF;
133
134 -- Standard call to check for call compatibility.
135 IF (NOT FND_API.compatible_api_call
136 (p_current_version_number => l_api_version
137 ,p_caller_version_number => p_api_version
138 ,p_api_name => l_api_name
139 ,p_pkg_name => C_DEFAULT_MODULE))
140 THEN
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
142 END IF;
143
144 -- Initialize global variables
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146
147 -----------------------------------------------------------------------------------
148 -- Validation
149 -----------------------------------------------------------------------------------
150 SELECT MAX(NVL(gl_transfer_status_code,'N')) -- N, NT, S, Y
151 INTO l_gl_transfer_status_code
152 FROM xla_ae_headers
153 WHERE application_id = p_application_id
154 AND event_id = p_event_id;
155
156 IF l_gl_transfer_status_code IS NULL THEN
157 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
158 trace(p_msg => 'No such journal entry.',
159 p_module => l_log_module,
160 p_level => C_LEVEL_STATEMENT);
161 END IF;
162 Log_error(p_module => l_log_module
163 ,p_error_msg => 'No such journal entry.');
164
165 ELSIF l_gl_transfer_status_code IN ('S','Y') THEN
166 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
167 trace(p_msg => 'l_gl_transfer_status_code='||l_gl_transfer_status_code||
168 ' Journal entries cannot be deleted because it has either been Transferred or set to Not Transferred.',
169 p_module => l_log_module,
170 p_level => C_LEVEL_STATEMENT);
171 END IF;
172 Log_error(p_module => l_log_module
173 ,p_error_msg => 'Journal entries cannot be deleted because it has either been Transferred or set to Not Transferred.');
174
175 ELSE
176
177 --------------------------------------------------------
178 -- delete all journal entries for the event
179 -- no impact on trial balance if not transferred
180 --------------------------------------------------------
181 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
182 trace(p_msg => 'Calling xla_journal_entries_pkg.delete_journal_entries.',
183 p_module => l_log_module,
184 p_level => C_LEVEL_STATEMENT);
185 END IF;
186
187 xla_journal_entries_pkg.delete_journal_entries
188 (p_application_id => p_application_id
189 ,p_event_id => p_event_id);
190
191 --------------------------------------------------------
192 -- mark event as un-processed so can be re-processed
193 --------------------------------------------------------
194 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
195 trace(p_msg => 'Update xla_events event_id'||p_event_id||' to Unprocessed.',
196 p_module => l_log_module,
197 p_level => C_LEVEL_STATEMENT);
198 END IF;
199 UPDATE XLA_EVENTS
200 SET EVENT_STATUS_CODE = xla_events_pub_pkg.C_EVENT_UNPROCESSED
201 ,PROCESS_STATUS_CODE = xla_events_pkg.C_INTERNAL_UNPROCESSED
202 WHERE application_id = p_application_id
203 AND event_id = p_event_id;
204
205
206 audit_datafix (p_application_id => p_application_id
207 ,p_event_id => p_event_id);
208
209 END IF;
210
211 FND_MSG_PUB.count_and_get(p_count => x_msg_count
212 ,p_data => x_msg_data);
213
214 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
215 trace(p_msg => 'END of procedure delete_journal_entries',
216 p_module => l_log_module,
217 p_level => C_LEVEL_PROCEDURE);
218 END IF;
219
220 EXCEPTION
221 WHEN FND_API.G_EXC_ERROR THEN
222 ROLLBACK;
223 x_return_status := FND_API.G_RET_STS_ERROR ;
224 FND_MSG_PUB.count_and_get(p_count => x_msg_count
225 ,p_data => x_msg_data);
226
227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
228 ROLLBACK;
229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
230 FND_MSG_PUB.count_and_get(p_count => x_msg_count
231 ,p_data => x_msg_data);
232
233 WHEN OTHERS THEN
234 ROLLBACK;
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
236 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
237 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
238 END IF;
239 FND_MSG_PUB.count_and_get(p_count => x_msg_count
240 ,p_data => x_msg_data);
241 END delete_journal_entries;
242
243
244
245 /*=== Logic ====================================================================
246 1) find the ae_header_id of the primary ledger (and original parent entry of
247 MPA/Accrual Reversal entry, if exists) for the original p_event_id
248 2) calls Reverse_Journal_Entry with the ae_header_id
249 a) delete the incomplete MPA
250 b) calls Create_Reversal_Entry of the ae_header_id to create the reversal of
251 the original entry, returning the new rev_ae_header_id and rev_event_id
252 i) calls Complete_Journal_Entry with rev_ae_header_id, p_event_id and
253 p_rev_flag = 'Y' to validate the reversal entry rev_ae_header_id and on
254 success,
255 -> calls Create_MRC_Reversal_Entry to create reversal of all other
256 ledgers and entries related to the original entry p_event_id.
257 c) Create a new event and entity, and map the original entry to the new
258 event id and entity id.
259 ==============================================================================*/
260 PROCEDURE reverse_journal_entries
261 (p_api_version IN NUMBER
262 ,p_init_msg_list IN VARCHAR2
263 ,p_application_id IN INTEGER
264 ,p_event_id IN INTEGER
265 ,p_reversal_method IN VARCHAR2
266 ,p_gl_date IN DATE
267 ,p_post_to_gl_flag IN VARCHAR2
268 ,x_return_status OUT NOCOPY VARCHAR2
269 ,x_msg_count OUT NOCOPY NUMBER
270 ,x_msg_data OUT NOCOPY VARCHAR2
271 ,x_rev_ae_header_id OUT NOCOPY INTEGER
272 ,x_rev_event_id OUT NOCOPY INTEGER
273 ,x_rev_entity_id OUT NOCOPY INTEGER
274 ,x_new_event_id OUT NOCOPY INTEGER
275 ,x_new_entity_id OUT NOCOPY INTEGER
276 ) IS
277
278 l_api_name CONSTANT VARCHAR2(30) := 'reverse_journal_entries';
279 l_api_version CONSTANT NUMBER := 1.0;
280
281 ---------------------------------------------------------------
282 -- in order to reverse, they must be FINAL and Transferred.
283 ---------------------------------------------------------------
284 /* Bug 7011889 - Removed ae_header_id out of this CURSOR to fetch through BULK COLLECT */
285
286 CURSOR c_orig_je IS
287 SELECT xgl.currency_code, xsu.je_source_name,
288 xah.entity_id, xah.accounting_date,
289 xah.ledger_id, e.legal_entity_id, xah.accrual_reversal_flag,
290 xe.budgetary_control_flag
291 FROM xla_gl_ledgers_v xgl
292 , xla_ae_headers xah
293 , xla_subledgers xsu
294 , xla_transaction_entities e
295 , xla_events xe
296 WHERE xgl.ledger_id = xah.ledger_id
297 AND xsu.application_id = xah.application_id
298 AND xah.event_id = p_event_id
299 AND xah.application_id = p_application_id
300 AND ledger_category_code ='PRIMARY'
301 AND e.application_id = xah.application_id
302 AND e.entity_id = xah.entity_id
303 AND xe.application_id = xah.application_id
304 AND xe.event_id = xah.event_id
305 AND xah.accounting_entry_status_code = C_STATUS_FINAL_CODE
306 AND xah.parent_ae_header_id IS NULL
307 AND NOT EXISTS (SELECT 1
308 FROM xla_ae_headers xah2
309 WHERE xah2.application_id = p_application_id
310 AND xah2.event_id = p_event_id
311 AND xah2.accounting_entry_status_code = C_STATUS_FINAL_CODE
312 AND NVL(xah2.gl_transfer_status_code,'N') IN ('N','NT')); -- can be reversed only if it is transferred
313
314 l_functional_curr xla_gl_ledgers_v.currency_code%TYPE;
315 l_je_source_name xla_subledgers.je_source_name%TYPE;
316 l_entity_id INTEGER;
317 l_pri_ae_header_id INTEGER;
318 l_pri_gl_date DATE;
319 l_ledger_id INTEGER;
320 l_legal_entity_id INTEGER;
324
321 l_mpa_acc_rev_flag VARCHAR2(1);
322 l_bc_flag VARCHAR2(1);
323 l_transfer_request_id INTEGER;
325 l_event_source_info xla_events_pub_pkg.t_event_source_info;
326 l_array_ae_header_id t_array_integer;
327
328 /* Bug 7011889 - Array to hold ae_header_ids from BULK COLLECT in case of Encumbarance events */
329 l_array_je_header_id xla_je_validation_pkg.t_array_int;
330
331 l_retcode INTEGER;
332 l_log_module VARCHAR2(240);
333 l_completion_option VARCHAR2(1);
334 l_completion_retcode VARCHAR2(30);
335
336 BEGIN
337 IF g_log_enabled THEN
338 l_log_module := C_DEFAULT_MODULE||'.reverse_journal_entries';
339 END IF;
340
341 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
342 trace(p_msg => 'BEGIN of procedure reverse_journal_entries',
343 p_module => l_log_module,
344 p_level => C_LEVEL_PROCEDURE);
345 END IF;
346
347 IF (FND_API.to_boolean(p_init_msg_list)) THEN
348 FND_MSG_PUB.initialize;
349 END IF;
350
351 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
352 trace(p_msg => 'Delete entries from xla_trial_balances',
353 p_module => l_log_module,
354 p_level => C_LEVEL_PROCEDURE);
355 END IF;
356
357 -- Standard call to check for call compatibility.
358 IF (NOT FND_API.compatible_api_call
359 (p_current_version_number => l_api_version
360 ,p_caller_version_number => p_api_version
361 ,p_api_name => l_api_name
362 ,p_pkg_name => C_DEFAULT_MODULE))
363 THEN
364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
365 END IF;
366
367 -- Initialize global variables
368 x_return_status := FND_API.G_RET_STS_SUCCESS;
369
370
371 -- Validation -------------------------------------------------------
372 /* Bug 7011889 - Modified from OPEN,FETCH to CURSOR FOR LOOP */
373
374 FOR c_orig_je_rec IN c_orig_je
375 LOOP
376 l_functional_curr := c_orig_je_rec.currency_code;
377 l_je_source_name := c_orig_je_rec.je_source_name;
378 l_entity_id := c_orig_je_rec.entity_id;
379 l_pri_gl_date := c_orig_je_rec.accounting_date;
380 l_ledger_id := c_orig_je_rec.ledger_id;
381 l_legal_entity_id := c_orig_je_rec.legal_entity_id;
382 l_mpa_acc_rev_flag := c_orig_je_rec.accrual_reversal_flag;
383 l_bc_flag := c_orig_je_rec.budgetary_control_flag;
384
385 END LOOP;
386
387 /* Bug 7011889 - Bulk collecting header ids into an array */
388
389 SELECT xah.ae_header_id BULK COLLECT INTO l_array_je_header_id
390 FROM xla_gl_ledgers_v xgl
391 , xla_ae_headers xah
392 , xla_subledgers xsu
393 , xla_transaction_entities e
394 , xla_events xe
395 WHERE xgl.ledger_id = xah.ledger_id
396 AND xsu.application_id = xah.application_id
397 AND xah.event_id = p_event_id
398 AND xah.application_id = p_application_id
399 AND ledger_category_code ='PRIMARY'
400 AND e.application_id = xah.application_id
401 AND e.entity_id = xah.entity_id
402 AND xe.application_id = xah.application_id
403 AND xe.event_id = xah.event_id
404 AND xah.accounting_entry_status_code = C_STATUS_FINAL_CODE
405 AND xah.parent_ae_header_id IS NULL
406 AND NOT EXISTS (SELECT 1
407 FROM xla_ae_headers xah2
408 WHERE xah2.application_id = p_application_id
409 AND xah2.event_id = p_event_id
410 AND xah2.accounting_entry_status_code = C_STATUS_FINAL_CODE
411 AND NVL(xah2.gl_transfer_status_code,'N') IN ('N','NT'));
412
413
414 --------------------------------------------------------------
415 -- if this is not Accrual Reversal entry, check if it is MPA
416 --------------------------------------------------------------
417 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
418 trace(p_msg => 'l_functional_curr = '||l_functional_curr,
419 p_module => l_log_module,
420 p_level => C_LEVEL_STATEMENT);
421 trace(p_msg => 'l_je_source_name = '||l_je_source_name,
422 p_module => l_log_module,
423 p_level => C_LEVEL_STATEMENT);
424 trace(p_msg => 'l_entity_id = '||l_entity_id,
425 p_module => l_log_module,
426 p_level => C_LEVEL_STATEMENT);
427 trace(p_msg => 'l_pri_gl_date = '||l_pri_gl_date,
428 p_module => l_log_module,
429 p_level => C_LEVEL_STATEMENT);
430 trace(p_msg => 'l_ledger_id = '||l_ledger_id,
431 p_module => l_log_module,
432 p_level => C_LEVEL_STATEMENT);
433 trace(p_msg => 'l_legal_entity_id = '||l_legal_entity_id,
434 p_module => l_log_module,
435 p_level => C_LEVEL_STATEMENT);
436 trace(p_msg => 'l_bc_flag = '||l_bc_flag,
437 p_module => l_log_module,
438 p_level => C_LEVEL_STATEMENT);
439 trace(p_msg => 'Accrual Reversal = '||l_mpa_acc_rev_flag,
440 p_module => l_log_module,
441 p_level => C_LEVEL_STATEMENT);
442 END IF;
443
444 /* Bug 7011889 - Writing to trace file the array of header ids through LOOP */
445
449 trace(p_msg => 'l_pri_ae_header_id = '||l_array_je_header_id(i),
446 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
447 FOR i IN l_array_je_header_id.FIRST..l_array_je_header_id.LAST
448 LOOP
450 p_module => l_log_module,
451 p_level => C_LEVEL_STATEMENT);
452 END LOOP;
453 END IF;
454
455 If l_mpa_acc_rev_flag = 'N' THEN
456
457 /* Bug 7011889 - Modified the SQL to handle multiple header ids */
458
459 SELECT MAX(NVL(MPA_ACCRUAL_ENTRY_FLAG,'N'))
460 INTO l_mpa_acc_rev_flag
461 FROM xla_ae_lines
462 WHERE application_id = p_application_id
463 AND ae_header_id in (SELECT ae_header_id
464 FROM xla_ae_headers
465 WHERE event_id = p_event_id
466 AND application_id = p_application_id);
467
468 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
469 trace(p_msg => 'MPA Accrual = '||l_mpa_acc_rev_flag,
470 p_module => l_log_module,
471 p_level => C_LEVEL_STATEMENT);
472 END IF;
473 END IF;
474
475
476 IF NVl(p_post_to_gl_flag,'N') = 'Y' THEN
477 l_completion_option := C_STATUS_POSTING_CODE; -- if previously posted, then reverse is Final and Post to GL
478 ELSE
479 l_completion_option := C_STATUS_FINAL_CODE; -- if not previously posted, then reverse is only Final
480 END IF;
481 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
482 trace(p_msg => 'l_completion_option = '||l_completion_option,
483 p_module => l_log_module,
484 p_level => C_LEVEL_STATEMENT);
485 END IF;
486
487 -------------------------------------------------------------------------
488 -- delete incomplete MPA here or later :
489 -- less work in subsequent APIs, and anyway rollback if there is error
490 -------------------------------------------------------------------------
491 IF l_mpa_acc_rev_flag = 'Y' THEN
492 FOR i in (SELECT ae_header_id
493 FROM xla_ae_headers
494 WHERE application_id = p_application_id
495 AND event_id = p_event_id
496 AND parent_ae_header_id IS NOT NULL
497 AND accounting_entry_status_code <> C_STATUS_FINAL_CODE ) LOOP
498 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
499 trace(p_msg => 'Delete journal entry = '||i.ae_header_id,
500 p_module => l_log_module,
501 p_level => C_LEVEL_STATEMENT);
502 END IF;
503 -------------------------------------------------
504 -- delete incomplete MPA/Accrual Reversal Entries
505 -------------------------------------------------
506 DELETE xla_ae_lines
507 WHERE application_id = p_application_id
508 AND ae_header_id = i.ae_header_id;
509 --
510 DELETE xla_distribution_links
511 WHERE application_id = p_application_id
512 AND ae_header_id = i.ae_header_id;
513 --
514 DELETE xla_ae_headers
515 WHERE application_id = p_application_id
516 AND ae_header_id = i.ae_header_id;
517 --
518 END LOOP;
519 END IF;
520
521 -----------------------------------------------------------------------------------
522 -- Currently, xla_journal_entries_pkg.reverse_journal_entry only process MANUAL entry
523 -----------------------------------------------------------------------------------
524 update xla_ae_headers
525 set accounting_entry_type_code = 'MANUAL'
526 where application_id = p_application_id
527 and event_id = p_event_id;
528
529 --------------------------------------------------------
530 -- reverse journal entries
531 --------------------------------------------------------
532 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
533 trace(p_msg => 'Calling xla_journal_entries_pkg.reverse_journal_entry.',
534 p_module => l_log_module,
535 p_level => C_LEVEL_STATEMENT);
536 END IF;
537
538 /* Bug 7011889 - Replace call to l_pri_ae_header_id with array of header ids for Encumbarance */
539
540 xla_journal_entries_pkg.reverse_journal_entry(
541 p_array_je_header_id => l_array_je_header_id
542 ,p_application_id => p_application_id
543 ,p_reversal_method => p_reversal_method
544 ,p_gl_date => p_gl_date
545 ,p_completion_option => l_completion_option
546 ,p_functional_curr => l_functional_curr
547 ,p_je_source_name => l_je_source_name
548 ,p_rev_header_id => x_rev_ae_header_id
549 ,p_rev_event_id => x_rev_event_id
550 ,p_completion_retcode => l_completion_retcode -- S,X
551 ,p_transfer_request_id => l_transfer_request_id
552 );
553
554 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
555 trace(p_msg => 'Returned from xla_journal_entries_pkg.reverse_journal_entry.',
556 p_module => l_log_module,
557 p_level => C_LEVEL_STATEMENT);
558 END IF;
559
560 IF l_completion_retcode <> 'S' or x_rev_ae_header_id IS NULL THEN
561 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
562 trace(p_msg => 'Failure in xla_journal_entries_pkg.reverse_journal_entry. Please verify log file.',
566 Log_error(p_module => l_log_module
563 p_module => l_log_module,
564 p_level => C_LEVEL_STATEMENT);
565 END IF;
567 ,p_error_msg => 'Failure in xla_journal_entries_pkg.reverse_journal_entry. Please verify log file.');
568 END IF;
569
570 SELECT entity_id
571 INTO x_rev_entity_id
572 FROM xla_events
573 WHERE application_id = p_application_id
574 AND event_id = x_rev_event_id
575 AND rownum = 1;
576
577 -- Bug 6964268 Begin
578 UPDATE xla_transaction_entities
579 SET (entity_code
580 , source_id_int_1
581 , source_id_char_1
582 , security_id_int_1
583 , security_id_int_2
584 , security_id_int_3
585 , security_id_char_1
586 , security_id_char_2
587 , security_id_char_3
588 , source_id_int_2
589 , source_id_char_2
590 , source_id_int_3
591 , source_id_char_3
592 , source_id_int_4
593 , source_id_char_4
594 , valuation_method
595 , source_application_id
596 , upg_batch_id
597 , upg_source_application_id
598 , upg_valid_flag
599 -- transaction_number
600 -- legal_entity_id
601 -- ledger_id
602 , creation_date
603 , created_by
604 , last_update_date
605 , last_updated_by
606 , last_update_login) = (SELECT 'MANUAL' -- entity_code This also prevents transaction to be used in bflow.
607 ,source_id_int_1
608 ,source_id_char_1
609 ,security_id_int_1
610 ,security_id_int_2
611 ,security_id_int_3
612 ,security_id_char_1
613 ,security_id_char_2
614 ,security_id_char_3
615 ,source_id_int_2
616 ,source_id_char_2
617 ,source_id_int_3
618 ,source_id_char_3
619 ,source_id_int_4
620 ,source_id_char_4
621 ,valuation_method
622 ,source_application_id
623 ,upg_batch_id
624 ,upg_source_application_id
625 ,upg_valid_flag
626 -- transaction_number
627 -- legal_entity_id
628 -- ledger_id
629 ,sysdate
630 ,fnd_global.user_id
631 ,sysdate
632 ,fnd_global.user_id
633 ,fnd_global.user_id
634 FROM xla_transaction_entities
635 WHERE application_id = p_application_id
636 AND entity_id = l_entity_id)
637 WHERE application_id = p_application_id
638 AND entity_id = x_rev_entity_id;
639
640
641 -- Bug 6964268 End
642
643 -----------------------------------------------------------------
644 -- Create new event and entity, same details as original entry
645 -----------------------------------------------------------------
646 l_event_source_info.application_id := p_application_id;
647 l_event_source_info.legal_entity_id := l_legal_entity_id;
648 l_event_source_info.ledger_id := l_ledger_id;
649 l_event_source_info.entity_type_code := 'MANUAL';
650
651 ---------------------------------------------------------------------------------------------
652 -- Currently, xla_events_pkg.validate_event_type_code failes if not MANUAL event type
653 ---------------------------------------------------------------------------------------------
654 -- Currently, xla_events_pkg.validate_event_type_code can only process MANUAL event type
655 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
656 trace(p_msg => 'Calling xla_events_pkg.create_manual_event.',
657 p_module => l_log_module,
658 p_level => C_LEVEL_STATEMENT);
659 END IF;
660 x_new_event_id := xla_events_pkg.create_manual_event
661 (p_event_source_info => l_event_source_info
662 ,p_event_type_code => 'MANUAL'
663 ,p_event_date => l_pri_gl_date
664 ,p_event_status_code => xla_events_pub_pkg.C_EVENT_UNPROCESSED
665 ,p_process_status_code => xla_events_pkg.C_INTERNAL_UNPROCESSED
666 ,p_event_number => 1
667 ,p_budgetary_control_flag => l_bc_flag);
668 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
669 trace(p_msg => 'Returned from xla_events_pkg.create_manual_event = event id '||x_new_event_id,
670 p_module => l_log_module,
671 p_level => C_LEVEL_STATEMENT);
672 END IF;
673
674 -----------------------------------------------------
675 -- update new EVENT_ID and ENTITY_ID
679 , process_status_code = xla_events_pub_pkg.C_EVENT_PROCESSED
676 -----------------------------------------------------
677 UPDATE xla_events
678 SET event_status_code = xla_events_pub_pkg.C_EVENT_PROCESSED
680 ,(event_type_code
681 , event_date
682 , reference_num_1
683 , reference_num_2
684 , reference_num_3
685 , reference_num_4
686 , reference_char_1
687 , reference_char_2
688 , reference_char_3
689 , reference_char_4
690 , reference_date_1
691 , reference_date_2
692 , reference_date_3
693 , reference_date_4
694 , on_hold_flag
695 , upg_batch_id
696 , upg_source_application_id
697 , upg_valid_flag
698 , transaction_date
699 , budgetary_control_flag
700 , merge_event_set_id
701 -- event_number
702 , creation_date
703 , created_by
704 , last_update_date
705 , last_updated_by
706 , last_update_login
707 , program_update_date
708 , program_application_id
709 , program_id
710 , request_id) = (SELECT 'MANUAL' -- event_type_code
711 , event_date
712 , reference_num_1
713 , reference_num_2
714 , reference_num_3
715 , reference_num_4
716 , reference_char_1
717 , reference_char_2
718 , reference_char_3
719 , reference_char_4
720 , reference_date_1
721 , reference_date_2
722 , reference_date_3
723 , reference_date_4
724 , on_hold_flag
725 , upg_batch_id
726 , upg_source_application_id
727 , upg_valid_flag
728 , transaction_date
729 , budgetary_control_flag
730 , merge_event_set_id
731 -- event_number
732 , sysdate
733 , fnd_global.user_id
734 , sysdate
735 , fnd_global.user_id
736 , fnd_global.user_id
737 , sysdate
738 , -1
739 , -1
740 , -1
741 FROM xla_events
742 WHERE application_id = p_application_id
743 AND event_id = p_event_id)
744 WHERE application_id = p_application_id
745 AND event_id = x_new_event_id
746 RETURNING entity_id INTO x_new_entity_id;
747
748 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
749 trace(p_msg => 'entity id = '||x_new_entity_id,
750 p_module => l_log_module,
751 p_level => C_LEVEL_STATEMENT);
752 END IF;
753
754 UPDATE xla_transaction_entities
755 SET (entity_code
756 , source_id_int_1
757 , source_id_char_1
758 , security_id_int_1
759 , security_id_int_2
760 , security_id_int_3
761 , security_id_char_1
762 , security_id_char_2
763 , security_id_char_3
764 , source_id_int_2
765 , source_id_char_2
766 , source_id_int_3
767 , source_id_char_3
768 , source_id_int_4
769 , source_id_char_4
770 , valuation_method
771 , source_application_id
772 , upg_batch_id
773 , upg_source_application_id
774 , upg_valid_flag
775 -- transaction_number
776 -- legal_entity_id
777 -- ledger_id
778 , creation_date
779 , created_by
780 , last_update_date
781 , last_updated_by
782 , last_update_login) = (SELECT 'MANUAL' -- entity_code This also prevents transaction to be used in bflow.
783 ,source_id_int_1
784 ,source_id_char_1
785 ,security_id_int_1
786 ,security_id_int_2
787 ,security_id_int_3
788 ,security_id_char_1
789 ,security_id_char_2
790 ,security_id_char_3
791 ,source_id_int_2
792 ,source_id_char_2
793 ,source_id_int_3
794 ,source_id_char_3
795 ,source_id_int_4
796 ,source_id_char_4
797 ,valuation_method
798 ,source_application_id
799 ,upg_batch_id
800 ,upg_source_application_id
801 ,upg_valid_flag
802 -- transaction_number
806 ,fnd_global.user_id
803 -- legal_entity_id
804 -- ledger_id
805 ,sysdate
807 ,sysdate
808 ,fnd_global.user_id
809 ,fnd_global.user_id
810 FROM xla_transaction_entities
811 WHERE application_id = p_application_id
812 AND entity_id = l_entity_id)
813 WHERE application_id = p_application_id
814 AND entity_id = x_new_entity_id;
815
816
817 ---------------------------------------------------------
818 -- audit original event and entries
819 ---------------------------------------------------------
820 audit_datafix (p_application_id => p_application_id
821 ,p_event_id => p_event_id
822 ,p_audit_all => 'Y');
823
824 -------------------------------------------------------------------------------
825 -- set original entries to link to new event, entity. Also update Description
826 -------------------------------------------------------------------------------
827 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
828 trace(p_msg => 'Update xla_ae_headers',
829 p_module => l_log_module,
830 p_level => C_LEVEL_STATEMENT);
831 END IF;
832 UPDATE xla_ae_headers
833 SET entity_id = x_new_entity_id
834 ,event_id = x_new_event_id
835 ,event_type_code = 'MANUAL'
836 ,description = 'Data fix entry: event_id of '||p_event_id
837 WHERE application_id = p_application_id
838 AND event_id = p_event_id
839 RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
840
841 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
842 trace(p_msg => 'Update xla_ae_lines',
843 p_module => l_log_module,
844 p_level => C_LEVEL_STATEMENT);
845 END IF;
846 FORALL i in 1..l_array_ae_header_id.COUNT
847 UPDATE xla_ae_lines
848 SET description = 'Data fix entry: event_id of '||p_event_id
849 -- business_class_code = NULL -- This is not needed to prevent use by bflow since the entity_code is now 'MANUAL'
850 WHERE application_id = p_application_id
851 AND ae_header_id = l_array_ae_header_id(i);
852
853 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
854 trace(p_msg => 'Update xla_distribution_links',
855 p_module => l_log_module,
856 p_level => C_LEVEL_STATEMENT);
857 END IF;
858 FORALL i in 1..l_array_ae_header_id.COUNT
859 UPDATE xla_distribution_links
860 SET event_id = x_new_event_id
861 WHERE application_id = p_application_id
862 AND ae_header_id = l_array_ae_header_id(i);
863
864
865 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
866 trace(p_msg => 'Update xla_events',
867 p_module => l_log_module,
868 p_level => C_LEVEL_STATEMENT);
869 END IF;
870 ---------------------------------------------------------
871 -- set original event to Unprocessed
872 ---------------------------------------------------------
873 UPDATE XLA_EVENTS
874 SET EVENT_STATUS_CODE = xla_events_pub_pkg.C_EVENT_UNPROCESSED
875 ,PROCESS_STATUS_CODE = xla_events_pkg.C_INTERNAL_UNPROCESSED
876 WHERE application_id = p_application_id
877 AND event_id = p_event_id;
878
879 -------------------------------------------------------------------------------
880 -- update Description for reverse entries
881 -------------------------------------------------------------------------------
882 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
883 trace(p_msg => 'Update descriptions',
884 p_module => l_log_module,
885 p_level => C_LEVEL_STATEMENT);
886 END IF;
887 UPDATE xla_ae_headers
888 SET description = 'Data fix reversal entry: event_id of '||x_new_event_id
889 WHERE application_id = p_application_id
890 AND event_id = x_rev_event_id
891 RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
892
893 FORALL i in 1..l_array_ae_header_id.COUNT
894 UPDATE xla_ae_lines
895 SET description = 'Data fix reversal entry: event_id of '||x_new_event_id
896 WHERE application_id = p_application_id
897 AND ae_header_id = l_array_ae_header_id(i);
898
899 ----------------------------------------------------------
900 -- audit reversed event
901 ----------------------------------------------------------
902 audit_datafix (p_application_id => p_application_id
903 ,p_event_id => x_rev_event_id
904 ,p_audit_all => 'Y');
905
906
907 FND_MSG_PUB.count_and_get(p_count => x_msg_count
908 ,p_data => x_msg_data);
909
910 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
911 trace(p_msg => 'END of procedure reverse_journal_entries',
912 p_module => l_log_module,
913 p_level => C_LEVEL_PROCEDURE);
914 END IF;
915
916 EXCEPTION
917 WHEN FND_API.G_EXC_ERROR THEN
918 ROLLBACK;
919 x_return_status := FND_API.G_RET_STS_ERROR ;
923 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
920 FND_MSG_PUB.count_and_get(p_count => x_msg_count
921 ,p_data => x_msg_data);
922
924 ROLLBACK;
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
926 FND_MSG_PUB.count_and_get(p_count => x_msg_count
927 ,p_data => x_msg_data);
928
929 WHEN OTHERS THEN
930 ROLLBACK;
931 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
932 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
933 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
934 END IF;
935 FND_MSG_PUB.count_and_get(p_count => x_msg_count
936 ,p_data => x_msg_data);
937 END reverse_journal_entries;
938
939
940
941 --=============================================================================
942 --
943 --
944 --
945 --=============================================================================
946 PROCEDURE redo_accounting
947 (p_api_version IN NUMBER
948 ,p_init_msg_list IN VARCHAR2
949 ,p_application_id IN INTEGER
950 ,p_event_id IN INTEGER
951 ,p_gl_posting_flag IN VARCHAR2
952 ,x_return_status OUT NOCOPY VARCHAR2
953 ,x_msg_count OUT NOCOPY NUMBER
954 ,x_msg_data OUT NOCOPY VARCHAR2
955 ) IS
956 l_api_name CONSTANT VARCHAR2(30) := 'redo_accounting';
957 l_api_version CONSTANT NUMBER := 1.0;
958
959 l_errbuf VARCHAR2(240);
960 l_retcode INTEGER;
961 l_log_module VARCHAR2(240);
962 l_dummy INTEGER;
963 l_accounting_mode VARCHAR2(30);
964 l_process_status VARCHAR2(1);
965 l_batch_id INTEGER;
966
967 BEGIN
968 IF g_log_enabled THEN
969 l_log_module := C_DEFAULT_MODULE||'.redo_accounting';
970 END IF;
971
972 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
973 trace(p_msg => 'BEGIN of procedure redo_accounting',
974 p_module => l_log_module,
975 p_level => C_LEVEL_PROCEDURE);
976 END IF;
977
978 IF (FND_API.to_boolean(p_init_msg_list)) THEN
979 FND_MSG_PUB.initialize;
980 END IF;
981
982 -- Standard call to check for call compatibility.
983 IF (NOT FND_API.compatible_api_call
984 (p_current_version_number => l_api_version
985 ,p_caller_version_number => p_api_version
986 ,p_api_name => l_api_name
987 ,p_pkg_name => C_DEFAULT_MODULE))
988 THEN
989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
990 END IF;
991
992 -- Initialize global variables
993 x_return_status := FND_API.G_RET_STS_SUCCESS;
994
995 -- Validation ------------------------------------------------------------------------------------------
996 SELECT DECODE(NVL(budgetary_control_flag,'N'),'Y', C_STATUS_FUNDS_RESERVE, C_STATUS_FINAL),process_status_code
997 INTO l_accounting_mode, l_process_status
998 FROM xla_events
999 WHERE application_id = p_application_id
1000 AND event_id = p_event_id;
1001
1002 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1003 trace(p_msg => 'l_accounting_mode='||l_accounting_mode||', l_process_status='||l_process_status,
1004 p_module => l_log_module,
1005 p_level => C_LEVEL_STATEMENT);
1006 END IF;
1007 IF l_process_status <> 'U' THEN
1008 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1009 trace(p_msg => 'No such event or event has been processed. Please verify.',
1010 p_module => l_log_module,
1011 p_level => C_LEVEL_STATEMENT);
1012 END IF;
1013 Log_error(p_module => l_log_module
1014 ,p_error_msg => 'No such event or event has been processed. Please verify.');
1015 END IF;
1016 --------------------------------------------------------------------------------------------------------
1017
1018 ---------------------------------------------------------
1019 -- populate a row to be used by accounting_program_events
1020 ---------------------------------------------------------
1021 INSERT INTO xla_acct_prog_events_gt (event_id, ledger_id)
1022 VALUES (p_event_id, null);
1023 --
1024 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1025 trace(p_msg => 'Calling xla_accounting_pub_pkg.accounting_program_events.',
1026 p_module => l_log_module,
1027 p_level => C_LEVEL_STATEMENT);
1028 END IF;
1029 xla_accounting_pub_pkg.accounting_program_events
1030 (p_application_id => p_application_id
1031 ,p_accounting_mode => l_accounting_mode
1032 ,p_gl_posting_flag => p_gl_posting_flag
1033 ,p_accounting_batch_id => l_batch_id
1034 ,p_errbuf => l_errbuf
1035 ,p_retcode => l_retcode);
1036 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1037 trace(p_msg => 'Returned from xla_accounting_pub_pkg.accounting_program_events.',
1038 p_module => l_log_module,
1039 p_level => C_LEVEL_STATEMENT);
1040 END IF;
1041
1042 ----------------------------------------------------------------------------------------------
1043 -- when BC event failed, l_retcode is still 0, do this check to make sure vent is procesed.
1047 FROM xla_events
1044 ----------------------------------------------------------------------------------------------
1045 SELECT process_status_code
1046 INTO l_process_status
1048 WHERE application_id = p_application_id
1049 AND event_id = p_event_id;
1050
1051 IF l_retcode = 0 AND l_process_status = 'P' THEN
1052
1053 audit_datafix (p_application_id => p_application_id
1054 ,p_event_id => p_event_id
1055 ,p_audit_all => 'Y');
1056
1057 ELSE
1058 Log_error(p_module => l_log_module
1059 ,p_error_msg => 'Error in redo accounting. Please check the log file.');
1060 END IF;
1061
1062 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1063 ,p_data => x_msg_data);
1064
1065 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1066 trace(p_msg => 'END of procedure redo_accounting',
1067 p_module => l_log_module,
1068 p_level => C_LEVEL_PROCEDURE);
1069 END IF;
1070
1071 EXCEPTION
1072 WHEN FND_API.G_EXC_ERROR THEN
1073 ROLLBACK;
1074 x_return_status := FND_API.G_RET_STS_ERROR ;
1075 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1076 ,p_data => x_msg_data);
1077
1078 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1079 ROLLBACK;
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1081 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1082 ,p_data => x_msg_data);
1083
1084
1085 WHEN OTHERS THEN
1086 ROLLBACK;
1087 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1088 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1089 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
1090 END IF;
1091 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1092 ,p_data => x_msg_data);
1093 END redo_accounting;
1094
1095
1096
1097 --=============================================================================
1098 --
1099 --
1100 --
1101 --=============================================================================
1102 PROCEDURE do_not_transfer_je
1103 (p_api_version IN NUMBER
1104 ,p_init_msg_list IN VARCHAR2
1105 ,p_application_id IN INTEGER
1106 ,p_ae_header_id IN INTEGER
1107 ,x_return_status OUT NOCOPY VARCHAR2
1108 ,x_msg_count OUT NOCOPY NUMBER
1109 ,x_msg_data OUT NOCOPY VARCHAR2
1110 ) IS
1111 l_api_name CONSTANT VARCHAR2(30) := 'do_not_transfer_je';
1112 l_api_version CONSTANT NUMBER := 1.0;
1113
1114 l_retcode INTEGER;
1115 l_log_module VARCHAR2(240);
1116 l_dummy NUMBER;
1117
1118 BEGIN
1119 IF g_log_enabled THEN
1120 l_log_module := C_DEFAULT_MODULE||'.do_not_transfer_je';
1121 END IF;
1122
1123 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1124 trace(p_msg => 'BEGIN of procedure do_not_transfer_je',
1125 p_module => l_log_module,
1126 p_level => C_LEVEL_PROCEDURE);
1127 END IF;
1128
1129 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1130 FND_MSG_PUB.initialize;
1131 END IF;
1132
1133 -- Standard call to check for call compatibility.
1134 IF (NOT FND_API.compatible_api_call
1135 (p_current_version_number => l_api_version
1136 ,p_caller_version_number => p_api_version
1137 ,p_api_name => l_api_name
1138 ,p_pkg_name => C_DEFAULT_MODULE))
1139 THEN
1140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1141 END IF;
1142
1143 -- Initialize global variables
1144 x_return_status := FND_API.G_RET_STS_SUCCESS;
1145
1146 UPDATE xla_ae_headers
1147 SET gl_transfer_status_code = 'NT'
1148 WHERE application_id = p_application_id
1149 AND ae_header_id = p_ae_header_id
1150 AND accounting_entry_status_code = C_STATUS_FINAL_CODE
1151 AND gl_transfer_status_code = 'N'; -- if already transferred, S or Y, then do not set to NT.
1152
1153 l_dummy := SQL%ROWCOUNT;
1154 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1155 trace(p_msg => 'Rows updated = '||l_dummy,
1156 p_module => l_log_module,
1157 p_level => C_LEVEL_STATEMENT);
1158 END IF;
1159
1160 IF l_dummy = 0 THEN
1161 Log_error(p_module => l_log_module
1162 ,p_error_msg => 'No such entry, or the entry is not in Final mode or it has been transferred. Please verify.');
1163 END IF;
1164
1165 audit_datafix (p_application_id => p_application_id
1166 ,p_ae_header_id => p_ae_header_id);
1167
1168 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1169 ,p_data => x_msg_data);
1170
1171 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1172 trace(p_msg => 'END of procedure do_not_transfer_je',
1173 p_module => l_log_module,
1174 p_level => C_LEVEL_PROCEDURE);
1175 END IF;
1176
1177 EXCEPTION
1178 WHEN FND_API.G_EXC_ERROR THEN
1179 ROLLBACK;
1180 x_return_status := FND_API.G_RET_STS_ERROR ;
1181 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1182 ,p_data => x_msg_data);
1183
1184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1185 ROLLBACK;
1186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1187 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1188 ,p_data => x_msg_data);
1189
1190 WHEN OTHERS THEN
1191 ROLLBACK;
1192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1193 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1194 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
1195 END IF;
1196 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1197 ,p_data => x_msg_data);
1198 END do_not_transfer_je;
1199
1200
1201 --=============================================================================
1202 --
1203 --
1204 --
1205 --=============================================================================
1206 PROCEDURE validate_journal_entry
1207 (p_api_version IN NUMBER
1208 ,p_init_msg_list IN VARCHAR2
1209 ,p_application_id IN INTEGER
1210 ,p_ae_header_id IN INTEGER
1211 ,x_return_status OUT NOCOPY VARCHAR2
1212 ,x_msg_count OUT NOCOPY NUMBER
1213 ,x_msg_data OUT NOCOPY VARCHAR2
1214 ) IS
1215 l_api_name CONSTANT VARCHAR2(30) := 'validate_journal_entry';
1216 l_api_version CONSTANT NUMBER := 1.0;
1217
1218 l_retcode INTEGER;
1219 l_log_module VARCHAR2(240);
1220 l_dummy INTEGER;
1221
1222 BEGIN
1223 IF g_log_enabled THEN
1224 l_log_module := C_DEFAULT_MODULE||'.validate_journal_entry';
1225 END IF;
1226
1227 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1228 trace(p_msg => 'BEGIN of procedure validate_journal_entry',
1229 p_module => l_log_module,
1230 p_level => C_LEVEL_PROCEDURE);
1231 END IF;
1232
1233 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1234 FND_MSG_PUB.initialize;
1235 END IF;
1236
1237 -- Standard call to check for call compatibility.
1238 IF (NOT FND_API.compatible_api_call
1239 (p_current_version_number => l_api_version
1240 ,p_caller_version_number => p_api_version
1241 ,p_api_name => l_api_name
1242 ,p_pkg_name => C_DEFAULT_MODULE))
1243 THEN
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1245 END IF;
1246
1247 -- Initialize global variables
1248 x_return_status := FND_API.G_RET_STS_SUCCESS;
1249
1250 SELECT count(*)
1251 INTO l_dummy
1252 FROM xla_ae_headers
1253 WHERE application_id = p_application_id
1254 AND ae_header_id = p_ae_header_id
1255 AND accounting_entry_status_code = C_STATUS_FINAL_CODE;
1256
1257 IF l_dummy = 0 THEN
1258 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1259 trace(p_msg => 'No such entry or it is not in Final mode.',
1260 p_module => l_log_module,
1261 p_level => C_LEVEL_STATEMENT);
1262 END IF;
1263 Log_error(p_module => l_log_module
1264 ,p_error_msg => 'No such entry or it is not in Final mode.');
1265 END IF;
1266
1267 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1268 trace(p_msg => 'Calling XLA_UPGRADE_PUB.Validate_Header_Line_Entries.',
1269 p_module => l_log_module,
1270 p_level => C_LEVEL_STATEMENT);
1271 END IF;
1272 XLA_UPGRADE_PUB.Validate_Header_Line_Entries (
1273 p_application_id => p_application_id
1277 p_module => l_log_module,
1274 ,p_header_id => p_ae_header_id);
1275 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1276 trace(p_msg => 'Returned from XLA_UPGRADE_PUB.Validate_Header_Line_Entries.',
1278 p_level => C_LEVEL_STATEMENT);
1279 END IF;
1280
1281
1282 FOR i IN (SELECT error_message_name
1283 FROM xla_upg_errors
1284 WHERE application_id = p_application_id
1285 AND ae_header_id = p_ae_header_id) LOOP
1286 Log_error(p_error_name => i.ERROR_MESSAGE_NAME);
1287 END LOOP;
1288
1289 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1290 ,p_data => x_msg_data);
1291
1292 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1293 trace(p_msg => 'END of procedure validate_journal_entry',
1294 p_module => l_log_module,
1295 p_level => C_LEVEL_PROCEDURE);
1296 END IF;
1297
1298 EXCEPTION
1299 WHEN FND_API.G_EXC_ERROR THEN
1300 ROLLBACK;
1301 x_return_status := FND_API.G_RET_STS_ERROR ;
1302 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1303 ,p_data => x_msg_data);
1304
1305 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1306 ROLLBACK;
1307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1308 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1309 ,p_data => x_msg_data);
1310
1311 WHEN OTHERS THEN
1312 ROLLBACK;
1313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1314 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1315 FND_MSG_PUB.add_exc_msg(C_DEFAULT_MODULE, l_api_name);
1316 END IF;
1317 FND_MSG_PUB.count_and_get(p_count => x_msg_count
1318 ,p_data => x_msg_data);
1319 END validate_journal_entry;
1320
1321
1322
1323 --=============================================================================
1324 --
1325 --
1326 --
1327 --=============================================================================
1328 PROCEDURE audit_datafix
1329 (p_application_id IN INTEGER
1330 ,p_ae_header_id IN INTEGER DEFAULT NULL
1331 ,p_ae_line_num IN INTEGER DEFAULT NULL
1332 ,p_event_id IN INTEGER DEFAULT NULL
1333 ,p_audit_all IN VARCHAR2 DEFAULT 'N'
1334 ) IS
1335
1336 l_log_module VARCHAR2(240);
1337 l_array_ae_header_id t_array_integer;
1338
1339 BEGIN
1340
1341 IF g_log_enabled THEN
1342 l_log_module := C_DEFAULT_MODULE||'.audit_datafix';
1343 END IF;
1344
1345 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1346 trace(p_msg => 'BEGIN of procedure audit_datafix',
1347 p_module => l_log_module,
1348 p_level => C_LEVEL_PROCEDURE);
1349 END IF;
1350
1351 -----------------------------------------------------
1352 -- audit xla_ae_headers
1353 -----------------------------------------------------
1354 IF p_ae_header_id IS NOT NULL THEN
1355 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1356 trace(p_msg => 'Audit xla_ae_headers.',
1357 p_module => l_log_module,
1358 p_level => C_LEVEL_STATEMENT);
1359 END IF;
1360 UPDATE XLA_AE_HEADERS
1361 SET LAST_UPDATE_DATE = sysdate
1362 ,UPG_BATCH_ID = -9999
1363 WHERE application_id = p_application_id
1364 AND ae_header_id = p_ae_header_id;
1365 END IF;
1366
1367 -----------------------------------------------------
1368 -- audit xla_ae_lines
1369 -----------------------------------------------------
1370 IF p_ae_line_num IS NOT NULL THEN
1371 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1372 trace(p_msg => 'Audit xla_ae_lines.',
1373 p_module => l_log_module,
1374 p_level => C_LEVEL_STATEMENT);
1375 END IF;
1376 UPDATE XLA_AE_LINES
1377 SET LAST_UPDATE_DATE = sysdate
1378 ,UPG_BATCH_ID = -9999
1379 WHERE application_id = p_application_id
1380 AND ae_header_id = p_ae_header_id
1381 AND ae_line_num = p_ae_line_num;
1382 END IF;
1383
1384 -----------------------------------------------------
1385 -- audit xla_events and all related entries
1386 -----------------------------------------------------
1387 IF p_event_id IS NOT NULL THEN
1388 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1389 trace(p_msg => 'Audit xla_events.',
1390 p_module => l_log_module,
1391 p_level => C_LEVEL_STATEMENT);
1392 END IF;
1393 UPDATE XLA_EVENTS
1394 SET LAST_UPDATE_DATE = sysdate
1395 ,UPG_BATCH_ID = -9999
1396 WHERE application_id = p_application_id
1397 AND event_id = p_event_id;
1398
1399 IF p_audit_all = 'Y' THEN
1400 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1401 trace(p_msg => 'Audit all details of xla_events.',
1402 p_module => l_log_module,
1403 p_level => C_LEVEL_STATEMENT);
1404 END IF;
1405 UPDATE XLA_AE_HEADERS
1406 SET LAST_UPDATE_DATE = sysdate
1407 ,UPG_BATCH_ID = -9999
1408 WHERE application_id = p_application_id
1409 AND event_id = p_event_id
1410 RETURNING ae_header_id BULK COLLECT INTO l_array_ae_header_id;
1411
1412 FORALL i in 1..l_array_ae_header_id.COUNT
1413 UPDATE XLA_AE_LINES
1414 SET LAST_UPDATE_DATE = sysdate
1415 ,UPG_BATCH_ID = -9999
1416 WHERE application_id = p_application_id
1417 AND ae_header_id = l_array_ae_header_id(i);
1418 END IF;
1419
1420 END IF;
1421
1422 END audit_datafix;
1423
1424
1425
1426 --=============================================================================
1427 --
1428 --
1429 --
1430 --=============================================================================
1431 -- Currently there is no token param needed, but can be enhanced if necessary.
1432 PROCEDURE log_error
1433 (p_module IN VARCHAR2 DEFAULT NULL
1434 ,p_error_msg IN VARCHAR2 DEFAULT NULL
1435 ,p_error_name IN VARCHAR2 DEFAULT NULL
1436 ) IS
1437
1438 BEGIN
1439
1440 IF p_error_name IS NULL THEN
1441 -- An internal error occurred. Please inform your system administrator or
1442 -- support representative that:
1443 -- An internal error has occurred in the program LOCATION. ERROR.
1444 --
1445 Xla_exceptions_pkg.raise_message
1446 (p_appli_s_name => 'XLA'
1447 ,p_msg_name => 'XLA_COMMON_ERROR'
1448 ,p_token_1 => 'LOCATION'
1449 ,p_value_1 => p_module
1450 ,p_token_2 => 'ERROR'
1451 ,p_value_2 => p_error_msg
1452 ,p_msg_mode => g_msg_mode);
1453
1454 ELSE
1455 Xla_exceptions_pkg.raise_message
1456 (p_appli_s_name => 'XLA'
1457 ,p_msg_name => p_error_name
1458 ,p_msg_mode => g_msg_mode);
1459
1460 END IF;
1461
1462 Raise FND_API.G_EXC_ERROR;
1463
1464 END log_error;
1465
1466
1467 --=============================================================================
1468 --
1469 -- Following code is executed when the package body is referenced for the first
1470 -- time
1471 --
1472 --=============================================================================
1473 BEGIN
1474
1475 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1476 g_log_enabled := fnd_log.test
1477 (log_level => g_log_level
1478 ,module => C_DEFAULT_MODULE);
1479
1480 IF NOT g_log_enabled THEN
1481 g_log_level := C_LEVEL_LOG_DISABLED;
1482 END IF;
1483
1484 END xla_datafixes_pub;