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_STR_STAT_PKG

Source


1 package body IGS_FI_FEE_STR_STAT_PKG AS
2   /* $Header: IGSSI35B.pls 115.4 2002/11/29 03:45:04 nsidana ship $*/
3   l_rowid VARCHAR2(25);
4   old_references IGS_FI_FEE_STR_STAT%RowType;
5   new_references IGS_FI_FEE_STR_STAT%RowType;
6   PROCEDURE Set_Column_Values (
7     p_action IN VARCHAR2,
8     x_rowid IN VARCHAR2 DEFAULT NULL,
9     x_fee_structure_status IN VARCHAR2 DEFAULT NULL,
10     x_description IN VARCHAR2 DEFAULT NULL,
11     x_s_fee_structure_status IN VARCHAR2 DEFAULT NULL,
12     x_closed_ind 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     CURSOR cur_old_ref_values IS
20       SELECT   *
21       FROM     IGS_FI_FEE_STR_STAT
22       WHERE    rowid = x_rowid;
23   BEGIN
24     l_rowid := x_rowid;
25     -- Code for setting the Old and New Reference Values.
26     -- Populate Old Values.
27     Open cur_old_ref_values;
28     Fetch cur_old_ref_values INTO old_references;
29     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
30       Close cur_old_ref_values;
31       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
32       IGS_GE_MSG_STACK.ADD;
33       App_Exception.Raise_Exception;
34       Return;
35     END IF;
36     Close cur_old_ref_values;
37     -- Populate New Values.
38     new_references.fee_structure_status := x_fee_structure_status;
39     new_references.description := x_description;
40     new_references.s_fee_structure_status := x_s_fee_structure_status;
41     new_references.closed_ind := x_closed_ind;
42     IF (p_action = 'UPDATE') THEN
43       new_references.creation_date := old_references.creation_date;
44       new_references.created_by := old_references.created_by;
45     ELSE
46       new_references.creation_date := x_creation_date;
47       new_references.created_by := x_created_by;
48     END IF;
49     new_references.last_update_date := x_last_update_date;
50     new_references.last_updated_by := x_last_updated_by;
51     new_references.last_update_login := x_last_update_login;
52   END Set_Column_Values;
53 
54 PROCEDURE Check_Constraints (
55      Column_Name	IN	VARCHAR2	DEFAULT NULL,
56      Column_Value 	IN	VARCHAR2	DEFAULT NULL
57      )AS
58  /*----------------------------------------------------------------------------
59   ||  Created By :
60   ||  Created On :
61   ||  Purpose :
62   ||  Known limitations, enhancements or remarks :
63   ||  Change History :
64   ||  Who             When            What
65   ||  (reverse chronological order - newest change first)
66   ||  vvutukur        11-May-2002     removed upper check constraint on fee category column.bug#2344826.
67   ----------------------------------------------------------------------------*/
68   BEGIN
69 
70   IF Column_Name is NULL THEN
71     	NULL;
72     ELSIF upper(Column_Name) = 'CLOSED_IND' then
73     	new_references.closed_ind := Column_Value;
74     ELSIF upper(Column_Name) = 'DESCRIPTION' then
75     	new_references.description := Column_Value;
76     ELSIF upper(Column_Name) = 'S_FEE_STRUCTURE_STATUS' then
77       	new_references.s_fee_structure_status := Column_Value;
78   END IF;
79 
80   	IF upper(Column_Name) = 'CLOSED_IND' OR
81     		column_name is NULL THEN
82   		IF new_references.closed_ind <> 'Y' AND new_references.closed_ind <> 'N' THEN
83   			Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
84                         IGS_GE_MSG_STACK.ADD;
85   			App_Exception.Raise_Exception;
86   		END IF;
87 	END IF;
88 
89 	IF upper(Column_Name) = 'S_FEE_STRUCTURE_STATUS' OR
90 	    		column_name is NULL THEN
91 	  		IF new_references.s_fee_structure_status <> 'ACTIVE' AND
92 			   new_references.s_fee_structure_status <> 'INACTIVE' AND
93 			   new_references.s_fee_structure_status <> 'PLANNED'  THEN
94 	  			Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
95 				IGS_GE_MSG_STACK.ADD;
96 	  			App_Exception.Raise_Exception;
97 	  		END IF;
98 	END IF;
99   END Check_Constraints;
100     PROCEDURE Check_Child_Existance AS
101   BEGIN
102     IGS_FI_F_CAT_CA_INST_PKG.GET_FK_IGS_FI_FEE_STR_STAT (
103       old_references.fee_structure_status
104       );
105     IGS_FI_F_CAT_FEE_LBL_PKG.GET_FK_IGS_FI_FEE_STR_STAT (
106       old_references.fee_structure_status
107       );
108     IGS_FI_F_TYP_CA_INST_PKG.GET_FK_IGS_FI_FEE_STR_STAT (
109       old_references.fee_structure_status
110       );
111   END Check_Child_Existance;
112 FUNCTION Get_PK_For_Validation (
113     x_fee_structure_status IN VARCHAR2
114     ) RETURN BOOLEAN AS
115     CURSOR cur_rowid IS
116       SELECT   rowid
117       FROM     IGS_FI_FEE_STR_STAT
118       WHERE    fee_structure_status = x_fee_structure_status
119       FOR UPDATE NOWAIT;
120     lv_rowid cur_rowid%RowType;
121   BEGIN
122     Open cur_rowid;
123     Fetch cur_rowid INTO lv_rowid;
124 	 IF (cur_rowid%FOUND) THEN
125        Close cur_rowid;
126        Return (TRUE);
127 	 ELSE
128        Close cur_rowid;
129        Return (FALSE);
130 	 END IF;
131   END Get_PK_For_Validation;
132   PROCEDURE Before_DML (
133     p_action IN VARCHAR2,
134     x_rowid IN  VARCHAR2 DEFAULT NULL,
135     x_fee_structure_status IN VARCHAR2 DEFAULT NULL,
136     x_description IN VARCHAR2 DEFAULT NULL,
137     x_s_fee_structure_status IN VARCHAR2 DEFAULT NULL,
138     x_closed_ind IN VARCHAR2 DEFAULT NULL,
139     x_creation_date IN DATE DEFAULT NULL,
140     x_created_by IN NUMBER DEFAULT NULL,
141     x_last_update_date IN DATE DEFAULT NULL,
142     x_last_updated_by IN NUMBER DEFAULT NULL,
143     x_last_update_login IN NUMBER DEFAULT NULL
144   ) AS
145   BEGIN
146     Set_Column_Values (
147       p_action,
148       x_rowid,
149       x_fee_structure_status,
150       x_description,
151       x_s_fee_structure_status,
152       x_closed_ind,
153       x_creation_date,
154       x_created_by,
155       x_last_update_date,
156       x_last_updated_by,
157       x_last_update_login
158     );
159     IF (p_action = 'INSERT') THEN
160       -- Call all the procedures related to Before Insert.
161 	  IF Get_PK_For_Validation ( new_references.fee_structure_status) THEN
162 	  	  Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
163 	          IGS_GE_MSG_STACK.ADD;
164           App_Exception.Raise_Exception;
165 	  END IF;
166       Check_Constraints;
167     ELSIF (p_action = 'UPDATE') THEN
168       -- Call all the procedures related to Before Update.
169       Check_Constraints;
170     ELSIF (p_action = 'DELETE') THEN
171       -- Call all the procedures related to Before Delete.
172       Check_Child_Existance;
173    ELSIF (p_action = 'VALIDATE_INSERT') THEN
174       -- Call all the procedures related to Before Insert.
175 	  IF Get_PK_For_Validation ( new_references.fee_structure_status) THEN
176 	  	  Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
177 		  IGS_GE_MSG_STACK.ADD;
178           App_Exception.Raise_Exception;
179 	  END IF;
180 			Check_Constraints;
181     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
182 	 	Check_Constraints;
183     ELSIF (p_action = 'VALIDATE_DELETE') THEN
184 		 	Check_Child_Existance;
185     END IF;
186   END Before_DML;
187 
188 procedure INSERT_ROW (
189   X_ROWID in out NOCOPY VARCHAR2,
190   X_FEE_STRUCTURE_STATUS in VARCHAR2,
191   X_DESCRIPTION in VARCHAR2,
192   X_S_FEE_STRUCTURE_STATUS in VARCHAR2,
193   X_CLOSED_IND in VARCHAR2,
194   X_MODE in VARCHAR2 default 'R'
195   ) AS
196     cursor C is select ROWID from IGS_FI_FEE_STR_STAT
197       where FEE_STRUCTURE_STATUS = X_FEE_STRUCTURE_STATUS;
198     X_LAST_UPDATE_DATE DATE;
199     X_LAST_UPDATED_BY NUMBER;
200     X_LAST_UPDATE_LOGIN NUMBER;
201 begin
202   X_LAST_UPDATE_DATE := SYSDATE;
203   if(X_MODE = 'I') then
204     X_LAST_UPDATED_BY := 1;
205     X_LAST_UPDATE_LOGIN := 0;
206   elsif (X_MODE = 'R') then
207     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
208     if X_LAST_UPDATED_BY is NULL then
209       X_LAST_UPDATED_BY := -1;
210     end if;
211     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
212     if X_LAST_UPDATE_LOGIN is NULL then
213       X_LAST_UPDATE_LOGIN := -1;
214     end if;
215   else
216     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
217     IGS_GE_MSG_STACK.ADD;
218     app_exception.raise_exception;
219   end if;
220 Before_DML(
221  p_action=>'INSERT',
222  x_rowid=>X_ROWID,
223  x_closed_ind=>NVL(X_CLOSED_IND,'N'),
224  x_description=>X_DESCRIPTION,
225  x_fee_structure_status=>X_FEE_STRUCTURE_STATUS,
226  x_s_fee_structure_status=>X_S_FEE_STRUCTURE_STATUS,
227  x_creation_date=>X_LAST_UPDATE_DATE,
228  x_created_by=>X_LAST_UPDATED_BY,
229  x_last_update_date=>X_LAST_UPDATE_DATE,
230  x_last_updated_by=>X_LAST_UPDATED_BY,
231  x_last_update_login=>X_LAST_UPDATE_LOGIN
232 );
233   insert into IGS_FI_FEE_STR_STAT (
234     FEE_STRUCTURE_STATUS,
235     DESCRIPTION,
236     S_FEE_STRUCTURE_STATUS,
237     CLOSED_IND,
238     CREATION_DATE,
239     CREATED_BY,
240     LAST_UPDATE_DATE,
241     LAST_UPDATED_BY,
242     LAST_UPDATE_LOGIN
243   ) values (
244     NEW_REFERENCES.FEE_STRUCTURE_STATUS,
245     NEW_REFERENCES.DESCRIPTION,
246     NEW_REFERENCES.S_FEE_STRUCTURE_STATUS,
247     NEW_REFERENCES.CLOSED_IND,
248     X_LAST_UPDATE_DATE,
249     X_LAST_UPDATED_BY,
250     X_LAST_UPDATE_DATE,
251     X_LAST_UPDATED_BY,
252     X_LAST_UPDATE_LOGIN
253   );
254   open c;
255   fetch c into X_ROWID;
256   if (c%notfound) then
257     close c;
258     raise no_data_found;
259   end if;
260   close c;
261 end INSERT_ROW;
262 procedure LOCK_ROW (
263   X_ROWID in VARCHAR2,
264   X_FEE_STRUCTURE_STATUS in VARCHAR2,
265   X_DESCRIPTION in VARCHAR2,
266   X_S_FEE_STRUCTURE_STATUS in VARCHAR2,
267   X_CLOSED_IND in VARCHAR2
268 ) AS
269   cursor c1 is select
270       DESCRIPTION,
271       S_FEE_STRUCTURE_STATUS,
272       CLOSED_IND
273     from IGS_FI_FEE_STR_STAT
274     where ROWID = X_ROWID
275     for update nowait;
276   tlinfo c1%rowtype;
277 begin
278   open c1;
279   fetch c1 into tlinfo;
280   if (c1%notfound) then
281     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
282     IGS_GE_MSG_STACK.ADD;
283     app_exception.raise_exception;
284     close c1;
285     return;
286   end if;
287   close c1;
288   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
289       AND (tlinfo.S_FEE_STRUCTURE_STATUS = X_S_FEE_STRUCTURE_STATUS)
290       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
291   ) then
292     null;
293   else
294     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
295     IGS_GE_MSG_STACK.ADD;
296     app_exception.raise_exception;
297   end if;
298   return;
299 end LOCK_ROW;
300 procedure UPDATE_ROW (
301   X_ROWID in VARCHAR2,
302   X_FEE_STRUCTURE_STATUS in VARCHAR2,
303   X_DESCRIPTION in VARCHAR2,
304   X_S_FEE_STRUCTURE_STATUS in VARCHAR2,
305   X_CLOSED_IND in VARCHAR2,
306   X_MODE in VARCHAR2 default 'R'
307   ) AS
308     X_LAST_UPDATE_DATE DATE;
309     X_LAST_UPDATED_BY NUMBER;
310     X_LAST_UPDATE_LOGIN NUMBER;
311 begin
312   X_LAST_UPDATE_DATE := SYSDATE;
313   if(X_MODE = 'I') then
314     X_LAST_UPDATED_BY := 1;
315     X_LAST_UPDATE_LOGIN := 0;
316   elsif (X_MODE = 'R') then
317     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
318     if X_LAST_UPDATED_BY is NULL then
319       X_LAST_UPDATED_BY := -1;
320     end if;
321     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
322     if X_LAST_UPDATE_LOGIN is NULL then
323       X_LAST_UPDATE_LOGIN := -1;
324     end if;
325   else
326     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
327     IGS_GE_MSG_STACK.ADD;
328     app_exception.raise_exception;
329   end if;
330 Before_DML(
331  p_action=>'UPDATE',
332  x_rowid=>X_ROWID,
333  x_closed_ind=>X_CLOSED_IND,
334  x_description=>X_DESCRIPTION,
335  x_fee_structure_status=>X_FEE_STRUCTURE_STATUS,
336  x_s_fee_structure_status=>X_S_FEE_STRUCTURE_STATUS,
337  x_creation_date=>X_LAST_UPDATE_DATE,
338  x_created_by=>X_LAST_UPDATED_BY,
339  x_last_update_date=>X_LAST_UPDATE_DATE,
340  x_last_updated_by=>X_LAST_UPDATED_BY,
341  x_last_update_login=>X_LAST_UPDATE_LOGIN
342 );
343   update IGS_FI_FEE_STR_STAT
344   set
345     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
346     S_FEE_STRUCTURE_STATUS = NEW_REFERENCES.S_FEE_STRUCTURE_STATUS,
347     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
348     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
349     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
350     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
351   where ROWID = X_ROWID;
352   if (sql%notfound) then
353     raise no_data_found;
354   end if;
355 end UPDATE_ROW;
356 procedure ADD_ROW (
357   X_ROWID in out NOCOPY VARCHAR2,
358   X_FEE_STRUCTURE_STATUS in VARCHAR2,
359   X_DESCRIPTION in VARCHAR2,
360   X_S_FEE_STRUCTURE_STATUS in VARCHAR2,
361   X_CLOSED_IND in VARCHAR2,
362   X_MODE in VARCHAR2 default 'R'
363   ) AS
364   cursor c1 is select rowid from IGS_FI_FEE_STR_STAT
365      where FEE_STRUCTURE_STATUS = X_FEE_STRUCTURE_STATUS
366   ;
367 begin
368   open c1;
369   fetch c1 into X_ROWID;
370   if (c1%notfound) then
371     close c1;
372     INSERT_ROW (
373      X_ROWID,
374      X_FEE_STRUCTURE_STATUS,
375      X_DESCRIPTION,
376      X_S_FEE_STRUCTURE_STATUS,
377      X_CLOSED_IND,
378      X_MODE);
379     return;
380   end if;
381   close c1;
382   UPDATE_ROW (
383    X_ROWID,
384    X_FEE_STRUCTURE_STATUS,
385    X_DESCRIPTION,
386    X_S_FEE_STRUCTURE_STATUS,
387    X_CLOSED_IND,
388    X_MODE);
389 end ADD_ROW;
390 procedure DELETE_ROW (
391   X_ROWID in VARCHAR2
392 ) AS
393 begin
394 Before_DML (
395  p_action => 'DELETE',
396  x_rowid => X_ROWID
397 );
398   delete from IGS_FI_FEE_STR_STAT
399   where ROWID = X_ROWID;
400   if (sql%notfound) then
401     raise no_data_found;
402   end if;
403 end DELETE_ROW;
404 end IGS_FI_FEE_STR_STAT_PKG;