[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SC_ATMPT_NOTE_PKG
Source
1 package body IGS_AS_SC_ATMPT_NOTE_PKG AS
2 /* $Header: IGSDI20B.pls 120.0 2005/07/05 11:51:58 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_SC_ATMPT_NOTE%RowType;
6 new_references IGS_AS_SC_ATMPT_NOTE%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_reference_number IN NUMBER DEFAULT NULL,
13 x_enr_note_type IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20 CURSOR cur_old_ref_values IS
21 SELECT *
22 FROM IGS_AS_SC_ATMPT_NOTE
23 WHERE rowid = x_rowid;
24 BEGIN
25 l_rowid := x_rowid;
26 -- Code for setting the Old and New Reference Values.
27 -- Populate Old Values.
28 Open cur_old_ref_values;
29 Fetch cur_old_ref_values INTO old_references;
30 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
31 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
32 IGS_GE_MSG_STACK.ADD;
33 Close cur_old_ref_values;
34 APP_EXCEPTION.RAISE_EXCEPTION;
35
36 Return;
37 END IF;
38 Close cur_old_ref_values;
39 -- Populate New Values.
40 new_references.person_id := x_person_id;
41 new_references.course_cd := x_course_cd;
42 new_references.reference_number := x_reference_number;
43 new_references.enr_note_type:= x_enr_note_type;
44 IF (p_action = 'UPDATE') THEN
45 new_references.creation_date := old_references.creation_date;
46 new_references.created_by := old_references.created_by;
47 ELSE
48 new_references.creation_date := x_creation_date;
49 new_references.created_by := x_created_by;
50 END IF;
51 new_references.last_update_date := x_last_update_date;
52 new_references.last_updated_by := x_last_updated_by;
53 new_references.last_update_login := x_last_update_login;
54 END Set_Column_Values;
55
56 PROCEDURE Check_Constraints (
57 Column_Name IN VARCHAR2 DEFAULT NULL,
58 Column_Value IN VARCHAR2 DEFAULT NULL
59 ) as
60
61 BEGIN
62
63 -- The following code checks for check constraints on the Columns.
64
65 IF column_name is NULL THEN
66 NULL;
67 ELSIF UPPER(column_name) = 'ENR_NOTE_TYPE' THEN
68 new_references.enr_note_type := column_value;
69 ELSIF UPPER(column_name) = 'COURSE_CD' THEN
70 new_references.course_cd := column_value;
71 END IF;
72
73 IF ((UPPER (column_name) = 'COURSE_CD') OR (column_name IS NULL)) THEN
74 IF (new_references.course_cd <> UPPER (new_references.course_cd)) THEN
75 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
76 IGS_GE_MSG_STACK.ADD;
77 APP_EXCEPTION.RAISE_EXCEPTION;
78 END IF;
79 END IF;
80
81 IF ((UPPER (column_name) = 'ENR_NOTE_TYPE') OR (column_name IS NULL)) THEN
82 IF (new_references.enr_note_type <> UPPER (new_references.enr_note_type)) THEN
83 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
84 IGS_GE_MSG_STACK.ADD;
85 APP_EXCEPTION.RAISE_EXCEPTION;
86 END IF;
87 END IF;
88
89 END Check_Constraints ;
90
91 PROCEDURE Check_Parent_Existance AS
92 BEGIN
93 IF (((old_references.enr_note_type= new_references.enr_note_type)) OR
94 ((new_references.enr_note_type IS NULL))) THEN
95 NULL;
96 ELSE
97 IF NOT IGS_EN_NOTE_TYPE_PKG.Get_PK_For_Validation (
98 new_references.enr_note_type
99 ) THEN
100
101 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
102 IGS_GE_MSG_STACK.ADD;
103 APP_EXCEPTION.RAISE_EXCEPTION;
104
105 END IF;
106 END IF;
107 IF (((old_references.reference_number = new_references.reference_number)) OR
108 ((new_references.reference_number IS NULL))) THEN
109 NULL;
110 ELSE
111 IF NOT IGS_GE_NOTE_PKG.Get_PK_For_Validation (
112 new_references.reference_number
113 ) THEN
114 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
115 IGS_GE_MSG_STACK.ADD;
116 APP_EXCEPTION.RAISE_EXCEPTION;
117
118 END IF;
119 END IF;
120 IF (((old_references.person_id = new_references.person_id) AND
121 (old_references.course_cd = new_references.course_cd)) OR
122 ((new_references.person_id IS NULL) OR
123 (new_references.course_cd IS NULL))) THEN
124 NULL;
125 ELSE
126 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
127 new_references.person_id,
128 new_references.course_cd
129 )THEN
130 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
131 IGS_GE_MSG_STACK.ADD;
132 APP_EXCEPTION.RAISE_EXCEPTION;
133
134 END IF;
135 END IF;
136 END Check_Parent_Existance;
137 FUNCTION Get_PK_For_Validation (
138 x_person_id IN NUMBER,
139 x_course_cd IN VARCHAR2,
140 x_reference_number IN NUMBER
141 ) RETURN BOOLEAN AS
142 CURSOR cur_rowid IS
143 SELECT rowid
144 FROM IGS_AS_SC_ATMPT_NOTE
145 WHERE person_id = x_person_id
146 AND course_cd = x_course_cd
147 AND reference_number = x_reference_number
148 FOR UPDATE NOWAIT;
149 lv_rowid cur_rowid%RowType;
150 BEGIN
151 Open cur_rowid;
152 Fetch cur_rowid INTO lv_rowid;
153
154 IF (cur_rowid%FOUND) THEN
155 Close cur_rowid;
156 Return(TRUE);
157 ELSE
158 Close cur_rowid;
159 Return(FALSE);
160 END IF;
161
162 END Get_PK_For_Validation;
163
164 PROCEDURE GET_FK_IGS_EN_NOTE_TYPE (
165 x_enr_note_type IN VARCHAR2
166 ) AS
167 CURSOR cur_rowid IS
168 SELECT rowid
169 FROM IGS_AS_SC_ATMPT_NOTE
170 WHERE enr_note_type= x_enr_note_type ;
171 lv_rowid cur_rowid%RowType;
172 BEGIN
173 Open cur_rowid;
174 Fetch cur_rowid INTO lv_rowid;
175 IF (cur_rowid%FOUND) THEN
176 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAN_ENT_FK');
177 IGS_GE_MSG_STACK.ADD;
178 Close cur_rowid;
179 APP_EXCEPTION.RAISE_EXCEPTION;
180
181 Return;
182 END IF;
183 Close cur_rowid;
184 END GET_FK_IGS_EN_NOTE_TYPE;
185 PROCEDURE GET_FK_IGS_GE_NOTE (
186 x_reference_number IN NUMBER
187 ) AS
188 CURSOR cur_rowid IS
189 SELECT rowid
190 FROM IGS_AS_SC_ATMPT_NOTE
191 WHERE reference_number = x_reference_number ;
192 lv_rowid cur_rowid%RowType;
193 BEGIN
194 Open cur_rowid;
195 Fetch cur_rowid INTO lv_rowid;
196 IF (cur_rowid%FOUND) THEN
197 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAN_NOTE_FK');
198 IGS_GE_MSG_STACK.ADD;
199 Close cur_rowid;
200 APP_EXCEPTION.RAISE_EXCEPTION;
201
202 Return;
203 END IF;
204 Close cur_rowid;
205 END GET_FK_IGS_GE_NOTE;
206 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
207 x_person_id IN NUMBER,
208 x_course_cd IN VARCHAR2
209 ) AS
210 CURSOR cur_rowid IS
211 SELECT rowid
212 FROM IGS_AS_SC_ATMPT_NOTE
213 WHERE person_id = x_person_id
214 AND course_cd = x_course_cd ;
215 lv_rowid cur_rowid%RowType;
216 BEGIN
217 Open cur_rowid;
218 Fetch cur_rowid INTO lv_rowid;
219 IF (cur_rowid%FOUND) THEN
220 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAN_SCA_FK');
221 IGS_GE_MSG_STACK.ADD;
222 Close cur_rowid;
223 APP_EXCEPTION.RAISE_EXCEPTION;
224
225 Return;
226 END IF;
227 Close cur_rowid;
228 END GET_FK_IGS_EN_STDNT_PS_ATT;
229 PROCEDURE Before_DML (
230 p_action IN VARCHAR2,
231 x_rowid IN VARCHAR2 DEFAULT NULL,
232 x_person_id IN NUMBER DEFAULT NULL,
233 x_course_cd IN VARCHAR2 DEFAULT NULL,
234 x_reference_number IN NUMBER DEFAULT NULL,
235 x_enr_note_type IN VARCHAR2 DEFAULT NULL,
236 x_creation_date IN DATE DEFAULT NULL,
237 x_created_by IN NUMBER DEFAULT NULL,
238 x_last_update_date IN DATE DEFAULT NULL,
239 x_last_updated_by IN NUMBER DEFAULT NULL,
240 x_last_update_login IN NUMBER DEFAULT NULL
241 ) AS
242 BEGIN
243 Set_Column_Values (
244 p_action,
245 x_rowid,
246 x_person_id,
247 x_course_cd,
248 x_reference_number,
249 x_enr_note_type,
250 x_creation_date,
251 x_created_by,
252 x_last_update_date,
253 x_last_updated_by,
254 x_last_update_login
255 );
256 IF (p_action = 'INSERT') THEN
257 -- Call all the procedures related to Before Insert.
258 IF Get_PK_For_Validation(
259 new_references.person_id,
260 new_references.course_cd,
261 new_references.reference_number
262 ) THEN
263
264 Fnd_message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
265 IGS_GE_MSG_STACK.ADD;
266 APP_EXCEPTION.RAISE_EXCEPTION;
267
268 END IF;
269
270 Check_Constraints;
271 Check_Parent_Existance;
272 ELSIF (p_action = 'UPDATE') THEN
273 -- Call all the procedures related to Before Update.
274
275 Check_Parent_Existance;
276
277 ELSIF (p_action = 'VALIDATE_INSERT') THEN
278 IF Get_PK_For_Validation (
279 new_references.person_id,
280 new_references.course_cd,
281 new_references.reference_number
282 ) THEN
283 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
284 IGS_GE_MSG_STACK.ADD;
285 APP_EXCEPTION.RAISE_EXCEPTION;
286 END IF;
287 Check_Constraints;
288 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
289 Check_Constraints;
290
291 END IF;
292 END Before_DML;
293
294 procedure INSERT_ROW (
295 X_ROWID in out NOCOPY VARCHAR2,
296 X_PERSON_ID in NUMBER,
297 X_COURSE_CD in VARCHAR2,
298 X_REFERENCE_NUMBER in NUMBER,
299 X_ENR_NOTE_TYPE in VARCHAR2,
300 X_MODE in VARCHAR2 default 'R'
301 ) AS
302 cursor C is select ROWID from IGS_AS_SC_ATMPT_NOTE
303 where PERSON_ID = X_PERSON_ID
304 and COURSE_CD = X_COURSE_CD
305 and REFERENCE_NUMBER = X_REFERENCE_NUMBER;
306 X_LAST_UPDATE_DATE DATE;
307 X_LAST_UPDATED_BY NUMBER;
308 X_LAST_UPDATE_LOGIN NUMBER;
309 begin
310 X_LAST_UPDATE_DATE := SYSDATE;
311 if(X_MODE = 'I') then
312 X_LAST_UPDATED_BY := 1;
313 X_LAST_UPDATE_LOGIN := 0;
314 elsif (X_MODE IN ('R', 'S')) then
315 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
316 if X_LAST_UPDATED_BY is NULL then
317 X_LAST_UPDATED_BY := -1;
318 end if;
319 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
320 if X_LAST_UPDATE_LOGIN is NULL then
321 X_LAST_UPDATE_LOGIN := -1;
322 end if;
323 else
324 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
325 IGS_GE_MSG_STACK.ADD;
326 APP_EXCEPTION.RAISE_EXCEPTION;
327 end if;
328 Before_DML(
329 p_action=>'INSERT',
330 x_rowid=>X_ROWID,
331 x_course_cd=>X_COURSE_CD,
332 x_enr_note_type=>X_ENR_NOTE_TYPE,
333 x_person_id=>X_PERSON_ID,
334 x_reference_number=>X_REFERENCE_NUMBER,
335 x_creation_date=>X_LAST_UPDATE_DATE,
336 x_created_by=>X_LAST_UPDATED_BY,
337 x_last_update_date=>X_LAST_UPDATE_DATE,
338 x_last_updated_by=>X_LAST_UPDATED_BY,
339 x_last_update_login=>X_LAST_UPDATE_LOGIN
340 );
341 IF (x_mode = 'S') THEN
342 igs_sc_gen_001.set_ctx('R');
343 END IF;
344 insert into IGS_AS_SC_ATMPT_NOTE (
345 PERSON_ID,
346 COURSE_CD,
347 REFERENCE_NUMBER,
348 ENR_NOTE_TYPE,
349 CREATION_DATE,
350 CREATED_BY,
351 LAST_UPDATE_DATE,
352 LAST_UPDATED_BY,
353 LAST_UPDATE_LOGIN
354 ) values (
355 NEW_REFERENCES.PERSON_ID,
356 NEW_REFERENCES.COURSE_CD,
357 NEW_REFERENCES.REFERENCE_NUMBER,
358 NEW_REFERENCES.ENR_NOTE_TYPE,
359 X_LAST_UPDATE_DATE,
360 X_LAST_UPDATED_BY,
361 X_LAST_UPDATE_DATE,
362 X_LAST_UPDATED_BY,
363 X_LAST_UPDATE_LOGIN
364 );
365 IF (x_mode = 'S') THEN
366 igs_sc_gen_001.unset_ctx('R');
367 END IF;
368
369 open c;
370 fetch c into X_ROWID;
371 if (c%notfound) then
372 close c;
373 raise no_data_found;
374 end if;
375 close c;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
380 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
381 fnd_message.set_token ('ERR_CD', SQLCODE);
382 igs_ge_msg_stack.add;
383 igs_sc_gen_001.unset_ctx('R');
384 app_exception.raise_exception;
385 ELSE
386 igs_sc_gen_001.unset_ctx('R');
387 RAISE;
388 END IF;
389
390 end INSERT_ROW;
391 procedure LOCK_ROW (
392 X_ROWID in VARCHAR2,
393 X_PERSON_ID in NUMBER,
394 X_COURSE_CD in VARCHAR2,
395 X_REFERENCE_NUMBER in NUMBER,
396 X_ENR_NOTE_TYPE in VARCHAR2
397 ) AS
398 cursor c1 is select
399 ENR_NOTE_TYPE
400 from IGS_AS_SC_ATMPT_NOTE
401 where ROWID = X_ROWID for update nowait;
402 tlinfo c1%rowtype;
403 begin
404 open c1;
405 fetch c1 into tlinfo;
406 if (c1%notfound) then
407 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
408 IGS_GE_MSG_STACK.ADD;
409 APP_EXCEPTION.RAISE_EXCEPTION;
410 close c1;
411 return;
412 end if;
413 close c1;
414 if ( (tlinfo.ENR_NOTE_TYPE = X_ENR_NOTE_TYPE)
415 ) then
416 null;
417 else
418 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
419 IGS_GE_MSG_STACK.ADD;
420 APP_EXCEPTION.RAISE_EXCEPTION;
421 end if;
422 return;
423 end LOCK_ROW;
424 procedure UPDATE_ROW (
425 X_ROWID in VARCHAR2,
426 X_PERSON_ID in NUMBER,
427 X_COURSE_CD in VARCHAR2,
428 X_REFERENCE_NUMBER in NUMBER,
429 X_ENR_NOTE_TYPE in VARCHAR2,
430 X_MODE in VARCHAR2 default 'R'
431 ) AS
432 X_LAST_UPDATE_DATE DATE;
433 X_LAST_UPDATED_BY NUMBER;
434 X_LAST_UPDATE_LOGIN NUMBER;
435 begin
436 X_LAST_UPDATE_DATE := SYSDATE;
437 if(X_MODE = 'I') then
438 X_LAST_UPDATED_BY := 1;
439 X_LAST_UPDATE_LOGIN := 0;
440 elsif (X_MODE IN ('R', 'S')) then
441 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
442 if X_LAST_UPDATED_BY is NULL then
443 X_LAST_UPDATED_BY := -1;
444 end if;
445 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
446 if X_LAST_UPDATE_LOGIN is NULL then
447 X_LAST_UPDATE_LOGIN := -1;
448 end if;
449 else
450 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
451 IGS_GE_MSG_STACK.ADD;
452 APP_EXCEPTION.RAISE_EXCEPTION;
453 end if;
454 Before_DML(
455 p_action=>'UPDATE',
456 x_rowid=>X_ROWID,
457 x_course_cd=>X_COURSE_CD,
458 x_enr_note_type=>X_ENR_NOTE_TYPE,
459 x_person_id=>X_PERSON_ID,
460 x_reference_number=>X_REFERENCE_NUMBER,
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 IF (x_mode = 'S') THEN
468 igs_sc_gen_001.set_ctx('R');
469 END IF;
470 update IGS_AS_SC_ATMPT_NOTE set
471 ENR_NOTE_TYPE = NEW_REFERENCES.ENR_NOTE_TYPE,
472 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
473 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
474 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
475 where ROWID = X_ROWID;
476 if (sql%notfound) then
477 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
478 igs_ge_msg_stack.add;
479 igs_sc_gen_001.unset_ctx('R');
480 app_exception.raise_exception;
481 end if;
482 IF (x_mode = 'S') THEN
483 igs_sc_gen_001.unset_ctx('R');
484 END IF;
485
486
487 EXCEPTION
488 WHEN OTHERS THEN
489 IF (SQLCODE = (-28115)) THEN
490 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
491 fnd_message.set_token ('ERR_CD', SQLCODE);
492 igs_ge_msg_stack.add;
493 igs_sc_gen_001.unset_ctx('R');
494 app_exception.raise_exception;
495 ELSE
496 igs_sc_gen_001.unset_ctx('R');
497 RAISE;
498 END IF;
499
500 end UPDATE_ROW;
501 procedure ADD_ROW (
502 X_ROWID in out NOCOPY VARCHAR2,
503 X_PERSON_ID in NUMBER,
504 X_COURSE_CD in VARCHAR2,
505 X_REFERENCE_NUMBER in NUMBER,
506 X_ENR_NOTE_TYPE in VARCHAR2,
507 X_MODE in VARCHAR2 default 'R'
508 ) AS
509 cursor c1 is select rowid from IGS_AS_SC_ATMPT_NOTE
510 where PERSON_ID = X_PERSON_ID
511 and COURSE_CD = X_COURSE_CD
512 and REFERENCE_NUMBER = X_REFERENCE_NUMBER
513 ;
514 begin
515 open c1;
516 fetch c1 into X_ROWID;
517 if (c1%notfound) then
518 close c1;
519 INSERT_ROW (
520 X_ROWID,
521 X_PERSON_ID,
522 X_COURSE_CD,
523 X_REFERENCE_NUMBER,
524 X_ENR_NOTE_TYPE,
525 X_MODE);
526 return;
527 end if;
528 close c1;
529 UPDATE_ROW (
530 X_ROWID,
531 X_PERSON_ID,
532 X_COURSE_CD,
533 X_REFERENCE_NUMBER,
534 X_ENR_NOTE_TYPE,
535 X_MODE);
536 end ADD_ROW;
537 procedure DELETE_ROW (
538 X_ROWID in VARCHAR2,
539 x_mode IN VARCHAR2) AS
540 begin
541 Before_DML(
542 p_action => 'DELETE',
543 x_rowid => X_ROWID
544 );
545 IF (x_mode = 'S') THEN
546 igs_sc_gen_001.set_ctx('R');
547 END IF;
548 delete from IGS_AS_SC_ATMPT_NOTE
549 where ROWID = X_ROWID;
550 if (sql%notfound) then
551 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
552 igs_ge_msg_stack.add;
553 igs_sc_gen_001.unset_ctx('R');
554 app_exception.raise_exception;
555 end if;
556 IF (x_mode = 'S') THEN
557 igs_sc_gen_001.unset_ctx('R');
558 END IF;
559
560
561 end DELETE_ROW;
562 end IGS_AS_SC_ATMPT_NOTE_PKG;