[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_ENROLMENT_CAT_PKG
Source
1 package body IGS_EN_ENROLMENT_CAT_PKG AS
2 /* $Header: IGSEI22B.pls 120.1 2005/09/08 14:48:02 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_EN_ENROLMENT_CAT%RowType;
5 new_references IGS_EN_ENROLMENT_CAT%RowType;
6
7 PROCEDURE beforerowdelete;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_closed_ind IN VARCHAR2 DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL
20 ) AS
21
22 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_EN_ENROLMENT_CAT
25 WHERE rowid = x_rowid;
26
27 BEGIN
28
29 l_rowid := x_rowid;
30
31 -- Code for setting the Old and New Reference Values.
32 -- Populate Old Values.
33 Open cur_old_ref_values;
34 Fetch cur_old_ref_values INTO old_references;
35 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
36 Close cur_old_ref_values;
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Return;
41 END IF;
42 Close cur_old_ref_values;
43
44 -- Populate New Values.
45 new_references.enrolment_cat := x_enrolment_cat;
46 new_references.description := x_description;
47 new_references.closed_ind := x_closed_ind;
48 IF (p_action = 'UPDATE') THEN
49 new_references.creation_date := old_references.creation_date;
50 new_references.created_by := old_references.created_by;
51 ELSE
52 new_references.creation_date := x_creation_date;
53 new_references.created_by := x_created_by;
54 END IF;
55 new_references.last_update_date := x_last_update_date;
56 new_references.last_updated_by := x_last_updated_by;
57 new_references.last_update_login := x_last_update_login;
58
59 END Set_Column_Values;
60
61 -- Trigger description :-
62 -- "OSS_TST".trg_ec_br_u
63 -- BEFORE UPDATE
64 -- ON IGS_EN_ENROLMENT_CAT
65 -- FOR EACH ROW
66
67 PROCEDURE BeforeRowUpdate1(
68 p_inserting IN BOOLEAN DEFAULT FALSE,
69 p_updating IN BOOLEAN DEFAULT FALSE,
70 p_deleting IN BOOLEAN DEFAULT FALSE
71 ) AS
72 v_message_name varchar2(30);
73 BEGIN
74 -- Validate update of closed indicator.
75 IF old_references.closed_ind <> new_references.closed_ind THEN
76 IF IGS_EN_VAL_EC.enrp_val_ec_clsd_upd (
77 new_references.enrolment_cat,
78 new_references.closed_ind,
79 v_message_name) = FALSE THEN
80 fnd_message.set_name('IGS',v_message_name);
81 IGS_GE_MSG_STACK.ADD;
82 app_exception.raise_exception;
83 END IF;
84 END IF;
85
86
87 END BeforeRowUpdate1;
88
89 procedure Check_constraints(
90 column_name IN VARCHAR2 DEFAULT NULL,
91 column_value IN VARCHAR2 DEFAULT NULL
92 ) AS
93 begin
94 IF column_name is null then
95 NULL;
96 ELSIF upper(column_name) = 'CLOSED_IND' then
97 new_references.closed_ind := column_value;
98 ELSIF upper(column_name) = 'ENROLMENT_CAT' then
99 new_references.enrolment_cat := column_value;
100 END IF;
101
102 IF upper(column_name) = 'CLOSED_IND' OR
103 column_name is null then
104 if new_references.closed_ind NOT IN ('Y','N') OR
105 new_references.closed_ind <> upper(new_references.closed_ind) 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
112 IF upper(column_name) = 'ENROLMENT_CAT' OR
113 column_name is null then
114 if new_references.enrolment_cat <> upper(new_references.enrolment_cat) then
115 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
116 IGS_GE_MSG_STACK.ADD;
117 App_Exception.Raise_Exception;
118 end if;
119 end if;
120
121 END check_constraints;
122
123 PROCEDURE Check_Child_Existance AS
124 BEGIN
125
126 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_EN_ENROLMENT_CAT (
127 old_references.enrolment_cat
128 );
129
130 IGS_EN_CAT_MAPPING_PKG.GET_FK_IGS_EN_ENROLMENT_CAT (
131 old_references.enrolment_cat
132 );
133
134 IGS_EN_CAT_PRC_DTL_PKG.GET_FK_IGS_EN_ENROLMENT_CAT (
135 old_references.enrolment_cat
136 );
137
138 IGS_AS_SC_ATMPT_ENR_PKG.GET_FK_IGS_EN_ENROLMENT_CAT (
139 old_references.enrolment_cat
140 );
141 igs_en_cpd_ext_pkg.get_fk_igs_en_enrolment_cat(
142 old_references.enrolment_cat
143 );
144 IGS_PS_TYPE_PKG.GET_FK_IGS_EN_ENROLMENT_CAT(
145 old_references.enrolment_cat
146 );
147
148 END Check_Child_Existance;
149
150 FUNCTION Get_PK_For_Validation (
151 x_enrolment_cat IN VARCHAR2
152 )RETURN BOOLEAN AS
153
154 CURSOR cur_rowid IS
155 SELECT rowid
156 FROM IGS_EN_ENROLMENT_CAT
157 WHERE enrolment_cat = x_enrolment_cat;
158
159 lv_rowid cur_rowid%RowType;
160
161 BEGIN
162
163 Open cur_rowid;
164 Fetch cur_rowid INTO lv_rowid;
165 IF (cur_rowid%FOUND) THEN
166 Close cur_rowid;
167 return(TRUE);
168 else
169 Close cur_rowid;
170 Return(FALSE);
171 END IF;
172
173 END Get_PK_For_Validation;
174
175 PROCEDURE Before_DML (
176 p_action IN VARCHAR2,
177 x_rowid IN VARCHAR2 DEFAULT NULL,
178 x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
179 x_description IN VARCHAR2 DEFAULT NULL,
180 x_closed_ind IN VARCHAR2 DEFAULT NULL,
181 x_creation_date IN DATE DEFAULT NULL,
182 x_created_by IN NUMBER DEFAULT NULL,
183 x_last_update_date IN DATE DEFAULT NULL,
184 x_last_updated_by IN NUMBER DEFAULT NULL,
185 x_last_update_login IN NUMBER DEFAULT NULL
186 ) AS
187 BEGIN
188
189 Set_Column_Values (
190 p_action,
191 x_rowid,
192 x_enrolment_cat,
193 x_description,
194 x_closed_ind,
195 x_creation_date,
196 x_created_by,
197 x_last_update_date,
198 x_last_updated_by,
199 x_last_update_login
200 );
201
202 IF (p_action = 'INSERT') THEN
203 -- Call all the procedures related to Before Insert.
204 IF Get_PK_For_Validation (
205 new_references.enrolment_cat
206 ) then
207 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
208 IGS_GE_MSG_STACK.ADD;
209 App_Exception.Raise_Exception;
210 end if;
211 Check_constraints;
212 ELSIF (p_action = 'UPDATE') THEN
213 -- Call all the procedures related to Before Update.
214 BeforeRowUpdate1 ( p_updating => TRUE );
215 Check_constraints;
216 ELSIF (p_action = 'DELETE') THEN
217 -- Call all the procedures related to Before Delete.
218 beforerowdelete;
219 Check_Child_Existance;
220 ELSIF (p_action = 'VALIDATE_INSERT') then
221 IF Get_PK_For_Validation (
222 new_references.enrolment_cat
223 ) then
224 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 end if;
228 Check_constraints;
229 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
230 Check_constraints;
231 ELSIF (p_action = 'VALIDATE_DELETE') THEN
232 Check_Child_Existance;
233 END IF;
234
235 END Before_DML;
236
237 PROCEDURE After_DML (
238 p_action IN VARCHAR2,
239 x_rowid IN VARCHAR2
240 ) AS
241 BEGIN
242
243 l_rowid := x_rowid;
244
245 IF (p_action = 'INSERT') THEN
246 -- Call all the procedures related to After Insert.
247 Null;
248 ELSIF (p_action = 'UPDATE') THEN
249 -- Call all the procedures related to After Update.
250 Null;
251 ELSIF (p_action = 'DELETE') THEN
252 -- Call all the procedures related to After Delete.
253 Null;
254 END IF;
255
256 END After_DML;
257 procedure INSERT_ROW (
258 X_ROWID in out NOCOPY VARCHAR2,
259 X_ENROLMENT_CAT in VARCHAR2,
260 X_DESCRIPTION in VARCHAR2,
261 X_CLOSED_IND in VARCHAR2,
262 X_MODE in VARCHAR2 default 'R'
263 ) AS
264 cursor C is select ROWID from IGS_EN_ENROLMENT_CAT
265 where ENROLMENT_CAT = X_ENROLMENT_CAT;
266 X_LAST_UPDATE_DATE DATE;
267 X_LAST_UPDATED_BY NUMBER;
268 X_LAST_UPDATE_LOGIN NUMBER;
269 begin
270 X_LAST_UPDATE_DATE := SYSDATE;
271 if(X_MODE = 'I') then
272 X_LAST_UPDATED_BY := 1;
273 X_LAST_UPDATE_LOGIN := 0;
274 elsif (X_MODE = 'R') then
275 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
276 if X_LAST_UPDATED_BY is NULL then
277 X_LAST_UPDATED_BY := -1;
278 end if;
279 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
280 if X_LAST_UPDATE_LOGIN is NULL then
281 X_LAST_UPDATE_LOGIN := -1;
282 end if;
283 else
284 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
285 IGS_GE_MSG_STACK.ADD;
286 app_exception.raise_exception;
287 end if;
288 Before_DML (
289 p_action => 'INSERT',
290 x_rowid => X_ROWID,
291 x_enrolment_cat => X_ENROLMENT_CAT,
292 x_description => X_DESCRIPTION,
293 x_closed_ind => NVL(X_CLOSED_IND,'N'),
294 x_creation_date => X_LAST_UPDATE_DATE,
295 x_created_by => X_LAST_UPDATED_BY,
296 x_last_update_date => X_LAST_UPDATE_DATE,
297 x_last_updated_by => X_LAST_UPDATED_BY,
298 x_last_update_login => X_LAST_UPDATE_LOGIN
299 );
300 insert into IGS_EN_ENROLMENT_CAT (
301 ENROLMENT_CAT,
302 DESCRIPTION,
303 CLOSED_IND,
304 CREATION_DATE,
305 CREATED_BY,
306 LAST_UPDATE_DATE,
307 LAST_UPDATED_BY,
308 LAST_UPDATE_LOGIN
309 ) values (
310 NEW_REFERENCES.ENROLMENT_CAT,
311 NEW_REFERENCES.DESCRIPTION,
312 NEW_REFERENCES.CLOSED_IND,
313 X_LAST_UPDATE_DATE,
314 X_LAST_UPDATED_BY,
315 X_LAST_UPDATE_DATE,
316 X_LAST_UPDATED_BY,
317 X_LAST_UPDATE_LOGIN
318 );
319
320 open c;
321 fetch c into X_ROWID;
322 if (c%notfound) then
323 close c;
324 raise no_data_found;
325 end if;
326 close c;
327 After_DML (
328 p_action => 'INSERT',
329 x_rowid => X_ROWID
330 );
331
332 end INSERT_ROW;
333
334 procedure LOCK_ROW (
335 X_ROWID in VARCHAR2,
336 X_ENROLMENT_CAT in VARCHAR2,
337 X_DESCRIPTION in VARCHAR2,
338 X_CLOSED_IND in VARCHAR2
339 ) AS
340 cursor c1 is select
341 DESCRIPTION,
342 CLOSED_IND
343 from IGS_EN_ENROLMENT_CAT
344 where ROWID = X_ROWID for update nowait;
345 tlinfo c1%rowtype;
346
347 begin
348 open c1;
349 fetch c1 into tlinfo;
350 if (c1%notfound) then
351 close c1;
352 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
353 IGS_GE_MSG_STACK.ADD;
354 app_exception.raise_exception;
355 return;
356 end if;
357 close c1;
358
359 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
360 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
361 ) then
362 null;
363 else
364 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
365 IGS_GE_MSG_STACK.ADD;
366 app_exception.raise_exception;
367 end if;
368 return;
369 end LOCK_ROW;
370
371 procedure UPDATE_ROW (
372 X_ROWID in VARCHAR2,
373 X_ENROLMENT_CAT in VARCHAR2,
374 X_DESCRIPTION in VARCHAR2,
375 X_CLOSED_IND in VARCHAR2,
376 X_MODE in VARCHAR2 default 'R'
377 ) AS
378 X_LAST_UPDATE_DATE DATE;
379 X_LAST_UPDATED_BY NUMBER;
380 X_LAST_UPDATE_LOGIN NUMBER;
381 begin
382 X_LAST_UPDATE_DATE := SYSDATE;
383 if(X_MODE = 'I') then
384 X_LAST_UPDATED_BY := 1;
385 X_LAST_UPDATE_LOGIN := 0;
386 elsif (X_MODE = 'R') then
387 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
388 if X_LAST_UPDATED_BY is NULL then
389 X_LAST_UPDATED_BY := -1;
390 end if;
391 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
392 if X_LAST_UPDATE_LOGIN is NULL then
393 X_LAST_UPDATE_LOGIN := -1;
394 end if;
395 else
396 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
397 IGS_GE_MSG_STACK.ADD;
398 app_exception.raise_exception;
399 end if;
400 Before_DML (
401 p_action => 'UPDATE',
402 x_rowid => X_ROWID,
403 x_enrolment_cat => X_ENROLMENT_CAT,
404 x_description => X_DESCRIPTION,
405 x_closed_ind => X_CLOSED_IND,
406 x_creation_date => X_LAST_UPDATE_DATE,
407 x_created_by => X_LAST_UPDATED_BY,
408 x_last_update_date => X_LAST_UPDATE_DATE,
409 x_last_updated_by => X_LAST_UPDATED_BY,
410 x_last_update_login => X_LAST_UPDATE_LOGIN
411 );
412 update IGS_EN_ENROLMENT_CAT set
413 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
414 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
415 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
416 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
417 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
418 where ROWID = X_ROWID;
419 if (sql%notfound) then
420 raise no_data_found;
421 end if;
422 After_DML (
423 p_action => 'UPDATE',
424 x_rowid => X_ROWID
425 );
426 end UPDATE_ROW;
427
428 procedure ADD_ROW (
429 X_ROWID in out NOCOPY VARCHAR2,
430 X_ENROLMENT_CAT in VARCHAR2,
431 X_DESCRIPTION in VARCHAR2,
432 X_CLOSED_IND in VARCHAR2,
433 X_MODE in VARCHAR2 default 'R'
434 ) AS
435 cursor c1 is select rowid from IGS_EN_ENROLMENT_CAT
436 where ENROLMENT_CAT = X_ENROLMENT_CAT
437 ;
438
439 begin
440 open c1;
441 fetch c1 into X_ROWID;
442 if (c1%notfound) then
443 close c1;
444 INSERT_ROW (
445 X_ROWID,
446 X_ENROLMENT_CAT,
447 X_DESCRIPTION,
448 X_CLOSED_IND,
449 X_MODE);
450 return;
451 end if;
452 close c1;
453 UPDATE_ROW (
454 X_ROWID,
455 X_ENROLMENT_CAT,
456 X_DESCRIPTION,
457 X_CLOSED_IND,
458 X_MODE);
459 end ADD_ROW;
460
461 procedure DELETE_ROW (
462 X_ROWID in VARCHAR2) AS
463 begin
464 Before_DML (
465 p_action => 'DELETE',
466 x_rowid => X_ROWID
467 );
468 delete from IGS_EN_ENROLMENT_CAT
469 where ROWID = X_ROWID;
470 if (sql%notfound) then
471 raise no_data_found;
472 end if;
473 After_DML (
474 p_action => 'DELETE',
475 x_rowid => X_ROWID
476 );
477 end DELETE_ROW;
478
479 PROCEDURE beforerowdelete AS
480 ------------------------------------------------------------------
481 --Created by : rnirwani
482 --Date created: 03-Jan-03
483 --
484 --Purpose: Validation to ensure that delation is not allowed
485 --
486 --
487 --Known limitations/enhancements and/or remarks:
488 --
489 --Change History:
490 --Who When What
491 -------------------------------------------------------------------
492
493 BEGIN
494
495 -- Deletion is not allowed in this table.
496 -- this change has been done since in the PK check the lock has been
497 -- removed. Hence to avoid data inconsistency the deletion should not
498 -- be done. The record should be closed instead by checking the closed
499 -- indicator.
500
501 FND_MESSAGE.SET_NAME('IGS','IGS_FI_DEL_NOT_ALLWD');
502 igs_ge_msg_stack.add;
503 APP_EXCEPTION.RAISE_EXCEPTION;
504
505 END beforerowdelete;
506
507
508 end IGS_EN_ENROLMENT_CAT_PKG;