[Home] [Help]
PACKAGE BODY: APPS.FUN_SEQ_BATCH
Source
1 PACKAGE BODY fun_seq_batch AS
2 /* $Header: funsqbtb.pls 120.43 2006/08/30 16:06:48 esayyed noship $ */
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 ELSE
1126
1127 begin
1128 --
1129 -- check if this is the parent request and all child request are completed.
1130 --
1131 select distinct phase_code into l_phase_code from fnd_concurrent_requests
1132 where parent_request_id= p_request_id;
1133 -- all childs completed
1134 if l_phase_code ='C' then
1135 delete from fun_seq_requests where request_id=p_request_id;
1136 -- childs may be pending or paused ..
1137 else
1138 null; -- do not release the lock
1139 end if;
1140 EXCEPTION
1141 -- this is not a parent request
1142 WHEN NO_DATA_FOUND THEN
1143 delete from fun_seq_requests where request_id=p_request_id;
1144 -- all child are not completed yet
1145 WHEN more_rows THEN
1146 null;
1147 end;
1148
1149 END IF;
1150
1151 IF l_req_id_tbl.COUNT > 0 THEN
1152 FOR i IN l_req_id_tbl.FIRST .. l_req_id_tbl.LAST LOOP
1153 --
1154 -- Check concurrent request status
1155 --
1156 l_result := fnd_concurrent.get_request_status(
1157 request_id => l_req_id_tbl(i),
1158 appl_shortname => NULL,
1159 program => NULL,
1160 phase => l_phase,
1161 status => l_status,
1162 dev_phase => l_dev_phase,
1163 dev_status => l_dev_status,
1164 message => l_message);
1165 --
1166 -- Store complete concurrent request ids in a local variable
1167 -- If request id does not exists, l_dev_phase is null.
1168 --
1169 IF NVL(l_dev_phase,'COMPLETE') = 'COMPLETE' THEN
1170 l_comp_req_id_tbl(i) := l_req_id_tbl(i);
1171 END IF;
1172 END LOOP;
1173 --
1174 -- Bulk delete completed request Ids.
1175 --
1176 IF l_comp_req_id_tbl.COUNT > 0 THEN
1177 FORALL i IN l_comp_req_id_tbl.FIRST .. l_comp_req_id_tbl.LAST
1178 DELETE
1179 FROM fun_seq_requests
1180 WHERE request_id = l_comp_req_id_tbl(i);
1181 END IF;
1182 END IF;
1183
1184 EXCEPTION
1185 WHEN NO_DATA_FOUND THEN
1186 NULL;
1187 WHEN OTHERS THEN
1188 --
1189 -- Logging
1190 --
1191 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1192 fnd_log.string(
1193 log_level => fnd_log.level_exception,
1194 module => l_module,
1195 message => 'p_request_id: ' || p_request_id || ', ' ||
1196 'SQLERRM: ' || SQLERRM);
1197 END IF;
1198 --
1199 -- Raise Exception
1200 --
1201 app_exception.raise_exception;
1202 END Delete_Seq_Requests;
1203
1204 PROCEDURE Populate_Acct_Seq_Prog_View(
1205 p_request_id IN NUMBER) IS
1206
1207 l_ae_header_id_tbl ae_header_id_tbl_type;
1208 l_ledger_id_tbl ledger_id_tbl_type;
1209 l_balance_type_code_tbl balance_type_code_tbl_type;
1210 l_je_source_name_tbl je_source_name_tbl_type;
1211 l_je_category_name_tbl je_category_name_tbl_type;
1212 l_doc_category_code_tbl doc_category_code_tbl_type;
1213 l_acct_event_type_code_tbl acct_event_type_code_tbl_type;
1214 l_acct_entry_type_code_tbl acct_entry_type_code_tbl_type;
1215 l_gl_date_tbl date_tbl_type;
1216
1217 l_seq_ver_id_tbl seq_ver_id_tbl_type;
1218 l_assign_id_tbl assign_id_tbl_type;
1219 l_out_ctrl_dt_tbl date_tbl_type;
1220 l_req_assign_flag_tbl req_assign_flag_tbl_type;
1221 l_error_code_tbl error_code_tbl_type;
1222
1223 l_ctrl_attr_rec fun_seq.control_attribute_rec_type;
1224 l_ctrl_date_tbl fun_seq.control_date_tbl_type
1225 := fun_seq.control_date_tbl_type();
1226
1227 --
1228 -- Values to be stored in XLA View
1229 --
1230 l_num_dummy_tbl num_tbl_type; -- for application id
1231 l_xla_ae_header_id_tbl ae_header_id_tbl_type;
1232 l_xla_seq_ver_id_tbl seq_ver_id_tbl_type;
1233 l_xla_assign_id_tbl assign_id_tbl_type;
1234 l_xla_seq_value_tbl seq_value_tbl_type;
1235 l_xla_completion_date_tbl date_tbl_type;
1236
1237 l_sorted_ae_header_id_tbl ae_header_id_tbl_type;
1238 l_sorted_seq_ver_id_tbl seq_ver_id_tbl_type;
1239 l_sorted_assign_id_tbl assign_id_tbl_type;
1240 l_sorted_seq_value_tbl seq_value_tbl_type;
1241
1242 l_sorted_seq_date_tbl date_tbl_type;
1243
1244 l_dummy fun_seq_headers.gapless_flag%TYPE;
1245 l_sort_option_code fun_seq_contexts.sort_option%TYPE;
1246 l_date_dummy_tbl date_tbl_type; -- For Sorting Key
1247
1248 no_assigned_seq_info EXCEPTION;
1249 j BINARY_INTEGER DEFAULT 1;
1250
1251 l_debug_ae_header_id NUMBER;
1252 BEGIN
1253 --
1254 -- Debug Information
1255 --
1256 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1257 FND_LOG.STRING(
1258 FND_LOG.LEVEL_PROCEDURE,
1259 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view.begin',
1260 'p_request_id => ' || p_request_id );
1261 END IF;
1262
1263 --
1264 -- Bulk Collect Accounting Entry Information
1265 --
1266 SELECT ae_header_id,
1267 ledger_id,
1268 balance_type_code,
1269 je_source_name,
1270 je_category_name,
1271 doc_category_code,
1272 application_id ||'.'|| accounting_event_type_code,
1273 accounting_entry_type_code,
1274 gl_date
1275 BULK COLLECT INTO
1276 l_ae_header_id_tbl,
1277 l_ledger_id_tbl,
1278 l_balance_type_code_tbl,
1279 l_je_source_name_tbl,
1280 l_je_category_name_tbl,
1281 l_doc_category_code_tbl,
1282 l_acct_event_type_code_tbl,
1283 l_acct_entry_type_code_tbl,
1284 l_gl_date_tbl
1285 FROM XLA_ACCT_PROG_SEQ_V
1286 WHERE completion_acct_seq_assign_id IS NULL
1287 AND completion_acct_seq_version_id IS NULL
1288 AND completion_acct_seq_value IS NULL;
1289
1290 --
1291 -- For Accounting Program, Sequencing Control Date Type is
1292 -- always "GL_DATE". Setting the type outside the LOOP to
1293 -- improve performance.
1294 --
1295 l_ctrl_date_tbl.EXTEND(1);
1296 l_ctrl_date_tbl(1).date_type := 'GL_DATE';
1297 --
1298 -- Retrieve Sequence Information
1299 --
1300 IF l_ae_header_id_tbl.COUNT > 0 THEN
1301 FOR i IN l_ae_header_id_tbl.FIRST .. l_ae_header_id_tbl.LAST LOOP
1302 l_debug_ae_header_id := l_ae_header_id_tbl(i);
1303 --
1304 -- Prepare parameters to retrieve Sequence information
1305 --
1306 l_ctrl_date_tbl(1).date_value := l_gl_date_tbl(i);
1307 l_ctrl_attr_rec.balance_type := l_balance_type_code_tbl(i);
1308 l_ctrl_attr_rec.journal_source := l_je_source_name_tbl(i);
1309 l_ctrl_attr_rec.journal_category := l_je_category_name_tbl(i);
1310 l_ctrl_attr_rec.document_category
1311 := l_doc_category_code_tbl(i);
1312 l_ctrl_attr_rec.accounting_event_type
1313 := l_acct_event_type_code_tbl(i);
1314 l_ctrl_attr_rec.accounting_entry_type
1315 := l_acct_entry_type_code_tbl(i);
1316
1317 --
1318 -- Debug Information
1319 --
1320 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1321 FND_LOG.STRING(
1322 FND_LOG.LEVEL_PROCEDURE,
1323 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view.config',
1324 'Before calling fun_seq.get_assigned_sequence_info... '
1325 ||', '||
1326 'l_debug_ae_header_id: '
1327 || l_debug_ae_header_id ||', '||
1328 'l_ctrl_date_tbl(1).date_value: '
1329 || l_ctrl_date_tbl(1).date_value ||', '||
1330 'l_ctrl_attr_rec.balance_type: '
1331 || l_ctrl_attr_rec.balance_type ||', '||
1332 'l_ctrl_attr_rec.journal_source: '
1333 || l_ctrl_attr_rec.journal_source ||', '||
1334 'l_ctrl_attr_rec.journal_category: '
1335 || l_ctrl_attr_rec.journal_category ||', '||
1336 'l_ctrl_attr_rec.document_category: '
1337 || l_ctrl_attr_rec.document_category ||', '||
1338 'l_ctrl_attr_rec.accounting_event_type: '
1339 || l_ctrl_attr_rec.accounting_event_type ||', '||
1340 'l_ctrl_attr_rec.accounting_entry_type: '
1341 || l_ctrl_attr_rec.accounting_entry_type
1342 );
1343 END IF;
1344 --
1345 -- Get Assignment and Version
1346 --
1347 fun_seq.get_assigned_sequence_info(
1348 p_context_type => 'LEDGER_AND_CURRENCY',
1349 p_context_value => l_ledger_id_tbl(i),
1350 p_application_Id => 602,
1351 p_table_name => 'XLA_AE_HEADERS',
1352 p_event_code => 'COMPLETION',
1353 p_control_attribute_rec => l_ctrl_attr_rec,
1354 p_control_date_tbl => l_ctrl_date_tbl,
1355 p_request_id => -1, -- Use Cache
1356 p_suppress_error => 'Y',
1357 x_sequence_type => l_dummy,
1358 x_seq_version_id => l_seq_ver_id_tbl(i),
1359 x_assignment_id => l_assign_id_tbl(i),
1360 x_control_date_value => l_out_ctrl_dt_tbl(i),
1361 x_req_assign_flag => l_req_assign_flag_tbl(i),
1362 x_sort_option_code => l_sort_option_code,
1363 x_error_code => l_error_code_tbl(i));
1364 --
1365 -- Debug Information
1366 --
1367 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1368 FND_LOG.STRING(
1369 FND_LOG.LEVEL_PROCEDURE,
1370 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view.config',
1371 'After calling fun_seq.get_assigned_sequence_info... '
1372 ||', '||
1373 'l_seq_ver_id_tbl(i): ' || l_seq_ver_id_tbl(i) ||', '||
1374 'l_assign_id_tbl(i): ' || l_assign_id_tbl(i) ||', '||
1375 'l_out_ctrl_dt_tbl(i): '|| l_out_ctrl_dt_tbl(i)||', '||
1376 'l_req_assign_flag_tbl(i): '
1377 || l_req_assign_flag_tbl(i) ||', '||
1378 'l_error_code_tbl(i): ' || l_error_code_tbl(i)
1379 );
1380 END IF;
1381
1382 --
1383 -- Raise Exception for Require Assignment Violation
1384 --
1385 IF l_error_code_tbl(i) = 'ENFORCED_NO_ASSIGNMENT' THEN
1386 RAISE no_assigned_seq_info;
1387 END IF;
1388
1389 --
1390 -- Prepare parameters to generate sequence numbers in batch
1391 --
1392 IF l_assign_id_tbl(i) IS NOT NULL THEN
1393 l_num_dummy_tbl(j) := NULL;
1394 l_xla_ae_header_id_tbl(j)
1395 := l_ae_header_id_tbl(i);
1396 l_xla_assign_id_tbl(j)
1397 := l_assign_id_tbl(i);
1398 l_xla_seq_ver_id_tbl(j)
1399 := l_seq_ver_id_tbl(i);
1400 j := j + 1;
1401 END IF;
1402 END LOOP;
1403 END IF;
1404
1405 --
1406 -- If there exists no valid assignment, skip the following routine.
1407 --
1408 IF l_xla_ae_header_id_tbl.COUNT > 0 THEN
1409 --
1410 -- Sort Accounting Entries by Sequence Version ID (Avoid Deadlock)
1411 -- Sorting Option at Accounting event can be GL Date or none.
1412 --
1413 IF l_sort_option_code = 'GL_DATE' THEN
1414 l_date_dummy_tbl := l_gl_date_tbl;
1415 END IF;
1416
1417 Sort_Acct_Entries (
1418 p_calling_program => 'ACCOUNTING',
1419 p_application_id_tbl => l_num_dummy_tbl,
1420 p_ae_header_id_tbl => l_xla_ae_header_id_tbl,
1421 p_assign_id_tbl => l_xla_assign_id_tbl,
1422 p_seq_ver_id_tbl => l_xla_seq_ver_id_tbl,
1423 p_sorting_key_tbl => l_date_dummy_tbl,
1424 x_application_id_tbl => l_num_dummy_tbl,
1425 x_ae_header_id_tbl => l_sorted_ae_header_id_tbl,
1426 x_assign_id_tbl => l_sorted_assign_id_tbl,
1427 x_seq_ver_id_tbl => l_sorted_seq_ver_id_tbl);
1428 --
1429 -- Generate Numbers in Bulk
1430 --
1431 IF l_sorted_ae_header_id_tbl.COUNT > 0 THEN
1432 generate_bulk_numbers(
1433 p_request_id => NVL(p_request_id,-99), -- Use cache
1434 p_seq_ver_id_tbl => l_sorted_seq_ver_id_tbl,
1435 p_assign_id_tbl => l_sorted_assign_id_tbl,
1436 x_seq_value_tbl => l_sorted_seq_value_tbl,
1437 x_seq_date_tbl => l_sorted_seq_date_tbl);
1438 END IF;
1439 --
1440 -- Update XLA View in Bulk
1441 --
1442 IF l_sorted_assign_id_tbl.COUNT > 0 THEN
1443 FOR i IN l_sorted_assign_id_tbl.FIRST .. l_sorted_assign_id_tbl.LAST LOOP
1444 UPDATE xla_acct_prog_seq_v
1445 SET completion_acct_seq_assign_id = l_sorted_assign_id_tbl(i),
1446 completion_acct_seq_version_id = l_sorted_seq_ver_id_tbl(i),
1447 completion_acct_seq_value = l_sorted_seq_value_tbl(i),
1448 completion_date = l_sorted_seq_date_tbl(i)
1449 WHERE ae_header_id = l_sorted_ae_header_id_tbl(i);
1450 END LOOP;
1451
1452 END IF;
1453 END IF; -- l_xla_ae_header_id_tbl.COUNT > 0
1454
1455 --
1456 -- Debug Information
1457 --
1458 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1459 FND_LOG.STRING(
1460 FND_LOG.LEVEL_PROCEDURE,
1461 'fun.plsql.fun_seq_batchpopulate_acct_seq_prog_view.end',
1462 'p_request_id: ' || p_request_id );
1463 END IF;
1464
1465 EXCEPTION
1466 WHEN no_assigned_seq_info THEN
1467 --
1468 -- Logging
1469 --
1470 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1471 fnd_log.string(
1472 fnd_log.level_exception,
1473 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view',
1474 'p_request_id: ' || p_request_id ||', ' ||
1475 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1476 'SQLERRM: ' || SQLERRM);
1477 END IF;
1478 --
1479 -- Raise Exception
1480 --
1481 fnd_message.set_name ('FUN','FUN_SEQ_NO_ACTIVE_ASSGN_FOUND');
1482 fnd_message.set_token ('SEQ_CONTEXT_NAME',
1483 fun_seq.get_seq_context_name(null));
1484 app_exception.raise_exception;
1485 WHEN OTHERS THEN
1486 --
1487 -- Logging
1488 --
1489 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1490 fnd_log.string(
1491 fnd_log.level_exception,
1492 'fun.plsql.fun_seq_batch.populate_acct_seq_prog_view',
1493 'Unexpected exception in Populate_Acct_Seq_Prog_View' || ', ' ||
1494 'p_request_id: ' || p_request_id ||', ' ||
1495 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1496 'SQLERRM: ' || SQLERRM);
1497 END IF;
1498 app_exception.raise_exception;
1499 END Populate_Acct_Seq_Prog_View;
1500
1501 --
1502 -- Populate sequencing information in xla_seq_je_headers_gt.
1503 -- XLA inserts 'Actual' journal entries into this table.
1504 --
1505 --
1506 PROCEDURE Populate_Rep_Seq_Prog_Gt(
1507 p_request_id IN NUMBER) IS
1508
1509 l_ae_header_id_tbl ae_header_id_tbl_type;
1510 l_application_id_tbl num_tbl_type;
1511 l_table_name_tbl vc30_tbl_type;
1512 l_ledger_id_tbl ledger_id_tbl_type;
1513 l_je_source_name_tbl je_source_name_tbl_type;
1514 l_je_category_name_tbl je_category_name_tbl_type;
1515 l_gl_date_tbl date_tbl_type;
1516 l_reference_date_tbl date_tbl_type;
1517 l_completion_date_tbl date_tbl_type; -- Completion /Posted Date
1518
1519 l_ctrl_attr_rec fun_seq.control_attribute_rec_type;
1520 l_ctrl_date_tbl fun_seq.control_date_tbl_type
1521 := fun_seq.control_date_tbl_type();
1522
1523 --
1524 -- Values to be stored in the XLA Temporary Table
1525 --
1526 l_xla_application_id_tbl num_tbl_type;
1527 l_xla_ae_header_id_tbl ae_header_id_tbl_type;
1528 l_xla_seq_ver_id_tbl seq_ver_id_tbl_type;
1529 l_xla_assign_id_tbl assign_id_tbl_type;
1530 l_xla_seq_value_tbl seq_value_tbl_type;
1531 l_xla_gl_date_tbl date_tbl_type;
1532 l_xla_reference_date_tbl date_tbl_type;
1533 l_xla_completion_date_tbl date_tbl_type;
1534
1535 l_seq_ver_id_tbl seq_ver_id_tbl_type;
1536 l_assign_id_tbl assign_id_tbl_type;
1537 l_out_ctrl_dt_tbl date_tbl_type;
1538 l_req_assign_flag_tbl req_assign_flag_tbl_type;
1539 l_sorting_key_tbl date_tbl_type;
1540 l_error_code_tbl error_code_tbl_type;
1541
1542 l_sort_option_code fun_seq_contexts.sort_option%TYPE;
1543 l_dummy fun_seq_headers.gapless_flag%TYPE;
1544
1545 l_sorted_application_id_tbl num_tbl_type;
1546 l_sorted_ae_header_id_tbl ae_header_id_tbl_type;
1547 l_sorted_seq_ver_id_tbl seq_ver_id_tbl_type;
1548 l_sorted_assign_id_tbl assign_id_tbl_type;
1549 l_sorted_seq_value_tbl seq_value_tbl_type;
1550 l_sorted_seq_date_tbl date_tbl_type;
1551 l_dummy_date_tbl date_tbl_type;
1552
1553 no_assigned_seq_info EXCEPTION;
1554 j BINARY_INTEGER DEFAULT 1;
1555
1556 l_debug_ae_header_id NUMBER;
1557 invalid_sort_option EXCEPTION;
1558 l_context_name VARCHAR2(200);
1559 l_context_type VARCHAR2(50);
1560 l_application_id NUMBER;
1561 l_context_value NUMBER;
1562 l_table_name VARCHAR2(50);
1563
1564
1565 BEGIN
1566 --
1567 -- Debug Information
1568 --
1569 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1570 FND_LOG.STRING(
1571 FND_LOG.LEVEL_PROCEDURE,
1572 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.begin',
1573 'p_request_id => ' || p_request_id );
1574 END IF;
1575
1576 --
1577 -- Bulk Collect Accounting Entry Information
1578 --
1579 SELECT ae_header_id,
1580 application_id,
1581 DECODE(application_id,602,'XLA_AE_HEADERS',
1582 101,'GL_JE_HEADERS',
1583 null),
1584 ledger_id,
1585 je_source_name,
1586 je_category_name,
1587 gl_date,
1588 reference_date,
1589 completion_posted_date
1590 BULK COLLECT INTO
1591 l_ae_header_id_tbl,
1592 l_application_id_tbl,
1593 l_table_name_tbl,
1594 l_ledger_id_tbl,
1595 l_je_source_name_tbl,
1596 l_je_category_name_tbl,
1597 l_gl_date_tbl,
1598 l_reference_date_tbl,
1599 l_completion_date_tbl
1600 FROM xla_seq_je_headers_gt
1601 WHERE sequence_assign_id IS NULL
1602 AND sequence_version_id IS NULL
1603 AND sequence_value IS NULL;
1604
1605 --
1606 -- Retrieve Sequence Information
1607 --
1608 IF l_ae_header_id_tbl.COUNT > 0 THEN
1609 FOR i IN l_ae_header_id_tbl.FIRST .. l_ae_header_id_tbl.LAST LOOP
1610 --
1611 -- to be used in exception section
1612 --
1613 l_debug_ae_header_id := l_ae_header_id_tbl(i);
1614 l_context_type := 'LEDGER_AND_CURRENCY';
1615 l_application_id:= l_application_id_tbl(i);
1616 l_context_value:= l_ledger_id_tbl(i);
1617 l_table_name:=l_table_name_tbl(i);
1618
1619 --
1620 -- Prepare parameters to retrieve Sequence information
1621 --
1622
1623 --
1624 -- Completion / Posted Date is not used to retrived Sequencing
1625 -- information. It is just for sorting.
1626 --
1627 l_ctrl_date_tbl.EXTEND(3);
1628 l_ctrl_date_tbl(1).date_type := 'GL_DATE';
1629 l_ctrl_date_tbl(1).date_value := l_gl_date_tbl(i);
1630 l_ctrl_date_tbl(2).date_type := 'REFERENCE_DATE';
1631 l_ctrl_date_tbl(2).date_value := l_reference_date_tbl(i);
1632 --
1633 -- balance type is always 'Actual' for reporting sequencing
1634 --
1635 l_ctrl_attr_rec.balance_type := 'A';
1636 l_ctrl_attr_rec.journal_source := l_je_source_name_tbl(i);
1637 l_ctrl_attr_rec.journal_category := l_je_category_name_tbl(i);
1638 --
1639 -- Debug Information
1640 --
1641 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1642 FND_LOG.STRING(
1643 FND_LOG.LEVEL_EVENT,
1644 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.config',
1645 'Before calling fun_seq.get_assigned_sequence_info... '
1646 ||', '||
1647 'l_debug_ae_header_id: '
1648 || l_debug_ae_header_id ||', '||
1649 'l_ctrl_date_tbl(1).date_value: '
1650 || l_ctrl_date_tbl(1).date_value ||', '||
1651 'l_ctrl_date_tbl(2).date_value: '
1652 || l_ctrl_date_tbl(2).date_value ||', '||
1653 'l_ctrl_attr_rec.journal_source: '
1654 || l_ctrl_attr_rec.journal_source
1655 );
1656 END IF;
1657
1658 --
1659 -- Get Assignment and Version
1660 --
1661 -- Sorting options of sequencing contexts are identical
1662 -- within the temporary table.
1663 --
1664 fun_seq.get_assigned_sequence_info(
1665 p_context_type => 'LEDGER_AND_CURRENCY',
1666 p_context_value => l_ledger_id_tbl(i),
1667 p_application_id => l_application_id_tbl(i),
1668 p_table_name => l_table_name_tbl(i),
1669 p_event_code => 'PERIOD_CLOSE',
1670 p_control_attribute_rec => l_ctrl_attr_rec,
1671 p_control_date_tbl => l_ctrl_date_tbl,
1672 p_request_id => -1, -- Use Cache
1673 p_suppress_error => 'Y',
1674 x_sequence_type => l_dummy,
1675 x_seq_version_id => l_seq_ver_id_tbl(i),
1676 x_assignment_id => l_assign_id_tbl(i),
1677 x_control_date_value => l_out_ctrl_dt_tbl(i),
1678 x_req_assign_flag => l_req_assign_flag_tbl(i),
1679 x_sort_option_code => l_sort_option_code,
1680 x_error_code => l_error_code_tbl(i));
1681
1682 --
1683 -- Debug Information
1684 --
1685 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1686 FND_LOG.STRING(
1687 FND_LOG.LEVEL_EVENT,
1688 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.config',
1689 'After calling fun_seq.get_assigned_sequence_info... '
1690 ||', '||
1691 'l_seq_ver_id_tbl(i): ' || l_seq_ver_id_tbl(i) ||', '||
1692 'l_assign_id_tbl(i): ' || l_assign_id_tbl(i) ||', '||
1693 'l_out_ctrl_dt_tbl(i): '|| l_out_ctrl_dt_tbl(i)||', '||
1694 'l_req_assign_flag_tbl(i): '
1695 || l_req_assign_flag_tbl(i) ||', '||
1696 'l_error_code_tbl(i): ' || l_error_code_tbl(i)
1697 );
1698 END IF;
1699
1700 --
1701 -- Raise Exception for Require Assignment Violation
1702 --
1703 IF l_error_code_tbl(i) = 'ENFORCED_NO_ASSIGNMENT' THEN
1704 RAISE no_assigned_seq_info;
1705 END IF;
1706
1707 --
1708 -- Prepare parameters to generate sequence numbers in batch
1709 --
1710 IF l_assign_id_tbl(i) IS NOT NULL THEN
1711 l_xla_application_id_tbl(j)
1712 := l_application_id_tbl(i);
1713 l_xla_ae_header_id_tbl(j)
1714 := l_ae_header_id_tbl(i);
1715 l_xla_assign_id_tbl(j)
1716 := l_assign_id_tbl(i);
1717 l_xla_seq_ver_id_tbl(j)
1718 := l_seq_ver_id_tbl(i);
1719 l_xla_gl_date_tbl(j)
1720 := l_gl_date_tbl(i);
1721 l_xla_reference_date_tbl(j)
1722 := l_reference_date_tbl(i);
1723 l_xla_completion_date_tbl(j)
1724 := l_completion_date_tbl(i);
1725 j := j + 1;
1726 END IF;
1727 END LOOP;
1728 END IF;
1729
1730 --
1731 -- If there exists no valid assignment, skip the following routine.
1732 --
1733 IF l_xla_ae_header_id_tbl.COUNT > 0 THEN
1734 --
1735 -- Debug Information
1736 --
1737 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1738 FND_LOG.STRING(
1739 FND_LOG.LEVEL_STATEMENT,
1740 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.config',
1741 'Before calling Sort_Acct_Entires: Sort Option - ' ||
1742 l_sort_option_code);
1743 END IF;
1744
1745 --
1746 -- Sort Accounting Entries by Sequence Version ID (Avoid Deadlock)
1747 --
1748 IF l_sort_option_code = 'GL_DATE' THEN
1749 l_sorting_key_tbl := l_xla_gl_date_tbl;
1750 ELSIF l_sort_option_code = 'REFERENCE_DATE' THEN
1751 l_sorting_key_tbl := l_xla_reference_date_tbl;
1752 ELSIF l_sort_option_code = 'COMPLETION_OR_POSTING_DATE' THEN
1753 l_sorting_key_tbl := l_xla_completion_date_tbl;
1754 ELSE
1755 RAISE invalid_sort_option;
1756 END IF;
1757
1758 Sort_Acct_Entries (
1759 p_calling_program => 'REPORTING',
1760 p_application_id_tbl => l_xla_application_id_tbl,
1761 p_ae_header_id_tbl => l_xla_ae_header_id_tbl,
1762 p_assign_id_tbl => l_xla_assign_id_tbl,
1763 p_seq_ver_id_tbl => l_xla_seq_ver_id_tbl,
1764 p_sorting_key_tbl => l_sorting_key_tbl,
1765 x_application_id_tbl => l_sorted_application_id_tbl,
1766 x_ae_header_id_tbl => l_sorted_ae_header_id_tbl,
1767 x_assign_id_tbl => l_sorted_assign_id_tbl,
1768 x_seq_ver_id_tbl => l_sorted_seq_ver_id_tbl);
1769
1770 --
1771 -- Debug Information
1772 --
1773 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1774 FND_LOG.STRING(
1775 FND_LOG.LEVEL_STATEMENT,
1776 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.config',
1777 'After calling Sort_Acct_Entires');
1778 END IF;
1779
1780 --
1781 -- Generate Numbers in Bulk
1782 --
1783 IF l_sorted_ae_header_id_tbl.COUNT > 0 THEN
1784 generate_bulk_numbers(
1785 p_request_id => NVL(p_request_id,-99), -- Use cache
1786 p_seq_ver_id_tbl => l_sorted_seq_ver_id_tbl,
1787 p_assign_id_tbl => l_sorted_assign_id_tbl,
1788 x_seq_value_tbl => l_sorted_seq_value_tbl,
1789 x_seq_date_tbl => l_sorted_seq_date_tbl);
1790 END IF;
1791 --
1792 -- Update XLA View in Bulk
1793 --
1794 IF l_sorted_assign_id_tbl.COUNT > 0 THEN
1795 FORALL i IN l_sorted_assign_id_tbl.FIRST .. l_sorted_assign_id_tbl.LAST
1796 UPDATE xla_seq_je_headers_gt
1797 SET sequence_assign_id = l_sorted_assign_id_tbl(i),
1798 sequence_version_id = l_sorted_seq_ver_id_tbl(i),
1799 sequence_value = l_sorted_seq_value_tbl(i)
1800 WHERE application_id = l_sorted_application_id_tbl(i)
1801 AND ae_header_id = l_sorted_ae_header_id_tbl(i);
1802 END IF;
1803 END IF; -- l_xla_ae_header_id_tbl.COUNT > 0
1804
1805 --
1806 -- Debug Information
1807 --
1808 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1809 FND_LOG.STRING(
1810 FND_LOG.LEVEL_PROCEDURE,
1811 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt.end',
1812 'p_request_id: ' || p_request_id );
1813 END IF;
1814 EXCEPTION
1815 WHEN no_assigned_seq_info THEN
1816 --
1817 -- Logging
1818 --
1819 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1820 fnd_log.string(
1821 fnd_log.level_exception,
1822 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt',
1823 'p_request_id: ' || p_request_id ||', ' ||
1824 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1825 'SQLERRM: ' || SQLERRM);
1826 END IF;
1827
1828 SELECT name
1829 INTO l_context_name
1830 FROM fun_seq_contexts WHERE
1831 context_type=l_context_type AND
1832 context_value=l_context_value AND
1833 application_id=l_application_id AND
1834 table_name=l_table_name AND
1835 event_code='PERIOD_CLOSE';
1836
1837 --
1838 -- Raise Exception
1839 --
1840 fnd_message.set_name ('FUN','FUN_SEQ_NO_ACTIVE_ASSGN_FOUND');
1841 fnd_message.set_token ('SEQ_CONTEXT_NAME',l_context_name);
1842
1843 app_exception.raise_exception;
1844
1845 WHEN invalid_sort_option THEN
1846 --
1847 -- Logging
1848 --
1849 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1850 fnd_log.string(
1851 fnd_log.level_exception,
1852 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt',
1853 'p_request_id: ' || p_request_id || ', ' ||
1854 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1855 'sort_option_code: ' || l_sort_option_code || ', ' ||
1856 'SQLERRM: ' || SQLERRM);
1857 END IF;
1858 --
1859 -- Raise Exception
1860 --
1861 app_exception.raise_exception;
1862
1863 WHEN OTHERS THEN
1864 --
1865 -- Logging
1866 --
1867 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1868 fnd_log.string(
1869 fnd_log.level_exception,
1870 'fun.plsql.fun_seq_batch.populate_rep_seq_prog_gt',
1871 'Unexpected exception in populate_rep_seq_prog_gt' || ', ' ||
1872 'p_request_id: ' || p_request_id ||', ' ||
1873 'ae_header_id: ' || l_debug_ae_header_id|| ', ' ||
1874 'SQLERRM: ' || SQLERRM);
1875 END IF;
1876
1877 app_exception.raise_exception;
1878
1879 END Populate_Rep_Seq_Prog_Gt;
1880
1881 PROCEDURE Sort_Acct_Entries (
1882 p_calling_program IN VARCHAR2,
1883 p_application_id_tbl IN num_tbl_type,
1884 p_ae_header_id_tbl IN ae_header_id_tbl_type,
1885 p_assign_id_tbl IN assign_id_tbl_type,
1886 p_seq_ver_id_tbl IN seq_ver_id_tbl_type,
1887 p_sorting_key_tbl IN date_tbl_type,
1888 x_application_id_tbl OUT NOCOPY num_tbl_type,
1889 x_ae_header_id_tbl OUT NOCOPY ae_header_id_tbl_type,
1890 x_assign_id_tbl OUT NOCOPY assign_id_tbl_type,
1891 x_seq_ver_id_tbl OUT NOCOPY seq_ver_id_tbl_type) IS
1892
1893 l_temp_tab fun_seq_bt_tbl_type;
1894
1895 BEGIN
1896 l_temp_tab := fun_seq_bt_tbl_type();
1897 --
1898 -- Debug Information
1899 --
1900 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1901 FND_LOG.STRING(
1902 FND_LOG.LEVEL_PROCEDURE,
1903 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.begin',
1904 'p_ae_header_id_tbl.count => ' || p_ae_header_id_tbl.COUNT);
1905 END IF;
1906 --
1907 -- Populate Sequence Info in Table Type
1908 --
1909 IF p_ae_header_id_tbl.COUNT > 0 THEN
1910 --
1911 -- Populate Parameter Values to Table
1912 --
1913 IF p_calling_program = 'ACCOUNTING' THEN
1914 --
1915 -- bug#5434859 added IF clause
1916 --
1917 IF p_sorting_key_tbl.COUNT > 0 THEN
1918 -- bug# 5373090 - Italian requirement
1919 FOR i IN p_ae_header_id_tbl.FIRST .. p_ae_header_id_tbl.LAST LOOP
1920 l_temp_tab.EXTEND;
1921 l_temp_tab(i) :=
1922 fun_seq_bt_obj_type(
1923 NULL, -- Application Id for Reporting Sequencing
1924 p_ae_header_id_tbl(i),
1925 p_assign_id_tbl(i),
1926 p_seq_ver_id_tbl(i),
1927 fnd_date.date_to_canonical(p_sorting_key_tbl(i)));
1928 END LOOP;
1929 ELSE
1930 FOR i IN p_ae_header_id_tbl.FIRST .. p_ae_header_id_tbl.LAST LOOP
1931 l_temp_tab.EXTEND;
1932 l_temp_tab(i) :=
1933 fun_seq_bt_obj_type(
1934 NULL, -- Application Id for Reporting Sequencing
1935 p_ae_header_id_tbl(i),
1936 p_assign_id_tbl(i),
1937 p_seq_ver_id_tbl(i),
1938 null);
1939 END LOOP;
1940 END IF;
1941
1942 --
1943 -- Debug Information
1944 --
1945 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1946 FND_LOG.STRING(
1947 FND_LOG.LEVEL_STATEMENT,
1948 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.config',
1949 'Local temp table has been populated: ' || p_calling_program);
1950 END IF;
1951
1952 --
1953 -- Sort Accounting Entries by Sequence Versions
1954 --
1955 SELECT sqtmp.source_id,
1956 sqtmp.assignment_id,
1957 sqtmp.seq_version_id
1958 -- Don't need sorting key for the Completion event
1959 BULK COLLECT
1960 INTO x_ae_header_id_tbl,
1961 x_assign_id_tbl,
1962 x_seq_ver_id_tbl
1963 FROM THE (SELECT CAST( l_temp_tab as fun_seq_bt_tbl_type)
1964 FROM dual ) sqtmp
1965 ORDER BY
1966 sqtmp.seq_version_id,
1967 sqtmp.sorting_key;
1968
1969 ELSIF p_calling_program = 'REPORTING' THEN
1970 --
1971 -- Populate Parameter Values to Table
1972 --
1973 FOR i IN p_ae_header_id_tbl.FIRST .. p_ae_header_id_tbl.LAST LOOP
1974 l_temp_tab.EXTEND;
1975 l_temp_tab(i) :=
1976 fun_seq_bt_obj_type(
1977 p_application_id_tbl(i),
1978 p_ae_header_id_tbl(i),
1979 p_assign_id_tbl(i),
1980 p_seq_ver_id_tbl(i),
1981 fnd_date.date_to_canonical(p_sorting_key_tbl(i)));
1982 END LOOP;
1983
1984 --
1985 -- Debug Information
1986 --
1987 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1988 FND_LOG.STRING(
1989 FND_LOG.LEVEL_STATEMENT,
1990 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.config',
1991 'Local temp table has been populated: ' || p_calling_program);
1992 END IF;
1993
1994 --
1995 -- Sort Accounting Entries by Sequence Versions and Control Dates
1996 --
1997 SELECT sqtmp.application_id,
1998 sqtmp.source_id,
1999 sqtmp.assignment_id,
2000 sqtmp.seq_version_id
2001 BULK COLLECT
2002 INTO x_application_id_tbl,
2003 x_ae_header_id_tbl,
2004 x_assign_id_tbl,
2005 x_seq_ver_id_tbl
2006 FROM THE (SELECT CAST( l_temp_tab as fun_seq_bt_tbl_type)
2007 FROM dual ) sqtmp
2008 ORDER BY
2009 sqtmp.seq_version_id,
2010 sqtmp.sorting_key;
2011
2012 END IF; -- p_calling_program = <ACCOUNTING/REPORTING>
2013 END IF; -- p_ae_header_id_tbl.COUNT > 0
2014
2015 --
2016 -- Debug Information
2017 --
2018 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2019 FND_LOG.STRING(
2020 FND_LOG.LEVEL_PROCEDURE,
2021 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.end',
2022 'p_ae_header_id_tbl.count => ' || p_ae_header_id_tbl.COUNT);
2023 END IF;
2024 EXCEPTION
2025 WHEN OTHERS THEN
2026 --
2027 -- Logging
2028 --
2029 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2030 fnd_log.string(
2031 log_level => fnd_log.level_exception,
2032 module => 'fun.plsql.fun_seq_batch.Sort_Acct_Entries.Exception',
2033 message => 'SQLERRM: ' || SQLERRM);
2034 END IF;
2035 app_exception.raise_exception;
2036 END Sort_Acct_Entries;
2037 END fun_seq_batch;