[Home] [Help]
PACKAGE BODY: APPS.XLA_UPG_VERIFICATION_PUB
Source
1 PACKAGE BODY XLA_UPG_VERIFICATION_PUB AS
2 -- $Header: xlaugval.pkb 120.1 2006/03/29 16:44:20 ksvenkat noship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | FILENAME |
9 | xlaugval.pkb |
10 | |
11 | PACKAGE NAME |
12 | XLA_UPG_VERIFICATION_PUB |
13 | |
14 | DESCRIPTION |
15 | This is a XLA package which contains verification scripts to |
16 | check the AX-SLA Upgrade. |
17 | |
18 | HISTORY |
19 | 27-Mar-06 Koushik VS Created |
20 | |
21 +===========================================================================*/
22 --=============================================================================
23 -- **************** declarations ********************
24 --=============================================================================
25
26
27 -------------------------------------------------------------------------------
28 -- declaring global variables
29 -------------------------------------------------------------------------------
30
31 g_batch_id INTEGER ;
32 g_batch_size INTEGER := 30000;
33 g_source_application_id NUMBER ;
34 g_application_id NUMBER;
35 g_validate_complete xla_upg_batches.VALIDATE_COMPLETE_FLAG%TYPE;
36 g_crsegvals_complete xla_upg_batches.CRSEGVALS_COMPLETE_FLAG%TYPE;
37 -------------------------------------------------------------------------------
38 -- declaring global pl/sql types
39 -------------------------------------------------------------------------------
40
41 TYPE t_entity_id IS TABLE OF
42 xla_transaction_entities.entity_id%type
43 INDEX BY BINARY_INTEGER;
44 TYPE t_error_flag IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
45 TYPE t_event_id IS TABLE OF
46 xla_events.event_id%type
47 INDEX BY BINARY_INTEGER;
48 TYPE t_header_id IS TABLE OF
49 xla_ae_headers.ae_header_id%type
50 INDEX BY BINARY_INTEGER;
51 TYPE t_line_num IS TABLE OF
52 xla_ae_lines.ae_line_num%type
53 INDEX BY BINARY_INTEGER;
54 TYPE t_seg_value IS TABLE OF
55 xla_ae_segment_values.segment_value%type
56 INDEX BY BINARY_INTEGER;
57 TYPE t_line_count IS TABLE OF
58 xla_ae_segment_values.ae_lines_count%type
59 INDEX BY BINARY_INTEGER;
60 TYPE t_seg_type IS TABLE OF
61 xla_ae_segment_values.segment_type_code%type
62 INDEX BY BINARY_INTEGER;
63 TYPE t_error_id IS TABLE OF
64 xla_upg_errors.upg_error_id%type
65 INDEX BY BINARY_INTEGER;
66 -------------------------------------------------------------------------------
67 -- declaring global constants
68 -------------------------------------------------------------------------------
69 -- The segment type code
70 C_BAL_SEGMENT CONSTANT VARCHAR2(1) := 'B';
71 C_MGT_SEGMENT CONSTANT VARCHAR2(1) := 'M';
72 --=============================================================================
73 -- *********** Local Trace Routine **********
74 --=============================================================================
75 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
76 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
77 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
78 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
79 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
80 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
81
82 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
83
84 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.XLA_UPG_VERIFICATION_PUB';
85
86 g_log_level NUMBER;
87 g_log_enabled BOOLEAN;
88
89
90 -------------------------------------------------------------------------------
91 -- forward declarion of private procedures and functions
92 -------------------------------------------------------------------------------
93 --=============================================================================
94 -- *********** Local Trace Routine **********
95 --=============================================================================
96
97 PROCEDURE trace
98 (p_msg IN VARCHAR2
99 ,p_level IN NUMBER
100 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
101 BEGIN
102 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
103 fnd_log.message(p_level, p_module);
104 ELSIF p_level >= g_log_level THEN
105 fnd_log.string(p_level, p_module, p_msg);
106 END IF;
107
108 EXCEPTION
109 WHEN xla_exceptions_pkg.application_exception THEN
110 RAISE;
111 WHEN OTHERS THEN
112 xla_exceptions_pkg.raise_message
113 (p_location => 'XLA_UPG_VERIFICATION_PUB.trace');
114 END trace;
115 --=============================================================================
116 -- *********** public procedures and functions **********
117 --=============================================================================
118 --=============================================================================
119 /*============================================================================+
120 | |
121 | Public Procedure |
122 | |
123 | Validate_Entries |
124 | |
125 | This routine is called to validate the upgrade. |
126 | |
127 +============================================================================*/
128
129 PROCEDURE Validate_Entries (
130 p_upgrading_application_id IN NUMBER,
131 p_application_id IN NUMBER) IS
132
133 l_log_module VARCHAR2(240);
134 BEGIN
135
136
137 g_application_id := p_application_id;
138 g_source_application_id := p_upgrading_application_id;
139
140 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
141 trace
142 (p_msg => 'BEGIN of procedure Validate_Entries'
143 ,p_level => C_LEVEL_PROCEDURE
144 ,p_module =>l_log_module);
145 END IF;
146
147 IF g_log_enabled THEN
148 l_log_module := C_DEFAULT_MODULE||'.Validate_Entries';
149 END IF;
150
151 -- Call to find entities that are not associated with
152 -- any application.
153 -- and stamp the invalids
154
155 Validate_Application_Entries;
156
157 -- Call to check if Entities are valid
158 -- and stamp the invalids
159
160 Validate_Entity_Entries(p_upgrading_application_id
161 ,p_application_id);
162
163 -- Call to check if Events are valid
164 -- and stamp the invalids
165
166 Validate_Event_Entries(p_upgrading_application_id
167 ,p_application_id);
168
169 -- Call to check if Headers are valid
170 -- and stamp the invalids
171
172 Validate_Header_Entries(p_upgrading_application_id
173 ,p_application_id);
174
175 -- Call to check if Lines are valid
176 -- and stamp the invalids
177
178 Validate_Line_Entries(p_upgrading_application_id
179 ,p_application_id);
180
181 -- Call to check if distribution links are valid
182 -- and stamp the invalids
183
184 Validate_Distribution_Entries(p_application_id);
185
186 Populate_Segment_Values (p_application_id => g_application_id);
187
188 COMMIT;
189
190 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
191 trace
192 (p_msg => 'END of procedure Validate_Entries'
193 ,p_level => C_LEVEL_PROCEDURE
194 ,p_module => l_log_module);
195 END IF;
196
197 EXCEPTION
198 WHEN xla_exceptions_pkg.application_exception THEN
199 RAISE;
200 WHEN OTHERS THEN
201 xla_exceptions_pkg.raise_message
202 (p_location => 'XLA_UPG_VERIFICATION_PUB.Validate_Entries');
203
204 END Validate_Entries;
205
206 /*============================================================================+
207 | |
208 | Public Procedure |
209 | |
210 | Validate_Application_Entries |
211 | |
212 | This routine is called to find out all those entities that are attached |
213 | to an application that is not registered with SLA. |
214 | |
215 +============================================================================*/
216 PROCEDURE Validate_Application_Entries IS
217
218 l_entity_id t_entity_id;
219 l_event_id t_event_id;
220 l_header_id t_header_id;
221 l_line_num t_line_num;
222 l_log_module VARCHAR2(240);
223 l_rowcount number(15) := 0;
224
225 CURSOR csr_application_exists IS
226 select distinct entity_id
227 from xla_upg_errors
228 where error_level = 'A'
229 and application_id = 602
230 and upg_source_application_id = 602;
231
232 BEGIN
233
234 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
235 trace
236 (p_msg => 'BEGIN of procedure Validate_Application_Entries'
237 ,p_level => C_LEVEL_PROCEDURE
238 ,p_module =>l_log_module);
239 END IF;
240
241 IF g_log_enabled THEN
242 l_log_module := C_DEFAULT_MODULE||'.Validate_Application_Entries';
243 END IF;
244
245 -- Deleting all xla_upg_errors from previous run
246
247 delete from xla_upg_errors
248 where application_id = 602
249 and upg_source_application_id = 602
250 and error_message_name IN ('XLA_UPG_APP_NOT_DEFINED'
251 ,'XLA_APP_VERIFICATION_RECORD');
252
253
254 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
255 (upg_error_id, application_id, upg_source_application_id, creation_date
256 , created_by, last_update_date, last_updated_by, upg_batch_id
257 , error_level, error_message_name,entity_id)
258 (SELECT
259 xla_upg_errors_s.nextval
260 ,602
261 ,602
262 ,sysdate
263 ,-1
264 ,sysdate
265 ,-1
266 ,-9999
267 , 'A'
268 ,'XLA_UPG_APP_NOT_DEFINED'
269 ,entity_id from(select entity_id
270 from xla_transaction_entities_upg xen
271 where NOT EXISTS (SELECT 1
272 FROM XLA_SUBLEDGERS XS
273 WHERE xen.application_id
274 = xs.application_id)));
275 COMMIT;
276
277 OPEN csr_application_exists;
278 LOOP
279 FETCH csr_application_exists
280 BULK COLLECT INTO
281 l_entity_id
282 LIMIT g_batch_size;
283 EXIT WHEN l_entity_id.count = 0;
284
285 FORALL i IN l_entity_id.FIRST..l_entity_id.LAST
286 UPDATE xla_transaction_entities_upg
287 set upg_valid_flag = 'A'
288 where entity_id = l_entity_id(i);
289
290 -- finding out how many rows got inserted/updated.
291
292 l_rowcount := l_rowcount + sql%rowcount;
293
294 -- Updating as invalids all events that are associated with invalid entity.
295
296 FORALL i IN l_entity_id.FIRST..l_entity_id.LAST
297 UPDATE xla_events
298 set upg_valid_flag = 'B'
299 where entity_id = l_entity_id(i);
300
301 -- Cumilitive number of rows updated.
302
303 l_rowcount := l_rowcount + sql%rowcount;
304
305 -- Updating as invalids all headers that are associated with
306 -- events that are associated with invalid entities.
307
308 FORALL i IN l_entity_id.FIRST..l_entity_id.LAST
309 UPDATE xla_ae_headers
310 set upg_valid_flag = 'C'
311 where entity_id = l_entity_id(i);
312
313 -- Cumilitive number of rows updated.
314
315 l_rowcount := l_rowcount + sql%rowcount;
316
317 COMMIT;
318 END LOOP;
319 CLOSE csr_application_exists;
320
321 INSERT INTO XLA_UPG_ERRORS
322 (upg_error_id, application_id, upg_source_application_id, creation_date
323 , created_by, last_update_date, last_updated_by, upg_batch_id
324 , error_level, error_message_name,entity_id)
325 values(
326 xla_upg_errors_s.nextval
327 ,602
328 ,602
329 ,sysdate
330 ,-1
331 ,sysdate
332 ,-1
333 ,-9999
334 , 'V'
335 ,'XLA_APP_VERIFICATION_RECORD'
336 ,l_rowcount);
337
338 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
339 trace
340 (p_msg => 'END of procedure Validate_Application_Entries'
341 ,p_level => C_LEVEL_PROCEDURE
342 ,p_module => l_log_module);
343 END IF;
344
345 COMMIT;
346
347 EXCEPTION
348 WHEN xla_exceptions_pkg.application_exception THEN
349 RAISE;
350 WHEN OTHERS THEN
351 xla_exceptions_pkg.raise_message
355
352 (p_location => 'XLA_UPG_VERIFICATION_PUB.Validate_Application_Entries');
353
354 END Validate_Application_Entries;
356 /*============================================================================+
357 | |
358 | Public Procedure |
359 | |
360 | Validate_Entity_Entries |
361 | |
362 | This routine is called to validate the entity entries in upgrade. |
363 | |
364 +============================================================================*/
365 PROCEDURE Validate_Entity_Entries (
366 p_upgrading_application_id IN NUMBER,
367 p_application_id IN NUMBER) IS
368
369 l_entity_id t_entity_id;
370 l_event_id t_event_id;
371 l_header_id t_header_id;
372 l_line_num t_line_num;
373 l_entity_error1 t_error_flag;
374 l_log_module VARCHAR2(240);
375 l_rowcount number(15) := 0;
376
377 cursor csr_entity_errors is
378 select distinct entity_id
379 from xla_upg_errors
380 where error_level = 'N'
381 and application_id = p_application_id
382 and upg_source_application_id = p_upgrading_application_id;
383
384 BEGIN
385
386 g_application_id := p_application_id;
387 g_source_application_id := p_upgrading_application_id;
388
389 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
390 trace
391 (p_msg => 'BEGIN of procedure Validate_Entity_Entries'
392 ,p_level => C_LEVEL_PROCEDURE
393 ,p_module =>l_log_module);
394 END IF;
395
396 IF g_log_enabled THEN
397 l_log_module := C_DEFAULT_MODULE||'.Validate_Entity_Entries';
398 END IF;
399
400 -- Deleting all xla_upg_errors from previous run
401
402 delete from xla_upg_errors
403 where application_id = p_application_id
404 and upg_source_application_id = p_upgrading_application_id
405 and error_message_name in ('XLA_UPG_ENCODE_INVALID'
406 ,'XLA_ENT_VERIFICATION_RECORD');
407
408 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
409 (upg_error_id, application_id, upg_source_application_id,creation_date
410 , created_by, last_update_date, last_updated_by, upg_batch_id
411 , error_level, error_message_name,entity_id)
412 (select xla_upg_errors_s.nextval
413 ,g_application_id
414 ,g_source_application_id
415 ,sysdate
416 ,-1
417 ,sysdate
418 ,-1
419 ,-9999
420 ,'N'
421 ,'XLA_UPG_ENCODE_INVALID'
422 ,entity_id
423 from xla_transaction_entities_upg xen
424 where not exists (select 1 from xla_entity_types_b xent
425 where xen.entity_code = xent.entity_code
426 and xen.application_id = xent.application_id)
427 and xen.application_id = p_application_id
428 and xen.upg_source_application_id = p_upgrading_application_id);
429
430 -- Updating invalid entities.
431
432 COMMIT;
433
434 OPEN csr_entity_errors;
435 LOOP
436 FETCH csr_entity_errors
437 BULK COLLECT INTO
438 l_entity_id
439 LIMIT g_batch_size;
440 EXIT when l_entity_id.COUNT = 0;
441
442 FORALL i IN l_entity_id.FIRST..l_entity_id.LAST
443 UPDATE xla_transaction_entities_upg
444 set upg_valid_flag = 'D'
445 where entity_id = l_entity_id(i);
446
447 -- finding out how many rows got inserted/updated.
448
449 l_rowcount := sql%rowcount;
450
451 -- Updating as invalids all events that are associated with invalid entity.
452
453 FORALL i IN l_entity_id.FIRST..l_entity_id.LAST
454 UPDATE xla_events
455 set upg_valid_flag = 'E'
456 where entity_id = l_entity_id(i);
457
458 -- Cumilitive number of rows updated.
459
460 l_rowcount := l_rowcount + sql%rowcount;
461
462 -- Updating as invalids all headers that are associated with
463 -- events that are associated with invalid entities.
464
465 FORALL i IN l_entity_id.FIRST..l_entity_id.LAST
466 UPDATE xla_ae_headers
467 set upg_valid_flag = 'F'
468 where entity_id = l_entity_id(i);
469
470 -- Cumilitive number of rows updated.
471
472 l_rowcount := l_rowcount + sql%rowcount;
473
474 COMMIT;
475 END LOOP;
476 CLOSE csr_entity_errors;
477
478 INSERT INTO XLA_UPG_ERRORS
479 (upg_error_id, application_id, upg_source_application_id, creation_date
480 ,created_by, last_update_date, last_updated_by, upg_batch_id
481 ,error_level, error_message_name,entity_id)
482 values(
483 xla_upg_errors_s.nextval
484 ,g_application_id
485 ,g_source_application_id
486 ,sysdate
487 ,-1
488 ,sysdate
489 ,-1
490 ,-9999
491 , 'V'
492 ,'XLA_ENT_VERIFICATION_RECORD'
493 ,l_rowcount);
494
495 COMMIT;
496
497 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
498 trace
499 (p_msg => 'END of procedure Validate_Entity_Entries'
500 ,p_level => C_LEVEL_PROCEDURE
501 ,p_module => l_log_module);
502 END IF;
503
504 EXCEPTION
505 WHEN xla_exceptions_pkg.application_exception THEN
509 (p_location => 'XLA_UPG_VERIFICATION_PUB.Validate_Entity_Entries');
506 RAISE;
507 WHEN OTHERS THEN
508 xla_exceptions_pkg.raise_message
510
511 END Validate_Entity_Entries;
512
513 /*============================================================================+
514 | |
515 | Public Procedure |
516 | |
517 | Validate_Event_Entries |
518 | |
519 | This routine is called to validate the Event entries in upgrade. |
520 | |
521 +============================================================================*/
522
523 PROCEDURE Validate_Event_Entries (
524 p_upgrading_application_id IN NUMBER,
525 p_application_id IN NUMBER) IS
526
527 l_event_id t_event_id;
528 l_event_error1 t_error_flag;
529 l_event_error2 t_error_flag;
530 l_event_error3 t_error_flag;
531 l_event_error4 t_error_flag;
532 l_event_error5 t_error_flag;
533 l_event_error6 t_error_flag;
534 l_event_error7 t_error_flag;
535 l_event_error8 t_error_flag;
536 l_log_module VARCHAR2(240);
537 l_rowcount number(15) := 0;
538
539 CURSOR csr_event_errors IS
540 select distinct event_id
541 from xla_upg_errors
542 where error_level = 'E'
543 and application_id = p_application_id
544 and upg_source_application_id = p_upgrading_application_id;
545
546 BEGIN
547
548 g_application_id := p_application_id;
549 g_source_application_id := p_upgrading_application_id;
550
551 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
552 trace
553 (p_msg => 'BEGIN of procedure Validate_Event_Entries'
554 ,p_level => C_LEVEL_PROCEDURE
555 ,p_module =>l_log_module);
556 END IF;
557
558 IF g_log_enabled THEN
559 l_log_module := C_DEFAULT_MODULE||'.Validate_Event_Entries';
560 END IF;
561
562 -- Deleting all xla_upg_errors from previous run of this procedure
563
564 delete from xla_upg_errors
565 where application_id = p_application_id
566 and upg_source_application_id = p_upgrading_application_id
567 and error_message_name in ('XLA_UPG_EVT_NO_ENTITY'
568 ,'XLA_UPG_EVT_INV_ENTITY'
569 ,'XLA_UPG_EVTYP_INVALID'
570 ,'XLA_UPG_EVSTCODE_INVALID'
571 ,'XLA_UPG_PROCSTCODE_INVALID'
572 ,'XLA_UPG_EVNO_INVALID'
573 ,'XLA_UPG_EVTCODE_INVALID'
574 ,'XLA_UPG_ACC_CLASS_INVALID'
575 ,'XLA_EVT_VERIFICATION_RECORD');
576
577 -- Write Errors
578 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
579 (upg_error_id, application_id, upg_source_application_id, creation_date
580 , created_by, last_update_date, last_updated_by, upg_batch_id
581 , error_level, error_message_name, event_id)
582 (select xla_upg_errors_s.nextval
583 ,g_application_id
584 ,g_source_application_id
585 ,sysdate
586 ,-1
587 ,sysdate
588 ,-1
589 ,-9999
590 , 'E'
591 ,decode(grm.multiplier,1,'XLA_UPG_EVT_NO_ENTITY'
592 ,2,'XLA_UPG_EVT_INV_ENTITY'
593 ,3,'XLA_UPG_EVTYP_INVALID'
594 ,4,'XLA_UPG_EVSTCODE_INVALID'
595 ,5,'XLA_UPG_PROCSTCODE_INVALID'
596 ,6,'XLA_UPG_EVNO_INVALID'
597 ,7,'XLA_UPG_EVTCODE_INVALID'
598 ,'XLA_UPG_ACC_CLASS_INVALID')
599 ,event_id
600 from (select distinct event_id
601 ,CASE when xen.entity_code IS NULL THEN 'Y'
602 ELSE 'N' END event_error1-- Event exists without entity
603 ,CASE when xent.entity_code IS NULL THEN 'Y'
604 ELSE 'N' END event_error2 -- Event attached to invalid entity
605 ,CASE when xevt.event_type_code IS NULL THEN 'Y'
606 ELSE 'N' END event_error3-- Event Type is Invalid
607 ,CASE when xe.EVENT_STATUS_CODE NOT IN ('I','N','P','U') THEN 'Y'
608 ELSE 'N' END event_error4-- Invalid event status Code.
609 ,CASE when xe.PROCESS_STATUS_CODE NOT IN ('D','E','I','P','R','U')
610 THEN 'Y'
611 ELSE 'N' END event_error5-- Invalid Process status code
612 ,CASE when xe.event_number < 0 THEN 'Y'
613 ELSE 'N' END event_error6-- Invalid Event Number
614 ,CASE when xevt.event_class_code IS NULL THEN 'Y'
615 ELSE 'N' END event_error7-- Event Class Code is Invalid
616 ,CASE when xalb.accounting_class_code IS NULL THEN 'Y'
617 ELSE 'N' END event_error8
618 from xla_events xe
619 , xla_transaction_entities_upg xen
620 , xla_event_types_b xevt
621 , xla_acct_line_types_b xalb
622 , xla_entity_types_b xent
623 where xen.entity_id(+) = xe.entity_id
624 and xevt.event_type_code(+) = xe.event_type_code
625 and xevt.application_id(+) = xe.application_id
626 and xalb.application_id = xevt.application_id
627 and xalb.entity_code = xevt.entity_code
628 and xalb.event_class_code = xevt.event_class_code
629 and xen.entity_code = xent.entity_code(+)
630 and xen.application_id = xent.application_id(+)
634 xe.PROCESS_STATUS_CODE NOT IN ('D','E','I','P','R','U') OR
631 and (xen.entity_code IS NULL OR
632 xevt.event_type_code IS NULL OR
633 xe.EVENT_STATUS_CODE NOT IN ('I','N','P','U') OR
635 xe.event_number < 0)
636 and xe.application_id = p_application_id
637 and xe.upg_source_Application_id = p_upgrading_application_id) xe
638 ,gl_row_multipliers grm
639 where grm.multiplier < 9
640 and decode(grm.multiplier,
641 1,event_error1,
642 2,event_error2,
643 3,event_error3,
644 4,event_error4,
645 5,event_error5,
646 6,event_error6,
647 7,event_error7
648 ,event_error8) = 'Y');
649
650 COMMIT;
651
652 OPEN csr_event_errors;
653 LOOP
654 FETCH csr_event_errors
655 BULK COLLECT INTO
656 l_event_id
657 LIMIT g_batch_size;
658 EXIT WHEN l_event_id.COUNT = 0;
659
660 -- Mark Event as having errors
661 FORALL i IN l_event_id.FIRST..l_event_id.LAST
662 UPDATE xla_events
663 set upg_valid_flag = CASE upg_valid_flag
664 WHEN 'E' THEN 'G'
665 ELSE 'H'
666 END
667 where event_id = l_event_id(i);
668
669 -- finding out how many rows got inserted/updated.
670
671 l_rowcount := sql%rowcount;
672
673 -- Updating as invalids all headers that are associated with
674 -- events that are associated with invalid entities.
675
676 FORALL i IN l_event_id.FIRST..l_event_id.LAST
677
678 UPDATE xla_ae_headers
679 set upg_valid_flag = CASE upg_valid_flag
680 WHEN 'F' THEN 'I'
681 ELSE 'J'
682 END
683 where event_id = l_event_id(i);
684
685 -- Cumilitive number of rows updated.
686
687 l_rowcount := l_rowcount + sql%rowcount;
688
689 --debug message to ensure that this validation took place
690 --successfully.
691
692 COMMIT;
693 END LOOP;
694 CLOSE csr_event_errors;
695
696 INSERT INTO XLA_UPG_ERRORS
697 (upg_error_id, application_id, upg_source_application_id, creation_date
698 , created_by, last_update_date, last_updated_by, upg_batch_id
699 , error_level, error_message_name,entity_id)
700 values( xla_upg_errors_s.nextval
701 ,g_application_id
702 ,g_source_application_id
703 ,sysdate
704 ,-1
705 ,sysdate
706 ,-1
707 ,-9999
708 , 'V'
709 ,'XLA_EVT_VERIFICATION_RECORD'
710 ,l_rowcount);
711
712 COMMIT;
713
714 EXCEPTION
715 WHEN xla_exceptions_pkg.application_exception THEN
716 RAISE;
717 WHEN OTHERS THEN
718 xla_exceptions_pkg.raise_message
719 (p_location => 'XLA_UPG_VERIFICATION_PUB.Validate_Event_Entries');
720
721 END Validate_Event_Entries;
722
723 /*============================================================================+
724 | |
725 | Public Procedure |
726 | |
727 | Validate_Header_Entries |
728 | |
729 | This routine is called to validate the Header entries in upgrade. |
730 | |
731 +============================================================================*/
732
733 PROCEDURE Validate_Header_Entries (
734 p_upgrading_application_id IN NUMBER,
735 p_application_id IN NUMBER) IS
736
737 l_entity_id t_entity_id;
738 l_event_id t_event_id;
739 l_header_id t_header_id;
740 l_line_num t_line_num;
741 l_header_error1 t_error_flag;
742 l_header_error2 t_error_flag;
743 l_header_error3 t_error_flag;
744 l_header_error4 t_error_flag;
745 l_header_error5 t_error_flag;
746 l_log_module VARCHAR2(240);
747 l_rowcount number(15) := 0;
748
749 CURSOR csr_header_entries IS
750 select distinct ae_header_id
751 from xla_upg_errors
752 where error_level = 'H'
753 and application_id = p_application_id
754 and upg_source_application_id = p_upgrading_application_id;
755
756 BEGIN
757
758 g_application_id := p_application_id;
759 g_source_application_id := p_upgrading_application_id;
760
761 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
762 trace
763 (p_msg => 'BEGIN of procedure Validate_Header_Entries'
764 ,p_level => C_LEVEL_PROCEDURE
765 ,p_module =>l_log_module);
766 END IF;
767
768 IF g_log_enabled THEN
769 l_log_module := C_DEFAULT_MODULE||'.Validate_Header_Entries';
770 END IF;
771
772 -- Deleting all xla_upg_errors from previous run
773
774 delete from xla_upg_errors
775 where application_id = p_application_id
776 and upg_source_application_id = p_upgrading_application_id
777 and error_message_name IN ('XLA_UPG_LEDGER_INVALID'
778 ,'XLA_UPG_NO_BUDGET_VER'
779 ,'XLA_UPG_NO_ENC_TYPE'
780 ,'XLA_UPG_BALTYP_INVALID'
781 ,'XLA_UPG_HDR_WO_EVT'
782 ,'XLA_UPG_UNBAL_ACCAMT'
783 ,'XLA_UPG_UNBAL_ENTRAMT'
787 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
784 ,'XLA_UPG_HDR_WO_LINES'
785 ,'XLA_HDR_VERIFICATION_RECORD');
786
788 (upg_error_id, application_id, upg_source_application_id, creation_date
789 , created_by, last_update_date, last_updated_by, upg_batch_id
790 , error_level, error_message_name, ae_header_id)
791 (select
792 xla_upg_errors_s.nextval
793 ,g_application_id
794 ,g_source_application_id
795 ,sysdate
796 ,-1
797 ,sysdate
798 ,-1
799 ,-9999
800 , 'H'
801 ,decode(grm.multiplier,1,'XLA_UPG_LEDGER_INVALID'
802 ,2,'XLA_UPG_NO_BUDGET_VER'
803 ,3,'XLA_UPG_NO_ENC_TYPE'
804 ,4,'XLA_UPG_BALTYP_INVALID'
805 ,'XLA_UPG_HDR_WO_EVT')
806 ,ae_header_id
807 from ( select ae_header_id
808 ,CASE when gll.ledger_id IS NULL THEN 'Y'
809 ELSE 'N' END header_error1-- Ledger Id is Invalid
810 ,CASE when xah.BALANCE_TYPE_CODE = 'B'
811 and xah.BUDGET_VERSION_ID IS NULL THEN 'Y'
812 ELSE 'N' END header_error2-- No Budget Version
813 ,CASE when xah.BALANCE_TYPE_CODE = 'E'
814 and xah.ENCUMBRANCE_TYPE_ID IS NULL THEN 'Y'
815 ELSE 'N' END header_error3-- No Enc Type
816 ,CASE when xah.BALANCE_TYPE_CODE NOT IN ('A','B','E')
817 THEN 'Y'
818 ELSE 'N' END header_error4-- Balance type code invalid
819 ,CASE when xe.event_id IS NULL THEN 'Y'
820 ELSE 'N' END header_error5-- Header without valid event
821 from xla_ae_headers xah
822 ,gl_ledgers gll
823 ,xla_events xe
824 where gll.ledger_id (+) = xah.ledger_id
825 and xe.event_id (+) = xah.event_id
826 and (gll.ledger_id IS NULL OR
827 (xah.BALANCE_TYPE_CODE = 'B' AND
828 xah.BUDGET_VERSION_ID IS NULL) OR
829 (xah.BALANCE_TYPE_CODE = 'E' AND
830 xah.ENCUMBRANCE_TYPE_ID IS NULL) OR
831 xah.BALANCE_TYPE_CODE NOT IN ('A','B','E') OR
832 xe.event_id IS NULL)
833 and xah.application_id = p_application_id
834 and xah.upg_source_application_id = p_upgrading_application_id) xah
835 ,gl_row_multipliers grm
836 where grm.multiplier < 6
837 and decode(grm.multiplier,
838 1,header_error1,
839 2,header_error2,
840 3,header_error3,
841 4,header_error4,
842 header_error5) = 'Y');
843
844 COMMIT;
845
846 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
847 (upg_error_id, application_id, upg_source_application_id, creation_date
848 , created_by, last_update_date, last_updated_by, upg_batch_id
849 , error_level, error_message_name, ae_header_id)
850 (select
851 xla_upg_errors_s.nextval
852 ,g_application_id
853 ,g_source_application_id
854 ,sysdate
855 ,-1
856 ,sysdate
857 ,-1
858 ,-9999
859 , 'H'
860 ,decode(grm.multiplier,1,'XLA_UPG_UNBAL_ACCAMT'
861 ,'XLA_UPG_UNBAL_ENTRAMT')
862 ,ae_header_id
863 from (select /*+ no_merge */ xal.ae_header_id,
864 case when nvl(sum(accounted_dr), 0) <> nvl(sum(accounted_cr), 0)
865 then 'Y' else 'N' end header_error1, -- amts not balanced,
866 case when nvl(sum(entered_dr), 0) <> nvl(sum(entered_cr), 0)
867 then 'Y' else 'N' end header_error2 -- entered amts not balanced
868 from xla_ae_lines xal
869 where xal.application_id = p_application_id
870 and xal.currency_code <> 'STAT'
871 and xal.ledger_id in (select gll.ledger_id
872 from gl_ledgers gll
873 where gll.suspense_allowed_flag = 'N')
874 group by xal.ae_header_id
875 having nvl(sum(accounted_dr), 0)
876 <> nvl(sum(accounted_cr), 0)
877 or nvl(sum(entered_dr), 0)
878 <> nvl(sum(entered_cr), 0)) xal,
879 gl_row_multipliers grm
880 where xal.ae_header_id in ( select /*+ use_hash(xah) swap_join_inputs(xah) */
881 xah.ae_header_id
882 from xla_ae_headers xah
883 where xah.application_id = p_application_id
884 and xah.upg_source_application_id
885 = p_upgrading_application_id
886 and xah.balance_type_code <> 'B')
887 and grm.multiplier < 3
888 and decode(grm.multiplier, 1, header_error1, header_error2) = 'Y');
889
890 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
891 (upg_error_id, application_id, upg_source_application_id,creation_date
892 , created_by, last_update_date, last_updated_by, upg_batch_id
893 , error_level, ae_header_id, error_message_name)
894 (select xla_upg_errors_s.nextval
895 ,g_application_id
896 ,g_source_application_id
897 ,sysdate
898 ,-1
899 ,sysdate
900 ,-1
901 ,-9999
902 , 'H'
903 ,ae_header_id
904 ,'XLA_UPG_HDR_WO_LINES'
905 from (select xah.ae_header_id
906 from xla_ae_headers xah
907 where NOT EXISTS (SELECT xal.ae_header_id
908 from xla_ae_lines xal
912 and application_id = p_application_id
909 where xah.ae_header_id = xal.ae_header_id
910 and xah.application_id = xal.application_id
911 and xal.application_id = p_application_id)
913 and upg_source_application_id = p_upgrading_application_id));
914
915 COMMIT;
916
917 open csr_header_entries;
918 LOOP
919 FETCH csr_header_entries
920 BULK COLLECT INTO
921 l_header_id
922 LIMIT g_batch_size;
923 EXIT WHEN l_header_id.COUNT = 0;
924
925 FORALL i IN l_header_id.FIRST..l_header_id.LAST
926 UPDATE xla_ae_headers
927 set upg_valid_flag = CASE upg_valid_flag
928 WHEN 'F' THEN 'L'
929 WHEN 'J' THEN 'M'
930 WHEN 'I' THEN 'N'
931 ELSE 'K'
932 END
933 where ae_header_id = l_header_id(i);
934
935 l_rowcount := l_rowcount + sql%rowcount;
936
937 COMMIT;
938 END LOOP;
939 CLOSE csr_header_entries;
940
941 INSERT INTO XLA_UPG_ERRORS
942 (upg_error_id, application_id, upg_source_application_id, creation_date
943 , created_by, last_update_date, last_updated_by, upg_batch_id
944 , error_level, error_message_name,entity_id)
945 values(
946 xla_upg_errors_s.nextval
947 ,g_application_id
948 ,g_source_application_id
949 ,sysdate
950 ,-1
951 ,sysdate
952 ,-1
953 ,-9999
954 , 'V'
955 ,'XLA_HDR_VERIFICATION_RECORD'
956 ,l_rowcount);
957
958 COMMIT;
959
960 EXCEPTION
961 WHEN xla_exceptions_pkg.application_exception THEN
962 RAISE;
963 WHEN OTHERS THEN
964 xla_exceptions_pkg.raise_message
965 (p_location => 'XLA_UPG_VERIFICATION_PUB.Validate_Header_Entries');
966
967 END Validate_Header_Entries;
968
969 /*============================================================================+
970 | |
971 | Public Procedure |
972 | |
973 | Validate_Line_Entries |
974 | |
975 | This routine is called to validate the Line entries in upgrade. |
976 | |
977 +============================================================================*/
978
979 PROCEDURE Validate_Line_Entries (
980 p_upgrading_application_id IN NUMBER,
981 p_application_id IN NUMBER) IS
982
983 l_entity_id t_entity_id;
984 l_event_id t_event_id;
985 l_header_id t_header_id;
986 l_line_num t_line_num;
987 l_line_error1 t_error_flag;
988 l_line_error2 t_error_flag;
989 l_line_error3 t_error_flag;
990 l_line_error4 t_error_flag;
991 l_line_error5 t_error_flag;
992 l_line_error6 t_error_flag;
993 l_line_error7 t_error_flag;
994 l_line_error8 t_error_flag;
995 l_line_error9 t_error_flag;
996 l_line_error10 t_error_flag;
997 l_log_module VARCHAR2(240);
998 l_rowcount number(15) := 0;
999
1000 CURSOR csr_line_errors IS
1001 select distinct ae_header_id
1002 from xla_upg_errors
1003 where error_level = 'L'
1004 and application_id = p_application_id
1005 and upg_Source_application_id = p_upgrading_application_id;
1006 BEGIN
1007
1008 g_application_id := p_application_id;
1009 g_source_application_id := p_upgrading_application_id;
1010
1011 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1012 trace
1013 (p_msg => 'BEGIN of procedure Validate_Line_Entries'
1014 ,p_level => C_LEVEL_PROCEDURE
1015 ,p_module =>l_log_module);
1016 END IF;
1017
1018 IF g_log_enabled THEN
1019 l_log_module := C_DEFAULT_MODULE||'.Validate_Line_Entries';
1020 END IF;
1021
1022 -- Deleting all xla_upg_errors from previous run
1023
1024 delete from xla_upg_errors
1025 where application_id = p_application_id
1026 and upg_source_application_id = p_upgrading_application_id
1027 and error_message_name IN ('XLA_UPG_CCID_INVALID'
1028 ,'XLA_UPG_CCID_SUMACCT'
1029 ,'XLA_UPG_CCID_NOBUDGET'
1030 ,'XLA_UPG_PARTY_TYP_INVALID'
1031 ,'XLA_UPG_DRCR_NULL'
1032 ,'XLA_UPG_ENTAMT_DIFF_ACCAMT'
1033 ,'XLA_UPG_LINE_NO_HDR'
1034 ,'XLA_UPG_ENTAMT_ACCAMT_DIFFSIDE'
1035 ,'XLA_UPG_PARTY_ID_INVALID'
1036 ,'XLA_UPG_PARTY_SITE_INVALID'
1037 ,'XLA_LINE_VERIFICATION_RECORD');
1038
1039 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
1040 (upg_error_id, application_id, upg_source_application_id, creation_date
1041 , created_by, last_update_date, last_updated_by, upg_batch_id
1042 , error_level, ae_header_id, ae_line_num,error_message_name)
1043 (select
1044 xla_upg_errors_s.nextval
1045 ,g_application_id
1046 ,g_source_application_id
1047 ,sysdate
1048 ,-1
1049 ,sysdate
1050 ,-1
1051 ,-9999
1052 , 'L'
1053 ,ae_header_id
1054 ,ae_line_num
1055 ,decode(grm.multiplier,1,'XLA_UPG_CCID_INVALID'
1056 ,2,'XLA_UPG_CCID_SUMACCT'
1057 ,3,'XLA_UPG_CCID_NOBUDGET'
1058 ,4,'XLA_UPG_PARTY_TYP_INVALID'
1059 ,5,'XLA_UPG_DRCR_NULL'
1063 ,9,'XLA_UPG_PARTY_ID_INVALID'
1060 ,6,'XLA_UPG_ENTAMT_DIFF_ACCAMT'
1061 ,7,'XLA_UPG_LINE_NO_HDR'
1062 ,8,'XLA_UPG_ENTAMT_ACCAMT_DIFFSIDE'
1064 ,'XLA_UPG_PARTY_SITE_INVALID')
1065 from ( select xal.ae_header_id
1066 , ae_line_num
1067 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NULL THEN 'Y'
1068 ELSE 'N' END line_error1-- Invalid Code Combination Id
1069 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL
1070 and glcc.SUMMARY_FLAG = 'Y' THEN 'Y'
1071 ELSE 'N' END line_error2-- CCID not a Summary Account
1072 , CASE when glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL
1073 and xah.APPLICATION_ID IS NOT NULL
1074 and xah.BALANCE_TYPE_CODE = 'B'
1075 and glcc.DETAIL_BUDGETING_ALLOWED_FLAG <> 'Y' THEN 'Y'
1076 ELSE 'N' END line_error3-- Budgeting not allowed
1077 , CASE when xal.PARTY_TYPE_CODE IS NOT NULL
1078 and xal.PARTY_TYPE_CODE NOT IN ('C','S') THEN 'Y'
1079 ELSE 'N' END line_error4-- Invalid Party Type Code
1080 , CASE when (xal.accounted_dr is NULL AND xal.accounted_cr is NULL)
1081 or (xal.entered_dr is NULL AND xal.entered_cr is NULL)
1082 or (xal.accounted_dr is NOT NULL
1083 AND xal.accounted_cr is NOT NULL)
1084 or (xal.entered_dr is NOT NULL
1085 AND xal.entered_cr is NOT NULL)
1086 THEN 'Y'
1087 ELSE 'N' END line_error5
1088 , CASE when gll.currency_code IS NOT NULL
1089 and xal.currency_code = gll.currency_code
1090 and (nvl(xal.entered_dr,0) <> nvl(xal.accounted_dr,0)
1091 or nvl(xal.entered_cr,0) <> nvl(xal.accounted_cr,0))
1092 THEN 'Y'
1093 ELSE 'N' END line_error6
1094 , CASE when xah.application_id IS NULL THEN 'Y'
1095 ELSE 'N' END line_error7-- Orphan Line.
1096 , CASE when (xal.accounted_dr is NOT NULL and
1097 xal.entered_cr is NOT NULL) or
1098 (xal.accounted_cr is NOT NULL and
1099 xal.entered_dr is NOT NULL) THEN 'Y'
1100 ELSE 'N' END line_error8
1101 ,CASE when xal.party_id IS NULL THEN 'Y'
1102 ELSE 'N' END line_error9
1103 , CASE when xal.party_site_id IS NULL
1104 and xal.party_id IS NULL then 'Y'
1105 ELSE 'N' END line_error10
1106 FROM xla_ae_headers xah
1107 , xla_ae_lines xal
1108 , gl_code_combinations glcc
1109 , gl_ledgers gll
1110 , hz_parties hz
1111 , hz_party_sites hps
1112 WHERE glcc.code_combination_id(+) = xal.code_combination_id
1113 AND xah.ae_header_id(+) = xal.ae_header_id
1114 AND gll.ledger_id(+) = xah.ledger_id
1115 AND xal.party_id(+) = hz.party_id
1116 AND xal.party_site_id = hps.party_site_id
1117 AND (glcc.CHART_OF_ACCOUNTS_ID IS NULL OR
1118 (glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL AND
1119 glcc.SUMMARY_FLAG = 'Y' ) OR
1120 (glcc.CHART_OF_ACCOUNTS_ID IS NOT NULL AND
1121 xah.APPLICATION_ID IS NOT NULL AND
1122 xah.BALANCE_TYPE_CODE = 'B' AND
1123 glcc.DETAIL_BUDGETING_ALLOWED_FLAG <> 'Y') OR
1124 (xal.PARTY_TYPE_CODE IS NOT NULL AND
1125 xal.PARTY_TYPE_CODE NOT IN ('C','S') ) OR
1126 (xal.accounted_dr is NULL AND xal.accounted_cr is NULL) OR
1127 (xal.entered_dr is NULL AND xal.entered_cr is NULL) OR
1128 (xal.accounted_dr is NOT NULL AND xal.accounted_cr is NOT NULL) OR
1129 (xal.entered_dr is NOT NULL AND xal.entered_cr is NOT NULL) OR
1130 (gll.currency_code IS NOT NULL AND
1131 xal.currency_code = gll.currency_code AND
1132 (nvl(xal.entered_dr,0) <> nvl(xal.accounted_dr,0) OR
1133 nvl(xal.entered_cr,0) <> nvl(xal.accounted_cr,0))) OR
1134 ((xal.accounted_dr is NOT NULL and xal.entered_cr is NOT NULL) OR
1135 (xal.accounted_cr is NOT NULL and xal.entered_dr is NOT NULL)) OR
1136 (xah.application_id IS NULL))
1137 and xal.application_id = p_application_id
1138 and xah.upg_source_application_id = p_upgrading_application_id) xal
1139 ,gl_row_multipliers grm
1140 where grm.multiplier < 11
1141 and decode (grm.multiplier,1,line_error1
1142 ,2,line_error2
1143 ,3,line_error3
1144 ,4,line_error4
1145 ,5,line_error5
1146 ,6,line_error6
1147 ,7,line_error7
1148 ,8,line_error8
1149 ,9,line_error9
1150 ,line_error10) = 'Y');
1151
1152 COMMIT;
1153
1154 OPEN csr_line_errors;
1155 LOOP
1156 FETCH csr_line_errors
1157 BULK COLLECT INTO
1158 l_header_id
1159 LIMIT g_batch_size;
1160 EXIT WHEN l_header_id.COUNT = 0;
1161
1162 -- Mark Header as having errors
1163 FORALL i IN l_header_id.FIRST..l_header_id.LAST
1164 UPDATE xla_ae_headers
1165 set upg_valid_flag = CASE upg_valid_flag
1166 WHEN 'F' THEN 'P'
1167 WHEN 'J' THEN 'Q'
1168 WHEN 'I' THEN 'R'
1169 WHEN 'L' THEN 'S'
1170 WHEN 'M' THEN 'T'
1171 WHEN 'N' THEN 'U'
1172 ELSE 'O'
1173 END
1174 where ae_header_id = l_header_id(i)
1175 and application_id = p_application_id
1176 and UPG_SOURCE_APPLICATION_ID = p_upgrading_application_id;
1177
1178 -- finding out how many rows got inserted/updated.
1179
1180 l_rowcount := l_rowcount + sql%rowcount;
1181
1182 --debug message to ensure that this validation took place
1183 --successfully.
1184
1185 COMMIT;
1186 END LOOP;
1187 CLOSE csr_line_errors;
1188
1189 INSERT INTO XLA_UPG_ERRORS
1190 (upg_error_id, application_id, upg_source_application_id, creation_date
1191 , created_by, last_update_date, last_updated_by, upg_batch_id
1192 , error_level, error_message_name,entity_id)
1193 values(
1194 xla_upg_errors_s.nextval
1195 ,g_application_id
1196 ,g_source_application_id
1197 ,sysdate
1198 ,-1
1199 ,sysdate
1200 ,-1
1201 ,-9999
1202 , 'V'
1203 ,'XLA_LINE_VERIFICATION_RECORD'
1204 ,l_rowcount);
1205
1206 COMMIT;
1207
1208 EXCEPTION
1209 WHEN xla_exceptions_pkg.application_exception THEN
1210 RAISE;
1211 WHEN OTHERS THEN
1212 xla_exceptions_pkg.raise_message
1213 (p_location => 'XLA_UPG_VERIFICATION_PUB.Validate_Line_Entries');
1214
1215 END Validate_Line_Entries;
1216
1217 /*============================================================================+
1218 | |
1219 | Public Procedure |
1220 | |
1221 | Validate_Distribution_Entries |
1222 | |
1223 | This routine is called to validate the distribution entries in upgrade. |
1224 | |
1225 +============================================================================*/
1226
1227 PROCEDURE Validate_Distribution_Entries (p_application_id IN NUMBER) IS
1228
1229 l_entity_id t_entity_id;
1230 l_event_id t_event_id;
1231 l_header_id t_header_id;
1232 l_line_num t_line_num;
1233 l_temp_line_num t_line_num;
1234 l_log_module VARCHAR2(240);
1235 l_rowcount number(10) := 0;
1236
1237 CURSOR csr_distribution_errors IS
1238 select distinct ae_header_id
1239 from xla_upg_errors
1240 where error_level = 'D'
1241 and application_id = p_application_id;
1242
1243 BEGIN
1244
1245 g_application_id := p_application_id;
1246
1247 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1248 trace
1249 (p_msg => 'BEGIN of procedure Validate_Distribution_Entries'
1250 ,p_level => C_LEVEL_PROCEDURE
1251 ,p_module =>l_log_module);
1252 END IF;
1253
1254 IF g_log_enabled THEN
1255 l_log_module := C_DEFAULT_MODULE||'.Validate_Distribution_Entries';
1256 END IF;
1257
1258 delete from xla_upg_errors
1259 where application_id = p_application_id
1260 and error_message_name IN ('XLA_UPG_LINK_NO_LINE'
1261 ,'XLA_DIST_VERIFICATION_RECORD');
1262
1263 INSERT /*+ APPEND */ INTO XLA_UPG_ERRORS
1264 (upg_error_id, application_id, upg_source_application_id,creation_date
1265 , created_by, last_update_date, last_updated_by, upg_batch_id
1266 , error_level, ae_header_id, ae_line_num, temp_line_num
1267 , error_message_name)
1268 (select
1269 xla_upg_errors_s.nextval
1270 ,g_application_id
1271 ,602
1272 ,sysdate
1273 ,-1
1274 ,sysdate
1275 ,-1
1276 ,-9999
1277 , 'D'
1278 ,ae_header_id
1279 ,ae_line_num
1280 ,temp_line_num
1281 ,'XLA_UPG_LINK_NO_LINE'
1282 from (select xdl.ae_header_id, xdl.ae_line_num,xdl.temp_line_num
1283 from xla_distribution_links xdl
1284 where not exists (SELECT xal.ae_header_id, xal.ae_line_num
1285 from xla_ae_lines xal
1286 where xal.ae_header_id = xdl.ae_header_id
1287 and xal.ae_line_num = xdl.ae_line_num
1288 and xal.application_id = p_application_id)
1289 and xdl.application_id = p_application_id));
1290
1291 -- finding out how many rows got updated.
1292
1293 COMMIT;
1294
1295 OPEN csr_distribution_errors;
1296 LOOP
1297 FETCH csr_distribution_errors
1298 BULK COLLECT INTO
1299 l_header_id
1300 LIMIT g_batch_size;
1301 EXIT when l_header_id.COUNT = 0;
1302
1303 FORALL i IN l_header_id.FIRST..l_header_id.LAST
1304 UPDATE xla_ae_headers
1305 set upg_valid_flag = CASE upg_valid_flag
1306 WHEN 'P' THEN 'W'
1307 WHEN 'Q' THEN 'X'
1308 WHEN 'R' THEN 'Y'
1309 WHEN 'F' THEN 'Z'
1310 WHEN 'J' THEN '1'
1311 WHEN 'I' THEN '2'
1312 WHEN 'L' THEN '3'
1313 WHEN 'M' THEN '4'
1314 WHEN 'N' THEN '5'
1315 WHEN 'S' THEN '6'
1316 WHEN 'T' THEN '7'
1317 WHEN 'U' THEN '8'
1318 ELSE 'V'
1319 END
1320 where ae_header_id = l_header_id(i)
1321 and application_id = p_application_id;
1322
1323 l_rowcount := l_rowcount + sql%rowcount;
1324
1325 COMMIT;
1326 END LOOP;
1327 CLOSE csr_distribution_errors;
1331 , created_by, last_update_date, last_updated_by, upg_batch_id
1328
1329 INSERT INTO XLA_UPG_ERRORS
1330 (upg_error_id, application_id, upg_source_application_id, creation_date
1332 , error_level, error_message_name,entity_id)
1333 values(
1334 xla_upg_errors_s.nextval
1335 ,g_application_id
1336 ,602
1337 ,sysdate
1338 ,-1
1339 ,sysdate
1340 ,-1
1341 ,-9999
1342 , 'V'
1343 ,'XLA_DIST_VERIFICATION_RECORD'
1344 ,l_rowcount);
1345
1346 COMMIT;
1347
1348 EXCEPTION
1349 WHEN xla_exceptions_pkg.application_exception THEN
1350 RAISE;
1351 WHEN OTHERS THEN
1352 xla_exceptions_pkg.raise_message
1353 (p_location => 'XLA_UPG_VERIFICATION_PUB.Validate_Distribution_Entries');
1354
1355 END Validate_Distribution_Entries;
1356
1357 /*============================================================================+
1358 | |
1359 | Public Procedure |
1360 | |
1361 | Populate_Segment_Values |
1362 | |
1363 | This routine is called to populate segment values. |
1364 | |
1365 +============================================================================*/
1366 PROCEDURE Populate_Segment_Values (
1367 p_application_id IN NUMBER) IS
1368
1369 L_LOG_MODULE VARCHAR2(240);
1370 L_HEADER_ID T_HEADER_ID;
1371 L_SEG_VALUE T_SEG_VALUE;
1372 L_LINE_COUNT T_LINE_COUNT;
1373 L_SEG_TYPE T_SEG_TYPE;
1374
1375 -- Cursor declarations
1376 Cursor csr_bal_segment_values IS
1377 select xal.ae_header_id, decode(gll.bal_seg_column_name,
1378 'SEGMENT1', ccid.segment1,
1379 'SEGMENT2', ccid.segment2,
1380 'SEGMENT3', ccid.segment3,
1381 'SEGMENT4', ccid.segment4,
1382 'SEGMENT5', ccid.segment5,
1383 'SEGMENT6', ccid.segment6,
1384 'SEGMENT7', ccid.segment7,
1385 'SEGMENT8', ccid.segment8,
1386 'SEGMENT9', ccid.segment9,
1387 'SEGMENT10', ccid.segment10,
1388 'SEGMENT11', ccid.segment11,
1389 'SEGMENT12', ccid.segment12,
1390 'SEGMENT13', ccid.segment13,
1391 'SEGMENT14', ccid.segment14,
1392 'SEGMENT15', ccid.segment15,
1393 'SEGMENT16', ccid.segment16,
1394 'SEGMENT17', ccid.segment17,
1395 'SEGMENT18', ccid.segment18,
1396 'SEGMENT19', ccid.segment19,
1397 'SEGMENT20', ccid.segment20,
1398 'SEGMENT21', ccid.segment21,
1399 'SEGMENT22', ccid.segment22,
1400 'SEGMENT23', ccid.segment23,
1401 'SEGMENT24', ccid.segment24,
1402 'SEGMENT25', ccid.segment25,
1403 'SEGMENT26', ccid.segment26,
1404 'SEGMENT27', ccid.segment27,
1405 'SEGMENT28', ccid.segment28,
1406 'SEGMENT29', ccid.segment29,
1407 'SEGMENT30', ccid.segment30,
1408 NULL), count(*)
1409 from xla_ae_lines xal,
1410 xla_ae_headers xah,
1411 gl_ledgers gll,
1412 gl_code_combinations ccid
1413 where gll.ledger_id = xah.ledger_id
1414 and xah.application_id = p_application_id
1415 and xah.ae_header_id = xal.ae_header_id
1416 and xal.application_id = p_application_id
1417 and ccid.code_combination_id = xal.code_combination_id
1418 GROUP BY xal.ae_header_id, decode(gll.bal_seg_column_name,
1419 'SEGMENT1', ccid.segment1,
1420 'SEGMENT2', ccid.segment2,
1421 'SEGMENT3', ccid.segment3,
1422 'SEGMENT4', ccid.segment4,
1423 'SEGMENT5', ccid.segment5,
1424 'SEGMENT6', ccid.segment6,
1425 'SEGMENT7', ccid.segment7,
1426 'SEGMENT8', ccid.segment8,
1427 'SEGMENT9', ccid.segment9,
1428 'SEGMENT10', ccid.segment10,
1429 'SEGMENT11', ccid.segment11,
1430 'SEGMENT12', ccid.segment12,
1431 'SEGMENT13', ccid.segment13,
1432 'SEGMENT14', ccid.segment14,
1433 'SEGMENT15', ccid.segment15,
1434 'SEGMENT16', ccid.segment16,
1435 'SEGMENT17', ccid.segment17,
1436 'SEGMENT18', ccid.segment18,
1440 'SEGMENT22', ccid.segment22,
1437 'SEGMENT19', ccid.segment19,
1438 'SEGMENT20', ccid.segment20,
1439 'SEGMENT21', ccid.segment21,
1441 'SEGMENT23', ccid.segment23,
1442 'SEGMENT24', ccid.segment24,
1443 'SEGMENT25', ccid.segment25,
1444 'SEGMENT26', ccid.segment26,
1445 'SEGMENT27', ccid.segment27,
1446 'SEGMENT28', ccid.segment28,
1447 'SEGMENT29', ccid.segment29,
1448 'SEGMENT30', ccid.segment30,
1449 NULL);
1450
1451 Cursor csr_mgt_segment_values IS
1452 select xal.ae_header_id, decode(gll.mgt_seg_column_name,
1453 'SEGMENT1', ccid.segment1,
1454 'SEGMENT2', ccid.segment2,
1455 'SEGMENT3', ccid.segment3,
1456 'SEGMENT4', ccid.segment4,
1457 'SEGMENT5', ccid.segment5,
1458 'SEGMENT6', ccid.segment6,
1459 'SEGMENT7', ccid.segment7,
1460 'SEGMENT8', ccid.segment8,
1461 'SEGMENT9', ccid.segment9,
1462 'SEGMENT10', ccid.segment10,
1463 'SEGMENT11', ccid.segment11,
1464 'SEGMENT12', ccid.segment12,
1465 'SEGMENT13', ccid.segment13,
1466 'SEGMENT14', ccid.segment14,
1467 'SEGMENT15', ccid.segment15,
1468 'SEGMENT16', ccid.segment16,
1469 'SEGMENT17', ccid.segment17,
1470 'SEGMENT18', ccid.segment18,
1471 'SEGMENT19', ccid.segment19,
1472 'SEGMENT20', ccid.segment20,
1473 'SEGMENT21', ccid.segment21,
1474 'SEGMENT22', ccid.segment22,
1475 'SEGMENT23', ccid.segment23,
1476 'SEGMENT24', ccid.segment24,
1477 'SEGMENT25', ccid.segment25,
1478 'SEGMENT26', ccid.segment26,
1479 'SEGMENT27', ccid.segment27,
1480 'SEGMENT28', ccid.segment28,
1481 'SEGMENT29', ccid.segment29,
1482 'SEGMENT30', ccid.segment30,
1483 NULL), count(*)
1484 from xla_ae_lines xal,
1485 xla_ae_headers xah,
1486 gl_ledgers gll,
1487 gl_code_combinations ccid
1488 where gll.ledger_id = xah.ledger_id
1489 and xah.application_id = p_application_id
1490 and xah.ae_header_id = xal.ae_header_id
1491 and xal.application_id = p_application_id
1492 and ccid.code_combination_id = xal.code_combination_id
1493 and gll.mgt_seg_column_name IS NOT NULL
1494 GROUP BY xal.ae_header_id, decode(gll.mgt_seg_column_name,
1495 'SEGMENT1', ccid.segment1,
1496 'SEGMENT2', ccid.segment2,
1497 'SEGMENT3', ccid.segment3,
1498 'SEGMENT4', ccid.segment4,
1499 'SEGMENT5', ccid.segment5,
1500 'SEGMENT6', ccid.segment6,
1501 'SEGMENT7', ccid.segment7,
1502 'SEGMENT8', ccid.segment8,
1503 'SEGMENT9', ccid.segment9,
1504 'SEGMENT10', ccid.segment10,
1505 'SEGMENT11', ccid.segment11,
1506 'SEGMENT12', ccid.segment12,
1507 'SEGMENT13', ccid.segment13,
1508 'SEGMENT14', ccid.segment14,
1509 'SEGMENT15', ccid.segment15,
1510 'SEGMENT16', ccid.segment16,
1511 'SEGMENT17', ccid.segment17,
1512 'SEGMENT18', ccid.segment18,
1513 'SEGMENT19', ccid.segment19,
1514 'SEGMENT20', ccid.segment20,
1515 'SEGMENT21', ccid.segment21,
1516 'SEGMENT22', ccid.segment22,
1517 'SEGMENT23', ccid.segment23,
1518 'SEGMENT24', ccid.segment24,
1519 'SEGMENT25', ccid.segment25,
1520 'SEGMENT26', ccid.segment26,
1521 'SEGMENT27', ccid.segment27,
1522 'SEGMENT28', ccid.segment28,
1523 'SEGMENT29', ccid.segment29,
1524 'SEGMENT30', ccid.segment30,
1525 NULL);
1526 BEGIN
1527 g_application_id := p_application_id;
1528
1529 IF g_log_enabled THEN
1530 l_log_module := C_DEFAULT_MODULE||'.Populate_Segment_Values';
1531 END IF;
1535 (p_msg => 'BEGIN of procedure Populate_Segment_Values'
1532
1533 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1534 trace
1536 ,p_level => C_LEVEL_PROCEDURE
1537 ,p_module =>l_log_module);
1538 END IF;
1539
1540 OPEN csr_bal_segment_values;
1541 LOOP
1542 FETCH csr_bal_segment_values
1543 BULK COLLECT INTO
1544 l_header_id
1545 , l_seg_value
1546 , l_line_count
1547 LIMIT g_batch_size;
1548 EXIT when l_header_id.COUNT = 0;
1549
1550 FORALL i IN l_header_id.FIRST..l_header_id.LAST
1551 INSERT INTO xla_ae_segment_values
1552 (ae_header_id, segment_type_code, segment_value, ae_lines_count,
1553 upg_batch_id)
1554 values (
1555 l_header_id(i)
1556 ,C_BAL_SEGMENT
1557 ,l_seg_value(i)
1558 ,l_line_count(i)
1559 ,-9999);
1560 COMMIT;
1561 END LOOP;
1562 CLOSE csr_bal_segment_values;
1563
1564 OPEN csr_mgt_segment_values;
1565 LOOP
1566 FETCH csr_mgt_segment_values
1567 BULK COLLECT INTO
1568 l_header_id
1569 , l_seg_value
1570 , l_line_count
1571 LIMIT g_batch_size;
1572 EXIT when l_header_id.COUNT = 0;
1573
1574 FORALL i IN l_header_id.FIRST..l_header_id.LAST
1575 INSERT INTO xla_ae_segment_values
1576 (ae_header_id, segment_type_code, segment_value, ae_lines_count,
1577 upg_batch_id)
1578 values (
1579 l_header_id(i)
1580 ,C_MGT_SEGMENT
1581 ,l_seg_value(i)
1582 ,l_line_count(i)
1583 ,-9999);
1584 COMMIT;
1585 END LOOP;
1586 CLOSE csr_mgt_segment_values;
1587
1588 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1589 trace
1590 (p_msg => 'END of procedure Populate_Segment_Values'
1591 ,p_level => C_LEVEL_PROCEDURE
1592 ,p_module => l_log_module);
1593 END IF;
1594
1595 EXCEPTION
1596 WHEN xla_exceptions_pkg.application_exception THEN
1597 RAISE;
1598 WHEN OTHERS THEN
1599 xla_exceptions_pkg.raise_message
1600 (p_location => 'XLA_UPG_VERIFICATION_PUB.Populate_Segment_Values');
1601 END Populate_Segment_Values;
1602
1603 BEGIN
1604 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1605 g_log_enabled := fnd_log.test
1606 (log_level => g_log_level
1607 ,MODULE => C_DEFAULT_MODULE);
1608
1609 IF NOT g_log_enabled THEN
1610 g_log_level := C_LEVEL_LOG_DISABLED;
1611 END IF;
1612
1613 END XLA_UPG_VERIFICATION_PUB;