[Home] [Help]
PACKAGE BODY: APPS.GL_PERIOD_TYPES_PKG
Source
1 PACKAGE BODY gl_period_types_pkg AS
2 /* $Header: gliprptb.pls 120.9 2005/05/05 01:18:12 kvora ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7
8 --
9 -- Procedure
10 -- select_row
11 -- Purpose
12 -- Used to select a particular period type row
13 -- History
14 -- 11-02-93 D. J. Ogg Created
15 -- Arguments
16 -- recinfo Various information about the row
17 -- Example
18 -- gl_period_types_pkg.select_row(recinfo)
19 -- Notes
20 --
21 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_period_types%ROWTYPE) IS
22 BEGIN
23 SELECT *
24 INTO recinfo
25 FROM gl_period_types
26 WHERE period_type = recinfo.period_type;
27 END SELECT_ROW;
28
29
30 --
31 -- PUBLIC FUNCTIONS
32 --
33
34 PROCEDURE select_columns(
35 x_period_type IN OUT NOCOPY VARCHAR2,
36 x_user_period_type IN OUT NOCOPY VARCHAR2,
37 x_year_type_in_name IN OUT NOCOPY VARCHAR2,
38 x_number_per_fiscal_year IN OUT NOCOPY NUMBER) IS
39
40 recinfo gl_period_types%ROWTYPE;
41
42 BEGIN
43 recinfo.period_type := x_period_type;
44
45 select_row(recinfo);
46
47 x_user_period_type := recinfo.user_period_type;
48 x_year_type_in_name := recinfo.year_type_in_name;
49 x_number_per_fiscal_year := recinfo.number_per_fiscal_year;
50 END select_columns;
51
52 PROCEDURE Check_Unique_User_Type(x_user_period_type VARCHAR2,
53 x_rowid VARCHAR2) IS
54 CURSOR check_dups is
55 SELECT 1
56 FROM GL_PERIOD_TYPES pt
57 WHERE pt.user_period_type =
58 check_unique_user_type.x_user_period_type
59 AND ( x_rowid is NULL
60 OR pt.rowid <> x_rowid );
61
62 dummy NUMBER;
63
64 BEGIN
65 OPEN check_dups;
66 FETCH check_dups INTO dummy;
67
68 IF check_dups%FOUND THEN
69 CLOSE check_dups;
70 fnd_message.set_name('SQLGL', 'GL_DUP_USER_PERIOD_TYPE');
71 app_exception.raise_exception;
72 END IF;
73
74 CLOSE check_dups;
75 EXCEPTION
76 WHEN app_exception.application_exception THEN RAISE;
77 WHEN OTHERS THEN
78 fnd_message.set_name('SQLGL','Unhandled Exception');
79 fnd_message.set_token('PROCEDURE', 'Check_Unique_User_Type');
80 RAISE;
81 END Check_Unique_User_Type;
82
83 PROCEDURE Check_Unique_Type(x_period_type VARCHAR2,
84 x_rowid VARCHAR2) IS
85 CURSOR chk_dups is
86 SELECT 1
87 FROM GL_PERIOD_TYPES pt
88 WHERE pt.period_type =
89 check_unique_type.x_period_type
90 AND ( x_rowid is NULL
91 OR pt.rowid <> x_rowid );
92
93 t_var NUMBER;
94
95 BEGIN
96 OPEN chk_dups;
97 FETCH chk_dups INTO t_var;
98
99 IF chk_dups%FOUND THEN
100 CLOSE chk_dups;
101 fnd_message.set_name('SQLGL', 'GL_DUP_UNIQUE_ID');
102 fnd_message.set_token('TAB_S', 'GL_PERIOD_TYPES_S');
103 app_exception.raise_exception;
104 END IF;
105
106 CLOSE chk_dups;
107 EXCEPTION
108 WHEN app_exception.application_exception THEN RAISE;
109 WHEN OTHERS THEN
110 fnd_message.set_name('SQLGL','Unhandled Exception');
111 fnd_message.set_token('PROCEDURE', 'Check_Unique_Type');
112 RAISE;
113 END Check_Unique_Type;
114
115 PROCEDURE Get_New_Id(x_period_type IN OUT NOCOPY VARCHAR2) IS
116
117 BEGIN
118 select GL_PERIOD_TYPES_S.NEXTVAL
119 into x_period_type
120 from dual;
121
122 IF (x_period_type is NULL) THEN
123 fnd_message.set_name('SQLGL', 'GL_SEQUENCE_NOT_FOUND');
124 fnd_message.set_token('TAB_S', 'GL_PERIOD_TYPES_S');
125 app_exception.raise_exception;
126 END IF;
127 EXCEPTION
128 WHEN app_exception.application_exception THEN RAISE;
129 WHEN OTHERS THEN
130 fnd_message.set_name('SQLGL','Unhandled Exception');
131 fnd_message.set_token('PROCEDURE', 'Get_New_Id');
132 RAISE;
133 END Get_New_Id;
134
135
136 PROCEDURE TRANSLATE_ROW(
137 x_period_type in varchar2,
138 x_user_period_type in varchar2,
139 x_description in varchar2,
140 x_owner in varchar2,
141 x_force_edits in varchar2 ) as
142
143 user_id number := 0;
144
145 Begin
146
147 if (X_OWNER = 'SEED') then
148 user_id := 1;
149 end if;
150
151 /* Update only if force_edits is 'Y' or it is seed data */
152 if ((x_force_edits = 'Y') OR (x_owner = 'SEED')) then
153 update gl_period_types
154 set
155 user_period_type = x_user_period_type,
156 description = x_description,
157 last_update_date = sysdate,
158 last_updated_by = user_id,
159 last_update_login = 0
160 where period_type = x_period_type
161 AND userenv('LANG') =
162 ( SELECT language_code
163 FROM FND_LANGUAGES
164 WHERE installed_flag = 'B' );
165 end if;
166
167 if (sql%notfound) then
168 null;
169 end if;
170
171 END TRANSLATE_ROW ;
172
173
174
175 PROCEDURE LOAD_ROW(
176 x_period_type in varchar2,
177 x_number_per_fiscal_year in number,
178 x_year_type_in_name in varchar2,
179 x_user_period_type in varchar2,
180 x_description in varchar2,
181 x_attribute1 in varchar2,
182 x_attribute2 in varchar2,
183 x_attribute3 in varchar2,
184 x_attribute4 in varchar2,
185 x_attribute5 in varchar2,
186 x_context in varchar2,
187 x_owner in varchar2,
188 x_force_edits in varchar2 default 'N'
189 ) AS
190 user_id number := 0;
191 v_creation_date date;
192 v_rowid rowid := null;
193 v_num_per_fiscal_yr number;
194 BEGIN
195 -- validate input parameters
196 if ( x_period_type is null) then
197
198 fnd_message.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
199 app_exception.raise_exception;
200 end if;
201
202 if (X_OWNER = 'SEED') then
203 user_id := 1;
204 end if;
205 begin
206
207 /*Check if the row exists in the database. If it does, retrieves
208 the creation date for update_row. */
209 /* bug 2407006: if it is an existing row, use the original
210 number_per_fiscal_year to update. */
211
212 select creation_date,rowid,number_per_fiscal_year
213 into v_creation_date, v_rowid, v_num_per_fiscal_yr
214 from gl_period_types
215 where period_type = x_period_type;
216
217 if ((x_force_edits = 'Y') OR (x_owner = 'SEED')) THEN
218 -- update row if present
219 gl_period_types_pkg.UPDATE_ROW(
220 x_row_id => v_rowid,
221 x_period_type => x_period_type,
222 x_number_per_fiscal_year => v_num_per_fiscal_yr,
223 x_year_type_in_name => x_year_type_in_name,
224 x_user_period_type => x_user_period_type,
225 x_description => x_description,
226 x_attribute1 => x_attribute1,
227 x_attribute2 => x_attribute2,
228 x_attribute3 => x_attribute3,
229 x_attribute4 => x_attribute4,
230 x_attribute5 => x_attribute5,
231 x_context => x_context,
232 x_last_update_date => sysdate,
233 x_last_updated_by => user_id,
234 x_last_update_login => 0,
235 x_creation_date => v_creation_date
236 );
237 end if;
238
239 exception
240 when NO_DATA_FOUND then
241 gl_period_types_pkg.INSERT_ROW(
242 x_row_id => v_rowid,
243 x_period_type => x_period_type,
244 x_number_per_fiscal_year => x_number_per_fiscal_year,
245 x_year_type_in_name => x_year_type_in_name,
246 x_user_period_type => x_user_period_type,
247 x_description => x_description,
248 x_attribute1 => x_attribute1,
249 x_attribute2 => x_attribute2,
250 x_attribute3 => x_attribute3,
251 x_attribute4 => x_attribute4,
252 x_attribute5 => x_attribute5,
253 x_context => x_context,
254 x_last_update_date => sysdate,
255 x_last_updated_by => user_id,
256 x_last_update_login => 0,
257 x_creation_date => sysdate,
258 x_created_by => user_id
259 );
260
261 end ;
262
263
264 END LOAD_ROW;
265
266 PROCEDURE UPDATE_ROW(
267 x_row_id in varchar2,
268 x_period_type in varchar2,
269 x_number_per_fiscal_year in number,
270 x_year_type_in_name in varchar2,
271 x_user_period_type in varchar2,
272 x_description in varchar2,
273 x_attribute1 in varchar2,
274 x_attribute2 in varchar2,
275 x_attribute3 in varchar2,
276 x_attribute4 in varchar2,
277 x_attribute5 in varchar2,
278 x_context in varchar2,
279 x_last_update_date in date,
280 x_last_updated_by in number,
281 x_last_update_login in number ,
282 x_creation_date in date
283 ) AS
284 BEGIN
285 Update gl_period_types
286 set period_type = x_period_type,
287 number_per_fiscal_year = x_number_per_fiscal_year,
288 year_type_in_name = x_year_type_in_name,
289 user_period_type = x_user_period_type,
290 description = x_description,
291 attribute1 = x_attribute1,
292 attribute2 = x_attribute2,
293 attribute3 = x_attribute3,
294 attribute4 = x_attribute4,
295 attribute5 = x_attribute5,
296 context = x_context,
297 last_update_date = x_last_update_date,
298 last_updated_by = x_last_updated_by,
299 last_update_login = x_last_update_login,
300 creation_date = x_creation_date
301 where period_type = x_period_type;
302
303 if (sql%notfound) then
304 raise no_data_found;
305 end if;
306
307 END UPDATE_ROW;
308
309 PROCEDURE INSERT_ROW(
310 x_row_id in out NOCOPY varchar2,
311 x_period_type in varchar2,
312 x_number_per_fiscal_year in number,
313 x_year_type_in_name in varchar2,
314 x_user_period_type in varchar2,
315 x_description in varchar2,
316 x_attribute1 in varchar2,
317 x_attribute2 in varchar2,
318 x_attribute3 in varchar2,
319 x_attribute4 in varchar2,
320 x_attribute5 in varchar2,
321 x_context in varchar2,
322 x_last_update_date in date,
323 x_last_updated_by in number,
324 x_last_update_login in number ,
325 x_creation_date in date,
326 x_created_by in number
327 )AS
328
329 cursor period_type_row is
330 select rowid
331 from gl_period_types
332 where period_type = x_period_type;
333 BEGIN
334 if (x_period_type is NULL) then
335 raise no_data_found;
336 end if;
337
338 INSERT INTO GL_PERIOD_TYPES(
339 period_type,
340 number_per_fiscal_year ,
341 year_type_in_name ,
342 user_period_type,
343 period_type_id,
344 description,
345 attribute1,
346 attribute2,
347 attribute3,
348 attribute4,
349 attribute5,
350 context,
351 last_update_date,
352 last_updated_by,
353 last_update_login ,
354 creation_date,
355 created_by )
356 select
357 x_period_type,
358 x_number_per_fiscal_year ,
359 x_year_type_in_name ,
360 x_user_period_type ,
361 gl_period_types_s.nextval,
362 x_description ,
363 x_attribute1,
364 x_attribute2,
365 x_attribute3,
366 x_attribute4,
367 x_attribute5,
368 x_context,
369 x_last_update_date ,
370 x_last_updated_by ,
371 x_last_update_login,
372 x_creation_date,
373 x_created_by
374 from dual
375 where not exists
376 ( select null
377 from gl_period_types B
378 where B.period_type = x_period_type );
379
380
381 open period_type_row;
382 fetch period_type_row into x_row_id;
383 if (period_type_row%notfound) then
384 close period_type_row;
385 raise no_data_found;
386 end if;
387 close period_type_row;
388
389 END INSERT_ROW;
390
391 /* Called from iSpeed calendar api */
392
393 procedure checkUpdate(
394 mReturnValue OUT NOCOPY VARCHAR2
395 ,mPeriodType IN VARCHAR2
396 ,mNumberPerFiscalYear IN VARCHAR2
397 ,mYearTypeInName IN VARCHAR2
398 )
399 AS
400
401 l_number_per_fiscal_year Number(15) ;
402 l_year_type_in_name Varchar2(1);
403
404 Cursor c1 is select number_per_fiscal_year,
405 year_type_in_name
406 from gl_period_types
407 where period_type = mPeriodType;
408 Begin
409 mReturnValue := 0;
410 open c1;
411 fetch c1 into l_number_per_fiscal_year,l_year_type_in_name;
412 close c1;
413 if ( ( l_number_per_fiscal_year = mNumberPerFiscalYear) AND
414 ( mYearTypeInName = l_year_type_in_name )) Then
415 mReturnValue := 1;
416 else
417 mReturnValue := 0;
418 end if;
419
420 End checkUpdate;
421
422 END gl_period_types_pkg;