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_GOVT_HEC_CNTB_PKG

Source


1 package body IGS_FI_GOVT_HEC_CNTB_PKG AS
2 /* $Header: IGSSI51B.pls 115.3 2002/11/29 03:49:59 nsidana ship $*/
3   l_rowid VARCHAR2(25);
4   old_references IGS_FI_GOVT_HEC_CNTB%RowType;
5   new_references IGS_FI_GOVT_HEC_CNTB%RowType;
6   PROCEDURE Set_Column_Values (
7     p_action IN VARCHAR2,
8     x_rowid IN VARCHAR2 DEFAULT NULL,
9     x_govt_hecs_cntrbtn_band IN NUMBER DEFAULT NULL,
10     x_description IN VARCHAR2 DEFAULT NULL,
11     x_closed_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_GOVT_HEC_CNTB
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.govt_hecs_cntrbtn_band := x_govt_hecs_cntrbtn_band;
38     new_references.description := x_description;
39     new_references.closed_ind := x_closed_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   PROCEDURE Check_Constraints (
52     column_name  IN  VARCHAR2 DEFAULT NULL,
53     column_value IN  VARCHAR2 DEFAULT NULL
54   ) AS
55   BEGIN
56     IF (column_name IS NULL) THEN
57       NULL;
58     ELSIF (UPPER (column_name) = 'GOVT_HECS_CNTRBTN_BAND') THEN
59       new_references.govt_hecs_cntrbtn_band := IGS_GE_NUMBER.TO_NUM (column_value);
60     ELSIF (UPPER (column_name) = 'CLOSED_IND') THEN
61       new_references.closed_ind := column_value;
62     END IF;
63     IF ((UPPER (column_name) = 'GOVT_HECS_CNTRBTN_BAND') OR (column_name IS NULL)) THEN
64       IF ((new_references.govt_hecs_cntrbtn_band < 1) OR (new_references.govt_hecs_cntrbtn_band > 99)) THEN
65         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
66         IGS_GE_MSG_STACK.ADD;
67         App_Exception.Raise_Exception;
68       END IF;
69     END IF;
70     IF ((UPPER (column_name) = 'CLOSED_IND') OR (column_name IS NULL)) THEN
71       IF (new_references.closed_ind NOT IN ('Y', 'N')) THEN
72         Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
73         IGS_GE_MSG_STACK.ADD;
74         App_Exception.Raise_Exception;
75       END IF;
76     END IF;
77   END Check_Constraints;
78   PROCEDURE Check_Child_Existance AS
79   BEGIN
80     IGS_FI_FEE_AS_RATE_PKG.GET_FK_IGS_FI_GOVT_HEC_CNTB (
81       old_references.govt_hecs_cntrbtn_band
82       );
83     IGS_FI_GV_DSP_HEC_CN_PKG.GET_FK_IGS_FI_GOVT_HEC_CNTB (
84       old_references.govt_hecs_cntrbtn_band
85       );
86   END Check_Child_Existance;
87   FUNCTION Get_PK_For_Validation (
88     x_govt_hecs_cntrbtn_band IN NUMBER
89     ) RETURN BOOLEAN AS
90     CURSOR cur_rowid IS
91       SELECT   rowid
92       FROM     IGS_FI_GOVT_HEC_CNTB
93       WHERE    govt_hecs_cntrbtn_band = x_govt_hecs_cntrbtn_band
94       FOR UPDATE NOWAIT;
95     lv_rowid cur_rowid%RowType;
96   BEGIN
97     Open cur_rowid;
98     Fetch cur_rowid INTO lv_rowid;
99     IF (cur_rowid%FOUND) THEN
100       Close cur_rowid;
101       Return (TRUE);
102     ELSE
103       Close cur_rowid;
104       Return (FALSE);
105     END IF;
106   END Get_PK_For_Validation;
107   PROCEDURE Before_DML (
108     p_action IN VARCHAR2,
109     x_rowid IN  VARCHAR2 DEFAULT NULL,
110     x_govt_hecs_cntrbtn_band IN NUMBER DEFAULT NULL,
111     x_description IN VARCHAR2 DEFAULT NULL,
112     x_closed_ind IN VARCHAR2 DEFAULT NULL,
113     x_creation_date IN DATE DEFAULT NULL,
114     x_created_by IN NUMBER DEFAULT NULL,
115     x_last_update_date IN DATE DEFAULT NULL,
116     x_last_updated_by IN NUMBER DEFAULT NULL,
117     x_last_update_login IN NUMBER DEFAULT NULL
118   ) AS
119   BEGIN
120     Set_Column_Values (
121       p_action,
122       x_rowid,
123       x_govt_hecs_cntrbtn_band,
124       x_description,
125       x_closed_ind,
126       x_creation_date,
127       x_created_by,
128       x_last_update_date,
129       x_last_updated_by,
130       x_last_update_login
131     );
132     IF (p_action = 'INSERT') THEN
133       -- Call all the procedures related to Before Insert.
134       IF (Get_PK_For_Validation (
135             new_references.govt_hecs_cntrbtn_band
136             )) THEN
137         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
138         IGS_GE_MSG_STACK.ADD;
139         App_Exception.Raise_Exception;
140       END IF;
141       Check_Constraints;
142     ELSIF (p_action = 'UPDATE') THEN
143       -- Call all the procedures related to Before Update.
144       Check_Constraints;
145     ELSIF (p_action = 'DELETE') THEN
146       -- Call all the procedures related to Before Delete.
147       Check_Child_Existance;
148     ELSIF (p_action = 'VALIDATE_INSERT') THEN
149       IF (Get_PK_For_Validation (
150             new_references.govt_hecs_cntrbtn_band
151 		 )) THEN
152         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
153         IGS_GE_MSG_STACK.ADD;
154         App_Exception.Raise_Exception;
155       END IF;
156       Check_Constraints;
157     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
158       Check_Constraints;
159     ELSIF (p_action = 'VALIDATE_DELETE') THEN
160       Check_Child_Existance;
161     END IF;
162   END Before_DML;
163 procedure INSERT_ROW (
164   X_ROWID in out NOCOPY VARCHAR2,
165   X_GOVT_HECS_CNTRBTN_BAND in NUMBER,
166   X_DESCRIPTION in VARCHAR2,
167   X_CLOSED_IND in VARCHAR2,
168   X_MODE in VARCHAR2 default 'R'
169   ) AS
170     cursor C is select ROWID from IGS_FI_GOVT_HEC_CNTB
171       where GOVT_HECS_CNTRBTN_BAND = X_GOVT_HECS_CNTRBTN_BAND;
172     X_LAST_UPDATE_DATE DATE;
173     X_LAST_UPDATED_BY NUMBER;
174     X_LAST_UPDATE_LOGIN NUMBER;
175 begin
176   X_LAST_UPDATE_DATE := SYSDATE;
177   if(X_MODE = 'I') then
178     X_LAST_UPDATED_BY := 1;
179     X_LAST_UPDATE_LOGIN := 0;
180   elsif (X_MODE = 'R') then
181     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
182     if X_LAST_UPDATED_BY is NULL then
183       X_LAST_UPDATED_BY := -1;
184     end if;
185     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
186     if X_LAST_UPDATE_LOGIN is NULL then
187       X_LAST_UPDATE_LOGIN := -1;
188     end if;
189   else
190     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
191     IGS_GE_MSG_STACK.ADD;
192     app_exception.raise_exception;
193   end if;
194  Before_DML(
195   p_action=>'INSERT',
196   x_rowid=>X_ROWID,
197   x_closed_ind=>NVL(X_CLOSED_IND,'N'),
198   x_description=>X_DESCRIPTION,
199   x_govt_hecs_cntrbtn_band=>X_GOVT_HECS_CNTRBTN_BAND,
200   x_creation_date=>X_LAST_UPDATE_DATE,
201   x_created_by=>X_LAST_UPDATED_BY,
202   x_last_update_date=>X_LAST_UPDATE_DATE,
203   x_last_updated_by=>X_LAST_UPDATED_BY,
204   x_last_update_login=>X_LAST_UPDATE_LOGIN
205 );
206   insert into IGS_FI_GOVT_HEC_CNTB (
207     GOVT_HECS_CNTRBTN_BAND,
208     DESCRIPTION,
209     CLOSED_IND,
210     CREATION_DATE,
211     CREATED_BY,
212     LAST_UPDATE_DATE,
213     LAST_UPDATED_BY,
214     LAST_UPDATE_LOGIN
215   ) values (
216     NEW_REFERENCES.GOVT_HECS_CNTRBTN_BAND,
217     NEW_REFERENCES.DESCRIPTION,
218     NEW_REFERENCES.CLOSED_IND,
219     X_LAST_UPDATE_DATE,
220     X_LAST_UPDATED_BY,
221     X_LAST_UPDATE_DATE,
222     X_LAST_UPDATED_BY,
223     X_LAST_UPDATE_LOGIN
224   );
225   open c;
226   fetch c into X_ROWID;
227   if (c%notfound) then
228     close c;
229     raise no_data_found;
230   end if;
231   close c;
232 end INSERT_ROW;
233 procedure LOCK_ROW (
234   X_ROWID in VARCHAR2,
235   X_GOVT_HECS_CNTRBTN_BAND in NUMBER,
236   X_DESCRIPTION in VARCHAR2,
237   X_CLOSED_IND in VARCHAR2
238 ) AS
239   cursor c1 is select
240       DESCRIPTION,
241       CLOSED_IND
242     from IGS_FI_GOVT_HEC_CNTB
243     where ROWID=X_ROWID
244     for update nowait;
245   tlinfo c1%rowtype;
246 begin
247   open c1;
248   fetch c1 into tlinfo;
249   if (c1%notfound) then
250     close c1;
251     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
252     IGS_GE_MSG_STACK.ADD;
253     app_exception.raise_exception;
254     return;
255   end if;
256   close c1;
257   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
258       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
259   ) then
260     null;
261   else
262     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
263     IGS_GE_MSG_STACK.ADD;
264     app_exception.raise_exception;
265   end if;
266   return;
267 end LOCK_ROW;
268 procedure UPDATE_ROW (
269   X_ROWID in VARCHAR2,
270   X_GOVT_HECS_CNTRBTN_BAND in NUMBER,
271   X_DESCRIPTION in VARCHAR2,
272   X_CLOSED_IND in VARCHAR2,
273   X_MODE in VARCHAR2 default 'R'
274   ) AS
275     X_LAST_UPDATE_DATE DATE;
276     X_LAST_UPDATED_BY NUMBER;
277     X_LAST_UPDATE_LOGIN NUMBER;
278 begin
279   X_LAST_UPDATE_DATE := SYSDATE;
280   if(X_MODE = 'I') then
281     X_LAST_UPDATED_BY := 1;
282     X_LAST_UPDATE_LOGIN := 0;
283   elsif (X_MODE = 'R') then
284     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
285     if X_LAST_UPDATED_BY is NULL then
286       X_LAST_UPDATED_BY := -1;
287     end if;
288     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
289     if X_LAST_UPDATE_LOGIN is NULL then
290       X_LAST_UPDATE_LOGIN := -1;
291     end if;
292   else
293     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
294     IGS_GE_MSG_STACK.ADD;
295     app_exception.raise_exception;
296   end if;
297  Before_DML(
298   p_action=>'UPDATE',
299   x_rowid=>X_ROWID,
300   x_closed_ind=>X_CLOSED_IND,
301   x_description=>X_DESCRIPTION,
302   x_govt_hecs_cntrbtn_band=>X_GOVT_HECS_CNTRBTN_BAND,
303   x_creation_date=>X_LAST_UPDATE_DATE,
304   x_created_by=>X_LAST_UPDATED_BY,
305   x_last_update_date=>X_LAST_UPDATE_DATE,
306   x_last_updated_by=>X_LAST_UPDATED_BY,
307   x_last_update_login=>X_LAST_UPDATE_LOGIN
308 );
309   update IGS_FI_GOVT_HEC_CNTB set
310     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
311     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
312     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
313     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
314     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
315   where ROWID=X_ROWID
316   ;
317   if (sql%notfound) then
318     raise no_data_found;
319   end if;
320 end UPDATE_ROW;
321 procedure ADD_ROW (
322   X_ROWID in out NOCOPY VARCHAR2,
323   X_GOVT_HECS_CNTRBTN_BAND in NUMBER,
324   X_DESCRIPTION in VARCHAR2,
325   X_CLOSED_IND in VARCHAR2,
326   X_MODE in VARCHAR2 default 'R'
327   ) AS
328   cursor c1 is select rowid from IGS_FI_GOVT_HEC_CNTB
329      where GOVT_HECS_CNTRBTN_BAND = X_GOVT_HECS_CNTRBTN_BAND
330   ;
331 begin
332   open c1;
333   fetch c1 into X_ROWID;
334   if (c1%notfound) then
335     close c1;
336     INSERT_ROW (
337      X_ROWID,
338      X_GOVT_HECS_CNTRBTN_BAND,
339      X_DESCRIPTION,
340      X_CLOSED_IND,
341      X_MODE);
342     return;
343   end if;
344   close c1;
345   UPDATE_ROW (
346    X_ROWID,
347    X_GOVT_HECS_CNTRBTN_BAND,
348    X_DESCRIPTION,
349    X_CLOSED_IND,
350    X_MODE);
351 end ADD_ROW;
352 procedure DELETE_ROW (
353   X_ROWID in VARCHAR2
354 ) AS
355 begin
356 BEfore_DML (
357  p_action => 'DELETE',
358  x_rowid => X_ROWID
359 );
360   delete from IGS_FI_GOVT_HEC_CNTB
361   where ROWID=X_ROWID;
362   if (sql%notfound) then
363     raise no_data_found;
364   end if;
365 end DELETE_ROW;
366 end IGS_FI_GOVT_HEC_CNTB_PKG;