DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_RE_THESIS_EXAM_PKG

Source


1 PACKAGE BODY IGS_RE_THESIS_EXAM_PKG AS
2 /* $Header: IGSRI16B.pls 120.1 2005/07/04 00:42:30 appldev ship $ */
3   -------------------------------------------------------------------------------------------
4   --Change History:
5   --Who         When            What
6   --Nishikant   19NOV2002       Bug#2661533. The call to the function IGS_RE_VAL_TEX.resp_val_tex_sbmsn got
7   --                            modified to add two more parameters p_legacy, p_final_title_ind.
8   --smadathi    24-AUG-2001     Bug No. 1956374 .The call to igs_re_val_tex.genp_val_sdtt_sess
9   --                            is changed to igs_as_val_suaap.genp_val_sdtt_sess
10   -------------------------------------------------------------------------------------------
11   l_rowid VARCHAR2(25);
12   old_references IGS_RE_THESIS_EXAM%RowType;
13   new_references IGS_RE_THESIS_EXAM%RowType;
14 
15   PROCEDURE Set_Column_Values (
16     p_action IN VARCHAR2,
17     x_rowid IN VARCHAR2,
18     x_person_id IN NUMBER,
19     x_ca_sequence_number IN NUMBER,
20     x_the_sequence_number IN NUMBER,
21     x_creation_dt IN DATE,
22     x_submission_dt IN DATE,
23     x_thesis_exam_type IN VARCHAR2,
24     x_thesis_panel_type IN VARCHAR2,
25     x_tracking_id IN NUMBER,
26     x_thesis_result_cd IN VARCHAR2,
27     x_creation_date IN DATE,
28     x_created_by IN NUMBER,
29     x_last_update_date IN DATE,
30     x_last_updated_by IN NUMBER,
31     x_last_update_login IN NUMBER
32   ) AS
33 
34     CURSOR cur_old_ref_values IS
35       SELECT   *
36       FROM     IGS_RE_THESIS_EXAM
37       WHERE    rowid = x_rowid;
38 
39   BEGIN
40 
41     l_rowid := x_rowid;
42 
43     -- Code for setting the Old and New Reference Values.
44     -- Populate Old Values.
45     Open cur_old_ref_values;
46     Fetch cur_old_ref_values INTO old_references;
47     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
48       Close cur_old_ref_values;
49       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
50       IGS_GE_MSG_STACK.ADD;
51       App_Exception.Raise_Exception;
52       Return;
53     END IF;
54     Close cur_old_ref_values;
55 
56     -- Populate New Values.
57     new_references.person_id := x_person_id;
58     new_references.ca_sequence_number := x_ca_sequence_number;
59     new_references.the_sequence_number := x_the_sequence_number;
60     new_references.creation_dt := x_creation_dt;
61     new_references.submission_dt := x_submission_dt;
62     new_references.thesis_exam_type := x_thesis_exam_type;
63     new_references.thesis_panel_type := x_thesis_panel_type;
64     new_references.tracking_id := x_tracking_id;
65     new_references.thesis_result_cd := x_thesis_result_cd;
66     IF (p_action = 'UPDATE') THEN
67       new_references.creation_date := old_references.creation_date;
68       new_references.created_by := old_references.created_by;
69     ELSE
70       new_references.creation_date := x_creation_date;
71       new_references.created_by := x_created_by;
72     END IF;
73     new_references.last_update_date := x_last_update_date;
74     new_references.last_updated_by := x_last_updated_by;
75     new_references.last_update_login := x_last_update_login;
76 
77   END Set_Column_Values;
78 
79   PROCEDURE BeforeRowInsertUpdate1(
80     p_inserting IN BOOLEAN,
81     p_updating IN BOOLEAN,
82     p_deleting IN BOOLEAN
83     ) AS
84         v_message_name          VARCHAR2(30);
85         v_transaction_type      VARCHAR2(10);
86   BEGIN
87         -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
88         -- as a result of IGS_PS_COURSE transfer
89         IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
90                 IF p_inserting OR
91                    ( p_updating AND
92                      old_references.thesis_panel_type <> new_references.thesis_panel_type) THEN
93                         -- Validate the IGS_RE_THESIS panel type
94                         IF IGS_RE_VAL_TEX.resp_val_tex_tpt(     new_references.thesis_panel_type,
95                                                         v_message_name) = FALSE THEN
96                                                                 Fnd_Message.Set_Name ('IGS', v_message_name);
97                                                                 IGS_GE_MSG_STACK.ADD;
98                                                                 App_Exception.Raise_Exception;
99                         END IF;
100                 END IF;
101                 IF p_inserting OR
102                    ( p_updating AND
103                      old_references.thesis_exam_type <> new_references.thesis_exam_type) THEN
104                         -- Validate the IGS_RE_THESIS examination type
105                         IF IGS_RE_VAL_TEX.resp_val_tex_tet(     new_references.thesis_exam_type,
106                                                         v_message_name) = FALSE THEN
107                                                                 Fnd_Message.Set_Name ('IGS', v_message_name);
108                                                                 IGS_GE_MSG_STACK.ADD;
109                                                                 App_Exception.Raise_Exception;
110                         END IF;
111                 END IF;
112         END IF;
113 
114 
115   END BeforeRowInsertUpdate1;
116 
117   PROCEDURE AfterRowInsertUpdateDelete2(
118     p_inserting IN BOOLEAN,
119     p_updating  IN BOOLEAN ,
120     p_deleting  IN BOOLEAN
121     ) AS
122         v_message_name          VARCHAR2(30);
123         v_rowid_saved           BOOLEAN := FALSE;
124   BEGIN
125         -- update of student IGS_PS_COURSE attempt after student IGS_PS_UNIT attempt is posted
126         -- to the database
127         IF v_rowid_saved = FALSE
128         THEN
129         IF p_updating OR p_deleting THEN
130                 IGS_RE_GEN_003.RESP_INS_TEX_HIST(old_references.person_id,
131                         old_references.ca_sequence_number,
132                         old_references.the_sequence_number,
133                         old_references.creation_dt,
134                         old_references.submission_dt,
135                         new_references.submission_dt,
136                         old_references.thesis_exam_type,
137                         new_references.thesis_exam_type,
138                         old_references.thesis_panel_type,
139                         new_references.thesis_panel_type,
140                         old_references.thesis_result_cd,
141                         new_references.thesis_result_cd,
142                         old_references.tracking_id,
143                         new_references.tracking_id,
144                         old_references.last_updated_by,
145                         new_references.last_updated_by,
146                         old_references.last_update_date,
147                         new_references.last_update_date);
148         END IF;
149         END IF;
150 
151 
152   -- The changes are done as per the Enrollments Notifications TD Bug # 3052429
153   -- The workflow notification is send when:
154   -- 1. A new record is created
155   -- 2. When value of field SUBMISSION_DT is changed
156 
157     IF p_inserting OR (
158                        p_updating AND
159                       (
160 		      (new_references.submission_dt IS NULL AND old_references.submission_dt IS NOT NULL) OR
161                       (new_references.submission_dt IS NOT NULL AND old_references.submission_dt IS NULL) OR
162 		      (new_references.submission_dt IS NOT NULL AND old_references.submission_dt IS NOT NULL AND trunc(old_references.submission_dt) <> trunc(new_references.submission_dt))
163 		      )
164 		      )THEN
165 
166          igs_re_workflow.thesis_exam_event(
167 			 p_personid	        =>  new_references.person_id ,
168 			 p_ca_sequence_number	=>  new_references.ca_sequence_number ,
169 			 p_the_sequence_number	=>  new_references.the_sequence_number ,
170 			 p_creation_dt	        =>  new_references.creation_dt ,
171 			 p_submission_dt	=>  new_references.submission_dt ,
172 			 p_thesis_exam_type	=>  new_references.thesis_exam_type
173             	          );
174 
175     END IF;
176 
177   -- The workflow notification is send when:
178   -- 1. A new record is created and THESIS_RESULT_CD is not null
179   -- 2. When value of field THESIS_RESULT_CD is changed
180 
181 
182     IF ( p_inserting AND new_references.thesis_result_cd IS NOT NULL )
183                       OR(p_updating AND
184 		        (
185 			(new_references.thesis_result_cd IS NULL AND old_references.thesis_result_cd IS NOT NULL) OR
186                         (new_references.thesis_result_cd IS NOT NULL AND old_references.thesis_result_cd IS NULL) OR
187 		        (new_references.thesis_result_cd IS NOT NULL AND old_references.thesis_result_cd IS NOT NULL AND old_references.thesis_result_cd <> new_references.thesis_result_cd)
188 			)) THEN
189 
190 
191           igs_re_workflow.thesis_result_event(
192 			  p_personid	        => new_references.person_id           ,
193 			  p_ca_sequence_number	=> new_references.ca_sequence_number  ,
194 			  p_the_sequence_number	=> new_references.the_sequence_number ,
195 			  p_creation_dt	        => new_references.creation_dt         ,
196 			  p_submission_dt	=> new_references.submission_dt       ,
197 			  p_thesis_exam_type	=> new_references.thesis_exam_type    ,
198 			  p_thesis_result_cd	=> new_references.thesis_result_cd
199 			  );
200 
201    END IF;
202 
203  END AfterRowInsertUpdateDelete2;
204 
205   PROCEDURE AfterStmtInsertUpdate3(
206     p_inserting IN BOOLEAN,
207     p_updating IN BOOLEAN,
208     p_deleting IN BOOLEAN
209     ) AS
210         v_message_name          VARCHAR2(30);
211         v_transaction_type      VARCHAR2(10);
212 
213   BEGIN
214         -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
215         -- as a result of IGS_PS_COURSE transfer
216         IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
217                 -- perform student IGS_PS_COURSE attempt status p_updating and
218                 -- student IGS_PS_UNIT attempt outcome insert/delete  on all the
219                 -- rows affected by the statement
220 
221                 IF p_inserting OR p_updating THEN
222 
223                         IF p_inserting THEN
224                                 v_transaction_type := 'INSERT';
225                         ELSE
226                                 v_transaction_type := 'UPDATE';
227                         END IF;
228                         -- Validate update transactions against the IGS_RE_THESIS examination.
229                         IF IGS_RE_VAL_TEX.resp_val_tex_upd(     new_references.person_id,
230                                                         new_references.ca_sequence_number,
231                                                         new_references.the_sequence_number,
232                                                         v_transaction_type,
233                                                         new_references.submission_dt,
234                                                         v_message_name) = FALSE THEN
235                                                                 Fnd_Message.Set_Name ('IGS', v_message_name);
236                                                                 IGS_GE_MSG_STACK.ADD;
237                                                                 App_Exception.Raise_Exception;
238                         END IF;
239                         IF NVL(NEW_REFERENCES.submission_dt,
240                                                 igs_ge_date.igsdate('1900/01/01')) <>
241                                 NVL(new_references.submission_dt,igs_ge_date.igsdate('1900/01/01')) THEN
242                                 -- Validate the submission date.
243 
244                                 --Nishikant-19NOV2002-Bug#2661533. The signature of the functions resp_val_tex_sbmsn got modified to add
245                                 --two more parameer p_legacy and p_final_title_ind.
246                                 IF IGS_RE_VAL_TEX.resp_val_tex_sbmsn(   new_references.person_id,
247                                                         new_references.ca_sequence_number,
248                                                         new_references.the_sequence_number,
249                                                         new_references.creation_dt,
250                                                         new_references.thesis_result_cd,
251                                                         new_references.submission_dt,
252                                                         'N', --p_legacy parameter
253                                                         NULL, --final title indicator parameter
254                                                         v_message_name) = FALSE THEN
255                                                                 Fnd_Message.Set_Name ('IGS', v_message_name);
256                                                                 IGS_GE_MSG_STACK.ADD;
257                                                                 App_Exception.Raise_Exception;
258                                 END IF;
259                         END IF;
260                         -- Validate the IGS_RE_THESIS result code
261                          IF IGS_RE_VAL_TEX.resp_val_tex_thr(    new_references.person_id,
262                                                         new_references.ca_sequence_number,
263                                                         new_references.the_sequence_number,
264                                                         new_references.creation_dt,
265                                                         new_references.submission_dt,
266                                                         new_references.thesis_result_cd,
267                                                         new_references.thesis_panel_type,
268                                                         v_message_name) = FALSE THEN
269                                                                 Fnd_Message.Set_Name ('IGS', v_message_name);
270                                                                 IGS_GE_MSG_STACK.ADD;
271                                                                 App_Exception.Raise_Exception;
272                         END IF;
273                 ELSE            --      Deleting!
274                         -- Validate for deletion of IGS_RE_THESIS examination details.
275                         IF IGS_RE_VAL_TEX.resp_val_tex_upd(     old_references.person_id,
276                                                         old_references.ca_sequence_number,
277                                                         old_references.the_sequence_number,
278                                                         'DELETE',
279                                                         old_references.submission_dt,
280                                                         v_message_name) = FALSE THEN
281                                                                 Fnd_Message.Set_Name ('IGS', v_message_name);
282                                                                 IGS_GE_MSG_STACK.ADD;
283                                                                 App_Exception.Raise_Exception;
284                         END IF;
285                 END IF;
286         END IF;
287 
288 
289   END AfterStmtInsertUpdate3;
290 
291   PROCEDURE Check_Constraints (
292     Column_Name in VARCHAR2 ,
293     Column_Value in VARCHAR2
294   ) AS
295  BEGIN
296 
297  IF Column_Name is null then
298    NULL;
299  ELSIF upper(Column_name) = 'THE_SEQUENCE_NUMBER' THEN
300    new_references.THE_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
301  ELSIF upper(Column_name) = 'CA_SEQUENCE_NUMBER' THEN
302    new_references.CA_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
303  ELSIF upper(Column_name) = 'THESIS_EXAM_TYPE' THEN
304    new_references.THESIS_EXAM_TYPE := COLUMN_VALUE ;
305  ELSIF upper(Column_name) = 'THESIS_PANEL_TYPE' THEN
306    new_references.THESIS_PANEL_TYPE := COLUMN_VALUE ;
307  ELSIF upper(Column_name) = 'THESIS_RESULT_CD' THEN
308    new_references.THESIS_RESULT_CD := COLUMN_VALUE ;
309  END IF;
310 
311   IF upper(column_name) = 'THE_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
312     IF new_references.THE_SEQUENCE_NUMBER < 1 OR new_references.THE_SEQUENCE_NUMBER > 999999 then
313           Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
314           IGS_GE_MSG_STACK.ADD;
315           App_Exception.Raise_Exception ;
316         END IF;
317   END IF;
318   IF upper(column_name) = 'CA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
319     IF new_references.CA_SEQUENCE_NUMBER < 1 OR new_references.CA_SEQUENCE_NUMBER > 999999 then
320           Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
321           IGS_GE_MSG_STACK.ADD;
322           App_Exception.Raise_Exception ;
323         END IF;
324   END IF;
325   IF upper(column_name) = 'THESIS_EXAM_TYPE' OR COLUMN_NAME IS NULL THEN
326     IF new_references.THESIS_EXAM_TYPE <> upper(NEW_REFERENCES.THESIS_EXAM_TYPE) then
327           Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
328           IGS_GE_MSG_STACK.ADD;
329           App_Exception.Raise_Exception ;
330         END IF;
331   END IF;
332   IF upper(column_name) = 'THESIS_PANEL_TYPE' OR COLUMN_NAME IS NULL THEN
333     IF new_references.THESIS_PANEL_TYPE <> upper(NEW_REFERENCES.THESIS_PANEL_TYPE) then
334           Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
335           IGS_GE_MSG_STACK.ADD;
336           App_Exception.Raise_Exception ;
337         END IF;
338   END IF;
339   IF upper(column_name) = 'THESIS_RESULT_CD' OR COLUMN_NAME IS NULL THEN
340     IF new_references.THESIS_RESULT_CD <> upper(NEW_REFERENCES.THESIS_RESULT_CD) then
341           Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
342           IGS_GE_MSG_STACK.ADD;
343           App_Exception.Raise_Exception ;
344         END IF;
345   END IF;
346  END Check_Constraints ;
347 
348 
349   PROCEDURE Check_Parent_Existance AS
350   BEGIN
351 
352     IF (((old_references.thesis_exam_type = new_references.thesis_exam_type)) OR
353         ((new_references.thesis_exam_type IS NULL))) THEN
354       NULL;
355     ELSE
356       IF NOT IGS_RE_THS_EXAM_TYPE_PKG.Get_PK_For_Validation (
357         new_references.thesis_exam_type
358         ) THEN
359              Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
360              IGS_GE_MSG_STACK.ADD;
361              App_Exception.Raise_Exception;
362        END IF;
363     END IF;
364 
365     IF (((old_references.person_id = new_references.person_id) AND
366          (old_references.ca_sequence_number = new_references.ca_sequence_number) AND
367          (old_references.the_sequence_number = new_references.the_sequence_number)) OR
368         ((new_references.person_id IS NULL) OR
369          (new_references.ca_sequence_number IS NULL) OR
370          (new_references.the_sequence_number IS NULL))) THEN
371       NULL;
372     ELSE
373       IF NOT IGS_RE_THESIS_PKG.Get_PK_For_Validation (
374         new_references.person_id,
375         new_references.ca_sequence_number,
376         new_references.the_sequence_number
377         ) THEN
378              Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
379              IGS_GE_MSG_STACK.ADD;
380              App_Exception.Raise_Exception;
381        END IF;
382     END IF;
383 
384     IF (((old_references.thesis_result_cd = new_references.thesis_result_cd)) OR
385         ((new_references.thesis_result_cd IS NULL))) THEN
386       NULL;
387     ELSE
388       IF NOT IGS_RE_THESIS_RESULT_PKG.Get_PK_For_Validation (
389         new_references.thesis_result_cd
390         ) THEN
391              Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
392              IGS_GE_MSG_STACK.ADD;
393              App_Exception.Raise_Exception;
394        END IF;
395     END IF;
396 
397     IF (((old_references.thesis_panel_type = new_references.thesis_panel_type)) OR
398         ((new_references.thesis_panel_type IS NULL))) THEN
399       NULL;
400     ELSE
401       IF NOT IGS_RE_THS_PNL_TYPE_PKG.Get_PK_For_Validation (
402         new_references.thesis_panel_type
403         ) THEN
404              Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
405              IGS_GE_MSG_STACK.ADD;
406              App_Exception.Raise_Exception;
407        END IF;
408     END IF;
409 
410     IF (((old_references.tracking_id = new_references.tracking_id)) OR
411         ((new_references.tracking_id IS NULL))) THEN
412       NULL;
413     ELSE
414       IF NOT IGS_TR_ITEM_PKG.Get_PK_For_Validation (
415         new_references.tracking_id
416         ) THEN
417              Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
418              IGS_GE_MSG_STACK.ADD;
419              App_Exception.Raise_Exception;
420        END IF;
421     END IF;
422 
423   END Check_Parent_Existance;
424 
425   PROCEDURE Check_Child_Existance AS
426   BEGIN
427 
428     IGS_RE_THS_PNL_MBR_PKG.GET_FK_IGS_RE_THESIS_EXAM (
429       old_references.person_id,
430       old_references.ca_sequence_number,
431       old_references.the_sequence_number,
432       old_references.creation_dt
433       );
434 
435   END Check_Child_Existance;
436 
437   FUNCTION Get_PK_For_Validation (
438     x_person_id IN NUMBER,
439     x_ca_sequence_number IN NUMBER,
440     x_the_sequence_number IN NUMBER,
441     x_creation_dt IN DATE
442     ) RETURN BOOLEAN
443    AS
444 
445     CURSOR cur_rowid IS
446       SELECT   rowid
447       FROM     IGS_RE_THESIS_EXAM
448       WHERE    person_id = x_person_id
449       AND      ca_sequence_number = x_ca_sequence_number
450       AND      the_sequence_number = x_the_sequence_number
451       AND      creation_dt = x_creation_dt
452       FOR UPDATE NOWAIT;
453 
454     lv_rowid cur_rowid%RowType;
455 
456   BEGIN
457 
458     Open cur_rowid;
459     Fetch cur_rowid INTO lv_rowid;
460     IF (cur_rowid%FOUND) THEN
461         Close cur_rowid;
462         RETURN(TRUE);
463     ELSE
464         Close cur_rowid;
465         RETURN(FALSE);
466     END IF;
467 
468   END Get_PK_For_Validation;
469 
470   PROCEDURE GET_FK_IGS_RE_THS_EXAM_TYPE (
471     x_thesis_exam_type IN VARCHAR2
472     ) AS
473 
474     CURSOR cur_rowid IS
475       SELECT   rowid
476       FROM     IGS_RE_THESIS_EXAM
477       WHERE    thesis_exam_type = x_thesis_exam_type ;
478 
479     lv_rowid cur_rowid%RowType;
480 
481   BEGIN
482 
483     Open cur_rowid;
484     Fetch cur_rowid INTO lv_rowid;
485     IF (cur_rowid%FOUND) THEN
486       Close cur_rowid;
487       Fnd_Message.Set_Name ('IGS', 'IGS_RE_TEX_TET_FK');
488       IGS_GE_MSG_STACK.ADD;
489       App_Exception.Raise_Exception;
490       Return;
491     END IF;
492     Close cur_rowid;
493 
494   END GET_FK_IGS_RE_THS_EXAM_TYPE;
495 
496   PROCEDURE GET_FK_IGS_RE_THESIS (
497     x_person_id IN NUMBER,
498     x_ca_sequence_number IN NUMBER,
499     x_sequence_number IN NUMBER
500     ) AS
501 
502     CURSOR cur_rowid IS
503       SELECT   rowid
504       FROM     IGS_RE_THESIS_EXAM
505       WHERE    person_id = x_person_id
506       AND      ca_sequence_number = x_ca_sequence_number
507       AND      the_sequence_number = x_sequence_number ;
508 
509     lv_rowid cur_rowid%RowType;
510 
511   BEGIN
512 
513     Open cur_rowid;
514     Fetch cur_rowid INTO lv_rowid;
515     IF (cur_rowid%FOUND) THEN
516       Close cur_rowid;
517       Fnd_Message.Set_Name ('IGS', 'IGS_RE_TEX_THE_FK');
518       IGS_GE_MSG_STACK.ADD;
519       App_Exception.Raise_Exception;
520       Return;
521     END IF;
522     Close cur_rowid;
523 
524   END GET_FK_IGS_RE_THESIS;
525 
526   PROCEDURE GET_FK_IGS_RE_THESIS_RESULT (
527     x_thesis_result_cd IN VARCHAR2
528     ) AS
529 
530     CURSOR cur_rowid IS
531       SELECT   rowid
532       FROM     IGS_RE_THESIS_EXAM
533       WHERE    thesis_result_cd = x_thesis_result_cd ;
534 
535     lv_rowid cur_rowid%RowType;
536 
537   BEGIN
538 
539     Open cur_rowid;
540     Fetch cur_rowid INTO lv_rowid;
541     IF (cur_rowid%FOUND) THEN
542       Close cur_rowid;
543       Fnd_Message.Set_Name ('IGS', 'IGS_RE_TEX_THR_FK');
544       IGS_GE_MSG_STACK.ADD;
545       App_Exception.Raise_Exception;
546       Return;
547     END IF;
548     Close cur_rowid;
549 
550   END GET_FK_IGS_RE_THESIS_RESULT;
551 
552   PROCEDURE GET_FK_IGS_RE_THS_PNL_TYPE (
553     x_thesis_panel_type IN VARCHAR2
554     ) AS
555 
556     CURSOR cur_rowid IS
557       SELECT   rowid
558       FROM     IGS_RE_THESIS_EXAM
559       WHERE    thesis_panel_type = x_thesis_panel_type ;
560 
561     lv_rowid cur_rowid%RowType;
562 
563   BEGIN
564 
565     Open cur_rowid;
566     Fetch cur_rowid INTO lv_rowid;
567     IF (cur_rowid%FOUND) THEN
568       Close cur_rowid;
569       Fnd_Message.Set_Name ('IGS', 'IGS_RE_TEX_TPT_FK');
570       IGS_GE_MSG_STACK.ADD;
571       App_Exception.Raise_Exception;
572       Return;
573     END IF;
574     Close cur_rowid;
575 
576   END GET_FK_IGS_RE_THS_PNL_TYPE;
577 
578   PROCEDURE GET_FK_IGS_TR_ITEM (
579     x_tracking_id IN NUMBER
580     ) AS
581 
582     CURSOR cur_rowid IS
583       SELECT   rowid
584       FROM     IGS_RE_THESIS_EXAM
585       WHERE    tracking_id = x_tracking_id ;
586 
587     lv_rowid cur_rowid%RowType;
588 
589   BEGIN
590 
591     Open cur_rowid;
592     Fetch cur_rowid INTO lv_rowid;
593     IF (cur_rowid%FOUND) THEN
594       Close cur_rowid;
595       Fnd_Message.Set_Name ('IGS', 'IGS_RE_TEX_TRI_FK');
596       IGS_GE_MSG_STACK.ADD;
597       App_Exception.Raise_Exception;
598       Return;
599     END IF;
600     Close cur_rowid;
601 
602   END GET_FK_IGS_TR_ITEM;
603 
604   PROCEDURE Before_DML (
605     p_action IN VARCHAR2,
606     x_rowid IN VARCHAR2,
607     x_person_id IN NUMBER,
608     x_ca_sequence_number IN NUMBER,
609     x_the_sequence_number IN NUMBER,
610     x_creation_dt IN DATE,
611     x_submission_dt IN DATE,
612     x_thesis_exam_type IN VARCHAR2,
613     x_thesis_panel_type IN VARCHAR2,
614     x_tracking_id IN NUMBER,
615     x_thesis_result_cd IN VARCHAR2,
616     x_creation_date IN DATE,
617     x_created_by IN NUMBER,
618     x_last_update_date IN DATE,
619     x_last_updated_by IN NUMBER,
620     x_last_update_login IN NUMBER
621   ) AS
622   BEGIN
623 
624     Set_Column_Values (
625       p_action,
626       x_rowid,
627       x_person_id,
628       x_ca_sequence_number,
629       x_the_sequence_number,
630       x_creation_dt,
631       x_submission_dt,
632       x_thesis_exam_type,
633       x_thesis_panel_type,
634       x_tracking_id,
635       x_thesis_result_cd,
636       x_creation_date,
637       x_created_by,
638       x_last_update_date,
639       x_last_updated_by,
640       x_last_update_login
641     );
642 
643     IF (p_action = 'INSERT') THEN
644       -- Call all the procedures related to Before Insert.
645       BeforeRowInsertUpdate1 ( p_inserting => TRUE,
646                                p_updating  => FALSE,
647                                p_deleting  => FALSE);
648 
649       IF Get_PK_For_Validation (
650             new_references.person_id,
651             new_references.ca_sequence_number,
652             new_references.the_sequence_number,
653             new_references.creation_dt
654       ) THEN
655 
656          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
657          IGS_GE_MSG_STACK.ADD;
658          App_Exception.Raise_Exception;
659      END IF;
660       Check_Constraints;
661       Check_Parent_Existance;
662     ELSIF (p_action = 'UPDATE') THEN
663       -- Call all the procedures related to Before Update.
664       BeforeRowInsertUpdate1 ( p_inserting => FALSE,
665                                p_updating  => TRUE,
666                                p_deleting  => FALSE);
667       Check_Constraints;
668       Check_Parent_Existance;
669     ELSIF (p_action = 'DELETE') THEN
670       -- Call all the procedures related to Before Delete.
671       Check_Child_Existance;
672     ELSIF (p_action = 'VALIDATE_INSERT') THEN
673       IF Get_PK_For_Validation (
674             new_references.person_id,
675             new_references.ca_sequence_number,
676             new_references.the_sequence_number,
677             new_references.creation_dt
678       ) THEN
679          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
680          App_Exception.Raise_Exception;
681      END IF;
682       Check_Constraints;
683     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
684       Check_Constraints;
685     ELSIF (p_action = 'VALIDATE_DELETE') THEN
686       Check_Child_Existance;
687     END IF;
688   END Before_DML;
689 
690   PROCEDURE After_DML (
691     p_action IN VARCHAR2,
692     x_rowid IN VARCHAR2
693   ) AS
694   BEGIN
695 
696     l_rowid := x_rowid;
697 
698     IF (p_action = 'INSERT') THEN
699       -- Call all the procedures related to After Insert.
700       AfterRowInsertUpdateDelete2 ( p_inserting => TRUE ,
701                                     p_updating  => FALSE,
702                                     p_deleting  => FALSE);
703       AfterStmtInsertUpdate3 ( p_inserting => TRUE,
704                                p_updating  => FALSE,
705                                p_deleting  => FALSE );
706     ELSIF (p_action = 'UPDATE') THEN
707       -- Call all the procedures related to After Update.
708       AfterRowInsertUpdateDelete2 ( p_inserting => FALSE,
709                                     p_updating  => TRUE,
710                                     p_deleting  => FALSE);
711       AfterStmtInsertUpdate3 (p_inserting => FALSE,
712                               p_updating  => TRUE,
713                               p_deleting  => FALSE );
714     ELSIF (p_action = 'DELETE') THEN
715       -- Call all the procedures related to After Delete.
716       AfterRowInsertUpdateDelete2 ( p_inserting => FALSE,
717                                     p_updating  => FALSE,
718                                     p_deleting  => TRUE );
719     END IF;
720 
721   END After_DML;
722 
723 procedure INSERT_ROW (
724   X_ROWID in out NOCOPY VARCHAR2,
725   X_PERSON_ID in NUMBER,
726   X_CA_SEQUENCE_NUMBER in NUMBER,
727   X_THE_SEQUENCE_NUMBER in NUMBER,
728   X_CREATION_DT in DATE,
729   X_SUBMISSION_DT in DATE,
730   X_THESIS_EXAM_TYPE in VARCHAR2,
731   X_THESIS_PANEL_TYPE in VARCHAR2,
732   X_TRACKING_ID in NUMBER,
733   X_THESIS_RESULT_CD in VARCHAR2,
734   X_MODE in VARCHAR2
735   ) as
736     cursor C is select ROWID from IGS_RE_THESIS_EXAM
737       where PERSON_ID = X_PERSON_ID
738       and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
739       and THE_SEQUENCE_NUMBER = X_THE_SEQUENCE_NUMBER
740       and CREATION_DT = X_CREATION_DT;
741     X_LAST_UPDATE_DATE DATE;
742     X_LAST_UPDATED_BY NUMBER;
743     X_LAST_UPDATE_LOGIN NUMBER;
744 begin
745   X_LAST_UPDATE_DATE := SYSDATE;
746   if(X_MODE = 'I') then
747     X_LAST_UPDATED_BY := 1;
748     X_LAST_UPDATE_LOGIN := 0;
749   elsif (X_MODE IN ('R', 'S')) then
750     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
751     if X_LAST_UPDATED_BY is NULL then
752       X_LAST_UPDATED_BY := -1;
753     end if;
754     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
755     if X_LAST_UPDATE_LOGIN is NULL then
756       X_LAST_UPDATE_LOGIN := -1;
757     end if;
758   else
759     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
760     IGS_GE_MSG_STACK.ADD;
761     app_exception.raise_exception;
762   end if;
763 
764   Before_DML (
765     p_action => 'INSERT',
766     x_rowid => X_ROWID,
767     x_person_id => X_PERSON_ID,
768     x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
769     x_the_sequence_number => X_THE_SEQUENCE_NUMBER,
770     x_creation_dt => X_CREATION_DT,
771     x_submission_dt => X_SUBMISSION_DT,
772     x_thesis_exam_type => X_THESIS_EXAM_TYPE,
773     x_thesis_panel_type => X_THESIS_PANEL_TYPE,
774     x_tracking_id => X_TRACKING_ID,
775     x_thesis_result_cd => X_THESIS_RESULT_CD,
776     x_created_by => X_LAST_UPDATED_BY ,
777     x_creation_date => X_LAST_UPDATE_DATE,
778     x_last_updated_by => X_LAST_UPDATED_BY,
779     x_last_update_login => X_LAST_UPDATE_LOGIN,
780     x_last_update_date => X_LAST_UPDATE_DATE
781  );
782 
783   IF (x_mode = 'S') THEN
784     igs_sc_gen_001.set_ctx('R');
785   END IF;
786   insert into IGS_RE_THESIS_EXAM (
787     PERSON_ID,
788     CA_SEQUENCE_NUMBER,
789     THE_SEQUENCE_NUMBER,
790     CREATION_DT,
791     SUBMISSION_DT,
792     THESIS_EXAM_TYPE,
793     THESIS_PANEL_TYPE,
794     TRACKING_ID,
795     THESIS_RESULT_CD,
796     CREATION_DATE,
797     CREATED_BY,
798     LAST_UPDATE_DATE,
799     LAST_UPDATED_BY,
800     LAST_UPDATE_LOGIN
801   ) values (
802     NEW_REFERENCES.PERSON_ID,
803     NEW_REFERENCES.CA_SEQUENCE_NUMBER,
804     NEW_REFERENCES.THE_SEQUENCE_NUMBER,
805     NEW_REFERENCES.CREATION_DT,
806     NEW_REFERENCES.SUBMISSION_DT,
807     NEW_REFERENCES.THESIS_EXAM_TYPE,
808     NEW_REFERENCES.THESIS_PANEL_TYPE,
809     NEW_REFERENCES.TRACKING_ID,
810     NEW_REFERENCES.THESIS_RESULT_CD,
811     X_LAST_UPDATE_DATE,
812     X_LAST_UPDATED_BY,
813     X_LAST_UPDATE_DATE,
814     X_LAST_UPDATED_BY,
815     X_LAST_UPDATE_LOGIN
816   );
817  IF (x_mode = 'S') THEN
818     igs_sc_gen_001.unset_ctx('R');
819   END IF;
820 
821 
822   open c;
823   fetch c into X_ROWID;
824   if (c%notfound) then
825     close c;
826     raise no_data_found;
827   end if;
828   close c;
829 
830  After_DML (
831     p_action => 'INSERT',
832     x_rowid => X_ROWID
833   );
834 
835 
836 EXCEPTION
837   WHEN OTHERS THEN
838     IF (SQLCODE IN (-28115, -28113, -28111)) THEN
839       fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
840       fnd_message.set_token ('ERR_CD', SQLCODE);
841       igs_ge_msg_stack.add;
842       igs_sc_gen_001.unset_ctx('R');
843       app_exception.raise_exception;
844     ELSE
845       igs_sc_gen_001.unset_ctx('R');
846       RAISE;
847     END IF;
848 
849 end INSERT_ROW;
850 
851 procedure LOCK_ROW (
852   X_ROWID in VARCHAR2,
853   X_PERSON_ID in NUMBER,
854   X_CA_SEQUENCE_NUMBER in NUMBER,
855   X_THE_SEQUENCE_NUMBER in NUMBER,
856   X_CREATION_DT in DATE,
857   X_SUBMISSION_DT in DATE,
858   X_THESIS_EXAM_TYPE in VARCHAR2,
859   X_THESIS_PANEL_TYPE in VARCHAR2,
860   X_TRACKING_ID in NUMBER,
861   X_THESIS_RESULT_CD in VARCHAR2
862 ) as
863   cursor c1 is select
864       SUBMISSION_DT,
865       THESIS_EXAM_TYPE,
866       THESIS_PANEL_TYPE,
867       TRACKING_ID,
868       THESIS_RESULT_CD
869     from IGS_RE_THESIS_EXAM
870     where ROWID = X_ROWID
871     for update nowait;
872   tlinfo c1%rowtype;
873 
874 begin
875   open c1;
876   fetch c1 into tlinfo;
877   if (c1%notfound) then
878     close c1;
879     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
880     app_exception.raise_exception;
881     return;
882   end if;
883   close c1;
884 
885       if ( ((tlinfo.SUBMISSION_DT = X_SUBMISSION_DT)
886            OR ((tlinfo.SUBMISSION_DT is null)
887                AND (X_SUBMISSION_DT is null)))
888       AND (tlinfo.THESIS_EXAM_TYPE = X_THESIS_EXAM_TYPE)
889       AND (tlinfo.THESIS_PANEL_TYPE = X_THESIS_PANEL_TYPE)
890       AND ((tlinfo.TRACKING_ID = X_TRACKING_ID)
891            OR ((tlinfo.TRACKING_ID is null)
892                AND (X_TRACKING_ID is null)))
893       AND ((tlinfo.THESIS_RESULT_CD = X_THESIS_RESULT_CD)
894            OR ((tlinfo.THESIS_RESULT_CD is null)
895                AND (X_THESIS_RESULT_CD is null)))
896   ) then
897     null;
898   else
899     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
900     app_exception.raise_exception;
901   end if;
902   return;
903 end LOCK_ROW;
904 
905 procedure UPDATE_ROW (
906   X_ROWID in VARCHAR2,
907   X_PERSON_ID in NUMBER,
908   X_CA_SEQUENCE_NUMBER in NUMBER,
909   X_THE_SEQUENCE_NUMBER in NUMBER,
910   X_CREATION_DT in DATE,
911   X_SUBMISSION_DT in DATE,
912   X_THESIS_EXAM_TYPE in VARCHAR2,
913   X_THESIS_PANEL_TYPE in VARCHAR2,
914   X_TRACKING_ID in NUMBER,
915   X_THESIS_RESULT_CD in VARCHAR2,
916   X_MODE in VARCHAR2
917   ) as
918     X_LAST_UPDATE_DATE DATE;
919     X_LAST_UPDATED_BY NUMBER;
920     X_LAST_UPDATE_LOGIN NUMBER;
921 begin
922   X_LAST_UPDATE_DATE := SYSDATE;
923   if(X_MODE = 'I') then
924     X_LAST_UPDATED_BY := 1;
925     X_LAST_UPDATE_LOGIN := 0;
926   elsif (X_MODE IN ('R', 'S')) then
927     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
928     if X_LAST_UPDATED_BY is NULL then
929       X_LAST_UPDATED_BY := -1;
930     end if;
931     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
932     if X_LAST_UPDATE_LOGIN is NULL then
933       X_LAST_UPDATE_LOGIN := -1;
934     end if;
935   else
936     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
937     IGS_GE_MSG_STACK.ADD;
938     app_exception.raise_exception;
939   end if;
940 
941   Before_DML (
942     p_action => 'UPDATE',
943     x_rowid => X_ROWID,
944     x_person_id => X_PERSON_ID,
945     x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
946     x_the_sequence_number => X_THE_SEQUENCE_NUMBER,
947     x_creation_dt => X_CREATION_DT,
948     x_submission_dt => X_SUBMISSION_DT,
949     x_thesis_exam_type => X_THESIS_EXAM_TYPE,
950     x_thesis_panel_type => X_THESIS_PANEL_TYPE,
951     x_tracking_id => X_TRACKING_ID,
952     x_thesis_result_cd => X_THESIS_RESULT_CD,
953     x_created_by => X_LAST_UPDATED_BY ,
954     x_creation_date => X_LAST_UPDATE_DATE,
955     x_last_updated_by => X_LAST_UPDATED_BY,
956     x_last_update_login => X_LAST_UPDATE_LOGIN,
957     x_last_update_date => X_LAST_UPDATE_DATE
958  );
959 
960   IF (x_mode = 'S') THEN
961     igs_sc_gen_001.set_ctx('R');
962   END IF;
963   update IGS_RE_THESIS_EXAM set
964     SUBMISSION_DT = NEW_REFERENCES.SUBMISSION_DT,
965     THESIS_EXAM_TYPE = NEW_REFERENCES.THESIS_EXAM_TYPE,
966     THESIS_PANEL_TYPE = NEW_REFERENCES.THESIS_PANEL_TYPE,
967     TRACKING_ID = NEW_REFERENCES.TRACKING_ID,
968     THESIS_RESULT_CD = NEW_REFERENCES.THESIS_RESULT_CD,
969     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
970     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
971     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
972   where ROWID = X_ROWID
973   ;
974   if (sql%notfound) then
975      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
976      igs_ge_msg_stack.add;
977      igs_sc_gen_001.unset_ctx('R');
978      app_exception.raise_exception;
979  end if;
980  IF (x_mode = 'S') THEN
981     igs_sc_gen_001.unset_ctx('R');
982   END IF;
983 
984 
985  After_DML (
986     p_action => 'UPDATE',
987     x_rowid => X_ROWID
988   );
989 
990 EXCEPTION
991   WHEN OTHERS THEN
992     IF (SQLCODE = (-28115)) THEN
993       fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
994       fnd_message.set_token ('ERR_CD', SQLCODE);
995       igs_ge_msg_stack.add;
996       igs_sc_gen_001.unset_ctx('R');
997       app_exception.raise_exception;
998     ELSE
999       igs_sc_gen_001.unset_ctx('R');
1000       RAISE;
1001     END IF;
1002 
1003 end UPDATE_ROW;
1004 
1005 procedure ADD_ROW (
1006   X_ROWID in out NOCOPY VARCHAR2,
1007   X_PERSON_ID in NUMBER,
1008   X_CA_SEQUENCE_NUMBER in NUMBER,
1009   X_THE_SEQUENCE_NUMBER in NUMBER,
1010   X_CREATION_DT in DATE,
1011   X_SUBMISSION_DT in DATE,
1012   X_THESIS_EXAM_TYPE in VARCHAR2,
1013   X_THESIS_PANEL_TYPE in VARCHAR2,
1014   X_TRACKING_ID in NUMBER,
1015   X_THESIS_RESULT_CD in VARCHAR2,
1016   X_MODE in VARCHAR2
1017   ) as
1018   cursor c1 is select rowid from IGS_RE_THESIS_EXAM
1019      where PERSON_ID = X_PERSON_ID
1020      and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
1021      and THE_SEQUENCE_NUMBER = X_THE_SEQUENCE_NUMBER
1022      and CREATION_DT = X_CREATION_DT
1023   ;
1024 begin
1025   open c1;
1026   fetch c1 into X_ROWID;
1027   if (c1%notfound) then
1028     close c1;
1029     INSERT_ROW (
1030      X_ROWID,
1031      X_PERSON_ID,
1032      X_CA_SEQUENCE_NUMBER,
1033      X_THE_SEQUENCE_NUMBER,
1034      X_CREATION_DT,
1035      X_SUBMISSION_DT,
1036      X_THESIS_EXAM_TYPE,
1037      X_THESIS_PANEL_TYPE,
1038      X_TRACKING_ID,
1039      X_THESIS_RESULT_CD,
1040      X_MODE);
1041     return;
1042   end if;
1043   close c1;
1044   UPDATE_ROW (
1045    X_ROWID,
1046    X_PERSON_ID,
1047    X_CA_SEQUENCE_NUMBER,
1048    X_THE_SEQUENCE_NUMBER,
1049    X_CREATION_DT,
1050    X_SUBMISSION_DT,
1051    X_THESIS_EXAM_TYPE,
1052    X_THESIS_PANEL_TYPE,
1053    X_TRACKING_ID,
1054    X_THESIS_RESULT_CD,
1055    X_MODE);
1056 end ADD_ROW;
1057 
1058 procedure DELETE_ROW (
1059   X_ROWID in VARCHAR2,
1060   x_mode IN VARCHAR2
1061   ) as
1062 begin
1063 
1064   Before_DML (
1065     p_action => 'DELETE',
1066     x_rowid => X_ROWID
1067    );
1068 
1069   IF (x_mode = 'S') THEN
1070     igs_sc_gen_001.set_ctx('R');
1071   END IF;
1072   delete from IGS_RE_THESIS_EXAM
1073   where ROWID = X_ROWID;
1074   if (sql%notfound) then
1075      fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1076      igs_ge_msg_stack.add;
1077      igs_sc_gen_001.unset_ctx('R');
1078      app_exception.raise_exception;
1079  end if;
1080  IF (x_mode = 'S') THEN
1081     igs_sc_gen_001.unset_ctx('R');
1082   END IF;
1083 
1084 
1085  After_DML (
1086     p_action => 'DELETE',
1087     x_rowid => X_ROWID
1088   );
1089 
1090 
1091 end DELETE_ROW;
1092 
1093 end igs_re_thesis_exam_pkg;