[Home] [Help]
PACKAGE BODY: APPS.XLA_FSAH_INT_PVT
Source
1 PACKAGE BODY xla_fsah_int_pvt AS
2 /* $Header: xlafsipvt.pkb 120.27.12010000.2 2009/08/05 11:41:49 karamakr noship $ */
3 /*================================================================================+
4 | FILENAME |
5 | xlafsipvt.pkb |
6 | |
7 | PACKAGE NAME |
8 | xla_fsah_int_pvt |
9 | |
10 | DESCRIPTION |
11 | This is a XLA private package, which contains all the fucntions and |
12 | procedures which required to update and reprocess the successfull and |
13 | non-succesfull transactions |
14 | and tranfermations to people soft General Ledger. |
15 | |
16 | Also API Return The new group_id for the further Successfull Update |
17 | |
18 | |
19 | Note: |
20 | - the APIs do not execute any COMMIT or ROLLBACK. |
21 | |
22 | HISTORY |
23 | ------- |
24 | 26-Jun-08 JAGAN KODURI |
25 | 30-Dec-08 JAGAN KODURI Updating the Original Event status to 'I' and 'U' | | |
26 | |
27 | PARAMETER DESCRIPTION |
28 | --------------------- |
29 | |
30 | SET_GROUP_ID |
31 | ------------ |
32 | p_ledger_short_name :in parameter |
33 | |
34 | SET_TRANSFER_STATUS |
35 | -------------------- |
36 | p_group_id :in parameter (xla_fsah_int_pvt.group_id) |
37 | p_batch_status :in parameter (F/S) |
38 | p_api_version :in parameter (Default API version 1.0) |
39 | p_return_status :out parameter (Use to Return Process Successfull Status) |
40 | p_msg_data :out parameter (Default API out to Error count) |
41 | p_msg_count :out parameter (return New Group Id for New Process Update) |
42 | |
43 +================================================================================*/
44
45 --==================================================================================
46 -- global declaration
47 --==================================================================================
48 TYPE t_je_info IS RECORD (
49 header_id INTEGER,
50 ledger_id INTEGER,
51 legal_entity_id INTEGER,
52 application_id INTEGER,
53 entity_id INTEGER,
54 event_id INTEGER,
55 gl_date DATE,
56 status_code VARCHAR2 (30),
57 type_code VARCHAR2 (30),
58 description VARCHAR2 (2400),
59 balance_type_code VARCHAR2 (30),
60 budget_version_id INTEGER,
61 reference_date DATE,
62 funds_status_code VARCHAR2 (30),
63 je_category_name VARCHAR2 (80),
64 packet_id INTEGER,
65 amb_context_code VARCHAR2 (30),
66 event_type_code VARCHAR2 (30),
67 completed_date DATE,
68 gl_transfer_status_code VARCHAR2 (30),
69 accounting_batch_id INTEGER,
70 period_name VARCHAR2 (15),
71 product_rule_code VARCHAR2 (30),
72 product_rule_type_code VARCHAR2 (30),
73 product_rule_version VARCHAR2 (30),
74 gl_transfer_date DATE,
75 doc_sequence_id INTEGER,
76 doc_sequence_value VARCHAR2 (240),
77 close_acct_seq_version_id INTEGER,
78 close_acct_seq_value VARCHAR2 (240),
79 close_acct_seq_assign_id INTEGER,
80 completion_acct_seq_version_id INTEGER,
81 completion_acct_seq_value VARCHAR2 (240),
82 completion_acct_seq_assign_id INTEGER,
83 accrual_reversal_flag VARCHAR2 (1),
84 budgetary_control_flag VARCHAR2 (1),
85 attribute_category VARCHAR2 (30),
86 attribute1 VARCHAR2 (150),
87 attribute2 VARCHAR2 (150),
88 attribute3 VARCHAR2 (150),
89 attribute4 VARCHAR2 (150),
90 attribute5 VARCHAR2 (150),
91 attribute6 VARCHAR2 (150),
92 attribute7 VARCHAR2 (150),
93 attribute8 VARCHAR2 (150),
94 attribute9 VARCHAR2 (150),
95 attribute10 VARCHAR2 (150),
96 attribute11 VARCHAR2 (150),
97 attribute12 VARCHAR2 (150),
98 attribute13 VARCHAR2 (150),
99 attribute14 VARCHAR2 (150),
100 attribute15 VARCHAR2 (150)
101 );
102
103 --=============================================================================
104 -- *********** LOCAL TRACE ROUTINE **********
105 --=============================================================================
106 TYPE t_array_integer IS TABLE OF INTEGER
107 INDEX BY BINARY_INTEGER;
108
109 TYPE t_array_char1 IS TABLE OF VARCHAR2 (1)
110 INDEX BY BINARY_INTEGER;
111
112 TYPE t_array_char30 IS TABLE OF VARCHAR2 (30)
113 INDEX BY BINARY_INTEGER;
114
115 c_level_statement CONSTANT NUMBER := fnd_log.level_statement;
116 c_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
117 c_level_event CONSTANT NUMBER := fnd_log.level_event;
118 c_level_exception CONSTANT NUMBER := fnd_log.level_exception;
119 c_level_error CONSTANT NUMBER := fnd_log.level_error;
120 c_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
121 c_level_log_disabled CONSTANT NUMBER := 99;
122 c_default_module CONSTANT VARCHAR2 (240)
123 := 'XLA.PLSQL.XLA_FSAH_INT_PVT';
124 --=============================================================================
125 -- *********** PRIVATE GLOBAL CONSTANT **********
126 --=============================================================================
127 c_status_final_code CONSTANT VARCHAR2 (1) := 'F';
128 c_entity_type_code_manual CONSTANT VARCHAR2 (30) := 'MANUAL';
129 c_reversal_switch_dr_cr CONSTANT VARCHAR2 (30) := 'SIDE';
130 c_event_type_code_manual CONSTANT VARCHAR2 (30) := 'MANUAL';
131 c_event_class_code_manual CONSTANT VARCHAR2 (30) := 'MANUAL';
132 c_gl_application_id CONSTANT INTEGER := 101;
133 c_ae_status_incomplete CONSTANT VARCHAR2 (30) := 'N';
134 c_gl_transfer_mode_no CONSTANT VARCHAR2 (30) := 'N';
135 g_log_level NUMBER;
136 g_log_enabled BOOLEAN;
137 g_msg_mode VARCHAR2 (200)
138 DEFAULT xla_exceptions_pkg.c_standard_message;
139
140 ----------------------------------------------------------------------------
141 -- FOLLOWING IS FOR FND LOG.
142 ----------------------------------------------------------------------------
143 PROCEDURE TRACE (p_msg IN VARCHAR2, p_module IN VARCHAR2, p_level IN NUMBER)
144 IS
145 BEGIN
146 IF (p_msg IS NULL AND p_level >= g_log_level)
147 THEN
148 fnd_log.MESSAGE (p_level, p_module);
149 ELSIF p_level >= g_log_level
150 THEN
151 fnd_log.STRING (p_level, p_module, p_msg);
152 END IF;
153 EXCEPTION
154 WHEN xla_exceptions_pkg.application_exception
155 THEN
156 RAISE;
157 WHEN OTHERS
158 THEN
159 xla_exceptions_pkg.raise_message (p_location => 'XLA_FSAH_INT_PVT.TRACE'
160 );
161 END TRACE;
162
163 --=============================================================================
164 --
165 -- NAME : GET_HEADER_INFO
166 -- DESCRIPTION : RETRIEVE HEADER INFORMATION.
167 -- RETURN : T_JE_INFO
168 --
169 --=============================================================================
170 FUNCTION get_header_info (
171 p_ae_header_id IN INTEGER,
172 p_application_id IN INTEGER,
173 p_msg_mode IN VARCHAR2
174 )
175 RETURN t_je_info
176 IS
177 CURSOR c_header
178 IS
179 SELECT xah.ae_header_id, xah.ledger_id, xte.legal_entity_id,
180 xah.application_id, xah.entity_id, xah.event_id,
181 xah.accounting_date, xah.accounting_entry_status_code,
182 xah.accounting_entry_type_code, xah.description,
183 xah.balance_type_code, xah.budget_version_id,
184 xah.reference_date, xah.funds_status_code,
185 xah.je_category_name, xah.packet_id,
186 xah.amb_context_code, xah.event_type_code,
187 xah.completed_date, xah.gl_transfer_status_code,
188 xah.accounting_batch_id, xah.period_name,
189 xah.product_rule_code, xah.product_rule_type_code,
190 xah.product_rule_version, xah.gl_transfer_date,
191 xah.doc_sequence_id, xah.doc_sequence_value,
192 xah.close_acct_seq_version_id, xah.close_acct_seq_value,
193 xah.close_acct_seq_assign_id,
194 xah.completion_acct_seq_version_id,
195 xah.completion_acct_seq_value,
196 xah.completion_acct_seq_assign_id,
197 NVL (xah.accrual_reversal_flag, 'N'),
198 xe.budgetary_control_flag, xah.attribute_category,
199 xah.attribute1, xah.attribute2, xah.attribute3,
200 xah.attribute4, xah.attribute5, xah.attribute6,
201 xah.attribute7, xah.attribute8, xah.attribute9,
202 xah.attribute10, xah.attribute11, xah.attribute12,
203 xah.attribute13, xah.attribute14, xah.attribute15
204 FROM xla_ae_headers xah,
205 xla_events xe,
206 xla_transaction_entities xte
207 WHERE xte.entity_id = xah.entity_id
208 AND xte.application_id = xah.application_id
209 AND xe.event_id = xah.event_id
210 AND xe.application_id = xah.application_id
211 AND xah.ae_header_id = p_ae_header_id
212 AND xah.application_id = p_application_id
213 FOR UPDATE NOWAIT;
214
215 l_info t_je_info;
216 l_log_module VARCHAR2 (240);
217 BEGIN
218 ------FND_LOG---------
219 IF g_log_enabled
220 THEN
221 l_log_module := c_default_module || '.get_header_info';
222 END IF;
223
224 IF (c_level_procedure >= g_log_level)
225 THEN
226 TRACE (p_msg => 'BEGIN of get_header_info',
227 p_level => c_level_procedure,
228 p_module => l_log_module
229 );
230 END IF;
231
232 -----------------------
233 OPEN c_header;
234 FETCH c_header INTO l_info.header_id,
235 l_info.ledger_id,
236 l_info.legal_entity_id,
237 l_info.application_id,
238 l_info.entity_id,
239 l_info.event_id,
240 l_info.gl_date,
241 l_info.status_code,
242 l_info.type_code,
243 l_info.description,
244 l_info.balance_type_code,
245 l_info.budget_version_id,
246 l_info.reference_date,
247 l_info.funds_status_code,
248 l_info.je_category_name,
249 l_info.packet_id,
250 l_info.amb_context_code,
251 l_info.event_type_code,
252 l_info.completed_date,
253 l_info.gl_transfer_status_code,
254 l_info.accounting_batch_id,
255 l_info.period_name,
256 l_info.product_rule_code,
257 l_info.product_rule_type_code,
258 l_info.product_rule_version,
259 l_info.gl_transfer_date,
260 l_info.doc_sequence_id,
261 l_info.doc_sequence_value,
262 l_info.close_acct_seq_version_id,
263 l_info.close_acct_seq_value,
264 l_info.close_acct_seq_assign_id,
265 l_info.completion_acct_seq_version_id,
266 l_info.completion_acct_seq_value,
267 l_info.completion_acct_seq_assign_id,
268 l_info.accrual_reversal_flag,
269 l_info.budgetary_control_flag,
270 l_info.attribute_category,
271 l_info.attribute1,
272 l_info.attribute2,
273 l_info.attribute3,
274 l_info.attribute4,
275 l_info.attribute5,
276 l_info.attribute6,
277 l_info.attribute7,
278 l_info.attribute8,
279 l_info.attribute9,
280 l_info.attribute10,
281 l_info.attribute11,
282 l_info.attribute12,
283 l_info.attribute13,
284 l_info.attribute14,
285 l_info.attribute15;
286 CLOSE c_header;
287
288 IF (l_info.ledger_id IS NULL)
289 THEN
290 xla_exceptions_pkg.raise_message (p_appli_s_name => 'XLA',
291 p_msg_name => 'XLA_MJE_INVALID_HEADER_ID',
292 p_msg_mode => p_msg_mode
293 );
294 END IF;
295
296 IF (c_level_procedure >= g_log_level)
297 THEN
298 TRACE (p_msg => 'END of get_header_info',
299 p_level => c_level_procedure,
300 p_module => l_log_module
301 );
302 END IF;
303 RETURN l_info;
304 EXCEPTION
305 WHEN xla_exceptions_pkg.application_exception
306 THEN
307 IF (c_header%ISOPEN)
308 THEN
309 CLOSE c_header;
310 END IF;
311
312 RAISE;
313 WHEN OTHERS
314 THEN
315 IF (c_header%ISOPEN)
316 THEN
317 CLOSE c_header;
318 END IF;
319
320 xla_exceptions_pkg.raise_message (p_location => 'XLA_FSAH_INT_PVT.GET_HEADER_INFO'
321 );
322 END get_header_info;
323
324 ------------------------------------------------------------------------------------
325 -- Procedure (create_reversal_distr_link)
326 ------------------------------------------------------------------------------------
327 PROCEDURE create_reversal_distr_link (
328 p_application_id IN INTEGER,
329 p_ae_header_id IN INTEGER,
330 p_ref_ae_header_id IN INTEGER,
331 p_ref_event_id IN INTEGER
332 )
333 IS
334 l_ref_event_id INTEGER;
335 l_log_module VARCHAR2 (240);
336 BEGIN
337
338 IF g_log_enabled
339 THEN
340 l_log_module := c_default_module || '.create_reversal_distr_link';
341 END IF;
342
343 IF (c_level_procedure >= g_log_level)
344 THEN
345 TRACE (p_msg => 'BEGIN of create_reversal_distr_link',
346 p_level => c_level_procedure,
347 p_module => l_log_module
348 );
349 END IF;
350
351 IF p_ref_event_id IS NOT NULL
352 THEN
353 --
354 -- CALLED FROM CREATE_MRC_REVERSAL_ENTRY
355 --
356 l_ref_event_id := p_ref_event_id;
357 ELSE
358 --
359 -- CALLED FROM CREATE_REVERSAL_ENTRY
360 --
361 SELECT event_id
362 INTO l_ref_event_id
363 FROM xla_ae_headers
364 WHERE application_id = p_application_id
365 AND ae_header_id = p_ref_ae_header_id;
366 END IF;
367
368 INSERT INTO xla_distribution_links
369 (application_id, event_id, ae_header_id, ae_line_num,
370 source_distribution_type, statistical_amount,
371 ref_ae_header_id, ref_temp_line_num, merge_duplicate_code,
372 temp_line_num, ref_event_id, event_class_code,
373 event_type_code, unrounded_entered_dr,
374 unrounded_entered_cr, unrounded_accounted_dr,
375 unrounded_accounted_cr)
376 SELECT p_application_id, xah.event_id, p_ae_header_id, ae_line_num,
377 'XLA_REVERSAL' -- SOURCE DISTRIBUTION TYPE
378 ,
379 xal.statistical_amount -- STATISTICAL AMOUNT
380 ,
381 p_ref_ae_header_id -- REF AE HEADER ID
382 ,
383 ae_line_num -- REF TEMP LINE NUM
384 , 'N' -- MERGE DUPLICATE CODE
385 ,
386 -1 * ae_line_num -- TEMP LINE NUM
387 ,
388 l_ref_event_id -- REF EVENT ID
389 ,
390 c_event_class_code_manual -- EVENT CLASS CODE
391 ,
392 c_event_type_code_manual -- EVENT TYPE CODE
393 ,
394 xal.unrounded_entered_dr, xal.unrounded_entered_cr,
395 xal.unrounded_accounted_dr, xal.unrounded_accounted_cr
396 FROM xla_ae_headers xah, xla_ae_lines xal
397 WHERE xah.application_id = p_application_id
398 AND xah.ae_header_id = p_ae_header_id
399 AND xal.application_id = xah.application_id
400 AND xal.ae_header_id = xah.ae_header_id;
401
402 ---------FND_LOG---------
403 IF (c_level_procedure >= g_log_level)
404 THEN
405 TRACE (p_msg => 'END of create_reversal_distr_link',
406 p_level => c_level_procedure,
407 p_module => l_log_module
408 );
409 END IF;
410 -------------------------
411 EXCEPTION
412 WHEN xla_exceptions_pkg.application_exception
413 THEN
414 RAISE;
415 WHEN OTHERS
416 THEN
417 xla_exceptions_pkg.raise_message (p_location => 'XLA_FSAH_INT_PVT.CREATE_REVERSAL_DISTR_LINK'
418 );
419 END create_reversal_distr_link;
420
421 --=============================================================================
422 --
423 -- NAME : GET_PERIOD_NAME
424 -- DESCRIPTION : RETRIEVE THE PERIOD NAME OF AN ACCOUNTING DATE FOR A LEDGER,
425 -- AND ITS STATUS AND PERIOD TYPE.
426 --
427 --=============================================================================
428 FUNCTION get_period_name (
429 p_ledger_id IN INTEGER,
430 p_accounting_date IN DATE,
431 p_closing_status OUT NOCOPY VARCHAR2,
432 p_period_type OUT NOCOPY VARCHAR2
433 )
434 RETURN VARCHAR2
435 IS
436 CURSOR c
437 IS
438 SELECT closing_status, period_name, period_type
439 FROM gl_period_statuses
440 WHERE application_id = c_gl_application_id
441 AND ledger_id = p_ledger_id
442 AND adjustment_period_flag = 'N'
443 AND TRUNC (p_accounting_date) BETWEEN start_date AND end_date;
444
445 l_period_name VARCHAR2 (25);
446 l_log_module VARCHAR2 (240);
447 BEGIN
448
449 IF g_log_enabled
450 THEN
451 l_log_module := c_default_module || '.get_period_name';
452 END IF;
453
454 IF (c_level_procedure >= g_log_level)
455 THEN
456 TRACE (p_msg => 'BEGIN of get_period_name',
457 p_level => c_level_procedure,
458 p_module => l_log_module
459 );
460 END IF;
461
462
463 OPEN c;
464 FETCH c INTO p_closing_status, l_period_name, p_period_type;
465 CLOSE c;
466
467 IF (c_level_procedure >= g_log_level)
468 THEN
469 TRACE (p_msg => 'END of get_period_name',
470 p_level => c_level_procedure,
471 p_module => l_log_module
472 );
473 END IF;
474
475 RETURN l_period_name;
476 EXCEPTION
477 WHEN xla_exceptions_pkg.application_exception
478 THEN
479 IF (c%ISOPEN)
480 THEN
481 CLOSE c;
482 END IF;
483
484 RAISE;
485 WHEN OTHERS
486 THEN
487 IF (c%ISOPEN)
488 THEN
489 CLOSE c;
490 END IF;
491
492 xla_exceptions_pkg.raise_message (p_location => 'XLA_FSAH_INT_PVT.GET_PERIOD_NAME'
493 );
494 END get_period_name;
495
496 --=============================================================================
497 -- PROCEDURE :CREATE_REVERSAL_ENTRY
498 -- DESCRIPTION :CREATE REVERSAL ENTRY FOR A JOURNAL ENTRY
499 --
500 --=============================================================================
501 PROCEDURE create_reversal_entry (
502 p_info IN t_je_info,
503 p_reversal_method IN VARCHAR2,
504 p_gl_date IN DATE,
505 p_msg_mode IN VARCHAR2
506 DEFAULT xla_exceptions_pkg.c_standard_message,
507 p_rev_header_id OUT NOCOPY INTEGER,
508 p_rev_event_id OUT NOCOPY INTEGER
509 )
510 IS
511 TYPE t_ae_header_id IS TABLE OF xla_ae_headers.ae_header_id%TYPE;
512
513 l_event_source_info xla_events_pub_pkg.t_event_source_info;
514 l_entity_id INTEGER;
515 l_period_name VARCHAR2 (30);
516 l_closing_status VARCHAR2 (30);
517 l_validate_period INTEGER;
518 l_result INTEGER;
519 l_period_type VARCHAR2 (30);
520 l_reversal_label VARCHAR2 (240);
521 l_last_updated_by INTEGER;
522 l_last_update_login INTEGER;
523 l_log_module VARCHAR2 (240);
524 l_info t_je_info;
525 l_ae_header_id t_ae_header_id;
526 BEGIN
527 IF g_log_enabled
528 THEN
529 l_log_module := c_default_module || '.create_reversal_entry';
530 --DBMS_OUTPUT.put_line ('Begin of create_reversal_entry');
531 END IF;
532
533 IF (c_level_procedure >= g_log_level)
534 THEN
535 TRACE (p_msg => 'BEGIN of create_reversal_entry',
536 p_level => c_level_procedure,
537 p_module => l_log_module
538 );
539 END IF;
540
541
542 --DBMS_OUTPUT.put_line ('Getting the Period name ');
543
544 IF (c_level_procedure >= g_log_level)
545 THEN
546 TRACE (p_msg => 'Getting the Period name',
547 p_level => c_level_procedure,
548 p_module => l_log_module
549 );
550 END IF;
551
552 l_period_name :=
553 get_period_name (p_ledger_id => p_info.ledger_id,
554 p_accounting_date => p_gl_date,
555 p_closing_status => l_closing_status,
556 p_period_type => l_period_type
557 );
558
559 IF (l_period_name IS NULL)
560 THEN
561 --DBMS_OUTPUT.put_line ('Period name is null ');
562
563 IF (c_level_procedure >= g_log_level)
564 THEN
565 TRACE (p_msg => 'Period name is null',
566 p_level => c_level_procedure,
567 p_module => l_log_module
568 );
569 END IF;
570
571 xla_exceptions_pkg.raise_message (p_appli_s_name => 'XLA',
572 p_msg_name => 'XLA_AP_INVALID_GL_DATE',
573 p_msg_mode => p_msg_mode
574 );
575 END IF;
576
577 --
578 -- CREATE EVENT FOR THE REVERSAL ENTRY
579 --
580 l_event_source_info.application_id := p_info.application_id;
581 l_event_source_info.legal_entity_id := p_info.legal_entity_id;
582 l_event_source_info.ledger_id := p_info.ledger_id;
583 l_event_source_info.entity_type_code := c_entity_type_code_manual;
584
585 IF (c_level_procedure >= g_log_level)
586 THEN
587 TRACE (p_msg => 'Before Creating the Event for the reversal entry',
588 p_level => c_level_procedure,
589 p_module => l_log_module
590 );
591 END IF;
592
593 p_rev_event_id :=
594 xla_events_pkg.create_manual_event (p_event_source_info => l_event_source_info,
595 p_event_type_code => c_event_type_code_manual,
596 p_event_date => p_gl_date,
597 p_event_status_code => xla_events_pub_pkg.c_event_unprocessed,
598 p_process_status_code => xla_events_pkg.c_internal_unprocessed,
599 p_event_number => 1,
600 p_budgetary_control_flag => p_info.budgetary_control_flag
601 );
602 --DBMS_OUTPUT.put_line ('After Creating the Event for the reversal entry ');
603
604 IF (c_level_procedure >= g_log_level)
605 THEN
606 TRACE (p_msg => 'After Creating the Event for the reversal entry',
607 p_level => c_level_procedure,
608 p_module => l_log_module
609 );
610 END IF;
611
612 BEGIN
613 SELECT entity_id
614 INTO l_entity_id
615 FROM xla_events
616 WHERE event_id = p_rev_event_id;
617 EXCEPTION
618 WHEN OTHERS
619 THEN
620 IF (c_level_procedure >= g_log_level)
621 THEN
622 TRACE (p_msg => SQLERRM,
623 p_level => c_level_procedure,
624 p_module => l_log_module
625 );
626 END IF;
627
628 --DBMS_OUTPUT.put_line (SQLERRM);
629 END;
630
631 -- DBMS_OUTPUT.put_line ('Reversal Event Id ' || l_entity_id);
632 fnd_message.set_name ('XLA', 'XLA_MJE_LABEL_REVERSAL');
633 l_reversal_label := fnd_message.get ();
634 l_last_updated_by := NVL (xla_environment_pkg.g_usr_id, -1);
635 l_last_update_login := NVL (xla_environment_pkg.g_login_id, -1);
636
637 --
638 -- CREATE A NEW JOURNAL ENTRY HEADER
639 --
640 BEGIN
641 SELECT ae_header_id
642 BULK COLLECT INTO l_ae_header_id
643 FROM xla_ae_headers
644 WHERE event_id = p_info.event_id;
645 EXCEPTION
646 WHEN OTHERS
647 THEN
648 IF (c_level_procedure >= g_log_level)
649 THEN
650 TRACE (p_msg => SQLERRM,
651 p_level => c_level_procedure,
652 p_module => l_log_module
653 );
654 END IF;
655
656 --DBMS_OUTPUT.put_line (SQLERRM);
657 END;
658
659 FOR i IN 1 .. l_ae_header_id.COUNT
660 LOOP
661 xla_security_pkg.set_security_context (602);
662 l_info :=
663 get_header_info (l_ae_header_id (i),
664 p_info.application_id,
665 g_msg_mode
666 );
667
668 INSERT INTO xla_ae_headers
669 (ae_header_id, application_id,
670 ledger_id, entity_id, event_id,
671 event_type_code, accounting_date, period_name,
672 reference_date, balance_type_code,
673 budget_version_id, gl_transfer_status_code,
674 je_category_name, accounting_entry_status_code,
675 accounting_entry_type_code,
676 description,
677 creation_date, created_by, last_update_date,
678 last_updated_by, last_update_login,
679 accrual_reversal_flag
680 )
681 VALUES (xla_ae_headers_s.NEXTVAL, l_info.application_id,
682 l_info.ledger_id, l_entity_id, p_rev_event_id,
683 c_event_type_code_manual,trunc(p_gl_date), l_period_name,
684 l_info.reference_date, l_info.balance_type_code,
685 l_info.budget_version_id, c_gl_transfer_mode_no,
686 l_info.je_category_name, c_status_final_code,
687 l_info.type_code,
688 'DATA FIX REVERSAL ENTRY: AE_HEADER_ID OF '||l_info.header_id,
689 SYSDATE, l_last_updated_by, SYSDATE,
690 l_last_updated_by, l_last_update_login,
691 NVL (l_info.accrual_reversal_flag, 'N')
692 ) -- 4262811 ACCRUAL_REVERSAL_FLAG
693 RETURNING ae_header_id
694 INTO p_rev_header_id;
695
696 --
697 -- COPY HEADER ANALYTICAL CRITERIA FROM THE ORIGINAL ENTRY TO THE REVERSAL ENTRY
698 --
699 INSERT INTO xla_ae_header_acs
700 (ae_header_id, analytical_criterion_code,
701 analytical_criterion_type_code, amb_context_code, ac1,
702 ac2, ac3, ac4, ac5, object_version_number)
703 SELECT p_rev_header_id, analytical_criterion_code,
704 analytical_criterion_type_code, amb_context_code, ac1, ac2,
705 ac3, ac4, ac5, 1
706 FROM xla_ae_header_acs
707 WHERE ae_header_id = l_info.header_id;
708
709 --
710 -- CREATE JOURNAL ENTRY LINES FOR THE REVERSAL JOURNAL ENTRY
711 --
712 INSERT INTO xla_ae_lines
713 (application_id, ae_header_id, ae_line_num,
714 displayed_line_number, code_combination_id,
715 gl_transfer_mode_code, creation_date, created_by,
716 last_update_date, last_updated_by, last_update_login,
717 party_id, party_site_id, party_type_code, entered_dr,
718 entered_cr, accounted_dr, accounted_cr,
719 unrounded_entered_dr, unrounded_entered_cr,
720 unrounded_accounted_dr, unrounded_accounted_cr,
721 description, statistical_amount, currency_code,
722 currency_conversion_type, currency_conversion_date,
723 currency_conversion_rate, accounting_class_code,
724 jgzz_recon_ref, gl_sl_link_id,gl_sl_link_table, attribute_category,
725 encumbrance_type_id, attribute1, attribute2, attribute3,
726 attribute4, attribute5, attribute6, attribute7,
727 attribute8, attribute9, attribute10, attribute11,
728 attribute12, attribute13, attribute14, attribute15,
729 gain_or_loss_flag, ledger_id, accounting_date,
730 mpa_accrual_entry_flag)
731 SELECT application_id, p_rev_header_id, ae_line_num,
732 displayed_line_number, code_combination_id,
733 gl_transfer_mode_code, SYSDATE, l_last_updated_by, SYSDATE,
734 l_last_updated_by, l_last_update_login, party_id,
735 party_site_id, party_type_code,
736 DECODE (p_reversal_method,
737 c_reversal_switch_dr_cr, entered_cr,
738 -entered_dr
739 ),
740 DECODE (p_reversal_method,
741 c_reversal_switch_dr_cr, entered_dr,
742 -entered_cr
743 ),
744 DECODE (p_reversal_method,
745 c_reversal_switch_dr_cr, accounted_cr,
746 -accounted_dr
747 ),
748 DECODE (p_reversal_method,
749 c_reversal_switch_dr_cr, accounted_dr,
750 -accounted_cr
751 ) -- 5109240 UNROUNDED AMOUNTS
752 ,
753 DECODE (p_reversal_method,
754 c_reversal_switch_dr_cr, unrounded_entered_cr,
755 -unrounded_entered_dr
756 ),
757 DECODE (p_reversal_method,
758 c_reversal_switch_dr_cr, unrounded_entered_dr,
759 -unrounded_entered_cr
760 ),
761 DECODE (p_reversal_method,
762 c_reversal_switch_dr_cr, unrounded_accounted_cr,
763 -unrounded_accounted_dr
764 ),
765 DECODE (p_reversal_method,
766 c_reversal_switch_dr_cr, unrounded_accounted_dr,
767 -unrounded_accounted_cr
768 ),
769 'DATA FIX REVERSAL ENTRY: AE_HEADER_ID OF '||l_info.header_id, statistical_amount, currency_code,
770 currency_conversion_type, currency_conversion_date,
771 currency_conversion_rate, accounting_class_code,
772 jgzz_recon_ref,xla_gl_sl_link_id_s.NEXTVAL, 'XLAJEL', attribute_category,
773 encumbrance_type_id, attribute1, attribute2, attribute3,
774 attribute4, attribute5, attribute6, attribute7, attribute8,
775 attribute9, attribute10, attribute11, attribute12,
776 attribute13, attribute14, attribute15, gain_or_loss_flag,
777 l_info.ledger_id,trunc(p_gl_date),
778 NVL (mpa_accrual_entry_flag, 'N')
779 -- 4262811 MPA_ACCRUAL_ENTRY_FLAG
780 FROM xla_ae_lines
781 WHERE application_id = l_info.application_id
782 AND ae_header_id = l_info.header_id;
783
784 create_reversal_distr_link (p_application_id => l_info.application_id,
785 p_ae_header_id => p_rev_header_id,
786 p_ref_ae_header_id => l_info.header_id -- ORIGINAL AE HEADER
787 ,
788 p_ref_event_id => NULL
789 );
790
791 --
792 -- COPY THE JOURNAL ENTRY LINES' ANALYTICAL CRITERIA FROM THE ORIGINAL ENTRY TO
793 -- THE REVERSAL ENTRY
794 --
795 INSERT INTO xla_ae_line_acs
796 (ae_header_id, ae_line_num, analytical_criterion_code,
797 analytical_criterion_type_code, amb_context_code, ac1,
798 ac2, ac3, ac4, ac5, object_version_number)
799 SELECT p_rev_header_id, ae_line_num, analytical_criterion_code,
800 analytical_criterion_type_code, amb_context_code, ac1, ac2,
801 ac3, ac4, ac5, 1
802 FROM xla_ae_line_acs
803 WHERE ae_header_id = l_info.header_id;
804 END LOOP;
805
806 ---------FND_LOG---------
807 IF (c_level_procedure >= g_log_level)
808 THEN
809 TRACE (p_msg => 'END of create_reversal_entry',
810 p_level => c_level_procedure,
811 p_module => l_log_module
812 );
813 END IF;
814 -------------------------
815 EXCEPTION
816 WHEN xla_exceptions_pkg.application_exception
817 THEN
818 ROLLBACK;
819 RAISE;
820 WHEN OTHERS
821 THEN
822 ROLLBACK;
823 xla_exceptions_pkg.raise_message (p_location => 'XLA_FSAH_INT_PVT.CREATE_REVERSAL_ENTRY'
824 );
825 END create_reversal_entry;
826
827 /*=== LOGIC ====================================================================
828 1) FIND THE AE_HEADER_ID OF THE PRIMARY LEDGER (AND ORIGINAL PARENT ENTRY OF
829 MPA/ACCRUAL REVERSAL ENTRY, IF EXISTS) FOR THE ORIGINAL P_EVENT_ID
830 2) CALLS REVERSE_JOURNAL_ENTRY WITH THE AE_HEADER_ID
831 A) DELETE THE INCOMPLETE MPA
832 B) CREATE A NEW EVENT AND ENTITY, AND MAP THE ORIGINAL ENTRY TO THE NEW
833 EVENT ID AND ENTITY ID.
834 C) CALLS CREATE_REVERSAL_ENTRY OF THE AE_HEADER_ID TO CREATE THE REVERSAL OF
835 THE ORIGINAL ENTRY, RETURNING THE NEW REV_AE_HEADER_ID AND REV_EVENT_ID
836 I) CALLS COMPLETE_JOURNAL_ENTRY WITH REV_AE_HEADER_ID, P_EVENT_ID AND
837 P_REV_FLAG = 'Y' TO VALIDATE THE REVERSAL ENTRY REV_AE_HEADER_ID AND ON
838 SUCCESS,
839 -> CALLS CREATE_MRC_REVERSAL_ENTRY TO CREATE REVERSAL OF ALL OTHER
840 LEDGERS AND ENTRIES RELATED TO THE ORIGINAL ENTRY P_EVENT_ID.
841
842 ==============================================================================*/
843 PROCEDURE reverse_journal_entries (
844 p_api_version IN NUMBER,
845 p_init_msg_list IN VARCHAR2,
846 p_application_id IN INTEGER,
847 p_event_id IN INTEGER,
848 p_reversal_method IN VARCHAR2,
849 p_gl_date IN DATE,
850 p_post_to_gl_flag IN VARCHAR2,
851 x_return_status OUT NOCOPY VARCHAR2,
852 x_msg_count OUT NOCOPY NUMBER,
853 x_msg_data OUT NOCOPY VARCHAR2,
854 x_rev_ae_header_id OUT NOCOPY INTEGER,
855 x_rev_event_id OUT NOCOPY INTEGER,
856 x_rev_entity_id OUT NOCOPY INTEGER,
857 x_new_event_id OUT NOCOPY INTEGER,
858 x_new_entity_id OUT NOCOPY INTEGER
859 )
860 IS
861 l_api_name CONSTANT VARCHAR2 (30) := 'REVERSE_JOURNAL_ENTRIES';
862 l_api_version CONSTANT NUMBER := 1.0;
863 l_info t_je_info;
864
865 -- l_ae_header_id_count number;
866
867 ---------------------------------------------------------------
868 -- IN ORDER TO REVERSE, THEY MUST BE FINAL AND TRANSFERRED.
869 ---------------------------------------------------------------
870 CURSOR c_orig_je
871 IS
872 SELECT xgl.currency_code, xsu.je_source_name, xah.entity_id,
873 xah.ae_header_id, xah.accounting_date, xah.ledger_id,
874 e.legal_entity_id, xah.accrual_reversal_flag,
875 xe.budgetary_control_flag
876 FROM xla_gl_ledgers_v xgl,
877 xla_ae_headers xah,
878 xla_subledgers xsu,
879 xla_transaction_entities e,
880 xla_events xe
881 WHERE xgl.ledger_id = xah.ledger_id
882 AND xsu.application_id = xah.application_id
883 AND xah.event_id = p_event_id
884 AND xah.application_id = p_application_id
885 AND ledger_category_code = 'PRIMARY'
886 AND e.application_id = xah.application_id
887 AND e.entity_id = xah.entity_id
888 AND xe.application_id = xah.application_id
889 AND xe.event_id = xah.event_id
890 AND xah.accounting_entry_status_code = c_status_final_code
891 AND xah.parent_ae_header_id IS NULL
892 AND NOT EXISTS (
893 SELECT 1
894 FROM xla_ae_headers xah2
895 WHERE xah2.application_id = p_application_id
896 AND xah2.event_id = p_event_id
897 AND xah2.accounting_entry_status_code =
898 c_status_final_code
899 AND NVL (xah2.gl_transfer_status_code, 'N') IN
900 ('N', 'NT'));
901
902 -- CAN BE REVERSED ONLY IF IT IS TRANSFERRED
903 l_functional_curr xla_gl_ledgers_v.currency_code%TYPE;
904 l_je_source_name xla_subledgers.je_source_name%TYPE;
905 l_entity_id INTEGER;
906 l_pri_ae_header_id INTEGER;
907 l_pri_gl_date DATE;
908 l_ledger_id INTEGER;
909 l_legal_entity_id INTEGER;
910 l_mpa_acc_rev_flag VARCHAR2 (1);
911 l_bc_flag VARCHAR2 (1);
912 l_transfer_request_id INTEGER;
913
914 TYPE t_ae_header_id IS TABLE OF xla_ae_headers.ae_header_id%TYPE;
915
916 l_ae_header_id INTEGER;
917 l_event_source_info xla_events_pub_pkg.t_event_source_info;
918 l_array_ae_header_id t_array_integer;
919 l_retcode INTEGER;
920 l_log_module VARCHAR2 (240);
921 l_completion_option VARCHAR2 (1);
922 l_completion_retcode VARCHAR2 (30);
923 BEGIN
924 -----FND_LOG-----------
925 IF g_log_enabled
926 THEN
927 l_log_module := c_default_module || '.reverse_journal_entries';
928 --DBMS_OUTPUT.put_line ('BEGIN of reverse_journal_entries');
929 END IF;
930
931 IF (c_level_procedure >= g_log_level)
932 THEN
933 TRACE (p_msg => 'BEGIN of reverse_journal_entries',
934 p_level => c_level_procedure,
935 p_module => l_log_module
936 );
937 TRACE (p_msg => 'p_application_id'||to_char(p_application_id),
938 p_level => c_level_procedure,
939 p_module => l_log_module
940 );
941
942 TRACE (p_msg => 'p_event_id '||to_char(p_event_id),
943 p_level => c_level_procedure,
944 p_module => l_log_module
945 );
946
947 TRACE (p_msg => 'p_reversal_method '||p_reversal_method,
948 p_level => c_level_procedure,
949 p_module => l_log_module
950 );
951
952 TRACE (p_msg => 'p_gl_date '||to_char(p_gl_date),
953 p_level => c_level_procedure,
954 p_module => l_log_module
955 );
956
957 END IF;
958
959 ----------------------
960 IF (fnd_api.to_boolean (p_init_msg_list))
961 THEN
962 fnd_msg_pub.initialize;
963 END IF;
964
965 --DBMS_OUTPUT.put_line ('fnd_api.to_boolean got initialized');
966
967 IF (c_level_procedure >= g_log_level)
968 THEN
969 TRACE (p_msg => 'fnd_api.to_boolean got initialized',
970 p_level => c_level_procedure,
971 p_module => l_log_module
972 );
973 END IF;
974
975 -- STANDARD CALL TO CHECK FOR CALL COMPATIBILITY.
976 IF (NOT fnd_api.compatible_api_call (p_current_version_number => l_api_version,
977 p_caller_version_number => p_api_version,
978 p_api_name => l_api_name,
979 p_pkg_name => c_default_module
980 )
981 )
982 THEN
983 RAISE fnd_api.g_exc_unexpected_error;
984 END IF;
985
986 -- INITIALIZE GLOBAL VARIABLES
987 x_return_status := fnd_api.g_ret_sts_success;
988 --DBMS_OUTPUT.put_line (x_return_status);
989
990 IF (c_level_procedure >= g_log_level)
991 THEN
992 TRACE (p_msg => x_return_status,
993 p_level => c_level_procedure,
994 p_module => l_log_module
995 );
996 END IF;
997
998 -- VALIDATION -------------------------------------------------------
999 OPEN c_orig_je;
1000 FETCH c_orig_je INTO l_functional_curr,
1001 l_je_source_name,
1002 l_entity_id,
1003 l_pri_ae_header_id,
1004 l_pri_gl_date,
1005 l_ledger_id,
1006 l_legal_entity_id,
1007 l_mpa_acc_rev_flag,
1008 l_bc_flag;
1009 --DBMS_OUTPUT.put_line ('cursor c_orig_je is opend');
1010
1011 IF (c_level_procedure >= g_log_level)
1012 THEN
1013 TRACE (p_msg => 'cursor c_orig_je is opend',
1014 p_level => c_level_procedure,
1015 p_module => l_log_module
1016 );
1017 END IF;
1018
1019 IF c_orig_je%NOTFOUND
1020 THEN
1021 CLOSE c_orig_je;
1022 END IF;
1023
1024 CLOSE c_orig_je;
1025 --DBMS_OUTPUT.put_line ('cursor c_orig_je closed');
1026
1027 IF (c_level_procedure >= g_log_level)
1028 THEN
1029 TRACE (p_msg => 'cursor c_orig_je is closed',
1030 p_level => c_level_procedure,
1031 p_module => l_log_module
1032 );
1033 END IF;
1034
1035 -----------------------------------------------------------------
1036 -- CREATE NEW EVENT AND ENTITY, SAME DETAILS AS ORIGINAL ENTRY
1037 -----------------------------------------------------------------
1038 l_event_source_info.application_id := p_application_id;
1039 l_event_source_info.legal_entity_id := l_legal_entity_id;
1040 l_event_source_info.ledger_id := l_ledger_id;
1041 l_event_source_info.entity_type_code := 'MANUAL';
1042 ---------------------------------------------------------------------------------------------
1043 -- CURRENTLY, XLA_EVENTS_PKG.VALIDATE_EVENT_TYPE_CODE FAILES IF NOT MANUAL EVENT TYPE
1044 ---------------------------------------------------------------------------------------------
1045
1046 --DBMS_OUTPUT.put_line ('Creating the Reversal Event');
1047
1048 IF (c_level_procedure >= g_log_level)
1049 THEN
1050 TRACE (p_msg => 'Creating the Reversal Event',
1051 p_level => c_level_procedure,
1052 p_module => l_log_module
1053 );
1054 END IF;
1055
1056 x_new_event_id :=
1057 xla_events_pkg.create_manual_event (p_event_source_info => l_event_source_info,
1058 p_event_type_code => 'MANUAL',
1059 p_event_date => l_pri_gl_date,
1060 p_event_status_code => xla_events_pub_pkg.c_event_unprocessed,
1061 p_process_status_code => xla_events_pkg.c_internal_unprocessed,
1062 p_event_number => 1,
1063 p_budgetary_control_flag => l_bc_flag
1064 );
1065 /*DBMS_OUTPUT.put_line ( 'RETURNED FROM XLA_EVENTS_PKG.CREATE_MANUAL_EVENT = EVENT ID '
1066 || x_new_event_id
1067 );*/
1068
1069 IF (c_level_procedure >= g_log_level)
1070 THEN
1071 TRACE (p_msg => 'RETURNED FROM XLA_EVENTS_PKG.CREATE_MANUAL_EVENT = EVENT ID '
1072 || x_new_event_id,
1073 p_level => c_level_procedure,
1074 p_module => l_log_module
1075 );
1076 END IF;
1077
1078 -----------------------------------------------------
1079 -- UPDATE NEW EVENT_ID AND ENTITY_ID
1080 -----------------------------------------------------
1081 --DBMS_OUTPUT.put_line ('Before Updating the xla_events');
1082
1083 IF (c_level_procedure >= g_log_level)
1084 THEN
1085 TRACE (p_msg => 'Before Updating the xla_events',
1086 p_level => c_level_procedure,
1087 p_module => l_log_module
1088 );
1089 END IF;
1090
1091 xla_security_pkg.set_security_context (602);
1092
1093 UPDATE xla_events
1094 SET event_status_code = xla_events_pub_pkg.c_event_processed,
1095 process_status_code = xla_events_pub_pkg.c_event_processed,
1096 (event_type_code, event_date, reference_num_1,
1097 reference_num_2, reference_num_3, reference_num_4,
1098 reference_char_1, reference_char_2, reference_char_3,
1099 reference_char_4, reference_date_1, reference_date_2,
1100 reference_date_3, reference_date_4, on_hold_flag,
1101 upg_batch_id, upg_source_application_id, upg_valid_flag,
1102 transaction_date, budgetary_control_flag,
1103 merge_event_set_id -- EVENT_NUMBER
1104 , creation_date, created_by,
1105 last_update_date, last_updated_by, last_update_login,
1106 program_update_date, program_application_id, program_id,
1107 request_id) =
1108 (SELECT 'REVERSAL' -- EVENT_TYPE_CODE
1109 , event_date, reference_num_1,
1110 reference_num_2, reference_num_3, reference_num_4,
1111 reference_char_1, reference_char_2,
1112 reference_char_3, reference_char_4,
1113 reference_date_1, reference_date_2,
1114 reference_date_3, reference_date_4, on_hold_flag,
1115 upg_batch_id, upg_source_application_id,
1116 upg_valid_flag, transaction_date,
1117 budgetary_control_flag,
1118 merge_event_set_id -- EVENT_NUMBER
1119 , SYSDATE, fnd_global.user_id,
1120 SYSDATE, fnd_global.user_id, fnd_global.user_id,
1121 SYSDATE, -1, -1, -1
1122 FROM xla_events
1123 WHERE application_id = p_application_id
1124 AND event_id = p_event_id)
1125 WHERE application_id = p_application_id
1126 AND event_id = x_new_event_id
1127 RETURNING entity_id
1128 INTO x_new_entity_id;
1129
1130 --DBMS_OUTPUT.put_line ('After Updating the xla_events');
1131
1132 IF (c_level_procedure >= g_log_level)
1133 THEN
1134 TRACE (p_msg => 'After Updating the xla_events',
1135 p_level => c_level_procedure,
1136 p_module => l_log_module
1137 );
1138 END IF;
1139
1140 --DBMS_OUTPUT.put_line ('Before Updating the xla_transaction_entities');
1141
1142 IF (c_level_procedure >= g_log_level)
1143 THEN
1144 TRACE (p_msg => 'Before Updating the xla_transaction_entities',
1145 p_level => c_level_procedure,
1146 p_module => l_log_module
1147 );
1148 END IF;
1149
1150 xla_security_pkg.set_security_context (602);
1151
1152 UPDATE xla_transaction_entities
1153 SET (entity_code, source_id_int_1, source_id_char_1,
1154 security_id_int_1, security_id_int_2, security_id_int_3,
1155 security_id_char_1, security_id_char_2, security_id_char_3,
1156 source_id_int_2, source_id_char_2, source_id_int_3,
1157 source_id_char_3, source_id_int_4, source_id_char_4,
1158 valuation_method, source_application_id, upg_batch_id,
1159 upg_source_application_id, upg_valid_flag -- TRANSACTION_NUMBER
1160 -- LEGAL_ENTITY_ID
1161 -- LEDGER_ID
1162 ,
1163 creation_date, created_by, last_update_date, last_updated_by,
1164 last_update_login) =
1165 (SELECT 'REVERSAL', -- ENTITY_CODE THIS ALSO PREVENTS TRANSACTION TO BE USED IN BFLOW.
1166 source_id_int_1,
1167 source_id_char_1, security_id_int_1,
1168 security_id_int_2, security_id_int_3,
1169 security_id_char_1, security_id_char_2,
1170 security_id_char_3, source_id_int_2, source_id_char_2,
1171 source_id_int_3, source_id_char_3, source_id_int_4,
1172 source_id_char_4, valuation_method,
1173 source_application_id, upg_batch_id,
1174 upg_source_application_id,
1175 upg_valid_flag -- TRANSACTION_NUMBER
1176 -- LEGAL_ENTITY_ID
1177 -- LEDGER_ID
1178 , SYSDATE, fnd_global.user_id, SYSDATE,
1179 fnd_global.user_id, fnd_global.user_id
1180 FROM xla_transaction_entities
1181 WHERE application_id = p_application_id
1182 AND entity_id = l_entity_id)
1183 WHERE application_id = p_application_id AND entity_id = x_new_entity_id;
1184
1185 IF (c_level_procedure >= g_log_level)
1186 THEN
1187 TRACE (p_msg => 'After Updating the the xla_transaction_entities',
1188 p_level => c_level_procedure,
1189 p_module => l_log_module
1190 );
1191 END IF;
1192
1193 --DBMS_OUTPUT.put_line ('After Updating the xla_transaction_entities');
1194 ------------------------------------------------------------------------------
1195 -- HEADERS TABLE UPDATE
1196 ------------------------------------------------------------------------------
1197 --DBMS_OUTPUT.put_line ('Before Updating the xla_ae_headers');
1198
1199 IF (c_level_procedure >= g_log_level)
1200 THEN
1201 TRACE (p_msg => 'Before Updating the xla_ae_headers',
1202 p_level => c_level_procedure,
1203 p_module => l_log_module
1204 );
1205 END IF;
1206
1207 UPDATE xla_ae_headers
1208 SET entity_id = x_new_entity_id,
1209 event_id = x_new_event_id,
1210 event_type_code = 'REVERSAL',
1211 description = 'DATA FIX ENTRY: EVENT_ID OF ' || p_event_id
1212 WHERE application_id = p_application_id AND event_id = p_event_id
1213 RETURNING ae_header_id
1214 BULK COLLECT INTO l_array_ae_header_id;
1215
1216 IF (c_level_procedure >= g_log_level)
1217 THEN
1218 TRACE (p_msg => 'After Updating the xla_ae_headers',
1219 p_level => c_level_procedure,
1220 p_module => l_log_module
1221 );
1222 END IF;
1223
1224 --DBMS_OUTPUT.put_line ('After Updating the xla_ae_headers1');
1225 ------------------------------------------------------------------------------
1226 -- LINES TABLE UPDATE
1227 ------------------------------------------------------------------------------
1228 --DBMS_OUTPUT.put_line ('Before Updating the xla_ae_lines');
1229
1230 IF (c_level_procedure >= g_log_level)
1231 THEN
1232 TRACE (p_msg => 'Before Updating Updating the xla_ae_lines',
1233 p_level => c_level_procedure,
1234 p_module => l_log_module
1235 );
1236 END IF;
1237
1238 FORALL i IN 1 .. l_array_ae_header_id.COUNT
1239 UPDATE xla_ae_lines
1240 SET description = 'DATA FIX ENTRY: EVENT_ID OF ' || p_event_id
1241 WHERE application_id = p_application_id
1242 AND ae_header_id = l_array_ae_header_id (i);
1243 ------------------------------------------------------------------------------
1244 -- DISTRIBUTION LINKS TABLE UPDATE
1245 ------------------------------------------------------------------------------
1246 --DBMS_OUTPUT.put_line ('after Updating the xla_ae_lines');
1247
1248 IF (c_level_procedure >= g_log_level)
1249 THEN
1250 TRACE (p_msg => 'After Updating Updating the xla_ae_lines',
1251 p_level => c_level_procedure,
1252 p_module => l_log_module
1253 );
1254 END IF;
1255
1256 FORALL i IN 1 .. l_array_ae_header_id.COUNT
1257 UPDATE xla_distribution_links
1258 SET event_id = x_new_event_id
1259 WHERE application_id = p_application_id
1260 AND ae_header_id = l_array_ae_header_id (i);
1261 ---------------------------------------------------------
1262 -- SET ORIGINAL EVENT TO UNPROCESSED
1263 ---------------------------------------------------------
1264 --DBMS_OUTPUT.put_line ('Before Updating the xla_events');
1265
1266 IF (c_level_procedure >= g_log_level)
1267 THEN
1268 TRACE (p_msg => 'Before Updating Updating the xla_events',
1269 p_level => c_level_procedure,
1270 p_module => l_log_module
1271 );
1272 END IF;
1273
1274 UPDATE xla_events
1275 SET event_status_code = xla_events_pub_pkg.C_EVENT_INCOMPLETE,
1276 process_status_code = xla_events_pkg.c_internal_unprocessed
1277 WHERE application_id = p_application_id AND event_id = p_event_id;
1278
1279 IF (c_level_procedure >= g_log_level)
1280 THEN
1281 TRACE (p_msg => 'After Updating Updating the xla_events',
1282 p_level => c_level_procedure,
1283 p_module => l_log_module
1284 );
1285 END IF;
1286
1287 --DBMS_OUTPUT.put_line ('after Updating the xla_events');
1288 -----------------------------------------------------------------------------------
1289 -- CURRENTLY, XLA_JOURNAL_ENTRIES_PKG.REVERSE_JOURNAL_ENTRY ONLY PROCESS REVERSAL ENTRY
1290 -----------------------------------------------------------------------------------
1291
1292 --DBMS_OUTPUT.put_line ('Before Updating the xla_ae_headers2');
1293
1294 IF (c_level_procedure >= g_log_level)
1295 THEN
1296 TRACE (p_msg => 'Before Updating the xla_ae_headers2',
1297 p_level => c_level_procedure,
1298 p_module => l_log_module
1299 );
1300 END IF;
1301
1302 UPDATE xla_ae_headers
1303 SET accounting_entry_type_code = 'REVERSAL'
1304 WHERE application_id = p_application_id AND event_id = x_new_event_id;
1305
1306 IF (c_level_procedure >= g_log_level)
1307 THEN
1308 TRACE (p_msg => 'After Updating the xla_ae_headers2',
1309 p_level => c_level_procedure,
1310 p_module => l_log_module
1311 );
1312 END IF;
1313
1314 --DBMS_OUTPUT.put_line ('After Updating the xla_ae_headers2');
1315 --------------------------------------------------------
1316 -- REVERSE JOURNAL ENTRIES
1317 --------------------------------------------------------
1318 --DBMS_OUTPUT.put_line ('x_new_event_id ' || x_new_event_id);
1319
1320 IF (c_level_procedure >= g_log_level)
1321 THEN
1322 TRACE (p_msg => 'x_new_event_id ' || x_new_event_id,
1323 p_level => c_level_procedure,
1324 p_module => l_log_module
1325 );
1326 END IF;
1327
1328 /*select count(*) into
1329 l_ae_header_id_count
1330 from xla_ae_headers where event_id = x_new_event_id ;
1331
1332 --DBMS_OUTPUT.put_line ('l_ae_header_id_count ' || l_ae_header_id_count);*/
1333 BEGIN
1334 SELECT ae_header_id
1335 INTO l_ae_header_id
1336 FROM xla_ae_headers
1337 WHERE event_id = x_new_event_id AND ROWNUM = 1;
1338 EXCEPTION
1339 WHEN OTHERS
1340 THEN
1341 IF (c_level_procedure >= g_log_level)
1342 THEN
1343 TRACE (p_msg => SQLERRM,
1344 p_level => c_level_procedure,
1345 p_module => l_log_module
1346 );
1347 END IF;
1348
1349 --DBMS_OUTPUT.put_line (SQLERRM);
1350 END;
1351
1352 l_info := get_header_info (l_ae_header_id, p_application_id, g_msg_mode);
1353 --DBMS_OUTPUT.put_line ('Call to create_reversal_entry ');
1354
1355 IF (c_level_procedure >= g_log_level)
1356 THEN
1357 TRACE (p_msg => 'Call to create_reversal_entry ',
1358 p_level => c_level_procedure,
1359 p_module => l_log_module
1360 );
1361 END IF;
1362
1363 create_reversal_entry (p_info => l_info,
1364 p_reversal_method => p_reversal_method,
1365 p_gl_date => p_gl_date,
1366 p_rev_header_id => x_rev_ae_header_id,
1367 p_rev_event_id => x_rev_event_id
1368 );
1369 -- DBMS_OUTPUT.put_line ('After Call to create_reversal_entry ');
1370
1371 IF (c_level_procedure >= g_log_level)
1372 THEN
1373 TRACE (p_msg => 'After Call to create_reversal_entry ',
1374 p_level => c_level_procedure,
1375 p_module => l_log_module
1376 );
1377 END IF;
1378
1379 IF l_completion_retcode <> 'S' OR x_rev_ae_header_id IS NULL
1380 THEN
1381 IF (c_level_statement >= g_log_level)
1382 THEN
1383 TRACE (p_msg => 'FAILURE IN XLA_JOURNAL_ENTRIES_PKG.REVERSE_JOURNAL_ENTRY. PLEASE VERIFY LOG FILE.',
1384 p_module => l_log_module,
1385 p_level => c_level_statement
1386 );
1387 END IF;
1388 END IF;
1389
1390 -------------------------------------------------------------------------------
1391 -- UPDATE DESCRIPTION FOR REVERSE ENTRIES
1392 -------------------------------------------------------------------------------
1393 IF (c_level_statement >= g_log_level)
1394 THEN
1395 TRACE (p_msg => 'UPDATE DESCRIPTIONS',
1396 p_module => l_log_module,
1397 p_level => c_level_statement
1398 );
1399 END IF;
1400
1401
1402 IF (c_level_statement >= g_log_level)
1403 THEN
1404 TRACE (p_msg => 'Updating the xla_ae_headers3 with the Description',
1405 p_module => l_log_module,
1406 p_level => c_level_statement
1407 );
1408 END IF;
1409
1410 UPDATE xla_events
1411 SET event_type_code = 'REVERSAL',
1412 event_status_code = 'P',
1413 process_status_code = 'P'
1414 WHERE event_id = x_rev_event_id;
1415
1416 UPDATE xla_ae_headers
1417 SET /*description =
1418 'DATA FIX REVERSAL ENTRY: EVENT_ID OF '
1419 || p_event_id,*/
1420 event_type_code = 'REVERSAL'
1421 WHERE application_id = p_application_id
1422 AND event_id = x_rev_event_id
1423 RETURNING ae_header_id
1424 BULK COLLECT INTO l_array_ae_header_id;
1425
1426 IF (c_level_statement >= g_log_level)
1427 THEN
1428 TRACE (p_msg => 'After Updating the xla_ae_headers3 with the Description',
1429 p_module => l_log_module,
1430 p_level => c_level_statement
1431 );
1432 END IF;
1433
1434 IF (c_level_statement >= g_log_level)
1435 THEN
1436 TRACE (p_msg => 'Updating the xla_ae_lines with the Description ',
1437 p_module => l_log_module,
1438 p_level => c_level_statement
1439 );
1440 END IF;
1441
1442 FORALL i IN 1 .. l_array_ae_header_id.COUNT
1443 UPDATE xla_ae_lines
1444 SET description =description||' Original Event_id '|| p_event_id
1445 WHERE application_id = p_application_id
1446 AND ae_header_id = l_array_ae_header_id (i);
1447
1448 IF (c_level_statement >= g_log_level)
1449 THEN
1450 TRACE (p_msg => 'After Updating the xla_ae_lines with the Description ',
1451 p_module => l_log_module,
1452 p_level => c_level_statement
1453 );
1454 END IF;
1455
1456 ---------FND_LOG---------
1457 IF (c_level_procedure >= g_log_level)
1458 THEN
1459 TRACE (p_msg => 'END of reverse_journal_entries',
1460 p_level => c_level_procedure,
1461 p_module => l_log_module
1462 );
1463 END IF;
1464 -------------------------
1465 EXCEPTION
1466 WHEN fnd_api.g_exc_error
1467 THEN
1468 ROLLBACK;
1469 x_return_status := fnd_api.g_ret_sts_error;
1470 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1471 p_data => x_msg_data
1472 );
1473 WHEN fnd_api.g_exc_unexpected_error
1474 THEN
1475 ROLLBACK;
1476 x_return_status := fnd_api.g_ret_sts_unexp_error;
1477 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1478 p_data => x_msg_data
1479 );
1480 WHEN xla_exceptions_pkg.application_exception
1481 THEN
1482 RAISE;
1483 WHEN OTHERS
1484 THEN
1485 --DBMS_OUTPUT.put_line (SQLERRM);
1486
1487 IF (c_level_statement >= g_log_level)
1488 THEN
1489 TRACE (p_msg => SQLERRM,
1490 p_module => l_log_module,
1491 p_level => c_level_statement
1492 );
1493 END IF;
1494
1495 ROLLBACK;
1496 x_return_status := fnd_api.g_ret_sts_unexp_error;
1497
1498 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1499 THEN
1500 fnd_msg_pub.add_exc_msg (c_default_module, l_api_name);
1501 END IF;
1502
1503 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1504 p_data => x_msg_data
1505 );
1506 xla_exceptions_pkg.raise_message (p_location => 'XLA_FSAH_INT_PVT.reverse_journal_entries'
1507 );
1508 END reverse_journal_entries;
1509
1510 -------------------------------------------------------------------------------
1511 -- UPDATE DESCRIPTION FOR REV_JOUR_ENTRY
1512 -------------------------------------------------------------------------------
1513 PROCEDURE rev_jour_entry (
1514 p_ae_header_id IN NUMBER,
1515 p_return_status OUT NOCOPY VARCHAR2,
1516 p_error_msg OUT NOCOPY VARCHAR2
1517 )
1518 AS
1519 -- variables declarations
1520 l_event_id NUMBER;
1521 l_api_version NUMBER;
1522 l_init_msg_list VARCHAR2 (300);
1523 l_application_id INTEGER;
1524 l_reversal_method VARCHAR2 (300);
1525 l_post_to_gl_flag VARCHAR2 (10);
1526 l_gl_date DATE;
1527 x_return_status VARCHAR2 (300);
1528 x_msg_count NUMBER;
1529 x_msg_data VARCHAR2 (4000);
1530 x_rev_ae_header_id INTEGER;
1531 x_rev_event_id INTEGER;
1532 x_rev_entity_id INTEGER;
1533 x_new_event_id INTEGER;
1534 x_new_entity_id INTEGER;
1535 l_log_module VARCHAR2 (240);
1536 l_security_id_int_1 NUMBER;
1537 BEGIN
1538 -----FND_LOG-----------
1539 IF g_log_enabled
1540 THEN
1541 l_log_module := c_default_module || '.rev_jour_entry';
1542 END IF;
1543
1544 IF (c_level_procedure >= g_log_level)
1545 THEN
1546 TRACE (p_msg => 'BEGIN of rev_jour_entry',
1547 p_level => c_level_procedure,
1548 p_module => l_log_module
1549 );
1550 END IF;
1551
1552 ----------------------
1553 l_api_version := 1.0;
1554 l_init_msg_list := fnd_api.g_true;
1555 l_application_id := 200;
1556 l_reversal_method := 'SIDE';
1557 -- l_gl_date := SYSDATE;
1558 l_post_to_gl_flag := 'Y';
1559
1560 -- collecting the data for reversal
1561 BEGIN
1562 SELECT event_id, application_id,accounting_date
1563 INTO l_event_id, l_application_id,l_gl_date
1564 FROM xla_ae_headers
1565 WHERE ae_header_id = p_ae_header_id;
1566 EXCEPTION
1567 WHEN OTHERS
1568 THEN
1569 fnd_file.put_line (fnd_file.LOG,
1570 SQLERRM || ' Selection of Application Id '
1571 );
1572 END;
1573
1574 /* BEGIN
1575 SELECT xte.security_id_int_1
1576 INTO l_security_id_int_1
1577 FROM xla_ae_headers xah,
1578 xla_events xe,
1579 xla_transaction_entities xte
1580 WHERE xah.ae_header_id = p_ae_header_id
1581 AND xah.event_id = xe.event_id
1582 AND xah.application_id = xe.application_id
1583 AND xe.application_id = xte.application_id
1584 AND xe.entity_id = xte.entity_id;
1585 EXCEPTION
1586 WHEN OTHERS
1587 THEN
1588 fnd_file.put_line (fnd_file.LOG,
1589 SQLERRM
1590 || ' Problem in setting the security Context '
1591 );
1592 END;*/
1593
1594 -- mo_global.set_policy_context ('S', l_security_id_int_1);
1595 xla_security_pkg.set_security_context (602);
1596
1597 IF (c_level_procedure >= g_log_level)
1598 THEN
1599 TRACE (p_msg => 'Security_context set ',
1600 p_level => c_level_procedure,
1601 p_module => l_log_module
1602 );
1603 END IF;
1604
1605 -- calling the reversal
1606 reverse_journal_entries (l_api_version,
1607 l_init_msg_list,
1608 l_application_id,
1609 l_event_id,
1610 l_reversal_method,
1611 l_gl_date,
1612 l_post_to_gl_flag,
1613 x_return_status,
1614 x_msg_count,
1615 x_msg_data,
1616 x_rev_ae_header_id,
1617 x_rev_event_id,
1618 x_rev_entity_id,
1619 x_new_event_id,
1620 x_new_entity_id
1621 );
1622 p_return_status := x_return_status; /*|| ' x_rev_ae_header_id '
1623 || x_rev_ae_header_id
1624 || 'x_rev_event_id '
1625 || x_rev_event_id
1626 || 'x_new_event_id '
1627 || x_new_event_id;
1628 p_error_msg := ;*/
1629
1630 ---------FND_LOG---------
1631 IF (c_level_procedure >= g_log_level)
1632 THEN
1633 TRACE (p_msg => 'END of rev_jour_entry',
1634 p_level => c_level_procedure,
1635 p_module => l_log_module
1636 );
1637 END IF;
1638 -------------------------
1639 EXCEPTION
1640 /* WHEN xla_exceptions_pkg.application_exception
1641 THEN
1642 RAISE;*/
1643 WHEN OTHERS
1644 THEN
1645 --DBMS_OUTPUT.put_line (SQLERRM);
1646
1647 IF (c_level_statement >= g_log_level)
1648 THEN
1649 TRACE (p_msg => SQLERRM,
1650 p_module => l_log_module,
1651 p_level => c_level_statement
1652 );
1653 END IF;
1654 /* xla_exceptions_pkg.raise_message (p_location => 'XLA_FSAH_INT_PVT.rev_jour_entry'
1655 );*/
1656 END rev_jour_entry;
1657
1658 PROCEDURE rev_jour_entry_list (
1659 p_list_ae_header_id IN fnd_table_of_number,
1660 p_return_status OUT NOCOPY VARCHAR2,
1661 p_error_msg OUT NOCOPY VARCHAR2
1662 )
1663 AS
1664 --l_table_of_headers fnd_table_of_number;
1665 l_first_ledger_id NUMBER;
1666 l_ledger_id NUMBER;
1667 l_ledger_category VARCHAR2 (100);
1668 l_log_module VARCHAR2 (240);
1669 l_return_status VARCHAR2 (2);
1670 l_error_msg VARCHAR2 (240);
1671 l_cont_flag VARCHAR2 (1) := 'Y';
1672 BEGIN
1673 IF g_log_enabled
1674 THEN
1675 l_log_module := c_default_module || '.rev_jour_entry_list';
1676 END IF;
1677
1678 IF (c_level_procedure >= g_log_level)
1679 THEN
1680 TRACE (p_msg => 'BEGIN of rev_jour_entry_list',
1681 p_level => c_level_procedure,
1682 p_module => l_log_module
1683 );
1684 END IF;
1685
1686 -- validating the all the accounting headers having the same ledger or not
1687 FOR i IN p_list_ae_header_id.FIRST .. p_list_ae_header_id.LAST
1688 LOOP
1689 SELECT ledger_id
1690 INTO l_first_ledger_id
1691 FROM xla_ae_headers
1692 WHERE ae_header_id = p_list_ae_header_id (i);
1693 END LOOP;
1694
1695 FOR j IN p_list_ae_header_id.FIRST .. p_list_ae_header_id.LAST
1696 LOOP
1697 SELECT ledger_id
1698 INTO l_ledger_id
1699 FROM xla_ae_headers
1700 WHERE ae_header_id = p_list_ae_header_id (j);
1701
1702 IF l_ledger_id <> l_first_ledger_id
1703 THEN
1704 l_cont_flag := 'N';
1705
1706 IF (c_level_procedure >= g_log_level)
1707 THEN
1708 TRACE (p_msg => 'Given Accounting headers belongs to different Ledgers ',
1709 p_level => c_level_procedure,
1710 p_module => l_log_module
1711 );
1712 END IF;
1713
1714 EXIT;
1715 END IF;
1716 END LOOP;
1717
1718 IF l_cont_flag = 'Y'
1719 THEN
1720 -- validating the ledger belongs to primary ledger or not
1721 BEGIN
1722 SELECT ledger_category_code
1723 INTO l_ledger_category
1724 FROM gl_ledgers
1725 WHERE ledger_id = l_first_ledger_id;
1726 END;
1727
1728 IF l_ledger_category <> 'PRIMARY'
1729 THEN
1730 IF (c_level_procedure >= g_log_level)
1731 THEN
1732 TRACE (p_msg => 'Ledger Is Not A Primary Ledger ',
1733 p_level => c_level_procedure,
1734 p_module => l_log_module
1735 );
1736 END IF;
1737
1738 --DBMS_OUTPUT.put_line ('LEDGER IS NOT A PRIMARY LEDGER ');
1739 ELSE
1740 FOR k IN p_list_ae_header_id.FIRST .. p_list_ae_header_id.LAST
1741 LOOP
1742 IF (c_level_statement >= g_log_level)
1743 THEN
1744 TRACE (p_msg => 'Before Calling the rev_jour_entry',
1745 p_module => l_log_module,
1746 p_level => c_level_statement
1747 );
1748 END IF;
1749
1750 IF (c_level_procedure >= g_log_level)
1751 THEN
1752 TRACE (p_msg => 'Creating Reversal for the accounting header'
1753 || p_list_ae_header_id (k),
1754 p_level => c_level_procedure,
1755 p_module => l_log_module
1756 );
1757 END IF;
1758
1759 /*--DBMS_OUTPUT.put_line
1760 ( 'Processing the Reversal for the accounting header '
1761 || p_list_ae_header_id (k)
1762 );*/
1763 rev_jour_entry (p_ae_header_id => p_list_ae_header_id (k),
1764 p_return_status => l_return_status,
1765 p_error_msg => l_error_msg
1766 );
1767
1768 IF (c_level_procedure >= g_log_level)
1769 THEN
1770 TRACE (p_msg => 'Status for the accounting header '
1771 || p_list_ae_header_id (k)
1772 || l_return_status,
1773 p_level => c_level_procedure,
1774 p_module => l_log_module
1775 );
1776 END IF;
1777
1778 /*DBMS_OUTPUT.put_line
1779 (
1780 );*/
1781 IF l_return_status <> 'S'
1782 THEN
1783 ROLLBACK;
1784 EXIT;
1785 END IF;
1786
1787 p_return_status := l_return_status;
1788 p_error_msg := l_error_msg;
1789
1790 IF (c_level_statement >= g_log_level)
1791 THEN
1792 TRACE (p_msg => 'After rev_jour_entry',
1793 p_module => l_log_module,
1794 p_level => c_level_statement
1795 );
1796 END IF;
1797 END LOOP;
1798 END IF;
1799 ELSE
1800 p_return_status := 'E';
1801 p_error_msg := l_error_msg;
1802 END IF;
1803 EXCEPTION
1804 WHEN OTHERS
1805 THEN
1806 IF (c_level_procedure >= g_log_level)
1807 THEN
1808 TRACE (p_msg => SQLERRM,
1809 p_level => c_level_procedure,
1810 p_module => l_log_module
1811 );
1812 END IF;
1813 END rev_jour_entry_list;
1814
1815 ----------------------------------------------------------------------------------
1816 -- Function (GET_GROUP_ID) To Get the Group ID
1817 ----------------------------------------------------------------------------------
1818 FUNCTION get_group_id (
1819 p_ledger_short_name IN VARCHAR2,
1820 p_appl_short_name IN VARCHAR2,
1821 p_end_date IN DATE,
1822 p_accounting_batch_id IN NUMBER,
1823 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,
1824 p_api_version IN NUMBER DEFAULT 1.0
1825 )
1826 RETURN NUMBER
1827 IS
1828 --Declaring Process Variables
1829 l_group_id xla_ae_headers.GROUP_ID%TYPE;
1830 l_log_module VARCHAR2 (240);
1831 l_pro_records NUMBER;
1832 l_ledger_status VARCHAR2 (100);
1833 l_primary VARCHAR2 (100);
1834 l_ledger_id NUMBER;
1835 l_application_id NUMBER;
1836
1837 TYPE tab_ae_header_id IS TABLE OF NUMBER
1838 INDEX BY BINARY_INTEGER;
1839
1840 l_arry_ae_hdr_id tab_ae_header_id;
1841 BEGIN
1842 IF g_log_enabled
1843 THEN
1844 l_log_module := c_default_module || '.get_group_id';
1845 END IF;
1846
1847 IF (c_level_procedure >= g_log_level)
1848 THEN
1849 TRACE (p_msg => 'BEGIN of get_group_id',
1850 p_level => c_level_procedure,
1851 p_module => l_log_module
1852 );
1853 END IF;
1854
1855 BEGIN
1856 SELECT application_id
1857 INTO l_application_id
1858 FROM fnd_application
1859 WHERE application_short_name = p_appl_short_name;
1860 EXCEPTION
1861 WHEN xla_exceptions_pkg.application_exception
1862 THEN
1863 RAISE;
1864 WHEN OTHERS
1865 THEN
1866 /* p_err_message :=
1867 ' Application short name is not a Valid Value'
1868 || p_appl_short_name;*/
1869 l_group_id := -2;
1870 xla_exceptions_pkg.raise_message (p_location => 'xla_fsah_int_pvt.set_group_id'
1871 );
1872 END;
1873
1874 BEGIN
1875 SELECT glc.completion_status_code, gll.ledger_category_code,
1876 gll.ledger_id
1877 INTO l_ledger_status, l_primary,
1878 l_ledger_id
1879 FROM gl_ledgers gll, gl_ledger_configurations glc
1880 WHERE gll.short_name = p_ledger_short_name AND gll.NAME = glc.NAME;
1881 EXCEPTION
1882 WHEN xla_exceptions_pkg.application_exception
1883 THEN
1884 RAISE;
1885 WHEN OTHERS
1886 THEN
1887 l_group_id := -2;
1888 xla_exceptions_pkg.raise_message (p_location => 'xla_fsah_int_pvt.Get_group_id'
1889 );
1890 END;
1891
1892 IF UPPER (l_ledger_status) = 'CONFIRMED'
1893 THEN
1894 IF UPPER (l_primary) = 'PRIMARY'
1895 THEN
1896 IF (c_level_statement >= g_log_level)
1897 THEN
1898 TRACE (p_msg => 'Ledger short name '
1899 || p_ledger_short_name,
1900 p_module => l_log_module,
1901 p_level => c_level_statement
1902 );
1903 END IF;
1904
1905 SELECT ae_header_id
1906 BULK COLLECT INTO l_arry_ae_hdr_id
1907 FROM xla_ae_headers
1908 WHERE gl_transfer_status_code = 'N'
1909 AND accounting_entry_status_code = 'F'
1910 AND application_id = l_application_id
1911 AND accounting_date <= p_end_date
1912 -- AND accounting_batch_id = p_accounting_batch_id -- excluded so as in the next run records failed to transfer to PSFT will pick again
1913 AND ledger_id IN (
1914 SELECT DISTINCT target_ledger_id
1915 FROM gl_ledger_relationships
1916 WHERE source_ledger_id = l_ledger_id
1917 AND NVL (relationship_enabled_flag, 'N') =
1918 'Y');
1919
1920 IF l_arry_ae_hdr_id.COUNT = 0
1921 THEN
1922 IF (c_level_statement >= g_log_level)
1923 THEN
1924 TRACE (p_msg => 'No. of Records need updated are zero '
1925 || p_ledger_short_name,
1926 p_module => l_log_module,
1927 p_level => c_level_statement
1928 );
1929 END IF;
1930
1931 l_group_id := -1;
1932 ELSE
1933 IF (c_level_statement >= g_log_level)
1934 THEN
1935 TRACE (p_msg => 'Before Updating the group_id and Status in xla_ae_headers',
1936 p_module => l_log_module,
1937 p_level => c_level_statement
1938 );
1939 END IF;
1940
1941 /* DBMS_OUTPUT.put_line ('Before Updating the group_id and Status in xla_ae_headers'
1942 );*/
1943
1944 SELECT gl_journal_import_s.NEXTVAL
1945 INTO l_group_id
1946 FROM DUAL;
1947 END IF;
1948 ELSE
1949 l_group_id := -2;
1950 END IF;
1951 ELSE
1952 l_group_id := -2;
1953 END IF;
1954
1955 --DBMS_OUTPUT.put_line ('new_group_id ' || l_group_id);
1956
1957 IF (c_level_statement >= g_log_level)
1958 THEN
1959 TRACE (p_msg => 'new_group_id ' || l_group_id,
1960 p_module => l_log_module,
1961 p_level => c_level_statement
1962 );
1963 END IF;
1964
1965 ---------FND_LOG---------
1966 /* IF (c_level_procedure >= g_log_level)
1967 THEN
1968 TRACE (p_msg => 'END of get_group_id',
1969 p_level => c_level_procedure,
1970 p_module => l_log_module
1971 );
1972 END IF;*/
1973 RETURN l_group_id;
1974 EXCEPTION
1975 WHEN xla_exceptions_pkg.application_exception
1976 THEN
1977 RAISE;
1978 WHEN OTHERS
1979 THEN
1980 --DBMS_OUTPUT.put_line (SQLERRM);
1981
1982 IF (c_level_statement >= g_log_level)
1983 THEN
1984 TRACE (p_msg => SQLERRM,
1985 p_module => l_log_module,
1986 p_level => c_level_statement
1987 );
1988 END IF;
1989
1990 xla_exceptions_pkg.raise_message (p_location => 'XLA_FSAH_INT_PVT.get_group_id'
1991 );
1992 END get_group_id;
1993
1994 ----------------------------------------------------------------------------------
1995 -- Procedure (SET_GROUP_ID) To Setting Up Group ID
1996 ----------------------------------------------------------------------------------
1997 PROCEDURE set_group_id (
1998 p_ledger_short_name IN VARCHAR2,
1999 p_appl_short_name IN VARCHAR2,
2000 p_end_date IN DATE,
2001 p_accounting_batch_id IN NUMBER,
2002 p_group_id IN NUMBER,
2003 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_true,
2004 p_api_version IN NUMBER DEFAULT 1.0,
2005 p_commit IN BOOLEAN DEFAULT TRUE
2006 )
2007 IS
2008 --Declaring Process Variables
2009 l_pro_records NUMBER;
2010 l_ledger_status VARCHAR2 (100);
2011 l_primary VARCHAR2 (100);
2012 l_ledger_id NUMBER;
2013
2014 TYPE tab_ae_header_id IS TABLE OF NUMBER
2015 INDEX BY BINARY_INTEGER;
2016
2017 l_arry_ae_hdr_id_set tab_ae_header_id;
2018 l_log_module VARCHAR2 (240);
2019 l_application_id NUMBER;
2020 BEGIN
2021 -----FND_LOG-----------
2022 -- IF g_log_enabled THEN
2023 l_log_module := c_default_module || '.set_group_id';
2024
2025 -- END IF;
2026 IF (c_level_procedure >= g_log_level)
2027 THEN
2028 TRACE (p_msg => 'BEGIN of set_group_id',
2029 p_level => c_level_procedure,
2030 p_module => l_log_module
2031 );
2032 END IF;
2033
2034 BEGIN
2035 SELECT ledger_id
2036 INTO l_ledger_id
2037 FROM gl_ledgers
2038 WHERE short_name = p_ledger_short_name;
2039 EXCEPTION
2040 WHEN xla_exceptions_pkg.application_exception
2041 THEN
2042 RAISE;
2043 WHEN OTHERS
2044 THEN
2045 --DBMS_OUTPUT.put_line ('Invalid Application');
2046
2047 IF (c_level_statement >= g_log_level)
2048 THEN
2049 TRACE (p_msg => 'Invalid Ledger',
2050 p_module => l_log_module,
2051 p_level => c_level_statement
2052 );
2053 END IF;
2054 END;
2055
2056 BEGIN
2057 SELECT application_id
2058 INTO l_application_id
2059 FROM fnd_application
2060 WHERE application_short_name = p_appl_short_name;
2061 EXCEPTION
2062 WHEN xla_exceptions_pkg.application_exception
2063 THEN
2064 RAISE;
2065 WHEN OTHERS
2066 THEN
2067 IF (c_level_statement >= g_log_level)
2068 THEN
2069 TRACE (p_msg => 'Invalid Ledger',
2070 p_module => l_log_module,
2071 p_level => c_level_statement
2072 );
2073 END IF;
2074 /* p_err_message :=
2075 ' Application short name is not a Valid Value'
2076 || p_appl_short_name;
2077 xla_exceptions_pkg.raise_message (p_location => 'Invalid Application ID'
2078 );*/
2079 END;
2080
2081 -- Identifying the records to update
2082 SELECT ae_header_id
2083 BULK COLLECT INTO l_arry_ae_hdr_id_set
2084 FROM xla_ae_headers
2085 WHERE gl_transfer_status_code = 'N'
2086 AND accounting_entry_status_code = 'F'
2087 AND application_id = l_application_id
2088 AND accounting_date <= p_end_date
2089 -- AND accounting_batch_id = p_accounting_batch_id -- excluded so as in the next run records failed to transfer to PSFT will pick again
2090 AND ledger_id IN (
2091 SELECT DISTINCT target_ledger_id
2092 FROM gl_ledger_relationships
2093 WHERE source_ledger_id = l_ledger_id
2094 AND NVL (relationship_enabled_flag, 'N') = 'Y');
2095
2096 -- Updating the records with group id
2097
2098 /*DBMS_OUTPUT.put_line ( 'Total records identified to Updated = '
2099 || l_arry_ae_hdr_id_set.COUNT
2100 );*/
2101 FORALL i IN l_arry_ae_hdr_id_set.FIRST .. l_arry_ae_hdr_id_set.LAST
2102 UPDATE xla_ae_headers
2103 SET gl_transfer_status_code = 'S',
2104 GROUP_ID = p_group_id
2105 WHERE ae_header_id = l_arry_ae_hdr_id_set (i);
2106
2107 --dbms_output.put_line('Total records Updated = ' || SQL%BULK_ROWCOUNT);
2108
2109 ---------FND_LOG---------
2110 IF (c_level_procedure >= g_log_level)
2111 THEN
2112 TRACE (p_msg => 'END of set_group_id',
2113 p_level => c_level_procedure,
2114 p_module => l_log_module
2115 );
2116 END IF;
2117 END set_group_id;
2118
2119 ------------------------------------------------------------------------------------
2120 -- Procedure (SET_TRANSFER_STATUS)
2121 ------------------------------------------------------------------------------------
2122 -- Used to data setup for the new transfermation
2123 PROCEDURE set_transfer_status (
2124 p_group_id IN NUMBER,
2125 p_batch_status IN VARCHAR2,
2126 p_api_version IN NUMBER DEFAULT 1.0,
2127 p_return_status OUT NOCOPY VARCHAR2,
2128 p_err_msg OUT NOCOPY VARCHAR2
2129 )
2130 IS
2131 ------------------------------------------------------------------------------------
2132 -- Declaring Local Variabls
2133 ------------------------------------------------------------------------------------
2134 l_group_id xla_ae_headers.GROUP_ID%TYPE;
2135 l_batch_status VARCHAR2 (10);
2136 l_records_updated NUMBER;
2137 l_return_status VARCHAR2 (100);
2138 l_msg_data VARCHAR2 (100);
2139 l_msg_count NUMBER;
2140 l_log_module VARCHAR2 (240);
2141 BEGIN
2142 -----FND_LOG-----------
2143 IF g_log_enabled
2144 THEN
2145 l_log_module := c_default_module || '.set_transfer_status';
2146 END IF;
2147
2148 IF (c_level_procedure >= g_log_level)
2149 THEN
2150 TRACE (p_msg => 'BEGIN of set_transfer_status',
2151 p_level => c_level_procedure,
2152 p_module => l_log_module
2153 );
2154 END IF;
2155
2156 ----------------------
2157 fnd_global.apps_initialize (1001530, 20419, 200);
2158 ------------------------------------------------------------------------------------
2159 --Local Variable Values
2160 ------------------------------------------------------------------------------------
2161 l_group_id := p_group_id;
2162 l_batch_status := p_batch_status;
2163
2164 ------------------------------------------------------------------------------------
2165 --CONDITIONAL UPDATE FOR REPROCESSING DATA
2166 ------------------------------------------------------------------------------------
2167 IF l_batch_status = 'Y' AND l_group_id IS NOT NULL
2168 THEN
2169 ------------------------------------------------------------------------------------
2170 --Condition Sucess
2171 ------------------------------------------------------------------------------------
2172 UPDATE xla_ae_headers
2173 SET gl_transfer_status_code = 'Y',
2174 gl_transfer_date = TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
2175 last_update_date = TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
2176 last_updated_by = fnd_profile.VALUE ('USER_ID'),
2177 last_update_login = fnd_profile.VALUE ('LOGIN_ID')
2178 WHERE GROUP_ID = l_group_id
2179 AND accounting_entry_status_code = 'F'
2180 AND gl_transfer_status_code = 'S';
2181
2182 IF SQL%ROWCOUNT > 0
2183 THEN
2184 l_return_status := 'Y';
2185 l_msg_data :=
2186 'SETTING UP TRANSFER STATUS IS SUCESSFULLY COMPLETED';
2187 l_msg_count := '0';
2188 ELSE
2189 l_return_status := 'N';
2190 l_msg_data := 'Validation Failure';
2191 l_msg_count := '0';
2192 END IF;
2193 ELSIF l_batch_status = 'F' AND l_group_id IS NOT NULL
2194 THEN
2195 ------------------------------------------------------------------------------------
2196 --Condition Failure
2197 ------------------------------------------------------------------------------------
2198 UPDATE xla_ae_headers
2199 SET GROUP_ID = NULL,
2200 gl_transfer_status_code = 'N',
2201 last_update_date = TO_CHAR (SYSDATE, 'DD-MON-YYYY'),
2202 last_updated_by = fnd_profile.VALUE ('USER_ID'),
2203 last_update_login = fnd_profile.VALUE ('LOGIN_ID')
2204 WHERE GROUP_ID = l_group_id
2205 AND accounting_entry_status_code = 'F'
2206 AND gl_transfer_status_code = 'S';
2207
2208 IF SQL%ROWCOUNT > 0
2209 THEN
2210 l_return_status := 'Y';
2211 l_msg_data :=
2212 'SETTING UP TRANSFER STATUS IS SUCESSFULLY COMPLETED';
2213 l_msg_count := '0';
2214 ELSE
2215 l_return_status := 'N';
2216 l_msg_data := 'Validation Failure';
2217 l_msg_count := '0';
2218 END IF;
2219
2220 COMMIT;
2221 END IF;
2222
2223 ---------FND_LOG---------
2224 IF (c_level_procedure >= g_log_level)
2225 THEN
2226 TRACE (p_msg => 'END of set_transfer_status',
2227 p_level => c_level_procedure,
2228 p_module => l_log_module
2229 );
2230 END IF;
2231
2232 -------------------------
2233 ------------------------------------------------------------------------------------
2234 --Setting Up Out Parameters
2235 ------------------------------------------------------------------------------------
2236 p_return_status := l_return_status;
2237 p_err_msg := l_msg_data;
2238 /* p_msg_count := l_msg_count;*/
2239 ------------------------------------------------------------------------------------
2240 --Exception
2241 ------------------------------------------------------------------------------------
2242 EXCEPTION
2243 WHEN xla_exceptions_pkg.application_exception
2244 THEN
2245 RAISE;
2246 WHEN OTHERS
2247 THEN
2248 NULL;
2249 xla_exceptions_pkg.raise_message (p_location => 'xla_fsah_int_pvt.set_transfer_status'
2250 );
2251 END set_transfer_status;
2252 BEGIN
2253 g_log_level := fnd_log.g_current_runtime_level;
2254 g_log_enabled :=
2255 fnd_log.test (log_level => g_log_level,
2256 module => c_default_module);
2257
2258 IF NOT g_log_enabled
2259 THEN
2260 g_log_level := c_level_log_disabled;
2261 END IF;
2262 END xla_fsah_int_pvt;