[Home] [Help]
PACKAGE BODY: APPS.XLA_UPGRADE_PUB
Source
1 PACKAGE BODY XLA_UPGRADE_PUB AS
2 -- $Header: xlaugupg.pkb 120.41.12010000.2 2008/08/06 21:26:32 sbhaskar ship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | FILENAME |
9 | xlaugupg.pkb |
10 | |
11 | PACKAGE NAME |
12 | XLA_UPGRADE_PUB |
13 | |
14 | DESCRIPTION |
15 | This is a XLA package which contains all the APIs required by the |
16 | product teams to validate data in journal entry tables and also to |
17 | input data in analytical criteria and ae segment values tables. |
18 | HISTORY |
19 | 15-Dec-04 G. Bellary Created |
20 | 23-Dec-05 Koushik VS Modification of validation entries |
21 | 04-Jan-06 Koushik VS Modification of set_status_code being used |
22 | for Upgrade On Demand Project |
23 | 15-Feb-06 Jorge Larre Bug 5011584 |
24 | Set je_from_sla_flag to 'U' as part of the upgrade |
25 | 16-May-06 Jorge Larre Add pre_upgrade_set_status_code |
26 | 19-May-06 Jorge Larre Add the call to AR upgrade program as per |
27 | Herve Yu's confirmation by mail. |
28 | 25-May-06 Jorge Larre Bug 5222005: populate l_source_name from |
29 | XLA_SUBLEDGERS instead of populating l_application_name from |
30 | FND_APPLICATIONS_VL, and use it to select the lines to update in |
31 | GL_JE_HEADERS. |
32 | 17-Aug-2006 Jorge Larre Bug 5468416: Add a parameter of type VARCHAR2 |
33 | to call the Costing upgrade program. |
34 | 24-Aug-2006 Jorge Larre Bug 5473838: when calling the Costing upgrade |
35 | program, X_init_msg_list must be passed the value FND_API.G_FALSE. |
36 | 05-SEP-2006 Jorge Larre Bug 5484337: AR needs to store the calling |
37 | parameters in a new table (XLA_UPGRADE_REQUESTS). Add ledger_id and |
38 | period_name as calling parameters in set_status_code. |
39 | 07-NOV-2006 Jorge Larre Bug 5648571: Obsolete the procedure |
40 | set_status_code. This change is to be in sync with xlaugupg.pkh. |
41 | The code is left commented in case we decide to use it again. |
42 +===========================================================================*/
43 --=============================================================================
44 -- **************** declarations ********************
45 --=============================================================================
46
47
48 -------------------------------------------------------------------------------
49 -- declaring global variables
50 -------------------------------------------------------------------------------
51
52 g_batch_id INTEGER ;
53 g_batch_size INTEGER := 30000;
54 g_source_application_id NUMBER ;
55 g_application_id NUMBER;
56 g_validate_complete xla_upg_batches.VALIDATE_COMPLETE_FLAG%TYPE;
57 g_crsegvals_complete xla_upg_batches.CRSEGVALS_COMPLETE_FLAG%TYPE;
58 -------------------------------------------------------------------------------
59 -- declaring global pl/sql types
60 -------------------------------------------------------------------------------
61
62 TYPE t_entity_id IS TABLE OF
63 xla_transaction_entities.entity_id%type
64 INDEX BY BINARY_INTEGER;
65 TYPE t_error_flag IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
66 TYPE t_event_id IS TABLE OF
67 xla_events.event_id%type
68 INDEX BY BINARY_INTEGER;
69 TYPE t_header_id IS TABLE OF
70 xla_ae_headers.ae_header_id%type
71 INDEX BY BINARY_INTEGER;
72 TYPE t_line_num IS TABLE OF
73 xla_ae_lines.ae_line_num%type
74 INDEX BY BINARY_INTEGER;
75 TYPE t_seg_value IS TABLE OF
76 xla_ae_segment_values.segment_value%type
77 INDEX BY BINARY_INTEGER;
78 TYPE t_line_count IS TABLE OF
79 xla_ae_segment_values.ae_lines_count%type
80 INDEX BY BINARY_INTEGER;
81 TYPE t_seg_type IS TABLE OF
82 xla_ae_segment_values.segment_type_code%type
83 INDEX BY BINARY_INTEGER;
84 TYPE t_error_id IS TABLE OF
85 xla_upg_errors.upg_error_id%type
86 INDEX BY BINARY_INTEGER;
87 -------------------------------------------------------------------------------
88 -- declaring global constants
89 -------------------------------------------------------------------------------
90 -- The segment type code
91 C_BAL_SEGMENT CONSTANT VARCHAR2(1) := 'B';
92 C_MGT_SEGMENT CONSTANT VARCHAR2(1) := 'M';
93 --=============================================================================
94 -- *********** Local Trace Routine **********
95 --=============================================================================
96 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
97 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
98 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
99 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
100 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
101 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
102
103 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
104
105 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.XLA_UPGRADE_PUB';
106
107 g_log_level NUMBER;
108 g_log_enabled BOOLEAN;
109
110
111 -------------------------------------------------------------------------------
112 -- forward declarion of private procedures and functions
113 -------------------------------------------------------------------------------
114 PROCEDURE recover_previous_run;
115 --=============================================================================
116 -- *********** Local Trace Routine **********
117 --=============================================================================
118
119 PROCEDURE trace
120 (p_msg IN VARCHAR2
121 ,p_level IN NUMBER
122 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
123 BEGIN
124 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
125 fnd_log.message(p_level, p_module);
126 ELSIF p_level >= g_log_level THEN
127 fnd_log.string(p_level, p_module, p_msg);
128 END IF;
129
130 EXCEPTION
131 WHEN xla_exceptions_pkg.application_exception THEN
132 RAISE;
133 WHEN OTHERS THEN
134 xla_exceptions_pkg.raise_message
135 (p_location => 'XLA_UPGRADE_PUB.trace');
136 END trace;
137 --=============================================================================
138 -- *********** public procedures and functions **********
139 --=============================================================================
140 --=============================================================================
141 /*============================================================================+
142 | |
143 | Public Procedure |
144 | |
145 | Insert_Line_Criteria |
146 | |
147 | This routine is called to insert line criteria. |
148 | |
149 +============================================================================*/
150 PROCEDURE Insert_Line_Criteria (
151 p_batch_id IN NUMBER
152 , p_batch_size IN NUMBER
153 , p_application_id IN NUMBER
154 , p_error_detected OUT NOCOPY BOOLEAN
155 , p_overwrite_flag IN BOOLEAN)
156 IS
157 l_log_module VARCHAR2(240);
158 BEGIN
159 IF g_log_enabled THEN
160 l_log_module := C_DEFAULT_MODULE||'.Insert_Line_Criteria';
161 END IF;
162 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
163 trace
164 (p_msg => 'BEGIN of procedure Insert_Line_Criteria'
165 ,p_level => C_LEVEL_PROCEDURE
166 ,p_module =>l_log_module);
167 END IF;
168 SAVEPOINT before_insert_criteria;
169 IF p_overwrite_flag
170 THEN
171 delete xla_ae_line_details xal
172 where (ae_header_id, ae_line_num) IN
173 (select xlgt.ae_header_id,ae_line_num
174 from xla_upg_line_criteria_gt xlgt
175 where xal.ae_header_id = xlgt.ae_header_id
176 and xal.ae_line_num = xlgt.ae_line_num);
177 END IF;
178 update xla_upg_line_criteria_gt xlgt
179 set error_message_name = 'XLA_UPG_INVALID_CRITERIA'
180 where NOT EXISTS
181 (select 1
182 from xla_analytical_hdrs_b xanh
183 where xanh.amb_context_code = 'DEFAULT'
184 and xanh.analytical_criterion_code = xlgt.analytical_criterion_code
185 and xanh.analytical_criterion_type_code = xlgt.analytical_criterion_type_code);
186 IF ( SQL%ROWCOUNT > 0 ) THEN
187 p_error_detected := true;
188 ELSE
189 p_error_detected := false;
190 END IF;
191
192 INSERT INTO xla_analytical_dtl_vals
193 (
194 analytical_detail_value_id
195 ,analytical_criterion_code
196 ,analytical_criterion_type_code
197 ,amb_context_code
198 ,analytical_detail_char_1
199 ,analytical_detail_char_2
200 ,analytical_detail_char_3
201 ,analytical_detail_char_4
202 ,analytical_detail_char_5
203 ,analytical_detail_date_1
204 ,analytical_detail_date_2
205 ,analytical_detail_date_3
206 ,analytical_detail_date_4
207 ,analytical_detail_date_5
208 ,analytical_detail_number_1
209 ,analytical_detail_number_2
210 ,analytical_detail_number_3
211 ,analytical_detail_number_4
212 ,analytical_detail_number_5
213 ,creation_date
214 ,created_by
215 ,last_update_date
216 ,last_updated_by
217 ,last_update_login
218 )
219 SELECT xla_analytical_dtl_vals_s.nextval
220 ,analytical_criterion_code
221 ,analytical_criterion_type_code
222 ,amb_context_code
223 ,analytical_detail_char_1
224 ,analytical_detail_char_2
225 ,analytical_detail_char_3
226 ,analytical_detail_char_4
227 ,analytical_detail_char_5
228 ,analytical_detail_date_1
229 ,analytical_detail_date_2
230 ,analytical_detail_date_3
231 ,analytical_detail_date_4
232 ,analytical_detail_date_5
233 ,analytical_detail_number_1
234 ,analytical_detail_number_2
235 ,analytical_detail_number_3
236 ,analytical_detail_number_4
237 ,analytical_detail_number_5
238 ,sysdate
239 ,-1
240 ,sysdate
241 ,-1
242 ,-1
243 FROM ( SELECT
244 DISTINCT
245 analytical_criterion_code
246 ,analytical_criterion_type_code
247 ,'DEFAULT' amb_context_code
248 ,analytical_detail_char_1
249 ,analytical_detail_char_2
250 ,analytical_detail_char_3
251 ,analytical_detail_char_4
252 ,analytical_detail_char_5
253 ,analytical_detail_date_1
254 ,analytical_detail_date_2
255 ,analytical_detail_date_3
256 ,analytical_detail_date_4
257 ,analytical_detail_date_5
258 ,analytical_detail_number_1
259 ,analytical_detail_number_2
260 ,analytical_detail_number_3
261 ,analytical_detail_number_4
262 ,analytical_detail_number_5
263 FROM
264 XLA_UPG_LINE_CRITERIA_GT
265 WHERE ERROR_MESSAGE_NAME IS NOT NULL
266 ) adv1
267 WHERE NOT exists ( SELECT 'x'
268 FROM xla_analytical_dtl_vals adv2
269 WHERE adv1.analytical_criterion_code = adv2.analytical_criterion_code
270 AND adv1.analytical_criterion_type_code = adv2.analytical_criterion_type_code
271 AND adv1.amb_context_code = adv2.amb_context_code
272 --Detail 1
273 AND NVL( adv1.analytical_detail_char_1
274 ,NVL( TO_CHAR( adv1.analytical_detail_date_1
275 ,'J'||'.'||'HH24MISS'
276 )
277 ,NVL( TO_CHAR( adv1.analytical_detail_number_1
278 ,'TM'
279 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
280 )
281 ,'%'
282 )
283 )
284 )
285 = NVL( adv2.analytical_detail_char_1
286 ,NVL( TO_CHAR( adv2.analytical_detail_date_1
287 ,'J'||'.'||'HH24MISS'
288 )
289 ,NVL( TO_CHAR( adv2.analytical_detail_number_1
290 ,'TM'
291 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
292 )
293 ,'%'
294 )
295 )
296 )
297 --Detail 2
298 AND NVL( adv1.analytical_detail_char_2
299 ,NVL( TO_CHAR( adv1.analytical_detail_date_2
300 ,'J'||'.'||'HH24MISS'
301 )
302 ,NVL( TO_CHAR( adv1.analytical_detail_number_2
303 ,'TM'
304 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
305 )
306 ,'%'
307 )
308 )
309 )
310 = NVL( adv2.analytical_detail_char_2
311 ,NVL( TO_CHAR( adv2.analytical_detail_date_2
312 ,'J'||'.'||'HH24MISS'
313 )
314 ,NVL( TO_CHAR( adv2.analytical_detail_number_2
315 ,'TM'
316 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
317 )
318 ,'%'
319 )
320 )
321 )
322 --Detail 3
323 AND NVL( adv1.analytical_detail_char_3
324 ,NVL( TO_CHAR( adv1.analytical_detail_date_3
328 ,'TM'
325 ,'J'||'.'||'HH24MISS'
326 )
327 ,NVL( TO_CHAR( adv1.analytical_detail_number_3
329 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
330 )
331 ,'%'
332 )
333 )
334 )
335 = NVL( adv2.analytical_detail_char_3
336 ,NVL( TO_CHAR( adv2.analytical_detail_date_3
337 ,'J'||'.'||'HH24MISS'
338 )
339 ,NVL( TO_CHAR( adv2.analytical_detail_number_3
340 ,'TM'
341 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
342 )
343 ,'%'
344 )
345 )
346 )
347 --Detail 4
348 AND NVL( adv1.analytical_detail_char_4
349 ,NVL( TO_CHAR( adv1.analytical_detail_date_4
350 ,'J'||'.'||'HH24MISS'
351 )
352 ,NVL( TO_CHAR( adv1.analytical_detail_number_4
353 ,'TM'
354 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
355 )
356 ,'%'
357 )
358 )
359 )
360 = NVL( adv2.analytical_detail_char_4
361 ,NVL( TO_CHAR( adv2.analytical_detail_date_4
362 ,'J'||'.'||'HH24MISS'
363 )
364 ,NVL( TO_CHAR( adv2.analytical_detail_number_4
365 ,'TM'
366 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
367 )
368 ,'%'
369 )
370 )
371 )
372 --Detail 5
373 AND NVL( adv1.analytical_detail_char_5
374 ,NVL( TO_CHAR( adv1.analytical_detail_date_5
375 ,'J'||'.'||'HH24MISS'
376 )
377 ,NVL( TO_CHAR( adv1.analytical_detail_number_5
378 ,'TM'
379 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
380 )
381 ,'%'
382 )
383 )
384 )
385 = NVL( adv2.analytical_detail_char_5
386 ,NVL( TO_CHAR( adv2.analytical_detail_date_5
387 ,'J'||'.'||'HH24MISS'
388 )
389 ,NVL( TO_CHAR( adv2.analytical_detail_number_5
390 ,'TM'
391 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
392 )
393 ,'%'
394 )
395 )
396 )
397 );
398
399 INSERT INTO XLA_AE_LINE_DETAILS
400 (
401 ae_header_id
402 , ae_line_num
403 , analytical_detail_value_id
404 )
405 SELECT adv.analytical_detail_value_id
406 ,alcg.ae_header_id
407 ,alcg.ae_line_num
408
409 FROM
410 XLA_UPG_LINE_CRITERIA_GT alcg, xla_analytical_dtl_vals adv
411 WHERE --Detail 1
412 NVL( alcg.analytical_detail_char_1
413 ,NVL( TO_CHAR( alcg.analytical_detail_date_1
414 ,'J'||'.'||'HH24MISS'
415 )
416 ,NVL( TO_CHAR( alcg.analytical_detail_number_1
417 ,'TM'
418 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
419 )
420 ,'%'
421 )
422 )
423 )
424 = NVL( adv.analytical_detail_char_1
425 ,NVL( TO_CHAR( adv.analytical_detail_date_1
426 ,'J'||'.'||'HH24MISS'
427 )
428 ,NVL( TO_CHAR( adv.analytical_detail_number_1
429 ,'TM'
430 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
431 )
432 ,'%'
433 )
437 AND NVL( alcg.analytical_detail_char_2
434 )
435 )
436 --Detail 2
438 ,NVL( TO_CHAR( alcg.analytical_detail_date_2
439 ,'J'||'.'||'HH24MISS'
440 )
441 ,NVL( TO_CHAR( alcg.analytical_detail_number_2
442 ,'TM'
443 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
444 )
445 ,'%'
446 )
447 )
448 )
449 = NVL( adv.analytical_detail_char_2
450 ,NVL( TO_CHAR( adv.analytical_detail_date_2
451 ,'J'||'.'||'HH24MISS'
452 )
453 ,NVL( TO_CHAR( adv.analytical_detail_number_2
454 ,'TM'
455 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
456 )
457 ,'%'
458 )
459 )
460 )
461 --Detail 3
462 AND NVL( alcg.analytical_detail_char_3
463 ,NVL( TO_CHAR( alcg.analytical_detail_date_3
464 ,'J'||'.'||'HH24MISS'
465 )
466 ,NVL( TO_CHAR( alcg.analytical_detail_number_3
467 ,'TM'
468 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
469 )
470 ,'%'
471 )
472 )
473 )
474 = NVL( adv.analytical_detail_char_3
475 ,NVL( TO_CHAR( adv.analytical_detail_date_3
476 ,'J'||'.'||'HH24MISS'
477 )
478 ,NVL( TO_CHAR( adv.analytical_detail_number_3
479 ,'TM'
480 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
481 )
482 ,'%'
483 )
484 )
485 )
486 --Detail 4
487 AND NVL( alcg.analytical_detail_char_4
488 ,NVL( TO_CHAR( alcg.analytical_detail_date_4
489 ,'J'||'.'||'HH24MISS'
490 )
491 ,NVL( TO_CHAR( alcg.analytical_detail_number_4
492 ,'TM'
493 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
494 )
495 ,'%'
496 )
497 )
498 )
499 = NVL( adv.analytical_detail_char_4
500 ,NVL( TO_CHAR( adv.analytical_detail_date_4
501 ,'J'||'.'||'HH24MISS'
502 )
503 ,NVL( TO_CHAR( adv.analytical_detail_number_4
504 ,'TM'
505 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
506 )
507 ,'%'
508 )
509 )
510 )
511 --Detail 5
512 AND NVL( alcg.analytical_detail_char_5
513 ,NVL( TO_CHAR( alcg.analytical_detail_date_5
514 ,'J'||'.'||'HH24MISS'
515 )
516 ,NVL( TO_CHAR( alcg.analytical_detail_number_5
517 ,'TM'
518 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
519 )
520 ,'%'
521 )
522 )
523 )
524 = NVL( adv.analytical_detail_char_5
525 ,NVL( TO_CHAR( adv.analytical_detail_date_5
526 ,'J'||'.'||'HH24MISS'
527 )
528 ,NVL( TO_CHAR( adv.analytical_detail_number_5
529 ,'TM'
530 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
531 )
532 ,'%'
533 )
534 )
535 );
536 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
537 trace
538 (p_msg => 'END of procedure Insert_Line_Criteria'
539 ,p_level => C_LEVEL_PROCEDURE
543 ROLLBACK to SAVEPOINT before_insert_criteria;
540 ,p_module => l_log_module);
541 END IF;EXCEPTION
542 WHEN xla_exceptions_pkg.application_exception THEN
544 RAISE;
545 WHEN OTHERS THEN
546 ROLLBACK to SAVEPOINT before_insert_criteria;
547 xla_exceptions_pkg.raise_message
548 (p_location => 'XLA_UPGRADE_PUB.Validate_Entries');
549 END Insert_Line_Criteria;
550 /*============================================================================+
551 | |
552 | Public Procedure |
553 | |
554 | recover_previous_run |
555 | |
556 | This routine is called to recover the previous run. |
557 | |
558 +============================================================================*/
559 PROCEDURE recover_previous_run IS
560 cursor csr_previous_entity_errors IS
561 select entity_id
562 from xla_upg_errors
563 where error_level = 'N'
564 and upg_batch_id = g_batch_id;
565
566 cursor csr_previous_event_errors IS
567 select event_id
568 from xla_upg_errors
569 where error_level = 'E'
570 and upg_batch_id = g_batch_id;
571
572 cursor csr_previous_header_errors IS
573 select distinct ae_header_id
574 from xla_upg_errors
575 where error_level IN ('H','L','D')
576 and upg_batch_id = g_batch_id;
577
578 cursor csr_previous_errors IS
579 select upg_error_id
580 from xla_upg_errors
581 where upg_batch_id = g_batch_id;
582
583 cursor csr_segs_previous_run IS
584 select ae_header_id, segment_type_code
585 from xla_ae_segment_values
586 where upg_batch_id = g_batch_id;
587
588 -- Local Variables
589 l_entity_id t_entity_id;
590 l_event_id t_event_id;
591 l_header_id t_header_id;
592 l_error_id t_error_id;
593 l_seg_type t_seg_type;
594
595 BEGIN
596 OPEN csr_previous_entity_errors;
597 LOOP
598
599 FETCH csr_previous_entity_errors
600 BULK COLLECT INTO
601 l_entity_id
602 LIMIT g_batch_size;
603 EXIT when l_entity_id.COUNT = 0;
604
605 FORALL i IN l_entity_id.FIRST..l_entity_id.LAST
606 update xla_transaction_entities_upg
607 set upg_valid_flag = null
608 where entity_id = l_entity_id(i);
609
610 COMMIT;
611 END LOOP;
612 CLOSE csr_previous_entity_errors;
613
614 OPEN csr_previous_event_errors;
615 LOOP
616 FETCH csr_previous_event_errors
617 BULK COLLECT INTO
618 l_event_id
619 LIMIT g_batch_size;
620 EXIT WHEN l_event_id.COUNT = 0;
621
622 FORALL i IN l_event_id.FIRST..l_event_id.LAST
623 update xla_events
624 set upg_valid_flag = null
625 where event_id = l_event_id(i);
626
627 COMMIT;
628 END LOOP;
629 CLOSE csr_previous_event_errors;
630 OPEN csr_previous_header_errors;
631 LOOP
632 FETCH csr_previous_header_errors
633 BULK COLLECT INTO
634 l_header_id
635 LIMIT g_batch_size;
636 EXIT WHEN l_header_id.COUNT = 0;
637
638 FORALL i IN l_header_id.FIRST..l_header_id.LAST
639 update xla_ae_headers
640 set upg_valid_flag = null
641 where ae_header_id = l_header_id(i)
642 and application_id = g_application_id;
643
644 COMMIT;
645 END LOOP;
646 CLOSE csr_previous_header_errors;
647
648 OPEN csr_previous_errors;
649 LOOP
650 FETCH csr_previous_errors
651 BULK COLLECT INTO
652 l_error_id
653 LIMIT g_batch_size;
654 EXIT WHEN l_error_id.COUNT = 0;
655
656 FORALL i IN l_error_id.FIRST..l_error_id.LAST
657 delete xla_upg_errors
658 where upg_error_id = l_error_id(i);
659
660 COMMIT;
661 END LOOP;
662 CLOSE csr_previous_errors;
663
664 OPEN csr_segs_previous_run;
665 LOOP
666 FETCH csr_segs_previous_run
667 BULK COLLECT INTO
668 l_header_id, l_seg_type
669 LIMIT g_batch_size;
670 EXIT WHEN l_header_id.COUNT = 0;
671
672 FORALL i IN l_header_id.FIRST..l_header_id.LAST
673 delete xla_ae_segment_values
674 where ae_header_id = l_header_id(i)
675 and segment_type_code = l_seg_type(i);
676
677 COMMIT;
678 END LOOP;
679 CLOSE csr_segs_previous_run;
680
681 EXCEPTION
682 WHEN xla_exceptions_pkg.application_exception THEN
683 RAISE;
684
685 WHEN OTHERS THEN
686 xla_exceptions_pkg.raise_message
687 (p_location => 'XLA_UPGRADE_PUB.recover_previous_run');
688
692 | Public Procedure |
689 END recover_previous_run;
690 /*============================================================================+
691 | |
693 | |
694 | Set_Migration_Status_Code |
695 | |
696 | This routine is called to set the migration status code for an upgrade |
697 | for the particular periods. |
698 +============================================================================*/
699 FUNCTION set_migration_status_code
700 (p_application_id in number,
701 p_set_of_books_id in number,
702 p_period_name in varchar2 default null,
703 p_period_year in number default null)
704 return varchar2 IS
705
706 p_status_code varchar2(10);
707 l_application_id number;
708 l_set_of_books_id number;
709 l_period_name varchar2(15) ;
710 l_period_year number ;
711 L_LOG_MODULE VARCHAR2(240);
712
713 begin
714
715 IF g_log_enabled THEN
716 l_log_module := C_DEFAULT_MODULE||'.Set_Migration_Status_Code';
717 END IF;
718
719 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
720 trace
721 (p_msg => 'BEGIN of procedure Set_Migration_Status_Code'
722 ,p_level => C_LEVEL_PROCEDURE
723 ,p_module =>l_log_module);
724 END IF;
725
726 l_application_id := p_application_id;
727 l_set_of_books_id := p_set_of_books_id;
728 l_period_name := p_period_name;
729 l_period_year := p_period_year;
730
731 if (l_application_id is null ) then
732 p_status_code := 'F';
733 return p_status_code;
734 end if;
735
736
737 if ( l_set_of_books_id is null ) then
738
739 if (l_period_name is null and l_period_year is null) then
740
741 update gl_period_statuses
742 set migration_status_code = 'U'
743 where application_id = l_application_id
744 and migration_status_code = 'P';
745
746 p_status_code := 'P';
747 COMMIT;
748 return p_status_code;
749
750 elsif l_period_name is null then
751
752 update gl_period_statuses
753 set migration_status_code = 'U'
754 where period_year = l_period_year
755 and migration_status_code = 'P'
756 and application_id = l_application_id;
757
758 p_status_code := 'P';
759 COMMIT;
760 return p_status_code;
761
762 elsif l_period_year is null then
763
764 update gl_period_statuses
768 and application_id = l_application_id;
765 set migration_status_code = 'U'
766 where period_name = l_period_name
767 and migration_status_code = 'P'
769
770 p_status_code := 'P';
771 COMMIT;
772 return p_status_code;
773
774 elsif (l_period_name is not null and l_period_year is not null) then
775
776 update gl_period_statuses
777 set migration_status_code = 'U'
778 where period_year = l_period_year
779 and period_name = l_period_name
780 and migration_status_code = 'P'
781 and application_id = l_application_id;
782
783 p_status_code := 'P';
784 COMMIT;
785 return p_status_code;
786
787 end if;
788
789 end if;
790
791 /* Set_Of_Books_ID is not null */
792
793 if (l_period_name is null and l_period_year is null) then
794
795 update gl_period_statuses
796 set migration_status_code = 'U'
797 where application_id = l_application_id
798 and migration_status_code = 'P'
799 and ledger_id = l_set_of_books_id;
800
801 p_status_code := 'P';
802 COMMIT;
803 return p_status_code;
804
805 elsif l_period_name is null then
806
807 update gl_period_statuses
808 set migration_status_code = 'U'
809 where period_year = l_period_year
810 and migration_status_code = 'P'
811 and ledger_id = l_set_of_books_id
812 and application_id = l_application_id;
813
814 p_status_code := 'P';
815 COMMIT;
816 return p_status_code;
817
818 elsif l_period_year is null then
819
820 update gl_period_statuses
821 set migration_status_code = 'U'
822 where period_name = l_period_name
823 and migration_status_code = 'P'
824 and ledger_id = l_set_of_books_id
825 and application_id = l_application_id;
826
827 p_status_code := 'P';
828 COMMIT;
829 return p_status_code;
830
831 elsif (l_period_name is not null and l_period_year is not null) then
832
833 update gl_period_statuses
834 set migration_status_code = 'U'
835 where period_year = l_period_year
836 and period_name = l_period_name
837 and migration_status_code = 'P'
838 and ledger_id = l_set_of_books_id
839 and application_id = l_application_id;
840
841 p_status_code := 'P';
842 COMMIT;
843 return p_status_code;
844
845 end if;
846
847 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
848 trace
849 (p_msg => 'END of procedure Set_Migration_Status_Code'
850 ,p_level => C_LEVEL_PROCEDURE
851 ,p_module => l_log_module);
852 END IF;
853
854 EXCEPTION
855 WHEN xla_exceptions_pkg.application_exception THEN
856
857 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
858 trace
859 (p_msg => 'Set_Migration_Status_Code ended in error'
860 ,p_level => C_LEVEL_PROCEDURE
861 ,p_module => l_log_module);
862 END IF;
863
864 RAISE;
865 WHEN OTHERS THEN
866 xla_exceptions_pkg.raise_message
867 (p_location => 'XLA_UPGRADE_PUB.Set_Migration_Status_Code');
868
869 end set_migration_status_code;
870
871 /*============================================================================+
872 | |
873 | Public Procedure |
874 | |
875 | Set_Status_Code |
876 | |
877 | This procedure is called during the Upgrade On-Demand, to update the |
878 | status code, and also to call the product team hooks. |
879 +============================================================================*/
880
881 --PROCEDURE set_status_code
882 --(p_error_buf OUT NOCOPY VARCHAR2,
883 -- p_retcode OUT NOCOPY NUMBER,
884 -- p_application_id IN NUMBER,
885 -- p_ledger_id IN NUMBER,
886 -- p_period_name IN VARCHAR2,
887 -- p_number_of_workers IN NUMBER,
888 -- p_batch_size IN NUMBER) IS
889
893 --l_ledger_id NUMBER;
890 --l_application_id NUMBER;
891 --l_source_name XLA_SUBLEDGERS.JE_SOURCE_NAME%TYPE;
892 --l_application_name FND_APPLICATION_VL.APPLICATION_NAME%TYPE;
894 --l_period_name VARCHAR2(15) ;
895 --l_batch_size NUMBER;
896 --l_number_of_workers NUMBER;
897 --l_error_buf VARCHAR2(1000);
898 --l_retcode NUMBER;
899 --l_processed VARCHAR2(1) := ' ';
900 --l_start_date date;
901 --l_end_date date;
902 --l_log_module VARCHAR2(240);
903 --NO_UPGRADE EXCEPTION;
904 --UPGRADE_ERROR EXCEPTION;
905 --l_temp BOOLEAN;
906 --l_retcode_char VARCHAR2(10);
907
908 --BEGIN
909
910 -- IF g_log_enabled THEN
911 -- l_log_module := C_DEFAULT_MODULE||'.set_status_code';
912 -- END IF;
913
914 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
915 -- trace('set_status_code.Begin',C_LEVEL_STATEMENT,l_log_module);
916 -- END IF;
917
918 -- IF p_batch_size IS NOT NULL THEN
919 -- l_batch_size := p_batch_size;
920 -- ELSE
921 -- l_batch_size := 10000;
922 -- END IF;
923
924 -- IF p_number_of_workers IS NOT NULL THEN
925 -- l_number_of_workers := p_number_of_workers;
926 -- ELSE
927 -- l_number_of_workers := 1;
928 -- END IF;
929
930 -- l_application_id := p_application_id;
931 -- l_period_name := p_period_name;
932 -- l_ledger_id := p_ledger_id;
933
934 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
935 -- trace('l_application_id '||l_application_id,
936 -- C_LEVEL_STATEMENT, l_Log_module);
937 -- END IF;
938
939 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
940 -- trace('l_ledger_id '||l_ledger_id,
941 -- C_LEVEL_STATEMENT, l_Log_module);
942 -- END IF;
943
944 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
945 -- trace('l_period_name '||l_period_name,
946 -- C_LEVEL_STATEMENT, l_Log_module);
947 -- END IF;
948
949 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
950 -- trace('l_number_of_workers '||l_number_of_workers,
951 -- C_LEVEL_STATEMENT, l_Log_module);
952 -- END IF;
953
954 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
955 -- trace('l_batch_size '||l_batch_size,
956 -- C_LEVEL_STATEMENT, l_Log_module);
957 -- END IF;
958
959 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
960 -- trace('Processing periods...',
961 -- C_LEVEL_STATEMENT, l_Log_module);
962 -- END IF;
963
964 /* FA uses GL's period */
965
966 -- IF p_application_id = 140 then
967 -- l_application_id :=101;
968 -- END IF;
969
970 -- Check with application id 707 is done separately, since there will be
971 -- no rows in GL_PERIOD_STATUSES corresponding to Application ID 707.
972 -- Associated Applications are 201 (PO) and 401 (INV).
973
974 -- IF p_application_id = 707 then
975
976 -- Since Application ID 707 will have no rows in GL_PERIOD_STATUSES
977 -- we are getting the minimum of start date for one of the two
978 -- applications which are associated with Costing (707)
979
980 -- SELECT start_date
981 -- INTO l_start_date
982 -- FROM gl_period_statuses
983 -- WHERE application_id = 401
984 -- AND ledger_id = p_ledger_id
985 -- AND period_name = p_period_name;
986
987 --fnd_file.put_line(fnd_file.log, '*Start date : '|| to_char(l_start_date));
988
989 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
990 -- trace('Start date of upgrade '|| l_start_date,
991 -- C_LEVEL_STATEMENT, l_Log_module);
992 -- END IF;
993
994 -- SELECT min(start_date)
995 -- INTO l_end_date
996 -- FROM gl_period_statuses
997 -- WHERE migration_status_code = 'U'
998 -- AND ledger_id = p_ledger_id
999 -- AND application_id = 401;
1000
1001 --fnd_file.put_line(fnd_file.log, '*End date : '|| to_char(l_end_date));
1002
1003 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1004 -- trace('End date of upgrade '|| l_end_date,
1005 -- C_LEVEL_STATEMENT, l_Log_module);
1006 -- END IF;
1007
1008 -- if l_end_date is NULL it means that the database that the On Demand
1009 -- Program is being run on is a fresh installation or Upgrade has
1010 -- never been performed on this instance. Hence, warning will be raised.
1011
1012 -- IF l_end_date is NULL THEN
1013 -- RAISE NO_UPGRADE;
1014 -- END IF;
1015
1016 -- Updation of GL Period Statuses.
1017
1018 -- UPDATE gl_period_statuses
1019 -- SET migration_status_code = 'P'
1020 -- WHERE ledger_id = l_ledger_id
1024 -- AND adjustment_period_flag = 'N'
1021 -- AND (start_date >= l_start_date
1022 -- and end_date < l_end_date)
1023 -- AND application_id in (201,401)
1025 -- AND migration_status_code IS NULL;
1026
1027 --fnd_file.put_line(fnd_file.log, '*Periods updated to P : '|| to_char(SQL%ROWCOUNT));
1028
1029 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1030 -- trace('Updated gl_period_statuses. '
1031 -- , C_LEVEL_STATEMENT, l_Log_module);
1032 -- END IF;
1033
1034 -- ELSE /*End for 707 Case and begin for all other applications*/
1035
1036 -- Choosing the start date of the first period that
1037 -- has to be migrated.
1038
1039 -- SELECT start_date
1040 -- INTO l_start_date
1041 -- FROM gl_period_statuses
1042 -- WHERE application_id = l_application_id
1043 -- AND ledger_id = p_ledger_id
1044 -- AND period_name = p_period_name;
1045
1046 --fnd_file.put_line(fnd_file.log, '*Start date : '|| to_char(l_start_date));
1047
1048 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1049 -- trace('Start date of upgrade '|| l_start_date,
1050 -- C_LEVEL_STATEMENT, l_Log_module);
1051 -- END IF;
1052
1053 -- Choosing the start date of the last period that
1054 -- was migrated.
1055
1056 -- SELECT min(start_date)
1057 -- INTO l_end_date
1058 -- FROM gl_period_statuses
1059 -- WHERE migration_status_code = 'U'
1060 -- AND ledger_id = p_ledger_id
1061 -- AND application_id = l_application_id;
1062
1063 --fnd_file.put_line(fnd_file.log, '*End date : '|| to_char(l_end_date));
1064
1065 -- IF l_end_date is NULL THEN
1066 -- RAISE NO_UPGRADE;
1067 -- END IF;
1068
1069 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1070 -- trace('Ending date of upgrade '|| l_end_date,
1071 -- C_LEVEL_STATEMENT, l_Log_module);
1072 -- END IF;
1073
1074 -- UPDATE gl_period_statuses
1075 -- SET migration_status_code = 'P'
1076 -- WHERE application_id = l_application_id
1077 -- AND ledger_id = l_ledger_id
1078 -- AND (start_date >= l_start_date
1079 -- AND end_date < l_end_date)
1080 -- AND adjustment_period_flag = 'N'
1081 -- AND migration_status_code IS NULL;
1082
1083 --fnd_file.put_line(fnd_file.log, '*Periods updated to P : '|| to_char(SQL%ROWCOUNT));
1084
1085 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1086 -- trace('Updated gl_period_statuses',
1087 -- C_LEVEL_STATEMENT, l_Log_module);
1088 -- END IF;
1089
1090 -- END IF;
1091
1092 -- IF l_start_date <> l_end_date THEN
1093
1094 -- DELETE FROM xla_upgrade_dates;
1095
1096 -- Inserting details of ledgers , start date and end date
1097 -- for use by product teams
1098 -- INSERT INTO xla_upgrade_dates
1099 -- (ledger_id
1100 -- ,start_date
1101 -- ,end_date)
1102 -- VALUES (l_ledger_id
1103 -- ,l_start_date
1104 -- ,l_end_date-1);
1105
1106 /* Call Product Team hooks for Accounting Upgrade */
1107
1108 -- IF l_application_id = 101 THEN
1109
1110 -- FA_UPGHARNESS_PKG.fa_master_upg(
1111 -- l_error_buf,
1112 -- l_retcode,
1113 -- l_number_of_workers,
1114 -- l_batch_size);
1115
1116 --fnd_file.put_line(fnd_file.log, '*Return code from FA: '|| to_char(l_retcode));
1117
1118 -- ELSIF l_application_id = 200 then
1119
1120 -- AP_XLA_UPGRADE_PKG.AP_XLA_Upgrade_OnDemand(
1121 -- Errbuf => l_error_buf,
1122 -- Retcode => l_retcode,
1123 -- P_Batch_Size => l_batch_size,
1124 -- P_Num_Workers => l_number_of_workers);
1125
1126 --fnd_file.put_line(fnd_file.log, '*Return code from AP: '|| to_char(l_retcode));
1127
1128 -- IF l_retcode = 0 THEN
1129 -- ZX_ON_DEMAND_TRX_UPGRADE_PKG.zx_trx_update_mgr(
1130 -- X_errbuf => l_error_buf,
1131 -- X_retcode => l_retcode,
1132 -- X_batch_size =>l_batch_size,
1133 -- X_Num_Workers =>l_number_of_workers,
1134 -- p_application_id => l_application_id);
1135
1136 -- fnd_file.put_line(fnd_file.log, '*Return code from ZX: '|| to_char(l_retcode));
1137 -- END IF;
1138
1139 -- ELSIF l_application_id = 222 then
1140 -- AR_UPGHARNESS_PKG.ar_master_upg(
1141 -- l_error_buf,
1142 -- l_retcode,
1143 -- l_ledger_id,
1144 -- l_period_name,
1145 -- l_number_of_workers,
1146 -- l_batch_size);
1147
1148 --fnd_file.put_line(fnd_file.log, '*Return code from AR: '|| to_char(l_retcode));
1149
1150 -- IF l_retcode = 0 THEN
1151 -- ZX_ON_DEMAND_TRX_UPGRADE_PKG.zx_trx_update_mgr(
1152 -- X_errbuf => l_error_buf,
1153 -- X_retcode => l_retcode,
1154 -- X_batch_size =>l_batch_size,
1155 -- X_Num_Workers =>l_number_of_workers,
1156 -- p_application_id => l_application_id);
1157
1158 -- fnd_file.put_line(fnd_file.log, '*Return code from ZX: '|| to_char(l_retcode));
1159 -- END IF;
1160 -- ELSIF l_application_id = 275 THEN
1161
1162 -- PA_UPGHARNESS_PKG.pa_master_upg
1163 -- (l_error_buf
1164 -- ,l_retcode
1165 -- ,l_number_of_workers
1166 -- ,l_batch_size);
1167
1168 --fnd_file.put_line(fnd_file.log, '*Return code from PA: '|| to_char(l_retcode));
1169
1170 -- ELSIF l_application_id = 707 THEN
1171
1172 -- CST_SLA_UPDATE_PKG.Update_Proc_MGR (
1173 -- X_errbuf => l_error_buf,
1174 -- X_retcode => l_retcode_char,
1175 -- X_api_version => 1.0,
1176 -- X_init_msg_list => FND_API.G_FALSE,
1177 -- X_batch_size => l_batch_size,
1181 -- X_Argument6 => 'NULL',
1178 -- X_Num_Workers => l_number_of_workers,
1179 -- X_Argument4 => 'NULL',
1180 -- X_Argument5 => 'NULL',
1182 -- X_Argument7 => 'NULL',
1183 -- X_Argument8 => 'NULL',
1184 -- X_Argument9 => 'NULL',
1185 -- X_Argument10 => 'NULL');
1186
1187 --fnd_file.put_line(fnd_file.log, '*Return code from COST: '|| l_retcode_char);
1188 -- IF l_retcode_char = 'S' THEN
1189 -- l_retcode := 0;
1190 -- END IF;
1191
1192 -- END IF;
1193
1194 -- Updating gl_je_headers
1195 -- Check to ensure rows are not updated if process errors out
1196
1197 -- IF l_retcode = 0 THEN
1198 /* means no error in the upgrade */
1199
1200 -- IF p_application_id <> 707 THEN
1201
1202 -- SELECT je_source_name
1203 -- INTO l_source_name
1204 -- FROM XLA_SUBLEDGERS
1205 -- WHERE application_id = p_application_id;
1206
1207 --fnd_file.put_line(fnd_file.log, '*Source name : '|| l_source_name);
1208
1209 -- UPDATE gl_je_headers a
1210 -- SET a.je_from_sla_flag = 'U'
1211 -- WHERE (decode(a.je_source, l_source_name, p_application_id)
1212 -- ,ledger_id, period_name) in
1213 -- (SELECT application_id, ledger_id, period_name
1214 -- FROM gl_period_statuses b
1215 -- WHERE b.start_date >= l_start_date
1216 -- AND b.end_date < l_end_date
1217 -- AND b.ledger_id = l_ledger_id
1218 -- AND application_id = p_application_id)
1219 -- AND a.ledgeR_id = l_ledger_id;
1220
1221 --fnd_file.put_line(fnd_file.log, '*Flags updated to U : '|| to_char(SQL%ROWCOUNT));
1222
1223 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1224 -- trace('Updated gl_je_headers',
1225 -- C_LEVEL_STATEMENT, l_Log_module);
1226 -- END IF;
1227
1228 -- ELSE
1229
1230 -- UPDATE gl_je_headers a
1231 -- SET a.je_from_sla_flag = 'U'
1232 -- WHERE (decode(a.je_source,'Purchasing',201,'Inventory',401)
1233 -- ,ledger_id, period_name) in
1234 -- (SELECT application_id, ledger_id, period_name
1235 -- FROM gl_period_statuses b
1236 -- WHERE b.start_date >= l_start_date
1237 -- AND b.end_date < l_end_date
1238 -- AND b.ledger_id = l_ledger_id
1239 -- AND application_id in (201,401))
1240 -- AND a.ledgeR_id = l_ledger_id;
1241
1242 --fnd_file.put_line(fnd_file.log, '*Flags updated to U : '|| to_char(SQL%ROWCOUNT));
1243
1244 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1245 -- trace('Updated gl_je_headers',
1246 -- C_LEVEL_STATEMENT, l_Log_module);
1247 -- END IF;
1248
1249 -- COMMIT;
1250 -- END IF;
1251
1252 -- ELSE
1253
1254 -- IF p_application_id = 707 THEN
1255
1256 -- UPDATE gl_period_statuses
1257 -- SET migration_status_code = NULL
1258 -- WHERE ledger_id = l_ledger_id
1259 -- AND (start_date >= l_start_date
1260 -- and end_date < l_end_date)
1261 -- AND application_id in (201,401)
1262 -- AND adjustment_period_flag = 'N'
1263 -- AND migration_status_code = 'P';
1264
1265 --fnd_file.put_line(fnd_file.log, '*Migration status code back to NULL : '|| to_char(SQL%ROWCOUNT));
1266
1267 -- ELSE
1268
1269 -- UPDATE gl_period_statuses
1270 -- SET migration_status_code = NULL
1271 -- WHERE application_id = l_application_id
1272 -- AND ledger_id = l_ledger_id
1273 -- AND (start_date >= l_start_date
1274 -- AND end_date < l_end_date)
1275 -- AND adjustment_period_flag = 'N'
1276 -- AND migration_status_code = 'P';
1277
1278 --fnd_file.put_line(fnd_file.log, '*Migration status code back to NULL : '|| to_char(SQL%ROWCOUNT));
1279
1280 -- END IF;
1281
1282 -- RAISE UPGRADE_ERROR;
1283
1284 -- END IF;
1285
1286 -- END IF;
1287
1288 -- COMMIT;
1289
1290 --EXCEPTION
1291 -- WHEN NO_UPGRADE THEN
1292 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1293 -- trace('This is either a fresh R12 installation or upgrade from
1294 -- an existing 11i instance has not taken place.',
1295 -- C_LEVEL_ERROR, l_Log_module);
1296 -- END IF;
1297
1298 -- fnd_file.put_line(fnd_file.log, 'This is either a fresh R12
1299 -- installation or upgrade
1300 -- from an existing 11i instance
1301 -- has not taken place.');
1302
1303 -- l_temp := fnd_concurrent.set_completion_status
1304 -- (status => 'WARNING'
1305 -- ,message => NULL);
1306
1307 -- WHEN UPGRADE_ERROR THEN
1308 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1312
1309 -- trace('There has been an error in the Product Upgrade',
1310 -- C_LEVEL_ERROR, l_Log_module);
1311 -- END IF;
1313 -- fnd_file.put_line(fnd_file.log, 'Product Team API Failed');
1314
1315 -- l_temp := fnd_concurrent.set_completion_status
1316 -- (status => 'ERROR'
1317 -- ,message => NULL);
1318
1319 -- WHEN xla_exceptions_pkg.application_exception THEN
1320 -- RAISE;
1321 -- WHEN OTHERS THEN
1322 -- xla_exceptions_pkg.raise_message
1323 -- (p_location => 'XLA_UPGRADE_PUB.set_status_code');
1324 --END SET_STATUS_CODE;
1325
1326 /*============================================================================+
1327 | |
1328 | Public Procedure |
1329 | |
1330 | Validate_Header_Line_Entries |
1331 | |
1332 | This routine is called to validate the Header entries in upgrade. |
1333 | |
1334 +============================================================================*/
1335
1336 PROCEDURE Validate_Header_Line_Entries
1337 (p_application_id IN NUMBER,
1338 p_header_id IN NUMBER) IS
1339
1340 l_entity_id t_entity_id;
1341 l_event_id t_event_id;
1342 l_header_id t_header_id;
1343 l_line_num t_line_num;
1344 l_header_error1 t_error_flag;
1345 l_header_error2 t_error_flag;
1346 l_header_error3 t_error_flag;
1347 l_header_error4 t_error_flag;
1348 l_header_error5 t_error_flag;
1349 l_line_error1 t_error_flag;
1350 l_line_error2 t_error_flag;
1351 l_line_error3 t_error_flag;
1352 l_line_error4 t_error_flag;
1353 l_line_error5 t_error_flag;
1354 l_line_error6 t_error_flag;
1355 l_line_error7 t_error_flag;
1356 l_line_error8 t_error_flag;
1357 l_line_error9 t_error_flag;
1358 l_line_error10 t_error_flag;
1359 l_log_module VARCHAR2(240);
1360 l_rowcount number(15) := 0;
1361
1362 BEGIN
1363
1364 g_application_id := p_application_id;
1365
1366 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1367 trace
1368 (p_msg => 'BEGIN of procedure Validate_Header_Line_Entries'
1369 ,p_level => C_LEVEL_PROCEDURE
1370 ,p_module =>l_log_module);
1371 END IF;
1372
1373 IF g_log_enabled THEN
1374 l_log_module := C_DEFAULT_MODULE||'.Validate_Header_Line_Entries';
1375 END IF;
1376
1377 -- Deleting all xla_upg_errors from previous run
1378
1379 delete from xla_upg_errors
1380 where application_id = p_application_id
1381 and error_message_name IN ('XLA_UPG_LEDGER_INVALID'
1382 ,'XLA_UPG_NO_BUDGET_VER'
1383 ,'XLA_UPG_NO_ENC_TYPE'
1384 ,'XLA_UPG_BALTYP_INVALID'
1385 ,'XLA_UPG_HDR_WO_EVT'
1386 ,'XLA_UPG_UNBAL_ACCAMT'
1387 ,'XLA_UPG_UNBAL_ENTRAMT'
1388 ,'XLA_UPG_HDR_WO_LINES'
1389 , 'XLA_UPG_CCID_INVALID'
1390 ,'XLA_UPG_CCID_SUMACCT'
1391 ,'XLA_UPG_CCID_NOBUDGET'
1392 ,'XLA_UPG_PARTY_TYP_INVALID'
1393 ,'XLA_UPG_DRCR_NULL'
1394 ,'XLA_UPG_ENTAMT_DIFF_ACCAMT'
1395 ,'XLA_UPG_LINE_NO_HDR'
1396 ,'XLA_UPG_ENTAMT_ACCAMT_DIFFSIDE'
1397 ,'XLA_UPG_PARTY_ID_INVALID'
1398 ,'XLA_UPG_PARTY_SITE_INVALID'
1399 ,'XLA_LINE_VERIFICATION_RECORD'
1400 ,'XLA_HDR_VERIFICATION_RECORD');
1401
1402 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
1403 (upg_error_id, application_id, upg_source_application_id, creation_date
1404 , created_by, last_update_date, last_updated_by, upg_batch_id
1405 , error_level, error_message_name, ae_header_id)
1406 (select
1407 xla_upg_errors_s.nextval
1408 ,g_application_id
1409 ,-9999
1410 ,sysdate
1411 ,-1
1412 ,sysdate
1413 ,-1
1414 ,-9999
1415 , 'H'
1416 ,decode(grm.multiplier,1,'XLA_UPG_LEDGER_INVALID'
1417 ,2,'XLA_UPG_NO_BUDGET_VER'
1418 ,3,'XLA_UPG_NO_ENC_TYPE'
1419 ,4,'XLA_UPG_BALTYP_INVALID'
1420 ,'XLA_UPG_HDR_WO_EVT')
1421 ,ae_header_id
1422 from ( select ae_header_id
1423 ,CASE when gll.ledger_id IS NULL THEN 'Y'
1424 ELSE 'N' END header_error1-- Ledger Id is Invalid
1425 ,CASE when xah.BALANCE_TYPE_CODE = 'B'
1426 and xah.BUDGET_VERSION_ID IS NULL THEN 'Y'
1427 ELSE 'N' END header_error2-- No Budget Version
1428 ,CASE when xah.BALANCE_TYPE_CODE = 'E'
1429 and xah.ENCUMBRANCE_TYPE_ID IS NULL THEN 'Y'
1430 ELSE 'N' END header_error3-- No Enc Type
1431 ,CASE when xah.BALANCE_TYPE_CODE NOT IN ('A','B','E')
1432 THEN 'Y'
1433 ELSE 'N' END header_error4-- Balance type code invalid
1434 ,CASE when xe.event_id IS NULL THEN 'Y'
1435 ELSE 'N' END header_error5-- Header without valid event
1436 from xla_ae_headers xah
1440 and xe.event_id (+) = xah.event_id
1437 ,gl_ledgers gll
1438 ,xla_events xe
1439 where gll.ledger_id (+) = xah.ledger_id
1441 and (gll.ledger_id IS NULL OR
1442 (xah.BALANCE_TYPE_CODE = 'B' AND
1443 xah.BUDGET_VERSION_ID IS NULL) OR
1444 (xah.BALANCE_TYPE_CODE = 'E' AND
1445 xah.ENCUMBRANCE_TYPE_ID IS NULL) OR
1446 xah.BALANCE_TYPE_CODE NOT IN ('A','B','E') OR
1447 xe.event_id IS NULL)
1448 and xah.application_id = p_application_id
1449 and xah.ae_header_id = p_header_id) xah
1450 ,gl_row_multipliers grm
1451 where grm.multiplier < 6
1452 and decode(grm.multiplier,
1453 1,header_error1,
1454 2,header_error2,
1455 3,header_error3,
1456 4,header_error4,
1457 header_error5) = 'Y');
1458 COMMIT;
1459
1460 l_rowcount := l_rowcount + sql%rowcount;
1461
1462 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
1463 (upg_error_id, application_id, upg_source_application_id, creation_date
1464 , created_by, last_update_date, last_updated_by, upg_batch_id
1465 , error_level, error_message_name, ae_header_id)
1466 (select
1467 xla_upg_errors_s.nextval
1468 ,g_application_id
1469 ,-9999
1470 ,sysdate
1471 ,-1
1472 ,sysdate
1473 ,-1
1474 ,-9999
1475 , 'H'
1476 ,decode(grm.multiplier,1,'XLA_UPG_UNBAL_ACCAMT'
1477 ,'XLA_UPG_UNBAL_ENTRAMT')
1478 ,ae_header_id
1479 from (select /*+ no_merge */ xal.ae_header_id,
1480 case when nvl(sum(accounted_dr), 0) <> nvl(sum(accounted_cr), 0)
1481 then 'Y' else 'N' end header_error1, -- amts not balanced,
1482 case when nvl(sum(entered_dr), 0) <> nvl(sum(entered_cr), 0)
1483 then 'Y' else 'N' end header_error2 -- entered amts not balanced
1484 from xla_ae_lines xal
1485 where xal.application_id = p_application_id
1486 and xal.ae_header_id = p_header_id
1487 and xal.currency_code <> 'STAT'
1488 and xal.ledger_id in (select gll.ledger_id
1489 from gl_ledgers gll
1490 where gll.suspense_allowed_flag = 'N')
1491 group by xal.ae_header_id
1492 having nvl(sum(accounted_dr), 0)
1493 <> nvl(sum(accounted_cr), 0)
1494 or nvl(sum(entered_dr), 0)
1495 <> nvl(sum(entered_cr), 0)) xal,
1496 gl_row_multipliers grm
1497 where xal.ae_header_id in ( select /*+ use_hash(xah) swap_join_inputs(xah) */
1498 xah.ae_header_id
1499 from xla_ae_headers xah
1500 where xah.application_id = p_application_id
1501 and xah.ae_header_id = p_header_id
1502 and xah.balance_type_code <> 'B')
1503 and grm.multiplier < 3
1504 and decode(grm.multiplier, 1, header_error1, header_error2) = 'Y');
1505
1506 COMMIT;
1507
1508 l_rowcount := l_rowcount + sql%rowcount;
1509
1510 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
1511 (upg_error_id, application_id, upg_source_application_id,creation_date
1512 , created_by, last_update_date, last_updated_by, upg_batch_id
1513 , error_level, ae_header_id, error_message_name)
1514 (select xla_upg_errors_s.nextval
1515 ,g_application_id
1516 ,-9999
1517 ,sysdate
1518 ,-1
1519 ,sysdate
1520 ,-1
1521 ,-9999
1522 , 'H'
1523 ,ae_header_id
1524 ,'XLA_UPG_HDR_WO_LINES'
1525 from (select xah.ae_header_id
1526 from xla_ae_headers xah
1527 where NOT EXISTS (SELECT xal.ae_header_id
1528 from xla_ae_lines xal
1529 where xah.ae_header_id = xal.ae_header_id
1530 and xah.application_id = xal.application_id
1531 and xal.application_id = p_application_id
1532 and xal.ae_header_id = p_header_id)
1533 and application_id = p_application_id
1534 and ae_header_id = p_header_id));
1535 COMMIT;
1536
1537 l_rowcount := l_rowcount + sql%rowcount;
1538
1539 If l_rowcount > 0 THEN
1540 UPDATE xla_ae_headers
1541 set upg_valid_flag = CASE upg_valid_flag
1542 WHEN 'F' THEN 'L'
1543 WHEN 'J' THEN 'M'
1544 WHEN 'I' THEN 'N'
1545 ELSE 'K'
1546 END
1547 where ae_header_id = p_header_id;
1548 end if;
1549
1550 l_rowcount := sql%rowcount;
1551
1552 INSERT INTO XLA_UPG_ERRORS
1553 (upg_error_id, application_id, upg_source_application_id, creation_date
1554 , created_by, last_update_date, last_updated_by, upg_batch_id
1555 , error_level, error_message_name,entity_id)
1556 values(
1557 xla_upg_errors_s.nextval
1558 ,g_application_id
1562 ,sysdate
1559 ,-9999
1560 ,sysdate
1561 ,-1
1563 ,-1
1564 ,-9999
1565 , 'V'
1566 ,'XLA_HDR_VERIFICATION_RECORD'
1567 ,l_rowcount);
1568
1569 COMMIT;
1570
1571 l_rowcount := 0;
1572
1573 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
1574 (upg_error_id, application_id, upg_source_application_id, creation_date
1575 , created_by, last_update_date, last_updated_by, upg_batch_id
1576 , error_level, ae_header_id, ae_line_num,error_message_name)
1577 (select
1578 xla_upg_errors_s.nextval
1579 ,g_application_id
1580 ,-9999
1581 ,sysdate
1582 ,-1
1583 ,sysdate
1584 ,-1
1585 ,-9999
1586 , 'L'
1587 ,ae_header_id
1588 ,ae_line_num
1589 ,decode(grm.multiplier,1,'XLA_UPG_CCID_INVALID'
1590 ,2,'XLA_UPG_CCID_SUMACCT'
1591 ,3,'XLA_UPG_CCID_NOBUDGET'
1592 ,4,'XLA_UPG_PARTY_TYP_INVALID'
1593 ,5,'XLA_UPG_DRCR_NULL'
1594 ,6,'XLA_UPG_ENTAMT_DIFF_ACCAMT'
1595 ,7,'XLA_UPG_LINE_NO_HDR'
1596 ,8,'XLA_UPG_ENTAMT_ACCAMT_DIFFSIDE'
1597 ,9,'XLA_UPG_PARTY_ID_INVALID'
1598 ,'XLA_UPG_PARTY_SITE_INVALID')
1599 from ( select xal.ae_header_id
1600 , ae_line_num
1601 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NULL THEN 'Y'
1602 ELSE 'N' END line_error1-- Invalid Code Combination Id
1603 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL
1604 and glcc.SUMMARY_FLAG = 'Y' THEN 'Y'
1605 ELSE 'N' END line_error2-- CCID not a Summary Account
1606 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL
1607 and xah.APPLICATION_ID IS NOT NULL
1608 and xah.BALANCE_TYPE_CODE = 'B'
1609 and glcc.DETAIL_BUDGETING_ALLOWED_FLAG <> 'Y' THEN 'Y'
1610 ELSE 'N' END line_error3-- Budgeting not allowed
1611 , CASE when xal.PARTY_TYPE_CODE IS NOT NULL
1612 and xal.PARTY_TYPE_CODE NOT IN ('C','S') THEN 'Y'
1613 ELSE 'N' END line_error4-- Invalid Party Type Code
1614 , CASE when (xal.accounted_dr is NULL AND xal.accounted_cr is NULL)
1615 or (xal.entered_dr is NULL AND xal.entered_cr is NULL)
1616 or (xal.accounted_dr is NOT NULL
1617 AND xal.accounted_cr is NOT NULL)
1618 or (xal.entered_dr is NOT NULL
1619 AND xal.entered_cr is NOT NULL)
1620 THEN 'Y'
1621 ELSE 'N' END line_error5
1622 , CASE when gll.currency_code IS NOT NULL
1623 and xal.currency_code = gll.currency_code
1624 and (nvl(xal.entered_dr,0) <> nvl(xal.accounted_dr,0)
1625 or nvl(xal.entered_cr,0) <> nvl(xal.accounted_cr,0))
1626 THEN 'Y'
1627 ELSE 'N' END line_error6
1628 , CASE when xah.application_id IS NULL THEN 'Y'
1629 ELSE 'N' END line_error7-- Orphan Line.
1633 xal.entered_dr is NOT NULL) THEN 'Y'
1630 , CASE when (xal.accounted_dr is NOT NULL and
1631 xal.entered_cr is NOT NULL) or
1632 (xal.accounted_cr is NOT NULL and
1634 ELSE 'N' END line_error8
1635 ,CASE when xal.party_id IS NULL THEN 'Y'
1636 ELSE 'N' END line_error9
1637 , CASE when xal.party_site_id IS NULL
1638 and xal.party_id IS NULL then 'Y'
1639 ELSE 'N' END line_error10
1640 FROM xla_ae_headers xah
1641 , xla_ae_lines xal
1642 , gl_code_combinations glcc
1643 , gl_ledgers gll
1644 , hz_parties hz
1645 , hz_party_sites hps
1646 WHERE glcc.code_combination_id(+) = xal.code_combination_id
1647 AND xah.ae_header_id = xal.ae_header_id
1648 AND gll.ledger_id(+) = xah.ledger_id
1649 AND xal.party_id(+) = hz.party_id
1650 AND xal.party_site_id = hps.party_site_id
1651 AND (glcc.CHART_OF_ACCOUNTS_ID IS NULL OR
1652 (glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL AND
1653 glcc.SUMMARY_FLAG = 'Y' ) OR
1654 (glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL AND
1655 xah.APPLICATION_ID IS NOT NULL AND
1656 xah.BALANCE_TYPE_CODE = 'B' AND
1657 glcc.DETAIL_BUDGETING_ALLOWED_FLAG <> 'Y') OR
1658 (xal.PARTY_TYPE_CODE IS NOT NULL AND
1659 xal.PARTY_TYPE_CODE NOT IN ('C','S') ) OR
1660 (xal.accounted_dr is NULL AND xal.accounted_cr is NULL) OR
1661 (xal.entered_dr is NULL AND xal.entered_cr is NULL) OR
1662 (xal.accounted_dr is NOT NULL AND xal.accounted_cr is NOT NULL) OR
1663 (xal.entered_dr is NOT NULL AND xal.entered_cr is NOT NULL) OR
1664 (gll.currency_code IS NOT NULL AND
1665 xal.currency_code = gll.currency_code AND
1666 (nvl(xal.entered_dr,0) <> nvl(xal.accounted_dr,0) OR
1667 nvl(xal.entered_cr,0) <> nvl(xal.accounted_cr,0))) OR
1668 ((xal.accounted_dr is NOT NULL and xal.entered_cr is NOT NULL) OR
1669 (xal.accounted_cr is NOT NULL and xal.entered_dr is NOT NULL)) OR
1670 (xah.application_id IS NULL))
1671 and xal.application_id = p_application_id
1672 and xal.ae_header_id = p_header_id) xal
1673 ,gl_row_multipliers grm
1674 where grm.multiplier < 11
1675 and decode (grm.multiplier,1,line_error1
1676 ,2,line_error2
1677 ,3,line_error3
1678 ,4,line_error4
1679 ,5,line_error5
1680 ,6,line_error6
1681 ,7,line_error7
1682 ,8,line_error8
1683 ,9,line_error9
1684 ,line_error10) = 'Y');
1685
1686 COMMIT;
1687
1688 l_rowcount := l_rowcount + sql%rowcount;
1689
1690 If l_rowcount > 0 THEN
1691 UPDATE xla_ae_headers
1692 set upg_valid_flag = CASE upg_valid_flag
1696 WHEN 'L' THEN 'S'
1693 WHEN 'F' THEN 'P'
1694 WHEN 'J' THEN 'Q'
1695 WHEN 'I' THEN 'R'
1697 WHEN 'M' THEN 'T'
1698 WHEN 'N' THEN 'U'
1699 ELSE 'O'
1700 END
1701 where ae_header_id = p_header_id
1702 and application_id = p_application_id;
1703
1704 end if;
1705
1706
1707 -- finding out how many rows got updated.
1708
1709 l_rowcount := sql%rowcount;
1710
1711 INSERT INTO XLA_UPG_ERRORS
1712 (upg_error_id, application_id, upg_source_application_id, creation_date
1713 , created_by, last_update_date, last_updated_by, upg_batch_id
1714 , error_level, error_message_name,entity_id)
1715 values(
1716 xla_upg_errors_s.nextval
1717 ,g_application_id
1718 ,-9999
1719 ,sysdate
1720 ,-1
1721 ,sysdate
1722 ,-1
1723 ,-9999
1724 , 'V'
1725 ,'XLA_LINE_VERIFICATION_RECORD'
1726 ,l_rowcount);
1727
1728 COMMIT;
1729
1730 EXCEPTION
1731 WHEN xla_exceptions_pkg.application_exception THEN
1732 RAISE;
1733 WHEN OTHERS THEN
1734 xla_exceptions_pkg.raise_message
1735 (p_location => 'XLA_UPGRADE_PUB.Validate_Header_Line_Entries');
1736
1737 END Validate_Header_Line_Entries;
1738
1739 /*============================================================================+
1740 | |
1741 | Public Procedure |
1742 | |
1743 | Pre_Upgrade_Set_Status_Code |
1744 | |
1745 | This procedure is called during the Pre Upgrade phase, to update the |
1746 | status code. |
1747 +============================================================================*/
1748
1749 PROCEDURE pre_upgrade_set_status_code
1750 (p_error_buf OUT NOCOPY VARCHAR2,
1751 p_retcode OUT NOCOPY NUMBER,
1752 p_migrate_all_ledgers IN VARCHAR2,
1753 p_dummy_parameter IN VARCHAR2,
1754 p_ledger_id IN NUMBER DEFAULT NULL,
1755 p_start_date IN VARCHAR2
1756 ) IS
1757
1758 CURSOR CUR_ALL_LEDGERS IS SELECT DISTINCT ledger_id
1759 FROM gl_period_statuses;
1760
1761 l_migrate_all_ledgers VARCHAR2(30);
1762 l_ledger_id NUMBER;
1763 l_start_date date;
1764 l_error_buf VARCHAR2(1000);
1765 l_retcode NUMBER;
1766 l_end_date date;
1767 l_log_module VARCHAR2(240);
1768
1769 l_all_ledgers CUR_ALL_LEDGERS%ROWTYPE;
1770
1771 BEGIN
1772
1773 IF g_log_enabled THEN
1774 l_log_module := C_DEFAULT_MODULE||'.pre_upgrade_set_status_code';
1775 END IF;
1776
1777 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1778 trace('pre_upgrade_set_status_code.Begin',C_LEVEL_STATEMENT,l_log_module);
1779 END IF;
1780
1781 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1782 trace('Date '||p_start_date,C_LEVEL_STATEMENT,l_log_module);
1783 END IF;
1784
1785 l_migrate_all_ledgers := p_migrate_all_ledgers;
1786 l_start_date := fnd_date.canonical_to_date(p_start_date);
1787 l_ledger_id := p_ledger_id;
1788
1789 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1790 trace('l_migrate_all_ledgers '|| l_migrate_all_ledgers,
1791 C_LEVEL_STATEMENT, l_Log_module);
1792 END IF;
1793
1794 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1795 trace('l_ledger_id '||l_ledger_id,
1796 C_LEVEL_STATEMENT, l_Log_module);
1797 END IF;
1798
1799 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1800 trace('l_start_date '||l_start_date,
1801 C_LEVEL_STATEMENT, l_Log_module);
1802 END IF;
1803
1804 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1805 trace('Processing periods...',
1806 C_LEVEL_STATEMENT, l_Log_module);
1807 END IF;
1808
1809 IF l_migrate_all_ledgers = 'N' THEN
1810
1811 SELECT min(start_date) - 1
1812 INTO l_end_date
1813 FROM gl_period_statuses
1814 WHERE migration_status_code = 'P'
1815 AND ledger_id = l_ledger_id
1816 AND application_id in (200,222,275,201,401,101,8721);
1817
1818 IF l_end_date is NULL THEN
1819
1820 SELECT max(end_date)
1821 INTO l_end_date
1822 FROM gl_period_statuses
1823 WHERE ledger_id = l_ledger_id
1824 AND application_id IN (200,222,275,201,401,101,8721);
1825 END IF;
1826
1827 UPDATE gl_period_statuses
1828 SET migration_status_code = 'P'
1829 WHERE ledger_id = l_ledger_id
1830 AND (start_date >= l_start_date
1831 and end_date <= l_end_date)
1832 AND application_id in (200,222,275,201,401,101,8721)
1833 AND adjustment_period_flag = 'N'
1834 AND migration_status_code IS NULL;
1835
1836 ELSE
1837
1838 OPEN CUR_ALL_LEDGERS;
1839 LOOP
1840 FETCH CUR_ALL_LEDGERS INTO l_all_ledgers;
1841 EXIT when CUR_ALL_LEDGERS%notfound;
1842
1843 SELECT min(start_date) - 1
1844 INTO l_end_date
1845 FROM gl_period_statuses
1849
1846 WHERE migration_status_code = 'P'
1847 AND ledger_id = l_all_ledgers.ledger_id
1848 AND application_id in (200,222,275,201,401,101,8721);
1850 IF l_end_date is NULL THEN
1851
1852 SELECT max(end_date)
1853 INTO l_end_date
1854 FROM gl_period_statuses
1855 WHERE ledger_id = l_all_ledgers.ledger_id
1856 AND application_id in (200,222,275,201,401,101,8721);
1857
1858 END IF;
1859
1860 -- Updation of GL Period Statuses.
1861
1862 UPDATE gl_period_statuses
1863 SET migration_status_code = 'P'
1864 WHERE ledger_id = l_all_ledgers.ledger_id
1865 AND (start_date >= l_start_date
1866 and end_date <= l_end_date)
1867 AND application_id in (200,222,275,201,401,101,8721)
1868 AND adjustment_period_flag = 'N'
1869 AND migration_status_code IS NULL;
1870
1871
1872 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1873 trace('Updated gl_period_statuses.'
1874 , C_LEVEL_STATEMENT, l_Log_module);
1875 END IF;
1876
1877 END LOOP;
1878 CLOSE CUR_ALL_LEDGERS;
1879
1880 END IF;
1881
1882 EXCEPTION
1883
1884 WHEN xla_exceptions_pkg.application_exception THEN
1885 RAISE;
1886
1887 WHEN OTHERS THEN
1888 xla_exceptions_pkg.raise_message
1889 (p_location => 'XLA_UPGRADE_PUB.pre_upgrade_set_status_code');
1890
1891 END PRE_UPGRADE_SET_STATUS_CODE;
1892
1893
1894 BEGIN
1895 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1896 g_log_enabled := fnd_log.test
1897 (log_level => g_log_level
1898 ,MODULE => C_DEFAULT_MODULE);
1899
1900 IF NOT g_log_enabled THEN
1901 g_log_level := C_LEVEL_LOG_DISABLED;
1902 END IF;
1903
1904 END XLA_UPGRADE_PUB;