[Home] [Help]
PACKAGE BODY: APPS.XLA_REPORTING_SEQUENCE_PKG
Source
1 PACKAGE BODY XLA_REPORTING_SEQUENCE_PKG AS
2 -- $Header: xlarepseq.pkb 120.7 2008/01/10 05:08:18 masada 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
385 END IF;
382 (p_msg => 'BEGIN of function already_assigned'
383 ,p_level => C_LEVEL_PROCEDURE
384 ,p_module => l_log_module);
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;
535 ,p_context_type => 'LEDGER_AND_CURRENCY'
532 fun_seq_batch.Batch_init(p_application_id => 602
533 ,p_table_name => 'XLA_AE_HEADERS'
534 ,p_event_code => 'PERIOD_CLOSE'
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 RETURN;
609 END IF;
610 END IF;
611
612 -- get the end date of the date range
613 get_end_date(p_ledger_id => p_ledger_id
614 ,p_end_date => l_end_date);
615
616 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
617 trace
618 (p_msg => 'end date:'||to_char(l_end_date)
619 ,p_level => C_LEVEL_STATEMENT
620 ,p_module => l_log_module);
621 END IF;
622
623 IF(l_adjustment_flag = 'Y') THEN
624 -- get the normal entry start date
625 -- reset the sequence number.
626 SELECT trunc(max(start_date))
627 INTO l_start_date
628 FROM gl_period_statuses
629 WHERE ledger_id = p_ledger_id
630 AND start_date <= l_start_date
631 AND adjustment_period_flag = 'N'
632 AND application_id = 101;
633
634 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
635 trace
636 (p_msg => 'adjustment period, normal start date:'||to_char(l_start_date)
637 ,p_level => C_LEVEL_STATEMENT
638 ,p_module => l_log_module);
639 END IF;
640 -- is it possible that gl has set up but not xla?
641 reset_reporting_seq_num( p_ledger_id => p_ledger_id
642 , p_start_date => l_start_date
643 , p_end_date => l_end_date
644 , p_sort_date => l_sort_date);
645
646 END IF;
647
648 populate_seq_gt_table(p_ledger_id => p_ledger_id
649 ,p_start_date => l_start_date
650 ,p_end_date => l_end_date
651 ,p_sort_date => l_sort_date);
652
653 IF (C_LEVEL_EVENT>= g_log_level) THEN
654 trace
655 (p_msg => 'before calling fun_seq_batch.populate_acct_seq_info'
659
656 ,p_level => C_LEVEL_EVENT
657 ,p_module => l_log_module);
658 END IF;
660 fun_seq_batch.populate_acct_seq_info(p_calling_program => 'REPORTING'
661 ,p_request_id => l_request_id);
662
663 IF (C_LEVEL_EVENT>= g_log_level) THEN
664 trace
665 (p_msg => 'after calling fun_seq_batch.populate_acct_seq_info'
666 ,p_level => C_LEVEL_EVENT
667 ,p_module => l_log_module);
668 END IF;
669
670 update_entries_from_gt;
671
672 IF (l_xla_seq_status = 'SUCCESS') THEN
673 fun_seq_batch.Batch_exit(p_request_id => l_request_id
674 ,x_status => l_xla_seq_status);
675 END IF;
676 IF (l_gl_seq_status = 'SUCCESS') THEN
677 fun_seq_batch.Batch_exit(p_request_id => l_request_id
678 ,x_status => l_gl_seq_status);
679 END IF;
680 END IF;
681 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
682 trace
683 (p_msg => 'end of procedure assign_sequence'
684 ,p_level => C_LEVEL_PROCEDURE
685 ,p_module => l_log_module);
686 trace
687 (p_msg => 'p_retcode = '||p_retcode
688 ,p_level => C_LEVEL_PROCEDURE
689 ,p_module => l_log_module);
690 trace
691 (p_msg => 'p_errbuf = '||p_errbuf
692 ,p_level => C_LEVEL_PROCEDURE
693 ,p_module => l_log_module);
694 END IF;
695 EXCEPTION
696 WHEN xla_exceptions_pkg.application_exception THEN
697 p_retcode := 2;
698 p_errbuf := xla_messages_pkg.get_message;
699 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
700 trace
701 (p_msg => 'p_retcode = '||p_retcode
702 ,p_level => C_LEVEL_PROCEDURE
703 ,p_module => l_log_module);
704 trace
705 (p_msg => 'p_errbuf = '||p_errbuf
706 ,p_level => C_LEVEL_PROCEDURE
707 ,p_module => l_log_module);
708 trace
709 (p_msg => 'END of procedure assign_sequence'
710 ,p_level => C_LEVEL_PROCEDURE
711 ,p_module => l_log_module);
712 END IF;
713
714 WHEN OTHERS THEN
715 p_retcode := 2;
716 p_errbuf := sqlerrm;
717 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
718 trace
719 (p_msg => 'p_retcode = '||p_retcode
720 ,p_level => C_LEVEL_PROCEDURE
721 ,p_module => l_log_module);
722 trace
723 (p_msg => 'p_errbuf = '||p_errbuf
724 ,p_level => C_LEVEL_PROCEDURE
725 ,p_module => l_log_module);
726 trace
727 (p_msg => 'END of procedure assign_sequence'
728 ,p_level => C_LEVEL_PROCEDURE
729 ,p_module => l_log_module);
730 END IF;
731 END assign_sequence;
732
733 --=============================================================================
734 -- This procedure is called by the concurrent request.
735 -- reporting sequence. The concurrent request is launched when gl period is
736 -- closed or reopened.
737 --=============================================================================
738
739 PROCEDURE reset_sequence(p_ledger_id IN NUMBER
740 , p_period_name IN VARCHAR2
741 , p_errbuf OUT NOCOPY VARCHAR2
742 , p_retcode OUT NOCOPY NUMBER) is
743 l_log_module VARCHAR2(240);
744 l_ledger_id number;
745 l_period_name varchar2(100);
746 l_seq_context_value fun_seq_batch.context_value_tbl_type;
747 l_xla_seq_status varchar2(30);
748 l_gl_seq_status varchar2(30);
749 l_start_date gl_period_statuses.start_date%TYPE;
750 l_end_date gl_period_statuses.end_date%TYPE;
751 l_adjustment_flag gl_period_statuses.adjustment_period_flag%TYPE;
752 l_xla_seq_context_id NUMBER;
753 l_gl_seq_context_id NUMBER;
754 l_sort_date VARCHAR2(30);
755 l_temp NUMBER;
756 l_request_id NUMBER;
757
758 cursor c_open_previous_normal_period is
759 SELECT 1
760 FROM gl_period_statuses
761 WHERE ledger_id = p_ledger_id
762 AND start_date<l_start_date
763 AND closing_status = C_PERIOD_STATUS_O
764 AND adjustment_period_flag = 'N'
765 AND application_id = 101;
766
767 BEGIN
768 IF g_log_enabled THEN
769 l_log_module := C_DEFAULT_MODULE||'.reset_sequence';
770 END IF;
771 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
772 trace
773 (p_msg => 'BEGIN of procedure reset_sequence'
774 ,p_level => C_LEVEL_PROCEDURE
775 ,p_module => l_log_module);
776 END IF;
777
778 -- get the start date and end date of the period
779 SELECT trunc(start_date), trunc(end_date), adjustment_period_flag
780 INTO l_start_date, l_end_date, l_adjustment_flag
781 FROM GL_PERIOD_STATUSES
782 WHERE ledger_id = p_ledger_id
783 AND period_name = p_period_name
784 AND application_id = 101;
785
786 IF(l_adjustment_flag = 'Y') THEN
787 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
788 trace
789 (p_msg => 'end of procedure reset_sequence, adjustment period'
790 ,p_level => C_LEVEL_PROCEDURE
794 END IF;
791 ,p_module => l_log_module);
792 END IF;
793 RETURN;
795
796 open c_open_previous_normal_period;
797 fetch c_open_previous_normal_period into l_temp;
798 IF(c_open_previous_normal_period%NOTFOUND) THEN
799 l_temp := 0;
800 END IF;
801 close c_open_previous_normal_period;
802
803 IF(l_temp = 1) THEN
804 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
805 trace
806 (p_msg => 'end of procedure reset_sequence, previous period open'
807 ,p_level => C_LEVEL_PROCEDURE
808 ,p_module => l_log_module);
809 END IF;
810 RETURN;
811 END IF;
812
813 l_request_id := fnd_global.conc_request_id;
814
815 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
816 trace
817 (p_msg => 'request_id:'|| to_char(l_request_id)
818 ,p_level => C_LEVEL_STATEMENT
819 ,p_module => l_log_module);
820 END IF;
821
822 IF (C_LEVEL_EVENT>= g_log_level) THEN
823 trace
824 (p_msg => 'calling fun_seq_batch.batch_init'
825 ,p_level => C_LEVEL_EVENT
826 ,p_module => l_log_module);
827 END IF;
828 -- call fun_seq_batch.batch_init
829 -- for xla first
830 l_seq_context_value(1) := p_ledger_id;
831 fun_seq_batch.Batch_init(p_application_id => 602
832 ,p_table_name => 'XLA_AE_HEADERS'
833 ,p_event_code => 'PERIOD_CLOSE'
834 ,p_context_type => 'LEDGER_AND_CURRENCY'
835 ,p_context_value_tbl =>l_seq_context_value
836 ,p_request_id => l_request_id
837 ,x_status => l_xla_seq_status
838 ,x_seq_context_id => l_xla_seq_context_id);
839
840 -- for gl
841 fun_seq_batch.Batch_init(p_application_id => 101
842 ,p_table_name => 'GL_JE_HEADERS'
843 ,p_event_code => 'PERIOD_CLOSE'
844 ,p_context_type => 'LEDGER_AND_CURRENCY'
845 ,p_context_value_tbl =>l_seq_context_value
846 ,p_request_id => l_request_id
847 ,x_status => l_gl_seq_status
848 ,x_seq_context_id => l_gl_seq_context_id);
849
850 IF (C_LEVEL_EVENT>= g_log_level) THEN
851 trace
852 (p_msg => 'after calling fun_seq_batch.batch_init'
853 ,p_level => C_LEVEL_EVENT
854 ,p_module => l_log_module);
855 END IF;
856
857 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
858 trace
859 (p_msg => 'xla_seq_status:'||l_xla_seq_status||' id:'||to_char(l_xla_seq_context_id)
860 ,p_level => C_LEVEL_STATEMENT
861 ,p_module => l_log_module);
862 trace
863 (p_msg => 'gl_seq_status:'||l_gl_seq_status||' id:'||to_char(l_gl_seq_context_id)
864 ,p_level => C_LEVEL_STATEMENT
865 ,p_module => l_log_module);
866 END IF;
867
868 -- only if there is sequence set up, start the sequence
869 IF (l_xla_seq_status = 'SUCCESS' or l_gl_seq_status = 'SUCCESS') then
870
871 -- get the sort date of each context
872 -- l_sort_date will have value 'GL_DATE', 'REFERENCE_DATE' or 'COMPLETION_DATE'
873 IF(l_gl_seq_status = 'SUCCESS') THEN
874 SELECT nvl(SORT_OPTION, DATE_TYPE)
875 INTO l_sort_date
876 FROM FUN_SEQ_CONTEXTS
877 WHERE SEQ_CONTEXT_ID = l_gl_seq_context_id;
878 ELSE
879 SELECT nvl(SORT_OPTION, DATE_TYPE)
880 INTO l_sort_date
881 FROM FUN_SEQ_CONTEXTS
882 WHERE SEQ_CONTEXT_ID = l_xla_seq_context_id;
883 END IF;
884
885 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
886 trace
887 (p_msg => 'sortdate:'||to_char(l_sort_date)
888 ,p_level => C_LEVEL_STATEMENT
889 ,p_module => l_log_module);
890 END IF;
891
892 get_end_date(p_ledger_id => p_ledger_id
893 ,p_end_date => l_end_date);
894
895 reset_reporting_seq_num( p_ledger_id => p_ledger_id
896 , p_start_date => l_start_date
897 , p_end_date => l_end_date
898 , p_sort_date => l_sort_date);
899 IF (l_xla_seq_status = 'SUCCESS') THEN
900 fun_seq_batch.Batch_exit(p_request_id => l_request_id
901 ,x_status => l_xla_seq_status);
902 END IF;
903 IF (l_gl_seq_status = 'SUCCESS') THEN
904 fun_seq_batch.Batch_exit(p_request_id => l_request_id
905 ,x_status => l_gl_seq_status);
906 END IF;
907 END IF;
908
909 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
910 trace
911 (p_msg => 'end of procedure reset_sequence'
912 ,p_level => C_LEVEL_PROCEDURE
913 ,p_module => l_log_module);
914 END IF;
915 EXCEPTION
916 WHEN xla_exceptions_pkg.application_exception THEN
917 p_retcode := 2;
918 p_errbuf := xla_messages_pkg.get_message;
919 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
920 trace
921 (p_msg => 'p_retcode = '||p_retcode
922 ,p_level => C_LEVEL_PROCEDURE
923 ,p_module => l_log_module);
924 trace
925 (p_msg => 'p_errbuf = '||p_errbuf
926 ,p_level => C_LEVEL_PROCEDURE
930 ,p_level => C_LEVEL_PROCEDURE
927 ,p_module => l_log_module);
928 trace
929 (p_msg => 'END of procedure assign_sequence'
931 ,p_module => l_log_module);
932 END IF;
933 WHEN OTHERS THEN
934 p_retcode := 2;
935 p_errbuf := sqlerrm;
936 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
937 trace
938 (p_msg => 'p_retcode = '||p_retcode
939 ,p_level => C_LEVEL_PROCEDURE
940 ,p_module => l_log_module);
941 trace
942 (p_msg => 'p_errbuf = '||p_errbuf
943 ,p_level => C_LEVEL_PROCEDURE
944 ,p_module => l_log_module);
945 trace
946 (p_msg => 'END of procedure assign_sequence'
947 ,p_level => C_LEVEL_PROCEDURE
948 ,p_module => l_log_module);
949 END IF;
950 END reset_sequence;
951
952 PROCEDURE update_entries_from_gt is
953 l_log_module VARCHAR2(240);
954 BEGIN
955 IF g_log_enabled THEN
956 l_log_module := C_DEFAULT_MODULE||'.update_entries_from_gt';
957 END IF;
958 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
959 trace
960 (p_msg => 'BEGIN of procedure update_entries_from_gt'
961 ,p_level => C_LEVEL_PROCEDURE
962 ,p_module => l_log_module);
963 END IF;
964
965 UPDATE gl_je_headers gjh
966 SET (gjh.close_acct_seq_value
967 ,gjh.close_acct_seq_version_id
968 ,gjh.close_acct_seq_assign_id) =
969 (select xgt.sequence_value
970 ,xgt.sequence_version_id
971 ,xgt.sequence_assign_id
972 FROM xla_seq_je_headers_gt xgt
973 WHERE xgt.application_id = 101
974 AND gjh.je_header_id = xgt.ae_header_id)
975 WHERE gjh.je_header_id in (select ae_header_id from xla_seq_je_headers_gt where application_id=101);
976
977 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
978 trace
979 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
980 ,p_level => C_LEVEL_STATEMENT
981 ,p_module => l_log_module);
982 END IF;
983
984 UPDATE xla_ae_headers xah
985 SET (xah.close_acct_seq_value
986 ,xah.close_acct_seq_version_id
987 ,xah.close_acct_seq_assign_id) =
988 (SELECT xgt.sequence_value
989 ,xgt.sequence_version_id
990 ,xgt.sequence_assign_id
991 FROM xla_seq_je_headers_gt xgt
992 WHERE xgt.application_id = 602
993 AND xah.ae_header_id = xgt.ae_header_id)
994 WHERE xah.ae_header_id in (select ae_header_id from xla_seq_je_headers_gt where application_id=602);
995
996 /*
997 UPDATE (SELECT xah.close_acct_seq_value
998 ,xah.close_acct_seq_version_id
999 ,xah.close_acct_seq_assign_id
1000 ,xgt.sequence_assign_id
1001 ,xgt.sequence_version_id
1002 ,xgt.sequence_value
1003 FROM xla_ae_headers xah
1004 ,xla_seq_je_headers_gt xgt
1005 WHERE xgt.application_id = 602
1006 AND xah.ae_header_id = xgt.ae_header_id)
1007 SET close_acct_seq_value=sequence_value
1008 ,close_acct_seq_version_id = sequence_version_id
1009 ,close_acct_seq_assign_id = sequence_assign_id;
1010 */
1011
1012 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1013 trace
1014 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1015 ,p_level => C_LEVEL_STATEMENT
1016 ,p_module => l_log_module);
1017 END IF;
1018
1019 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1020 trace
1021 (p_msg => 'end of procedure update_entries_from_gt'
1022 ,p_level => C_LEVEL_PROCEDURE
1023 ,p_module => l_log_module);
1024 END IF;
1025
1026 END update_entries_from_gt;
1027
1028 /*==============================================================
1029 -- assumption1: p_gl_sort_date and p_xla_sort_date must be the same
1030 ==============================================================*/
1031
1032 PROCEDURE reset_reporting_seq_num(p_ledger_id IN NUMBER
1033 , p_start_date IN DATE
1034 , p_end_date IN DATE
1035 , p_sort_date IN VARCHAR2) is
1036
1037 c_reset_cursor t_reset_seq;
1038 l_seq_value NUMBER;
1039 l_seq_ver_id NUMBER;
1040 l_log_module VARCHAR2(240);
1041
1042 BEGIN
1043 IF g_log_enabled THEN
1044 l_log_module := C_DEFAULT_MODULE||'.reset_reporting_seq_num';
1045 END IF;
1046 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1047 trace
1048 (p_msg => 'BEGIN of procedure reset_reporting_seq_num'
1049 ,p_level => C_LEVEL_PROCEDURE
1050 ,p_module => l_log_module);
1051 trace
1052 (p_msg => 'p_ledger_id:'||to_char(p_ledger_id)
1053 || 'p_start:'||to_char(p_start_date)
1054 || 'p_end:'||to_char(p_end_date)
1055 || 'p_sort_date:'||p_sort_date
1056 ,p_level => C_LEVEL_PROCEDURE
1057 ,p_module => l_log_module);
1058 END IF;
1059
1060
1061 IF(p_sort_date = 'GL_DATE') THEN
1062 OPEN c_reset_cursor FOR
1063 SELECT min(close_acct_seq_value), close_acct_seq_version_id
1064 FROM (
1065 SELECT close_acct_seq_value, close_acct_seq_version_id
1066 FROM xla_ae_headers
1067 WHERE ledger_id=p_ledger_id
1068 AND accounting_date >= p_start_date
1069 AND accounting_date < p_end_date
1070 AND close_acct_seq_version_id is not null
1071 AND gl_transfer_status_code = 'Y'
1072 AND accounting_entry_status_code = 'F'
1073 UNION
1074 SELECT close_acct_seq_value, close_acct_seq_version_id
1075 FROM gl_je_headers
1076 WHERE ledger_id=p_ledger_id
1077 AND default_effective_date >= p_start_date
1078 AND default_effective_date < p_end_date
1079 AND status = 'P'
1080 AND close_acct_seq_version_id is not null
1081 )
1082 GROUP BY close_acct_seq_version_id;
1083 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1084 OPEN c_reset_cursor FOR
1085 SELECT min(close_acct_seq_value), close_acct_seq_version_id
1086 FROM (
1087 SELECT close_acct_seq_value, close_acct_seq_version_id
1088 FROM xla_ae_headers
1089 WHERE ledger_id=p_ledger_id
1090 AND nvl(reference_date, accounting_date) >= p_start_date
1091 AND nvl(reference_date, accounting_date) < p_end_date
1092 AND close_acct_seq_version_id is not null
1093 AND gl_transfer_status_code = 'Y'
1094 AND accounting_entry_status_code = 'F'
1095 UNION
1096 SELECT close_acct_seq_value, close_acct_seq_version_id
1097 FROM gl_je_headers gjh, gl_period_statuses gps
1098 WHERE gjh.ledger_id=p_ledger_id
1099 AND nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) >= p_start_date
1100 AND nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) < p_end_date
1101 AND gjh.status = 'P'
1102 AND gjh.close_acct_seq_version_id is not null
1103 AND gps.application_id = 101
1104 AND gps.ledger_id=p_ledger_id
1105 AND gps.period_name = gjh.period_name
1106 )
1107 GROUP BY close_acct_seq_version_id;
1108 ELSE
1109 OPEN c_reset_cursor FOR
1110 SELECT min(close_acct_seq_value), close_acct_seq_version_id
1111 FROM (
1112 SELECT close_acct_seq_value, close_acct_seq_version_id
1113 FROM xla_ae_headers
1114 WHERE ledger_id=p_ledger_id
1115 AND nvl(completed_date,accounting_date)>= p_start_date
1116 AND nvl(completed_date,accounting_date) < p_end_date
1117 AND close_acct_seq_version_id is not null
1118 AND gl_transfer_status_code = 'Y'
1119 AND accounting_entry_status_code = 'F'
1120 UNION
1121 SELECT close_acct_seq_value, close_acct_seq_version_id
1122 FROM gl_je_headers
1123 WHERE ledger_id=p_ledger_id
1124 AND posted_date >= p_start_date
1125 AND posted_date < p_end_date
1126 AND status = 'P'
1127 AND close_acct_seq_version_id is not null
1128 )
1129 GROUP BY close_acct_seq_version_id;
1130 END IF;
1131
1132 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
1133 trace
1134 (p_msg => 'just before loop'
1135 ,p_level => C_LEVEL_STATEMENT
1136 ,p_module => l_log_module);
1137 END IF;
1138 LOOP
1139 FETCH c_reset_cursor into l_seq_value, l_seq_ver_id;
1140 EXIT WHEN c_reset_cursor%NOTFOUND;
1141
1142 fun_seq.reset(p_seq_version_id => l_seq_ver_id
1143 ,p_sequence_number => l_seq_value -1 );
1144 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
1145 trace
1146 (p_msg => 'ver_id:'||to_char(l_seq_ver_id) || ' ver value:'||
1147 to_char(l_seq_value -1 )
1148 ,p_level => C_LEVEL_STATEMENT
1149 ,p_module => l_log_module);
1150 END IF;
1151 END LOOP;
1152 CLOSE c_reset_cursor;
1153
1154 IF(p_sort_date = 'GL_DATE') THEN
1155 UPDATE xla_ae_headers
1156 SET close_acct_seq_value = null
1157 ,close_acct_seq_version_id = null
1158 ,close_acct_seq_assign_id = null
1159 WHERE ledger_id=p_ledger_id
1160 AND accounting_date >= p_start_date
1161 AND accounting_date < p_end_date
1162 AND close_acct_seq_version_id is not null
1163 AND gl_transfer_status_code = 'Y'
1164 AND accounting_entry_status_code = 'F';
1165
1166 UPDATE gl_je_headers
1167 SET close_acct_seq_value = null
1168 ,close_acct_seq_version_id = null
1169 ,close_acct_seq_assign_id = null
1170 WHERE ledger_id=p_ledger_id
1171 AND default_effective_date >= p_start_date
1172 AND default_effective_date < p_end_date
1173 AND status = 'P'
1174 AND close_acct_seq_version_id is not null;
1175
1176 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1177
1178 UPDATE xla_ae_headers
1179 SET close_acct_seq_value = null
1180 ,close_acct_seq_version_id = null
1181 ,close_acct_seq_assign_id = null
1182 WHERE ledger_id=p_ledger_id
1183 AND nvl(reference_date, accounting_date) >= p_start_date
1184 AND nvl(reference_date, accounting_date) < p_end_date
1185 AND close_acct_seq_version_id is not null
1186 AND gl_transfer_status_code = 'Y'
1187 AND accounting_entry_status_code = 'F';
1188
1189 UPDATE gl_je_headers
1190 SET close_acct_seq_value = null
1191 ,close_acct_seq_version_id = null
1192 ,close_acct_seq_assign_id = null
1193 WHERE je_header_id in
1194 (
1195 SELECT gjh.je_header_id
1196 FROM gl_je_headers gjh, gl_period_statuses gps
1197 WHERE gjh.ledger_id=p_ledger_id
1198 AND nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) >= p_start_date
1199 AND nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) < p_end_date
1200 AND gjh.status = 'P'
1201 AND gjh.close_acct_seq_version_id is not null
1202 AND gps.application_id = 101
1203 AND gps.ledger_id=p_ledger_id
1204 AND gps.period_name = gjh.period_name
1205 );
1206 ELSE
1207 UPDATE xla_ae_headers
1208 SET close_acct_seq_value = null
1209 ,close_acct_seq_version_id = null
1210 ,close_acct_seq_assign_id = null
1211 WHERE ledger_id=p_ledger_id
1212 AND nvl(completed_date,accounting_date) >= p_start_date
1213 AND nvl(completed_date,accounting_date) < p_end_date
1214 AND close_acct_seq_version_id is not null
1215 AND gl_transfer_status_code = 'Y'
1216 AND accounting_entry_status_code = 'F';
1217
1218 UPDATE gl_je_headers
1219 SET close_acct_seq_value = null
1220 ,close_acct_seq_version_id = null
1221 ,close_acct_seq_assign_id = null
1222 WHERE ledger_id=p_ledger_id
1223 AND posted_date >= p_start_date
1224 AND posted_date < p_end_date
1225 AND status = 'P'
1226 AND close_acct_seq_version_id is not null;
1227 END IF;
1228
1229 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1230 trace
1231 (p_msg => 'END of procedure reset_reporting_seq_num'
1232 ,p_level => C_LEVEL_PROCEDURE
1233 ,p_module => l_log_module);
1234 END IF;
1235
1236 END reset_reporting_seq_num;
1237
1238
1239 PROCEDURE populate_seq_gt_table(p_ledger_id IN NUMBER
1240 , p_start_date IN DATE
1241 , p_end_date IN DATE
1242 , p_sort_date IN VARCHAR2) is
1243 l_log_module VARCHAR2(240);
1244 l_temp NUMBER:=0;
1245
1246 -- if there is such segment defined, we need check before inserting into the table
1247 -- otherwise, all the segments are balancing segments.
1248
1249 cursor c_analytical_segment_defined(l_ledger_id NUMBER) is
1250 SELECT 1
1251 FROM gl_ledger_segment_values glsv
1252 ,gl_ledger_norm_seg_vals glnsv
1253 WHERE
1254 glsv.ledger_id = l_ledger_id
1255 AND glsv.segment_type_code = 'B'
1256 AND glsv.parent_record_id = glnsv.record_id
1257 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'N'
1258 AND glsv.Status_code is NULL
1262 IF g_log_enabled THEN
1259 AND glnsv.Status_code is NULL;
1260
1261 BEGIN
1263 l_log_module := C_DEFAULT_MODULE||'.populate_seq_gt_table';
1264 END IF;
1265 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1266 trace
1267 (p_msg => 'BEGIN of procedure populate_seq_gt_table'
1268 ,p_level => C_LEVEL_PROCEDURE
1269 ,p_module => l_log_module);
1270 END IF;
1271
1272 open c_analytical_segment_defined(p_ledger_id);
1273 fetch c_analytical_segment_defined into l_temp;
1274 IF(c_analytical_segment_defined%NOTFOUND) THEN
1275 l_temp := 0;
1276 END IF;
1277 close c_analytical_segment_defined;
1278
1279 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1280 trace
1281 (p_msg => 'analytical segment defined:'||to_char(l_temp)
1282 ,p_level => C_LEVEL_STATEMENT
1283 ,p_module => l_log_module);
1284 END IF;
1285
1286 IF (l_temp = 1) THEN
1287 IF (p_sort_date = 'GL_DATE') THEN
1288 -- populate the table XLA_SEQ_JE_HEADERS_GT
1289 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1290 (application_id
1291 ,ledger_id
1292 ,ae_header_id
1293 ,je_source_name
1294 ,je_category_name
1295 ,gl_date
1296 ,reference_date
1297 ,completion_posted_date)
1298 (SELECT 101
1299 ,p_ledger_id
1300 ,gjh.je_header_id
1301 ,gjh.je_source
1302 ,gjh.je_category
1303 ,gjh.default_effective_date
1304 ,nvl(gjh.reference_date,
1305 decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1306 ,gjh.posted_date
1307 FROM gl_je_headers gjh
1308 ,gl_period_statuses gps
1309 ,xla_subledgers xs
1310 WHERE gjh.ledger_id = p_ledger_id
1311 AND gjh.default_effective_date >= p_start_date
1312 AND gjh.default_effective_date < p_end_date
1313 AND gjh.status = 'P'
1314 AND gjh.actual_flag = 'A'
1315 AND ( gjh.parent_je_header_id is not null
1316 OR xs.je_source_name is null
1317 -- 6722378
1318 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1319 )
1320 AND gjh.je_source = xs.je_source_name (+)
1321 AND gps.application_id = 101
1322 AND gjh.period_name = gps.period_name
1323 AND gps.ledger_id = p_ledger_id
1324 AND exists
1325 (SELECT 1
1326 FROM gl_ledger_segment_values glsv
1327 ,gl_ledger_norm_seg_vals glnsv
1328 ,gl_je_segment_values gljsv
1329 WHERE gljsv.je_header_id = gjh.je_header_id
1330 AND glsv.ledger_id (+) = p_ledger_id
1331 AND gljsv.segment_type_code = 'B'
1332 AND gljsv.segment_type_code = glsv.segment_type_code (+)
1333 AND gljsv.segment_value = glsv.segment_value (+)
1334 AND glsv.parent_record_id = glnsv.record_id (+)
1335 AND glsv.status_code is NULL
1336 AND glnsv.status_code is NULL
1337 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1338 );
1339 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1340 trace
1341 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1342 ,p_level => C_LEVEL_STATEMENT
1343 ,p_module => l_log_module);
1344 END IF;
1345
1346 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1347 -- populate the table XLA_SEQ_JE_HEADERS_GT
1348 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1349 (application_id
1350 ,ledger_id
1351 ,ae_header_id
1352 ,je_source_name
1353 ,je_category_name
1354 ,gl_date
1355 ,reference_date
1356 ,completion_posted_date)
1357 (SELECT 101
1358 ,p_ledger_id
1359 ,gjh.je_header_id
1360 ,gjh.je_source
1361 ,gjh.je_category
1362 ,gjh.default_effective_date
1363 ,nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1364 ,gjh.posted_date
1365 FROM gl_je_headers gjh
1366 ,gl_period_statuses gps
1367 ,xla_subledgers xs
1368 WHERE gjh.ledger_id = p_ledger_id
1369 AND nvl(gjh.reference_date
1370 ,decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) >= p_start_date
1371 AND nvl(gjh.reference_date
1372 ,decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) < p_end_date
1373 AND gjh.status = 'P'
1374 AND gjh.actual_flag = 'A'
1375 AND nvl(gjh.je_from_sla_flag,'N') = 'N'
1376 AND ( gjh.parent_je_header_id is not null
1377 OR xs.je_source_name is null
1378 -- 6722378 upgraded journal entries
1379 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1380 )
1384 AND gps.ledger_id = p_ledger_id
1381 AND gjh.je_source = xs.je_source_name (+)
1382 AND gps.application_id = 101
1383 AND gjh.period_name = gps.period_name
1385 AND exists
1386 (SELECT 1
1387 FROM gl_ledger_segment_values glsv
1388 ,gl_ledger_norm_seg_vals glnsv
1389 ,gl_je_segment_values gljsv
1390 WHERE gljsv.je_header_id = gjh.je_header_id
1391 AND glsv.ledger_id (+) = p_ledger_id
1392 AND gljsv.segment_type_code = 'B'
1393 AND gljsv.segment_type_code = glsv.segment_type_code (+)
1394 AND gljsv.segment_value = glsv.segment_value (+)
1395 AND glsv.parent_record_id = glnsv.record_id (+)
1396 AND glsv.status_code is NULL
1397 AND glnsv.status_code is NULL
1398 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1399 );
1400 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1401 trace
1402 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1403 ,p_level => C_LEVEL_STATEMENT
1404 ,p_module => l_log_module);
1405 END IF;
1406 ELSE
1407 -- populate the table XLA_SEQ_JE_HEADERS_GT
1408 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1409 (application_id
1410 ,ledger_id
1411 ,ae_header_id
1412 ,je_source_name
1413 ,je_category_name
1414 ,gl_date
1415 ,reference_date
1416 ,completion_posted_date)
1417 (SELECT 101
1418 ,p_ledger_id
1419 ,gjh.je_header_id
1420 ,gjh.je_source
1421 ,gjh.je_category
1422 ,gjh.default_effective_date
1423 ,nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1424 ,gjh.posted_date
1425 FROM gl_je_headers gjh
1426 ,gl_period_statuses gps
1427 ,xla_subledgers xs
1428 WHERE gjh.ledger_id = p_ledger_id
1429 AND gjh.posted_date>= p_start_date
1430 AND gjh.posted_date< p_end_date
1431 AND gjh.status = 'P'
1432 AND gjh.actual_flag = 'A'
1433 AND ( gjh.parent_je_header_id is not null
1434 OR xs.je_source_name is null
1435 -- 6722378 upgraded journal entries
1436 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1437 )
1438 AND gjh.je_source=xs.je_source_name (+)
1439 AND gps.application_id = 101
1440 AND gjh.period_name = gps.period_name
1441 AND gps.ledger_id = p_ledger_id
1442 AND exists
1443 (SELECT 1
1444 FROM gl_ledger_segment_values glsv
1445 ,gl_ledger_norm_seg_vals glnsv
1446 ,gl_je_segment_values gljsv
1447 WHERE gljsv.je_header_id = gjh.je_header_id
1448 AND glsv.ledger_id (+) = p_ledger_id
1449 AND gljsv.segment_type_code = 'B'
1450 AND gljsv.segment_type_code = glsv.segment_type_code (+)
1451 AND gljsv.segment_value = glsv.segment_value (+)
1452 AND glsv.parent_record_id = glnsv.record_id (+)
1453 AND glsv.status_code is NULL
1454 AND glnsv.status_code is NULL
1455 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1456 );
1457 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1458 trace
1459 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1460 ,p_level => C_LEVEL_STATEMENT
1461 ,p_module => l_log_module);
1462 END IF;
1463 END IF;
1464
1465
1466 IF (p_sort_date = 'GL_DATE') THEN
1467 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1468 (application_id
1469 ,ledger_id
1470 ,ae_header_id
1471 ,je_source_name
1472 ,je_category_name
1473 ,gl_date
1474 ,reference_date
1475 ,completion_posted_date)
1476 (SELECT 602
1477 ,p_ledger_id
1478 ,xah.ae_header_id
1479 ,xs.je_source_name
1480 ,xah.je_category_name
1481 ,xah.accounting_date
1482 ,nvl(xah.reference_date, xah.accounting_date)
1483 ,nvl(xah.completed_date, xah.accounting_date)
1484 FROM xla_ae_headers xah
1485 ,xla_subledgers xs
1486 WHERE xah.ledger_id = p_ledger_id
1487 AND xah.accounting_date >= p_start_date
1488 AND xah.accounting_date < p_end_date
1489 AND xah.accounting_entry_status_code = 'F'
1490 AND xah.gl_transfer_status_code = 'Y'
1491 AND xah.application_id = xs.application_id
1492 AND xah.balance_type_code = 'A'
1493 AND exists
1494 (SELECT 1
1495 FROM gl_ledger_segment_values glsv
1496 ,gl_ledger_norm_seg_vals glnsv
1497 ,xla_ae_segment_values xasv
1501 AND xasv.segment_type_code = glsv.segment_type_code (+)
1498 WHERE xah.ae_header_id = xasv.ae_header_id
1499 AND glsv.ledger_id (+) = p_ledger_id
1500 AND xasv.segment_type_code = 'B'
1502 AND xasv.segment_value = glsv.segment_value (+)
1503 AND glsv.parent_record_id = glnsv.record_id (+)
1504 AND glsv.status_code is NULL
1505 AND glnsv.status_code is NULL
1506 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1507 );
1508 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1509 trace
1510 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1511 ,p_level => C_LEVEL_STATEMENT
1512 ,p_module => l_log_module);
1513 END IF;
1514 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1515 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1516 (application_id
1517 ,ledger_id
1518 ,ae_header_id
1519 ,je_source_name
1520 ,je_category_name
1521 ,gl_date
1522 ,reference_date
1523 ,completion_posted_date)
1524 (SELECT 602
1525 ,p_ledger_id
1526 ,xah.ae_header_id
1527 ,xs.je_source_name
1528 ,xah.je_category_name
1529 ,xah.accounting_date
1530 ,nvl(xah.reference_date, xah.accounting_date)
1531 ,nvl(xah.completed_date, xah.accounting_date)
1532 FROM xla_ae_headers xah
1533 ,xla_subledgers xs
1534 WHERE xah.ledger_id = p_ledger_id
1535 AND nvl(xah.reference_date, xah.accounting_date) >= p_start_date
1536 AND nvl(xah.reference_date, xah.accounting_date) < p_end_date
1537 AND xah.accounting_entry_status_code = 'F'
1538 AND xah.gl_transfer_status_code = 'Y'
1539 AND xah.application_id = xs.application_id
1540 AND xah.balance_type_code = 'A'
1541 AND exists
1542 (SELECT 1
1543 FROM gl_ledger_segment_values glsv
1544 ,gl_ledger_norm_seg_vals glnsv
1545 ,xla_ae_segment_values xasv
1546 WHERE xah.ae_header_id = xasv.ae_header_id
1547 AND glsv.ledger_id (+) = p_ledger_id
1548 AND xasv.segment_type_code = 'B'
1549 AND xasv.segment_type_code = glsv.segment_type_code (+)
1550 AND xasv.segment_value = glsv.segment_value (+)
1551 AND glsv.parent_record_id = glnsv.record_id(+)
1552 AND glsv.status_code is NULL
1553 AND glnsv.status_code is NULL
1554 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1555 );
1556 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1557 trace
1558 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1559 ,p_level => C_LEVEL_STATEMENT
1560 ,p_module => l_log_module);
1561 END IF;
1562 ELSE
1563 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1564 (application_id
1565 ,ledger_id
1566 ,ae_header_id
1567 ,je_source_name
1568 ,je_category_name
1569 ,gl_date
1570 ,reference_date
1571 ,completion_posted_date)
1572 (SELECT 602
1573 ,p_ledger_id
1574 ,xah.ae_header_id
1575 ,xs.je_source_name
1576 ,xah.je_category_name
1577 ,xah.accounting_date
1578 ,nvl(xah.reference_date, xah.accounting_date)
1579 ,nvl(xah.completed_date, xah.accounting_date)
1580
1581 FROM xla_ae_headers xah
1582 ,xla_subledgers xs
1583 WHERE xah.ledger_id = p_ledger_id
1584 AND nvl(xah.completed_date, xah.accounting_date) >= p_start_date
1585 AND nvl(xah.completed_date, xah.accounting_date) < p_end_date
1586 AND xah.accounting_entry_status_code = 'F'
1587 AND xah.gl_transfer_status_code = 'Y'
1588 AND xah.application_id = xs.application_id
1589 AND xah.balance_type_code = 'A'
1590 AND exists
1591 (SELECT 1
1592 FROM gl_ledger_segment_values glsv
1593 ,gl_ledger_norm_seg_vals glnsv
1594 ,xla_ae_segment_values xasv
1595 WHERE xah.ae_header_id = xasv.ae_header_id
1596 AND glsv.ledger_id (+) = p_ledger_id
1597 AND xasv.segment_type_code = 'B'
1598 AND xasv.segment_type_code = glsv.segment_type_code (+)
1599 AND xasv.segment_value = glsv.segment_value (+)
1600 AND glsv.parent_record_id = glnsv.record_id(+)
1601 AND glsv.status_code is NULL
1602 AND glnsv.status_code is NULL
1603 AND nvl(glnsv.sla_sequencing_flag, 'Y') = 'Y')
1604 );
1605 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1606 trace
1607 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1608 ,p_level => C_LEVEL_STATEMENT
1609 ,p_module => l_log_module);
1610 END IF;
1611
1612 END IF;
1613 ELSE
1614 IF (p_sort_date = 'GL_DATE') THEN
1615 -- populate the table XLA_SEQ_JE_HEADERS_GT
1616 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1617 (application_id
1618 ,ledger_id
1619 ,ae_header_id
1620 ,je_source_name
1621 ,je_category_name
1622 ,gl_date
1623 ,reference_date
1624 ,completion_posted_date)
1625 (SELECT 101
1626 ,p_ledger_id
1627 ,gjh.je_header_id
1628 ,gjh.je_source
1629 ,gjh.je_category
1630 ,gjh.default_effective_date
1631 ,nvl(gjh.reference_date,
1632 decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1633 ,gjh.posted_date
1634 FROM gl_je_headers gjh
1635 ,gl_period_statuses gps
1636 ,xla_subledgers xs
1637 WHERE gjh.ledger_id = p_ledger_id
1638 AND gjh.default_effective_date >= p_start_date
1639 AND gjh.default_effective_date < p_end_date
1640 AND gjh.status = 'P'
1641 AND gjh.actual_flag = 'A'
1642 AND ( gjh.parent_je_header_id is not null
1643 OR xs.je_source_name is null
1644 -- 6722378 upgraded journal entries
1645 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1646 )
1647 AND gjh.je_source = xs.je_source_name (+)
1648 AND gps.application_id = 101
1649 AND gjh.period_name = gps.period_name
1650 AND gps.ledger_id = p_ledger_id
1651 );
1652 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1653 trace
1654 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1655 ,p_level => C_LEVEL_STATEMENT
1656 ,p_module => l_log_module);
1657 END IF;
1658
1659 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1660 -- populate the table XLA_SEQ_JE_HEADERS_GT
1661 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1662 (application_id
1663 ,ledger_id
1664 ,ae_header_id
1665 ,je_source_name
1666 ,je_category_name
1667 ,gl_date
1668 ,reference_date
1669 ,completion_posted_date)
1670 (SELECT 101
1671 ,p_ledger_id
1672 ,gjh.je_header_id
1673 ,gjh.je_source
1674 ,gjh.je_category
1675 ,gjh.default_effective_date
1676 ,nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1677 ,gjh.posted_date
1678 FROM gl_je_headers gjh
1679 ,gl_period_statuses gps
1680 ,xla_subledgers xs
1681 WHERE gjh.ledger_id = p_ledger_id
1682 AND nvl(gjh.reference_date
1683 ,decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) >= p_start_date
1684 AND nvl(gjh.reference_date
1685 ,decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date)) < p_end_date
1686 AND gjh.status = 'P'
1687 AND gjh.actual_flag = 'A'
1688 AND nvl(gjh.je_from_sla_flag,'N') = 'N'
1689 AND ( gjh.parent_je_header_id is not null
1690 OR xs.je_source_name is null
1691 -- 6722378 upgraded journal entries
1692 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1693 )
1694 AND gjh.je_source = xs.je_source_name (+)
1698 );
1695 AND gps.application_id = 101
1696 AND gjh.period_name = gps.period_name
1697 AND gps.ledger_id = p_ledger_id
1699 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1700 trace
1701 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1702 ,p_level => C_LEVEL_STATEMENT
1703 ,p_module => l_log_module);
1704 END IF;
1705 ELSE
1706 -- populate the table XLA_SEQ_JE_HEADERS_GT
1707 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1708 (application_id
1709 ,ledger_id
1710 ,ae_header_id
1711 ,je_source_name
1712 ,je_category_name
1713 ,gl_date
1714 ,reference_date
1715 ,completion_posted_date)
1716 (SELECT 101
1717 ,p_ledger_id
1718 ,gjh.je_header_id
1719 ,gjh.je_source
1720 ,gjh.je_category
1721 ,gjh.default_effective_date
1722 ,nvl(gjh.reference_date, decode(gps.adjustment_period_flag, 'Y', gjh.posted_date, gjh.default_effective_date))
1723 ,gjh.posted_date
1724 FROM gl_je_headers gjh
1725 ,gl_period_statuses gps
1726 ,xla_subledgers xs
1727 WHERE gjh.ledger_id = p_ledger_id
1728 AND gjh.posted_date>= p_start_date
1729 AND gjh.posted_date< p_end_date
1730 AND gjh.status = 'P'
1731 AND gjh.actual_flag = 'A'
1732 AND ( gjh.parent_je_header_id is not null
1733 OR xs.je_source_name is null
1734 -- 6722378 upgraded journal entries
1735 OR (gjh.global_attribute_category = 'JE.GR.GLXJEENT.HEADER' AND xs.je_source_name = 'Project Accounting')
1736 )
1737 AND gjh.je_source=xs.je_source_name (+)
1738 AND gps.application_id = 101
1739 AND gjh.period_name = gps.period_name
1740 AND gps.ledger_id = p_ledger_id
1741 );
1742 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1743 trace
1744 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1745 ,p_level => C_LEVEL_STATEMENT
1746 ,p_module => l_log_module);
1747 END IF;
1748 END IF;
1749
1750
1751 IF (p_sort_date = 'GL_DATE') THEN
1752 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1753 (application_id
1754 ,ledger_id
1755 ,ae_header_id
1756 ,je_source_name
1757 ,je_category_name
1758 ,gl_date
1759 ,reference_date
1760 ,completion_posted_date)
1761 (SELECT 602
1762 ,p_ledger_id
1763 ,xah.ae_header_id
1764 ,xs.je_source_name
1765 ,xah.je_category_name
1766 ,xah.accounting_date
1767 ,nvl(xah.reference_date, xah.accounting_date)
1768 ,nvl(xah.completed_date, xah.accounting_date)
1769
1770 FROM xla_ae_headers xah
1771 ,xla_subledgers xs
1772 WHERE xah.ledger_id = p_ledger_id
1773 AND xah.accounting_date >= p_start_date
1774 AND xah.accounting_date < p_end_date
1775 AND xah.accounting_entry_status_code = 'F'
1776 AND xah.gl_transfer_status_code = 'Y'
1777 AND xah.application_id = xs.application_id
1778 AND xah.balance_type_code = 'A'
1779 );
1780 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1781 trace
1782 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1783 ,p_level => C_LEVEL_STATEMENT
1784 ,p_module => l_log_module);
1785 END IF;
1786 ELSIF (p_sort_date = 'REFERENCE_DATE') THEN
1787 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1788 (application_id
1789 ,ledger_id
1790 ,ae_header_id
1791 ,je_source_name
1792 ,je_category_name
1793 ,gl_date
1794 ,reference_date
1795 ,completion_posted_date)
1796 (SELECT 602
1797 ,p_ledger_id
1798 ,xah.ae_header_id
1799 ,xs.je_source_name
1800 ,xah.je_category_name
1801 ,xah.accounting_date
1802 ,nvl(xah.reference_date, xah.accounting_date)
1803 ,nvl(xah.completed_date, xah.accounting_date)
1804
1805 FROM xla_ae_headers xah
1806 ,xla_subledgers xs
1807 WHERE xah.ledger_id = p_ledger_id
1808 AND nvl(xah.reference_date, xah.accounting_date) >= p_start_date
1809 AND nvl(xah.reference_date, xah.accounting_date) < p_end_date
1810 AND xah.accounting_entry_status_code = 'F'
1811 AND xah.gl_transfer_status_code = 'Y'
1812 AND xah.application_id = xs.application_id
1813 AND xah.balance_type_code = 'A'
1814 );
1815 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1816 trace
1817 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1818 ,p_level => C_LEVEL_STATEMENT
1819 ,p_module => l_log_module);
1820 END IF;
1821 ELSE
1822 INSERT INTO XLA_SEQ_JE_HEADERS_GT
1823 (application_id
1824 ,ledger_id
1825 ,ae_header_id
1826 ,je_source_name
1827 ,je_category_name
1828 ,gl_date
1829 ,reference_date
1833 ,xah.ae_header_id
1830 ,completion_posted_date)
1831 (SELECT 602
1832 ,p_ledger_id
1834 ,xs.je_source_name
1835 ,xah.je_category_name
1836 ,xah.accounting_date
1837 ,nvl(xah.reference_date, xah.accounting_date)
1838 ,nvl(xah.completed_date, xah.accounting_date)
1839
1840 FROM xla_ae_headers xah
1841 ,xla_subledgers xs
1842 WHERE xah.ledger_id = p_ledger_id
1843 AND nvl(xah.completed_date, xah.accounting_date)>= p_start_date
1844 AND nvl(xah.completed_date, xah.accounting_date) < p_end_date
1845 AND xah.accounting_entry_status_code = 'F'
1846 AND xah.gl_transfer_status_code = 'Y'
1847 AND xah.application_id = xs.application_id
1848 AND xah.balance_type_code = 'A'
1849 );
1850 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1851 trace
1852 (p_msg => '#number of rows inserted:'||to_char(SQL%ROWCOUNT)
1853 ,p_level => C_LEVEL_STATEMENT
1854 ,p_module => l_log_module);
1855 END IF;
1856
1857 END IF;
1858 END IF;
1859
1860 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1861 trace
1862 (p_msg => 'end of procedure populate_seq_gt_table'
1863 ,p_level => C_LEVEL_PROCEDURE
1864 ,p_module => l_log_module);
1865 END IF;
1866
1867 exception
1868 when others then
1869 raise;
1870 end;
1871
1872
1873 --=============================================================================
1874 -- *********** Initialization routine **********
1875 --=============================================================================
1876
1877 --=============================================================================
1878 --
1879 --
1880 --
1881 --
1882 --
1883 --
1884 --
1885 --
1886 --
1887 --
1888 -- Following code is executed when the package body is referenced for the first
1889 -- time
1890 --
1891 --
1892 --
1893 --
1894 --
1895 --
1896 --
1897 --
1898 --
1899 --
1900 --
1901 --
1902 --=============================================================================
1903
1904 BEGIN
1905 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1906 g_log_enabled := fnd_log.test
1907 (log_level => g_log_level
1908 ,module => C_DEFAULT_MODULE);
1909
1910 IF NOT g_log_enabled THEN
1911 g_log_level := C_LEVEL_LOG_DISABLED;
1912 END IF;
1913
1914 END XLA_REPORTING_SEQUENCE_PKG;