1 PACKAGE BODY GL_DATE_HANDLER_PKG as
2 /* $Header: glustdtb.pls 120.5 2005/05/05 01:44:30 kvora ship $ */
3
4 ---
5 --- PUBLIC FUNCTIONS
6 ---
7
8 PROCEDURE find_active_period( lgr_id NUMBER,
9 calendar VARCHAR2,
10 per_type VARCHAR2,
11 active_date DATE,
12 active_period IN OUT NOCOPY VARCHAR2,
13 per_start_date IN OUT NOCOPY DATE,
14 per_end_date IN OUT NOCOPY DATE,
15 per_number IN OUT NOCOPY NUMBER,
16 per_year IN OUT NOCOPY NUMBER
17 ) IS
18
19 x_active_period VARCHAR2(15);
20 x_per_start_date DATE;
21 x_per_end_date DATE;
22 x_per_number NUMBER;
23 x_per_year NUMBER;
24 period_status VARCHAR2(1);
25
26 CURSOR find_period IS
27 SELECT ps.period_name, ps.start_date, ps.end_date, ps.period_num,
28 ps.period_year
29 FROM gl_period_statuses ps
30 WHERE ps.application_id = 101
31 AND ps.ledger_id = lgr_id
32 AND ps.start_date <= trunc(active_date)
33 AND ps.end_date >= trunc(active_date)
34 AND ps.closing_status IN ('O', 'F')
35 ORDER BY ps.effective_period_num ASC;
36
37 BEGIN
38 -- First, see if the nonadjusting period that contains this
39 -- date is open
40 SELECT ps.period_name, ps.closing_status, ps.start_date, ps.end_date,
41 ps.period_num, ps.period_year
42 INTO x_active_period, period_status, x_per_start_date, x_per_end_date,
43 x_per_number, x_per_year
44 FROM gl_date_period_map map, gl_period_statuses ps
45 WHERE map.period_set_name = calendar
46 AND map.period_type = per_type
47 AND map.accounting_date = trunc(active_date)
48 AND ps.application_id = 101
49 AND ps.ledger_id = lgr_id
50 AND ps.period_name = map.period_name;
51
52 IF (period_status NOT IN ('O', 'F')) THEN
53 RAISE NO_DATA_FOUND;
54 END IF;
55
56 active_period := x_active_period;
57 per_start_date := x_per_start_date;
58 per_end_date := x_per_end_date;
59 per_number := x_per_number;
60 per_year := x_per_year;
61
62 RETURN;
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 -- Search for an adjusting period that is open or future enterable
66 OPEN find_period;
67 FETCH find_period INTO x_active_period, x_per_start_date, x_per_end_date,
68 x_per_number, x_per_year;
69
70 IF (find_period%NOTFOUND) THEN
71 CLOSE find_period;
72 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
73 app_exception.raise_exception;
74 END IF;
75
76 CLOSE find_period;
77
78 active_period := x_active_period;
79 per_start_date := x_per_start_date;
80 per_end_date := x_per_end_date;
81 per_number := x_per_number;
82 per_year := x_per_year;
83 END find_active_period;
84
85 PROCEDURE find_enc_period( lgr_id NUMBER,
86 calendar VARCHAR2,
87 per_type VARCHAR2,
88 active_date DATE,
89 active_period IN OUT NOCOPY VARCHAR2,
90 per_start_date IN OUT NOCOPY DATE,
91 per_end_date IN OUT NOCOPY DATE,
92 per_number IN OUT NOCOPY NUMBER,
93 per_year IN OUT NOCOPY NUMBER
94 ) IS
95
96 x_active_period VARCHAR2(15);
97 x_per_start_date DATE;
98 x_per_end_date DATE;
99 x_per_number NUMBER;
100 x_per_year NUMBER;
101 x_latest_year NUMBER;
102 period_status VARCHAR2(1);
103
104 CURSOR find_period IS
105 SELECT ps.period_name, ps.start_date, ps.end_date, ps.period_num,
106 ps.period_year
107 FROM gl_period_statuses ps
108 WHERE ps.application_id = 101
109 AND ps.ledger_id = lgr_id
110 AND ps.start_date <= trunc(active_date)
111 AND ps.end_date >= trunc(active_date)
112 AND ps.period_year <= x_latest_year
113 ORDER BY ps.effective_period_num ASC;
114
115 BEGIN
116 SELECT latest_encumbrance_year
117 INTO x_latest_year
118 FROM gl_ledgers
119 WHERE ledger_id = lgr_id;
120
121 -- First, see if the non-adjusting period that contains this
122 -- date is within
123 SELECT ps.period_name, ps.start_date, ps.end_date,
124 ps.period_num, ps.period_year
125 INTO x_active_period, x_per_start_date, x_per_end_date,
126 x_per_number, x_per_year
127 FROM gl_date_period_map map, gl_period_statuses ps
128 WHERE map.period_set_name = calendar
129 AND map.period_type = per_type
130 AND map.accounting_date = trunc(active_date)
131 AND ps.application_id = 101
132 AND ps.ledger_id = lgr_id
133 AND ps.period_name = map.period_name;
134
135 IF (x_per_year > x_latest_year) THEN
136 RAISE NO_DATA_FOUND;
137 END IF;
138
139 active_period := x_active_period;
140 per_start_date := x_per_start_date;
141 per_end_date := x_per_end_date;
142 per_number := x_per_number;
143 per_year := x_per_year;
144
145 RETURN;
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 -- Search for an adjusting period that is open or future enterable
149 OPEN find_period;
150 FETCH find_period INTO x_active_period, x_per_start_date, x_per_end_date,
151 x_per_number, x_per_year;
152
153 IF (find_period%NOTFOUND) THEN
154 CLOSE find_period;
155 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_ENC_YEAR');
156 app_exception.raise_exception;
157 END IF;
158
159 CLOSE find_period;
160
161 active_period := x_active_period;
162 per_start_date := x_per_start_date;
163 per_end_date := x_per_end_date;
164 per_number := x_per_number;
165 per_year := x_per_year;
166 END find_enc_period;
167
168 PROCEDURE find_enc_period_batch(
169 batch_id NUMBER,
170 calendar VARCHAR2,
171 per_type VARCHAR2,
172 active_date DATE,
173 active_period IN OUT NOCOPY VARCHAR2,
174 per_start_date IN OUT NOCOPY DATE,
175 per_end_date IN OUT NOCOPY DATE,
176 per_number IN OUT NOCOPY NUMBER,
177 per_year IN OUT NOCOPY NUMBER
178 ) IS
179
180 x_active_period VARCHAR2(15);
181 x_per_start_date DATE;
182 x_per_end_date DATE;
183 x_per_number NUMBER;
184 x_per_year NUMBER;
185 period_status VARCHAR2(1);
186
187 one_ledger_id NUMBER;
188 one_latest_year NUMBER;
189
190 CURSOR find_period IS
191 SELECT ps.period_name, ps.start_date, ps.end_date, ps.period_num,
192 ps.period_year
193 FROM gl_period_statuses ps
194 WHERE ps.application_id = 101
195 AND ps.ledger_id = one_ledger_id
196 AND ps.start_date <= trunc(active_date)
197 AND ps.end_date >= trunc(active_date)
198 AND ps.period_year <= one_latest_year
199 AND NOT EXISTS
200 (SELECT 'not open or future'
201 FROM gl_je_headers jeh, gl_ledgers lgr
202 WHERE jeh.je_batch_id = batch_id
203 AND lgr.ledger_id = jeh.ledger_id
204 AND nvl(lgr.latest_encumbrance_year,-1) < ps.period_year)
205 ORDER BY ps.effective_period_num ASC;
206
207 BEGIN
208 SELECT lgr.ledger_id, lgr.latest_encumbrance_year
209 INTO one_ledger_id, one_latest_year
210 FROM gl_je_headers jeh, gl_ledgers lgr
211 WHERE jeh.je_batch_id = batch_id
212 AND lgr.ledger_id = jeh.ledger_id
213 AND rownum = 1;
214
215 -- First, see if the non-adjusting period that contains this
216 -- date has a valid year
217 SELECT ps.period_name, ps.start_date, ps.end_date,
218 ps.period_num, ps.period_year
219 INTO x_active_period, x_per_start_date, x_per_end_date,
220 x_per_number, x_per_year
221 FROM gl_date_period_map map, gl_period_statuses ps
222 WHERE map.period_set_name = calendar
223 AND map.period_type = per_type
224 AND map.accounting_date = trunc(active_date)
225 AND ps.application_id = 101
226 AND ps.ledger_id = one_ledger_id
227 AND ps.period_name = map.period_name
228 AND ps.period_year <= one_latest_year
229 AND NOT EXISTS
230 (SELECT 'not open or future'
231 FROM gl_je_headers jeh, gl_ledgers lgr
232 WHERE jeh.je_batch_id = batch_id
233 AND lgr.ledger_id = jeh.ledger_id
234 AND nvl(lgr.latest_encumbrance_year,-1) < ps.period_year);
235
236 IF (x_per_year > one_latest_year) THEN
237 RAISE NO_DATA_FOUND;
238 END IF;
239
240 active_period := x_active_period;
241 per_start_date := x_per_start_date;
242 per_end_date := x_per_end_date;
243 per_number := x_per_number;
244 per_year := x_per_year;
245
246 RETURN;
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 -- Search for an adjusting period that is open or future enterable
250 OPEN find_period;
251 FETCH find_period INTO x_active_period, x_per_start_date, x_per_end_date,
252 x_per_number, x_per_year;
253
254 IF (find_period%NOTFOUND) THEN
255 CLOSE find_period;
256 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_ENC_YEAR');
257 app_exception.raise_exception;
258 END IF;
259
260 CLOSE find_period;
261
262 active_period := x_active_period;
263 per_start_date := x_per_start_date;
264 per_end_date := x_per_end_date;
265 per_number := x_per_number;
266 per_year := x_per_year;
267 END find_enc_period_batch;
268
269 PROCEDURE validate_date(lgr_id NUMBER,
270 roll_date VARCHAR2,
271 initial_accounting_date DATE,
272 minimum_date DATE,
273 minimum_period VARCHAR2,
274 period_name IN OUT NOCOPY VARCHAR2,
275 start_date IN OUT NOCOPY DATE,
276 end_date IN OUT NOCOPY DATE,
277 period_num IN OUT NOCOPY NUMBER,
278 period_year IN OUT NOCOPY NUMBER,
279 rolled_accounting_date IN OUT NOCOPY DATE) IS
280
281 got_period BOOLEAN;
282
283 acct_cal_name VARCHAR2(15);
284 trans_cal_id NUMBER;
285 business_day VARCHAR2(1);
286 period_status VARCHAR2(1);
287 new_accounting_date DATE;
288 acc_period_type VARCHAR2(15);
289
290 period_start_date DATE;
291 period_end_date DATE;
292 tmp_num NUMBER;
293 tmp_year NUMBER;
294
295 x_period_name VARCHAR2(15);
296 min_eff_period_num NUMBER := 0;
297
298 CURSOR find_period IS
299 SELECT ps.period_name, ps.start_date, ps.end_date, ps.period_year,
300 ps.period_num
301 FROM gl_period_statuses ps
302 WHERE ps.application_id = 101
303 AND ps.ledger_id = lgr_id
304 AND ps.start_date <= trunc(initial_accounting_date)
305 AND ps.end_date >= trunc(initial_accounting_date)
306 AND ps.closing_status IN ('O', 'F')
307 AND ps.effective_period_num >= min_eff_period_num
308 ORDER BY ps.effective_period_num ASC;
309 BEGIN
310
311 -- Get the ledger information
312 SELECT period_set_name, transaction_calendar_id, accounted_period_type
313 INTO acct_cal_name, trans_cal_id, acc_period_type
314 FROM gl_ledgers
315 WHERE ledger_id = lgr_id;
316
317 -- Determine the status of the period
318 BEGIN
319 IF (period_name IS NULL) THEN
320
321 IF (minimum_period IS NOT NULL) THEN
322 SELECT effective_period_num
323 INTO min_eff_period_num
324 FROM gl_period_statuses
325 WHERE application_id = 101
326 AND ledger_id = lgr_id
327 AND period_name = minimum_period;
328 END IF;
329
330 SELECT ps.period_name, ps.closing_status, ps.start_date, ps.end_date,
331 ps.period_year, ps.period_num
332 INTO x_period_name, period_status, period_start_date,
333 period_end_date, tmp_year, tmp_num
334 FROM gl_date_period_map map, gl_period_statuses ps
335 WHERE map.period_set_name = acct_cal_name
336 AND map.period_type = acc_period_type
337 AND map.accounting_date = initial_accounting_date
338 AND ps.application_id = 101
339 AND ps.ledger_id = lgr_id
340 AND ps.period_name = map.period_name
341 AND ps.effective_period_num >= min_eff_period_num;
342
343 IF (period_status NOT IN ('O', 'F')) THEN
344 RAISE NO_DATA_FOUND;
345 ELSE
346 period_name := x_period_name;
347 start_date := period_start_date;
351 END IF;
348 end_date := period_end_date;
349 period_year := tmp_year;
350 period_num := tmp_num;
352 ELSE
353 x_period_name := period_name;
354 SELECT ps.closing_status, ps.start_date, ps.end_date,
355 ps.period_year, ps.period_num
356 INTO period_status, period_start_date, period_end_date,
357 tmp_year, tmp_num
358 FROM gl_period_statuses ps
359 WHERE ps.application_id = 101
360 AND ps.ledger_id = lgr_id
361 AND ps.period_name = x_period_name
362 AND ps.effective_period_num >= min_eff_period_num;
363
364 start_date := period_start_date;
365 end_date := period_end_date;
366 period_year := tmp_year;
367 period_num := tmp_num;
368
369 IF (period_status NOT IN ('O', 'F')) THEN
370 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
371 app_exception.raise_exception;
372 END IF;
373 END IF;
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 -- Search for an adjusting period that is open or future enterable
377 OPEN find_period;
378 FETCH find_period INTO x_period_name, period_start_date,
379 period_end_date, tmp_year, tmp_num;
380 IF find_period%FOUND THEN
381 CLOSE find_period;
382 ELSE
383 CLOSE find_period;
384 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
385 app_exception.raise_exception;
386 END IF;
387
388 period_name := x_period_name;
389 start_date := period_start_date;
390 end_date := period_end_date;
391 period_year := tmp_year;
392 period_num := tmp_num;
393 END;
394
395 -- Determine if the day is a business day
396 BEGIN
397 SELECT business_day_flag
398 INTO business_day
399 FROM gl_transaction_dates
400 WHERE transaction_calendar_id = trans_cal_id
401 AND transaction_date = initial_accounting_date;
402
403 IF (business_day = 'Y') THEN
404 rolled_accounting_date := initial_accounting_date;
405 RETURN;
406 ELSIF (roll_date <> 'Y') THEN
407 fnd_message.set_name('SQLGL', 'GL_JE_NOT_BUSINESS_DAY');
408 app_exception.raise_exception;
409 END IF;
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
413 app_exception.raise_exception;
414 END;
415
416 -- Roll the date back to a business day
417 BEGIN
418 SELECT max(transaction_date)
419 INTO new_accounting_date
420 FROM gl_transaction_dates trans
421 WHERE trans.transaction_calendar_id = trans_cal_id
422 AND trans.transaction_date >= greatest(period_start_date,
423 nvl(minimum_date,
424 period_start_date))
425 AND trans.business_day_flag = 'Y'
426 AND trans.transaction_date < initial_accounting_date;
427
428 IF (new_accounting_date IS NOT NULL) THEN
429 rolled_accounting_date := new_accounting_date;
430 RETURN;
431 END IF;
432 EXCEPTION
433 WHEN NO_DATA_FOUND THEN
434 null;
435 END;
436
437 -- Roll the date forward to a business day
438 BEGIN
439 SELECT min(transaction_date)
440 INTO new_accounting_date
441 FROM gl_transaction_dates trans
442 WHERE trans.transaction_calendar_id = trans_cal_id
443 AND trans.transaction_date <= period_end_date
444 AND trans.business_day_flag = 'Y'
445 AND trans.transaction_date > initial_accounting_date;
446
447 IF (new_accounting_date IS NOT NULL) THEN
448 rolled_accounting_date := new_accounting_date;
449 RETURN;
450 END IF;
451 EXCEPTION
452 WHEN NO_DATA_FOUND THEN
453 rolled_accounting_date := null;
454 END;
455
456 END validate_date;
457
458
459 PROCEDURE validate_date_batch(
460 batch_id NUMBER,
461 roll_date VARCHAR2,
462 initial_accounting_date DATE,
463 minimum_date DATE,
464 minimum_period VARCHAR2,
465 period_name IN OUT NOCOPY VARCHAR2,
466 start_date IN OUT NOCOPY DATE,
467 end_date IN OUT NOCOPY DATE,
468 period_num IN OUT NOCOPY NUMBER,
469 period_year IN OUT NOCOPY NUMBER,
470 rolled_accounting_date IN OUT NOCOPY DATE) IS
471
472 got_period BOOLEAN;
473
474 acct_cal_name VARCHAR2(15);
475 business_day VARCHAR2(1);
476 period_status VARCHAR2(1);
477 new_accounting_date DATE;
478 acc_period_type VARCHAR2(15);
479 roll_dates VARCHAR2(1);
480
481 period_start_date DATE;
482 period_end_date DATE;
483 tmp_num NUMBER;
484 tmp_year NUMBER;
485
486 one_ledger_id NUMBER;
487 one_trans_cal_id NUMBER;
488
489 x_period_name VARCHAR2(15);
490 min_eff_period_num NUMBER := 0;
491
492 CURSOR find_period IS
493 SELECT ps.period_name, ps.start_date, ps.end_date, ps.period_year,
494 ps.period_num
495 FROM gl_period_statuses ps
496 WHERE ps.application_id = 101
497 AND ps.ledger_id = one_ledger_id
498 AND ps.start_date <= trunc(initial_accounting_date)
502 AND NOT EXISTS
499 AND ps.end_date >= trunc(initial_accounting_date)
500 AND ps.effective_period_num >= min_eff_period_num
501 AND ps.closing_status IN ('O', 'F')
503 (SELECT 'not open or future'
504 FROM gl_je_headers jeh, gl_period_statuses ps2
505 WHERE jeh.je_batch_id = batch_id
506 AND ps2.application_id = 101
507 AND ps2.ledger_id = jeh.ledger_id
508 AND ps2.period_name = ps.period_name
509 AND ps2.closing_status NOT IN ('O', 'F'))
510 ORDER BY ps.effective_period_num ASC;
511 BEGIN
512
513 -- Get the ledger information
514 SELECT min(lgr.period_set_name), min(lgr.accounted_period_type),
515 min(lgr.transaction_calendar_id), min(lgr.ledger_id)
516 INTO acct_cal_name, acc_period_type, one_trans_cal_id, one_ledger_id
517 FROM gl_je_headers jeh, gl_ledgers lgr
518 WHERE jeh.je_batch_id = batch_id
519 AND lgr.ledger_id = jeh.ledger_id;
520
521 -- Get information for one ledger and transaction calendar
522 -- Determine the status of the period
523 BEGIN
524 IF (period_name IS NULL) THEN
525
526 IF (minimum_period IS NOT NULL) THEN
527 SELECT period_year * 10000 + period_num
528 INTO min_eff_period_num
529 FROM gl_periods
530 WHERE period_set_name = acct_cal_name
531 AND period_type = acc_period_type
532 AND period_name = minimum_period;
533 END IF;
534
535 SELECT ps.period_name, ps.closing_status, ps.start_date, ps.end_date,
536 ps.period_year, ps.period_num
537 INTO x_period_name, period_status, period_start_date,
538 period_end_date, tmp_year, tmp_num
539 FROM gl_date_period_map map, gl_period_statuses ps
540 WHERE map.period_set_name = acct_cal_name
541 AND map.period_type = acc_period_type
542 AND map.accounting_date = initial_accounting_date
543 AND ps.application_id = 101
544 AND ps.ledger_id = one_ledger_id
545 AND ps.period_name = map.period_name
546 AND ps.effective_period_num >= min_eff_period_num
547 AND ps.closing_status IN ('O', 'F')
548 AND NOT EXISTS
549 (SELECT 'not open or future'
550 FROM gl_je_headers jeh, gl_period_statuses ps2
551 WHERE jeh.je_batch_id = batch_id
552 AND ps2.application_id = 101
553 AND ps2.ledger_id = jeh.ledger_id
554 AND ps2.period_name = ps.period_name
555 AND ps2.closing_status NOT IN ('O', 'F'));
556
557 IF (period_status NOT IN ('O', 'F')) THEN
558 RAISE NO_DATA_FOUND;
559 ELSE
560 period_name := x_period_name;
561 start_date := period_start_date;
562 end_date := period_end_date;
563 period_year := tmp_year;
564 period_num := tmp_num;
565 END IF;
566 ELSE
567 x_period_name := period_name;
568 SELECT ps.closing_status, ps.start_date, ps.end_date,
569 ps.period_year, ps.period_num
570 INTO period_status, period_start_date, period_end_date,
571 tmp_year, tmp_num
572 FROM gl_period_statuses ps
573 WHERE ps.application_id = 101
574 AND ps.ledger_id = one_ledger_id
575 AND ps.period_name = x_period_name
576 AND ps.effective_period_num >= min_eff_period_num
577 AND ps.closing_status IN ('O', 'F')
578 AND NOT EXISTS
579 (SELECT 'not open or future'
580 FROM gl_je_headers jeh, gl_period_statuses ps2
581 WHERE jeh.je_batch_id = batch_id
582 AND ps2.application_id = 101
583 AND ps2.ledger_id = jeh.ledger_id
584 AND ps2.period_name = ps.period_name
585 AND ps2.closing_status NOT IN ('O', 'F'));
586
587 start_date := period_start_date;
588 end_date := period_end_date;
589 period_year := tmp_year;
590 period_num := tmp_num;
591
592 IF (period_status NOT IN ('O', 'F')) THEN
593 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
594 app_exception.raise_exception;
595 END IF;
596 END IF;
597 EXCEPTION
598 WHEN NO_DATA_FOUND THEN
599 -- Search for an adjusting period that is open or future enterable
600 OPEN find_period;
601 FETCH find_period INTO x_period_name, period_start_date,
602 period_end_date, tmp_year, tmp_num;
603 IF find_period%FOUND THEN
604 CLOSE find_period;
605 ELSE
606 CLOSE find_period;
607 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
608 app_exception.raise_exception;
609 END IF;
610
611 period_name := x_period_name;
612 start_date := period_start_date;
613 end_date := period_end_date;
614 period_year := tmp_year;
615 period_num := tmp_num;
616 END;
617
618 -- Determine if we need to roll dates
619 BEGIN
620 roll_dates := 'N';
621
622 SELECT nvl(max('Y'),'N')
623 INTO roll_dates
624 FROM gl_je_headers jeh, gl_ledgers lgr
625 WHERE jeh.je_batch_id = batch_id
626 AND lgr.ledger_id = jeh.ledger_id
627 AND lgr.enable_average_balances_flag = 'Y'
628 AND lgr.consolidation_ledger_flag = 'N'
629 AND rownum = 1;
630
631 IF (roll_dates = 'N') THEN
635 END;
632 rolled_accounting_date := initial_accounting_date;
633 RETURN;
634 END IF;
636
637 -- Determine if the day is a business day
638 BEGIN
639 SELECT decode(min(decode(business_day_flag, 'Y', 1, 0)),1, 'Y', 'N')
640 INTO business_day
641 FROM gl_transaction_dates
642 WHERE transaction_calendar_id
643 IN (SELECT transaction_calendar_id
644 FROM gl_je_headers jeh, gl_ledgers lgr
645 WHERE jeh.je_batch_id = batch_id
646 AND lgr.ledger_id = jeh.ledger_id)
647 AND transaction_date = initial_accounting_date;
648
649 IF (business_day = 'Y') THEN
650 rolled_accounting_date := initial_accounting_date;
651 RETURN;
652 ELSIF (roll_date <> 'Y') THEN
653 fnd_message.set_name('SQLGL', 'GL_JE_NOT_BUSINESS_DAY');
654 app_exception.raise_exception;
655 END IF;
656 EXCEPTION
657 WHEN NO_DATA_FOUND THEN
658 fnd_message.set_name('SQLGL', 'GL_JE_NOT_OPEN_OR_FUTURE_ENT');
659 app_exception.raise_exception;
660 END;
661
662 -- Roll the date back to a business day
663 BEGIN
664 SELECT max(transaction_date)
665 INTO new_accounting_date
666 FROM gl_transaction_dates trans
667 WHERE trans.transaction_calendar_id = one_trans_cal_id
668 AND trans.transaction_date >= greatest(period_start_date,
669 nvl(minimum_date,
670 period_start_date))
671 AND trans.business_day_flag = 'Y'
672 AND trans.transaction_date < initial_accounting_date
673 AND NOT EXISTS
674 (SELECT 'not business'
675 FROM gl_je_headers jeh, gl_ledgers lgr,
676 gl_transaction_dates trans2
677 WHERE jeh.je_batch_id = batch_id
678 AND lgr.ledger_id = jeh.ledger_id
679 AND trans2.transaction_calendar_id
680 = lgr.transaction_calendar_id
681 AND trans2.business_day_flag = 'N'
682 AND trans2.transaction_date = trans.transaction_date);
683
684 IF (new_accounting_date IS NOT NULL) THEN
685 rolled_accounting_date := new_accounting_date;
686 RETURN;
687 END IF;
688 EXCEPTION
689 WHEN NO_DATA_FOUND THEN
690 null;
691 END;
692
693 -- Roll the date forward to a business day
694 BEGIN
695 SELECT min(transaction_date)
696 INTO new_accounting_date
697 FROM gl_transaction_dates trans
698 WHERE trans.transaction_calendar_id = one_trans_cal_id
699 AND trans.transaction_date <= period_end_date
700 AND trans.business_day_flag = 'Y'
701 AND trans.transaction_date > initial_accounting_date
702 AND NOT EXISTS
703 (SELECT 'not business'
704 FROM gl_je_headers jeh, gl_ledgers lgr,
705 gl_transaction_dates trans2
706 WHERE jeh.je_batch_id = batch_id
707 AND lgr.ledger_id = jeh.ledger_id
708 AND trans2.transaction_calendar_id
709 = lgr.transaction_calendar_id
710 AND trans2.business_day_flag = 'N'
711 AND trans2.transaction_date = trans.transaction_date);
712
713 IF (new_accounting_date IS NOT NULL) THEN
714 rolled_accounting_date := new_accounting_date;
715 RETURN;
716 END IF;
717 EXCEPTION
718 WHEN NO_DATA_FOUND THEN
719 rolled_accounting_date := null;
720 END;
721
722 END validate_date_batch;
723
724
725 PROCEDURE find_from_period( lgr_id NUMBER,
726 calendar VARCHAR2,
727 per_type VARCHAR2,
728 active_date DATE,
729 from_period IN OUT NOCOPY VARCHAR2,
730 per_start_date IN OUT NOCOPY DATE,
731 per_end_date IN OUT NOCOPY DATE,
732 per_number IN OUT NOCOPY NUMBER,
733 per_year IN OUT NOCOPY NUMBER
734 ) IS
735
736 x_from_period VARCHAR2(15);
737 x_per_start_date DATE;
738 x_per_end_date DATE;
739 x_per_number NUMBER;
740 x_per_year NUMBER;
741 period_status VARCHAR2(1);
742
743 CURSOR find_period IS
744 SELECT ps.period_name, ps.start_date, ps.end_date, ps.period_num,
745 ps.period_year
746 FROM gl_period_statuses ps
747 WHERE ps.application_id = 101
748 AND ps.ledger_id = lgr_id
749 AND ps.start_date <= trunc(active_date)
750 AND ps.end_date >= trunc(active_date)
751 AND ps.closing_status IN ('O', 'C', 'P')
752 ORDER BY ps.effective_period_num ASC;
753
754 BEGIN
755 -- First, see if the non-adjusting period that contains this
756 -- date is open, closed or permanently closed.
757 SELECT ps.period_name, ps.closing_status, ps.start_date, ps.end_date,
758 ps.period_num, ps.period_year
759 INTO x_from_period, period_status, x_per_start_date, x_per_end_date,
760 x_per_number, x_per_year
761 FROM gl_date_period_map map, gl_period_statuses ps
762 WHERE map.period_set_name = calendar
763 AND map.period_type = per_type
764 AND map.accounting_date = trunc(active_date)
765 AND ps.application_id = 101
766 AND ps.ledger_id = lgr_id
767 AND ps.period_name = map.period_name;
768
769 IF (period_status NOT IN ('O', 'C', 'P')) THEN
770 RAISE NO_DATA_FOUND;
771 END IF;
772
773 from_period := x_from_period;
774 per_start_date := x_per_start_date;
775 per_end_date := x_per_end_date;
776 per_number := x_per_number;
777 per_year := x_per_year;
778
779 RETURN;
780 EXCEPTION
781 WHEN NO_DATA_FOUND THEN
782 -- Search for an adjusting period that is open, closed or permanently
783 -- closed.
784 OPEN find_period;
785 FETCH find_period INTO x_from_period, x_per_start_date, x_per_end_date,
786 x_per_number, x_per_year;
787
788 IF (find_period%NOTFOUND) THEN
789 CLOSE find_period;
790 fnd_message.set_name('SQLGL', 'GL_CONS_DATE_NOT_OPEN_CLOSED');
791 app_exception.raise_exception;
792 END IF;
793
794 CLOSE find_period;
795
796 from_period := x_from_period;
797 per_start_date := x_per_start_date;
798 per_end_date := x_per_end_date;
799 per_number := x_per_number;
800 per_year := x_per_year;
801 END find_from_period;
802
803
804 END GL_DATE_HANDLER_PKG;
[Home] [Help]