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