DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_PS_STDNT_UNT_TRN_PKG

Source


1 package body IGS_PS_STDNT_UNT_TRN_PKG as
2 /* $Header: IGSPI67B.pls 115.7 2003/07/23 07:11:34 kkillams ship $ */
3   -------------------------------------------------------------------------------------------
4   --Change History:
5   --Who         When            What
6   --smadathi    24-AUG-2001     Bug No. 1956374 .The call to igs_en_val_sut.genp_val_sdtt_sess
7   --                            is changed to igs_as_val_suaap.genp_val_sdtt_sess
8   --svanukur    29-apr-03       Added uoo-id as part of MUS build #2829262
9   -------------------------------------------------------------------------------------------
10   l_rowid VARCHAR2(25);
11   old_references IGS_PS_STDNT_UNT_TRN%RowType;
12   new_references IGS_PS_STDNT_UNT_TRN%RowType;
13 
14 
15   PROCEDURE Set_Column_Values (
16     p_action IN VARCHAR2,
17     x_rowid IN VARCHAR2 DEFAULT NULL,
18     x_person_id IN NUMBER DEFAULT NULL,
19     x_course_cd IN VARCHAR2 DEFAULT NULL,
20     x_transfer_course_cd IN VARCHAR2 DEFAULT NULL,
21     x_transfer_dt IN DATE DEFAULT NULL,
22     x_unit_cd IN VARCHAR2 DEFAULT NULL,
23     x_cal_type IN VARCHAR2 DEFAULT NULL,
24     x_ci_sequence_number IN NUMBER DEFAULT NULL,
25     x_creation_date IN DATE DEFAULT NULL,
26     x_created_by IN NUMBER DEFAULT NULL,
27     x_last_update_date IN DATE DEFAULT NULL,
28     x_last_updated_by IN NUMBER DEFAULT NULL,
29     x_last_update_login IN NUMBER DEFAULT NULL,
30     x_uoo_id IN NUMBER DEFAULT NULL
31   ) AS
32 
33     CURSOR cur_old_ref_values IS
34       SELECT   *
35       FROM     IGS_PS_STDNT_UNT_TRN
36       WHERE    rowid = x_rowid;
37 
38   BEGIN
39 
40     l_rowid := x_rowid;
41 
42     -- Code for setting the Old and New Reference Values.
43     -- Populate Old Values.
44     Open cur_old_ref_values;
45     Fetch cur_old_ref_values INTO old_references;
46     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
47       Close cur_old_ref_values;
48       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49       IGS_GE_MSG_STACK.ADD;
50       App_Exception.Raise_Exception;
51       Return;
52     END IF;
53     Close cur_old_ref_values;
54 
55     -- Populate New Values.
56     new_references.person_id := x_person_id;
57     new_references.course_cd := x_course_cd;
58     new_references.transfer_course_cd := x_transfer_course_cd;
59     new_references.transfer_dt := x_transfer_dt;
60     new_references.unit_cd := x_unit_cd;
61     new_references.cal_type := x_cal_type;
62     new_references.ci_sequence_number := x_ci_sequence_number;
63     new_references.uoo_id := x_uoo_id;
64     IF (p_action = 'UPDATE') THEN
65       new_references.creation_date := old_references.creation_date;
66       new_references.created_by := old_references.created_by;
67     ELSE
68       new_references.creation_date := x_creation_date;
69       new_references.created_by := x_created_by;
70     END IF;
71     new_references.last_update_date := x_last_update_date;
72     new_references.last_updated_by := x_last_updated_by;
73     new_references.last_update_login := x_last_update_login;
74 
75   END Set_Column_Values;
76 -------------------------------------------------------------------------------------------
77   --Change History:
78   --Who         When            What
79   --svanukur    29-APR-03    Passed uoo_id to IGS_EN_VAL_SUT.enrp_val_sut_insert , IGS_EN_VAL_SUT.enrp_val_sut_delete
80   --                           as part of MUS build, # 2829262
81   -------------------------------------------------------------------------------------------
82   PROCEDURE BeforeRowInsertDelete1(
83     p_inserting IN BOOLEAN DEFAULT FALSE,
84     p_updating IN BOOLEAN DEFAULT FALSE,
85     p_deleting IN BOOLEAN DEFAULT FALSE
86     ) AS
87 	v_message_name	VARCHAR2(30);
88   BEGIN
89 	-- If trigger has not been disabled, perform required processing
90 	IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_PS_STDNT_UNT_TRN') THEN
91 		-- Insert validation
92 		IF	p_inserting THEN
93 			IF IGS_EN_VAL_SUT.enrp_val_sut_insert (
94 				new_references.person_id,
95 				new_references.course_cd,
96 				new_references.transfer_course_cd,
97 				new_references.unit_cd,
98 				new_references.cal_type,
99 				new_references.ci_sequence_number,
100 				v_message_name,
101                 new_references.uoo_id) = FALSE THEN
102 					Fnd_Message.Set_Name('IGS', v_message_name);
103 					IGS_GE_MSG_STACK.ADD;
104 					App_Exception.Raise_Exception;
105 			END IF;
106 		END IF;
107 		--Delete validation
108 		IF	p_deleting THEN
109 			IF IGS_EN_VAL_SUT.enrp_val_sut_delete (
110 				old_references.person_id,
111 				old_references.course_cd,
112 				old_references.unit_cd,
113 				old_references.cal_type,
114 				old_references.ci_sequence_number,
115 				v_message_name,
116                 old_references.uoo_id) = FALSE THEN
117 					Fnd_Message.Set_Name('IGS', v_message_name);
118 					IGS_GE_MSG_STACK.ADD;
119 					App_Exception.Raise_Exception;
120 			END IF;
121 		END IF;
122 	END IF;
123 
124 
125   END BeforeRowInsertDelete1;
126 
127  PROCEDURE Check_Constraints (
128  Column_Name	IN	VARCHAR2	DEFAULT NULL,
129  Column_Value 	IN	VARCHAR2	DEFAULT NULL
130  )
131  AS
132  BEGIN
133 
134  IF  column_name is null then
135      NULL;
136  ELSIF upper(Column_name) = 'COURSE_CD' then
137      new_references.course_cd := column_value;
138  ELSIF upper(Column_name) = 'TRANSFER_COURSE_CD' then
139      new_references.transfer_course_cd := column_value;
140  ELSIF upper(Column_name) = 'UNIT_CD' then
141      new_references.unit_cd:= column_value;
142  ELSIF upper(Column_name) = 'CAL_TYPE' then
143      new_references.cal_type := column_value;
144  END IF;
145 
146 IF upper(column_name) = 'COURSE_CD' OR
147      column_name is null Then
148      IF new_references.course_cd <> UPPER(new_references.course_cd) Then
149        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
150        IGS_GE_MSG_STACK.ADD;
151        App_Exception.Raise_Exception;
152      END IF;
153 END IF;
154 
155 IF upper(column_name) = 'TRANSFER_COURSE_CD' OR
156      column_name is null Then
157      IF new_references.transfer_course_cd <> UPPER(new_references.transfer_course_cd) Then
158        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
159        IGS_GE_MSG_STACK.ADD;
160        App_Exception.Raise_Exception;
161      END IF;
162 END IF;
163 
164 IF upper(column_name) = 'UNIT_CD' OR
165      column_name is null Then
166      IF new_references.unit_cd <> UPPER(new_references.unit_cd) Then
167        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
168        IGS_GE_MSG_STACK.ADD;
169        App_Exception.Raise_Exception;
170      END IF;
171 END IF;
172 
173 IF upper(column_name) = 'CAL_TYPE' OR
174      column_name is null Then
175      IF new_references.cal_type <> UPPER(new_references.cal_type) Then
176        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
177        IGS_GE_MSG_STACK.ADD;
178        App_Exception.Raise_Exception;
179      END IF;
180 END IF;
181 END check_constraints;
182 
183 
184  PROCEDURE Check_Parent_Existance AS
185  -------------------------------------------------------------------------------------------
186  --Change History:
187  --Who         When            What
188  --KKILLAMS    27-07-2003      Passing transfer_course_cd instead of course_cd while call
189  --                            IGS_EN_SU_ATTEMPT_PKG.Get_PK_For_Validation api w.r.t. bug 3064355
190  -------------------------------------------------------------------------------------------
191   BEGIN
192 
193     IF (((old_references.person_id = new_references.person_id) AND
194          (old_references.course_cd = new_references.course_cd) AND
195          (old_references.transfer_course_cd = new_references.transfer_course_cd) AND
196          (old_references.transfer_dt = new_references.transfer_dt)) OR
197         ((new_references.person_id IS NULL) OR
198          (new_references.course_cd IS NULL) OR
199          (new_references.transfer_course_cd IS NULL) OR
200          (new_references.transfer_dt IS NULL))) THEN
201       NULL;
202     ELSE
203       IF NOT IGS_PS_STDNT_TRN_PKG.Get_PK_For_Validation (
204         new_references.person_id,
205         new_references.course_cd,
206         new_references.transfer_course_cd,
207         new_references.transfer_dt
208         ) THEN
209 		  Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
210 		  IGS_GE_MSG_STACK.ADD;
211 		  App_Exception.Raise_Exception;
212 	END IF;
213 
214     END IF;
215 
216     IF (((old_references.person_id = new_references.person_id) AND
217          (old_references.transfer_course_cd = new_references.transfer_course_cd) AND
218          (old_references.uoo_id = new_references.uoo_id)) OR
219         ((new_references.person_id IS NULL) OR
220          (new_references.transfer_course_cd IS NULL) OR
221          (new_references.uoo_id IS NULL))) THEN
222       NULL;
223     ELSE
224       IF NOT IGS_EN_SU_ATTEMPT_PKG.Get_PK_For_Validation (
225         new_references.person_id,
226         new_references.transfer_course_cd,
227         new_references.uoo_id
228          ) THEN
229 		    Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
230 		    IGS_GE_MSG_STACK.ADD;
231 		    App_Exception.Raise_Exception;
232 	END IF;
233 
234     END IF;
235 
236   END Check_Parent_Existance;
237  -------------------------------------------------------------------------------------------
238   --Change History:
239   --Who         When            What
240   --svanukur    29-APR-03    changed the PK columns as part of MUS build, # 2829262
241   -------------------------------------------------------------------------------------------
242   FUNCTION Get_PK_For_Validation (
243     x_person_id IN NUMBER,
244     x_course_cd IN VARCHAR2,
245     x_transfer_course_cd IN VARCHAR2,
246     x_transfer_dt IN DATE,
247     x_uoo_id IN NUMBER
248      ) RETURN BOOLEAN AS
249 
250     CURSOR cur_rowid IS
251       SELECT   rowid
252       FROM     IGS_PS_STDNT_UNT_TRN
253       WHERE    person_id = x_person_id
254       AND      course_cd = x_course_cd
255       AND      transfer_course_cd = x_transfer_course_cd
256       AND      transfer_dt = x_transfer_dt
257       AND      uoo_id = x_uoo_id
258       FOR UPDATE NOWAIT;
259 
260     lv_rowid cur_rowid%RowType;
261 
262   BEGIN
263 
264     Open cur_rowid;
265     Fetch cur_rowid INTO lv_rowid;
266 	IF (cur_rowid%FOUND) THEN
267        Close cur_rowid;
268        Return (TRUE);
269 	ELSE
270        Close cur_rowid;
271        Return (FALSE);
272 	END IF;
273 END Get_PK_For_Validation;
274 
275   PROCEDURE GET_FK_IGS_PS_STDNT_TRN (
276     x_person_id IN NUMBER,
277     x_course_cd IN VARCHAR2,
278     x_transfer_course_cd IN VARCHAR2,
279     x_transfer_dt IN DATE
280     ) AS
281 
282     CURSOR cur_rowid IS
283       SELECT   rowid
284       FROM     IGS_PS_STDNT_UNT_TRN
285       WHERE    person_id = x_person_id
286       AND      course_cd = x_course_cd
287       AND      transfer_course_cd = x_transfer_course_cd
288       AND      transfer_dt = x_transfer_dt ;
289 
290     lv_rowid cur_rowid%RowType;
291 
292   BEGIN
293 
294     Open cur_rowid;
295     Fetch cur_rowid INTO lv_rowid;
296     IF (cur_rowid%FOUND) THEN
297       Close cur_rowid;
298       Fnd_Message.Set_Name ('IGS', 'IGS_PS_SUT_SCT_FK');
299       IGS_GE_MSG_STACK.ADD;
300       App_Exception.Raise_Exception;
301       Return;
302     END IF;
303     Close cur_rowid;
304 
305   END GET_FK_IGS_PS_STDNT_TRN;
306 
307   PROCEDURE GET_FK_IGS_EN_SU_ATTEMPT (
308     x_person_id IN NUMBER,
309     x_course_cd IN VARCHAR2,
310     x_uoo_id IN NUMBER
311      ) AS
312 
313     CURSOR cur_rowid IS
314       SELECT   rowid
315       FROM     IGS_PS_STDNT_UNT_TRN
316       WHERE    person_id = x_person_id
317       AND      transfer_course_cd = x_course_cd
318       AND      uoo_id = x_uoo_id
319        ;
320 
321     lv_rowid cur_rowid%RowType;
322 
323   BEGIN
324 
325     Open cur_rowid;
326     Fetch cur_rowid INTO lv_rowid;
327     IF (cur_rowid%FOUND) THEN
328       Close cur_rowid;
329       Fnd_Message.Set_Name ('IGS', 'IGS_PS_SUT_SUA_TRANSFER_FK');
330       IGS_GE_MSG_STACK.ADD;
331       App_Exception.Raise_Exception;
332       Return;
333     END IF;
334     Close cur_rowid;
335 
336   END GET_FK_IGS_EN_SU_ATTEMPT;
337 
338   PROCEDURE Before_DML (
339     p_action IN VARCHAR2,
340     x_rowid IN VARCHAR2 DEFAULT NULL,
341     x_person_id IN NUMBER DEFAULT NULL,
342     x_course_cd IN VARCHAR2 DEFAULT NULL,
343     x_transfer_course_cd IN VARCHAR2 DEFAULT NULL,
344     x_transfer_dt IN DATE DEFAULT NULL,
345     x_unit_cd IN VARCHAR2 DEFAULT NULL,
346     x_cal_type IN VARCHAR2 DEFAULT NULL,
347     x_ci_sequence_number IN NUMBER DEFAULT NULL,
348     x_creation_date IN DATE DEFAULT NULL,
349     x_created_by IN NUMBER DEFAULT NULL,
350     x_last_update_date IN DATE DEFAULT NULL,
351     x_last_updated_by IN NUMBER DEFAULT NULL,
352     x_last_update_login IN NUMBER DEFAULT NULL,
353     x_uoo_id IN NUMBER DEFAULT NULL
354   ) AS
355   BEGIN
356 
357     Set_Column_Values (
358       p_action,
359       x_rowid,
360       x_person_id,
361       x_course_cd,
362       x_transfer_course_cd,
363       x_transfer_dt,
364       x_unit_cd,
365       x_cal_type,
366       x_ci_sequence_number,
367       x_creation_date,
368       x_created_by,
369       x_last_update_date,
370       x_last_updated_by,
371       x_last_update_login,
372       x_uoo_id
373     );
374 
375  IF (p_action = 'INSERT') THEN
376        -- Call all the procedures related to Before Insert.
377       BeforeRowInsertDelete1 ( p_inserting => TRUE );
378       IF  Get_PK_For_Validation (
379 			    new_references.person_id,
380 			    new_references.course_cd,
381 			    new_references.transfer_course_cd,
382 			    new_references.transfer_dt,
383 			    new_references.uoo_id
384 			    	) THEN
385          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
386          IGS_GE_MSG_STACK.ADD;
387           App_Exception.Raise_Exception;
388       END IF;
389       Check_Constraints;
390       Check_Parent_Existance;
391  ELSIF (p_action = 'UPDATE') THEN
392        -- Call all the procedures related to Before Update.
393        Check_Constraints;
394        Check_Parent_Existance;
395  ELSIF (p_action = 'DELETE') THEN
396        -- Call all the procedures related to Before Delete.
397       BeforeRowInsertDelete1 ( p_deleting => TRUE );
398  ELSIF (p_action = 'VALIDATE_INSERT') THEN
399       IF  Get_PK_For_Validation (
400 			    new_references.person_id,
401 			    new_references.course_cd,
402 			    new_references.transfer_course_cd,
403 			    new_references.transfer_dt,
404 			    new_references.uoo_id
405 			    	) THEN
406          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
407          IGS_GE_MSG_STACK.ADD;
408           App_Exception.Raise_Exception;
409       END IF;
410       Check_Constraints;
411  ELSIF (p_action = 'VALIDATE_UPDATE') THEN
412        Check_Constraints;
413  END IF;
414 
415   END Before_DML;
416 
417   PROCEDURE After_DML (
418     p_action IN VARCHAR2,
419     x_rowid IN VARCHAR2
420   ) AS
421   BEGIN
422 
423     l_rowid := x_rowid;
424 
425 
426   END After_DML;
427 
428 procedure INSERT_ROW (
429   X_ROWID in out NOCOPY VARCHAR2,
430   X_PERSON_ID in NUMBER,
431   X_TRANSFER_COURSE_CD in VARCHAR2,
432   X_COURSE_CD in VARCHAR2,
433   X_CAL_TYPE in VARCHAR2,
434   X_CI_SEQUENCE_NUMBER in NUMBER,
435   X_UNIT_CD in VARCHAR2,
436   X_TRANSFER_DT in DATE,
437   X_MODE in VARCHAR2 default 'R',
438   X_UOO_ID in NUMBER
439   ) as
440     cursor C is select ROWID from IGS_PS_STDNT_UNT_TRN
441       where PERSON_ID = X_PERSON_ID
442       and TRANSFER_COURSE_CD = X_TRANSFER_COURSE_CD
443       and COURSE_CD = X_COURSE_CD
444       and UOO_ID = X_UOO_ID
445       and TRANSFER_DT = X_TRANSFER_DT;
446     X_LAST_UPDATE_DATE DATE;
447     X_LAST_UPDATED_BY NUMBER;
448     X_LAST_UPDATE_LOGIN NUMBER;
449 begin
450   X_LAST_UPDATE_DATE := SYSDATE;
451   if(X_MODE = 'I') then
452     X_LAST_UPDATED_BY := 1;
453     X_LAST_UPDATE_LOGIN := 0;
454   elsif (X_MODE = 'R') then
455     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
456     if X_LAST_UPDATED_BY is NULL then
457       X_LAST_UPDATED_BY := -1;
458     end if;
459     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
460     if X_LAST_UPDATE_LOGIN is NULL then
461       X_LAST_UPDATE_LOGIN := -1;
462     end if;
463   else
464     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
465     IGS_GE_MSG_STACK.ADD;
466     app_exception.raise_exception;
467   end if;
468 
469   Before_DML(
470   p_action => 'INSERT',
471   x_rowid => X_ROWID,
472   x_person_id => X_PERSON_ID,
473   x_course_cd => X_COURSE_CD,
474   x_transfer_course_cd => X_TRANSFER_COURSE_CD,
475   x_transfer_dt => X_TRANSFER_DT,
476   x_unit_cd => X_UNIT_CD,
477   x_cal_type => X_CAL_TYPE,
478   x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
479   x_creation_date => X_LAST_UPDATE_DATE,
480   x_created_by => X_LAST_UPDATED_BY,
481   x_last_update_date => X_LAST_UPDATE_DATE,
482   x_last_updated_by => X_LAST_UPDATED_BY,
483   x_last_update_login => X_LAST_UPDATE_LOGIN,
484   x_uoo_id =>X_UOO_ID
485   );
486 
487   insert into IGS_PS_STDNT_UNT_TRN (
488     PERSON_ID,
489     COURSE_CD,
490     TRANSFER_COURSE_CD,
491     TRANSFER_DT,
492     UNIT_CD,
493     CAL_TYPE,
494     CI_SEQUENCE_NUMBER,
495     CREATION_DATE,
496     CREATED_BY,
497     LAST_UPDATE_DATE,
498     LAST_UPDATED_BY,
499     LAST_UPDATE_LOGIN,
500     UOO_ID
501   ) values (
502     NEW_REFERENCES.PERSON_ID,
503     NEW_REFERENCES.COURSE_CD,
504     NEW_REFERENCES.TRANSFER_COURSE_CD,
505     NEW_REFERENCES.TRANSFER_DT,
506     NEW_REFERENCES.UNIT_CD,
507     NEW_REFERENCES.CAL_TYPE,
508     NEW_REFERENCES.CI_SEQUENCE_NUMBER,
509     X_LAST_UPDATE_DATE,
510     X_LAST_UPDATED_BY,
511     X_LAST_UPDATE_DATE,
512     X_LAST_UPDATED_BY,
513     X_LAST_UPDATE_LOGIN,
514     NEW_REFERENCES.UOO_ID
515   );
516 
517   open c;
518   fetch c into X_ROWID;
519   if (c%notfound) then
520     close c;
521     raise no_data_found;
522   end if;
523   close c;
524   After_DML (
525      p_action => 'INSERT',
526      x_rowid => X_ROWID
527     );
528 
529 end INSERT_ROW;
530 
531 procedure LOCK_ROW (
532   X_ROWID IN VARCHAR2,
533   X_PERSON_ID in NUMBER,
534   X_TRANSFER_COURSE_CD in VARCHAR2,
535   X_COURSE_CD in VARCHAR2,
536   X_CAL_TYPE in VARCHAR2,
537   X_CI_SEQUENCE_NUMBER in NUMBER,
538   X_UNIT_CD in VARCHAR2,
539   X_TRANSFER_DT in DATE,
540   X_UOO_ID in NUMBER
541 ) as
542   cursor c1 is select ROWID
543     from IGS_PS_STDNT_UNT_TRN
544     where ROWID = X_ROWID
545     for update nowait;
546   tlinfo c1%rowtype;
547 
548 begin
549   open c1;
550   fetch c1 into tlinfo;
551   if (c1%notfound) then
552     close c1;
553     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
554     IGS_GE_MSG_STACK.ADD;
555     app_exception.raise_exception;
556     return;
557   end if;
558   close c1;
559 
560   return;
561 end LOCK_ROW;
562 
563 procedure DELETE_ROW (
564   X_ROWID IN VARCHAR2
565 ) as
566 begin
567   Before_DML(
568   p_action => 'DELETE',
569   x_rowid => X_ROWID
570   );
571   delete from IGS_PS_STDNT_UNT_TRN
572   where ROWID = X_ROWID;
573   if (sql%notfound) then
574     raise no_data_found;
575   end if;
576   After_DML(
577   p_action => 'DELETE',
578   x_rowid => X_ROWID
579   );
580 end DELETE_ROW;
581 
582 end IGS_PS_STDNT_UNT_TRN_PKG;