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_TER_ED_LVL_QF_PKG

Source


1 package body IGS_AD_TER_ED_LVL_QF_PKG as
2 /* $Header: IGSAI52B.pls 115.4 2003/10/30 13:13:28 akadam ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_AD_TER_ED_LVL_QF%RowType;
6   new_references IGS_AD_TER_ED_LVL_QF%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_tertiary_edu_lvl_qual IN VARCHAR2 DEFAULT NULL,
12     x_description IN VARCHAR2 DEFAULT NULL,
13     x_tac_level_of_qual IN VARCHAR2 DEFAULT NULL,
14     x_closed_ind IN VARCHAR2 DEFAULT NULL,
15     x_creation_date IN DATE DEFAULT NULL,
16     x_created_by IN NUMBER DEFAULT NULL,
17     x_last_update_date IN DATE DEFAULT NULL,
18     x_last_updated_by IN NUMBER DEFAULT NULL,
19     x_last_update_login IN NUMBER DEFAULT NULL
20   ) as
21 
22     CURSOR cur_old_ref_values IS
23       SELECT   *
24       FROM     IGS_AD_TER_ED_LVL_QF
25       WHERE    rowid = x_rowid;
26 
27   BEGIN
28 
29     l_rowid := x_rowid;
30 
31     -- Code for setting the Old and New Reference Values.
32     -- Populate Old Values.
33     Open cur_old_ref_values;
34     Fetch cur_old_ref_values INTO old_references;
35     IF (cur_old_ref_values%NOTFOUND) AND p_action NOT IN ('INSERT','VALIDATE_INSERT') THEN
36       Close cur_old_ref_values;
37       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38       IGS_GE_MSG_STACK.ADD;
39       App_Exception.Raise_Exception;
40       Return;
41     END IF;
42     Close cur_old_ref_values;
43 
44     -- Populate New Values.
45     new_references.tertiary_edu_lvl_qual := x_tertiary_edu_lvl_qual;
46     new_references.description := x_description;
47     new_references.tac_level_of_qual := x_tac_level_of_qual;
48     new_references.closed_ind := x_closed_ind;
49     IF (p_action = 'UPDATE') THEN
50       new_references.creation_date := old_references.creation_date;
51       new_references.created_by := old_references.created_by;
52     ELSE
53       new_references.creation_date := x_creation_date;
54       new_references.created_by := x_created_by;
55     END IF;
56     new_references.last_update_date := x_last_update_date;
57     new_references.last_updated_by := x_last_updated_by;
58     new_references.last_update_login := x_last_update_login;
59 
60   END Set_Column_Values;
61 
62   PROCEDURE BeforeRowInsertUpdate1(
63     p_inserting IN BOOLEAN DEFAULT FALSE,
64     p_updating IN BOOLEAN DEFAULT FALSE,
65     p_deleting IN BOOLEAN DEFAULT FALSE
66     ) as
67 	v_message_name VARCHAR2(30);
68   BEGIN
69 	-- Validate TAC level of Qualfication.
70 	IF p_inserting OR ((old_references.tac_level_of_qual <> new_references.tac_level_of_qual) OR
71 			(old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N')) THEN
72 		IF IGS_AD_VAL_TELOQ.admp_val_tloq_closed(
73 					new_references.tac_level_of_qual,
74 					v_message_name) = FALSE THEN
75 		    Fnd_Message.Set_Name('IGS', v_message_name);
76 		    IGS_GE_MSG_STACK.ADD;
77 			App_Exception.Raise_Exception;
78 		END IF;
79 	END IF;
80 
81 
82   END BeforeRowInsertUpdate1;
83 
84   procedure Check_Constraints (
85     Column_Name IN VARCHAR2 DEFAULT NULL,
86     Column_Value IN VARCHAR2 DEFAULT NULL
87   )
88   AS
89   BEGIN
90 	IF Column_Name is null then
91 		NULL;
92 	ELSIF upper(Column_Name) = 'CLOSED_IND' then
93 		new_references.closed_ind := column_value;
94 	ELSIF upper(Column_Name) = 'TAC_LEVEL_OF_QUAL' then
95 		new_references.tac_level_of_qual := column_value;
96 	ELSIF upper(Column_Name) = 'TERTIARY_EDU_LVL_QUAL' then
97 		new_references.tertiary_edu_lvl_qual := column_value;
98 	END IF;
99 
100 	IF upper(Column_Name) = 'CLOSED_IND' OR Column_Name IS NULL THEN
101 		IF new_references.closed_ind NOT IN ('Y','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 	IF upper(Column_Name) = 'TAC_LEVEL_OF_QUAL' OR Column_Name IS NULL THEN
108 		IF new_references.tac_level_of_qual <> UPPER(new_references.tac_level_of_qual) THEN
109 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
110 			IGS_GE_MSG_STACK.ADD;
111 			App_Exception.Raise_Exception;
112 		END IF;
113 	END IF;
114 	IF upper(Column_Name) = 'TERTIARY_EDU_LVL_QUAL' OR Column_Name IS NULL THEN
115 		IF new_references.tertiary_edu_lvl_qual <> UPPER(new_references.tertiary_edu_lvl_qual) THEN
116 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
117 			IGS_GE_MSG_STACK.ADD;
118 			App_Exception.Raise_Exception;
119 		END IF;
120 	END IF;
121 
122   END Check_Constraints;
123 
124   PROCEDURE Check_Parent_Existance as
125   BEGIN
126 
127     IF (((old_references.tac_level_of_qual = new_references.tac_level_of_qual)) OR
128         ((new_references.tac_level_of_qual IS NULL))) THEN
129       NULL;
130     ELSE
131       IF NOT IGS_AD_TAC_LVL_OF_QF_PKG.Get_PK_For_Validation (
132         new_references.tac_level_of_qual ,
133         'N') THEN
134 	Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
135 	IGS_GE_MSG_STACK.ADD;
136 	App_Exception.Raise_Exception;
137 	END IF;
138     END IF;
139 
140   END Check_Parent_Existance;
141 
142   PROCEDURE Check_Child_Existance AS
143   BEGIN
144 
145     IGS_AD_TER_EDU_PKG.GET_FK_IGS_AD_TER_EDU_LVL_QF (
146       old_references.tertiary_edu_lvl_qual
147       );
148 
149   END Check_Child_Existance;
150 
151 function Get_PK_For_Validation (
152     x_tertiary_edu_lvl_qual IN VARCHAR2,
153     x_closed_ind IN VARCHAR2
154 )return BOOLEAN AS
155 
156     CURSOR cur_rowid IS
157       SELECT   rowid
158       FROM     IGS_AD_TER_ED_LVL_QF
159       WHERE    tertiary_edu_lvl_qual = x_tertiary_edu_lvl_qual AND
160                closed_ind = NVL(x_closed_ind,closed_ind)
161       FOR UPDATE NOWAIT;
162 
163     lv_rowid cur_rowid%RowType;
164 
165   BEGIN
166 
167     Open cur_rowid;
168     Fetch cur_rowid INTO lv_rowid;
169     IF (cur_rowid%FOUND) THEN
170       Close cur_rowid;
171       Return(TRUE);
172     ELSE
173       Close cur_rowid;
174       Return(FALSE);
175     END IF;
176 
177   END Get_PK_For_Validation;
178 
179   PROCEDURE GET_FK_IGS_AD_TAC_LVL_OF_QF (
180     x_tac_level_of_qual IN VARCHAR2
181     ) AS
182 
183     CURSOR cur_rowid IS
184       SELECT   rowid
185       FROM     IGS_AD_TER_ED_LVL_QF
186       WHERE    tac_level_of_qual = x_tac_level_of_qual ;
187 
188     lv_rowid cur_rowid%RowType;
189 
190   BEGIN
191 
192     Open cur_rowid;
193     Fetch cur_rowid INTO lv_rowid;
194     IF (cur_rowid%FOUND) THEN
195       Close cur_rowid;
196       Fnd_Message.Set_Name ('IGS', 'IGS_AD_TELOQ_TLOQ_FK');
197       IGS_GE_MSG_STACK.ADD;
198       App_Exception.Raise_Exception;
199       Return;
200     END IF;
201     Close cur_rowid;
202 
203   END GET_FK_IGS_AD_TAC_LVL_OF_QF;
204 
205   PROCEDURE Before_DML (
206     p_action IN VARCHAR2,
207     x_rowid IN VARCHAR2 DEFAULT NULL,
208     x_tertiary_edu_lvl_qual IN VARCHAR2 DEFAULT NULL,
209     x_description IN VARCHAR2 DEFAULT NULL,
210     x_tac_level_of_qual IN VARCHAR2 DEFAULT NULL,
211     x_closed_ind IN VARCHAR2 DEFAULT NULL,
212     x_creation_date IN DATE DEFAULT NULL,
213     x_created_by IN NUMBER DEFAULT NULL,
214     x_last_update_date IN DATE DEFAULT NULL,
215     x_last_updated_by IN NUMBER DEFAULT NULL,
216     x_last_update_login IN NUMBER DEFAULT NULL
217   ) AS
218   BEGIN
219 
220     Set_Column_Values (
221       p_action,
222       x_rowid,
223       x_tertiary_edu_lvl_qual,
224       x_description,
225       x_tac_level_of_qual,
226       x_closed_ind,
227       x_creation_date,
228       x_created_by,
229       x_last_update_date,
230       x_last_updated_by,
231       x_last_update_login
232     );
233 
234     IF (p_action = 'INSERT') THEN
235       BeforeRowInsertUpdate1 ( p_inserting => TRUE );
236 	IF Get_PK_For_Validation (
237 		new_references.tertiary_edu_lvl_qual
238 	) THEN
239 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
240 		IGS_GE_MSG_STACK.ADD;
241 		App_Exception.Raise_Exception;
242 	END IF;
243 	Check_Constraints;
244       Check_Parent_Existance;
245     ELSIF (p_action = 'UPDATE') THEN
246       BeforeRowInsertUpdate1 ( p_updating => TRUE );
247 	Check_Constraints;
248       Check_Parent_Existance;
249     ELSIF (p_action = 'DELETE') THEN
250       Null;
251       Check_Child_Existance;
252     ELSIF (p_action = 'VALIDATE_INSERT') THEN
253 	IF Get_PK_For_Validation (
254 		new_references.tertiary_edu_lvl_qual
255 	) THEN
256 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
257 		IGS_GE_MSG_STACK.ADD;
258 		App_Exception.Raise_Exception;
259 	END IF;
260 	Check_Constraints;
261     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
262 	Check_Constraints;
263     ELSIF (p_action = 'VALIDATE_DELETE') THEN
264       Check_Child_Existance;
265     END IF;
266 
267   END Before_DML;
268 
269   PROCEDURE After_DML (
270     p_action IN VARCHAR2,
271     x_rowid IN VARCHAR2
272   ) as
273   BEGIN
274     l_rowid := x_rowid;
275   END After_DML;
276 
277 procedure INSERT_ROW (
278   X_ROWID in out NOCOPY VARCHAR2,
279   X_TERTIARY_EDU_LVL_QUAL in VARCHAR2,
280   X_DESCRIPTION in VARCHAR2,
281   X_TAC_LEVEL_OF_QUAL in VARCHAR2,
282   X_CLOSED_IND in VARCHAR2,
283   X_MODE in VARCHAR2 default 'R'
284   ) as
285     cursor C is select ROWID from IGS_AD_TER_ED_LVL_QF
286       where TERTIARY_EDU_LVL_QUAL = X_TERTIARY_EDU_LVL_QUAL;
287     X_LAST_UPDATE_DATE DATE;
288     X_LAST_UPDATED_BY NUMBER;
289     X_LAST_UPDATE_LOGIN NUMBER;
290 begin
291   X_LAST_UPDATE_DATE := SYSDATE;
292   if(X_MODE = 'I') then
293     X_LAST_UPDATED_BY := 1;
294     X_LAST_UPDATE_LOGIN := 0;
295   elsif (X_MODE = 'R') then
296     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
297     if X_LAST_UPDATED_BY is NULL then
298       X_LAST_UPDATED_BY := -1;
299     end if;
300     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
301     if X_LAST_UPDATE_LOGIN is NULL then
302       X_LAST_UPDATE_LOGIN := -1;
303     end if;
304   else
305     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
306     IGS_GE_MSG_STACK.ADD;
307     app_exception.raise_exception;
308   end if;
309   Before_DML (
310      p_action => 'INSERT',
311      x_rowid => X_ROWID,
312      x_tertiary_edu_lvl_qual => X_TERTIARY_EDU_LVL_QUAL,
313      x_description => X_DESCRIPTION,
314      x_tac_level_of_qual => X_TAC_LEVEL_OF_QUAL,
315      x_closed_ind => NVL(X_CLOSED_IND,'N'),
316      x_creation_date => X_LAST_UPDATE_DATE,
317      x_created_by => X_LAST_UPDATED_BY,
318      x_last_update_date => X_LAST_UPDATE_DATE,
319      x_last_updated_by => X_LAST_UPDATED_BY,
320      x_last_update_login => X_LAST_UPDATE_LOGIN
321   );
322   insert into IGS_AD_TER_ED_LVL_QF (
323     TERTIARY_EDU_LVL_QUAL,
324     DESCRIPTION,
325     TAC_LEVEL_OF_QUAL,
326     CLOSED_IND,
327     CREATION_DATE,
328     CREATED_BY,
329     LAST_UPDATE_DATE,
330     LAST_UPDATED_BY,
331     LAST_UPDATE_LOGIN
332   ) values (
333     NEW_REFERENCES.TERTIARY_EDU_LVL_QUAL,
334     NEW_REFERENCES.DESCRIPTION,
335     NEW_REFERENCES.TAC_LEVEL_OF_QUAL,
336     NEW_REFERENCES.CLOSED_IND,
337     X_LAST_UPDATE_DATE,
338     X_LAST_UPDATED_BY,
339     X_LAST_UPDATE_DATE,
340     X_LAST_UPDATED_BY,
341     X_LAST_UPDATE_LOGIN
342   );
343 
344   open c;
345   fetch c into X_ROWID;
346   if (c%notfound) then
347     close c;
348     raise no_data_found;
349   end if;
350   close c;
351 
352   After_DML (
353      p_action => 'INSERT',
354      x_rowid => X_ROWID
355     );
356 end INSERT_ROW;
357 
358 procedure LOCK_ROW (
359   X_ROWID in VARCHAR2,
360   X_TERTIARY_EDU_LVL_QUAL in VARCHAR2,
361   X_DESCRIPTION in VARCHAR2,
362   X_TAC_LEVEL_OF_QUAL in VARCHAR2,
363   X_CLOSED_IND in VARCHAR2
364 ) as
365   cursor c1 is select
366       DESCRIPTION,
367       TAC_LEVEL_OF_QUAL,
368       CLOSED_IND
369     from IGS_AD_TER_ED_LVL_QF
370     where ROWID = X_ROWID for update nowait;
371   tlinfo c1%rowtype;
372 
373 begin
374   open c1;
375   fetch c1 into tlinfo;
376   if (c1%notfound) then
377     close c1;
378     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
379     IGS_GE_MSG_STACK.ADD;
380     app_exception.raise_exception;
381     return;
382   end if;
383   close c1;
384 
385   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
386       AND ((tlinfo.TAC_LEVEL_OF_QUAL = X_TAC_LEVEL_OF_QUAL)
387            OR ((tlinfo.TAC_LEVEL_OF_QUAL is null)
388                AND (X_TAC_LEVEL_OF_QUAL is null)))
389       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
390   ) then
391     null;
392   else
393     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
394     IGS_GE_MSG_STACK.ADD;
395     app_exception.raise_exception;
396   end if;
397   return;
398 end LOCK_ROW;
399 
400 procedure UPDATE_ROW (
401   X_ROWID in VARCHAR2,
402   X_TERTIARY_EDU_LVL_QUAL in VARCHAR2,
403   X_DESCRIPTION in VARCHAR2,
404   X_TAC_LEVEL_OF_QUAL in VARCHAR2,
405   X_CLOSED_IND in VARCHAR2,
406   X_MODE in VARCHAR2 default 'R'
407   ) as
408     X_LAST_UPDATE_DATE DATE;
409     X_LAST_UPDATED_BY NUMBER;
410     X_LAST_UPDATE_LOGIN NUMBER;
411 begin
412   X_LAST_UPDATE_DATE := SYSDATE;
413   if(X_MODE = 'I') then
414     X_LAST_UPDATED_BY := 1;
415     X_LAST_UPDATE_LOGIN := 0;
416   elsif (X_MODE = 'R') then
417     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
418     if X_LAST_UPDATED_BY is NULL then
419       X_LAST_UPDATED_BY := -1;
420     end if;
421     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
422     if X_LAST_UPDATE_LOGIN is NULL then
423       X_LAST_UPDATE_LOGIN := -1;
424     end if;
425   else
426     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
427     IGS_GE_MSG_STACK.ADD;
428     app_exception.raise_exception;
429   end if;
430   Before_DML (
431      p_action => 'UPDATE',
432      x_rowid => X_ROWID,
433      x_tertiary_edu_lvl_qual => X_TERTIARY_EDU_LVL_QUAL,
434      x_description => X_DESCRIPTION,
435      x_tac_level_of_qual => X_TAC_LEVEL_OF_QUAL,
436      x_closed_ind => X_CLOSED_IND,
437      x_creation_date => X_LAST_UPDATE_DATE,
438      x_created_by => X_LAST_UPDATED_BY,
439      x_last_update_date => X_LAST_UPDATE_DATE,
440      x_last_updated_by => X_LAST_UPDATED_BY,
441      x_last_update_login => X_LAST_UPDATE_LOGIN
442   );
443   update IGS_AD_TER_ED_LVL_QF set
444     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
445     TAC_LEVEL_OF_QUAL = NEW_REFERENCES.TAC_LEVEL_OF_QUAL,
446     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
447     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
448     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
449     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
450   where ROWID = X_ROWID
451   ;
452   if (sql%notfound) then
453     raise no_data_found;
454   end if;
455   After_DML (
456      p_action => 'UPDATE',
457      x_rowid => X_ROWID
458     );
459 end UPDATE_ROW;
460 
461 procedure ADD_ROW (
462   X_ROWID in out NOCOPY VARCHAR2,
463   X_TERTIARY_EDU_LVL_QUAL in VARCHAR2,
464   X_DESCRIPTION in VARCHAR2,
465   X_TAC_LEVEL_OF_QUAL in VARCHAR2,
466   X_CLOSED_IND in VARCHAR2,
467   X_MODE in VARCHAR2 default 'R'
468   ) as
469   cursor c1 is select rowid from IGS_AD_TER_ED_LVL_QF
470      where TERTIARY_EDU_LVL_QUAL = X_TERTIARY_EDU_LVL_QUAL
471   ;
472 begin
473   open c1;
474   fetch c1 into X_ROWID;
475   if (c1%notfound) then
476     close c1;
477     INSERT_ROW (
478      X_ROWID,
479      X_TERTIARY_EDU_LVL_QUAL,
480      X_DESCRIPTION,
481      X_TAC_LEVEL_OF_QUAL,
482      X_CLOSED_IND,
483      X_MODE);
484     return;
485   end if;
486   close c1;
487   UPDATE_ROW (
488    X_ROWID,
489    X_TERTIARY_EDU_LVL_QUAL,
490    X_DESCRIPTION,
491    X_TAC_LEVEL_OF_QUAL,
492    X_CLOSED_IND,
493    X_MODE);
494 end ADD_ROW;
495 
496 procedure DELETE_ROW (
497 X_ROWID in VARCHAR2
498 ) as
499 begin
500   Before_DML (
501      p_action => 'DELETE',
502      x_rowid => X_ROWID
503   );
504   delete from IGS_AD_TER_ED_LVL_QF
505   where ROWID = X_ROWID;
506   if (sql%notfound) then
507     raise no_data_found;
508   end if;
509   After_DML (
510      p_action => 'DELETE',
511      x_rowid => X_ROWID
512   );
513 end DELETE_ROW;
514 
515 end IGS_AD_TER_ED_LVL_QF_PKG;