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_HECS_PAY_OPTN_PKG

Source


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