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_TAC_LVL_OF_QF_PKG

Source


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