[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_AWARD_PKG
Source
1 package body IGS_PS_AWARD_PKG AS
2 /* $Header: IGSPI06B.pls 115.8 2003/06/16 11:50:07 jbegum ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_AWARD%RowType;
6 new_references IGS_PS_AWARD%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_version_number IN NUMBER DEFAULT NULL,
13 x_award_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_default_ind IN VARCHAR2 DEFAULT NULL,
20 x_closed_ind IN VARCHAR2 DEFAULT NULL
21 ) AS
22
23 CURSOR cur_old_ref_values IS
24 SELECT *
25 FROM IGS_PS_AWARD
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 Close cur_old_ref_values;
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.course_cd := x_course_cd;
47 new_references.version_number := x_version_number;
48 new_references.award_cd := x_award_cd;
49 new_references.default_ind := x_default_ind;
50 new_references.closed_ind := x_closed_ind;
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 -- Trigger description :-
65 -- "OSS_TST".TRG_CAW_BR_IUD
66 -- BEFORE INSERT OR UPDATE ON IGS_PS_AWARD
67 -- REFERENCING
68 -- NEW AS NEW
69 -- OLD AS OLD
70 -- FOR EACH ROW
71
72 PROCEDURE BeforeRowInsertUpdate(
73 p_inserting IN BOOLEAN DEFAULT FALSE,
74 p_updating IN BOOLEAN DEFAULT FALSE
75 ) AS
76 v_message_name varchar2(30);
77 v_course_cd IGS_PS_AWARD.course_cd%TYPE;
78 v_version_number IGS_PS_AWARD.version_number%TYPE;
79 BEGIN
80
81 -- Set variables.
82 v_course_cd := new_references.course_cd;
83 v_version_number := new_references.version_number;
84
85 -- Validate the insert/update/delete.
86 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl (
87 v_course_cd,
88 v_version_number,
89 v_message_name) = FALSE THEN
90 Fnd_Message.Set_Name('IGS',v_message_name);
91 IGS_GE_MSG_STACK.ADD;
92 App_Exception.Raise_Exception;
93 END IF;
94 -- Validate the insert.
95 IF p_inserting THEN
96 IF IGS_PS_VAL_CAW.crsp_val_caw_insert (
97 new_references.course_cd,
98 new_references.version_number,
99 v_message_name) = FALSE THEN
100 Fnd_Message.Set_Name('IGS',v_message_name);
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103 END IF;
104 -- Validate IGS_PS_AWD code. IGS_PS_AWD code is not updateable.
105 IF IGS_PS_VAL_CAW.crsp_val_caw_award (
106 new_references.award_cd,
107 v_message_name) = FALSE THEN
108 Fnd_Message.Set_Name('IGS',v_message_name);
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END IF;
112 END IF;
113
114 END BeforeRowInsertUpdate;
115
116 PROCEDURE Check_Constraints (
117 Column_Name IN VARCHAR2 DEFAULT NULL,
118 Column_Value IN VARCHAR2 DEFAULT NULL
119 )
120 AS
121 BEGIN
122
123 IF column_name is null then
124 NULL;
125 ELSIF upper(Column_name) = 'AWARD_CD' then
126 new_references.award_cd := column_value;
127 ELSIF upper(Column_name) = 'COURSE_CD' then
128 new_references.course_cd := column_value;
129 ELSIF upper(Column_name) = 'CLOSED_IND' then
130 new_references.course_cd := column_value;
131 END IF;
132
133 IF upper(column_name) = 'AWARD_CD' OR
134 column_name is null Then
135 IF ( new_references.award_cd <> UPPER(new_references.award_cd) ) Then
136 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 END IF;
140 END IF;
141
142
143 IF upper(column_name) = 'COURSE_CD' OR
144 column_name is null Then
145 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
146 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 END IF;
151
152 IF upper(column_name) = 'CLOSED_IND' OR column_name is null Then
153 IF new_references.closed_ind NOT IN ('Y', 'N') Then
154 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
155 igs_ge_msg_stack.add;
156 app_exception.raise_exception;
157 END IF;
158 END IF;
159
160 END Check_Constraints;
161
162 PROCEDURE Check_Parent_Existance AS
163 BEGIN
164
165 IF (((old_references.award_cd = new_references.award_cd)) OR
166 ((new_references.award_cd IS NULL))) THEN
167 NULL;
168 ELSE
169 IF NOT IGS_PS_AWD_PKG.Get_PK_For_Validation (
170 new_references.award_cd
171 ) THEN
172 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177
178 IF (((old_references.course_cd = new_references.course_cd) AND
179 (old_references.version_number = new_references.version_number)) OR
180 ((new_references.course_cd IS NULL) OR
181 (new_references.version_number IS NULL))) THEN
182 NULL;
183 ELSE
184 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
185 new_references.course_cd,
186 new_references.version_number
187 ) THEN
188 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
189 IGS_GE_MSG_STACK.ADD;
190 App_Exception.Raise_Exception;
191 END IF;
192 END IF;
193
194 END Check_Parent_Existance;
195
196 FUNCTION Get_PK_For_Validation (
197 x_course_cd IN VARCHAR2,
198 x_version_number IN NUMBER,
199 x_award_cd IN VARCHAR2
200 )
201 RETURN BOOLEAN AS
202
203 CURSOR cur_rowid IS
204 SELECT rowid
205 FROM IGS_PS_AWARD
206 WHERE course_cd = x_course_cd
207 AND version_number = x_version_number
208 AND award_cd = x_award_cd;
209
210 lv_rowid cur_rowid%RowType;
211
212 BEGIN
213
214 Open cur_rowid;
215 Fetch cur_rowid INTO lv_rowid;
216 IF (cur_rowid%FOUND) THEN
217 Close cur_rowid;
218 Return (TRUE);
219 ELSE
220 Close cur_rowid;
221 Return (FALSE);
222 END IF;
223
224 END Get_PK_For_Validation;
225
226
227 PROCEDURE GET_FK_IGS_PS_VER (
228 x_course_cd IN VARCHAR2,
229 x_version_number IN NUMBER
230 ) AS
231
232 CURSOR cur_rowid IS
233 SELECT rowid
234 FROM IGS_PS_AWARD
235 WHERE course_cd = x_course_cd
236 AND version_number = x_version_number ;
237
238 lv_rowid cur_rowid%RowType;
239
240 BEGIN
241
242 Open cur_rowid;
243 Fetch cur_rowid INTO lv_rowid;
244 IF (cur_rowid%FOUND) THEN
245 Close cur_rowid;
246 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CAW_CRV_FK');
247 IGS_GE_MSG_STACK.ADD;
248 App_Exception.Raise_Exception;
249 Return;
250 END IF;
251 Close cur_rowid;
252
253 END GET_FK_IGS_PS_VER;
254
255 PROCEDURE Before_DML (
256 p_action IN VARCHAR2,
257 x_rowid IN VARCHAR2 DEFAULT NULL,
258 x_course_cd IN VARCHAR2 DEFAULT NULL,
259 x_version_number IN NUMBER DEFAULT NULL,
260 x_award_cd IN VARCHAR2 DEFAULT NULL,
261 x_creation_date IN DATE DEFAULT NULL,
262 x_created_by IN NUMBER DEFAULT NULL,
263 x_last_update_date IN DATE DEFAULT NULL,
264 x_last_updated_by IN NUMBER DEFAULT NULL,
265 x_last_update_login IN NUMBER DEFAULT NULL,
266 x_default_ind IN VARCHAR2 DEFAULT NULL,
267 x_closed_ind IN VARCHAR2
268 ) AS
269 BEGIN
270
271 Set_Column_Values (
272 p_action,
273 x_rowid,
274 x_course_cd,
275 x_version_number,
276 x_award_cd,
277 x_creation_date,
278 x_created_by,
279 x_last_update_date,
280 x_last_updated_by,
281 x_last_update_login,
282 x_default_ind,
283 x_closed_ind
284 );
285
286 IF (p_action = 'INSERT') THEN
287 -- Call all the procedures related to Before Insert.
288 BeforeRowInsertUpdate( p_inserting => TRUE );
289 IF Get_PK_For_Validation (
290 new_references.course_cd ,
291 new_references.version_number,
292 new_references.award_cd ) THEN
293 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
294 IGS_GE_MSG_STACK.ADD;
295 App_Exception.Raise_Exception;
296 END IF;
297 Check_Constraints;
298 Check_Parent_Existance;
299 ELSIF (p_action = 'UPDATE') THEN
300 -- Call all the procedures related to Before Update.
301 BeforeRowInsertUpdate( p_updating => TRUE );
302 Check_Constraints;
303 Check_Parent_Existance;
304 ELSIF (p_action = 'VALIDATE_INSERT') THEN
305 IF Get_PK_For_Validation (
306 new_references.course_cd ,
307 new_references.version_number,
308 new_references.award_cd ) THEN
309 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception;
312 END IF;
313 Check_Constraints;
314 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
315 Check_Constraints;
316 END IF;
317
318 END Before_DML;
319
320 PROCEDURE After_DML (
321 p_action IN VARCHAR2,
322 x_rowid IN VARCHAR2
323 ) AS
324 BEGIN
325 l_rowid := x_rowid;
326 END After_DML;
327
328 procedure INSERT_ROW (
329 X_ROWID in out NOCOPY VARCHAR2,
330 X_COURSE_CD in VARCHAR2,
331 X_AWARD_CD in VARCHAR2,
332 X_VERSION_NUMBER in NUMBER,
333 X_MODE in VARCHAR2 default 'R',
334 X_DEFAULT_IND in VARCHAR2 default 'Y',
335 x_closed_ind IN VARCHAR2
336 ) AS
337 cursor C is select ROWID from IGS_PS_AWARD
338 where COURSE_CD = X_COURSE_CD
339 and AWARD_CD = X_AWARD_CD
340 and VERSION_NUMBER = X_VERSION_NUMBER;
341 X_LAST_UPDATE_DATE DATE;
342 X_LAST_UPDATED_BY NUMBER;
343 X_LAST_UPDATE_LOGIN NUMBER;
344 begin
345 X_LAST_UPDATE_DATE := SYSDATE;
346 if(X_MODE = 'I') then
347 X_LAST_UPDATED_BY := 1;
348 X_LAST_UPDATE_LOGIN := 0;
349 elsif (X_MODE = 'R') then
350 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
351 if X_LAST_UPDATED_BY is NULL then
352 X_LAST_UPDATED_BY := -1;
353 end if;
354 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
355 if X_LAST_UPDATE_LOGIN is NULL then
356 X_LAST_UPDATE_LOGIN := -1;
357 end if;
358 else
359 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
360 IGS_GE_MSG_STACK.ADD;
361 app_exception.raise_exception;
362 end if;
363
364 Before_DML (
365 p_action => 'INSERT',
366 x_rowid => X_ROWID,
367 x_course_cd => X_COURSE_CD,
368 x_version_number => X_VERSION_NUMBER,
369 x_award_cd => X_AWARD_CD,
370 x_creation_date => X_LAST_UPDATE_DATE ,
371 x_created_by => X_LAST_UPDATED_BY ,
372 x_last_update_date => X_LAST_UPDATE_DATE ,
373 x_last_updated_by => X_LAST_UPDATED_BY ,
374 x_last_update_login => X_LAST_UPDATE_LOGIN,
375 x_default_ind => X_DEFAULT_IND,
376 x_closed_ind => X_CLOSED_IND
377 );
378
379 insert into IGS_PS_AWARD (
380 COURSE_CD,
381 VERSION_NUMBER,
382 AWARD_CD,
383 CREATION_DATE,
384 CREATED_BY,
385 LAST_UPDATE_DATE,
386 LAST_UPDATED_BY,
387 LAST_UPDATE_LOGIN,
388 DEFAULT_IND,
389 CLOSED_IND
390 ) values (
391 NEW_REFERENCES.COURSE_CD,
392 NEW_REFERENCES.VERSION_NUMBER,
393 NEW_REFERENCES.AWARD_CD,
394 X_LAST_UPDATE_DATE,
395 X_LAST_UPDATED_BY,
396 X_LAST_UPDATE_DATE,
397 X_LAST_UPDATED_BY,
398 X_LAST_UPDATE_LOGIN,
399 NEW_REFERENCES.DEFAULT_IND,
400 NEW_REFERENCES.CLOSED_IND
401 );
402
403 open c;
404 fetch c into X_ROWID;
405 if (c%notfound) then
406 close c;
407 raise no_data_found;
408 end if;
409 close c;
410
411 After_DML (
412 p_action => 'INSERT',
413 x_rowid => X_ROWID
414 );
415
416 end INSERT_ROW;
417
418 procedure LOCK_ROW (
419 X_ROWID IN VARCHAR2,
420 X_COURSE_CD in VARCHAR2,
421 X_AWARD_CD in VARCHAR2,
422 X_VERSION_NUMBER in NUMBER,
423 X_DEFAULT_IND in VARCHAR2,
424 x_closed_ind IN VARCHAR2
425 ) AS
426 cursor c1 is select
427 DEFAULT_IND,
428 CLOSED_IND
429 from IGS_PS_AWARD
430 where ROWID = X_ROWID
431 for update nowait;
432 tlinfo c1%rowtype;
433
434 begin
435 open c1;
436 fetch c1 into tlinfo;
437 if (c1%notfound) then
438 close c1;
439 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
440 IGS_GE_MSG_STACK.ADD;
441 app_exception.raise_exception;
442 return;
443 end if;
444 close c1;
445
446 if (
447 ((tlinfo.DEFAULT_IND = X_DEFAULT_IND) OR ((tlinfo.DEFAULT_IND is null) AND (X_DEFAULT_IND is null)))
448 AND ((tlinfo.CLOSED_IND = X_CLOSED_IND) OR ((tlinfo.CLOSED_IND is null) AND (X_CLOSED_IND is null)))
449 ) then
450 null;
451 else
452 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
453 IGS_GE_MSG_STACK.ADD;
454 app_exception.raise_exception;
455 end if;
456 return;
457 end LOCK_ROW;
458
459 procedure UPDATE_ROW (
460 X_ROWID IN VARCHAR2,
461 X_COURSE_CD in VARCHAR2,
462 X_AWARD_CD in VARCHAR2,
463 X_VERSION_NUMBER in NUMBER,
464 X_MODE in VARCHAR2 default 'R',
465 X_DEFAULT_IND in VARCHAR2 default 'Y',
466 X_CLOSED_IND IN VARCHAR2
467 ) AS
468 X_LAST_UPDATE_DATE DATE;
469 X_LAST_UPDATED_BY NUMBER;
470 X_LAST_UPDATE_LOGIN NUMBER;
471 begin
472 X_LAST_UPDATE_DATE := SYSDATE;
473 if(X_MODE = 'I') then
474 X_LAST_UPDATED_BY := 1;
475 X_LAST_UPDATE_LOGIN := 0;
476 elsif (X_MODE = 'R') then
477 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
478 if X_LAST_UPDATED_BY is NULL then
479 X_LAST_UPDATED_BY := -1;
480 end if;
481 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
482 if X_LAST_UPDATE_LOGIN is NULL then
483 X_LAST_UPDATE_LOGIN := -1;
484 end if;
485 else
486 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
487 IGS_GE_MSG_STACK.ADD;
488 app_exception.raise_exception;
489 end if;
490
491 Before_DML (
492 p_action => 'UPDATE',
493 x_rowid => X_ROWID,
494 x_course_cd => X_COURSE_CD,
495 x_version_number => X_VERSION_NUMBER,
496 x_award_cd => X_AWARD_CD,
497 x_creation_date => X_LAST_UPDATE_DATE ,
498 x_created_by => X_LAST_UPDATED_BY ,
499 x_last_update_date => X_LAST_UPDATE_DATE ,
500 x_last_updated_by => X_LAST_UPDATED_BY ,
501 x_last_update_login => X_LAST_UPDATE_LOGIN,
502 x_default_ind => X_DEFAULT_IND,
503 x_closed_ind => X_CLOSED_IND
504 );
505
506 UPDATE IGS_PS_AWARD SET
507 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
508 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
509 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
510 DEFAULT_IND = NEW_REFERENCES.DEFAULT_IND,
511 CLOSED_IND = NEW_REFERENCES.CLOSED_IND
512 where ROWID = X_ROWID
513 ;
514 if (sql%notfound) then
515 raise no_data_found;
516 end if;
517
518 After_DML (
519 p_action => 'UPDATE',
520 x_rowid => X_ROWID
521 );
522
523 end UPDATE_ROW;
524
525 procedure ADD_ROW (
526 X_ROWID in out NOCOPY VARCHAR2,
527 X_COURSE_CD in VARCHAR2,
528 X_AWARD_CD in VARCHAR2,
529 X_VERSION_NUMBER in NUMBER,
530 X_MODE in VARCHAR2 default 'R',
531 X_DEFAULT_IND in VARCHAR2 default 'Y',
532 x_closed_ind IN VARCHAR2
533 ) AS
534 cursor c1 is select rowid from IGS_PS_AWARD
535 where COURSE_CD = X_COURSE_CD
536 and AWARD_CD = X_AWARD_CD
537 and VERSION_NUMBER = X_VERSION_NUMBER
538 ;
539 begin
540 open c1;
541 fetch c1 into X_ROWID;
542 if (c1%notfound) then
543 close c1;
544 INSERT_ROW (
545 X_ROWID,
546 X_COURSE_CD,
547 X_AWARD_CD,
548 X_VERSION_NUMBER,
549 X_MODE,
550 X_DEFAULT_IND,
551 X_CLOSED_IND);
552 return;
553 end if;
554 close c1;
555 UPDATE_ROW (
556 X_ROWID,
557 X_COURSE_CD,
558 X_AWARD_CD,
559 X_VERSION_NUMBER,
560 X_MODE,
561 X_DEFAULT_IND,
562 X_CLOSED_IND);
563 end ADD_ROW;
564
565 end IGS_PS_AWARD_PKG;