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_UNIT_VER_NOTE_PKG

Source


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