[Home] [Help]
PACKAGE BODY: APPS.XLA_RETRIVE_CCID_PKG
Source
1 PACKAGE BODY xla_retrive_ccid_pkg AS
2 /* $Header: xlarccid.pkb 120.1.12010000.2 2009/08/05 12:38:47 karamakr noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | ALL rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_retrive_ccid_pkg |
10 | |
11 | DESCRIPTION |
12 | Package body for the Retrive CCID's Program. |
13 | |
14 | HISTORY |
15 | 04/08/2008 T.V.Vamsi Krishna Created |
16 +===========================================================================*/
17
18 --=============================================================================
19 -- *********** Local Trace Line **********
20 --=============================================================================
21 c_level_statement CONSTANT NUMBER := fnd_log.level_statement;
22 c_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
23 c_level_event CONSTANT NUMBER := fnd_log.level_event;
24 c_level_exception CONSTANT NUMBER := fnd_log.level_exception;
25 c_level_error CONSTANT NUMBER := fnd_log.level_error;
26 c_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
27 c_level_log_disabled CONSTANT NUMBER := 99;
28 c_default_module CONSTANT VARCHAR2 (240)
29 := 'xla.plsql.xla_retrive_ccid_pkg';
30 g_log_level NUMBER;
31 g_log_enabled BOOLEAN;
32 c_log_size CONSTANT NUMBER := 2000;
33 c_ccid_unprocessed CONSTANT VARCHAR2 (30) := 'UNPROCESSED';
34 c_ccid_processed CONSTANT VARCHAR2 (30) := 'PROCESSED';
35 c_ccid_fail CONSTANT VARCHAR2 (30) := 'FAILED';
36 c_ccid_success CONSTANT VARCHAR2 (30) := 'SUCCESSED';
37 g_tot_ccid NUMBER;
38 g_seq_context_value t_array_number;
39
40 PROCEDURE wait_for_requests (
41 p_array_request_id IN t_array_number,
42 p_error_status OUT NOCOPY VARCHAR2,
43 p_warning_status OUT NOCOPY VARCHAR2
44 );
45
46 PROCEDURE wait_for_sing_req (
47 p_request_id IN NUMBER,
48 p_error_status OUT NOCOPY VARCHAR2,
49 p_warning_status OUT NOCOPY VARCHAR2
50 );
51
52 PROCEDURE TRACE (
53 p_msg IN VARCHAR2,
54 p_level IN NUMBER,
55 p_module IN VARCHAR2 DEFAULT c_default_module
56 )
57 IS
58 l_max NUMBER;
59 l_pos NUMBER := 1;
60 BEGIN
61 l_pos := 1;
62
63 IF (p_msg IS NULL AND p_level >= g_log_level)
64 THEN
65 fnd_log.MESSAGE (p_level, p_module);
66 ELSIF p_level >= g_log_level
67 THEN
68 l_max := LENGTH (p_msg);
69
70 IF l_max <= c_log_size
71 THEN
72 fnd_log.STRING (p_level, p_module, p_msg);
73 ELSE
74 WHILE (l_pos - 1) * c_log_size <= l_max
75 LOOP
76 fnd_log.STRING (p_level,
77 p_module,
78 SUBSTR (p_msg,
79 (l_pos - 1) * c_log_size + 1,
80 c_log_size
81 )
82 );
83 l_pos := l_pos + 1;
84 END LOOP;
85 END IF;
86 END IF;
87 EXCEPTION
88 WHEN xla_exceptions_pkg.application_exception
89 THEN
90 RAISE;
91 WHEN OTHERS
92 THEN
93 xla_exceptions_pkg.raise_message
94 (p_location => 'xla_retrive_ccid_pkg.trace');
95 END TRACE;
96
97 --=============================================================================
98 -- ******* Print Log File **********
99 --=============================================================================
100 PROCEDURE print_logfile (p_msg IN VARCHAR2)
101 IS
102 BEGIN
103 fnd_file.put_line (fnd_file.LOG, p_msg);
104 EXCEPTION
105 WHEN xla_exceptions_pkg.application_exception
106 THEN
107 RAISE;
108 WHEN OTHERS
109 THEN
110 xla_exceptions_pkg.raise_message
111 (p_location => 'xla_retrive_ccid_pkg.print_logfile');
112 END print_logfile;
113
114 --========================================================================================
115 -- Procedure to collect ccids and split them into ranges
116 --========================================================================================
117 PROCEDURE collect_ccid_inf (
118 p_application_id IN NUMBER,
119 p_acc_batch_id IN NUMBER,
120 p_ledger_id IN NUMBER,
121 p_parent_request_id IN NUMBER,
122 p_parallel_processes IN NUMBER
123 )
124 IS
125 l_count NUMBER := 0;
126 l_loop_count NUMBER := 0;
127 l_parll_proc_size NUMBER;
128 l_log_module VARCHAR2 (240);
129 l_rec_count NUMBER;
130 BEGIN
131 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
132 || ' - Starting of the Collect CCID Information'
133 );
134
135 IF g_log_enabled
136 THEN
137 l_log_module := c_default_module || '.collect_ccid_inf';
138 END IF;
139
140 IF (c_level_procedure >= g_log_level)
141 THEN
142 TRACE (p_msg => 'BEGIN of procedure COLLECT_CCID_INF',
143 p_level => c_level_procedure,
144 p_module => l_log_module
145 );
146 TRACE (p_msg => 'p_application_id = '
147 || TO_CHAR (p_application_id),
148 p_level => c_level_procedure,
149 p_module => l_log_module
150 );
151 TRACE (p_msg => 'p_ledger_id = ' || TO_CHAR (p_ledger_id),
152 p_level => c_level_procedure,
153 p_module => l_log_module
154 );
155 TRACE (p_msg => 'p_accounting_batch_id = '
156 || TO_CHAR (p_acc_batch_id),
157 p_level => c_level_procedure,
158 p_module => l_log_module
159 );
160 TRACE (p_msg => 'p_parent_request_id = '
161 || TO_CHAR (p_parent_request_id),
162 p_level => c_level_procedure,
163 p_module => l_log_module
164 );
165 TRACE (p_msg => 'p_parallel_processes = '
166 || TO_CHAR (p_parallel_processes),
167 p_level => c_level_procedure,
168 p_module => l_log_module
169 );
170 END IF;
171
172 ----------------------------------------------------------------------------
173 -- Getting ledger ids that will be used/cached in this run of accounting
174 -- program
175 ----------------------------------------------------------------------------
176 SELECT xlr.ledger_id
177 BULK COLLECT INTO g_seq_context_value
178 FROM xla_ledger_relationships_v xlr, xla_subledger_options_v xso
179 WHERE xlr.relationship_enabled_flag = 'Y'
180 AND xlr.ledger_category_code IN ('ALC', 'PRIMARY', 'SECONDARY')
181 AND DECODE (xso.valuation_method_flag,
182 'N', xlr.primary_ledger_id,
183 DECODE (xlr.ledger_category_code,
184 'ALC', xlr.primary_ledger_id,
185 xlr.ledger_id
186 )
187 ) = p_ledger_id
188 AND xso.application_id = p_application_id
189 AND xso.ledger_id =
190 DECODE (xlr.ledger_category_code,
191 'ALC', xlr.primary_ledger_id,
192 xlr.ledger_id
193 )
194 AND xso.enabled_flag = 'Y';
195
196 print_logfile ( 'Number of ledgers generated for this run = '
197 || g_seq_context_value.COUNT
198 );
199
200 IF (c_level_statement >= g_log_level)
201 THEN
202 TRACE (p_msg => 'Number of ledgers generated for this run = '
203 || g_seq_context_value.COUNT,
204 p_level => c_level_statement,
205 p_module => l_log_module
206 );
207 END IF;
208
209 -- Count total number of CCIDs for Primary and Secondary Ledgers
210 BEGIN
211 FOR i IN g_seq_context_value.FIRST .. g_seq_context_value.LAST
212 LOOP
213 SELECT NVL (MAX (ROWNUM), 0)
214 INTO l_loop_count
215 FROM gl_code_combinations gcc
216 WHERE gcc.code_combination_id IN (
217 SELECT code_combination_id
218 FROM xla_ae_lines
219 WHERE ae_header_id IN (
220 SELECT ae_header_id
221 FROM xla_ae_headers
222 WHERE accounting_batch_id = p_acc_batch_id
223 AND application_id = p_application_id
224 AND ledger_id = g_seq_context_value (i)));
225
226 l_count := l_count + NVL (l_loop_count, 0);
227 END LOOP;
228 EXCEPTION
229 WHEN NO_DATA_FOUND
230 THEN
231 NULL;
232 END;
233
234 g_tot_ccid := l_count;
235 print_logfile ('Number of Distinct CCIDs :' || l_count);
236
237 BEGIN
238 IF p_parallel_processes <> 0 AND l_count <> 0
239 THEN
240 l_parll_proc_size := CEIL (l_count / p_parallel_processes);
241
242 IF l_parll_proc_size <> 0
243 THEN
244 FOR i IN g_seq_context_value.FIRST .. g_seq_context_value.LAST
245 LOOP
246 SELECT NVL (MAX (ROWNUM), 0)
247 INTO l_rec_count
248 FROM xla_ae_headers
249 WHERE accounting_batch_id = p_acc_batch_id
250 AND application_id = p_application_id
251 AND ledger_id = g_seq_context_value (i);
252
253 IF l_rec_count <> 0
254 THEN
255 INSERT INTO xla_fsah_ccid_ranges
256 (parent_request_id, request_id, batch_id,
257 application_id, ledger_id, from_ccid,
258 to_ccid, status_code)
259 SELECT p_parent_request_id, NULL, p_acc_batch_id,
260 p_application_id, g_seq_context_value (i),
261 MIN (code_combination_id),
262 MAX (code_combination_id),
263 c_ccid_unprocessed
264 FROM (SELECT gcc.code_combination_id,
265 CEIL
266 ( SUM (COUNT (*)) OVER (ORDER BY gcc.code_combination_id ROWS UNBOUNDED PRECEDING)
267 / l_parll_proc_size
268 ) wu
269 FROM gl_code_combinations gcc
270 WHERE gcc.code_combination_id IN (
271 SELECT code_combination_id
272 FROM xla_ae_lines
273 WHERE ae_header_id IN (
274 SELECT ae_header_id
275 FROM xla_ae_headers
276 WHERE accounting_batch_id =
277 p_acc_batch_id
278 AND application_id =
279 p_application_id
280 AND ledger_id =
281 g_seq_context_value
282 (i)
283 GROUP BY ae_header_id))
284 GROUP BY gcc.code_combination_id)
285 GROUP BY wu;
286 END IF;
287 END LOOP;
288
289 COMMIT;
290 ELSE
291 print_logfile ('No CCIDs are available for process');
292 END IF;
293 END IF;
294 EXCEPTION
295 WHEN OTHERS
296 THEN
297 NULL;
298 END;
299
300 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
301 || ' - End of the Collect CCID Information'
302 );
303 EXCEPTION
304 WHEN xla_exceptions_pkg.application_exception
305 THEN
306 RAISE;
307 WHEN OTHERS
308 THEN
309 xla_accounting_err_pkg.build_message
310 (p_appli_s_name => 'XLA',
311 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
312 p_token_1 => 'APPLICATION_NAME',
313 p_value_1 => 'SLA',
314 p_entity_id => NULL,
315 p_event_id => NULL
316 );
317 print_logfile ('Error occured in collect_ccid_inf');
318 xla_exceptions_pkg.raise_message
319 (p_location => 'xla_retrive_ccid_pkg.collect_ccid_inf');
320 END collect_ccid_inf;
321
322 --===================================================================================
323 --Procedure for Update table xla_fsah_ccid_ranges with status
324 --===================================================================================
325 PROCEDURE update_ccid_inf (
326 p_parent_request_id IN NUMBER,
327 p_from_ccid OUT NOCOPY NUMBER,
328 p_to_ccid OUT NOCOPY NUMBER,
329 p_ledger_id OUT NOCOPY NUMBER
330 )
331 IS
332 l_log_module VARCHAR2 (240);
333 BEGIN
334 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
335 || ' - Starting of the Update CCID Information'
336 );
337
338 IF g_log_enabled
339 THEN
340 l_log_module := c_default_module || '.update_ccid_inf';
341 END IF;
342
343 IF (c_level_procedure >= g_log_level)
344 THEN
345 TRACE (p_msg => 'BEGIN of procedure UPDATE_CCID_INF',
346 p_level => c_level_procedure,
347 p_module => l_log_module
348 );
349 TRACE (p_msg => 'p_parent_request_id = '
350 || TO_CHAR (p_parent_request_id),
351 p_level => c_level_procedure,
352 p_module => l_log_module
353 );
354 TRACE (p_msg => 'p_from_ccid = ' || TO_CHAR (p_from_ccid),
355 p_level => c_level_procedure,
356 p_module => l_log_module
357 );
358 TRACE (p_msg => 'p_to_ccid = ' || TO_CHAR (p_to_ccid),
359 p_level => c_level_procedure,
360 p_module => l_log_module
361 );
362 END IF;
363
364 UPDATE xla_fsah_ccid_ranges
365 SET status_code = c_ccid_processed
366 WHERE parent_request_id = p_parent_request_id
367 AND status_code = c_ccid_unprocessed
368 AND ROWNUM = 1
369 RETURNING from_ccid, to_ccid, ledger_id
370 INTO p_from_ccid, p_to_ccid, p_ledger_id;
371
372 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
373 || ' - End of the Update CCID Information'
374 );
375 EXCEPTION
376 WHEN xla_exceptions_pkg.application_exception
377 THEN
378 RAISE;
379 WHEN OTHERS
380 THEN
381 xla_accounting_err_pkg.build_message
382 (p_appli_s_name => 'XLA',
383 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
384 p_token_1 => 'APPLICATION_NAME',
385 p_value_1 => 'SLA',
386 p_entity_id => NULL,
387 p_event_id => NULL
388 );
389 print_logfile ('Error occured in update_ccid_inf');
390 xla_exceptions_pkg.raise_message
391 (p_location => 'xla_retrive_ccid_pkg.update_ccid_inf');
392 END update_ccid_inf;
393
394 --============================================================================================
395 -- Procedure for getting CCID Information, CP which is called from Create Accounting Program
396 -- and calls Java Concurrent Program based on number of Parallel Processors
397 --============================================================================================
398 PROCEDURE get_ccid_information (
399 errbuf OUT NOCOPY VARCHAR2,
400 retcode OUT NOCOPY VARCHAR2,
401 p_application_id IN NUMBER,
402 p_acc_batch_id IN NUMBER,
403 p_ledger_id IN NUMBER,
404 p_parent_request_id IN NUMBER
405 )
406 IS
407 l_array_ccid t_array_ccid;
408 l_javacp_request_id t_array_number;
409 l_javacp_request_id_sing NUMBER;
410 l_tot_ccid NUMBER (5);
411 l_batch_count NUMBER (4)
412 := fnd_profile.VALUE ('XLA_FSAH_EXT_THRD_CNT');
413 l_parallel_processes NUMBER
414 := fnd_profile.VALUE ('XLA_FSAH_EXT_THRD_SIZE');
415 l_start_index NUMBER (10);
416 l_end_index NUMBER (10);
417 l_num_proc NUMBER (4) := 0;
418 l_seq_num NUMBER := 0;
419 l_error_status VARCHAR2 (1) := 'N';
420 l_warning_status VARCHAR2 (1) := 'N';
421 l_log_module VARCHAR2 (240);
422 l_application_id NUMBER := p_application_id;
423 l_acc_batch_id NUMBER := p_acc_batch_id;
424 l_ledger_id NUMBER := p_ledger_id;
425 l_parent_request_id NUMBER := p_parent_request_id;
426 l_ledger_id_i NUMBER;
427 l_callstatus BOOLEAN;
428 l_phase VARCHAR2 (30);
429 l_status VARCHAR2 (30);
430 l_dev_phase VARCHAR2 (30);
431 l_message VARCHAR2 (240);
432 l_dev_status VARCHAR2 (30);
433 n NUMBER := 0;
434 BEGIN
435 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
436 || ' - Starting of the Retrive CCID Information'
437 );
438
439 IF g_log_enabled
440 THEN
441 l_log_module := c_default_module || '.get_ccid_information';
442 END IF;
443
444 IF (c_level_procedure >= g_log_level)
445 THEN
446 TRACE (p_msg => 'BEGIN of procedure GET_CCID_INFORMATION',
447 p_level => c_level_procedure,
448 p_module => l_log_module
449 );
450 TRACE (p_msg => 'p_application_id = '
451 || TO_CHAR (p_application_id),
452 p_level => c_level_procedure,
453 p_module => l_log_module
454 );
455 TRACE (p_msg => 'p_ledger_id = ' || TO_CHAR (p_ledger_id),
456 p_level => c_level_procedure,
457 p_module => l_log_module
458 );
459 TRACE (p_msg => 'p_accounting_batch_id = '
460 || TO_CHAR (p_acc_batch_id),
461 p_level => c_level_procedure,
462 p_module => l_log_module
463 );
464 END IF;
465
466 collect_ccid_inf (p_application_id => l_application_id,
467 p_acc_batch_id => l_acc_batch_id,
468 p_ledger_id => l_ledger_id,
469 p_parent_request_id => l_parent_request_id,
470 p_parallel_processes => l_parallel_processes
471 );
472
473 BEGIN
474 IF g_tot_ccid <> 0 then
475 IF g_tot_ccid <= l_parallel_processes
476 THEN
477 FOR i IN g_seq_context_value.FIRST .. g_seq_context_value.LAST
478 LOOP
479 l_javacp_request_id_sing :=
480 fnd_request.submit_request
481 (application => 'XLA',
482 program => 'XLAFSAHJCP',
483 description => NULL,
484 start_time => NULL,
485 sub_request => FALSE,
486 argument1 => l_batch_count,
487 argument2 => 0,
488 argument3 => 0,
489 argument4 => p_application_id,
490 argument5 => p_acc_batch_id,
491 argument6 => g_seq_context_value
492 (i),
493 -- ledger value
494 argument7 => p_parent_request_id
495 );
496 COMMIT;
497 wait_for_sing_req (p_request_id => l_javacp_request_id_sing,
498 p_error_status => l_error_status,
499 p_warning_status => l_warning_status
500 );
501 END LOOP;
502 ELSE
503 FOR i IN g_seq_context_value.FIRST .. g_seq_context_value.LAST
504 LOOP
505 BEGIN
506 --print_logfile('Ledger Id from main loop: '||' '||g_seq_context_value(i));
507 LOOP
508 SELECT ledger_id, from_ccid
509 INTO l_ledger_id_i, l_start_index
510 FROM xla_fsah_ccid_ranges
511 WHERE parent_request_id = p_parent_request_id
512 AND status_code = 'UNPROCESSED'
513 AND ROWNUM = 1;
514
515 -- print_logfile('Ledger Id from table: '||' '||l_ledger_id_i);
516 EXIT WHEN l_start_index IS NULL
517 OR l_start_index = 0
518 OR g_seq_context_value (i) <> l_ledger_id_i;
519 update_ccid_inf
520 (p_parent_request_id => l_parent_request_id,
521 p_from_ccid => l_start_index,
522 p_to_ccid => l_end_index,
523 p_ledger_id => l_ledger_id_i
524 );
525 l_javacp_request_id_sing :=
526 fnd_request.submit_request
527 (application => 'XLA',
528 program => 'XLAFSAHJCP',
529 description => NULL,
530 start_time => NULL,
531 sub_request => FALSE,
532 argument1 => l_batch_count,
533 argument2 => l_start_index,
534 argument3 => l_end_index,
535 argument4 => p_application_id,
536 argument5 => p_acc_batch_id,
537 argument6 => l_ledger_id_i,
538 argument7 => p_parent_request_id
539 );
540
541 UPDATE xla_fsah_ccid_ranges
542 SET request_id = l_javacp_request_id_sing
543 WHERE parent_request_id = p_parent_request_id
544 AND from_ccid = l_start_index
545 AND to_ccid = l_end_index;
546
547 IF l_javacp_request_id_sing = 0
548 THEN
549 xla_accounting_err_pkg.build_message
550 (p_appli_s_name => 'XLA',
551 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
552 p_token_1 => 'APPLICATION_NAME',
553 p_value_1 => 'SLA',
554 p_entity_id => NULL,
555 p_event_id => NULL
556 );
557 print_logfile
558 ('Technical Error : Unable to submit Java Concurrent Program request'
559 );
560 xla_exceptions_pkg.raise_message
561 (p_appli_s_name => 'XLA',
562 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
563 p_token_1 => 'APPLICATION_NAME',
564 p_value_1 => 'SLA'
565 );
566 ELSE
567 n := n + 1;
568 l_javacp_request_id (n) := l_javacp_request_id_sing;
569 END IF;
570 END LOOP;
571 EXCEPTION
572 WHEN NO_DATA_FOUND
573 THEN
574 NULL;
575
576 END;
577
578 COMMIT;
579 wait_for_requests (p_array_request_id => l_javacp_request_id,
580 p_error_status => l_error_status,
581 p_warning_status => l_warning_status
582 );
583 print_logfile ( 'Number of java cp launched for Ledger '
584 || g_seq_context_value (i)
585 || ' '
586 || 'is/are :::'
587 || ' '
588 || l_javacp_request_id.COUNT
589 );
590 l_javacp_request_id.DELETE;
591 n := 0;
592 END LOOP;
593 END IF;
594 END IF;
595 EXCEPTION
596 WHEN OTHERS
597 THEN
598 print_logfile (SQLERRM);
599 END;
600
601 /*-------------------------------------------------------------------------
602 -- Commit is required after fnd_request.submit_request
603 -------------------------------------------------------------------------
604 COMMIT;
605 -------------------------------------------------------------------------
606 -- wait for requests to complete
607 -------------------------------------------------------------------------
608 wait_for_requests (p_array_request_id => l_javacp_request_id,
609 p_error_status => l_error_status,
610 p_warning_status => l_warning_status
611 );
612 ---------------------------------------------------------------------------
613 -- Delete rows from table for ranges which are having status 'SUCCESSFUL'
614 --------------------------------------------------------------------------- */
615 DELETE FROM xla_fsah_ccid_ranges
616 WHERE status_message = 'SUCCESSFUL';
617
618 COMMIT;
619 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
620 || ' - Ending of the Retrive CCID Information'
621 );
622 EXCEPTION
623 WHEN xla_exceptions_pkg.application_exception
624 THEN
625 RAISE;
626 WHEN OTHERS
627 THEN
628 xla_accounting_err_pkg.build_message
629 (p_appli_s_name => 'XLA',
630 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
631 p_token_1 => 'APPLICATION_NAME',
632 p_value_1 => 'SLA',
633 p_entity_id => NULL,
634 p_event_id => NULL
635 );
636 print_logfile ('Error occured in get_ccid_information');
637 xla_exceptions_pkg.raise_message
638 (p_location => 'xla_retrive_ccid_pkg.get_ccid_information');
639 END get_ccid_information;
640
641 --============================================================================================
642 -- Procedure for getting CCIDs for the given range i.e CCIDs between min_num and max_num
643 -- This procedure is calling from Java Concurrent Program
644 --============================================================================================
645 PROCEDURE get_thread_ccid_inf (
646 p_min_num IN NUMBER,
647 p_max_num IN NUMBER,
648 p_out_ccid OUT NOCOPY t_xla_array_ccid_inf,
649 p_application_id IN NUMBER,
650 p_acc_batch_id IN NUMBER,
651 p_ledger_id IN NUMBER,
652 p_parent_request_id IN NUMBER
653 )
654 IS
655 l_thread_ccid_inf t_xla_array_ccid_inf;
656 l_log_module VARCHAR2 (240);
657 BEGIN
658 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
659 || ' - Starting of the Retrive Thread CCID Information'
660 );
661
662 IF g_log_enabled
663 THEN
664 l_log_module := c_default_module || '.get_thread_ccid_inf ';
665 END IF;
666
667 IF (c_level_procedure >= g_log_level)
668 THEN
669 TRACE (p_msg => 'BEGIN of procedure GET_THREAD_CCID_INF',
670 p_level => c_level_procedure,
671 p_module => l_log_module
672 );
673 TRACE (p_msg => 'p_min_num = ' || TO_CHAR (p_min_num),
674 p_level => c_level_procedure,
675 p_module => l_log_module
676 );
677 TRACE (p_msg => 'p_max_num = ' || TO_CHAR (p_max_num),
678 p_level => c_level_procedure,
679 p_module => l_log_module
680 );
681 TRACE (p_msg => 'application_id = '
682 || TO_CHAR (p_application_id),
683 p_level => c_level_procedure,
684 p_module => l_log_module
685 );
686 TRACE (p_msg => 'ledger_id = ' || TO_CHAR (p_ledger_id),
687 p_level => c_level_procedure,
688 p_module => l_log_module
689 );
690 TRACE (p_msg => 'accounting_batch_id = '
691 || TO_CHAR (p_acc_batch_id),
692 p_level => c_level_procedure,
693 p_module => l_log_module
694 );
695 END IF;
696
697 IF (p_min_num <> 0 AND p_max_num <> 0)
698 THEN
699 SELECT xla_array_ccid_inf (e.id_flex_structure_name,
700 e.chart_of_accounts_id,
701 e.code_combination_id,
702 e.segment1,
703 e.seg1_name,
704 e.segment2,
705 e.seg2_name,
706 e.segment3,
707 e.seg3_name,
708 e.segment4,
709 e.seg4_name,
710 e.segment5,
711 e.seg5_name,
712 e.segment6,
713 e.seg6_name,
714 e.segment7,
715 e.seg7_name,
716 e.segment8,
717 e.seg8_name,
718 e.segment9,
719 e.seg9_name,
720 e.segment10,
721 e.seg10_name,
722 e.segment11,
723 e.seg11_name,
724 e.segment12,
725 e.seg12_name,
726 e.segment13,
727 e.seg13_name,
728 e.segment14,
729 e.seg14_name,
730 e.segment15,
731 e.seg15_name,
732 e.segment16,
733 e.seg16_name,
734 e.segment17,
735 e.seg17_name,
736 e.segment18,
737 e.seg18_name,
738 e.segment19,
739 e.seg19_name,
740 e.segment20,
741 e.seg20_name,
742 e.segment21,
743 e.seg21_name,
744 e.segment22,
745 e.seg22_name,
746 e.segment23,
747 e.seg23_name,
748 e.segment24,
749 e.seg24_name,
750 e.segment25,
751 e.seg25_name,
752 e.segment26,
753 e.seg26_name,
754 e.segment27,
755 e.seg27_name,
756 e.segment28,
757 e.seg28_name,
758 e.segment29,
759 e.seg29_name,
760 e.segment30,
761 e.seg30_name
762 )
763 BULK COLLECT INTO l_thread_ccid_inf
764 FROM (SELECT ffsv.id_flex_structure_name, gcc.chart_of_accounts_id,
765 gcc.code_combination_id, gcc.segment1,
766 'SEGMENT1' seg1_name, gcc.segment2,
767 'SEGMENT2' seg2_name, gcc.segment3,
768 'SEGMENT3' seg3_name, gcc.segment4,
769 'SEGMENT4' seg4_name, gcc.segment5,
770 'SEGMENT5' seg5_name, gcc.segment6,
771 'SEGMENT6' seg6_name, gcc.segment7,
772 'SEGMENT7' seg7_name, gcc.segment8,
773 'SEGMENT8' seg8_name, gcc.segment9,
774 'SEGMENT9' seg9_name, gcc.segment10,
775 'SEGMENT10' seg10_name, gcc.segment11,
776 'SEGMENT11' seg11_name, gcc.segment12,
777 'SEGMENT12' seg12_name, gcc.segment13,
778 'SEGMENT13' seg13_name, gcc.segment14,
779 'SEGMENT14' seg14_name, gcc.segment15,
780 'SEGMENT15' seg15_name, gcc.segment16,
781 'SEGMENT16' seg16_name, gcc.segment17,
782 'SEGMENT17' seg17_name, gcc.segment18,
783 'SEGMENT18' seg18_name, gcc.segment19,
784 'SEGMENT19' seg19_name, gcc.segment20,
785 'SEGMENT20' seg20_name, gcc.segment21,
786 'SEGMENT21' seg21_name, gcc.segment22,
787 'SEGMENT22' seg22_name, gcc.segment23,
788 'SEGMENT23' seg23_name, gcc.segment24,
789 'SEGMENT24' seg24_name, gcc.segment25,
790 'SEGMENT25' seg25_name, gcc.segment26,
791 'SEGMENT26' seg26_name, gcc.segment27,
792 'SEGMENT27' seg27_name, gcc.segment28,
793 'SEGMENT28' seg28_name, gcc.segment29,
794 'SEGMENT29' seg29_name, gcc.segment30,
795 'SEGMENT30' seg30_name
796 FROM gl_code_combinations gcc,
797 fnd_id_flex_structures_vl ffsv
798 WHERE gcc.chart_of_accounts_id = ffsv.id_flex_num
799 AND ffsv.application_id = 101
800 AND ffsv.id_flex_code = 'GL#'
801 AND gcc.code_combination_id IN (
802 SELECT code_combination_id
803 FROM xla_ae_lines
804 WHERE ae_header_id IN (
805 SELECT ae_header_id
806 FROM xla_ae_headers
807 WHERE accounting_batch_id =
808 p_acc_batch_id
809 AND application_id = p_application_id
810 AND ledger_id = p_ledger_id))) e
811 WHERE e.code_combination_id BETWEEN p_min_num AND p_max_num;
812 ELSE
813 SELECT xla_array_ccid_inf (e.id_flex_structure_name,
814 e.chart_of_accounts_id,
815 e.code_combination_id,
816 e.segment1,
817 e.seg1_name,
818 e.segment2,
819 e.seg2_name,
820 e.segment3,
821 e.seg3_name,
822 e.segment4,
823 e.seg4_name,
824 e.segment5,
825 e.seg5_name,
826 e.segment6,
827 e.seg6_name,
828 e.segment7,
829 e.seg7_name,
830 e.segment8,
831 e.seg8_name,
832 e.segment9,
833 e.seg9_name,
834 e.segment10,
835 e.seg10_name,
836 e.segment11,
837 e.seg11_name,
838 e.segment12,
839 e.seg12_name,
840 e.segment13,
841 e.seg13_name,
842 e.segment14,
843 e.seg14_name,
844 e.segment15,
845 e.seg15_name,
846 e.segment16,
847 e.seg16_name,
848 e.segment17,
849 e.seg17_name,
850 e.segment18,
851 e.seg18_name,
852 e.segment19,
853 e.seg19_name,
854 e.segment20,
855 e.seg20_name,
856 e.segment21,
857 e.seg21_name,
858 e.segment22,
859 e.seg22_name,
860 e.segment23,
861 e.seg23_name,
862 e.segment24,
863 e.seg24_name,
864 e.segment25,
865 e.seg25_name,
866 e.segment26,
867 e.seg26_name,
868 e.segment27,
869 e.seg27_name,
870 e.segment28,
871 e.seg28_name,
872 e.segment29,
873 e.seg29_name,
874 e.segment30,
875 e.seg30_name
876 )
877 BULK COLLECT INTO l_thread_ccid_inf
878 FROM (SELECT ffsv.id_flex_structure_name, gcc.chart_of_accounts_id,
879 gcc.code_combination_id, gcc.segment1,
880 'SEGMENT1' seg1_name, gcc.segment2,
881 'SEGMENT2' seg2_name, gcc.segment3,
882 'SEGMENT3' seg3_name, gcc.segment4,
883 'SEGMENT4' seg4_name, gcc.segment5,
884 'SEGMENT5' seg5_name, gcc.segment6,
885 'SEGMENT6' seg6_name, gcc.segment7,
886 'SEGMENT7' seg7_name, gcc.segment8,
887 'SEGMENT8' seg8_name, gcc.segment9,
888 'SEGMENT9' seg9_name, gcc.segment10,
889 'SEGMENT10' seg10_name, gcc.segment11,
890 'SEGMENT11' seg11_name, gcc.segment12,
891 'SEGMENT12' seg12_name, gcc.segment13,
892 'SEGMENT13' seg13_name, gcc.segment14,
893 'SEGMENT14' seg14_name, gcc.segment15,
894 'SEGMENT15' seg15_name, gcc.segment16,
895 'SEGMENT16' seg16_name, gcc.segment17,
896 'SEGMENT17' seg17_name, gcc.segment18,
897 'SEGMENT18' seg18_name, gcc.segment19,
898 'SEGMENT19' seg19_name, gcc.segment20,
899 'SEGMENT20' seg20_name, gcc.segment21,
900 'SEGMENT21' seg21_name, gcc.segment22,
901 'SEGMENT22' seg22_name, gcc.segment23,
902 'SEGMENT23' seg23_name, gcc.segment24,
903 'SEGMENT24' seg24_name, gcc.segment25,
904 'SEGMENT25' seg25_name, gcc.segment26,
905 'SEGMENT26' seg26_name, gcc.segment27,
906 'SEGMENT27' seg27_name, gcc.segment28,
907 'SEGMENT28' seg28_name, gcc.segment29,
908 'SEGMENT29' seg29_name, gcc.segment30,
909 'SEGMENT30' seg30_name
910 FROM gl_code_combinations gcc,
911 fnd_id_flex_structures_vl ffsv
912 WHERE gcc.chart_of_accounts_id = ffsv.id_flex_num
913 AND ffsv.application_id = 101
914 AND ffsv.id_flex_code = 'GL#'
915 AND gcc.code_combination_id IN (
916 SELECT code_combination_id
917 FROM xla_ae_lines
918 WHERE ae_header_id IN (
919 SELECT ae_header_id
920 FROM xla_ae_headers
921 WHERE accounting_batch_id =
922 p_acc_batch_id
923 AND application_id = p_application_id
924 AND ledger_id = p_ledger_id))) e;
925 END IF;
926
927 p_out_ccid := l_thread_ccid_inf;
928 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
929 || ' - End of the Retrive Thread CCID Information'
930 );
931 EXCEPTION
932 WHEN xla_exceptions_pkg.application_exception
933 THEN
934 RAISE;
935 WHEN OTHERS
936 THEN
937 xla_accounting_err_pkg.build_message
938 (p_appli_s_name => 'XLA',
939 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
940 p_token_1 => 'APPLICATION_NAME',
941 p_value_1 => 'SLA',
942 p_entity_id => NULL,
943 p_event_id => NULL
944 );
945 print_logfile ('Error occured in get_thread_ccid_inf');
946 xla_exceptions_pkg.raise_message
947 (p_location => 'xla_retrive_ccid_pkg.get_thread_ccid_inf');
948 END get_thread_ccid_inf;
949
950 --============================================================================================
951 -- Procedure for getting CCID Sequence Numbers
952 -- This Procedure is calling from Java Concurrent program
953 --============================================================================================
954 PROCEDURE get_ccid_seq (
955 p_coa_num IN NUMBER,
956 p_ccid_seq_out OUT NOCOPY t_xla_array_ccid_seq_inf
957 )
958 IS
959 l_log_module VARCHAR2 (240);
960 BEGIN
961 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
962 || ' - Starting of the Retrive CCID Sequence Information'
963 );
964
965 IF g_log_enabled
966 THEN
967 l_log_module := c_default_module || '.get_ccid_seq ';
968 END IF;
969
970 IF (c_level_procedure >= g_log_level)
971 THEN
972 TRACE (p_msg => 'BEGIN of procedure GET_CCID_SEQ',
973 p_level => c_level_procedure,
974 p_module => l_log_module
975 );
976 TRACE (p_msg => 'p_coa_num = ' || TO_CHAR (p_coa_num),
977 p_level => c_level_procedure,
978 p_module => l_log_module
979 );
980 END IF;
981
982 SELECT xla_array_ccid_seq_inf (e.application_column_name, e.segment_num)
983 BULK COLLECT INTO p_ccid_seq_out
984 FROM fnd_id_flex_segments e
985 WHERE e.id_flex_code = 'GL#' AND e.id_flex_num = p_coa_num;
986
987 print_logfile ( TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
988 || ' - Ending of the Retrive CCID Sequence Information'
989 );
990 EXCEPTION
991 WHEN xla_exceptions_pkg.application_exception
992 THEN
993 RAISE;
994 WHEN OTHERS
995 THEN
996 xla_accounting_err_pkg.build_message
997 (p_appli_s_name => 'XLA',
998 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
999 p_token_1 => 'APPLICATION_NAME',
1000 p_value_1 => 'SLA',
1001 p_entity_id => NULL,
1002 p_event_id => NULL
1003 );
1004 print_logfile ('Error occured in get_ccid_seq');
1005 xla_exceptions_pkg.raise_message
1006 (p_location => 'xla_retrive_ccid_pkg.get_ccid_seq');
1007 END get_ccid_seq;
1008
1009 --=============================================================================
1010 --
1011 -- Procedure for Wait for request
1012 --
1013 --=============================================================================
1014 PROCEDURE wait_for_requests (
1015 p_array_request_id IN t_array_number,
1016 p_error_status OUT NOCOPY VARCHAR2,
1017 p_warning_status OUT NOCOPY VARCHAR2
1018 )
1019 IS
1020 l_phase VARCHAR2 (30);
1021 l_status VARCHAR2 (30);
1022 l_dphase VARCHAR2 (30);
1023 l_dstatus VARCHAR2 (30);
1024 l_message VARCHAR2 (240);
1025 l_btemp BOOLEAN;
1026 l_log_module VARCHAR2 (240);
1027 BEGIN
1028 IF g_log_enabled
1029 THEN
1030 l_log_module := c_default_module || '.wait_for_requests';
1031 END IF;
1032
1033 IF (c_level_procedure >= g_log_level)
1034 THEN
1035 TRACE (p_msg => 'BEGIN of procedure WAIT_FOR_REQUESTS',
1036 p_level => c_level_procedure,
1037 p_module => l_log_module
1038 );
1039 END IF;
1040
1041 ----------------------------------------------------------------------------
1042 -- Waiting for active/pending requests to complete
1043 ----------------------------------------------------------------------------
1044 IF p_array_request_id.COUNT > 0
1045 THEN
1046 FOR i IN 1 .. p_array_request_id.COUNT
1047 LOOP
1048 IF (c_level_statement >= g_log_level)
1049 THEN
1050 TRACE (p_msg => 'waiting for request id = '
1051 || p_array_request_id (i),
1052 p_level => c_level_statement,
1053 p_module => l_log_module
1054 );
1055 END IF;
1056
1057 l_btemp :=
1058 fnd_concurrent.wait_for_request
1059 (request_id => p_array_request_id
1060 (i),
1061 INTERVAL => 30,
1062 phase => l_phase,
1063 status => l_status,
1064 dev_phase => l_dphase,
1065 dev_status => l_dstatus,
1066 MESSAGE => l_message
1067 );
1068
1069 UPDATE xla_fsah_ccid_ranges
1070 SET status_message = l_message
1071 WHERE request_id = p_array_request_id (i);
1072
1073 IF NOT l_btemp
1074 THEN
1075 xla_accounting_err_pkg.build_message
1076 (p_appli_s_name => 'XLA',
1077 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
1078 p_token_1 => 'APPLICATION_NAME',
1079 p_value_1 => 'SLA',
1080 p_entity_id => NULL,
1081 p_event_id => NULL
1082 );
1083 print_logfile
1084 ( 'Technical problem : FND_CONCURRENT.WAIT_FOR_REQUEST returned FALSE '
1085 || 'while executing for request id '
1086 || p_array_request_id (i)
1087 );
1088 ELSE
1089 IF (c_level_event >= g_log_level)
1090 THEN
1091 TRACE (p_msg => 'request completed with status = '
1092 || l_status,
1093 p_level => c_level_event,
1094 p_module => l_log_module
1095 );
1096 END IF;
1097
1098 IF l_dstatus = 'WARNING'
1099 THEN
1100 p_warning_status := 'Y';
1101
1102 UPDATE xla_fsah_ccid_ranges
1103 SET status_message = l_message
1104 WHERE request_id = p_array_request_id (i);
1105 ELSIF l_dstatus = 'ERROR'
1106 THEN
1107 p_error_status := 'Y';
1108
1109 UPDATE xla_fsah_ccid_ranges
1110 SET status_message = l_message
1111 WHERE request_id = p_array_request_id (i);
1112 END IF;
1113
1114 COMMIT;
1115 END IF;
1116 END LOOP;
1117 END IF;
1118
1119 IF (c_level_procedure >= g_log_level)
1120 THEN
1121 TRACE (p_msg => 'END of procedure WAIT_FOR_REQUESTS',
1122 p_level => c_level_procedure,
1123 p_module => l_log_module
1124 );
1125 END IF;
1126 EXCEPTION
1127 WHEN xla_exceptions_pkg.application_exception
1128 THEN
1129 RAISE;
1130 WHEN OTHERS
1131 THEN
1132 xla_exceptions_pkg.raise_message
1133 (p_location => 'xla_accounting_pkg.wait_for_requests');
1134 END wait_for_requests;
1135
1136 -- end of procedure
1137
1138 --=============================================================================
1139 --
1140 --
1141 --
1142 --=============================================================================
1143 PROCEDURE wait_for_sing_req (
1144 p_request_id IN NUMBER,
1145 p_error_status OUT NOCOPY VARCHAR2,
1146 p_warning_status OUT NOCOPY VARCHAR2
1147 )
1148 IS
1149 l_phase VARCHAR2 (30);
1150 l_status VARCHAR2 (30);
1151 l_dphase VARCHAR2 (30);
1152 l_dstatus VARCHAR2 (30);
1153 l_message VARCHAR2 (240);
1154 l_btemp BOOLEAN;
1155 l_log_module VARCHAR2 (240);
1156 BEGIN
1157 IF g_log_enabled
1158 THEN
1159 l_log_module := c_default_module || '.wait_for_combo_edit_req';
1160 END IF;
1161
1162 IF (c_level_procedure >= g_log_level)
1163 THEN
1164 TRACE (p_msg => 'BEGIN of procedure WAIT_FOR_COMBO_EDIT_REQ',
1165 p_level => c_level_procedure,
1166 p_module => l_log_module
1167 );
1168 END IF;
1169
1170 ----------------------------------------------------------------------------
1171 -- Waiting for active/pending requests to complete
1172 ----------------------------------------------------------------------------
1173 IF p_request_id <> 0
1174 THEN
1175 l_btemp :=
1176 fnd_concurrent.wait_for_request (request_id => p_request_id,
1177 INTERVAL => 30,
1178 phase => l_phase,
1179 status => l_status,
1180 dev_phase => l_dphase,
1181 dev_status => l_dstatus,
1182 MESSAGE => l_message
1183 );
1184
1185 IF NOT l_btemp
1186 THEN
1187 xla_accounting_err_pkg.build_message
1188 (p_appli_s_name => 'XLA',
1189 p_msg_name => 'XLA_AP_TECHNICAL_ERROR',
1190 p_token_1 => 'APPLICATION_NAME',
1191 p_value_1 => 'SLA',
1192 p_entity_id => NULL,
1193 p_event_id => NULL
1194 );
1195 print_logfile
1196 ( 'Technical problem : FND_CONCURRENT.WAIT_FOR_REQUEST returned FALSE '
1197 || 'while executing for request id '
1198 || p_request_id
1199 );
1200 ELSE
1201 IF (c_level_event >= g_log_level)
1202 THEN
1203 TRACE (p_msg => 'request completed with status = '
1204 || l_status,
1205 p_level => c_level_event,
1206 p_module => l_log_module
1207 );
1208 END IF;
1209
1210 IF l_dstatus = 'WARNING'
1211 THEN
1212 p_warning_status := 'Y';
1213 ELSIF l_dstatus = 'ERROR'
1214 THEN
1215 p_error_status := 'Y';
1216 END IF;
1217 END IF;
1218 END IF;
1219
1220 IF (c_level_procedure >= g_log_level)
1221 THEN
1222 TRACE (p_msg => 'END of procedure WAIT_FOR_SING_REQUESTS',
1223 p_level => c_level_procedure,
1224 p_module => l_log_module
1225 );
1226 END IF;
1227 EXCEPTION
1228 WHEN xla_exceptions_pkg.application_exception
1229 THEN
1230 RAISE;
1231 WHEN OTHERS
1232 THEN
1233 xla_exceptions_pkg.raise_message
1234 (p_location => 'xla_accounting_pkg.wait_for_combo_edit_req');
1235 END wait_for_sing_req; -- end of procedure
1236 BEGIN
1237 g_log_level := fnd_log.g_current_runtime_level;
1238 g_log_enabled :=
1239 fnd_log.TEST (log_level => g_log_level,
1240 module => c_default_module);
1241
1242 IF NOT g_log_enabled
1243 THEN
1244 g_log_level := c_level_log_disabled;
1245 END IF;
1246 END xla_retrive_ccid_pkg;