eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_PS_VER_NOTE_PKG

Source


1 package body IGS_PS_VER_NOTE_PKG AS
2  /* $Header: IGSPI44B.pls 115.3 2002/11/29 02:28:33 nsidana ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_PS_VER_NOTE%RowType;
6   new_references IGS_PS_VER_NOTE%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_reference_number IN NUMBER DEFAULT NULL,
14     x_crs_note_type 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_PS_VER_NOTE
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        Close cur_old_ref_values;
37       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38       IGS_GE_MSG_STACK.ADD;
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.reference_number := x_reference_number;
48     new_references.crs_note_type := x_crs_note_type;
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   PROCEDURE Check_Constraints (
63 	Column_Name IN VARCHAR2 DEFAULT NULL,
64 	Column_Value IN VARCHAR2 DEFAULT NULL
65   ) IS
66   BEGIN
67 	IF column_name is null THEN
68 	   NULL;
69 	ELSIF upper(column_name) = 'COURSE_CD' THEN
70 	   new_references.course_cd := column_value;
71 	ELSIF upper(column_name) = 'CRS_NOTE_TYPE' THEN
72 	   new_references.crs_note_type := column_value;
73 	END IF;
74 	IF upper(column_name)= 'COURSE_CD' OR
75 		column_name is null THEN
76 		IF new_references.course_cd <> UPPER(new_references.course_cd)
77 		THEN
78             	Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
79             	IGS_GE_MSG_STACK.ADD;
80             	App_Exception.Raise_Exception;
81 		END IF;
82 	END IF;
83 	IF upper(column_name)= 'CRS_NOTE_TYPE' OR
84 		column_name is null THEN
85 		IF new_references.crs_note_type <> UPPER(new_references.crs_note_type)
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 
95   PROCEDURE Check_Parent_Existance AS
96   BEGIN
97 
98     IF (((old_references.crs_note_type = new_references.crs_note_type)) OR
99         ((new_references.crs_note_type IS NULL))) THEN
100       NULL;
101     ELSE
102       IF NOT IGS_PS_NOTE_TYPE_PKG.Get_PK_For_Validation (
103         new_references.crs_note_type
104       )THEN
105 	Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
106 	IGS_GE_MSG_STACK.ADD;
107       App_Exception.Raise_Exception;
108       END IF;
109     END IF;
110 
111     IF (((old_references.course_cd = new_references.course_cd) AND
112          (old_references.version_number = new_references.version_number)) OR
113         ((new_references.course_cd IS NULL) OR
114          (new_references.version_number IS NULL))) THEN
115       NULL;
116     ELSE
117       IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
118         new_references.course_cd,
119         new_references.version_number
120       )THEN
121 	Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
122 	IGS_GE_MSG_STACK.ADD;
123       App_Exception.Raise_Exception;
124       END IF;
125 
126     END IF;
127 
128     IF (((old_references.reference_number = new_references.reference_number)) OR
129         ((new_references.reference_number IS NULL))) THEN
130       NULL;
131     ELSE
132       IF NOT IGS_GE_NOTE_PKG.Get_PK_For_Validation (
133         new_references.reference_number
134       )THEN
135 	Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
136 	IGS_GE_MSG_STACK.ADD;
137       App_Exception.Raise_Exception;
138       END IF;
139 
140     END IF;
141 
142   END Check_Parent_Existance;
143 
144   FUNCTION Get_PK_For_Validation (
145     x_course_cd IN VARCHAR2,
146     x_version_number IN NUMBER,
147     x_reference_number IN NUMBER
148     ) RETURN BOOLEAN AS
149 
150     CURSOR cur_rowid IS
151       SELECT   rowid
152       FROM     IGS_PS_VER_NOTE
153       WHERE    course_cd = x_course_cd
154       AND      version_number = x_version_number
155       AND      reference_number = x_reference_number
156       FOR UPDATE NOWAIT;
157 
158     lv_rowid cur_rowid%RowType;
159 
160   BEGIN
161 
162     Open cur_rowid;
163 	IF (cur_rowid%FOUND) THEN
164 		Close cur_rowid;
165 		Return(TRUE);
166 	ELSE
167 		Close cur_rowid;
168 		Return(FALSE);
169 	END IF;
170   END Get_PK_For_Validation;
171 
172   PROCEDURE GET_FK_IGS_PS_NOTE_TYPE (
173     x_crs_note_type IN VARCHAR2
174     ) AS
175 
176     CURSOR cur_rowid IS
177       SELECT   rowid
178       FROM     IGS_PS_VER_NOTE
179       WHERE    crs_note_type = x_crs_note_type ;
180 
181     lv_rowid cur_rowid%RowType;
182 
183   BEGIN
184 
185     Open cur_rowid;
186     Fetch cur_rowid INTO lv_rowid;
187     IF (cur_rowid%FOUND) THEN
188       Close cur_rowid;
189       Fnd_Message.Set_Name ('IGS', 'IGS_PS_CVN_CNT_FK');
190       IGS_GE_MSG_STACK.ADD;
191       App_Exception.Raise_Exception;
192       Return;
193     END IF;
194     Close cur_rowid;
195 
196   END GET_FK_IGS_PS_NOTE_TYPE;
197 
198   PROCEDURE GET_FK_IGS_PS_VER (
199     x_course_cd IN VARCHAR2,
200     x_version_number IN NUMBER
201     ) AS
202 
203     CURSOR cur_rowid IS
204       SELECT   rowid
205       FROM     IGS_PS_VER_NOTE
206       WHERE    course_cd = x_course_cd
207       AND      version_number = x_version_number ;
208 
209     lv_rowid cur_rowid%RowType;
210 
211   BEGIN
212 
213     Open cur_rowid;
214     Fetch cur_rowid INTO lv_rowid;
215     IF (cur_rowid%FOUND) THEN
216       Close cur_rowid;
217       Fnd_Message.Set_Name ('IGS', 'IGS_PS_CVN_CRV_FK');
218       IGS_GE_MSG_STACK.ADD;
219       App_Exception.Raise_Exception;
220       Return;
221     END IF;
222     Close cur_rowid;
223 
224   END GET_FK_IGS_PS_VER;
225 
226   PROCEDURE GET_FK_IGS_GE_NOTE (
227     x_reference_number IN NUMBER
228     ) AS
229 
230     CURSOR cur_rowid IS
231       SELECT   rowid
232       FROM     IGS_PS_VER_NOTE
233       WHERE    reference_number = x_reference_number ;
234 
235     lv_rowid cur_rowid%RowType;
236 
237   BEGIN
238 
239     Open cur_rowid;
240     Fetch cur_rowid INTO lv_rowid;
241     IF (cur_rowid%FOUND) THEN
242       Close cur_rowid;
243       Fnd_Message.Set_Name ('IGS', 'IGS_PS_CVN_NOTE_FK');
244       IGS_GE_MSG_STACK.ADD;
245       App_Exception.Raise_Exception;
246       Return;
247     END IF;
248     Close cur_rowid;
249 
250   END GET_FK_IGS_GE_NOTE;
251 
252   PROCEDURE Before_DML (
253     p_action IN VARCHAR2,
254     x_rowid IN VARCHAR2 DEFAULT NULL,
255     x_course_cd IN VARCHAR2 DEFAULT NULL,
256     x_version_number IN NUMBER DEFAULT NULL,
257     x_reference_number IN NUMBER DEFAULT NULL,
258     x_crs_note_type IN VARCHAR2 DEFAULT NULL,
259     x_creation_date IN DATE DEFAULT NULL,
260     x_created_by IN NUMBER DEFAULT NULL,
261     x_last_update_date IN DATE DEFAULT NULL,
262     x_last_updated_by IN NUMBER DEFAULT NULL,
263     x_last_update_login IN NUMBER DEFAULT NULL
264   ) AS
265   BEGIN
266 
267     Set_Column_Values (
268       p_action,
269       x_rowid,
270       x_course_cd,
271       x_version_number,
272       x_reference_number,
273       x_crs_note_type,
274       x_creation_date,
275       x_created_by,
276       x_last_update_date,
277       x_last_updated_by,
278       x_last_update_login
279     );
280 
281     IF (p_action = 'INSERT') THEN
282       -- Call all the procedures related to Before Insert.
283 
284      	IF Get_PK_For_Validation(
285     		new_references.course_cd ,
286     		new_references.version_number,
287 		new_references.reference_number
288    	) THEN
289 	Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
290 	IGS_GE_MSG_STACK.ADD;
291       App_Exception.Raise_Exception;
292 	END IF;
293 	Check_Constraints;
294       Check_Parent_Existance;
295     ELSIF (p_action = 'UPDATE') THEN
296       -- Call all the procedures related to Before Update.
297 
298 	Check_Constraints;
299       Check_Parent_Existance;
300 
301     ELSIF (p_action = 'VALIDATE_INSERT') THEN
302 	 IF Get_PK_For_Validation(
303     		new_references.course_cd ,
304     		new_references.version_number,
305 		new_references.reference_number
306    	) THEN
307 	Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
308 	IGS_GE_MSG_STACK.ADD;
309       App_Exception.Raise_Exception;
310 	END IF;
311      	Check_Constraints;
312     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
313      	Check_Constraints;
314 
315      END IF;
316   END Before_DML;
317 
318   PROCEDURE After_DML (
319     p_action IN VARCHAR2,
320     x_rowid IN VARCHAR2
321   ) AS
322   BEGIN
323 
324     l_rowid := x_rowid;
325 
326 
327   END After_DML;
328 
329 procedure INSERT_ROW (
330   X_ROWID in out NOCOPY VARCHAR2,
331   X_COURSE_CD in VARCHAR2,
332   X_VERSION_NUMBER in NUMBER,
333   X_REFERENCE_NUMBER in NUMBER,
334   X_CRS_NOTE_TYPE in VARCHAR2,
335   X_MODE in VARCHAR2 default 'R'
336   ) AS
337     cursor C is select ROWID from IGS_PS_VER_NOTE
338       where COURSE_CD = X_COURSE_CD
339       and VERSION_NUMBER = X_VERSION_NUMBER
340       and REFERENCE_NUMBER = X_REFERENCE_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( p_action => 'INSERT',
365     x_rowid => X_ROWID,
366     x_course_cd => X_COURSE_CD,
367     x_version_number => X_VERSION_NUMBER,
368     x_reference_number => X_REFERENCE_NUMBER,
369     x_crs_note_type => X_CRS_NOTE_TYPE,
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   );
376   insert into IGS_PS_VER_NOTE (
377     COURSE_CD,
378     VERSION_NUMBER,
379     REFERENCE_NUMBER,
380     CRS_NOTE_TYPE,
381     CREATION_DATE,
382     CREATED_BY,
383     LAST_UPDATE_DATE,
384     LAST_UPDATED_BY,
385     LAST_UPDATE_LOGIN
386   ) values (
387     NEW_REFERENCES.COURSE_CD,
388     NEW_REFERENCES.VERSION_NUMBER,
389     NEW_REFERENCES.REFERENCE_NUMBER,
390     NEW_REFERENCES.CRS_NOTE_TYPE,
391     X_LAST_UPDATE_DATE,
392     X_LAST_UPDATED_BY,
393     X_LAST_UPDATE_DATE,
394     X_LAST_UPDATED_BY,
395     X_LAST_UPDATE_LOGIN
396   );
397 
398   open c;
399   fetch c into X_ROWID;
400   if (c%notfound) then
401     close c;
402     raise no_data_found;
403   end if;
404   close c;
405  After_DML(
406   p_action => 'INSERT',
407   x_rowid =>  X_ROWID
408   );
409 
410 end INSERT_ROW;
411 
412 procedure LOCK_ROW (
413   X_ROWID in VARCHAR2,
414   X_COURSE_CD in VARCHAR2,
415   X_VERSION_NUMBER in NUMBER,
416   X_REFERENCE_NUMBER in NUMBER,
417   X_CRS_NOTE_TYPE in VARCHAR2
418 ) AS
419   cursor c1 is select
420       CRS_NOTE_TYPE
421     from IGS_PS_VER_NOTE
422     where ROWID = X_ROWID for update nowait;
423   tlinfo c1%rowtype;
424 
425 begin
426   open c1;
427   fetch c1 into tlinfo;
428   if (c1%notfound) then
429     close c1;
430     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
431       IGS_GE_MSG_STACK.ADD;
432     app_exception.raise_exception;
433     return;
434   end if;
435   close c1;
436 
437   if ( (tlinfo.CRS_NOTE_TYPE = X_CRS_NOTE_TYPE)
438   ) then
439     null;
440   else
441     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
442       IGS_GE_MSG_STACK.ADD;
443     app_exception.raise_exception;
444   end if;
445   return;
446 end LOCK_ROW;
447 
448 procedure UPDATE_ROW (
449   X_ROWID in VARCHAR2,
450   X_COURSE_CD in VARCHAR2,
451   X_VERSION_NUMBER in NUMBER,
452   X_REFERENCE_NUMBER in NUMBER,
453   X_CRS_NOTE_TYPE in VARCHAR2,
454   X_MODE in VARCHAR2 default 'R'
455   ) AS
456     X_LAST_UPDATE_DATE DATE;
457     X_LAST_UPDATED_BY NUMBER;
458     X_LAST_UPDATE_LOGIN NUMBER;
459 begin
460   X_LAST_UPDATE_DATE := SYSDATE;
461   if(X_MODE = 'I') then
462     X_LAST_UPDATED_BY := 1;
463     X_LAST_UPDATE_LOGIN := 0;
464   elsif (X_MODE = 'R') then
465     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
466     if X_LAST_UPDATED_BY is NULL then
467       X_LAST_UPDATED_BY := -1;
468     end if;
469     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
470     if X_LAST_UPDATE_LOGIN is NULL then
471       X_LAST_UPDATE_LOGIN := -1;
472     end if;
473   else
474     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
475     IGS_GE_MSG_STACK.ADD;
476     app_exception.raise_exception;
477   end if;
478 
479  Before_DML( p_action => 'UPDATE',
480     x_rowid => X_ROWID,
481     x_course_cd => X_COURSE_CD,
482     x_version_number => X_VERSION_NUMBER,
483     x_reference_number => X_REFERENCE_NUMBER,
484     x_crs_note_type => X_CRS_NOTE_TYPE,
485     x_creation_date => X_LAST_UPDATE_DATE,
486     x_created_by => X_LAST_UPDATED_BY,
487     x_last_update_date => X_LAST_UPDATE_DATE,
488     x_last_updated_by => X_LAST_UPDATED_BY,
489     x_last_update_login => X_LAST_UPDATE_LOGIN
490   );
491   update IGS_PS_VER_NOTE set
492     CRS_NOTE_TYPE = NEW_REFERENCES.CRS_NOTE_TYPE,
493     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
494     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
495     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
496   where ROWID = X_ROWID
497   ;
498   if (sql%notfound) then
499     raise no_data_found;
500   end if;
501  After_DML(
502   p_action => 'UPDATE',
503   x_rowid => X_ROWID
504   );
505 end UPDATE_ROW;
506 
507 procedure ADD_ROW (
508   X_ROWID in out NOCOPY VARCHAR2,
509   X_COURSE_CD in VARCHAR2,
510   X_VERSION_NUMBER in NUMBER,
511   X_REFERENCE_NUMBER in NUMBER,
512   X_CRS_NOTE_TYPE in VARCHAR2,
513   X_MODE in VARCHAR2 default 'R'
514   ) AS
515   cursor c1 is select rowid from IGS_PS_VER_NOTE
516      where COURSE_CD = X_COURSE_CD
517      and VERSION_NUMBER = X_VERSION_NUMBER
518      and REFERENCE_NUMBER = X_REFERENCE_NUMBER
519   ;
520 begin
521   open c1;
522   fetch c1 into X_ROWID;
523   if (c1%notfound) then
524     close c1;
525     INSERT_ROW (
526      X_ROWID,
527      X_COURSE_CD,
528      X_VERSION_NUMBER,
529      X_REFERENCE_NUMBER,
530      X_CRS_NOTE_TYPE,
531      X_MODE);
532     return;
533   end if;
534   close c1;
535   UPDATE_ROW (
536    X_ROWID,
537    X_COURSE_CD,
538    X_VERSION_NUMBER,
539    X_REFERENCE_NUMBER,
540    X_CRS_NOTE_TYPE,
541    X_MODE);
542 end ADD_ROW;
543 
544 procedure DELETE_ROW (
545 X_ROWID in VARCHAR2
546 ) AS
547 begin
548  Before_DML( p_action => 'DELETE',
549     x_rowid => X_ROWID
550   );
551   delete from IGS_PS_VER_NOTE
552   where ROWID = X_ROWID;
553   if (sql%notfound) then
554     raise no_data_found;
555   end if;
556  After_DML(
557   p_action => 'DELETE',
558   x_rowid => X_ROWID
559   );
560 
561 end DELETE_ROW;
562 
563 end IGS_PS_VER_NOTE_PKG;