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_CAT_PS_TYPE_PKG

Source


1 package body IGS_AD_CAT_PS_TYPE_PKG as
2 /* $Header: IGSAI13B.pls 115.7 2003/10/30 13:19:01 rghosh ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_AD_CAT_PS_TYPE%RowType;
6   new_references IGS_AD_CAT_PS_TYPE%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_admission_cat IN VARCHAR2 DEFAULT NULL,
12     x_course_type IN VARCHAR2 DEFAULT NULL,
13     x_creation_date IN DATE DEFAULT NULL,
14     x_created_by IN NUMBER DEFAULT NULL,
15     x_last_update_date IN DATE DEFAULT NULL,
16     x_last_updated_by IN NUMBER DEFAULT NULL,
17     x_last_update_login IN NUMBER DEFAULT NULL
18   ) AS
19 
20     CURSOR cur_old_ref_values IS
21       SELECT   *
22       FROM     IGS_AD_CAT_PS_TYPE
23       WHERE    rowid = x_rowid;
24 
25   BEGIN
26 
27     l_rowid := x_rowid;
28 
29     -- Code for setting the Old and New Reference Values.
30     -- Populate Old Values.
31     Open cur_old_ref_values;
32     Fetch cur_old_ref_values INTO old_references;
33     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
34       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35       IGS_GE_MSG_STACK.ADD;
36       App_Exception.Raise_Exception;
37       Close cur_old_ref_values;
38       Return;
39     END IF;
40     Close cur_old_ref_values;
41 
42     -- Populate New Values.
43     new_references.admission_cat := x_admission_cat;
44     new_references.course_type := x_course_type;
45     IF (p_action = 'UPDATE') THEN
46       new_references.creation_date := old_references.creation_date;
47       new_references.created_by := old_references.created_by;
48     ELSE
49       new_references.creation_date := x_creation_date;
50       new_references.created_by := x_created_by;
51     END IF;
52     new_references.last_update_date := x_last_update_date;
53     new_references.last_updated_by := x_last_updated_by;
54     new_references.last_update_login := x_last_update_login;
55 
56   END Set_Column_Values;
57 
58    PROCEDURE BeforeRowInsert1(
59     p_inserting IN BOOLEAN DEFAULT FALSE,
60     p_updating IN BOOLEAN DEFAULT FALSE,
61     p_deleting IN BOOLEAN DEFAULT FALSE
62     ) AS
63 	v_message_name	VARCHAR2(30);
64   BEGIN
65 	-- Validate the admission category closed indicator.
66 	IF IGS_AD_VAL_ACCT.admp_val_ac_closed (
67 			new_references.admission_cat,
68 			v_message_name) = FALSE THEN
69 		         Fnd_Message.Set_Name('IGS',v_message_name);
70                          IGS_GE_MSG_STACK.ADD;
71                         App_Exception.Raise_Exception;
72 	END IF;
73 	-- Validate the course type closed indicator.
74 	IF IGS_AS_VAL_ACOT.crsp_val_cty_closed (
75 			new_references.course_type,
76 			v_message_name) = FALSE THEN
77                          Fnd_Message.Set_Name('IGS',v_message_name);
78                          IGS_GE_MSG_STACK.ADD;
79                      App_Exception.Raise_Exception;
80 	END IF;
81   END BeforeRowInsert1;
82 
83   PROCEDURE Check_Constraints (
84     Column_Name	IN	VARCHAR2	DEFAULT NULL,
85     Column_Value 	IN	VARCHAR2	DEFAULT NULL
86   ) AS
87   BEGIN
88 	IF  column_name is null then
89      		NULL;
90 	ELSIF upper(Column_name) = 'ADMISSION_CAT' Then
91      		new_references.admission_cat := column_value;
92 	ELSIF upper(Column_name) = 'COURSE_TYPE' Then
93      		new_references.course_type := column_value;
94 	END IF;
95 	IF upper(column_name) = 'ADMISSION_CAT' OR column_name is null Then
96      		IF new_references.admission_cat <> UPPER(new_references.admission_cat) Then
97        		  Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
98        		  IGS_GE_MSG_STACK.ADD;
99        		  App_Exception.Raise_Exception;
100      		END IF;
101 	END IF;
102 	IF upper(column_name) = 'COURSE_TYPE' OR column_name is null Then
103      		IF new_references.course_type <> UPPER(new_references.course_type ) Then
104        		  Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
105        		  IGS_GE_MSG_STACK.ADD;
106        		  App_Exception.Raise_Exception;
107      		END IF;
108 	END IF;
109   END Check_Constraints;
110 
111 
112   PROCEDURE Check_Parent_Existance AS
113   BEGIN
114 
115     IF (((old_references.admission_cat = new_references.admission_cat)) OR
116         ((new_references.admission_cat IS NULL))) THEN
117       NULL;
118     ELSE
119       IF NOT IGS_AD_CAT_PKG.Get_PK_For_Validation (
120         new_references.admission_cat , 'N' ) THEN
121      		Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
122      		IGS_GE_MSG_STACK.ADD;
123 		App_Exception.Raise_Exception;
124       END IF;
125     END IF;
126 
127     IF (((old_references.course_type = new_references.course_type)) OR
128         ((new_references.course_type IS NULL))) THEN
129       NULL;
130     ELSE
131        IF NOT IGS_PS_TYPE_PKG.Get_PK_For_Validation (
132         new_references.course_type ) THEN
133      		Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
134      		IGS_GE_MSG_STACK.ADD;
135 		App_Exception.Raise_Exception;
136       END IF;
137    END IF;
138 
139   END Check_Parent_Existance;
140 
141   Function Get_PK_For_Validation (
142     x_admission_cat IN VARCHAR2,
143     x_course_type IN VARCHAR2 )
144   RETURN BOOLEAN  AS
145 	CURSOR cur_rowid IS
146       	SELECT   rowid
147       	FROM     IGS_AD_CAT_PS_TYPE
148       	WHERE    admission_cat = x_admission_cat
149       	AND      course_type = x_course_type
150            	FOR UPDATE NOWAIT;
151 	lv_rowid cur_rowid%RowType;
152   BEGIN  -- Get_PK_For_Validation
153 	Open cur_rowid;
154 	Fetch cur_rowid INTO lv_rowid;
155 	IF (cur_rowid%FOUND) THEN
156 		Close cur_rowid;
157 		Return (TRUE);
158 	ELSE
159 		Close cur_rowid;
160 		Return (FALSE);
161 	END IF;
162   END Get_PK_For_Validation;
163 
164   PROCEDURE GET_FK_IGS_AD_CAT (
165     x_admission_cat IN VARCHAR2
166     ) AS
167 
168     CURSOR cur_rowid IS
169       SELECT   rowid
170       FROM     IGS_AD_CAT_PS_TYPE
171       WHERE    admission_cat = x_admission_cat ;
172 
173     lv_rowid cur_rowid%RowType;
174 
175   BEGIN
176 
177     Open cur_rowid;
178     Fetch cur_rowid INTO lv_rowid;
179     IF (cur_rowid%FOUND) THEN
180       Close cur_rowid;
181       Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACCT_AC_FK');
182       IGS_GE_MSG_STACK.ADD;
183       App_Exception.Raise_Exception;
184       Return;
185     END IF;
186     Close cur_rowid;
187 
188   END GET_FK_IGS_AD_CAT;
189 
190   PROCEDURE Before_DML (
191     p_action IN VARCHAR2,
192     x_rowid IN VARCHAR2 DEFAULT NULL,
193     x_admission_cat IN VARCHAR2 DEFAULT NULL,
194     x_course_type IN VARCHAR2 DEFAULT NULL,
195     x_creation_date IN DATE DEFAULT NULL,
196     x_created_by IN NUMBER DEFAULT NULL,
197     x_last_update_date IN DATE DEFAULT NULL,
198     x_last_updated_by IN NUMBER DEFAULT NULL,
199     x_last_update_login IN NUMBER DEFAULT NULL
200   ) AS
201   BEGIN
202     Set_Column_Values (
203       p_action,
204       x_rowid,
205       x_admission_cat,
206       x_course_type,
207       x_creation_date,
208       x_created_by,
209       x_last_update_date,
210       x_last_updated_by,
211       x_last_update_login
212     );
213 
214 	IF (p_action = 'INSERT') THEN
215       	-- Call all the procedures related to Before Insert.
216      		BeforeRowInsert1 ( p_inserting => TRUE );
217       	IF  Get_PK_For_Validation (
218           		new_references.admission_cat,
219 			new_references.course_type ) THEN
220          		Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
221          		IGS_GE_MSG_STACK.ADD;
222           		App_Exception.Raise_Exception;
223       	END IF;
224       	Check_Constraints;
225 		Check_Parent_Existance;
226  	ELSIF (p_action = 'UPDATE') THEN
227        	-- Call all the procedures related to Before Update.
228        	Check_Constraints;
229  		Check_Parent_Existance;
230  	ELSIF (p_action = 'VALIDATE_INSERT') THEN
231       	IF  Get_PK_For_Validation (
232           		new_references.admission_cat,
233 			new_references.course_type ) THEN
234          		Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
235          		IGS_GE_MSG_STACK.ADD;
236           		App_Exception.Raise_Exception;
237       	END IF;
238 	      Check_Constraints;
239  	ELSIF (p_action = 'VALIDATE_UPDATE') THEN
240 	      Check_Constraints;
241 	END IF;
242   END Before_DML;
243 
244   PROCEDURE After_DML (
245     p_action IN VARCHAR2,
246     x_rowid IN VARCHAR2
247   ) AS
248   BEGIN
249 
250     l_rowid := x_rowid;
251 
252   END After_DML;
253 
254 procedure INSERT_ROW (
255   X_ROWID in out NOCOPY VARCHAR2,
256   X_ADMISSION_CAT in VARCHAR2,
257   X_COURSE_TYPE in VARCHAR2,
258   X_MODE in VARCHAR2 default 'R'
259   ) AS
260     cursor C is select ROWID from IGS_AD_CAT_PS_TYPE
261       where ADMISSION_CAT = X_ADMISSION_CAT
262       and COURSE_TYPE = X_COURSE_TYPE;
263     X_LAST_UPDATE_DATE DATE;
264     X_LAST_UPDATED_BY NUMBER;
265     X_LAST_UPDATE_LOGIN NUMBER;
266 begin
267   X_LAST_UPDATE_DATE := SYSDATE;
268   if(X_MODE = 'I') then
269     X_LAST_UPDATED_BY := 1;
270     X_LAST_UPDATE_LOGIN := 0;
271   elsif (X_MODE = 'R') then
272     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
273     if X_LAST_UPDATED_BY is NULL then
274       X_LAST_UPDATED_BY := -1;
275     end if;
276     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
277     if X_LAST_UPDATE_LOGIN is NULL then
278       X_LAST_UPDATE_LOGIN := -1;
279     end if;
280   else
281     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
282     IGS_GE_MSG_STACK.ADD;
283     app_exception.raise_exception;
284   end if;
285 
286   Before_DML(
287    p_action=>'INSERT',
288    x_rowid=>X_ROWID,
289    x_admission_cat=>X_ADMISSION_CAT,
290    x_course_type=>X_COURSE_TYPE,
291    x_creation_date=>X_LAST_UPDATE_DATE,
292    x_created_by=>X_LAST_UPDATED_BY,
293    x_last_update_date=>X_LAST_UPDATE_DATE,
294    x_last_updated_by=>X_LAST_UPDATED_BY,
295    x_last_update_login=>X_LAST_UPDATE_LOGIN
296    );
297 
298   insert into IGS_AD_CAT_PS_TYPE (
299     ADMISSION_CAT,
300     COURSE_TYPE,
301     CREATION_DATE,
302     CREATED_BY,
303     LAST_UPDATE_DATE,
304     LAST_UPDATED_BY,
305     LAST_UPDATE_LOGIN
306   ) values (
307     NEW_REFERENCES.ADMISSION_CAT,
308     NEW_REFERENCES.COURSE_TYPE,
309     X_LAST_UPDATE_DATE,
310     X_LAST_UPDATED_BY,
311     X_LAST_UPDATE_DATE,
312     X_LAST_UPDATED_BY,
313     X_LAST_UPDATE_LOGIN
314   );
315 
316   open c;
317   fetch c into X_ROWID;
318   if (c%notfound) then
319     close c;
320     raise no_data_found;
321   end if;
322   close c;
323 
324   After_DML (
325     p_action => 'INSERT',
326     x_rowid => X_ROWID);
327 
328 end INSERT_ROW;
329 
330 procedure LOCK_ROW (
331   X_ROWID in VARCHAR2,
332   X_ADMISSION_CAT in VARCHAR2,
333   X_COURSE_TYPE in VARCHAR2
334 ) AS
335   cursor c1 is select ROWID
336     from IGS_AD_CAT_PS_TYPE
337     where ROWID = X_ROWID for update nowait;
338   tlinfo c1%rowtype;
339 
340 begin
341   open c1;
342   fetch c1 into tlinfo;
343   if (c1%notfound) then
344     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
345     IGS_GE_MSG_STACK.ADD;
346     app_exception.raise_exception;
347     close c1;
348     return;
349   end if;
350   close c1;
351 
352   return;
353 end LOCK_ROW;
354 
355 procedure DELETE_ROW (
356   X_ROWID in VARCHAR2
357 ) AS
358 begin
359 
360   Before_DML (
361     p_action => 'DELETE',
362     x_rowid => X_ROWID);
363 
364   delete from IGS_AD_CAT_PS_TYPE
365   where ROWID = X_ROWID;
366   if (sql%notfound) then
367     raise no_data_found;
368   end if;
369 
370   After_DML (
371     p_action => 'DELETE',
372     x_rowid => X_ROWID);
373 
374 end DELETE_ROW;
375 
376 end IGS_AD_CAT_PS_TYPE_PKG;