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