[Home] [Help]
PACKAGE BODY: APPS.FUN_SEQ_BATCH
Source
1 PACKAGE BODY fun_seq_batch AS
2 /* $Header: funsqbtb.pls 120.43.12010000.4 2010/01/18 12:17:35 degoel ship $ */
3 --
4 -- For debuggin
5 --
6 g_module CONSTANT VARCHAR2(30) := 'fun.plsql.fun_seq_batch';
7 -- added global variable for sort by GL Date at posting event
8 g_sort_option_code fun_seq_contexts.sort_option%TYPE;
9 -- PROCEDURE NAME:
10 -- Batch_Init
11 -- *** For XLA Accounting Program ***
12 -- DESCRIPTION:
13 -- Populate Sequencing setup data in fun_seq_request
14 -- This procedure is called from Accounting and Reporting Sequencing
15 -- Program.
16 -- INPUT:
17 -- - p_application_id
18 -- Application Id of your Sequence Entity.
19 -- - p_table_name
20 -- Table Name of your Sequence Entity.
21 -- - p_event_code
22 -- Sequence Event Code
23 -- - p_context_type
24 -- Sequence Context Type. Only 'LEDGER_AND_CURRENCY' is supported
25 -- for Accounting Sequencing.
26 -- - p_context_value_tbl
27 -- Sequence Context Value. Only Ledger ID is supported for
28 -- Accounting Sequencing.
29 -- - p_request_id
30 -- the request ID of current process
31 -- OUTPUT:
32 -- - x_status
33 -- the status of current processing
34 -- SUCCESS - At least one active sequencing context was locked
35 -- NO_SEQUENCING - No sequencing context was found for the input
36 -- - x_seq_context_id
37 -- Sequence Context ID found based on the provided input.
38 -- Meaningful only when a single context value is passed in.
39 --
40 PROCEDURE Batch_Init(
41 p_application_id IN NUMBER,
42 p_table_name IN VARCHAR2,
43 p_event_code IN VARCHAR2,
44 p_context_type IN VARCHAR2,
45 p_context_value_tbl IN context_value_tbl_type,
46 p_request_id IN NUMBER,
47 x_status OUT NOCOPY VARCHAR2,
48 x_seq_context_id OUT NOCOPY NUMBER)
49 IS
50 PRAGMA AUTONOMOUS_TRANSACTION;
51 l_seq_context_id fun_seq_contexts.seq_context_id%TYPE;
52 l_control_date_type fun_seq_contexts.date_type%TYPE;
53 l_req_assign_flag fun_seq_contexts.require_assign_flag%TYPE;
54 l_sort_option_code fun_seq_contexts.sort_option%TYPE;
55 l_sequence_type fun_seq_headers.gapless_flag%TYPE;
56
57 l_seq_context_found BOOLEAN DEFAULT FALSE;
58 l_module CONSTANT VARCHAR2(100) DEFAULT
59 g_module || '.' || 'batch_init';
60 BEGIN
61 --
62 -- Debug Info Begin
63 --
64 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
65 fnd_log.string(
66 log_level => fnd_log.level_procedure,
67 module => l_module || '.' || 'begin',
68 message =>
69 'p_application_id => ' || p_application_id || ', ' ||
70 'p_table_name => ' || p_table_name || ', ' ||
71 'p_event_code => ' || p_event_code || ', ' ||
72 'p_context_type => ' || p_context_type || ', ' ||
73 'p_context_value => ' || p_context_value_tbl.FIRST || ', ' ||
74 'p_request_id => ' || p_request_id);
75 END IF;
76 --
77 -- Release the lock of Sequencing Setup Data
78 -- of completed concurrent requests
79 --
80 delete_seq_requests (p_request_id => null);
81 --
82 -- Retrieve Sequencing Context Information
83 --
84 IF p_context_value_tbl.COUNT > 0 THEN
85 FOR i IN p_context_value_tbl.FIRST .. p_context_value_tbl.LAST LOOP
86 fun_seq.get_assign_context_info(
87 p_context_type => p_context_type,
88 p_context_value => p_context_value_tbl(i),
89 p_application_id => p_application_id,
90 p_table_name => p_table_name,
91 p_event_code => p_event_code,
92 p_request_id => p_request_id,
93 x_seq_context_id => l_seq_context_id,
94 x_control_date_type => l_control_date_type,
95 x_req_assign_flag => l_req_assign_flag,
96 x_sort_option_code => l_sort_option_code);
97
98 --
99 -- Create Sequencing Setup Records in FUN_SEQ_REQUESTS
100 --
101 IF l_seq_context_id IS NOT NULL THEN
102 --
103 -- Make Sequencing Setup pages display only
104 --
105 populate_seq_requests(
106 p_request_id => p_request_id,
107 p_seq_context_id => l_seq_context_id);
108
109 IF NOT l_seq_context_found THEN
110 l_seq_context_found := TRUE;
111 END IF;
112
113 END IF;
114 END LOOP;
115 END IF; -- p_context_value_tbl.COUNT > 0
116
117 --
118 -- Set return values
119 --
120 IF l_seq_context_found THEN
121 x_status := 'SUCCESS';
122 x_seq_context_id := l_seq_context_id;
123 ELSE
124 x_status := 'NO_SEQUENCING';
125 END IF;
126 --
127 -- Autonomous Commit
128 --
129 COMMIT;
130 --
131 -- Debug Info End
132 --
133 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
134 fnd_log.string(
135 log_level => fnd_log.level_procedure,
136 module => l_module || '.' || 'end',
137 message => 'x_status => ' || x_status || ', ' ||
138 'x_seq_context_id => ' || x_seq_context_id);
139 END IF;
140 EXCEPTION
141 WHEN OTHERS THEN
142 --
143 -- Logging
144 --
145 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
146 fnd_log.string (
147 log_level => fnd_log.level_error,
148 module => l_module,
149 message => 'SQLERRM: ' || SQLERRM);
150 END IF;
151 --
152 -- Raise Exception
153 --
154 app_exception.raise_exception;
155 END Batch_Init;
156
157 -- PROCEDURE NAME:
158 -- Batch_Init
159 -- DESCRIPTION:
160 -- Populate Sequencing setup data in fun_seq_request
161 -- **** For GL Posting Program ****
162 PROCEDURE Batch_Init(
163 p_request_id IN NUMBER,
164 p_ledgers_tbl IN num15_tbl_type,
165 x_ledgers_locked_tbl OUT NOCOPY num15_tbl_type,
166 x_ledgers_locked_cnt OUT NOCOPY NUMBER) IS
167
168 PRAGMA AUTONOMOUS_TRANSACTION;
169 l_seq_context_id fun_seq_contexts.seq_context_id%TYPE;
170 l_date_type fun_seq_contexts.date_type%TYPE;
171 l_req_assign_flag fun_seq_contexts.require_assign_flag%TYPE;
172 l_sort_option_code fun_seq_contexts.sort_option%TYPE;
173 l_assign_id_tbl assign_id_tbl_type;
174 l_seq_type_tbl seq_type_tbl_type;
175 l_seq_head_id_tbl seq_head_id_tbl_type;
176
177 l_module CONSTANT VARCHAR2(100) DEFAULT
178 g_module || '.' || 'batch_init';
179 BEGIN
180 --
181 -- Debug Info
182 --
183 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
184 fnd_log.string(
185 log_level => fnd_log.level_procedure,
186 module => l_module || '.' || 'begin',
187 message => 'p_request_id => ' || p_request_id);
188 END IF;
189 --
190 -- Release the lock of Sequencing Setup Data
191 -- of completed concurrent requests
192 --
193 delete_seq_requests (p_request_id => null);
194 --
195 --
196 --
197 IF p_ledgers_tbl.COUNT > 0 THEN
198 FOR i IN p_ledgers_tbl.FIRST .. p_ledgers_tbl.LAST LOOP
199 --
200 -- Retrieve Sequencing Context Information
201 --
202 fun_seq.get_assign_context_info(
203 p_context_type => 'LEDGER_AND_CURRENCY',
204 p_context_value => p_ledgers_tbl(i),
205 p_application_id => 101,
206 p_table_name => 'GL_JE_HEADERS',
207 p_event_code => 'POSTING',
208 p_request_id => NULL, -- Don't use cache. Not locked yet.
209 x_seq_context_id => l_seq_context_id,
210 x_control_date_type => l_date_type,
211 x_req_assign_flag => l_req_assign_flag,
212 x_sort_option_code => l_sort_option_code);
213
214 --
215 -- If a valid sequencing context is found,
216 -- insert a record into fun_seq_requests.
217 --
218 IF l_seq_context_id IS NOT NULL THEN
219 populate_seq_requests (
220 p_request_id => p_request_id,
221 p_seq_context_id => l_seq_context_id);
222 --
223 -- Set the locked Ledger Ids to the parameter
224 --
225 x_ledgers_locked_tbl(i) := p_ledgers_tbl(i);
226 END IF;
227 END LOOP;
228 END IF;
229
230 --
231 -- Set the number of locked Ledgers to the parameter
232 --
233 x_ledgers_locked_cnt := x_ledgers_locked_tbl.COUNT;
234
235 COMMIT;
236 --
237 -- Debug Info
238 --
239 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
240 fnd_log.string(
241 log_level => fnd_log.level_procedure,
242 module => l_module || '.' || 'end',
243 message => 'x_ledgers_locked_cnt => ' || x_ledgers_locked_cnt);
244 END IF;
245 EXCEPTION
246 WHEN OTHERS THEN
247 --
248 -- Logging
249 --
250 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
251 fnd_log.string(
252 log_level => fnd_log.level_exception,
253 module => l_module,
254 message => 'p_request_id: ' || p_request_id || ', ' ||
255 'SQLERRM: ' || SQLERRM);
256 END IF;
257 --
258 -- Rollback transctions. As the autonomous transaction pragma is
259 -- specified, transactions outside this procedure are not affected.
260 --
261 ROLLBACK;
262 --
263 -- Raise Exception
264 --
265 app_exception.raise_exception;
266 END Batch_Init;
267
268 --
269 -- Procedure Name: Batch_Exit
270 -- Description:
271 -- Unlocks setup data by deleting records from fun_seq_requests
272 -- The exception in this program is not considered critical.
273 -- So, even if the caller receives 'FAILURE', they should not
274 -- raise an exception.
275 -- INPUT
276 -- p_request_id
277 -- the request ID of current process
278 -- OUTPUT
279 -- x_status
280 -- the status of current processing
281 -- SUCCESS setup data were unlocked successfully
282 -- FAILURE unexpected error occured during unlocking
283 PROCEDURE Batch_Exit(
284 p_request_id IN NUMBER,
285 x_status OUT NOCOPY VARCHAR2)
286 IS
287 PRAGMA AUTONOMOUS_TRANSACTION;
288
289 l_module CONSTANT VARCHAR2(100) DEFAULT g_module || '.' || 'batch_exit';
290
291 BEGIN
292 --
293 -- Debug Info - Begin
294 --
295 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
296 fnd_log.string(
297 log_level => fnd_log.level_procedure,
298 module => l_module || '.' || 'begin',
299 message => 'p_request_id => ' || p_request_id);
300 END IF;
301 --
302 -- Release the lock of Sequencing Setup Data
303 -- of completed concurrent requests
304 --
305 delete_seq_requests (p_request_id => p_request_id);
306 --
307 -- Return Status
308 --
309 x_status := 'SUCCESS';
310 --
311 -- Automnomous Commit
312 --
313 COMMIT;
314
315 -- Debug Info - End
316 --
317 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
318 fnd_log.string(
319 log_level => fnd_log.level_procedure,
320 module => l_module || '.'|| 'end',
321 message => 'x_status => ' || x_status);
322 END IF;
323 EXCEPTION
324 WHEN OTHERS THEN
325 --
326 -- Logging
327 --
328 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
329 fnd_log.string(
330 log_level => fnd_log.level_exception,
331 module => l_module,
332 message => 'p_request_id: ' || p_request_id || ', ' ||
333 'SQLERRM: ' || SQLERRM);
334 END IF;
335 --
336 -- Send Alert to the OAM and set the request status to Warning
337 --
338 x_status := 'FAILURE';
339 END Batch_Exit;
340
341 --
342 -- PROCEDURE NAME:
343 -- Generate_Bulk_Numbers
344 -- 1. returns the sequence numbers and ae_header_IDs for records with the same
345 -- assignment ID and sequence version ID
346 -- 2. updates the current number for the sequence version
347 --
348 -- Used in Subledger Accounting Program and GL Posting Program
349 --
350 -- UNIT 1
351 -- Ae_header_Id Seq_Ver_Id Assignment_Id
352 -- 1 2 200
353 -- 2 2 200
354 -- UNIT 2
355 -- 3 3 100
356 -- 4 3 100
357 -- 5 3 100
358 -- UNIT 3
359 -- 6 3 300
360
361 PROCEDURE Generate_Bulk_Numbers(
362 p_request_id IN NUMBER,
363 p_seq_ver_id_tbl IN seq_ver_id_tbl_type,
364 p_assign_id_tbl IN assign_id_tbl_type,
365 x_seq_value_tbl OUT NOCOPY seq_value_tbl_type,
366 x_seq_date_tbl OUT NOCOPY date_tbl_type) IS
367
368 ind_prior BINARY_INTEGER;
369 ind_next BINARY_INTEGER;
370
371 l_seq_ver_id_tbl seq_ver_id_tbl_type;
372 l_assign_id_tbl assign_id_tbl_type;
373
374 l_current_value fun_seq_versions.current_value%TYPE;
375 l_sequenced_date DATE;
376 l_error_code VARCHAR2(30);
377
378 l_module CONSTANT VARCHAR2(100) DEFAULT
379 g_module || '.' || 'generate_bulk_numbers';
380
381 BEGIN
382 --
383 -- Debug Information
384 --
385 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
386 FND_LOG.STRING(
387 FND_LOG.LEVEL_PROCEDURE,
388 'fun.plsql.fun_seq_batch.generate_bulk_numbers.begin',
389 'Beginning of generate_bulk_numbers');
390 END IF;
391 --
392 -- Hold parameters in local variables
393 --
394 l_seq_ver_id_tbl := p_seq_ver_id_tbl;
395 l_assign_id_tbl := p_assign_id_tbl;
396 --
397 -- Loop for Accounting Entries
398 --
399 IF l_seq_ver_id_tbl.COUNT > 0 THEN
400 FOR i in l_seq_ver_id_tbl.FIRST .. l_seq_ver_id_tbl.LAST LOOP
401 ind_prior := l_assign_id_tbl.PRIOR(i);
402 ind_next := l_assign_id_tbl.NEXT(i);
403 --
404 -- Call Generate Sequence Number for the first record
405 -- ,or when Assignment ID or Sequence Version ID is different
406 -- from the prior record
407 --
408 -- !! Warning !!
409 -- Evaluate "IS NULL" first. l_assign_id_tbl(null) gives an exception
410 IF (ind_prior IS NULL) OR
411 (l_assign_id_tbl(ind_prior) <> l_assign_id_tbl(i) OR
412 l_seq_ver_id_tbl(ind_prior) <> l_seq_ver_id_tbl(i))
413 THEN
414 fun_seq.generate_sequence_number(
415 p_assignment_id => l_assign_id_tbl(i),
416 p_seq_version_id => l_seq_ver_id_tbl(i),
417 p_sequence_type => 'G',
418 p_request_id => NVL(p_request_id,-99),
419 x_sequence_number => l_current_value,
420 x_sequenced_date => l_sequenced_date,
421 x_error_code => l_error_code) ;
422 x_seq_value_tbl(i) := l_current_value;
423 x_seq_date_tbl(i) := l_sequenced_date;
424 ELSE
425 --
426 -- Same Assignment ID and Sequence Version ID
427 -- Increment current value
428 --
429 l_current_value := l_current_value + 1;
430 x_seq_value_tbl(i) := l_current_value;
431 x_seq_date_tbl(i) := l_sequenced_date; -- Number/Date Correlation
432 --
433 -- If this is the last record (ind_next is NULL) of the input parameter
434 -- or of the unit then update DB Current Value of the Sequence
435 -- Version ID.
436 --
437 -- !! Warning !!
438 -- Evaluate "IS NULL" first. l_assign_id_tbl(null) gives an exception
439 --
440 IF ind_next IS NULL OR
441 (l_assign_id_tbl(ind_next) <> l_assign_id_tbl(i) OR
442 l_seq_ver_id_tbl(ind_next) <> l_seq_ver_id_tbl(i))
443 THEN
444 --
445 -- Update the current_value
446 --
447 UPDATE fun_seq_versions
448 SET current_value = l_current_value
449 WHERE seq_version_id = l_seq_ver_id_tbl(i);
450 END IF; -- Update the Last Used Number or Not
451 END IF; -- Call Generate Sequence Number or Not
452 END LOOP;
453 END IF;
454 --
455 -- Debug Information
456 --
457 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
458 FND_LOG.STRING(
459 FND_LOG.LEVEL_PROCEDURE,
460 'fun.plsql.fun_seq_batch.generate_bulk_numbers.end',
461 'Generate_bulk_numbers completes successfully.');
462 END IF;
463 EXCEPTION
464 WHEN OTHERS THEN
465 --
466 -- Logging
467 --
468 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
469 fnd_log.string(
470 log_level => fnd_log.level_exception,
471 module => l_module,
472 message => 'p_request_id: ' || p_request_id ||', ' ||
473 'SQLERRM: ' || SQLERRM);
474 END IF;
475 --
476 -- Raise Exception
477 --
478 app_exception.raise_exception;
479 END Generate_Bulk_Numbers;
480
481 --
482 -- PROCEDURE NAME:
483 -- Populate_Acct_Seq_Info *** For XLA Accounting Program ***
484 -- DESCRIPTION
485 --
486 PROCEDURE Populate_Acct_Seq_Info(
487 p_calling_program IN VARCHAR2,
488 p_request_id IN NUMBER) IS
489
490 no_assigned_seq_info EXCEPTION;
491
492 BEGIN
493 --
494 -- Debug Information
495 --
496 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
497 FND_LOG.STRING(
498 FND_LOG.LEVEL_PROCEDURE,
499 'fun.plsql.fun_seq_batch.populate_acct_seq_info.begin',
500 'Calling fun_seq_batch.populate_acct_seq_info:'||
501 'p_calling_program => ' || p_calling_program ||', ' ||
502 'p_request_id => ' || p_request_id );
503 END IF;
504
505 --
506 -- Populate Sequencing information in Accounting and Reporting
507 -- Sequence Database Objects
508 --
509 IF p_calling_program = 'ACCOUNTING' THEN
510 Populate_Acct_Seq_Prog_View (p_request_id);
511 ELSIF p_calling_program = 'REPORTING' THEN
512 Populate_Rep_Seq_Prog_Gt (p_request_id);
513 END IF;
514
515 --
516 -- Debug Information
517 --
518 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
519 FND_LOG.STRING(
520 FND_LOG.LEVEL_PROCEDURE,
521 'fun.plsql.fun_seq_batch.populate_acct_seq_info.end',
522 'Calling fun_seq_batch.populate_acct_seq_info:'||
523 'p_calling_program => ' || p_calling_program ||', ' ||
524 'p_request_id => ' || p_request_id );
525 END IF;
526 EXCEPTION
527 WHEN no_assigned_seq_info THEN
528 --
529 -- Logging
530 --
531 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
532 fnd_log.string(
533 log_level => fnd_log.level_error,
534 module
535 => 'fun.plsql.fun_seq_batch.populate_acct_seq_info.error',
536 message => 'p_request_id: ' || p_request_id ||', ' ||
537 'SQLERRM: ' || SQLERRM);
538 END IF;
539 --
540 -- Raise Exception
541 --
542 fnd_message.set_name ('FUN','FUN_SEQ_NO_ACTIVE_ASSGN_FOUND');
543 fnd_message.set_token ('SEQ_CONTEXT_NAME',
544 fun_seq.get_seq_context_name(null));
545 app_exception.raise_exception;
546 WHEN OTHERS THEN
547 --
548 -- Logging
549 --
550 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
551 fnd_log.string(
552 log_level => fnd_log.level_exception,
553 module
554 => 'fun.plsql.fun_seq_batch.populate_acct_seq_info.exception',
555 message => 'p_request_id: ' || p_request_id ||', ' ||
556 'SQLERRM: ' || SQLERRM);
557 END IF;
558 --
559 -- Raise Exception
560 --
561 app_exception.raise_exception;
562 END Populate_Acct_Seq_Info;
563 --
564 -- PROCEDURE NAME:
565 -- Populate_Seq_Info *** For GL Posting Program ***
566 -- DESCRIPTION
567 --
568 PROCEDURE Populate_Seq_Info IS
569
570 l_ledger_id_tbl ledger_id_tbl_type;
571 l_je_header_id_tbl je_header_id_tbl_type;
572 l_actual_flag_tbl actual_flag_tbl_type;
573 l_je_source_tbl je_source_tbl_type;
574 l_je_category_tbl je_category_tbl_type;
575 l_gl_date_tbl date_tbl_type;
576
577 l_ctrl_attr_rec fun_seq.control_attribute_rec_type;
578 l_ctrl_date_tbl fun_seq.control_date_tbl_type
579 := fun_seq.control_date_tbl_type();
580
581 l_seq_ver_id_tbl seq_ver_id_tbl_type;
582 l_assign_id_tbl assign_id_tbl_type;
583 l_out_ctrl_dt_tbl date_tbl_type;
584 l_req_assign_flag_tbl req_assign_flag_tbl_type;
585 l_error_code_tbl error_code_tbl_type;
586 l_dummy_tbl vc30_tbl_type; -- For Sort Option
587
588 l_dummy fun_seq_headers.gapless_flag%TYPE;
589 no_assigned_seq_info EXCEPTION;
590
591 l_debug_je_header_id NUMBER;
592 BEGIN
593 --
594 -- Debug Information
595 --
596 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
597 FND_LOG.STRING(
598 FND_LOG.LEVEL_PROCEDURE,
599 'fun.plsql.fun_seq_batch.populate_seq_info.begin',
600 'Beginning of Populate_Seq_Info');
601 END IF;
602 --
603 -- GL date is the only Sequence Control Date Type
604 -- avaialbe for GL Posting Program
605 --
606 l_ctrl_date_tbl.EXTEND(1);
607 l_ctrl_date_tbl(1).date_type := 'GL_DATE';
608 -- gl date is populated within the loop
609
610 --
611 -- Bulk Collect Journal Entry Information from fun_seq_batch_gt
612 --
613 SELECT jh.ledger_id,
614 bg.source_id,
615 jh.actual_flag,
616 jh.je_source,
617 jh.je_category,
618 jh.default_effective_date
619 BULK COLLECT
620 INTO l_ledger_id_tbl,
621 l_je_header_id_tbl,
622 l_actual_flag_tbl,
623 l_je_source_tbl,
624 l_je_category_tbl,
625 l_gl_date_tbl
626 FROM fun_seq_batch_gt bg,
627 gl_je_headers jh
628 WHERE bg.source_id = jh.je_header_id;
629 --
630 -- Loop for journal entries in fun_seq_batch_gt
631 --
632 FOR i IN l_je_header_id_tbl.FIRST .. l_je_header_id_tbl.LAST LOOP
633 l_debug_je_header_id := l_je_header_id_tbl(i);
634 --
635 -- Set Local Variables
636 --
637 l_ctrl_attr_rec.balance_type := l_actual_flag_tbl(i);
638 l_ctrl_attr_rec.journal_source := l_je_source_tbl(i);
639 l_ctrl_attr_rec.journal_category := l_je_category_tbl(i);
640 l_ctrl_date_tbl(1).date_value := l_gl_date_tbl(i);
641 --
642 -- Debug Information
643 --
644 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
645 FND_LOG.STRING(
646 FND_LOG.LEVEL_PROCEDURE,
647 'fun.plsql.fun_seq_batch.populate_seq_info.config',
648 'Calling fun_seq.get_assigned_sequence_info... '
649 ||', '||
650 'l_debug_je_header_id: '
651 || l_debug_je_header_id ||', '||
652 'l_ctrl_date_tbl(1).date_value (GL_DATE): '
653 || l_ctrl_date_tbl(1).date_value ||', '||
654 'l_ctrl_attr_rec.balance_type: '
655 || l_ctrl_attr_rec.balance_type ||', '||
656 'l_ctrl_attr_rec.journal_source: '
657 || l_ctrl_attr_rec.journal_source ||', '||
658 'l_ctrl_attr_rec.journal_category: '
659 || l_ctrl_attr_rec.journal_category
660 );
661 END IF;
662 --
663 -- Get Sequencing Context and Assignment Information
664 --
665 fun_seq.get_assigned_sequence_info(
666 p_context_type => 'LEDGER_AND_CURRENCY',
667 p_context_value => l_ledger_id_tbl(i),
668 p_application_Id => 101,
669 p_table_name => 'GL_JE_HEADERS',
670 p_event_code => 'POSTING',
671 p_control_attribute_rec => l_ctrl_attr_rec,
672 p_control_date_tbl => l_ctrl_date_tbl,
673 p_request_id => -1, -- Use Cache
674 p_suppress_error => 'Y',
675 x_sequence_type => l_dummy,
676 x_seq_version_id => l_seq_ver_id_tbl(i),
677 x_assignment_id => l_assign_id_tbl(i),
678 x_control_date_value => l_out_ctrl_dt_tbl(i), -- Not Used
679 x_req_assign_flag => l_req_assign_flag_tbl(i), -- Not Used
680 x_sort_option_code => g_sort_option_code,
681 x_error_code => l_error_code_tbl(i));
682
683 --
684 -- Debug Information
685 --
686 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
687 FND_LOG.STRING(
688 FND_LOG.LEVEL_PROCEDURE,
689 'fun.plsql.fun_seq_batch.populate_seq_info.config',
690 'Returning from fun_seq.get_assigned_sequence_info... '
691 ||', '||
692 'l_seq_ver_id_tbl(i): ' || l_seq_ver_id_tbl(i) ||', '||
693 'l_assign_id_tbl(i): ' || l_assign_id_tbl(i) ||', '||
694 'l_out_ctrl_dt_tbl(i): '|| l_out_ctrl_dt_tbl(i)||', '||
695 'l_req_assign_flag_tbl(i): '
696 || l_req_assign_flag_tbl(i) ||', '||
697 'l_error_code_tbl(i): ' || l_error_code_tbl(i)
698 );
699 END IF;
700 --
701 -- Check Status Code
702 -- Meaning of each Status
703 -- [No action is required]
704 -- NO_ASSIGN_CONTEXT
705 -- NO_ASSIGNMENT
706 -- [Update GL_JE_HEADERS with the information in FUN_SEQ_BATCH_GT
707 -- DO_NOT_SEQUENCE
708 -- SEQ_VER_FOUND
709 -- [Critical Error]
710 -- ENFORCE_NO_ASSIGNMENT
711 IF l_error_code_tbl(i) = 'ENFORCED_NO_ASSIGNMENT' THEN
712 -- may use fnd_message.raise_error (automatically log the message)
713 RAISE no_assigned_seq_info;
714 END IF;
715 END LOOP;
716 --
717 -- Bulk Update fun_seq_batch_gt
718 --
719 IF l_assign_id_tbl.COUNT > 0 THEN
720 FORALL i IN l_assign_id_tbl.FIRST .. l_assign_id_tbl.LAST
721 UPDATE fun_seq_batch_gt
722 SET assignment_id = l_assign_id_tbl(i),
723 seq_version_id = l_seq_ver_id_tbl(i),
724 status_code = l_error_code_tbl(i)
725 WHERE source_id = l_je_header_id_tbl(i);
726 END IF;
727 --
728 -- Debug Information
729 --
730 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
731 FND_LOG.STRING(
732 FND_LOG.LEVEL_PROCEDURE,
733 'fun.plsql.fun_seq_batch.populate_seq_info.end',
734 'Populate_Seq_Info completes successfully.');
735 END IF;
736 EXCEPTION
737 WHEN no_assigned_seq_info THEN
738 --
739 -- Logging
740 --
741 IF fnd_log.level_error >= fnd_log.g_current_runtime_level THEN
742 -- Retrieve FUN_SEQ_NO_ACTIVE_ASSGN_FOUND from the message stack
743 -- and clear the message from the message stack.
744 -- See fun_seq.get_assigned_seq_info.
745 -- This is necessary to use the shorter version of the message
746 -- for concurrent program logs of posting program. Message text
747 -- longer than 70 chars is not allowed in Pro*C code.
748 -- The longer version is stored in the database via FND logging.
749 fnd_log.string (
750 log_level => fnd_log.level_error,
751 module
752 => 'fun.plsql.fun_seq_batch.populate_seq_info.exception',
753 message
754 => 'EXCEPTION: no_assigned_seq_info' || ', ' ||
755 fnd_message.get); -- Retrive mesg from the stack
756 END IF;
757 --
758 -- Set the shorter version of the error message on the stack
759 --
760 fnd_message.set_name ('FUN','FUN_SEQ_NO_ACTIVE_ASSGN_SHORT');
761 --
762 -- Raise Exception
763 --
764 app_exception.raise_exception;
765 WHEN OTHERS THEN
766 --
767 -- Logging
768 --
769 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
770 fnd_log.string(
771 log_level => fnd_log.level_exception,
772 module => 'fun.plsql.fun_seq_batch.populate_seq_info',
773 message => 'SQLERRM: ' || SQLERRM);
774 END IF;
775 --
776 -- Raise Exception
777 --
778 app_exception.raise_exception;
779 END Populate_Seq_Info;
780
781 --
782 -- PROCEDURE NAME:
783 -- Populate_Numbers *** For GL Posting Program ***
784 -- DESCRIPTION
785 -- Return the system date at the end of this API
786 -- GL Posting Program update posting date of
787 -- selected batches with the system date.
788 --
789 FUNCTION Populate_Numbers RETURN DATE IS
790
791 l_source_id_tbl num_tbl_type;
792 l_seq_ver_id_tbl seq_ver_id_tbl_type;
793 l_assign_id_tbl assign_id_tbl_type;
794 l_seq_value_tbl seq_value_tbl_type;
795 l_seq_date_tbl date_tbl_type;
796 BEGIN
797 --
798 -- Debug Information
799 --
800 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
801 FND_LOG.STRING(
802 FND_LOG.LEVEL_PROCEDURE,
803 'fun.plsql.fun_seq_batch.populate_numbers.begin',
804 'Beginning of populate_numbers');
805 END IF;
806 --
807 -- Bulk Collect Sequence Info. for GL Journal Entries
808 -- Sequence version / sequence number / posting date
809 -- must correlate.
810 --
811 -- Sorting Option can be GL Date or none for Posting
812 IF g_sort_option_code ='GL_DATE' THEN
813 SELECT bg.source_id,
814 bg.seq_version_id,
815 bg.assignment_id
816 BULK COLLECT
817 INTO l_source_id_tbl,
818 l_seq_ver_id_tbl,
819 l_assign_id_tbl
820 FROM fun_seq_batch_gt bg,
821 gl_je_headers jh
822 WHERE jh.je_header_id =bg.source_id and
823 bg.status_code = 'SEQ_VER_FOUND'
824 ORDER BY bg.seq_version_id,
825 bg.assignment_id,
826 jh.default_effective_date;
827 ELSE
828 SELECT source_id,
829 seq_version_id,
830 assignment_id
831 BULK COLLECT
832 INTO l_source_id_tbl,
833 l_seq_ver_id_tbl,
834 l_assign_id_tbl
835 FROM fun_seq_batch_gt
836 WHERE status_code = 'SEQ_VER_FOUND'
837 ORDER BY seq_version_id,
838 assignment_id;
839 END IF;
840
841 --
842 -- Call Generate_Bulk_Numbers
843 --
844 Generate_Bulk_Numbers(
845 p_request_id => -1,
846 p_seq_ver_id_tbl => l_seq_ver_id_tbl,
847 p_assign_id_tbl => l_assign_id_tbl,
848 x_seq_value_tbl => l_seq_value_tbl,
849 x_seq_date_tbl => l_seq_date_tbl); -- Not used here
850 --
851 -- Bulk Update fun_seq_batch_gt
852 --
853 IF l_source_id_tbl.COUNT > 0 THEN
854 FORALL i IN l_source_id_tbl.FIRST..l_source_id_tbl.LAST
855 UPDATE fun_seq_batch_gt gt
856 SET gt.seq_version_id = l_seq_ver_id_tbl(i),
857 gt.assignment_id = l_assign_id_tbl(i),
858 gt.seq_value = l_seq_value_tbl(i)
859 WHERE gt.source_id = l_source_id_tbl(i);
860 END IF;
861 --
862 -- Debug Information
863 --
864 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
865 FND_LOG.STRING(
866 FND_LOG.LEVEL_PROCEDURE,
867 'fun.plsql.fun_seq_batch.populate_numbers.end',
868 'Populate_numbers completes successfully.');
869 END IF;
870 --
871 -- Return posting date
872 --
873 RETURN SYSDATE;
874 EXCEPTION
875 WHEN OTHERS THEN
876 --
877 -- Logging
878 --
879 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
880 fnd_log.string (
881 log_level => fnd_log.level_exception,
882 module => 'fun.plsql.fun_seq_batch.populate_numbers.exception',
883 message => 'SQLERRM: ' || SQLERRM);
884 END IF;
885 --
886 -- Raise Exception
887 --
888 app_exception.raise_exception;
889 END Populate_Numbers;
890
891 --
892 -- *** Do not change the order of parameters of this procedure ***
893 -- *** Do not change the name of the parameters ***
894 --
895 PROCEDURE Release_Lock (
896 errbuf OUT NOCOPY VARCHAR2, -- Required by Conc Manager
897 retcode OUT NOCOPY NUMBER, -- Required by Conc Manager
898 p_request_id IN NUMBER) IS
899
900 l_module CONSTANT VARCHAR2(100) DEFAULT g_module || '.' || 'release_lock';
901 BEGIN
902 --
903 -- Parameters passed from Concurrent Manager
904 --
905 fnd_file.put_line(fnd_file.log, 'p_request_id: ' ||
906 NVL(TO_CHAR(p_request_id),'NULL'));
907 --
908 -- Debug Information
909 --
910 fnd_file.put_line(fnd_file.log, 'Starting Release_Lock... ');
911 --
912 -- If p_request_id is null, delete all complete requests from
913 -- fun_seq_requests
914 --
915 delete_seq_requests(p_request_id => p_request_id);
916 --
917 -- Populate Return Values 'SUCCESS'
918 --
919 retcode := 0;
920 --
921 -- Debug Information
922 --
923 fnd_file.put_line(fnd_file.log, 'Release_Lock completes successfully.');
924 EXCEPTION
925 WHEN OTHERS THEN
926 --
927 -- Logging
928 --
929 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
930 fnd_log.string(
931 log_level => fnd_log.level_exception,
932 module => l_module,
933 message => 'p_request_id: ' || p_request_id || ', ' ||
934 'SQLERRM: ' || SQLERRM);
935 END IF;
936 --
937 -- Concurrent Program Standard
938 --
939 errbuf := 'p_request_id: ' || p_request_id || ', ' || 'SQLERRM: ' || SQLERRM;
940 retcode := 2;
941 END Release_Lock;
942 ------------------------------------------------------------------------------
943 -- Supporting Procedures. Do not call them without consulting with SSMOA team
944 ------------------------------------------------------------------------------
945 PROCEDURE Populate_Seq_Requests (
946 p_request_id IN NUMBER,
947 p_seq_context_id IN NUMBER) IS
948
949 l_module CONSTANT VARCHAR2(100) DEFAULT
950 g_module || '.' || 'populate_seq_requests';
951 BEGIN
952 --
953 -- Populate Sequencing Context in Fun_Seq_Requests
954 --
955 Populate_Seq_Context (
956 p_request_id => p_request_id,
957 p_seq_context_id => p_seq_context_id);
958
959 --
960 -- Populate Sequence Headers in Fun_Seq_Requests
961 --
962 Populate_Seq_Headers (
963 p_request_id => p_request_id,
964 p_Seq_Context_Id => p_seq_context_id);
965
966 EXCEPTION
967 WHEN OTHERS THEN
968 --
969 -- Logging
970 --
971 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
972 fnd_log.string(
973 log_level => fnd_log.level_exception,
974 module => l_module,
975 message => 'p_request_id: ' || p_request_id || ', ' ||
976 'p_seq_context_id: ' || p_seq_context_id || ', ' ||
977 'SQLERRM: ' || SQLERRM);
978 END IF;
979 --
980 -- Exception
981 --
982 app_exception.raise_exception;
983 END Populate_Seq_Requests;
984
985 PROCEDURE Populate_Seq_Context (
986 p_request_id IN NUMBER,
987 p_seq_context_id IN NUMBER) IS
988
989 l_user_id NUMBER;
990 l_login_id NUMBER;
991 l_module CONSTANT VARCHAR2(100) DEFAULT
992 g_module || '.' || 'populate_seq_context';
993 BEGIN
994 l_user_id := fnd_global.user_id;
995 l_login_id := fnd_global.login_id;
996
997 INSERT INTO fun_seq_requests (
998 request_id,
999 source_type,
1000 source_id,
1001 last_update_date,
1002 last_updated_by,
1003 creation_date,
1004 created_by,
1005 last_update_login)
1006 VALUES (
1007 p_request_id,
1008 'C',
1009 p_seq_context_id,
1010 sysdate,
1011 l_user_id,
1012 sysdate,
1013 l_user_id,
1014 l_login_id);
1015
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 --
1019 -- Logging
1020 --
1021 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1022 fnd_log.string(
1023 log_level => fnd_log.level_exception,
1024 module => l_module,
1025 message => 'p_request_id: ' || p_request_id || ', ' ||
1026 'p_seq_context_id: ' || p_seq_context_id || ', ' ||
1027 'SQLERRM: ' || SQLERRM);
1028 END IF;
1029 --
1030 -- Raise Exception
1031 --
1032 app_exception.raise_exception;
1033 END Populate_Seq_Context;
1034
1035 PROCEDURE Populate_Seq_Headers (
1036 p_request_id IN NUMBER,
1037 p_seq_context_id IN NUMBER) IS
1038
1039 l_seq_headers num_tbl_type;
1040 l_user_id NUMBER;
1041 l_login_id NUMBER;
1042
1043 l_module CONSTANT VARCHAR2(100) DEFAULT
1044 g_module || '.' || 'populate_seq_headers';
1045 BEGIN
1046 l_user_id := fnd_global.user_id;
1047 l_login_id := fnd_global.login_id;
1048
1049 SELECT sa.seq_header_id
1050 BULK COLLECT
1051 INTO l_seq_headers
1052 FROM fun_seq_assignments sa
1053 WHERE sa.seq_context_id = p_seq_context_id
1054 AND sa.use_status_code IN ('NEW','USED');
1055
1056 IF l_seq_headers.COUNT > 0 THEN
1057 FORALL i IN l_seq_headers.FIRST .. l_seq_headers.LAST
1058 INSERT INTO fun_seq_requests(
1059 request_id,
1060 source_type,
1061 source_id,
1062 last_update_date,
1063 last_updated_by,
1064 creation_date,
1065 created_by,
1066 last_update_login)
1067 VALUES (
1068 p_request_id,
1069 'S',
1070 l_seq_headers(i),
1071 sysdate,
1072 l_user_id,
1073 sysdate,
1074 l_user_id,
1075 l_login_id);
1076 END IF;
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 --
1080 -- Logging
1081 --
1082 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1083 fnd_log.string(
1084 log_level => fnd_log.level_exception,
1085 module => l_module,
1086 message => 'p_request_id: ' || p_request_id || ', ' ||
1087 'p_seq_context_id: ' || p_seq_context_id || ', ' ||
1088 'SQLERRM: ' || SQLERRM);
1089 END IF;
1090 --
1091 -- Raise Exception
1092 --
1093 app_exception.raise_exception;
1094 END Populate_Seq_Headers;
1095
1096 PROCEDURE Delete_Seq_Requests (
1097 p_request_id IN NUMBER) IS
1098
1099 l_phase VARCHAR2(30);
1100 l_status VARCHAR2(30);
1101 l_dev_phase VARCHAR2(30);
1102 l_dev_status VARCHAR2(30);
1103 l_message VARCHAR2(240);
1104
1105 l_req_id_tbl num15_tbl_type;
1106 l_comp_req_id_tbl num15_tbl_type; -- "Complete" Request ID
1107 l_result BOOLEAN;
1108
1109 l_module CONSTANT VARCHAR2(100) DEFAULT
1110 g_module || '.' || 'populate_seq_requests';
1111
1112 l_phase_code VARCHAR2(2);
1113 more_rows EXCEPTION;
1114 pragma exception_init(more_rows, -1422);
1115
1116 BEGIN
1117 --
1118 -- Bulk collect request ids in fun_seq_requests
1119 --
1120 IF p_request_id IS NULL THEN
1121 SELECT request_id
1122 BULK COLLECT
1123 INTO l_req_id_tbl
1124 FROM fun_seq_requests;
1125
1126 ELSE
1127
1128 begin
1129 --
1130 -- check if this is the parent request and all child request are completed.
1131 --
1132 select distinct phase_code into l_phase_code from fnd_concurrent_requests
1133 where parent_request_id= p_request_id;
1134 -- all childs completed
1135 if l_phase_code ='C' then
1136 delete from fun_seq_requests where request_id=p_request_id;
1137 -- childs may be pending or paused ..
1138 else
1139 null; -- do not release the lock
1140 end if;
1141 EXCEPTION
1142 -- this is not a parent request
1143 WHEN NO_DATA_FOUND THEN
1144 delete from fun_seq_requests where request_id=p_request_id;
1145 -- all child are not completed yet
1146 WHEN more_rows THEN
1147 null;
1148 end;
1149
1150 END IF;
1151
1152 IF l_req_id_tbl.COUNT > 0 THEN
1153 FOR i IN l_req_id_tbl.FIRST .. l_req_id_tbl.LAST LOOP
1154 --
1155 -- Check concurrent request status
1156 --
1157 l_result := fnd_concurrent.get_request_status(
1158 request_id => l_req_id_tbl(i),
1159 appl_shortname => NULL,
1160 program => NULL,
1161 phase => l_phase,
1162 status => l_status,
1163 dev_phase => l_dev_phase,
1164 dev_status => l_dev_status,
1165 message => l_message);
1166 --
1167 -- Store complete concurrent request ids in a local variable
1168 -- If request id does not exists, l_dev_phase is null.
1169 --
1170 IF NVL(l_dev_phase,'COMPLETE') = 'COMPLETE' THEN
1171 l_comp_req_id_tbl(i) := l_req_id_tbl(i);
1172 END IF;
1173 END LOOP;
1174 --
1175 -- Bulk delete completed request Ids.
1176 --
1177 IF l_comp_req_id_tbl.COUNT > 0 THEN
1178 FORALL i IN INDICES OF l_comp_req_id_tbl
1179 DELETE
1180 FROM fun_seq_requests
1181 WHERE request_id = l_comp_req_id_tbl(i);
1182 END IF;
1183 END IF;
1184
1185 EXCEPTION
1186 WHEN NO_DATA_FOUND THEN
1187 NULL;
1188 WHEN OTHERS THEN
1189 --
1190 -- Logging
1191 --
1192 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1193 fnd_log.string(
1194 log_level => fnd_log.level_exception,
1195 module => l_module,
1196 message => 'p_request_id: ' || p_request_id || ', ' ||
1197 'SQLERRM: ' || SQLERRM);
1198 END IF;
1199 --
1200 -- Raise Exception
1201 --
1202 app_exception.raise_exception;
1203 END Delete_Seq_Requests;
1204
1205 PROCEDURE Populate_Acct_Seq_Prog_View(
1206 p_request_id IN NUMBER) IS
1207
1208 l_ae_header_id_tbl ae_header_id_tbl_type;
1209 l_ledger_id_tbl ledger_id_tbl_type;
1210 l_balance_type_code_tbl balance_type_code_tbl_type;
1211 l_je_source_name_tbl je_source_name_tbl_type;
1212 l_je_category_name_tbl je_category_name_tbl_type;
1213 l_doc_category_code_tbl doc_category_code_tbl_type;
1214 l_acct_event_type_code_tbl acct_event_type_code_tbl_type;
1215 l_acct_entry_type_code_tbl acct_entry_type_code_tbl_type;
1216 l_gl_date_tbl date_tbl_type;
1217
1218 l_seq_ver_id_tbl seq_ver_id_tbl_type;
1219 l_assign_id_tbl assign_id_tbl_type;
1220 l_out_ctrl_dt_tbl date_tbl_type;
1221 l_req_assign_flag_tbl req_assign_flag_tbl_type;
1222 l_error_code_tbl error_code_tbl_type;
1223
1224 l_ctrl_attr_rec fun_seq.control_attribute_rec_type;
1225 l_ctrl_date_tbl fun_seq.control_date_tbl_type
1226 := fun_seq.control_date_tbl_type();
1227
1228 --
1229 -- Values to be stored in XLA View
1230 --
1231 l_num_dummy_tbl num_tbl_type; -- for application id
1232 l_xla_ae_header_id_tbl ae_header_id_tbl_type;
1233 l_xla_seq_ver_id_tbl seq_ver_id_tbl_type;
1234 l_xla_assign_id_tbl assign_id_tbl_type;
1235 l_xla_seq_value_tbl seq_value_tbl_type;
1236 l_xla_completion_date_tbl date_tbl_type;
1237
1238 l_sorted_ae_header_id_tbl ae_header_id_tbl_type;
1239 l_sorted_seq_ver_id_tbl seq_ver_id_tbl_type;
1240 l_sorted_assign_id_tbl assign_id_tbl_type;
1241 l_sorted_seq_value_tbl seq_value_tbl_type;
1242
1243 l_sorted_seq_date_tbl date_tbl_type;
1244
1245 l_dummy fun_seq_headers.gapless_flag%TYPE;
1246 l_sort_option_code fun_seq_contexts.sort_option%TYPE;
1247 l_date_dummy_tbl date_tbl_type; -- For Sorting Key
1248
1249 no_assigned_seq_info EXCEPTION;
1250 j BINARY_INTEGER DEFAULT 1;
1251
1252 l_debug_ae_header_id NUMBER;
1253 BEGIN
1254 --
1255 -- Debug Information
1256 --
1257 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1258 FND_LOG.STRING(
1259 FND_LOG.LEVEL_PROCEDURE,
1260 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view.begin',
1261 'p_request_id => ' || p_request_id );
1262 END IF;
1263
1264 --
1265 -- Bulk Collect Accounting Entry Information
1266 --
1267 SELECT ae_header_id,
1268 ledger_id,
1269 balance_type_code,
1270 je_source_name,
1271 je_category_name,
1272 doc_category_code,
1273 application_id ||'.'|| accounting_event_type_code,
1274 accounting_entry_type_code,
1275 gl_date
1276 BULK COLLECT INTO
1277 l_ae_header_id_tbl,
1278 l_ledger_id_tbl,
1279 l_balance_type_code_tbl,
1280 l_je_source_name_tbl,
1281 l_je_category_name_tbl,
1282 l_doc_category_code_tbl,
1283 l_acct_event_type_code_tbl,
1284 l_acct_entry_type_code_tbl,
1285 l_gl_date_tbl
1286 FROM XLA_ACCT_PROG_SEQ_V
1287 WHERE completion_acct_seq_assign_id IS NULL
1288 AND completion_acct_seq_version_id IS NULL
1289 AND completion_acct_seq_value IS NULL;
1290
1291 --
1292 -- For Accounting Program, Sequencing Control Date Type is
1293 -- always "GL_DATE". Setting the type outside the LOOP to
1294 -- improve performance.
1295 --
1296 l_ctrl_date_tbl.EXTEND(1);
1297 l_ctrl_date_tbl(1).date_type := 'GL_DATE';
1298 --
1299 -- Retrieve Sequence Information
1300 --
1301 IF l_ae_header_id_tbl.COUNT > 0 THEN
1302 FOR i IN l_ae_header_id_tbl.FIRST .. l_ae_header_id_tbl.LAST LOOP
1303 l_debug_ae_header_id := l_ae_header_id_tbl(i);
1304 --
1305 -- Prepare parameters to retrieve Sequence information
1306 --
1307 l_ctrl_date_tbl(1).date_value := l_gl_date_tbl(i);
1308 l_ctrl_attr_rec.balance_type := l_balance_type_code_tbl(i);
1309 l_ctrl_attr_rec.journal_source := l_je_source_name_tbl(i);
1310 l_ctrl_attr_rec.journal_category := l_je_category_name_tbl(i);
1311 l_ctrl_attr_rec.document_category
1312 := l_doc_category_code_tbl(i);
1313 l_ctrl_attr_rec.accounting_event_type
1314 := l_acct_event_type_code_tbl(i);
1315 l_ctrl_attr_rec.accounting_entry_type
1316 := l_acct_entry_type_code_tbl(i);
1317
1318 --
1319 -- Debug Information
1320 --
1321 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1322 FND_LOG.STRING(
1323 FND_LOG.LEVEL_PROCEDURE,
1324 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view.config',
1325 'Before calling fun_seq.get_assigned_sequence_info... '
1326 ||', '||
1327 'l_debug_ae_header_id: '
1328 || l_debug_ae_header_id ||', '||
1329 'l_ctrl_date_tbl(1).date_value: '
1330 || l_ctrl_date_tbl(1).date_value ||', '||
1331 'l_ctrl_attr_rec.balance_type: '
1332 || l_ctrl_attr_rec.balance_type ||', '||
1333 'l_ctrl_attr_rec.journal_source: '
1334 || l_ctrl_attr_rec.journal_source ||', '||
1335 'l_ctrl_attr_rec.journal_category: '
1336 || l_ctrl_attr_rec.journal_category ||', '||
1337 'l_ctrl_attr_rec.document_category: '
1338 || l_ctrl_attr_rec.document_category ||', '||
1339 'l_ctrl_attr_rec.accounting_event_type: '
1340 || l_ctrl_attr_rec.accounting_event_type ||', '||
1341 'l_ctrl_attr_rec.accounting_entry_type: '
1342 || l_ctrl_attr_rec.accounting_entry_type
1343 );
1344 END IF;
1345 --
1346 -- Get Assignment and Version
1347 --
1348 fun_seq.get_assigned_sequence_info(
1349 p_context_type => 'LEDGER_AND_CURRENCY',
1350 p_context_value => l_ledger_id_tbl(i),
1351 p_application_Id => 602,
1352 p_table_name => 'XLA_AE_HEADERS',
1353 p_event_code => 'COMPLETION',
1354 p_control_attribute_rec => l_ctrl_attr_rec,
1355 p_control_date_tbl => l_ctrl_date_tbl,
1356 p_request_id => -1, -- Use Cache
1357 p_suppress_error => 'Y',
1358 x_sequence_type => l_dummy,
1359 x_seq_version_id => l_seq_ver_id_tbl(i),
1360 x_assignment_id => l_assign_id_tbl(i),
1361 x_control_date_value => l_out_ctrl_dt_tbl(i),
1362 x_req_assign_flag => l_req_assign_flag_tbl(i),
1363 x_sort_option_code => l_sort_option_code,
1364 x_error_code => l_error_code_tbl(i));
1365 --
1366 -- Debug Information
1367 --
1368 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1369 FND_LOG.STRING(
1370 FND_LOG.LEVEL_PROCEDURE,
1371 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view.config',
1372 'After calling fun_seq.get_assigned_sequence_info... '
1373 ||', '||
1374 'l_seq_ver_id_tbl(i): ' || l_seq_ver_id_tbl(i) ||', '||
1375 'l_assign_id_tbl(i): ' || l_assign_id_tbl(i) ||', '||
1376 'l_out_ctrl_dt_tbl(i): '|| l_out_ctrl_dt_tbl(i)||', '||
1377 'l_req_assign_flag_tbl(i): '
1378 || l_req_assign_flag_tbl(i) ||', '||
1379 'l_error_code_tbl(i): ' || l_error_code_tbl(i)
1380 );
1381 END IF;
1382
1383 --
1384 -- Raise Exception for Require Assignment Violation
1385 --
1386 IF l_error_code_tbl(i) = 'ENFORCED_NO_ASSIGNMENT' THEN
1387 RAISE no_assigned_seq_info;
1388 END IF;
1389
1390 --
1391 -- Prepare parameters to generate sequence numbers in batch
1392 --
1393 IF l_assign_id_tbl(i) IS NOT NULL THEN
1394 l_num_dummy_tbl(j) := NULL;
1395 l_xla_ae_header_id_tbl(j)
1396 := l_ae_header_id_tbl(i);
1397 l_xla_assign_id_tbl(j)
1398 := l_assign_id_tbl(i);
1399 l_xla_seq_ver_id_tbl(j)
1400 := l_seq_ver_id_tbl(i);
1401 j := j + 1;
1402 END IF;
1403 END LOOP;
1404 END IF;
1405
1406 --
1407 -- If there exists no valid assignment, skip the following routine.
1408 --
1409 IF l_xla_ae_header_id_tbl.COUNT > 0 THEN
1410 --
1411 -- Sort Accounting Entries by Sequence Version ID (Avoid Deadlock)
1412 -- Sorting Option at Accounting event can be GL Date or none.
1413 --
1414 IF l_sort_option_code = 'GL_DATE' THEN
1415 l_date_dummy_tbl := l_gl_date_tbl;
1416 END IF;
1417
1418 Sort_Acct_Entries (
1419 p_calling_program => 'ACCOUNTING',
1420 p_application_id_tbl => l_num_dummy_tbl,
1421 p_ae_header_id_tbl => l_xla_ae_header_id_tbl,
1422 p_assign_id_tbl => l_xla_assign_id_tbl,
1423 p_seq_ver_id_tbl => l_xla_seq_ver_id_tbl,
1424 p_sorting_key_tbl => l_date_dummy_tbl,
1425 x_application_id_tbl => l_num_dummy_tbl,
1426 x_ae_header_id_tbl => l_sorted_ae_header_id_tbl,
1427 x_assign_id_tbl => l_sorted_assign_id_tbl,
1428 x_seq_ver_id_tbl => l_sorted_seq_ver_id_tbl);
1429 --
1430 -- Generate Numbers in Bulk
1431 --
1432 IF l_sorted_ae_header_id_tbl.COUNT > 0 THEN
1433 generate_bulk_numbers(
1434 p_request_id => NVL(p_request_id,-99), -- Use cache
1435 p_seq_ver_id_tbl => l_sorted_seq_ver_id_tbl,
1436 p_assign_id_tbl => l_sorted_assign_id_tbl,
1437 x_seq_value_tbl => l_sorted_seq_value_tbl,
1438 x_seq_date_tbl => l_sorted_seq_date_tbl);
1439 END IF;
1440 --
1441 -- Update XLA View in Bulk
1442 --
1443 IF l_sorted_assign_id_tbl.COUNT > 0 THEN
1444 FOR i IN l_sorted_assign_id_tbl.FIRST .. l_sorted_assign_id_tbl.LAST LOOP
1445 UPDATE xla_acct_prog_seq_v
1446 SET completion_acct_seq_assign_id = l_sorted_assign_id_tbl(i),
1447 completion_acct_seq_version_id = l_sorted_seq_ver_id_tbl(i),
1448 completion_acct_seq_value = l_sorted_seq_value_tbl(i),
1449 completion_date = l_sorted_seq_date_tbl(i)
1450 WHERE ae_header_id = l_sorted_ae_header_id_tbl(i);
1451 END LOOP;
1452
1453 END IF;
1454 END IF; -- l_xla_ae_header_id_tbl.COUNT > 0
1455
1456 --
1457 -- Debug Information
1458 --
1459 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1460 FND_LOG.STRING(
1461 FND_LOG.LEVEL_PROCEDURE,
1462 'fun.plsql.fun_seq_batchpopulate_acct_seq_prog_view.end',
1463 'p_request_id: ' || p_request_id );
1464 END IF;
1465
1466 EXCEPTION
1467 WHEN no_assigned_seq_info THEN
1468 --
1469 -- Logging
1470 --
1471 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1472 fnd_log.string(
1473 fnd_log.level_exception,
1474 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view',
1475 'p_request_id: ' || p_request_id ||', ' ||
1476 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1477 'SQLERRM: ' || SQLERRM);
1478 END IF;
1479 --
1480 -- Raise Exception
1481 --
1482 fnd_message.set_name ('FUN','FUN_SEQ_NO_ACTIVE_ASSGN_FOUND');
1483 fnd_message.set_token ('SEQ_CONTEXT_NAME',
1484 fun_seq.get_seq_context_name(null));
1485 app_exception.raise_exception;
1486 WHEN OTHERS THEN
1487 --
1488 -- Logging
1489 --
1490 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1491 fnd_log.string(
1492 fnd_log.level_exception,
1493 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view',
1494 'Unexpected exception in Populate_Acct_Seq_Prog_View' || ', ' ||
1495 'p_request_id: ' || p_request_id ||', ' ||
1496 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1497 'SQLERRM: ' || SQLERRM);
1498 END IF;
1499 app_exception.raise_exception;
1500 END Populate_Acct_Seq_Prog_View;
1501
1502 --
1503 -- Populate sequencing information in xla_seq_je_headers_gt.
1504 -- XLA inserts 'Actual' journal entries into this table.
1505 --
1506 --
1507 PROCEDURE Populate_Rep_Seq_Prog_Gt(
1508 p_request_id IN NUMBER) IS
1509
1510 l_ae_header_id_tbl ae_header_id_tbl_type;
1511 l_application_id_tbl num_tbl_type;
1512 l_table_name_tbl vc30_tbl_type;
1513 l_ledger_id_tbl ledger_id_tbl_type;
1514 l_je_source_name_tbl je_source_name_tbl_type;
1515 l_je_category_name_tbl je_category_name_tbl_type;
1516 l_gl_date_tbl date_tbl_type;
1517 l_reference_date_tbl date_tbl_type;
1518 l_completion_date_tbl date_tbl_type; -- Completion /Posted Date
1519
1520 l_ctrl_attr_rec fun_seq.control_attribute_rec_type;
1521 l_ctrl_date_tbl fun_seq.control_date_tbl_type
1522 := fun_seq.control_date_tbl_type();
1523
1524 --
1525 -- Values to be stored in the XLA Temporary Table
1526 --
1527 l_xla_application_id_tbl num_tbl_type;
1528 l_xla_ae_header_id_tbl ae_header_id_tbl_type;
1529 l_xla_seq_ver_id_tbl seq_ver_id_tbl_type;
1530 l_xla_assign_id_tbl assign_id_tbl_type;
1531 l_xla_seq_value_tbl seq_value_tbl_type;
1532 l_xla_gl_date_tbl date_tbl_type;
1533 l_xla_reference_date_tbl date_tbl_type;
1534 l_xla_completion_date_tbl date_tbl_type;
1535
1536 l_seq_ver_id_tbl seq_ver_id_tbl_type;
1537 l_assign_id_tbl assign_id_tbl_type;
1538 l_out_ctrl_dt_tbl date_tbl_type;
1539 l_req_assign_flag_tbl req_assign_flag_tbl_type;
1540 l_sorting_key_tbl date_tbl_type;
1541 l_error_code_tbl error_code_tbl_type;
1542
1543 l_sort_option_code fun_seq_contexts.sort_option%TYPE;
1544 l_dummy fun_seq_headers.gapless_flag%TYPE;
1545
1546 l_sorted_application_id_tbl num_tbl_type;
1547 l_sorted_ae_header_id_tbl ae_header_id_tbl_type;
1548 l_sorted_seq_ver_id_tbl seq_ver_id_tbl_type;
1549 l_sorted_assign_id_tbl assign_id_tbl_type;
1550 l_sorted_seq_value_tbl seq_value_tbl_type;
1551 l_sorted_seq_date_tbl date_tbl_type;
1552 l_dummy_date_tbl date_tbl_type;
1553
1554 no_assigned_seq_info EXCEPTION;
1555 j BINARY_INTEGER DEFAULT 1;
1556
1557 l_debug_ae_header_id NUMBER;
1558 invalid_sort_option EXCEPTION;
1559 l_context_name VARCHAR2(200);
1560 l_context_type VARCHAR2(50);
1561 l_application_id NUMBER;
1562 l_context_value NUMBER;
1563 l_table_name VARCHAR2(50);
1564
1565
1566 BEGIN
1567 --
1568 -- Debug Information
1569 --
1570 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1571 FND_LOG.STRING(
1572 FND_LOG.LEVEL_PROCEDURE,
1573 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.begin',
1574 'p_request_id => ' || p_request_id );
1575 END IF;
1576
1577 --
1578 -- Bulk Collect Accounting Entry Information
1579 --
1580 SELECT ae_header_id,
1581 application_id,
1582 DECODE(application_id,602,'XLA_AE_HEADERS',
1583 101,'GL_JE_HEADERS',
1584 null),
1585 ledger_id,
1586 je_source_name,
1587 je_category_name,
1588 gl_date,
1589 reference_date,
1590 completion_posted_date
1591 BULK COLLECT INTO
1592 l_ae_header_id_tbl,
1593 l_application_id_tbl,
1594 l_table_name_tbl,
1595 l_ledger_id_tbl,
1596 l_je_source_name_tbl,
1597 l_je_category_name_tbl,
1598 l_gl_date_tbl,
1599 l_reference_date_tbl,
1600 l_completion_date_tbl
1601 FROM xla_seq_je_headers_gt
1602 WHERE sequence_assign_id IS NULL
1603 AND sequence_version_id IS NULL
1604 AND sequence_value IS NULL;
1605
1606 --
1607 -- Retrieve Sequence Information
1608 --
1609 IF l_ae_header_id_tbl.COUNT > 0 THEN
1610 FOR i IN l_ae_header_id_tbl.FIRST .. l_ae_header_id_tbl.LAST LOOP
1611 --
1612 -- to be used in exception section
1613 --
1614 l_debug_ae_header_id := l_ae_header_id_tbl(i);
1615 l_context_type := 'LEDGER_AND_CURRENCY';
1616 l_application_id:= l_application_id_tbl(i);
1617 l_context_value:= l_ledger_id_tbl(i);
1618 l_table_name:=l_table_name_tbl(i);
1619
1620 --
1621 -- Prepare parameters to retrieve Sequence information
1622 --
1623
1624 --
1625 -- Completion / Posted Date is not used to retrived Sequencing
1626 -- information. It is just for sorting.
1627 --
1628 l_ctrl_date_tbl.EXTEND(3);
1629 l_ctrl_date_tbl(1).date_type := 'GL_DATE';
1630 l_ctrl_date_tbl(1).date_value := l_gl_date_tbl(i);
1631 l_ctrl_date_tbl(2).date_type := 'REFERENCE_DATE';
1632 l_ctrl_date_tbl(2).date_value := l_reference_date_tbl(i);
1633 --
1634 -- balance type is always 'Actual' for reporting sequencing
1635 --
1636 l_ctrl_attr_rec.balance_type := 'A';
1637 l_ctrl_attr_rec.journal_source := l_je_source_name_tbl(i);
1638 l_ctrl_attr_rec.journal_category := l_je_category_name_tbl(i);
1639 --
1640 -- Debug Information
1641 --
1642 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1643 FND_LOG.STRING(
1644 FND_LOG.LEVEL_EVENT,
1645 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.config',
1646 'Before calling fun_seq.get_assigned_sequence_info... '
1647 ||', '||
1648 'l_debug_ae_header_id: '
1649 || l_debug_ae_header_id ||', '||
1650 'l_ctrl_date_tbl(1).date_value: '
1651 || l_ctrl_date_tbl(1).date_value ||', '||
1652 'l_ctrl_date_tbl(2).date_value: '
1653 || l_ctrl_date_tbl(2).date_value ||', '||
1654 'l_ctrl_attr_rec.journal_source: '
1655 || l_ctrl_attr_rec.journal_source
1656 );
1657 END IF;
1658
1659 --
1660 -- Get Assignment and Version
1661 --
1662 -- Sorting options of sequencing contexts are identical
1663 -- within the temporary table.
1664 --
1665 fun_seq.get_assigned_sequence_info(
1666 p_context_type => 'LEDGER_AND_CURRENCY',
1667 p_context_value => l_ledger_id_tbl(i),
1668 p_application_id => l_application_id_tbl(i),
1669 p_table_name => l_table_name_tbl(i),
1670 p_event_code => 'PERIOD_CLOSE',
1671 p_control_attribute_rec => l_ctrl_attr_rec,
1672 p_control_date_tbl => l_ctrl_date_tbl,
1673 p_request_id => -1, -- Use Cache
1674 p_suppress_error => 'Y',
1675 x_sequence_type => l_dummy,
1676 x_seq_version_id => l_seq_ver_id_tbl(i),
1677 x_assignment_id => l_assign_id_tbl(i),
1678 x_control_date_value => l_out_ctrl_dt_tbl(i),
1679 x_req_assign_flag => l_req_assign_flag_tbl(i),
1680 x_sort_option_code => l_sort_option_code,
1681 x_error_code => l_error_code_tbl(i));
1682
1683 --
1684 -- Debug Information
1685 --
1686 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1687 FND_LOG.STRING(
1688 FND_LOG.LEVEL_EVENT,
1689 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.config',
1690 'After calling fun_seq.get_assigned_sequence_info... '
1691 ||', '||
1692 'l_seq_ver_id_tbl(i): ' || l_seq_ver_id_tbl(i) ||', '||
1693 'l_assign_id_tbl(i): ' || l_assign_id_tbl(i) ||', '||
1694 'l_out_ctrl_dt_tbl(i): '|| l_out_ctrl_dt_tbl(i)||', '||
1695 'l_req_assign_flag_tbl(i): '
1696 || l_req_assign_flag_tbl(i) ||', '||
1697 'l_error_code_tbl(i): ' || l_error_code_tbl(i)
1698 );
1699 END IF;
1700
1701 --
1702 -- Raise Exception for Require Assignment Violation
1703 --
1704 IF l_error_code_tbl(i) = 'ENFORCED_NO_ASSIGNMENT' THEN
1705 RAISE no_assigned_seq_info;
1706 END IF;
1707
1708 --
1709 -- Prepare parameters to generate sequence numbers in batch
1710 --
1711 IF l_assign_id_tbl(i) IS NOT NULL THEN
1712 l_xla_application_id_tbl(j)
1713 := l_application_id_tbl(i);
1714 l_xla_ae_header_id_tbl(j)
1715 := l_ae_header_id_tbl(i);
1716 l_xla_assign_id_tbl(j)
1717 := l_assign_id_tbl(i);
1718 l_xla_seq_ver_id_tbl(j)
1719 := l_seq_ver_id_tbl(i);
1720 l_xla_gl_date_tbl(j)
1721 := l_gl_date_tbl(i);
1722 l_xla_reference_date_tbl(j)
1723 := l_reference_date_tbl(i);
1724 l_xla_completion_date_tbl(j)
1725 := l_completion_date_tbl(i);
1726 j := j + 1;
1727 END IF;
1728 END LOOP;
1729 END IF;
1730
1731 --
1732 -- If there exists no valid assignment, skip the following routine.
1733 --
1734 IF l_xla_ae_header_id_tbl.COUNT > 0 THEN
1735 --
1736 -- Debug Information
1737 --
1738 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1739 FND_LOG.STRING(
1740 FND_LOG.LEVEL_STATEMENT,
1741 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.config',
1742 'Before calling Sort_Acct_Entires: Sort Option - ' ||
1743 l_sort_option_code);
1744 END IF;
1745
1746 --
1747 -- Sort Accounting Entries by Sequence Version ID (Avoid Deadlock)
1748 --
1749 IF l_sort_option_code = 'GL_DATE' THEN
1750 l_sorting_key_tbl := l_xla_gl_date_tbl;
1751 ELSIF l_sort_option_code = 'REFERENCE_DATE' THEN
1752 l_sorting_key_tbl := l_xla_reference_date_tbl;
1753 ELSIF l_sort_option_code = 'COMPLETION_OR_POSTING_DATE' THEN
1754 l_sorting_key_tbl := l_xla_completion_date_tbl;
1755 ELSE
1756 RAISE invalid_sort_option;
1757 END IF;
1758
1759 Sort_Acct_Entries (
1760 p_calling_program => 'REPORTING',
1761 p_application_id_tbl => l_xla_application_id_tbl,
1762 p_ae_header_id_tbl => l_xla_ae_header_id_tbl,
1763 p_assign_id_tbl => l_xla_assign_id_tbl,
1764 p_seq_ver_id_tbl => l_xla_seq_ver_id_tbl,
1765 p_sorting_key_tbl => l_sorting_key_tbl,
1766 x_application_id_tbl => l_sorted_application_id_tbl,
1767 x_ae_header_id_tbl => l_sorted_ae_header_id_tbl,
1768 x_assign_id_tbl => l_sorted_assign_id_tbl,
1769 x_seq_ver_id_tbl => l_sorted_seq_ver_id_tbl);
1770
1771 --
1772 -- Debug Information
1773 --
1774 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1775 FND_LOG.STRING(
1776 FND_LOG.LEVEL_STATEMENT,
1777 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.config',
1778 'After calling Sort_Acct_Entires');
1779 END IF;
1780
1781 --
1782 -- Generate Numbers in Bulk
1783 --
1784 IF l_sorted_ae_header_id_tbl.COUNT > 0 THEN
1785 generate_bulk_numbers(
1786 p_request_id => NVL(p_request_id,-99), -- Use cache
1787 p_seq_ver_id_tbl => l_sorted_seq_ver_id_tbl,
1788 p_assign_id_tbl => l_sorted_assign_id_tbl,
1789 x_seq_value_tbl => l_sorted_seq_value_tbl,
1790 x_seq_date_tbl => l_sorted_seq_date_tbl);
1791 END IF;
1792 --
1793 -- Update XLA View in Bulk
1794 --
1795 IF l_sorted_assign_id_tbl.COUNT > 0 THEN
1796 FORALL i IN l_sorted_assign_id_tbl.FIRST .. l_sorted_assign_id_tbl.LAST
1797 UPDATE xla_seq_je_headers_gt
1798 SET sequence_assign_id = l_sorted_assign_id_tbl(i),
1799 sequence_version_id = l_sorted_seq_ver_id_tbl(i),
1800 sequence_value = l_sorted_seq_value_tbl(i)
1801 WHERE application_id = l_sorted_application_id_tbl(i)
1802 AND ae_header_id = l_sorted_ae_header_id_tbl(i);
1803 END IF;
1804 END IF; -- l_xla_ae_header_id_tbl.COUNT > 0
1805
1806 --
1807 -- Debug Information
1808 --
1809 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1810 FND_LOG.STRING(
1811 FND_LOG.LEVEL_PROCEDURE,
1812 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.end',
1813 'p_request_id: ' || p_request_id );
1814 END IF;
1815 EXCEPTION
1816 WHEN no_assigned_seq_info THEN
1817 --
1818 -- Logging
1819 --
1820 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1821 fnd_log.string(
1822 fnd_log.level_exception,
1823 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt',
1824 'p_request_id: ' || p_request_id ||', ' ||
1825 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1826 'SQLERRM: ' || SQLERRM);
1827 END IF;
1828
1829 SELECT name
1830 INTO l_context_name
1831 FROM fun_seq_contexts WHERE
1832 context_type=l_context_type AND
1833 context_value=l_context_value AND
1834 application_id=l_application_id AND
1835 table_name=l_table_name AND
1836 event_code='PERIOD_CLOSE';
1837
1838 --
1839 -- Raise Exception
1840 --
1841 fnd_message.set_name ('FUN','FUN_SEQ_NO_ACTIVE_ASSGN_FOUND');
1842 fnd_message.set_token ('SEQ_CONTEXT_NAME',l_context_name);
1843
1844 app_exception.raise_exception;
1845
1846 WHEN invalid_sort_option THEN
1847 --
1848 -- Logging
1849 --
1850 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1851 fnd_log.string(
1852 fnd_log.level_exception,
1853 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt',
1854 'p_request_id: ' || p_request_id || ', ' ||
1855 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1856 'sort_option_code: ' || l_sort_option_code || ', ' ||
1857 'SQLERRM: ' || SQLERRM);
1858 END IF;
1859 --
1860 -- Raise Exception
1861 --
1862 app_exception.raise_exception;
1863
1864 WHEN OTHERS THEN
1865 --
1866 -- Logging
1867 --
1868 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1869 fnd_log.string(
1870 fnd_log.level_exception,
1871 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt',
1872 'Unexpected exception in populate_rep_seq_prog_gt' || ', ' ||
1873 'p_request_id: ' || p_request_id ||', ' ||
1874 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1875 'SQLERRM: ' || SQLERRM);
1876 END IF;
1877
1878 app_exception.raise_exception;
1879
1880 END Populate_Rep_Seq_Prog_Gt;
1881
1882 PROCEDURE Sort_Acct_Entries (
1883 p_calling_program IN VARCHAR2,
1884 p_application_id_tbl IN num_tbl_type,
1885 p_ae_header_id_tbl IN ae_header_id_tbl_type,
1886 p_assign_id_tbl IN assign_id_tbl_type,
1887 p_seq_ver_id_tbl IN seq_ver_id_tbl_type,
1888 p_sorting_key_tbl IN date_tbl_type,
1889 x_application_id_tbl OUT NOCOPY num_tbl_type,
1890 x_ae_header_id_tbl OUT NOCOPY ae_header_id_tbl_type,
1891 x_assign_id_tbl OUT NOCOPY assign_id_tbl_type,
1892 x_seq_ver_id_tbl OUT NOCOPY seq_ver_id_tbl_type) IS
1893
1894 l_temp_tab fun_seq_bt_tbl_type;
1895
1896 BEGIN
1897 l_temp_tab := fun_seq_bt_tbl_type();
1898 --
1899 -- Debug Information
1900 --
1901 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1902 FND_LOG.STRING(
1903 FND_LOG.LEVEL_PROCEDURE,
1904 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.begin',
1905 'p_ae_header_id_tbl.count => ' || p_ae_header_id_tbl.COUNT);
1906 END IF;
1907 --
1908 -- Populate Sequence Info in Table Type
1909 --
1910 IF p_ae_header_id_tbl.COUNT > 0 THEN
1911 --
1912 -- Populate Parameter Values to Table
1913 --
1914 IF p_calling_program = 'ACCOUNTING' THEN
1915 --
1916 -- bug#5434859 added IF clause
1917 --
1918 IF p_sorting_key_tbl.COUNT > 0 THEN
1919 -- bug# 5373090 - Italian requirement
1920 FOR i IN p_ae_header_id_tbl.FIRST .. p_ae_header_id_tbl.LAST LOOP
1921 l_temp_tab.EXTEND;
1922 l_temp_tab(i) :=
1923 fun_seq_bt_obj_type(
1924 NULL, -- Application Id for Reporting Sequencing
1925 p_ae_header_id_tbl(i),
1926 p_assign_id_tbl(i),
1927 p_seq_ver_id_tbl(i),
1928 fnd_date.date_to_canonical(p_sorting_key_tbl(i)));
1929 END LOOP;
1930 ELSE
1931 FOR i IN p_ae_header_id_tbl.FIRST .. p_ae_header_id_tbl.LAST LOOP
1932 l_temp_tab.EXTEND;
1933 l_temp_tab(i) :=
1934 fun_seq_bt_obj_type(
1935 NULL, -- Application Id for Reporting Sequencing
1936 p_ae_header_id_tbl(i),
1937 p_assign_id_tbl(i),
1938 p_seq_ver_id_tbl(i),
1939 null);
1940 END LOOP;
1941 END IF;
1942
1943 --
1944 -- Debug Information
1945 --
1946 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1947 FND_LOG.STRING(
1948 FND_LOG.LEVEL_STATEMENT,
1949 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.config',
1950 'Local temp table has been populated: ' || p_calling_program);
1951 END IF;
1952
1953 --
1954 -- Sort Accounting Entries by Sequence Versions
1955 --
1956 SELECT sqtmp.source_id,
1957 sqtmp.assignment_id,
1958 sqtmp.seq_version_id
1959 -- Don't need sorting key for the Completion event
1960 BULK COLLECT
1961 INTO x_ae_header_id_tbl,
1962 x_assign_id_tbl,
1963 x_seq_ver_id_tbl
1964 FROM THE (SELECT CAST( l_temp_tab as fun_seq_bt_tbl_type)
1965 FROM dual ) sqtmp
1966 ORDER BY
1967 sqtmp.seq_version_id,
1968 sqtmp.sorting_key;
1969
1970 ELSIF p_calling_program = 'REPORTING' THEN
1971 --
1972 -- Populate Parameter Values to Table
1973 --
1974 FOR i IN p_ae_header_id_tbl.FIRST .. p_ae_header_id_tbl.LAST LOOP
1975 l_temp_tab.EXTEND;
1976 l_temp_tab(i) :=
1977 fun_seq_bt_obj_type(
1978 p_application_id_tbl(i),
1979 p_ae_header_id_tbl(i),
1980 p_assign_id_tbl(i),
1981 p_seq_ver_id_tbl(i),
1982 fnd_date.date_to_canonical(p_sorting_key_tbl(i)));
1983 END LOOP;
1984
1985 --
1986 -- Debug Information
1987 --
1988 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1989 FND_LOG.STRING(
1990 FND_LOG.LEVEL_STATEMENT,
1991 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.config',
1992 'Local temp table has been populated: ' || p_calling_program);
1993 END IF;
1994
1995 --
1996 -- Sort Accounting Entries by Sequence Versions and Control Dates
1997 --
1998 -- Modified the code for Bug7692368, added period_num is order by
1999 --
2000 SELECT res.application_id,
2001 res.source_id,
2002 res.assignment_id,
2003 res.seq_version_id
2004 BULK COLLECT
2005 INTO x_application_id_tbl,
2006 x_ae_header_id_tbl,
2007 x_assign_id_tbl,
2008 x_seq_ver_id_tbl
2009 FROM
2010 (SELECT
2011 sqtmp.application_id application_id ,
2012 sqtmp.source_id source_id,
2013 sqtmp.assignment_id assignment_id,
2014 sqtmp.seq_version_id seq_version_id,
2015 ps.period_num period_num,
2016 sqtmp.sorting_key sorting_key
2017 FROM THE (SELECT CAST( l_temp_tab as fun_seq_bt_tbl_type)
2018 FROM dual ) sqtmp, gl_period_statuses ps, gl_je_headers h
2019 WHERE
2020 ps.period_name = h.period_name
2021 AND ps.ledger_id = h.ledger_id
2022 AND ps.application_id = 101
2023 AND h.je_header_id = sqtmp.source_id
2024 AND sqtmp.application_id = 101
2025 union
2026 SELECT sqtmp.application_id application_id ,
2027 sqtmp.source_id source_id,
2028 sqtmp.assignment_id assignment_id,
2029 sqtmp.seq_version_id seq_version_id,
2030 ps.period_num period_num,
2031 sqtmp.sorting_key sorting_key
2032 FROM THE (SELECT CAST( l_temp_tab as fun_seq_bt_tbl_type)
2033 FROM dual ) sqtmp, gl_period_statuses ps, xla_ae_headers ah
2034 WHERE
2035 ps.period_name = ah.period_name
2036 AND ps.ledger_id = ah.ledger_id
2037 AND ps.application_id = 101
2038 AND ah.ae_header_id = sqtmp.source_id
2039 AND sqtmp.application_id = 602) res
2040
2041 ORDER BY
2042 res.seq_version_id,
2043 res.sorting_key,
2044 res.period_num;
2045
2046 END IF; -- p_calling_program = <ACCOUNTING/REPORTING>
2047 END IF; -- p_ae_header_id_tbl.COUNT > 0
2048
2049 --
2050 -- Debug Information
2051 --
2052 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2053 FND_LOG.STRING(
2054 FND_LOG.LEVEL_PROCEDURE,
2055 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.end',
2056 'p_ae_header_id_tbl.count => ' || p_ae_header_id_tbl.COUNT);
2057 END IF;
2058 EXCEPTION
2059 WHEN OTHERS THEN
2060 --
2061 -- Logging
2062 --
2063 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2064 fnd_log.string(
2065 log_level => fnd_log.level_exception,
2066 module => 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.Exception',
2067 message => 'SQLERRM: ' || SQLERRM);
2068 END IF;
2069 app_exception.raise_exception;
2070 END Sort_Acct_Entries;
2071 END fun_seq_batch;