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_AUSCED_AS_PKG

Source


1 package body IGS_AD_TAC_AUSCED_AS_PKG as
2 /* $Header: IGSAI57B.pls 115.4 2003/10/30 13:13:42 akadam ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_AD_TAC_AUSCED_AS%RowType;
6   new_references IGS_AD_TAC_AUSCED_AS%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_tac_aus_scndry_edu_ass_type 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_AUSCED_AS
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_aus_scndry_edu_ass_type := x_tac_aus_scndry_edu_ass_type;
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 
67 	IF Column_Name is null then
68 		NULL;
69 	ELSIF upper(Column_Name) = 'CLOSED_IND' then
70 		new_references.closed_ind := column_value;
71 	ELSIF upper(Column_Name) = 'TAC_AUS_SCNDRY_EDU_ASS_TYPE' then
72 		new_references.tac_aus_scndry_edu_ass_type := column_value;
73 	END IF;
74 
75 	IF upper(Column_Name) = 'CLOSED_IND' OR Column_Name IS NULL THEN
76 		IF new_references.closed_ind NOT IN ('Y','N') THEN
77 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
78 			IGS_GE_MSG_STACK.ADD;
79 			App_Exception.Raise_Exception;
80 		END IF;
81 	END IF;
82 	IF upper(Column_Name) = 'TAC_AUS_SCNDRY_EDU_ASS_TYPE' OR Column_Name IS NULL THEN
83 		IF new_references.tac_aus_scndry_edu_ass_type <> UPPER(new_references.tac_aus_scndry_edu_ass_type) THEN
84 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
85 			IGS_GE_MSG_STACK.ADD;
86 			App_Exception.Raise_Exception;
87 		END IF;
88 	END IF;
89   END Check_Constraints;
90 
91   PROCEDURE Check_Child_Existance AS
92   BEGIN
93 
94     IGS_AD_AUSE_ED_AS_TY_PKG.GET_FK_IGS_AD_TAC_AUSCED_AS (
95       old_references.tac_aus_scndry_edu_ass_type
96       );
97 
98   END Check_Child_Existance;
99 
100 function Get_PK_For_Validation (
101     x_tac_aus_scndry_edu_ass_type 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_AUSCED_AS
108       WHERE    tac_aus_scndry_edu_ass_type = x_tac_aus_scndry_edu_ass_type 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_aus_scndry_edu_ass_type 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_aus_scndry_edu_ass_type,
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_aus_scndry_edu_ass_type
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_aus_scndry_edu_ass_type
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_AUS_SCNDRY_EDU_ASS_TYPE 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_AUSCED_AS
202       where TAC_AUS_SCNDRY_EDU_ASS_TYPE = X_TAC_AUS_SCNDRY_EDU_ASS_TYPE;
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_aus_scndry_edu_ass_type => X_TAC_AUS_SCNDRY_EDU_ASS_TYPE,
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_AUSCED_AS (
239     TAC_AUS_SCNDRY_EDU_ASS_TYPE,
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_AUS_SCNDRY_EDU_ASS_TYPE,
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_AUS_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
275   X_DESCRIPTION in VARCHAR2,
276   X_CLOSED_IND in VARCHAR2
277 ) AS
278   cursor c1 is
279     select *
280     from IGS_AD_TAC_AUSCED_AS
281     where ROWID = X_ROWID for update nowait;
282   tlinfo c1%rowtype;
283 
284 begin
285   open c1;
286   fetch c1 into tlinfo;
287   if (c1%notfound) then
288     close c1;
289     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
290     IGS_GE_MSG_STACK.ADD;
291     app_exception.raise_exception;
292     return;
293   end if;
294   close c1;
295 
296   if ((tlinfo.TAC_AUS_SCNDRY_EDU_ASS_TYPE = X_TAC_AUS_SCNDRY_EDU_ASS_TYPE)
297        AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
298        AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
299   ) then
300     null;
301   else
302     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
303     IGS_GE_MSG_STACK.ADD;
304     app_exception.raise_exception;
305   end if;
306   return;
307 end LOCK_ROW;
308 
309 procedure UPDATE_ROW (
310   X_ROWID in VARCHAR2,
311   X_TAC_AUS_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
312   X_DESCRIPTION in VARCHAR2,
313   X_CLOSED_IND in VARCHAR2,
314   X_MODE in VARCHAR2 default 'R'
315   ) AS
316     X_LAST_UPDATE_DATE DATE;
317     X_LAST_UPDATED_BY NUMBER;
318     X_LAST_UPDATE_LOGIN NUMBER;
319 begin
320   X_LAST_UPDATE_DATE := SYSDATE;
321   if(X_MODE = 'I') then
322     X_LAST_UPDATED_BY := 1;
323     X_LAST_UPDATE_LOGIN := 0;
324   elsif (X_MODE = 'R') then
325     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
326     if X_LAST_UPDATED_BY is NULL then
327       X_LAST_UPDATED_BY := -1;
328     end if;
329     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
330     if X_LAST_UPDATE_LOGIN is NULL then
331       X_LAST_UPDATE_LOGIN := -1;
332     end if;
333   else
334     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
335 IGS_GE_MSG_STACK.ADD;
336     app_exception.raise_exception;
337   end if;
338   Before_DML (
339      p_action => 'UPDATE',
340      x_rowid => X_ROWID,
341      x_tac_aus_scndry_edu_ass_type => X_TAC_AUS_SCNDRY_EDU_ASS_TYPE,
342      x_description => X_DESCRIPTION,
343      x_closed_ind => X_CLOSED_IND,
344      x_creation_date => X_LAST_UPDATE_DATE,
345      x_created_by => X_LAST_UPDATED_BY,
346      x_last_update_date => X_LAST_UPDATE_DATE,
347      x_last_updated_by => X_LAST_UPDATED_BY,
348      x_last_update_login => X_LAST_UPDATE_LOGIN
349   );
350 
351   update IGS_AD_TAC_AUSCED_AS 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_AUS_SCNDRY_EDU_ASS_TYPE 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_AUSCED_AS
376      where TAC_AUS_SCNDRY_EDU_ASS_TYPE = X_TAC_AUS_SCNDRY_EDU_ASS_TYPE
377   ;
378 
379 begin
380   open c1;
381   fetch c1 into X_ROWID;
382   if (c1%notfound) then
383     close c1;
384     INSERT_ROW (
385      X_ROWID,
386      X_TAC_AUS_SCNDRY_EDU_ASS_TYPE,
387      X_DESCRIPTION,
388      X_CLOSED_IND,
389      X_MODE);
390     return;
391   end if;
392   close c1;
393   UPDATE_ROW (
394    X_ROWID,
395    X_TAC_AUS_SCNDRY_EDU_ASS_TYPE,
396    X_DESCRIPTION,
397    X_CLOSED_IND,
398    X_MODE);
399 end ADD_ROW;
400 
401 procedure DELETE_ROW (
402   X_ROWID in VARCHAR2
403 ) AS
404 begin
405   Before_DML (
406      p_action => 'DELETE',
407      x_rowid => X_ROWID
408   );
409   delete from IGS_AD_TAC_AUSCED_AS
410   where ROWID = X_ROWID;
411   if (sql%notfound) then
412     raise no_data_found;
413   end if;
414   After_DML (
415      p_action => 'DELETE',
416      x_rowid => X_ROWID
417   );
418 end DELETE_ROW;
419 
420 end IGS_AD_TAC_AUSCED_AS_PKG;