1 package body IGS_PR_OU_PS_PKG as
2 /* $Header: IGSQI05B.pls 115.7 2003/02/25 09:11:31 anilk ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PR_OU_PS_ALL%RowType;
5 new_references IGS_PR_OU_PS_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
11 x_pra_sequence_number IN NUMBER DEFAULT NULL,
12 x_pro_sequence_number IN NUMBER DEFAULT NULL,
13 x_course_cd 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 x_org_id IN NUMBER DEFAULT NULL
20 ) AS
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_PR_OU_PS_ALL
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 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.progression_rule_cat := x_progression_rule_cat;
46 new_references.pra_sequence_number := x_pra_sequence_number;
47 new_references.pro_sequence_number := x_pro_sequence_number;
48 new_references.course_cd := x_course_cd;
49 new_references.org_id := x_org_id;
50
51 IF (p_action = 'UPDATE') THEN
52 new_references.creation_date := old_references.creation_date;
53 new_references.created_by := old_references.created_by;
54 ELSE
55 new_references.creation_date := x_creation_date;
56 new_references.created_by := x_created_by;
57 END IF;
58 new_references.last_update_date := x_last_update_date;
59 new_references.last_updated_by := x_last_updated_by;
60 new_references.last_update_login := x_last_update_login;
61
62 END Set_Column_Values;
63
64
65 PROCEDURE Check_Parent_Existance AS
66 BEGIN
67
68 IF (((old_references.course_cd = new_references.course_cd)) OR
69 ((new_references.course_cd IS NULL))) THEN
70 NULL;
71 ELSE
72 IF NOT IGS_PS_COURSE_PKG.Get_PK_For_Validation (
73 new_references.course_cd
74 ) THEN
75 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
76 IGS_GE_MSG_STACK.ADD;
77 App_Exception.Raise_Exception;
78 END IF;
79 END IF;
80
81 IF (((old_references.progression_rule_cat = new_references.progression_rule_cat) AND
82 (old_references.pra_sequence_number = new_references.pra_sequence_number) AND
83 (old_references.pro_sequence_number = new_references.pro_sequence_number)) OR
84 ((new_references.progression_rule_cat IS NULL) OR
85 (new_references.pra_sequence_number IS NULL) OR
86 (new_references.pro_sequence_number IS NULL))) THEN
87 NULL;
88 ELSE
89 IF NOT IGS_PR_RU_OU_PKG.Get_PK_For_Validation (
90 new_references.progression_rule_cat,
91 new_references.pra_sequence_number,
92 new_references.pro_sequence_number
93 ) THEN
94 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
95 IGS_GE_MSG_STACK.ADD;
96 App_Exception.Raise_Exception;
97 END IF;
98 END IF;
99
100 END Check_Parent_Existance;
101
102 FUNCTION Get_PK_For_Validation (
103 x_progression_rule_cat IN VARCHAR2,
104 x_pra_sequence_number IN NUMBER,
105 x_pro_sequence_number IN NUMBER,
106 x_course_cd IN VARCHAR2
107 ) RETURN BOOLEAN AS
108
109 CURSOR cur_rowid IS
110 SELECT rowid
111 FROM IGS_PR_OU_PS_ALL
112 WHERE progression_rule_cat = x_progression_rule_cat
113 AND pra_sequence_number = x_pra_sequence_number
114 AND pro_sequence_number = x_pro_sequence_number
115 AND course_cd = x_course_cd
116 FOR UPDATE NOWAIT;
117
118 lv_rowid cur_rowid%RowType;
119 BEGIN
120 Open cur_rowid;
121 Fetch cur_rowid INTO lv_rowid;
122 IF (cur_rowid%FOUND) THEN
123 Close Cur_rowid;
124 Return(TRUE);
125 ELSE
126 Close cur_rowid;
127 Return(FALSE);
128 END IF;
129
130 END Get_PK_For_Validation;
131
132 PROCEDURE GET_FK_IGS_PS_COURSE (
133 x_course_cd IN VARCHAR2
134 ) AS
135
136 CURSOR cur_rowid IS
137 SELECT rowid
138 FROM IGS_PR_OU_PS_ALL
139 WHERE course_cd = x_course_cd ;
140
141 lv_rowid cur_rowid%RowType;
142
143 BEGIN
144
145 Open cur_rowid;
146 Fetch cur_rowid INTO lv_rowid;
147 IF (cur_rowid%FOUND) THEN
148 Fnd_Message.Set_Name ('IGS', 'IGS_PR_POC_CRS_FK');
149 IGS_GE_MSG_STACK.ADD;
150 Close cur_rowid;
151 App_Exception.Raise_Exception;
152 Return;
153 END IF;
154 Close cur_rowid;
155
156 END GET_FK_IGS_PS_COURSE;
157
158 PROCEDURE GET_FK_IGS_PR_RU_OU (
159 x_progression_rule_cat IN VARCHAR2,
160 x_pra_sequence_number IN NUMBER,
161 x_sequence_number IN NUMBER
162 ) AS
163
164 CURSOR cur_rowid IS
165 SELECT rowid
166 FROM IGS_PR_OU_PS_ALL
167 WHERE progression_rule_cat = x_progression_rule_cat
168 AND pra_sequence_number = x_pra_sequence_number
169 AND pro_sequence_number = x_sequence_number ;
170
171 lv_rowid cur_rowid%RowType;
172
173 BEGIN
174
175 Open cur_rowid;
176 Fetch cur_rowid INTO lv_rowid;
177 IF (cur_rowid%FOUND) THEN
178 Fnd_Message.Set_Name ('IGS','IGS_PR_POC_PRO_FK');
179 IGS_GE_MSG_STACK.ADD;
180 Close cur_rowid;
181 App_Exception.Raise_Exception;
182 Return;
183 END IF;
184 Close cur_rowid;
185
186 END GET_FK_IGS_PR_RU_OU;
187
188 PROCEDURE BeforeInsertUpdate( p_action VARCHAR2 ) AS
189 /*
190 || Created By : anilk
191 || Created On : 25-FEB-2003
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 */
197 CURSOR c_parent (
198 cp_progression_rule_cat IGS_PR_RU_OU.progression_rule_cat%TYPE,
199 cp_pra_sequence_number IGS_PR_RU_OU.pra_sequence_number%TYPE,
200 cp_sequence_number IGS_PR_RU_OU.sequence_number%TYPE ) IS
201 SELECT 1
202 FROM IGS_PR_RU_OU pro
203 WHERE pro.progression_rule_cat = cp_progression_rule_cat AND
204 pro.pra_sequence_number = cp_pra_sequence_number AND
205 pro.sequence_number = cp_sequence_number AND
206 pro.logical_delete_dt is NULL;
207
208 l_dummy NUMBER;
209
210 BEGIN
211
212 IF (p_action = 'INSERT') THEN
213 OPEN c_parent( new_references.progression_rule_cat, new_references.pra_sequence_number, new_references.pro_sequence_number );
214 FETCH c_parent INTO l_dummy;
215 IF c_parent%NOTFOUND THEN
216 CLOSE c_parent;
217 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
218 IGS_GE_MSG_STACK.ADD;
219 APP_EXCEPTION.RAISE_EXCEPTION;
220 END IF;
221 CLOSE c_parent;
222 ELSIF(p_action = 'UPDATE') THEN
223 IF new_references.progression_rule_cat <> old_references.progression_rule_cat OR
224 new_references.pra_sequence_number <> old_references.pra_sequence_number OR
225 new_references.pro_sequence_number <> old_references.pro_sequence_number THEN
226 OPEN c_parent( new_references.progression_rule_cat, new_references.pra_sequence_number, new_references.pro_sequence_number );
227 FETCH c_parent INTO l_dummy;
228 IF c_parent%NOTFOUND THEN
229 CLOSE c_parent;
230 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
231 IGS_GE_MSG_STACK.ADD;
232 APP_EXCEPTION.RAISE_EXCEPTION;
233 END IF;
234 CLOSE c_parent;
235 END IF;
236 END IF;
237
238 END BeforeInsertUpdate;
239
240 PROCEDURE Before_DML (
241 p_action IN VARCHAR2,
242 x_rowid IN VARCHAR2 DEFAULT NULL,
243 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
244 x_pra_sequence_number IN NUMBER DEFAULT NULL,
245 x_pro_sequence_number IN NUMBER DEFAULT NULL,
246 x_course_cd IN VARCHAR2 DEFAULT NULL,
247 x_creation_date IN DATE DEFAULT NULL,
248 x_created_by IN NUMBER DEFAULT NULL,
249 x_last_update_date IN DATE DEFAULT NULL,
250 x_last_updated_by IN NUMBER DEFAULT NULL,
251 x_last_update_login IN NUMBER DEFAULT NULL,
252 x_org_id IN NUMBER DEFAULT NULL
253 ) AS
254 BEGIN
255
256 Set_Column_Values (
257 p_action,
258 x_rowid,
259 x_progression_rule_cat,
260 x_pra_sequence_number,
261 x_pro_sequence_number,
262 x_course_cd,
263 x_creation_date,
264 x_created_by,
265 x_last_update_date,
266 x_last_updated_by,
267 x_last_update_login ,
268 x_org_id
269 );
270
271 IF (p_action = 'INSERT') THEN
272 -- Call all the procedures related to Before Insert.
273 IF Get_PK_For_Validation (
274 new_references.progression_rule_cat,
275 new_references.pra_sequence_number,
276 new_references.pro_sequence_number,
277 new_references.course_cd
278 ) THEN
279 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
280 IGS_GE_MSG_STACK.ADD;
281 App_Exception.Raise_Exception;
282 END IF;
283 Check_Constraints;
284 Check_Parent_Existance;
285 ELSIF (p_action = 'UPDATE') THEN
286 -- Call all the procedures related to Before Update.
287 Check_Constraints;
288 Check_Parent_Existance;
289 ELSIF (p_action = 'VALIDATE_INSERT') THEN
290 -- Call all the procedures related to Before Insert.
291 IF Get_PK_For_Validation (
292 new_references.progression_rule_cat,
293 new_references.pra_sequence_number,
294 new_references.pro_sequence_number,
295 new_references.course_cd
296 ) THEN
297 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
298 IGS_GE_MSG_STACK.ADD;
299 App_Exception.Raise_Exception;
300 END IF;
301 Check_Constraints;
302 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
303 -- Call all the procedures related to Before Update.
304 Check_Constraints;
305 END IF;
306
307 -- anilk, bug#2784198
308 BeforeInsertUpdate(p_action);
309
310 END Before_DML;
311
312 procedure INSERT_ROW (
313 X_ROWID in out NOCOPY VARCHAR2,
314 X_PROGRESSION_RULE_CAT in VARCHAR2,
315 X_PRA_SEQUENCE_NUMBER in NUMBER,
316 X_PRO_SEQUENCE_NUMBER in NUMBER,
317 X_COURSE_CD in VARCHAR2,
318 X_MODE in VARCHAR2 default 'R',
319 X_ORG_ID in NUMBER
320 ) as
321 cursor C is select ROWID from IGS_PR_OU_PS_ALL
322 where PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT
323 and PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER
324 and PRO_SEQUENCE_NUMBER = X_PRO_SEQUENCE_NUMBER
325 and COURSE_CD = X_COURSE_CD;
326 X_LAST_UPDATE_DATE DATE;
327 X_LAST_UPDATED_BY NUMBER;
328 X_LAST_UPDATE_LOGIN NUMBER;
329 begin
330 X_LAST_UPDATE_DATE := SYSDATE;
331 if(X_MODE = 'I') then
332 X_LAST_UPDATED_BY := 1;
333 X_LAST_UPDATE_LOGIN := 0;
334 elsif (X_MODE = 'R') then
335 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
336 if X_LAST_UPDATED_BY is NULL then
337 X_LAST_UPDATED_BY := -1;
338 end if;
339 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
340 if X_LAST_UPDATE_LOGIN is NULL then
341 X_LAST_UPDATE_LOGIN := -1;
342 end if;
343 else
344 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
345 IGS_GE_MSG_STACK.ADD;
346 app_exception.raise_exception;
347 end if;
348
349 Before_DML (
350 p_action => 'INSERT',
351 x_rowid => x_rowid,
352 x_progression_rule_cat => x_progression_rule_cat,
353 x_pra_sequence_number => x_pra_sequence_number,
354 x_pro_sequence_number => x_pro_sequence_number,
355 x_course_cd => x_course_cd,
356 x_creation_date => x_last_update_date,
357 x_created_by => x_last_updated_by,
358 x_last_update_date => x_last_update_date,
359 x_last_updated_by => x_last_updated_by,
360 x_last_update_login => x_last_update_login,
361 x_org_id => igs_ge_gen_003.get_org_id
362 );
363
364 insert into IGS_PR_OU_PS_ALL (
365 PROGRESSION_RULE_CAT,
366 PRA_SEQUENCE_NUMBER,
367 PRO_SEQUENCE_NUMBER,
368 COURSE_CD,
369 CREATION_DATE,
370 CREATED_BY,
371 LAST_UPDATE_DATE,
372 LAST_UPDATED_BY,
373 LAST_UPDATE_LOGIN,
374 ORG_ID
375 ) values (
376 NEW_REFERENCES.PROGRESSION_RULE_CAT,
377 NEW_REFERENCES.PRA_SEQUENCE_NUMBER,
378 NEW_REFERENCES.PRO_SEQUENCE_NUMBER,
379 NEW_REFERENCES.COURSE_CD,
380 X_LAST_UPDATE_DATE,
381 X_LAST_UPDATED_BY,
382 X_LAST_UPDATE_DATE,
383 X_LAST_UPDATED_BY,
384 X_LAST_UPDATE_LOGIN,
385 NEW_REFERENCES.ORG_ID
386 );
387
388 open c;
389 fetch c into X_ROWID;
390 if (c%notfound) then
391 close c;
392 raise no_data_found;
393 end if;
394 close c;
395 end INSERT_ROW;
396
397 procedure LOCK_ROW (
398 X_ROWID in VARCHAR2,
399 X_PROGRESSION_RULE_CAT in VARCHAR2,
400 X_PRA_SEQUENCE_NUMBER in NUMBER,
401 X_PRO_SEQUENCE_NUMBER in NUMBER,
402 X_COURSE_CD in VARCHAR2
403 ) as
404 cursor c1 is select
405 rowid
406 from IGS_PR_OU_PS_ALL
407 Where ROWID = X_ROWID for update nowait;
408 tlinfo c1%rowtype;
409
410 begin
411 open c1;
412 fetch c1 into tlinfo;
413 if (c1%notfound) then
414 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
415 IGS_GE_MSG_STACK.ADD;
416 close c1;
417 app_exception.raise_exception;
418 return;
419 end if;
420 close c1;
421 return;
422 end LOCK_ROW;
423
424 procedure DELETE_ROW (
425 X_ROWID in VARCHAR2
426 ) as
427 begin
428 Before_DML (
429 p_action => 'DELETE',
430 x_rowid => X_ROWID
431 );
432
433 delete from IGS_PR_OU_PS_ALL
434 where ROWID = X_ROWID;
435 if (sql%notfound) then
436 raise no_data_found;
437 end if;
438
439
440 end DELETE_ROW;
441
442 PROCEDURE Check_Constraints (
443 Column_Name IN VARCHAR2 DEFAULT NULL,
444 Column_Value IN VARCHAR2 DEFAULT NULL
445 ) AS
446
447 BEGIN
448 IF Column_Name is null THEN
449 NULL;
450 ELSIF upper(Column_name) = 'COURSE_CD' THEN
451 new_references.COURSE_CD:= COLUMN_VALUE ;
452
453 ELSIF upper(Column_name) = 'PROGRESSION_RULE_CAT' THEN
454 new_references.PROGRESSION_RULE_CAT:= COLUMN_VALUE ;
455
456 ELSIF upper(Column_name) = 'PRO_SEQUENCE_NUMBER' THEN
457 new_references.PRO_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
458
459 ELSIF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' THEN
460 new_references.PRA_SEQUENCE_NUMBER:= IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
461
462 END IF ;
463
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 IF upper(Column_name) = 'PROGRESSION_RULE_CAT' OR COLUMN_NAME IS NULL THEN
474 IF new_references.PROGRESSION_RULE_CAT<> upper(new_references.PROGRESSION_RULE_CAT) 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) = 'PRO_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
483 IF new_references.PRO_SEQUENCE_NUMBER < 1 or new_references.PRO_SEQUENCE_NUMBER > 999999 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 IF upper(Column_name) = 'PRA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
492 IF new_references.PRA_SEQUENCE_NUMBER < 1 or new_references.PRA_SEQUENCE_NUMBER > 999999 then
493 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
494 IGS_GE_MSG_STACK.ADD;
495 App_Exception.Raise_Exception ;
496 END IF;
497
498 END IF ;
499
500
501
502 END Check_Constraints;
503
504 end IGS_PR_OU_PS_PKG;
[Home] [Help]