[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;