[Home] [Help]
PACKAGE BODY: APPS.XLA_REVERSE_EVENTS_PVT_PKG
Source
1 PACKAGE BODY xla_reverse_events_pvt_pkg AS
2 -- $Header: xlavrevevt.pkb 120.7 2011/03/11 13:46:01 vgopiset noship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | FILENAME |
9 | xlavrevevt.pkb |
10 | |
11 | PACKAGE NAME |
12 | xla_reverse_events_pvt_pkg |
13 | |
14 | DESCRIPTION |
15 | This is a XLA private package, which contains all the APIs |
16 | required for processing BULK Reversal of ACCOUNTED Events. |
17 | |
18 | |
19 | HISTORY |
20 | VGOPISET 10-NOV-2010 Created. |
21 | VGOPISET 01-MAR-2010 Added the VM Ledger |
22 | VGOPISET 08-MAR-2011 Changes for Batch wise processing even for|
23 | Delete_Incomplete_Reversal |
24 +===========================================================================*/
25
26 --=============================================================================
27 -- *********** Local Trace Routine **********
28 --=============================================================================
29 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
30 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
31 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
32 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
33 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
34 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
35
36 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
37
38 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_reverse_events_pvt_pkg';
39 C_REVERSAL_SWITCH_DR_CR VARCHAR2(30) := 'SIDE' ;
40 C_TYPE_MANUAL VARCHAR2(6) := 'MANUAL' ;
41
42 TYPE t_event_data IS RECORD (
43 entity_id XLA_AE_JOURNAL_ENTRY_PKG.t_array_Num
44 ,application_id XLA_AE_JOURNAL_ENTRY_PKG.t_array_Num
45 ,ledger_id XLA_AE_JOURNAL_ENTRY_PKG.t_array_Num
46 ,entity_code XLA_AE_JOURNAL_ENTRY_PKG.t_array_V30L
47 ,event_id XLA_AE_JOURNAL_ENTRY_PKG.t_array_Num
48 ,event_number XLA_AE_JOURNAL_ENTRY_PKG.t_array_Num
49 ,event_date XLA_AE_JOURNAL_ENTRY_PKG.t_array_DATE
50 ,transaction_date XLA_AE_JOURNAL_ENTRY_PKG.t_array_DATE
51 ,event_status_code XLA_AE_JOURNAL_ENTRY_PKG.t_array_V1L
52 ,process_status_code XLA_AE_JOURNAL_ENTRY_PKG.t_array_V1L
53 ,budgetary_control_flag XLA_AE_JOURNAL_ENTRY_PKG.t_array_V1L
54 ,reference_num_1 XLA_AE_JOURNAL_ENTRY_PKG.t_array_Num
55 ,reference_num_2 XLA_AE_JOURNAL_ENTRY_PKG.t_array_Num
56 ,reference_char_1 XLA_AE_JOURNAL_ENTRY_PKG.t_array_V1L
57 ,reference_char_2 XLA_AE_JOURNAL_ENTRY_PKG.t_array_V240L
58 );
59
60
61 --
62 g_log_level NUMBER;
63 g_log_enabled BOOLEAN;
64 g_application_id NUMBER;
65 g_batch_code VARCHAR2(30);
66 g_ledger_id NUMBER ;
67 g_gl_transfer_flag VARCHAR2(1);
68 g_batch_commit_size NUMBER;
69 g_accounting_batch_id NUMBER;
70 g_manual_entity_id NUMBER;
71 g_last_updated_by NUMBER;
72 g_last_update_login NUMBER;
73 g_error_source VARCHAR2(240);
74 g_event_number NUMBER ;
75 g_initialized VARCHAR2(1) ;
76 -------------------------------------------------------------------------------
77 -- Private Procedures
78 -------------------------------------------------------------------------------
79 --+==========================================================================+
80 --| PRIVATE PROCEDURE |
81 --| Trace |
82 --| Local Procedure to obtain the FND Debug Logs |
83 --+==========================================================================+
84 PROCEDURE trace
85 (p_msg IN VARCHAR2
86 ,p_level IN NUMBER
87 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
88 BEGIN
89
90 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
91 fnd_log.message(p_level, p_module);
92 ELSIF p_level >= g_log_level THEN
93 fnd_log.string(p_level, p_module, p_msg);
94 END IF;
95
96 EXCEPTION
97 WHEN xla_exceptions_pkg.application_exception THEN
98 RAISE;
99 WHEN OTHERS THEN
100 xla_exceptions_pkg.raise_message
101 (p_location => 'xla_reverse_events_pvt_pkg.trace');
102 END trace;
103
104 --+==========================================================================+
105 --| PRIVATE PROCEDURE |
106 --| Create_Manual_Entity |
107 --| Local Procedure to Create a MANUAL Entity for the list of events |
108 --| getting processed in the given run of Reversal Accounting. |
109 --| TRANSACTION_NUMBER in ENTITIES Table contains the BATCH_CODE. |
110 --+==========================================================================+
111 PROCEDURE create_manual_entity
112 IS
113 l_log_module VARCHAR2(240);
114
115 BEGIN
116
117 IF g_log_enabled THEN
118 l_log_module := C_DEFAULT_MODULE||'.create_manual_entity' ;
119 END IF;
120
121 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
122 trace
123 (p_msg => 'BEGIN of procedure create_manual_entity '
124 ,p_level => C_LEVEL_PROCEDURE
125 ,p_module =>l_log_module);
126
127 END IF;
128 INSERT INTO xla_transaction_entities
129 (entity_id
130 ,application_id
131 ,source_application_id
132 ,ledger_id
133 ,legal_entity_id
134 ,entity_code
135 ,transaction_number
136 ,creation_date
137 ,created_by
138 ,last_update_date
139 ,last_updated_by
140 ,last_update_login
141 ,valuation_method
142 ,security_id_int_1
143 ,security_id_int_2
144 ,security_id_int_3
145 ,security_id_char_1
146 ,security_id_char_2
147 ,security_id_char_3
148 ,source_id_int_1
149 ,source_id_int_2
150 ,source_id_int_3
151 ,source_id_int_4
152 ,source_id_char_1
153 ,source_id_char_2
154 ,source_id_char_3
155 ,source_id_char_4)
156 VALUES
157 (XLA_TRANSACTION_ENTITIES_S.NEXTVAL
158 ,g_application_id
159 ,g_application_id
160 ,g_ledger_id
161 ,NULL
162 ,C_TYPE_MANUAL
163 ,g_batch_code
164 ,sysdate
165 ,g_last_updated_by -- xla_environment_pkg.g_usr_id
166 ,sysdate
167 ,g_last_updated_by -- xla_environment_pkg.g_usr_id
168 ,g_last_update_login -- xla_environment_pkg.g_login_id
169 ,NULL
170 ,NULL
171 ,NULL
172 ,NULL
173 ,NULL
174 ,NULL
175 ,NULL
176 ,g_accounting_batch_id
177 ,NULL
178 ,NULL
179 ,NULL
180 ,NULL
181 ,NULL
182 ,NULL
183 ,NULL )
184 RETURNING entity_id INTO g_manual_entity_id ;
185
186 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
187 trace
188 (p_msg => 'Manual Entity ID: '|| g_manual_entity_id
189 ,p_level => C_LEVEL_PROCEDURE
190 ,p_module =>l_log_module);
191
192 END IF;
193
194 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
195 trace
196 (p_msg => 'END of procedure create_manual_entity '
197 ,p_level => C_LEVEL_PROCEDURE
198 ,p_module =>l_log_module);
199
200 END IF;
201
202 EXCEPTION
203 WHEN xla_exceptions_pkg.application_exception THEN
204 RAISE;
205 WHEN OTHERS THEN
206 xla_exceptions_pkg.raise_message
207 (p_location => 'xla_reverse_events_pvt_pkg.create_manual_entity');
208
209 END create_manual_entity;
210
211 --+==========================================================================+
212 --| PRIVATE PROCEDURE |
213 --| Delete_Incomplete_Journals |
214 --| Local Procedure to DELETE the Invalid Accounting for REVERSAL EVENTS |
215 --| that got created in the earlier run. |
216 --| Note: REFERENCE_NUM_2 contains the REVERSE_EVENT_ID when prior run had |
217 --| failed with invalid accounting. |
218 --+==========================================================================+
219 PROCEDURE delete_incomplete_journals
220 IS
221 l_log_module VARCHAR2(240);
222 BEGIN
223
224 IF g_log_enabled THEN
225 l_log_module := C_DEFAULT_MODULE||'.delete_incomplete_journals';
226 END IF;
227
228 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
229 trace
230 (p_msg => 'BEGIN of procedure delete_incomplete_journals '
231 ,p_level => C_LEVEL_PROCEDURE
232 ,p_module =>l_log_module);
233
234 END IF;
235
236 -- Delete from xla_accounting_errors
237 DELETE FROM xla_accounting_errors
238 WHERE event_id IN
239 ( SELECT reference_num_2 FROM xla_events_gt
240 WHERE reference_char_1 = 'E'
241 AND reference_num_2 IS NOT NULL );
242
243 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
244 trace
245 (p_msg => 'Number of errors deleted = '||SQL%ROWCOUNT
246 ,p_level => C_LEVEL_STATEMENT
247 ,p_module => l_log_module);
248 END IF;
249
250 -- Delete from xla_distribution_links
251 DELETE FROM xla_distribution_links
252 WHERE ae_header_id IN
253 (SELECT /*+ leading(XE) use_nl(XH) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
254 FROM xla_events_gt xe,
255 xla_ae_headers xh
256 WHERE xe.reference_char_1 = 'E'
257 AND xh.application_id = xe.application_id
258 AND xh.event_id = xe.reference_num_2
259 AND xh.accounting_entry_status_code <> 'F'
260 AND xe.reference_num_2 IS NOT NULL
261 AND xh.application_id = g_application_id
262 )
263 AND application_id = g_application_id;
264
265 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
266 trace
267 (p_msg => 'Number of distribution links deleted = '||SQL%ROWCOUNT
268 ,p_level => C_LEVEL_STATEMENT
269 ,p_module => l_log_module);
270 END IF;
271
272 -- Delete from xla_ae_segment_values
273 DELETE /*+ index(XLA_AE_SEGMENT_VALUES, XLA_AE_SEGMENT_VALUES_U1) */
274 FROM xla_ae_segment_values
275 WHERE ae_header_id IN
276 (SELECT /*+ leading(XE) use_nl(XH) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
277 FROM xla_events_gt xe,
278 xla_ae_headers xh
279 WHERE xe.reference_char_1 = 'E'
280 AND xh.application_id = xe.application_id
281 AND xh.event_id = xe.reference_num_2
282 AND xh.accounting_entry_status_code <> 'F'
283 AND xe.reference_num_2 IS NOT NULL
284 AND xh.application_id = g_application_id
285 );
286
287 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
288 trace
289 (p_msg => 'Number of segment values deleted = '||SQL%ROWCOUNT
290 ,p_level => C_LEVEL_STATEMENT
291 ,p_module => l_log_module);
292 END IF;
293
294 -- Delete from xla_ae_line_acs
295 DELETE /*+ index(xal_acs,XLA_AE_LINE_ACS_U1) */
296 FROM xla_ae_line_acs xal_acs
297 WHERE xal_acs.ae_header_id IN
298 (SELECT/*+ leading(evt) use_nl(aeh) unnest index(aeh,XLA_AE_HEADERS_N2) */ aeh.ae_header_id
299 FROM xla_events_gt evt
300 ,xla_ae_headers aeh
301 WHERE evt.reference_char_1 = 'E'
302 AND aeh.application_id = evt.application_id
303 AND aeh.event_id = evt.reference_num_2
304 AND aeh.accounting_entry_status_code <> 'F'
305 AND evt.reference_num_2 IS NOT NULL
306 AND aeh.application_id = g_application_id
307 );
308
309 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
310 trace
311 (p_msg => 'Number of line acs deleted = '||SQL%ROWCOUNT
312 ,p_level => C_LEVEL_STATEMENT
313 ,p_module => l_log_module);
314 END IF;
315
316 -- Delete from xla_ae_header_acs
317 DELETE FROM xla_ae_header_acs
318 WHERE ae_header_id IN
319 (SELECT /*+ leading(evt) use_nl(aeh) unnest index(aeh,XLA_AE_HEADERS_N2) */ aeh.ae_header_id
320 FROM xla_events_gt evt
321 ,xla_ae_headers aeh
322 WHERE evt.reference_char_1 = 'E'
323 AND aeh.application_id = evt.application_id
324 AND aeh.event_id = evt.reference_num_2
325 AND aeh.accounting_entry_status_code <> 'F'
326 AND evt.reference_num_2 IS NOT NULL
327 AND aeh.application_id = g_application_id
328 );
329
330 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
331 trace
332 (p_msg => 'Number of header acs deleted = '||SQL%ROWCOUNT
333 ,p_level => C_LEVEL_STATEMENT
334 ,p_module => l_log_module);
335 END IF;
336
337 -- Delete from xla_ae_lines
338 DELETE FROM xla_ae_lines
339 WHERE application_id = g_application_id
340 AND ae_header_id IN
341 ( SELECT /*+ leading(xe) use_nl(xh) unnest index(xh,XLA_AE_HEADERS_N2) */ xh.ae_header_id
342 FROM xla_events_gt xe,
343 xla_ae_headers xh
344 WHERE xe.reference_char_1 = 'E'
345 AND xh.application_id = xe.application_id
346 AND xh.event_id = xe.reference_num_2
347 AND xh.accounting_entry_status_code <> 'F'
348 AND xe.reference_num_2 IS NOT NULL
349 AND xh.application_id = g_application_id
350 );
351
352 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
353 trace
354 (p_msg => 'Number of ae lines deleted = '||SQL%ROWCOUNT
355 ,p_level => C_LEVEL_STATEMENT
356 ,p_module => l_log_module);
357 END IF;
358
359 -- Delete from xla_ae_headers
360 DELETE /*+ index(aeh, xla_ae_headers_n2) */
361 FROM xla_ae_headers aeh
362 WHERE application_id = g_application_id
363 AND event_id IN (SELECT reference_num_2
364 FROM xla_events_gt
365 WHERE reference_char_1 = 'E'
366 AND reference_num_2 IS NOT NULL )
367 AND accounting_entry_status_code <> 'F' ;
368
369 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
370 trace
371 (p_msg => 'Number of ae headers deleted = '||SQL%ROWCOUNT
372 ,p_level => C_LEVEL_STATEMENT
373 ,p_module => l_log_module);
374 END IF;
375
376 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
377 trace
378 (p_msg => 'END procedure delete_incomplete_journals'
379 ,p_level => C_LEVEL_PROCEDURE
380 ,p_module =>l_log_module);
381
382 END IF;
383 EXCEPTION
384 WHEN xla_exceptions_pkg.application_exception THEN
385 RAISE;
386 WHEN OTHERS THEN
387 xla_exceptions_pkg.raise_message
388 (p_location => 'xla_reverse_events_pvt_pkg.delete_incomplete_journals');
389
390 END delete_incomplete_journals ;
391
392 --+==========================================================================+
393 --| PRIVATE PROCEDURE |
394 --| Validate_Events |
395 --| Local Procedure to validate the consistency of the data that has been |
396 --| provided by the user in the Interface table |
397 --| Note: Event_Number in XLA_EVENTS_GT contains the EVENT_ID from which |
398 --| reversal accounting needs to be created. |
399 --+==========================================================================+
400
401 PROCEDURE validate_events ( x_return_status OUT NOCOPY VARCHAR2)
402 IS
403
404 CURSOR c_errors IS
405 SELECT /*+ leading(evt) use_nl(evt,xah,xte) INDEX(xah,xla_ae_headers_n2)INDEX(xte,XLA_TRANSACTION_ENTITIES_U1) */
406 DISTINCT xte.ledger_id trx_ledger_id ,
407 xte.entity_code trx_entity_code ,
408 xah.gl_transfer_status_code ,
409 evt.*
410 FROM xla_events_gt evt ,
411 xla_ae_headers xah ,
412 XLA_TRANSACTION_ENTITIES_UPG xte
413 WHERE xah.application_id(+) = evt.application_id
414 AND xah.event_id(+) = evt.event_number
415 AND xte.APPLICATION_ID(+) = xah.application_id
416 AND xte.entity_id(+) = xah.entity_id
417 AND xah.application_id(+) = g_application_id
418 AND xte.application_id(+) = g_application_id
419 AND (
420 ( xah.gl_transfer_status_code <> 'Y' OR xah.gl_transfer_status_code IS NULL )
421 OR
422 evt.BUDGETARY_CONTROL_FLAG = 'Y'
423 OR
424 xte.ledger_id <> evt.ledger_id
425 OR
426 xte.entity_code <> evt.entity_code
427 OR
428 evt.BUDGETARY_CONTROL_FLAG IS NULL -- will be NULL only when Event_id,application_id is incorrect in the rest we default to N even if its NULL
429 ) ;
430
431 l_log_module VARCHAR2(240);
432 BEGIN
433
434 IF g_log_enabled THEN
435 l_log_module := C_DEFAULT_MODULE||'.validate_events';
436 END IF;
437
438 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
439 trace
440 (p_msg => 'BEGIN of procedure validate_events'
441 ,p_level => C_LEVEL_PROCEDURE
442 ,p_module =>l_log_module);
443
444 END IF;
445
446 FOR i IN c_errors
447 LOOP
448 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
449 trace
450 (p_msg => 'Errors for Event_ID: ' || i.event_id
451 ,p_level => C_LEVEL_PROCEDURE
452 ,p_module =>l_log_module);
453
454 END IF;
455 UPDATE xla_events_gt t
456 SET t.process_status_code = 'E'
457 WHERE t.event_id = i.event_id ;
458
459 x_return_status := FND_API.G_RET_STS_ERROR;
460
461 IF ( i.BUDGETARY_CONTROL_FLAG IS NULL ) THEN
462
463 xla_accounting_err_pkg.build_message(
464 p_appli_s_name => 'XLA'
465 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
466 ,p_token_1 => 'ERROR'
467 ,p_value_1 => 'Application ID,Event ID ('||i.application_id ||', '|| i.event_number ||')combination is Invalid.'
468 ,p_entity_id => i.entity_id
469 ,p_event_id => i.event_id
470 ,p_ledger_id => i.ledger_id
471 ,p_ae_header_id => NULL
472 ,p_ae_line_num => NULL
473 ,p_accounting_batch_id => g_accounting_batch_id );
474
475 ELSE
476 IF i.BUDGETARY_CONTROL_FLAG = 'Y'
477 THEN
478 xla_accounting_err_pkg.build_message(
479 p_appli_s_name => 'XLA'
480 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
481 ,p_token_1 => 'ERROR'
482 ,p_value_1 => 'Budgetary Control Enabled Event, which is not supported by the API.'
483 ,p_entity_id => i.entity_id
484 ,p_event_id => i.event_id
485 ,p_ledger_id => i.ledger_id
486 ,p_ae_header_id => NULL
487 ,p_ae_line_num => NULL
488 ,p_accounting_batch_id => g_accounting_batch_id );
489 END IF;
490
491 IF i.gl_transfer_status_code <> 'Y' AND i.gl_transfer_status_code IS NOT NULL
492 THEN
493 xla_accounting_err_pkg.build_message(
494 p_appli_s_name => 'XLA'
495 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
496 ,p_token_1 => 'ERROR'
497 ,p_value_1 => 'Un-Transferred Headers exist for the Event Specified: '|| i.event_number
498 ,p_entity_id => i.entity_id
499 ,p_event_id => i.event_id
500 ,p_ledger_id => i.ledger_id
501 ,p_ae_header_id => NULL
502 ,p_ae_line_num => NULL
503 ,p_accounting_batch_id => g_accounting_batch_id );
504 END IF;
505
506 IF i.gl_transfer_status_code IS NULL
507 THEN
508 xla_accounting_err_pkg.build_message(
509 p_appli_s_name => 'XLA'
510 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
511 ,p_token_1 => 'ERROR'
512 ,p_value_1 => 'No Accounting Exists for the Event Specified: '|| i.event_number
513 ,p_entity_id => i.entity_id
514 ,p_event_id => i.event_id
515 ,p_ledger_id => i.ledger_id
516 ,p_ae_header_id => NULL
517 ,p_ae_line_num => NULL
518 ,p_accounting_batch_id => g_accounting_batch_id );
519 END IF;
520
521 IF i.trx_ledger_id <> i.ledger_id
522 THEN
523
524 xla_accounting_err_pkg.build_message(
525 p_appli_s_name => 'XLA'
526 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
527 ,p_token_1 => 'ERROR'
528 ,p_value_1 => 'Ledger Associated with the Event( '||i.trx_ledger_id||
529 ' ) is not same as the value in the Interface( '|| i.ledger_id||' ).'
530 ,p_entity_id => i.entity_id
531 ,p_event_id => i.event_id
532 ,p_ledger_id => i.ledger_id
533 ,p_ae_header_id => NULL
534 ,p_ae_line_num => NULL
535 ,p_accounting_batch_id => g_accounting_batch_id );
536 END IF;
537
538 IF i.trx_entity_code <> i.entity_code
539 THEN
540 xla_accounting_err_pkg.build_message(
541 p_appli_s_name => 'XLA'
542 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
543 ,p_token_1 => 'ERROR'
544 ,p_value_1 => 'Entity Code Associated with the Event( '||i.trx_entity_code||
545 ' ) is not same as the value in the Interface( '||i.entity_code||' ).'
546 ,p_entity_id => i.entity_id
547 ,p_event_id => i.event_id
548 ,p_ledger_id => i.ledger_id
549 ,p_ae_header_id => NULL
550 ,p_ae_line_num => NULL
551 ,p_accounting_batch_id => g_accounting_batch_id );
552 END IF;
553
554 END IF;
555 END LOOP;
556 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
557 trace
558 (p_msg => 'END procedure validate_events'
559 ,p_level => C_LEVEL_PROCEDURE
560 ,p_module =>l_log_module);
561
562 END IF;
563
564 EXCEPTION
565 WHEN xla_exceptions_pkg.application_exception THEN
566 RAISE;
567 WHEN OTHERS THEN
568 xla_exceptions_pkg.raise_message
569 (p_location => 'xla_reverse_events_pvt_pkg.validate_events');
570
571 END validate_events ;
572
573
574 --+==========================================================================+
575 --| PRIVATE PROCEDURE |
576 --| Create_Reversal_Entries |
577 --| Local Procedure to create the reversal accounting. Following are done:|
578 --| a) Reversal Events are created. |
579 --| b) Headers_GT/Headers/Lines/Distributions/HeaderACS/LineACS are created |
580 --| Note: Headers/Lines etc are created for those EVENTS which passed the |
581 --| validations in the procedure VALIDATE_EVENTS. Errored Events will |
582 --| just have EVENTS alone. |
583 --+==========================================================================+
584 PROCEDURE create_reversal_entries
585 IS
586 l_log_module VARCHAR2(240);
587 l_reversal_label VARCHAR2(240);
588 l_rows_inserted NUMBER ;
589
590 BEGIN
591
592 IF g_log_enabled THEN
593 l_log_module := C_DEFAULT_MODULE||'.create_reversal_entries';
594 END IF;
595
596 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
597 trace
598 (p_msg => 'BEGIN of procedure create_reversal_entries'
599 ,p_level => C_LEVEL_PROCEDURE
600 ,p_module =>l_log_module);
601
602 END IF;
603
604 -- Events which have failed the VALIDATIONS in VALIDATE_EVENTS are
605 -- also created to maintain the accounting_errors for customer to review
606 INSERT INTO XLA_EVENTS
607 ( EVENT_ID,
608 APPLICATION_ID,
609 EVENT_TYPE_CODE,
610 EVENT_DATE,
611 ENTITY_ID,
612 EVENT_STATUS_CODE,
613 PROCESS_STATUS_CODE,
614 EVENT_NUMBER,
615 ON_HOLD_FLAG,
616 CREATION_DATE,
617 CREATED_BY,
618 LAST_UPDATE_DATE,
619 LAST_UPDATED_BY,
620 LAST_UPDATE_LOGIN,
621 PROGRAM_UPDATE_DATE,
622 PROGRAM_APPLICATION_ID,
623 TRANSACTION_DATE,
624 BUDGETARY_CONTROL_FLAG,
625 UPG_BATCH_ID ,
626 REFERENCE_NUM_1
627 )
628 SELECT evt.event_id ,
629 g_APPLICATION_ID,
630 C_TYPE_MANUAL ,
631 evt.EVENT_DATE,
632 evt.ENTITY_ID,
633 evt.EVENT_STATUS_CODE,
634 evt.PROCESS_STATUS_CODE,
635 g_event_number + ROWNUM EVENT_NUMBER,
636 'N' ON_HOLD_FLAG,
637 SYSDATE CREATION_DATE,
638 g_last_updated_by CREATED_BY,
639 SYSDATE LAST_UPDATE_DATE,
640 g_last_updated_by LAST_UPDATED_BY,
641 g_last_update_login LAST_UPDATE_LOGIN,
642 SYSDATE PROGRAM_UPDATE_DATE,
643 g_application_id PROGRAM_APPLICATION_ID,
644 evt.event_date TRANSACTION_DATE,
645 evt.BUDGETARY_CONTROL_FLAG ,
646 -9999 UPG_BATCH_ID ,
647 evt.event_number REFERENCE_NUM_1
648 FROM xla_events_gt evt
649 WHERE 1 = 1
650 AND NOT EXISTS
651 ( SELECT /*+ INDEX(xe,XLA_EVENTS_U1) */1
652 FROM xla_events xe
653 WHERE xe.application_id = evt.application_id
654 AND xe.event_id = evt.event_id
655 AND xe.application_id = g_application_id ) ;
656
657 l_rows_inserted := SQL%ROWCOUNT ;
658
659 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
660 trace
661 (p_msg => 'Number of Events Created : ' || l_rows_inserted
662 ,p_level => C_LEVEL_PROCEDURE
663 ,p_module =>l_log_module);
664
665 END IF;
666
667 g_event_number := g_event_number + l_rows_inserted ;
668
669 fnd_message.set_name('XLA', 'XLA_MJE_LABEL_REVERSAL');
670 l_reversal_label := fnd_message.get();
671
672 -- Data is inserted into Headers_GT to maintain the relationship between
673 -- new_header_id and original_header_id from which reversal_accounting is created
674 -- PARENT_HEADER_ID contains the ORIGINAL_HEADER_ID
675 -- EVENT_NUMBER contains the ORIGINAL_EVENT_ID corresponding to ORIGINAL_HEADER_ID
676 INSERT INTO XLA_AE_HEADERS_GT
677 ( AE_HEADER_ID
678 ,LEDGER_ID
679 ,ENTITY_ID
680 ,EVENT_ID
681 ,EVENT_TYPE_CODE
682 ,ACCOUNTING_DATE
683 ,GL_TRANSFER_STATUS_CODE
684 ,JE_CATEGORY_NAME
685 ,ACCOUNTING_ENTRY_STATUS_CODE
686 ,ACCOUNTING_ENTRY_TYPE_CODE
687 ,DESCRIPTION
688 ,DOC_SEQUENCE_ID
689 ,DOC_SEQUENCE_VALUE
690 ,BUDGET_VERSION_ID
691 ,BALANCE_TYPE_CODE
692 ,PERIOD_NAME
693 ,DOC_CATEGORY_CODE
694 ,PARENT_HEADER_ID
695 ,ACCRUAL_REVERSAL_FLAG
696 ,EVENT_NUMBER
697 )
698 SELECT /*+ leading(xe) use_nl(xe,xah) INDEX(xah,XLA_AE_HEADERS_N2) */
699 XLA_AE_HEADERS_S.NEXTVAL
700 ,xah.LEDGER_ID
701 ,xe.ENTITY_ID
702 ,xe.EVENT_ID
703 ,xe.EVENT_TYPE_CODE
704 ,xe.EVENT_DATE
705 ,DECODE(NVL(g_gl_transfer_flag,'Y'),'Y','N','NT')
706 ,xah.JE_CATEGORY_NAME
707 ,'N' ACCOUNTING_ENTRY_STATUS_CODE
708 , C_TYPE_MANUAL ACCOUNTING_ENTRY_TYPE_CODE
709 , l_reversal_label || ':' || xah.DESCRIPTION
710 ,xah.DOC_SEQUENCE_ID
711 ,xah.DOC_SEQUENCE_VALUE
712 ,NULL
713 ,xah.BALANCE_TYPE_CODE
714 ,( SELECT gps.PERIOD_NAME
715 FROM GL_PERIOD_STATUSES gps
716 WHERE 1 = 1
717 AND gps.ledger_id = xah.ledger_id
718 AND xe.event_date between gps.start_date and gps.end_Date
719 AND gps.application_id = 101
720 AND gps.adjustment_period_flag = 'N'
721 )
722 ,xah.DOC_CATEGORY_CODE
723 ,xah.AE_HEADER_ID
724 ,'N'
725 ,xah.event_id
726 FROM XLA_AE_HEADERS xah
727 ,XLA_EVENTS_GT xe
728 WHERE xe.application_id = xah.application_id
729 AND xe.event_number = xah.event_id
730 AND xah.application_id = g_application_id
731 AND xe.PROCESS_STATUS_CODE = 'U'
732 AND xah.ledger_id IN ( SELECT /*+ un_nest */xlr.ledger_id
733 FROM xla_ledger_relationships_v xlr
734 WHERE xlr.primary_ledger_id = g_ledger_id
735 AND xlr.relationship_enabled_flag = 'Y'
736 AND EXISTS (SELECT 1
737 FROM xla_ledger_options xlo
738 WHERE application_id = g_application_id
739 AND DECODE(xlr.ledger_category_code ,'ALC',xlr.ledger_id ,xlo.ledger_id) = xlr.ledger_id
740 AND DECODE(xlr.ledger_category_code ,'SECONDARY',xlo.capture_event_flag ,'N') = 'N'
741 AND DECODE(xlr.ledger_category_code ,'ALC','Y',xlo.enabled_flag) = 'Y' )
742 UNION ALL
743 SELECT gl.ledger_id
744 FROM gl_ledgers gl
745 WHERE gl.ledger_id = g_ledger_id
746 AND gl.ledger_category_code = 'SECONDARY'
747 );
748
749 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
750 trace
751 (p_msg => 'Number of rows Created in headers GT: ' || SQL%ROWCOUNT
752 ,p_level => C_LEVEL_PROCEDURE
753 ,p_module =>l_log_module);
754
755 END IF;
756
757 INSERT INTO XLA_AE_HEADERS
758 ( APPLICATION_ID
759 ,AE_HEADER_ID
760 ,LEDGER_ID
761 ,ENTITY_ID
762 ,EVENT_ID
763 ,EVENT_TYPE_CODE
764 ,ACCOUNTING_DATE
765 ,GL_TRANSFER_STATUS_CODE
766 ,JE_CATEGORY_NAME
767 ,ACCOUNTING_ENTRY_STATUS_CODE
768 ,ACCOUNTING_ENTRY_TYPE_CODE
769 ,DESCRIPTION
770 ,DOC_SEQUENCE_ID
771 ,DOC_SEQUENCE_VALUE
772 ,ACCOUNTING_BATCH_ID
773 ,BUDGET_VERSION_ID
774 ,BALANCE_TYPE_CODE
775 ,PERIOD_NAME
776 ,DOC_CATEGORY_CODE
777 ,ACCRUAL_REVERSAL_FLAG
778 ,creation_date
779 ,created_by
780 ,last_update_date
781 ,last_updated_by
782 ,last_update_login
783 ,upg_batch_id
784 )
785 SELECT g_application_id
786 ,AE_HEADER_ID
787 ,LEDGER_ID
788 ,ENTITY_ID
789 ,EVENT_ID
790 ,EVENT_TYPE_CODE
791 ,ACCOUNTING_DATE
792 ,GL_TRANSFER_STATUS_CODE
793 ,JE_CATEGORY_NAME
794 ,ACCOUNTING_ENTRY_STATUS_CODE
795 ,ACCOUNTING_ENTRY_TYPE_CODE
796 ,DESCRIPTION
797 ,DOC_SEQUENCE_ID
798 ,DOC_SEQUENCE_VALUE
799 ,g_accounting_batch_id
800 ,BUDGET_VERSION_ID
801 ,BALANCE_TYPE_CODE
802 ,PERIOD_NAME
803 ,DOC_CATEGORY_CODE
804 ,ACCRUAL_REVERSAL_FLAG
805 ,sysdate
806 ,g_last_updated_by
807 ,sysdate
808 ,g_last_updated_by
809 ,g_last_update_login
810 ,-9999
811 FROM XLA_AE_HEADERS_GT
812 ;
813
814 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
815 trace
816 (p_msg => 'Number of Headers Created : ' || SQL%ROWCOUNT
817 ,p_level => C_LEVEL_PROCEDURE
818 ,p_module =>l_log_module);
819
820 END IF;
821
822 -- Copy header analytical criteria FROM the original entry to the reversal entry
823 INSERT INTO xla_ae_header_acs(
824 ae_header_id
825 ,analytical_criterion_code
826 ,analytical_criterion_type_code
827 ,amb_context_code
828 ,ac1
829 ,ac2
830 ,ac3
831 ,ac4
832 ,ac5
833 ,object_version_number)
834 SELECT xah.ae_header_id
835 ,xah_acs.analytical_criterion_code
836 ,xah_acs.analytical_criterion_type_code
837 ,xah_acs.amb_context_code
838 ,xah_acs.ac1
839 ,xah_acs.ac2
840 ,xah_acs.ac3
841 ,xah_acs.ac4
842 ,xah_acs.ac5
843 ,1
844 FROM xla_ae_header_acs xah_acs ,
845 xla_ae_headers_gt xah
846 WHERE xah_acs.ae_header_id = xah.parent_header_id;
847
848 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
849 trace
850 (p_msg => 'Number of Header ACS Created : ' || SQL%ROWCOUNT
851 ,p_level => C_LEVEL_PROCEDURE
852 ,p_module =>l_log_module);
853
854 END IF;
855
856 -- Create journal entry lines for the reversal journal entry
857 INSERT INTO xla_ae_lines
858 (application_id
859 ,ae_header_id
860 ,ae_line_num
861 ,displayed_line_number
862 ,code_combination_id
863 ,gl_transfer_mode_code
864 ,creation_date
865 ,created_by
866 ,last_update_date
867 ,last_updated_by
868 ,last_update_login
869 ,party_id
870 ,party_site_id
871 ,party_type_code
872 ,entered_dr
873 ,entered_cr
874 ,accounted_dr
875 ,accounted_cr
876 ,unrounded_entered_dr
877 ,unrounded_entered_cr
878 ,unrounded_accounted_dr
879 ,unrounded_accounted_cr
880 ,description
881 ,statistical_amount
882 ,currency_code
883 ,currency_conversion_type
884 ,currency_conversion_date
885 ,currency_conversion_rate
886 ,accounting_class_code
887 ,jgzz_recon_ref
888 ,gl_sl_link_id
889 ,gl_sl_link_table
890 ,attribute_category
891 ,encumbrance_type_id
892 ,attribute1
893 ,attribute2
894 ,attribute3
895 ,attribute4
896 ,attribute5
897 ,attribute6
898 ,attribute7
899 ,attribute8
900 ,attribute9
901 ,attribute10
902 ,attribute11
903 ,attribute12
904 ,attribute13
905 ,attribute14
906 ,attribute15
907 ,gain_or_loss_flag
908 ,ledger_id
909 ,accounting_date
910 ,mpa_accrual_entry_flag
911 ,control_balance_flag
912 ,analytical_balance_flag
913 ,upg_batch_id ) -- 4262811
914 SELECT /*+ leading(xah) USE_NL_WITH_INDEX(xal,XLA_AE_LINES_U1) */
915 xal.application_id
916 ,xah.ae_header_id
917 ,xal.ae_line_num
918 ,xal.displayed_line_number
919 ,xal.code_combination_id
920 ,xal.gl_transfer_mode_code
921 ,sysdate
922 ,g_last_updated_by
923 ,sysdate
924 ,g_last_updated_by
925 ,g_last_update_login
926 ,xal.party_id
927 ,xal.party_site_id
928 ,xal.party_type_code
929 ,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,xal.entered_cr, -1 * xal.entered_dr)
930 ,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,xal.entered_dr, -1 * xal.entered_cr)
931 ,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,xal.accounted_cr, -1 * xal.accounted_dr)
932 ,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,xal.accounted_dr, -1 * xal.accounted_cr)
933 ,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,NVL(xal.unrounded_entered_cr,xal.entered_cr),
934 -1 * NVL(xal.unrounded_entered_dr,xal.entered_dr))
935 ,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,NVL(xal.unrounded_entered_dr,xal.entered_dr) ,
936 -1 * NVL(xal.unrounded_entered_cr,xal.entered_cr))
937 ,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,NVL(xal.unrounded_accounted_cr,xal.accounted_cr),
938 -1 * NVL(xal.unrounded_accounted_dr,xal.accounted_dr))
939 ,DECODE(xlo.ACCT_REVERSAL_OPTION_CODE, C_REVERSAL_SWITCH_DR_CR,NVL(xal.unrounded_accounted_dr,xal.accounted_dr),
940 -1 * NVL(xal.unrounded_accounted_cr,xal.accounted_cr))
941 ,l_reversal_label || ':' || xal.description
942 ,xal.statistical_amount
943 ,xal.currency_code
944 ,xal.currency_conversion_type
945 ,xal.currency_conversion_date
946 ,xal.currency_conversion_rate
947 ,xal.accounting_class_code
948 ,xal.jgzz_recon_ref
949 ,XLA_GL_SL_LINK_ID_S.NEXTVAL
950 ,'XLAJEL'
951 ,xal.attribute_category
952 ,xal.encumbrance_type_id
953 ,xal.attribute1
954 ,xal.attribute2
955 ,xal.attribute3
956 ,xal.attribute4
957 ,xal.attribute5
958 ,xal.attribute6
959 ,xal.attribute7
960 ,xal.attribute8
961 ,xal.attribute9
962 ,xal.attribute10
963 ,xal.attribute11
964 ,xal.attribute12
965 ,xal.attribute13
966 ,xal.attribute14
967 ,xal.attribute15
968 ,xal.gain_or_loss_flag
969 ,xah.ledger_id
970 ,xah.accounting_date
971 ,NVL(xal.mpa_accrual_entry_flag,'N')
972 ,( SELECT DECODE(xal.accounting_class_code,
973 'INTER', NULL,
974 'INTRA', NULL,
975 DECODE(NVL(ccid.reference3,'N'),'N',NULL,
976 'R', NULL,
977 DECODE(ccid.account_type
978 , 'A', 'P'
979 , 'L', 'P'
980 , 'O', 'P'
981 , NULL)
982 ))
983 FROM gl_code_combinations ccid
984 WHERE ccid.code_combination_id = xal.code_combination_id
985 ) CONTROL_BALANCE_FLAG
986 ,DECODE(NVL(xal.analytical_balance_flag ,'N'),'N',NULL,'P')
987 , -9999
988 FROM xla_ae_lines xal ,
989 xla_ae_headers_gt xah ,
990 xla_ledger_options xlo ,
991 gl_ledgers gl
992 WHERE xal.application_id = g_application_id
993 AND xal.ae_header_id = xah.parent_header_id
994 AND xah.ledger_id = gl.ledger_id
995 AND xlo.application_id = xal.application_id
996 AND xlo.ledger_id = DECODE(gl.ledger_category_code,'ALC',g_ledger_id,xal.ledger_id)
997 ;
998
999 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1000 trace
1001 (p_msg => 'Number of Lines Created : ' || SQL%ROWCOUNT
1002 ,p_level => C_LEVEL_PROCEDURE
1003 ,p_module =>l_log_module);
1004
1005 END IF;
1006
1007 -- Copy the journal entry lines' analytical criteria from the original entry to
1008 -- the reversal entry
1009 INSERT INTO xla_ae_line_acs(
1010 ae_header_id
1011 ,ae_line_num
1012 ,analytical_criterion_code
1013 ,analytical_criterion_type_code
1014 ,amb_context_code
1015 ,ac1
1016 ,ac2
1017 ,ac3
1018 ,ac4
1019 ,ac5
1020 ,object_version_number)
1021 SELECT /*+ leading(xah) USE_NL_WITH_INDEX(xal_acs,XLA_AE_LINE_ACS_U1) */xah.ae_header_id
1022 ,xal_acs.ae_line_num
1023 ,xal_acs.analytical_criterion_code
1024 ,xal_acs.analytical_criterion_type_code
1025 ,xal_acs.amb_context_code
1026 ,xal_acs.ac1
1027 ,xal_acs.ac2
1028 ,xal_acs.ac3
1029 ,xal_acs.ac4
1030 ,xal_acs.ac5
1031 ,1
1032 FROM xla_ae_line_acs xal_acs
1033 , xla_ae_headers_gt xah
1034 WHERE xal_acs.ae_header_id = xah.parent_header_id ;
1035
1036 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1037 trace
1038 (p_msg => 'Number of Line ACS Created : ' || SQL%ROWCOUNT
1039 ,p_level => C_LEVEL_PROCEDURE
1040 ,p_module =>l_log_module);
1041
1042 END IF;
1043
1044 INSERT INTO xla_distribution_links
1045 (application_id
1046 ,event_id
1047 ,ae_header_id
1048 ,ae_line_num
1049 ,source_distribution_type
1050 ,statistical_amount
1051 ,ref_ae_header_id
1052 ,ref_temp_line_num
1053 ,merge_duplicate_code
1054 ,temp_line_num
1055 ,ref_event_id
1056 ,event_class_code
1057 ,event_type_code
1058 ,unrounded_entered_dr
1059 ,unrounded_entered_cr
1060 ,unrounded_accounted_dr
1061 ,unrounded_accounted_cr
1062 ,upg_batch_id )
1063 SELECT /*+ leading(xah) USE_NL_WITH_INDEX(xal,XLA_AE_LINES_U1) */
1064 xal.application_id
1065 ,xah.event_id
1066 ,xal.ae_header_id
1067 ,xal.ae_line_num
1068 ,C_TYPE_MANUAL -- source distribution type
1069 ,xal.statistical_amount -- statistical amount
1070 ,xah.parent_header_id -- ref ae header id
1071 ,xal.ae_line_num -- ref temp line num
1072 ,'N' -- merge duplicate code
1073 ,ae_line_num -- temp line num
1074 ,xah.EVENT_NUMBER -- ref event id
1075 ,xah.event_type_code -- event class code
1076 ,xah.event_type_code -- event type code
1077 ,xal.unrounded_entered_dr
1078 ,xal.unrounded_entered_cr
1079 ,xal.unrounded_accounted_dr
1080 ,xal.unrounded_accounted_cr
1081 ,-9999
1082 FROM xla_ae_headers_gt xah
1083 ,xla_ae_lines xal
1084 WHERE 1 = 1
1085 AND xal.application_id = g_application_id
1086 AND xal.ae_header_id = xah.ae_header_id;
1087
1088 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1089 trace
1090 (p_msg => 'Number of Distribution Links Created : ' || SQL%ROWCOUNT
1091 ,p_level => C_LEVEL_PROCEDURE
1092 ,p_module =>l_log_module);
1093
1094 END IF;
1095
1096 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1097 trace
1098 (p_msg => 'END procedure create_reversal_entries'
1099 ,p_level => C_LEVEL_PROCEDURE
1100 ,p_module =>l_log_module);
1101
1102 END IF;
1103 EXCEPTION
1104 WHEN xla_exceptions_pkg.application_exception THEN
1105 RAISE;
1106 WHEN OTHERS THEN
1107 xla_exceptions_pkg.raise_message
1108 (p_location => 'xla_reverse_events_pvt_pkg.create_reversal_entries');
1109
1110 END create_reversal_entries ;
1111
1112 --+==========================================================================+
1113 --| PRIVATE PROCEDURE |
1114 --| Complete_Reversal_Entries |
1115 --| Local Procedure to mark success/failure of the reversal accounting of |
1116 --| each event in INTERFACE table/XLA_EVENTS/XLA_AE_HEADERS. |
1117 --+==========================================================================+
1118 PROCEDURE complete_reversal_entries
1119 IS
1120 l_log_module VARCHAR2(240);
1121 BEGIN
1122
1123 IF g_log_enabled THEN
1124 l_log_module := C_DEFAULT_MODULE||'.complete_reversal_entries';
1125 END IF;
1126
1127 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1128 trace
1129 (p_msg => 'BEGIN of procedure complete_reversal_entries'
1130 ,p_level => C_LEVEL_PROCEDURE
1131 ,p_module =>l_log_module);
1132
1133 END IF;
1134
1135 MERGE /*+ INDEX(int,XLA_REVERSE_EVENTS_INTF_U1) */ INTO XLA_REVERSE_EVENTS_INTERFACE int
1136 USING XLA_EVENTS_GT evt
1137 ON ( int.event_id = evt.event_number
1138 AND int.ledger_id = g_ledger_id
1139 AND int.batch_code = g_batch_code
1140 AND int.application_id = evt.application_id
1141 )
1142 WHEN MATCHED THEN
1143 UPDATE SET int.process_status_code = DECODE(evt.process_status_code ,'U','P','E')
1144 ,int.REVERSAL_ENTITY_ID = evt.entity_id
1145 ,int.REVERSAL_EVENT_ID = evt.event_id
1146 ,int.ACCOUNTING_BATCH_ID = DECODE(evt.process_status_code ,'U',g_accounting_batch_id,NULL)
1147 ;
1148
1149 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1150 trace
1151 ( p_msg => 'Number of Rows Updated in XLA_REVERSE_EVENTS_INTERFACE ' || SQL%ROWCOUNT
1152 ,p_level => C_LEVEL_PROCEDURE
1153 ,p_module =>l_log_module);
1154 END IF;
1155
1156 MERGE /*+ INDEX(xe,XLA_EVENTS_U1) */ INTO xla_events xe
1157 USING XLA_EVENTS_GT evt
1158 ON ( evt.event_id = xe.event_id
1159 AND evt.application_id = xe.application_id
1160 AND xe.application_id = g_application_id
1161 )
1162 WHEN MATCHED THEN
1163 UPDATE SET xe.event_status_code = DECODE(evt.process_status_code ,'U','P','U')
1164 ,xe.process_status_code = DECODE(evt.process_status_code ,'U','P','I')
1165 WHERE xe.event_status_code <> 'P' ;
1166
1167 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1168 trace
1169 ( p_msg => 'Number of Rows Updated in XLA_EVENTS ' || SQL%ROWCOUNT
1170 ,p_level => C_LEVEL_PROCEDURE
1171 ,p_module =>l_log_module);
1172 END IF;
1173
1174 MERGE /*+ INDEX(xah,XLA_AE_HEADERS_N2) */ INTO xla_ae_headers xah
1175 USING XLA_EVENTS_GT evt
1176 ON ( evt.event_id = xah.event_id
1177 AND evt.application_id = xah.application_id
1178 AND xah.accounting_batch_id = g_accounting_batch_id
1179 AND xah.application_id = g_application_id
1180 )
1181 WHEN MATCHED THEN
1182 UPDATE SET xah.accounting_entry_status_code = DECODE(evt.process_status_code ,'U','F',xah.accounting_entry_status_code )
1183 WHERE xah.accounting_entry_status_code <> 'F' ;
1184
1185 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1186 trace
1187 ( p_msg => 'Number of Rows Updated in XLA_AE_HEADERS ' || SQL%ROWCOUNT
1188 ,p_level => C_LEVEL_PROCEDURE
1189 ,p_module =>l_log_module);
1190 END IF;
1191
1192 -- Insert Errors that have been encountered during VALIDATE_EVENTS/JEBAL_VALIDATION
1193 xla_accounting_err_pkg.insert_errors;
1194
1195 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1196 trace
1197 (p_msg => 'END procedure complete_reversal_entries'
1198 ,p_level => C_LEVEL_PROCEDURE
1199 ,p_module =>l_log_module);
1200
1201 END IF;
1202 EXCEPTION
1203 WHEN xla_exceptions_pkg.application_exception THEN
1204 RAISE;
1205 WHEN OTHERS THEN
1206 xla_exceptions_pkg.raise_message
1207 (p_location => 'xla_reverse_events_pvt_pkg.complete_reversal_entries');
1208
1209 END complete_reversal_entries ;
1210
1211 --+==========================================================================+
1212 --| PRIVATE PROCEDURE |
1213 --| Process_Events |
1214 --| Local Procedure to process the list of Events that have determined |
1215 --| based on the batch_commit_size |
1216 --+==========================================================================+
1217 PROCEDURE process_events ( p_array_event_data IN t_event_data
1218 ,x_return_status OUT NOCOPY VARCHAR2 )
1219 IS
1220 l_log_module VARCHAR2(240);
1221 l_retcode NUMBER;
1222 l_return_status VARCHAR2(1);
1223 l_max_event_Date DATE;
1224 l_max_event_id NUMBER;
1225 PRAGMA AUTONOMOUS_TRANSACTION;
1226 BEGIN
1227
1228 IF g_log_enabled THEN
1229 l_log_module := C_DEFAULT_MODULE||'.process_events';
1230 END IF;
1231
1232 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1233 trace
1234 (p_msg => 'BEGIN of procedure process_events '
1235 ,p_level => C_LEVEL_PROCEDURE
1236 ,p_module =>l_log_module);
1237
1238 END IF;
1239
1240 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1241
1242 IF g_manual_entity_id IS NULL THEN
1243 create_manual_entity ;
1244 END IF;
1245
1246 FORALL i IN 1..p_array_event_data.event_number.COUNT
1247 INSERT INTO xla_events_gt
1248 (entity_id
1249 ,application_id
1250 ,ledger_id
1251 ,entity_code
1252 ,event_id
1253 ,event_class_code
1254 ,event_type_code
1255 ,event_number
1256 ,event_date
1257 ,transaction_date
1258 ,event_status_code
1259 ,process_status_code
1260 ,budgetary_control_flag
1261 ,reference_num_1
1262 ,reference_num_2
1263 ,reference_char_1
1264 )
1265 VALUES (
1266 NVL(p_array_event_data.reference_num_1(i) , g_manual_entity_id ) -- use the existing entity_id in the interface or use the new one
1267 ,p_array_event_data.application_id(i)
1268 ,p_array_event_data.ledger_id(i) -- contains the LEDGER_ID from Interface
1269 ,p_array_event_data.entity_code(i) -- contains the ENTITY_CODE from Interface
1270 ,NVL(p_array_event_data.event_id(i), XLA_EVENTS_S.NEXTVAL ) -- use the existing event_id in the interface or use the new one
1271 ,C_TYPE_MANUAL
1272 ,C_TYPE_MANUAL
1273 ,p_array_event_data.event_number(i) -- contains the ORIGINAL EVENT_ID
1274 ,p_array_event_data.event_date(i) -- contains the REVERSAL_GL_DATE from Interface
1275 ,p_array_event_data.transaction_date(i)
1276 ,p_array_event_data.event_status_code(i)
1277 ,p_array_event_data.process_status_code(i)
1278 ,p_array_event_data.budgetary_control_flag(i)
1279 ,p_array_event_data.reference_num_1(i) -- contains the REVERSAL_ENTITY_ID from Interface
1280 ,p_array_event_data.reference_num_2(i) -- contains the REVERSAL_EVENT_ID from Interface
1281 ,p_array_event_data.reference_char_1(i) -- contains the PROCESS_STATUS_CODE from Interface
1282 );
1283
1284 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1285 trace
1286 (p_msg => 'Events Inserted Count:'||SQL%ROWCOUNT
1287 ,p_level => C_LEVEL_STATEMENT
1288 ,p_module => l_log_module);
1289 END IF;
1290
1291 IF ( g_initialized = 'N' ) THEN
1292
1293 SELECT MAX(EVENT_DATE) , MAX(event_number)
1294 INTO l_max_event_Date ,
1295 l_max_event_id
1296 FROM xla_events_gt ;
1297
1298 -- Need the Caching for the usage of the set up in the XLA_JE_VALIDATION
1299 xla_accounting_cache_pkg.load_application_ledgers(g_application_id , g_ledger_id , l_max_event_date );
1300 g_initialized := 'Y' ;
1301
1302 UPDATE xla_transaction_entities_upg xtem
1303 SET xtem.valuation_method = ( select xte.valuation_method
1304 from xla_transaction_entities_upg xte ,
1305 xla_events xe
1306 where xe.application_id = g_application_id
1307 and xe.event_id = l_max_event_id
1308 and xte.application_id = g_application_id
1309 and xte.entity_id = xe.entity_id
1310 )
1311 WHERE xtem.application_id = g_application_id
1312 AND xtem.entity_id = g_manual_entity_id
1313 AND xtem.valuation_method IS NULL ;
1314
1315 END IF;
1316
1317 -- Clean any invalid accounting created in the prior run of Reversal Accounting.
1318 delete_incomplete_journals ;
1319
1320 -- Validate the events data for the consistency of data between INTERFACE and XLA Main tables
1321 validate_events(l_return_status);
1322
1323 x_return_status := NVL(l_return_status , x_return_status);
1324 -- Create the Reverals for all the events which passed through the Validation successfully.
1325 create_reversal_entries ;
1326
1327 -- Validate the accounting
1328 l_retcode := xla_je_validation_pkg.balance_amounts
1329 ( p_application_id => g_application_id
1330 ,p_mode => 'CREATE_ACCOUNTING'
1331 ,p_end_date => l_max_event_date
1332 ,p_ledger_id => g_ledger_id
1333 ,p_budgetary_control_mode => 'NONE'
1334 ,p_accounting_mode => 'F'
1335 );
1336
1337 IF l_retcode <> 0 THEN
1338 x_return_status := FND_API.G_RET_STS_ERROR ;
1339 END IF;
1340 -- Mark the events/headers/interface success/failure based on the validation status
1341 complete_reversal_entries ;
1342
1343 -- Commit for any accounting that has been generated.
1344 COMMIT;
1345
1346 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1347 trace
1348 (p_msg => 'END procedure process_events'
1349 ,p_level => C_LEVEL_PROCEDURE
1350 ,p_module =>l_log_module);
1351
1352 END IF;
1353
1354 EXCEPTION
1355 WHEN xla_exceptions_pkg.application_exception THEN
1356 ROLLBACK;
1357 RAISE;
1358 WHEN OTHERS THEN
1359 ROLLBACK;
1360 xla_exceptions_pkg.raise_message
1361 (p_location => 'xla_reverse_events_pvt_pkg.process_events');
1362
1363 END process_events ;
1364
1365 --+==========================================================================+
1366 --| PUBLIC PROCEDURE |
1367 --| Create_Reversal_Accounting |
1368 --| Would need the following parameters as input |
1369 --| i)P_APPLICATION_ID : Application ID of the subledger from which |
1370 --| accounting needs to be Reversed. |
1371 --| ii)P_BATCH_CODE : Batch Code of List of the Events for which |
1372 --| accounting needs to be Reversed. |
1373 --| iii)P_LEDGER_ID : Ledger ID of the Transaction to which Events |
1374 --| belongs to |
1375 --| iv)P_BUDGETARY_CONTROL_FLAG: Default Value of "N". This determines |
1376 --| whether Encumbrance Accounting is required. |
1377 --| v)P_GL_TRANSFER_FLAG : Pass "N" when accounting not be transferred |
1378 --| to General Ledger."Y" when accounting to be|
1379 --| eligible for Transfer to GL, via "Transfer |
1380 --| Journal Entries to GL" concurrent program |
1381 --| vi)P_BATCH_COMMIT_SIZE: Minimum Batch Commit Size is 2000. Pass value |
1382 --| when more no of events need to be processed |
1383 --| vii)x_return_status : S is SUCCESS , E is Error , U is Unexpected |
1384 --| Error |
1385 --+==========================================================================+
1386
1387 PROCEDURE create_reversal_accounting( p_application_id IN NUMBER
1388 , p_batch_code IN VARCHAR2
1389 , p_ledger_id IN NUMBER
1390 , p_budgetary_control_flag IN VARCHAR2 DEFAULT 'N'
1391 , p_gl_transfer_flag IN VARCHAR2 DEFAULT 'Y'
1392 , p_batch_commit_size IN NUMBER
1393 , x_return_status OUT NOCOPY VARCHAR2
1394 ) IS
1395
1396 CURSOR csr_application_id IS
1397 SELECT application_id
1398 FROM xla_subledgers
1399 WHERE application_id = p_application_id ;
1400
1401 C_LOCK_EVENTS_STR CONSTANT VARCHAR2(32000) :=
1402 '
1403 SELECT /*+ USE_NL(evt,xe) INDEX(xe,XLA_EVENTS_U1) */null entity_id ,
1404 evt.APPLICATION_ID ,
1405 evt.LEDGER_ID ,
1406 evt.ENTITY_CODE ,
1407 evt.REVERSAL_EVENT_ID EVENT_ID ,
1408 evt.EVENT_ID EVENT_NUMBER ,
1409 evt.REVERSAL_GL_DATE EVENT_DATE,
1410 SYSDATE TRANSACTION_DATE,
1411 ''U'' EVENT_STATUS_CODE,
1412 ''U'' PROCESS_STATUS_CODE,
1413 NVL2(xe.application_id,NVL(xe.BUDGETARY_CONTROL_FLAG,''N''),NULL) BUDGETARY_CONTROL_FLAG,
1414 evt.REVERSAL_ENTITY_ID REFERENCE_NUM_1 ,
1415 evt.REVERSAL_EVENT_ID REFERENCE_NUM_2 ,
1416 evt.process_status_code REFERENCE_CHAR_1 ,
1417 NULL REFERENCE_CHAR_2
1418 FROM XLA_REVERSE_EVENTS_INTERFACE evt
1419 ,xla_events xe
1420 WHERE evt.application_id = :1
1421 AND evt.ledger_id = :2
1422 AND evt.batch_code = :3
1423 AND evt.process_status_code IN ( ''U'',''E'')
1424 AND xe.application_id(+) = :4
1425 AND xe.event_id(+) = evt.event_id
1426 ORDER BY evt.event_id
1427 FOR UPDATE OF xe.event_id SKIP LOCKED
1428 ';
1429
1430 l_log_module VARCHAR2(240);
1431 l_proceed VARCHAR2(1) := 'N' ;
1432 l_array_event_data t_event_data ;
1433 l_batch_id NUMBER ;
1434 l_return_status_code VARCHAR2(1);
1435 TYPE t_lock_events_cur IS REF CURSOR;
1436 lock_events_cur t_lock_events_cur;
1437 l_max_event_Date DATE;
1438 l_event_count NUMBER :=0 ;
1439
1440 BEGIN
1441
1442 IF g_log_enabled THEN
1443 l_log_module := C_DEFAULT_MODULE||'.create_reversal_accounting';
1444 END IF;
1445
1446 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1447
1448 trace
1449 (p_msg => 'BEGIN of procedure create_reversal_accounting'
1450 ,p_level => C_LEVEL_PROCEDURE
1451 ,p_module =>l_log_module);
1452 trace
1453 (p_msg => 'p_application_id = '||p_application_id
1454 ,p_level => C_LEVEL_PROCEDURE
1455 ,p_module =>l_log_module);
1456
1457 trace
1458 (p_msg => 'p_batch_code = '||p_batch_code
1459 ,p_level => C_LEVEL_PROCEDURE
1460 ,p_module =>l_log_module);
1461
1462 trace
1463 (p_msg => 'p_ledger_id = '||p_ledger_id
1464 ,p_level => C_LEVEL_PROCEDURE
1465 ,p_module =>l_log_module);
1466
1467 trace
1468 (p_msg => 'p_budgetary_control_flag = '||p_budgetary_control_flag
1469 ,p_level => C_LEVEL_PROCEDURE
1470 ,p_module =>l_log_module);
1471
1472 trace
1473 (p_msg => 'p_gl_transfer_flag = '||p_gl_transfer_flag
1474 ,p_level => C_LEVEL_PROCEDURE
1475 ,p_module =>l_log_module);
1476
1477 trace
1478 (p_msg => 'p_batch_commit_size = '||p_batch_commit_size
1479 ,p_level => C_LEVEL_PROCEDURE
1480 ,p_module =>l_log_module);
1481 END IF;
1482
1483 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1484
1485 IF p_application_id IS NULL THEN
1486 x_return_status := FND_API.G_RET_STS_ERROR ;
1487 xla_exceptions_pkg.raise_message
1488 ( p_appli_s_name => 'XLA'
1489 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1490 ,p_token_1 => 'ERROR'
1491 ,p_value_1 => 'Application ID has an invalid value. It cannot have a NULL value.'
1492 );
1493 END IF;
1494
1495 IF p_batch_code IS NULL THEN
1496 x_return_status := FND_API.G_RET_STS_ERROR ;
1497 xla_exceptions_pkg.raise_message
1498 ( p_appli_s_name => 'XLA'
1499 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1500 ,p_token_1 => 'ERROR'
1501 ,p_value_1 => 'Batch Code has an invalid value. It cannot have a NULL value.'
1502 );
1503 END IF;
1504
1505 IF LENGTHB(p_batch_code) > 30 THEN
1506 x_return_status := FND_API.G_RET_STS_ERROR ;
1507 xla_exceptions_pkg.raise_message
1508 ( p_appli_s_name => 'XLA'
1509 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1510 ,p_token_1 => 'ERROR'
1511 ,p_value_1 => 'Batch Code has an invalid value. It cannot have more than 30 characters.'
1512 );
1513 END IF;
1514
1515 IF p_ledger_id IS NULL THEN
1516 x_return_status := FND_API.G_RET_STS_ERROR ;
1517 xla_exceptions_pkg.raise_message
1518 ( p_appli_s_name => 'XLA'
1519 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1520 ,p_token_1 => 'ERROR'
1521 ,p_value_1 => 'Ledger ID has an invalid value. It cannot have a NULL value.'
1522 );
1523 END IF;
1524
1525 IF p_budgetary_control_flag IS NULL or p_budgetary_control_flag NOT IN ('Y','N') THEN
1526 x_return_status := FND_API.G_RET_STS_ERROR ;
1527 xla_exceptions_pkg.raise_message
1528 ( p_appli_s_name => 'XLA'
1529 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1530 ,p_token_1 => 'ERROR'
1531 ,p_value_1 => 'Budgetary Control Flag has an invalid value. It can have either Y or N as valid values.'
1532 );
1533 END IF;
1534
1535 IF p_budgetary_control_flag IN ('Y') THEN
1536 x_return_status := FND_API.G_RET_STS_ERROR ;
1537 xla_exceptions_pkg.raise_message
1538 ( p_appli_s_name => 'XLA'
1539 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1540 ,p_token_1 => 'ERROR'
1541 ,p_value_1 => 'Budgetary Control is not supported with this API.'
1542 );
1543 END IF;
1544
1545 IF p_gl_transfer_flag IS NULL or p_gl_transfer_flag NOT IN ('Y','N') THEN
1546 x_return_status := FND_API.G_RET_STS_ERROR ;
1547 xla_exceptions_pkg.raise_message
1548 ( p_appli_s_name => 'XLA'
1549 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1550 ,p_token_1 => 'ERROR'
1551 ,p_value_1 => 'GL Transfer Flag has an invalid value. It can have either Y or N as valid values.'
1552 );
1553 END IF;
1554
1555 g_application_id := NULL ;
1556
1557 FOR c1 IN csr_application_id
1558 LOOP
1559 g_application_id := c1.application_id ;
1560 END LOOP;
1561
1562 IF g_application_id IS NULL
1563 THEN
1564 x_return_status := FND_API.G_RET_STS_ERROR ;
1565 xla_exceptions_pkg.raise_message
1566 (p_appli_s_name => 'XLA'
1567 ,p_msg_name => 'XLA_MJE_INVALID_APP_ID'
1568 ,p_token_1 => 'APPLICATION_ID'
1569 ,p_value_1 => p_application_id
1570 );
1571 END IF;
1572
1573 IF ( NVL(p_batch_commit_size,0) <= 2000 )
1574 THEN
1575 g_batch_commit_size := 2000 ;
1576 ELSE
1577 g_batch_commit_size := p_batch_commit_size ;
1578 END IF;
1579
1580 g_batch_code := p_batch_code ;
1581 g_ledger_id := p_ledger_id ;
1582 g_gl_transfer_flag := p_gl_transfer_flag ;
1583 g_manual_entity_id := NULL ;
1584 g_error_source := 'XLA_BULK_REVERSAL_API';
1585 g_event_number := 0 ;
1586 g_initialized := 'N' ;
1587
1588 SELECT XLA_ACCOUNTING_BATCHES_S.NEXTVAL
1589 INTO g_accounting_batch_id
1590 FROM DUAL ;
1591
1592 g_last_updated_by := nvl(xla_environment_pkg.g_usr_id,-1);
1593 g_last_update_login := nvl(xla_environment_pkg.g_login_id,-1);
1594
1595 ----------------------------------------------------------------------------
1596 -- Following sets the Security Context for the execution. This enables the
1597 -- event API to respect the transaction security.
1598 ----------------------------------------------------------------------------
1599 xla_security_pkg.set_security_context(p_application_id);
1600
1601 XLA_ACCOUNTING_ERR_PKG.initialize
1602 (p_client_id => NULL
1603 ,p_error_limit => NULL
1604 ,p_error_source => g_error_source
1605 ,p_request_id => -1
1606 ,p_application_id => g_application_id );
1607
1608 OPEN lock_events_cur for C_LOCK_EVENTS_STR
1609 USING p_application_id
1610 , p_ledger_id
1611 , p_batch_code
1612 , p_application_id;
1613 LOOP
1614 fetch lock_events_cur bulk collect into l_array_event_data limit g_batch_commit_size ;
1615 EXIT WHEN l_array_event_data.event_number.COUNT = 0;
1616
1617 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1618 trace
1619 (p_msg => 'l_array_event_data.COUNT = '|| l_array_event_data.event_number.COUNT
1620 ,p_level => C_LEVEL_STATEMENT
1621 ,p_module => l_log_module);
1622 END IF;
1623 l_event_count := l_event_count + l_array_event_data.event_number.COUNT;
1624 process_events(l_array_event_data , l_return_status_code) ;
1625
1626 IF ( l_return_status_code <> FND_API.G_RET_STS_SUCCESS ) THEN
1627 x_return_status := l_return_status_code;
1628 END IF ;
1629 END LOOP;
1630
1631 CLOSE lock_events_cur;
1632
1633 IF l_event_count = 0 THEN
1634 x_return_status := FND_API.G_RET_STS_ERROR ;
1635 xla_exceptions_pkg.raise_message
1636 ( p_appli_s_name => 'XLA'
1637 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1638 ,p_token_1 => 'ERROR'
1639 ,p_value_1 => 'There are no events in the Interface to process for the given input parameters: '||
1640 ' p_application_id => ' || p_application_id ||
1641 ' p_batch_code => '|| p_batch_code ||
1642 ' p_ledger_id => '|| p_ledger_id
1643 );
1644 END IF;
1645
1646 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1647 trace
1648 (p_msg => 'END procedure create_reversal_accounting'
1649 ,p_level => C_LEVEL_PROCEDURE
1650 ,p_module =>l_log_module);
1651
1652 END IF;
1653
1654 EXCEPTION
1655 WHEN xla_exceptions_pkg.application_exception THEN
1656 ROLLBACK;
1657 IF lock_events_cur%ISOPEN THEN
1658 CLOSE lock_events_cur;
1659 END IF;
1660 x_return_status := FND_API.G_RET_STS_ERROR ;
1661 RAISE;
1662 WHEN OTHERS THEN
1663 ROLLBACK;
1664 IF lock_events_cur%ISOPEN THEN
1665 CLOSE lock_events_cur;
1666 END IF;
1667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1668 xla_exceptions_pkg.raise_message
1669 (p_location => 'xla_reverse_events_pvt_pkg.create_reversal_accounting');
1670
1671 END create_reversal_accounting ;
1672
1673
1674 --+==========================================================================+
1675 --| PUBLIC PROCEDURE |
1676 --| Delete_Incomplete_Reversal |
1677 --| Would need the following parameters as input |
1678 --| i)P_APPLICATION_ID : Application ID of the subledger from which |
1679 --| accounting needs to be Reversed. |
1680 --| ii)P_BATCH_CODE : Batch Name of List of the Events for which |
1681 --| accounting needs to be Reversed. |
1682 --| iii)P_LEDGER_ID : Ledger ID of the Transaction to which Events |
1683 --| belongs to |
1684 --| iv)P_DELETE_FLAG : DEFAULT of "N". Determines whether ERRORED rows|
1685 --| need to be DELETE from the INTERFACE Table after|
1686 --| the invalid accounting is deleted. |
1687 --| v)P_BATCH_COMMIT_SIZE: Minimum Batch Commit Size is 2000. Pass value |
1688 --| when more no of events need to be processed |
1689 --| vi)x_return_status : S is SUCCESS , E is Error , U is Unexpected |
1690 --| Error |
1691 --+==========================================================================+
1692 PROCEDURE delete_incomplete_reversal( p_application_id IN NUMBER
1693 , p_batch_code IN VARCHAR2
1694 , p_ledger_id IN NUMBER
1695 , p_delete_flag IN VARCHAR2 DEFAULT 'N'
1696 , p_batch_commit_size IN NUMBER
1697 , x_return_status OUT NOCOPY VARCHAR2
1698 ) IS
1699
1700 CURSOR csr_application_id IS
1701 SELECT application_id
1702 FROM xla_subledgers
1703 WHERE application_id = p_application_id ;
1704
1705 -- This cursor will check
1706 -- 1. the application_id, entity_code, event_id is not null
1707 -- 2. the event_status_code cannot be 'P'
1708 -- 4. application_id is populated and all equals p_application_id
1709 CURSOR csr_manual_processed_events(app_id NUMBER) is
1710 SELECT /*+use_nl(xeg,xe,xte) INDEX(xe,XLA_EVENTS_U1) INDEX(xte,XLA_TRANSACTION_ENTITIES_U1)*/
1711 xe.event_status_code ,
1712 xe.event_id ,
1713 xte.entity_code
1714 FROM xla_events_gt xeg, xla_events xe, xla_transaction_entities_upg xte
1715 WHERE xeg.application_id = xe.application_id (+)
1716 AND xeg.event_id = xe.event_id (+)
1717 AND xe.entity_id = xte.entity_id (+)
1718 AND xte.application_id(+) = app_id
1719 AND xe.application_id(+) = app_id
1720 AND xeg.event_id IS NOT NULL
1721 AND ( xe.event_status_code not in ('I', 'N', 'U')
1722 OR xte.entity_code is null
1723 OR xe.event_id is null
1724 OR xte.entity_code <> C_TYPE_MANUAL );
1725
1726 C_LOCK_DEL_EVENTS_STR CONSTANT VARCHAR2(32000) :=
1727 '
1728 SELECT intf.reversal_entity_id entity_id
1729 ,intf.application_id
1730 ,intf.ledger_id
1731 ,NULL entity_code
1732 ,intf.reversal_event_id event_id
1733 ,intf.reversal_event_id event_number
1734 ,intf.reversal_gl_date event_date
1735 ,intf.reversal_gl_date transaction_date
1736 ,''U'' event_status_code
1737 ,intf.process_status_code
1738 ,''N'' budgetary_control_flag
1739 ,intf.reversal_entity_id reference_num_1
1740 ,intf.reversal_event_id reference_num_2
1741 ,intf.process_status_code reference_char_1
1742 ,intf.rowid reference_char_2
1743 FROM xla_reverse_events_interface intf
1744 WHERE intf.batch_code = :1
1745 AND intf.application_id = :2
1746 AND intf.ledger_id = :3
1747 AND intf.process_status_code IN( ''U'', ''E'' )
1748 ORDER BY intf.reversal_event_id
1749 FOR UPDATE OF intf.reversal_event_id SKIP LOCKED
1750 ';
1751
1752 l_array_del_event_data t_event_data ;
1753 TYPE t_lock_del_events_cur IS REF CURSOR;
1754 lock_del_events_cur t_lock_del_events_cur;
1755 l_log_module VARCHAR2(240);
1756 l_rowcount_gt NUMBER;
1757 l_delete_count NUMBER;
1758 l_deleted_count NUMBER;
1759 l_iteration_count NUMBER;
1760 l_temp NUMBER;
1761
1762 BEGIN
1763
1764 IF g_log_enabled THEN
1765 l_log_module := C_DEFAULT_MODULE||'.delete_incomplete_reversal';
1766 END IF;
1767
1768 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1769 trace
1770 (p_msg => 'BEGIN of procedure delete_incomplete_reversal'
1771 ,p_level => C_LEVEL_PROCEDURE
1772 ,p_module =>l_log_module);
1773
1774 trace
1775 (p_msg => 'p_application_id = '||p_application_id
1776 ,p_level => C_LEVEL_PROCEDURE
1777 ,p_module =>l_log_module);
1778
1779 trace
1780 (p_msg => 'p_batch_code = '||p_batch_code
1781 ,p_level => C_LEVEL_PROCEDURE
1782 ,p_module =>l_log_module);
1783
1784 trace
1785 (p_msg => 'p_ledger_id = '||p_ledger_id
1786 ,p_level => C_LEVEL_PROCEDURE
1787 ,p_module =>l_log_module);
1788
1789 trace
1790 (p_msg => 'p_delete_flag = '||p_delete_flag
1791 ,p_level => C_LEVEL_PROCEDURE
1792 ,p_module =>l_log_module);
1793
1794 trace
1795 (p_msg => 'p_batch_commit_size = '||p_batch_commit_size
1796 ,p_level => C_LEVEL_PROCEDURE
1797 ,p_module =>l_log_module);
1798
1799 END IF;
1800
1801 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1802
1803 IF p_application_id IS NULL THEN
1804 x_return_status := FND_API.G_RET_STS_ERROR ;
1805 xla_exceptions_pkg.raise_message
1806 ( p_appli_s_name => 'XLA'
1807 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1808 ,p_token_1 => 'ERROR'
1809 ,p_value_1 => 'Application ID has an invalid value. It cannot have a NULL value.'
1810 );
1811 END IF;
1812
1813 IF p_batch_code IS NULL THEN
1814 x_return_status := FND_API.G_RET_STS_ERROR ;
1815 xla_exceptions_pkg.raise_message
1816 ( p_appli_s_name => 'XLA'
1817 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1818 ,p_token_1 => 'ERROR'
1819 ,p_value_1 => 'Batch Code has an invalid value. It cannot have a NULL value.'
1820 );
1821 END IF;
1822
1823 IF LENGTHB(p_batch_code) > 30 THEN
1824 x_return_status := FND_API.G_RET_STS_ERROR ;
1825 xla_exceptions_pkg.raise_message
1826 ( p_appli_s_name => 'XLA'
1827 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1828 ,p_token_1 => 'ERROR'
1829 ,p_value_1 => 'Batch Code has an invalid value. It cannot have more than 30 characters.'
1830 );
1831 END IF;
1832
1833 IF p_ledger_id IS NULL THEN
1834 x_return_status := FND_API.G_RET_STS_ERROR ;
1835 xla_exceptions_pkg.raise_message
1836 ( p_appli_s_name => 'XLA'
1837 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1838 ,p_token_1 => 'ERROR'
1839 ,p_value_1 => 'Ledger ID has an invalid value. It cannot have a NULL value.'
1840 );
1841 END IF;
1842
1843 IF p_delete_flag IS NULL or p_delete_flag NOT IN ('Y','N') THEN
1844 x_return_status := FND_API.G_RET_STS_ERROR ;
1845 xla_exceptions_pkg.raise_message
1846 ( p_appli_s_name => 'XLA'
1847 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1848 ,p_token_1 => 'ERROR'
1849 ,p_value_1 => 'Delete Flag has an invalid value. It can have either Y or N as valid values.'
1850 );
1851 END IF;
1852
1853 g_application_id := NULL ;
1854
1855 FOR c1 IN csr_application_id
1856 LOOP
1857 g_application_id := c1.application_id ;
1858 END LOOP;
1859
1860 IF g_application_id IS NULL
1861 THEN
1862 x_return_status := FND_API.G_RET_STS_ERROR ;
1863 xla_exceptions_pkg.raise_message
1864 (p_appli_s_name => 'XLA'
1865 ,p_msg_name => 'XLA_MJE_INVALID_APP_ID'
1866 ,p_token_1 => 'APPLICATION_ID'
1867 ,p_value_1 => p_application_id
1868 );
1869 END IF;
1870
1871 IF ( NVL(p_batch_commit_size,0) <= 2000 )
1872 THEN
1873 g_batch_commit_size := 2000 ;
1874 ELSE
1875 g_batch_commit_size := p_batch_commit_size ;
1876 END IF;
1877
1878 g_error_source := 'XLA_BULK_REVERSAL_API';
1879 g_batch_code := p_batch_code ;
1880 g_ledger_id := p_ledger_id ;
1881
1882 ----------------------------------------------------------------------------
1883 -- Following sets the Security Context for the execution. This enables the
1884 -- event API to respect the transaction security.
1885 ----------------------------------------------------------------------------
1886 xla_security_pkg.set_security_context(p_application_id);
1887
1888 l_iteration_count := 0;
1889 OPEN lock_del_events_cur for C_LOCK_DEL_EVENTS_STR
1890 USING p_batch_code
1891 , p_application_id
1892 , p_ledger_id ;
1893 LOOP
1894 fetch lock_del_events_cur bulk collect into l_array_del_event_data LIMIT g_batch_commit_size ;
1895 EXIT WHEN l_array_del_event_data.event_number.COUNT = 0;
1896
1897 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1898 trace
1899 (p_msg => 'l_array_event_data.COUNT = '|| l_array_del_event_data.application_id.COUNT
1900 ,p_level => C_LEVEL_STATEMENT
1901 ,p_module => l_log_module);
1902 END IF;
1903 l_iteration_count := l_iteration_count + 1 ;
1904
1905 FORALL i IN 1..l_array_del_event_data.application_id.COUNT
1906 INSERT INTO xla_events_gt
1907 (entity_id
1908 ,application_id
1909 ,ledger_id
1910 ,entity_code
1911 ,event_id
1912 ,event_number
1913 ,event_date
1914 ,transaction_date
1915 ,event_status_code
1916 ,process_status_code
1917 ,reference_num_1
1918 ,reference_num_2
1919 ,reference_char_1
1920 ,REFERENCE_CHAR_2
1921 )
1922 VALUES (
1923 l_array_del_event_data.entity_id(i)
1924 ,l_array_del_event_data.application_id(i)
1925 ,l_array_del_event_data.ledger_id(i)
1926 ,C_TYPE_MANUAL -- entity_code
1927 ,l_array_del_event_data.event_id(i)
1928 ,l_array_del_event_data.event_number(i)
1929 ,l_array_del_event_data.event_date(i)
1930 ,l_array_del_event_data.transaction_date(i)
1931 ,l_array_del_event_data.event_status_code(i)
1932 ,l_array_del_event_data.process_status_code(i)
1933 ,l_array_del_event_data.reference_num_1(i)
1934 ,l_array_del_event_data.reference_num_2(i)
1935 ,l_array_del_event_data.reference_char_1(i)
1936 ,l_array_del_event_data.reference_char_2(i)
1937 );
1938
1939 l_rowcount_gt := l_array_del_event_data.application_id.COUNT ;
1940
1941 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1942 trace
1943 (p_msg => 'Rows Inserted into XLA_EVENTS_GT' || l_rowcount_gt
1944 ,p_level => C_LEVEL_PROCEDURE
1945 ,p_module =>l_log_module);
1946 END IF;
1947
1948 ----------------------------------------------------------------------------
1949 -- Immediately exit if the table is empty
1950 -- the l_rowcount_gt is also useful to find if there is invalid data
1951 ----------------------------------------------------------------------------
1952 if(l_rowcount_gt = 0) then
1953 return;
1954 end if;
1955
1956 FOR i IN csr_manual_processed_events(p_application_id)
1957 LOOP
1958
1959 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1960 trace
1961 (p_msg => 'Error for Event_ID: ' || i.event_id
1962 ,p_level => C_LEVEL_PROCEDURE
1963 ,p_module =>l_log_module);
1964 END IF;
1965 IF ( i.event_status_code not in ('I', 'N', 'U') ) THEN
1966 x_return_status := FND_API.G_RET_STS_ERROR ;
1967 xla_exceptions_pkg.raise_message
1968 ( p_appli_s_name => 'XLA'
1969 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1970 ,p_token_1 => 'ERROR'
1971 ,p_value_1 => 'The event('|| i.event_id||') to be deleted must be in status I, N or U.Error in Iteration Number '||
1972 l_iteration_count||'.'
1973 );
1974 END IF;
1975
1976 IF ( NVL(i.entity_code,' ') <> C_TYPE_MANUAL ) THEN
1977 x_return_status := FND_API.G_RET_STS_ERROR ;
1978 xla_exceptions_pkg.raise_message
1979 ( p_appli_s_name => 'XLA'
1980 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
1981 ,p_token_1 => 'ERROR'
1982 ,p_value_1 => 'Entity code for reversal event('||i.event_id||')is not MANUAL. This API can be called to delete event for MANUAL entity.'||
1983 'Error in Iteration Number '||l_iteration_count||'.'
1984 );
1985 END IF;
1986
1987 END LOOP;
1988
1989 delete_incomplete_journals ;
1990
1991 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1992 trace
1993 (p_msg => 'returned from procedure delete_incomplete_journals'
1994 ,p_level => C_LEVEL_PROCEDURE
1995 ,p_module =>l_log_module);
1996 END IF;
1997
1998 SELECT COUNT(1)
1999 INTO l_delete_count
2000 FROM xla_events_gt
2001 WHERE reference_num_2 IS NOT NULL ;
2002
2003 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2004 trace
2005 (p_msg => 'Rows to be deleted from Events table: ' || l_delete_count
2006 ,p_level => C_LEVEL_PROCEDURE
2007 ,p_module =>l_log_module);
2008 END IF;
2009
2010 DELETE
2011 FROM xla_events xe
2012 WHERE application_id = g_application_id
2013 AND event_id IN (SELECT reference_num_2
2014 FROM xla_events_gt
2015 WHERE reference_char_1 = 'E'
2016 AND reference_num_2 IS NOT NULL )
2017 AND NOT EXISTS
2018 ( SELECT 1
2019 FROM xla_ae_headers xah
2020 WHERE xah.application_id = xe.application_id
2021 AND xah.event_id = xe.event_id
2022 ) ;
2023
2024 l_deleted_count := SQL%ROWCOUNT ;
2025
2026 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2027 trace
2028 (p_msg => 'Number of events deleted = '||l_deleted_count
2029 ,p_level => C_LEVEL_STATEMENT
2030 ,p_module => l_log_module);
2031 END IF;
2032
2033 IF l_deleted_count <> l_delete_count
2034 THEN
2035 x_return_status := FND_API.G_RET_STS_ERROR ;
2036 ROLLBACK;
2037 xla_exceptions_pkg.raise_message
2038 (p_appli_s_name => 'XLA'
2039 ,p_msg_name => 'XLA_BULK_REV_COMMON_ERROR'
2040 ,p_token_1 => 'ERROR'
2041 ,p_value_1 => 'Number of Events Deleted('||l_deleted_count||') is not same as the Events eligible for delete('||
2042 l_delete_count||') in the interface table for the iteration number '||l_iteration_count||'.'
2043 );
2044 END IF;
2045
2046
2047 IF p_delete_flag = 'Y' THEN
2048
2049 DELETE /*+ rowid(xet) */
2050 FROM XLA_REVERSE_EVENTS_INTERFACE xet
2051 WHERE xet.rowid IN ( SELECT reference_char_2
2052 FROM xla_events_gt
2053 ) ;
2054 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2055 trace
2056 (p_msg => 'Number of rows DELETE FROM Interface = '|| SQL%ROWCOUNT
2057 ,p_level => C_LEVEL_STATEMENT
2058 ,p_module => l_log_module);
2059 END IF;
2060 ELSE
2061
2062 UPDATE /*+ rowid(xet) */XLA_REVERSE_EVENTS_INTERFACE xet
2063 SET xet.REVERSAL_EVENT_ID = NULL
2064 ,xet.REVERSAL_ENTITY_ID = NULL
2065 ,xet.ACCOUNTING_BATCH_ID = NULL
2066 ,xet.process_status_code = 'U'
2067 WHERE xet.rowid IN ( SELECT reference_char_2
2068 FROM xla_events_gt
2069 ) ;
2070 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2071 trace
2072 (p_msg => 'Number of rows UPDATED in Interface = '|| SQL%ROWCOUNT
2073 ,p_level => C_LEVEL_STATEMENT
2074 ,p_module => l_log_module);
2075 END IF;
2076
2077 END IF;
2078
2079 DELETE FROM xla_events_gt;
2080
2081 END LOOP;
2082
2083 CLOSE lock_del_events_cur;
2084
2085 COMMIT;
2086 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2087 trace
2088 (p_msg => 'Commit Executed'
2089 ,p_level => C_LEVEL_STATEMENT
2090 ,p_module => l_log_module);
2091 END IF;
2092
2093 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2094 trace
2095 (p_msg => 'End of delete_incomplete_reversal '
2096 ,p_level => C_LEVEL_STATEMENT
2097 ,p_module => l_log_module);
2098 END IF;
2099
2100
2101 EXCEPTION
2102 WHEN xla_exceptions_pkg.application_exception THEN
2103 ROLLBACK;
2104 x_return_status := FND_API.G_RET_STS_ERROR ;
2105 IF lock_del_events_cur%ISOPEN THEN
2106 CLOSE lock_del_events_cur;
2107 END IF;
2108 RAISE;
2109 WHEN OTHERS THEN
2110 ROLLBACK;
2111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2112 IF lock_del_events_cur%ISOPEN THEN
2113 CLOSE lock_del_events_cur;
2114 END IF;
2115 xla_exceptions_pkg.raise_message
2116 (p_location => 'xla_reverse_events_pvt_pkg.delete_incomplete_reversal');
2117
2118 END delete_incomplete_reversal ;
2119
2120 BEGIN
2121
2122 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2123 g_log_enabled := fnd_log.test
2124 (log_level => g_log_level
2125 ,MODULE => C_DEFAULT_MODULE);
2126
2127 IF NOT g_log_enabled THEN
2128 g_log_level := C_LEVEL_LOG_DISABLED;
2129 END IF;
2130
2131
2132 END xla_reverse_events_pvt_pkg;