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_AD_CD_PKG

Source


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