1 PACKAGE BODY gl_encumbrance_types_pkg AS
2 /* $Header: glietdfb.pls 120.8 2006/02/06 18:29:21 kvora ship $ */
3
4 ---
5 --- PRIVATE FUNCTIONS
6 ---
7
8 --
9 -- Procedure
10 -- select_row
11 -- Purpose
12 -- Gets the row from gl_encumbrance_types associated with
13 -- the given encumbrance.
14 -- History
15 -- 01-NOV-94 D J Ogg Created.
16 -- Arguments
17 -- recinfo A row from gl_encumbrance_types
18 -- Example
19 -- gl_encumbrance_types_pkg.select_row(recinfo);
20 -- Notes
21 --
22 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_encumbrance_types%ROWTYPE ) IS
23 BEGIN
24 SELECT *
25 INTO recinfo
26 FROM gl_encumbrance_types
27 WHERE encumbrance_type_id = recinfo.encumbrance_type_id;
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 RETURN;
31 WHEN app_exceptions.application_exception THEN
32 RAISE;
33 WHEN OTHERS THEN
34 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
35 fnd_message.set_token('PROCEDURE',
36 'gl_encumbrance_types.select_row');
37 RAISE;
38 END select_row;
39
40
41 --
42 -- PUBLIC FUNCTIONS
43 --
44
45 PROCEDURE check_unique_name(x_name VARCHAR2,
46 x_row_id VARCHAR2) IS
47 CURSOR chk_duplicates IS
48 SELECT 'Duplicate'
49 FROM GL_ENCUMBRANCE_TYPES et
50 WHERE et.encumbrance_type = x_name
51 AND ( (x_row_id is null)
52 OR (et.rowid <> x_row_id));
53 dummy VARCHAR2(100);
54 BEGIN
55 OPEN chk_duplicates;
56 FETCH chk_duplicates INTO dummy;
57
58 IF chk_duplicates%FOUND THEN
59 CLOSE chk_duplicates;
60 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_ENC_TYPE');
61 app_exception.raise_exception;
62 END IF;
63
64 CLOSE chk_duplicates;
65
66 EXCEPTION
67 WHEN app_exceptions.application_exception THEN
68 RAISE;
69 WHEN OTHERS THEN
70 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
71 fnd_message.set_token('PROCEDURE', 'gl_encumbrance_types_pkg.check_unique_name');
72 RAISE;
73 END check_unique_name;
74
75 PROCEDURE check_unique_id(x_etid NUMBER,
76 x_row_id VARCHAR2) IS
77 CURSOR chk_duplicate_ids IS
78 SELECT 'Duplicate'
79 FROM GL_ENCUMBRANCE_TYPES et
80 WHERE et.encumbrance_type_id = x_etid
81 AND ( (x_row_id is null)
82 OR (et.rowid <> x_row_id));
83 dummy VARCHAR2(100);
84 BEGIN
85 OPEN chk_duplicate_ids;
86 FETCH chk_duplicate_ids INTO dummy;
87
88 IF chk_duplicate_ids%FOUND THEN
89 CLOSE chk_duplicate_ids;
90 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_ENC_TYPE');
91 app_exception.raise_exception;
92 END IF;
93
94 CLOSE chk_duplicate_ids;
95
96 EXCEPTION
97 WHEN app_exceptions.application_exception THEN
98 RAISE;
99 WHEN OTHERS THEN
100 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
101 fnd_message.set_token('PROCEDURE', 'gl_encumbrance_types_pkg.check_unique_id');
102 RAISE;
103 END check_unique_id;
104
105
106 PROCEDURE get_unique_id (x_encumbrance_type_id IN OUT NOCOPY NUMBER) IS
107 CURSOR get_new_id IS
108 SELECT gl_encumbrance_types_s.NEXTVAL
109 FROM dual;
110 new_id number;
111 BEGIN
112 OPEN get_new_id;
113 FETCH get_new_id INTO x_encumbrance_type_id;
114
115 IF get_new_id%FOUND THEN
116 CLOSE get_new_id;
117 ELSE
118 CLOSE get_new_id;
119 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
120 fnd_message.set_token('SEQUENCE', 'GL_ENCUMBRANCE_TYPES_S');
121 app_exception.raise_exception;
122 END IF;
123
124 EXCEPTION
125 WHEN app_exceptions.application_exception THEN
126 RAISE;
127 WHEN OTHERS THEN
128 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
129 fnd_message.set_token('PROCEDURE', 'gl_encumbrance_types_pkg.get_unique_id');
130 RAISE;
131 END get_unique_id;
132
133
134 PROCEDURE select_columns(
135 x_encumbrance_type_id NUMBER,
136 x_encumbrance_type IN OUT NOCOPY VARCHAR2 ) IS
137
138 recinfo gl_encumbrance_types%ROWTYPE;
139
140 BEGIN
141 recinfo.encumbrance_type_id := x_encumbrance_type_id;
142 select_row( recinfo );
143 x_encumbrance_type := recinfo.encumbrance_type;
144
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147 RETURN;
148 WHEN app_exceptions.application_exception THEN
149 RAISE;
150 WHEN OTHERS THEN
151 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
152 fnd_message.set_token('PROCEDURE',
153 'gl_encumbrance_types.select_columns');
154 RAISE;
155 END select_columns;
156
157
158 FUNCTION get_enc_type_all(enc_type IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
159 CURSOR get_enc_type IS
160 SELECT meaning
161 FROM gl_lookups
162 WHERE lookup_type = 'LITERAL' AND lookup_code = 'ALL';
163
164 BEGIN
165 OPEN get_enc_type;
166 FETCH get_enc_type INTO enc_type;
167
168 IF get_enc_type%FOUND THEN
169 CLOSE get_enc_type;
170 return ( TRUE );
171 ELSE
172 CLOSE get_enc_type;
173 return ( FALSE );
174 END IF;
175
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178 RETURN ( FALSE );
179 WHEN app_exceptions.application_exception THEN
180 RAISE;
181 WHEN OTHERS THEN
182 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
183 fnd_message.set_token('FUNCTION',
184 'gl_encumbrance_types.get_enc_type_all');
185 RAISE;
186 END get_enc_type_all;
187
188 PROCEDURE insert_row(
189 x_rowid IN OUT NOCOPY VARCHAR2,
190 x_encumbrance_type_id IN OUT NOCOPY NUMBER,
191 x_encumbrance_type_key IN OUT NOCOPY VARCHAR2,
192 x_encumbrance_type IN VARCHAR2,
193 x_enabled_flag IN VARCHAR2,
194 x_last_update_date IN DATE,
195 x_last_updated_by IN NUMBER,
196 x_creation_date IN DATE,
197 x_created_by IN NUMBER,
198 x_last_update_login IN NUMBER,
199 x_description IN VARCHAR2) IS
200
201 CURSOR C IS SELECT rowid FROM gl_encumbrance_types
202 WHERE encumbrance_type = x_encumbrance_type;
203
204 current_sequence NUMBER := 0;
205 BEGIN
206 IF (x_encumbrance_type_id IS NULL) THEN
207 SELECT gl_encumbrance_types_s.nextval
208 INTO x_encumbrance_type_id
209 FROM dual;
210 END IF;
211
212 IF (x_encumbrance_type_key IS NULL) THEN
213 x_encumbrance_type_key := to_char(x_encumbrance_type_id);
214 END IF;
215
216 INSERT INTO gl_encumbrance_types(
217 encumbrance_type_id,
218 encumbrance_type_key,
219 encumbrance_type,
220 enabled_flag,
221 last_update_date,
222 last_updated_by,
223 creation_date,
224 created_by,
225 last_update_login,
226 description
227 )
228 VALUES(x_encumbrance_type_id,
229 x_encumbrance_type_key,
230 x_encumbrance_type,
231 x_enabled_flag,
232 x_last_update_date,
233 x_last_updated_by,
234 x_creation_date,
235 x_created_by,
236 x_last_update_login,
237 x_description);
238
239 OPEN C;
240 FETCH C INTO x_Rowid;
241 if (C%NOTFOUND) then
242 CLOSE C;
243 Raise NO_DATA_FOUND;
244 end if;
245 CLOSE C;
246
247 END insert_row;
248
249
250 PROCEDURE update_row(
251 x_encumbrance_type_id IN NUMBER,
252 x_encumbrance_type IN VARCHAR2,
253 x_enabled_flag IN VARCHAR2,
254 x_last_update_date IN DATE,
255 x_last_updated_by IN NUMBER,
256 x_last_update_login IN NUMBER,
257 x_description IN VARCHAR2) IS
258
259 BEGIN
260 UPDATE gl_encumbrance_types
261 SET encumbrance_type = x_encumbrance_type,
262 enabled_flag = x_enabled_flag,
263 last_update_date = x_last_update_date,
264 last_updated_by = x_last_updated_by,
265 last_update_login = x_last_update_login,
266 description = x_description
267 WHERE encumbrance_type_id = x_encumbrance_type_id;
268
269 if (SQL%NOTFOUND) then
270 Raise NO_DATA_FOUND;
271 end if;
272 END update_row;
273
274 PROCEDURE load_row(
275 y_encumbrance_type_key IN VARCHAR2,
276 y_owner IN VARCHAR2,
277 y_encumbrance_type IN VARCHAR2,
278 y_description IN VARCHAR2,
279 y_enabled_flag IN VARCHAR2,
280 y_force_edits IN VARCHAR2 default 'N') IS
281
282 l_user_id NUMBER := 0;
283 l_encumbrance_type_id NUMBER;
284 l_rowid ROWID := NULL;
285 l_encumbrance_type_key VARCHAR2(30);
286 BEGIN
287
288 l_encumbrance_type_key := y_encumbrance_type_key;
289
290 IF y_owner = 'SEED' THEN
291 l_user_id := 1;
292 END IF;
293
294 -- validate input parameters
295 IF (l_encumbrance_type_key IS NULL OR
296 y_encumbrance_type IS NULL OR
297 y_enabled_flag IS NULL) THEN
298
299 FND_MESSAGE.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
300 APP_EXCEPTION.raise_exception;
301 END IF;
302
303 BEGIN
304
305 /* Update only if force_edits is 'Y' or it is seed data */
306 IF ((y_force_edits = 'Y') OR (y_owner = 'SEED')) THEN
307
308 SELECT encumbrance_type_id
309 INTO l_encumbrance_type_id
310 FROM gl_encumbrance_types
311 WHERE encumbrance_type_key = l_encumbrance_type_key;
312
313 -- update row if present
314 GL_ENCUMBRANCE_TYPES_PKG.update_row(
315 x_encumbrance_type_id => l_encumbrance_type_id,
316 x_encumbrance_type => y_encumbrance_type,
317 x_enabled_flag => y_enabled_flag,
318 x_last_update_date => sysdate,
319 x_last_updated_by => l_user_id,
320 x_last_update_login => 0,
321 x_description => y_description);
322 END IF;
323
324 EXCEPTION WHEN NO_DATA_FOUND THEN
325 GL_ENCUMBRANCE_TYPES_PKG.insert_row(
326 x_rowid => l_rowid,
327 x_encumbrance_type_id => l_encumbrance_type_id,
328 x_encumbrance_type_key => l_encumbrance_type_key,
329 x_encumbrance_type => y_encumbrance_type,
330 x_enabled_flag => y_enabled_flag,
331 x_last_update_date => sysdate,
332 x_last_updated_by => l_user_id,
333 x_creation_date => sysdate,
334 x_created_by => l_user_id,
335 x_last_update_login => 0,
336 x_description => y_description);
337 END;
338 END load_row;
339
340 PROCEDURE Translate_Row(
341 x_encumbrance_type_key IN VARCHAR2,
342 x_owner IN VARCHAR2,
343 x_encumbrance_type IN VARCHAR2,
344 x_description IN VARCHAR2,
345 x_enabled_flag IN VARCHAR2,
346 x_force_edits IN VARCHAR2 default 'N') IS
347
348 l_user_id NUMBER := 0;
349
350 BEGIN
351
352 IF x_owner = 'SEED' THEN
353 l_user_id := 1;
354 END IF;
355
356 /* Update only if force_edits is 'Y' or it is seed data */
357 IF ((x_force_edits = 'Y') OR (x_owner = 'SEED')) THEN
358
359 UPDATE GL_ENCUMBRANCE_TYPES
360 SET encumbrance_type = x_encumbrance_type,
361 description = x_description,
362 last_update_date = sysdate,
363 last_updated_by = l_user_id,
364 last_update_login = 0
365 WHERE encumbrance_type_key = x_encumbrance_type_key
366 AND userenv('LANG') =
367 ( SELECT language_code
368 FROM FND_LANGUAGES
369 WHERE installed_flag = 'B' );
370 END IF;
371 /*If base language is not set to the language being uploaded, then do nothing.*/
372 IF SQL%NOTFOUND THEN
373 NULL;
374 END IF;
375
376 END translate_row;
377
378 END gl_encumbrance_types_pkg;
[Home] [Help]