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_GR_STAT_PKG

Source


1 package body IGS_GR_STAT_PKG as
2 /* $Header: IGSGI17B.pls 115.6 2003/05/19 04:45:39 ijeddy ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_GR_STAT%RowType;
5   new_references IGS_GR_STAT%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10     x_graduand_status IN VARCHAR2 DEFAULT NULL,
11     x_description IN VARCHAR2 DEFAULT NULL,
12     x_s_graduand_status IN VARCHAR2 DEFAULT NULL,
13     x_closed_ind 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_GR_STAT
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       App_Exception.Raise_Exception;
38       Return;
39     END IF;
40     Close cur_old_ref_values;
41 
42     -- Populate New Values.
43     new_references.graduand_status := x_graduand_status;
44     new_references.description := x_description;
45     new_references.s_graduand_status := x_s_graduand_status;
46     new_references.closed_ind := x_closed_ind;
47     IF (p_action = 'UPDATE') THEN
48       new_references.creation_date := old_references.creation_date;
49       new_references.created_by := old_references.created_by;
50     ELSE
51       new_references.creation_date := x_creation_date;
52       new_references.created_by := x_created_by;
53     END IF;
54     new_references.last_update_date := x_last_update_date;
55     new_references.last_updated_by := x_last_updated_by;
56     new_references.last_update_login := x_last_update_login;
57 
58   END Set_Column_Values;
59 
60   PROCEDURE Check_Child_Existance AS
61   BEGIN
62 
63     IGS_GR_GRADUAND_PKG.GET_FK_IGS_GR_STAT (
64       old_references.graduand_status
65       );
66 
67   END Check_Child_Existance;
68 
69   FUNCTION Get_PK_For_Validation (
70     x_graduand_status IN VARCHAR2
71     ) RETURN BOOLEAN AS
72 
73     CURSOR cur_rowid IS
74       SELECT   rowid
75       FROM     IGS_GR_STAT
76       WHERE    graduand_status = x_graduand_status ;
77 
78     lv_rowid cur_rowid%RowType;
79 
80   BEGIN
81 
82     Open cur_rowid;
83     Fetch cur_rowid INTO lv_rowid;
84     	IF (cur_rowid%FOUND) THEN
85 		Close cur_rowid;
86 		Return (TRUE);
87 	ELSE
88 		Close cur_rowid;
89 		Return (FALSE);
90 	END IF;
91 
92   END Get_PK_For_Validation;
93 
94   PROCEDURE CHECK_CONSTRAINTS(
95 	Column_Name IN VARCHAR2 DEFAULT NULL,
96 	Column_Value IN VARCHAR2 DEFAULT NULL
97 	) AS
98   BEGIN
99 IF Column_Name is null THEN
100   NULL;
101 ELSIF upper(Column_name) = 'CLOSED_IND' THEN
102   new_references.CLOSED_IND:= COLUMN_VALUE ;
103 
104 ELSIF upper(Column_name) = 'GRADUAND_STATUS' THEN
105   new_references.GRADUAND_STATUS:= COLUMN_VALUE ;
106 
107 ELSIF upper(Column_name) = 'S_GRADUAND_STATUS' THEN
108   new_references.S_GRADUAND_STATUS:= COLUMN_VALUE ;
109 
110 END IF ;
111 
112 IF upper(Column_name) = 'CLOSED_IND' OR COLUMN_NAME IS NULL THEN
113   IF new_references.CLOSED_IND not in  ('Y','N') then
114     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
115     App_Exception.Raise_Exception ;
116   END IF;
117 END IF ;
118 
119 IF upper(Column_name) = 'GRADUAND_STATUS' OR COLUMN_NAME IS NULL THEN
120   IF new_references.GRADUAND_STATUS<> upper(new_references.GRADUAND_STATUS) then
121     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
122     App_Exception.Raise_Exception ;
123   END IF;
124 
125 END IF ;
126 
127 IF upper(Column_name) = 'S_GRADUAND_STATUS' OR COLUMN_NAME IS NULL THEN
128   IF new_references.S_GRADUAND_STATUS<> upper(new_references.S_GRADUAND_STATUS) then
129     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
130     App_Exception.Raise_Exception ;
131   END IF;
132 
133   IF new_references.S_GRADUAND_STATUS not in  ( 'POTENTIAL' , 'ELIGIBLE' , 'SURRENDER' , 'GRADUATED' ) then
134     Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
135     App_Exception.Raise_Exception ;
136   END IF;
137 
138 END IF ;
139   END CHECK_CONSTRAINTS;
140 
141   PROCEDURE Before_DML (
142     p_action IN VARCHAR2,
143     x_rowid IN VARCHAR2 DEFAULT NULL,
144     x_graduand_status IN VARCHAR2 DEFAULT NULL,
145     x_description IN VARCHAR2 DEFAULT NULL,
146     x_s_graduand_status IN VARCHAR2 DEFAULT NULL,
147     x_closed_ind IN VARCHAR2 DEFAULT NULL,
148     x_creation_date IN DATE DEFAULT NULL,
149     x_created_by IN NUMBER DEFAULT NULL,
150     x_last_update_date IN DATE DEFAULT NULL,
151     x_last_updated_by IN NUMBER DEFAULT NULL,
152     x_last_update_login IN NUMBER DEFAULT NULL
153   ) AS
154   BEGIN
155 
156     Set_Column_Values (
157       p_action,
158       x_rowid,
159       x_graduand_status,
160       x_description,
161       x_s_graduand_status,
162       x_closed_ind,
163       x_creation_date,
164       x_created_by,
165       x_last_update_date,
166       x_last_updated_by,
167       x_last_update_login
168     );
169 
170     IF (p_action = 'INSERT') THEN
171       -- Call all the procedures related to Before Insert.
172 	IF GET_PK_FOR_VALIDATION(NEW_REFERENCES.graduand_status) THEN
173 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
174 		App_Exception.Raise_Exception;
175 	END IF;
176 
177 	check_constraints;
178     ELSIF (p_action = 'UPDATE') THEN
179       -- Call all the procedures related to Before Update.
180 
181 	check_constraints;
182     ELSIF (p_action = 'DELETE') THEN
183       -- Call all the procedures related to Before Delete.
184       Check_Child_Existance;
185     ELSIF (p_action = 'VALIDATE_INSERT') THEN
186 	IF GET_PK_FOR_VALIDATION(NEW_REFERENCES.graduand_status) THEN
187 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
188 		App_Exception.Raise_Exception;
189 	END IF;
190 
191 	check_constraints;
192     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
193 
194 	check_constraints;
195     ELSIF (p_action = 'VALIDATE_DELETE') THEN
196 	check_child_existance;
197     END IF;
198 
199 /*
200 The (L_ROWID := null) was added by ijeddy on the 12-apr-2003 as
201 part of the bug fix for bug no 2868726, (Uniqueness Check at Item Level)
202 */
203 L_ROWID := null;
204 
205   END Before_DML;
206 
207 procedure INSERT_ROW (
208   X_ROWID in out NOCOPY VARCHAR2,
209   X_GRADUAND_STATUS in VARCHAR2,
210   X_DESCRIPTION in VARCHAR2,
211   X_S_GRADUAND_STATUS in VARCHAR2,
212   X_CLOSED_IND in VARCHAR2,
213   X_MODE in VARCHAR2 default 'R'
214   ) AS
215     cursor C is select ROWID from IGS_GR_STAT
216       where GRADUAND_STATUS = X_GRADUAND_STATUS;
217     X_LAST_UPDATE_DATE DATE;
218     X_LAST_UPDATED_BY NUMBER;
219     X_LAST_UPDATE_LOGIN NUMBER;
220 begin
221   X_LAST_UPDATE_DATE := SYSDATE;
222   if(X_MODE = 'I') then
223     X_LAST_UPDATED_BY := 1;
224     X_LAST_UPDATE_LOGIN := 0;
225   elsif (X_MODE = 'R') then
226     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
227     if X_LAST_UPDATED_BY is NULL then
228       X_LAST_UPDATED_BY := -1;
229     end if;
230     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
231     if X_LAST_UPDATE_LOGIN is NULL then
232       X_LAST_UPDATE_LOGIN := -1;
233     end if;
234   else
235     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
236     app_exception.raise_exception;
237   end if;
238 
239  Before_DML (
240      p_action => 'INSERT',
241      x_rowid => X_ROWID,
242     x_graduand_status => X_GRADUAND_STATUS,
243     x_description => X_DESCRIPTION,
244     x_s_graduand_status => NVL(X_S_GRADUAND_STATUS, 'POTENTIAL'),
245     x_closed_ind => NVL(X_CLOSED_IND, 'N'),
246     x_creation_date => X_LAST_UPDATE_DATE,
247      x_created_by => X_LAST_UPDATED_BY,
248      x_last_update_date => X_LAST_UPDATE_DATE,
249      x_last_updated_by => X_LAST_UPDATED_BY,
250      x_last_update_login => X_LAST_UPDATE_LOGIN
251   );
252 
253   insert into IGS_GR_STAT (
254     GRADUAND_STATUS,
255     DESCRIPTION,
256     S_GRADUAND_STATUS,
257     CLOSED_IND,
258     CREATION_DATE,
259     CREATED_BY,
260     LAST_UPDATE_DATE,
261     LAST_UPDATED_BY,
262     LAST_UPDATE_LOGIN
263   ) values (
264     NEW_REFERENCES.GRADUAND_STATUS,
265     NEW_REFERENCES.DESCRIPTION,
266     NEW_REFERENCES.S_GRADUAND_STATUS,
267     NEW_REFERENCES.CLOSED_IND,
268     X_LAST_UPDATE_DATE,
269     X_LAST_UPDATED_BY,
270     X_LAST_UPDATE_DATE,
271     X_LAST_UPDATED_BY,
272     X_LAST_UPDATE_LOGIN
273   );
274 
275   open c;
276   fetch c into X_ROWID;
277   if (c%notfound) then
278     close c;
279     raise no_data_found;
280   end if;
281   close c;
282 
283 end INSERT_ROW;
284 
285 procedure LOCK_ROW (
286   X_ROWID in VARCHAR2,
287   X_GRADUAND_STATUS in VARCHAR2,
288   X_DESCRIPTION in VARCHAR2,
289   X_S_GRADUAND_STATUS in VARCHAR2,
290   X_CLOSED_IND in VARCHAR2
291 ) AS
292   cursor c1 is select
293       DESCRIPTION,
294       S_GRADUAND_STATUS,
295       CLOSED_IND
296     from IGS_GR_STAT
297     where ROWID = X_ROWID for update nowait;
298   tlinfo c1%rowtype;
299 
300 begin
301   open c1;
302   fetch c1 into tlinfo;
303   if (c1%notfound) then
304     close c1;
305     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
306     app_exception.raise_exception;
307     return;
308   end if;
309   close c1;
310 
311   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
312       AND (tlinfo.S_GRADUAND_STATUS = X_S_GRADUAND_STATUS)
313       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
314   ) then
315     null;
316   else
317     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
318     app_exception.raise_exception;
319   end if;
320   return;
321 end LOCK_ROW;
322 
323 procedure UPDATE_ROW (
324   X_ROWID in VARCHAR2,
325   X_GRADUAND_STATUS in VARCHAR2,
326   X_DESCRIPTION in VARCHAR2,
327   X_S_GRADUAND_STATUS in VARCHAR2,
328   X_CLOSED_IND in VARCHAR2,
329   X_MODE in VARCHAR2 default 'R'
330   ) AS
331     X_LAST_UPDATE_DATE DATE;
332     X_LAST_UPDATED_BY NUMBER;
333     X_LAST_UPDATE_LOGIN NUMBER;
334 begin
335   X_LAST_UPDATE_DATE := SYSDATE;
336   if(X_MODE = 'I') then
337     X_LAST_UPDATED_BY := 1;
338     X_LAST_UPDATE_LOGIN := 0;
339   elsif (X_MODE = 'R') then
340     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
341     if X_LAST_UPDATED_BY is NULL then
342       X_LAST_UPDATED_BY := -1;
343     end if;
344     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
345     if X_LAST_UPDATE_LOGIN is NULL then
346       X_LAST_UPDATE_LOGIN := -1;
347     end if;
348   else
349     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
350     app_exception.raise_exception;
351   end if;
352 
353  Before_DML (
354      p_action => 'UPDATE',
355      x_rowid => X_ROWID,
356     x_graduand_status => X_GRADUAND_STATUS,
357     x_description => X_DESCRIPTION,
358     x_s_graduand_status => X_S_GRADUAND_STATUS,
359     x_closed_ind => X_CLOSED_IND,
360     x_creation_date => X_LAST_UPDATE_DATE,
361      x_created_by => X_LAST_UPDATED_BY,
362      x_last_update_date => X_LAST_UPDATE_DATE,
363      x_last_updated_by => X_LAST_UPDATED_BY,
364      x_last_update_login => X_LAST_UPDATE_LOGIN
365   );
366 
367   update IGS_GR_STAT set
368     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
369     S_GRADUAND_STATUS = NEW_REFERENCES.S_GRADUAND_STATUS,
370     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
371     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
372     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
373     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
374   where ROWID = X_ROWID
375   ;
376   if (sql%notfound) then
377     raise no_data_found;
378   end if;
379 
380 end UPDATE_ROW;
381 
382 procedure ADD_ROW (
383   X_ROWID in out NOCOPY VARCHAR2,
384   X_GRADUAND_STATUS in VARCHAR2,
385   X_DESCRIPTION in VARCHAR2,
386   X_S_GRADUAND_STATUS in VARCHAR2,
387   X_CLOSED_IND in VARCHAR2,
388   X_MODE in VARCHAR2 default 'R'
389   ) AS
390   cursor c1 is select rowid from IGS_GR_STAT
391      where GRADUAND_STATUS = X_GRADUAND_STATUS
392   ;
393 
394 begin
395   open c1;
396   fetch c1 into X_ROWID;
397   if (c1%notfound) then
398     close c1;
399     INSERT_ROW (
400      X_ROWID,
401      X_GRADUAND_STATUS,
402      X_DESCRIPTION,
403      X_S_GRADUAND_STATUS,
404      X_CLOSED_IND,
405      X_MODE);
406     return;
407   end if;
408   close c1;
409   UPDATE_ROW (
410    X_ROWID,
411    X_GRADUAND_STATUS,
412    X_DESCRIPTION,
413    X_S_GRADUAND_STATUS,
414    X_CLOSED_IND,
415    X_MODE);
416 end ADD_ROW;
417 
418 /* Removed the code for delete row  For Locking Issus */
419 
420 end IGS_GR_STAT_PKG;