[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_EXP_QUAL_SUM_PKG
Source
1 PACKAGE BODY igs_uc_exp_qual_sum_pkg AS
2 /* $Header: IGSXI36B.pls 115.6 2003/02/28 07:50:39 bayadav noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_exp_qual_sum%ROWTYPE;
6 new_references igs_uc_exp_qual_sum%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_exp_qual_sum_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_exp_gce IN NUMBER ,
14 x_exp_vce IN NUMBER ,
15 x_winter_a_levels IN NUMBER ,
16 x_prev_a_levels IN NUMBER ,
17 x_prev_as_levels IN NUMBER ,
18 x_sqa IN VARCHAR2,
19 x_btec IN VARCHAR2,
20 x_ib IN VARCHAR2,
21 x_ilc IN VARCHAR2,
22 x_ailc IN VARCHAR2,
23 x_ksi IN VARCHAR2,
24 x_roa IN VARCHAR2,
25 x_manual IN VARCHAR2,
26 x_oeq IN VARCHAR2,
27 x_prev_oeq IN VARCHAR2,
28 x_vqi IN VARCHAR2,
29 x_seq_updated_date IN DATE ,
30 x_creation_date IN DATE ,
31 x_created_by IN NUMBER ,
32 x_last_update_date IN DATE ,
33 x_last_updated_by IN NUMBER ,
34 x_last_update_login IN NUMBER
35 ) AS
36 /*
37 || Created By : [email protected]
38 || Created On : 01-FEB-2002
39 || Purpose : Initialises the Old and New references for the columns of the table.
40 || Known limitations, enhancements or remarks :
41 || Change History :
42 || Who When What
43 || (reverse chronological order - newest change first)
44 */
45
46 CURSOR cur_old_ref_values IS
47 SELECT *
48 FROM IGS_UC_EXP_QUAL_SUM
49 WHERE rowid = x_rowid;
50
51 BEGIN
52
53 l_rowid := x_rowid;
54
55 -- Code for setting the Old and New Reference Values.
56 -- Populate Old Values.
57 OPEN cur_old_ref_values;
58 FETCH cur_old_ref_values INTO old_references;
59 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
60 CLOSE cur_old_ref_values;
61 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
62 igs_ge_msg_stack.add;
63 app_exception.raise_exception;
64 RETURN;
65 END IF;
66 CLOSE cur_old_ref_values;
67
68 -- Populate New Values.
69 new_references.exp_qual_sum_id := x_exp_qual_sum_id;
70 new_references.person_id := x_person_id;
71 new_references.exp_gce := x_exp_gce;
72 new_references.exp_vce := x_exp_vce;
73 new_references.winter_a_levels := x_winter_a_levels;
74 new_references.prev_a_levels := x_prev_a_levels;
75 new_references.prev_as_levels := x_prev_as_levels;
76 new_references.sqa := x_sqa;
77 new_references.btec := x_btec;
78 new_references.ib := x_ib;
79 new_references.ilc := x_ilc;
80 new_references.ailc := x_ailc;
81 new_references.ksi := x_ksi;
82 new_references.roa := x_roa;
83 new_references.manual := x_manual;
84 new_references.oeq := x_oeq;
85 new_references.prev_oeq := x_prev_oeq;
86 new_references.vqi := x_vqi;
87 new_references.seq_updated_date := x_seq_updated_date;
88
89 IF (p_action = 'UPDATE') THEN
90 new_references.creation_date := old_references.creation_date;
91 new_references.created_by := old_references.created_by;
92 ELSE
93 new_references.creation_date := x_creation_date;
94 new_references.created_by := x_created_by;
95 END IF;
96
97 new_references.last_update_date := x_last_update_date;
98 new_references.last_updated_by := x_last_updated_by;
99 new_references.last_update_login := x_last_update_login;
100
101 END set_column_values;
102
103
104 PROCEDURE check_parent_existance AS
105 /*
106 || Created By : [email protected]
107 || Created On : 01-FEB-2002
108 || Purpose : Checks for the existance of Parent records.
109 || Known limitations, enhancements or remarks :
110 || Change History :
111 || Who When What
112 || (reverse chronological order - newest change first)
113 || rbezawad 16-Dec-2002 Changed FK relation get_fk_pe_hz_parties to get_fk_igs_pe_person w.r.t. Bug 2541370.
114 || So changed the get_pk...() call from igs_pe_hz_parties_pkg to igs_pe_person_pkg.
115 */
116 BEGIN
117
118 IF (((old_references.person_id = new_references.person_id)) OR
119 ((new_references.person_id IS NULL))) THEN
120 NULL;
121 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
122 new_references.person_id
123 ) THEN
124 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
125 igs_ge_msg_stack.add;
126 app_exception.raise_exception;
127 END IF;
128
129 END check_parent_existance;
130
131
132 FUNCTION get_pk_for_validation (
133 x_exp_qual_sum_id IN NUMBER
134 ) RETURN BOOLEAN AS
135 /*
136 || Created By : [email protected]
137 || Created On : 01-FEB-2002
138 || Purpose : Validates the Primary Key of the table.
139 || Known limitations, enhancements or remarks :
140 || Change History :
141 || Who When What
142 || (reverse chronological order - newest change first)
143 */
144 CURSOR cur_rowid IS
145 SELECT rowid
146 FROM igs_uc_exp_qual_sum
147 WHERE exp_qual_sum_id = x_exp_qual_sum_id ;
148
149 lv_rowid cur_rowid%RowType;
150
151 BEGIN
152
153 OPEN cur_rowid;
154 FETCH cur_rowid INTO lv_rowid;
155 IF (cur_rowid%FOUND) THEN
156 CLOSE cur_rowid;
157 RETURN(TRUE);
158 ELSE
159 CLOSE cur_rowid;
160 RETURN(FALSE);
161 END IF;
162
163 END get_pk_for_validation;
164
165
166 PROCEDURE get_fk_igs_pe_person (
167 x_person_id IN NUMBER
168 ) AS
169 /*
170 || Created By : [email protected]
171 || Created On : 01-FEB-2002
172 || Purpose : Validates the Foreign Keys for the table.
173 || Known limitations, enhancements or remarks :
174 || Change History :
175 || Who When What
176 || (reverse chronological order - newest change first)
177 || rbezawad 16-Dec-2002 Changed FK relation get_fk_pe_hz_parties to
178 || get_fk_igs_pe_person w.r.t. Bug 2541370.
179 */
180 CURSOR cur_rowid IS
181 SELECT rowid
182 FROM igs_uc_exp_qual_sum
183 WHERE ((person_id = x_person_id));
184
185 lv_rowid cur_rowid%RowType;
186
187 BEGIN
188
189 OPEN cur_rowid;
190 FETCH cur_rowid INTO lv_rowid;
191 IF (cur_rowid%FOUND) THEN
192 CLOSE cur_rowid;
193 fnd_message.set_name ('IGS', 'IGS_UC_UCEQS_IHP_FK');
194 igs_ge_msg_stack.add;
195 app_exception.raise_exception;
196 RETURN;
197 END IF;
198 CLOSE cur_rowid;
199
200 END get_fk_igs_pe_person;
201
202
203 PROCEDURE before_dml (
204 p_action IN VARCHAR2,
205 x_rowid IN VARCHAR2,
206 x_exp_qual_sum_id IN NUMBER ,
207 x_person_id IN NUMBER ,
208 x_exp_gce IN NUMBER ,
209 x_exp_vce IN NUMBER ,
210 x_winter_a_levels IN NUMBER ,
211 x_prev_a_levels IN NUMBER ,
212 x_prev_as_levels IN NUMBER ,
213 x_sqa IN VARCHAR2,
214 x_btec IN VARCHAR2,
215 x_ib IN VARCHAR2,
216 x_ilc IN VARCHAR2,
217 x_ailc IN VARCHAR2,
218 x_ksi IN VARCHAR2,
219 x_roa IN VARCHAR2,
220 x_manual IN VARCHAR2,
221 x_oeq IN VARCHAR2,
222 x_prev_oeq IN VARCHAR2,
223 x_vqi IN VARCHAR2,
224 x_seq_updated_date IN DATE ,
225 x_creation_date IN DATE ,
226 x_created_by IN NUMBER ,
227 x_last_update_date IN DATE ,
228 x_last_updated_by IN NUMBER ,
229 x_last_update_login IN NUMBER
230 ) AS
231 /*
232 || Created By : [email protected]
233 || Created On : 01-FEB-2002
234 || Purpose : Initialises the columns, Checks Constraints, Calls the
235 || Trigger Handlers for the table, before any DML operation.
236 || Known limitations, enhancements or remarks :
237 || Change History :
238 || Who When What
239 || (reverse chronological order - newest change first)
240 */
241 BEGIN
242
243 set_column_values (
244 p_action,
245 x_rowid,
246 x_exp_qual_sum_id,
247 x_person_id,
248 x_exp_gce,
249 x_exp_vce,
250 x_winter_a_levels,
251 x_prev_a_levels,
252 x_prev_as_levels,
253 x_sqa,
254 x_btec,
255 x_ib,
256 x_ilc,
257 x_ailc,
258 x_ksi,
259 x_roa,
260 x_manual,
261 x_oeq,
262 x_prev_oeq,
263 x_vqi,
264 x_seq_updated_date,
265 x_creation_date,
266 x_created_by,
267 x_last_update_date,
268 x_last_updated_by,
269 x_last_update_login
270 );
271
272 IF (p_action = 'INSERT') THEN
273 -- Call all the procedures related to Before Insert.
274 IF ( get_pk_for_validation(
275 new_references.exp_qual_sum_id
276 )
277 ) THEN
278 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
279 igs_ge_msg_stack.add;
280 app_exception.raise_exception;
281 END IF;
282 check_parent_existance;
283 ELSIF (p_action = 'UPDATE') THEN
284 -- Call all the procedures related to Before Update.
285 check_parent_existance;
286 ELSIF (p_action = 'VALIDATE_INSERT') THEN
287 -- Call all the procedures related to Before Insert.
288 IF ( get_pk_for_validation (
289 new_references.exp_qual_sum_id
290 )
291 ) THEN
292 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
293 igs_ge_msg_stack.add;
294 app_exception.raise_exception;
295 END IF;
296 END IF;
297
298 END before_dml;
299
300
301 PROCEDURE insert_row (
302 x_rowid IN OUT NOCOPY VARCHAR2,
303 x_exp_qual_sum_id IN OUT NOCOPY NUMBER,
304 x_person_id IN NUMBER,
305 x_exp_gce IN NUMBER,
306 x_exp_vce IN NUMBER,
307 x_winter_a_levels IN NUMBER,
308 x_prev_a_levels IN NUMBER,
309 x_prev_as_levels IN NUMBER,
310 x_sqa IN VARCHAR2,
311 x_btec IN VARCHAR2,
312 x_ib IN VARCHAR2,
313 x_ilc IN VARCHAR2,
314 x_ailc IN VARCHAR2,
315 x_ksi IN VARCHAR2,
316 x_roa IN VARCHAR2,
317 x_manual IN VARCHAR2,
318 x_oeq IN VARCHAR2,
319 x_prev_oeq IN VARCHAR2,
320 x_vqi IN VARCHAR2,
321 x_seq_updated_date IN DATE,
322 x_mode IN VARCHAR2
323 ) AS
324 /*
325 || Created By : [email protected]
326 || Created On : 01-FEB-2002
327 || Purpose : Handles the INSERT DML logic for the table.
328 || Known limitations, enhancements or remarks :
329 || Change History :
330 || Who When What
331 || (reverse chronological order - newest change first)
332 */
333 CURSOR c IS
334 SELECT rowid
335 FROM igs_uc_exp_qual_sum
336 WHERE exp_qual_sum_id = x_exp_qual_sum_id;
337
338 x_last_update_date DATE;
339 x_last_updated_by NUMBER;
340 x_last_update_login NUMBER;
341
342 BEGIN
343
344 x_last_update_date := SYSDATE;
345 IF (x_mode = 'I') THEN
346 x_last_updated_by := 1;
347 x_last_update_login := 0;
348 ELSIF (x_mode = 'R') THEN
349 x_last_updated_by := fnd_global.user_id;
350 IF (x_last_updated_by IS NULL) THEN
351 x_last_updated_by := -1;
352 END IF;
353 x_last_update_login := fnd_global.login_id;
354 IF (x_last_update_login IS NULL) THEN
355 x_last_update_login := -1;
356 END IF;
357 ELSE
358 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
359 igs_ge_msg_stack.add;
360 app_exception.raise_exception;
361 END IF;
362
363 SELECT igs_uc_exp_qual_sum_s.NEXTVAL
364 INTO x_exp_qual_sum_id
365 FROM dual;
366
367 before_dml(
368 p_action => 'INSERT',
369 x_rowid => x_rowid,
370 x_exp_qual_sum_id => x_exp_qual_sum_id,
371 x_person_id => x_person_id,
372 x_exp_gce => x_exp_gce,
373 x_exp_vce => x_exp_vce,
374 x_winter_a_levels => x_winter_a_levels,
375 x_prev_a_levels => x_prev_a_levels,
376 x_prev_as_levels => x_prev_as_levels,
377 x_sqa => x_sqa,
378 x_btec => x_btec,
379 x_ib => x_ib,
380 x_ilc => x_ilc,
381 x_ailc => x_ailc,
382 x_ksi => x_ksi,
383 x_roa => x_roa,
384 x_manual => x_manual,
385 x_oeq => x_oeq,
386 x_prev_oeq => x_prev_oeq,
390 x_created_by => x_last_updated_by,
387 x_vqi => x_vqi,
388 x_seq_updated_date => x_seq_updated_date,
389 x_creation_date => x_last_update_date,
391 x_last_update_date => x_last_update_date,
392 x_last_updated_by => x_last_updated_by,
393 x_last_update_login => x_last_update_login
394 );
395
396 INSERT INTO igs_uc_exp_qual_sum (
397 exp_qual_sum_id,
398 person_id,
399 exp_gce,
400 exp_vce,
401 winter_a_levels,
402 prev_a_levels,
403 prev_as_levels,
404 sqa,
405 btec,
406 ib,
407 ilc,
408 ailc,
409 ksi,
410 roa,
411 manual,
412 oeq,
413 prev_oeq,
414 vqi,
415 seq_updated_date,
416 creation_date,
417 created_by,
418 last_update_date,
419 last_updated_by,
420 last_update_login
421 ) VALUES (
422 new_references.exp_qual_sum_id,
423 new_references.person_id,
424 new_references.exp_gce,
425 new_references.exp_vce,
426 new_references.winter_a_levels,
427 new_references.prev_a_levels,
428 new_references.prev_as_levels,
429 new_references.sqa,
430 new_references.btec,
431 new_references.ib,
432 new_references.ilc,
433 new_references.ailc,
434 new_references.ksi,
435 new_references.roa,
436 new_references.manual,
437 new_references.oeq,
438 new_references.prev_oeq,
439 new_references.vqi,
440 new_references.seq_updated_date,
441 x_last_update_date,
442 x_last_updated_by,
443 x_last_update_date,
444 x_last_updated_by,
445 x_last_update_login
446 );
447
448 OPEN c;
449 FETCH c INTO x_rowid;
450 IF (c%NOTFOUND) THEN
451 CLOSE c;
452 RAISE NO_DATA_FOUND;
453 END IF;
454 CLOSE c;
455
456 END insert_row;
457
458
459 PROCEDURE lock_row (
460 x_rowid IN VARCHAR2,
461 x_exp_qual_sum_id IN NUMBER,
462 x_person_id IN NUMBER,
463 x_exp_gce IN NUMBER,
464 x_exp_vce IN NUMBER,
465 x_winter_a_levels IN NUMBER,
466 x_prev_a_levels IN NUMBER,
467 x_prev_as_levels IN NUMBER,
468 x_sqa IN VARCHAR2,
469 x_btec IN VARCHAR2,
470 x_ib IN VARCHAR2,
471 x_ilc IN VARCHAR2,
472 x_ailc IN VARCHAR2,
473 x_ksi IN VARCHAR2,
474 x_roa IN VARCHAR2,
475 x_manual IN VARCHAR2,
476 x_oeq IN VARCHAR2,
477 x_prev_oeq IN VARCHAR2,
478 x_vqi IN VARCHAR2,
479 x_seq_updated_date IN DATE
480 ) AS
481 /*
482 || Created By : [email protected]
483 || Created On : 01-FEB-2002
484 || Purpose : Handles the LOCK mechanism for the table.
485 || Known limitations, enhancements or remarks :
486 || Change History :
487 || Who When What
488 || (reverse chronological order - newest change first)
489 */
490 CURSOR c1 IS
491 SELECT
492 person_id,
493 exp_gce,
494 exp_vce,
495 winter_a_levels,
496 prev_a_levels,
497 prev_as_levels,
498 sqa,
499 btec,
500 ib,
501 ilc,
502 ailc,
503 ksi,
504 roa,
505 manual,
506 oeq,
507 prev_oeq,
508 vqi,
509 seq_updated_date
510 FROM igs_uc_exp_qual_sum
511 WHERE rowid = x_rowid
512 FOR UPDATE NOWAIT;
513
514 tlinfo c1%ROWTYPE;
515
516 BEGIN
517
518 OPEN c1;
519 FETCH c1 INTO tlinfo;
520 IF (c1%notfound) THEN
521 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
522 igs_ge_msg_stack.add;
523 CLOSE c1;
524 app_exception.raise_exception;
525 RETURN;
526 END IF;
527 CLOSE c1;
528
529 IF (
530 (tlinfo.person_id = x_person_id)
531 AND ((tlinfo.exp_gce = x_exp_gce) OR ((tlinfo.exp_gce IS NULL) AND (X_exp_gce IS NULL)))
532 AND ((tlinfo.exp_vce = x_exp_vce) OR ((tlinfo.exp_vce IS NULL) AND (X_exp_vce IS NULL)))
533 AND ((tlinfo.winter_a_levels = x_winter_a_levels) OR ((tlinfo.winter_a_levels IS NULL) AND (X_winter_a_levels IS NULL)))
534 AND ((tlinfo.prev_a_levels = x_prev_a_levels) OR ((tlinfo.prev_a_levels IS NULL) AND (X_prev_a_levels IS NULL)))
538 AND ((tlinfo.ib = x_ib) OR ((tlinfo.ib IS NULL) AND (X_ib IS NULL)))
535 AND ((tlinfo.prev_as_levels = x_prev_as_levels) OR ((tlinfo.prev_as_levels IS NULL) AND (X_prev_as_levels IS NULL)))
536 AND ((tlinfo.sqa = x_sqa) OR ((tlinfo.sqa IS NULL) AND (X_sqa IS NULL)))
537 AND ((tlinfo.btec = x_btec) OR ((tlinfo.btec IS NULL) AND (X_btec IS NULL)))
539 AND ((tlinfo.ilc = x_ilc) OR ((tlinfo.ilc IS NULL) AND (X_ilc IS NULL)))
540 AND ((tlinfo.ailc = x_ailc) OR ((tlinfo.ailc IS NULL) AND (X_ailc IS NULL)))
541 AND ((tlinfo.ksi = x_ksi) OR ((tlinfo.ksi IS NULL) AND (X_ksi IS NULL)))
542 AND ((tlinfo.roa = x_roa) OR ((tlinfo.roa IS NULL) AND (X_roa IS NULL)))
543 AND ((tlinfo.manual = x_manual) OR ((tlinfo.manual IS NULL) AND (X_manual IS NULL)))
544 AND ((tlinfo.oeq = x_oeq) OR ((tlinfo.oeq IS NULL) AND (X_oeq IS NULL)))
545 AND ((tlinfo.prev_oeq = x_prev_oeq) OR ((tlinfo.prev_oeq IS NULL) AND (X_prev_oeq IS NULL)))
546 AND ((tlinfo.vqi = x_vqi) OR ((tlinfo.vqi IS NULL) AND (X_vqi IS NULL)))
547 AND (tlinfo.seq_updated_date = x_seq_updated_date)
548 ) THEN
549 NULL;
550 ELSE
551 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
552 igs_ge_msg_stack.add;
553 app_exception.raise_exception;
554 END IF;
555
556 RETURN;
557
558 END lock_row;
559
560
561 PROCEDURE update_row (
562 x_rowid IN VARCHAR2,
563 x_exp_qual_sum_id IN NUMBER,
564 x_person_id IN NUMBER,
565 x_exp_gce IN NUMBER,
566 x_exp_vce IN NUMBER,
567 x_winter_a_levels IN NUMBER,
568 x_prev_a_levels IN NUMBER,
569 x_prev_as_levels IN NUMBER,
570 x_sqa IN VARCHAR2,
571 x_btec IN VARCHAR2,
572 x_ib IN VARCHAR2,
573 x_ilc IN VARCHAR2,
574 x_ailc IN VARCHAR2,
575 x_ksi IN VARCHAR2,
576 x_roa IN VARCHAR2,
577 x_manual IN VARCHAR2,
578 x_oeq IN VARCHAR2,
579 x_prev_oeq IN VARCHAR2,
580 x_vqi IN VARCHAR2,
581 x_seq_updated_date IN DATE,
582 x_mode IN VARCHAR2
583 ) AS
584 /*
585 || Created By : [email protected]
586 || Created On : 01-FEB-2002
587 || Purpose : Handles the UPDATE DML logic for the table.
588 || Known limitations, enhancements or remarks :
589 || Change History :
590 || Who When What
591 || (reverse chronological order - newest change first)
592 */
593 x_last_update_date DATE ;
594 x_last_updated_by NUMBER;
595 x_last_update_login NUMBER;
596
597 BEGIN
598
599 x_last_update_date := SYSDATE;
600 IF (X_MODE = 'I') THEN
601 x_last_updated_by := 1;
602 x_last_update_login := 0;
603 ELSIF (x_mode = 'R') THEN
604 x_last_updated_by := fnd_global.user_id;
605 IF x_last_updated_by IS NULL THEN
606 x_last_updated_by := -1;
607 END IF;
608 x_last_update_login := fnd_global.login_id;
609 IF (x_last_update_login IS NULL) THEN
610 x_last_update_login := -1;
611 END IF;
612 ELSE
613 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
614 igs_ge_msg_stack.add;
615 app_exception.raise_exception;
616 END IF;
617
618 before_dml(
619 p_action => 'UPDATE',
620 x_rowid => x_rowid,
621 x_exp_qual_sum_id => x_exp_qual_sum_id,
622 x_person_id => x_person_id,
623 x_exp_gce => x_exp_gce,
624 x_exp_vce => x_exp_vce,
625 x_winter_a_levels => x_winter_a_levels,
626 x_prev_a_levels => x_prev_a_levels,
627 x_prev_as_levels => x_prev_as_levels,
628 x_sqa => x_sqa,
629 x_btec => x_btec,
630 x_ib => x_ib,
631 x_ilc => x_ilc,
632 x_ailc => x_ailc,
633 x_ksi => x_ksi,
634 x_roa => x_roa,
635 x_manual => x_manual,
636 x_oeq => x_oeq,
637 x_prev_oeq => x_prev_oeq,
638 x_vqi => x_vqi,
639 x_seq_updated_date => x_seq_updated_date,
640 x_creation_date => x_last_update_date,
641 x_created_by => x_last_updated_by,
642 x_last_update_date => x_last_update_date,
643 x_last_updated_by => x_last_updated_by,
644 x_last_update_login => x_last_update_login
645 );
646
650 exp_gce = new_references.exp_gce,
647 UPDATE igs_uc_exp_qual_sum
648 SET
649 person_id = new_references.person_id,
651 exp_vce = new_references.exp_vce,
652 winter_a_levels = new_references.winter_a_levels,
653 prev_a_levels = new_references.prev_a_levels,
654 prev_as_levels = new_references.prev_as_levels,
655 sqa = new_references.sqa,
656 btec = new_references.btec,
657 ib = new_references.ib,
658 ilc = new_references.ilc,
659 ailc = new_references.ailc,
660 ksi = new_references.ksi,
661 roa = new_references.roa,
662 manual = new_references.manual,
663 oeq = new_references.oeq,
664 prev_oeq = new_references.prev_oeq,
665 vqi = new_references.vqi,
666 seq_updated_date = new_references.seq_updated_date,
667 last_update_date = x_last_update_date,
668 last_updated_by = x_last_updated_by,
669 last_update_login = x_last_update_login
670 WHERE rowid = x_rowid;
671
672 IF (SQL%NOTFOUND) THEN
673 RAISE NO_DATA_FOUND;
674 END IF;
675
676 END update_row;
677
678
679 PROCEDURE add_row (
680 x_rowid IN OUT NOCOPY VARCHAR2,
681 x_exp_qual_sum_id IN OUT NOCOPY NUMBER,
682 x_person_id IN NUMBER,
683 x_exp_gce IN NUMBER,
684 x_exp_vce IN NUMBER,
685 x_winter_a_levels IN NUMBER,
686 x_prev_a_levels IN NUMBER,
687 x_prev_as_levels IN NUMBER,
688 x_sqa IN VARCHAR2,
689 x_btec IN VARCHAR2,
690 x_ib IN VARCHAR2,
691 x_ilc IN VARCHAR2,
692 x_ailc IN VARCHAR2,
693 x_ksi IN VARCHAR2,
694 x_roa IN VARCHAR2,
695 x_manual IN VARCHAR2,
696 x_oeq IN VARCHAR2,
697 x_prev_oeq IN VARCHAR2,
698 x_vqi IN VARCHAR2,
699 x_seq_updated_date IN DATE,
700 x_mode IN VARCHAR2
701 ) AS
702 /*
703 || Created By : [email protected]
704 || Created On : 01-FEB-2002
705 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
706 || Known limitations, enhancements or remarks :
707 || Change History :
708 || Who When What
709 || (reverse chronological order - newest change first)
710 */
711 CURSOR c1 IS
712 SELECT rowid
713 FROM igs_uc_exp_qual_sum
714 WHERE exp_qual_sum_id = x_exp_qual_sum_id;
715
716 BEGIN
717
718 OPEN c1;
719 FETCH c1 INTO x_rowid;
720 IF (c1%NOTFOUND) THEN
721 CLOSE c1;
722
723 insert_row (
724 x_rowid,
725 x_exp_qual_sum_id,
726 x_person_id,
727 x_exp_gce,
728 x_exp_vce,
729 x_winter_a_levels,
730 x_prev_a_levels,
731 x_prev_as_levels,
732 x_sqa,
733 x_btec,
734 x_ib,
735 x_ilc,
736 x_ailc,
737 x_ksi,
738 x_roa,
739 x_manual,
740 x_oeq,
741 x_prev_oeq,
742 x_vqi,
743 x_seq_updated_date,
744 x_mode
745 );
746 RETURN;
747 END IF;
748 CLOSE c1;
749
750 update_row (
751 x_rowid,
752 x_exp_qual_sum_id,
753 x_person_id,
754 x_exp_gce,
755 x_exp_vce,
756 x_winter_a_levels,
757 x_prev_a_levels,
758 x_prev_as_levels,
759 x_sqa,
760 x_btec,
761 x_ib,
762 x_ilc,
763 x_ailc,
764 x_ksi,
765 x_roa,
766 x_manual,
767 x_oeq,
768 x_prev_oeq,
769 x_vqi,
770 x_seq_updated_date,
771 x_mode
772 );
773
774 END add_row;
775
776
777 PROCEDURE delete_row (
778 x_rowid IN VARCHAR2
779 ) AS
780 /*
781 || Created By : [email protected]
782 || Created On : 01-FEB-2002
783 || Purpose : Handles the DELETE DML logic for the table.
784 || Known limitations, enhancements or remarks :
785 || Change History :
786 || Who When What
787 || (reverse chronological order - newest change first)
788 */
789 BEGIN
790
791 before_dml (
792 p_action => 'DELETE',
793 x_rowid => x_rowid
794 );
795
796 DELETE FROM igs_uc_exp_qual_sum
797 WHERE rowid = x_rowid;
798
799 IF (SQL%NOTFOUND) THEN
800 RAISE NO_DATA_FOUND;
801 END IF;
802
803 END delete_row;
804
805
806 END igs_uc_exp_qual_sum_pkg;