[Home] [Help]
PACKAGE BODY: APPS.GL_TRANSACTION_DATES_PKG
Source
1 PACKAGE BODY gl_transaction_dates_pkg AS
2 /* $Header: glitcdab.pls 120.2 2005/03/02 20:12:49 kvora ship $ */
3 --
4 -- PUBLIC FUNCTIONS
5 --
6 PROCEDURE extend_transaction_calendars
7 (
8 x_period_set_name VARCHAR2,
9 x_period_type VARCHAR2,
10 x_entered_year VARCHAR2,
11 x_CREATION_DATE DATE,
12 x_CREATED_BY NUMBER,
13 x_LAST_UPDATE_DATE DATE,
14 x_LAST_UPDATED_BY NUMBER,
15 x_LAST_UPDATE_LOGIN NUMBER
16 ) IS
17 CURSOR check_new_year IS
18 SELECT '1' FROM sys.dual
19 WHERE EXISTS
20 (SELECT 'Existing Year'
21 FROM gl_periods
22 WHERE
23 period_set_name = x_period_set_name
24 AND end_date BETWEEN
25 TO_DATE(x_entered_year || '/01/01', 'YYYY/MM/DD')
26 AND TO_DATE(x_entered_year || '/12/31', 'YYYY/MM/DD')
27 );
28 dummy VARCHAR2(1000);
29 new_entered_year VARCHAR2(30);
30 BEGIN
31 -- check whether the current record inserts/updates with a new year
32 OPEN check_new_year;
33 FETCH check_new_year INTO dummy;
34 IF (check_new_year%NOTFOUND) THEN
35 CLOSE check_new_year;
36 ELSE
37 -- this is not a new year, exit
38 CLOSE check_new_year;
39 RETURN;
40 END IF;
41
42 -- Lock GL_CONCURRENCY_CONTROL table in the row share mode using
43 -- CONCURRENCY_CLASS = EXTEND_TRANSACTION_CALENDAR. This is to
44 -- indicate that the GL_TRANSACTION_DATES table is locked for INSERT/UPDATE
45 -- but can be used for SELECT.
46
47 SELECT concurrency_class
48 INTO dummy
49 FROM gl_concurrency_control
50 WHERE concurrency_class = 'EXTEND_TRANSACTION_CALENDAR'
51 FOR UPDATE OF concurrency_class;
52
53
54
55 -- Insert records for all transaction calendars into the
56 -- GL_TRANSACTION_DATES table:
57 new_entered_year := x_entered_year;
58 INSERT INTO gl_transaction_dates
59 ( TRANSACTION_CALENDAR_ID,
60 TRANSACTION_DATE,
61 DAY_OF_WEEK,
62 BUSINESS_DAY_FLAG,
63 CREATION_DATE,
64 CREATED_BY,
65 LAST_UPDATE_DATE,
66 LAST_UPDATED_BY,
67 LAST_UPDATE_LOGIN
68 )
69 SELECT
70 tcal.transaction_calendar_id,
71 TO_DATE(new_entered_year || '/01/01', 'YYYY/MM/DD')+ cnt.multiplier-1,
72 DECODE
73 (
74 TO_CHAR(TO_DATE( new_entered_year || '/01/01', 'YYYY/MM/DD')+cnt.multiplier-1,'DY'),
75 TO_CHAR(TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'DY'), 'MON',
76 TO_CHAR(TO_DATE('1996/01/02', 'YYYY/MM/DD'), 'DY'), 'TUE',
77 TO_CHAR(TO_DATE('1996/01/03', 'YYYY/MM/DD'), 'DY'), 'WED',
78 TO_CHAR(TO_DATE('1996/01/04', 'YYYY/MM/DD'), 'DY'), 'THU',
79 TO_CHAR(TO_DATE('1996/01/05', 'YYYY/MM/DD'), 'DY'), 'FRI',
80 TO_CHAR(TO_DATE('1996/01/06', 'YYYY/MM/DD'), 'DY'), 'SAT',
81 TO_CHAR(TO_DATE('1996/01/07', 'YYYY/MM/DD'), 'DY'), 'SUN',
82 'NONE'
83 ),
84 DECODE
85 (
86 DECODE
87 (
88 TO_CHAR(TO_DATE( new_entered_year || '/01/01', 'YYYY/MM/DD')
89 + cnt.multiplier-1,'DY'),
90 TO_CHAR(TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'DY'), 'MON',
91 TO_CHAR(TO_DATE('1996/01/02', 'YYYY/MM/DD'), 'DY'), 'TUE',
92 TO_CHAR(TO_DATE('1996/01/03', 'YYYY/MM/DD'), 'DY'), 'WED',
93 TO_CHAR(TO_DATE('1996/01/04', 'YYYY/MM/DD'), 'DY'), 'THU',
94 TO_CHAR(TO_DATE('1996/01/05', 'YYYY/MM/DD'), 'DY'), 'FRI',
95 TO_CHAR(TO_DATE('1996/01/06', 'YYYY/MM/DD'), 'DY'), 'SAT',
96 TO_CHAR(TO_DATE('1996/01/07', 'YYYY/MM/DD'), 'DY'), 'SUN',
97 'NONE'
98 ),
99 'MON', tcal.mon_business_day_flag,
100 'TUE', tcal.tue_business_day_flag,
101 'WED', tcal.wed_business_day_flag,
102 'THU', tcal.thu_business_day_flag,
103 'FRI', tcal.fri_business_day_flag,
104 'SAT', tcal.sat_business_day_flag,
105 'SUN', tcal.sun_business_day_flag,
106 'Y'
107 ),
108 x_CREATION_DATE,
109 x_CREATED_BY,
110 x_LAST_UPDATE_DATE,
111 x_LAST_UPDATED_BY,
112 x_LAST_UPDATE_LOGIN
113 FROM gl_transaction_calendar tcal, gl_row_multipliers cnt
114 WHERE
115 cnt.multiplier <= TO_DATE(new_entered_year || '/12/31', 'YYYY/MM/DD') -
116 TO_DATE(new_entered_year || '/01/01', 'YYYY/MM/DD')+1
117 AND NOT EXISTS
118 (SELECT 'duplicate'
119 FROM gl_transaction_dates tdates
120 WHERE tdates.transaction_date =
121 to_date(new_entered_year || '/01/01',
122 'YYYY/MM/DD')+cnt.multiplier-1
123 AND tdates.transaction_calendar_id =
124 tcal.transaction_calendar_id);
125
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
130 fnd_message.set_token('PROCEDURE',
131 'gl_periods_pkg.extend_transaction_calendars');
132 RAISE;
133
134 END extend_transaction_calendars;
135
136 PROCEDURE insert_all_years_for_calendar
137 (
138 x_transaction_calendar_id NUMBER,
139 x_CREATION_DATE DATE,
140 x_CREATED_BY NUMBER,
141 x_LAST_UPDATE_DATE DATE,
142 x_LAST_UPDATED_BY NUMBER,
143 x_LAST_UPDATE_LOGIN NUMBER
144 ) IS
145
146 dummy VARCHAR2(1000);
147 earliest_year NUMBER;
148 latest_year NUMBER;
149 BEGIN
150 -- get the earliest year
151 SELECT TO_NUMBER(TO_CHAR(MIN(start_date), 'YYYY'))
152 INTO earliest_year
153 FROM gl_periods;
154
155 -- get the latest date
156 SELECT TO_NUMBER(TO_CHAR(MAX(end_date), 'YYYY'))
157 INTO latest_year
158 FROM gl_periods;
159
160 -- Lock GL_CONCURRENCY_CONTROL table in the row share mode using
161 -- CONCURRENCY_CLASS = EXTEND_TRANSACTION_CALENDAR. This is to
162 -- indicate that the GL_TRANSACTION_DATES table is locked for INSERT/UPDATE but
163 -- can be used for SELECT.
164 SELECT concurrency_class
165 INTO dummy
166 FROM gl_concurrency_control
167 WHERE concurrency_class = 'EXTEND_TRANSACTION_CALENDAR'
168 FOR UPDATE OF concurrency_class;
169
170 -- Insert records for all transaction calendars into the
171 -- GL_TRANSACTION_DATES table:
172 INSERT INTO gl_transaction_dates
173 (TRANSACTION_CALENDAR_ID,
174 TRANSACTION_DATE,
175 DAY_OF_WEEK,
176 BUSINESS_DAY_FLAG,
177 CREATION_DATE,
178 CREATED_BY,
179 LAST_UPDATE_DATE,
180 LAST_UPDATED_BY,
181 LAST_UPDATE_LOGIN
182 )
183 SELECT
184 x_transaction_calendar_id,
185 TO_DATE(TO_CHAR(yr.multiplier+1900) || '/01/01', 'YYYY/MM/DD')+ cnt.multiplier-1,
186 DECODE
187 (
188 TO_CHAR(TO_DATE( TO_CHAR(yr.multiplier+1900) || '/01/01', 'YYYY/MM/DD')
189 + cnt.multiplier-1,'DY'),
190 TO_CHAR(TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'DY'), 'MON',
191 TO_CHAR(TO_DATE('1996/01/02', 'YYYY/MM/DD'), 'DY'), 'TUE',
192 TO_CHAR(TO_DATE('1996/01/03', 'YYYY/MM/DD'), 'DY'), 'WED',
193 TO_CHAR(TO_DATE('1996/01/04', 'YYYY/MM/DD'), 'DY'), 'THU',
194 TO_CHAR(TO_DATE('1996/01/05', 'YYYY/MM/DD'), 'DY'), 'FRI',
195 TO_CHAR(TO_DATE('1996/01/06', 'YYYY/MM/DD'), 'DY'), 'SAT',
196 TO_CHAR(TO_DATE('1996/01/07', 'YYYY/MM/DD'), 'DY'), 'SUN',
197 'NONE'
198
199 ),
200 DECODE
201 (
202 DECODE
203 (
204 TO_CHAR(TO_DATE( TO_CHAR(yr.multiplier+1900) || '/01/01', 'YYYY/MM/DD')
205 + cnt.multiplier-1,'DY'),
206 TO_CHAR(TO_DATE('1996/01/01', 'YYYY/MM/DD'), 'DY'), 'MON',
207 TO_CHAR(TO_DATE('1996/01/02', 'YYYY/MM/DD'), 'DY'), 'TUE',
208 TO_CHAR(TO_DATE('1996/01/03', 'YYYY/MM/DD'), 'DY'), 'WED',
209 TO_CHAR(TO_DATE('1996/01/04', 'YYYY/MM/DD'), 'DY'), 'THU',
210 TO_CHAR(TO_DATE('1996/01/05', 'YYYY/MM/DD'), 'DY'), 'FRI',
211 TO_CHAR(TO_DATE('1996/01/06', 'YYYY/MM/DD'), 'DY'), 'SAT',
212 TO_CHAR(TO_DATE('1996/01/07', 'YYYY/MM/DD'), 'DY'), 'SUN',
213 'NONE'
214 ),
215 'MON', cal.mon_business_day_flag,
216 'TUE', cal.tue_business_day_flag,
217 'WED', cal.wed_business_day_flag,
218 'THU', cal.thu_business_day_flag,
219 'FRI', cal.fri_business_day_flag,
220 'SAT', cal.sat_business_day_flag,
221 'SUN', cal.sun_business_day_flag,
222 'Y'
223 ),
224 x_CREATION_DATE,
225 x_CREATED_BY,
226 x_LAST_UPDATE_DATE,
227 x_LAST_UPDATED_BY,
228 x_LAST_UPDATE_LOGIN
229 FROM gl_transaction_calendar cal, gl_row_multipliers yr, gl_row_multipliers cnt
230 WHERE
231 cal.transaction_calendar_id = x_transaction_calendar_id
232 AND cnt.multiplier <= TO_DATE(TO_CHAR(yr.multiplier+1900) || '/12/31', 'YYYY/MM/DD') -
233 TO_DATE(TO_CHAR(yr.multiplier+1900) || '/01/01', 'YYYY/MM/DD')+1
234 AND yr.multiplier >= earliest_year-1900
235 AND yr.multiplier <= latest_year-1900
236 AND NOT EXISTS
237 (SELECT 'duplicate'
238 FROM gl_transaction_dates tdates
239 WHERE tdates.transaction_date =
240 to_date(TO_CHAR(yr.multiplier+1900) || '/01/01',
241 'YYYY/MM/DD')+cnt.multiplier-1
242 AND tdates.transaction_calendar_id = x_transaction_calendar_id);
243 EXCEPTION
244 WHEN OTHERS THEN
245 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
246 fnd_message.set_token('PROCEDURE',
247 'gl_periods_pkg.insert_all_years_for_calendar');
248 RAISE;
249 END insert_all_years_for_calendar;
250
251
252 END;