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_OFR_NOTE_PKG

Source


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