[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_CAT_PRC_DTL_PKG
Source
1 package body IGS_EN_CAT_PRC_DTL_PKG AS
2 /* $Header: IGSEI15B.pls 115.6 2003/06/11 06:23:21 rnirwani ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_ge_gen_004.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9
10 l_rowid VARCHAR2(25);
11 old_references IGS_EN_CAT_PRC_DTL%RowType;
12 new_references IGS_EN_CAT_PRC_DTL%RowType;
13
14 PROCEDURE Set_Column_Values (
15 p_action IN VARCHAR2,
16 x_rowid IN VARCHAR2 ,
17 x_enrolment_cat IN VARCHAR2 ,
18 x_s_student_comm_type IN VARCHAR2 ,
19 x_enr_method_type IN VARCHAR2 ,
20 x_person_add_allow_ind IN VARCHAR2 ,
21 x_course_add_allow_ind IN VARCHAR2 ,
22 x_creation_date IN DATE ,
23 x_created_by IN NUMBER ,
24 x_last_update_date IN DATE ,
25 x_last_updated_by IN NUMBER ,
26 x_last_update_login IN NUMBER ,
27 x_enforce_date_alias IN VARCHAR2 ,
28 x_config_min_cp_valdn IN VARCHAR2
29 ) AS
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_EN_CAT_PRC_DTL
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 Open cur_old_ref_values;
43 Fetch cur_old_ref_values INTO old_references;
44 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
45 Close cur_old_ref_values;
46 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
47 IGS_GE_MSG_STACK.ADD;
48 App_Exception.Raise_Exception;
49 Return;
50 END IF;
51 Close cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.enrolment_cat := x_enrolment_cat;
55 new_references.s_student_comm_type := x_s_student_comm_type;
56 new_references.enr_method_type := x_enr_method_type;
57 new_references.person_add_allow_ind := x_person_add_allow_ind;
58 new_references.course_add_allow_ind := x_course_add_allow_ind;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69 new_references.enforce_date_alias := x_enforce_date_alias;
70 new_references.config_min_cp_valdn := x_config_min_cp_valdn;
71
72
73 END Set_Column_Values;
74
75 -- Trigger description :-
76 -- "OSS_TST".trg_ecpd_br_i
77 -- BEFORE INSERT
78 -- ON IGS_EN_CAT_PRC_DTL
79 -- FOR EACH ROW
80
81 PROCEDURE BeforeRowInsert1(
82 p_inserting IN BOOLEAN ,
83 p_updating IN BOOLEAN ,
84 p_deleting IN BOOLEAN
85 ) AS
86 v_message_name varchar2(30);
87 BEGIN
88 -- If trigger has not been disabled, perform required processing
89 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_CAT_PRC_DTL') THEN
90 IF p_inserting THEN
91 -- Validate the enrolment method type
92 IF IGS_EN_VAL_ECPD.enrp_val_ecpd_emt(
93 new_references.enr_method_type,
94 v_message_name) = FALSE THEN
95 Fnd_Message.Set_Name('IGS', v_message_name);
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 END IF;
99 END IF;
100 END IF;
101
102
103 END BeforeRowInsert1;
104
105 -- Trigger description :-
106 -- "OSS_TST".trg_ecpd_as_i
107 -- AFTER INSERT
108 -- ON IGS_EN_CAT_PRC_DTL
109
110 PROCEDURE AfterStmtInsert2(
111 p_inserting IN BOOLEAN ,
112 p_updating IN BOOLEAN ,
113 p_deleting IN BOOLEAN
114 ) AS
115 CURSOR c_ecpd IS
116 SELECT enrolment_cat,
117 enr_method_type,
118 s_student_comm_type
119 FROM IGS_EN_CAT_PRC_DTL;
120 v_message_name varchar2(30);
121 BEGIN
122 -- If trigger has not been disabled, perform required processing
123 IF p_inserting AND
124 igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_CAT_PRC_DTL') THEN
125 FOR v_ecpd_rec IN c_ecpd
126 LOOP
127 -- Validate the enrolment method type
128 IF IGS_EN_VAL_ECPD.enrp_val_ecpd_comm(
129 v_ecpd_rec.enrolment_cat,
130 v_ecpd_rec.enr_method_type,
131 v_ecpd_rec.s_student_comm_type,
132 v_message_name) = FALSE THEN
133 Fnd_Message.Set_Name('IGS', v_message_name);
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IF;
137 END LOOP;
138 END IF;
139
140
141 END AfterStmtInsert2;
142
143 PROCEDURE Check_Constraints (
144 Column_Name IN VARCHAR2 ,
145 Column_Value IN VARCHAR2
146 ) as
147
148 BEGIN
149
150 -- The following code checks for check constraints on the Columns.
151
152 IF column_name is NULL THEN
153 NULL;
154 ELSIF UPPER(column_name) = 'ENR_METHOD_TYPE' THEN
155 new_references.enr_method_type := column_value;
156 ELSIF UPPER(column_name) = 'ENROLMENT_CAT' THEN
157 new_references.enrolment_cat := column_value;
158 ELSIF UPPER(column_name) = 'COURSE_ADD_ALLOW_IND' THEN
159 new_references.course_add_allow_ind := column_value;
160 ELSIF UPPER(column_name) = 'PERSON_ADD_ALLOW_IND' THEN
161 new_references.person_add_allow_ind := column_value;
162 ELSIF UPPER(column_name) = 'S_STUDENT_COMM_TYPE' THEN
163 new_references.s_student_comm_type := column_value;
164 END IF;
165
166 IF ((UPPER (column_name) = 'S_STUDENT_COMM_TYPE') OR (column_name IS NULL)) THEN
167 IF new_references.s_student_comm_type NOT IN ( 'NEW' , 'RETURN' , 'ALL' ) THEN
168 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 END IF;
173
174 IF ((UPPER (column_name) = 'PERSON_ADD_ALLOW_IND') OR (column_name IS NULL)) THEN
175 IF new_references.person_add_allow_ind NOT IN ( 'Y' , 'N' ) THEN
176 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception;
179 END IF;
180 END IF;
181
182 IF ((UPPER (column_name) = 'COURSE_ADD_ALLOW_IND') OR (column_name IS NULL)) THEN
183 IF new_references.course_add_allow_ind NOT IN ( 'Y' , 'N' ) THEN
184 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
185 IGS_GE_MSG_STACK.ADD;
186 App_Exception.Raise_Exception;
187 END IF;
188 END IF;
189
190 IF ((UPPER (column_name) = 'ENROLMENT_CAT') OR (column_name IS NULL)) THEN
191 IF (new_references.enrolment_cat <> UPPER (new_references.enrolment_cat)) THEN
192 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
193 IGS_GE_MSG_STACK.ADD;
194 App_Exception.Raise_Exception;
195 END IF;
196 END IF;
197
198 IF ((UPPER (column_name) = 'ENR_METHOD_TYPE') OR (column_name IS NULL)) THEN
199 IF (new_references.enr_method_type <> UPPER (new_references.enr_method_type)) THEN
200 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203 END IF;
204 END IF;
205
206
207 END Check_Constraints;
208
209 PROCEDURE Check_Parent_Existance AS
210 BEGIN
211
212 IF (((old_references.enr_method_type = new_references.enr_method_type)) OR
213 ((new_references.enr_method_type IS NULL))) THEN
214 NULL;
215 ELSE
216 IF NOT IGS_EN_METHOD_TYPE_PKG.Get_PK_For_Validation (
217 new_references.enr_method_type
218 ) THEN
219
220 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223
224 END IF;
225
226 END IF;
227
228 IF (((old_references.enrolment_cat = new_references.enrolment_cat)) OR
229 ((new_references.enrolment_cat IS NULL))) THEN
230 NULL;
231 ELSE
232 IF NOT IGS_EN_ENROLMENT_CAT_PKG.Get_PK_For_Validation (
233 new_references.enrolment_cat
234 ) THEN
235 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238
239 END IF;
240
241 END IF;
242
243 END Check_Parent_Existance;
244
245 PROCEDURE Check_Child_Existance AS
246 BEGIN
247
248 IGS_EN_CAT_PRC_STEP_PKG.GET_FK_IGS_EN_CAT_PRC_DTL (
249 old_references.enrolment_cat,
250 old_references.s_student_comm_type,
251 old_references.enr_method_type
252 );
253
254 END Check_Child_Existance;
255
256 FUNCTION Get_PK_For_Validation (
257 x_enrolment_cat IN VARCHAR2,
258 x_s_student_comm_type IN VARCHAR2,
259 x_enr_method_type IN VARCHAR2
260 ) RETURN BOOLEAN AS
261
262 CURSOR cur_rowid IS
263 SELECT rowid
264 FROM IGS_EN_CAT_PRC_DTL
265 WHERE enrolment_cat = x_enrolment_cat
266 AND s_student_comm_type = x_s_student_comm_type
267 AND enr_method_type = x_enr_method_type
268 FOR UPDATE NOWAIT;
269
270 lv_rowid cur_rowid%RowType;
271
272 BEGIN
273
274 Open cur_rowid;
275 Fetch cur_rowid INTO lv_rowid;
276
277 IF (cur_rowid%FOUND) THEN
278 Close cur_rowid;
279 Return(TRUE);
280 ELSE
281 Close cur_rowid;
282 Return(FALSE);
283 END IF;
284
285 END Get_PK_For_Validation;
286
287 PROCEDURE GET_FK_IGS_EN_METHOD_TYPE (
288 x_enr_method_type IN VARCHAR2
289 ) AS
290
291 CURSOR cur_rowid IS
292 SELECT rowid
293 FROM IGS_EN_CAT_PRC_DTL
294 WHERE enr_method_type = x_enr_method_type ;
295
296 lv_rowid cur_rowid%RowType;
297
298 BEGIN
299
300 Open cur_rowid;
301 Fetch cur_rowid INTO lv_rowid;
302 IF (cur_rowid%FOUND) THEN
303 Close cur_rowid;
304 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ECPD_EMT_FK');
305 IGS_GE_MSG_STACK.ADD;
306 App_Exception.Raise_Exception;
307 Return;
308 END IF;
309 Close cur_rowid;
310
311 END GET_FK_IGS_EN_METHOD_TYPE;
312
313 PROCEDURE GET_FK_IGS_EN_ENROLMENT_CAT (
314 x_enrolment_cat IN VARCHAR2
315 ) AS
316
317 CURSOR cur_rowid IS
318 SELECT rowid
319 FROM IGS_EN_CAT_PRC_DTL
320 WHERE enrolment_cat = x_enrolment_cat ;
321
322 lv_rowid cur_rowid%RowType;
323
324 BEGIN
325
326 Open cur_rowid;
327 Fetch cur_rowid INTO lv_rowid;
328 IF (cur_rowid%FOUND) THEN
329 Close cur_rowid;
330 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ECPD_EC_FK');
331 IGS_GE_MSG_STACK.ADD;
332 App_Exception.Raise_Exception;
333 Return;
334 END IF;
335 Close cur_rowid;
336
337 END GET_FK_IGS_EN_ENROLMENT_CAT;
338
339
340 PROCEDURE get_fk_igs_ca_da (
341 x_dt_alias IN VARCHAR2
342 ) AS
343
344 CURSOR cur_rowid IS
345 SELECT rowid
346 FROM igs_en_cat_prc_dtl
347 WHERE ((enforce_date_alias = x_dt_alias));
348
349 lv_rowid cur_rowid%RowType;
350
351 BEGIN
352
353 OPEN cur_rowid;
354 FETCH cur_rowid INTO lv_rowid;
355 IF (cur_rowid%FOUND) THEN
356 CLOSE cur_rowid;
357 fnd_message.set_name ('IGS', 'IGS_EN_ECPD_DA_FK');
358 igs_ge_msg_stack.add;
359 app_exception.raise_exception;
360 RETURN;
361 END IF;
362 CLOSE cur_rowid;
363
364 END get_fk_igs_ca_da;
365
366
367 PROCEDURE Before_DML (
368 p_action IN VARCHAR2,
369 x_rowid IN VARCHAR2 ,
370 x_enrolment_cat IN VARCHAR2 ,
371 x_s_student_comm_type IN VARCHAR2 ,
372 x_enr_method_type IN VARCHAR2 ,
373 x_person_add_allow_ind IN VARCHAR2 ,
374 x_course_add_allow_ind IN VARCHAR2 ,
375 x_creation_date IN DATE ,
376 x_created_by IN NUMBER ,
377 x_last_update_date IN DATE ,
378 x_last_updated_by IN NUMBER ,
379 x_last_update_login IN NUMBER ,
380 x_enforce_date_alias IN VARCHAR2 ,
381 x_config_min_cp_valdn IN VARCHAR2
382 ) AS
383 BEGIN
384
385 Set_Column_Values (
386 p_action,
387 x_rowid,
388 x_enrolment_cat,
389 x_s_student_comm_type,
390 x_enr_method_type,
391 x_person_add_allow_ind,
392 x_course_add_allow_ind,
393 x_creation_date,
394 x_created_by,
395 x_last_update_date,
396 x_last_updated_by,
397 x_last_update_login,
398 x_enforce_date_alias ,
399 x_config_min_cp_valdn
400 );
401
402 IF (p_action = 'INSERT') THEN
403 -- Call all the procedures related to Before Insert.
404 BeforeRowInsert1 ( p_inserting => TRUE ,
405 p_updating => FALSE,
406 p_deleting => FALSE );
407 IF Get_PK_For_Validation(
408 new_references.enrolment_cat,
409 new_references.s_student_comm_type,
410 new_references.enr_method_type
411 ) THEN
412 Fnd_message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
413 IGS_GE_MSG_STACK.ADD;
414 App_Exception.Raise_Exception;
415
416 END IF;
417
418 Check_Constraints;
419 Check_Parent_Existance;
420 ELSIF (p_action = 'UPDATE') THEN
421 -- Call all the procedures related to Before Update.
422 Check_Constraints;
423 Check_Parent_Existance;
424 ELSIF (p_action = 'DELETE') THEN
425 -- Call all the procedures related to Before Delete.
426
427 Check_Child_Existance;
428
429 ELSIF (p_action = 'VALIDATE_INSERT') THEN
430 IF Get_PK_For_Validation (
431 new_references.enrolment_cat,
432 new_references.s_student_comm_type,
433 new_references.enr_method_type
434 ) THEN
435 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
436 IGS_GE_MSG_STACK.ADD;
437 App_Exception.Raise_Exception;
438 END IF;
439 Check_Constraints;
440 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
441 Check_Constraints;
442 ELSIF (p_action = 'VALIDATE_DELETE') THEN
443
444 Check_Child_Existance;
445 END IF;
446
447 END Before_DML;
448
449 PROCEDURE After_DML (
450 p_action IN VARCHAR2,
451 x_rowid IN VARCHAR2
452 )AS
453 BEGIN
454
455 l_rowid := x_rowid;
456
457 IF (p_action = 'INSERT') THEN
458 -- Call all the procedures related to After Insert.
459 AfterStmtInsert2 ( p_inserting => TRUE,
460 p_updating => FALSE,
461 p_deleting => FALSE);
462 ELSIF (p_action = 'UPDATE') THEN
463 -- Call all the procedures related to After Update.
464 Null;
465 ELSIF (p_action = 'DELETE') THEN
466 -- Call all the procedures related to After Delete.
467 Null;
468 END IF;
469
470 END After_DML;
471
472 procedure INSERT_ROW (
473 X_ROWID in out NOCOPY VARCHAR2,
474 X_ENROLMENT_CAT in VARCHAR2,
475 X_S_STUDENT_COMM_TYPE in VARCHAR2,
476 X_ENR_METHOD_TYPE in VARCHAR2,
477 X_PERSON_ADD_ALLOW_IND in VARCHAR2,
478 X_COURSE_ADD_ALLOW_IND in VARCHAR2,
479 X_MODE in VARCHAR2 ,
480 X_ENFORCE_DATE_ALIAS IN VARCHAR2 ,
481 X_CONFIG_MIN_CP_VALDN IN VARCHAR2
482 ) AS
483 cursor C is select ROWID from IGS_EN_CAT_PRC_DTL
484 where ENROLMENT_CAT = X_ENROLMENT_CAT
485 and S_STUDENT_COMM_TYPE = X_S_STUDENT_COMM_TYPE
486 and ENR_METHOD_TYPE = X_ENR_METHOD_TYPE;
487 X_LAST_UPDATE_DATE DATE;
488 X_LAST_UPDATED_BY NUMBER;
489 X_LAST_UPDATE_LOGIN NUMBER;
490 begin
491 X_LAST_UPDATE_DATE := SYSDATE;
492 if(X_MODE = 'I') then
493 X_LAST_UPDATED_BY := 1;
494 X_LAST_UPDATE_LOGIN := 0;
495 elsif (X_MODE = 'R') then
496 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
497 if X_LAST_UPDATED_BY is NULL then
498 X_LAST_UPDATED_BY := -1;
499 end if;
500 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
501 if X_LAST_UPDATE_LOGIN is NULL then
502 X_LAST_UPDATE_LOGIN := -1;
503 end if;
504 else
505 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
506 IGS_GE_MSG_STACK.ADD;
507 app_exception.raise_exception;
508 end if;
509
510 Before_DML(
511 p_action => 'INSERT',
512 x_rowid => X_ROWID,
513 x_enrolment_cat => X_ENROLMENT_CAT,
514 x_s_student_comm_type => X_S_STUDENT_COMM_TYPE,
515 x_enr_method_type => X_ENR_METHOD_TYPE,
516 x_person_add_allow_ind => X_PERSON_ADD_ALLOW_IND,
517 x_course_add_allow_ind => X_COURSE_ADD_ALLOW_IND,
518 x_creation_date => X_LAST_UPDATE_DATE,
519 x_created_by => X_LAST_UPDATED_BY,
520 x_last_update_date =>X_LAST_UPDATE_DATE,
521 x_last_updated_by => X_LAST_UPDATED_BY,
522 x_last_update_login => X_LAST_UPDATE_LOGIN,
523 x_enforce_date_alias => X_ENFORCE_DATE_ALIAS,
524 x_config_min_cp_valdn => X_CONFIG_MIN_CP_VALDN
525 );
526
527 insert into IGS_EN_CAT_PRC_DTL (
528 ENROLMENT_CAT,
529 S_STUDENT_COMM_TYPE,
530 ENR_METHOD_TYPE,
531 PERSON_ADD_ALLOW_IND,
532 COURSE_ADD_ALLOW_IND,
533 CREATION_DATE,
534 CREATED_BY,
535 LAST_UPDATE_DATE,
536 LAST_UPDATED_BY,
537 LAST_UPDATE_LOGIN,
538 ENFORCE_DATE_ALIAS,
539 CONFIG_MIN_CP_VALDN
540 ) values (
541 NEW_REFERENCES.ENROLMENT_CAT,
542 NEW_REFERENCES.S_STUDENT_COMM_TYPE,
543 NEW_REFERENCES.ENR_METHOD_TYPE,
544 NEW_REFERENCES.PERSON_ADD_ALLOW_IND,
545 NEW_REFERENCES.COURSE_ADD_ALLOW_IND,
546 X_LAST_UPDATE_DATE,
547 X_LAST_UPDATED_BY,
548 X_LAST_UPDATE_DATE,
549 X_LAST_UPDATED_BY,
550 X_LAST_UPDATE_LOGIN,
551 NEW_REFERENCES.ENFORCE_DATE_ALIAS,
552 NEW_REFERENCES.CONFIG_MIN_CP_VALDN
553 );
554
555 open c;
556 fetch c into X_ROWID;
557 if (c%notfound) then
558 close c;
559 raise no_data_found;
560 end if;
561 close c;
562
563 After_DML(
564 p_action => 'INSERT',
565 x_rowid => X_ROWID
566 );
567
568
569 end INSERT_ROW;
570
571 procedure LOCK_ROW (
572 X_ROWID in VARCHAR2,
573 X_ENROLMENT_CAT in VARCHAR2,
574 X_S_STUDENT_COMM_TYPE in VARCHAR2,
575 X_ENR_METHOD_TYPE in VARCHAR2,
576 X_PERSON_ADD_ALLOW_IND in VARCHAR2,
577 X_COURSE_ADD_ALLOW_IND in VARCHAR2,
578 X_ENFORCE_DATE_ALIAS IN VARCHAR2 ,
579 X_CONFIG_MIN_CP_VALDN IN VARCHAR2
580 ) AS
581 cursor c1 is select
582 PERSON_ADD_ALLOW_IND,
583 COURSE_ADD_ALLOW_IND,
584 ENFORCE_DATE_ALIAS,
585 CONFIG_MIN_CP_VALDN
586 from IGS_EN_CAT_PRC_DTL
587 where ROWID = X_ROWID
588 for update nowait;
589 tlinfo c1%rowtype;
590
591 begin
592 open c1;
593 fetch c1 into tlinfo;
594 if (c1%notfound) then
595 close c1;
596 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
597 IGS_GE_MSG_STACK.ADD;
598 app_exception.raise_exception;
599 return;
600 end if;
601 close c1;
602
603 if ( (tlinfo.PERSON_ADD_ALLOW_IND = X_PERSON_ADD_ALLOW_IND)
604 AND (tlinfo.COURSE_ADD_ALLOW_IND = X_COURSE_ADD_ALLOW_IND)
605 AND ((tlinfo.ENFORCE_DATE_ALIAS = X_ENFORCE_DATE_ALIAS) OR ((tlinfo.ENFORCE_DATE_ALIAS IS NULL) AND (X_ENFORCE_DATE_ALIAS IS NULL)))
606 AND ((tlinfo.CONFIG_MIN_CP_VALDN = X_CONFIG_MIN_CP_VALDN) OR ((tlinfo.CONFIG_MIN_CP_VALDN IS NULL) AND (X_CONFIG_MIN_CP_VALDN IS NULL)))
607 ) THEN
608 NULL;
609 else
610 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
611 IGS_GE_MSG_STACK.ADD;
612 app_exception.raise_exception;
613 end if;
614 return;
615 end LOCK_ROW;
616
617 procedure UPDATE_ROW (
618 X_ROWID in VARCHAR2,
619 X_ENROLMENT_CAT in VARCHAR2,
620 X_S_STUDENT_COMM_TYPE in VARCHAR2,
621 X_ENR_METHOD_TYPE in VARCHAR2,
622 X_PERSON_ADD_ALLOW_IND in VARCHAR2,
623 X_COURSE_ADD_ALLOW_IND in VARCHAR2,
624 X_MODE in VARCHAR2 ,
625 X_ENFORCE_DATE_ALIAS IN VARCHAR2 ,
626 X_CONFIG_MIN_CP_VALDN IN VARCHAR2
627 ) AS
628 X_LAST_UPDATE_DATE DATE;
629 X_LAST_UPDATED_BY NUMBER;
630 X_LAST_UPDATE_LOGIN NUMBER;
631 begin
632 X_LAST_UPDATE_DATE := SYSDATE;
633 if(X_MODE = 'I') then
634 X_LAST_UPDATED_BY := 1;
635 X_LAST_UPDATE_LOGIN := 0;
636 elsif (X_MODE = 'R') then
637 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
638 if X_LAST_UPDATED_BY is NULL then
639 X_LAST_UPDATED_BY := -1;
640 end if;
641 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
642 if X_LAST_UPDATE_LOGIN is NULL then
643 X_LAST_UPDATE_LOGIN := -1;
644 end if;
645 else
646 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
647 IGS_GE_MSG_STACK.ADD;
648 app_exception.raise_exception;
649 end if;
650
651 Before_DML(
652 p_action => 'UPDATE',
653 x_rowid => X_ROWID,
654 x_enrolment_cat => X_ENROLMENT_CAT,
655 x_s_student_comm_type => X_S_STUDENT_COMM_TYPE,
656 x_enr_method_type => X_ENR_METHOD_TYPE,
657 x_person_add_allow_ind => X_PERSON_ADD_ALLOW_IND,
658 x_course_add_allow_ind => X_COURSE_ADD_ALLOW_IND,
659 x_creation_date => X_LAST_UPDATE_DATE,
660 x_created_by => X_LAST_UPDATED_BY,
661 x_last_update_date =>X_LAST_UPDATE_DATE,
662 x_last_updated_by => X_LAST_UPDATED_BY,
663 x_last_update_login => X_LAST_UPDATE_LOGIN,
664 x_enforce_date_alias => X_ENFORCE_DATE_ALIAS,
665 x_config_min_cp_valdn => X_CONFIG_MIN_CP_VALDN
666 );
667
668 update IGS_EN_CAT_PRC_DTL set
669 PERSON_ADD_ALLOW_IND = NEW_REFERENCES.PERSON_ADD_ALLOW_IND,
670 COURSE_ADD_ALLOW_IND = NEW_REFERENCES.COURSE_ADD_ALLOW_IND,
671 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
672 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
673 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
674 ENFORCE_DATE_ALIAS = NEW_REFERENCES.ENFORCE_DATE_ALIAS,
675 CONFIG_MIN_CP_VALDN = NEW_REFERENCES.CONFIG_MIN_CP_VALDN
676 where ROWID = X_ROWID;
677
678 if (sql%notfound) then
679 raise no_data_found;
680 end if;
681
682 After_DML(
683 p_action => 'UPDATE',
684 x_rowid => X_ROWID
685 );
686
687 end UPDATE_ROW;
688
689 procedure ADD_ROW (
690 X_ROWID in out NOCOPY VARCHAR2,
691 X_ENROLMENT_CAT in VARCHAR2,
692 X_S_STUDENT_COMM_TYPE in VARCHAR2,
693 X_ENR_METHOD_TYPE in VARCHAR2,
694 X_PERSON_ADD_ALLOW_IND in VARCHAR2,
695 X_COURSE_ADD_ALLOW_IND in VARCHAR2,
696 X_MODE in VARCHAR2 ,
697 X_ENFORCE_DATE_ALIAS IN VARCHAR2 ,
698 X_CONFIG_MIN_CP_VALDN IN VARCHAR2
699 ) AS
700 cursor c1 is select rowid from IGS_EN_CAT_PRC_DTL
701 where ENROLMENT_CAT = X_ENROLMENT_CAT
702 and S_STUDENT_COMM_TYPE = X_S_STUDENT_COMM_TYPE
703 and ENR_METHOD_TYPE = X_ENR_METHOD_TYPE
704 ;
705
706 begin
707 open c1;
708 fetch c1 into X_ROWID;
709 if (c1%notfound) then
710 close c1;
711 INSERT_ROW (
712 X_ROWID,
713 X_ENROLMENT_CAT,
714 X_S_STUDENT_COMM_TYPE,
715 X_ENR_METHOD_TYPE,
716 X_PERSON_ADD_ALLOW_IND,
717 X_COURSE_ADD_ALLOW_IND,
718 X_MODE,
719 X_ENFORCE_DATE_ALIAS,
720 X_CONFIG_MIN_CP_VALDN
721 );
722 return;
723 end if;
724 close c1;
725 UPDATE_ROW (
726 X_ROWID,
727 X_ENROLMENT_CAT,
728 X_S_STUDENT_COMM_TYPE,
729 X_ENR_METHOD_TYPE,
730 X_PERSON_ADD_ALLOW_IND,
731 X_COURSE_ADD_ALLOW_IND,
732 X_MODE,
733 X_ENFORCE_DATE_ALIAS,
734 X_CONFIG_MIN_CP_VALDN
735 );
736 end ADD_ROW;
737
738 procedure DELETE_ROW (
739 X_ROWID in VARCHAR2
740 ) AS
741 begin
742 Before_DML(
743 p_action => 'DELETE',
744 x_rowid => X_ROWID
745 );
746
747
748 delete from IGS_EN_CAT_PRC_DTL
749 where ROWID = X_ROWID;
750 if (sql%notfound) then
751 raise no_data_found;
752 end if;
753 After_DML(
754 p_action => 'DELETE',
755 x_rowid => X_ROWID
756 );
757
758 end DELETE_ROW;
759
760 end IGS_EN_CAT_PRC_DTL_PKG;