[Home] [Help]
PACKAGE BODY: APPS.XLA_REPORTING_SEQUENCE_PKG
Source
1 PACKAGE BODY XLA_REPORTING_SEQUENCE_PKG AS
2 -- $Header: xlarepseq.pkb 120.7.12010000.2 2009/03/18 14:15:48 nksurana ship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | XLA_REPORTING_SEQUENCE_PKG |
10 | |
11 | DESCRIPTION |
12 | Package body for reporting sequence. |
13 | |
14 | HISTORY |
15 | 07/16/2004 W. Shen Created |
16 +===========================================================================*/
17
18 --=============================================================================
19 -- **************** declaraions ********************
20 --=============================================================================
21
22
23 C_PERIOD_STATUS_C CONSTANT VARCHAR2(1) :='C'; --closed
24 C_PERIOD_STATUS_O CONSTANT VARCHAR2(1) :='O'; --open
25 C_PERIOD_STATUS_F CONSTANT VARCHAR2(1) :='F'; --future entry
26 C_PERIOD_STATUS_N CONSTANT VARCHAR2(1) :='N'; --Never opened
27 C_PERIOD_STATUS_P CONSTANT VARCHAR2(1) :='P'; --Permanently closed
28
29 TYPE t_reset_seq IS REF CURSOR;
30
31 PROCEDURE reset_reporting_seq_num(p_ledger_id IN NUMBER
32 , p_start_date IN DATE
33 , p_end_date IN DATE
34 , p_sort_date IN VARCHAR2);
35 PROCEDURE populate_seq_gt_table(p_ledger_id IN NUMBER
36 , p_start_date IN DATE
37 , p_end_date IN DATE
38 , p_sort_date IN VARCHAR2);
39 PROCEDURE update_entries_from_gt;
40
41 PROCEDURE assign_sequence(p_ledger_id IN NUMBER
42 , p_period_name IN VARCHAR2
43 , p_errbuf OUT NOCOPY VARCHAR2
44 , p_retcode OUT NOCOPY NUMBER);
45
46 PROCEDURE reset_sequence(p_ledger_id IN NUMBER
47 , p_period_name IN VARCHAR2
48 , p_errbuf OUT NOCOPY VARCHAR2
49 , p_retcode OUT NOCOPY NUMBER);
50
51 --=============================================================================
52 -- *********** Local Trace Routine **********
53 --=============================================================================
54 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
55 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
56 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
57 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
58 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
59 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
60
61 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
62 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.XLA_REPORTING_SEQUENCE_PKG';
63
64 g_log_level NUMBER;
65 g_log_enabled BOOLEAN;
66
67 PROCEDURE trace
68 (p_msg IN VARCHAR2
69 ,p_level IN NUMBER
70 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
71 BEGIN
72 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
73 fnd_log.message(p_level, p_module);
74 ELSIF p_level >= g_log_level THEN
75 fnd_log.string(p_level, p_module, p_msg);
76 END IF;
77
78 EXCEPTION
79 WHEN xla_exceptions_pkg.application_exception THEN
80 RAISE;
81 WHEN OTHERS THEN
82 xla_exceptions_pkg.raise_message
83 (p_location => 'XLA_REPORTING_SEQUENCE_PKG.trace');
84 END trace;
85
86
87 --=============================================================================
88 -- This function is the subscription routine to gl workflow business event
89 -- oracle.apps.gl.CloseProcess.period.close. It get the parameters of the
90 -- event and submit a concurrent request
91 --=============================================================================
92
93 FUNCTION period_close(p_subscription_guid IN raw,
94 p_event IN OUT NOCOPY WF_EVENT_T) return varchar2 is
95 l_parameter_list wf_parameter_list_t;
96 l_ledger_id number;
97 l_period_name varchar2(100);
98 l_log_module VARCHAR2(240);
99 l_request_id number;
100 BEGIN
101
102 IF g_log_enabled THEN
103 l_log_module := C_DEFAULT_MODULE||'.period_close';
104 END IF;
105 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
106 trace
107 (p_msg => 'BEGIN of procedure period_close'
108 ,p_level => C_LEVEL_PROCEDURE
109 ,p_module => l_log_module);
110 END IF;
111
112 -- get the parameter of the event
113 l_parameter_list := p_event.getParameterList;
114 l_period_name:=wf_event.getValueForParameter('PERIOD_NAME', l_parameter_list);
115 l_ledger_id:=to_number(wf_event.getValueForParameter('LEDGER_ID', l_parameter_list));
116 -- insert_to('close:'||l_period_name ||' '||to_char(l_ledger_id));
117
118 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
119 trace
120 (p_msg => 'period_name:'|| l_period_name
121 || ' ledger_id:'||to_char(l_ledger_id)
122 ,p_level => C_LEVEL_STATEMENT
123 ,p_module => l_log_module);
124 END IF;
125
126 -- launch the concurrent request
127 l_request_id :=
128 fnd_request.submit_request
129 (application => 'XLA'
130 ,program => 'XLAREPSEQ'
131 ,description => NULL
132 ,start_time => NULL
133 ,sub_request => FALSE
134 ,argument1 => l_ledger_id
135 ,argument2 => l_period_name
136 ,argument3 => 'ASSIGN');
137 IF l_request_id = 0 THEN
138 xla_exceptions_pkg.raise_message
139 (p_appli_s_name => 'XLA'
140 ,p_msg_name => 'XLA_REP_TECHNICAL_ERROR'
141 ,p_token_1 => 'APPLICATION_NAME'
142 ,p_value_1 => 'SLA');
143
144 END IF;
145 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
146 trace
147 (p_msg => 'END of procedure period_close'
148 ,p_level => C_LEVEL_PROCEDURE
149 ,p_module => l_log_module);
150 END IF;
151
152 return 'SUCCESS';
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 return 'ERROR';
157 END period_close;
158
159 --=============================================================================
160 -- This function is the subscription routine to gl workflow business event
161 -- oracle.apps.gl.CloseProcess.period.reopen. It get the parameters of the
162 -- event and submit a concurrent request
163 --=============================================================================
164
165 FUNCTION period_reopen(p_subscription_guid IN raw,
166 p_event IN OUT NOCOPY WF_EVENT_T) return varchar2 is
167 l_parameter_list wf_parameter_list_t;
168 l_ledger_id number;
169 l_period_name varchar2(100);
170 l_log_module VARCHAR2(240);
171 l_request_id NUMBER;
172 BEGIN
173 IF g_log_enabled THEN
174 l_log_module := C_DEFAULT_MODULE||'.period_reopen';
175 END IF;
176 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
177 trace
178 (p_msg => 'BEGIN of procedure period_reopen'
179 ,p_level => C_LEVEL_PROCEDURE
180 ,p_module => l_log_module);
181 END IF;
182
183
184 -- get the parameter of the event
185 l_parameter_list := p_event.getParameterList;
186 l_period_name:=wf_event.getValueForParameter('PERIOD_NAME', l_parameter_list);
187 l_ledger_id:=to_number(wf_event.getValueForParameter('LEDGER_ID', l_parameter_list));
188 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
189 trace
190 (p_msg => 'periodname:'||l_period_name ||' ledger_id:'||to_char(l_ledger_id)
191 ,p_level => C_LEVEL_STATEMENT
192 ,p_module => l_log_module);
193 END IF;
194
195 -- launch the concurrent request
196 l_request_id :=
197 fnd_request.submit_request
198 (application => 'XLA'
199 ,program => 'XLAREPSEQ'
200 ,description => NULL
201 ,start_time => NULL
202 ,sub_request => FALSE
203 ,argument1 => l_ledger_id
204 ,argument2 => l_period_name
205 ,argument3 => 'RESET');
206 IF l_request_id = 0 THEN
207 xla_exceptions_pkg.raise_message
208 (p_appli_s_name => 'XLA'
209 ,p_msg_name => 'XLA_REP_TECHNICAL_ERROR'
210 ,p_token_1 => 'APPLICATION_NAME'
211 ,p_value_1 => 'SLA');
212
213 END IF;
214 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
215 trace
216 (p_msg => 'END of procedure period_reopen'
217 ,p_level => C_LEVEL_PROCEDURE
218 ,p_module => l_log_module);
219 END IF;
220
221 return 'SUCCESS';
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 return 'ERROR';
226 END period_reopen;
227
228
229 --=============================================================================
230 -- This procedure is registered as a concurrent request to assign/reset the
231 -- reporting sequence. The concurrent request is launched when gl period is
232 -- closed or reopened.
233 --=============================================================================
234
235 PROCEDURE reporting_sequence(p_errbuf OUT NOCOPY VARCHAR2
236 , p_retcode OUT NOCOPY NUMBER
237 , p_ledger_id IN NUMBER
238 , p_period_name IN VARCHAR2
239 , p_mode IN VARCHAR2) is
240
241 l_log_module VARCHAR2(240);
242 BEGIN
243 IF g_log_enabled THEN
244 l_log_module := C_DEFAULT_MODULE||'.reporting_sequence';
245 END IF;
246 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
247 trace
248 (p_msg => 'BEGIN of procedure reporting_sequence'
249 ,p_level => C_LEVEL_PROCEDURE
250 ,p_module => l_log_module);
251 END IF;
252
253 IF(p_mode='ASSIGN') THEN
254 assign_sequence(p_ledger_id => p_ledger_id
255 ,p_period_name => p_period_name
256 ,p_errbuf => p_errbuf
257 ,p_retcode => p_retcode);
258 ELSIF(p_mode='RESET') THEN
259 reset_sequence(p_ledger_id => p_ledger_id
260 ,p_period_name => p_period_name
261 ,p_errbuf => p_errbuf
262 ,p_retcode => p_retcode);
263 ELSE
264 p_errbuf := 'INVALID MODE';
265 p_retcode := 2;
266 END IF;
267 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
268 trace
269 (p_msg => 'end of procedure reporting_sequence'
270 ,p_level => C_LEVEL_PROCEDURE
271 ,p_module => l_log_module);
272 END IF;
273
274 END reporting_sequence;
275
276 PROCEDURE get_end_date(p_ledger_id IN NUMBER
277 ,p_end_date IN OUT NOCOPY DATE) is
278
279 l_end_date DATE;
280 l_log_module VARCHAR2(240);
281 BEGIN
282 IF g_log_enabled THEN
283 l_log_module := C_DEFAULT_MODULE||'.get_end_date';
284 END IF;
285 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
286 trace
287 (p_msg => 'BEGIN of procedure get_end_date'
288 ,p_level => C_LEVEL_PROCEDURE
289 ,p_module => l_log_module);
290 trace
291 (p_msg => 'parameter:ledger_id:'||to_char(p_ledger_id)
292 || ' end_date:'||to_char(p_end_date)
293 ,p_level => C_LEVEL_PROCEDURE
294 ,p_module => l_log_module);
295 END IF;
296
297
298 SELECT min(start_date)
299 INTO l_end_date
300 FROM gl_period_statuses
301 WHERE ledger_id=p_ledger_id
302 AND adjustment_period_flag = 'N'
303 AND CLOSING_STATUS in (C_PERIOD_STATUS_O, C_PERIOD_STATUS_F, C_PERIOD_STATUS_N)
304 AND start_date>p_end_date
305 AND application_id = 101;
306
307 IF (l_end_date is null) THEN
308 -- all the following period is closed or permentantly closed
309 SELECT max(end_date)
310 INTO l_end_date
311 FROM gl_period_statuses
312 WHERE ledger_id=p_ledger_id
313 AND adjustment_period_flag = 'N'
314 AND end_date>=p_end_date
315 AND application_id = 101;
316 p_end_date := trunc(l_end_date)+1;
317 ELSE
318 p_end_date := trunc(l_end_date);
319 END IF;
320
321 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
322 trace
323 (p_msg => 'END of procedure get_end_date'
324 ,p_level => C_LEVEL_PROCEDURE
325 ,p_module => l_log_module);
326 trace
327 (p_msg => 'return value of end_date'||to_char(p_end_date)
328 ,p_level => C_LEVEL_PROCEDURE
329 ,p_module => l_log_module);
330 END IF;
331 END get_end_date;
332
333 FUNCTION already_assigned(p_ledger_id IN NUMBER
334 ,p_period_name IN VARCHAR2
335 ,p_start_date IN DATE
336 ,p_end_date IN DATE
337 ,p_sort_date IN VARCHAR2 ) return boolean is
338 cursor c_gl_date is
339 SELECT 1
340 FROM gl_je_headers gjh
341 , xla_subledgers xs
342 WHERE gjh.ledger_id = p_ledger_id
343 AND gjh.period_name = p_period_name
344 AND gjh.status = 'P'
345 AND (gjh.parent_je_header_id is not null or xs.je_source_name is null)
346 AND gjh.close_acct_seq_version_id is null
347 AND gjh.default_effective_date>= p_start_date
348 AND gjh.default_effective_date< p_end_date;
349
350 cursor c_ref_date is
351 SELECT 1
352 FROM gl_je_headers gjh
353 , xla_subledgers xs
354 WHERE ledger_id = p_ledger_id
355 AND period_name = p_period_name
356 AND gjh.status = 'P'
357 AND (gjh.parent_je_header_id is not null or xs.je_source_name is null)
358 AND close_acct_seq_version_id is null
359 AND nvl(gjh.reference_date, gjh.posted_date) >= p_start_date
360 AND nvl(gjh.reference_date, gjh.posted_date) < p_end_date;
361
362 cursor c_comp_date is
363 SELECT 1
364 FROM gl_je_headers gjh
365 , xla_subledgers xs
366 WHERE ledger_id = p_ledger_id
367 AND period_name = p_period_name
368 AND gjh.status = 'P'
369 AND (gjh.parent_je_header_id is not null or xs.je_source_name is null)
370 AND close_acct_seq_version_id is null
371 AND gjh.posted_date >= p_start_date
372 AND gjh.posted_date < p_end_date;
373
374 l_temp NUMBER;
375 l_log_module VARCHAR2(240);
376 BEGIN
377 IF g_log_enabled THEN
378 l_log_module := C_DEFAULT_MODULE||'.already_assigned';
379 END IF;
380 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
381 trace
382 (p_msg => 'BEGIN of function already_assigned'
383 ,p_level => C_LEVEL_PROCEDURE
384 ,p_module => l_log_module);
385 END IF;
386
387 IF(p_sort_date = 'GL_DATE') THEN
388 OPEN c_gl_date;
389 FETCH c_gl_date into l_temp;
390 IF(c_gl_date%NOTFOUND) THEN
391 l_temp := 0;
392 END IF;
393 CLOSE c_gl_date;
394 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
395 OPEN c_ref_date;
396 FETCH c_ref_date into l_temp;
397 IF(c_ref_date%NOTFOUND) THEN
398 l_temp := 0;
399 END IF;
400 CLOSE c_ref_date;
401 ELSE
402 OPEN c_comp_date;
403 FETCH c_comp_date into l_temp;
404 IF(c_comp_date%NOTFOUND) THEN
405 l_temp := 0;
406 END IF;
407 CLOSE c_comp_date;
408 END IF;
409
410 IF (l_temp = 0 ) THEN
411 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
412 trace
413 (p_msg => 'end of function already_assigned, return true'
414 ,p_level => C_LEVEL_PROCEDURE
415 ,p_module => l_log_module);
416 END IF;
417
418 RETURN TRUE;
419 ELSE
420 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
421 trace
422 (p_msg => 'end of function already_assigned, return false'
423 ,p_level => C_LEVEL_PROCEDURE
424 ,p_module => l_log_module);
425 END IF;
426 RETURN FALSE;
427 END IF;
428 END already_assigned;
429
430 --=============================================================================
431 -- This procedure is called by the concurrent request.
432 -- reporting sequence. The concurrent request is launched when gl period is
433 -- closed or reopened.
434 --=============================================================================
435
436 PROCEDURE assign_sequence(p_ledger_id IN NUMBER
437 , p_period_name IN VARCHAR2
438 , p_errbuf OUT NOCOPY VARCHAR2
439 , p_retcode OUT NOCOPY NUMBER) is
440 l_exception exception;
441 l_parameter_list wf_parameter_list_t;
442 l_ledger_id number;
443 l_period_name varchar2(100);
444 l_seq_context_value fun_seq_batch.context_value_tbl_type;
445 l_xla_seq_status varchar2(30);
446 l_gl_seq_status varchar2(30);
447 l_start_date gl_period_statuses.start_date%TYPE;
448 l_end_date gl_period_statuses.end_date%TYPE;
449 l_adjustment_flag gl_period_statuses.adjustment_period_flag%TYPE;
450 l_xla_seq_context_id NUMBER;
451 l_gl_seq_context_id NUMBER;
452 l_sort_date VARCHAR2(30);
453
454 l_log_module VARCHAR2(240);
455
456 l_request_id NUMBER;
457
458 l_temp NUMBER;
459 cursor c_open_previous_normal_period is
460 SELECT 1
461 FROM gl_period_statuses
462 WHERE ledger_id = p_ledger_id
463 AND start_date<l_start_date
464 AND closing_status = C_PERIOD_STATUS_O
465 AND adjustment_period_flag = 'N'
466 AND application_id = 101;
467
468
469 begin
470 IF g_log_enabled THEN
471 l_log_module := C_DEFAULT_MODULE||'.assign_sequence';
472 END IF;
473 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
474 trace
475 (p_msg => 'BEGIN of procedure assign_sequence'
476 ,p_level => C_LEVEL_PROCEDURE
477 ,p_module => l_log_module);
478 trace
479 (p_msg => 'parameter: ledger_id:'|| to_char(p_ledger_id)
480 || ' period_name:'|| p_period_name
481 ,p_level => C_LEVEL_PROCEDURE
482 ,p_module => l_log_module);
483 END IF;
484
485 p_retcode := 0;
486
487 -- get the start date and end date of the period
488 SELECT trunc(start_date), trunc(end_date), adjustment_period_flag
489 INTO l_start_date, l_end_date, l_adjustment_flag
490 FROM GL_PERIOD_STATUSES
491 WHERE ledger_id = p_ledger_id
492 AND period_name = p_period_name
493 AND application_id = 101;
494
495 -- find if there is previous open period
496 open c_open_previous_normal_period;
497 fetch c_open_previous_normal_period into l_temp;
498 IF(c_open_previous_normal_period%NOTFOUND) THEN
499 l_temp := 0;
500 END IF;
501 close c_open_previous_normal_period;
502
503 IF(l_temp = 1) THEN
504 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
505 trace
506 (p_msg => 'previous period open, end of procedure assign_sequence'
507 ,p_level => C_LEVEL_PROCEDURE
508 ,p_module => l_log_module);
509 END IF;
510 return;
511 END IF;
512
513 l_request_id := fnd_global.conc_request_id;
514
515 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
516 trace
517 (p_msg => 'request_id:'|| to_char(l_request_id)
518 ,p_level => C_LEVEL_STATEMENT
519 ,p_module => l_log_module);
520 END IF;
521
522 IF (C_LEVEL_EVENT>= g_log_level) THEN
523 trace
524 (p_msg => 'calling fun_seq_batch.batch_init'
525 ,p_level => C_LEVEL_EVENT
526 ,p_module => l_log_module);
527 END IF;
528
529 -- call fun_seq_batch.batch_init
530 -- for xla first
531 l_seq_context_value(1) := p_ledger_id;
532 fun_seq_batch.Batch_init(p_application_id => 602
533 ,p_table_name => 'XLA_AE_HEADERS'
534 ,p_event_code => 'PERIOD_CLOSE'
535 ,p_context_type => 'LEDGER_AND_CURRENCY'
536 ,p_context_value_tbl =>l_seq_context_value
537 ,p_request_id => l_request_id
538 ,x_status => l_xla_seq_status
539 ,x_seq_context_id => l_xla_seq_context_id);
540
541 -- for gl
542 fun_seq_batch.Batch_init(p_application_id => 101
543 ,p_table_name => 'GL_JE_HEADERS'
544 ,p_event_code => 'PERIOD_CLOSE'
545 ,p_context_type => 'LEDGER_AND_CURRENCY'
546 ,p_context_value_tbl =>l_seq_context_value
547 ,p_request_id => l_request_id
548 ,x_status => l_gl_seq_status
549 ,x_seq_context_id => l_gl_seq_context_id);
550
551 IF (C_LEVEL_EVENT>= g_log_level) THEN
552 trace
553 (p_msg => 'after calling fun_seq_batch.batch_init'
554 ,p_level => C_LEVEL_EVENT
555 ,p_module => l_log_module);
556 END IF;
557
558 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
559 trace
560 (p_msg => 'xla_seq_status:'||l_xla_seq_status||' id:'||to_char(l_xla_seq_context_id)
561 ,p_level => C_LEVEL_STATEMENT
562 ,p_module => l_log_module);
563 trace
564 (p_msg => 'gl_seq_status:'||l_gl_seq_status||' id:'||to_char(l_gl_seq_context_id)
565 ,p_level => C_LEVEL_STATEMENT
566 ,p_module => l_log_module);
567 END IF;
568 -- only if there is sequence set up, start the sequence
569 IF (l_xla_seq_status = 'SUCCESS' or l_gl_seq_status = 'SUCCESS') then
570
571 -- get the sort date of each context
572 -- l_sort_date will have value 'GL_DATE', 'REFERENCE_DATE' or 'COMPLETION_DATE'
573 IF(l_gl_seq_status = 'SUCCESS') THEN
574 SELECT nvl(SORT_OPTION, DATE_TYPE)
575 INTO l_sort_date
576 FROM FUN_SEQ_CONTEXTS
577 WHERE SEQ_CONTEXT_ID = l_gl_seq_context_id;
578 ELSE
579 SELECT nvl(SORT_OPTION, DATE_TYPE)
580 INTO l_sort_date
581 FROM FUN_SEQ_CONTEXTS
582 WHERE SEQ_CONTEXT_ID = l_xla_seq_context_id;
583 END IF;
584
585 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
586 trace
587 (p_msg => 'sortdate:'||to_char(l_sort_date)
588 ,p_level => C_LEVEL_STATEMENT
589 ,p_module => l_log_module);
590 END IF;
591
592 IF(l_adjustment_flag = 'Y') THEN
593 --check if there is entries from the adjustment period that is not
594 -- sequenced yet.
595 -- only gl have entry in adjustment period
596 IF(l_gl_seq_status='SUCCESS' and
597 already_assigned(p_ledger_id => p_ledger_id
598 ,p_period_name => p_period_name
599 ,p_start_date => l_start_date
600 ,p_end_date => l_end_date
601 ,p_sort_date => l_sort_date)) THEN
602 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
603 trace
604 (p_msg => 'adjustment period, already assigned. End of assign_sequence'
605 ,p_level => C_LEVEL_PROCEDURE
606 ,p_module => l_log_module);
607 END IF;
608 fun_seq_batch.Batch_exit(p_request_id => l_request_id
609 ,x_status => l_gl_seq_status); --Added for bug 8310543
610 RETURN;
611 END IF;
612 END IF;
613
614 -- get the end date of the date range
615 get_end_date(p_ledger_id => p_ledger_id
616 ,p_end_date => l_end_date);
617
618 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
619 trace
620 (p_msg => 'end date:'||to_char(l_end_date)
621 ,p_level => C_LEVEL_STATEMENT
622 ,p_module => l_log_module);
623 END IF;
624
625 IF(l_adjustment_flag = 'Y') THEN
626 -- get the normal entry start date
627 -- reset the sequence number.
628 SELECT trunc(max(start_date))
629 INTO l_start_date
630 FROM gl_period_statuses
631 WHERE ledger_id = p_ledger_id
632 AND start_date <= l_start_date
633 AND adjustment_period_flag = 'N'
634 AND application_id = 101;
635
636 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
637 trace
638 (p_msg => 'adjustment period, normal start date:'||to_char(l_start_date)
639 ,p_level => C_LEVEL_STATEMENT
640 ,p_module => l_log_module);
641 END IF;
642 -- is it possible that gl has set up but not xla?
643 reset_reporting_seq_num( p_ledger_id => p_ledger_id
644 , p_start_date => l_start_date
645 , p_end_date => l_end_date
646 , p_sort_date => l_sort_date);
647
648 END IF;
649
650 populate_seq_gt_table(p_ledger_id => p_ledger_id
651 ,p_start_date => l_start_date
652 ,p_end_date => l_end_date
653 ,p_sort_date => l_sort_date);
654
655 IF (C_LEVEL_EVENT>= g_log_level) THEN
656 trace
657 (p_msg => 'before calling fun_seq_batch.populate_acct_seq_info'
658 ,p_level => C_LEVEL_EVENT
659 ,p_module => l_log_module);
660 END IF;
661
662 fun_seq_batch.populate_acct_seq_info(p_calling_program => 'REPORTING'
663 ,p_request_id => l_request_id);
664
665 IF (C_LEVEL_EVENT>= g_log_level) THEN
666 trace
667 (p_msg => 'after calling fun_seq_batch.populate_acct_seq_info'
668 ,p_level => C_LEVEL_EVENT
669 ,p_module => l_log_module);
670 END IF;
671
672 update_entries_from_gt;
673
674 IF (l_xla_seq_status = 'SUCCESS') THEN
675 fun_seq_batch.Batch_exit(p_request_id => l_request_id
676 ,x_status => l_xla_seq_status);
677 END IF;
678 IF (l_gl_seq_status = 'SUCCESS') THEN
679 fun_seq_batch.Batch_exit(p_request_id => l_request_id
680 ,x_status => l_gl_seq_status);
681 END IF;
682 END IF;
683 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
684 trace
685 (p_msg => 'end of procedure assign_sequence'
686 ,p_level => C_LEVEL_PROCEDURE
687 ,p_module => l_log_module);
688 trace
689 (p_msg => 'p_retcode = '||p_retcode
690 ,p_level => C_LEVEL_PROCEDURE
691 ,p_module => l_log_module);
692 trace
693 (p_msg => 'p_errbuf = '||p_errbuf
694 ,p_level => C_LEVEL_PROCEDURE
695 ,p_module => l_log_module);
696 END IF;
697 EXCEPTION
698 WHEN xla_exceptions_pkg.application_exception THEN
699 p_retcode := 2;
700 p_errbuf := xla_messages_pkg.get_message;
701 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
702 trace
703 (p_msg => 'p_retcode = '||p_retcode
704 ,p_level => C_LEVEL_PROCEDURE
705 ,p_module => l_log_module);
706 trace
707 (p_msg => 'p_errbuf = '||p_errbuf
708 ,p_level => C_LEVEL_PROCEDURE
709 ,p_module => l_log_module);
710 trace
711 (p_msg => 'END of procedure assign_sequence'
712 ,p_level => C_LEVEL_PROCEDURE
713 ,p_module => l_log_module);
714 END IF;
715
716 WHEN OTHERS THEN
717 p_retcode := 2;
718 p_errbuf := sqlerrm;
719 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
720 trace
721 (p_msg => 'p_retcode = '||p_retcode
722 ,p_level => C_LEVEL_PROCEDURE
723 ,p_module => l_log_module);
724 trace
725 (p_msg => 'p_errbuf = '||p_errbuf
726 ,p_level => C_LEVEL_PROCEDURE
727 ,p_module => l_log_module);
728 trace
729 (p_msg => 'END of procedure assign_sequence'
730 ,p_level => C_LEVEL_PROCEDURE
731 ,p_module => l_log_module);
732 END IF;
733 END assign_sequence;
734
735 --=============================================================================
736 -- This procedure is called by the concurrent request.
737 -- reporting sequence. The concurrent request is launched when gl period is
738 -- closed or reopened.
739 --=============================================================================
740
741 PROCEDURE reset_sequence(p_ledger_id IN NUMBER
742 , p_period_name IN VARCHAR2
743 , p_errbuf OUT NOCOPY VARCHAR2
744 , p_retcode OUT NOCOPY NUMBER) is
745 l_log_module VARCHAR2(240);
746 l_ledger_id number;
747 l_period_name varchar2(100);
748 l_seq_context_value fun_seq_batch.context_value_tbl_type;
749 l_xla_seq_status varchar2(30);
750 l_gl_seq_status varchar2(30);
751 l_start_date gl_period_statuses.start_date%TYPE;
752 l_end_date gl_period_statuses.end_date%TYPE;
753 l_adjustment_flag gl_period_statuses.adjustment_period_flag%TYPE;
754 l_xla_seq_context_id NUMBER;
755 l_gl_seq_context_id NUMBER;
756 l_sort_date VARCHAR2(30);
757 l_temp NUMBER;
758 l_request_id NUMBER;
759
760 cursor c_open_previous_normal_period is
761 SELECT 1
762 FROM gl_period_statuses
763 WHERE ledger_id = p_ledger_id
764 AND start_date<l_start_date
765 AND closing_status = C_PERIOD_STATUS_O
766 AND adjustment_period_flag = 'N'
767 AND application_id = 101;
768
769 BEGIN
770 IF g_log_enabled THEN
771 l_log_module := C_DEFAULT_MODULE||'.reset_sequence';
772 END IF;
773 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
774 trace
775 (p_msg => 'BEGIN of procedure reset_sequence'
776 ,p_level => C_LEVEL_PROCEDURE
777 ,p_module => l_log_module);
778 END IF;
779
780 -- get the start date and end date of the period
781 SELECT trunc(start_date), trunc(end_date), adjustment_period_flag
782 INTO l_start_date, l_end_date, l_adjustment_flag
783 FROM GL_PERIOD_STATUSES
784 WHERE ledger_id = p_ledger_id
785 AND period_name = p_period_name
786 AND application_id = 101;
787
788 IF(l_adjustment_flag = 'Y') THEN
789 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
790 trace
791 (p_msg => 'end of procedure reset_sequence, adjustment period'
792 ,p_level => C_LEVEL_PROCEDURE
793 ,p_module => l_log_module);
794 END IF;
795 RETURN;
796 END IF;
797
798 open c_open_previous_normal_period;
799 fetch c_open_previous_normal_period into l_temp;
800 IF(c_open_previous_normal_period%NOTFOUND) THEN
801 l_temp := 0;
802 END IF;
803 close c_open_previous_normal_period;
804
805 IF(l_temp = 1) THEN
806 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
807 trace
808 (p_msg => 'end of procedure reset_sequence, previous period open'
809 ,p_level => C_LEVEL_PROCEDURE
810 ,p_module => l_log_module);
811 END IF;
812 RETURN;
813 END IF;
814
815 l_request_id := fnd_global.conc_request_id;
816
817 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
818 trace
819 (p_msg => 'request_id:'|| to_char(l_request_id)
820 ,p_level => C_LEVEL_STATEMENT
821 ,p_module => l_log_module);
822 END IF;
823
824 IF (C_LEVEL_EVENT>= g_log_level) THEN
825 trace
826 (p_msg => 'calling fun_seq_batch.batch_init'
827 ,p_level => C_LEVEL_EVENT
828 ,p_module => l_log_module);
829 END IF;
830 -- call fun_seq_batch.batch_init
831 -- for xla first
832 l_seq_context_value(1) := p_ledger_id;
833 fun_seq_batch.Batch_init(p_application_id => 602
834 ,p_table_name => 'XLA_AE_HEADERS'
835 ,p_event_code => 'PERIOD_CLOSE'
836 ,p_context_type => 'LEDGER_AND_CURRENCY'
837 ,p_context_value_tbl =>l_seq_context_value
838 ,p_request_id => l_request_id
839 ,x_status => l_xla_seq_status
840 ,x_seq_context_id => l_xla_seq_context_id);
841
842 -- for gl
843 fun_seq_batch.Batch_init(p_application_id => 101
844 ,p_table_name => 'GL_JE_HEADERS'
845 ,p_event_code => 'PERIOD_CLOSE'
846 ,p_context_type => 'LEDGER_AND_CURRENCY'
847 ,p_context_value_tbl =>l_seq_context_value
848 ,p_request_id => l_request_id
849 ,x_status => l_gl_seq_status
850 ,x_seq_context_id => l_gl_seq_context_id);
851
852 IF (C_LEVEL_EVENT>= g_log_level) THEN
853 trace
854 (p_msg => 'after calling fun_seq_batch.batch_init'
855 ,p_level => C_LEVEL_EVENT
856 ,p_module => l_log_module);
857 END IF;
858
859 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
860 trace
861 (p_msg => 'xla_seq_status:'||l_xla_seq_status||' id:'||to_char(l_xla_seq_context_id)
862 ,p_level => C_LEVEL_STATEMENT
863 ,p_module => l_log_module);
864 trace
865 (p_msg => 'gl_seq_status:'||l_gl_seq_status||' id:'||to_char(l_gl_seq_context_id)
866 ,p_level => C_LEVEL_STATEMENT
867 ,p_module => l_log_module);
868 END IF;
869
870 -- only if there is sequence set up, start the sequence
871 IF (l_xla_seq_status = 'SUCCESS' or l_gl_seq_status = 'SUCCESS') then
872
873 -- get the sort date of each context
874 -- l_sort_date will have value 'GL_DATE', 'REFERENCE_DATE' or 'COMPLETION_DATE'
875 IF(l_gl_seq_status = 'SUCCESS') THEN
876 SELECT nvl(SORT_OPTION, DATE_TYPE)
877 INTO l_sort_date
878 FROM FUN_SEQ_CONTEXTS
879 WHERE SEQ_CONTEXT_ID = l_gl_seq_context_id;
880 ELSE
881 SELECT nvl(SORT_OPTION, DATE_TYPE)
882 INTO l_sort_date
883 FROM FUN_SEQ_CONTEXTS
884 WHERE SEQ_CONTEXT_ID = l_xla_seq_context_id;
885 END IF;
886
887 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
888 trace
889 (p_msg => 'sortdate:'||to_char(l_sort_date)
890 ,p_level => C_LEVEL_STATEMENT
891 ,p_module => l_log_module);
892 END IF;
893
894 get_end_date(p_ledger_id => p_ledger_id
895 ,p_end_date => l_end_date);
896
897 reset_reporting_seq_num( p_ledger_id => p_ledger_id
898 , p_start_date => l_start_date
899 , p_end_date => l_end_date
900 , p_sort_date => l_sort_date);
901 IF (l_xla_seq_status = 'SUCCESS') THEN
902 fun_seq_batch.Batch_exit(p_request_id => l_request_id
903 ,x_status => l_xla_seq_status);
904 END IF;
905 IF (l_gl_seq_status = 'SUCCESS') THEN
906 fun_seq_batch.Batch_exit(p_request_id => l_request_id
907 ,x_status => l_gl_seq_status);
908 END IF;
909 END IF;
910
911 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
912 trace
913 (p_msg => 'end of procedure reset_sequence'
914 ,p_level => C_LEVEL_PROCEDURE
915 ,p_module => l_log_module);
916 END IF;
917 EXCEPTION
918 WHEN xla_exceptions_pkg.application_exception THEN
919 p_retcode := 2;
920 p_errbuf := xla_messages_pkg.get_message;
921 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
922 trace
923 (p_msg => 'p_retcode = '||p_retcode
924 ,p_level => C_LEVEL_PROCEDURE
925 ,p_module => l_log_module);
926 trace
927 (p_msg => 'p_errbuf = '||p_errbuf
928 ,p_level => C_LEVEL_PROCEDURE
929 ,p_module => l_log_module);
930 trace
931 (p_msg => 'END of procedure assign_sequence'
932 ,p_level => C_LEVEL_PROCEDURE
933 ,p_module => l_log_module);
934 END IF;
935 WHEN OTHERS THEN
936 p_retcode := 2;
937 p_errbuf := sqlerrm;
938 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
939 trace
940 (p_msg => 'p_retcode = '||p_retcode
941 ,p_level => C_LEVEL_PROCEDURE
942 ,p_module => l_log_module);
943 trace
944 (p_msg => 'p_errbuf = '||p_errbuf
945 ,p_level => C_LEVEL_PROCEDURE
946 ,p_module => l_log_module);
947 trace
948 (p_msg => 'END of procedure assign_sequence'
949 ,p_level => C_LEVEL_PROCEDURE
950 ,p_module => l_log_module);
951 END IF;
952 END reset_sequence;
953
954 PROCEDURE update_entries_from_gt is
955 l_log_module VARCHAR2(240);
956 BEGIN
957 IF g_log_enabled THEN
958 l_log_module := C_DEFAULT_MODULE||'.update_entries_from_gt';
959 END IF;
960 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
961 trace
962 (p_msg => 'BEGIN of procedure update_entries_from_gt'
963 ,p_level => C_LEVEL_PROCEDURE
964 ,p_module => l_log_module);
965 END IF;
966
967 UPDATE gl_je_headers gjh
968 SET (gjh.close_acct_seq_value
969 ,gjh.close_acct_seq_version_id
970 ,gjh.close_acct_seq_assign_id) =
971 (select xgt.sequence_value
972 ,xgt.sequence_version_id
973 ,xgt.sequence_assign_id
974 FROM xla_seq_je_headers_gt xgt
975 WHERE xgt.application_id = 101
976 AND gjh.je_header_id = xgt.ae_header_id)
977 WHERE gjh.je_header_id in (select ae_header_id from xla_seq_je_headers_gt where application_id=101);
978
979 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
980 trace
981 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
982 ,p_level => C_LEVEL_STATEMENT
983 ,p_module => l_log_module);
984 END IF;
985
986 UPDATE xla_ae_headers xah
987 SET (xah.close_acct_seq_value
988 ,xah.close_acct_seq_version_id
989 ,xah.close_acct_seq_assign_id) =
990 (SELECT xgt.sequence_value
991 ,xgt.sequence_version_id
992 ,xgt.sequence_assign_id
993 FROM xla_seq_je_headers_gt xgt
994 WHERE xgt.application_id = 602
995 AND xah.ae_header_id = xgt.ae_header_id)
996 WHERE xah.ae_header_id in (select ae_header_id from xla_seq_je_headers_gt where application_id=602);
997
998 /*
999 UPDATE (SELECT xah.close_acct_seq_value
1000 ,xah.close_acct_seq_version_id
1001 ,xah.close_acct_seq_assign_id
1002 ,xgt.sequence_assign_id
1003 ,xgt.sequence_version_id
1004 ,xgt.sequence_value
1005 FROM xla_ae_headers xah
1006 ,xla_seq_je_headers_gt xgt
1007 WHERE xgt.application_id = 602
1008 AND xah.ae_header_id = xgt.ae_header_id)
1009 SET close_acct_seq_value=sequence_value
1010 ,close_acct_seq_version_id = sequence_version_id
1011 ,close_acct_seq_assign_id = sequence_assign_id;
1012 */
1013
1014 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1015 trace
1016 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1017 ,p_level => C_LEVEL_STATEMENT
1018 ,p_module => l_log_module);
1019 END IF;
1020
1021 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1022 trace
1023 (p_msg => 'end of procedure update_entries_from_gt'
1024 ,p_level => C_LEVEL_PROCEDURE
1025 ,p_module => l_log_module);
1026 END IF;
1027
1028 END update_entries_from_gt;
1029
1030 /*==============================================================
1031 -- assumption1: p_gl_sort_date and p_xla_sort_date must be the same
1032 ==============================================================*/
1033
1034 PROCEDURE reset_reporting_seq_num(p_ledger_id IN NUMBER
1035 , p_start_date IN DATE
1036 , p_end_date IN DATE
1037 , p_sort_date IN VARCHAR2) is
1038
1039 c_reset_cursor t_reset_seq;
1040 l_seq_value NUMBER;
1041 l_seq_ver_id NUMBER;
1042 l_log_module VARCHAR2(240);
1043
1044 BEGIN
1045 IF g_log_enabled THEN
1046 l_log_module := C_DEFAULT_MODULE||'.reset_reporting_seq_num';
1047 END IF;
1048 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1049 trace
1050 (p_msg => 'BEGIN of procedure reset_reporting_seq_num'
1051 ,p_level => C_LEVEL_PROCEDURE
1052 ,p_module => l_log_module);
1053 trace
1054 (p_msg => 'p_ledger_id:'||to_char(p_ledger_id)
1055 || 'p_start:'||to_char(p_start_date)
1056 || 'p_end:'||to_char(p_end_date)
1057 || 'p_sort_date:'||p_sort_date
1058 ,p_level => C_LEVEL_PROCEDURE
1059 ,p_module => l_log_module);
1060 END IF;
1061
1062
1063 IF(p_sort_date = 'GL_DATE') THEN
1064 OPEN c_reset_cursor FOR
1065 SELECT min(close_acct_seq_value), close_acct_seq_version_id
1066 FROM (
1067 SELECT close_acct_seq_value, close_acct_seq_version_id
1068 FROM xla_ae_headers
1069 WHERE ledger_id=p_ledger_id
1070 AND accounting_date >= p_start_date
1071 AND accounting_date < p_end_date
1072 AND close_acct_seq_version_id is not null
1073 AND gl_transfer_status_code = 'Y'
1074 AND accounting_entry_status_code = 'F'
1075 UNION
1076 SELECT close_acct_seq_value, close_acct_seq_version_id
1077 FROM gl_je_headers
1078 WHERE ledger_id=p_ledger_id
1079 AND default_effective_date >= p_start_date
1080 AND default_effective_date < p_end_date
1081 AND status = 'P'
1082 AND close_acct_seq_version_id is not null
1083 )
1084 GROUP BY close_acct_seq_version_id;
1085 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1086 OPEN c_reset_cursor FOR
1087 SELECT min(close_acct_seq_value), close_acct_seq_version_id
1088 FROM (
1089 SELECT close_acct_seq_value, close_acct_seq_version_id
1090 FROM xla_ae_headers
1091 WHERE ledger_id=p_ledger_id
1092 AND nvl(reference_date, accounting_date) >= p_start_date
1093 AND nvl(reference_date, accounting_date) < p_end_date
1094 AND close_acct_seq_version_id is not null
1095 AND gl_transfer_status_code = 'Y'
1096 AND accounting_entry_status_code = 'F'
1097 UNION
1098 SELECT close_acct_seq_value, close_acct_seq_version_id
1099 FROM gl_je_headers gjh, gl_period_statuses gps
1100 WHERE gjh.ledger_id=p_ledger_id
1101 AND nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) >= p_start_date
1102 AND nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) < p_end_date
1103 AND gjh.status = 'P'
1104 AND gjh.close_acct_seq_version_id is not null
1105 AND gps.application_id = 101
1106 AND gps.ledger_id=p_ledger_id
1107 AND gps.period_name = gjh.period_name
1108 )
1109 GROUP BY close_acct_seq_version_id;
1110 ELSE
1111 OPEN c_reset_cursor FOR
1112 SELECT min(close_acct_seq_value), close_acct_seq_version_id
1113 FROM (
1114 SELECT close_acct_seq_value, close_acct_seq_version_id
1115 FROM xla_ae_headers
1116 WHERE ledger_id=p_ledger_id
1117 AND nvl(completed_date,accounting_date)>= p_start_date
1118 AND nvl(completed_date,accounting_date) < p_end_date
1119 AND close_acct_seq_version_id is not null
1120 AND gl_transfer_status_code = 'Y'
1121 AND accounting_entry_status_code = 'F'
1122 UNION
1123 SELECT close_acct_seq_value, close_acct_seq_version_id
1124 FROM gl_je_headers
1125 WHERE ledger_id=p_ledger_id
1126 AND posted_date >= p_start_date
1127 AND posted_date < p_end_date
1128 AND status = 'P'
1129 AND close_acct_seq_version_id is not null
1130 )
1131 GROUP BY close_acct_seq_version_id;
1132 END IF;
1133
1134 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
1135 trace
1136 (p_msg => 'just before loop'
1137 ,p_level => C_LEVEL_STATEMENT
1138 ,p_module => l_log_module);
1139 END IF;
1140 LOOP
1141 FETCH c_reset_cursor into l_seq_value, l_seq_ver_id;
1142 EXIT WHEN c_reset_cursor%NOTFOUND;
1143
1144 fun_seq.reset(p_seq_version_id => l_seq_ver_id
1145 ,p_sequence_number => l_seq_value -1 );
1146 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
1147 trace
1148 (p_msg => 'ver_id:'||to_char(l_seq_ver_id) || ' ver value:'||
1149 to_char(l_seq_value -1 )
1150 ,p_level => C_LEVEL_STATEMENT
1151 ,p_module => l_log_module);
1152 END IF;
1153 END LOOP;
1154 CLOSE c_reset_cursor;
1155
1156 IF(p_sort_date = 'GL_DATE') THEN
1157 UPDATE xla_ae_headers
1158 SET close_acct_seq_value = null
1159 ,close_acct_seq_version_id = null
1160 ,close_acct_seq_assign_id = null
1161 WHERE ledger_id=p_ledger_id
1162 AND accounting_date >= p_start_date
1163 AND accounting_date < p_end_date
1164 AND close_acct_seq_version_id is not null
1165 AND gl_transfer_status_code = 'Y'
1166 AND accounting_entry_status_code = 'F';
1167
1168 UPDATE gl_je_headers
1169 SET close_acct_seq_value = null
1170 ,close_acct_seq_version_id = null
1171 ,close_acct_seq_assign_id = null
1172 WHERE ledger_id=p_ledger_id
1173 AND default_effective_date >= p_start_date
1174 AND default_effective_date < p_end_date
1175 AND status = 'P'
1176 AND close_acct_seq_version_id is not null;
1177
1178 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1179
1180 UPDATE xla_ae_headers
1181 SET close_acct_seq_value = null
1182 ,close_acct_seq_version_id = null
1183 ,close_acct_seq_assign_id = null
1184 WHERE ledger_id=p_ledger_id
1185 AND nvl(reference_date, accounting_date) >= p_start_date
1186 AND nvl(reference_date, accounting_date) < p_end_date
1187 AND close_acct_seq_version_id is not null
1188 AND gl_transfer_status_code = 'Y'
1189 AND accounting_entry_status_code = 'F';
1190
1191 UPDATE gl_je_headers
1192 SET close_acct_seq_value = null
1193 ,close_acct_seq_version_id = null
1194 ,close_acct_seq_assign_id = null
1195 WHERE je_header_id in
1196 (
1197 SELECT gjh.je_header_id
1198 FROM gl_je_headers gjh, gl_period_statuses gps
1199 WHERE gjh.ledger_id=p_ledger_id
1200 AND nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) >= p_start_date
1201 AND nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) < p_end_date
1202 AND gjh.status = 'P'
1203 AND gjh.close_acct_seq_version_id is not null
1204 AND gps.application_id = 101
1205 AND gps.ledger_id=p_ledger_id
1206 AND gps.period_name = gjh.period_name
1207 );
1208 ELSE
1209 UPDATE xla_ae_headers
1210 SET close_acct_seq_value = null
1211 ,close_acct_seq_version_id = null
1212 ,close_acct_seq_assign_id = null
1213 WHERE ledger_id=p_ledger_id
1214 AND nvl(completed_date,accounting_date) >= p_start_date
1215 AND nvl(completed_date,accounting_date) < p_end_date
1216 AND close_acct_seq_version_id is not null
1217 AND gl_transfer_status_code = 'Y'
1218 AND accounting_entry_status_code = 'F';
1219
1220 UPDATE gl_je_headers
1221 SET close_acct_seq_value = null
1222 ,close_acct_seq_version_id = null
1223 ,close_acct_seq_assign_id = null
1224 WHERE ledger_id=p_ledger_id
1225 AND posted_date >= p_start_date
1226 AND posted_date < p_end_date
1227 AND status = 'P'
1228 AND close_acct_seq_version_id is not null;
1229 END IF;
1230
1231 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1232 trace
1233 (p_msg => 'END of procedure reset_reporting_seq_num'
1234 ,p_level => C_LEVEL_PROCEDURE
1235 ,p_module => l_log_module);
1236 END IF;
1237
1238 END reset_reporting_seq_num;
1239
1240
1241 PROCEDURE populate_seq_gt_table(p_ledger_id IN NUMBER
1242 , p_start_date IN DATE
1243 , p_end_date IN DATE
1244 , p_sort_date IN VARCHAR2) is
1245 l_log_module VARCHAR2(240);
1246 l_temp NUMBER:=0;
1247
1248 -- if there is such segment defined, we need check before inserting into the table
1249 -- otherwise, all the segments are balancing segments.
1250
1251 cursor c_analytical_segment_defined(l_ledger_id NUMBER) is
1252 SELECT 1
1253 FROM gl_ledger_segment_values glsv
1254 ,gl_ledger_norm_seg_vals glnsv
1255 WHERE
1256 glsv.ledger_id = l_ledger_id
1257 AND glsv.segment_type_code = 'B'
1258 AND glsv.parent_record_id = glnsv.record_id
1259 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'N'
1260 AND glsv.Status_code is NULL
1261 AND glnsv.Status_code is NULL;
1262
1263 BEGIN
1264 IF g_log_enabled THEN
1265 l_log_module := C_DEFAULT_MODULE||'.populate_seq_gt_table';
1266 END IF;
1267 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1268 trace
1269 (p_msg => 'BEGIN of procedure populate_seq_gt_table'
1270 ,p_level => C_LEVEL_PROCEDURE
1271 ,p_module => l_log_module);
1272 END IF;
1273
1274 open c_analytical_segment_defined(p_ledger_id);
1275 fetch c_analytical_segment_defined into l_temp;
1276 IF(c_analytical_segment_defined%NOTFOUND) THEN
1277 l_temp := 0;
1278 END IF;
1279 close c_analytical_segment_defined;
1280
1281 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1282 trace
1283 (p_msg => 'analytical segment defined:'||to_char(l_temp)
1284 ,p_level => C_LEVEL_STATEMENT
1285 ,p_module => l_log_module);
1286 END IF;
1287
1288 IF (l_temp = 1) THEN
1289 IF (p_sort_date = 'GL_DATE') THEN
1290 -- populate the table XLA_SEQ_JE_HEADERS_GT
1291 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1292 (application_id
1293 ,ledger_id
1294 ,ae_header_id
1295 ,je_source_name
1296 ,je_category_name
1297 ,gl_date
1298 ,reference_date
1299 ,completion_posted_date)
1300 (SELECT 101
1301 ,p_ledger_id
1302 ,gjh.je_header_id
1303 ,gjh.je_source
1304 ,gjh.je_category
1305 ,gjh.default_effective_date
1306 ,nvl(gjh.reference_date,
1307 decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1308 ,gjh.posted_date
1309 FROM gl_je_headers gjh
1310 ,gl_period_statuses gps
1311 ,xla_subledgers xs
1312 WHERE gjh.ledger_id = p_ledger_id
1313 AND gjh.default_effective_date >= p_start_date
1314 AND gjh.default_effective_date < p_end_date
1315 AND gjh.status = 'P'
1316 AND gjh.actual_flag = 'A'
1317 AND ( gjh.parent_je_header_id is not null
1318 OR xs.je_source_name is null
1319 -- 6722378
1320 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1321 )
1322 AND gjh.je_source = xs.je_source_name (+)
1323 AND gps.application_id = 101
1324 AND gjh.period_name = gps.period_name
1325 AND gps.ledger_id = p_ledger_id
1326 AND exists
1327 (SELECT 1
1328 FROM gl_ledger_segment_values glsv
1329 ,gl_ledger_norm_seg_vals glnsv
1330 ,gl_je_segment_values gljsv
1331 WHERE gljsv.je_header_id = gjh.je_header_id
1332 AND glsv.ledger_id (+) = p_ledger_id
1333 AND gljsv.segment_type_code = 'B'
1334 AND gljsv.segment_type_code = glsv.segment_type_code (+)
1335 AND gljsv.segment_value = glsv.segment_value (+)
1336 AND glsv.parent_record_id = glnsv.record_id (+)
1337 AND glsv.status_code is NULL
1338 AND glnsv.status_code is NULL
1339 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1340 );
1341 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1342 trace
1343 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1344 ,p_level => C_LEVEL_STATEMENT
1345 ,p_module => l_log_module);
1346 END IF;
1347
1348 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1349 -- populate the table XLA_SEQ_JE_HEADERS_GT
1350 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1351 (application_id
1352 ,ledger_id
1353 ,ae_header_id
1354 ,je_source_name
1355 ,je_category_name
1356 ,gl_date
1357 ,reference_date
1358 ,completion_posted_date)
1359 (SELECT 101
1360 ,p_ledger_id
1361 ,gjh.je_header_id
1362 ,gjh.je_source
1363 ,gjh.je_category
1364 ,gjh.default_effective_date
1365 ,nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1366 ,gjh.posted_date
1367 FROM gl_je_headers gjh
1368 ,gl_period_statuses gps
1369 ,xla_subledgers xs
1370 WHERE gjh.ledger_id = p_ledger_id
1371 AND nvl(gjh.reference_date
1372 ,decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) >= p_start_date
1373 AND nvl(gjh.reference_date
1374 ,decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) < p_end_date
1375 AND gjh.status = 'P'
1376 AND gjh.actual_flag = 'A'
1377 AND nvl(gjh.je_from_sla_flag,'N') = 'N'
1378 AND ( gjh.parent_je_header_id is not null
1379 OR xs.je_source_name is null
1380 -- 6722378 upgraded journal entries
1381 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1382 )
1383 AND gjh.je_source = xs.je_source_name (+)
1384 AND gps.application_id = 101
1385 AND gjh.period_name = gps.period_name
1386 AND gps.ledger_id = p_ledger_id
1387 AND exists
1388 (SELECT 1
1389 FROM gl_ledger_segment_values glsv
1390 ,gl_ledger_norm_seg_vals glnsv
1391 ,gl_je_segment_values gljsv
1392 WHERE gljsv.je_header_id = gjh.je_header_id
1393 AND glsv.ledger_id (+) = p_ledger_id
1394 AND gljsv.segment_type_code = 'B'
1395 AND gljsv.segment_type_code = glsv.segment_type_code (+)
1396 AND gljsv.segment_value = glsv.segment_value (+)
1397 AND glsv.parent_record_id = glnsv.record_id (+)
1398 AND glsv.status_code is NULL
1399 AND glnsv.status_code is NULL
1400 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1401 );
1402 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1403 trace
1404 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1405 ,p_level => C_LEVEL_STATEMENT
1406 ,p_module => l_log_module);
1407 END IF;
1408 ELSE
1409 -- populate the table XLA_SEQ_JE_HEADERS_GT
1410 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1411 (application_id
1412 ,ledger_id
1413 ,ae_header_id
1414 ,je_source_name
1415 ,je_category_name
1416 ,gl_date
1417 ,reference_date
1418 ,completion_posted_date)
1419 (SELECT 101
1420 ,p_ledger_id
1421 ,gjh.je_header_id
1422 ,gjh.je_source
1423 ,gjh.je_category
1424 ,gjh.default_effective_date
1425 ,nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1426 ,gjh.posted_date
1427 FROM gl_je_headers gjh
1428 ,gl_period_statuses gps
1429 ,xla_subledgers xs
1430 WHERE gjh.ledger_id = p_ledger_id
1431 AND gjh.posted_date>= p_start_date
1432 AND gjh.posted_date< p_end_date
1433 AND gjh.status = 'P'
1434 AND gjh.actual_flag = 'A'
1435 AND ( gjh.parent_je_header_id is not null
1436 OR xs.je_source_name is null
1437 -- 6722378 upgraded journal entries
1438 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1439 )
1440 AND gjh.je_source=xs.je_source_name (+)
1441 AND gps.application_id = 101
1442 AND gjh.period_name = gps.period_name
1443 AND gps.ledger_id = p_ledger_id
1444 AND exists
1445 (SELECT 1
1446 FROM gl_ledger_segment_values glsv
1447 ,gl_ledger_norm_seg_vals glnsv
1448 ,gl_je_segment_values gljsv
1449 WHERE gljsv.je_header_id = gjh.je_header_id
1450 AND glsv.ledger_id (+) = p_ledger_id
1451 AND gljsv.segment_type_code = 'B'
1452 AND gljsv.segment_type_code = glsv.segment_type_code (+)
1453 AND gljsv.segment_value = glsv.segment_value (+)
1454 AND glsv.parent_record_id = glnsv.record_id (+)
1455 AND glsv.status_code is NULL
1456 AND glnsv.status_code is NULL
1457 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1458 );
1459 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1460 trace
1461 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1462 ,p_level => C_LEVEL_STATEMENT
1463 ,p_module => l_log_module);
1464 END IF;
1465 END IF;
1466
1467
1468 IF (p_sort_date = 'GL_DATE') THEN
1469 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1470 (application_id
1471 ,ledger_id
1472 ,ae_header_id
1473 ,je_source_name
1474 ,je_category_name
1475 ,gl_date
1476 ,reference_date
1477 ,completion_posted_date)
1478 (SELECT 602
1479 ,p_ledger_id
1480 ,xah.ae_header_id
1481 ,xs.je_source_name
1482 ,xah.je_category_name
1483 ,xah.accounting_date
1484 ,nvl(xah.reference_date, xah.accounting_date)
1485 ,nvl(xah.completed_date, xah.accounting_date)
1486 FROM xla_ae_headers xah
1487 ,xla_subledgers xs
1488 WHERE xah.ledger_id = p_ledger_id
1489 AND xah.accounting_date >= p_start_date
1490 AND xah.accounting_date < p_end_date
1491 AND xah.accounting_entry_status_code = 'F'
1492 AND xah.gl_transfer_status_code = 'Y'
1493 AND xah.application_id = xs.application_id
1494 AND xah.balance_type_code = 'A'
1495 AND exists
1496 (SELECT 1
1497 FROM gl_ledger_segment_values glsv
1498 ,gl_ledger_norm_seg_vals glnsv
1499 ,xla_ae_segment_values xasv
1500 WHERE xah.ae_header_id = xasv.ae_header_id
1501 AND glsv.ledger_id (+) = p_ledger_id
1502 AND xasv.segment_type_code = 'B'
1503 AND xasv.segment_type_code = glsv.segment_type_code (+)
1504 AND xasv.segment_value = glsv.segment_value (+)
1505 AND glsv.parent_record_id = glnsv.record_id (+)
1506 AND glsv.status_code is NULL
1507 AND glnsv.status_code is NULL
1508 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1509 );
1510 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1511 trace
1512 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1513 ,p_level => C_LEVEL_STATEMENT
1514 ,p_module => l_log_module);
1515 END IF;
1516 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1517 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1518 (application_id
1519 ,ledger_id
1520 ,ae_header_id
1521 ,je_source_name
1522 ,je_category_name
1523 ,gl_date
1524 ,reference_date
1525 ,completion_posted_date)
1526 (SELECT 602
1527 ,p_ledger_id
1528 ,xah.ae_header_id
1529 ,xs.je_source_name
1530 ,xah.je_category_name
1531 ,xah.accounting_date
1532 ,nvl(xah.reference_date, xah.accounting_date)
1533 ,nvl(xah.completed_date, xah.accounting_date)
1534 FROM xla_ae_headers xah
1535 ,xla_subledgers xs
1536 WHERE xah.ledger_id = p_ledger_id
1537 AND nvl(xah.reference_date, xah.accounting_date) >= p_start_date
1538 AND nvl(xah.reference_date, xah.accounting_date) < p_end_date
1539 AND xah.accounting_entry_status_code = 'F'
1540 AND xah.gl_transfer_status_code = 'Y'
1541 AND xah.application_id = xs.application_id
1542 AND xah.balance_type_code = 'A'
1543 AND exists
1544 (SELECT 1
1545 FROM gl_ledger_segment_values glsv
1546 ,gl_ledger_norm_seg_vals glnsv
1547 ,xla_ae_segment_values xasv
1548 WHERE xah.ae_header_id = xasv.ae_header_id
1549 AND glsv.ledger_id (+) = p_ledger_id
1550 AND xasv.segment_type_code = 'B'
1551 AND xasv.segment_type_code = glsv.segment_type_code (+)
1552 AND xasv.segment_value = glsv.segment_value (+)
1553 AND glsv.parent_record_id = glnsv.record_id(+)
1554 AND glsv.status_code is NULL
1555 AND glnsv.status_code is NULL
1556 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1557 );
1558 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1559 trace
1560 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1561 ,p_level => C_LEVEL_STATEMENT
1562 ,p_module => l_log_module);
1563 END IF;
1564 ELSE
1565 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1566 (application_id
1567 ,ledger_id
1568 ,ae_header_id
1569 ,je_source_name
1570 ,je_category_name
1571 ,gl_date
1572 ,reference_date
1573 ,completion_posted_date)
1574 (SELECT 602
1575 ,p_ledger_id
1576 ,xah.ae_header_id
1577 ,xs.je_source_name
1578 ,xah.je_category_name
1579 ,xah.accounting_date
1580 ,nvl(xah.reference_date, xah.accounting_date)
1581 ,nvl(xah.completed_date, xah.accounting_date)
1582
1583 FROM xla_ae_headers xah
1584 ,xla_subledgers xs
1585 WHERE xah.ledger_id = p_ledger_id
1586 AND nvl(xah.completed_date, xah.accounting_date) >= p_start_date
1587 AND nvl(xah.completed_date, xah.accounting_date) < p_end_date
1588 AND xah.accounting_entry_status_code = 'F'
1589 AND xah.gl_transfer_status_code = 'Y'
1590 AND xah.application_id = xs.application_id
1591 AND xah.balance_type_code = 'A'
1592 AND exists
1593 (SELECT 1
1594 FROM gl_ledger_segment_values glsv
1595 ,gl_ledger_norm_seg_vals glnsv
1596 ,xla_ae_segment_values xasv
1597 WHERE xah.ae_header_id = xasv.ae_header_id
1598 AND glsv.ledger_id (+) = p_ledger_id
1599 AND xasv.segment_type_code = 'B'
1600 AND xasv.segment_type_code = glsv.segment_type_code (+)
1601 AND xasv.segment_value = glsv.segment_value (+)
1602 AND glsv.parent_record_id = glnsv.record_id(+)
1603 AND glsv.status_code is NULL
1604 AND glnsv.status_code is NULL
1605 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1606 );
1607 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1608 trace
1609 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1610 ,p_level => C_LEVEL_STATEMENT
1611 ,p_module => l_log_module);
1612 END IF;
1613
1614 END IF;
1615 ELSE
1616 IF (p_sort_date = 'GL_DATE') THEN
1617 -- populate the table XLA_SEQ_JE_HEADERS_GT
1618 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1619 (application_id
1620 ,ledger_id
1621 ,ae_header_id
1622 ,je_source_name
1623 ,je_category_name
1624 ,gl_date
1625 ,reference_date
1626 ,completion_posted_date)
1627 (SELECT 101
1628 ,p_ledger_id
1629 ,gjh.je_header_id
1630 ,gjh.je_source
1631 ,gjh.je_category
1632 ,gjh.default_effective_date
1633 ,nvl(gjh.reference_date,
1634 decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1635 ,gjh.posted_date
1636 FROM gl_je_headers gjh
1637 ,gl_period_statuses gps
1638 ,xla_subledgers xs
1639 WHERE gjh.ledger_id = p_ledger_id
1640 AND gjh.default_effective_date >= p_start_date
1641 AND gjh.default_effective_date < p_end_date
1642 AND gjh.status = 'P'
1643 AND gjh.actual_flag = 'A'
1644 AND ( gjh.parent_je_header_id is not null
1645 OR xs.je_source_name is null
1646 -- 6722378 upgraded journal entries
1647 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1648 )
1649 AND gjh.je_source = xs.je_source_name (+)
1650 AND gps.application_id = 101
1651 AND gjh.period_name = gps.period_name
1652 AND gps.ledger_id = p_ledger_id
1653 );
1654 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1655 trace
1656 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1657 ,p_level => C_LEVEL_STATEMENT
1658 ,p_module => l_log_module);
1659 END IF;
1660
1661 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1662 -- populate the table XLA_SEQ_JE_HEADERS_GT
1663 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1664 (application_id
1665 ,ledger_id
1666 ,ae_header_id
1667 ,je_source_name
1668 ,je_category_name
1669 ,gl_date
1670 ,reference_date
1671 ,completion_posted_date)
1672 (SELECT 101
1673 ,p_ledger_id
1674 ,gjh.je_header_id
1675 ,gjh.je_source
1676 ,gjh.je_category
1677 ,gjh.default_effective_date
1678 ,nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1679 ,gjh.posted_date
1680 FROM gl_je_headers gjh
1681 ,gl_period_statuses gps
1682 ,xla_subledgers xs
1683 WHERE gjh.ledger_id = p_ledger_id
1684 AND nvl(gjh.reference_date
1685 ,decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) >= p_start_date
1686 AND nvl(gjh.reference_date
1687 ,decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) < p_end_date
1688 AND gjh.status = 'P'
1689 AND gjh.actual_flag = 'A'
1690 AND nvl(gjh.je_from_sla_flag,'N') = 'N'
1691 AND ( gjh.parent_je_header_id is not null
1692 OR xs.je_source_name is null
1693 -- 6722378 upgraded journal entries
1694 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1695 )
1696 AND gjh.je_source = xs.je_source_name (+)
1697 AND gps.application_id = 101
1698 AND gjh.period_name = gps.period_name
1699 AND gps.ledger_id = p_ledger_id
1700 );
1701 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1702 trace
1703 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1704 ,p_level => C_LEVEL_STATEMENT
1705 ,p_module => l_log_module);
1706 END IF;
1707 ELSE
1708 -- populate the table XLA_SEQ_JE_HEADERS_GT
1709 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1710 (application_id
1711 ,ledger_id
1712 ,ae_header_id
1713 ,je_source_name
1714 ,je_category_name
1715 ,gl_date
1716 ,reference_date
1717 ,completion_posted_date)
1718 (SELECT 101
1719 ,p_ledger_id
1720 ,gjh.je_header_id
1721 ,gjh.je_source
1722 ,gjh.je_category
1723 ,gjh.default_effective_date
1724 ,nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1725 ,gjh.posted_date
1726 FROM gl_je_headers gjh
1727 ,gl_period_statuses gps
1728 ,xla_subledgers xs
1729 WHERE gjh.ledger_id = p_ledger_id
1730 AND gjh.posted_date>= p_start_date
1731 AND gjh.posted_date< p_end_date
1732 AND gjh.status = 'P'
1733 AND gjh.actual_flag = 'A'
1734 AND ( gjh.parent_je_header_id is not null
1735 OR xs.je_source_name is null
1736 -- 6722378 upgraded journal entries
1737 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1738 )
1739 AND gjh.je_source=xs.je_source_name (+)
1740 AND gps.application_id = 101
1741 AND gjh.period_name = gps.period_name
1742 AND gps.ledger_id = p_ledger_id
1743 );
1744 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1745 trace
1746 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1747 ,p_level => C_LEVEL_STATEMENT
1748 ,p_module => l_log_module);
1749 END IF;
1750 END IF;
1751
1752
1753 IF (p_sort_date = 'GL_DATE') THEN
1754 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1755 (application_id
1756 ,ledger_id
1757 ,ae_header_id
1758 ,je_source_name
1759 ,je_category_name
1760 ,gl_date
1761 ,reference_date
1762 ,completion_posted_date)
1763 (SELECT 602
1764 ,p_ledger_id
1765 ,xah.ae_header_id
1766 ,xs.je_source_name
1767 ,xah.je_category_name
1768 ,xah.accounting_date
1769 ,nvl(xah.reference_date, xah.accounting_date)
1770 ,nvl(xah.completed_date, xah.accounting_date)
1771
1772 FROM xla_ae_headers xah
1773 ,xla_subledgers xs
1774 WHERE xah.ledger_id = p_ledger_id
1775 AND xah.accounting_date >= p_start_date
1776 AND xah.accounting_date < p_end_date
1777 AND xah.accounting_entry_status_code = 'F'
1778 AND xah.gl_transfer_status_code = 'Y'
1779 AND xah.application_id = xs.application_id
1780 AND xah.balance_type_code = 'A'
1781 );
1782 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1783 trace
1784 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1785 ,p_level => C_LEVEL_STATEMENT
1786 ,p_module => l_log_module);
1787 END IF;
1788 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1789 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1790 (application_id
1791 ,ledger_id
1792 ,ae_header_id
1793 ,je_source_name
1794 ,je_category_name
1795 ,gl_date
1796 ,reference_date
1797 ,completion_posted_date)
1798 (SELECT 602
1799 ,p_ledger_id
1800 ,xah.ae_header_id
1801 ,xs.je_source_name
1802 ,xah.je_category_name
1803 ,xah.accounting_date
1804 ,nvl(xah.reference_date, xah.accounting_date)
1805 ,nvl(xah.completed_date, xah.accounting_date)
1806
1807 FROM xla_ae_headers xah
1808 ,xla_subledgers xs
1809 WHERE xah.ledger_id = p_ledger_id
1810 AND nvl(xah.reference_date, xah.accounting_date) >= p_start_date
1811 AND nvl(xah.reference_date, xah.accounting_date) < p_end_date
1812 AND xah.accounting_entry_status_code = 'F'
1813 AND xah.gl_transfer_status_code = 'Y'
1814 AND xah.application_id = xs.application_id
1815 AND xah.balance_type_code = 'A'
1816 );
1817 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1818 trace
1819 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1820 ,p_level => C_LEVEL_STATEMENT
1821 ,p_module => l_log_module);
1822 END IF;
1823 ELSE
1824 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1825 (application_id
1826 ,ledger_id
1827 ,ae_header_id
1828 ,je_source_name
1829 ,je_category_name
1830 ,gl_date
1831 ,reference_date
1832 ,completion_posted_date)
1833 (SELECT 602
1834 ,p_ledger_id
1835 ,xah.ae_header_id
1836 ,xs.je_source_name
1837 ,xah.je_category_name
1838 ,xah.accounting_date
1839 ,nvl(xah.reference_date, xah.accounting_date)
1840 ,nvl(xah.completed_date, xah.accounting_date)
1841
1842 FROM xla_ae_headers xah
1843 ,xla_subledgers xs
1844 WHERE xah.ledger_id = p_ledger_id
1845 AND nvl(xah.completed_date, xah.accounting_date)>= p_start_date
1846 AND nvl(xah.completed_date, xah.accounting_date) < p_end_date
1847 AND xah.accounting_entry_status_code = 'F'
1848 AND xah.gl_transfer_status_code = 'Y'
1849 AND xah.application_id = xs.application_id
1850 AND xah.balance_type_code = 'A'
1851 );
1852 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1853 trace
1854 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1855 ,p_level => C_LEVEL_STATEMENT
1856 ,p_module => l_log_module);
1857 END IF;
1858
1859 END IF;
1860 END IF;
1861
1862 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1863 trace
1864 (p_msg => 'end of procedure populate_seq_gt_table'
1865 ,p_level => C_LEVEL_PROCEDURE
1866 ,p_module => l_log_module);
1867 END IF;
1868
1869 exception
1870 when others then
1871 raise;
1872 end;
1873
1874
1875 --=============================================================================
1876 -- *********** Initialization routine **********
1877 --=============================================================================
1878
1879 --=============================================================================
1880 --
1881 --
1882 --
1883 --
1884 --
1885 --
1886 --
1887 --
1888 --
1889 --
1890 -- Following code is executed when the package body is referenced for the first
1891 -- time
1892 --
1893 --
1894 --
1895 --
1896 --
1897 --
1898 --
1899 --
1900 --
1901 --
1902 --
1903 --
1904 --=============================================================================
1905
1906 BEGIN
1907 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1908 g_log_enabled := fnd_log.test
1909 (log_level => g_log_level
1910 ,module => C_DEFAULT_MODULE);
1911
1912 IF NOT g_log_enabled THEN
1913 g_log_level := C_LEVEL_LOG_DISABLED;
1914 END IF;
1915
1916 END XLA_REPORTING_SEQUENCE_PKG;