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_GOV_HEC_PA_OP_PKG

Source


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