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