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_PKG

Source


1 package body IGS_AD_CAT_PKG As
2 /* $Header: IGSAI07B.pls 115.11 2003/10/30 13:10:10 akadam ship $ */
3 
4   l_rowid VARCHAR2(25);
5   old_references IGS_AD_CAT_ALL%RowType;
6   new_references IGS_AD_CAT_ALL%RowType;
7 
8   PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_org_id IN NUMBER DEFAULT NULL,
12     x_admission_cat IN VARCHAR2 DEFAULT NULL,
13     x_description IN VARCHAR2 DEFAULT NULL,
14     x_closed_ind IN VARCHAR2 DEFAULT NULL,
15     x_s_citizenship_type IN VARCHAR2 DEFAULT NULL,
16     x_creation_date IN DATE DEFAULT NULL,
17     x_created_by IN NUMBER DEFAULT NULL,
18     x_last_update_date IN DATE DEFAULT NULL,
19     x_last_updated_by IN NUMBER DEFAULT NULL,
20     x_last_update_login IN NUMBER DEFAULT NULL
21   ) AS
22 
23     CURSOR cur_old_ref_values IS
24       SELECT   *
25       FROM     IGS_AD_CAT_ALL
26       WHERE    rowid = x_rowid;
27 
28   BEGIN
29 
30     l_rowid := x_rowid;
31 
32     -- Code for setting the Old and New Reference Values.
33     -- Populate Old Values.
34     Open cur_old_ref_values;
35     Fetch cur_old_ref_values INTO old_references;
36     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
37       Close cur_old_ref_values;
38       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39       IGS_GE_MSG_STACK.ADD;
40       App_Exception.Raise_Exception;
41       Return;
42     END IF;
43     Close cur_old_ref_values;
44 
45     -- Populate New Values.
46     new_references.org_id := x_org_id;
47     new_references.admission_cat := x_admission_cat;
48     new_references.description := x_description;
49     new_references.closed_ind := x_closed_ind;
50     new_references.s_citizenship_type := x_s_citizenship_type;
51     IF (p_action = 'UPDATE') THEN
52       new_references.creation_date := old_references.creation_date;
53       new_references.created_by := old_references.created_by;
54     ELSE
55       new_references.creation_date := x_creation_date;
56       new_references.created_by := x_created_by;
57     END IF;
58     new_references.last_update_date := x_last_update_date;
59     new_references.last_updated_by := x_last_updated_by;
60     new_references.last_update_login := x_last_update_login;
61 
62   END Set_Column_Values;
63 
64   PROCEDURE BeforeRowInsertUpdate1(
65     p_inserting IN BOOLEAN DEFAULT FALSE,
66     p_updating IN BOOLEAN DEFAULT FALSE,
67     p_deleting IN BOOLEAN DEFAULT FALSE
68     ) AS
69 	v_message_name   VARCHAR2(30);
70   BEGIN
71 	-- Validate the update.
72 	IF old_references.s_citizenship_type <> new_references.s_citizenship_type THEN
73 		IF IGS_AD_VAL_AC.admp_val_ac_upd (
74 				new_references.admission_cat,
75 				v_message_name) = FALSE THEN
76 		         Fnd_Message.Set_Name('IGS',v_message_name);
77 		         IGS_GE_MSG_STACK.ADD;
78                      App_Exception.Raise_Exception;
79 		END IF;
80 	END IF;
81   END BeforeRowInsertUpdate1;
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) = 'S_CITIZENSHIP_TYPE' Then
91      		new_references.s_citizenship_type := column_value;
92 	ELSIF upper(Column_name) = 'ADMISSION_CAT' Then
93      		new_references.admission_cat := column_value;
94 	ELSIF upper(Column_name) = 'CLOSED_IND' Then
95      		new_references.closed_ind := column_value;
96 	END IF;
97 
98 	IF upper(column_name) = 'S_CITIZENSHIP_TYPE' OR column_name is null Then
99      		IF new_references.s_citizenship_type  NOT IN ('ALL','DOMESTIC','INTRNTNL') Then
100        		  Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
101        		  IGS_GE_MSG_STACK.ADD;
102        		  App_Exception.Raise_Exception;
103      		END IF;
104 	END IF;
105 	IF upper(column_name) = 'ADMISSION_CAT' OR column_name is null Then
106      		IF new_references.admission_cat <> UPPER(new_references.admission_cat) Then
107        		  Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
108        		  IGS_GE_MSG_STACK.ADD;
109        		  App_Exception.Raise_Exception;
110      		END IF;
111 	END IF;
112 	IF upper(column_name) = 'CLOSED_IND' OR column_name is null Then
113      		IF new_references.closed_ind  NOT IN ( 'Y' , 'N' ) Then
114        		  Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
115        		  IGS_GE_MSG_STACK.ADD;
116        		  App_Exception.Raise_Exception;
117      		END IF;
118 	END IF;
119   END Check_Constraints;
120 
121 
122   PROCEDURE Check_Child_Existance AS
123   BEGIN
124 
125     IGS_AD_PRCS_CAT_PKG.GET_FK_IGS_AD_CAT (
126       old_references.admission_cat
127       );
128 
129     IGS_AD_CAT_PS_TYPE_PKG.GET_FK_IGS_AD_CAT (
130       old_references.admission_cat
131       );
132 
133     IGS_AD_CT_HECS_PAYOP_PKG.GET_FK_IGS_AD_CAT (
134       old_references.admission_cat
135       );
136 
137     IGS_AD_PERD_AD_CAT_PKG.GET_FK_IGS_AD_CAT (
138       old_references.admission_cat
139       );
140 
141     IGS_CO_CAT_MAP_PKG.GET_FK_IGS_AD_CAT (
142       old_references.admission_cat
143       );
144 
145     IGS_PS_OF_OPT_AD_CAT_PKG.GET_FK_IGS_AD_CAT (
146       old_references.admission_cat
147       );
148 
149     IGS_EN_CAT_MAPPING_PKG.GET_FK_IGS_AD_CAT (
150       old_references.admission_cat
151       );
152 
153     IGS_FI_FEE_CAT_MAP_PKG.GET_FK_IGS_AD_CAT (
154       old_references.admission_cat
155       );
156 
157     IGS_PS_PAT_OF_STUDY_PKG.GET_FK_IGS_AD_CAT (
158       old_references.admission_cat
159       );
160 
161     IGS_AD_APCTR_RU_PKG.GET_FK_IGS_AD_CAT (
162       old_references.admission_cat
163       );
164 
165   END Check_Child_Existance;
166 
167   Function Get_PK_For_Validation (
168     x_admission_cat IN VARCHAR2,
169     x_closed_ind IN VARCHAR2 )
170   RETURN BOOLEAN  AS
171 	gv_other_detail		VARCHAR2(255);
172 	CURSOR cur_rowid IS
173       	SELECT   rowid
174       	FROM     IGS_AD_CAT_ALL
175       	WHERE  admission_cat = x_admission_cat AND
176                closed_ind = NVL(x_closed_ind,closed_ind);
177 
178 	lv_rowid cur_rowid%RowType;
179   BEGIN  -- Get_PK_For_Validation
180 	Open cur_rowid;
181 	Fetch cur_rowid INTO lv_rowid;
182 	IF (cur_rowid%FOUND) THEN
183 		Close cur_rowid;
184 		Return (TRUE);
185 	ELSE
186 		Close cur_rowid;
187 		Return (FALSE);
188 	END IF;
189   END Get_PK_For_Validation;
190 
191   PROCEDURE Before_DML (
192     p_action IN VARCHAR2,
193     x_rowid IN VARCHAR2 DEFAULT NULL,
194     x_org_id IN NUMBER DEFAULT NULL,
195     x_admission_cat IN VARCHAR2 DEFAULT NULL,
196     x_description IN VARCHAR2 DEFAULT NULL,
197     x_closed_ind IN VARCHAR2 DEFAULT NULL,
198     x_s_citizenship_type IN VARCHAR2 DEFAULT NULL,
199     x_creation_date IN DATE DEFAULT NULL,
200     x_created_by IN NUMBER DEFAULT NULL,
201     x_last_update_date IN DATE DEFAULT NULL,
202     x_last_updated_by IN NUMBER DEFAULT NULL,
203     x_last_update_login IN NUMBER DEFAULT NULL
204   ) AS
205   BEGIN
206     Set_Column_Values (
207       p_action,
208       x_rowid,
209       x_org_id,
210       x_admission_cat,
211       x_description,
212       x_closed_ind,
213       x_s_citizenship_type,
214       x_creation_date,
215       x_created_by,
216       x_last_update_date,
217       x_last_updated_by,
218       x_last_update_login
219     );
220 	IF (p_action = 'INSERT') THEN
221       	-- Call all the procedures related to Before Insert.
222 
223      		BeforeRowInsertUpdate1 ( p_inserting => TRUE );
224       	IF  Get_PK_For_Validation (
225           		new_references.admission_cat ) THEN
226          		Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
227          		IGS_GE_MSG_STACK.ADD;
228           		App_Exception.Raise_Exception;
229       	END IF;
230       	Check_Constraints;
231  	ELSIF (p_action = 'UPDATE') THEN
232        	-- Call all the procedures related to Before Update.
233        	BeforeRowInsertUpdate1 ( p_updating => TRUE );
234        	Check_Constraints;
235  	ELSIF (p_action = 'DELETE') THEN
236        	-- Call all the procedures related to Before Delete.
237        	Check_Child_Existance;
238  	ELSIF (p_action = 'VALIDATE_INSERT') THEN
239       	IF  Get_PK_For_Validation (
240           		new_references.admission_cat  ) THEN
241          		Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
242          		IGS_GE_MSG_STACK.ADD;
243           		App_Exception.Raise_Exception;
244       	END IF;
245 	      Check_Constraints;
246  	ELSIF (p_action = 'VALIDATE_UPDATE') THEN
247 	      Check_Constraints;
248 	ELSIF (p_action = 'VALIDATE_DELETE') THEN
249       	Check_Child_Existance;
250 	END IF;
251   END Before_DML;
252 
253   PROCEDURE After_DML (
254     p_action IN VARCHAR2,
255     x_rowid IN VARCHAR2
256   ) AS
257   BEGIN
258 
259     l_rowid := x_rowid;
260 
261   END After_DML;
262 
263 procedure INSERT_ROW (
264   X_ROWID in out NOCOPY VARCHAR2,
265   X_ORG_ID in NUMBER,
266   X_ADMISSION_CAT in VARCHAR2,
267   X_DESCRIPTION in VARCHAR2,
268   X_S_CITIZENSHIP_TYPE in VARCHAR2,
269   X_CLOSED_IND in VARCHAR2,
270   X_MODE in VARCHAR2 default 'R'
271   ) AS
272     cursor C is select ROWID from IGS_AD_CAT_ALL
273       where ADMISSION_CAT = X_ADMISSION_CAT;
274     X_LAST_UPDATE_DATE DATE;
275     X_LAST_UPDATED_BY NUMBER;
276     X_LAST_UPDATE_LOGIN NUMBER;
277 begin
278   X_LAST_UPDATE_DATE := SYSDATE;
279   if(X_MODE = 'I') then
280     X_LAST_UPDATED_BY := 1;
281     X_LAST_UPDATE_LOGIN := 0;
282   elsif (X_MODE = 'R') then
283     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
284     if X_LAST_UPDATED_BY is NULL then
285       X_LAST_UPDATED_BY := -1;
286     end if;
287     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
288     if X_LAST_UPDATE_LOGIN is NULL then
289       X_LAST_UPDATE_LOGIN := -1;
290     end if;
291   else
292     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
293     IGS_GE_MSG_STACK.ADD;
294     app_exception.raise_exception;
295   end if;
296 
297   Before_DML(
298    p_action=>'INSERT',
299    x_rowid=>X_ROWID,
300    x_org_id => igs_ge_gen_003.get_org_id,
301    x_admission_cat=>X_ADMISSION_CAT,
302    x_closed_ind=>Nvl(X_CLOSED_IND, 'N'),
303    x_description=>X_DESCRIPTION,
304    x_s_citizenship_type=>X_S_CITIZENSHIP_TYPE,
305    x_creation_date=>X_LAST_UPDATE_DATE,
306    x_created_by=>X_LAST_UPDATED_BY,
307    x_last_update_date=>X_LAST_UPDATE_DATE,
308    x_last_updated_by=>X_LAST_UPDATED_BY,
309    x_last_update_login=>X_LAST_UPDATE_LOGIN
310    );
311 
312   insert into IGS_AD_CAT_ALL (
313     ORG_ID,
314     ADMISSION_CAT,
315     DESCRIPTION,
316     S_CITIZENSHIP_TYPE,
317     CLOSED_IND,
318     CREATION_DATE,
319     CREATED_BY,
320     LAST_UPDATE_DATE,
321     LAST_UPDATED_BY,
322     LAST_UPDATE_LOGIN
323   ) values (
324     NEW_REFERENCES.ORG_ID,
325     NEW_REFERENCES.ADMISSION_CAT,
326     NEW_REFERENCES.DESCRIPTION,
327     NEW_REFERENCES.S_CITIZENSHIP_TYPE,
328     NEW_REFERENCES.CLOSED_IND,
329     X_LAST_UPDATE_DATE,
330     X_LAST_UPDATED_BY,
331     X_LAST_UPDATE_DATE,
332     X_LAST_UPDATED_BY,
333     X_LAST_UPDATE_LOGIN
334   );
335 
336   open c;
337   fetch c into X_ROWID;
338   if (c%notfound) then
339     close c;
340     raise no_data_found;
341   end if;
342   close c;
343 
344   After_DML (
345     p_action => 'INSERT',
346     x_rowid => X_ROWID);
347 
348 end INSERT_ROW;
349 
350 procedure LOCK_ROW (
351   X_ROWID in VARCHAR2,
352   X_ADMISSION_CAT in VARCHAR2,
353   X_DESCRIPTION in VARCHAR2,
354   X_S_CITIZENSHIP_TYPE in VARCHAR2,
355   X_CLOSED_IND in VARCHAR2
356 ) AS
357   cursor c1 is select
358       DESCRIPTION,
359       S_CITIZENSHIP_TYPE,
360       CLOSED_IND
361     from IGS_AD_CAT_ALL
362     where ROWID = X_ROWID for update nowait;
363   tlinfo c1%rowtype;
364 
365 begin
366   open c1;
367   fetch c1 into tlinfo;
368   if (c1%notfound) then
369     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
370     IGS_GE_MSG_STACK.ADD;
371     app_exception.raise_exception;
372     close c1;
373     return;
374   end if;
375   close c1;
376 
377   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
378       AND (tlinfo.S_CITIZENSHIP_TYPE = X_S_CITIZENSHIP_TYPE)
379       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
380   ) then
381     null;
382   else
383     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
384     IGS_GE_MSG_STACK.ADD;
385     app_exception.raise_exception;
386   end if;
387   return;
388 end LOCK_ROW;
389 
390 procedure UPDATE_ROW (
391   X_ROWID in VARCHAR2,
392   X_ADMISSION_CAT in VARCHAR2,
393   X_DESCRIPTION in VARCHAR2,
394   X_S_CITIZENSHIP_TYPE in VARCHAR2,
395   X_CLOSED_IND in VARCHAR2,
396   X_MODE in VARCHAR2 default 'R'
397   ) AS
398     X_LAST_UPDATE_DATE DATE;
399     X_LAST_UPDATED_BY NUMBER;
400     X_LAST_UPDATE_LOGIN NUMBER;
401 begin
402   X_LAST_UPDATE_DATE := SYSDATE;
403   if(X_MODE = 'I') then
404     X_LAST_UPDATED_BY := 1;
405     X_LAST_UPDATE_LOGIN := 0;
406   elsif (X_MODE = 'R') then
407     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
408     if X_LAST_UPDATED_BY is NULL then
409       X_LAST_UPDATED_BY := -1;
410     end if;
411     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
412     if X_LAST_UPDATE_LOGIN is NULL then
413       X_LAST_UPDATE_LOGIN := -1;
414     end if;
415   else
416     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
417     IGS_GE_MSG_STACK.ADD;
418     app_exception.raise_exception;
419   end if;
420 
421   Before_DML(
422    p_action=>'UPDATE',
423    x_rowid=>X_ROWID,
424    x_admission_cat=>X_ADMISSION_CAT,
425    x_closed_ind=>X_CLOSED_IND,
426    x_description=>X_DESCRIPTION,
427    x_s_citizenship_type=>X_S_CITIZENSHIP_TYPE,
428    x_creation_date=>X_LAST_UPDATE_DATE,
429    x_created_by=>X_LAST_UPDATED_BY,
430    x_last_update_date=>X_LAST_UPDATE_DATE,
431    x_last_updated_by=>X_LAST_UPDATED_BY,
432    x_last_update_login=>X_LAST_UPDATE_LOGIN
433    );
434 
435   update IGS_AD_CAT_ALL set
436     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
437     S_CITIZENSHIP_TYPE = NEW_REFERENCES.S_CITIZENSHIP_TYPE,
438     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
439     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
440     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
441     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
442   where ROWID = X_ROWID
443   ;
444   if (sql%notfound) then
445     raise no_data_found;
446   end if;
447 
448   After_DML (
449     p_action => 'UPDATE',
450     x_rowid => X_ROWID);
451 
452 end UPDATE_ROW;
453 
454 procedure ADD_ROW (
455   X_ROWID in out NOCOPY VARCHAR2,
456   X_ORG_ID in NUMBER,
457   X_ADMISSION_CAT in VARCHAR2,
458   X_DESCRIPTION in VARCHAR2,
459   X_S_CITIZENSHIP_TYPE in VARCHAR2,
460   X_CLOSED_IND in VARCHAR2,
461   X_MODE in VARCHAR2 default 'R'
462   ) AS
463   cursor c1 is select rowid from IGS_AD_CAT_ALL
464      where ADMISSION_CAT = X_ADMISSION_CAT
465   ;
466 begin
467   open c1;
468   fetch c1 into X_ROWID;
469   if (c1%notfound) then
470     close c1;
471     INSERT_ROW (
472      X_ROWID,
473      X_ORG_ID,
474      X_ADMISSION_CAT,
475      X_DESCRIPTION,
476      X_S_CITIZENSHIP_TYPE,
477      X_CLOSED_IND,
478      X_MODE);
479     return;
480   end if;
481   close c1;
482   UPDATE_ROW (
483    X_ROWID,
484    X_ADMISSION_CAT,
485    X_DESCRIPTION,
486    X_S_CITIZENSHIP_TYPE,
487    X_CLOSED_IND,
488    X_MODE);
489 end ADD_ROW;
490 
491 procedure DELETE_ROW (
492   X_ROWID in VARCHAR2
493 ) AS
494 begin
495 
496   Before_DML (
497     p_action => 'DELETE',
498     x_rowid => X_ROWID);
499 
500   delete from IGS_AD_CAT_ALL
501   where ROWID = X_ROWID;
502   if (sql%notfound) then
503     raise no_data_found;
504   end if;
505 
506   After_DML (
507     p_action => 'DELETE',
508     x_rowid => X_ROWID);
509 
510 end DELETE_ROW;
511 
512 end IGS_AD_CAT_PKG;