[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_TER_ED_UNI_AT_PKG
Source
1 package body IGS_AD_TER_ED_UNI_AT_PKG as
2 /* $Header: IGSAI51B.pls 115.4 2002/11/28 22:08:07 nsidana ship $ */
3 -- Bg No 1956374 , Procedure admp_val_teua_sret reference is changed
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_AD_TER_ED_UNI_AT%RowType;
7 new_references IGS_AD_TER_ED_UNI_AT%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_te_sequence_number IN NUMBER DEFAULT NULL,
14 x_unit_cd IN VARCHAR2 DEFAULT NULL,
15 x_sequence_number IN NUMBER DEFAULT NULL,
16 x_enrolled_yr IN NUMBER DEFAULT NULL,
17 x_result_type IN VARCHAR2 DEFAULT NULL,
18 x_title IN VARCHAR2 DEFAULT NULL,
19 x_credit_points IN NUMBER DEFAULT NULL,
20 x_grade IN VARCHAR2 DEFAULT NULL,
21 x_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL
27 ) as
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_AD_TER_ED_UNI_AT
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 Open cur_old_ref_values;
41 Fetch cur_old_ref_values INTO old_references;
42 IF (cur_old_ref_values%NOTFOUND) AND p_action NOT IN ('INSERT','VALIDATE_INSERT') THEN
43 Close cur_old_ref_values;
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 App_Exception.Raise_Exception;
47 Return;
48 END IF;
49 Close cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.person_id := x_person_id;
53 new_references.te_sequence_number := x_te_sequence_number;
54 new_references.unit_cd := x_unit_cd;
55 new_references.sequence_number := x_sequence_number;
56 new_references.enrolled_yr := x_enrolled_yr;
57 new_references.result_type := x_result_type;
58 new_references.title := x_title;
59 new_references.credit_points := x_credit_points;
60 new_references.grade := x_grade;
61 new_references.discipline_group_cd := x_discipline_group_cd;
62 IF (p_action = 'UPDATE') THEN
63 new_references.creation_date := old_references.creation_date;
64 new_references.created_by := old_references.created_by;
65 ELSE
66 new_references.creation_date := x_creation_date;
67 new_references.created_by := x_created_by;
68 END IF;
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END Set_Column_Values;
74
75 PROCEDURE BeforeRowInsertUpdate1(
76 p_inserting IN BOOLEAN DEFAULT FALSE,
77 p_updating IN BOOLEAN DEFAULT FALSE,
78 p_deleting IN BOOLEAN DEFAULT FALSE
79 ) as
80 v_message_name VARCHAR2(30);
81 BEGIN
82 --
83 -- Validate Tertiary Education unit Attempt
84 --
85 IF p_inserting
86 OR (old_references.result_type <> new_references.result_type) THEN
87 -- Validate Result Type
88 IF IGS_AD_VAL_OSES.admp_val_teua_sret(
89 new_references.result_type,
90 v_message_name) = FALSE THEN
91 Fnd_Message.Set_Name('IGS', v_message_name);
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception;
94 END IF;
95 END IF;
96
97
98 END BeforeRowInsertUpdate1;
99
100 procedure Check_Constraints (
101 Column_Name IN VARCHAR2 DEFAULT NULL,
102 Column_Value IN VARCHAR2 DEFAULT NULL
103 )
104 AS
105 BEGIN
106
107 IF Column_Name is null then
108 NULL;
109 ELSIF upper(Column_Name) = 'TE_SEQUENCE_NUMBER' then
110 new_references.te_sequence_number := igs_ge_number.to_num(column_value);
111 ELSIF upper(Column_Name) = 'SEQUENCE_NUMBER' then
112 new_references.sequence_number := igs_ge_number.to_num(column_value);
113 ELSIF upper(Column_Name) = 'CREDIT_POINTS' then
114 new_references.credit_points := igs_ge_number.to_num(column_value);
115 ELSIF upper(Column_Name) = 'ENROLLED_YR' then
116 new_references.enrolled_yr := igs_ge_number.to_num(column_value);
117 ELSIF upper(Column_Name) = 'DISCIPLINE_GROUP_CD' then
118 new_references.discipline_group_cd := column_value;
119 ELSIF upper(Column_Name) = 'UNIT_CD' then
120 new_references.unit_cd := column_value;
121 ELSIF upper(Column_Name) = 'GRADE' then
122 new_references.grade := column_value;
123 ELSIF upper(Column_Name) = 'TITLE' then
124 new_references.title := column_value;
125 ELSIF upper(Column_Name) = 'RESULT_TYPE' then
126 new_references.result_type := column_value;
127 END IF;
128
129 IF upper(Column_Name) = 'TE_SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
130 IF new_references.te_sequence_number < 1 OR new_references.te_sequence_number > 999999 THEN
131 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136 IF upper(Column_Name) = 'SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
137 IF new_references.sequence_number < 1 OR new_references.sequence_number > 999999 THEN
138 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
139 IGS_GE_MSG_STACK.ADD;
140 App_Exception.Raise_Exception;
141 END IF;
142 END IF;
143 IF upper(Column_Name) = 'CREDIT_POINTS' OR Column_Name IS NULL THEN
144 IF new_references.credit_points < 0 OR new_references.credit_points > 999.999 THEN
145 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END IF;
149 END IF;
150 IF upper(Column_Name) = 'ENROLLED_YR' OR Column_Name IS NULL THEN
151 IF new_references.enrolled_yr < 1900 OR new_references.enrolled_yr > 2050 THEN
152 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
153 IGS_GE_MSG_STACK.ADD;
154 App_Exception.Raise_Exception;
155 END IF;
156 END IF;
157 IF upper(Column_Name) = 'DISCIPLINE_GROUP_CD' OR Column_Name IS NULL THEN
158 IF new_references.discipline_group_cd <> UPPER(new_references.discipline_group_cd) THEN
159 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163 END IF;
164 IF upper(Column_Name) = 'UNIT_CD' OR Column_Name IS NULL THEN
165 IF new_references.unit_cd <> UPPER(new_references.unit_cd) THEN
166 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
167 IGS_GE_MSG_STACK.ADD;
168 App_Exception.Raise_Exception;
169 END IF;
170 END IF;
171 IF upper(Column_Name) = 'GRADE' OR Column_Name IS NULL THEN
172 IF new_references.grade <> UPPER(new_references.grade) THEN
173 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177 END IF;
178 IF upper(Column_Name) = 'TITLE' OR Column_Name IS NULL THEN
179 IF new_references.title <> UPPER(new_references.title) THEN
180 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 END IF;
185 IF upper(Column_Name) = 'RESULT_TYPE' OR Column_Name IS NULL THEN
186 IF new_references.result_type <> UPPER(new_references.result_type) THEN
187 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192
193 END Check_Constraints;
194
195 PROCEDURE Check_Parent_Existance as
196 BEGIN
197
198 IF (((old_references.discipline_group_cd = new_references.discipline_group_cd)) OR
199 ((new_references.discipline_group_cd IS NULL))) THEN
200 NULL;
201 ELSE
202 IF NOT IGS_PS_DSCP_PKG.Get_PK_For_Validation (
203 new_references.discipline_group_cd
204 ) THEN
205 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
206 IGS_GE_MSG_STACK.ADD;
207 App_Exception.Raise_Exception;
208 END IF;
209 END IF;
210
211 IF (((old_references.person_id = new_references.person_id) AND
212 (old_references.te_sequence_number = new_references.te_sequence_number)) OR
213 ((new_references.person_id IS NULL) OR
214 (new_references.te_sequence_number IS NULL))) THEN
215 NULL;
216 ELSE
217 IF NOT IGS_AD_TER_EDU_PKG.Get_PK_For_Validation (
218 new_references.person_id,
219 new_references.te_sequence_number
220 ) THEN
221 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END IF;
225 END IF;
226
227 END Check_Parent_Existance;
228
229 function Get_PK_For_Validation (
230 x_person_id IN NUMBER,
231 x_te_sequence_number IN NUMBER,
232 x_unit_cd IN VARCHAR2,
233 x_sequence_number IN NUMBER
234 )return BOOLEAN AS
235
236 CURSOR cur_rowid IS
237 SELECT rowid
238 FROM IGS_AD_TER_ED_UNI_AT
239 WHERE person_id = x_person_id
240 AND te_sequence_number = x_te_sequence_number
241 AND unit_cd = x_unit_cd
242 AND sequence_number = x_sequence_number
243 FOR UPDATE NOWAIT;
244
245 lv_rowid cur_rowid%RowType;
246
247 BEGIN
248
249 Open cur_rowid;
250 Fetch cur_rowid INTO lv_rowid;
251 IF (cur_rowid%FOUND) THEN
252 Close cur_rowid;
253 Return(TRUE);
254 ELSE
255 Close cur_rowid;
256 Return(FALSE);
257 END IF;
258
259 END Get_PK_For_Validation;
260
261 PROCEDURE GET_FK_IGS_PS_DSCP (
262 x_discipline_group_cd IN VARCHAR2
263 ) AS
264
265 CURSOR cur_rowid IS
266 SELECT rowid
267 FROM IGS_AD_TER_ED_UNI_AT
268 WHERE discipline_group_cd = x_discipline_group_cd ;
269
270 lv_rowid cur_rowid%RowType;
271
272 BEGIN
273
274 Open cur_rowid;
275 Fetch cur_rowid INTO lv_rowid;
276 IF (cur_rowid%FOUND) THEN
277 Close cur_rowid;
278 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TEUA_DI_FK');
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 Return;
282 END IF;
283 Close cur_rowid;
284
285 END GET_FK_IGS_PS_DSCP;
286
287 PROCEDURE GET_FK_IGS_AD_TER_EDU (
288 x_person_id IN NUMBER,
289 x_sequence_number IN NUMBER
290 ) AS
291
292 CURSOR cur_rowid IS
293 SELECT rowid
294 FROM IGS_AD_TER_ED_UNI_AT
295 WHERE person_id = x_person_id
296 AND te_sequence_number = x_sequence_number ;
297
298 lv_rowid cur_rowid%RowType;
299
300 BEGIN
301
302 Open cur_rowid;
303 Fetch cur_rowid INTO lv_rowid;
304 IF (cur_rowid%FOUND) THEN
305 Close cur_rowid;
306 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TEUA_TE_FK');
307 IGS_GE_MSG_STACK.ADD;
308 App_Exception.Raise_Exception;
309 Return;
310 END IF;
311 Close cur_rowid;
312
313 END GET_FK_IGS_AD_TER_EDU;
314
315 PROCEDURE Before_DML (
316 p_action IN VARCHAR2,
317 x_rowid IN VARCHAR2 DEFAULT NULL,
321 x_sequence_number IN NUMBER DEFAULT NULL,
318 x_person_id IN NUMBER DEFAULT NULL,
319 x_te_sequence_number IN NUMBER DEFAULT NULL,
320 x_unit_cd IN VARCHAR2 DEFAULT NULL,
322 x_enrolled_yr IN NUMBER DEFAULT NULL,
323 x_result_type IN VARCHAR2 DEFAULT NULL,
324 x_title IN VARCHAR2 DEFAULT NULL,
325 x_credit_points IN NUMBER DEFAULT NULL,
326 x_grade IN VARCHAR2 DEFAULT NULL,
327 x_discipline_group_cd IN VARCHAR2 DEFAULT NULL,
328 x_creation_date IN DATE DEFAULT NULL,
329 x_created_by IN NUMBER DEFAULT NULL,
330 x_last_update_date IN DATE DEFAULT NULL,
331 x_last_updated_by IN NUMBER DEFAULT NULL,
332 x_last_update_login IN NUMBER DEFAULT NULL
333 ) AS
334 BEGIN
335
336 Set_Column_Values (
337 p_action,
338 x_rowid,
339 x_person_id,
340 x_te_sequence_number,
341 x_unit_cd,
342 x_sequence_number,
343 x_enrolled_yr,
344 x_result_type,
345 x_title,
346 x_credit_points,
347 x_grade,
348 x_discipline_group_cd,
349 x_creation_date,
350 x_created_by,
351 x_last_update_date,
352 x_last_updated_by,
353 x_last_update_login
354 );
355
356 IF (p_action = 'INSERT') THEN
357 -- Call all the procedures related to Before Insert.
358 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
359 IF Get_PK_For_Validation (
360 new_references.person_id,
361 new_references.te_sequence_number,
362 new_references.unit_cd,
363 new_references.sequence_number
364 ) THEN
365 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
366 IGS_GE_MSG_STACK.ADD;
367 App_Exception.Raise_Exception;
368 END IF;
369 Check_Constraints;
370 Check_Parent_Existance;
371 ELSIF (p_action = 'UPDATE') THEN
372 -- Call all the procedures related to Before Update.
373 BeforeRowInsertUpdate1 ( p_updating => TRUE );
374 Check_Constraints;
375 Check_Parent_Existance;
376 ELSIF (p_action = 'DELETE') THEN
377 -- Call all the procedures related to Before Delete.
378 Null;
379 ELSIF (p_action = 'VALIDATE_INSERT') THEN
380 IF Get_PK_For_Validation (
381 new_references.person_id,
382 new_references.te_sequence_number,
383 new_references.unit_cd,
384 new_references.sequence_number
385 ) THEN
386 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
387 IGS_GE_MSG_STACK.ADD;
388 App_Exception.Raise_Exception;
389 END IF;
390 Check_Constraints;
391 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
392 Check_Constraints;
393 ELSIF (p_action = 'VALIDATE_DELETE') THEN
394 Null;
395 END IF;
396
397 END Before_DML;
398
399 PROCEDURE After_DML (
400 p_action IN VARCHAR2,
401 x_rowid IN VARCHAR2
402 ) as
403 BEGIN
404 l_rowid := x_rowid;
405 END After_DML;
406
407 procedure INSERT_ROW (
408 X_ROWID in out NOCOPY VARCHAR2,
409 X_PERSON_ID in NUMBER,
410 X_TE_SEQUENCE_NUMBER in NUMBER,
411 X_UNIT_CD in VARCHAR2,
412 X_SEQUENCE_NUMBER in NUMBER,
413 X_ENROLLED_YR in NUMBER,
414 X_RESULT_TYPE in VARCHAR2,
415 X_TITLE in VARCHAR2,
416 X_CREDIT_POINTS in NUMBER,
417 X_GRADE in VARCHAR2,
418 X_DISCIPLINE_GROUP_CD in VARCHAR2,
419 X_MODE in VARCHAR2 default 'R'
420 ) As
421 cursor C is select ROWID from IGS_AD_TER_ED_UNI_AT
422 where PERSON_ID = X_PERSON_ID
423 and TE_SEQUENCE_NUMBER = X_TE_SEQUENCE_NUMBER
424 and UNIT_CD = X_UNIT_CD
425 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
426 X_LAST_UPDATE_DATE DATE;
427 X_LAST_UPDATED_BY NUMBER;
428 X_LAST_UPDATE_LOGIN NUMBER;
429 begin
430 X_LAST_UPDATE_DATE := SYSDATE;
431 if(X_MODE = 'I') then
432 X_LAST_UPDATED_BY := 1;
433 X_LAST_UPDATE_LOGIN := 0;
434 elsif (X_MODE = 'R') then
435 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
436 if X_LAST_UPDATED_BY is NULL then
437 X_LAST_UPDATED_BY := -1;
438 end if;
439 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
440 if X_LAST_UPDATE_LOGIN is NULL then
441 X_LAST_UPDATE_LOGIN := -1;
442 end if;
443 else
444 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
445 IGS_GE_MSG_STACK.ADD;
446 app_exception.raise_exception;
447 end if;
448 Before_DML (
449 p_action => 'INSERT',
450 x_rowid => X_ROWID,
451 x_person_id => X_PERSON_ID,
452 x_te_sequence_number => X_TE_SEQUENCE_NUMBER,
453 x_unit_cd => X_UNIT_CD,
454 x_sequence_number => X_SEQUENCE_NUMBER,
455 x_enrolled_yr => X_ENROLLED_YR,
456 x_result_type => X_RESULT_TYPE,
457 x_title => X_TITLE,
458 x_credit_points => X_CREDIT_POINTS,
459 x_grade => X_GRADE,
460 x_discipline_group_cd => X_DISCIPLINE_GROUP_CD,
461 x_creation_date => X_LAST_UPDATE_DATE,
462 x_created_by => X_LAST_UPDATED_BY,
463 x_last_update_date => X_LAST_UPDATE_DATE,
464 x_last_updated_by => X_LAST_UPDATED_BY,
465 x_last_update_login => X_LAST_UPDATE_LOGIN
466 );
467
468 insert into IGS_AD_TER_ED_UNI_AT (
469 PERSON_ID,
470 TE_SEQUENCE_NUMBER,
471 UNIT_CD,
472 SEQUENCE_NUMBER,
473 ENROLLED_YR,
474 RESULT_TYPE,
475 TITLE,
476 CREDIT_POINTS,
477 GRADE,
478 DISCIPLINE_GROUP_CD,
479 CREATION_DATE,
480 CREATED_BY,
481 LAST_UPDATE_DATE,
482 LAST_UPDATED_BY,
483 LAST_UPDATE_LOGIN
484 ) values (
485 NEW_REFERENCES.PERSON_ID,
486 NEW_REFERENCES.TE_SEQUENCE_NUMBER,
487 NEW_REFERENCES.UNIT_CD,
488 NEW_REFERENCES.SEQUENCE_NUMBER,
489 NEW_REFERENCES.ENROLLED_YR,
490 NEW_REFERENCES.RESULT_TYPE,
491 NEW_REFERENCES.TITLE,
492 NEW_REFERENCES.CREDIT_POINTS,
493 NEW_REFERENCES.GRADE,
494 NEW_REFERENCES.DISCIPLINE_GROUP_CD,
495 X_LAST_UPDATE_DATE,
496 X_LAST_UPDATED_BY,
497 X_LAST_UPDATE_DATE,
498 X_LAST_UPDATED_BY,
499 X_LAST_UPDATE_LOGIN
500 );
501
502 open c;
503 fetch c into X_ROWID;
504 if (c%notfound) then
505 close c;
506 raise no_data_found;
507 end if;
508 close c;
509
510 After_DML (
511 p_action => 'INSERT',
512 x_rowid => X_ROWID
513 );
514 end INSERT_ROW;
515
516 procedure LOCK_ROW (
517 X_ROWID in VARCHAR2,
518 X_PERSON_ID in NUMBER,
519 X_TE_SEQUENCE_NUMBER in NUMBER,
520 X_UNIT_CD in VARCHAR2,
521 X_SEQUENCE_NUMBER in NUMBER,
522 X_ENROLLED_YR in NUMBER,
523 X_RESULT_TYPE in VARCHAR2,
524 X_TITLE in VARCHAR2,
525 X_CREDIT_POINTS in NUMBER,
526 X_GRADE in VARCHAR2,
527 X_DISCIPLINE_GROUP_CD in VARCHAR2
528 ) As
529 cursor c1 is select
530 ENROLLED_YR,
531 RESULT_TYPE,
532 TITLE,
533 CREDIT_POINTS,
534 GRADE,
535 DISCIPLINE_GROUP_CD
536 from IGS_AD_TER_ED_UNI_AT
537 where ROWID = X_ROWID for update nowait;
538 tlinfo c1%rowtype;
539
540 begin
541 open c1;
542 fetch c1 into tlinfo;
543 if (c1%notfound) then
544 close c1;
545 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
546 IGS_GE_MSG_STACK.ADD;
547 app_exception.raise_exception;
548 return;
549 end if;
550 close c1;
551
552 if ( (tlinfo.ENROLLED_YR = X_ENROLLED_YR)
553 AND (tlinfo.RESULT_TYPE = X_RESULT_TYPE)
554 AND ((tlinfo.TITLE = X_TITLE)
555 OR ((tlinfo.TITLE is null)
556 AND (X_TITLE is null)))
557 AND ((tlinfo.CREDIT_POINTS = X_CREDIT_POINTS)
558 OR ((tlinfo.CREDIT_POINTS is null)
559 AND (X_CREDIT_POINTS is null)))
560 AND ((tlinfo.GRADE = X_GRADE)
561 OR ((tlinfo.GRADE is null)
562 AND (X_GRADE is null)))
563 AND ((tlinfo.DISCIPLINE_GROUP_CD = X_DISCIPLINE_GROUP_CD)
564 OR ((tlinfo.DISCIPLINE_GROUP_CD is null)
565 AND (X_DISCIPLINE_GROUP_CD is null)))
566 ) then
567 null;
568 else
569 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
570 IGS_GE_MSG_STACK.ADD;
571 app_exception.raise_exception;
572 end if;
573 return;
574 end LOCK_ROW;
575
576 procedure UPDATE_ROW (
577 X_ROWID in VARCHAR2,
578 X_PERSON_ID in NUMBER,
579 X_TE_SEQUENCE_NUMBER in NUMBER,
580 X_UNIT_CD in VARCHAR2,
581 X_SEQUENCE_NUMBER in NUMBER,
582 X_ENROLLED_YR in NUMBER,
583 X_RESULT_TYPE in VARCHAR2,
584 X_TITLE in VARCHAR2,
585 X_CREDIT_POINTS in NUMBER,
586 X_GRADE in VARCHAR2,
587 X_DISCIPLINE_GROUP_CD in VARCHAR2,
588 X_MODE in VARCHAR2 default 'R'
589 ) As
590 X_LAST_UPDATE_DATE DATE;
591 X_LAST_UPDATED_BY NUMBER;
592 X_LAST_UPDATE_LOGIN NUMBER;
593 begin
594 X_LAST_UPDATE_DATE := SYSDATE;
595 if(X_MODE = 'I') then
596 X_LAST_UPDATED_BY := 1;
597 X_LAST_UPDATE_LOGIN := 0;
598 elsif (X_MODE = 'R') then
599 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
600 if X_LAST_UPDATED_BY is NULL then
601 X_LAST_UPDATED_BY := -1;
602 end if;
603 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
604 if X_LAST_UPDATE_LOGIN is NULL then
605 X_LAST_UPDATE_LOGIN := -1;
606 end if;
607 else
608 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
609 IGS_GE_MSG_STACK.ADD;
610 app_exception.raise_exception;
611 end if;
612 Before_DML (
613 p_action => 'UPDATE',
614 x_rowid => X_ROWID,
615 x_person_id => X_PERSON_ID,
616 x_te_sequence_number => X_TE_SEQUENCE_NUMBER,
617 x_unit_cd => X_UNIT_CD,
618 x_sequence_number => X_SEQUENCE_NUMBER,
619 x_enrolled_yr => X_ENROLLED_YR,
620 x_result_type => X_RESULT_TYPE,
621 x_title => X_TITLE,
622 x_credit_points => X_CREDIT_POINTS,
623 x_grade => X_GRADE,
624 x_discipline_group_cd => X_DISCIPLINE_GROUP_CD,
625 x_creation_date => X_LAST_UPDATE_DATE,
626 x_created_by => X_LAST_UPDATED_BY,
627 x_last_update_date => X_LAST_UPDATE_DATE,
628 x_last_updated_by => X_LAST_UPDATED_BY,
629 x_last_update_login => X_LAST_UPDATE_LOGIN
630 );
631
632 update IGS_AD_TER_ED_UNI_AT set
633 ENROLLED_YR = NEW_REFERENCES.ENROLLED_YR,
634 RESULT_TYPE = NEW_REFERENCES.RESULT_TYPE,
635 TITLE = NEW_REFERENCES.TITLE,
636 CREDIT_POINTS = NEW_REFERENCES.CREDIT_POINTS,
637 GRADE = NEW_REFERENCES.GRADE,
638 DISCIPLINE_GROUP_CD = NEW_REFERENCES.DISCIPLINE_GROUP_CD,
639 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
640 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
641 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
642 where ROWID = X_ROWID
643 ;
644 if (sql%notfound) then
645 raise no_data_found;
646 end if;
647 After_DML (
648 p_action => 'UPDATE',
649 x_rowid => X_ROWID
650 );
651
652 end UPDATE_ROW;
653
654 procedure ADD_ROW (
655 X_ROWID in out NOCOPY VARCHAR2,
656 X_PERSON_ID in NUMBER,
657 X_TE_SEQUENCE_NUMBER in NUMBER,
658 X_UNIT_CD in VARCHAR2,
659 X_SEQUENCE_NUMBER in NUMBER,
660 X_ENROLLED_YR in NUMBER,
661 X_RESULT_TYPE in VARCHAR2,
662 X_TITLE in VARCHAR2,
663 X_CREDIT_POINTS in NUMBER,
664 X_GRADE in VARCHAR2,
665 X_DISCIPLINE_GROUP_CD in VARCHAR2,
666 X_MODE in VARCHAR2 default 'R'
667 ) As
668 cursor c1 is select rowid from IGS_AD_TER_ED_UNI_AT
669 where PERSON_ID = X_PERSON_ID
670 and TE_SEQUENCE_NUMBER = X_TE_SEQUENCE_NUMBER
671 and UNIT_CD = X_UNIT_CD
672 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
673 ;
674 begin
675 open c1;
676 fetch c1 into X_ROWID;
677 if (c1%notfound) then
678 close c1;
679 INSERT_ROW (
680 X_ROWID,
681 X_PERSON_ID,
682 X_TE_SEQUENCE_NUMBER,
683 X_UNIT_CD,
684 X_SEQUENCE_NUMBER,
685 X_ENROLLED_YR,
686 X_RESULT_TYPE,
687 X_TITLE,
688 X_CREDIT_POINTS,
689 X_GRADE,
690 X_DISCIPLINE_GROUP_CD,
691 X_MODE);
692 return;
693 end if;
694 close c1;
695 UPDATE_ROW (
696 X_ROWID,
697 X_PERSON_ID,
698 X_TE_SEQUENCE_NUMBER,
699 X_UNIT_CD,
700 X_SEQUENCE_NUMBER,
701 X_ENROLLED_YR,
702 X_RESULT_TYPE,
703 X_TITLE,
704 X_CREDIT_POINTS,
705 X_GRADE,
706 X_DISCIPLINE_GROUP_CD,
707 X_MODE);
708 end ADD_ROW;
709
710 procedure DELETE_ROW (
711 X_ROWID in VARCHAR2
712 ) As
713 begin
714 Before_DML (
715 p_action => 'DELETE',
716 x_rowid => X_ROWID
717 );
718
719 delete from IGS_AD_TER_ED_UNI_AT
720 where ROWID = X_ROWID;
721 if (sql%notfound) then
722 raise no_data_found;
723 end if;
724
725 After_DML (
726 p_action => 'DELETE',
727 x_rowid => X_ROWID
728 );
729 end DELETE_ROW;
730
731 end IGS_AD_TER_ED_UNI_AT_PKG;