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_LV_OF_COM_PKG

Source


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