[Home] [Help]
PACKAGE BODY: APPS.GL_PERIODS_PKG
Source
1 PACKAGE BODY gl_periods_pkg AS
2 /* $Header: gliprdeb.pls 120.8 2006/12/06 06:44:34 spala ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7
8 PROCEDURE fix_max_len(call_mode VARCHAR2,
9 per_type VARCHAR2,
10 row_id VARCHAR2,
11 old_len NUMBER,
12 new_len NUMBER) IS
13 CURSOR get_max IS
14 SELECT nvl(max_regular_period_length,0)
15 FROM ar_period_types
16 WHERE period_type = per_type;
17 max_len NUMBER;
18 tmp_len NUMBER;
19
20 does_exist VARCHAR2(100);
21 BEGIN
22 -- If this is an update and the size of the period did not
23 -- change, then just return
24 IF ( (call_mode = 'U')
25 AND (old_len = new_len)) THEN
26 RETURN;
27 END IF;
28
29 OPEN get_max;
30 FETCH get_max INTO max_len;
31
32 -- Check if this is the first period ever of this type
33 IF get_max%NOTFOUND THEN
34 CLOSE get_max;
35
36 max_len := new_len;
37 INSERT into ar_period_types
38 (period_type, max_regular_period_length)
39 VALUES (per_type, max_len);
40
41 -- Not the first period
42 ELSE
43 CLOSE get_max;
44
45 -- Check if this period is larger than any earlier period
46 IF ( (call_mode IN ('I', 'U'))
47 AND (max_len < new_len)) THEN
48
49 max_len := new_len;
50 UPDATE ar_period_types
51 SET max_regular_period_length = max_len
52 WHERE period_type = per_type;
53 END IF;
54
55 -- Check if this period was the maximum one
56 IF ( (call_mode IN ('D', 'U'))
57 AND (max_len = old_len)) THEN
58
59 -- Normally there will be many periods with the same
60 -- maximum length. Thus, first we do an exists
61 -- check, since in general that will be faster.
62 BEGIN
63 SELECT 'still max'
64 INTO does_exist
65 FROM dual
66 WHERE EXISTS
67 (SELECT 'still max'
68 FROM gl_periods
69 WHERE period_type = per_type
70 AND adjustment_period_flag = 'N'
71 AND end_date - start_date + 1 = max_len
72 AND rowid <> row_id);
73
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76
77 -- No other periods with this maximum length.
78 -- Thus, we give up and search for the new maximum
79 -- Note that if there are no other periods, the max
80 -- will be set to null
81 UPDATE ar_period_types pt
82 SET pt.max_regular_period_length =
83 (SELECT decode(call_mode,
84 'D',max(end_date - start_date) + 1,
85 'U',greatest(new_len, nvl(max(end_date-start_date)+1,0)))
86 FROM gl_periods per
87 WHERE per.adjustment_period_flag = 'N'
88 AND per.period_type = per_type
89 AND per.rowid <> row_id)
90 WHERE pt.period_type = per_type;
91 END;
92 END IF;
93 END IF;
94 END fix_max_len;
95
96
97 PROCEDURE fix_ar_periods(call_mode VARCHAR2,
98 cal_name VARCHAR2,
99 per_type VARCHAR2,
100 new_start_date DATE,
101 new_end_date DATE,
102 new_per_name VARCHAR2,
103 old_per_name VARCHAR2,
104 old_start_date DATE) IS
105 CURSOR get_new_pos IS
106 SELECT nvl(max(new_period_num + 1), 1)
107 FROM ar_periods
108 WHERE period_set_name = cal_name
109 AND period_type = per_type
110 AND start_date < new_start_date;
111
112 CURSOR get_old_pos IS
113 SELECT new_period_num
114 FROM ar_periods
115 WHERE period_set_name = cal_name
116 AND period_type = per_type
117 AND period_name = old_per_name;
118
119 new_pos NUMBER;
120 old_pos NUMBER;
121 BEGIN
122 IF (call_mode = 'I') THEN
123
124 -- Find where this period goes
125 OPEN get_new_pos;
126 FETCH get_new_pos INTO new_pos;
127 IF get_new_pos%NOTFOUND THEN
128 new_pos := 1;
129 END IF;
130 CLOSE get_new_pos;
131
132 -- Increase the AR period number of all
133 -- periods after it
134 UPDATE ar_periods
135 SET new_period_num = new_period_num + 1
136 WHERE period_set_name = cal_name
137 AND period_type = per_type
138 AND new_period_num >= new_pos;
139
140 -- Insert the new period
141 INSERT INTO ar_periods
142 (period_set_name, period_type, start_date, end_date,
143 new_period_num, period_name)
144 VALUES
145 (cal_name, per_type, new_start_date, new_end_date,
146 new_pos, new_per_name);
147
148 ELSIF (call_mode = 'U') THEN
149
150 new_pos := 1;
151 old_pos := new_pos;
152
153 -- If the start date has changed, then the AR period number
154 -- may have changed also. Get the old and new AR period
155 -- numbers in this case.
156 IF (new_start_date <> old_start_date) THEN
157 -- Find where this period was
158 OPEN get_old_pos;
159 FETCH get_old_pos INTO old_pos;
160 IF get_old_pos%NOTFOUND THEN
161 CLOSE get_old_pos;
162 RAISE NO_DATA_FOUND;
163 ELSE
164 CLOSE get_old_pos;
165 END IF;
166
167 -- Find where this period goes
168 OPEN get_new_pos;
169 FETCH get_new_pos INTO new_pos;
170 IF get_new_pos%NOTFOUND THEN
171 new_pos := 1;
172 END IF;
173 CLOSE get_new_pos;
174 END IF;
175
176 -- If we are moving the period later, then
177 -- everything is going to be moved earlier
178 -- anyway, so the new position is actually
179 -- one period earlier
180 IF (old_pos < new_pos) THEN
181 new_pos := new_pos - 1;
182 END IF;
183
184 -- Check if we have changed position. This is an unusual case.
185 -- Normally, the customer will just be moving the start date a couple
186 -- of days one way or another, not moving the entire period
187 IF (old_pos <> new_pos) THEN
188
189 -- Update the changed periods AR period number to null, so that
190 -- we can change the AR period number of the other periods
191 UPDATE ar_periods
192 SET new_period_num = NULL
193 WHERE period_set_name = cal_name
194 AND period_type = per_type
195 AND period_name = old_per_name;
196
197 IF (new_pos < old_pos) THEN
198 -- Moving the period earlier
199 UPDATE ar_periods
200 SET new_period_num = new_period_num + 1
201 WHERE period_set_name = cal_name
202 AND period_type = per_type
203 AND new_period_num BETWEEN new_pos AND old_pos;
204
205 ELSE
206 -- Moving the period later
207 UPDATE ar_periods
208 SET new_period_num = new_period_num - 1
209 WHERE period_set_name = cal_name
210 AND period_type = per_type
211 AND new_period_num BETWEEN old_pos AND new_pos;
212 END IF;
213
214 -- Move the period to its new position. Also fix everything
215 -- else
216 UPDATE ar_periods
217 SET start_date = new_start_date,
218 end_date = new_end_date,
219 period_name = new_per_name,
220 new_period_num = new_pos
221 WHERE period_set_name = cal_name
222 AND period_type = per_type
223 AND period_name = old_per_name;
224
225 ELSE
226 -- The AR period number isn't changing, but fix everything else
227 UPDATE ar_periods
228 SET start_date = new_start_date,
229 end_date = new_end_date,
230 period_name = new_per_name
231 WHERE period_set_name = cal_name
232 AND period_type = per_type
233 AND period_name = old_per_name;
234 END IF;
235
236 ELSIF (call_mode = 'D') THEN
237
238 -- Find where this period was
239 OPEN get_old_pos;
240 FETCH get_old_pos INTO old_pos;
241 IF get_old_pos%NOTFOUND THEN
242 CLOSE get_old_pos;
243 RAISE NO_DATA_FOUND;
244 ELSE
245 CLOSE get_old_pos;
246 END IF;
247
248 -- Delete the old period
249 DELETE ar_periods
250 WHERE period_set_name = cal_name
251 AND period_type = per_type
252 AND period_name = old_per_name;
253
254 -- Decrease the AR period number of all
255 -- periods after it
256 UPDATE ar_periods
257 SET new_period_num = new_period_num - 1
258 WHERE period_set_name = cal_name
259 AND period_type = per_type
260 AND new_period_num > old_pos;
261
262 END IF;
263
264 END fix_ar_periods;
265
266
267 --
268 -- PUBLIC FUNCTIONS
269 --
270
271 PROCEDURE check_unique_num(calendar_name VARCHAR2,
272 period_type VARCHAR2,
273 period_year NUMBER,
274 period_num NUMBER,
275 row_id VARCHAR2) IS
276 CURSOR chk_duplicates IS
277 SELECT 'Duplicate'
278 FROM GL_PERIODS gp
279 WHERE gp.period_set_name =
280 check_unique_num.calendar_name
281 AND gp.period_type = check_unique_num.period_type
282 AND gp.period_year = check_unique_num.period_year
283 AND gp.period_num = check_unique_num.period_num
284
285 AND ( row_id is NULL
286 OR gp.rowid <> row_id);
287 dummy VARCHAR2(100);
288 BEGIN
289 OPEN chk_duplicates;
290 FETCH chk_duplicates INTO dummy;
291
292 IF chk_duplicates%FOUND THEN
293 CLOSE chk_duplicates;
294 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_PERIOD_NUMBER');
295 app_exception.raise_exception;
296 END IF;
297
298 CLOSE chk_duplicates;
299
300 EXCEPTION
301 WHEN app_exceptions.application_exception THEN
302 RAISE;
303 WHEN OTHERS THEN
304 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
305 fnd_message.set_token('PROCEDURE', 'PERIODS.check_unique_num');
306 RAISE;
307 END check_unique_num;
308
309
310 PROCEDURE check_unique_name(calendar_name VARCHAR2,
311 period_name VARCHAR2,
312 row_id VARCHAR2) IS
313 CURSOR chk_duplicates IS
314 SELECT 'Duplicate'
315 FROM GL_PERIODS gp
316 WHERE gp.period_name = check_unique_name.period_name
317 AND gp.period_set_name =
318 check_unique_name.calendar_name
319 AND ( row_id IS NULL
320 OR gp.rowid <> row_id);
321 dummy VARCHAR2(100);
322 BEGIN
323 OPEN chk_duplicates;
324 FETCH chk_duplicates INTO dummy;
325
326 IF (chk_duplicates%FOUND) THEN
327 CLOSE chk_duplicates;
328 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_PERIOD_NAME');
329 app_exception.raise_exception;
330 END IF;
331
332 CLOSE chk_duplicates;
333
334 EXCEPTION
335 WHEN app_exceptions.application_exception THEN
336 RAISE;
337 WHEN OTHERS THEN
338 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
339 fnd_message.set_token('PROCEDURE', 'PERIODS.check_unique_name');
340 RAISE;
341 END check_unique_name;
342
343
344 FUNCTION check_period_used(row_id VARCHAR2) RETURN BOOLEAN IS
345 dummy VARCHAR2(100);
346 calendar_name VARCHAR2(15);
347 period_type VARCHAR2(15);
348 period_name VARCHAR2(15);
349 period_year NUMBER;
350 BEGIN
351
352 DECLARE
353 CURSOR get_data IS
354 SELECT per.period_set_name, per.period_type,
355 per.period_name, per.period_year
356 FROM gl_periods per
357 WHERE per.rowid = row_id;
358 BEGIN
359 OPEN get_data;
360 FETCH get_data INTO calendar_name, period_type, period_name, period_year;
361
362 IF (get_data%NOTFOUND) THEN
363 CLOSE get_data;
364 RAISE NO_DATA_FOUND;
365 END IF;
366
367 CLOSE get_data;
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
372 fnd_message.set_token('PROCEDURE', 'PERIODS.check_period_used');
373 fnd_message.set_token('EVENT', 'Getting data');
374 RAISE;
375 END;
376
377 -- Check for use with actuals
378 DECLARE
379 CURSOR chk_actual IS
380 SELECT 'Opened'
381 FROM gl_period_statuses ps,
382 gl_ledgers led
383 WHERE ps.application_id IN (101, 275, 283)
384 AND ps.period_name = check_period_used.period_name
385 AND ps.closing_status <> 'N'
386 AND ps.ledger_id = led.ledger_id+0
387 AND ps.period_type = check_period_used.period_type
388 AND led.period_set_name =
389 check_period_used.calendar_name;
390 BEGIN
391 OPEN chk_actual;
392 FETCH chk_actual INTO dummy;
393
394 IF (chk_actual%FOUND) THEN
395 CLOSE chk_actual;
396 RETURN(TRUE);
397 END IF;
398
399 CLOSE chk_actual;
400
401 EXCEPTION
402 WHEN app_exceptions.application_exception THEN
403 RAISE;
404 WHEN OTHERS THEN
405 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
406 fnd_message.set_token('PROCEDURE', 'PERIODS.check_period_used');
407 fnd_message.set_token('EVENT', 'Checking Actuals');
408 RAISE;
409 END;
410
411 -- Check for use with budgets
412 DECLARE
413 CURSOR chk_budget is
414 SELECT 'Used for budgets'
415 FROM gl_budgets b,
416 gl_budget_versions bv,
417 gl_budget_period_ranges bpr,
418 gl_ledgers led
419 WHERE bv.budget_name = b.budget_name
420 AND bv.budget_type = b.budget_type
421 AND bpr.budget_version_id = bv.budget_version_id
422 AND bpr.period_year =
423 check_period_used.period_year
424 AND led.ledger_id = b.ledger_id
425 AND led.period_set_name =
426 check_period_used.calendar_name
427 AND led.accounted_period_type = check_period_used.period_type;
428 BEGIN
429 OPEN chk_budget;
430 FETCH chk_budget INTO dummy;
431
432 IF (chk_budget%FOUND) THEN
433 CLOSE chk_budget;
434 RETURN(TRUE);
435 END IF;
436
437 CLOSE chk_budget;
438
439 EXCEPTION
440 WHEN app_exceptions.application_exception THEN
441 RAISE;
442 WHEN OTHERS THEN
443 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
444 fnd_message.set_token('PROCEDURE', 'PERIODS.check_period_used');
445 fnd_message.set_token('EVENT', 'Checking Budgets');
446 RAISE;
447 END;
448
449 -- Check for use with encumbrances
450 DECLARE
451 CURSOR chk_encumbrance IS
452 SELECT 'Used for encumbrances'
453 FROM gl_ledgers led
454 WHERE led.period_set_name =
455 check_period_used.calendar_name
456 AND led.accounted_period_type = check_period_used.period_type
457 AND led.latest_encumbrance_year >=
458 check_period_used.period_year;
459 BEGIN
460 OPEN chk_encumbrance;
461 FETCH chk_encumbrance INTO dummy;
462
463 IF (chk_encumbrance%FOUND) THEN
464 CLOSE chk_encumbrance;
465 RETURN(TRUE);
466 END IF;
467
468 CLOSE chk_encumbrance;
469
470 EXCEPTION
471 WHEN app_exceptions.application_exception THEN
472 RAISE;
473 WHEN OTHERS THEN
474 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
475 fnd_message.set_token('PROCEDURE', 'PERIODS.check_period_used');
476 fnd_message.set_token('EVENT', 'Checking Encumbrances');
477 RAISE;
478 END;
479
480 -- Check period used by projects
481 IF (pa_periods_pkg.check_gl_period_used_in_pa(period_name, calendar_name)
482 = 'Y') THEN
483 RETURN(TRUE);
484 END IF;
485
486 RETURN(FALSE);
487 END check_period_used;
488
489 FUNCTION period_changed(X_Rowid VARCHAR2,
490 X_Period_Set_Name VARCHAR2,
491 X_Period_Name VARCHAR2,
492 X_Start_Date DATE,
493 X_End_Date DATE,
494 X_Period_Type VARCHAR2,
495 X_Period_Year NUMBER,
496 X_Period_Num NUMBER,
497 X_Quarter_Num NUMBER,
498 X_Entered_Period_Name VARCHAR2,
499 X_Description VARCHAR2
500 ) RETURN BOOLEAN IS
501 CURSOR C IS
502 SELECT *
503 FROM GL_PERIODS
504 WHERE rowid = X_Rowid
505 FOR UPDATE of Period_Set_Name NOWAIT;
506 Recinfo C%ROWTYPE;
507 BEGIN
508 OPEN C;
509 FETCH C INTO Recinfo;
510 if (C%NOTFOUND) then
511 CLOSE C;
512 RAISE NO_DATA_FOUND;
513 end if;
514 CLOSE C;
515 if (
516 ( (Recinfo.period_set_name = X_Period_Set_Name)
517 OR ( (Recinfo.period_set_name IS NULL)
518 AND (X_Period_Set_Name IS NULL)))
519 AND ( (Recinfo.period_name = X_Period_Name)
520 OR ( (Recinfo.period_name IS NULL)
521 AND (X_Period_Name IS NULL)))
522 AND ( (Recinfo.start_date = X_Start_Date)
523 OR ( (Recinfo.start_date IS NULL)
524 AND (X_Start_Date IS NULL)))
525 AND ( (Recinfo.end_date = X_End_Date)
526 OR ( (Recinfo.end_date IS NULL)
527 AND (X_End_Date IS NULL)))
528 AND ( (Recinfo.period_type = X_Period_Type)
529 OR ( (Recinfo.period_type IS NULL)
530 AND (X_Period_Type IS NULL)))
531 AND ( (Recinfo.period_year = X_Period_Year)
532 OR ( (Recinfo.period_year IS NULL)
533 AND (X_Period_Year IS NULL)))
534 AND ( (Recinfo.period_num = X_Period_Num)
535 OR ( (Recinfo.period_num IS NULL)
536 AND (X_Period_Num IS NULL)))
537 AND ( (Recinfo.quarter_num = X_Quarter_Num)
538 OR ( (Recinfo.quarter_num IS NULL)
539 AND (X_Quarter_Num IS NULL)))
540 AND ( (Recinfo.entered_period_name = X_Entered_Period_Name)
541 OR ( (Recinfo.entered_period_name IS NULL)
542 AND (X_Entered_Period_Name IS NULL)))
543 AND ( (Recinfo.description = X_Description)
544 OR ( (Recinfo.description IS NULL)
545 AND (X_Description IS NULL)))
546 ) then
547 RETURN(FALSE);
548 else
549 RETURN(TRUE);
550 end if;
551 END period_changed;
552
553 FUNCTION overlapping_period(x_period_set_name VARCHAR2,
554 x_period_type VARCHAR2,
555 x_start_date DATE,
556 x_end_date DATE,
557 row_id VARCHAR2
558 ) RETURN BOOLEAN IS
559 CURSOR check_overlaps IS
560 SELECT 'Overlapping'
561 FROM GL_PERIODS
562 WHERE period_set_name = x_period_set_name
563 AND period_type = x_period_type
564 AND start_date <= x_end_date
565 AND end_date >= x_start_date
566 AND adjustment_period_flag = 'N'
567 AND ( row_id is NULL
568 OR rowid <> row_id);
569
570 dummy VARCHAR2(100);
571 BEGIN
572 OPEN check_overlaps;
573 FETCH check_overlaps INTO dummy;
574 if (check_overlaps%NOTFOUND) then
575 CLOSE check_overlaps;
576 RETURN(FALSE);
577 else
578 CLOSE check_overlaps;
579 RETURN(TRUE);
580 end if;
581 END overlapping_period;
582
583 PROCEDURE Load_Row(X_Period_Set_Name VARCHAR2,
584 X_Period_Name VARCHAR2,
585 X_Owner VARCHAR2,
586 X_Start_Date VARCHAR2,
587 X_End_Date VARCHAR2,
588 X_Period_Type VARCHAR2,
589 X_Period_Year NUMBER,
590 X_Period_Num NUMBER,
591 X_Quarter_Num NUMBER,
592 X_Entered_Period_Name VARCHAR2,
593 X_Description VARCHAR2,
594 X_Attribute1 VARCHAR2,
595 X_Attribute2 VARCHAR2,
596 X_Attribute3 VARCHAR2,
597 X_Attribute4 VARCHAR2,
598 X_Attribute5 VARCHAR2,
599 X_Attribute6 VARCHAR2,
600 X_Attribute7 VARCHAR2,
601 X_Attribute8 VARCHAR2,
602 X_Context VARCHAR2,
603 X_Adjustment_Period_Flag VARCHAR2
604 ) AS
605 user_id number := 0;
606 v_creation_date date;
607 v_rowid rowid := null;
608 BEGIN
609 -- validate input parameters
610 if ( X_Period_Set_Name is null) then
611
612 fnd_message.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
613 app_exception.raise_exception;
614 end if;
615
616 if (X_OWNER = 'SEED') then
617 user_id := 1;
618 end if;
619
620 BEGIN
621
622 SELECT creation_date, rowid
623 INTO v_creation_date, v_rowid
624 FROM GL_PERIODS
625 WHERE period_set_name = X_Period_Set_Name
626 AND period_name = X_Period_Name;
627
628 IF ( X_OWNER = 'SEED' ) THEN
629 gl_periods_pkg.Update_Row (
630 X_Rowid => v_rowid ,
631 X_Period_Set_Name => X_Period_Set_Name,
632 X_Period_Name => X_Period_Name,
633 X_Last_Update_Date => sysdate,
634 X_Last_Updated_By => user_id,
635 X_Start_Date => to_date(X_Start_Date,'YYYY/MM/DD'),
636 X_End_Date => to_date(X_End_Date,'YYYY/MM/DD'),
637 X_Period_Type => X_Period_Type,
638 X_Period_Year => X_Period_Year,
639 X_Period_Num => X_Period_Num,
640 X_Quarter_Num => X_Quarter_Num,
641 X_Entered_Period_Name => X_Entered_Period_Name,
642 X_Last_Update_Login => 0,
643 X_Description => X_Description,
644 X_Attribute1 => X_Attribute1,
645 X_Attribute2 => X_Attribute2,
646 X_Attribute3 => X_Attribute3,
647 X_Attribute4 => X_Attribute4,
648 X_Attribute5 => X_Attribute5,
649 X_Attribute6 => X_Attribute6,
650 X_Attribute7 => X_Attribute7,
651 X_Attribute8 => X_Attribute8,
652 X_Context => X_Context,
653 X_Adjustment_Period_Flag => X_Adjustment_Period_Flag
654 );
655 END IF;
656
657 EXCEPTION
658 WHEN NO_DATA_FOUND THEN
659
660 gl_periods_pkg.Insert_Row (
661 X_Rowid => v_rowid ,
662 X_Period_Set_Name => X_Period_Set_Name,
663 X_Period_Name => X_Period_Name,
664 X_Last_Update_Date => sysdate,
665 X_Last_Updated_By => user_id,
666 X_Creation_date => sysdate,
667 X_Created_By => user_id,
668 X_Start_Date => to_date(X_Start_Date,'YYYY/MM/DD'),
669 X_End_Date => to_date(X_End_Date,'YYYY/MM/DD'),
670 X_Period_Type => X_Period_Type,
671 X_Period_Year => X_Period_Year,
672 X_Period_Num => X_Period_Num,
673 X_Quarter_Num => X_Quarter_Num,
674 X_Entered_Period_Name => X_Entered_Period_Name,
675 X_Last_Update_Login => 0,
676 X_Description => X_Description,
677 X_Attribute1 => X_Attribute1,
678 X_Attribute2 => X_Attribute2,
679 X_Attribute3 => X_Attribute3,
680 X_Attribute4 => X_Attribute4,
681 X_Attribute5 => X_Attribute5,
682 X_Attribute6 => X_Attribute6,
683 X_Attribute7 => X_Attribute7,
684 X_Attribute8 => X_Attribute8,
685 X_Context => X_Context,
686 X_Adjustment_Period_Flag => X_Adjustment_Period_Flag
687 );
688
689 END;
690 END Load_Row;
691
692
693
694 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
695 X_Period_Set_Name VARCHAR2,
696 X_Period_Name VARCHAR2,
697 X_Last_Update_Date DATE,
698 X_Last_Updated_By NUMBER,
699 X_Start_Date DATE,
700 X_End_Date DATE,
701 X_Period_Type VARCHAR2,
702 X_Period_Year NUMBER,
703 X_Period_Num NUMBER,
704 X_Quarter_Num NUMBER,
705 X_Entered_Period_Name VARCHAR2,
706 X_Creation_Date DATE,
707 X_Created_By NUMBER,
708 X_Last_Update_Login NUMBER,
709 X_Description VARCHAR2,
710 X_Attribute1 VARCHAR2,
711 X_Attribute2 VARCHAR2,
712 X_Attribute3 VARCHAR2,
713 X_Attribute4 VARCHAR2,
714 X_Attribute5 VARCHAR2,
715 X_Attribute6 VARCHAR2,
716 X_Attribute7 VARCHAR2,
717 X_Attribute8 VARCHAR2,
718 X_Context VARCHAR2,
719 X_Adjustment_Period_Flag VARCHAR2
720 ) IS
721 CURSOR C IS SELECT rowid FROM GL_PERIODS
722 WHERE period_set_name = X_Period_Set_Name
723 AND period_name = X_Period_Name;
724
725
726 x_quarter_start_date DATE;
727 x_year_start_date DATE;
728
729 BEGIN
730
731
732 IF (instr(X_period_name, '''') <> 0) THEN
733 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_WITH_QUOTE');
734 app_exception.raise_exception;
735 END IF;
736
737 IF (instr(X_entered_period_name, '''') <> 0) THEN
738 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_WITH_QUOTE');
739 app_exception.raise_exception;
740 END IF;
741
742 IF (X_Adjustment_Period_Flag = 'N') THEN
743 -- If this is not an adjusting period, then verify that this period
744 -- does not overlap with any other periods
745 IF (overlapping_period(X_Period_Set_Name,
746 X_Period_Type,
747 X_Start_Date,
748 X_End_Date,
749 null)
750 ) THEN
751 fnd_message.set_name('SQLGL', 'GL_CALENDAR_OVERLAP_PERIODS');
752 app_exception.raise_exception;
753 END IF;
754
755 -- Reset the maximum length, if necessary
756 fix_max_len('I', X_Period_Type, NULL, NULL, X_End_Date - X_Start_Date + 1);
757
758 -- Insert the new row into ar_periods
759 fix_ar_periods('I', X_Period_Set_Name, X_Period_Type, X_Start_Date,
760 X_End_Date, X_Period_Name, NULL, NULL);
761
762 END IF;
763
764 -- call AB procedure which maintains all AB data
765 maintain_AB_data (
766 'INSERT',
767 X_Period_Set_Name,
768 X_Period_Type,
769 X_Adjustment_Period_Flag,
770 X_Start_Date+1, -- to make old and new date different
771 X_Start_Date,
772 X_End_Date,
773 X_Period_Name,
774 X_Period_Year,
775 X_Period_Year,
776 X_Quarter_Num,
777 X_Quarter_Num,
778 x_quarter_start_date,
779 x_year_start_date,
780 X_Creation_Date,
781 X_Created_By,
782 X_Last_Update_Date,
783 X_Last_Updated_By,
784 X_Last_Update_Login
785 );
786
787 INSERT INTO GL_PERIODS(
788 period_set_name,
789 period_name,
790 last_update_date,
791 last_updated_by,
792 start_date,
793 end_date,
794 period_type,
795 period_year,
796 period_num,
797 quarter_num,
798 entered_period_name,
799 creation_date,
800 created_by,
801 last_update_login,
802 description,
803 attribute1,
804 attribute2,
805 attribute3,
806 attribute4,
807 attribute5,
808 attribute6,
809 attribute7,
810 attribute8,
811 context,
812 adjustment_period_flag,
813 quarter_start_date,
814 year_start_date
815 ) VALUES (
816 X_Period_Set_Name,
817 X_Period_Name,
818 X_Last_Update_Date,
819 X_Last_Updated_By,
820 X_Start_Date,
821 X_End_Date,
822 X_Period_Type,
823 X_Period_Year,
824 X_Period_Num,
825 X_Quarter_Num,
826 X_Entered_Period_Name,
827 X_Creation_Date,
828 X_Created_By,
829 X_Last_Update_Login,
830 X_Description,
831 X_Attribute1,
832 X_Attribute2,
833 X_Attribute3,
834 X_Attribute4,
835 X_Attribute5,
836 X_Attribute6,
837 X_Attribute7,
838 X_Attribute8,
839 X_Context,
840 X_Adjustment_Period_Flag,
841 x_quarter_start_date,
842 x_year_start_date
843 );
844
845 OPEN C;
846 FETCH C INTO X_Rowid;
847 if (C%NOTFOUND) then
848 CLOSE C;
849 RAISE NO_DATA_FOUND;
850 end if;
851 CLOSE C;
852
853 -- Insert rows in gl_period_statuses for this period
854 gl_period_statuses_pkg.insert_period(
855 X_period_set_name,
856 X_period_name,
857 X_start_date,
858 X_end_date,
859 X_period_type,
860 X_period_year,
861 X_period_num,
862 X_quarter_num,
863 X_adjustment_period_flag,
864 X_last_updated_by,
865 X_last_update_login,
866 x_quarter_start_date,
867 x_year_start_date);
868
869 END Insert_Row;
870
871 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
872
873 X_Period_Set_Name VARCHAR2,
874 X_Period_Name VARCHAR2,
875 X_Start_Date DATE,
876 X_End_Date DATE,
877 X_Period_Type VARCHAR2,
878 X_Period_Year NUMBER,
879 X_Period_Num NUMBER,
880 X_Quarter_Num NUMBER,
881 X_Entered_Period_Name VARCHAR2,
882 X_Description VARCHAR2,
883 X_Attribute1 VARCHAR2,
884 X_Attribute2 VARCHAR2,
885 X_Attribute3 VARCHAR2,
886 X_Attribute4 VARCHAR2,
887 X_Attribute5 VARCHAR2,
888 X_Attribute6 VARCHAR2,
889 X_Attribute7 VARCHAR2,
890 X_Attribute8 VARCHAR2,
891 X_Context VARCHAR2,
892 X_Adjustment_Period_Flag VARCHAR2
893 ) IS
894 CURSOR C IS
895 SELECT *
896 FROM GL_PERIODS
897 WHERE rowid = X_Rowid
898 FOR UPDATE of Period_Set_Name NOWAIT;
899 Recinfo C%ROWTYPE;
900 BEGIN
901 OPEN C;
902 FETCH C INTO Recinfo;
903 if (C%NOTFOUND) then
904 CLOSE C;
905 RAISE NO_DATA_FOUND;
906 end if;
907 CLOSE C;
908 if (
909 ( (Recinfo.period_set_name = X_Period_Set_Name)
910 OR ( (Recinfo.period_set_name IS NULL)
911 AND (X_Period_Set_Name IS NULL)))
912 AND ( (Recinfo.period_name = X_Period_Name)
913 OR ( (Recinfo.period_name IS NULL)
914 AND (X_Period_Name IS NULL)))
915 AND ( (Recinfo.start_date = X_Start_Date)
916 OR ( (Recinfo.start_date IS NULL)
917 AND (X_Start_Date IS NULL)))
918 AND ( (Recinfo.end_date = X_End_Date)
919 OR ( (Recinfo.end_date IS NULL)
920 AND (X_End_Date IS NULL)))
921 AND ( (Recinfo.period_type = X_Period_Type)
922 OR ( (Recinfo.period_type IS NULL)
923 AND (X_Period_Type IS NULL)))
924 AND ( (Recinfo.period_year = X_Period_Year)
925 OR ( (Recinfo.period_year IS NULL)
926 AND (X_Period_Year IS NULL)))
927 AND ( (Recinfo.period_num = X_Period_Num)
928 OR ( (Recinfo.period_num IS NULL)
929 AND (X_Period_Num IS NULL)))
930 AND ( (Recinfo.quarter_num = X_Quarter_Num)
931 OR ( (Recinfo.quarter_num IS NULL)
932 AND (X_Quarter_Num IS NULL)))
933 AND ( (Recinfo.entered_period_name = X_Entered_Period_Name)
934 OR ( (Recinfo.entered_period_name IS NULL)
935 AND (X_Entered_Period_Name IS NULL)))
936 AND ( (Recinfo.description = X_Description)
937 OR ( (Recinfo.description IS NULL)
938 AND (X_Description IS NULL)))
939 AND ( (Recinfo.attribute1 = X_Attribute1)
940 OR ( (Recinfo.attribute1 IS NULL)
941 AND (X_Attribute1 IS NULL)))
942 AND ( (Recinfo.attribute2 = X_Attribute2)
943 OR ( (Recinfo.attribute2 IS NULL)
944 AND (X_Attribute2 IS NULL)))
945 AND ( (Recinfo.attribute3 = X_Attribute3)
946 OR ( (Recinfo.attribute3 IS NULL)
947 AND (X_Attribute3 IS NULL)))
948 AND ( (Recinfo.attribute4 = X_Attribute4)
949 OR ( (Recinfo.attribute4 IS NULL)
950 AND (X_Attribute4 IS NULL)))
951 AND ( (Recinfo.attribute5 = X_Attribute5)
952 OR ( (Recinfo.attribute5 IS NULL)
953 AND (X_Attribute5 IS NULL)))
954 AND ( (Recinfo.attribute6 = X_Attribute6)
955 OR ( (Recinfo.attribute6 IS NULL)
956 AND (X_Attribute6 IS NULL)))
957 AND ( (Recinfo.attribute7 = X_Attribute7)
958 OR ( (Recinfo.attribute7 IS NULL)
959 AND (X_Attribute7 IS NULL)))
960 AND ( (Recinfo.attribute8 = X_Attribute8)
961 OR ( (Recinfo.attribute8 IS NULL)
962 AND (X_Attribute8 IS NULL)))
963 AND ( (Recinfo.context = X_Context)
964 OR ( (Recinfo.context IS NULL)
965 AND (X_Context IS NULL)))
966 AND ( (Recinfo.adjustment_period_flag = X_Adjustment_Period_Flag)
967 OR ( (Recinfo.adjustment_period_flag IS NULL)
968 AND (X_Adjustment_Period_Flag IS NULL)))
969 ) then
970 return;
971 else
972 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
973 APP_EXCEPTION.RAISE_EXCEPTION;
974 end if;
975 END Lock_Row;
976
977 PROCEDURE Update_Row(X_Rowid VARCHAR2,
978 X_Period_Set_Name VARCHAR2,
979 X_Period_Name VARCHAR2,
980 X_Last_Update_Date DATE,
981 X_Last_Updated_By NUMBER,
982 X_Start_Date DATE,
983 X_End_Date DATE,
984 X_Period_Type VARCHAR2,
985 X_Period_Year NUMBER,
986 X_Period_Num NUMBER,
987 X_Quarter_Num NUMBER,
988 X_Entered_Period_Name VARCHAR2,
989 X_Last_Update_Login NUMBER,
990 X_Description VARCHAR2,
991 X_Attribute1 VARCHAR2,
992 X_Attribute2 VARCHAR2,
993 X_Attribute3 VARCHAR2,
994 X_Attribute4 VARCHAR2,
995 X_Attribute5 VARCHAR2,
996 X_Attribute6 VARCHAR2,
997 X_Attribute7 VARCHAR2,
998 X_Attribute8 VARCHAR2,
999 X_Context VARCHAR2,
1000 X_Adjustment_Period_Flag VARCHAR2
1001 ) IS
1002 CURSOR get_old_name IS
1003 SELECT period_name, start_date, end_date, period_year, quarter_num,
1004 adjustment_period_flag
1005 FROM gl_periods
1006 WHERE rowid = X_rowid;
1007
1008 X_period_name_old VARCHAR2(15);
1009 x_start_date_old DATE;
1010 x_end_date_old DATE;
1011 x_period_year_old NUMBER;
1012 x_quarter_num_old NUMBER;
1013 x_adj_flag_old VARCHAR2(1);
1014 x_quarter_start_date DATE;
1015 x_year_start_date DATE;
1016
1017 BEGIN
1018
1019 -- Make sure you are allowed to change this period
1020 IF (gl_periods_pkg.period_changed(X_Rowid,
1021 X_Period_Set_Name,
1022 X_Period_Name,
1023 X_Start_Date,
1024 X_End_Date,
1025 X_Period_Type,
1026 X_Period_Year,
1027 X_Period_Num,
1028 X_Quarter_Num,
1029 X_Entered_Period_Name,
1030 X_Description)) THEN
1031 IF (gl_periods_pkg.check_period_used(X_Rowid)) THEN
1032 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_IN_USE');
1033 app_exception.raise_exception;
1034 END IF;
1035 END IF;
1036
1037
1038 IF (instr(X_period_name, '''') <> 0) THEN
1039 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_WITH_QUOTE');
1040 app_exception.raise_exception;
1041 END IF;
1042
1043 IF (instr(X_entered_period_name, '''') <> 0) THEN
1044 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_WITH_QUOTE');
1045 app_exception.raise_exception;
1046 END IF;
1047
1048 -- If this is not an adjusting period, then verify that this period
1049 -- does not overlap with any other periods
1050 IF (X_Adjustment_Period_Flag = 'N') THEN
1051 IF (overlapping_period(X_Period_Set_Name,
1052 X_Period_Type,
1053 X_Start_Date,
1054 X_End_Date,
1055 X_RowId)
1056 ) THEN
1057 fnd_message.set_name('SQLGL', 'GL_CALENDAR_OVERLAP_PERIODS');
1058 app_exception.raise_exception;
1059 END IF;
1060 END IF;
1061
1062
1063 -- Get the original period name, in case it has been changed
1064 OPEN get_old_name;
1065 FETCH get_old_name INTO X_period_name_old, x_start_date_old, x_end_date_old,
1066 x_period_year_old, x_quarter_num_old,
1067 x_adj_flag_old;
1068 if (get_old_name%NOTFOUND) then
1069 CLOSE get_old_name;
1070 RAISE NO_DATA_FOUND;
1071 end if;
1072 CLOSE get_old_name;
1073
1074 IF (X_Adjustment_Period_Flag = 'N') THEN
1075 IF (x_adj_flag_old = 'N') THEN
1076 -- Reset the maximum length, if necessary
1077 fix_max_len('U', X_Period_Type, X_rowid,
1078 x_end_date_old - x_start_date_old + 1,
1079 X_End_Date - X_Start_Date + 1);
1080 -- Update the row in ar_periods
1081 fix_ar_periods('U', X_Period_Set_Name, X_Period_Type, X_Start_Date,
1082 X_End_Date, X_Period_Name, X_period_name_old,
1083 x_start_date_old);
1084 ELSE
1085 -- Reset the maximum length, if necessary
1086 fix_max_len('I', X_Period_Type, NULL,NULL, X_End_Date - X_Start_Date + 1);
1087 -- Insert the row into ar_periods
1088 fix_ar_periods('I', X_Period_Set_Name, X_Period_Type, X_Start_Date,
1089 X_End_Date, X_Period_Name, NULL, NULL);
1090 END IF;
1091 ELSE
1092 IF (x_adj_flag_old = 'N') THEN
1093 -- Reset the maximum length, if necessary
1094 fix_max_len('D', X_Period_Type, X_rowid,
1095 x_end_date_old - x_start_date_old + 1,
1096 NULL);
1097 -- Delete the row from ar_periods
1098 fix_ar_periods('D', X_Period_Set_Name, X_Period_Type, NULL, NULL, NULL,
1099 x_period_name_old, x_start_date_old);
1100 END IF;
1101 END IF;
1102
1103 UPDATE GL_PERIODS
1104 SET
1105
1106 period_set_name = X_Period_Set_Name,
1107 period_name = X_Period_Name,
1108 last_update_date = X_Last_Update_Date,
1109 last_updated_by = X_Last_Updated_By,
1110 start_date = X_Start_Date,
1111 end_date = X_End_Date,
1112 period_type = X_Period_Type,
1113 period_year = X_Period_Year,
1114 period_num = X_Period_Num,
1115 quarter_num = X_Quarter_Num,
1116 entered_period_name = X_Entered_Period_Name,
1117 last_update_login = X_Last_Update_Login,
1118 description = X_Description,
1119 attribute1 = X_Attribute1,
1120 attribute2 = X_Attribute2,
1121 attribute3 = X_Attribute3,
1122 attribute4 = X_Attribute4,
1123 attribute5 = X_Attribute5,
1124 attribute6 = X_Attribute6,
1125 attribute7 = X_Attribute7,
1126 attribute8 = X_Attribute8,
1127 context = X_Context,
1128 adjustment_period_flag = X_Adjustment_Period_Flag
1129 WHERE rowid = X_rowid;
1130
1131 if (SQL%NOTFOUND) then
1132 RAISE NO_DATA_FOUND;
1133 end if;
1134
1135 -- Update any rows in gl_period_statuses for this period
1136 gl_period_statuses_pkg.update_period(
1137 X_period_set_name,
1138 X_period_name_old,
1139 X_period_name,
1140 X_start_date,
1141 X_end_date,
1142 X_period_type,
1143 X_period_year,
1144 X_period_num,
1145 X_quarter_num,
1146 X_adjustment_period_flag,
1147 X_last_updated_by,
1148 X_last_update_login);
1149
1150 -- call AB proceudre which maintains all AB data
1151 maintain_AB_data (
1152 'UPDATE',
1153 X_Period_Set_Name,
1154 X_Period_Type,
1155 X_Adjustment_Period_Flag,
1156 x_start_date_old,
1157 X_Start_Date,
1158 X_End_Date,
1159 X_Period_Name,
1160 x_period_year_old,
1161 X_Period_Year,
1162 x_quarter_num_old,
1163 X_Quarter_Num,
1164 x_quarter_start_date,
1165 x_year_start_date,
1166 sysdate,
1167 X_Last_Updated_By,
1168 X_Last_Update_Date,
1169 X_Last_Updated_By,
1170 X_Last_Update_Login
1171 );
1172
1173 END Update_Row;
1174
1175 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
1176 CURSOR get_old_name IS
1177 SELECT period_name, period_set_name,
1178 start_date,period_year, quarter_num, period_type,
1179 adjustment_period_flag, end_date, period_num,
1180 last_updated_by,last_update_login
1181 FROM gl_periods
1182 WHERE rowid = X_rowid;
1183
1184 X_period_name_old VARCHAR2(15);
1185 X_period_set_name VARCHAR2(15);
1186 X_period_type VARCHAR2(15);
1187 X_adjustment_period_flag VARCHAR2(1);
1188 x_start_date_old DATE;
1189 x_end_date_old DATE;
1190 x_period_year_old NUMBER;
1191 x_quarter_num_old NUMBER;
1192 x_period_num_old NUMBER;
1193 x_quarter_start_date DATE;
1194 x_year_start_date DATE;
1195 x_last_updated_by NUMBER;
1196 x_last_update_login NUMBER;
1197 BEGIN
1198
1199 -- Get the original period name, in case it has been changed
1200 OPEN get_old_name;
1201 FETCH get_old_name INTO X_period_name_old, X_period_set_name,
1202 x_start_date_old,
1203 x_period_year_old, x_quarter_num_old, X_period_type,
1204 X_adjustment_period_flag, x_end_date_old,
1205 x_period_num_old,x_last_updated_by,x_last_update_login;
1206 if (get_old_name%NOTFOUND) then
1207 CLOSE get_old_name;
1208 RAISE NO_DATA_FOUND;
1209 end if;
1210 CLOSE get_old_name;
1211
1212 -- Make sure the period has never been used
1213 IF (gl_periods_pkg.check_period_used(X_Rowid)) THEN
1214 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_IN_USE');
1215 app_exception.raise_exception;
1216 END IF;
1217
1218 IF (X_Adjustment_Period_Flag = 'N') THEN
1219 -- Reset the maximum length, if necessary
1220 fix_max_len('D', X_Period_Type, x_rowid,
1221 x_end_date_old - x_start_date_old + 1,
1222 NULL);
1223 -- Delete the row from ar_periods
1224 fix_ar_periods('D', X_Period_Set_Name, X_Period_Type, NULL, NULL, NULL,
1225 x_period_name_old, x_start_date_old);
1226 END IF;
1227
1228 DELETE FROM GL_PERIODS
1229 WHERE rowid = X_Rowid;
1230
1231 if (SQL%NOTFOUND) then
1232 RAISE NO_DATA_FOUND;
1233 end if;
1234
1235 -- Delete any rows in gl_period_statuses
1236 -- associated with this period
1237 gl_period_statuses_pkg.delete_period(
1238 X_period_set_name,
1239 X_period_name_old);
1240
1241
1242 -- call AB procedure which maintains all AB data
1243 maintain_AB_data (
1244 'DELETE',
1245 X_Period_Set_Name,
1246 X_Period_Type,
1247 X_Adjustment_Period_Flag,
1248 x_start_date_old+1,
1249 X_start_date_old,
1250 x_end_date_old,
1251 X_period_name_old,
1252 x_period_year_old,
1253 x_period_year_old,
1254 x_quarter_num_old,
1255 x_period_num_old,
1256 x_quarter_start_date,
1257 x_year_start_date,
1258 sysdate,
1259 x_last_updated_by,
1260 sysdate,
1261 x_last_updated_by,
1262 x_last_update_login
1263 );
1264 END Delete_Row;
1265
1266 PROCEDURE maintain_quarter_start_date
1267 (
1268 x_period_set_name VARCHAR2,
1269 x_period_type VARCHAR2,
1270 x_period_year NUMBER,
1271 x_quarter_num NUMBER,
1272 x_start_date DATE,
1273 x_operation VARCHAR2,
1274 x_quarter_start_date IN OUT NOCOPY DATE
1275 ) IS
1276
1277 existing_quarter_start_date DATE;
1278 --x_quarter_start_date DATE;
1279
1280 BEGIN
1281
1282 -- Maintain quarter_start_date in GL_PERIODS and GL_PERIOD_STATUSES tables
1283 -- in Insert_row call to this procedure should be put before actual insert in the tables
1284 -- in Delete_Row call to this procedure should be put after actual update/delete
1285
1286 -- For quarter_start_date get existing_quarter_start_date:
1287 SELECT min(start_date)
1288 INTO existing_quarter_start_date
1289 FROM gl_periods
1290 WHERE
1291 period_set_name = x_period_set_name
1292 AND period_type = x_period_type
1293 AND period_year = x_period_year
1294 AND quarter_num = x_quarter_num;
1295
1296 x_quarter_start_date := NVL(existing_quarter_start_date,x_start_date);
1297
1298 -- update tables if necessary
1299 IF ( x_operation = 'INSERT') THEN
1300 IF(x_start_date >= existing_quarter_start_date ) THEN
1301 RETURN;
1302 ELSE
1303 x_quarter_start_date := x_start_date;
1304 END IF;
1305 END IF;
1306
1307
1308 UPDATE gl_periods
1309 SET quarter_start_date = x_quarter_start_date
1310 WHERE
1311 period_set_name = x_period_set_name
1312 AND quarter_num = x_quarter_num
1313 AND period_type = x_period_type
1314 AND period_year = x_period_year;
1315
1316 UPDATE gl_period_statuses
1317 SET quarter_start_date = x_quarter_start_date
1318 WHERE
1319 quarter_num = x_quarter_num
1320 AND period_type = x_period_type
1321 AND period_year = x_period_year
1322 AND ledger_id IN
1323 (SELECT ledger_id
1324 FROM gl_ledgers
1325 WHERE period_set_name = x_period_set_name);
1326
1327 END maintain_quarter_start_date;
1328
1329 PROCEDURE maintain_year_start_date
1330 (
1331 x_period_set_name VARCHAR2,
1332 x_period_type VARCHAR2,
1333 x_period_year NUMBER,
1334 x_start_date DATE,
1335 x_operation VARCHAR2,
1336 x_year_start_date IN OUT NOCOPY DATE
1337 ) IS
1338
1339 existing_year_start_date DATE;
1340 --x_year_start_date DATE;
1341
1342 BEGIN
1343
1344 --Maintain year_start_date in GL_PERIODS and GL_PERIOD_STATUSES tables
1345
1346 -- For year_start_date get existing_year_start_date:
1347 SELECT min(start_date)
1348 INTO existing_year_start_date
1349 FROM gl_periods
1350 WHERE
1351 period_set_name = x_period_set_name
1352 AND period_type = x_period_type
1353 AND period_year = x_period_year;
1354
1355 x_year_start_date := NVL(existing_year_start_date, x_start_date);
1356
1357 -- update tables if necessary
1358 IF ( x_operation = 'INSERT') THEN
1359 IF(x_start_date >= existing_year_start_date ) THEN
1360 RETURN;
1361 ELSE
1362 x_year_start_date := x_start_date;
1363 END IF;
1364 END IF;
1365
1366 UPDATE gl_periods
1367 SET year_start_date = x_year_start_date
1368 WHERE
1369 period_set_name = x_period_set_name
1370 AND period_type = x_period_type
1371 AND period_year = x_period_year;
1372
1373 UPDATE gl_period_statuses
1374 SET year_start_date = x_year_start_date
1375 WHERE
1376 period_type = x_period_type
1377 AND period_year = x_period_year
1378 AND ledger_id IN
1379 (SELECT ledger_id
1380 FROM gl_ledgers
1381 WHERE period_set_name = x_period_set_name);
1382
1383 END maintain_year_start_date;
1384
1385 FUNCTION period_set_with_AB
1386 (
1387 x_period_set_name VARCHAR2
1388 ) RETURN VARCHAR2 IS
1389 -- check does this period_set is used by LED with AB enabled
1390 CURSOR check_LED IS
1391 SELECT '1' FROM sys.dual
1392 WHERE EXISTS
1393 (SELECT 'Calendar used in LED with Average Balancing enabled'
1394 FROM gl_ledgers
1395 WHERE
1396 period_set_name = x_period_set_name
1397 AND enable_average_balances_flag= 'Y'
1398 );
1399 dummy VARCHAR2(1000);
1400 BEGIN
1401 -- check whether the current record inserts/updates with a new year
1402 OPEN check_LED;
1403 FETCH check_LED INTO dummy;
1404 IF (check_LED%NOTFOUND) THEN
1405 CLOSE check_LED;
1406 RETURN('NO');
1407 ELSE
1408 -- this is not a new year, exit
1409 CLOSE check_LED;
1410 RETURN('YES');
1411 END IF;
1412
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1416 fnd_message.set_token('PROCEDURE',
1417 'gl_periods_pkg.period_set_with_AB');
1418 RAISE;
1419
1420 END period_set_with_AB;
1421
1422 PROCEDURE maintain_AB_data
1423 (
1424 x_operation VARCHAR2,
1425 x_period_set_name VARCHAR2,
1426 x_period_type VARCHAR2,
1427 x_adjust_period_flag VARCHAR2,
1428 x_start_date_old DATE,
1429 x_start_date_new DATE,
1430 x_end_date DATE,
1431 x_period_name VARCHAR2,
1432 x_period_year_old NUMBER,
1433 x_period_year_new NUMBER,
1434 x_quarter_num_old NUMBER,
1435 x_quarter_num_new NUMBER,
1436 x_quarter_start_date IN OUT NOCOPY DATE,
1437 x_year_start_date IN OUT NOCOPY DATE,
1438 x_CREATION_DATE DATE,
1439 x_CREATED_BY NUMBER,
1440 x_LAST_UPDATE_DATE DATE,
1441 x_LAST_UPDATED_BY NUMBER,
1442 x_LAST_UPDATE_LOGIN NUMBER
1443 ) IS
1444 entered_year VARCHAR2(30);
1445 BEGIN
1446
1447 -- check if a period has more than 35 days
1448 IF( x_start_date_new - x_end_date + 1 > 35
1449 AND period_set_with_AB(x_period_set_name) = 'YES') THEN
1450 fnd_message.set_name('SQLGL', 'GL_AB_PERIOD_LASTS_M_35');
1451 app_exception.raise_exception;
1452 END IF;
1453
1454 -- populate GL_TRANSACTION_DATES table
1455 entered_year := TO_CHAR(x_end_date, 'YYYY');
1456 gl_transaction_dates_pkg.extend_transaction_calendars
1457 (
1458 x_period_set_name,
1459 x_period_type,
1460 entered_year,
1461 x_CREATION_DATE,
1462 x_CREATED_BY,
1463 x_LAST_UPDATE_DATE,
1464 x_LAST_UPDATED_BY,
1465 x_LAST_UPDATE_LOGIN
1466 );
1467 -- maintain GL_DATE_PERIOD_MAP table
1468 gl_date_period_map_pkg.maintain_date_period_map
1469 (
1470 x_period_set_name,
1471 x_period_type,
1472 x_adjust_period_flag,
1473 x_operation,
1474 x_start_date_new,
1475 x_end_date,
1476 x_period_name,
1477 x_CREATION_DATE,
1478 x_CREATED_BY,
1479 x_LAST_UPDATE_DATE,
1480 x_LAST_UPDATED_BY,
1481 x_LAST_UPDATE_LOGIN
1482 );
1483
1484 -- the following is logic for maintaining the quarter_start_date
1485 -- and year_start_date columns in the GL_PERIODS and GL_PERIOD_STATUSES
1486 -- IF the year has changed:
1487 -- do 1.(maintain_year_start_date) and
1488 -- 2.(maintain_quarter_start_date) for:
1489 -- new year
1490 -- old year
1491 -- new quarter
1492 -- old quarter
1493
1494 -- ELSIF the quarter has changed:
1495 -- do 1.
1496 -- do 2. for:
1497 -- new quarter
1498 -- old quarter
1499 -- ELSIF :entered_start_date has changed
1500 -- do 1. and 2. for current quarter and year
1501 -- END IF
1502 IF (x_period_year_new <> x_period_year_old) THEN
1503 maintain_year_start_date
1504 (
1505 x_period_set_name,
1506 x_period_type,
1507 x_period_year_new,
1508 x_start_date_new,
1509 x_operation,
1510 x_year_start_date
1511 );
1512 maintain_year_start_date
1513 (
1514 x_period_set_name,
1515 x_period_type,
1516 x_period_year_old,
1517 x_start_date_old,
1518 'UPDATE',
1519 x_year_start_date
1520 );
1521
1522 maintain_quarter_start_date
1523 (
1524 x_period_set_name,
1525 x_period_type,
1526 x_period_year_new,
1527 x_quarter_num_new,
1528 x_start_date_new,
1529 x_operation,
1530 x_quarter_start_date
1531 );
1532
1533 maintain_quarter_start_date
1534 (
1535 x_period_set_name,
1536 x_period_type,
1537 x_period_year_old,
1538 x_quarter_num_old,
1539 x_start_date_old,
1540 x_operation,
1541 x_quarter_start_date
1542 );
1543 ELSIF (x_quarter_num_new <> x_quarter_num_old) THEN
1544 maintain_year_start_date
1545 (
1546 x_period_set_name,
1547 x_period_type,
1548 x_period_year_new,
1549 x_start_date_new,
1550 x_operation,
1551 x_year_start_date
1552 );
1553 maintain_quarter_start_date
1554 (
1555 x_period_set_name,
1556 x_period_type,
1557 x_period_year_new,
1558 x_quarter_num_new,
1559 x_start_date_new,
1560 'UPDATE',
1561 x_quarter_start_date
1562 );
1563
1564 maintain_quarter_start_date
1565 (
1566 x_period_set_name,
1567 x_period_type,
1568 x_period_year_old,
1569 x_quarter_num_old,
1570 x_start_date_old,
1571 x_operation,
1572 x_quarter_start_date
1573 );
1574 ELSIF (x_start_date_new <> x_start_date_old) THEN
1575 maintain_year_start_date
1576 (
1577 x_period_set_name,
1578 x_period_type,
1579 x_period_year_new,
1580 x_start_date_new,
1581 x_operation,
1582 x_year_start_date
1583 );
1584 maintain_quarter_start_date
1585 (
1586 x_period_set_name,
1587 x_period_type,
1588 x_period_year_new,
1589 x_quarter_num_new,
1590 x_start_date_new,
1591 x_operation,
1592 x_quarter_start_date
1593 );
1594 END IF;
1595
1596 END maintain_AB_data;
1597
1598 PROCEDURE Insert_Period(Y_Rowid IN OUT NOCOPY VARCHAR2,
1599 Y_Period_Set_Name VARCHAR2,
1600 Y_Period_Name VARCHAR2,
1601 Y_Last_Update_Date DATE,
1602 Y_Last_Updated_By NUMBER,
1603 Y_Start_Date DATE,
1604 Y_End_Date DATE,
1605 Y_Period_Type VARCHAR2,
1606 Y_Period_Year NUMBER,
1607 Y_Period_Num NUMBER,
1608 Y_Quarter_Num NUMBER,
1609 Y_Entered_Period_Name VARCHAR2,
1610 Y_Creation_Date DATE,
1611 Y_Created_By NUMBER,
1612 Y_Last_Update_Login NUMBER,
1613 Y_Description VARCHAR2,
1614 Y_Attribute1 VARCHAR2,
1615 Y_Attribute2 VARCHAR2,
1616 Y_Attribute3 VARCHAR2,
1617 Y_Attribute4 VARCHAR2,
1618 Y_Attribute5 VARCHAR2,
1619 Y_Attribute6 VARCHAR2,
1620 Y_Attribute7 VARCHAR2,
1621 Y_Attribute8 VARCHAR2,
1622 Y_Context VARCHAR2,
1623 Y_Adjustment_Period_Flag VARCHAR2
1624 ) IS
1625
1626 CURSOR check_period_num IS
1627 SELECT number_per_fiscal_year
1628 FROM GL_PERIOD_TYPES
1629 WHERE period_type = Y_Period_Type;
1630
1631 v_number_per_fiscal_year NUMBER;
1632
1633 BEGIN
1634 -- Check Unique Combination
1635 GL_PERIODS_PKG.check_unique_num(calendar_name => Y_Period_Set_Name,
1636 period_type => Y_Period_Type,
1637 period_year => Y_Period_Year,
1638 period_num => Y_Period_Num,
1639 row_id => Y_Rowid);
1640
1641 -- Check if Period_Num is between 1 and the number of periods per year for the
1642 -- period type.
1643 OPEN check_period_num;
1644 FETCH check_period_num INTO v_number_per_fiscal_year;
1645
1646 IF check_period_num%NOTFOUND THEN
1647 CLOSE check_period_num;
1648 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
1649 fnd_message.set_token('VALUE', Y_Period_Type);
1650 fnd_message.set_token('ATTRIBUTE', 'PeriodType');
1651 app_exception.raise_exception;
1652 ELSE
1653 CLOSE check_period_num;
1654 END IF;
1655
1656 IF (Y_Period_Num < 1 OR Y_Period_Num > v_number_per_fiscal_year) THEN
1657 fnd_message.set_name('SQLGL', 'GL_PERIOD_NUMBER_LESS_THAN_MAX');
1658 fnd_message.set_token('MAX_NUM', v_number_per_fiscal_year);
1659 app_exception.raise_exception;
1660 END IF;
1661
1662 -- Check that the Entered Period Name does not contain any spaces.
1663 IF (INSTR(Y_Entered_Period_Name,' ') <> 0) THEN
1664 fnd_message.set_name('SQLGL', 'GL_CALENDAR_NAME_NO_SPACES');
1665 app_exception.raise_exception;
1666 END IF;
1667
1668 IF (instr(Y_period_name, '''') <> 0) THEN
1669 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_WITH_QUOTE');
1670 app_exception.raise_exception;
1671 END IF;
1672
1673 IF (instr(Y_entered_period_name, '''') <> 0) THEN
1674 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_WITH_QUOTE');
1675 app_exception.raise_exception;
1676 END IF;
1677
1678 -- Call the forms table handler.
1679 GL_PERIODS_PKG.Insert_Row(Y_Rowid,
1680 Y_Period_Set_Name,
1681 Y_Period_Name,
1682 Y_Last_Update_Date,
1683 Y_Last_Updated_By,
1684 Y_Start_Date,
1685 Y_End_Date,
1686 Y_Period_Type,
1687 Y_Period_Year,
1688 Y_Period_Num,
1689 Y_Quarter_Num,
1690 Y_Entered_Period_Name,
1691 Y_Creation_Date,
1692 Y_Created_By,
1693 Y_Last_Update_Login,
1694 Y_Description,
1695 Y_Attribute1,
1696 Y_Attribute2,
1697 Y_Attribute3,
1698 Y_Attribute4,
1699 Y_Attribute5,
1700 Y_Attribute6,
1701 Y_Attribute7,
1702 Y_Attribute8,
1703 Y_Context,
1704 Y_Adjustment_Period_Flag
1705 );
1706
1707 EXCEPTION
1708 WHEN app_exceptions.application_exception THEN
1709 RAISE;
1710 WHEN OTHERS THEN
1711 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1712 fnd_message.set_token('PROCEDURE', 'GL_PERIODS_PKG.Insert_Period');
1713 RAISE;
1714 END Insert_Period;
1715
1716 PROCEDURE Update_Period(Y_Rowid IN OUT NOCOPY VARCHAR2,
1717 Y_Period_Set_Name VARCHAR2,
1718 Y_Period_Name VARCHAR2,
1719 Y_Last_Update_Date DATE,
1720 Y_Last_Updated_By NUMBER,
1721 Y_Start_Date DATE,
1722 Y_End_Date DATE,
1723 Y_Period_Type VARCHAR2,
1724 Y_Period_Year NUMBER,
1725 Y_Period_Num NUMBER,
1726 Y_Quarter_Num NUMBER,
1727 Y_Entered_Period_Name VARCHAR2,
1728 Y_Last_Update_Login NUMBER,
1729 Y_Description VARCHAR2,
1730 Y_Attribute1 VARCHAR2,
1731 Y_Attribute2 VARCHAR2,
1732 Y_Attribute3 VARCHAR2,
1733 Y_Attribute4 VARCHAR2,
1734 Y_Attribute5 VARCHAR2,
1735 Y_Attribute6 VARCHAR2,
1736 Y_Attribute7 VARCHAR2,
1737 Y_Attribute8 VARCHAR2,
1738 Y_Context VARCHAR2,
1739 Y_Adjustment_Period_Flag VARCHAR2
1740 ) IS
1741
1742 CURSOR current_period IS
1743 SELECT start_date,
1744 end_date,
1745 period_type,
1746 period_year,
1747 period_num,
1748 quarter_num,
1749 entered_period_name,
1750 adjustment_period_flag,
1751 rowid
1752 FROM gl_periods
1753 WHERE period_set_name = Y_Period_Set_Name
1754 AND period_name = Y_Period_Name;
1755
1756 CURSOR check_period_num IS
1757 SELECT number_per_fiscal_year
1758 FROM GL_PERIOD_TYPES
1759 WHERE period_type = Y_Period_Type;
1760
1761 v_period_used_flag VARCHAR2(1);
1762 old_start_date DATE;
1763 old_end_date DATE;
1764 old_period_type VARCHAR2(15);
1765 old_period_year NUMBER(15);
1766 old_period_num NUMBER(15);
1767 old_quarter_num NUMBER(15);
1768 old_entered_period_name VARCHAR2(15);
1769 old_adjustment_period_flag VARCHAR2(1);
1770 v_number_per_fiscal_year NUMBER(15);
1771
1772 BEGIN
1773 -- Check if the fields are updateable.
1774 OPEN current_period;
1775 FETCH current_period INTO old_start_date,
1776 old_end_date,
1777 old_period_type,
1778 old_period_year,
1779 old_period_num,
1780 old_quarter_num,
1781 old_entered_period_name,
1782 old_adjustment_period_flag,
1783 Y_Rowid;
1784 CLOSE current_period;
1785
1786 -- Check if the period has been used. If the period has been used,
1787 -- the period year, quarter number, period number, start date,
1788 -- end date, entered period name, and the adjustment flag fields
1789 -- cannot be updated.
1790 IF (GL_PERIODS_PKG.check_period_used(Y_Rowid)) THEN
1791 v_period_used_flag := 'Y';
1792 ELSE
1793 v_period_used_flag := 'N';
1794 END IF;
1795
1796 -- If the period has been used, the period year, quarter number,
1797 -- period number, start date, end date, entered period name,
1798 -- and the adjustment flag fields cannot be updated.
1799 IF (v_period_used_flag = 'Y') THEN
1800 IF ((old_start_date <> Y_Start_Date) OR
1801 (old_end_date <> Y_End_Date) OR
1802 (old_period_type <> Y_Period_Type) OR
1803 (old_period_year <> Y_Period_Year) OR
1804 (old_period_num <> Y_Period_Num) OR
1805 (old_quarter_num <> Y_Quarter_Num) OR
1806 (old_entered_period_name <> Y_Entered_Period_Name) OR
1807 (old_adjustment_period_flag <> Y_Adjustment_Period_Flag)) THEN
1808 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_IN_USE');
1809 app_exception.raise_exception;
1810 END IF;
1811 END IF;
1812
1813 -- Cannot update the period type.
1814 IF (old_period_type <> Y_Period_Type) THEN
1815 fnd_message.set_name('SQLGL', 'GL_API_UPDATE_NOT_ALLOWED');
1816 fnd_message.set_token('ATTRIBUTE', 'UserPeriodType');
1817 app_exception.raise_exception;
1818 END IF;
1819
1820 -- Check Unique Combination.
1821 GL_PERIODS_PKG.check_unique_num(calendar_name => Y_Period_Set_Name,
1822 period_type => Y_Period_Type,
1823 period_year => Y_Period_Year,
1824 period_num => Y_Period_Num,
1825 row_id => Y_Rowid);
1826
1827 -- Check if Period_Num is between 1 and the number of periods per year for the
1828 -- period type.
1829 OPEN check_period_num;
1830 FETCH check_period_num INTO v_number_per_fiscal_year;
1831
1832 IF check_period_num%NOTFOUND THEN
1833 fnd_message.set_name('SQLGL', 'GL_API_VALUE_NOT_EXIST');
1834 fnd_message.set_token('VALUE', Y_Period_Type);
1835 fnd_message.set_token('ATTRIBUTE', 'PeriodType');
1836 app_exception.raise_exception;
1837 ELSE
1838 CLOSE check_period_num;
1839 END IF;
1840
1841 IF (Y_Period_Num < 1 OR Y_Period_Num > v_number_per_fiscal_year) THEN
1842 fnd_message.set_name('SQLGL', 'GL_PERIOD_NUMBER_LESS_THAN_MAX');
1843 fnd_message.set_token('MAX_NUM', v_number_per_fiscal_year);
1844 app_exception.raise_exception;
1845 END IF;
1846
1847 -- Check that the Entered Period Name does not contain any spaces.
1848 IF (INSTR(Y_Entered_Period_Name,' ') <> 0) THEN
1849 fnd_message.set_name('SQLGL', 'GL_CALENDAR_NAME_NO_SPACES');
1850 app_exception.raise_exception;
1851 END IF;
1852
1853 IF (instr(Y_period_name, '''') <> 0) THEN
1854 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_WITH_QUOTE');
1855 app_exception.raise_exception;
1856 END IF;
1857
1858 IF (instr(Y_entered_period_name, '''') <> 0) THEN
1859 fnd_message.set_name('SQLGL', 'GL_CALENDAR_PERIOD_WITH_QUOTE');
1860 app_exception.raise_exception;
1861 END IF;
1862
1863 -- Call the forms table handler.
1864 GL_PERIODS_PKG.Update_Row(Y_Rowid,
1865 Y_Period_Set_Name,
1866 Y_Period_Name,
1867 Y_Last_Update_Date,
1868 Y_Last_Updated_By,
1869 Y_Start_Date,
1870 Y_End_Date,
1871 Y_Period_Type,
1872 Y_Period_Year,
1873 Y_Period_Num,
1874 Y_Quarter_Num,
1875 Y_Entered_Period_Name,
1876 Y_Last_Update_Login,
1877 Y_Description,
1878 Y_Attribute1,
1879 Y_Attribute2,
1880 Y_Attribute3,
1881 Y_Attribute4,
1882 Y_Attribute5,
1883 Y_Attribute6,
1884 Y_Attribute7,
1885 Y_Attribute8,
1886 Y_Context,
1887 Y_Adjustment_Period_Flag
1888 );
1889
1890 EXCEPTION
1891 WHEN app_exceptions.application_exception THEN
1892 RAISE;
1893 WHEN OTHERS THEN
1894 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
1895 fnd_message.set_token('PROCEDURE', 'GL_PERIODS_PKG.Update_Period');
1896 RAISE;
1897 END Update_Period;
1898
1899 END gl_periods_pkg;