[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_CT_HECS_PAYOP_PKG
Source
1 package body IGS_AD_CT_HECS_PAYOP_PKG AS
2 /* $Header: IGSAI14B.pls 115.6 2003/10/30 13:19:08 rghosh ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_CT_HECS_PAYOP%RowType;
6 new_references IGS_AD_CT_HECS_PAYOP%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_admission_cat IN VARCHAR2 DEFAULT NULL,
12 x_hecs_payment_option 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
20 CURSOR cur_old_ref_values IS
21 SELECT *
22 FROM IGS_AD_CT_HECS_PAYOP
23 WHERE rowid = x_rowid;
24
25 BEGIN
26
27 l_rowid := x_rowid;
28
29 -- Code for setting the Old and New Reference Values.
30 -- Populate Old Values.
31 Open cur_old_ref_values;
32 Fetch cur_old_ref_values INTO old_references;
33 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
34 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35 IGS_GE_MSG_STACK.ADD;
36 App_Exception.Raise_Exception;
37 Close cur_old_ref_values;
38 Return;
39 END IF;
40 Close cur_old_ref_values;
41
42 -- Populate New Values.
43 new_references.admission_cat := x_admission_cat;
44 new_references.hecs_payment_option := x_hecs_payment_option;
45 IF (p_action = 'UPDATE') THEN
46 new_references.creation_date := old_references.creation_date;
47 new_references.created_by := old_references.created_by;
48 ELSE
49 new_references.creation_date := x_creation_date;
50 new_references.created_by := x_created_by;
51 END IF;
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
56 END Set_Column_Values;
57
58 PROCEDURE BeforeRowInsert1(
59 p_inserting IN BOOLEAN DEFAULT FALSE,
60 p_updating IN BOOLEAN DEFAULT FALSE,
61 p_deleting IN BOOLEAN DEFAULT FALSE
62 ) AS
63 v_message_name VARCHAR2(30);
64 BEGIN
65 -- Validate the admission category closed indicator.
66 IF IGS_AD_VAL_ACCT.admp_val_ac_closed (
67 new_references.admission_cat ,
68 v_message_name) = FALSE THEN
69 Fnd_Message.Set_Name('IGS',v_message_name);
70 IGS_GE_MSG_STACK.ADD;
71 App_Exception.Raise_Exception;
72 END IF;
73 -- Validate the HECS payment option closed indicator.
74 -- change igs_ad_val_achpo.enrp_val_hpo_closed
75 -- to igs_en_val_scho.enrp_val_hpo_closed
76 IF IGS_EN_VAL_SCHO.enrp_val_hpo_closed (
77 new_references.hecs_payment_option,
78 v_message_name) = FALSE THEN
79 Fnd_Message.Set_Name('IGS',v_message_name);
80 IGS_GE_MSG_STACK.ADD;
81 App_Exception.Raise_Exception;
82 END IF;
83 END BeforeRowInsert1;
84
85 PROCEDURE Check_Constraints (
86 Column_Name IN VARCHAR2 DEFAULT NULL,
87 Column_Value IN VARCHAR2 DEFAULT NULL
88 ) AS
89 BEGIN
90 IF column_name is null then
91 NULL;
92 ELSIF upper(Column_name) = 'ADMISSION_CAT' Then
93 new_references.admission_cat := column_value;
94 ELSIF upper(Column_name) = 'HECS_PAYMENT_OPTION' Then
95 new_references.hecs_payment_option := column_value;
96 END IF;
97 IF upper(column_name) = 'ADMISSION_CAT' OR column_name is null Then
98 IF new_references.admission_cat <> UPPER(new_references.admission_cat) Then
99 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
100 IGS_GE_MSG_STACK.ADD;
101 App_Exception.Raise_Exception;
102 END IF;
103 END IF;
104 IF upper(column_name) = 'HECS_PAYMENT_OPTION' OR column_name is null Then
105 IF new_references.hecs_payment_option <> UPPER(new_references.hecs_payment_option ) Then
106 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
107 IGS_GE_MSG_STACK.ADD;
108 App_Exception.Raise_Exception;
109 END IF;
110 END IF;
111 END Check_Constraints;
112
113
114 PROCEDURE Check_Parent_Existance AS
115 BEGIN
116 IF (((old_references.admission_cat = new_references.admission_cat )) OR
117 ((new_references.admission_cat IS NULL))) THEN
118 NULL;
119 ELSE
120 IF NOT IGS_AD_CAT_PKG.Get_PK_For_Validation (
121 new_references.admission_cat , 'N' )THEN
122 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 END IF;
126 END IF;
127
128 IF (((old_references.hecs_payment_option = new_references.hecs_payment_option)) OR
129 ((new_references.hecs_payment_option IS NULL))) THEN
130 NULL;
131 ELSE
132 IF NOT IGS_FI_HECS_PAY_OPTN_PKG.Get_PK_For_Validation (
133 new_references.hecs_payment_option )THEN
134 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END IF;
138 END IF;
139
140 END Check_Parent_Existance;
141
142 Function Get_PK_For_Validation (
143 x_admission_cat IN VARCHAR2,
144 x_hecs_payment_option IN VARCHAR2)
145 RETURN BOOLEAN AS
146 CURSOR cur_rowid IS
147 SELECT rowid
148 FROM IGS_AD_CT_HECS_PAYOP
149 WHERE admission_cat = x_admission_cat
150 AND hecs_payment_option = x_hecs_payment_option
151 FOR UPDATE NOWAIT;
152 lv_rowid cur_rowid%RowType;
153 BEGIN -- Get_PK_For_Validation
154 Open cur_rowid;
155 Fetch cur_rowid INTO lv_rowid;
156 IF (cur_rowid%FOUND) THEN
157 Close cur_rowid;
158 Return (TRUE);
159 ELSE
160 Close cur_rowid;
161 Return (FALSE);
162 END IF;
163 END Get_PK_For_Validation;
164
165 PROCEDURE GET_FK_IGS_AD_CAT (
166 x_admission_cat IN VARCHAR2
167 ) AS
168
169 CURSOR cur_rowid IS
170 SELECT rowid
171 FROM IGS_AD_CT_HECS_PAYOP
172 WHERE admission_cat = x_admission_cat ;
173
174 lv_rowid cur_rowid%RowType;
175
176 BEGIN
177
178 Open cur_rowid;
179 Fetch cur_rowid INTO lv_rowid;
180 IF (cur_rowid%FOUND) THEN
181 Close cur_rowid;
182 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACHPO_AC_FK');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 Return;
186 END IF;
187 Close cur_rowid;
188
189 END GET_FK_IGS_AD_CAT;
190
191 PROCEDURE GET_FK_IGS_FI_HECS_PAY_OPTN (
192 x_hecs_payment_option IN VARCHAR2
193 ) AS
194
195 CURSOR cur_rowid IS
196 SELECT rowid
197 FROM IGS_AD_CT_HECS_PAYOP
198 WHERE hecs_payment_option = x_hecs_payment_option ;
199
200 lv_rowid cur_rowid%RowType;
201
202 BEGIN
203
204 Open cur_rowid;
205 Fetch cur_rowid INTO lv_rowid;
206 IF (cur_rowid%FOUND) THEN
207 Close cur_rowid;
208 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ACHPO_HPO_FK');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 Return;
212 END IF;
213 Close cur_rowid;
214
215 END GET_FK_IGS_FI_HECS_PAY_OPTN;
216
217 PROCEDURE Before_DML (
218 p_action IN VARCHAR2,
219 x_rowid IN VARCHAR2 DEFAULT NULL,
220 x_admission_cat IN VARCHAR2 DEFAULT NULL,
221 x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
222 x_creation_date IN DATE DEFAULT NULL,
223 x_created_by IN NUMBER DEFAULT NULL,
224 x_last_update_date IN DATE DEFAULT NULL,
225 x_last_updated_by IN NUMBER DEFAULT NULL,
226 x_last_update_login IN NUMBER DEFAULT NULL
227 ) AS
228 BEGIN
229
230 Set_Column_Values (
231 p_action,
232 x_rowid,
233 x_admission_cat,
234 x_hecs_payment_option,
235 x_creation_date,
236 x_created_by,
237 x_last_update_date,
238 x_last_updated_by,
239 x_last_update_login
240 );
241
242 IF (p_action = 'INSERT') THEN
243 -- Call all the procedures related to Before Insert.
244 BeforeRowInsert1 ( p_inserting => TRUE );
245 IF Get_PK_For_Validation (
246 new_references.admission_cat,
247 new_references.hecs_payment_option ) THEN
248 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END IF;
252 Check_Constraints;
253 Check_Parent_Existance;
254 ELSIF (p_action = 'UPDATE') THEN
255 -- Call all the procedures related to Before Update.
256 Check_Constraints;
257 Check_Parent_Existance;
258 ELSIF (p_action = 'VALIDATE_INSERT') THEN
259 IF Get_PK_For_Validation (
260 new_references.admission_cat,
261 new_references.hecs_payment_option ) THEN
262 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
263 IGS_GE_MSG_STACK.ADD;
264 App_Exception.Raise_Exception;
265 END IF;
266 Check_Constraints;
267
268 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
269 Check_Constraints;
270 END IF;
271 END Before_DML;
272
273 PROCEDURE After_DML (
274 p_action IN VARCHAR2,
275 x_rowid IN VARCHAR2
276 ) AS
277 BEGIN
278
279 l_rowid := x_rowid;
280
281 IF (p_action = 'INSERT') THEN
282 -- Call all the procedures related to After Insert.
283 Null;
284 ELSIF (p_action = 'UPDATE') THEN
285 -- Call all the procedures related to After Update.
286 Null;
287 ELSIF (p_action = 'DELETE') THEN
288 -- Call all the procedures related to After Delete.
289 Null;
290 END IF;
291
292 END After_DML;
293
294 procedure INSERT_ROW (
295 X_ROWID in out NOCOPY VARCHAR2,
296 X_ADMISSION_CAT in VARCHAR2,
297 X_HECS_PAYMENT_OPTION in VARCHAR2,
298 X_MODE in VARCHAR2 default 'R'
299 ) AS
300 cursor C is select ROWID from IGS_AD_CT_HECS_PAYOP
301 where ADMISSION_CAT = X_ADMISSION_CAT
302 and HECS_PAYMENT_OPTION = X_HECS_PAYMENT_OPTION;
303 X_LAST_UPDATE_DATE DATE;
304 X_LAST_UPDATED_BY NUMBER;
305 X_LAST_UPDATE_LOGIN NUMBER;
306 begin
307 X_LAST_UPDATE_DATE := SYSDATE;
308 if(X_MODE = 'I') then
309 X_LAST_UPDATED_BY := 1;
310 X_LAST_UPDATE_LOGIN := 0;
311 elsif (X_MODE = 'R') then
312 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
313 if X_LAST_UPDATED_BY is NULL then
314 X_LAST_UPDATED_BY := -1;
315 end if;
316 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
317 if X_LAST_UPDATE_LOGIN is NULL then
318 X_LAST_UPDATE_LOGIN := -1;
319 end if;
320 else
321 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
322 IGS_GE_MSG_STACK.ADD;
323 app_exception.raise_exception;
324 end if;
325
326 Before_DML(
327 p_action=>'INSERT',
328 x_rowid=>X_ROWID,
329 x_admission_cat=>X_ADMISSION_CAT,
330 x_hecs_payment_option=>X_HECS_PAYMENT_OPTION,
331 x_creation_date=>X_LAST_UPDATE_DATE,
332 x_created_by=>X_LAST_UPDATED_BY,
333 x_last_update_date=>X_LAST_UPDATE_DATE,
334 x_last_updated_by=>X_LAST_UPDATED_BY,
335 x_last_update_login=>X_LAST_UPDATE_LOGIN
336 );
337
338 insert into IGS_AD_CT_HECS_PAYOP (
339 ADMISSION_CAT,
340 HECS_PAYMENT_OPTION,
341 CREATION_DATE,
342 CREATED_BY,
343 LAST_UPDATE_DATE,
344 LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN
346 ) values (
347 NEW_REFERENCES.ADMISSION_CAT,
348 NEW_REFERENCES.HECS_PAYMENT_OPTION,
349 X_LAST_UPDATE_DATE,
350 X_LAST_UPDATED_BY,
351 X_LAST_UPDATE_DATE,
352 X_LAST_UPDATED_BY,
353 X_LAST_UPDATE_LOGIN
354 );
355
356 open c;
357 fetch c into X_ROWID;
358 if (c%notfound) then
359 close c;
360 raise no_data_found;
361 end if;
362 close c;
363
364 After_DML (
365 p_action => 'INSERT',
366 x_rowid => X_ROWID);
367
368 end INSERT_ROW;
369
370 procedure LOCK_ROW (
371 X_ROWID in VARCHAR2,
372 X_ADMISSION_CAT in VARCHAR2,
373 X_HECS_PAYMENT_OPTION in VARCHAR2
374 ) AS
375 cursor c1 is select ROWID
376 from IGS_AD_CT_HECS_PAYOP
377 where ROWID = X_ROWID for update nowait;
378 tlinfo c1%rowtype;
379
380 begin
381 open c1;
382 fetch c1 into tlinfo;
383 if (c1%notfound) then
384 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
385 IGS_GE_MSG_STACK.ADD;
386 app_exception.raise_exception;
387 close c1;
388 return;
389 end if;
390 close c1;
391
392 return;
393 end LOCK_ROW;
394
395 procedure DELETE_ROW (
396 X_ROWID in VARCHAR2
397 ) AS
398 begin
399
400 Before_DML (
401 p_action => 'DELETE',
402 x_rowid => X_ROWID);
403
404 delete from IGS_AD_CT_HECS_PAYOP
405 where ROWID = X_ROWID;
406 if (sql%notfound) then
407 raise no_data_found;
408 end if;
409
410 After_DML (
411 p_action => 'DELETE',
412 x_rowid => X_ROWID);
413
414 end DELETE_ROW;
415
416 end IGS_AD_CT_HECS_PAYOP_PKG;