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_PKG

Source


1 PACKAGE BODY igs_fi_fee_cat_pkg AS
2  /* $Header: IGSSI23B.pls 115.19 2003/12/05 05:45:48 ckasu ship $ */
3   l_rowid VARCHAR2(25);
4   old_references IGS_FI_FEE_CAT_ALL%RowType;
5   new_references IGS_FI_FEE_CAT_ALL%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 ,
10     x_fee_cat IN VARCHAR2 ,
11     x_description IN VARCHAR2 ,
12     x_currency_cd IN VARCHAR2 ,
13     x_closed_ind IN VARCHAR2 ,
14     x_org_id IN NUMBER ,
15     x_creation_date IN DATE ,
16     x_created_by IN NUMBER ,
17     x_last_update_date IN DATE ,
18     x_last_updated_by IN NUMBER ,
19     x_last_update_login IN NUMBER
20   ) AS
21     CURSOR cur_old_ref_values IS
22       SELECT   *
23       FROM     IGS_FI_FEE_CAT_ALL
24       WHERE    rowid = x_rowid;
25   BEGIN
26     l_rowid := x_rowid;
27     -- Code for setting the Old and New Reference Values.
28     -- Populate Old Values.
29     Open cur_old_ref_values;
30     Fetch cur_old_ref_values INTO old_references;
31     IF (cur_old_ref_values%NOTFOUND) AND (p_action not in ('INSERT', 'VALIDATE_INSERT')) THEN
32       Close cur_old_ref_values;
33       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
34       IGS_GE_MSG_STACK.ADD;
35       App_Exception.Raise_Exception;
36       Return;
37     END IF;
38     Close cur_old_ref_values;
39     -- Populate New Values.
40     new_references.fee_cat := x_fee_cat;
41     new_references.description := x_description;
42     new_references.currency_cd := x_currency_cd;
43     new_references.closed_ind := x_closed_ind;
44     IF (p_action = 'UPDATE') THEN
45       new_references.creation_date := old_references.creation_date;
46       new_references.created_by := old_references.created_by;
47     ELSE
48       new_references.creation_date := x_creation_date;
49       new_references.created_by := x_created_by;
50     END IF;
51     new_references.org_id := x_org_id;
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   END Set_Column_Values;
56   -- Trigger description :-
57   -- "OSS_TST".trg_fc_br_iud
58   -- BEFORE INSERT OR DELETE OR UPDATE
59   -- ON IGS_FI_FEE_CAT_ALL
60   -- FOR EACH ROW
61   PROCEDURE BeforeRowInsertUpdateDelete1(
62     p_inserting IN BOOLEAN ,
63     p_updating IN BOOLEAN ,
64     p_deleting IN BOOLEAN
65     ) AS
66 	v_message_name varchar2(30);
67   BEGIN
68 	-- Validate Fee Category can be closed.
69 	IF (p_updating AND (old_references.closed_ind <> new_references.closed_ind)) THEN
70 		IF IGS_FI_VAL_FC.finp_val_fc_clsd_upd (
71 					new_references.fee_cat,
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     -- Fee Category records can be deleted logically by  making closed_ind as 'Y'
81     --    No physical deletion is allowed. As a part of Bug # 2729919
82     -- Preventing deletion of the Fee Category records.
83     IF p_deleting = TRUE THEN
84       FND_MESSAGE.SET_NAME('IGS','IGS_FI_DEL_NOT_ALLWD');
85       IGS_GE_MSG_STACK.ADD;
86       APP_EXCEPTION.RAISE_EXCEPTION;
87     END IF;
88 
89   END BeforeRowInsertUpdateDelete1;
90 
91    PROCEDURE Check_Constraints (
92      Column_Name	IN	VARCHAR2	,
93      Column_Value 	IN	VARCHAR2
94      )AS
95    /*----------------------------------------------------------------------------
96   ||  Created By :
97   ||  Created On :
98   ||  Purpose :
99   ||  Known limitations, enhancements or remarks :
100   ||  Change History :
101   ||  Who             When            What
102   ||  (reverse chronological order - newest change first)
103   ||  vvutukur        12-May-2002     removed upper check constraint on fee category column.bug#2344826.
104   ----------------------------------------------------------------------------*/
105    BEGIN
106      IF Column_Name is NULL THEN
107        NULL;
108      ELSIF upper(Column_Name) = 'CLOSED_IND' then
109        new_references.closed_ind := Column_Value;
110      ELSIF upper(Column_Name) = 'CURRENCY_CD' then
111        new_references.currency_cd := Column_Value;
112      END IF;
113 
114      IF upper(Column_Name) = 'CLOSED_IND' OR
115         column_name is NULL THEN
116        IF new_references.closed_ind <> 'N' AND new_references.closed_ind <> 'Y' THEN
117          Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
118          IGS_GE_MSG_STACK.ADD;
119 	 App_Exception.Raise_Exception;
120        END IF;
121      END IF;
122 
123      IF upper(Column_Name) = 'CURRENCY_CD' OR
124         column_name is NULL THEN
125         IF new_references.currency_cd <> UPPER(new_references.currency_cd) 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 
132    END Check_Constraints;
133 
134 
135   PROCEDURE Check_Child_Existance AS
136   ------------------------------------------------------------------
137   --Change History:
138 
139   --Who         When            What
140   --ckasu     04-Dec-2003      Added IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_FI_FEE_CAT
141   --                           for Term Records Build Bug# 2829263
142 
143   -------------------------------------------------------------------
144 
145   BEGIN
146     IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_FI_FEE_CAT (
147       old_references.fee_cat
148       );
149     IGS_FI_F_CAT_CA_INST_PKG.GET_FK_IGS_FI_FEE_CAT (
150       old_references.fee_cat
151       );
152     IGS_FI_FEE_CAT_MAP_PKG.GET_FK_IGS_FI_FEE_CAT (
153       old_references.fee_cat
154       );
155     IGS_EN_STDNT_PS_ATT_PKG.GET_FK_IGS_FI_FEE_CAT (
156       old_references.fee_cat
157       );
158     IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_FI_FEE_CAT (
159       old_references.fee_cat
160       );
161   END check_child_existance;
162 
163 
164   FUNCTION Get_PK_For_Validation (
165     x_fee_cat IN VARCHAR2
166     ) RETURN BOOLEAN AS
167     -- Bug# 2729919, removed 'FOR UPDATE NOWAIT' clause is removed from the cursor.
168     CURSOR cur_rowid IS
169       SELECT   rowid
170       FROM     IGS_FI_FEE_CAT_ALL
171       WHERE    fee_cat = x_fee_cat;
172     lv_rowid cur_rowid%RowType;
173   BEGIN
174     Open cur_rowid;
175     Fetch cur_rowid INTO lv_rowid;
176 	 IF (cur_rowid%FOUND) THEN
177        Close cur_rowid;
178        Return (TRUE);
179 	 ELSE
180        Close cur_rowid;
181        Return (FALSE);
182 	 END IF;
183   END Get_PK_For_Validation;
184 
185   PROCEDURE Before_DML (
186     p_action IN VARCHAR2,
187     x_rowid IN VARCHAR2 ,
188     x_fee_cat IN VARCHAR2 ,
189     x_description IN VARCHAR2 ,
190     x_currency_cd IN VARCHAR2 ,
191     x_closed_ind IN VARCHAR2 ,
192     x_org_id IN NUMBER ,
193     x_creation_date IN DATE ,
194     x_created_by IN NUMBER ,
195     x_last_update_date IN DATE ,
196     x_last_updated_by IN NUMBER ,
197     x_last_update_login IN NUMBER
198   ) AS
199   ------------------------------------------------------------------
200   --
201   --Known limitations/enhancements and/or remarks:
202   --
203   --Change History:
204   --Who         When            What
205   --smadathi    06-Nov-2002     Enh. Bug 2584986.Removed refereces to check_parent_existance.
206   --                            procedure call.
207   -------------------------------------------------------------------
208   BEGIN
209     Set_Column_Values (
210       p_action,
211       x_rowid,
212       x_fee_cat,
213       x_description,
214       x_currency_cd,
215       x_closed_ind,
216       x_org_id,
217       x_creation_date,
218       x_created_by,
219       x_last_update_date,
220       x_last_updated_by,
221       x_last_update_login
222     );
223     IF (p_action = 'INSERT') THEN
224       -- Call all the procedures related to Before Insert.
225       BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
226                                      p_updating  => FALSE,
227                                      p_deleting  => FALSE
228                                    );
229       IF Get_PK_For_Validation ( new_references.fee_cat ) THEN
230 	  	Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
231         IGS_GE_MSG_STACK.ADD;
232         App_Exception.Raise_Exception;
233 	  END IF;
234 	  Check_Constraints;
235 
236     ELSIF (p_action = 'UPDATE') THEN
237       -- Call all the procedures related to Before Update.
238       BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
239                                      p_updating  => TRUE,
240                                      p_deleting  => FALSE
241                                    );
242 	  Check_Constraints;
243     ELSIF (p_action = 'DELETE') THEN
244       -- Call all the procedures related to Before Delete.
245       BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
246                                      p_updating  => FALSE,
247                                      p_deleting  => TRUE
248                                    );
249       Check_Child_Existance;
250 	ELSIF (p_action = 'VALIDATE_INSERT') THEN
251       -- Call all the procedures related to Before Insert.
252       IF Get_PK_For_Validation ( new_references.fee_cat ) THEN
253 	  	Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
254                 IGS_GE_MSG_STACK.ADD;
255         App_Exception.Raise_Exception;
256 	  END IF;
257       Check_Constraints;
258 	ELSIF (p_action = 'VALIDATE_UPDATE') THEN
259       -- Call all the procedures related to Before UPdate.
260       Check_Constraints;
261 	ELSIF (p_action = 'VALIDATE_DELETE') THEN
262       -- Call all the procedures related to Before Delete
263       BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
264                                      p_updating  => FALSE,
265                                      p_deleting  => TRUE
266                                    );
267       Check_Child_Existance;
268     END IF;
269   END Before_DML;
270 procedure INSERT_ROW (
271   X_ROWID in out NOCOPY VARCHAR2,
272   X_FEE_CAT in VARCHAR2,
273   X_DESCRIPTION in VARCHAR2,
274   X_CURRENCY_CD in VARCHAR2,
275   X_CLOSED_IND in VARCHAR2,
276   X_ORG_ID in NUMBER ,
277   X_MODE in VARCHAR2
278   ) AS
279     cursor C is select ROWID from IGS_FI_FEE_CAT_ALL
280       where FEE_CAT = X_FEE_CAT;
281     X_LAST_UPDATE_DATE DATE;
282     X_LAST_UPDATED_BY NUMBER;
283     X_LAST_UPDATE_LOGIN NUMBER;
284 begin
285   X_LAST_UPDATE_DATE := SYSDATE;
286   if(X_MODE = 'I') then
287     X_LAST_UPDATED_BY := 1;
288     X_LAST_UPDATE_LOGIN := 0;
289   elsif (X_MODE = 'R') then
290     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
291     if X_LAST_UPDATED_BY is NULL then
292       X_LAST_UPDATED_BY := -1;
293     end if;
294     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
295     if X_LAST_UPDATE_LOGIN is NULL then
296       X_LAST_UPDATE_LOGIN := -1;
297     end if;
298   else
299     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
300      IGS_GE_MSG_STACK.ADD;
301     app_exception.raise_exception;
302   end if;
303 Before_DML(
304  p_action=>'INSERT',
305  x_rowid=>X_ROWID,
306  x_closed_ind=>NVL(X_CLOSED_IND,'N'),
307  x_currency_cd=>X_CURRENCY_CD,
308  x_description=>X_DESCRIPTION,
309  x_fee_cat=>X_FEE_CAT,
310  x_org_id=>igs_ge_gen_003.get_org_id,
311  x_creation_date=>X_LAST_UPDATE_DATE,
312  x_created_by=>X_LAST_UPDATED_BY,
313  x_last_update_date=>X_LAST_UPDATE_DATE,
314  x_last_updated_by=>X_LAST_UPDATED_BY,
315  x_last_update_login=>X_LAST_UPDATE_LOGIN
316  );
317   insert into IGS_FI_FEE_CAT_ALL (
318     FEE_CAT,
319     DESCRIPTION,
320     CURRENCY_CD,
321     CLOSED_IND,
322     ORG_ID,
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.DESCRIPTION,
331     NEW_REFERENCES.CURRENCY_CD,
332     NEW_REFERENCES.CLOSED_IND,
333     NEW_REFERENCES.ORG_ID,
334     X_LAST_UPDATE_DATE,
335     X_LAST_UPDATED_BY,
336     X_LAST_UPDATE_DATE,
337     X_LAST_UPDATED_BY,
338     X_LAST_UPDATE_LOGIN
339   );
340   open c;
341   fetch c into X_ROWID;
342   if (c%notfound) then
343     close c;
344     raise no_data_found;
345   end if;
346   close c;
347 end INSERT_ROW;
348 procedure LOCK_ROW (
349   X_ROWID in VARCHAR2,
350   X_FEE_CAT in VARCHAR2,
351   X_DESCRIPTION in VARCHAR2,
352   X_CURRENCY_CD in VARCHAR2,
353   X_CLOSED_IND in VARCHAR2
354 ) AS
355   cursor c1 is select
356       DESCRIPTION,
357       CURRENCY_CD,
358       CLOSED_IND
359     from IGS_FI_FEE_CAT_ALL
360     where ROWID = X_ROWID
361     for update nowait;
362   tlinfo c1%rowtype;
363 begin
364   open c1;
365   fetch c1 into tlinfo;
366   if (c1%notfound) then
367     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
368      IGS_GE_MSG_STACK.ADD;
369     app_exception.raise_exception;
370     close c1;
371     return;
372   end if;
373   close c1;
374   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
375       AND ((tlinfo.CURRENCY_CD = X_CURRENCY_CD)
376            OR ((tlinfo.CURRENCY_CD is null)
377                AND (X_CURRENCY_CD is null)))
378       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
379   ) then
380     null;
381   else
382     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
383      IGS_GE_MSG_STACK.ADD;
384     app_exception.raise_exception;
385   end if;
386   return;
387 end LOCK_ROW;
388 procedure UPDATE_ROW (
389   X_ROWID in VARCHAR2,
390   X_FEE_CAT in VARCHAR2,
391   X_DESCRIPTION in VARCHAR2,
392   X_CURRENCY_CD in VARCHAR2,
393   X_CLOSED_IND in VARCHAR2,
394   X_MODE in VARCHAR2
395 
396   ) AS
397     X_LAST_UPDATE_DATE DATE;
398     X_LAST_UPDATED_BY NUMBER;
399     X_LAST_UPDATE_LOGIN NUMBER;
400 begin
401   X_LAST_UPDATE_DATE := SYSDATE;
402   if(X_MODE = 'I') then
403     X_LAST_UPDATED_BY := 1;
404     X_LAST_UPDATE_LOGIN := 0;
405   elsif (X_MODE = 'R') then
406     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
407     if X_LAST_UPDATED_BY is NULL then
408       X_LAST_UPDATED_BY := -1;
409     end if;
410     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
411     if X_LAST_UPDATE_LOGIN is NULL then
412       X_LAST_UPDATE_LOGIN := -1;
413     end if;
414   else
415     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
416      IGS_GE_MSG_STACK.ADD;
417     app_exception.raise_exception;
418   end if;
419 Before_DML(
420  p_action=>'UPDATE',
421  x_rowid=>X_ROWID,
422  x_closed_ind=>X_CLOSED_IND,
423  x_currency_cd=>X_CURRENCY_CD,
424  x_description=>X_DESCRIPTION,
425  x_fee_cat=>X_FEE_CAT,
426  x_creation_date=>X_LAST_UPDATE_DATE,
427  x_created_by=>X_LAST_UPDATED_BY,
428  x_last_update_date=>X_LAST_UPDATE_DATE,
429  x_last_updated_by=>X_LAST_UPDATED_BY,
430  x_last_update_login=>X_LAST_UPDATE_LOGIN
431  );
432   update IGS_FI_FEE_CAT_ALL set
433     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
434     CURRENCY_CD = NEW_REFERENCES.CURRENCY_CD,
435     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
436     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
437     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
438     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
439   where ROWID = X_ROWID;
440   if (sql%notfound) then
441     raise no_data_found;
442   end if;
443 end UPDATE_ROW;
444 procedure ADD_ROW (
445   X_ROWID in OUT NOCOPY VARCHAR2,
446   X_FEE_CAT in VARCHAR2,
447   X_DESCRIPTION in VARCHAR2,
448   X_CURRENCY_CD in VARCHAR2,
449   X_CLOSED_IND in VARCHAR2,
450   X_ORG_ID in NUMBER ,
451   X_MODE in VARCHAR2
452 
453   ) AS
454   cursor c1 is select rowid from IGS_FI_FEE_CAT_ALL
455      where FEE_CAT = X_FEE_CAT
456   ;
457 begin
458   open c1;
459   fetch c1 into X_ROWID;
460   if (c1%notfound) then
461     close c1;
462     INSERT_ROW (
463      X_ROWID,
464      X_FEE_CAT,
465      X_DESCRIPTION,
466      X_CURRENCY_CD,
467      X_CLOSED_IND,
468      X_ORG_ID,
469      X_MODE
470      );
471     return;
472   end if;
473   close c1;
474   UPDATE_ROW (
475    X_ROWID,
476    X_FEE_CAT,
477    X_DESCRIPTION,
478    X_CURRENCY_CD,
479    X_CLOSED_IND,
480    X_MODE
481    );
482 end ADD_ROW;
483 PROCEDURE DELETE_ROW (
484   X_ROWID in VARCHAR2
485 ) AS
486 begin
487 BEfore_DML (
488  p_action => 'DELETE',
489  x_rowid => X_ROWID
490 );
491   delete from IGS_FI_FEE_CAT_ALL
492   where ROWID = X_ROWID;
493   if (sql%notfound) then
494     raise no_data_found;
495   end if;
496 END delete_row;
497 
498 END igs_fi_fee_cat_pkg;