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_FI_FEE_CAT_MAP_PKG

Source


1 package body IGS_FI_FEE_CAT_MAP_PKG AS
2  /* $Header: IGSSI25B.pls 115.6 2003/10/30 13:31:57 rghosh ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_FI_FEE_CAT_MAP%RowType;
5   new_references IGS_FI_FEE_CAT_MAP%RowType;
6   PROCEDURE Set_Column_Values (
7     p_action IN VARCHAR2,
8     x_rowid IN VARCHAR2 DEFAULT NULL,
9     x_fee_cat IN VARCHAR2 DEFAULT NULL,
10     x_admission_cat IN VARCHAR2 DEFAULT NULL,
11     x_dflt_cat_ind IN VARCHAR2 DEFAULT NULL,
12     x_creation_date IN DATE DEFAULT NULL,
13     x_created_by IN NUMBER DEFAULT NULL,
14     x_last_update_date IN DATE DEFAULT NULL,
15     x_last_updated_by IN NUMBER DEFAULT NULL,
16     x_last_update_login IN NUMBER DEFAULT NULL
17   ) AS
18     CURSOR cur_old_ref_values IS
19       SELECT   *
20       FROM     IGS_FI_FEE_CAT_MAP
21       WHERE    rowid = x_rowid;
22   BEGIN
23     l_rowid := x_rowid;
24     -- Code for setting the Old and New Reference Values.
25     -- Populate Old Values.
26     Open cur_old_ref_values;
27     Fetch cur_old_ref_values INTO old_references;
28     IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT', 'VALIDATE_INSERT')) THEN
29       Close cur_old_ref_values;
30       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
31       IGS_GE_MSG_STACK.ADD;
32       App_Exception.Raise_Exception;
33       Return;
34     END IF;
35     Close cur_old_ref_values;
36     -- Populate New Values.
37     new_references.fee_cat := x_fee_cat;
38     new_references.admission_cat := x_admission_cat;
39     new_references.dflt_cat_ind := x_dflt_cat_ind;
40     IF (p_action = 'UPDATE') THEN
41       new_references.creation_date := old_references.creation_date;
42       new_references.created_by := old_references.created_by;
43     ELSE
44       new_references.creation_date := x_creation_date;
45       new_references.created_by := x_created_by;
46     END IF;
47     new_references.last_update_date := x_last_update_date;
48     new_references.last_updated_by := x_last_updated_by;
49     new_references.last_update_login := x_last_update_login;
50   END Set_Column_Values;
51   -- Trigger description :-
52   -- "OSS_TST".trg_fcm_br_iud
53   -- BEFORE INSERT OR DELETE OR UPDATE
54   -- ON IGS_FI_FEE_CAT_MAP
55   -- FOR EACH ROW
56   PROCEDURE BeforeRowInsertUpdateDelete1(
57     p_inserting IN BOOLEAN DEFAULT FALSE,
58     p_updating IN BOOLEAN DEFAULT FALSE,
59     p_deleting IN BOOLEAN DEFAULT FALSE
60     ) AS
61 	v_admission_cat	IGS_FI_FEE_CAT_MAP.admission_cat%TYPE;
62 	v_message_name varchar2(30);
63   BEGIN
64 	IF p_inserting THEN
65 		-- Validate the fee category closed indicator.
66 		IF IGS_AD_VAL_FCM.finp_val_fc_closed (
67 				new_references.fee_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 	END IF;
74 	-- Set the Admission Category value.
75 	IF p_deleting THEN
76 		v_admission_cat := old_references.admission_cat;
77 	ELSE
78 		v_admission_cat := new_references.admission_cat;
79 	END IF;
80 	-- Validate the admission category closed indicator.
81 	IF IGS_AD_VAL_ACCT.admp_val_ac_closed (
82 			v_admission_cat,
83 			v_message_name) = FALSE THEN
84 		Fnd_Message.Set_Name('IGS',v_message_name);
85                 IGS_GE_MSG_STACK.ADD;
86 		App_Exception.Raise_Exception;
87 	END IF;
88   END BeforeRowInsertUpdateDelete1;
89 
90 
91 
92   PROCEDURE Check_Constraints (
93      Column_Name	IN	VARCHAR2	DEFAULT NULL,
94      Column_Value 	IN	VARCHAR2	DEFAULT NULL
95      )AS
96   /*----------------------------------------------------------------------------
97   ||  Created By :
98   ||  Created On :
99   ||  Purpose :
100   ||  Known limitations, enhancements or remarks :
101   ||  Change History :
102   ||  Who             When            What
103   ||  (reverse chronological order - newest change first)
104   ||  vvutukur        17-May-2002     removed upper check constraint on fee_cat column.bug#2344826.
105   ----------------------------------------------------------------------------*/
106    BEGIN
107    IF Column_Name is NULL THEN
108      	NULL;
109    ELSIF upper(Column_Name) = 'ADMISSION_CAT' then
110      	new_references.admission_cat := Column_Value;
111    ELSIF upper(Column_Name) = 'DFLT_CAT_IND' then
112      	new_references.dflt_cat_ind := Column_Value;
113    END IF;
114 
115    IF upper(Column_Name) = 'ADMISSION_CAT' OR
116      		column_name is NULL THEN
117    		IF new_references.admission_cat <> UPPER(new_references.admission_cat) THEN
118    			Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
119                         IGS_GE_MSG_STACK.ADD;
120    			App_Exception.Raise_Exception;
121    		END IF;
122    END IF;
123    IF upper(Column_Name) = 'DFLT_CAT_IND' OR
124         		column_name is NULL THEN
125       		IF new_references.dflt_cat_ind <> 'Y' AND new_references.dflt_cat_ind <> 'N' THEN
126       			Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
127                         IGS_GE_MSG_STACK.ADD;
128       			App_Exception.Raise_Exception;
129       		END IF;
130    END IF;
131    END Check_Constraints;
132 
133   PROCEDURE Check_Parent_Existance AS
134   BEGIN
135     IF (((old_references.admission_cat = new_references.admission_cat)) OR
136         ((new_references.admission_cat IS NULL))) THEN
137       NULL;
138     ELSIF NOT IGS_AD_CAT_PKG.Get_PK_For_Validation ( new_references.admission_cat, 'N' ) THEN
139 		Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
140                 IGS_GE_MSG_STACK.ADD;
141     	App_Exception.Raise_Exception;
142     END IF;
143     IF (((old_references.fee_cat = new_references.fee_cat)) OR
144         ((new_references.fee_cat IS NULL))) THEN
145       NULL;
146     ELSIF NOT IGS_FI_FEE_CAT_PKG.Get_PK_For_Validation (new_references.fee_cat ) THEN
147 		Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
148         IGS_GE_MSG_STACK.ADD;
149     	App_Exception.Raise_Exception;
150     END IF;
151   END Check_Parent_Existance;
152 
153   FUNCTION Get_PK_For_Validation (
154     x_fee_cat IN VARCHAR2,
155     x_admission_cat IN VARCHAR2
156     ) RETURN BOOLEAN AS
157     CURSOR cur_rowid IS
158       SELECT   rowid
159       FROM     IGS_FI_FEE_CAT_MAP
160       WHERE    fee_cat = x_fee_cat
161       AND      admission_cat = x_admission_cat
162       FOR UPDATE NOWAIT;
163     lv_rowid cur_rowid%RowType;
164   BEGIN
165     Open cur_rowid;
166     Fetch cur_rowid INTO lv_rowid;
167    IF (cur_rowid%FOUND) THEN
168          Close cur_rowid;
169          Return (TRUE);
170    ELSE
171          Close cur_rowid;
172          Return (FALSE);
173    END IF;
174   END Get_PK_For_Validation;
175 
176   PROCEDURE GET_FK_IGS_AD_CAT (
177     x_admission_cat IN VARCHAR2
178     ) AS
179     CURSOR cur_rowid IS
180       SELECT   rowid
181       FROM     IGS_FI_FEE_CAT_MAP
182       WHERE    admission_cat = x_admission_cat ;
183     lv_rowid cur_rowid%RowType;
184   BEGIN
185     Open cur_rowid;
186     Fetch cur_rowid INTO lv_rowid;
187     IF (cur_rowid%FOUND) THEN
188       Close cur_rowid;
189       Fnd_Message.Set_Name ('IGS', 'IGS_FI_FCM_AC_FK');
190         IGS_GE_MSG_STACK.ADD;
191       App_Exception.Raise_Exception;
192       Return;
193     END IF;
194     Close cur_rowid;
195   END GET_FK_IGS_AD_CAT;
196   PROCEDURE GET_FK_IGS_FI_FEE_CAT (
197     x_fee_cat IN VARCHAR2
198     ) AS
199     CURSOR cur_rowid IS
200       SELECT   rowid
201       FROM     IGS_FI_FEE_CAT_MAP
202       WHERE    fee_cat = x_fee_cat ;
203     lv_rowid cur_rowid%RowType;
204   BEGIN
205     Open cur_rowid;
206     Fetch cur_rowid INTO lv_rowid;
207     IF (cur_rowid%FOUND) THEN
208       Close cur_rowid;
209       Fnd_Message.Set_Name ('IGS', 'IGS_FI_FCM_FC_FK');
210         IGS_GE_MSG_STACK.ADD;
211       App_Exception.Raise_Exception;
212       Return;
213     END IF;
214     Close cur_rowid;
215   END GET_FK_IGS_FI_FEE_CAT;
216   PROCEDURE Before_DML (
217     p_action IN VARCHAR2,
218     x_rowid IN VARCHAR2 DEFAULT NULL,
219     x_fee_cat IN VARCHAR2 DEFAULT NULL,
220     x_admission_cat IN VARCHAR2 DEFAULT NULL,
221     x_dflt_cat_ind IN VARCHAR2 DEFAULT NULL,
222     x_creation_date IN DATE DEFAULT NULL,
223     x_created_by IN NUMBER DEFAULT NULL,
224     x_last_update_date IN DATE DEFAULT NULL,
225     x_last_updated_by IN NUMBER DEFAULT NULL,
226     x_last_update_login IN NUMBER DEFAULT NULL
227   ) AS
228   BEGIN
229     Set_Column_Values (
230       p_action,
231       x_rowid,
232       x_fee_cat,
233       x_admission_cat,
234       x_dflt_cat_ind,
235       x_creation_date,
236       x_created_by,
237       x_last_update_date,
238       x_last_updated_by,
239       x_last_update_login
240     );
241     IF (p_action = 'INSERT') THEN
242       -- Call all the procedures related to Before Insert.
243       BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
244 		IF Get_PK_For_Validation ( new_references.fee_cat,
245 				new_references.admission_cat  )
246 				THEN
247 			  		Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
248                                 IGS_GE_MSG_STACK.ADD;
249 	          		App_Exception.Raise_Exception;
250 		END IF;
251 	    Check_Constraints;
252         Check_Parent_Existance;
253     ELSIF (p_action = 'UPDATE') THEN
254       -- Call all the procedures related to Before Update.
255     	BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
256 		Check_Constraints;
257 		Check_Parent_Existance;
258     ELSIF (p_action = 'DELETE') THEN
259       -- Call all the procedures related to Before Delete.
260       BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
261 	ELSIF (p_action = 'VALIDATE_INSERT') THEN
262       -- Call all the procedures related to Before Insert.
263 		IF Get_PK_For_Validation ( new_references.fee_cat,
264 			new_references.admission_cat  )
265 			THEN
266 		  		Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
267                                 IGS_GE_MSG_STACK.ADD;
268           		App_Exception.Raise_Exception;
269 		END IF;
270 		Check_Constraints;
271     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
272 	 	Check_Constraints;
273     END IF;
274   END Before_DML;
275 procedure INSERT_ROW (
276   X_ROWID in out NOCOPY VARCHAR2,
277   X_FEE_CAT in VARCHAR2,
278   X_ADMISSION_CAT in VARCHAR2,
279   X_DFLT_CAT_IND in VARCHAR2,
280   X_MODE in VARCHAR2 default 'R'
281   ) AS
282     cursor C is select ROWID from IGS_FI_FEE_CAT_MAP
283       where FEE_CAT = X_FEE_CAT
284       and ADMISSION_CAT = X_ADMISSION_CAT;
285     X_LAST_UPDATE_DATE DATE;
286     X_LAST_UPDATED_BY NUMBER;
287     X_LAST_UPDATE_LOGIN NUMBER;
288 begin
289   X_LAST_UPDATE_DATE := SYSDATE;
290   if(X_MODE = 'I') then
291     X_LAST_UPDATED_BY := 1;
292     X_LAST_UPDATE_LOGIN := 0;
293   elsif (X_MODE = 'R') then
294     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
295     if X_LAST_UPDATED_BY is NULL then
296       X_LAST_UPDATED_BY := -1;
297     end if;
298     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
299     if X_LAST_UPDATE_LOGIN is NULL then
300       X_LAST_UPDATE_LOGIN := -1;
301     end if;
302   else
303     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
304     IGS_GE_MSG_STACK.ADD;
305     app_exception.raise_exception;
306   end if;
307  Before_DML(
308   p_action=>'INSERT',
309   x_rowid=>X_ROWID,
310   x_admission_cat=>X_ADMISSION_CAT,
311   x_dflt_cat_ind=>NVL(X_DFLT_CAT_IND,'N'),
312   x_fee_cat=>X_FEE_CAT,
313   x_creation_date=>X_LAST_UPDATE_DATE,
314   x_created_by=>X_LAST_UPDATED_BY,
315   x_last_update_date=>X_LAST_UPDATE_DATE,
316   x_last_updated_by=>X_LAST_UPDATED_BY,
317   x_last_update_login=>X_LAST_UPDATE_LOGIN
318 );
319   insert into IGS_FI_FEE_CAT_MAP (
320     FEE_CAT,
321     ADMISSION_CAT,
322     DFLT_CAT_IND,
323     CREATION_DATE,
324     CREATED_BY,
325     LAST_UPDATE_DATE,
326     LAST_UPDATED_BY,
327     LAST_UPDATE_LOGIN
328   ) values (
329     NEW_REFERENCES.FEE_CAT,
330     NEW_REFERENCES.ADMISSION_CAT,
331     NEW_REFERENCES.DFLT_CAT_IND,
332     X_LAST_UPDATE_DATE,
333     X_LAST_UPDATED_BY,
334     X_LAST_UPDATE_DATE,
335     X_LAST_UPDATED_BY,
336     X_LAST_UPDATE_LOGIN
337   );
338   open c;
339   fetch c into X_ROWID;
340   if (c%notfound) then
341     close c;
342     raise no_data_found;
343   end if;
344   close c;
345 end INSERT_ROW;
346 procedure LOCK_ROW (
347   X_ROWID in VARCHAR2,
348   X_FEE_CAT in VARCHAR2,
349   X_ADMISSION_CAT in VARCHAR2,
350   X_DFLT_CAT_IND in VARCHAR2
351 ) AS
352   cursor c1 is select
353       DFLT_CAT_IND
354     from IGS_FI_FEE_CAT_MAP
355     where ROWID = X_ROWID
356     for update nowait;
357   tlinfo c1%rowtype;
358 begin
359   open c1;
360   fetch c1 into tlinfo;
361   if (c1%notfound) then
362     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
363     IGS_GE_MSG_STACK.ADD;
364     app_exception.raise_exception;
365     close c1;
366     return;
367   end if;
368   close c1;
369   if ( (tlinfo.DFLT_CAT_IND = X_DFLT_CAT_IND)
370   ) then
371     null;
372   else
373     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
374     IGS_GE_MSG_STACK.ADD;
375     app_exception.raise_exception;
376   end if;
377   return;
378 end LOCK_ROW;
379 procedure UPDATE_ROW (
380   X_ROWID in VARCHAR2,
381   X_FEE_CAT in VARCHAR2,
382   X_ADMISSION_CAT in VARCHAR2,
383   X_DFLT_CAT_IND in VARCHAR2,
384   X_MODE in VARCHAR2 default 'R'
385   ) AS
386     X_LAST_UPDATE_DATE DATE;
387     X_LAST_UPDATED_BY NUMBER;
388     X_LAST_UPDATE_LOGIN NUMBER;
389 begin
390   X_LAST_UPDATE_DATE := SYSDATE;
391   if(X_MODE = 'I') then
392     X_LAST_UPDATED_BY := 1;
393     X_LAST_UPDATE_LOGIN := 0;
394   elsif (X_MODE = 'R') then
395     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
396     if X_LAST_UPDATED_BY is NULL then
397       X_LAST_UPDATED_BY := -1;
398     end if;
399     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
400     if X_LAST_UPDATE_LOGIN is NULL then
401       X_LAST_UPDATE_LOGIN := -1;
402     end if;
403   else
404     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
405     IGS_GE_MSG_STACK.ADD;
406     app_exception.raise_exception;
407   end if;
408  Before_DML(
409   p_action=>'UPDATE',
410   x_rowid=>X_ROWID,
411   x_admission_cat=>X_ADMISSION_CAT,
412   x_dflt_cat_ind=>X_DFLT_CAT_IND,
413   x_fee_cat=>X_FEE_CAT,
414   x_creation_date=>X_LAST_UPDATE_DATE,
415   x_created_by=>X_LAST_UPDATED_BY,
416   x_last_update_date=>X_LAST_UPDATE_DATE,
417   x_last_updated_by=>X_LAST_UPDATED_BY,
418   x_last_update_login=>X_LAST_UPDATE_LOGIN
419 );
420   update IGS_FI_FEE_CAT_MAP set
421     DFLT_CAT_IND = NEW_REFERENCES.DFLT_CAT_IND,
422     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
423     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
424     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
425   where ROWID = X_ROWID;
426   if (sql%notfound) then
427     raise no_data_found;
428   end if;
429 end UPDATE_ROW;
430 procedure ADD_ROW (
431   X_ROWID in out NOCOPY VARCHAR2,
432   X_FEE_CAT in VARCHAR2,
433   X_ADMISSION_CAT in VARCHAR2,
434   X_DFLT_CAT_IND in VARCHAR2,
435   X_MODE in VARCHAR2 default 'R'
436   ) AS
437   cursor c1 is select rowid from IGS_FI_FEE_CAT_MAP
438      where FEE_CAT = X_FEE_CAT
439      and ADMISSION_CAT = X_ADMISSION_CAT
440   ;
441 begin
442   open c1;
443   fetch c1 into X_ROWID;
444   if (c1%notfound) then
445     close c1;
446     INSERT_ROW (
447      X_ROWID,
448      X_FEE_CAT,
449      X_ADMISSION_CAT,
450      X_DFLT_CAT_IND,
451      X_MODE);
452     return;
453   end if;
454   close c1;
455   UPDATE_ROW (
456    X_ROWID,
457    X_FEE_CAT,
458    X_ADMISSION_CAT,
459    X_DFLT_CAT_IND,
460    X_MODE);
461 end ADD_ROW;
462 procedure DELETE_ROW (
463   X_ROWID in VARCHAR2
464 ) AS
465 begin
466 Before_DML (
467  p_action => 'DELETE',
468  x_rowid => X_ROWID
469 );
470   delete from IGS_FI_FEE_CAT_MAP
471   where ROWID = X_ROWID;
472   if (sql%notfound) then
473     raise no_data_found;
474   end if;
475 end DELETE_ROW;
476 end IGS_FI_FEE_CAT_MAP_PKG;