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_LV_COM_PKG

Source


1 package body IGS_AD_TER_ED_LV_COM_PKG as
2 /* $Header: IGSAI53B.pls 115.4 2003/10/30 13:13:32 akadam ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_AD_TER_ED_LV_COM%RowType;
6   new_references IGS_AD_TER_ED_LV_COM%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_tertiary_edu_lvl_comp IN VARCHAR2 DEFAULT NULL,
12     x_description IN VARCHAR2 DEFAULT NULL,
13     x_tac_level_of_comp 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_LV_COM
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_comp := x_tertiary_edu_lvl_comp;
46     new_references.description := x_description;
47     new_references.tac_level_of_comp := x_tac_level_of_comp;
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 Completion.
70 	IF p_inserting OR ((old_references.tac_level_of_comp <> new_references.tac_level_of_comp) OR
71 			(old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N')) THEN
72 		IF IGS_AD_VAL_TELOC.admp_val_tloc_closed(
73 					new_references.tac_level_of_comp,
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 
91 	IF Column_Name is null then
92 		NULL;
93 	ELSIF upper(Column_Name) = 'CLOSED_IND' then
94 		new_references.closed_ind := column_value;
95 	ELSIF upper(Column_Name) = 'TAC_LEVEL_OF_COMP' then
96 		new_references.tac_level_of_comp := column_value;
97 	ELSIF upper(Column_Name) = 'TERTIARY_EDU_LVL_COMP' then
98 		new_references.tertiary_edu_lvl_comp := column_value;
99 	END IF;
100 
101 	IF upper(Column_Name) = 'CLOSED_IND' OR Column_Name IS NULL THEN
102 		IF new_references.closed_ind NOT IN ('Y','N') THEN
103 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
104 			IGS_GE_MSG_STACK.ADD;
105 			App_Exception.Raise_Exception;
106 		END IF;
107 	END IF;
108 	IF upper(Column_Name) = 'TAC_LEVEL_OF_COMP' OR Column_Name IS NULL THEN
109 		IF new_references.tac_level_of_comp <> UPPER(new_references.tac_level_of_comp) THEN
110 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
111 			IGS_GE_MSG_STACK.ADD;
112 			App_Exception.Raise_Exception;
113 		END IF;
114 	END IF;
115 	IF upper(Column_Name) = 'TERTIARY_EDU_LVL_COMP' OR Column_Name IS NULL THEN
116 		IF new_references.tertiary_edu_lvl_comp <> UPPER(new_references.tertiary_edu_lvl_comp) THEN
117 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
118 			IGS_GE_MSG_STACK.ADD;
119 			App_Exception.Raise_Exception;
120 		END IF;
121 	END IF;
122   END Check_Constraints;
123 
124   PROCEDURE Check_Parent_Existance as
125   BEGIN
126 
127     IF (((old_references.tac_level_of_comp = new_references.tac_level_of_comp)) OR
128         ((new_references.tac_level_of_comp IS NULL))) THEN
129       NULL;
130     ELSE
131       IF NOT IGS_AD_TAC_LV_OF_COM_PKG.Get_PK_For_Validation (
132         new_references.tac_level_of_comp ,
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_LV_COM (
146       old_references.tertiary_edu_lvl_comp
147       );
148 
149   END Check_Child_Existance;
150 
151 function Get_PK_For_Validation (
152     x_tertiary_edu_lvl_comp 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_LV_COM
159       WHERE    tertiary_edu_lvl_comp = x_tertiary_edu_lvl_comp 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_COM (
180     x_tac_level_of_comp IN VARCHAR2
181     ) AS
182 
183     CURSOR cur_rowid IS
184       SELECT   rowid
185       FROM     IGS_AD_TER_ED_LV_COM
186       WHERE    tac_level_of_comp = x_tac_level_of_comp ;
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_TELOC_TLOC_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_COM;
204 
205   PROCEDURE Before_DML (
206     p_action IN VARCHAR2,
207     x_rowid IN VARCHAR2 DEFAULT NULL,
208     x_tertiary_edu_lvl_comp IN VARCHAR2 DEFAULT NULL,
209     x_description IN VARCHAR2 DEFAULT NULL,
210     x_tac_level_of_comp 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_comp,
224       x_description,
225       x_tac_level_of_comp,
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_comp
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_comp
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_COMP in VARCHAR2,
280   X_DESCRIPTION in VARCHAR2,
281   X_TAC_LEVEL_OF_COMP 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_LV_COM
286       where TERTIARY_EDU_LVL_COMP = X_TERTIARY_EDU_LVL_COMP;
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_comp => X_TERTIARY_EDU_LVL_COMP,
313      x_description => X_DESCRIPTION,
314      x_tac_level_of_comp => X_TAC_LEVEL_OF_COMP,
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_LV_COM (
323     TERTIARY_EDU_LVL_COMP,
324     DESCRIPTION,
325     TAC_LEVEL_OF_COMP,
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_COMP,
334     NEW_REFERENCES.DESCRIPTION,
335     NEW_REFERENCES.TAC_LEVEL_OF_COMP,
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 
357 end INSERT_ROW;
358 
359 procedure LOCK_ROW (
360   X_ROWID in VARCHAR2,
361   X_TERTIARY_EDU_LVL_COMP in VARCHAR2,
362   X_DESCRIPTION in VARCHAR2,
363   X_TAC_LEVEL_OF_COMP in VARCHAR2,
364   X_CLOSED_IND in VARCHAR2
365 ) as
366   cursor c1 is select
367       DESCRIPTION,
368       TAC_LEVEL_OF_COMP,
369       CLOSED_IND
370     from IGS_AD_TER_ED_LV_COM
371     where ROWID = X_ROWID for update nowait;
372   tlinfo c1%rowtype;
373 
374 begin
375   open c1;
376   fetch c1 into tlinfo;
377   if (c1%notfound) then
378     close c1;
379     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
380     IGS_GE_MSG_STACK.ADD;
381     app_exception.raise_exception;
382     return;
383   end if;
384   close c1;
385 
386   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
387       AND ((tlinfo.TAC_LEVEL_OF_COMP = X_TAC_LEVEL_OF_COMP)
391   ) then
388            OR ((tlinfo.TAC_LEVEL_OF_COMP is null)
389                AND (X_TAC_LEVEL_OF_COMP is null)))
390       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
392     null;
393   else
394     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
395     IGS_GE_MSG_STACK.ADD;
396     app_exception.raise_exception;
397   end if;
398   return;
399 end LOCK_ROW;
400 
401 procedure UPDATE_ROW (
402   X_ROWID in VARCHAR2,
403   X_TERTIARY_EDU_LVL_COMP in VARCHAR2,
404   X_DESCRIPTION in VARCHAR2,
405   X_TAC_LEVEL_OF_COMP in VARCHAR2,
406   X_CLOSED_IND in VARCHAR2,
407   X_MODE in VARCHAR2 default 'R'
408   ) as
409     X_LAST_UPDATE_DATE DATE;
410     X_LAST_UPDATED_BY NUMBER;
411     X_LAST_UPDATE_LOGIN NUMBER;
412 begin
413   X_LAST_UPDATE_DATE := SYSDATE;
414   if(X_MODE = 'I') then
415     X_LAST_UPDATED_BY := 1;
416     X_LAST_UPDATE_LOGIN := 0;
417   elsif (X_MODE = 'R') then
418     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
419     if X_LAST_UPDATED_BY is NULL then
420       X_LAST_UPDATED_BY := -1;
421     end if;
422     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
423     if X_LAST_UPDATE_LOGIN is NULL then
424       X_LAST_UPDATE_LOGIN := -1;
425     end if;
426   else
427     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
428     IGS_GE_MSG_STACK.ADD;
429     app_exception.raise_exception;
430   end if;
431   Before_DML (
432      p_action => 'UPDATE',
433      x_rowid => X_ROWID,
434      x_tertiary_edu_lvl_comp => X_TERTIARY_EDU_LVL_COMP,
435      x_description => X_DESCRIPTION,
436      x_tac_level_of_comp => X_TAC_LEVEL_OF_COMP,
437      x_closed_ind => X_CLOSED_IND,
438      x_creation_date => X_LAST_UPDATE_DATE,
439      x_created_by => X_LAST_UPDATED_BY,
440      x_last_update_date => X_LAST_UPDATE_DATE,
441      x_last_updated_by => X_LAST_UPDATED_BY,
442      x_last_update_login => X_LAST_UPDATE_LOGIN
443   );
444 update IGS_AD_TER_ED_LV_COM set
445     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
446     TAC_LEVEL_OF_COMP = NEW_REFERENCES.TAC_LEVEL_OF_COMP,
447     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
448     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
449     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
450     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
451   where ROWID = X_ROWID
452   ;
453   if (sql%notfound) then
454     raise no_data_found;
455   end if;
456   After_DML (
457      p_action => 'UPDATE',
458      x_rowid => X_ROWID
459     );
460 end UPDATE_ROW;
461 
462 procedure ADD_ROW (
463   X_ROWID in out NOCOPY VARCHAR2,
464   X_TERTIARY_EDU_LVL_COMP in VARCHAR2,
465   X_DESCRIPTION in VARCHAR2,
466   X_TAC_LEVEL_OF_COMP in VARCHAR2,
467   X_CLOSED_IND in VARCHAR2,
468   X_MODE in VARCHAR2 default 'R'
469   ) as
470   cursor c1 is select rowid from IGS_AD_TER_ED_LV_COM
471      where TERTIARY_EDU_LVL_COMP = X_TERTIARY_EDU_LVL_COMP
472   ;
473 begin
474   open c1;
475   fetch c1 into X_ROWID;
476   if (c1%notfound) then
477     close c1;
478     INSERT_ROW (
479      X_ROWID,
480      X_TERTIARY_EDU_LVL_COMP,
481      X_DESCRIPTION,
482      X_TAC_LEVEL_OF_COMP,
483      X_CLOSED_IND,
484      X_MODE);
485     return;
486   end if;
487   close c1;
488   UPDATE_ROW (
489    X_ROWID,
490    X_TERTIARY_EDU_LVL_COMP,
491    X_DESCRIPTION,
492    X_TAC_LEVEL_OF_COMP,
493    X_CLOSED_IND,
494    X_MODE);
495 end ADD_ROW;
496 
497 procedure DELETE_ROW (
498 X_ROWID in VARCHAR2
499 ) as
500 begin
501   Before_DML (
502      p_action => 'DELETE',
503      x_rowid => X_ROWID
504   );
505   delete from IGS_AD_TER_ED_LV_COM
506   where ROWID = X_ROWID;
507   if (sql%notfound) then
508     raise no_data_found;
509   end if;
510   After_DML (
511      p_action => 'DELETE',
512      x_rowid => X_ROWID
513   );
514 end DELETE_ROW;
515 
516 end IGS_AD_TER_ED_LV_COM_PKG;