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_GOVT_HNS_LVL_PKG

Source


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