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