[Home] [Help]
PACKAGE BODY: APPS.GL_PERIOD_STATUSES_PKG
Source
1 PACKAGE BODY gl_period_statuses_pkg AS
2 /* $Header: glipstab.pls 120.11.12010000.2 2009/09/18 06:21:33 akhanapu ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7
8 PROCEDURE check_for_gap (
9 x_periodsetname IN VARCHAR2,
10 x_periodtype IN VARCHAR2 ) IS
11
12 not_assigned CONSTANT VARCHAR2(15) := 'NOT ASSIGNED';
13
14 gap_date DATE;
15 start_date DATE;
16 end_date DATE;
17 beginning DATE;
18 ending DATE;
19
20 CURSOR period_set IS
21 SELECT min(start_date) begins, max(end_date) ends
22 FROM gl_periods
23 WHERE period_set_name = x_periodsetname
24 AND period_type = x_periodtype;
25
26 CURSOR gap_exists IS
27 SELECT accounting_date
28 FROM gl_date_period_map
29 WHERE period_name = not_assigned
30 AND period_set_name = x_periodsetname
31 AND period_type = x_periodtype
32 AND accounting_date BETWEEN beginning AND ending;
33
34 CURSOR gap_start IS
35 SELECT max(accounting_date)
36 FROM gl_date_period_map
37 WHERE period_name <> not_assigned
38 AND period_set_name = x_periodsetname
39 AND period_type = x_periodtype
40 AND accounting_date < gap_date;
41
42 CURSOR gap_end IS
43 SELECT min(accounting_date)
44 FROM gl_date_period_map
45 WHERE period_name <> not_assigned
46 AND period_set_name = x_periodsetname
47 AND period_type = x_periodtype
48 AND accounting_date > gap_date;
49
50 BEGIN
51 -- Open the gap_exists cursor and see if we get anything
52 OPEN period_set;
53 FETCH period_set INTO beginning, ending;
54 CLOSE period_set;
55 OPEN gap_exists;
56 FETCH gap_exists INTO gap_date;
57 IF gap_exists%NOTFOUND THEN
58 CLOSE gap_exists;
59 ELSE
60 CLOSE gap_exists;
61 -- Get the spanning dates
62 OPEN gap_start;
63 FETCH gap_start INTO start_date;
64 CLOSE gap_start;
65 OPEN gap_end;
66 FETCH gap_end INTO end_date;
67 CLOSE gap_end;
68 -- Tell the user
69 fnd_message.set_name('SQLGL', 'GL_GAP_IN_CALENDAR');
70 fnd_message.set_token('CALENDAR_NAME', x_periodsetname);
71 fnd_message.set_token('START_DATE', nvl(start_date, beginning));
72 fnd_message.set_token('END_DATE', nvl(end_date, ending));
73 RAISE app_exceptions.application_exception;
74 END IF;
75
76 EXCEPTION
77 WHEN app_exceptions.application_exception THEN
78 RAISE;
79 WHEN OTHERS THEN
80 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
81 fnd_message.set_token('PROCEDURE',
82 'gl_period_statuses_pkg.check_for_gap');
83 RAISE;
84 END check_for_gap;
85
86
87 --
88 -- PUBLIC FUNCTIONS
89 --
90
91 FUNCTION default_actual_period(acc_id NUMBER,
92 led_id NUMBER) RETURN VARCHAR2 IS
93 CURSOR get_latest_opened IS
94 SELECT ps.period_name
95 FROM gl_period_statuses ps,
96 gl_access_set_ledgers acc
97 WHERE ps.application_id = 101
98 AND ps.ledger_id = led_id
99 AND ps.closing_status = 'O'
100 AND acc.access_set_id = acc_id
101 AND acc.ledger_id = ps.ledger_id
102 AND acc.access_privilege_code IN ('B','F')
103 AND ps.end_date between nvl(acc.start_date, ps.end_date-1)
104 and nvl(acc.end_date, ps.end_date+1)
105 ORDER BY effective_period_num DESC;
106
107 CURSOR get_earliest_future_ent IS
108 SELECT ps.period_name
109 FROM gl_period_statuses ps,
110 gl_access_set_ledgers acc
111 WHERE ps.application_id = 101
112 AND ps.ledger_id = led_id
113 AND ps.closing_status = 'F'
114 AND acc.access_set_id = acc_id
115 AND acc.ledger_id = ps.ledger_id
116 AND acc.access_privilege_code IN ('B','F')
117 AND ps.end_date between nvl(acc.start_date, ps.end_date-1)
118 and nvl(acc.end_date, ps.end_date+1)
119 ORDER BY effective_period_num ASC;
120 default_period VARCHAR2(15);
121 BEGIN
122 OPEN get_latest_opened;
123 FETCH get_latest_opened INTO default_period;
124
125 IF get_latest_opened%FOUND THEN
126 CLOSE get_latest_opened;
127 return(default_period);
128 ELSE
129 CLOSE get_latest_opened;
130
131 OPEN get_earliest_future_ent;
132 FETCH get_earliest_future_ent INTO default_period;
133
134 IF get_earliest_future_ent%FOUND THEN
135 CLOSE get_earliest_future_ent;
136 return(default_period);
137 ELSE
138 CLOSE get_earliest_future_ent;
139 fnd_message.set_name('SQLGL', 'GL_NO_OPEN_OR_FUTURE_PERIODS');
140 return(null);
141 END IF;
142 END IF;
143
144 EXCEPTION
145 WHEN app_exceptions.application_exception THEN
146 RAISE;
147 WHEN OTHERS THEN
148 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
149 fnd_message.set_token('PROCEDURE',
150 'gl_period_statuses_pkg.default_actual_period');
151 RAISE;
152 END default_actual_period;
153
154 PROCEDURE get_next_period(
155 x_ledger_id NUMBER,
156 x_period VARCHAR2,
157 x_next_period IN OUT NOCOPY VARCHAR2 ) IS
158
159 CURSOR c_period IS
160 SELECT ps1.period_name
161 FROM gl_period_statuses ps1,
162 gl_period_statuses ps2
163 WHERE ps1.application_id = 101
164 AND ps1.ledger_id = x_ledger_id
165 AND ps2.application_id = 101
166 AND ps2.ledger_id = x_ledger_id
167 AND ps2.period_name = x_period
168 AND ( ps1.start_date =
169 ( SELECT MIN( ps3.start_date )
170 FROM gl_period_statuses ps3
171 WHERE ps3.application_id = 101
172 AND ps3.ledger_id = x_ledger_id
173 AND ps3.start_date > ps2.start_date ) )
174 AND ps1.closing_status NOT IN ( 'N','C','P' );
175
176 BEGIN
177 OPEN c_period;
178 FETCH c_period INTO x_next_period;
179 CLOSE c_period;
180
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 RETURN;
184 WHEN app_exceptions.application_exception THEN
185 RAISE;
186 WHEN OTHERS THEN
187 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
188 fnd_message.set_token('PROCEDURE',
189 'gl_period_statuses_pkg.get_next_period');
190 RAISE;
191
192 END get_next_period;
193
194
195
196 PROCEDURE insert_led_ps(
197 x_ledger_id NUMBER,
198 x_period_set_name VARCHAR2,
199 x_accounted_period_type VARCHAR2,
200 x_last_update_date DATE,
201 x_last_updated_by NUMBER,
202 x_last_update_login NUMBER,
203 x_creation_date DATE,
204 x_created_by NUMBER ) IS
205 BEGIN
206
207 -- Before doing anything else...
208 check_for_gap(x_period_set_name, x_accounted_period_type);
209
210 LOCK TABLE GL_PERIOD_STATUSES IN SHARE UPDATE MODE;
211
212 INSERT INTO GL_PERIOD_STATUSES
213 ( application_id,
214 ledger_id,
215 set_of_books_id,
216 period_name,
217 closing_status,
218 start_date,
219 end_date,
220 period_type,
221 period_year,
222 period_num,
223 quarter_num,
224 last_update_date,
225 last_updated_by,
226 last_update_login,
227 creation_date,
228 created_by,
229 adjustment_period_flag,
230 quarter_start_date,
231 year_start_date,
232 effective_period_num,
233 migration_status_code)
234 SELECT
235 ag.application_id,
236 x_ledger_id,
237 x_ledger_id,
238 p.period_name,
239 'N',
240 p.start_date,
241 p.end_date,
242 p.period_type,
243 p.period_year,
244 p.period_num,
245 p.quarter_num,
246 x_last_update_date,
247 x_last_updated_by,
248 x_last_update_login,
249 x_creation_date,
250 x_created_by,
251 p.adjustment_period_flag,
252 p.quarter_start_date,
253 p.year_start_date,
254 p.period_year*10000 + p.period_num,
255 'N'
256 FROM
257 GL_APPLICATION_GROUPS ag,
258 GL_PERIODS p
259 WHERE p.period_set_name = x_period_set_name
260 AND p.period_type = x_accounted_period_type
261 AND ag.group_name = 'PERIOD_STATUS'
262 AND EXISTS ( SELECT 'Application Installed'
263 FROM FND_PRODUCT_INSTALLATIONS pr
264 WHERE pr.application_id = ag.application_id );
265
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 RETURN;
269 WHEN app_exceptions.application_exception THEN
270 RAISE;
271 WHEN OTHERS THEN
272 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
273 fnd_message.set_token('PROCEDURE',
274 'gl_period_statuses_pkg.insert_led_ps');
275 RAISE;
276
277 END insert_led_ps;
278
279
280 PROCEDURE insert_ps_api( x_appl_id NUMBER,
281 x_ledger_id NUMBER,
282 x_period_name VARCHAR2,
283 x_status VARCHAR2,
284 x_period_set_name VARCHAR2,
285 x_user_id NUMBER,
286 x_login_id NUMBER ) IS
287
288
289 BEGIN
290
291 LOCK TABLE GL_PERIOD_STATUSES IN SHARE UPDATE MODE;
292
293 INSERT INTO GL_PERIOD_STATUSES
294 ( application_id,
295 ledger_id,
296 set_of_books_id,
297 period_name,
298 closing_status,
299 start_date,
300 end_date,
301 period_type,
302 period_year,
303 period_num,
304 quarter_num,
305 last_update_date,
306 last_updated_by,
307 last_update_login,
308 creation_date,
309 created_by,
310 adjustment_period_flag,
311 quarter_start_date,
312 year_start_date,
313 effective_period_num,
314 migration_status_code)
315 SELECT
316 x_appl_id,
317 x_ledger_id,
318 x_ledger_id,
319 x_period_name,
320 x_status,
321 GP.start_date,
322 GP.end_date,
323 GP.period_type,
324 GP.period_year,
325 GP.period_num,
326 GP.quarter_num,
327 sysdate,
328 x_user_id,
329 x_login_id,
330 sysdate,
331 x_user_id,
332 GP.adjustment_period_flag,
333 GP.quarter_start_date,
334 GP.year_start_date,
335 GP.period_year*10000 + GP.period_num,
336 'N'
337 FROM
338 GL_PERIODS GP,
339 GL_LEDGERS LD
340 WHERE GP.period_set_name = nvl(x_period_set_name, LD.period_set_name)
341 AND GP.period_name = x_period_name
342 AND LD.ledger_id = x_ledger_id;
343
344 EXCEPTION
345 WHEN NO_DATA_FOUND THEN
346 RAISE;
347 WHEN DUP_VAL_ON_INDEX THEN
348 NULL;
349 WHEN app_exceptions.application_exception THEN
350 RAISE;
351 WHEN OTHERS THEN
352 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
353 fnd_message.set_token('PROCEDURE',
354 'gl_period_statuses_pkg.insert_ps_api');
355 RAISE;
356
357 END insert_ps_api;
358
359
360 PROCEDURE insert_period(
361 x_calendar_name VARCHAR2,
362 x_period_name VARCHAR2,
363 x_start_date DATE,
364 x_end_date DATE,
365 x_period_type VARCHAR2,
366 x_period_year NUMBER,
367 x_period_num NUMBER,
368 x_quarter_num NUMBER,
369 x_adj_period_flag VARCHAR2,
370 x_last_updated_by NUMBER,
371 x_last_update_login NUMBER,
372 x_quarter_start_date DATE,
373 x_year_start_date DATE) IS
374
375 l_Effective_period_num GL_PERIOD_STATUSES.effective_period_num%type;
376 l_track_bc_ytd_flag GL_PERIOD_STATUSES.track_bc_ytd_flag%type;
377 l_sob_id GL_PERIOD_STATUSES.ledger_id%type;
378
379 CURSOR c_period_statuses IS
380 SELECT track_bc_ytd_flag, set_of_books_id
381 FROM gl_period_statuses
382 WHERE application_id = 101
383 AND set_of_books_id in (SELECT ledger_id
384 FROM gl_ledgers
385 WHERE period_set_name = x_calendar_name
386 AND accounted_period_type = x_period_type)
387 AND effective_period_num = l_Effective_period_num;
388
389 BEGIN
390
391 SELECT max(effective_period_num)
392 INTO l_Effective_period_num
393 FROM gl_period_statuses
394 WHERE application_id = 101
395 AND set_of_books_id in (
396 SELECT ledger_id
397 FROM gl_ledgers
398 WHERE period_set_name = x_calendar_name
399 AND accounted_period_type = x_period_type);
400
401 INSERT INTO GL_PERIOD_STATUSES
402 ( application_id,
403 ledger_id,
404 set_of_books_id,
405 period_name,
406 closing_status,
407 start_date,
408 end_date,
409 period_type,
410 period_year,
411 period_num,
412 quarter_num,
413 last_update_date,
414 last_updated_by,
415 last_update_login,
416 creation_date,
417 created_by,
418 adjustment_period_flag,
419 quarter_start_date,
420 year_start_date,
421 effective_period_num,
422 migration_status_code)
423 SELECT
424 ag.application_id,
425 led.ledger_id,
426 led.ledger_id,
427 x_period_name,
428 'N',
429 x_start_date,
430 x_end_date,
431 x_period_type,
432 x_period_year,
433 x_period_num,
434 x_quarter_num,
435 sysdate,
436 x_last_updated_by,
437 x_last_update_login,
438 sysdate,
439 x_last_updated_by,
440 x_adj_period_flag,
441 x_quarter_start_date,
442 x_year_start_date,
443 x_period_year * 10000 + x_period_num,
444 'N'
445 FROM
446 GL_APPLICATION_GROUPS ag,
447 GL_LEDGERS led
448 WHERE ag.group_name = 'PERIOD_STATUS'
449 AND led.period_set_name = x_calendar_name
450 AND led.accounted_period_type = x_period_type
451 AND EXISTS ( SELECT 'Application Installed'
452 FROM FND_PRODUCT_INSTALLATIONS pr
453 WHERE pr.application_id = ag.application_id );
454
455 OPEN c_period_statuses;
456 LOOP
457 FETCH c_period_statuses
458 INTO l_track_bc_ytd_flag,l_sob_id;
459 EXIT WHEN c_period_statuses%NOTFOUND ;
460
461 IF l_track_bc_ytd_flag IS NOT NULL AND l_track_bc_ytd_flag = 'Y' THEN
462
463 UPDATE gl_period_statuses
464 SET track_bc_ytd_flag = 'Y'
465 WHERE application_id = 101
466 AND set_of_books_id = l_sob_id
467 AND period_year=x_period_year
468 AND period_num=x_period_num;
469
470 END IF;
471 END LOOP;
472 CLOSE c_period_statuses;
473
474 EXCEPTION
475 WHEN NO_DATA_FOUND THEN
476 RETURN;
477 WHEN app_exceptions.application_exception THEN
478 RAISE;
479 WHEN OTHERS THEN
480 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
481 fnd_message.set_token('PROCEDURE',
482 'gl_period_statuses_pkg.insert_period');
483 RAISE;
484 END insert_period;
485
486
487 PROCEDURE update_period(
488 x_calendar_name VARCHAR2,
489 x_old_period_name VARCHAR2,
490 x_period_name VARCHAR2,
491 x_start_date DATE,
492 x_end_date DATE,
493 x_period_type VARCHAR2,
494 x_period_year NUMBER,
495 x_period_num NUMBER,
496 x_quarter_num NUMBER,
497 x_adj_period_flag VARCHAR2,
498 x_last_updated_by NUMBER,
499 x_last_update_login NUMBER) IS
500 BEGIN
501
502 UPDATE GL_PERIOD_STATUSES ps
503 SET ps.period_name = x_period_name,
504 ps.start_date = x_start_date,
505 ps.end_date = x_end_date,
506 ps.period_type = x_period_type,
507 ps.period_year = x_period_year,
508 ps.quarter_num = x_quarter_num,
509 ps.period_num = x_period_num,
510 ps.adjustment_period_flag = x_adj_period_flag,
511 ps.last_update_date = sysdate,
512 ps.last_updated_by = x_last_updated_by,
513 ps.last_update_login = x_last_update_login,
514 ps.effective_period_num = x_period_year * 10000 + x_period_num
515 WHERE ps.period_name = x_old_period_name
516 AND ps.ledger_id IN
517 ( SELECT led.ledger_id
518 FROM GL_LEDGERS led
519 WHERE led.period_set_name = x_calendar_name );
520
521 exception
522 WHEN NO_DATA_FOUND THEN
523 RETURN;
524 WHEN app_exceptions.application_exception THEN
525 RAISE;
526 WHEN OTHERS THEN
527 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
528 fnd_message.set_token('PROCEDURE',
529 'gl_period_statuses_pkg.update_period');
530 RAISE;
531 END update_period;
532
533
534 PROCEDURE delete_period(
535 x_calendar_name VARCHAR2,
536 x_old_period_name VARCHAR2) IS
537 BEGIN
538 DELETE gl_period_statuses ps
539 WHERE ps.period_name = x_old_period_name
540 AND ps.ledger_id in
541 (SELECT led.ledger_id
542 FROM gl_ledgers led
543 WHERE led.period_set_name = x_calendar_name);
544
545 exception
546 WHEN NO_DATA_FOUND THEN
547 RETURN;
548 WHEN app_exceptions.application_exception THEN
549 RAISE;
550 WHEN OTHERS THEN
551 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
552 fnd_message.set_token('PROCEDURE',
553 'gl_period_statuses_pkg.delete_period');
554 RAISE;
555 END delete_period;
556
557
558 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_period_statuses%ROWTYPE ) IS
559 BEGIN
560 SELECT *
561 INTO recinfo
562 FROM gl_period_statuses
563 WHERE application_id = recinfo.application_id
564 AND ledger_id = recinfo.ledger_id
565 AND period_name = recinfo.period_name ;
566 EXCEPTION
567 WHEN NO_DATA_FOUND THEN
568 RETURN;
569 WHEN app_exceptions.application_exception THEN
570 RAISE;
571 WHEN OTHERS THEN
572 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
573 fnd_message.set_token('PROCEDURE',
574 'gl_period_statuses_pkg.select_row');
575 RAISE;
576 END select_row;
577
578
579 PROCEDURE select_columns(
580 x_application_id NUMBER,
581 x_ledger_id NUMBER,
582 x_period_name VARCHAR2,
583 x_closing_status IN OUT NOCOPY VARCHAR2,
584 x_start_date IN OUT NOCOPY DATE,
585 x_end_date IN OUT NOCOPY DATE,
586 x_period_num IN OUT NOCOPY NUMBER,
587 x_period_year IN OUT NOCOPY NUMBER ) IS
588 recinfo gl_period_statuses%ROWTYPE;
589 BEGIN
590 recinfo.application_id := x_application_id;
591 recinfo.ledger_id := x_ledger_id;
592 recinfo.period_name := x_period_name;
593 select_row( recinfo );
594 x_closing_status := recinfo.closing_status;
595 x_start_date := recinfo.start_date;
596 x_end_date := recinfo.end_date;
597 x_period_num := recinfo.period_num;
598 x_period_year := recinfo.period_year;
599 EXCEPTION
600 WHEN NO_DATA_FOUND THEN
601 RETURN;
602 WHEN app_exceptions.application_exception THEN
603 RAISE;
604 WHEN OTHERS THEN
605 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
606 fnd_message.set_token('PROCEDURE',
607 'gl_period_statuses_pkg.select_columns');
608 RAISE;
609 END select_columns;
610
611
612 PROCEDURE initialize_period_statuses(
613 x_application_id NUMBER,
614 x_ledger_id NUMBER,
615 x_period_year NUMBER,
616 x_period_num NUMBER,
617 x_user_id NUMBER ) IS
618
619 v_fut_ent_periods_limit NUMBER(15);
620 v_num_periods_updated NUMBER(15);
621 v_period_type VARCHAR2(15);
622
623 BEGIN
624 select led.future_enterable_periods_limit,
625 led.accounted_period_type
626 into v_fut_ent_periods_limit,
627 v_period_type
628 from gl_ledgers led
629 where led.ledger_id = x_ledger_id;
630
631 update gl_period_statuses ps
632 set ps.closing_status =
633 decode(ps.period_year, x_period_year,
634 decode(ps.period_num, x_period_num,'O',
635 'F'),
636 'F'),
637 ps.last_update_date = sysdate,
638 ps.last_updated_by = x_user_id
639 where ps.ledger_id = x_ledger_id
640 and ps.application_id = x_application_id
641 and ps.period_name in
642 (select period_name
643 from gl_period_statuses ps1,
644 gl_period_types pt
645 where v_period_type = pt.period_type
646 and ps1.application_id = x_application_id
647 and ps1.ledger_id = x_ledger_id
648 and ps1.period_type = pt.period_type
649 and ((ps1.period_year * pt.number_per_fiscal_year +
650 ps1.period_num) >=
651 (x_period_year * pt.number_per_fiscal_year +
652 x_period_num)
653 and (ps1.period_year * pt.number_per_fiscal_year +
654 ps1.period_num) <=
655 (x_period_year * pt.number_per_fiscal_year +
656 x_period_num +
657 v_fut_ent_periods_limit))) ;
658
659 -- Count the number of periods updated
660 v_num_periods_updated := SQL%ROWCOUNT;
661
662 -- If some future enterable periods are not yet defined,
663 -- raise an error
664 IF (v_num_periods_updated <> (v_fut_ent_periods_limit + 1)) THEN
665 fnd_message.set_name('SQLGL', 'GL_MISSING_FUT_ENT_PERIODS');
666 RAISE NO_DATA_FOUND;
667 END IF;
668
669 EXCEPTION
670 WHEN app_exceptions.application_exception THEN
671 RAISE;
672 WHEN OTHERS THEN
673 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
674 fnd_message.set_token('PROCEDURE',
675 'gl_period_statuses_pkg.initialize_period_statuses');
676 RAISE;
677 END initialize_period_statuses;
678
679 PROCEDURE select_encumbrance_periods(
680 x_application_id NUMBER,
681 x_ledger_id NUMBER,
682 x_first_period IN OUT NOCOPY VARCHAR2,
683 x_first_period_start_date IN OUT NOCOPY DATE,
684 x_second_period IN OUT NOCOPY VARCHAR2,
685 x_second_period_year IN OUT NOCOPY NUMBER,
686 x_second_period_start_date IN OUT NOCOPY DATE) IS
687
688 CURSOR c_period IS
689 SELECT PS1.period_name,
690 PS1.start_date,
691 PS2.period_name,
692 PS2.period_year,
693 PS2.start_date
694 FROM GL_LEDGERS LED,
695 GL_PERIOD_STATUSES PS1,
696 GL_PERIOD_STATUSES PS2,
697 GL_PERIOD_TYPES GPT
698 WHERE PS1.application_id = x_application_id
699 AND PS1.closing_status || '' in ('C', 'P')
700 AND PS1.ledger_id = x_ledger_id
701 AND PS1.period_type = GPT.period_type
702 AND PS1.period_year * GPT.number_per_fiscal_year +
703 PS1.period_num + 1
704 = PS2.period_year * GPT.number_per_fiscal_year + PS2.period_num
705 AND PS2.application_id = x_application_id
706 AND PS2.ledger_id = x_ledger_id
707 AND PS2.period_num = 1
708 AND LED.ledger_id = x_ledger_id
709 AND PS2.period_year <= LED.latest_encumbrance_year;
710
711 BEGIN
712 OPEN c_period;
713 FETCH c_period INTO x_first_period,
714 x_first_period_start_date,
715 x_second_period,
716 x_second_period_year,
717 x_second_period_start_date;
718 CLOSE c_period;
719
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 RETURN;
723 WHEN app_exceptions.application_exception THEN
724 RAISE;
725 WHEN OTHERS THEN
726 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
727 fnd_message.set_token('PROCEDURE',
728 'gl_period_statuses_pkg.select_encumbrance_periods');
729 RAISE;
730
731 END select_encumbrance_periods;
732
733
734 PROCEDURE select_prior_year_1st_period(
735 x_application_id NUMBER,
736 x_ledger_id NUMBER,
737 x_period_year NUMBER,
738 x_period_name IN OUT NOCOPY VARCHAR2) IS
739
740 CURSOR c_period IS
741 SELECT period_name
742 FROM GL_PERIOD_STATUSES
743 WHERE application_id = x_application_id
744 AND ledger_id = x_ledger_id
745 AND period_year = x_period_year - 1
746 AND period_num = (SELECT min(period_num)
747 FROM GL_PERIOD_STATUSES
748 WHERE application_id = x_application_id
749 AND ledger_id = x_ledger_id
750 AND period_year = x_period_year - 1);
751
752 BEGIN
753 OPEN c_period;
754 FETCH c_period INTO x_period_name;
755 CLOSE c_period;
756
757 EXCEPTION
758 WHEN NO_DATA_FOUND THEN
759 RETURN;
760 WHEN app_exceptions.application_exception THEN
761 RAISE;
762 WHEN OTHERS THEN
763 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
764 fnd_message.set_token('PROCEDURE',
765 'gl_period_statuses_pkg.select_prior_year_1st_period');
766 RAISE;
767 END select_prior_year_1st_period;
768
769
770
771 PROCEDURE select_year_1st_period(
772 x_application_id NUMBER,
773 x_ledger_id NUMBER,
774 x_period_year NUMBER,
775 x_period_name IN OUT NOCOPY VARCHAR2) IS
776
777 CURSOR c_period IS
778 select
779 period_name
780 from
781 gl_period_statuses s1
782 where
783 s1.application_id = x_application_id
784 and s1.ledger_id = x_ledger_id
785 and s1.period_year = x_period_year
786 and s1.period_num = (select min(period_num)
787 from gl_period_statuses s2
788 where s2.period_year = x_period_year
789 and s2.application_id = x_application_id
790 and s2.ledger_id = x_ledger_id);
791
792 BEGIN
793 OPEN c_period;
794 FETCH c_period INTO x_period_name;
795 CLOSE c_period;
796
797 EXCEPTION
798 WHEN NO_DATA_FOUND THEN
799 RETURN;
800 WHEN app_exceptions.application_exception THEN
801 RAISE;
802 WHEN OTHERS THEN
803 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
804 fnd_message.set_token('PROCEDURE',
805 'gl_period_statuses_pkg.select_year_1st_period');
806 RAISE;
807 END select_year_1st_period;
808
809
810 PROCEDURE get_extended_quarter(
811 x_application_id NUMBER,
812 x_ledger_id NUMBER,
813 x_period_year NUMBER,
814 x_period_name VARCHAR2,
815 x_period_set_name VARCHAR2,
816 x_accounted_period_type VARCHAR2,
817 x_period_used_for_ext_actuals IN OUT NOCOPY VARCHAR2,
818 x_num_used_for_ext_actuals IN OUT NOCOPY NUMBER,
819 x_year_used_for_ext_actuals IN OUT NOCOPY NUMBER,
820 x_quarter_used_for_ext_actuals IN OUT NOCOPY NUMBER ) IS
821
822 CURSOR c_qtde IS
823 select
824 period_name, period_num, period_year, quarter_num
825 from
826 gl_period_statuses
827 where
828 application_id = x_application_id
829 and ledger_id = x_ledger_id
830 and period_year = x_period_year
831 and period_num = (select max(glps.period_num)
832 from gl_period_statuses glps
833 where glps.closing_status in ('O','C','P')
834 and glps.quarter_num = (
835 select quarter_num from gl_periods
836 where period_name = x_period_name
837 and period_set_name = x_period_set_name)
838 and glps.period_year = x_period_year
839 and glps.application_id = 101
840 and glps.ledger_id = x_ledger_id
841 and glps.period_type = x_accounted_period_type );
842 /* Removed the redundant join to the GL_PERIODS table
843 for perf.bug Fix 2925883*/
844
845
846 BEGIN
847 OPEN c_qtde;
848 FETCH c_qtde INTO x_period_used_for_ext_actuals,
849 x_num_used_for_ext_actuals,
850 x_year_used_for_ext_actuals,
851 x_quarter_used_for_ext_actuals;
852 CLOSE c_qtde;
853
854 EXCEPTION
855 WHEN NO_DATA_FOUND THEN
856 RETURN;
857 WHEN app_exceptions.application_exception THEN
858 RAISE;
859 WHEN OTHERS THEN
860 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
861 fnd_message.set_token('PROCEDURE',
862 'gl_period_statuses_pkg.get_extended_quarter');
863 RAISE;
864 END get_extended_quarter;
865
866
867 PROCEDURE get_extended_year(
868 x_application_id NUMBER,
869 x_ledger_id NUMBER,
870 x_period_year NUMBER,
871 x_accounted_period_type VARCHAR2,
872 x_period_used_for_ext_actuals IN OUT NOCOPY VARCHAR2,
873 x_num_used_for_ext_actuals IN OUT NOCOPY NUMBER,
874 x_year_used_for_ext_actuals IN OUT NOCOPY NUMBER,
875 x_quarter_used_for_ext_actuals IN OUT NOCOPY NUMBER ) IS
876
877 CURSOR c_ytde IS
878 select
879 period_name, period_num, period_year, quarter_num
880 from
881 gl_period_statuses
882 where
883 application_id = x_application_id
884 and ledger_id = x_ledger_id
885 and period_year = x_period_year
886 and period_num = (select max(period_num)
887 from gl_period_statuses
888 where period_type = x_accounted_period_type
889 and ledger_id = x_ledger_id
890 and period_year = x_period_year
891 and closing_status in ('O','C','P')
892 and application_id = x_application_id);
893
894 BEGIN
895 OPEN c_ytde;
896 FETCH c_ytde INTO x_period_used_for_ext_actuals,
897 x_num_used_for_ext_actuals,
898 x_year_used_for_ext_actuals,
899 x_quarter_used_for_ext_actuals;
900 CLOSE c_ytde;
901
902 EXCEPTION
903 WHEN NO_DATA_FOUND THEN
904 RETURN;
905 WHEN app_exceptions.application_exception THEN
906 RAISE;
907 WHEN OTHERS THEN
908 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
909 fnd_message.set_token('PROCEDURE',
910 'gl_period_statuses_pkg.get_extended_year');
911 RAISE;
912 END get_extended_year;
913
914
915
916 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
917 X_Application_Id NUMBER,
918 X_Ledger_Id NUMBER,
919 X_Period_Name VARCHAR2,
920 X_Last_Update_Date DATE,
921 X_Last_Updated_By NUMBER,
922 X_Closing_Status VARCHAR2,
923 X_Start_Date DATE,
924 X_End_Date DATE,
925 X_Period_Type VARCHAR2,
926 X_Period_Year NUMBER,
927 X_Period_Num NUMBER,
928 X_Quarter_Num NUMBER,
929 X_Adjustment_Period_Flag VARCHAR2,
930 X_Creation_Date DATE,
931 X_Created_By NUMBER,
932 X_Last_Update_Login NUMBER,
933 X_Attribute1 VARCHAR2,
934 X_Attribute2 VARCHAR2,
935 X_Attribute3 VARCHAR2,
936 X_Attribute4 VARCHAR2,
937 X_Attribute5 VARCHAR2,
938 X_Context VARCHAR2
939
940 ) IS
941 CURSOR C IS SELECT rowid FROM gl_period_statuses
942 WHERE application_id = X_Application_Id
943
944 AND ledger_id = X_Ledger_Id
945
946 AND period_name = X_Period_Name;
947
948
949
950 BEGIN
951
952
953 INSERT INTO gl_period_statuses(
954 application_id,
955 ledger_id,
956 set_of_books_id,
957 period_name,
958 last_update_date,
959 last_updated_by,
960 closing_status,
961 start_date,
962 end_date,
963 period_type,
964 period_year,
965 period_num,
966 quarter_num,
967 adjustment_period_flag,
968 creation_date,
969 created_by,
970 last_update_login,
971 attribute1,
972 attribute2,
973 attribute3,
974 attribute4,
975 attribute5,
976 context,
977 effective_period_num,
978 migration_status_code
979 ) VALUES (
980 X_Application_Id,
981 X_Ledger_Id,
982 X_Ledger_Id,
983 X_Period_Name,
984 X_Last_Update_Date,
985 X_Last_Updated_By,
986 X_Closing_Status,
987 X_Start_Date,
988 X_End_Date,
989 X_Period_Type,
990 X_Period_Year,
991 X_Period_Num,
992 X_Quarter_Num,
993 X_Adjustment_Period_Flag,
994 X_Creation_Date,
995 X_Created_By,
996 X_Last_Update_Login,
997 X_Attribute1,
998 X_Attribute2,
999 X_Attribute3,
1000 X_Attribute4,
1001 X_Attribute5,
1002 X_Context,
1003 X_period_year * 10000 + x_period_num,
1004 'N'
1005 );
1006
1007 OPEN C;
1008 FETCH C INTO X_Rowid;
1009 if (C%NOTFOUND) then
1010 CLOSE C;
1011 Raise NO_DATA_FOUND;
1012 end if;
1013 CLOSE C;
1014 END Insert_Row;
1015
1016
1017 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
1018 X_Application_Id NUMBER,
1019 X_Ledger_Id NUMBER,
1020 X_Period_Name VARCHAR2,
1021 X_Closing_Status VARCHAR2,
1022 X_Start_Date DATE,
1023 X_End_Date DATE,
1024 X_Period_Type VARCHAR2,
1025 X_Period_Year NUMBER,
1026 X_Period_Num NUMBER,
1027 X_Quarter_Num NUMBER,
1028 X_Adjustment_Period_Flag VARCHAR2,
1029 X_Attribute1 VARCHAR2,
1030 X_Attribute2 VARCHAR2,
1031 X_Attribute3 VARCHAR2,
1032 X_Attribute4 VARCHAR2,
1033 X_Attribute5 VARCHAR2,
1034 X_Context VARCHAR2
1035
1036 ) IS
1037 CURSOR C IS
1038 SELECT *
1039 FROM gl_period_statuses
1040 WHERE rowid = X_Rowid
1041 FOR UPDATE of Application_Id NOWAIT;
1042 Recinfo C%ROWTYPE;
1043 BEGIN
1044 OPEN C;
1045 FETCH C INTO Recinfo;
1046 if (C%NOTFOUND) then
1047 CLOSE C;
1048 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1049 APP_EXCEPTION.Raise_Exception;
1050 end if;
1051 CLOSE C;
1052 if (
1053 (Recinfo.application_id = X_Application_Id)
1054 AND (Recinfo.ledger_id = X_Ledger_Id)
1055 AND (Recinfo.period_name = X_Period_Name)
1056 AND (Recinfo.closing_status = X_Closing_Status)
1057 AND (Recinfo.start_date = X_Start_Date)
1058 AND (Recinfo.end_date = X_End_Date)
1059 AND (Recinfo.period_type = X_Period_Type)
1060 AND (Recinfo.period_year = X_Period_Year)
1061 AND (Recinfo.period_num = X_Period_Num)
1062 AND (Recinfo.quarter_num = X_Quarter_Num)
1063 AND (Recinfo.adjustment_period_flag = X_Adjustment_Period_Flag)
1064 AND ( (Recinfo.attribute1 = X_Attribute1)
1065 OR ( (Recinfo.attribute1 IS NULL)
1066 AND (X_Attribute1 IS NULL)))
1067 AND ( (Recinfo.attribute2 = X_Attribute2)
1068 OR ( (Recinfo.attribute2 IS NULL)
1069 AND (X_Attribute2 IS NULL)))
1070 AND ( (Recinfo.attribute3 = X_Attribute3)
1071 OR ( (Recinfo.attribute3 IS NULL)
1072 AND (X_Attribute3 IS NULL)))
1073 AND ( (Recinfo.attribute4 = X_Attribute4)
1074 OR ( (Recinfo.attribute4 IS NULL)
1075 AND (X_Attribute4 IS NULL)))
1076 AND ( (Recinfo.attribute5 = X_Attribute5)
1077 OR ( (Recinfo.attribute5 IS NULL)
1078 AND (X_Attribute5 IS NULL)))
1079 AND ( (Recinfo.context = X_Context)
1080 OR ( (Recinfo.context IS NULL)
1081 AND (X_Context IS NULL)))
1082
1083 ) then
1084 return;
1085 else
1086 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1087 APP_EXCEPTION.RAISE_EXCEPTION;
1088 end if;
1089 END Lock_Row;
1090
1091
1092 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1093 X_Application_Id NUMBER,
1094 X_Ledger_Id NUMBER,
1095 X_Period_Name VARCHAR2,
1096 X_Last_Update_Date DATE,
1097 X_Last_Updated_By NUMBER,
1098 X_Closing_Status VARCHAR2,
1099 X_Start_Date DATE,
1100 X_End_Date DATE,
1101 X_Period_Type VARCHAR2,
1102 X_Period_Year NUMBER,
1103 X_Period_Num NUMBER,
1104 X_Quarter_Num NUMBER,
1105 X_Adjustment_Period_Flag VARCHAR2,
1106 X_Last_Update_Login NUMBER,
1107 X_Attribute1 VARCHAR2,
1108 X_Attribute2 VARCHAR2,
1109 X_Attribute3 VARCHAR2,
1110 X_Attribute4 VARCHAR2,
1111 X_Attribute5 VARCHAR2,
1112 X_Context VARCHAR2
1113
1114 ) IS
1115 BEGIN
1116
1117 IF (X_Closing_Status IN ('C', 'P')) THEN
1118 UPDATE gl_period_statuses
1119 SET
1120 application_id = X_Application_Id,
1121 ledger_id = X_Ledger_Id,
1122 period_name = X_Period_Name,
1123 last_update_date = X_Last_Update_Date,
1124 last_updated_by = X_Last_Updated_By,
1125 closing_status = X_Closing_Status,
1126 start_date = X_Start_Date,
1127 end_date = X_End_Date,
1128 period_type = X_Period_Type,
1129 period_year = X_Period_Year,
1130 period_num = X_Period_Num,
1131 quarter_num = X_Quarter_Num,
1132 adjustment_period_flag = X_Adjustment_Period_Flag,
1133 elimination_confirmed_flag = 'Y',
1134 last_update_login = X_Last_Update_Login,
1135 attribute1 = X_Attribute1,
1136 attribute2 = X_Attribute2,
1137 attribute3 = X_Attribute3,
1138 attribute4 = X_Attribute4,
1139 attribute5 = X_Attribute5,
1140 context = X_Context,
1141 effective_period_num = X_Period_Year * 10000 + X_Period_Num
1142
1143 WHERE rowid = X_rowid;
1144 ELSE
1145 UPDATE gl_period_statuses
1146 SET
1147 application_id = X_Application_Id,
1148 ledger_id = X_Ledger_Id,
1149 period_name = X_Period_Name,
1150 last_update_date = X_Last_Update_Date,
1151 last_updated_by = X_Last_Updated_By,
1152 closing_status = X_Closing_Status,
1153 start_date = X_Start_Date,
1154 end_date = X_End_Date,
1155 period_type = X_Period_Type,
1156 period_year = X_Period_Year,
1157 period_num = X_Period_Num,
1158 quarter_num = X_Quarter_Num,
1159 adjustment_period_flag = X_Adjustment_Period_Flag,
1160 last_update_login = X_Last_Update_Login,
1161 attribute1 = X_Attribute1,
1162 attribute2 = X_Attribute2,
1163 attribute3 = X_Attribute3,
1164 attribute4 = X_Attribute4,
1165 attribute5 = X_Attribute5,
1166 context = X_Context,
1167 effective_period_num = X_Period_Year * 10000 + X_Period_Num
1168 WHERE rowid = X_rowid;
1169 END IF;
1170
1171
1172 if (SQL%NOTFOUND) then
1173 Raise NO_DATA_FOUND;
1174 end if;
1175
1176 END Update_Row;
1177
1178 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
1179 BEGIN
1180 DELETE FROM gl_period_statuses
1181 WHERE rowid = X_Rowid;
1182
1183 if (SQL%NOTFOUND) then
1184 Raise NO_DATA_FOUND;
1185 end if;
1186 END Delete_Row;
1187
1188 PROCEDURE update_row_dff(
1189 X_rowid VARCHAR2,
1190 X_attribute1 VARCHAR2,
1191 X_attribute2 VARCHAR2,
1192 X_attribute3 VARCHAR2,
1193 X_attribute4 VARCHAR2,
1194 X_attribute5 VARCHAR2,
1195 X_context VARCHAR2,
1196 X_Last_Update_Date DATE,
1197 X_Last_Updated_By NUMBER,
1198 X_Last_Update_Login NUMBER
1199 ) IS
1200 CURSOR C IS
1201 SELECT *
1202 FROM gl_period_statuses
1203 WHERE rowid = X_rowid
1204 FOR UPDATE of Application_Id NOWAIT;
1205 Recinfo C%ROWTYPE;
1206 BEGIN
1207 OPEN C;
1208 FETCH C INTO Recinfo;
1209 if (C%NOTFOUND) then
1210 CLOSE C;
1211 Raise NO_DATA_FOUND;
1212 else
1213 IF (( ( (Recinfo.attribute1 <> X_Attribute1)
1214 OR ( (Recinfo.attribute1 IS NOT NULL)
1215 AND (X_Attribute1 IS NULL))
1216 OR ( (Recinfo.attribute1 IS NULL)
1217 AND (X_Attribute1 IS NOT NULL))
1218 )
1219 OR ( (Recinfo.attribute2 <> X_Attribute2)
1220 OR ( (Recinfo.attribute2 IS NOT NULL)
1221 AND (X_Attribute2 IS NULL))
1222 OR ( (Recinfo.attribute2 IS NULL)
1223 AND (X_Attribute2 IS NOT NULL))
1224 )
1225 OR ( (Recinfo.attribute3 <> X_Attribute3)
1226 OR ( (Recinfo.attribute3 IS NOT NULL)
1227 AND (X_Attribute3 IS NULL))
1228 OR ( (Recinfo.attribute3 IS NULL)
1229 AND (X_Attribute3 IS NOT NULL))
1230 )
1231 OR ( (Recinfo.attribute4 <> X_Attribute4)
1232 OR ( (Recinfo.attribute4 IS NOT NULL)
1233 AND (X_Attribute4 IS NULL))
1234 OR ( (Recinfo.attribute4 IS NULL)
1235 AND (X_Attribute4 IS NOT NULL))
1236 )
1237 OR ( (Recinfo.attribute5 <> X_Attribute5)
1238 OR ( (Recinfo.attribute5 IS NOT NULL)
1239 AND (X_Attribute5 IS NULL))
1240 OR ( (Recinfo.attribute5 IS NULL)
1241 AND (X_Attribute5 IS NOT NULL))
1242 )
1243 OR ( (Recinfo.context <> X_Context)
1244 OR ( (Recinfo.context IS NOT NULL)
1245 AND (X_Context IS NULL))
1246 OR ( (Recinfo.context IS NULL)
1247 AND (X_Context IS NOT NULL))
1248 )
1249 )) THEN
1250 UPDATE gl_period_statuses
1251 SET
1252 Attribute1 = X_Attribute1,
1253 Attribute2 = X_Attribute2,
1254 Attribute3 = X_Attribute3,
1255 Attribute4 = X_Attribute4,
1256 Attribute5 = X_Attribute5,
1257 Context = X_Context,
1258 Last_Update_Date = X_Last_Update_Date,
1259 Last_Updated_By = X_Last_Updated_By,
1260 Last_Update_Login = X_Last_Update_Login
1261 WHERE rowid = X_rowid;
1262 END IF;
1263 END IF;
1264 END Update_Row_Dff;
1265
1266 PROCEDURE get_period_by_date(
1267 x_application_id NUMBER,
1268 x_ledger_id NUMBER,
1269 x_given_date DATE,
1270 x_period_name IN OUT NOCOPY VARCHAR2,
1271 x_closing_status IN OUT NOCOPY VARCHAR2,
1272 x_period_year IN OUT NOCOPY NUMBER,
1273 x_period_num IN OUT NOCOPY NUMBER,
1274 x_period_type IN OUT NOCOPY VARCHAR2) IS
1275 BEGIN
1276 SELECT ps.period_name, ps.closing_status,
1277 ps.period_year, ps.period_num, ps.period_type
1278 INTO x_period_name, x_closing_status, x_period_year,
1279 x_period_num, x_period_type
1280 FROM gl_period_statuses ps,
1281 gl_date_period_map dpm,
1282 gl_ledgers led
1283 WHERE led.ledger_id = x_ledger_id
1284 AND dpm.accounting_date = x_given_date
1285 AND dpm.period_set_name = led.period_set_name
1286 AND dpm.period_type = led.accounted_period_type
1287 AND ps.period_name = dpm.period_name
1288 AND ps.ledger_id = led.ledger_id
1289 AND ps.application_id = x_application_id
1290 AND ps.adjustment_period_flag = 'N';
1291
1292 EXCEPTION
1293 WHEN NO_DATA_FOUND THEN
1294 x_period_name := NULL;
1295 x_closing_status := NULL;
1296 RETURN;
1297 /*
1298 fnd_message.set_name('SQLGL', 'GL_IEA_NOT_IN_OPEN_FUTURE_PER');
1299 fnd_message.set_token('PROCEDURE',
1300 'gl_period_statuses_pkg.get_period_by_date');
1301 APP_EXCEPTION.Raise_Exception;
1302 */
1303 WHEN app_exceptions.application_exception THEN
1304 RAISE;
1305 WHEN OTHERS THEN
1306 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1307 fnd_message.set_token('PROCEDURE',
1308 'gl_period_statuses_pkg.get_period_by_date');
1309 RAISE;
1310 END get_period_by_date;
1311
1312
1313 PROCEDURE get_calendar_range(
1314 x_ledger_id NUMBER,
1315 x_start_date IN OUT NOCOPY DATE,
1316 x_end_date IN OUT NOCOPY DATE) IS
1317 CURSOR not_never_opened_period IS
1318 SELECT min(start_date), max(end_date)
1319 FROM gl_period_statuses
1320 WHERE application_id = 101
1321 AND ledger_id = x_ledger_id
1322 AND closing_status <> 'N';
1323
1324 BEGIN
1325 OPEN not_never_opened_period;
1326 FETCH not_never_opened_period INTO x_start_date, x_end_date;
1327 CLOSE not_never_opened_period;
1328
1329 EXCEPTION
1330 WHEN NO_DATA_FOUND THEN
1331 x_start_date := NULL;
1332 x_end_date := NULL;
1333 RETURN;
1334 WHEN app_exceptions.application_exception THEN
1335 RAISE;
1336 WHEN OTHERS THEN
1337 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1338 fnd_message.set_token('PROCEDURE',
1339 'gl_period_statuses_pkg.get_calendar_range');
1340 RAISE;
1341 END get_calendar_range;
1342
1343
1344 PROCEDURE get_open_closed_calendar_range(
1345 x_ledger_id NUMBER,
1346 x_start_date IN OUT NOCOPY DATE,
1347 x_end_date IN OUT NOCOPY DATE) IS
1348 CURSOR closed_opened_period IS
1349 SELECT min(start_date), max(end_date)
1350 FROM gl_period_statuses
1351 WHERE application_id = 101
1352 AND ledger_id = x_ledger_id
1353 AND closing_status in ('C', 'O', 'P');
1354
1355 BEGIN
1356 OPEN closed_opened_period;
1357 FETCH closed_opened_period INTO x_start_date, x_end_date;
1358 CLOSE closed_opened_period;
1359
1360 EXCEPTION
1361 WHEN NO_DATA_FOUND THEN
1362 x_start_date := NULL;
1363 x_end_date := NULL;
1364 RETURN;
1365 WHEN app_exceptions.application_exception THEN
1366 RAISE;
1367 WHEN OTHERS THEN
1368 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1369 fnd_message.set_token('PROCEDURE',
1370 'gl_period_statuses_pkg.get_open_closed_calendar_range');
1371 RAISE;
1372 END get_open_closed_calendar_range;
1373
1374
1375 PROCEDURE get_journal_range(
1376 x_ledger_id NUMBER,
1377 x_start_date IN OUT NOCOPY DATE,
1378 x_end_date IN OUT NOCOPY DATE) IS
1379 CURSOR journal_period IS
1380 SELECT min(start_date), max(end_date)
1381 FROM gl_period_statuses
1382 WHERE application_id = 101
1383 AND ledger_id = x_ledger_id
1384 AND closing_status||'' IN ('O', 'F');
1385
1386 BEGIN
1387 OPEN journal_period;
1388 FETCH journal_period INTO x_start_date, x_end_date;
1389 CLOSE journal_period;
1390
1391 EXCEPTION
1392 WHEN NO_DATA_FOUND THEN
1393 x_start_date := NULL;
1394 x_end_date := NULL;
1395 RETURN;
1396 WHEN app_exceptions.application_exception THEN
1397 RAISE;
1398 WHEN OTHERS THEN
1399 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1400 fnd_message.set_token('PROCEDURE',
1401 'gl_period_statuses_pkg.get_journal_range');
1402 RAISE;
1403 END get_journal_range;
1404
1405 END gl_period_statuses_pkg;