[Home] [Help]
PACKAGE BODY: APPS.GL_DAILY_CONV_TYPES_PKG
Source
1 PACKAGE BODY GL_DAILY_CONV_TYPES_PKG AS
2 /* $Header: glirtctb.pls 120.7 2005/05/05 01:20:52 kvora ship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7
8 --
9 -- Procedure
10 -- select_row
11 -- Purpose
12 -- Used to select a particular source row
13 -- History
14 -- 11-02-93 D. J. Ogg Created
15 -- Arguments
16 -- recinfo Various information about the row
17 -- Example
18 -- gl_daily_conv_types_pkg.select_row(recinfo)
19 -- Notes
20 --
21 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_daily_conversion_types%ROWTYPE) IS
22 BEGIN
23 SELECT *
24 INTO recinfo
25 FROM gl_daily_conversion_types
26 WHERE conversion_type = recinfo.conversion_type;
27 END SELECT_ROW;
28
29 --
30 -- PUBLIC FUNCTIONS
31 --
32
33 PROCEDURE select_columns(
34 x_conversion_type VARCHAR2,
35 x_user_conversion_type IN OUT NOCOPY VARCHAR2) IS
36
37 recinfo gl_daily_conversion_types%ROWTYPE;
38
39 BEGIN
40 recinfo.conversion_type := x_conversion_type;
41
42 select_row(recinfo);
43
44 x_user_conversion_type := recinfo.user_conversion_type;
45 END select_columns;
46
47 PROCEDURE Check_Unique_User_Type(user_conversion_type VARCHAR2,
48 x_rowid VARCHAR2) IS
49
50 CURSOR check_dups is
51 SELECT 1
52 FROM GL_DAILY_CONVERSION_TYPES dct
53 WHERE dct.user_conversion_type =
54 check_unique_user_type.user_conversion_type
55 AND ( x_rowid is NULL
56 OR dct.rowid <> x_rowid );
57
58 dummy NUMBER;
59
60 BEGIN
61 OPEN check_dups;
62 FETCH check_dups INTO dummy;
63
64 IF check_dups%FOUND THEN
65 CLOSE check_dups;
66 fnd_message.set_name('SQLGL', 'GL_DUP_USER_CONVERSION_TYPE');
67 app_exception.raise_exception;
68 END IF;
69
70 CLOSE check_dups;
71 EXCEPTION
72 WHEN app_exception.application_exception THEN RAISE;
73 WHEN OTHERS THEN
74 fnd_message.set_name('SQLGL','Unhandled Exception');
75 fnd_message.set_token('PROCEDURE', 'Check_Unique_User_Type');
76 RAISE;
77 END Check_Unique_User_Type;
78
79 PROCEDURE Check_Unique_Type(conversion_type VARCHAR2,
80 x_rowid VARCHAR2) IS
81
82 CURSOR chk_dups is
83 SELECT 1
84 FROM GL_DAILY_CONVERSION_TYPES dct
85 WHERE dct.conversion_type =
86 check_unique_type.conversion_type
87 AND ( x_rowid is NULL
88 OR dct.rowid <> x_rowid );
89
90 t_var NUMBER;
91
92 BEGIN
93 OPEN chk_dups;
94 FETCH chk_dups INTO t_var;
95
96 IF chk_dups%FOUND THEN
97 CLOSE chk_dups;
98 fnd_message.set_name('SQLGL', 'GL_DUP_UNIQUE_ID');
99 fnd_message.set_token('TAB_S', 'GL_DAILY_CONVERSION_TYPES_S');
100 app_exception.raise_exception;
101 END IF;
102
103 CLOSE chk_dups;
104 EXCEPTION
105 WHEN app_exception.application_exception THEN RAISE;
106 WHEN OTHERS THEN
107 fnd_message.set_name('SQLGL','Unhandled Exception');
108 fnd_message.set_token('PROCEDURE', 'Check_Unique_Type');
109 RAISE;
110 END Check_Unique_Type;
111
112 PROCEDURE Get_New_Id(next_val IN OUT NOCOPY VARCHAR2) IS
113
114 BEGIN
115 select GL_DAILY_CONVERSION_TYPES_S.NEXTVAL
116 into next_val
117 from dual;
118
119 IF (next_val is NULL) THEN
120 fnd_message.set_name('SQLGL', 'GL_SEQUENCE_NOT_FOUND');
121 fnd_message.set_token('TAB_S', 'GL_DAILY_CONVERSION_TYPES_S');
122 app_exception.raise_exception;
123 END IF;
124 EXCEPTION
125 WHEN app_exception.application_exception THEN RAISE;
126 WHEN OTHERS THEN
127 fnd_message.set_name('SQLGL','Unhandled Exception');
128 fnd_message.set_token('PROCEDURE', 'Get_New_Id');
129 RAISE;
130 END Get_New_Id;
131
132 /* Added X_Security_Flag for Definition Access Sets Project */
133 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
134 X_Conversion_Type VARCHAR2,
135 X_User_Conversion_Type VARCHAR2,
136 X_Last_Update_Date DATE,
137 X_Last_Updated_By NUMBER,
138 X_Creation_Date DATE,
139 X_Created_By NUMBER,
140 X_Last_Update_Login NUMBER,
141 X_Description VARCHAR2,
142 X_Attribute1 VARCHAR2,
143 X_Attribute2 VARCHAR2,
144 X_Attribute3 VARCHAR2,
145 X_Attribute4 VARCHAR2,
146 X_Attribute5 VARCHAR2,
147 X_Attribute6 VARCHAR2,
148 X_Attribute7 VARCHAR2,
149 X_Attribute8 VARCHAR2,
150 X_Attribute9 VARCHAR2,
151 X_Attribute10 VARCHAR2,
152 X_Attribute11 VARCHAR2,
153 X_Attribute12 VARCHAR2,
154 X_Attribute13 VARCHAR2,
155 X_Attribute14 VARCHAR2,
156 X_Attribute15 VARCHAR2,
157 X_Context VARCHAR2,
158 X_Security_Flag VARCHAR2) IS
159 CURSOR C IS SELECT rowid
160 FROM GL_DAILY_CONVERSION_TYPES
161 WHERE conversion_type = X_Conversion_Type
162 AND user_conversion_type = X_User_Conversion_Type;
163
164 BEGIN
165
166 INSERT INTO GL_DAILY_CONVERSION_TYPES(
167 conversion_type,
168 user_conversion_type,
169 last_update_date,
170 last_updated_by,
171 creation_date,
172 created_by,
173 last_update_login,
174 description,
175 attribute1,
176 attribute2,
177 attribute3,
178 attribute4,
179 attribute5,
180 attribute6,
181 attribute7,
182 attribute8,
183 attribute9,
184 attribute10,
185 attribute11,
186 attribute12,
187 attribute13,
188 attribute14,
189 attribute15,
190 context,
191 security_flag)
192 VALUES(
193 X_Conversion_Type,
194 X_User_Conversion_Type,
195 X_Last_Update_Date,
196 X_Last_Updated_By,
197 X_Creation_Date,
198 X_Created_By,
199 X_Last_Update_Login,
200 X_Description,
201 X_Attribute1,
202 X_Attribute2,
203 X_Attribute3,
204 X_Attribute4,
205 X_Attribute5,
206 X_Attribute6,
207 X_Attribute7,
208 X_Attribute8,
209 X_Attribute9,
210 X_Attribute10,
211 X_Attribute11,
212 X_Attribute12,
213 X_Attribute13,
214 X_Attribute14,
215 X_Attribute15,
216 X_Context,
217 X_Security_Flag );
218
219 OPEN C;
220 FETCH C INTO X_Rowid;
221
222 if (C%NOTFOUND) then
223 CLOSE C;
224 RAISE NO_DATA_FOUND;
225 end if;
226 CLOSE C;
227
228 EXCEPTION
229 WHEN app_exceptions.application_exception THEN
230 RAISE;
231 WHEN OTHERS THEN
232 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
233 fnd_message.set_token('PROCEDURE',
234 'GL_DAILY_CONV_TYPES_PKG.Insert_Row');
235 RAISE;
236
237 END Insert_Row;
238
239 /* Added for Definition Access Sets Project */
240 PROCEDURE lock_row(X_Rowid IN OUT NOCOPY VARCHAR2,
241 X_Conversion_Type VARCHAR2,
242 X_User_Conversion_Type VARCHAR2,
243 X_Description VARCHAR2,
244 X_Attribute1 VARCHAR2,
245 X_Attribute2 VARCHAR2,
246 X_Attribute3 VARCHAR2,
247 X_Attribute4 VARCHAR2,
248 X_Attribute5 VARCHAR2,
249 X_Attribute6 VARCHAR2,
250 X_Attribute7 VARCHAR2,
251 X_Attribute8 VARCHAR2,
252 X_Attribute9 VARCHAR2,
253 X_Attribute10 VARCHAR2,
254 X_Attribute11 VARCHAR2,
255 X_Attribute12 VARCHAR2,
256 X_Attribute13 VARCHAR2,
257 X_Attribute14 VARCHAR2,
258 X_Attribute15 VARCHAR2,
259 X_Context VARCHAR2,
260 X_Security_Flag VARCHAR2) IS
261 CURSOR C IS SELECT
262 conversion_type,
263 user_conversion_type,
264 description,
265 attribute1,
266 attribute2,
267 attribute3,
268 attribute4,
269 attribute5,
270 attribute6,
271 attribute7,
272 attribute8,
273 attribute9,
274 attribute10,
275 attribute11,
276 attribute12,
277 attribute13,
278 attribute14,
279 attribute15,
280 context,
281 security_flag
282 FROM GL_DAILY_CONVERSION_TYPES
283 WHERE ROWID = X_Rowid
284 FOR UPDATE OF conversion_type NOWAIT;
285 recinfo C%ROWTYPE;
286
287 BEGIN
288 OPEN C;
289 FETCH C INTO recinfo;
290 IF (C%NOTFOUND) THEN
291 CLOSE C;
292 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
293 app_exception.raise_exception;
294 END IF;
295 CLOSE C;
296
297 IF (
298 (recinfo.conversion_type = x_conversion_type)
299 AND (recinfo.user_conversion_type = x_user_conversion_type)
300 AND (recinfo.security_flag = x_security_flag)
301
302 AND ((recinfo.description = x_description)
303 OR ((recinfo.description is null)
304 AND (x_description is null)))
305
306 AND ((recinfo.context = x_context)
307 OR ((recinfo.context is null)
308 AND (x_context is null)))
309
310 AND ((recinfo.attribute1 = x_attribute1)
311 OR ((recinfo.attribute1 is null)
312 AND (x_attribute1 is null)))
313
314 AND ((recinfo.attribute2 = x_attribute2)
315 OR ((recinfo.attribute2 is null)
316 AND (x_attribute2 is null)))
317
318 AND ((recinfo.attribute3 = x_attribute3)
319 OR ((recinfo.attribute3 is null)
320 AND (x_attribute3 is null)))
321
322 AND ((recinfo.attribute4 = x_attribute4)
323 OR ((recinfo.attribute4 is null)
324 AND (x_attribute4 is null)))
325
326 AND ((recinfo.attribute5 = x_attribute5)
327 OR ((recinfo.attribute5 is null)
328 AND (x_attribute5 is null)))
329
330 AND ((recinfo.attribute6 = x_attribute6)
331 OR ((recinfo.attribute6 is null)
332 AND (x_attribute6 is null)))
333
334 AND ((recinfo.attribute7 = x_attribute7)
335 OR ((recinfo.attribute7 is null)
336 AND (x_attribute7 is null)))
337
338 AND ((recinfo.attribute8 = x_attribute8)
339 OR ((recinfo.attribute8 is null)
340 AND (x_attribute8 is null)))
341
342 AND ((recinfo.attribute9 = x_attribute9)
343 OR ((recinfo.attribute9 is null)
344 AND (x_attribute9 is null)))
345
346 AND ((recinfo.attribute10 = x_attribute10)
347 OR ((recinfo.attribute10 is null)
348 AND (x_attribute10 is null)))
349
350 AND ((recinfo.attribute11 = x_attribute11)
351 OR ((recinfo.attribute11 is null)
352 AND (x_attribute11 is null)))
353
354 AND ((recinfo.attribute12 = x_attribute12)
355 OR ((recinfo.attribute12 is null)
356 AND (x_attribute12 is null)))
357
358 AND ((recinfo.attribute13 = x_attribute13)
359 OR ((recinfo.attribute13 is null)
360 AND (x_attribute13 is null)))
361
362 AND ((recinfo.attribute14 = x_attribute14)
363 OR ((recinfo.attribute14 is null)
364 AND (x_attribute14 is null)))
365
366 AND ((recinfo.attribute15 = x_attribute15)
367 OR ((recinfo.attribute15 is null)
368 AND (x_attribute15 is null)))
369 ) THEN
370 return;
371 ELSE
372 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
373 app_exception.raise_exception;
374 END IF;
375
376 END lock_row;
377
378 /* Added X_Security_Flag for Definition Access Sets Project */
379 PROCEDURE Update_Row(X_Conversion_Type VARCHAR2,
380 X_User_Conversion_Type VARCHAR2,
381 X_Last_Update_Date DATE,
382 X_Last_Updated_By NUMBER,
383 X_Last_Update_Login NUMBER,
384 X_Description VARCHAR2,
385 X_Attribute1 VARCHAR2,
386 X_Attribute2 VARCHAR2,
387 X_Attribute3 VARCHAR2,
388 X_Attribute4 VARCHAR2,
389 X_Attribute5 VARCHAR2,
390 X_Attribute6 VARCHAR2,
391 X_Attribute7 VARCHAR2,
392 X_Attribute8 VARCHAR2,
393 X_Attribute9 VARCHAR2,
394 X_Attribute10 VARCHAR2,
395 X_Attribute11 VARCHAR2,
396 X_Attribute12 VARCHAR2,
397 X_Attribute13 VARCHAR2,
398 X_Attribute14 VARCHAR2,
399 X_Attribute15 VARCHAR2,
400 X_Context VARCHAR2,
401 X_Security_Flag VARCHAR2) IS
402 BEGIN
403 UPDATE gl_daily_conversion_types
404 SET
405 user_conversion_type = X_User_Conversion_Type,
406 last_update_date = X_Last_Update_Date,
407 last_updated_by = X_Last_Updated_By,
408 last_update_login = X_Last_Update_Login,
409 description = X_Description,
410 attribute1 = X_Attribute1,
411 attribute2 = X_Attribute2,
412 attribute3 = X_Attribute3,
413 attribute4 = X_Attribute4,
414 attribute5 = X_Attribute5,
418 attribute9 = X_Attribute9,
415 attribute6 = X_Attribute6,
416 attribute7 = X_Attribute7,
417 attribute8 = X_Attribute8,
419 attribute10 = X_Attribute10,
420 attribute11 = X_Attribute11,
421 attribute12 = X_Attribute12,
422 attribute13 = X_Attribute13,
423 attribute14 = X_Attribute14,
424 attribute15 = X_Attribute15,
425 context = X_Context,
426 security_flag = X_Security_Flag
427 WHERE conversion_type = X_Conversion_Type;
428
429 if (SQL%NOTFOUND) then
430 RAISE NO_DATA_FOUND;
431 end if;
432
433 EXCEPTION
434 WHEN app_exceptions.application_exception THEN
435 RAISE;
436 WHEN OTHERS THEN
437 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
438 fnd_message.set_token('PROCEDURE',
439 'GL_DAILY_CONV_TYPES_PKG.Update_Row');
440 RAISE;
441
442 END Update_Row;
443
444 /* Modified calls to Insert_Row and Update_Row to include Security_Flag
445 (for Definition Access Sets Project) */
446 PROCEDURE Load_Row(
447 V_Conversion_Type VARCHAR2,
448 V_User_Conversion_Type VARCHAR2,
449 V_Description VARCHAR2,
450 V_Attribute1 VARCHAR2,
451 V_Attribute2 VARCHAR2,
452 V_Attribute3 VARCHAR2,
453 V_Attribute4 VARCHAR2,
454 V_Attribute5 VARCHAR2,
455 V_Attribute6 VARCHAR2,
456 V_Attribute7 VARCHAR2,
457 V_Attribute8 VARCHAR2,
458 V_Attribute9 VARCHAR2,
459 V_Attribute10 VARCHAR2,
460 V_Attribute11 VARCHAR2,
461 V_Attribute12 VARCHAR2,
462 V_Attribute13 VARCHAR2,
463 V_Attribute14 VARCHAR2,
464 V_Attribute15 VARCHAR2,
465 V_Context VARCHAR2,
466 V_Owner VARCHAR2,
467 V_Force_Edits VARCHAR2) IS
468
469 user_id NUMBER := 0;
470 V_Rowid ROWID := null;
471 Force_Edits VARCHAR2(1) := 'N';
472 x_creation_date DATE;
473 x_security_flag VARCHAR2(1);
474 BEGIN
475
476 -- validate input parameter
477 IF ((V_User_Conversion_Type is NULL) OR
478 (V_Conversion_Type is NULL)) THEN
479 FND_MESSAGE.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
480 APP_EXCEPTION.raise_exception;
481 END IF;
482
483 IF (V_Owner = 'SEED') THEN
484 user_id := 1;
485 END IF;
486
487 IF (V_Force_Edits = 'Y') THEN
488 Force_Edits := 'Y';
489 END IF;
490
491 BEGIN
492
493 /* Check if the row exists in the database. If it does, retrieves
494 the creation date for update_row. */
495 -- Added security_flag for DAS project
496 select creation_date, security_flag
497 into x_creation_date, x_security_flag
498 from gl_daily_conversion_types
499 where conversion_type = V_Conversion_Type;
500
501 /* Update only if Force_Edits is 'Y' or user_id = 1 */
502 IF ( Force_Edits = 'Y' OR user_id = 1 ) THEN
503 -- update row if present
504 GL_DAILY_CONV_TYPES_PKG.Update_Row(
505 X_Conversion_Type => V_Conversion_Type,
506 X_User_Conversion_Type => V_User_Conversion_Type,
507 X_Last_Update_Date => sysdate,
508 X_Last_Updated_By => user_id,
509 X_Last_Update_Login => 0,
510 X_Description => V_Description,
511 X_Attribute1 => V_Attribute1,
512 X_Attribute2 => V_Attribute2,
513 X_Attribute3 => V_Attribute3,
514 X_Attribute4 => V_Attribute4,
515 X_Attribute5 => V_Attribute5,
516 X_Attribute6 => V_Attribute6,
517 X_Attribute7 => V_Attribute7,
518 X_Attribute8 => V_Attribute8,
519 X_Attribute9 => V_Attribute9,
520 X_Attribute10 => V_Attribute10,
521 X_Attribute11 => V_Attribute11,
522 X_Attribute12 => V_Attribute12,
523 X_Attribute13 => V_Attribute13,
524 X_Attribute14 => V_Attribute14,
525 X_Attribute15 => V_Attribute15,
526 X_Context => V_Context,
527 X_Security_Flag => x_security_flag);
528 END IF;
529
530 EXCEPTION
531 WHEN NO_DATA_FOUND THEN
532 GL_DAILY_CONV_TYPES_PKG.Insert_Row(
533 X_Rowid => V_Rowid,
534 X_Conversion_Type => V_Conversion_Type,
535 X_User_Conversion_Type => V_User_Conversion_Type,
536 X_Last_Update_Date => sysdate,
537 X_Last_Updated_By => user_id,
538 X_Creation_Date => x_creation_date,
539 X_Created_By => user_id,
540 X_Last_Update_Login => 0,
541 X_Description => V_Description,
542 X_Attribute1 => V_Attribute1,
543 X_Attribute2 => V_Attribute2,
544 X_Attribute3 => V_Attribute3,
545 X_Attribute4 => V_Attribute4,
546 X_Attribute5 => V_Attribute5,
547 X_Attribute6 => V_Attribute6,
548 X_Attribute7 => V_Attribute7,
549 X_Attribute8 => V_Attribute8,
550 X_Attribute9 => V_Attribute9,
551 X_Attribute10 => V_Attribute10,
552 X_Attribute11 => V_Attribute11,
553 X_Attribute12 => V_Attribute12,
554 X_Attribute13 => V_Attribute13,
555 X_Attribute14 => V_Attribute14,
556 X_Attribute15 => V_Attribute15,
557 X_Context => V_Context,
558 X_Security_Flag => 'N');
559
560 END;
561
562 END Load_Row;
563
564 PROCEDURE Translate_Row(
565 V_Conversion_Type VARCHAR2,
566 V_User_Conversion_Type VARCHAR2,
567 V_Description VARCHAR2,
568 V_Owner VARCHAR2,
569 V_Force_Edits VARCHAR2) IS
570
571 user_id NUMBER := 0;
572 Force_Edits VARCHAR2(1) := 'N';
573
574 BEGIN
575 IF (V_Owner = 'SEED') THEN
576 user_id := 1;
577 END IF;
578
579 IF (V_Force_Edits = 'Y') THEN
580 Force_Edits := 'Y';
581 END IF;
582
583 /* Update only if Force_Edits is 'Y' or user_id = 1 */
584 IF ( Force_Edits = 'Y' OR user_id = 1 ) THEN
585 UPDATE GL_DAILY_CONVERSION_TYPES
586 SET
587 user_conversion_type = V_User_Conversion_Type,
588 description = V_Description,
589 last_update_date = sysdate,
590 last_updated_by = user_id,
591 last_update_login = 0
592 WHERE conversion_type = V_Conversion_Type
593 AND userenv('LANG') =
594 ( SELECT language_code
595 FROM FND_LANGUAGES
596 WHERE installed_flag = 'B');
597 END IF;
598 /*If base language is not set to the language being uploaded, then do nothing.*/
599 IF (SQL%NOTFOUND) THEN
600 NULL;
601 END IF;
602
603 END Translate_Row;
604
605 END GL_DAILY_CONV_TYPES_PKG;