1 PACKAGE BODY igs_uc_qual_dets_pkg AS
2 /* $Header: IGSXI37B.pls 120.3 2005/10/17 02:23:43 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_qual_dets%ROWTYPE;
6 new_references igs_uc_qual_dets%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_qual_dets_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_exam_level IN VARCHAR2 ,
14 x_subject_code IN VARCHAR2 ,
15 x_year IN NUMBER ,
16 x_sitting IN VARCHAR2 ,
17 x_awarding_body IN VARCHAR2 ,
18 x_grading_schema_cd IN VARCHAR2 ,
19 x_version_number IN NUMBER ,
20 x_predicted_result IN VARCHAR2 ,
21 x_approved_result IN VARCHAR2 ,
22 x_claimed_result IN VARCHAR2 ,
23 x_ucas_tariff IN NUMBER ,
24 x_imported_flag IN VARCHAR2 ,
25 x_imported_date IN DATE ,
26 x_creation_date IN DATE ,
27 x_created_by IN NUMBER ,
28 x_last_update_date IN DATE ,
29 x_last_updated_by IN NUMBER ,
30 x_last_update_login IN NUMBER
31 ) AS
32 /*
33 || Created By : [email protected]
34 || Created On : 12-FEB-2002
35 || Purpose : Initialises the Old and New references for the columns of the table.
36 || Known limitations, enhancements or remarks :
37 || Change History :
38 || Who When What
39 || (reverse chronological order - newest change first)
40 */
41
42 CURSOR cur_old_ref_values IS
43 SELECT *
44 FROM IGS_UC_QUAL_DETS
45 WHERE rowid = x_rowid;
46
47 BEGIN
48
49 l_rowid := x_rowid;
50
51 -- Code for setting the Old and New Reference Values.
52 -- Populate Old Values.
53 OPEN cur_old_ref_values;
54 FETCH cur_old_ref_values INTO old_references;
55 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
56 CLOSE cur_old_ref_values;
57 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
58 igs_ge_msg_stack.add;
59 app_exception.raise_exception;
60 RETURN;
61 END IF;
62 CLOSE cur_old_ref_values;
63
64 -- Populate New Values.
65 new_references.qual_dets_id := x_qual_dets_id;
66 new_references.person_id := x_person_id;
67 new_references.exam_level := x_exam_level;
68 new_references.subject_code := x_subject_code;
69 new_references.year := x_year;
70 new_references.sitting := x_sitting;
71 new_references.awarding_body := x_awarding_body;
72 new_references.grading_schema_cd := x_grading_schema_cd;
73 new_references.version_number := x_version_number;
74 new_references.predicted_result := x_predicted_result;
75 new_references.approved_result := x_approved_result;
76 new_references.claimed_result := x_claimed_result;
77 new_references.ucas_tariff := x_ucas_tariff;
78 new_references.imported_flag := x_imported_flag;
79 new_references.imported_date := x_imported_date;
80
81 IF (p_action = 'UPDATE') THEN
82 new_references.creation_date := old_references.creation_date;
83 new_references.created_by := old_references.created_by;
84 ELSE
85 new_references.creation_date := x_creation_date;
86 new_references.created_by := x_created_by;
87 END IF;
88
89 new_references.last_update_date := x_last_update_date;
90 new_references.last_updated_by := x_last_updated_by;
91 new_references.last_update_login := x_last_update_login;
92
93 END set_column_values;
94
95 PROCEDURE check_uniqueness AS
96 /*
97 || Created By : rbezawad
98 || Created On : 23-MAY-2002
99 || Purpose : Handles the Unique Constraint logic defined for the columns.
100 || Known limitations, enhancements or remarks :
101 || Change History :
102 || Who When What
103 || (reverse chronological order - newest change first)
104 */
105 BEGIN
106
107 IF ( get_uk_for_validation (
108 new_references.person_id,
109 new_references.exam_level,
110 new_references.subject_code,
111 new_references.year,
112 new_references.sitting,
113 new_references.awarding_body,
114 new_references.approved_result
115 )
116 ) THEN
117 fnd_message.set_name ('IGS', 'IGS_PE_QUAL_DUP_EXISTS');
118 igs_ge_msg_stack.add;
119 app_exception.raise_exception;
120 END IF;
121
122 END check_uniqueness;
123
124 FUNCTION get_uk_for_validation (
125 x_person_id IN NUMBER,
126 x_exam_level IN VARCHAR2,
127 x_subject_code IN VARCHAR2,
128 x_year IN NUMBER,
129 x_sitting IN VARCHAR2,
130 x_awarding_body IN VARCHAR2,
131 x_approved_result IN VARCHAR2
132 ) RETURN BOOLEAN AS
133 /*
134 || Created By : rbezawad
135 || Created On : 23-MAY-2002
136 || Purpose : Validates the Unique Keys of the table.
137 || Known limitations, enhancements or remarks :
138 || Change History :
139 || Who When What
140 || smaddali 11-jun-2002 added new field approved result to procedure
141 || get_uk_for_validation for bug 2409543
142 || (reverse chronological order - newest change first)
143 */
144 CURSOR cur_rowid IS
145 SELECT rowid
146 FROM igs_uc_qual_dets
147 WHERE person_id = x_person_id
148 AND exam_level = x_exam_level
149 AND ((subject_code = x_subject_code) OR (subject_code IS NULL AND x_subject_code IS NULL))
150 AND ((year = x_year) OR (year IS NULL AND x_year IS NULL))
151 AND ((sitting = x_sitting) OR (sitting IS NULL AND x_sitting IS NULL))
152 AND ((awarding_body = x_awarding_body) OR (awarding_body IS NULL AND x_awarding_body IS NULL))
153 AND ( (approved_result = x_approved_result) OR (approved_result IS NULL AND x_approved_result IS NULL) )
154 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
155
156 lv_rowid cur_rowid%RowType;
157
158 BEGIN
159
160 OPEN cur_rowid;
161 FETCH cur_rowid INTO lv_rowid;
162 IF (cur_rowid%FOUND) THEN
163 CLOSE cur_rowid;
164 RETURN (true);
165 ELSE
166 CLOSE cur_rowid;
167 RETURN(FALSE);
168 END IF;
169
170 END get_uk_for_validation ;
171
172 PROCEDURE check_parent_existance AS
173 /*
174 || Created By : [email protected]
175 || Created On : 12-FEB-2002
176 || Purpose : Checks for the existance of Parent records.
177 || Known limitations, enhancements or remarks :
178 || Change History :
179 || Who When What
180 || (reverSE chronological order - newest change first)
181 || smaddali 27-jun-2002 bug 2430139 modified the calls to patent tbhs by passing UPPER of columns
182 || grading_schema_cd, approved_result,claimd_result,predicted_result,exam_level,subject_code
183 || also trimming the result fields
184 || rbezawad 16-Dec-2002 1) Changed FK relation get_fk_pe_hz_parties to get_fk_igs_pe_person. So changed the get_pk...() call from igs_pe_hz_parties_pkg to igs_pe_person_pkg.
185 || 2) Removed the commented code which was checking awarding_body column value.
186 || 3) Uncommented the code which was checking subject_code value in igs_ps_fld_of_study_all_pkg.
187 || Modifications are done w.r.t. Bug 2541370.
188 */
189 BEGIN
190
191 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
192 (old_references.version_number = new_references.version_number) AND
193 (old_references.claimed_result = new_references.claimed_result)) OR
194 ((new_references.grading_schema_cd IS NULL) OR
195 (new_references.version_number IS NULL) OR
196 (new_references.claimed_result IS NULL))) THEN
197 NULL;
198 ELSIF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
199 UPPER(TRIM(new_references.grading_schema_cd)),
200 new_references.version_number,
201 UPPER(TRIM(new_references.claimed_result))
202 ) THEN
203 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
204 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_GRD_SCHEMA_CLAIM_RSLT'));
205 IGS_GE_MSG_STACK.ADD;
206 App_Exception.Raise_Exception;
207 END IF;
208
209 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
210 (old_references.version_number = new_references.version_number) AND
211 (old_references.predicted_result = new_references.predicted_result)) OR
212 ((new_references.grading_schema_cd IS NULL) OR
213 (new_references.version_number IS NULL) OR
214 (new_references.predicted_result IS NULL))) THEN
215 NULL;
216 ELSIF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
217 UPPER(TRIM(new_references.grading_schema_cd)),
218 new_references.version_number,
219 UPPER(TRIM(new_references.predicted_result))
220 ) THEN
221 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
222 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_GRD_SCHEMA_PRD_RSLT'));
223 IGS_GE_MSG_STACK.ADD;
224 App_Exception.Raise_Exception;
225 END IF;
226
227 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
228 (old_references.version_number = new_references.version_number) AND
229 (old_references.approved_result = new_references.approved_result)) OR
230 ((new_references.grading_schema_cd IS NULL) OR
231 (new_references.version_number IS NULL) OR
232 (new_references.approved_result IS NULL))) THEN
233 NULL;
234 ELSIF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
235 UPPER(TRIM(new_references.grading_schema_cd)),
236 new_references.version_number,
237 UPPER(TRIM(new_references.approved_result))
238 ) THEN
239 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
240 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_GRD_SCHEMA_APRD_RSLT'));
241 IGS_GE_MSG_STACK.ADD;
242 App_Exception.Raise_Exception;
243 END IF;
244
245 IF (((old_references.exam_level = new_references.exam_level)) OR
246 ((new_references.exam_level IS NULL))) THEN
247 NULL;
248 ELSIF NOT igs_ps_awd_pkg.get_pk_for_validation (
249 UPPER(TRIM(new_references.exam_level))
250 ) THEN
251 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
252 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_EXAM_LEVEL'));
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 END IF;
256
257 IF (((old_references.subject_code = new_references.subject_code)) OR
258 ((new_references.subject_code IS NULL))) THEN
259 NULL;
260 ELSIF NOT igs_ps_fld_of_study_pkg.get_pk_for_validation (
261 UPPER(TRIM(new_references.subject_code))
262 ) THEN
263 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
264 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SUBJECT'));
265 IGS_GE_MSG_STACK.ADD;
266 App_Exception.Raise_Exception;
267 END IF;
268
269 IF (((old_references.person_id = new_references.person_id)) OR
270 ((new_references.person_id IS NULL))) THEN
271 NULL;
272 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
273 new_references.person_id
274 ) THEN
275 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
276 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PERSON'));
277 IGS_GE_MSG_STACK.ADD;
278 App_Exception.Raise_Exception;
279 END IF;
280
281 END check_parent_existance;
282
283
284 PROCEDURE Check_Child_Existance AS
285 BEGIN
286 IGS_AV_STND_UNIT_LVL_PKG.GET_FK_IGS_UC_QUAL_DETS(
287 old_references.QUAL_DETS_ID
288 );
289 END Check_Child_Existance;
290
291
292
293 FUNCTION get_pk_for_validation (
294 x_qual_dets_id IN NUMBER
295 ) RETURN BOOLEAN AS
296 /*
297 || Created By : [email protected]
298 || Created On : 12-FEB-2002
299 || Purpose : Validates the Primary Key of the table.
300 || Known limitations, enhancements or remarks :
301 || Change History :
302 || Who When What
303 || (reverse chronological order - newest change first)
304 */
305 CURSOR cur_rowid IS
306 SELECT rowid
307 FROM igs_uc_qual_dets
308 WHERE qual_dets_id = x_qual_dets_id;
309
310 lv_rowid cur_rowid%RowType;
311
312 BEGIN
313
314 OPEN cur_rowid;
315 FETCH cur_rowid INTO lv_rowid;
316 IF (cur_rowid%FOUND) THEN
317 CLOSE cur_rowid;
318 RETURN(TRUE);
319 ELSE
320 CLOSE cur_rowid;
321 RETURN(FALSE);
322 END IF;
323
324 END get_pk_for_validation;
325
326
327 PROCEDURE get_fk_igs_as_grd_sch_grade (
328 x_grading_schema_cd IN VARCHAR2,
329 x_version_number IN NUMBER,
330 x_grade IN VARCHAR2
331 ) AS
332 /*
333 || Created By : [email protected]
334 || Created On : 12-FEB-2002
335 || Purpose : Validates the Foreign Keys for the table.
336 || Known limitations, enhancements or remarks :
337 || Change History :
338 || Who When What
339 || (reverse chronological order - newest change first)
340 */
341 CURSOR cur_rowid IS
342 SELECT rowid
343 FROM igs_uc_qual_dets
344 WHERE ((claimed_result = x_grade) AND
345 (grading_schema_cd = x_grading_schema_cd) AND
346 (version_number = x_version_number))
347 OR ((grading_schema_cd = x_grading_schema_cd) AND
348 (predicted_result = x_grade) AND
349 (version_number = x_version_number))
350 OR ((approved_result = x_grade) AND
351 (grading_schema_cd = x_grading_schema_cd) AND
352 (version_number = x_version_number));
353
354 lv_rowid cur_rowid%RowType;
355
356 BEGIN
357
358 OPEN cur_rowid;
359 FETCH cur_rowid INTO lv_rowid;
360 IF (cur_rowid%FOUND) THEN
361 CLOSE cur_rowid;
362 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
363 igs_ge_msg_stack.add;
364 app_exception.raise_exception;
365 RETURN;
366 END IF;
367 CLOSE cur_rowid;
368
369 END get_fk_igs_as_grd_sch_grade;
370
371
372 PROCEDURE get_fk_igs_ps_awd (
373 x_award_cd IN VARCHAR2
374 ) AS
375 /*
376 || Created By : [email protected]
377 || Created On : 12-FEB-2002
378 || Purpose : Validates the Foreign Keys for the table.
379 || Known limitations, enhancements or remarks :
380 || Change History :
381 || Who When What
382 || (reverse chronological order - newest change first)
383 */
384 CURSOR cur_rowid IS
385 SELECT rowid
386 FROM igs_uc_qual_dets
387 WHERE ((exam_level = x_award_cd));
388
389 lv_rowid cur_rowid%RowType;
390
391 BEGIN
392
393 OPEN cur_rowid;
394 FETCH cur_rowid INTO lv_rowid;
395 IF (cur_rowid%FOUND) THEN
396 CLOSE cur_rowid;
397 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
398 igs_ge_msg_stack.add;
399 app_exception.raise_exception;
400 RETURN;
401 END IF;
402 CLOSE cur_rowid;
403
404 END get_fk_igs_ps_awd;
405
406
407 PROCEDURE get_fk_igs_ps_fld_of_study_all (
408 x_field_of_study IN VARCHAR2
409 ) AS
410 /*
411 || Created By : [email protected]
412 || Created On : 12-FEB-2002
413 || Purpose : Validates the Foreign Keys for the table.
414 || Known limitations, enhancements or remarks :
415 || Change History :
416 || Who When What
417 || (reverse chronological order - newest change first)
418 */
419 CURSOR cur_rowid IS
420 SELECT rowid
421 FROM igs_uc_qual_dets
422 WHERE ((subject_code = x_field_of_study));
423
424 lv_rowid cur_rowid%RowType;
425
426 BEGIN
427
428 OPEN cur_rowid;
429 FETCH cur_rowid INTO lv_rowid;
430 IF (cur_rowid%FOUND) THEN
431 CLOSE cur_rowid;
432 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
433 igs_ge_msg_stack.add;
434 app_exception.raise_exception;
435 RETURN;
436 END IF;
437 CLOSE cur_rowid;
438
439 END get_fk_igs_ps_fld_of_study_all;
440
441
442 PROCEDURE get_ufk_hz_parties (
443 x_party_number IN VARCHAR2
444 ) AS
445 /*
446 || Created By : [email protected]
447 || Created On : 12-FEB-2002
448 || Purpose : Validates the Foreign Keys for the table.
449 || Known limitations, enhancements or remarks :
450 || Change History :
451 || Who When What
452 || (reverse chronological order - newest change first)
453 */
454 CURSOR cur_rowid IS
455 SELECT rowid
456 FROM igs_uc_qual_dets
457 WHERE ((awarding_body = x_party_number));
458
459 lv_rowid cur_rowid%RowType;
460
461 BEGIN
462
463 OPEN cur_rowid;
464 FETCH cur_rowid INTO lv_rowid;
465 IF (cur_rowid%FOUND) THEN
466 CLOSE cur_rowid;
467 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
468 igs_ge_msg_stack.add;
469 app_exception.raise_exception;
470 RETURN;
471 END IF;
472 CLOSE cur_rowid;
473
474 END get_ufk_hz_parties;
475
476
477 PROCEDURE get_fk_igs_pe_person (
478 x_person_id IN NUMBER
479 ) AS
480 /*
481 || Created By : [email protected]
482 || Created On : 12-FEB-2002
483 || Purpose : Validates the Foreign Keys for the table.
484 || Known limitations, enhancements or remarks :
485 || Change History :
486 || Who When What
487 || (reverse chronological order - newest change first)
488 || rbezawad 16-Dec-2002 Changed FK relation get_fk_pe_hz_parties to
489 || get_fk_igs_pe_person w.r.t. Bug 2541370.
490 */
491 CURSOR cur_rowid IS
492 SELECT rowid
493 FROM igs_uc_qual_dets
494 WHERE ((person_id = x_person_id));
495
496 lv_rowid cur_rowid%RowType;
497
498 BEGIN
499
500 OPEN cur_rowid;
501 FETCH cur_rowid INTO lv_rowid;
502 IF (cur_rowid%FOUND) THEN
503 CLOSE cur_rowid;
504 fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
505 igs_ge_msg_stack.add;
506 app_exception.raise_exception;
507 RETURN;
508 END IF;
509 CLOSE cur_rowid;
510
511 END get_fk_igs_pe_person;
512
513 PROCEDURE before_dml (
514 p_action IN VARCHAR2,
515 x_rowid IN VARCHAR2 ,
516 x_qual_dets_id IN NUMBER ,
517 x_person_id IN NUMBER ,
518 x_exam_level IN VARCHAR2 ,
519 x_subject_code IN VARCHAR2 ,
520 x_year IN NUMBER ,
521 x_sitting IN VARCHAR2 ,
522 x_awarding_body IN VARCHAR2 ,
523 x_grading_schema_cd IN VARCHAR2 ,
524 x_version_number IN NUMBER ,
525 x_predicted_result IN VARCHAR2 ,
526 x_approved_result IN VARCHAR2 ,
527 x_claimed_result IN VARCHAR2 ,
528 x_ucas_tariff IN NUMBER ,
529 x_imported_flag IN VARCHAR2 ,
530 x_imported_date IN DATE ,
531 x_creation_date IN DATE ,
532 x_created_by IN NUMBER ,
533 x_last_update_date IN DATE ,
534 x_last_updated_by IN NUMBER ,
535 x_last_update_login IN NUMBER
536 ) AS
537 /*
538 || Created By : [email protected]
539 || Created On : 12-FEB-2002
540 || Purpose : Initialises the columns, Checks Constraints, Calls the
541 || Trigger Handlers for the table, before any DML operation.
542 || Known limitations, enhancements or remarks :
543 || Change History :
544 || Who When What
545 || (reverse chronological order - newest change first)
546 */
547 BEGIN
548
549 set_column_values (
550 p_action,
551 x_rowid,
552 x_qual_dets_id,
553 x_person_id,
554 x_exam_level,
555 x_subject_code,
556 x_year,
557 x_sitting,
558 x_awarding_body,
559 x_grading_schema_cd,
560 x_version_number,
561 x_predicted_result,
562 x_approved_result,
563 x_claimed_result,
564 x_ucas_tariff,
565 x_imported_flag,
566 x_imported_date,
567 x_creation_date,
568 x_created_by,
569 x_last_update_date,
570 x_last_updated_by,
571 x_last_update_login
572 );
573
574 IF (p_action = 'INSERT') THEN
575 -- Call all the procedures related to Before Insert.
576 IF ( get_pk_for_validation(
577 new_references.qual_dets_id
578 )
579 ) THEN
580 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
581 igs_ge_msg_stack.add;
582 app_exception.raise_exception;
583 END IF;
584 check_parent_existance;
585 check_uniqueness;
586 ELSIF (p_action = 'UPDATE') THEN
587 -- Call all the procedures related to Before Update.
588 check_parent_existance;
589 check_uniqueness;
590 ELSIF (p_action = 'DELETE') THEN
591 -- Call all the procedures related to Before Delete.
592 Check_Child_Existance;
593 ELSIF (p_action = 'VALIDATE_INSERT') THEN
594 -- Call all the procedures related to Before Insert.
595 IF ( get_pk_for_validation (
596 new_references.qual_dets_id
597 )
598 ) THEN
599 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
600 igs_ge_msg_stack.add;
601 app_exception.raise_exception;
602 END IF;
603 check_uniqueness;
604 ELSIF (p_action = 'VALIDATE_DELETE') THEN
605 Check_Child_Existance;
606 END IF;
607
608 END before_dml;
609
610
611 PROCEDURE insert_row (
612 x_rowid IN OUT NOCOPY VARCHAR2,
613 x_qual_dets_id IN OUT NOCOPY NUMBER,
614 x_person_id IN NUMBER,
615 x_exam_level IN VARCHAR2,
616 x_subject_code IN VARCHAR2,
617 x_year IN NUMBER,
618 x_sitting IN VARCHAR2,
619 x_awarding_body IN VARCHAR2,
620 x_grading_schema_cd IN VARCHAR2,
621 x_version_number IN NUMBER,
622 x_predicted_result IN VARCHAR2,
623 x_approved_result IN VARCHAR2,
624 x_claimed_result IN VARCHAR2,
625 x_ucas_tariff IN NUMBER,
626 x_imported_flag IN VARCHAR2,
627 x_imported_date IN DATE,
628 x_mode IN VARCHAR2
629 ) AS
630 /*
631 || Created By : [email protected]
632 || Created On : 12-FEB-2002
633 || Purpose : Handles the INSERT DML logic for the table.
634 || Known limitations, enhancements or remarks :
635 || Change History :
636 || Who When What
637 || (reverse chronological order - newest change first)
638 */
639 CURSOR c IS
640 SELECT rowid
641 FROM igs_uc_qual_dets
642 WHERE qual_dets_id = x_qual_dets_id;
643
644 x_last_update_date DATE;
645 x_last_updated_by NUMBER;
646 x_last_update_login NUMBER;
647
648 BEGIN
649
650 x_last_update_date := SYSDATE;
651 IF (x_mode = 'I') THEN
652 x_last_updated_by := 1;
653 x_last_update_login := 0;
654 ELSIF (X_MODE IN ('R', 'S')) THEN
655 x_last_updated_by := fnd_global.user_id;
656 IF (x_last_updated_by IS NULL) THEN
657 x_last_updated_by := -1;
658 END IF;
659 x_last_update_login := fnd_global.login_id;
660 IF (x_last_update_login IS NULL) THEN
661 x_last_update_login := -1;
662 END IF;
663 ELSE
664 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
665 igs_ge_msg_stack.add;
666 app_exception.raise_exception;
667 END IF;
668
669 SELECT igs_uc_qual_dets_s.NEXTVAL
670 INTO x_qual_dets_id
671 FROM dual;
672
673 before_dml(
674 p_action => 'INSERT',
675 x_rowid => x_rowid,
676 x_qual_dets_id => x_qual_dets_id,
677 x_person_id => x_person_id,
678 x_exam_level => x_exam_level,
679 x_subject_code => x_subject_code,
680 x_year => x_year,
681 x_sitting => x_sitting,
682 x_awarding_body => x_awarding_body,
683 x_grading_schema_cd => x_grading_schema_cd,
684 x_version_number => x_version_number,
685 x_predicted_result => x_predicted_result,
686 x_approved_result => x_approved_result,
687 x_claimed_result => x_claimed_result,
688 x_ucas_tariff => x_ucas_tariff,
689 x_imported_flag => x_imported_flag,
690 x_imported_date => x_imported_date,
691 x_creation_date => x_last_update_date,
692 x_created_by => x_last_updated_by,
693 x_last_update_date => x_last_update_date,
694 x_last_updated_by => x_last_updated_by,
695 x_last_update_login => x_last_update_login
696 );
697
698 IF (x_mode = 'S') THEN
699 igs_sc_gen_001.set_ctx('R');
700 END IF;
701 INSERT INTO igs_uc_qual_dets (
702 qual_dets_id,
703 person_id,
704 exam_level,
705 subject_code,
706 year,
707 sitting,
708 awarding_body,
709 grading_schema_cd,
710 version_number,
711 predicted_result,
712 approved_result,
713 claimed_result,
714 ucas_tariff,
715 imported_flag,
716 imported_date,
717 creation_date,
718 created_by,
719 last_update_date,
720 last_updated_by,
721 last_update_login
722 ) VALUES (
723 new_references.qual_dets_id,
724 new_references.person_id,
725 new_references.exam_level,
726 new_references.subject_code,
727 new_references.year,
728 new_references.sitting,
729 new_references.awarding_body,
730 new_references.grading_schema_cd,
731 new_references.version_number,
732 new_references.predicted_result,
733 new_references.approved_result,
734 new_references.claimed_result,
735 new_references.ucas_tariff,
736 new_references.imported_flag,
737 new_references.imported_date,
738 x_last_update_date,
739 x_last_updated_by,
740 x_last_update_date,
741 x_last_updated_by,
742 x_last_update_login
743 );
744 IF (x_mode = 'S') THEN
745 igs_sc_gen_001.unset_ctx('R');
746 END IF;
747
748
749 OPEN c;
750 FETCH c INTO x_rowid;
751 IF (c%NOTFOUND) THEN
752 CLOSE c;
753 RAISE NO_DATA_FOUND;
754 END IF;
755 CLOSE c;
756
757
758 EXCEPTION
759 WHEN OTHERS THEN
760 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
761 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
762 fnd_message.set_token ('ERR_CD', SQLCODE);
763 igs_ge_msg_stack.add;
764 igs_sc_gen_001.unset_ctx('R');
765 app_exception.raise_exception;
766 ELSE
767 igs_sc_gen_001.unset_ctx('R');
768 RAISE;
769 END IF;
770 END insert_row;
771
772
773 PROCEDURE lock_row (
774 x_rowid IN VARCHAR2,
775 x_qual_dets_id IN NUMBER,
776 x_person_id IN NUMBER,
777 x_exam_level IN VARCHAR2,
778 x_subject_code IN VARCHAR2,
779 x_year IN NUMBER,
780 x_sitting IN VARCHAR2,
781 x_awarding_body IN VARCHAR2,
782 x_grading_schema_cd IN VARCHAR2,
783 x_version_number IN NUMBER,
784 x_predicted_result IN VARCHAR2,
785 x_approved_result IN VARCHAR2,
786 x_claimed_result IN VARCHAR2,
787 x_ucas_tariff IN NUMBER,
788 x_imported_flag IN VARCHAR2,
789 x_imported_date IN DATE
790 ) AS
791 /*
792 || Created By : [email protected]
793 || Created On : 12-FEB-2002
794 || Purpose : Handles the LOCK mechanism for the table.
795 || Known limitations, enhancements or remarks :
796 || Change History :
797 || Who When What
798 || (reverse chronological order - newest change first)
799 */
800 CURSOR c1 IS
801 SELECT
802 person_id,
803 exam_level,
804 subject_code,
805 year,
806 sitting,
807 awarding_body,
808 grading_schema_cd,
809 version_number,
810 predicted_result,
811 approved_result,
812 claimed_result,
813 ucas_tariff,
814 imported_flag,
815 imported_date
816 FROM igs_uc_qual_dets
817 WHERE rowid = x_rowid
818 FOR UPDATE NOWAIT;
819
820 tlinfo c1%ROWTYPE;
821
822 BEGIN
823
824 OPEN c1;
825 FETCH c1 INTO tlinfo;
826 IF (c1%notfound) THEN
827 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
828 igs_ge_msg_stack.add;
829 CLOSE c1;
830 app_exception.raise_exception;
831 RETURN;
832 END IF;
833 CLOSE c1;
834
835 IF (
836 (tlinfo.person_id = x_person_id)
837 AND (tlinfo.exam_level = x_exam_level)
838 AND ((tlinfo.subject_code = x_subject_code) OR ((tlinfo.subject_code IS NULL) AND (X_subject_code IS NULL)))
839 AND ((tlinfo.year = x_year) OR ((tlinfo.year IS NULL) AND (X_year IS NULL)))
840 AND ((tlinfo.sitting = x_sitting) OR ((tlinfo.sitting IS NULL) AND (X_sitting IS NULL)))
841 AND ((tlinfo.awarding_body = x_awarding_body) OR ((tlinfo.awarding_body IS NULL) AND (X_awarding_body IS NULL)))
842 AND ((tlinfo.grading_schema_cd = x_grading_schema_cd) OR ((tlinfo.grading_schema_cd IS NULL) AND (X_grading_schema_cd IS NULL)))
843 AND ((tlinfo.version_number = x_version_number) OR ((tlinfo.version_number IS NULL) AND (X_version_number IS NULL)))
844 AND ((tlinfo.predicted_result = x_predicted_result) OR ((tlinfo.predicted_result IS NULL) AND (X_predicted_result IS NULL)))
845 AND ((tlinfo.approved_result = x_approved_result) OR ((tlinfo.approved_result IS NULL) AND (X_approved_result IS NULL)))
846 AND ((tlinfo.claimed_result = x_claimed_result) OR ((tlinfo.claimed_result IS NULL) AND (X_claimed_result IS NULL)))
847 AND ((tlinfo.ucas_tariff = x_ucas_tariff) OR ((tlinfo.ucas_tariff IS NULL) AND (X_ucas_tariff IS NULL)))
848 AND ((tlinfo.imported_flag = x_imported_flag) OR ((tlinfo.imported_flag IS NULL) AND (X_imported_flag IS NULL)))
849 AND (tlinfo.imported_date = x_imported_date)
850 ) THEN
851 NULL;
852 ELSE
853 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
854 igs_ge_msg_stack.add;
855 app_exception.raise_exception;
856 END IF;
857
858 RETURN;
859
860 END lock_row;
861
862
863 PROCEDURE update_row (
864 x_rowid IN VARCHAR2,
865 x_qual_dets_id IN NUMBER,
866 x_person_id IN NUMBER,
867 x_exam_level IN VARCHAR2,
868 x_subject_code IN VARCHAR2,
869 x_year IN NUMBER,
870 x_sitting IN VARCHAR2,
871 x_awarding_body IN VARCHAR2,
872 x_grading_schema_cd IN VARCHAR2,
873 x_version_number IN NUMBER,
874 x_predicted_result IN VARCHAR2,
875 x_approved_result IN VARCHAR2,
876 x_claimed_result IN VARCHAR2,
877 x_ucas_tariff IN NUMBER,
878 x_imported_flag IN VARCHAR2,
879 x_imported_date IN DATE,
880 x_mode IN VARCHAR2
881 ) AS
882 /*
883 || Created By : [email protected]
884 || Created On : 12-FEB-2002
885 || Purpose : Handles the UPDATE DML logic for the table.
886 || Known limitations, enhancements or remarks :
887 || Change History :
888 || Who When What
889 || (reverse chronological order - newest change first)
890 */
891 x_last_update_date DATE ;
892 x_last_updated_by NUMBER;
893 x_last_update_login NUMBER;
894
895 BEGIN
896
897 x_last_update_date := SYSDATE;
898 IF (X_MODE = 'I') THEN
899 x_last_updated_by := 1;
900 x_last_update_login := 0;
901 ELSIF (X_MODE IN ('R', 'S')) THEN
902 x_last_updated_by := fnd_global.user_id;
903 IF x_last_updated_by IS NULL THEN
904 x_last_updated_by := -1;
905 END IF;
906 x_last_update_login := fnd_global.login_id;
907 IF (x_last_update_login IS NULL) THEN
908 x_last_update_login := -1;
909 END IF;
910 ELSE
911 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
912 igs_ge_msg_stack.add;
913 app_exception.raise_exception;
914 END IF;
915
916 before_dml(
917 p_action => 'UPDATE',
918 x_rowid => x_rowid,
919 x_qual_dets_id => x_qual_dets_id,
920 x_person_id => x_person_id,
921 x_exam_level => x_exam_level,
922 x_subject_code => x_subject_code,
923 x_year => x_year,
924 x_sitting => x_sitting,
925 x_awarding_body => x_awarding_body,
926 x_grading_schema_cd => x_grading_schema_cd,
927 x_version_number => x_version_number,
928 x_predicted_result => x_predicted_result,
929 x_approved_result => x_approved_result,
930 x_claimed_result => x_claimed_result,
931 x_ucas_tariff => x_ucas_tariff,
932 x_imported_flag => x_imported_flag,
933 x_imported_date => x_imported_date,
934 x_creation_date => x_last_update_date,
935 x_created_by => x_last_updated_by,
936 x_last_update_date => x_last_update_date,
937 x_last_updated_by => x_last_updated_by,
938 x_last_update_login => x_last_update_login
939 );
940
941 IF (x_mode = 'S') THEN
942 igs_sc_gen_001.set_ctx('R');
943 END IF;
944 UPDATE igs_uc_qual_dets
945 SET
946 person_id = new_references.person_id,
947 exam_level = new_references.exam_level,
948 subject_code = new_references.subject_code,
949 year = new_references.year,
950 sitting = new_references.sitting,
951 awarding_body = new_references.awarding_body,
952 grading_schema_cd = new_references.grading_schema_cd,
953 version_number = new_references.version_number,
954 predicted_result = new_references.predicted_result,
955 approved_result = new_references.approved_result,
956 claimed_result = new_references.claimed_result,
957 ucas_tariff = new_references.ucas_tariff,
958 imported_flag = new_references.imported_flag,
959 imported_date = new_references.imported_date,
960 last_update_date = x_last_update_date,
961 last_updated_by = x_last_updated_by,
962 last_update_login = x_last_update_login
963 WHERE rowid = x_rowid;
964
965 IF (SQL%NOTFOUND) THEN
966 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
967 igs_ge_msg_stack.add;
968 igs_sc_gen_001.unset_ctx('R');
969 app_exception.raise_exception;
970 END IF;
971 IF (x_mode = 'S') THEN
972 igs_sc_gen_001.unset_ctx('R');
973 END IF;
974
975 EXCEPTION
976 WHEN OTHERS THEN
977 IF (SQLCODE = (-28115)) THEN
978 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
979 fnd_message.set_token ('ERR_CD', SQLCODE);
980 igs_ge_msg_stack.add;
981 igs_sc_gen_001.unset_ctx('R');
982 app_exception.raise_exception;
983 ELSE
984 igs_sc_gen_001.unset_ctx('R');
985 RAISE;
986 END IF;
987
988 END update_row;
989
990
991 PROCEDURE add_row (
992 x_rowid IN OUT NOCOPY VARCHAR2,
993 x_qual_dets_id IN OUT NOCOPY NUMBER,
994 x_person_id IN NUMBER,
995 x_exam_level IN VARCHAR2,
996 x_subject_code IN VARCHAR2,
997 x_year IN NUMBER,
998 x_sitting IN VARCHAR2,
999 x_awarding_body IN VARCHAR2,
1000 x_grading_schema_cd IN VARCHAR2,
1001 x_version_number IN NUMBER,
1002 x_predicted_result IN VARCHAR2,
1003 x_approved_result IN VARCHAR2,
1004 x_claimed_result IN VARCHAR2,
1005 x_ucas_tariff IN NUMBER,
1006 x_imported_flag IN VARCHAR2,
1007 x_imported_date IN DATE,
1008 x_mode IN VARCHAR2
1009 ) AS
1010 /*
1011 || Created By : [email protected]
1012 || Created On : 12-FEB-2002
1013 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1014 || Known limitations, enhancements or remarks :
1015 || Change History :
1016 || Who When What
1017 || (reverse chronological order - newest change first)
1018 */
1019 CURSOR c1 IS
1020 SELECT rowid
1021 FROM igs_uc_qual_dets
1022 WHERE qual_dets_id = x_qual_dets_id;
1023
1024 BEGIN
1025
1026 OPEN c1;
1027 FETCH c1 INTO x_rowid;
1028 IF (c1%NOTFOUND) THEN
1029 CLOSE c1;
1030
1031 insert_row (
1032 x_rowid,
1033 x_qual_dets_id,
1034 x_person_id,
1035 x_exam_level,
1036 x_subject_code,
1037 x_year,
1038 x_sitting,
1039 x_awarding_body,
1040 x_grading_schema_cd,
1041 x_version_number,
1042 x_predicted_result,
1043 x_approved_result,
1044 x_claimed_result,
1045 x_ucas_tariff,
1046 x_imported_flag,
1047 x_imported_date,
1048 x_mode
1049 );
1050 RETURN;
1051 END IF;
1052 CLOSE c1;
1053
1054 update_row (
1055 x_rowid,
1056 x_qual_dets_id,
1057 x_person_id,
1058 x_exam_level,
1059 x_subject_code,
1060 x_year,
1061 x_sitting,
1062 x_awarding_body,
1063 x_grading_schema_cd,
1064 x_version_number,
1065 x_predicted_result,
1066 x_approved_result,
1067 x_claimed_result,
1068 x_ucas_tariff,
1069 x_imported_flag,
1070 x_imported_date,
1071 x_mode
1072 );
1073
1074 END add_row;
1075
1076
1077 PROCEDURE delete_row (
1078 x_rowid IN VARCHAR2,
1079 x_mode IN VARCHAR2
1080 ) AS
1081 /*
1082 || Created By : [email protected]
1083 || Created On : 12-FEB-2002
1084 || Purpose : Handles the DELETE DML logic for the table.
1085 || Known limitations, enhancements or remarks :
1086 || Change History :
1087 || Who When What
1088 || (reverse chronological order - newest change first)
1089 */
1090 BEGIN
1091
1092 before_dml (
1093 p_action => 'DELETE',
1094 x_rowid => x_rowid
1095 );
1096
1097 IF (x_mode = 'S') THEN
1098 igs_sc_gen_001.set_ctx('R');
1099 END IF;
1100 DELETE FROM igs_uc_qual_dets
1101 WHERE rowid = x_rowid;
1102
1103 IF (SQL%NOTFOUND) THEN
1104 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1105 igs_ge_msg_stack.add;
1106 igs_sc_gen_001.unset_ctx('R');
1107 app_exception.raise_exception;
1108 END IF;
1109 IF (x_mode = 'S') THEN
1110 igs_sc_gen_001.unset_ctx('R');
1111 END IF;
1112
1113
1114 END delete_row;
1115
1116
1117 END igs_uc_qual_dets_pkg;
[Home] [Help]