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