[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_CAT_MAPPING_PKG
Source
1 package body IGS_EN_CAT_MAPPING_PKG AS
2 /* $Header: IGSEI23B.pls 115.6 2003/10/30 13:28:24 rghosh ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_EN_CAT_MAPPING%RowType;
5 new_references IGS_EN_CAT_MAPPING%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
11 x_admission_cat IN VARCHAR2 DEFAULT NULL,
12 x_dflt_cat_ind 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_EN_CAT_MAPPING
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 Close cur_old_ref_values;
35 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
36 IGS_GE_MSG_STACK.ADD;
37 App_Exception.Raise_Exception;
38 Return;
39 END IF;
40 Close cur_old_ref_values;
41
42 -- Populate New Values.
43 new_references.enrolment_cat := x_enrolment_cat;
44 new_references.admission_cat := x_admission_cat;
45 new_references.dflt_cat_ind := x_dflt_cat_ind;
46 IF (p_action = 'UPDATE') THEN
47 new_references.creation_date := old_references.creation_date;
48 new_references.created_by := old_references.created_by;
49 ELSE
50 new_references.creation_date := x_creation_date;
51 new_references.created_by := x_created_by;
52 END IF;
53 new_references.last_update_date := x_last_update_date;
54 new_references.last_updated_by := x_last_updated_by;
55 new_references.last_update_login := x_last_update_login;
56
57 END Set_Column_Values;
58
59 -- Trigger description :-
60 -- "OSS_TST".trg_ecm_br_iud
61 -- BEFORE INSERT OR DELETE OR UPDATE
62 -- ON IGS_EN_CAT_MAPPING
63 -- FOR EACH ROW
64
65 PROCEDURE BeforeRowInsertUpdateDelete1(
66 p_inserting IN BOOLEAN DEFAULT FALSE,
67 p_updating IN BOOLEAN DEFAULT FALSE,
68 p_deleting IN BOOLEAN DEFAULT FALSE
69 ) AS
70 v_admission_cat IGS_EN_CAT_MAPPING.admission_cat%TYPE;
71 v_message_name varchar2(30);
72 BEGIN
73 IF p_inserting THEN
74 -- Validate the enrolment category closed indicator.
75 IF IGS_AD_VAL_ECM.enrp_val_ec_closed (
76 new_references.enrolment_cat,
77 v_message_name) = FALSE THEN
78 fnd_message.set_name('IGS',v_message_name);
79 IGS_GE_MSG_STACK.ADD;
80 app_exception.raise_exception;
81 END IF;
82 END IF;
83 -- Set the Admission Category value.
84 IF p_deleting THEN
85 v_admission_cat := old_references.admission_cat;
86 ELSE
87 v_admission_cat := new_references.admission_cat;
88 END IF;
89 -- Validate the admission category closed indicator.
90 IF IGS_AD_VAL_ACCT.admp_val_ac_closed (
91 v_admission_cat,
92 v_message_name) = FALSE THEN
93 fnd_message.set_name('IGS',v_message_name);
94 IGS_GE_MSG_STACK.ADD;
95 app_exception.raise_exception;
96 END IF;
97
98
99 END BeforeRowInsertUpdateDelete1;
100
101 procedure Check_constraints(
102 column_name IN VARCHAR2 DEFAULT NULL,
103 column_value IN VARCHAR2 DEFAULT NULL
104 ) AS
105 begin
106 IF column_name is null then
107 NULL;
108 ELSIF upper(column_name) = 'DFLT_CAT_IND' then
109 new_references.dflt_cat_ind := column_value;
110 ELSIF upper(column_name) = 'ADMISSION_CAT' then
111 new_references.admission_cat := column_value;
112 ELSIF upper(column_name) = 'ENROLMENT_CAT' then
113 new_references.enrolment_cat := column_value;
114 END IF;
115
116 IF upper(column_name) = 'DFLT_CAT_IND' OR
117 column_name is null then
118 if new_references.dflt_cat_ind NOT IN ('Y','N') OR
119 new_references.dflt_cat_ind <> upper(new_references.dflt_cat_ind) then
120 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
121 IGS_GE_MSG_STACK.ADD;
122 App_Exception.Raise_Exception;
123 end if;
124 end if;
125 IF upper(column_name) = 'ADMISSION_CAT' OR
126 column_name is null then
127 if new_references.admission_cat <> upper(new_references.admission_cat) then
128 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
129 IGS_GE_MSG_STACK.ADD;
130 App_Exception.Raise_Exception;
131 end if;
132 end if;
133 IF upper(column_name) = 'ENROLMENT_CAT' OR
134 column_name is null then
135 if new_references.enrolment_cat <> upper(new_references.enrolment_cat) then
136 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 end if;
140 end if;
141 END check_constraints;
142
143 PROCEDURE Check_Parent_Existance AS
144 BEGIN
145
146 IF (((old_references.admission_cat = new_references.admission_cat)) OR
147 ((new_references.admission_cat IS NULL))) THEN
148 NULL;
149 ELSE
150 if not IGS_AD_CAT_PKG.Get_PK_For_Validation (
151 new_references.admission_cat ,
152 'N'
153 )then
154 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
155 IGS_GE_MSG_STACK.ADD;
156 App_Exception.Raise_Exception;
157 end if;
158 END IF;
159
160 IF (((old_references.enrolment_cat = new_references.enrolment_cat)) OR
161 ((new_references.enrolment_cat IS NULL))) THEN
162 NULL;
163 ELSE
164 if not IGS_EN_ENROLMENT_CAT_PKG.Get_PK_For_Validation (
165 new_references.enrolment_cat
166 ) then
167 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 end if;
171 END IF;
172
173 END Check_Parent_Existance;
174
175 FUNCTION Get_PK_For_Validation (
176 x_enrolment_cat IN VARCHAR2,
177 x_admission_cat IN VARCHAR2
178 ) RETURN BOOLEAN AS
179
180 CURSOR cur_rowid IS
181 SELECT rowid
182 FROM IGS_EN_CAT_MAPPING
183 WHERE enrolment_cat = x_enrolment_cat
184 AND admission_cat = x_admission_cat
185 FOR UPDATE NOWAIT;
186
187 lv_rowid cur_rowid%RowType;
188
189 BEGIN
190
191 Open cur_rowid;
192 Fetch cur_rowid INTO lv_rowid;
193 IF (cur_rowid%FOUND) THEN
194 Close cur_rowid;
195 return(TRUE);
196 else
197 Close cur_rowid;
198 Return(FALSE);
199 END IF;
200
201 END Get_PK_For_Validation;
202
203 PROCEDURE GET_FK_IGS_AD_CAT (
204 x_admission_cat IN VARCHAR2
205 ) AS
206
207 CURSOR cur_rowid IS
208 SELECT rowid
209 FROM IGS_EN_CAT_MAPPING
210 WHERE admission_cat = x_admission_cat ;
211
212 lv_rowid cur_rowid%RowType;
213
214 BEGIN
215
216 Open cur_rowid;
217 Fetch cur_rowid INTO lv_rowid;
218 IF (cur_rowid%FOUND) THEN
219 Close cur_rowid;
220 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ECM_AC_FK');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 Return;
224 END IF;
225 Close cur_rowid;
226
227 END GET_FK_IGS_AD_CAT;
228
229 PROCEDURE GET_FK_IGS_EN_ENROLMENT_CAT (
230 x_enrolment_cat IN VARCHAR2
231 ) AS
232
233 CURSOR cur_rowid IS
234 SELECT rowid
235 FROM IGS_EN_CAT_MAPPING
236 WHERE enrolment_cat = x_enrolment_cat ;
237
238 lv_rowid cur_rowid%RowType;
239
240 BEGIN
241
242 Open cur_rowid;
243 Fetch cur_rowid INTO lv_rowid;
244 IF (cur_rowid%FOUND) THEN
245 Close cur_rowid;
246 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ECM_EC_FK');
247 IGS_GE_MSG_STACK.ADD;
248 App_Exception.Raise_Exception;
249 Return;
250 END IF;
251 Close cur_rowid;
252
253 END GET_FK_IGS_EN_ENROLMENT_CAT;
254
255 PROCEDURE Before_DML (
256 p_action IN VARCHAR2,
257 x_rowid IN VARCHAR2 DEFAULT NULL,
258 x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
259 x_admission_cat IN VARCHAR2 DEFAULT NULL,
260 x_dflt_cat_ind IN VARCHAR2 DEFAULT NULL,
261 x_creation_date IN DATE DEFAULT NULL,
262 x_created_by IN NUMBER DEFAULT NULL,
263 x_last_update_date IN DATE DEFAULT NULL,
264 x_last_updated_by IN NUMBER DEFAULT NULL,
265 x_last_update_login IN NUMBER DEFAULT NULL
266 ) AS
267 BEGIN
268
269 Set_Column_Values (
270 p_action,
271 x_rowid,
272 x_enrolment_cat,
273 x_admission_cat,
274 x_dflt_cat_ind,
275 x_creation_date,
276 x_created_by,
277 x_last_update_date,
278 x_last_updated_by,
279 x_last_update_login
280 );
281
282 IF (p_action = 'INSERT') THEN
283 -- Call all the procedures related to Before Insert.
284 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
285 if Get_PK_For_Validation (
286 new_references.enrolment_cat,
287 new_references.admission_cat
288 ) then
289
290 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 end if;
294 Check_constraints;
295 Check_Parent_Existance;
296 ELSIF (p_action = 'UPDATE') THEN
297 -- Call all the procedures related to Before Update.
298 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
299 Check_constraints;
300 Check_Parent_Existance;
301 ELSIF (p_action = 'DELETE') THEN
302 -- Call all the procedures related to Before Delete.
303 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
304 ELSIF (p_action = 'VALIDATE_INSERT') then
305 if Get_PK_For_Validation (
306 new_references.enrolment_cat,
307 new_references.admission_cat
308 ) then
309
310 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
311 IGS_GE_MSG_STACK.ADD;
312 App_Exception.Raise_Exception;
313 end if;
314 Check_constraints;
315 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
316 Check_constraints;
317 ELSIF (p_action = 'VALIDATE_DELETE') THEN
318 null;
319 END IF;
320
321 END Before_DML;
322
323 PROCEDURE After_DML (
324 p_action IN VARCHAR2,
325 x_rowid IN VARCHAR2
326 ) AS
327 BEGIN
328
329 l_rowid := x_rowid;
330
331 IF (p_action = 'INSERT') THEN
332 -- Call all the procedures related to After Insert.
333 Null;
334 ELSIF (p_action = 'UPDATE') THEN
335 -- Call all the procedures related to After Update.
336 Null;
337 ELSIF (p_action = 'DELETE') THEN
338 -- Call all the procedures related to After Delete.
339 Null;
340 END IF;
341
342 END After_DML;
343 procedure INSERT_ROW (
344 X_ROWID in out NOCOPY VARCHAR2,
345 X_ENROLMENT_CAT in VARCHAR2,
346 X_ADMISSION_CAT in VARCHAR2,
347 X_DFLT_CAT_IND in VARCHAR2,
348 X_MODE in VARCHAR2 default 'R'
349 ) AS
350 cursor C is select ROWID from IGS_EN_CAT_MAPPING
351 where ENROLMENT_CAT = X_ENROLMENT_CAT
352 and ADMISSION_CAT = X_ADMISSION_CAT;
353 X_LAST_UPDATE_DATE DATE;
354 X_LAST_UPDATED_BY NUMBER;
355 X_LAST_UPDATE_LOGIN NUMBER;
356 begin
357 X_LAST_UPDATE_DATE := SYSDATE;
358 if(X_MODE = 'I') then
359 X_LAST_UPDATED_BY := 1;
360 X_LAST_UPDATE_LOGIN := 0;
361 elsif (X_MODE = 'R') then
362 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
363 if X_LAST_UPDATED_BY is NULL then
364 X_LAST_UPDATED_BY := -1;
365 end if;
366 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
367 if X_LAST_UPDATE_LOGIN is NULL then
368 X_LAST_UPDATE_LOGIN := -1;
369 end if;
370 else
371 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
372 IGS_GE_MSG_STACK.ADD;
373 app_exception.raise_exception;
374 end if;
375 Before_DML (
376 p_action => 'INSERT',
377 x_rowid => X_ROWID,
378 x_enrolment_cat => X_ENROLMENT_CAT,
379 x_admission_cat => X_ADMISSION_CAT,
380 x_dflt_cat_ind => NVL(X_DFLT_CAT_IND,'N'),
381 x_creation_date => X_LAST_UPDATE_DATE,
382 x_created_by => X_LAST_UPDATED_BY,
383 x_last_update_date => X_LAST_UPDATE_DATE,
384 x_last_updated_by => X_LAST_UPDATED_BY,
385 x_last_update_login => X_LAST_UPDATE_LOGIN
386 );
387 insert into IGS_EN_CAT_MAPPING (
388 ENROLMENT_CAT,
389 ADMISSION_CAT,
390 DFLT_CAT_IND,
391 CREATION_DATE,
392 CREATED_BY,
393 LAST_UPDATE_DATE,
394 LAST_UPDATED_BY,
395 LAST_UPDATE_LOGIN
396 ) values (
397 NEW_REFERENCES.ENROLMENT_CAT,
398 NEW_REFERENCES.ADMISSION_CAT,
399 NEW_REFERENCES.DFLT_CAT_IND,
400 X_LAST_UPDATE_DATE,
401 X_LAST_UPDATED_BY,
402 X_LAST_UPDATE_DATE,
403 X_LAST_UPDATED_BY,
404 X_LAST_UPDATE_LOGIN
405 );
406
407 open c;
408 fetch c into X_ROWID;
409 if (c%notfound) then
410 close c;
411 raise no_data_found;
412 end if;
413 close c;
414 After_DML (
415 p_action => 'INSERT',
416 x_rowid => X_ROWID
417 );
418
419 end INSERT_ROW;
420
421 procedure LOCK_ROW (
422 X_ROWID in VARCHAR2,
423 X_ENROLMENT_CAT in VARCHAR2,
424 X_ADMISSION_CAT in VARCHAR2,
425 X_DFLT_CAT_IND in VARCHAR2
426 ) AS
427 cursor c1 is select
428 DFLT_CAT_IND
429 from IGS_EN_CAT_MAPPING
430 where ROWID = X_ROWID for update nowait;
431 tlinfo c1%rowtype;
432
433 begin
434 open c1;
435 fetch c1 into tlinfo;
436 if (c1%notfound) then
437 close c1;
438 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
439 IGS_GE_MSG_STACK.ADD;
440 app_exception.raise_exception;
441 return;
442 end if;
443 close c1;
444
445 if ( (tlinfo.DFLT_CAT_IND = X_DFLT_CAT_IND)
446 ) then
447 null;
448 else
449 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
450 IGS_GE_MSG_STACK.ADD;
451 app_exception.raise_exception;
452 end if;
453 return;
454 end LOCK_ROW;
455
456 procedure UPDATE_ROW (
457 X_ROWID in VARCHAR2,
458 X_ENROLMENT_CAT in VARCHAR2,
459 X_ADMISSION_CAT in VARCHAR2,
460 X_DFLT_CAT_IND in VARCHAR2,
461 X_MODE in VARCHAR2 default 'R'
462 ) AS
463 X_LAST_UPDATE_DATE DATE;
464 X_LAST_UPDATED_BY NUMBER;
465 X_LAST_UPDATE_LOGIN NUMBER;
466 begin
467 X_LAST_UPDATE_DATE := SYSDATE;
468 if(X_MODE = 'I') then
469 X_LAST_UPDATED_BY := 1;
470 X_LAST_UPDATE_LOGIN := 0;
471 elsif (X_MODE = 'R') then
472 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
473 if X_LAST_UPDATED_BY is NULL then
474 X_LAST_UPDATED_BY := -1;
475 end if;
476 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
477 if X_LAST_UPDATE_LOGIN is NULL then
478 X_LAST_UPDATE_LOGIN := -1;
479 end if;
480 else
481 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
482 IGS_GE_MSG_STACK.ADD;
483 app_exception.raise_exception;
484 end if;
485 Before_DML (
486 p_action => 'UPDATE',
487 x_rowid => X_ROWID,
488 x_enrolment_cat => X_ENROLMENT_CAT,
489 x_admission_cat => X_ADMISSION_CAT,
490 x_dflt_cat_ind => X_DFLT_CAT_IND,
491 x_creation_date => X_LAST_UPDATE_DATE,
492 x_created_by => X_LAST_UPDATED_BY,
493 x_last_update_date => X_LAST_UPDATE_DATE,
494 x_last_updated_by => X_LAST_UPDATED_BY,
495 x_last_update_login => X_LAST_UPDATE_LOGIN
496 );
497 update IGS_EN_CAT_MAPPING set
498 DFLT_CAT_IND = NEW_REFERENCES.DFLT_CAT_IND,
499 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
500 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
501 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
502 where ROWID = X_ROWID;
503
504 if (sql%notfound) then
505 raise no_data_found;
506 end if;
507 After_DML (
508 p_action => 'UPDATE',
509 x_rowid => X_ROWID
510 );
511 end UPDATE_ROW;
512
513 procedure ADD_ROW (
514 X_ROWID in out NOCOPY VARCHAR2,
515 X_ENROLMENT_CAT in VARCHAR2,
516 X_ADMISSION_CAT in VARCHAR2,
517 X_DFLT_CAT_IND in VARCHAR2,
518 X_MODE in VARCHAR2 default 'R'
519 ) AS
520 cursor c1 is select rowid from IGS_EN_CAT_MAPPING
521 where ENROLMENT_CAT = X_ENROLMENT_CAT
522 and ADMISSION_CAT = X_ADMISSION_CAT
523 ;
524
525 begin
526 open c1;
527 fetch c1 into X_ROWID;
528 if (c1%notfound) then
529 close c1;
530 INSERT_ROW (
531 X_ROWID,
532 X_ENROLMENT_CAT,
533 X_ADMISSION_CAT,
534 X_DFLT_CAT_IND,
535 X_MODE);
536 return;
537 end if;
538 close c1;
539 UPDATE_ROW (
540 X_ROWID,
541 X_ENROLMENT_CAT,
542 X_ADMISSION_CAT,
543 X_DFLT_CAT_IND,
544 X_MODE);
545 end ADD_ROW;
546
547 procedure DELETE_ROW (
548 X_ROWID in VARCHAR2
549 ) AS
550 begin
551 Before_DML (
552 p_action => 'DELETE',
553 x_rowid => X_ROWID
554 );
555 delete from IGS_EN_CAT_MAPPING
556 where ROWID = X_ROWID;
557 if (sql%notfound) then
558 raise no_data_found;
559 end if;
560 After_DML (
561 p_action => 'DELETE',
562 x_rowid => X_ROWID
563 );
564 end DELETE_ROW;
565
566 end IGS_EN_CAT_MAPPING_PKG;