[Home] [Help]
PACKAGE BODY: APPS.XLA_UPGRADE_PUB
Source
1 PACKAGE BODY XLA_UPGRADE_PUB AS
2 -- $Header: xlaugupg.pkb 120.49 2011/09/30 11:20:44 vgopiset 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 | 22-JUL-2009 VGOPISET Bug 8717476 Enabled Procedures SET_STATUS_CODE |
43 | and added procedures: UPDATE_UPG_REQUEST_STATUS|
44 | and RESET_PERIOD_STATUSES. |
45 | 24-AUG-2009 VGOPISET Bug 8834301 Resetting the Periods to NULL from |
46 | PENDING when EXCEPTION is raised by Product API|
47 | 10-Mar-2011 11854401 Change the Last_Updated_By from |
48 | -601 to 2 for Downtime |
49 | -602 to 3 for Hotpatch in GL_PERIOD_STATUSES |
50 +===========================================================================*/
51 --=============================================================================
52 -- **************** declarations ********************
53 --=============================================================================
54
55
56 -------------------------------------------------------------------------------
57 -- declaring global variables
58 -------------------------------------------------------------------------------
59
60 g_batch_id INTEGER ;
61 g_batch_size INTEGER := 30000;
62 g_source_application_id NUMBER ;
63 g_application_id NUMBER;
64 g_validate_complete xla_upg_batches.VALIDATE_COMPLETE_FLAG%TYPE;
65 g_crsegvals_complete xla_upg_batches.CRSEGVALS_COMPLETE_FLAG%TYPE;
66 -------------------------------------------------------------------------------
67 -- declaring global pl/sql types
68 -------------------------------------------------------------------------------
69
70 TYPE t_entity_id IS TABLE OF
71 xla_transaction_entities.entity_id%type
72 INDEX BY BINARY_INTEGER;
73 TYPE t_error_flag IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
74 TYPE t_event_id IS TABLE OF
75 xla_events.event_id%type
76 INDEX BY BINARY_INTEGER;
77 TYPE t_header_id IS TABLE OF
78 xla_ae_headers.ae_header_id%type
79 INDEX BY BINARY_INTEGER;
80 TYPE t_line_num IS TABLE OF
81 xla_ae_lines.ae_line_num%type
82 INDEX BY BINARY_INTEGER;
83 TYPE t_seg_value IS TABLE OF
84 xla_ae_segment_values.segment_value%type
85 INDEX BY BINARY_INTEGER;
86 TYPE t_line_count IS TABLE OF
87 xla_ae_segment_values.ae_lines_count%type
88 INDEX BY BINARY_INTEGER;
89 TYPE t_seg_type IS TABLE OF
90 xla_ae_segment_values.segment_type_code%type
91 INDEX BY BINARY_INTEGER;
92 TYPE t_error_id IS TABLE OF
93 xla_upg_errors.upg_error_id%type
94 INDEX BY BINARY_INTEGER;
95 TYPE T_ARRAY_LEDGER_ID IS TABLE OF XLA_TRANSACTION_ENTITIES.LEDGER_ID%TYPE
96 INDEX BY BINARY_INTEGER ; -- bug:8717476
97
98 -------------------------------------------------------------------------------
99 -- declaring global constants
100 -------------------------------------------------------------------------------
101 -- The segment type code
102 C_BAL_SEGMENT CONSTANT VARCHAR2(1) := 'B';
103 C_MGT_SEGMENT CONSTANT VARCHAR2(1) := 'M';
104 --=============================================================================
105 -- *********** Local Trace Routine **********
106 --=============================================================================
107 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
108 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
109 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
110 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
111 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
112 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
113
114 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
115
116 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.XLA_UPGRADE_PUB';
117 /* new constant values added for 8717476 */
118 C_PROGRESS_STATUS CONSTANT VARCHAR2(30) := 'IN PROGRESS';
119 C_ERROR_STATUS CONSTANT VARCHAR2(30) := 'ERROR';
120 C_SUCCESS_STATUS CONSTANT VARCHAR2(30) := 'FINISHED';
121 C_INITIAL_STATUS CONSTANT VARCHAR2(30) := 'INITIAL ROW';
122
123 g_log_level NUMBER;
124 g_log_enabled BOOLEAN;
125 g_array_ledger_id T_ARRAY_LEDGER_ID ; -- bug:8717476
126
127 -------------------------------------------------------------------------------
128 -- forward declarion of private procedures and functions
129 -------------------------------------------------------------------------------
130 PROCEDURE recover_previous_run;
131 --=============================================================================
132 -- *********** Local Trace Routine **********
133 --=============================================================================
134
135 PROCEDURE trace
136 (p_msg IN VARCHAR2
137 ,p_level IN NUMBER
138 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
139 BEGIN
140 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
141 fnd_log.message(p_level, p_module);
142 ELSIF p_level >= g_log_level THEN
143 fnd_log.string(p_level, p_module, p_msg);
144 END IF;
145
146 EXCEPTION
147 WHEN xla_exceptions_pkg.application_exception THEN
148 RAISE;
149 WHEN OTHERS THEN
150 xla_exceptions_pkg.raise_message
151 (p_location => 'XLA_UPGRADE_PUB.trace');
152 END trace;
153
154 --=============================================================================
155 -- ********** Procedure to Update the Upgrade Request Status**********
156 -- ********** added for bug: 8717476 **********
157 --=============================================================================
158 PROCEDURE Update_upg_request_status
159 (p_application_id IN NUMBER,
160 p_status_code IN VARCHAR2)
161 IS
162 l_log_module VARCHAR2(240);
163 BEGIN
164 IF g_log_enabled THEN
165 l_log_module := c_default_module
166 ||'.update_upg_request_status';
167 END IF;
168
169 IF (c_level_statement >= g_log_level) THEN
170 Trace('update_upg_request_status.Begin',c_level_statement,
171 l_log_module);
172
173 Trace('Status being Updated for Application: '
174 ||p_application_id
175 ||' is: '
176 ||p_status_code,c_level_statement,l_log_module);
177 END IF;
178
179 UPDATE xla_upgrade_requests
180 SET status_code = p_status_code,
181 last_update_date = SYSDATE
182 WHERE application_id = p_application_id
183 AND program_code = 'ONDEMAND UPGRADE'
184 AND status_code <> C_SUCCESS_STATUS;
185
186 COMMIT;
187 EXCEPTION
188 WHEN xla_exceptions_pkg.application_exception THEN
189 RAISE;
190 WHEN OTHERS THEN
191 xla_exceptions_pkg.Raise_message(p_location => 'XLA_UPGRADE_PUB.update_upg_request_status');
192 END update_upg_request_status;
193
194 --=============================================================================
195 --****** Procedure to RESET periods selected for UPGRADE as NOT-MIGRATED ******
196 --********** added for bug: 8717476 ******
197 --=============================================================================
198 PROCEDURE Reset_period_statuses
199 (p_application_id IN NUMBER)
200 IS
201 l_log_module VARCHAR2(240);
202 BEGIN
203 IF g_log_enabled THEN
204 l_log_module := c_default_module
205 ||'.reset_period_statuses';
206 END IF;
207
208 IF (c_level_statement >= g_log_level) THEN
209 Trace('reset_period_statuses.Begin',c_level_statement,
210 l_log_module);
211 END IF;
212
213 IF p_application_id = 275 THEN
214 NULL;
215 -- Commented as Project's is not participating in this Upgrade.
216 /*
217 FOR i IN 1..g_array_ledger_id.COUNT
218 LOOP
219 UPDATE gl_period_statuses gps
220 SET migration_status_code = NULL
221 WHERE gps.migration_status_code = 'P'
222 AND gps.application_id IN (275, 8721)
223 AND gps.adjustment_period_flag = 'N'
224 -- AND gps.closing_status in ('F', 'O', 'C', 'N') -- commented for bug12917429
225 AND gps.ledger_id = g_array_ledger_id(i) ;
226
227 fnd_file.put_line(fnd_file.log, '*Migration status code Updated to NULL for ledger_id : '|| g_array_ledger_id(i)
228 || ' are : '|| to_char(SQL%ROWCOUNT));
229 END LOOP;
230 */
231 ELSE
232 FOR i IN 1.. g_array_ledger_id.COUNT LOOP
233 UPDATE gl_period_statuses gps
234 SET migration_status_code = NULL
235 WHERE gps.migration_status_code = 'P'
236 AND gps.application_id = p_application_id
237 AND gps.adjustment_period_flag = 'N'
238 -- AND gps.closing_status in ('F', 'O', 'C', 'N') -- commented for bug12917429
239 AND gps.ledger_id = g_array_ledger_id(i);
240
241 fnd_file.Put_line(fnd_file.LOG,'*Migration status code Updated to NULL for ledger_id : '
242 ||G_array_ledger_id(i)
243 ||' are : '
244 ||To_char(SQL%ROWCOUNT));
245 END LOOP;
246 END IF;
247
248 COMMIT;
249 EXCEPTION
250 WHEN xla_exceptions_pkg.application_exception THEN
251 RAISE;
252 WHEN OTHERS THEN
253 xla_exceptions_pkg.Raise_message(p_location => 'XLA_UPGRADE_PUB.reset_period_statuses');
254 END reset_period_statuses;
255
256 --=============================================================================
257 -- *********** public procedures and functions **********
258 --=============================================================================
259 --=============================================================================
260 /*============================================================================+
261 | |
262 | Public Procedure |
263 | |
264 | Insert_Line_Criteria |
265 | |
266 | This routine is called to insert line criteria. |
267 | |
268 +============================================================================*/
269 PROCEDURE Insert_Line_Criteria (
270 p_batch_id IN NUMBER
271 , p_batch_size IN NUMBER
272 , p_application_id IN NUMBER
273 , p_error_detected OUT NOCOPY BOOLEAN
274 , p_overwrite_flag IN BOOLEAN)
275 IS
276 l_log_module VARCHAR2(240);
277 BEGIN
278 IF g_log_enabled THEN
279 l_log_module := C_DEFAULT_MODULE||'.Insert_Line_Criteria';
280 END IF;
281 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
282 trace
283 (p_msg => 'BEGIN of procedure Insert_Line_Criteria'
284 ,p_level => C_LEVEL_PROCEDURE
285 ,p_module =>l_log_module);
286 END IF;
287 SAVEPOINT before_insert_criteria;
288 IF p_overwrite_flag
289 THEN
290 delete xla_ae_line_details xal
291 where (ae_header_id, ae_line_num) IN
292 (select xlgt.ae_header_id,ae_line_num
293 from xla_upg_line_criteria_gt xlgt
294 where xal.ae_header_id = xlgt.ae_header_id
295 and xal.ae_line_num = xlgt.ae_line_num);
296 END IF;
297 update xla_upg_line_criteria_gt xlgt
298 set error_message_name = 'XLA_UPG_INVALID_CRITERIA'
299 where NOT EXISTS
300 (select 1
301 from xla_analytical_hdrs_b xanh
302 where xanh.amb_context_code = 'DEFAULT'
303 and xanh.analytical_criterion_code = xlgt.analytical_criterion_code
304 and xanh.analytical_criterion_type_code = xlgt.analytical_criterion_type_code);
305 IF ( SQL%ROWCOUNT > 0 ) THEN
306 p_error_detected := true;
307 ELSE
308 p_error_detected := false;
309 END IF;
310
311 INSERT INTO xla_analytical_dtl_vals
312 (
313 analytical_detail_value_id
314 ,analytical_criterion_code
315 ,analytical_criterion_type_code
316 ,amb_context_code
317 ,analytical_detail_char_1
318 ,analytical_detail_char_2
319 ,analytical_detail_char_3
320 ,analytical_detail_char_4
321 ,analytical_detail_char_5
322 ,analytical_detail_date_1
323 ,analytical_detail_date_2
324 ,analytical_detail_date_3
325 ,analytical_detail_date_4
326 ,analytical_detail_date_5
327 ,analytical_detail_number_1
328 ,analytical_detail_number_2
329 ,analytical_detail_number_3
330 ,analytical_detail_number_4
331 ,analytical_detail_number_5
332 ,creation_date
333 ,created_by
334 ,last_update_date
335 ,last_updated_by
336 ,last_update_login
337 )
338 SELECT xla_analytical_dtl_vals_s.nextval
339 ,analytical_criterion_code
340 ,analytical_criterion_type_code
341 ,amb_context_code
342 ,analytical_detail_char_1
343 ,analytical_detail_char_2
344 ,analytical_detail_char_3
345 ,analytical_detail_char_4
346 ,analytical_detail_char_5
347 ,analytical_detail_date_1
348 ,analytical_detail_date_2
349 ,analytical_detail_date_3
350 ,analytical_detail_date_4
351 ,analytical_detail_date_5
352 ,analytical_detail_number_1
353 ,analytical_detail_number_2
354 ,analytical_detail_number_3
355 ,analytical_detail_number_4
356 ,analytical_detail_number_5
357 ,sysdate
358 ,-1
359 ,sysdate
360 ,-1
361 ,-1
362 FROM ( SELECT
363 DISTINCT
364 analytical_criterion_code
365 ,analytical_criterion_type_code
366 ,'DEFAULT' amb_context_code
367 ,analytical_detail_char_1
368 ,analytical_detail_char_2
369 ,analytical_detail_char_3
370 ,analytical_detail_char_4
371 ,analytical_detail_char_5
372 ,analytical_detail_date_1
373 ,analytical_detail_date_2
374 ,analytical_detail_date_3
375 ,analytical_detail_date_4
376 ,analytical_detail_date_5
377 ,analytical_detail_number_1
378 ,analytical_detail_number_2
379 ,analytical_detail_number_3
380 ,analytical_detail_number_4
381 ,analytical_detail_number_5
382 FROM
383 XLA_UPG_LINE_CRITERIA_GT
384 WHERE ERROR_MESSAGE_NAME IS NOT NULL
385 ) adv1
386 WHERE NOT exists ( SELECT 'x'
387 FROM xla_analytical_dtl_vals adv2
388 WHERE adv1.analytical_criterion_code = adv2.analytical_criterion_code
389 AND adv1.analytical_criterion_type_code = adv2.analytical_criterion_type_code
390 AND adv1.amb_context_code = adv2.amb_context_code
391 --Detail 1
392 AND NVL( adv1.analytical_detail_char_1
393 ,NVL( TO_CHAR( adv1.analytical_detail_date_1
394 ,'J'||'.'||'HH24MISS'
395 )
396 ,NVL( TO_CHAR( adv1.analytical_detail_number_1
397 ,'TM'
398 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
399 )
400 ,'%'
401 )
402 )
403 )
404 = NVL( adv2.analytical_detail_char_1
405 ,NVL( TO_CHAR( adv2.analytical_detail_date_1
406 ,'J'||'.'||'HH24MISS'
407 )
408 ,NVL( TO_CHAR( adv2.analytical_detail_number_1
409 ,'TM'
410 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
411 )
412 ,'%'
413 )
414 )
415 )
416 --Detail 2
417 AND NVL( adv1.analytical_detail_char_2
418 ,NVL( TO_CHAR( adv1.analytical_detail_date_2
419 ,'J'||'.'||'HH24MISS'
420 )
421 ,NVL( TO_CHAR( adv1.analytical_detail_number_2
422 ,'TM'
423 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
424 )
425 ,'%'
426 )
427 )
428 )
429 = NVL( adv2.analytical_detail_char_2
430 ,NVL( TO_CHAR( adv2.analytical_detail_date_2
431 ,'J'||'.'||'HH24MISS'
432 )
433 ,NVL( TO_CHAR( adv2.analytical_detail_number_2
434 ,'TM'
435 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
436 )
437 ,'%'
438 )
439 )
440 )
441 --Detail 3
442 AND NVL( adv1.analytical_detail_char_3
443 ,NVL( TO_CHAR( adv1.analytical_detail_date_3
444 ,'J'||'.'||'HH24MISS'
445 )
446 ,NVL( TO_CHAR( adv1.analytical_detail_number_3
447 ,'TM'
448 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
449 )
450 ,'%'
451 )
452 )
453 )
454 = NVL( adv2.analytical_detail_char_3
455 ,NVL( TO_CHAR( adv2.analytical_detail_date_3
456 ,'J'||'.'||'HH24MISS'
457 )
458 ,NVL( TO_CHAR( adv2.analytical_detail_number_3
459 ,'TM'
460 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
461 )
462 ,'%'
463 )
464 )
465 )
466 --Detail 4
467 AND NVL( adv1.analytical_detail_char_4
468 ,NVL( TO_CHAR( adv1.analytical_detail_date_4
469 ,'J'||'.'||'HH24MISS'
470 )
471 ,NVL( TO_CHAR( adv1.analytical_detail_number_4
472 ,'TM'
473 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
474 )
475 ,'%'
476 )
477 )
478 )
479 = NVL( adv2.analytical_detail_char_4
480 ,NVL( TO_CHAR( adv2.analytical_detail_date_4
481 ,'J'||'.'||'HH24MISS'
482 )
483 ,NVL( TO_CHAR( adv2.analytical_detail_number_4
484 ,'TM'
485 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
486 )
487 ,'%'
488 )
489 )
490 )
491 --Detail 5
492 AND NVL( adv1.analytical_detail_char_5
493 ,NVL( TO_CHAR( adv1.analytical_detail_date_5
494 ,'J'||'.'||'HH24MISS'
495 )
496 ,NVL( TO_CHAR( adv1.analytical_detail_number_5
497 ,'TM'
498 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
499 )
500 ,'%'
501 )
502 )
503 )
504 = NVL( adv2.analytical_detail_char_5
505 ,NVL( TO_CHAR( adv2.analytical_detail_date_5
506 ,'J'||'.'||'HH24MISS'
507 )
508 ,NVL( TO_CHAR( adv2.analytical_detail_number_5
509 ,'TM'
510 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
511 )
512 ,'%'
513 )
514 )
515 )
516 );
517
518 INSERT INTO XLA_AE_LINE_DETAILS
519 (
520 ae_header_id
521 , ae_line_num
522 , analytical_detail_value_id
523 )
524 SELECT adv.analytical_detail_value_id
525 ,alcg.ae_header_id
526 ,alcg.ae_line_num
527
528 FROM
529 XLA_UPG_LINE_CRITERIA_GT alcg, xla_analytical_dtl_vals adv
530 WHERE --Detail 1
531 NVL( alcg.analytical_detail_char_1
532 ,NVL( TO_CHAR( alcg.analytical_detail_date_1
533 ,'J'||'.'||'HH24MISS'
534 )
535 ,NVL( TO_CHAR( alcg.analytical_detail_number_1
536 ,'TM'
537 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
538 )
539 ,'%'
540 )
541 )
542 )
543 = NVL( adv.analytical_detail_char_1
544 ,NVL( TO_CHAR( adv.analytical_detail_date_1
545 ,'J'||'.'||'HH24MISS'
546 )
547 ,NVL( TO_CHAR( adv.analytical_detail_number_1
548 ,'TM'
549 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
550 )
551 ,'%'
552 )
553 )
554 )
555 --Detail 2
556 AND NVL( alcg.analytical_detail_char_2
557 ,NVL( TO_CHAR( alcg.analytical_detail_date_2
558 ,'J'||'.'||'HH24MISS'
559 )
560 ,NVL( TO_CHAR( alcg.analytical_detail_number_2
561 ,'TM'
562 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
563 )
564 ,'%'
565 )
566 )
567 )
568 = NVL( adv.analytical_detail_char_2
569 ,NVL( TO_CHAR( adv.analytical_detail_date_2
570 ,'J'||'.'||'HH24MISS'
571 )
572 ,NVL( TO_CHAR( adv.analytical_detail_number_2
573 ,'TM'
574 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
575 )
576 ,'%'
577 )
578 )
579 )
580 --Detail 3
581 AND NVL( alcg.analytical_detail_char_3
582 ,NVL( TO_CHAR( alcg.analytical_detail_date_3
583 ,'J'||'.'||'HH24MISS'
584 )
585 ,NVL( TO_CHAR( alcg.analytical_detail_number_3
586 ,'TM'
587 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
588 )
589 ,'%'
590 )
591 )
592 )
593 = NVL( adv.analytical_detail_char_3
594 ,NVL( TO_CHAR( adv.analytical_detail_date_3
595 ,'J'||'.'||'HH24MISS'
596 )
597 ,NVL( TO_CHAR( adv.analytical_detail_number_3
598 ,'TM'
599 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
600 )
601 ,'%'
602 )
603 )
604 )
605 --Detail 4
606 AND NVL( alcg.analytical_detail_char_4
607 ,NVL( TO_CHAR( alcg.analytical_detail_date_4
608 ,'J'||'.'||'HH24MISS'
609 )
610 ,NVL( TO_CHAR( alcg.analytical_detail_number_4
611 ,'TM'
612 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
613 )
614 ,'%'
615 )
616 )
617 )
618 = NVL( adv.analytical_detail_char_4
619 ,NVL( TO_CHAR( adv.analytical_detail_date_4
620 ,'J'||'.'||'HH24MISS'
621 )
622 ,NVL( TO_CHAR( adv.analytical_detail_number_4
623 ,'TM'
624 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
625 )
626 ,'%'
627 )
628 )
629 )
630 --Detail 5
631 AND NVL( alcg.analytical_detail_char_5
632 ,NVL( TO_CHAR( alcg.analytical_detail_date_5
633 ,'J'||'.'||'HH24MISS'
634 )
635 ,NVL( TO_CHAR( alcg.analytical_detail_number_5
636 ,'TM'
637 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
638 )
639 ,'%'
640 )
641 )
642 )
643 = NVL( adv.analytical_detail_char_5
644 ,NVL( TO_CHAR( adv.analytical_detail_date_5
645 ,'J'||'.'||'HH24MISS'
646 )
647 ,NVL( TO_CHAR( adv.analytical_detail_number_5
648 ,'TM'
649 ,'NLS_NUMERIC_CHARACTERS = ''.,'''
650 )
651 ,'%'
652 )
653 )
654 );
655 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
656 trace
657 (p_msg => 'END of procedure Insert_Line_Criteria'
658 ,p_level => C_LEVEL_PROCEDURE
659 ,p_module => l_log_module);
660 END IF;EXCEPTION
661 WHEN xla_exceptions_pkg.application_exception THEN
662 ROLLBACK to SAVEPOINT before_insert_criteria;
663 RAISE;
664 WHEN OTHERS THEN
665 ROLLBACK to SAVEPOINT before_insert_criteria;
666 xla_exceptions_pkg.raise_message
667 (p_location => 'XLA_UPGRADE_PUB.Validate_Entries');
668 END Insert_Line_Criteria;
669 /*============================================================================+
670 | |
671 | Public Procedure |
672 | |
673 | recover_previous_run |
674 | |
675 | This routine is called to recover the previous run. |
676 | |
677 +============================================================================*/
678 PROCEDURE recover_previous_run IS
679 cursor csr_previous_entity_errors IS
680 select entity_id
681 from xla_upg_errors
682 where error_level = 'N'
683 and upg_batch_id = g_batch_id;
684
685 cursor csr_previous_event_errors IS
686 select event_id
687 from xla_upg_errors
688 where error_level = 'E'
689 and upg_batch_id = g_batch_id;
690
691 cursor csr_previous_header_errors IS
692 select distinct ae_header_id
693 from xla_upg_errors
694 where error_level IN ('H','L','D')
695 and upg_batch_id = g_batch_id;
696
697 cursor csr_previous_errors IS
698 select upg_error_id
699 from xla_upg_errors
700 where upg_batch_id = g_batch_id;
701
702 cursor csr_segs_previous_run IS
703 select ae_header_id, segment_type_code
704 from xla_ae_segment_values
705 where upg_batch_id = g_batch_id;
706
707 -- Local Variables
708 l_entity_id t_entity_id;
709 l_event_id t_event_id;
710 l_header_id t_header_id;
711 l_error_id t_error_id;
712 l_seg_type t_seg_type;
713
714 BEGIN
715 OPEN csr_previous_entity_errors;
716 LOOP
717
718 FETCH csr_previous_entity_errors
719 BULK COLLECT INTO
720 l_entity_id
721 LIMIT g_batch_size;
722 EXIT when l_entity_id.COUNT = 0;
723
724 FORALL i IN l_entity_id.FIRST..l_entity_id.LAST
725 update xla_transaction_entities_upg
726 set upg_valid_flag = null
727 where entity_id = l_entity_id(i);
728
729 COMMIT;
730 END LOOP;
731 CLOSE csr_previous_entity_errors;
732
733 OPEN csr_previous_event_errors;
734 LOOP
735 FETCH csr_previous_event_errors
736 BULK COLLECT INTO
737 l_event_id
738 LIMIT g_batch_size;
739 EXIT WHEN l_event_id.COUNT = 0;
740
741 FORALL i IN l_event_id.FIRST..l_event_id.LAST
742 update xla_events
743 set upg_valid_flag = null
744 where event_id = l_event_id(i);
745
746 COMMIT;
747 END LOOP;
748 CLOSE csr_previous_event_errors;
749 OPEN csr_previous_header_errors;
750 LOOP
751 FETCH csr_previous_header_errors
752 BULK COLLECT INTO
753 l_header_id
754 LIMIT g_batch_size;
755 EXIT WHEN l_header_id.COUNT = 0;
756
757 FORALL i IN l_header_id.FIRST..l_header_id.LAST
758 update xla_ae_headers
759 set upg_valid_flag = null
760 where ae_header_id = l_header_id(i)
761 and application_id = g_application_id;
762
763 COMMIT;
764 END LOOP;
765 CLOSE csr_previous_header_errors;
766
767 OPEN csr_previous_errors;
768 LOOP
769 FETCH csr_previous_errors
770 BULK COLLECT INTO
771 l_error_id
772 LIMIT g_batch_size;
773 EXIT WHEN l_error_id.COUNT = 0;
774
775 FORALL i IN l_error_id.FIRST..l_error_id.LAST
776 delete xla_upg_errors
777 where upg_error_id = l_error_id(i);
778
779 COMMIT;
780 END LOOP;
781 CLOSE csr_previous_errors;
782
783 OPEN csr_segs_previous_run;
784 LOOP
785 FETCH csr_segs_previous_run
786 BULK COLLECT INTO
787 l_header_id, l_seg_type
788 LIMIT g_batch_size;
789 EXIT WHEN l_header_id.COUNT = 0;
790
791 FORALL i IN l_header_id.FIRST..l_header_id.LAST
792 delete xla_ae_segment_values
793 where ae_header_id = l_header_id(i)
794 and segment_type_code = l_seg_type(i);
795
796 COMMIT;
797 END LOOP;
798 CLOSE csr_segs_previous_run;
799
800 EXCEPTION
801 WHEN xla_exceptions_pkg.application_exception THEN
802 RAISE;
803
804 WHEN OTHERS THEN
805 xla_exceptions_pkg.raise_message
806 (p_location => 'XLA_UPGRADE_PUB.recover_previous_run');
807
808 END recover_previous_run;
809 /*============================================================================+
810 | |
811 | Public Procedure |
812 | |
813 | Set_Migration_Status_Code |
814 | |
815 | This routine is called to set the migration status code for an upgrade |
816 | for the particular periods. |
817 +============================================================================*/
818 FUNCTION set_migration_status_code
819 (p_application_id in number,
820 p_set_of_books_id in number,
821 p_period_name in varchar2 default null,
822 p_period_year in number default null)
823 return varchar2 IS
824
825 p_status_code varchar2(10);
826 l_application_id number;
827 l_set_of_books_id number;
828 l_period_name varchar2(15) ;
829 l_period_year number ;
830 L_LOG_MODULE VARCHAR2(240);
831
832 begin
833
834 IF g_log_enabled THEN
835 l_log_module := C_DEFAULT_MODULE||'.Set_Migration_Status_Code';
836 END IF;
837
838 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
839 trace
840 (p_msg => 'BEGIN of procedure Set_Migration_Status_Code'
841 ,p_level => C_LEVEL_PROCEDURE
842 ,p_module =>l_log_module);
843 END IF;
844
845 l_application_id := p_application_id;
846 l_set_of_books_id := p_set_of_books_id;
847 l_period_name := p_period_name;
848 l_period_year := p_period_year;
849
850 if (l_application_id is null ) then
851 p_status_code := 'F';
852 return p_status_code;
853 end if;
854
855
856 if ( l_set_of_books_id is null ) then
857
858 if (l_period_name is null and l_period_year is null) then
859
860 update gl_period_statuses
861 set migration_status_code = 'U'
862 where application_id = l_application_id
863 and migration_status_code = 'P';
864
865 p_status_code := 'P';
866 COMMIT;
867 return p_status_code;
868
869 elsif l_period_name is null then
870
871 update gl_period_statuses
872 set migration_status_code = 'U'
873 where period_year = l_period_year
874 and migration_status_code = 'P'
875 and application_id = l_application_id;
876
877 p_status_code := 'P';
878 COMMIT;
879 return p_status_code;
880
881 elsif l_period_year is null then
882
883 update gl_period_statuses
884 set migration_status_code = 'U'
885 where period_name = l_period_name
886 and migration_status_code = 'P'
887 and application_id = l_application_id;
888
889 p_status_code := 'P';
890 COMMIT;
891 return p_status_code;
892
893 elsif (l_period_name is not null and l_period_year is not null) then
894
895 update gl_period_statuses
896 set migration_status_code = 'U'
897 where period_year = l_period_year
898 and period_name = l_period_name
899 and migration_status_code = 'P'
900 and application_id = l_application_id;
901
902 p_status_code := 'P';
903 COMMIT;
904 return p_status_code;
905
906 end if;
907
908 end if;
909
910 /* Set_Of_Books_ID is not null */
911
912 if (l_period_name is null and l_period_year is null) then
913
914 update gl_period_statuses
915 set migration_status_code = 'U'
916 where application_id = l_application_id
917 and migration_status_code = 'P'
918 and ledger_id = l_set_of_books_id;
919
920 p_status_code := 'P';
921 COMMIT;
922 return p_status_code;
923
924 elsif l_period_name is null then
925
926 update gl_period_statuses
927 set migration_status_code = 'U'
928 where period_year = l_period_year
929 and migration_status_code = 'P'
930 and ledger_id = l_set_of_books_id
931 and application_id = l_application_id;
932
933 p_status_code := 'P';
934 COMMIT;
935 return p_status_code;
936
937 elsif l_period_year is null then
938
939 update gl_period_statuses
940 set migration_status_code = 'U'
941 where period_name = l_period_name
942 and migration_status_code = 'P'
943 and ledger_id = l_set_of_books_id
944 and application_id = l_application_id;
945
946 p_status_code := 'P';
947 COMMIT;
948 return p_status_code;
949
950 elsif (l_period_name is not null and l_period_year is not null) then
951
952 update gl_period_statuses
953 set migration_status_code = 'U'
954 where period_year = l_period_year
955 and period_name = l_period_name
956 and migration_status_code = 'P'
957 and ledger_id = l_set_of_books_id
958 and application_id = l_application_id;
959
960 p_status_code := 'P';
961 COMMIT;
962 return p_status_code;
963
964 end if;
965
966 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
967 trace
968 (p_msg => 'END of procedure Set_Migration_Status_Code'
969 ,p_level => C_LEVEL_PROCEDURE
970 ,p_module => l_log_module);
971 END IF;
972
973 EXCEPTION
974 WHEN xla_exceptions_pkg.application_exception THEN
975
976 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
977 trace
978 (p_msg => 'Set_Migration_Status_Code ended in error'
979 ,p_level => C_LEVEL_PROCEDURE
980 ,p_module => l_log_module);
981 END IF;
982
983 RAISE;
984 WHEN OTHERS THEN
985 xla_exceptions_pkg.raise_message
986 (p_location => 'XLA_UPGRADE_PUB.Set_Migration_Status_Code');
987
988 end set_migration_status_code;
989
990 /*============================================================================+
991 | |
992 | Public Procedure |
993 | |
994 | Set_Status_Code |
995 | |
996 | This procedure is called during the Upgrade On-Demand, to update the |
997 | status code, and also to call the product team hooks. |
998 +============================================================================*/
999
1000 PROCEDURE Set_status_code
1001 (p_errbuf OUT NOCOPY VARCHAR2,
1002 p_retcode OUT NOCOPY NUMBER,
1003 p_application_id IN NUMBER,
1004 p_ledger_id IN NUMBER,
1005 p_period_name IN VARCHAR2,
1006 p_number_of_workers IN NUMBER,
1007 p_batch_size IN NUMBER)
1008 IS
1009 l_application_id NUMBER;
1010 l_source_name xla_subledgers.je_source_name%TYPE;
1011 l_application_name fnd_application_vl.application_name%TYPE;
1012 l_ledger_id NUMBER;
1013 l_period_name VARCHAR2(15);
1014 l_upgraded_period_name VARCHAR2(15);
1015 l_batch_size NUMBER;
1016 l_number_of_workers NUMBER;
1017 l_error_buf VARCHAR2(1000);
1018 l_retcode NUMBER := -1 ;
1019 l_processed VARCHAR2(1) := ' ';
1020 l_start_date DATE;
1021 l_end_date DATE;
1022 l_log_module VARCHAR2(240);
1023 no_upgrade EXCEPTION;
1024 upgrade_error EXCEPTION;
1025 l_temp BOOLEAN;
1026 l_retcode_char VARCHAR2(10);
1027 /* variables added for bug:8717476 */
1028 l_program_running NUMBER;
1029 l_prev_run_status VARCHAR2(20);
1030 l_hotpatch_running NUMBER;
1031 mutliple_prgms_running EXCEPTION;
1032 recovery_run_incorrect EXCEPTION;
1033 incorrect_upg_date EXCEPTION;
1034 pending_periods EXCEPTION;
1035 un_registered_application EXCEPTION;
1036 incorrect_prior_run_status EXCEPTION;
1037 upgrade_by_patch_running EXCEPTION;
1038 l_upg_ledger_name VARCHAR2(30);
1039 l_upg_ledger_id NUMBER;
1040 l_upg_start_date DATE;
1041 l_upg_end_date DATE;
1042 l_upg_period_name VARCHAR2(15);
1043 l_upg_batch_size NUMBER;
1044 l_upg_number_of_workers NUMBER;
1045 l_pending_periods NUMBER;
1046 l_step_value VARCHAR2(100);
1047 C_DEFAULT_BATCH_SIZE NUMBER := 10000 ;
1048 C_DEFAULT_NUM_OF_WORKERS NUMBER := 1 ;
1049 /* end of new variables */
1050
1051 -- Cursor to get the Ledger and Minimum Upgraded Period for Projects Accounting
1052 -- Projects use 275 application of Oralce Grants(8721)
1053 CURSOR c_pa_last_date(i_ledger_id NUMBER) IS
1054 SELECT gps.ledger_id ledger_id,
1055 Min(gps.start_date) last_date
1056 FROM gl_period_statuses gps
1057 WHERE gps.migration_status_code = 'U'
1058 AND gps.application_id IN (275,8721)
1059 AND gps.ledger_id IN (SELECT l.ledger_id
1060 FROM gl_ledgers l
1061 WHERE l.ledger_id IN (SELECT DISTINCT target_ledger_id
1062 FROM gl_ledger_relationships glr
1063 WHERE glr.primary_ledger_id = i_ledger_id
1064 AND glr.application_id = 101
1065 AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
1066 AND glr.relationship_type_code = 'SUBLEDGER')
1067 OR (glr.target_ledger_category_code IN ('PRIMARY')
1068 AND glr.relationship_type_code = 'NONE')))
1069
1070 AND Nvl(l.complete_flag,'Y') = 'Y')
1071 GROUP BY gps.ledger_id;
1072
1073 -- Cursor to get the Ledger and Minimum Upgraded Period for NON-Projects Accounting
1074 CURSOR c_last_date(i_application_id NUMBER,
1075 i_ledger_id NUMBER) IS
1076 SELECT gps.ledger_id ledger_id,
1077 Min(start_date) last_date
1078 FROM gl_period_statuses gps
1079 WHERE gps.migration_status_code = 'U'
1080 AND gps.application_id = i_application_id
1081 AND gps.ledger_id IN (SELECT l.ledger_id
1082 FROM gl_ledgers l
1083 WHERE l.ledger_id IN (SELECT DISTINCT target_ledger_id
1084 FROM gl_ledger_relationships glr
1085 WHERE glr.primary_ledger_id = i_ledger_id
1086 AND glr.application_id = 101
1087 AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
1088 AND glr.relationship_type_code = 'SUBLEDGER')
1089 OR (glr.target_ledger_category_code IN ('PRIMARY')
1090 AND glr.relationship_type_code = 'NONE')))
1091 AND Nvl(l.complete_flag,'Y') = 'Y')
1092 GROUP BY gps.ledger_id;
1093 BEGIN
1094 IF g_log_enabled THEN
1095 l_log_module := c_default_module
1096 ||'.set_status_code';
1097 END IF;
1098
1099 IF (c_level_statement >= g_log_level) THEN
1100 Trace('set_status_code.Begin',c_level_statement,
1101 l_log_module);
1102 END IF;
1103
1104 IF p_batch_size IS NOT NULL THEN
1105 l_batch_size := p_batch_size;
1106 ELSE
1107 l_batch_size := C_DEFAULT_BATCH_SIZE ;
1108 END IF;
1109
1110 IF p_number_of_workers IS NOT NULL THEN
1111 l_number_of_workers := p_number_of_workers;
1112 ELSE
1113 l_number_of_workers := C_DEFAULT_NUM_OF_WORKERS ;
1114 END IF;
1115
1116 l_application_id := p_application_id;
1117
1118 l_period_name := p_period_name;
1119
1120 l_ledger_id := p_ledger_id;
1121
1122 IF (c_level_statement >= g_log_level) THEN
1123 Trace('l_application_id '
1124 ||l_application_id,c_level_statement,l_log_module);
1125 END IF;
1126
1127 IF (c_level_statement >= g_log_level) THEN
1128 Trace('l_ledger_id '
1129 ||l_ledger_id,c_level_statement,l_log_module);
1130 END IF;
1131
1132 IF (c_level_statement >= g_log_level) THEN
1133 Trace('l_period_name '
1134 ||l_period_name,c_level_statement,l_log_module);
1135 END IF;
1136
1137 IF (c_level_statement >= g_log_level) THEN
1138 Trace('l_number_of_workers '
1139 ||l_number_of_workers,c_level_statement,l_log_module);
1140 END IF;
1141
1142 IF (c_level_statement >= g_log_level) THEN
1143 Trace('l_batch_size '
1144 ||l_batch_size,c_level_statement,l_log_module);
1145 END IF;
1146
1147 SELECT application_name
1148 INTO l_application_name
1149 FROM fnd_application_vl v
1150 WHERE v.application_id = p_application_id;
1151
1152
1153 /* 707 - Cost Management 201 - Purchasing
1154 200 - Payables 222 - Receivables 140 - Fixed Assets
1155 New Applications need to add application ID here */
1156 IF p_application_id NOT IN (707,201,200,222,140) THEN
1157 RAISE un_registered_application;
1158 END IF;
1159
1160 /* FA uses GL's period
1161 Cost Management Uses Inventory Periods */
1162 IF p_application_id = 140 THEN
1163 l_application_id := 101;
1164 ELSIF p_application_id = 707 THEN
1165 l_application_id := 401;
1166 END IF;
1167
1168 -- This has been achieved by CP Incompatibility, by making upgrade CP incompatible with itself.
1169 /*
1170 -- Check that no TWO Upgrade Program's Run at the SAME TIME
1171 SELECT Count(1)
1172 INTO l_program_running
1173 FROM fnd_concurrent_requests fcr
1174 WHERE (fcr.program_application_id,fcr.concurrent_program_id) IN (SELECT fcp.application_id,
1175 fcp.concurrent_program_id
1176 FROM fnd_concurrent_programs fcp
1177 WHERE fcp.application_id = 602
1178 AND fcp.concurrent_program_name = 'XLAONDEUPG')
1179 AND fcr.phase_code = 'R';
1180
1181 -- For Multiple Programs Running Raise Error.
1182 IF (l_program_running > 1) THEN
1183 RAISE mutliple_prgms_running;
1184 END IF;
1185 */
1186 -- Check the status of the previous run
1187 BEGIN
1188 SELECT status_code
1189 INTO l_prev_run_status
1190 FROM xla_upgrade_requests
1191 WHERE application_id = p_application_id
1192 AND program_code = 'ONDEMAND UPGRADE';
1193 EXCEPTION
1194 WHEN no_data_found THEN
1195 l_prev_run_status := C_INITIAL_STATUS;
1196 END;
1197
1198 IF (l_prev_run_status NOT IN ( C_INITIAL_STATUS ,C_SUCCESS_STATUS ,C_ERROR_STATUS)) THEN
1199 IF (l_prev_run_status = C_PROGRESS_STATUS ) THEN
1200 fnd_file.Put_line(fnd_file.LOG,'Previous Run for Upgrade for Application : '
1201 ||l_application_name
1202 ||' is in PENDING STATUS.'
1203 ||'Marking it as ERROR and Proceeding ');
1204
1205 l_prev_run_status := C_ERROR_STATUS;
1206 ELSE
1207 RAISE incorrect_prior_run_status;
1208 END IF;
1209 END IF;
1210
1211 -- Extra Validation in place to not to allow any two concurrent program's
1212 -- to run simultaneously
1213 EXECUTE IMMEDIATE 'LOCK TABLE XLA_UPGRADE_DATES IN EXCLUSIVE MODE NOWAIT ';
1214 EXECUTE IMMEDIATE 'LOCK TABLE XLA_UPGRADE_REQUESTS IN EXCLUSIVE MODE NOWAIT ';
1215
1216 SELECT Count(1)
1217 INTO l_hotpatch_running
1218 FROM xla_upgrade_requests
1219 WHERE application_id = 602
1220 AND status_code IN (C_INITIAL_STATUS , C_PROGRESS_STATUS) ;
1221
1222 IF( l_hotpatch_running > 0 ) THEN
1223 RAISE upgrade_by_patch_running ;
1224 END IF;
1225
1226 -- Retreive the List of Primary and ALC Ledgers for Upgrade
1227 SELECT target_ledger_id
1228 BULK COLLECT INTO g_array_ledger_id
1229 FROM gl_ledger_relationships glr
1230 WHERE glr.application_id = 101
1231 AND glr.primary_ledger_id = p_ledger_id
1232 AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
1233 AND glr.relationship_type_code = 'SUBLEDGER')
1234 OR (glr.target_ledger_category_code IN ('PRIMARY')
1235 AND glr.relationship_type_code = 'NONE'));
1236
1237 IF l_application_id = 275 THEN
1238 -- As Projects are not participating in this Upgrade, commenting the code for time being
1239 NULL;
1240 /*
1241 -- Since Application ID 275(Projects) might have periods either iby 275
1242 -- or 8721 , so query both the application_ids'
1243 SELECT gps.start_date
1244 INTO l_start_date
1245 FROM gl_period_statuses gps
1246 WHERE gps.application_id IN (275, 8721)
1247 AND gps.ledger_id = p_ledger_id
1248 AND gps.period_name = p_period_name;
1249
1250 fnd_file.put_line(fnd_file.log, '*Start date : '|| to_char(l_start_date));
1251
1252 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1253 trace('Start date of upgrade '|| l_start_date,
1254 C_LEVEL_STATEMENT, l_Log_module);
1255 END IF;
1256
1257 SELECT min(gps.start_date)
1258 INTO l_end_date
1259 FROM gl_period_statuses gps
1260 WHERE gps.migration_status_code = 'U'
1261 AND gps.ledger_id = p_ledger_id
1262 AND gps.application_id IN (275, 8721) ;
1263
1264 fnd_file.put_line(fnd_file.log, '*End date : '|| to_char(l_end_date));
1265
1266 IF l_end_date IS NOT NULL THEN
1267
1268 select distinct gps.period_name
1269 into l_upgraded_period_name
1270 from gl_period_statuses gps
1271 WHERE gps.migration_status_code = 'U'
1272 AND gps.ledger_id = p_ledger_id
1273 AND gps.start_date = l_end_date
1274 AND gps.application_id IN (275, 8721) ;
1275
1276 END IF;
1277
1278 SELECT count(*)
1279 INTO l_pending_periods
1280 FROM gl_period_statuses gps
1281 WHERE gps.migration_status_code = 'P'
1282 AND gps.application_id IN (275, 8721)
1283 AND gps.ledger_id IN ( SELECT l.ledger_id
1284 FROM gl_ledgers l
1285 WHERE l.ledger_id IN (SELECT DISTINCT glr.target_ledger_id
1286 FROM gl_ledger_relationships glr
1287 WHERE glr.primary_ledger_id = p_ledger_id
1288 AND glr.application_id = 101
1289 AND (( glr.target_ledger_category_code IN ('SECONDARY' , 'ALC')
1290 AND glr.relationship_type_code = 'SUBLEDGER' )
1291 OR
1292 ( glr.target_ledger_category_code IN ('PRIMARY')
1293 AND glr.relationship_type_code = 'NONE' )
1294 )
1295 )
1296 AND nvl(l.complete_flag,'Y') = 'Y' ) ;
1297 */
1298 ELSE
1299 -- Choosing the start date of the first period that has to be migrated.
1300 SELECT gps.start_date
1301 INTO l_start_date
1302 FROM gl_period_statuses gps
1303 WHERE gps.application_id = l_application_id
1304 AND gps.ledger_id = p_ledger_id
1305 AND gps.period_name = p_period_name;
1306
1307 fnd_file.Put_line(fnd_file.LOG,'*Start date : '||To_char(l_start_date));
1308
1309 IF (c_level_statement >= g_log_level) THEN
1310 Trace('Start date of upgrade '||l_start_date,c_level_statement,l_log_module);
1311 END IF;
1312
1313 -- Choosing the start date of the last period that was migrated.
1314 SELECT Min(gps.start_date)
1315 INTO l_end_date
1316 FROM gl_period_statuses gps
1317 WHERE gps.migration_status_code = 'U'
1318 AND gps.ledger_id = p_ledger_id
1319 AND gps.application_id = l_application_id;
1320
1321 fnd_file.Put_line(fnd_file.LOG,'*End date : '||To_char(l_end_date));
1322
1323 IF l_end_date IS NOT NULL THEN
1324
1325 select gps.period_name
1326 into l_upgraded_period_name
1327 from gl_period_statuses gps
1328 WHERE gps.migration_status_code = 'U'
1329 AND gps.ledger_id = p_ledger_id
1330 AND gps.start_date = l_end_date
1331 AND gps.application_id = l_application_id ;
1332
1333 IF (c_level_statement >= g_log_level) THEN
1334 Trace('Last Successfully Upgraded Period '||l_upgraded_period_name,c_level_statement,l_log_module);
1335 END IF;
1336
1337 END IF;
1338
1339 SELECT Count(*)
1340 INTO l_pending_periods
1341 FROM gl_period_statuses gps
1342 WHERE gps.migration_status_code = 'P'
1343 AND gps.application_id = l_application_id
1344 AND gps.ledger_id IN (SELECT l.ledger_id
1345 FROM gl_ledgers l
1346 WHERE l.ledger_id IN (SELECT DISTINCT glr.target_ledger_id
1347 FROM gl_ledger_relationships glr
1348 WHERE glr.primary_ledger_id = p_ledger_id
1349 AND glr.application_id = 101
1350 AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
1351 AND glr.relationship_type_code = 'SUBLEDGER')
1352 OR (glr.target_ledger_category_code IN ('PRIMARY')
1353 AND glr.relationship_type_code = 'NONE')))
1354 AND Nvl(l.complete_flag,'Y') = 'Y');
1355 END IF;
1356
1357 /************ ALL VALIDATIONS BEFORE UPGRADE KICKS OFF **************************/
1358 IF l_end_date IS NULL THEN
1359 RAISE no_upgrade;
1360 END IF;
1361
1362 -- Check for Correct Dates being passed.
1363 IF l_start_date >= l_end_date THEN
1364 RAISE incorrect_upg_date;
1365 END IF;
1366
1367 -- Check for any pending Upgrade Periods.
1368 IF l_pending_periods <> 0 THEN
1369 RAISE pending_periods;
1370 END IF;
1371
1372 -- Check if its a RE-RUN or a FRESH Run
1373 -- If ReRun then DATES in XLA_UPGRADES should be same as L_START_DATE AND L_END_DATE
1374 IF l_prev_run_status = C_ERROR_STATUS THEN
1375 -- Need to Check if the ledger is same or not ???
1376 BEGIN
1377 SELECT xur.ledger_id,
1378 xur.start_date,
1379 xur.end_date,
1380 xur.workers_num,
1381 xur.batch_size,
1382 xur.period_name
1383 INTO l_upg_ledger_id,
1384 l_upg_start_date,
1385 l_upg_end_date,
1386 l_upg_number_of_workers,
1387 l_upg_batch_size,
1388 l_upg_period_name
1389 FROM xla_upgrade_requests xur
1390 WHERE xur.application_id = p_application_id
1391 AND xur.program_code = 'ONDEMAND UPGRADE';
1392
1393 IF (c_level_statement >= g_log_level) THEN
1394 Trace('Last Run Upgrade Details '||
1395 'upg_ledger_id: '||l_upg_ledger_id ||
1396 ' upg_start_date: '||l_upg_start_date ||
1397 ' upg_end_date: '||l_upg_end_date ||
1398 ' upg_number_of_workers: '||l_upg_number_of_workers ||
1399 ' upg_batch_size: '|| l_upg_batch_size ||
1400 ' l_upg_period_name: '|| l_upg_period_name ,c_level_statement,l_log_module);
1401 END IF;
1402
1403 SELECT l.name
1404 INTO l_upg_ledger_name
1405 FROM gl_ledgers l
1406 WHERE l.ledger_id = l_upg_ledger_id ;
1407
1408
1409
1410 EXCEPTION
1411 WHEN no_data_found THEN
1412 IF (c_level_statement >= g_log_level) THEN
1413 Trace('No Concurrent Program Upgrade is run for application: '||p_application_id,c_level_statement,l_log_module);
1414 END IF;
1415 l_upg_ledger_id := 0;
1416 l_upg_ledger_name := '-1' ;
1417 END;
1418
1419 IF ((l_upg_ledger_id <> l_ledger_id)
1420 OR (l_upg_start_date <> l_start_date)
1421 OR ((l_upg_end_date + 1) <> l_end_date)
1422 OR (l_upg_period_name <> l_period_name)
1423 OR (NVL(l_upg_number_of_workers,C_DEFAULT_NUM_OF_WORKERS ) <> l_number_of_workers)
1424 OR (NVL(l_upg_batch_size,C_DEFAULT_BATCH_SIZE ) <> l_batch_size )) THEN
1425 RAISE recovery_run_incorrect;
1426 END IF;
1427
1428 ELSIF l_prev_run_status = C_INITIAL_STATUS THEN
1429 INSERT INTO xla_upgrade_requests
1430 (application_id,
1431 request_control_id,
1432 status_code,
1433 phase_num,
1434 ledger_id,
1435 order_num,
1436 creation_date,
1437 created_by,
1438 last_update_date,
1439 last_updated_by,
1440 program_code)
1441 VALUES (p_application_id,
1442 0,
1443 C_INITIAL_STATUS ,
1444 p_application_id,
1445 p_ledger_id,
1446 p_application_id,
1447 SYSDATE,
1448 -169,
1449 SYSDATE,
1450 -169,
1451 'ONDEMAND UPGRADE');
1452 IF (c_level_statement >= g_log_level) THEN
1453 Trace('Inserted a row into XLA_UPGRADE_REQUESTS for application: '||p_application_id,c_level_statement,l_log_module);
1454 END IF;
1455 END IF;
1456
1457 -- This has been achieved by CP Incompatibility, by making upgrade CP incompatible with itself.
1458 /*
1459 -- Check that no TWO Upgrade Program's Run at the SAME TIME
1460 SELECT Count(1)
1461 INTO l_program_running
1462 FROM fnd_concurrent_requests fcr
1463 WHERE (fcr.program_application_id,fcr.concurrent_program_id) IN (SELECT fcp.application_id,
1464 fcp.concurrent_program_id
1465 FROM fnd_concurrent_programs fcp
1466 WHERE fcp.application_id = 602
1467 AND fcp.concurrent_program_name = 'XLAONDEUPG')
1468 AND fcr.phase_code = 'R';
1469
1470 -- For Multiple Programs Running Raise Error.
1471 IF (l_program_running > 1) THEN
1472 RAISE mutliple_prgms_running;
1473 END IF;
1474 */
1475
1476 IF l_start_date <> l_end_date THEN
1477
1478 /* Update the data for the current run */
1479 UPDATE xla_upgrade_requests
1480 SET status_code = C_PROGRESS_STATUS,
1481 request_control_id = xla_upgrade_requests_s.nextval,
1482 batch_size = p_batch_size,
1483 workers_num = p_number_of_workers,
1484 period_name = p_period_name,
1485 start_date = l_start_date,
1486 end_date = l_end_date - 1,
1487 ledger_id = p_ledger_id,
1488 last_update_date = SYSDATE ,
1489 last_updated_by = -169
1490 WHERE application_id = p_application_id
1491 AND program_code = 'ONDEMAND UPGRADE';
1492
1493 COMMIT;
1494
1495 l_step_value := 'PERIOD_PENDING_UPGRADE';
1496
1497 IF p_application_id = 275 THEN
1498 NULL;
1499 -- Projects are not participating in this upgrade, so for time being commented
1500 /*
1501 FOR i_ledger_periods IN c_pa_last_date( p_ledger_id )
1502 LOOP
1503 UPDATE gl_period_statuses
1504 SET migration_status_code = 'P'
1505 ,last_update_date = SYSDATE
1506 ,last_updated_by = 3 -- -169 changed to 3 for bug11854401
1507 -- ,last_update_login = 3 -- -169 changed to 3 for bug11854401
1508 WHERE ledger_id = i_ledger_periods.ledger_id
1509 AND ( end_date >= l_start_date
1510 and end_date < i_ledger_periods.last_date)
1511 AND application_id IN (275, 8721)
1512 AND adjustment_period_flag = 'N'
1513 -- AND closing_status in ('F', 'O', 'C', 'N') -- commented for bug12917429
1514 AND migration_status_code IS NULL;
1515
1516 fnd_file.put_line(fnd_file.log, '*Periods updated to P for ledger_id: '
1517 || i_ledger_periods.ledger_id || ' are : '|| to_char(SQL%ROWCOUNT));
1518
1519 END LOOP ;
1520 */
1521 ELSE
1522 FOR i_ledger_periods IN c_last_date(l_application_id,p_ledger_id) LOOP
1523 UPDATE gl_period_statuses
1524 SET migration_status_code = 'P'
1525 ,last_update_date = SYSDATE
1526 ,last_updated_by = 3 -- -169 changed to 3 for bug11854401
1527 -- ,last_update_login = 3 -- -169 changed to 3 for bug11854401
1528 WHERE application_id = l_application_id
1529 AND ledger_id = i_ledger_periods.ledger_id
1530 AND (end_date >= l_start_date
1531 AND end_date < i_ledger_periods.last_date)
1532 AND adjustment_period_flag = 'N'
1533 -- AND closing_status in ('F', 'O', 'C', 'N') -- commented for bug12917429
1534 AND migration_status_code IS NULL;
1535
1536 fnd_file.Put_line(fnd_file.LOG,'*Periods updated to P for ledger_id: ' ||i_ledger_periods.ledger_id
1537 ||' are : ' ||To_char(SQL%ROWCOUNT));
1538 END LOOP;
1539 END IF;
1540
1541 DELETE FROM xla_upgrade_dates;
1542
1543 -- Inserting details of ledgers , start date and end date for use by product teams
1544 IF p_application_id = 275 THEN
1545 NULL;
1546 -- Projects is not participating in this upgrade. So for time being upgraded the code.
1547 /*
1548 FORALL i IN 1..v_array_ledger_id.COUNT
1549 INSERT INTO xla_upgrade_dates
1550 (ledger_id
1551 ,start_date
1552 ,end_date)
1553 SELECT gps.ledger_id
1554 ,min(start_date)
1555 ,max(end_date)
1556 FROM gl_period_statuses gps
1557 WHERE gps.migration_status_code = 'P'
1558 AND gps.application_id IN (275, 8721)
1559 AND gps.ledger_id = v_array_ledger_id(i)
1560 GROUP BY gps.ledger_id ;
1561 */
1562 ELSE
1563 FORALL i IN 1..g_array_ledger_id.COUNT
1564 INSERT INTO xla_upgrade_dates
1565 (ledger_id,
1566 start_date,
1567 end_date)
1568 SELECT gps.ledger_id,
1569 Min(start_date),
1570 Max(end_date)
1571 FROM gl_period_statuses gps
1572 WHERE gps.migration_status_code = 'P'
1573 AND gps.application_id = l_application_id
1574 AND gps.ledger_id = G_array_ledger_id(i)
1575 GROUP BY gps.ledger_id;
1576 END IF;
1577
1578 COMMIT;
1579
1580 IF (c_level_statement >= g_log_level) THEN
1581 Trace('Gather Statistics on XLA_UPGRADE_DATES',c_level_statement,l_log_module);
1582 END IF;
1583 fnd_stats.gather_table_stats('XLA', 'XLA_UPGRADE_DATES');
1584
1585 -- Call Product Team Upgrade Manager API's for Upgrade
1586 IF (c_level_statement >= g_log_level) THEN
1587 Trace('Calling Product APIs for actual upgrade.',c_level_statement,l_log_module);
1588 END IF;
1589
1590 BEGIN
1591 IF l_application_id = 101 THEN
1592 l_step_value := 'Upgrade for Assets via FA Master API' ;
1593 fa_upgharness_pkg.Fa_master_upg(l_error_buf,l_retcode,l_number_of_workers,
1594 l_batch_size);
1595
1596 fnd_file.Put_line(fnd_file.LOG,'*Return code from FA: '||To_char(l_retcode));
1597
1598 ELSIF l_application_id = 200 THEN
1599 l_step_value := 'E-Tax Upgrade for Payables via E-Tax Master API' ;
1600 zx_on_demand_trx_upgrade_pkg.Zx_trx_update_mgr(
1601 x_errbuf => l_error_buf,
1602 x_retcode => l_retcode,
1603 x_batch_size => l_batch_size,
1604 x_num_workers => l_number_of_workers,
1605 p_application_id => l_application_id,
1606 p_ledger_id => l_ledger_id ,
1607 p_period_name => l_period_name);
1608
1609 fnd_file.Put_line(fnd_file.LOG,'*Return code from ZX: '||To_char(l_retcode));
1610
1611 IF l_retcode = 0 THEN
1612 -- resetting the return code to NON-ZERO so that the success of ZX is not propagated to Products run.
1613 -- Also, if Product API's donot initiliaze the recode correctly,then ZX retcode is treated as Products Retcode.
1614 l_retcode := -1 ;
1615 l_step_value := 'Upgrade for Payables via Payables Master API' ;
1616 ap_xla_upgrade_pkg.Ap_xla_upgrade_ondemand(
1617 errbuf => l_error_buf,
1618 retcode => l_retcode,
1619 p_batch_size => l_batch_size,
1620 p_num_workers => l_number_of_workers);
1621
1622 fnd_file.Put_line(fnd_file.LOG,'*Return code from AP: '||To_char(l_retcode));
1623 END IF;
1624 ELSIF l_application_id = 222 THEN
1625 l_step_value := 'E-Tax Upgrade for Receivables via E-Tax Master API' ;
1626 zx_on_demand_trx_upgrade_pkg.Zx_trx_update_mgr(
1627 x_errbuf => l_error_buf,
1628 x_retcode => l_retcode,
1629 x_batch_size => l_batch_size,
1630 x_num_workers => l_number_of_workers,
1631 p_application_id => l_application_id,
1632 p_ledger_id => l_ledger_id ,
1633 p_period_name => l_period_name );
1634
1635 fnd_file.Put_line(fnd_file.LOG,'*Return code from ZX: '||To_char(l_retcode));
1636
1637 IF l_retcode = 0 THEN
1638 -- resetting the return code to NON-ZERO so that the success of ZX is not propagated to Products run.
1639 -- Also, if Product API's donot initiliaze the recode correctly,then ZX retcode is treated as Products Retcode.
1640 l_retcode := -1 ;
1641 l_step_value := 'Upgrade for Receivables via Receivables Master API' ;
1642 ar_upgharness_pkg.Ar_master_upg(l_error_buf,
1643 l_retcode,
1644 l_ledger_id,
1645 l_period_name,
1646 l_number_of_workers,
1647 l_batch_size);
1648
1649 fnd_file.Put_line(fnd_file.LOG,'*Return code from AR: ' ||To_char(l_retcode));
1650 END IF;
1651 ELSIF l_application_id = 275 THEN
1652 NULL;
1653 -- Proect's is not participating in this upgrade. So, commented call for time being.
1654 /*
1655 l_step_value := 'Upgrade for Projects via Projects Master API' ;
1656 PA_UPGHARNESS_PKG.pa_master_upg
1657 (l_error_buf
1658 ,l_retcode
1659 ,l_number_of_workers
1660 ,l_batch_size);
1661
1662 fnd_file.put_line(fnd_file.log, '*Return code from PA: '|| to_char(l_retcode));
1663 */
1664 ELSIF l_application_id IN (401) THEN
1665 l_retcode := -1 ;
1666 l_step_value := 'Upgrade for Inventory/WIP via Costing Master API' ;
1667 CST_SLA_UPDATE_PKG.CST_Upgrade_Wrapper (
1668 X_errbuf => l_error_buf ,
1669 X_retcode => l_retcode_char ,
1670 X_batch_size => l_batch_size ,
1671 X_Num_Workers => l_number_of_workers ,
1672 X_ledger_id => p_ledger_id ,
1673 X_application_id => l_application_id ) ;
1674
1675 fnd_file.Put_line(fnd_file.LOG,'*Return code from CST :' ||l_retcode_char ||' '||l_error_buf);
1676
1677 IF l_retcode_char = 'S' THEN
1678 l_retcode := 0;
1679 END IF;
1680
1681 ELSIF l_application_id = 201 THEN
1682 l_step_value := 'E-Tax Upgrade for Receiving via E-Tax Master API' ;
1683 zx_on_demand_trx_upgrade_pkg.Zx_trx_update_mgr( x_errbuf => l_error_buf,
1684 x_retcode => l_retcode,
1685 x_batch_size => l_batch_size,
1686 x_num_workers => l_number_of_workers,
1687 p_application_id => l_application_id,
1688 p_ledger_id => l_ledger_id ,
1689 p_period_name => l_period_name);
1690
1691 fnd_file.Put_line(fnd_file.LOG,'*Return code from ZX: '||To_char(l_retcode));
1692
1693 IF l_retcode = 0 THEN
1694 -- resetting the return code to NON-ZERO so that the success of ZX is not propagated to Products run.
1695 -- Also, if Product API's donot initiliaze the recode correctly,then ZX retcode is treated as Products Retcode.
1696 l_retcode := -1 ;
1697
1698 l_step_value := 'Upgrade for Receiving via Costing Master API' ;
1699 CST_SLA_UPDATE_PKG.CST_Upgrade_Wrapper (
1700 X_errbuf => l_error_buf ,
1701 X_retcode => l_retcode_char ,
1702 X_batch_size => l_batch_size ,
1703 X_Num_Workers => l_number_of_workers ,
1704 X_ledger_id => p_ledger_id ,
1705 X_application_id => l_application_id ) ;
1706
1707 fnd_file.Put_line(fnd_file.LOG,'*Return code from Receiving(PO): ' ||l_retcode_char);
1708
1709 IF l_retcode_char = 'S' THEN
1710 l_retcode := 0;
1711 END IF;
1712 END IF;
1713 END IF;
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 IF (c_level_statement >= g_log_level) THEN
1717 Trace('Upgrade failed at: '||l_step_value ,c_level_statement,l_log_module);
1718 END IF;
1719
1720 fnd_file.Put_line(fnd_file.LOG,'Upgrade failed at: '||l_step_value );
1721 fnd_file.Put_line(fnd_file.LOG,'Updating the Return Code as 2(ERROR)');
1722 -- Set the Retcode as 2, so that it marks the product upgrade as ERROR
1723 l_retcode := 2 ; -- changes for 8834301
1724 END;
1725
1726 IF l_retcode = 0 THEN /* means no error in the upgrade */
1727 -- Upgrade The Request Status as SUCCESS
1728 Update_upg_request_status(p_application_id,C_SUCCESS_STATUS );
1729
1730 -- Upgdate the GL Journal's JE_FROM_SLA_FLAG as Upgraded.
1731 IF p_application_id NOT IN (200,275) THEN
1732 SELECT je_source_name
1733 INTO l_source_name
1734 FROM xla_subledgers
1735 WHERE application_id = p_application_id;
1736
1737 fnd_file.Put_line(fnd_file.LOG,'*Source name : '|| l_source_name);
1738
1739 FORALL i IN 1..g_array_ledger_id.COUNT
1740 UPDATE gl_je_headers a
1741 SET a.je_from_sla_flag = decode(a.reversed_je_header_id,null,'U','N') ,
1742 a.je_source = Decode(a.je_source,'Inventory','Cost Management',
1743 'Purchasing','Cost Management',
1744 je_source),
1745 a.last_update_date = SYSDATE,
1746 a.last_updated_by = 3 -- -169, changed to 3 for bug11854401
1747 -- ,a.last_update_login = 3 -- -169 changed to 3 for bug11854401
1748 WHERE (Decode(a.je_source,'Receivables',222,
1749 'Assets',101,
1750 'Inventory',401,
1751 'Purchasing',201,
1752 -101),ledger_id,period_name) IN (SELECT gps.application_id,
1753 gps.ledger_id,
1754 gps.period_name
1755 FROM gl_period_statuses gps
1756 WHERE gps.end_date >= l_start_date
1757 AND gps.end_date < l_end_date
1758 AND gps.ledger_id = G_array_ledger_id(i)
1759 AND gps.application_id = l_application_id
1760 AND gps.migration_status_code = 'U')
1761 AND a.je_from_sla_flag IS NULL
1762 AND a.je_source <> 'Project Accounting'
1763 AND a.actual_flag = 'A'
1764 AND EXISTS (SELECT 1
1765 FROM xla_subledgers xsu
1766 WHERE xsu.je_source_name = a.je_source);
1767
1768 fnd_file.Put_line(fnd_file.LOG,'*Flags updated to U : '||To_char(SQL%ROWCOUNT));
1769
1770 IF (c_level_statement >= g_log_level) THEN
1771 Trace('Updated gl_je_headers',c_level_statement,l_log_module);
1772 END IF;
1773 END IF;
1774 ELSE
1775 -- Upgrade The Request Status as ERROR
1776 Update_upg_request_status(p_application_id,C_ERROR_STATUS);
1777
1778 -- Reset the periods to NULL
1779 Reset_period_statuses(l_application_id);
1780
1781 -- Raise Error
1782 RAISE upgrade_error;
1783 END IF;
1784 END IF;
1785
1786 COMMIT;
1787
1788
1789 EXCEPTION
1790 WHEN no_upgrade THEN
1791 ROLLBACK ;
1792 IF (c_level_statement >= g_log_level) THEN
1793 Trace('This is either a fresh R12 installation or upgrade from an existing 11i instance has not taken place.',
1794 c_level_error,l_log_module);
1795 END IF;
1796 xla_messages_pkg.build_message
1797 (p_appli_s_name => 'XLA'
1798 ,p_msg_name => 'XLA_OD_UPG_NOT_ELIGIBLE'
1799 );
1800 p_retcode := 2 ;
1801 p_errbuf := xla_messages_pkg.get_message ;
1802
1803 WHEN upgrade_error THEN
1804 ROLLBACK ;
1805 IF (c_level_statement >= g_log_level) THEN
1806 Trace('There has been an error in the Product Upgrade',c_level_error,l_log_module);
1807 END IF;
1808 xla_messages_pkg.build_message
1809 (p_appli_s_name => 'XLA'
1810 ,p_msg_name => 'XLA_OD_PROD_API_ERROR'
1811 ,p_token_1 => 'P_APPLICATION_NAME'
1812 ,p_value_1 => l_application_name
1813 );
1814 fnd_file.Put_line(fnd_file.LOG, l_error_buf) ;
1815 p_retcode := 2 ;
1816 p_errbuf := xla_messages_pkg.get_message ;
1817
1818 WHEN incorrect_upg_date THEN
1819 ROLLBACK ;
1820 IF (c_level_statement >= g_log_level) THEN
1821 Trace('The provided start date for upgrade is incorrect. Please provide a valid start period for upgrade',
1822 c_level_error,l_log_module);
1823 END IF;
1824 xla_messages_pkg.build_message
1825 (p_appli_s_name => 'XLA'
1826 ,p_msg_name => 'XLA_OD_INCORRECT_PERIOD'
1827 ,p_token_1 => 'P_PERIOD_NAME'
1828 ,p_value_1 => l_upgraded_period_name
1829 );
1830 p_retcode := 2 ;
1831 p_errbuf := xla_messages_pkg.get_message ;
1832
1833 WHEN pending_periods THEN
1834 ROLLBACK ;
1835 IF (c_level_statement >= g_log_level) THEN
1836 Trace('There are periods pending in upgrade, upgrade cannot be run.',
1837 c_level_error,l_log_module);
1838 END IF;
1839 xla_messages_pkg.build_message
1840 (p_appli_s_name => 'XLA'
1841 ,p_msg_name => 'XLA_OD_PENDING_PERIODS'
1842 );
1843 p_retcode := 2 ;
1844 p_errbuf := xla_messages_pkg.get_message ;
1845
1846 WHEN recovery_run_incorrect THEN
1847 ROLLBACK ;
1848 IF (c_level_statement >= g_log_level) THEN
1849 Trace('Paremeters between Failed Request and present request are incorrect',
1850 c_level_error,l_log_module);
1851 END IF;
1852 xla_messages_pkg.build_message
1853 (p_appli_s_name => 'XLA'
1854 ,p_msg_name => 'XLA_OD_INCORRECT_RERUN'
1855 ,p_token_1 => 'P_LED'
1856 ,p_value_1 => l_upg_ledger_name
1857 ,p_token_2 => 'P_PRD'
1858 ,p_value_2 => l_upg_period_name
1859 ,p_token_3 => 'P_BTCH'
1860 ,p_value_3 => l_upg_batch_size
1861 ,p_token_4 => 'P_NUM_WRK'
1862 ,p_value_4 => l_upg_number_of_workers
1863 ) ;
1864 p_retcode := 2 ;
1865 p_errbuf := xla_messages_pkg.get_message ;
1866
1867 WHEN un_registered_application THEN
1868 ROLLBACK ;
1869 IF (c_level_statement >= g_log_level) THEN
1870 Trace('SLA UPGRADE is not enabled for this Application',c_level_error,l_log_module);
1871 END IF;
1872 xla_messages_pkg.build_message
1873 (p_appli_s_name => 'XLA'
1874 ,p_msg_name =>'XLA_OD_UNREG_APPLICATION'
1875 ,p_token_1 => 'P_APPLICATION_NAME'
1876 ,p_value_1 => l_application_name
1877 );
1878 p_retcode := 2 ;
1879 p_errbuf := xla_messages_pkg.get_message ;
1880
1881 /*
1882 WHEN mutliple_prgms_running THEN
1883 ROLLBACK ;
1884 IF (c_level_statement >= g_log_level) THEN
1885 Trace('Multiple upgrade by concurrent programs cannot be run.', c_level_error,l_log_module);
1886 END IF;
1887 xla_messages_pkg.build_message
1888 (p_appli_s_name => 'XLA'
1889 ,p_msg_name => 'XLA_OD_MULTI_PRGM_RUNNING'
1890 );
1891 p_retcode := 2 ;
1892 p_errbuf := xla_messages_pkg.get_message ;
1893 */
1894
1895 WHEN incorrect_prior_run_status THEN
1896 ROLLBACK ;
1897 IF (c_level_statement >= g_log_level) THEN
1898 Trace('Incorrect status for Prior Upgrade Run.', c_level_error,l_log_module);
1899 END IF;
1900 xla_messages_pkg.build_message
1901 (p_appli_s_name => 'XLA'
1902 ,p_msg_name => 'XLA_OD_INCORRECT_STATUS'
1903 );
1904 p_retcode := 2 ;
1905 p_errbuf := xla_messages_pkg.get_message ;
1906
1907 WHEN upgrade_by_patch_running THEN
1908 ROLLBACK ;
1909 IF (c_level_statement >= g_log_level) THEN
1910 Trace('On Demand Upgrade by Patch is running, upgrade by concurrent program cannot be run.', c_level_error,l_log_module);
1911 END IF;
1912 xla_messages_pkg.build_message
1913 (p_appli_s_name => 'XLA'
1914 ,p_msg_name => 'XLA_OD_HOTPATCH_RUNNING'
1915 );
1916 p_retcode := 2 ;
1917 p_errbuf := xla_messages_pkg.get_message ;
1918
1919 WHEN xla_exceptions_pkg.application_exception THEN
1920 -- Upgrade The Request Status as ERROR
1921 Update_upg_request_status(p_application_id,C_ERROR_STATUS);
1922
1923 -- Reset the periods to NULL
1924 Reset_period_statuses(l_application_id);
1925 p_errbuf := xla_messages_pkg.get_message || l_error_buf;
1926 p_retcode := 2;
1927
1928 WHEN OTHERS THEN
1929 -- Upgrade The Request Status as ERROR
1930 Update_upg_request_status(p_application_id,C_ERROR_STATUS);
1931
1932 -- Reset the periods to NULL
1933 Reset_period_statuses(l_application_id);
1934 p_retcode := 2;
1935 p_errbuf := sqlerrm ;
1936
1937 END set_status_code;
1938
1939
1940 --PROCEDURE set_status_code
1941 --(p_error_buf OUT NOCOPY VARCHAR2,
1942 -- p_retcode OUT NOCOPY NUMBER,
1943 -- p_application_id IN NUMBER,
1944 -- p_ledger_id IN NUMBER,
1945 -- p_period_name IN VARCHAR2,
1946 -- p_number_of_workers IN NUMBER,
1947 -- p_batch_size IN NUMBER) IS
1948
1949 --l_application_id NUMBER;
1950 --l_source_name XLA_SUBLEDGERS.JE_SOURCE_NAME%TYPE;
1951 --l_application_name FND_APPLICATION_VL.APPLICATION_NAME%TYPE;
1952 --l_ledger_id NUMBER;
1953 --l_period_name VARCHAR2(15) ;
1954 --l_batch_size NUMBER;
1955 --l_number_of_workers NUMBER;
1956 --l_error_buf VARCHAR2(1000);
1957 --l_retcode NUMBER;
1958 --l_processed VARCHAR2(1) := ' ';
1959 --l_start_date date;
1960 --l_end_date date;
1961 --l_log_module VARCHAR2(240);
1962 --NO_UPGRADE EXCEPTION;
1963 --UPGRADE_ERROR EXCEPTION;
1964 --l_temp BOOLEAN;
1965 --l_retcode_char VARCHAR2(10);
1966
1967 --BEGIN
1968
1969 -- IF g_log_enabled THEN
1970 -- l_log_module := C_DEFAULT_MODULE||'.set_status_code';
1971 -- END IF;
1972
1973 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1974 -- trace('set_status_code.Begin',C_LEVEL_STATEMENT,l_log_module);
1975 -- END IF;
1976
1977 -- IF p_batch_size IS NOT NULL THEN
1978 -- l_batch_size := p_batch_size;
1979 -- ELSE
1980 -- l_batch_size := 10000;
1981 -- END IF;
1982
1983 -- IF p_number_of_workers IS NOT NULL THEN
1984 -- l_number_of_workers := p_number_of_workers;
1985 -- ELSE
1986 -- l_number_of_workers := 1;
1987 -- END IF;
1988
1989 -- l_application_id := p_application_id;
1990 -- l_period_name := p_period_name;
1991 -- l_ledger_id := p_ledger_id;
1992
1993 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1994 -- trace('l_application_id '||l_application_id,
1995 -- C_LEVEL_STATEMENT, l_Log_module);
1996 -- END IF;
1997
1998 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1999 -- trace('l_ledger_id '||l_ledger_id,
2000 -- C_LEVEL_STATEMENT, l_Log_module);
2001 -- END IF;
2002
2003 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2004 -- trace('l_period_name '||l_period_name,
2005 -- C_LEVEL_STATEMENT, l_Log_module);
2006 -- END IF;
2007
2008 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2009 -- trace('l_number_of_workers '||l_number_of_workers,
2010 -- C_LEVEL_STATEMENT, l_Log_module);
2011 -- END IF;
2012
2013 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2014 -- trace('l_batch_size '||l_batch_size,
2015 -- C_LEVEL_STATEMENT, l_Log_module);
2016 -- END IF;
2017
2018 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2019 -- trace('Processing periods...',
2020 -- C_LEVEL_STATEMENT, l_Log_module);
2021 -- END IF;
2022
2023 /* FA uses GL's period */
2024
2025 -- IF p_application_id = 140 then
2026 -- l_application_id :=101;
2027 -- END IF;
2028
2029 -- Check with application id 707 is done separately, since there will be
2030 -- no rows in GL_PERIOD_STATUSES corresponding to Application ID 707.
2031 -- Associated Applications are 201 (PO) and 401 (INV).
2032
2033 -- IF p_application_id = 707 then
2034
2035 -- Since Application ID 707 will have no rows in GL_PERIOD_STATUSES
2036 -- we are getting the minimum of start date for one of the two
2037 -- applications which are associated with Costing (707)
2038
2039 -- SELECT start_date
2040 -- INTO l_start_date
2041 -- FROM gl_period_statuses
2042 -- WHERE application_id = 401
2043 -- AND ledger_id = p_ledger_id
2044 -- AND period_name = p_period_name;
2045
2046 --fnd_file.put_line(fnd_file.log, '*Start date : '|| to_char(l_start_date));
2047
2048 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2049 -- trace('Start date of upgrade '|| l_start_date,
2050 -- C_LEVEL_STATEMENT, l_Log_module);
2051 -- END IF;
2052
2053 -- SELECT min(start_date)
2054 -- INTO l_end_date
2055 -- FROM gl_period_statuses
2056 -- WHERE migration_status_code = 'U'
2057 -- AND ledger_id = p_ledger_id
2058 -- AND application_id = 401;
2059
2060 --fnd_file.put_line(fnd_file.log, '*End date : '|| to_char(l_end_date));
2061
2062 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2063 -- trace('End date of upgrade '|| l_end_date,
2064 -- C_LEVEL_STATEMENT, l_Log_module);
2065 -- END IF;
2066
2067 -- if l_end_date is NULL it means that the database that the On Demand
2068 -- Program is being run on is a fresh installation or Upgrade has
2069 -- never been performed on this instance. Hence, warning will be raised.
2070
2071 -- IF l_end_date is NULL THEN
2072 -- RAISE NO_UPGRADE;
2073 -- END IF;
2074
2075 -- Updation of GL Period Statuses.
2076
2077 -- UPDATE gl_period_statuses
2078 -- SET migration_status_code = 'P'
2079 -- WHERE ledger_id = l_ledger_id
2080 -- AND (start_date >= l_start_date
2081 -- and end_date < l_end_date)
2082 -- AND application_id in (201,401)
2083 -- AND adjustment_period_flag = 'N'
2084 -- AND migration_status_code IS NULL;
2085
2086 --fnd_file.put_line(fnd_file.log, '*Periods updated to P : '|| to_char(SQL%ROWCOUNT));
2087
2088 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2089 -- trace('Updated gl_period_statuses. '
2090 -- , C_LEVEL_STATEMENT, l_Log_module);
2091 -- END IF;
2092
2093 -- ELSE /*End for 707 Case and begin for all other applications*/
2094
2095 -- Choosing the start date of the first period that
2096 -- has to be migrated.
2097
2098 -- SELECT start_date
2099 -- INTO l_start_date
2100 -- FROM gl_period_statuses
2101 -- WHERE application_id = l_application_id
2102 -- AND ledger_id = p_ledger_id
2103 -- AND period_name = p_period_name;
2104
2105 --fnd_file.put_line(fnd_file.log, '*Start date : '|| to_char(l_start_date));
2106
2107 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2108 -- trace('Start date of upgrade '|| l_start_date,
2109 -- C_LEVEL_STATEMENT, l_Log_module);
2110 -- END IF;
2111
2112 -- Choosing the start date of the last period that
2113 -- was migrated.
2114
2115 -- SELECT min(start_date)
2116 -- INTO l_end_date
2117 -- FROM gl_period_statuses
2118 -- WHERE migration_status_code = 'U'
2119 -- AND ledger_id = p_ledger_id
2120 -- AND application_id = l_application_id;
2121
2122 --fnd_file.put_line(fnd_file.log, '*End date : '|| to_char(l_end_date));
2123
2124 -- IF l_end_date is NULL THEN
2125 -- RAISE NO_UPGRADE;
2126 -- END IF;
2127
2128 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2129 -- trace('Ending date of upgrade '|| l_end_date,
2130 -- C_LEVEL_STATEMENT, l_Log_module);
2131 -- END IF;
2132
2133 -- UPDATE gl_period_statuses
2134 -- SET migration_status_code = 'P'
2135 -- WHERE application_id = l_application_id
2136 -- AND ledger_id = l_ledger_id
2137 -- AND (start_date >= l_start_date
2138 -- AND end_date < l_end_date)
2139 -- AND adjustment_period_flag = 'N'
2140 -- AND migration_status_code IS NULL;
2141
2142 --fnd_file.put_line(fnd_file.log, '*Periods updated to P : '|| to_char(SQL%ROWCOUNT));
2143
2144 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2145 -- trace('Updated gl_period_statuses',
2146 -- C_LEVEL_STATEMENT, l_Log_module);
2147 -- END IF;
2148
2149 -- END IF;
2150
2151 -- IF l_start_date <> l_end_date THEN
2152
2153 -- DELETE FROM xla_upgrade_dates;
2154
2155 -- Inserting details of ledgers , start date and end date
2156 -- for use by product teams
2157 -- INSERT INTO xla_upgrade_dates
2158 -- (ledger_id
2159 -- ,start_date
2160 -- ,end_date)
2161 -- VALUES (l_ledger_id
2162 -- ,l_start_date
2163 -- ,l_end_date-1);
2164
2165 /* Call Product Team hooks for Accounting Upgrade */
2166
2167 -- IF l_application_id = 101 THEN
2168
2169 -- FA_UPGHARNESS_PKG.fa_master_upg(
2170 -- l_error_buf,
2171 -- l_retcode,
2172 -- l_number_of_workers,
2173 -- l_batch_size);
2174
2175 --fnd_file.put_line(fnd_file.log, '*Return code from FA: '|| to_char(l_retcode));
2176
2177 -- ELSIF l_application_id = 200 then
2178
2179 -- AP_XLA_UPGRADE_PKG.AP_XLA_Upgrade_OnDemand(
2180 -- Errbuf => l_error_buf,
2181 -- Retcode => l_retcode,
2182 -- P_Batch_Size => l_batch_size,
2183 -- P_Num_Workers => l_number_of_workers);
2184
2185 --fnd_file.put_line(fnd_file.log, '*Return code from AP: '|| to_char(l_retcode));
2186
2187 -- IF l_retcode = 0 THEN
2188 -- ZX_ON_DEMAND_TRX_UPGRADE_PKG.zx_trx_update_mgr(
2189 -- X_errbuf => l_error_buf,
2190 -- X_retcode => l_retcode,
2191 -- X_batch_size =>l_batch_size,
2192 -- X_Num_Workers =>l_number_of_workers,
2193 -- p_application_id => l_application_id);
2194
2195 -- fnd_file.put_line(fnd_file.log, '*Return code from ZX: '|| to_char(l_retcode));
2196 -- END IF;
2197
2198 -- ELSIF l_application_id = 222 then
2199 -- AR_UPGHARNESS_PKG.ar_master_upg(
2200 -- l_error_buf,
2201 -- l_retcode,
2202 -- l_ledger_id,
2203 -- l_period_name,
2204 -- l_number_of_workers,
2205 -- l_batch_size);
2206
2207 --fnd_file.put_line(fnd_file.log, '*Return code from AR: '|| to_char(l_retcode));
2208
2209 -- IF l_retcode = 0 THEN
2210 -- ZX_ON_DEMAND_TRX_UPGRADE_PKG.zx_trx_update_mgr(
2211 -- X_errbuf => l_error_buf,
2212 -- X_retcode => l_retcode,
2213 -- X_batch_size =>l_batch_size,
2214 -- X_Num_Workers =>l_number_of_workers,
2215 -- p_application_id => l_application_id);
2216
2217 -- fnd_file.put_line(fnd_file.log, '*Return code from ZX: '|| to_char(l_retcode));
2218 -- END IF;
2219 -- ELSIF l_application_id = 275 THEN
2220
2221 -- PA_UPGHARNESS_PKG.pa_master_upg
2222 -- (l_error_buf
2223 -- ,l_retcode
2224 -- ,l_number_of_workers
2225 -- ,l_batch_size);
2226
2227 --fnd_file.put_line(fnd_file.log, '*Return code from PA: '|| to_char(l_retcode));
2228
2229 -- ELSIF l_application_id = 707 THEN
2230
2231 -- CST_SLA_UPDATE_PKG.Update_Proc_MGR (
2232 -- X_errbuf => l_error_buf,
2233 -- X_retcode => l_retcode_char,
2234 -- X_api_version => 1.0,
2235 -- X_init_msg_list => FND_API.G_FALSE,
2236 -- X_batch_size => l_batch_size,
2237 -- X_Num_Workers => l_number_of_workers,
2238 -- X_Argument4 => 'NULL',
2239 -- X_Argument5 => 'NULL',
2240 -- X_Argument6 => 'NULL',
2241 -- X_Argument7 => 'NULL',
2242 -- X_Argument8 => 'NULL',
2243 -- X_Argument9 => 'NULL',
2244 -- X_Argument10 => 'NULL');
2245
2246 --fnd_file.put_line(fnd_file.log, '*Return code from COST: '|| l_retcode_char);
2247 -- IF l_retcode_char = 'S' THEN
2248 -- l_retcode := 0;
2249 -- END IF;
2250
2251 -- END IF;
2252
2253 -- Updating gl_je_headers
2254 -- Check to ensure rows are not updated if process errors out
2255
2256 -- IF l_retcode = 0 THEN
2257 /* means no error in the upgrade */
2258
2259 -- IF p_application_id <> 707 THEN
2260
2261 -- SELECT je_source_name
2262 -- INTO l_source_name
2263 -- FROM XLA_SUBLEDGERS
2264 -- WHERE application_id = p_application_id;
2265
2266 --fnd_file.put_line(fnd_file.log, '*Source name : '|| l_source_name);
2267
2268 -- UPDATE gl_je_headers a
2269 -- SET a.je_from_sla_flag = 'U'
2270 -- WHERE (decode(a.je_source, l_source_name, p_application_id)
2271 -- ,ledger_id, period_name) in
2272 -- (SELECT application_id, ledger_id, period_name
2273 -- FROM gl_period_statuses b
2274 -- WHERE b.start_date >= l_start_date
2275 -- AND b.end_date < l_end_date
2276 -- AND b.ledger_id = l_ledger_id
2277 -- AND application_id = p_application_id)
2278 -- AND a.ledgeR_id = l_ledger_id;
2279
2280 --fnd_file.put_line(fnd_file.log, '*Flags updated to U : '|| to_char(SQL%ROWCOUNT));
2281
2282 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2283 -- trace('Updated gl_je_headers',
2284 -- C_LEVEL_STATEMENT, l_Log_module);
2285 -- END IF;
2286
2287 -- ELSE
2288
2289 -- UPDATE gl_je_headers a
2290 -- SET a.je_from_sla_flag = 'U'
2291 -- WHERE (decode(a.je_source,'Purchasing',201,'Inventory',401)
2292 -- ,ledger_id, period_name) in
2293 -- (SELECT application_id, ledger_id, period_name
2294 -- FROM gl_period_statuses b
2295 -- WHERE b.start_date >= l_start_date
2296 -- AND b.end_date < l_end_date
2297 -- AND b.ledger_id = l_ledger_id
2298 -- AND application_id in (201,401))
2299 -- AND a.ledgeR_id = l_ledger_id;
2300
2301 --fnd_file.put_line(fnd_file.log, '*Flags updated to U : '|| to_char(SQL%ROWCOUNT));
2302
2303 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2304 -- trace('Updated gl_je_headers',
2305 -- C_LEVEL_STATEMENT, l_Log_module);
2306 -- END IF;
2307
2308 -- COMMIT;
2309 -- END IF;
2310
2311 -- ELSE
2312
2313 -- IF p_application_id = 707 THEN
2314
2315 -- UPDATE gl_period_statuses
2316 -- SET migration_status_code = NULL
2317 -- WHERE ledger_id = l_ledger_id
2318 -- AND (start_date >= l_start_date
2319 -- and end_date < l_end_date)
2320 -- AND application_id in (201,401)
2321 -- AND adjustment_period_flag = 'N'
2322 -- AND migration_status_code = 'P';
2323
2324 --fnd_file.put_line(fnd_file.log, '*Migration status code back to NULL : '|| to_char(SQL%ROWCOUNT));
2325
2326 -- ELSE
2327
2328 -- UPDATE gl_period_statuses
2329 -- SET migration_status_code = NULL
2330 -- WHERE application_id = l_application_id
2331 -- AND ledger_id = l_ledger_id
2332 -- AND (start_date >= l_start_date
2333 -- AND end_date < l_end_date)
2334 -- AND adjustment_period_flag = 'N'
2335 -- AND migration_status_code = 'P';
2336
2337 --fnd_file.put_line(fnd_file.log, '*Migration status code back to NULL : '|| to_char(SQL%ROWCOUNT));
2338
2339 -- END IF;
2340
2341 -- RAISE UPGRADE_ERROR;
2342
2343 -- END IF;
2344
2345 -- END IF;
2346
2347 -- COMMIT;
2348
2349 --EXCEPTION
2350 -- WHEN NO_UPGRADE THEN
2351 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2352 -- trace('This is either a fresh R12 installation or upgrade from
2353 -- an existing 11i instance has not taken place.',
2354 -- C_LEVEL_ERROR, l_Log_module);
2355 -- END IF;
2356
2357 -- fnd_file.put_line(fnd_file.log, 'This is either a fresh R12
2358 -- installation or upgrade
2359 -- from an existing 11i instance
2360 -- has not taken place.');
2361
2362 -- l_temp := fnd_concurrent.set_completion_status
2363 -- (status => 'WARNING'
2364 -- ,message => NULL);
2365
2366 -- WHEN UPGRADE_ERROR THEN
2367 -- IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2368 -- trace('There has been an error in the Product Upgrade',
2369 -- C_LEVEL_ERROR, l_Log_module);
2370 -- END IF;
2371
2372 -- fnd_file.put_line(fnd_file.log, 'Product Team API Failed');
2373
2374 -- l_temp := fnd_concurrent.set_completion_status
2375 -- (status => 'ERROR'
2376 -- ,message => NULL);
2377
2378 -- WHEN xla_exceptions_pkg.application_exception THEN
2379 -- RAISE;
2380 -- WHEN OTHERS THEN
2381 -- xla_exceptions_pkg.raise_message
2382 -- (p_location => 'XLA_UPGRADE_PUB.set_status_code');
2383 --END SET_STATUS_CODE;
2384
2385 /*============================================================================+
2386 | |
2387 | Public Procedure |
2388 | |
2389 | Validate_Header_Line_Entries |
2390 | |
2391 | This routine is called to validate the Header entries in upgrade. |
2392 | |
2393 +============================================================================*/
2394
2395 PROCEDURE Validate_Header_Line_Entries
2396 (p_application_id IN NUMBER,
2397 p_header_id IN NUMBER) IS
2398
2399 l_entity_id t_entity_id;
2400 l_event_id t_event_id;
2401 l_header_id t_header_id;
2402 l_line_num t_line_num;
2403 l_header_error1 t_error_flag;
2404 l_header_error2 t_error_flag;
2405 l_header_error3 t_error_flag;
2406 l_header_error4 t_error_flag;
2407 l_header_error5 t_error_flag;
2408 l_line_error1 t_error_flag;
2409 l_line_error2 t_error_flag;
2410 l_line_error3 t_error_flag;
2411 l_line_error4 t_error_flag;
2412 l_line_error5 t_error_flag;
2413 l_line_error6 t_error_flag;
2414 l_line_error7 t_error_flag;
2415 l_line_error8 t_error_flag;
2416 l_line_error9 t_error_flag;
2417 l_line_error10 t_error_flag;
2418 l_log_module VARCHAR2(240);
2419 l_rowcount number(15) := 0;
2420
2421 BEGIN
2422
2423 g_application_id := p_application_id;
2424
2425 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2426 trace
2427 (p_msg => 'BEGIN of procedure Validate_Header_Line_Entries'
2428 ,p_level => C_LEVEL_PROCEDURE
2429 ,p_module =>l_log_module);
2430 END IF;
2431
2432 IF g_log_enabled THEN
2433 l_log_module := C_DEFAULT_MODULE||'.Validate_Header_Line_Entries';
2434 END IF;
2435
2436 -- Deleting all xla_upg_errors from previous run
2437
2438 delete from xla_upg_errors
2439 where application_id = p_application_id
2440 and error_message_name IN ('XLA_UPG_LEDGER_INVALID'
2441 ,'XLA_UPG_NO_BUDGET_VER'
2442 ,'XLA_UPG_NO_ENC_TYPE'
2443 ,'XLA_UPG_BALTYP_INVALID'
2444 ,'XLA_UPG_HDR_WO_EVT'
2445 ,'XLA_UPG_UNBAL_ACCAMT'
2446 ,'XLA_UPG_UNBAL_ENTRAMT'
2447 ,'XLA_UPG_HDR_WO_LINES'
2448 , 'XLA_UPG_CCID_INVALID'
2449 ,'XLA_UPG_CCID_SUMACCT'
2450 ,'XLA_UPG_CCID_NOBUDGET'
2451 ,'XLA_UPG_PARTY_TYP_INVALID'
2452 ,'XLA_UPG_DRCR_NULL'
2453 ,'XLA_UPG_ENTAMT_DIFF_ACCAMT'
2454 ,'XLA_UPG_LINE_NO_HDR'
2455 ,'XLA_UPG_ENTAMT_ACCAMT_DIFFSIDE'
2456 ,'XLA_UPG_PARTY_ID_INVALID'
2457 ,'XLA_UPG_PARTY_SITE_INVALID'
2458 ,'XLA_LINE_VERIFICATION_RECORD'
2459 ,'XLA_HDR_VERIFICATION_RECORD');
2460
2461 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
2462 (upg_error_id, application_id, upg_source_application_id, creation_date
2463 , created_by, last_update_date, last_updated_by, upg_batch_id
2464 , error_level, error_message_name, ae_header_id)
2465 (select
2466 xla_upg_errors_s.nextval
2467 ,g_application_id
2468 ,-9999
2469 ,sysdate
2470 ,-1
2471 ,sysdate
2472 ,-1
2473 ,-9999
2474 , 'H'
2475 ,decode(grm.multiplier,1,'XLA_UPG_LEDGER_INVALID'
2476 ,2,'XLA_UPG_NO_BUDGET_VER'
2477 ,3,'XLA_UPG_NO_ENC_TYPE'
2478 ,4,'XLA_UPG_BALTYP_INVALID'
2479 ,'XLA_UPG_HDR_WO_EVT')
2480 ,ae_header_id
2481 from ( select ae_header_id
2482 ,CASE when gll.ledger_id IS NULL THEN 'Y'
2483 ELSE 'N' END header_error1-- Ledger Id is Invalid
2484 ,CASE when xah.BALANCE_TYPE_CODE = 'B'
2485 and xah.BUDGET_VERSION_ID IS NULL THEN 'Y'
2486 ELSE 'N' END header_error2-- No Budget Version
2487 ,CASE when xah.BALANCE_TYPE_CODE = 'E'
2488 and xah.ENCUMBRANCE_TYPE_ID IS NULL THEN 'Y'
2489 ELSE 'N' END header_error3-- No Enc Type
2490 ,CASE when xah.BALANCE_TYPE_CODE NOT IN ('A','B','E')
2491 THEN 'Y'
2492 ELSE 'N' END header_error4-- Balance type code invalid
2493 ,CASE when xe.event_id IS NULL THEN 'Y'
2494 ELSE 'N' END header_error5-- Header without valid event
2495 from xla_ae_headers xah
2496 ,gl_ledgers gll
2497 ,xla_events xe
2498 where gll.ledger_id (+) = xah.ledger_id
2499 and xe.event_id (+) = xah.event_id
2500 and (gll.ledger_id IS NULL OR
2501 (xah.BALANCE_TYPE_CODE = 'B' AND
2502 xah.BUDGET_VERSION_ID IS NULL) OR
2503 (xah.BALANCE_TYPE_CODE = 'E' AND
2504 xah.ENCUMBRANCE_TYPE_ID IS NULL) OR
2505 xah.BALANCE_TYPE_CODE NOT IN ('A','B','E') OR
2506 xe.event_id IS NULL)
2507 and xah.application_id = p_application_id
2508 and xah.ae_header_id = p_header_id) xah
2509 ,gl_row_multipliers grm
2510 where grm.multiplier < 6
2511 and decode(grm.multiplier,
2512 1,header_error1,
2513 2,header_error2,
2514 3,header_error3,
2515 4,header_error4,
2516 header_error5) = 'Y');
2517 COMMIT;
2518
2519 l_rowcount := l_rowcount + sql%rowcount;
2520
2521 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
2522 (upg_error_id, application_id, upg_source_application_id, creation_date
2523 , created_by, last_update_date, last_updated_by, upg_batch_id
2524 , error_level, error_message_name, ae_header_id)
2525 (select
2526 xla_upg_errors_s.nextval
2527 ,g_application_id
2528 ,-9999
2529 ,sysdate
2530 ,-1
2531 ,sysdate
2532 ,-1
2533 ,-9999
2534 , 'H'
2535 ,decode(grm.multiplier,1,'XLA_UPG_UNBAL_ACCAMT'
2536 ,'XLA_UPG_UNBAL_ENTRAMT')
2537 ,ae_header_id
2538 from (select /*+ no_merge */ xal.ae_header_id,
2539 case when nvl(sum(accounted_dr), 0) <> nvl(sum(accounted_cr), 0)
2540 then 'Y' else 'N' end header_error1, -- amts not balanced,
2541 case when nvl(sum(entered_dr), 0) <> nvl(sum(entered_cr), 0)
2542 then 'Y' else 'N' end header_error2 -- entered amts not balanced
2543 from xla_ae_lines xal
2544 where xal.application_id = p_application_id
2545 and xal.ae_header_id = p_header_id
2546 and xal.currency_code <> 'STAT'
2547 and xal.ledger_id in (select gll.ledger_id
2548 from gl_ledgers gll
2549 where gll.suspense_allowed_flag = 'N')
2550 group by xal.ae_header_id
2551 having nvl(sum(accounted_dr), 0)
2552 <> nvl(sum(accounted_cr), 0)
2553 or nvl(sum(entered_dr), 0)
2554 <> nvl(sum(entered_cr), 0)) xal,
2555 gl_row_multipliers grm
2556 where xal.ae_header_id in ( select /*+ use_hash(xah) swap_join_inputs(xah) */
2557 xah.ae_header_id
2558 from xla_ae_headers xah
2559 where xah.application_id = p_application_id
2560 and xah.ae_header_id = p_header_id
2561 and xah.balance_type_code <> 'B')
2562 and grm.multiplier < 3
2563 and decode(grm.multiplier, 1, header_error1, header_error2) = 'Y');
2564
2565 COMMIT;
2566
2567 l_rowcount := l_rowcount + sql%rowcount;
2568
2569 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
2570 (upg_error_id, application_id, upg_source_application_id,creation_date
2571 , created_by, last_update_date, last_updated_by, upg_batch_id
2572 , error_level, ae_header_id, error_message_name)
2573 (select xla_upg_errors_s.nextval
2574 ,g_application_id
2575 ,-9999
2576 ,sysdate
2577 ,-1
2578 ,sysdate
2579 ,-1
2580 ,-9999
2581 , 'H'
2582 ,ae_header_id
2583 ,'XLA_UPG_HDR_WO_LINES'
2584 from (select xah.ae_header_id
2585 from xla_ae_headers xah
2586 where NOT EXISTS (SELECT xal.ae_header_id
2587 from xla_ae_lines xal
2588 where xah.ae_header_id = xal.ae_header_id
2589 and xah.application_id = xal.application_id
2590 and xal.application_id = p_application_id
2591 and xal.ae_header_id = p_header_id)
2592 and application_id = p_application_id
2593 and ae_header_id = p_header_id));
2594 COMMIT;
2595
2596 l_rowcount := l_rowcount + sql%rowcount;
2597
2598 If l_rowcount > 0 THEN
2599 UPDATE xla_ae_headers
2600 set upg_valid_flag = CASE upg_valid_flag
2601 WHEN 'F' THEN 'L'
2602 WHEN 'J' THEN 'M'
2603 WHEN 'I' THEN 'N'
2604 ELSE 'K'
2605 END
2606 where ae_header_id = p_header_id;
2607 end if;
2608
2609 l_rowcount := sql%rowcount;
2610
2611 INSERT INTO XLA_UPG_ERRORS
2612 (upg_error_id, application_id, upg_source_application_id, creation_date
2613 , created_by, last_update_date, last_updated_by, upg_batch_id
2614 , error_level, error_message_name,entity_id)
2615 values(
2616 xla_upg_errors_s.nextval
2617 ,g_application_id
2618 ,-9999
2619 ,sysdate
2620 ,-1
2621 ,sysdate
2622 ,-1
2623 ,-9999
2624 , 'V'
2625 ,'XLA_HDR_VERIFICATION_RECORD'
2626 ,l_rowcount);
2627
2628 COMMIT;
2629
2630 l_rowcount := 0;
2631
2632 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
2633 (upg_error_id, application_id, upg_source_application_id, creation_date
2634 , created_by, last_update_date, last_updated_by, upg_batch_id
2635 , error_level, ae_header_id, ae_line_num,error_message_name)
2636 (select
2637 xla_upg_errors_s.nextval
2638 ,g_application_id
2639 ,-9999
2640 ,sysdate
2641 ,-1
2642 ,sysdate
2643 ,-1
2644 ,-9999
2645 , 'L'
2646 ,ae_header_id
2647 ,ae_line_num
2648 ,decode(grm.multiplier,1,'XLA_UPG_CCID_INVALID'
2649 ,2,'XLA_UPG_CCID_SUMACCT'
2650 ,3,'XLA_UPG_CCID_NOBUDGET'
2651 ,4,'XLA_UPG_PARTY_TYP_INVALID'
2652 ,5,'XLA_UPG_DRCR_NULL'
2653 ,6,'XLA_UPG_ENTAMT_DIFF_ACCAMT'
2654 ,7,'XLA_UPG_LINE_NO_HDR'
2655 ,8,'XLA_UPG_ENTAMT_ACCAMT_DIFFSIDE'
2656 ,9,'XLA_UPG_PARTY_ID_INVALID'
2657 ,'XLA_UPG_PARTY_SITE_INVALID')
2658 from ( select xal.ae_header_id
2659 , ae_line_num
2660 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NULL THEN 'Y'
2661 ELSE 'N' END line_error1-- Invalid Code Combination Id
2662 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL
2663 and glcc.SUMMARY_FLAG = 'Y' THEN 'Y'
2664 ELSE 'N' END line_error2-- CCID not a Summary Account
2665 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL
2666 and xah.APPLICATION_ID IS NOT NULL
2667 and xah.BALANCE_TYPE_CODE = 'B'
2668 and glcc.DETAIL_BUDGETING_ALLOWED_FLAG <> 'Y' THEN 'Y'
2669 ELSE 'N' END line_error3-- Budgeting not allowed
2670 , CASE when xal.PARTY_TYPE_CODE IS NOT NULL
2671 and xal.PARTY_TYPE_CODE NOT IN ('C','S') THEN 'Y'
2672 ELSE 'N' END line_error4-- Invalid Party Type Code
2673 , CASE when (xal.accounted_dr is NULL AND xal.accounted_cr is NULL)
2674 or (xal.entered_dr is NULL AND xal.entered_cr is NULL)
2675 or (xal.accounted_dr is NOT NULL
2676 AND xal.accounted_cr is NOT NULL)
2677 or (xal.entered_dr is NOT NULL
2678 AND xal.entered_cr is NOT NULL)
2679 THEN 'Y'
2680 ELSE 'N' END line_error5
2681 , CASE when gll.currency_code IS NOT NULL
2682 and xal.currency_code = gll.currency_code
2683 and (nvl(xal.entered_dr,0) <> nvl(xal.accounted_dr,0)
2684 or nvl(xal.entered_cr,0) <> nvl(xal.accounted_cr,0))
2685 THEN 'Y'
2686 ELSE 'N' END line_error6
2687 , CASE when xah.application_id IS NULL THEN 'Y'
2688 ELSE 'N' END line_error7-- Orphan Line.
2689 , CASE when (xal.accounted_dr is NOT NULL and
2690 xal.entered_cr is NOT NULL) or
2691 (xal.accounted_cr is NOT NULL and
2692 xal.entered_dr is NOT NULL) THEN 'Y'
2693 ELSE 'N' END line_error8
2694 ,CASE when xal.party_id IS NULL THEN 'Y'
2695 ELSE 'N' END line_error9
2696 , CASE when xal.party_site_id IS NULL
2697 and xal.party_id IS NULL then 'Y'
2698 ELSE 'N' END line_error10
2699 FROM xla_ae_headers xah
2700 , xla_ae_lines xal
2701 , gl_code_combinations glcc
2702 , gl_ledgers gll
2703 , hz_parties hz
2704 , hz_party_sites hps
2705 WHERE glcc.code_combination_id(+) = xal.code_combination_id
2706 AND xah.ae_header_id = xal.ae_header_id
2707 AND gll.ledger_id(+) = xah.ledger_id
2708 AND xal.party_id(+) = hz.party_id
2709 AND xal.party_site_id = hps.party_site_id
2710 AND (glcc.CHART_OF_ACCOUNTS_ID IS NULL OR
2711 (glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL AND
2712 glcc.SUMMARY_FLAG = 'Y' ) OR
2713 (glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL AND
2714 xah.APPLICATION_ID IS NOT NULL AND
2715 xah.BALANCE_TYPE_CODE = 'B' AND
2716 glcc.DETAIL_BUDGETING_ALLOWED_FLAG <> 'Y') OR
2717 (xal.PARTY_TYPE_CODE IS NOT NULL AND
2718 xal.PARTY_TYPE_CODE NOT IN ('C','S') ) OR
2719 (xal.accounted_dr is NULL AND xal.accounted_cr is NULL) OR
2720 (xal.entered_dr is NULL AND xal.entered_cr is NULL) OR
2721 (xal.accounted_dr is NOT NULL AND xal.accounted_cr is NOT NULL) OR
2722 (xal.entered_dr is NOT NULL AND xal.entered_cr is NOT NULL) OR
2723 (gll.currency_code IS NOT NULL AND
2724 xal.currency_code = gll.currency_code AND
2725 (nvl(xal.entered_dr,0) <> nvl(xal.accounted_dr,0) OR
2726 nvl(xal.entered_cr,0) <> nvl(xal.accounted_cr,0))) OR
2727 ((xal.accounted_dr is NOT NULL and xal.entered_cr is NOT NULL) OR
2728 (xal.accounted_cr is NOT NULL and xal.entered_dr is NOT NULL)) OR
2729 (xah.application_id IS NULL))
2730 and xal.application_id = p_application_id
2731 and xal.ae_header_id = p_header_id) xal
2732 ,gl_row_multipliers grm
2733 where grm.multiplier < 11
2734 and decode (grm.multiplier,1,line_error1
2735 ,2,line_error2
2736 ,3,line_error3
2737 ,4,line_error4
2738 ,5,line_error5
2739 ,6,line_error6
2740 ,7,line_error7
2741 ,8,line_error8
2742 ,9,line_error9
2743 ,line_error10) = 'Y');
2744
2745 COMMIT;
2746
2747 l_rowcount := l_rowcount + sql%rowcount;
2748
2749 If l_rowcount > 0 THEN
2750 UPDATE xla_ae_headers
2751 set upg_valid_flag = CASE upg_valid_flag
2752 WHEN 'F' THEN 'P'
2753 WHEN 'J' THEN 'Q'
2754 WHEN 'I' THEN 'R'
2755 WHEN 'L' THEN 'S'
2756 WHEN 'M' THEN 'T'
2757 WHEN 'N' THEN 'U'
2758 ELSE 'O'
2759 END
2760 where ae_header_id = p_header_id
2761 and application_id = p_application_id;
2762
2763 end if;
2764
2765
2766 -- finding out how many rows got updated.
2767
2768 l_rowcount := sql%rowcount;
2769
2770 INSERT INTO XLA_UPG_ERRORS
2771 (upg_error_id, application_id, upg_source_application_id, creation_date
2772 , created_by, last_update_date, last_updated_by, upg_batch_id
2773 , error_level, error_message_name,entity_id)
2774 values(
2775 xla_upg_errors_s.nextval
2776 ,g_application_id
2777 ,-9999
2778 ,sysdate
2779 ,-1
2780 ,sysdate
2781 ,-1
2782 ,-9999
2783 , 'V'
2784 ,'XLA_LINE_VERIFICATION_RECORD'
2785 ,l_rowcount);
2786
2787 COMMIT;
2788
2789 EXCEPTION
2790 WHEN xla_exceptions_pkg.application_exception THEN
2791 RAISE;
2792 WHEN OTHERS THEN
2793 xla_exceptions_pkg.raise_message
2794 (p_location => 'XLA_UPGRADE_PUB.Validate_Header_Line_Entries');
2795
2796 END Validate_Header_Line_Entries;
2797
2798 /*============================================================================+
2799 | |
2800 | Public Procedure |
2801 | |
2802 | Pre_Upgrade_Set_Status_Code |
2803 | |
2804 | This procedure is called during the Pre Upgrade phase, to update the |
2805 | status code. |
2806 +============================================================================*/
2807
2808 PROCEDURE pre_upgrade_set_status_code
2809 (p_error_buf OUT NOCOPY VARCHAR2,
2810 p_retcode OUT NOCOPY NUMBER,
2811 p_migrate_all_ledgers IN VARCHAR2,
2812 p_dummy_parameter IN VARCHAR2,
2813 p_ledger_id IN NUMBER DEFAULT NULL,
2814 p_start_date IN VARCHAR2
2815 ) IS
2816
2817 CURSOR CUR_ALL_LEDGERS IS SELECT DISTINCT ledger_id
2818 FROM gl_period_statuses;
2819
2820 l_migrate_all_ledgers VARCHAR2(30);
2821 l_ledger_id NUMBER;
2822 l_start_date date;
2823 l_error_buf VARCHAR2(1000);
2824 l_retcode NUMBER;
2825 l_end_date date;
2826 l_log_module VARCHAR2(240);
2827
2828 l_all_ledgers CUR_ALL_LEDGERS%ROWTYPE;
2829
2830 BEGIN
2831
2832 IF g_log_enabled THEN
2833 l_log_module := C_DEFAULT_MODULE||'.pre_upgrade_set_status_code';
2834 END IF;
2835
2836 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2837 trace('pre_upgrade_set_status_code.Begin',C_LEVEL_STATEMENT,l_log_module);
2838 END IF;
2839
2840 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2841 trace('Date '||p_start_date,C_LEVEL_STATEMENT,l_log_module);
2842 END IF;
2843
2844 l_migrate_all_ledgers := p_migrate_all_ledgers;
2845 l_start_date := fnd_date.canonical_to_date(p_start_date);
2846 l_ledger_id := p_ledger_id;
2847
2848 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2849 trace('l_migrate_all_ledgers '|| l_migrate_all_ledgers,
2850 C_LEVEL_STATEMENT, l_Log_module);
2851 END IF;
2852
2853 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2854 trace('l_ledger_id '||l_ledger_id,
2855 C_LEVEL_STATEMENT, l_Log_module);
2856 END IF;
2857
2858 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2859 trace('l_start_date '||l_start_date,
2860 C_LEVEL_STATEMENT, l_Log_module);
2861 END IF;
2862
2863 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2864 trace('Processing periods...',
2865 C_LEVEL_STATEMENT, l_Log_module);
2866 END IF;
2867
2868 IF l_migrate_all_ledgers = 'N' THEN
2869
2870 SELECT min(start_date) - 1
2871 INTO l_end_date
2872 FROM gl_period_statuses
2873 WHERE migration_status_code = 'P'
2874 AND ledger_id = l_ledger_id
2875 AND application_id in (200,222,275,201,401,101,8721);
2876
2877 IF l_end_date is NULL THEN
2878
2879 SELECT max(end_date)
2880 INTO l_end_date
2881 FROM gl_period_statuses
2882 WHERE ledger_id = l_ledger_id
2883 AND application_id IN (200,222,275,201,401,101,8721);
2884 END IF;
2885
2886 UPDATE gl_period_statuses
2887 SET migration_status_code = 'P'
2888 WHERE ledger_id = l_ledger_id
2889 AND (start_date >= l_start_date
2890 and end_date <= l_end_date)
2891 AND application_id in (200,222,275,201,401,101,8721)
2892 AND adjustment_period_flag = 'N'
2893 AND migration_status_code IS NULL;
2894
2895 ELSE
2896
2897 OPEN CUR_ALL_LEDGERS;
2898 LOOP
2899 FETCH CUR_ALL_LEDGERS INTO l_all_ledgers;
2900 EXIT when CUR_ALL_LEDGERS%notfound;
2901
2902 SELECT min(start_date) - 1
2903 INTO l_end_date
2904 FROM gl_period_statuses
2905 WHERE migration_status_code = 'P'
2906 AND ledger_id = l_all_ledgers.ledger_id
2907 AND application_id in (200,222,275,201,401,101,8721);
2908
2909 IF l_end_date is NULL THEN
2910
2911 SELECT max(end_date)
2912 INTO l_end_date
2913 FROM gl_period_statuses
2914 WHERE ledger_id = l_all_ledgers.ledger_id
2915 AND application_id in (200,222,275,201,401,101,8721);
2916
2917 END IF;
2918
2919 -- Updation of GL Period Statuses.
2920
2921 UPDATE gl_period_statuses
2922 SET migration_status_code = 'P'
2923 WHERE ledger_id = l_all_ledgers.ledger_id
2924 AND (start_date >= l_start_date
2925 and end_date <= l_end_date)
2926 AND application_id in (200,222,275,201,401,101,8721)
2927 AND adjustment_period_flag = 'N'
2928 AND migration_status_code IS NULL;
2929
2930
2931 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2932 trace('Updated gl_period_statuses.'
2933 , C_LEVEL_STATEMENT, l_Log_module);
2934 END IF;
2935
2936 END LOOP;
2937 CLOSE CUR_ALL_LEDGERS;
2938
2939 END IF;
2940
2941 EXCEPTION
2942
2943 WHEN xla_exceptions_pkg.application_exception THEN
2944 RAISE;
2945
2946 WHEN OTHERS THEN
2947 xla_exceptions_pkg.raise_message
2948 (p_location => 'XLA_UPGRADE_PUB.pre_upgrade_set_status_code');
2949
2950 END PRE_UPGRADE_SET_STATUS_CODE;
2951
2952
2953 BEGIN
2954 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2955 g_log_enabled := fnd_log.test
2956 (log_level => g_log_level
2957 ,MODULE => C_DEFAULT_MODULE);
2958
2959 IF NOT g_log_enabled THEN
2960 g_log_level := C_LEVEL_LOG_DISABLED;
2961 END IF;
2962
2963 END XLA_UPGRADE_PUB;