[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_REF_TARIFF_PKG
Source
1 PACKAGE BODY igs_uc_ref_tariff_pkg AS
2 /* $Header: IGSXI41B.pls 115.3 2003/02/28 07:51:34 bayadav noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_ref_tariff%ROWTYPE;
6 new_references igs_uc_ref_tariff%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_exam_level IN VARCHAR2,
12 x_exam_grade IN VARCHAR2,
13 x_tariff_score IN NUMBER,
14 x_imported IN VARCHAR2,
15 x_creation_date IN DATE,
16 x_created_by IN NUMBER,
17 x_last_update_date IN DATE,
18 x_last_updated_by IN NUMBER,
19 x_last_update_login IN NUMBER
20 ) AS
21 /*
22 || Created By : [email protected]
23 || Created On : 17-SEP-2002
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM igs_uc_ref_tariff
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.exam_level := x_exam_level;
55 new_references.exam_grade := x_exam_grade;
56 new_references.tariff_score := x_tariff_score;
57 new_references.imported := x_imported;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 FUNCTION get_pk_for_validation (
75 x_exam_level IN VARCHAR2,
76 x_exam_grade IN VARCHAR2
77 ) RETURN BOOLEAN AS
78 /*
79 || Created By : [email protected]
80 || Created On : 17-SEP-2002
81 || Purpose : Validates the Primary Key of the table.
82 || Known limitations, enhancements or remarks :
83 || Change History :
84 || Who When What
85 || (reverse chronological order - newest change first)
86 */
87 CURSOR cur_rowid IS
88 SELECT rowid
89 FROM igs_uc_ref_tariff
90 WHERE exam_level = x_exam_level
91 AND exam_grade = x_exam_grade ;
92
93 lv_rowid cur_rowid%RowType;
94
95 BEGIN
96
97 OPEN cur_rowid;
98 FETCH cur_rowid INTO lv_rowid;
99 IF (cur_rowid%FOUND) THEN
100 CLOSE cur_rowid;
101 RETURN(TRUE);
102 ELSE
103 CLOSE cur_rowid;
104 RETURN(FALSE);
105 END IF;
106
107 END get_pk_for_validation;
108
109
110 PROCEDURE before_dml (
111 p_action IN VARCHAR2,
112 x_rowid IN VARCHAR2,
113 x_exam_level IN VARCHAR2,
114 x_exam_grade IN VARCHAR2,
115 x_tariff_score IN NUMBER,
116 x_imported IN VARCHAR2,
117 x_creation_date IN DATE,
118 x_created_by IN NUMBER,
119 x_last_update_date IN DATE,
120 x_last_updated_by IN NUMBER,
121 x_last_update_login IN NUMBER
122 ) AS
123 /*
124 || Created By : [email protected]
125 || Created On : 17-SEP-2002
126 || Purpose : Initialises the columns, Checks Constraints, Calls the
127 || Trigger Handlers for the table, before any DML operation.
128 || Known limitations, enhancements or remarks :
129 || Change History :
130 || Who When What
131 || (reverse chronological order - newest change first)
132 */
133 BEGIN
134
135 set_column_values (
136 p_action,
137 x_rowid,
138 x_exam_level,
139 x_exam_grade,
140 x_tariff_score,
141 x_imported,
142 x_creation_date,
143 x_created_by,
144 x_last_update_date,
145 x_last_updated_by,
146 x_last_update_login
147 );
148
149 IF (p_action = 'INSERT') THEN
150 -- Call all the procedures related to Before Insert.
151 IF ( get_pk_for_validation(
152 new_references.exam_level,
153 new_references.exam_grade
154 )
155 ) THEN
156 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
157 igs_ge_msg_stack.add;
158 app_exception.raise_exception;
159 END IF;
160 ELSIF (p_action = 'VALIDATE_INSERT') THEN
161 -- Call all the procedures related to Before Insert.
162 IF ( get_pk_for_validation (
163 new_references.exam_level,
164 new_references.exam_grade
165 )
166 ) THEN
167 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
168 igs_ge_msg_stack.add;
169 app_exception.raise_exception;
170 END IF;
171 END IF;
172
173 END before_dml;
174
175
176 PROCEDURE insert_row (
177 x_rowid IN OUT NOCOPY VARCHAR2,
178 x_exam_level IN VARCHAR2,
179 x_exam_grade IN VARCHAR2,
180 x_tariff_score IN NUMBER,
181 x_imported IN VARCHAR2,
182 x_mode IN VARCHAR2
183 ) AS
184 /*
185 || Created By : [email protected]
186 || Created On : 17-SEP-2002
187 || Purpose : Handles the INSERT DML logic for the table.
188 || Known limitations, enhancements or remarks :
189 || Change History :
190 || Who When What
191 || (reverse chronological order - newest change first)
192 */
193
194 x_last_update_date DATE;
195 x_last_updated_by NUMBER;
196 x_last_update_login NUMBER;
197
198 BEGIN
199
200 x_last_update_date := SYSDATE;
201 IF (x_mode = 'I') THEN
202 x_last_updated_by := 1;
203 x_last_update_login := 0;
204 ELSIF (x_mode = 'R') THEN
205 x_last_updated_by := fnd_global.user_id;
206 IF (x_last_updated_by IS NULL) THEN
207 x_last_updated_by := -1;
208 END IF;
209 x_last_update_login := fnd_global.login_id;
210 IF (x_last_update_login IS NULL) THEN
211 x_last_update_login := -1;
212 END IF;
213 ELSE
214 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
215 igs_ge_msg_stack.add;
216 app_exception.raise_exception;
217 END IF;
218
219 before_dml(
220 p_action => 'INSERT',
221 x_rowid => x_rowid,
222 x_exam_level => x_exam_level,
223 x_exam_grade => x_exam_grade,
224 x_tariff_score => x_tariff_score,
225 x_imported => x_imported,
226 x_creation_date => x_last_update_date,
227 x_created_by => x_last_updated_by,
228 x_last_update_date => x_last_update_date,
229 x_last_updated_by => x_last_updated_by,
230 x_last_update_login => x_last_update_login
231 );
232
233 INSERT INTO igs_uc_ref_tariff (
234 exam_level,
235 exam_grade,
236 tariff_score,
237 imported,
238 creation_date,
239 created_by,
240 last_update_date,
241 last_updated_by,
242 last_update_login
243 ) VALUES (
244 new_references.exam_level,
245 new_references.exam_grade,
246 new_references.tariff_score,
247 new_references.imported,
248 x_last_update_date,
249 x_last_updated_by,
250 x_last_update_date,
251 x_last_updated_by,
252 x_last_update_login
253 ) RETURNING ROWID INTO x_rowid;
254
255 END insert_row;
256
257
258 PROCEDURE lock_row (
259 x_rowid IN VARCHAR2,
260 x_exam_level IN VARCHAR2,
261 x_exam_grade IN VARCHAR2,
262 x_tariff_score IN NUMBER,
263 x_imported IN VARCHAR2
264 ) AS
265 /*
266 || Created By : [email protected]
267 || Created On : 17-SEP-2002
268 || Purpose : Handles the LOCK mechanism for the table.
269 || Known limitations, enhancements or remarks :
270 || Change History :
271 || Who When What
272 || (reverse chronological order - newest change first)
273 */
274 CURSOR c1 IS
275 SELECT
276 tariff_score,
277 imported
278 FROM igs_uc_ref_tariff
279 WHERE rowid = x_rowid
280 FOR UPDATE NOWAIT;
281
282 tlinfo c1%ROWTYPE;
283
284 BEGIN
285
286 OPEN c1;
287 FETCH c1 INTO tlinfo;
288 IF (c1%notfound) THEN
289 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
290 igs_ge_msg_stack.add;
291 CLOSE c1;
292 app_exception.raise_exception;
293 RETURN;
294 END IF;
295 CLOSE c1;
296
297 IF (
298 (tlinfo.tariff_score = x_tariff_score)
299 AND (tlinfo.imported = x_imported)
300 ) THEN
301 NULL;
302 ELSE
303 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
304 igs_ge_msg_stack.add;
305 app_exception.raise_exception;
306 END IF;
307
308 RETURN;
309
310 END lock_row;
311
312
313 PROCEDURE update_row (
314 x_rowid IN VARCHAR2,
315 x_exam_level IN VARCHAR2,
316 x_exam_grade IN VARCHAR2,
317 x_tariff_score IN NUMBER,
318 x_imported IN VARCHAR2,
319 x_mode IN VARCHAR2
320 ) AS
321 /*
322 || Created By : [email protected]
323 || Created On : 17-SEP-2002
324 || Purpose : Handles the UPDATE DML logic for the table.
325 || Known limitations, enhancements or remarks :
326 || Change History :
327 || Who When What
328 || (reverse chronological order - newest change first)
329 */
330 x_last_update_date DATE ;
331 x_last_updated_by NUMBER;
332 x_last_update_login NUMBER;
333
334 BEGIN
335
336 x_last_update_date := SYSDATE;
337 IF (X_MODE = 'I') THEN
338 x_last_updated_by := 1;
339 x_last_update_login := 0;
340 ELSIF (x_mode = 'R') THEN
341 x_last_updated_by := fnd_global.user_id;
342 IF x_last_updated_by IS NULL THEN
343 x_last_updated_by := -1;
344 END IF;
345 x_last_update_login := fnd_global.login_id;
346 IF (x_last_update_login IS NULL) THEN
347 x_last_update_login := -1;
348 END IF;
349 ELSE
350 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
351 igs_ge_msg_stack.add;
352 app_exception.raise_exception;
353 END IF;
354
355 before_dml(
356 p_action => 'UPDATE',
357 x_rowid => x_rowid,
358 x_exam_level => x_exam_level,
359 x_exam_grade => x_exam_grade,
360 x_tariff_score => x_tariff_score,
361 x_imported => x_imported,
362 x_creation_date => x_last_update_date,
363 x_created_by => x_last_updated_by,
364 x_last_update_date => x_last_update_date,
365 x_last_updated_by => x_last_updated_by,
366 x_last_update_login => x_last_update_login
367 );
368
369 UPDATE igs_uc_ref_tariff
370 SET
371 tariff_score = new_references.tariff_score,
372 imported = new_references.imported,
373 last_update_date = x_last_update_date,
374 last_updated_by = x_last_updated_by,
375 last_update_login = x_last_update_login
376 WHERE rowid = x_rowid;
377
378 IF (SQL%NOTFOUND) THEN
379 RAISE NO_DATA_FOUND;
380 END IF;
381
382 END update_row;
383
384
385 PROCEDURE add_row (
386 x_rowid IN OUT NOCOPY VARCHAR2,
387 x_exam_level IN VARCHAR2,
388 x_exam_grade IN VARCHAR2,
389 x_tariff_score IN NUMBER,
390 x_imported IN VARCHAR2,
391 x_mode IN VARCHAR2
392 ) AS
393 /*
394 || Created By : [email protected]
395 || Created On : 17-SEP-2002
396 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
397 || Known limitations, enhancements or remarks :
398 || Change History :
399 || Who When What
400 || (reverse chronological order - newest change first)
401 */
402 CURSOR c1 IS
403 SELECT rowid
404 FROM igs_uc_ref_tariff
405 WHERE exam_level = x_exam_level
406 AND exam_grade = x_exam_grade;
407
408 BEGIN
409
410 OPEN c1;
411 FETCH c1 INTO x_rowid;
412 IF (c1%NOTFOUND) THEN
413 CLOSE c1;
414
415 insert_row (
416 x_rowid,
417 x_exam_level,
418 x_exam_grade,
419 x_tariff_score,
420 x_imported,
421 x_mode
422 );
423 RETURN;
424 END IF;
425 CLOSE c1;
426
427 update_row (
428 x_rowid,
429 x_exam_level,
430 x_exam_grade,
431 x_tariff_score,
432 x_imported,
433 x_mode
434 );
435
436 END add_row;
437
438
439 PROCEDURE delete_row (
440 x_rowid IN VARCHAR2
441 ) AS
442 /*
443 || Created By : [email protected]
444 || Created On : 17-SEP-2002
445 || Purpose : Handles the DELETE DML logic for the table.
446 || Known limitations, enhancements or remarks :
447 || Change History :
448 || Who When What
449 || (reverse chronological order - newest change first)
450 */
451 BEGIN
452
453 before_dml (
454 p_action => 'DELETE',
455 x_rowid => x_rowid
456 );
457
458 DELETE FROM igs_uc_ref_tariff
459 WHERE rowid = x_rowid;
460
461 IF (SQL%NOTFOUND) THEN
462 RAISE NO_DATA_FOUND;
463 END IF;
464
465 END delete_row;
466
467
468 END igs_uc_ref_tariff_pkg;