1 PACKAGE BODY igs_he_ut_excl_qals_pkg AS
2 /* $Header: IGSWI41B.pls 115.0 2003/09/02 13:20:29 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_he_ut_excl_qals%ROWTYPE;
6 new_references igs_he_ut_excl_qals%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_excl_qual_id IN NUMBER,
12 x_tariff_calc_type_cd IN VARCHAR2,
13 x_award_cd IN VARCHAR2,
14 x_field_of_study 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 :
23 || Created On : 28-AUG-2003
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_he_ut_excl_qals
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.excl_qual_id := x_excl_qual_id;
55 new_references.tariff_calc_type_cd := x_tariff_calc_type_cd;
56 new_references.award_cd := x_award_cd;
57 new_references.field_of_study := x_field_of_study;
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 PROCEDURE check_parent_existance AS
75 /*
76 || Created By :
77 || Created On : 28-AUG-2003
78 || Purpose : Checks for the existance of Parent records.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84 BEGIN
85
86 IF (((old_references.tariff_calc_type_cd = new_references.tariff_calc_type_cd)) OR
87 ((new_references.tariff_calc_type_cd IS NULL))) THEN
88 NULL;
89 ELSIF NOT igs_he_ut_calc_type_pkg.get_pk_for_validation (
90 new_references.tariff_calc_type_cd,
91 'N'
92 ) THEN
93 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
94 igs_ge_msg_stack.add;
95 app_exception.raise_exception;
96 END IF;
97
98 END check_parent_existance;
99
100
101 FUNCTION get_pk_for_validation (
102 x_excl_qual_id IN NUMBER
103 ) RETURN BOOLEAN AS
104 /*
105 || Created By :
106 || Created On : 28-AUG-2003
107 || Purpose : Validates the Primary Key of the table.
108 || Known limitations, enhancements or remarks :
109 || Change History :
110 || Who When What
111 || (reverse chronological order - newest change first)
112 */
113 CURSOR cur_rowid IS
114 SELECT rowid
115 FROM igs_he_ut_excl_qals
116 WHERE excl_qual_id = x_excl_qual_id
117 FOR UPDATE NOWAIT;
118
119 lv_rowid cur_rowid%RowType;
120
121 BEGIN
122
123 OPEN cur_rowid;
124 FETCH cur_rowid INTO lv_rowid;
125 IF (cur_rowid%FOUND) THEN
126 CLOSE cur_rowid;
127 RETURN(TRUE);
128 ELSE
129 CLOSE cur_rowid;
130 RETURN(FALSE);
131 END IF;
132
133 END get_pk_for_validation;
134
135
136 PROCEDURE get_fk_igs_he_ut_calc_type (
137 x_tariff_calc_type_cd IN VARCHAR2
138 ) AS
139 /*
140 || Created By :
141 || Created On : 28-AUG-2003
142 || Purpose : Validates the Foreign Keys for the table.
143 || Known limitations, enhancements or remarks :
144 || Change History :
145 || Who When What
146 || (reverse chronological order - newest change first)
147 */
148 CURSOR cur_rowid IS
149 SELECT rowid
150 FROM igs_he_ut_excl_qals
151 WHERE ((tariff_calc_type_cd = x_tariff_calc_type_cd));
152
153 lv_rowid cur_rowid%RowType;
154
155 BEGIN
156
157 OPEN cur_rowid;
158 FETCH cur_rowid INTO lv_rowid;
159 IF (cur_rowid%FOUND) THEN
160 CLOSE cur_rowid;
161 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
162 igs_ge_msg_stack.add;
163 app_exception.raise_exception;
164 RETURN;
165 END IF;
166 CLOSE cur_rowid;
167
168 END get_fk_igs_he_ut_calc_type;
169
170
171 FUNCTION get_uk_for_validation (
172 x_tariff_calc_type_cd IN VARCHAR2,
173 x_award_cd IN VARCHAR2,
174 x_field_of_study IN VARCHAR2
175 ) RETURN BOOLEAN AS
176 /*
177 || Created By : smaddali
178 || Created On : 13-jun-2002
179 || Purpose : Validates the Unique Keys of the table.
180 || Known limitations, enhancements or remarks :
181 || Change History :
182 || Who When What
183 || smaddali modified cursor cur_rowid to add check for rowid as it was missing
184 */
185 CURSOR cur_rowid IS
186 SELECT rowid
187 FROM igs_he_ut_excl_qals
188 WHERE tariff_calc_type_cd = x_tariff_calc_type_cd
189 AND ((award_cd = x_award_cd) OR ((award_cd IS NULL) AND (X_award_cd IS NULL)))
190 AND ((field_of_study = x_field_of_study) OR ((field_of_study IS NULL) AND (X_field_of_study IS NULL)))
191 AND ((l_rowid IS NULL) OR (rowid <> l_rowid)) ;
192
193 lv_rowid cur_rowid%RowType;
194
195 BEGIN
196 OPEN cur_rowid;
197 FETCH cur_rowid INTO lv_rowid;
198 IF (cur_rowid%FOUND) THEN
199 CLOSE cur_rowid;
200 RETURN (true);
201 ELSE
202 CLOSE cur_rowid;
203 RETURN(FALSE);
204 END IF;
205
206 END get_uk_for_validation ;
207
208 PROCEDURE check_uniqueness AS
209 /*
210 || Created By : smaddali
211 || Created On : 13-jun-2002
212 || Purpose : tariff_calc_type_cd, award_cd and field_of_study should be unique
213 || Known limitations, enhancements or remarks :
214 || Change History :
215 || Who When What
216 || (reverse chronological order - newest change first)
217 */
218 BEGIN
219 IF ( get_uk_for_validation (
220 x_tariff_calc_type_cd => new_references.tariff_calc_type_cd,
221 x_award_cd => new_references.award_cd,
222 x_field_of_study => new_references.field_of_study )
223 ) THEN
224 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
225 igs_ge_msg_stack.add;
226 app_exception.raise_exception;
227 END IF;
228
229 END check_uniqueness;
230
231
232 PROCEDURE before_dml (
233 p_action IN VARCHAR2,
234 x_rowid IN VARCHAR2,
235 x_excl_qual_id IN NUMBER,
236 x_tariff_calc_type_cd IN VARCHAR2,
237 x_award_cd IN VARCHAR2,
238 x_field_of_study IN VARCHAR2,
239 x_creation_date IN DATE,
240 x_created_by IN NUMBER,
241 x_last_update_date IN DATE,
242 x_last_updated_by IN NUMBER,
243 x_last_update_login IN NUMBER
244 ) AS
245 /*
246 || Created By :
247 || Created On : 28-AUG-2003
248 || Purpose : Initialises the columns, Checks Constraints, Calls the
249 || Trigger Handlers for the table, before any DML operation.
250 || Known limitations, enhancements or remarks :
251 || Change History :
252 || Who When What
253 || (reverse chronological order - newest change first)
254 */
255 BEGIN
256
257 set_column_values (
258 p_action,
259 x_rowid,
260 x_excl_qual_id,
261 x_tariff_calc_type_cd,
262 x_award_cd,
263 x_field_of_study,
264 x_creation_date,
265 x_created_by,
266 x_last_update_date,
267 x_last_updated_by,
268 x_last_update_login
269 );
270
271 IF (p_action = 'INSERT') THEN
272 -- Call all the procedures related to Before Insert.
273 IF ( get_pk_for_validation(
274 new_references.excl_qual_id
275 )
276 ) THEN
277 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
278 igs_ge_msg_stack.add;
279 app_exception.raise_exception;
280 END IF;
281 check_parent_existance;
282 check_uniqueness;
283 ELSIF (p_action = 'UPDATE') THEN
284 -- Call all the procedures related to Before Update.
285 check_parent_existance;
286 check_uniqueness;
287 ELSIF (p_action = 'VALIDATE_INSERT') THEN
288 -- Call all the procedures related to Before Insert.
289 IF ( get_pk_for_validation (
290 new_references.excl_qual_id
291 )
292 ) THEN
293 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
294 igs_ge_msg_stack.add;
295 app_exception.raise_exception;
296 END IF;
297 check_uniqueness;
298 END IF;
299
300 IF (p_action IN ('VALIDATE_INSERT', 'VALIDATE_UPDATE', 'VALIDATE_DELETE')) THEN
301 l_rowid := NULL;
302 END IF;
303
304 END before_dml;
305
306
307 PROCEDURE insert_row (
308 x_rowid IN OUT NOCOPY VARCHAR2,
309 x_excl_qual_id IN OUT NOCOPY NUMBER,
310 x_tariff_calc_type_cd IN VARCHAR2,
311 x_award_cd IN VARCHAR2,
312 x_field_of_study IN VARCHAR2,
313 x_mode IN VARCHAR2
314 ) AS
315 /*
316 || Created By :
317 || Created On : 28-AUG-2003
318 || Purpose : Handles the INSERT DML logic for the table.
319 || Known limitations, enhancements or remarks :
320 || Change History :
321 || Who When What
322 || (reverse chronological order - newest change first)
323 */
324
325 x_last_update_date DATE;
326 x_last_updated_by NUMBER;
327 x_last_update_login NUMBER;
328
329 BEGIN
330
331 x_last_update_date := SYSDATE;
332 IF (x_mode = 'I') THEN
333 x_last_updated_by := 1;
334 x_last_update_login := 0;
335 ELSIF (x_mode = 'R') THEN
336 x_last_updated_by := fnd_global.user_id;
337 IF (x_last_updated_by IS NULL) THEN
338 x_last_updated_by := -1;
339 END IF;
340 x_last_update_login := fnd_global.login_id;
341 IF (x_last_update_login IS NULL) THEN
342 x_last_update_login := -1;
343 END IF;
344 ELSE
345 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
346 fnd_message.set_token ('ROUTINE', 'IGS_HE_UT_EXCL_QALS_PKG.INSERT_ROW');
347 igs_ge_msg_stack.add;
348 app_exception.raise_exception;
349 END IF;
350
351 x_excl_qual_id := NULL;
352
353 before_dml(
354 p_action => 'INSERT',
355 x_rowid => x_rowid,
356 x_excl_qual_id => x_excl_qual_id,
357 x_tariff_calc_type_cd => x_tariff_calc_type_cd,
358 x_award_cd => x_award_cd,
359 x_field_of_study => x_field_of_study,
360 x_creation_date => x_last_update_date,
361 x_created_by => x_last_updated_by,
362 x_last_update_date => x_last_update_date,
363 x_last_updated_by => x_last_updated_by,
364 x_last_update_login => x_last_update_login
365 );
366
367 INSERT INTO igs_he_ut_excl_qals (
368 excl_qual_id,
369 tariff_calc_type_cd,
370 award_cd,
371 field_of_study,
372 creation_date,
373 created_by,
374 last_update_date,
375 last_updated_by,
376 last_update_login
377 ) VALUES (
378 igs_he_ut_excl_qals_s.NEXTVAL,
379 new_references.tariff_calc_type_cd,
380 new_references.award_cd,
381 new_references.field_of_study,
382 x_last_update_date,
383 x_last_updated_by,
384 x_last_update_date,
385 x_last_updated_by,
386 x_last_update_login
387 ) RETURNING ROWID, excl_qual_id INTO x_rowid, x_excl_qual_id;
388
389 l_rowid := NULL;
390
391 END insert_row;
392
393
394 PROCEDURE lock_row (
395 x_rowid IN VARCHAR2,
396 x_excl_qual_id IN NUMBER,
397 x_tariff_calc_type_cd IN VARCHAR2,
398 x_award_cd IN VARCHAR2,
399 x_field_of_study IN VARCHAR2
400 ) AS
401 /*
402 || Created By :
403 || Created On : 28-AUG-2003
404 || Purpose : Handles the LOCK mechanism for the table.
405 || Known limitations, enhancements or remarks :
406 || Change History :
407 || Who When What
408 || (reverse chronological order - newest change first)
409 */
410 CURSOR c1 IS
411 SELECT
412 tariff_calc_type_cd,
413 award_cd,
414 field_of_study
415 FROM igs_he_ut_excl_qals
416 WHERE rowid = x_rowid
417 FOR UPDATE NOWAIT;
418
419 tlinfo c1%ROWTYPE;
420
421 BEGIN
422
423 OPEN c1;
424 FETCH c1 INTO tlinfo;
425 IF (c1%notfound) THEN
426 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
427 igs_ge_msg_stack.add;
428 CLOSE c1;
429 app_exception.raise_exception;
430 RETURN;
431 END IF;
432 CLOSE c1;
433
434 IF (
435 (tlinfo.tariff_calc_type_cd = x_tariff_calc_type_cd)
436 AND ((tlinfo.award_cd = x_award_cd) OR ((tlinfo.award_cd IS NULL) AND (X_award_cd IS NULL)))
437 AND ((tlinfo.field_of_study = x_field_of_study) OR ((tlinfo.field_of_study IS NULL) AND (X_field_of_study IS NULL)))
438 ) THEN
439 NULL;
440 ELSE
441 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
442 igs_ge_msg_stack.add;
443 app_exception.raise_exception;
444 END IF;
445
446 RETURN;
447
448 END lock_row;
449
450
451 PROCEDURE update_row (
452 x_rowid IN VARCHAR2,
453 x_excl_qual_id IN NUMBER,
454 x_tariff_calc_type_cd IN VARCHAR2,
455 x_award_cd IN VARCHAR2,
456 x_field_of_study IN VARCHAR2,
457 x_mode IN VARCHAR2
458 ) AS
459 /*
460 || Created By :
461 || Created On : 28-AUG-2003
462 || Purpose : Handles the UPDATE DML logic for the table.
463 || Known limitations, enhancements or remarks :
464 || Change History :
465 || Who When What
466 || (reverse chronological order - newest change first)
467 */
468 x_last_update_date DATE ;
469 x_last_updated_by NUMBER;
470 x_last_update_login NUMBER;
471
472 BEGIN
473
474 x_last_update_date := SYSDATE;
475 IF (X_MODE = 'I') THEN
476 x_last_updated_by := 1;
477 x_last_update_login := 0;
478 ELSIF (x_mode = 'R') THEN
479 x_last_updated_by := fnd_global.user_id;
480 IF x_last_updated_by IS NULL THEN
481 x_last_updated_by := -1;
482 END IF;
483 x_last_update_login := fnd_global.login_id;
484 IF (x_last_update_login IS NULL) THEN
485 x_last_update_login := -1;
486 END IF;
487 ELSE
488 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
489 fnd_message.set_token ('ROUTINE', 'IGS_HE_UT_EXCL_QALS_PKG.UPDATE_ROW');
490 igs_ge_msg_stack.add;
491 app_exception.raise_exception;
492 END IF;
493
494 before_dml(
495 p_action => 'UPDATE',
496 x_rowid => x_rowid,
497 x_excl_qual_id => x_excl_qual_id,
498 x_tariff_calc_type_cd => x_tariff_calc_type_cd,
499 x_award_cd => x_award_cd,
500 x_field_of_study => x_field_of_study,
501 x_creation_date => x_last_update_date,
502 x_created_by => x_last_updated_by,
503 x_last_update_date => x_last_update_date,
504 x_last_updated_by => x_last_updated_by,
505 x_last_update_login => x_last_update_login
506 );
507
508 UPDATE igs_he_ut_excl_qals
509 SET
510 tariff_calc_type_cd = new_references.tariff_calc_type_cd,
511 award_cd = new_references.award_cd,
512 field_of_study = new_references.field_of_study,
513 last_update_date = x_last_update_date,
514 last_updated_by = x_last_updated_by,
515 last_update_login = x_last_update_login
516 WHERE rowid = x_rowid;
517
518 IF (SQL%NOTFOUND) THEN
519 RAISE NO_DATA_FOUND;
520 END IF;
521
522 l_rowid := NULL;
523
524 END update_row;
525
526
527 PROCEDURE add_row (
528 x_rowid IN OUT NOCOPY VARCHAR2,
529 x_excl_qual_id IN OUT NOCOPY NUMBER,
530 x_tariff_calc_type_cd IN VARCHAR2,
531 x_award_cd IN VARCHAR2,
532 x_field_of_study IN VARCHAR2,
533 x_mode IN VARCHAR2
534 ) AS
535 /*
536 || Created By :
537 || Created On : 28-AUG-2003
538 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
539 || Known limitations, enhancements or remarks :
540 || Change History :
541 || Who When What
542 || (reverse chronological order - newest change first)
543 */
544 CURSOR c1 IS
545 SELECT rowid
546 FROM igs_he_ut_excl_qals
547 WHERE excl_qual_id = x_excl_qual_id;
548
549 BEGIN
550
551 OPEN c1;
552 FETCH c1 INTO x_rowid;
553 IF (c1%NOTFOUND) THEN
554 CLOSE c1;
555
556 insert_row (
557 x_rowid,
558 x_excl_qual_id,
559 x_tariff_calc_type_cd,
560 x_award_cd,
561 x_field_of_study,
562 x_mode
563 );
564 RETURN;
565 END IF;
566 CLOSE c1;
567
568 update_row (
569 x_rowid,
570 x_excl_qual_id,
571 x_tariff_calc_type_cd,
572 x_award_cd,
573 x_field_of_study,
574 x_mode
575 );
576
577 END add_row;
578
579
580 PROCEDURE delete_row (
581 x_rowid IN VARCHAR2
582 ) AS
583 /*
584 || Created By :
585 || Created On : 28-AUG-2003
586 || Purpose : Handles the DELETE DML logic for the table.
587 || Known limitations, enhancements or remarks :
588 || Change History :
589 || Who When What
590 || (reverse chronological order - newest change first)
591 */
592 BEGIN
593
594 before_dml (
595 p_action => 'DELETE',
596 x_rowid => x_rowid
597 );
598
599 DELETE FROM igs_he_ut_excl_qals
600 WHERE rowid = x_rowid;
601
602 IF (SQL%NOTFOUND) THEN
603 RAISE NO_DATA_FOUND;
604 END IF;
605
606 l_rowid := NULL;
607
608 END delete_row;
609
610
611 END igs_he_ut_excl_qals_pkg;
[Home] [Help]