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_AD_ADM_UT_STA_GD_PKG

Source


1 package body IGS_AD_ADM_UT_STA_GD_PKG AS
2 /* $Header: IGSAI02B.pls 115.6 2003/10/30 13:18:35 rghosh ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_AD_ADM_UT_STA_GD%RowType;
5   new_references IGS_AD_ADM_UT_STA_GD%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10     x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
11     x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
12     x_version_number IN NUMBER DEFAULT NULL,
13     x_grade 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_AD_ADM_UT_STA_GD
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.administrative_unit_status := x_administrative_unit_status;
45     new_references.grading_schema_cd := x_grading_schema_cd;
46     new_references.version_number := x_version_number;
47     new_references.grade := x_grade;
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 BeforeRowInsertUpdate1(
62     p_inserting IN BOOLEAN DEFAULT FALSE,
63     p_updating IN BOOLEAN DEFAULT FALSE,
64     p_deleting IN BOOLEAN DEFAULT FALSE
65     ) IS
66 	v_message_name		VARCHAR2(30);
67   BEGIN
68 	-- Validate that inserts/updates are allowed
69 	IF  p_inserting OR p_updating THEN
70 	    IF	IGS_EN_VAL_UDDC.ENRP_VAL_AUS_CLOSED(new_references.administrative_unit_status
71 						,v_message_name) = FALSE THEN
72 		    Fnd_Message.Set_Name('IGS',v_message_name);
73 		    IGS_GE_MSG_STACK.ADD;
74 		    App_Exception.Raise_Exception;
75 	    END IF;
76 	    IF	IGS_EN_VAL_UDDC.ENRP_VAL_AUS_DISCONT(new_references.administrative_unit_status
77 						,v_message_name) = FALSE THEN
78 		    Fnd_Message.Set_Name('IGS',v_message_name);
79 		    IGS_GE_MSG_STACK.ADD;
80 		    App_Exception.Raise_Exception;
81 	    END IF;
82 	    IF	IGS_EN_VAL_AUSG.ENRP_VAL_AUSG_GS(new_references.grading_schema_cd
83 						,new_references.version_number
84 						,v_message_name) = FALSE THEN
85 		    Fnd_Message.Set_Name('IGS',v_message_name);
86 		    IGS_GE_MSG_STACK.ADD;
87 		    App_Exception.Raise_Exception;
88 	    END IF;
89 	END IF;
90 
91   END BeforeRowInsertUpdate1;
92 
93 
94   PROCEDURE Check_Parent_Existance AS
95   BEGIN
96 
97     IF (((old_references.administrative_unit_status = new_references.administrative_unit_status)) OR
98         ((new_references.administrative_unit_status IS NULL))) THEN
99       NULL;
100     ELSE
101       IF NOT IGS_AD_ADM_UNIT_STAT_PKG.Get_PK_For_Validation (new_references.administrative_unit_status,'N')  THEN
102         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
103         IGS_GE_MSG_STACK.ADD;
104         App_Exception.Raise_Exception;
105 	END IF;
106     END IF;
107 
108     IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
109          (old_references.version_number = new_references.version_number) AND
110          (old_references.grade = new_references.grade)) OR
111         ((new_references.grading_schema_cd IS NULL) OR
112          (new_references.version_number IS NULL) OR
113          (new_references.grade IS NULL))) THEN
114       NULL;
115     ELSE
116       IF NOT IGS_AS_GRD_SCH_GRADE_PKG.Get_PK_For_Validation (
117         new_references.grading_schema_cd,
118         new_references.version_number,
119         new_references.grade ) THEN
120         Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
121         IGS_GE_MSG_STACK.ADD;
122         App_Exception.Raise_Exception;
123       END IF;
124     END IF;
125 
126   END Check_Parent_Existance;
127 
128   PROCEDURE Check_Constraints (
129 			Column_Name IN VARCHAR2 DEFAULT NULL,
130 			Column_Value IN VARCHAR2 DEFAULT NULL
131 			) AS
132 
133   BEGIN
134       IF Column_Name IS NULL THEN
135 	   NULL;
136 	ELSIF upper(Column_Name) = 'VERSION_NUMBER' THEN
137 	   new_references.version_number := igs_ge_number.to_num(column_value) ;
138       ELSIF upper(Column_Name) = 'ADMINISTRATIVE_UNIT_STATUS' THEN
139 	   new_references.administrative_unit_status := column_value ;
140       ELSIF upper(Column_Name) = 'GRADE' THEN
141 	   new_references.grade := column_value ;
142       ELSIF upper(Column_Name) = 'GRADING_SCHEMA_CD' THEN
143 	   new_references.grading_schema_cd := column_value ;
144   	END IF;
145 
146 	IF upper(Column_Name) = 'VERSION_NUMBER' OR
147 	   Column_name IS NULL THEN
148          IF new_references.version_number < 0 OR
149             new_references.version_number > 999 THEN
150             Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
151             IGS_GE_MSG_STACK.ADD;
152            App_Exception.Raise_Exception;
153 	   END IF;
154 	END IF;
155       IF upper(Column_Name) = 'ADMINISTRATIVE_UNIT_STATUS' OR
156 	   Column_name IS NULL THEN
157          IF new_references.administrative_unit_status <> upper(new_references.administrative_unit_status) THEN
158             Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
159             IGS_GE_MSG_STACK.ADD;
160             App_Exception.Raise_Exception;
161 	   END IF;
162       END IF;
163       IF upper(Column_Name) = 'GRADE' OR
164 	   Column_name IS NULL THEN
165          IF new_references.grade <> upper(new_references.grade) THEN
166             Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
167             IGS_GE_MSG_STACK.ADD;
168             App_Exception.Raise_Exception;
169 	   END IF;
170       END IF;
171       IF upper(Column_Name) = 'GRADING_SCHEMA_CD' OR
172 	   Column_name IS NULL THEN
173          IF new_references.grading_schema_cd <> upper(new_references.grading_schema_cd ) THEN
174             Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
175             IGS_GE_MSG_STACK.ADD;
176             App_Exception.Raise_Exception;
177 	   END IF;
178       END IF;
179 
180   END Check_Constraints;
181 
182   FUNCTION Get_PK_For_Validation ( x_administrative_unit_status IN VARCHAR2,
183 					     x_grading_schema_cd IN VARCHAR2,
184 					     x_version_number IN NUMBER,
185 					     x_grade IN VARCHAR2) RETURN BOOLEAN AS
186 
187     CURSOR cur_rowid IS
188       SELECT   rowid
189       FROM     IGS_AD_ADM_UT_STA_GD
190       WHERE    administrative_unit_status = x_administrative_unit_status
191       AND      grading_schema_cd = x_grading_schema_cd
192       AND      version_number = x_version_number
193       AND      grade = x_grade
194       FOR UPDATE NOWAIT;
195 
196     lv_rowid cur_rowid%RowType;
197 
198   BEGIN
199 
200     Open cur_rowid;
201     Fetch cur_rowid INTO lv_rowid;
202     IF (cur_rowid%FOUND) THEN
203       Close cur_rowid;
204       Return (True) ;
205     ELSE
206       Close cur_rowid;
207       Return (False) ;
208     END IF;
209 
210   END Get_PK_For_Validation;
211 
212 
213   PROCEDURE GET_FK_IGS_AS_GRD_SCH_GRADE (
214     x_grading_schema_cd IN VARCHAR2,
215     x_version_number IN NUMBER,
216     x_grade IN VARCHAR2
217     ) AS
218 
219     CURSOR cur_rowid IS
220       SELECT   rowid
221       FROM     IGS_AD_ADM_UT_STA_GD
222       WHERE    grading_schema_cd = x_grading_schema_cd
223       AND      version_number = x_version_number
224       AND      grade = x_grade ;
225 
226     lv_rowid cur_rowid%RowType;
227 
228   BEGIN
229 
230     Open cur_rowid;
231     Fetch cur_rowid INTO lv_rowid;
232     IF (cur_rowid%FOUND) THEN
233       Close cur_rowid;
234       Fnd_Message.Set_Name ('IGS', 'IGS_AD_AUSG_GSG_FK');
235       IGS_GE_MSG_STACK.ADD;
236       App_Exception.Raise_Exception;
237       Return;
238     END IF;
239     Close cur_rowid;
240 
241   END GET_FK_IGS_AS_GRD_SCH_GRADE;
242 
243   PROCEDURE Before_DML (
244     p_action IN VARCHAR2,
245     x_rowid IN VARCHAR2 DEFAULT NULL,
246     x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
247     x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
248     x_version_number IN NUMBER DEFAULT NULL,
249     x_grade IN VARCHAR2 DEFAULT NULL,
250     x_creation_date IN DATE DEFAULT NULL,
251     x_created_by IN NUMBER DEFAULT NULL,
252     x_last_update_date IN DATE DEFAULT NULL,
253     x_last_updated_by IN NUMBER DEFAULT NULL,
254     x_last_update_login IN NUMBER DEFAULT NULL
255   ) AS
256   BEGIN
257 
258     Set_Column_Values (
259       p_action,
260       x_rowid,
261       x_administrative_unit_status,
262       x_grading_schema_cd,
263       x_version_number,
264       x_grade,
265       x_creation_date,
266       x_created_by,
267       x_last_update_date,
268       x_last_updated_by,
269       x_last_update_login
270     );
271 
272     IF (p_action = 'INSERT') THEN
273       -- Call all the procedures related to Before Insert.
274       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
275       IF Get_PK_For_Validation (
276          new_references.administrative_unit_status ,
277          new_references.grading_schema_cd ,
278          new_references.version_number ,
279          new_references.grade ) THEN
280 
281          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
282          IGS_GE_MSG_STACK.ADD;
283          App_Exception.Raise_Exception;
284       END IF;
285       Check_Constraints;
286       Check_Parent_Existance;
287 
288     ELSIF (p_action = 'UPDATE') THEN
289       -- Call all the procedures related to Before Update.
290       BeforeRowInsertUpdate1 ( p_updating => TRUE );
291       Check_Constraints;
292       Check_Parent_Existance;
293 
294     ELSIF (p_action = 'DELETE') THEN
295       -- Call all the procedures related to Before Delete.
296       Null;
297     ELSIF (p_action = 'VALIDATE_INSERT') THEN
298       IF Get_PK_For_Validation (
299          new_references.administrative_unit_status ,
300          new_references.grading_schema_cd ,
301          new_references.version_number ,
302          new_references.grade ) THEN
303 
304          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
305          IGS_GE_MSG_STACK.ADD;
306          App_Exception.Raise_Exception;
307       END IF;
308       Check_Constraints;
309     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
310       Check_Constraints;
311     END IF;
312 
313   END Before_DML;
314 
315   PROCEDURE After_DML (
316     p_action IN VARCHAR2,
317     x_rowid IN VARCHAR2
318   ) AS
319   BEGIN
320 
321     l_rowid := x_rowid;
322 
323   END After_DML;
324 
325 procedure INSERT_ROW (
326   X_ROWID in out NOCOPY VARCHAR2,
327   X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
328   X_GRADING_SCHEMA_CD in VARCHAR2,
329   X_VERSION_NUMBER in NUMBER,
330   X_GRADE in VARCHAR2,
331   X_MODE in VARCHAR2 default 'R'
332   ) AS
333     cursor C is select ROWID from IGS_AD_ADM_UT_STA_GD
334       where ADMINISTRATIVE_UNIT_STATUS = X_ADMINISTRATIVE_UNIT_STATUS
335       and GRADING_SCHEMA_CD = X_GRADING_SCHEMA_CD
336       and VERSION_NUMBER = X_VERSION_NUMBER
337       and GRADE = X_GRADE;
338     X_LAST_UPDATE_DATE DATE;
339     X_LAST_UPDATED_BY NUMBER;
340     X_LAST_UPDATE_LOGIN NUMBER;
341 begin
342   X_LAST_UPDATE_DATE := SYSDATE;
343   if(X_MODE = 'I') then
344     X_LAST_UPDATED_BY := 1;
345     X_LAST_UPDATE_LOGIN := 0;
346   elsif (X_MODE = 'R') then
347     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
348     if X_LAST_UPDATED_BY is NULL then
349       X_LAST_UPDATED_BY := -1;
350     end if;
351     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
352     if X_LAST_UPDATE_LOGIN is NULL then
353       X_LAST_UPDATE_LOGIN := -1;
354     end if;
355   else
356     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
357     IGS_GE_MSG_STACK.ADD;
358     app_exception.raise_exception;
359   end if;
360 
361 Before_DML (
362     p_action => 'INSERT',
363     x_rowid => X_ROWID,
364     x_administrative_unit_status => X_ADMINISTRATIVE_UNIT_STATUS,
365     x_grading_schema_cd => X_GRADING_SCHEMA_CD,
366     x_version_number => X_VERSION_NUMBER,
367     x_grade => X_GRADE,
368     x_creation_date => X_LAST_UPDATE_DATE,
369     x_created_by => X_LAST_UPDATED_BY,
370     x_last_update_date => X_LAST_UPDATE_DATE,
371     x_last_updated_by => X_LAST_UPDATED_BY,
372     x_last_update_login => X_LAST_UPDATE_LOGIN
373   );
374   insert into IGS_AD_ADM_UT_STA_GD (
375     ADMINISTRATIVE_UNIT_STATUS,
376     GRADING_SCHEMA_CD,
377     VERSION_NUMBER,
378     GRADE,
379     CREATION_DATE,
380     CREATED_BY,
381     LAST_UPDATE_DATE,
382     LAST_UPDATED_BY,
383     LAST_UPDATE_LOGIN
384   ) values (
385     X_ADMINISTRATIVE_UNIT_STATUS,
386     X_GRADING_SCHEMA_CD,
387     X_VERSION_NUMBER,
388     X_GRADE,
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_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
413   X_GRADING_SCHEMA_CD in VARCHAR2,
414   X_VERSION_NUMBER in NUMBER,
415   X_GRADE in VARCHAR2
416 ) AS
417   cursor c1 is select ROWID
418     from IGS_AD_ADM_UT_STA_GD
419     where ROWID = X_ROWID
420     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   return;
436 end LOCK_ROW;
437 
438 procedure DELETE_ROW (
439   X_ROWID in VARCHAR2
440 ) AS
441 begin
442 Before_DML (
443     p_action => 'DELETE',
444     x_rowid => X_ROWID
445   );
446 
447   delete from IGS_AD_ADM_UT_STA_GD
448   where ROWID = X_ROWID ;
449   if (sql%notfound) then
450     raise no_data_found;
451   end if;
452 After_DML (
453     p_action => 'DELETE',
454     x_rowid => X_ROWID
455   );
456 end DELETE_ROW;
457 
458 end IGS_AD_ADM_UT_STA_GD_PKG;