[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SC_ATMPT_ENR_PKG
Source
1 package body IGS_AS_SC_ATMPT_ENR_PKG AS
2 /* $Header: IGSDI18B.pls 120.0 2005/07/05 12:17:44 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_SC_ATMPT_ENR%RowType;
6 new_references IGS_AS_SC_ATMPT_ENR%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
15 x_enrolled_dt IN DATE DEFAULT NULL,
16 x_enr_form_due_dt IN DATE DEFAULT NULL,
17 x_enr_pckg_prod_dt IN DATE DEFAULT NULL,
18 x_enr_form_received_dt IN DATE DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL
24 ) AS
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_AS_SC_ATMPT_ENR
28 WHERE rowid = x_rowid;
29 BEGIN
30 l_rowid := x_rowid;
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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
37 IGS_GE_MSG_STACK.ADD;
38 Close cur_old_ref_values;
39 APP_EXCEPTION.RAISE_EXCEPTION;
40
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44 -- Populate New Values.
45 new_references.person_id := x_person_id;
46 new_references.course_cd := x_course_cd;
47 new_references.CAL_TYPE := x_cal_type;
48 new_references.ci_sequence_number := x_ci_sequence_number;
49 new_references.ENROLMENT_CAT := x_enrolment_cat;
50 new_references.enrolled_dt := x_enrolled_dt;
51 new_references.enr_form_due_dt := x_enr_form_due_dt;
52 new_references.enr_pckg_prod_dt := x_enr_pckg_prod_dt;
53 new_references.enr_form_received_dt := x_enr_form_received_dt;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64 END Set_Column_Values;
65
66 PROCEDURE Check_Parent_Existance IS
67 BEGIN
68 IF (((old_references.CAL_TYPE = new_references.CAL_TYPE) AND
69 (old_references.ci_sequence_number = new_references.ci_sequence_number)) AND
70 ((new_references.CAL_TYPE IS NULL) OR
71 (new_references.ci_sequence_number IS NULL))) THEN
72 NULL;
73 ELSIF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
74 new_references.CAL_TYPE,
75 new_references.ci_sequence_number
76 ) THEN
77 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
78 IGS_GE_MSG_STACK.ADD;
79 APP_EXCEPTION.RAISE_EXCEPTION;
80 END IF;
81 IF (((old_references.ENROLMENT_CAT = new_references.ENROLMENT_CAT)) OR
82 ((new_references.ENROLMENT_CAT IS NULL))) THEN
83 NULL;
84 ELSIF NOT IGS_EN_ENROLMENT_CAT_PKG.Get_PK_For_Validation (
85 new_references.ENROLMENT_CAT
86 ) THEN
87 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
88 IGS_GE_MSG_STACK.ADD;
89 APP_EXCEPTION.RAISE_EXCEPTION;
90 END IF;
91 IF (((old_references.person_id = new_references.person_id) OR
92 (old_references.course_cd = new_references.course_cd)) AND
93 ((new_references.person_id IS NULL) OR
94 (new_references.course_cd IS NULL))) THEN
95 NULL;
96 ELSIF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
97 new_references.person_id,
98 new_references.course_cd
99 ) THEN
100 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
101 IGS_GE_MSG_STACK.ADD;
102 APP_EXCEPTION.RAISE_EXCEPTION;
103 END IF;
104 END Check_Parent_Existance;
105 FUNCTION Get_PK_For_Validation (
106 x_person_id IN NUMBER,
107 x_course_cd IN VARCHAR2,
108 x_cal_type IN VARCHAR2,
109 x_ci_sequence_number IN NUMBER
110 ) RETURN BOOLEAN AS
111 CURSOR cur_rowid IS
112 SELECT rowid
113 FROM IGS_AS_SC_ATMPT_ENR
114 WHERE person_id = x_person_id
115 AND course_cd = x_course_cd
116 AND CAL_TYPE = x_cal_type
117 AND ci_sequence_number = x_ci_sequence_number
118 FOR UPDATE NOWAIT;
119 lv_rowid cur_rowid%RowType;
120 BEGIN
121 Open cur_rowid;
122 Fetch cur_rowid INTO lv_rowid;
123 IF (cur_rowid%FOUND) THEN
124 Close cur_rowid;
125 Return (TRUE);
126 ELSE
127 Close cur_rowid;
128 Return (FALSE);
129 END IF;
130 END Get_PK_For_Validation;
131 PROCEDURE GET_FK_IGS_CA_INST (
132 x_cal_type IN VARCHAR2,
133 x_sequence_number IN NUMBER
134 ) AS
135 CURSOR cur_rowid IS
136 SELECT rowid
137 FROM IGS_AS_SC_ATMPT_ENR
138 WHERE CAL_TYPE = x_cal_type
139 AND ci_sequence_number = x_sequence_number ;
140 lv_rowid cur_rowid%RowType;
141 BEGIN
142 Open cur_rowid;
143 Fetch cur_rowid INTO lv_rowid;
144 IF (cur_rowid%FOUND) THEN
145 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAE_CI_FK');
146 IGS_GE_MSG_STACK.ADD;
147 Close cur_rowid;
148 APP_EXCEPTION.RAISE_EXCEPTION;
149
150 Return;
151 END IF;
152 Close cur_rowid;
153 END GET_FK_IGS_CA_INST;
154 PROCEDURE GET_FK_IGS_EN_ENROLMENT_CAT (
155 x_enrolment_cat IN VARCHAR2
156 ) AS
157 CURSOR cur_rowid IS
158 SELECT rowid
159 FROM IGS_AS_SC_ATMPT_ENR
160 WHERE ENROLMENT_CAT = x_enrolment_cat ;
161 lv_rowid cur_rowid%RowType;
162 BEGIN
163 Open cur_rowid;
164 Fetch cur_rowid INTO lv_rowid;
165 IF (cur_rowid%FOUND) THEN
166 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAE_EC_FK');
167 IGS_GE_MSG_STACK.ADD;
168 Close cur_rowid;
169 APP_EXCEPTION.RAISE_EXCEPTION;
170
171 Return;
172 END IF;
173 Close cur_rowid;
174 END GET_FK_IGS_EN_ENROLMENT_CAT;
175 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
176 x_person_id IN NUMBER,
177 x_course_cd IN VARCHAR2
178 ) AS
179 CURSOR cur_rowid IS
180 SELECT rowid
181 FROM IGS_AS_SC_ATMPT_ENR
182 WHERE person_id = x_person_id
183 AND course_cd = x_course_cd ;
184 lv_rowid cur_rowid%RowType;
185 BEGIN
186 Open cur_rowid;
187 Fetch cur_rowid INTO lv_rowid;
188 IF (cur_rowid%FOUND) THEN
189 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAE_SCA_FK');
190 IGS_GE_MSG_STACK.ADD;
191 Close cur_rowid;
192 APP_EXCEPTION.RAISE_EXCEPTION;
193
194 Return;
195 END IF;
196 Close cur_rowid;
197 END GET_FK_IGS_EN_STDNT_PS_ATT;
198 PROCEDURE Before_DML (
199 p_action IN VARCHAR2,
200 x_rowid IN VARCHAR2 DEFAULT NULL,
201 x_person_id IN NUMBER DEFAULT NULL,
202 x_course_cd IN VARCHAR2 DEFAULT NULL,
203 x_cal_type IN VARCHAR2 DEFAULT NULL,
204 x_ci_sequence_number IN NUMBER DEFAULT NULL,
205 x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
206 x_enrolled_dt IN DATE DEFAULT NULL,
207 x_enr_form_due_dt IN DATE DEFAULT NULL,
208 x_enr_pckg_prod_dt IN DATE DEFAULT NULL,
209 x_enr_form_received_dt IN DATE DEFAULT NULL,
210 x_creation_date IN DATE DEFAULT NULL,
211 x_created_by IN NUMBER DEFAULT NULL,
212 x_last_update_date IN DATE DEFAULT NULL,
213 x_last_updated_by IN NUMBER DEFAULT NULL,
214 x_last_update_login IN NUMBER DEFAULT NULL
215 ) AS
216 BEGIN
217 Set_Column_Values (
218 p_action,
219 x_rowid,
220 x_person_id,
221 x_course_cd,
222 x_cal_type,
223 x_ci_sequence_number,
224 x_enrolment_cat,
225 x_enrolled_dt,
226 x_enr_form_due_dt,
227 x_enr_pckg_prod_dt,
228 x_enr_form_received_dt,
229 x_creation_date,
230 x_created_by,
231 x_last_update_date,
232 x_last_updated_by,
233 x_last_update_login
234 );
235 IF (p_action = 'INSERT') THEN
236 -- Call all the procedures related to Before Insert.
237
238 IF Get_PK_For_Validation ( NEW_REFERENCES.person_id ,
239 NEW_REFERENCES.course_cd ,
240 NEW_REFERENCES.cal_type ,
241 NEW_REFERENCES.ci_sequence_number
242 ) THEN
243 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
244 IGS_GE_MSG_STACK.ADD;
245 APP_EXCEPTION.RAISE_EXCEPTION;
246 END IF;
247
248 Check_Constraints;
249
250 Check_Parent_Existance;
251 ELSIF (p_action = 'UPDATE') THEN
252 -- Call all the procedures related to Before Update.
253
254 Check_Constraints;
255 Check_Parent_Existance;
256
257 ELSIF (p_action = 'VALIDATE_INSERT') THEN
258 IF Get_PK_For_Validation (
259 NEW_REFERENCES.person_id ,
260 NEW_REFERENCES.course_cd ,
261 NEW_REFERENCES.cal_type ,
262 NEW_REFERENCES.ci_sequence_number ) THEN
263 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
264 IGS_GE_MSG_STACK.ADD;
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 END IF;
267
268 Check_Constraints;
269 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
270 Check_Constraints;
271
272 END IF;
273 END Before_DML;
274
275 procedure INSERT_ROW (
276 X_ROWID in out NOCOPY VARCHAR2,
277 X_PERSON_ID in NUMBER,
278 X_COURSE_CD in VARCHAR2,
279 X_CAL_TYPE in VARCHAR2,
280 X_CI_SEQUENCE_NUMBER in NUMBER,
281 X_ENROLMENT_CAT in VARCHAR2,
282 X_ENROLLED_DT in DATE,
283 X_ENR_FORM_DUE_DT in DATE,
284 X_ENR_PCKG_PROD_DT in DATE,
285 X_ENR_FORM_RECEIVED_DT in DATE,
286 X_MODE in VARCHAR2 default 'R'
287 ) AS
288 cursor C is select ROWID from IGS_AS_SC_ATMPT_ENR
289 where PERSON_ID = X_PERSON_ID
290 and COURSE_CD = X_COURSE_CD
291 and CAL_TYPE = X_CAL_TYPE
292 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER;
293 X_LAST_UPDATE_DATE DATE;
294 X_LAST_UPDATED_BY NUMBER;
295 X_LAST_UPDATE_LOGIN NUMBER;
296 X_REQUEST_ID NUMBER;
297 X_PROGRAM_ID NUMBER;
298 X_PROGRAM_APPLICATION_ID NUMBER;
299 X_PROGRAM_UPDATE_DATE DATE;
300 begin
301 X_LAST_UPDATE_DATE := SYSDATE;
302 if(X_MODE = 'I') then
303 X_LAST_UPDATED_BY := 1;
304 X_LAST_UPDATE_LOGIN := 0;
305 elsif (X_MODE IN ('R', 'S')) then
306 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
307 if X_LAST_UPDATED_BY is NULL then
308 X_LAST_UPDATED_BY := -1;
309 end if;
310 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
311 if X_LAST_UPDATE_LOGIN is NULL then
312 X_LAST_UPDATE_LOGIN := -1;
313 end if;
314 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
315 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
316 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
317 if (X_REQUEST_ID = -1) then
318 X_REQUEST_ID := NULL;
319 X_PROGRAM_ID := NULL;
320 X_PROGRAM_APPLICATION_ID := NULL;
321 X_PROGRAM_UPDATE_DATE := NULL;
322 else
323 X_PROGRAM_UPDATE_DATE := SYSDATE;
324 end if;
325 else
326 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
327 IGS_GE_MSG_STACK.ADD;
328 APP_EXCEPTION.RAISE_EXCEPTION;
329 end if;
330 Before_DML(
331 p_action=>'INSERT',
332 x_rowid=>X_ROWID,
333 x_cal_type=>X_CAL_TYPE,
334 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
335 x_course_cd=>X_COURSE_CD,
336 x_enr_form_due_dt=>X_ENR_FORM_DUE_DT,
337 x_enr_form_received_dt=>X_ENR_FORM_RECEIVED_DT,
338 x_enr_pckg_prod_dt=>X_ENR_PCKG_PROD_DT,
339 x_enrolled_dt=>X_ENROLLED_DT,
340 x_enrolment_cat=>X_ENROLMENT_CAT,
341 x_person_id=>X_PERSON_ID,
342 x_creation_date=>X_LAST_UPDATE_DATE,
343 x_created_by=>X_LAST_UPDATED_BY,
344 x_last_update_date=>X_LAST_UPDATE_DATE,
345 x_last_updated_by=>X_LAST_UPDATED_BY,
346 x_last_update_login=>X_LAST_UPDATE_LOGIN
347 );
348 IF (x_mode = 'S') THEN
349 igs_sc_gen_001.set_ctx('R');
350 END IF;
351 insert into IGS_AS_SC_ATMPT_ENR (
352 PERSON_ID,
353 COURSE_CD,
354 CAL_TYPE,
355 CI_SEQUENCE_NUMBER,
356 ENROLMENT_CAT,
357 ENROLLED_DT,
358 ENR_FORM_DUE_DT,
359 ENR_PCKG_PROD_DT,
360 ENR_FORM_RECEIVED_DT,
361 CREATION_DATE,
362 CREATED_BY,
363 LAST_UPDATE_DATE,
364 LAST_UPDATED_BY,
365 LAST_UPDATE_LOGIN,
366 REQUEST_ID,
367 PROGRAM_ID,
368 PROGRAM_APPLICATION_ID,
369 PROGRAM_UPDATE_DATE
370 ) values (
371 NEW_REFERENCES.PERSON_ID,
372 NEW_REFERENCES.COURSE_CD,
373 NEW_REFERENCES.CAL_TYPE,
374 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
375 NEW_REFERENCES.ENROLMENT_CAT,
376 NEW_REFERENCES.ENROLLED_DT,
377 NEW_REFERENCES.ENR_FORM_DUE_DT,
378 NEW_REFERENCES.ENR_PCKG_PROD_DT,
379 NEW_REFERENCES.ENR_FORM_RECEIVED_DT,
380 X_LAST_UPDATE_DATE,
381 X_LAST_UPDATED_BY,
382 X_LAST_UPDATE_DATE,
383 X_LAST_UPDATED_BY,
384 X_LAST_UPDATE_LOGIN,
385 X_REQUEST_ID,
386 X_PROGRAM_ID,
387 X_PROGRAM_APPLICATION_ID,
388 X_PROGRAM_UPDATE_DATE
389 );
390 IF (x_mode = 'S') THEN
391 igs_sc_gen_001.unset_ctx('R');
392 END IF;
393
394 open c;
395 fetch c into X_ROWID;
396 if (c%notfound) then
397 close c;
398 raise no_data_found;
399 end if;
400 close c;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
405 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
406 fnd_message.set_token ('ERR_CD', SQLCODE);
407 igs_ge_msg_stack.add;
408 igs_sc_gen_001.unset_ctx('R');
409 app_exception.raise_exception;
410 ELSE
411 igs_sc_gen_001.unset_ctx('R');
412 RAISE;
413 END IF;
414
415 end INSERT_ROW;
416 procedure LOCK_ROW (
417 X_ROWID in VARCHAR2,
418 X_PERSON_ID in NUMBER,
419 X_COURSE_CD in VARCHAR2,
420 X_CAL_TYPE in VARCHAR2,
421 X_CI_SEQUENCE_NUMBER in NUMBER,
422 X_ENROLMENT_CAT in VARCHAR2,
423 X_ENROLLED_DT in DATE,
424 X_ENR_FORM_DUE_DT in DATE,
425 X_ENR_PCKG_PROD_DT in DATE,
426 X_ENR_FORM_RECEIVED_DT in DATE
427 ) AS
428 cursor c1 is select
429 ENROLMENT_CAT,
430 ENROLLED_DT,
431 ENR_FORM_DUE_DT,
432 ENR_PCKG_PROD_DT,
433 ENR_FORM_RECEIVED_DT
434 from IGS_AS_SC_ATMPT_ENR
435 where ROWID = X_ROWID for update nowait;
436 tlinfo c1%rowtype;
437 begin
438 open c1;
439 fetch c1 into tlinfo;
440 if (c1%notfound) then
441 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
442 IGS_GE_MSG_STACK.ADD;
443 APP_EXCEPTION.RAISE_EXCEPTION;
444 close c1;
445 return;
446 end if;
447 close c1;
448 if ( (tlinfo.ENROLMENT_CAT = X_ENROLMENT_CAT)
449 AND ((tlinfo.ENROLLED_DT = X_ENROLLED_DT)
450 OR ((tlinfo.ENROLLED_DT is null)
451 AND (X_ENROLLED_DT is null)))
452 AND ((tlinfo.ENR_FORM_DUE_DT = X_ENR_FORM_DUE_DT)
453 OR ((tlinfo.ENR_FORM_DUE_DT is null)
454 AND (X_ENR_FORM_DUE_DT is null)))
455 AND ((tlinfo.ENR_PCKG_PROD_DT = X_ENR_PCKG_PROD_DT)
456 OR ((tlinfo.ENR_PCKG_PROD_DT is null)
457 AND (X_ENR_PCKG_PROD_DT is null)))
458 AND ((tlinfo.ENR_FORM_RECEIVED_DT = X_ENR_FORM_RECEIVED_DT)
459 OR ((tlinfo.ENR_FORM_RECEIVED_DT is null)
460 AND (X_ENR_FORM_RECEIVED_DT is null)))
461 ) then
462 null;
463 else
464 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
465 IGS_GE_MSG_STACK.ADD;
466 APP_EXCEPTION.RAISE_EXCEPTION;
467 end if;
468 return;
469 end LOCK_ROW;
470 procedure UPDATE_ROW (
471 X_ROWID in VARCHAR2,
472 X_PERSON_ID in NUMBER,
473 X_COURSE_CD in VARCHAR2,
474 X_CAL_TYPE in VARCHAR2,
475 X_CI_SEQUENCE_NUMBER in NUMBER,
476 X_ENROLMENT_CAT in VARCHAR2,
477 X_ENROLLED_DT in DATE,
478 X_ENR_FORM_DUE_DT in DATE,
479 X_ENR_PCKG_PROD_DT in DATE,
480 X_ENR_FORM_RECEIVED_DT in DATE,
481 X_MODE in VARCHAR2 default 'R'
482 ) AS
483 X_LAST_UPDATE_DATE DATE;
484 X_LAST_UPDATED_BY NUMBER;
485 X_LAST_UPDATE_LOGIN NUMBER;
486 X_REQUEST_ID NUMBER;
487 X_PROGRAM_ID NUMBER;
488 X_PROGRAM_APPLICATION_ID NUMBER;
489 X_PROGRAM_UPDATE_DATE DATE;
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 IN ('R', 'S')) 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 Before_DML(
510 p_action=>'UPDATE',
511 x_rowid=>X_ROWID,
512 x_cal_type=>X_CAL_TYPE,
513 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
514 x_course_cd=>X_COURSE_CD,
515 x_enr_form_due_dt=>X_ENR_FORM_DUE_DT,
516 x_enr_form_received_dt=>X_ENR_FORM_RECEIVED_DT,
517 x_enr_pckg_prod_dt=>X_ENR_PCKG_PROD_DT,
518 x_enrolled_dt=>X_ENROLLED_DT,
519 x_enrolment_cat=>X_ENROLMENT_CAT,
520 x_person_id=>X_PERSON_ID,
521 x_creation_date=>X_LAST_UPDATE_DATE,
522 x_created_by=>X_LAST_UPDATED_BY,
523 x_last_update_date=>X_LAST_UPDATE_DATE,
524 x_last_updated_by=>X_LAST_UPDATED_BY,
525 x_last_update_login=>X_LAST_UPDATE_LOGIN
526 );
527 if (X_MODE IN ('R', 'S')) then
528 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
529 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
530 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
531 if (X_REQUEST_ID = -1) then
532 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
533 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
534 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
535 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
536 else
537 X_PROGRAM_UPDATE_DATE := SYSDATE;
538 end if;
539 end if;
540 IF (x_mode = 'S') THEN
541 igs_sc_gen_001.set_ctx('R');
542 END IF;
543 update IGS_AS_SC_ATMPT_ENR set
544 ENROLMENT_CAT = NEW_REFERENCES.ENROLMENT_CAT,
545 ENROLLED_DT = NEW_REFERENCES.ENROLLED_DT,
546 ENR_FORM_DUE_DT = NEW_REFERENCES.ENR_FORM_DUE_DT,
547 ENR_PCKG_PROD_DT = NEW_REFERENCES.ENR_PCKG_PROD_DT,
548 ENR_FORM_RECEIVED_DT = NEW_REFERENCES.ENR_FORM_RECEIVED_DT,
549 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
550 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
551 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
552 REQUEST_ID = X_REQUEST_ID,
553 PROGRAM_ID = X_PROGRAM_ID,
554 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
555 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
556 where ROWID = X_ROWID;
557 if (sql%notfound) then
558 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
559 igs_ge_msg_stack.add;
560 igs_sc_gen_001.unset_ctx('R');
561 app_exception.raise_exception;
562 end if;
563 IF (x_mode = 'S') THEN
564 igs_sc_gen_001.unset_ctx('R');
565 END IF;
566
567
568 EXCEPTION
569 WHEN OTHERS THEN
570 IF (SQLCODE = (-28115)) THEN
571 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
572 fnd_message.set_token ('ERR_CD', SQLCODE);
573 igs_ge_msg_stack.add;
574 igs_sc_gen_001.unset_ctx('R');
575 app_exception.raise_exception;
576 ELSE
577 igs_sc_gen_001.unset_ctx('R');
578 RAISE;
579 END IF;
580
581 end UPDATE_ROW;
582 procedure ADD_ROW (
583 X_ROWID in out NOCOPY VARCHAR2,
584 X_PERSON_ID in NUMBER,
585 X_COURSE_CD in VARCHAR2,
586 X_CAL_TYPE in VARCHAR2,
587 X_CI_SEQUENCE_NUMBER in NUMBER,
588 X_ENROLMENT_CAT in VARCHAR2,
589 X_ENROLLED_DT in DATE,
590 X_ENR_FORM_DUE_DT in DATE,
591 X_ENR_PCKG_PROD_DT in DATE,
592 X_ENR_FORM_RECEIVED_DT in DATE,
593 X_MODE in VARCHAR2 default 'R'
594 ) AS
595 cursor c1 is select rowid from IGS_AS_SC_ATMPT_ENR
596 where PERSON_ID = X_PERSON_ID
597 and COURSE_CD = X_COURSE_CD
598 and CAL_TYPE = X_CAL_TYPE
599 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
600 ;
601 begin
602 open c1;
603 fetch c1 into X_ROWID;
604 if (c1%notfound) then
605 close c1;
606 INSERT_ROW (
607 X_ROWID,
608 X_PERSON_ID,
609 X_COURSE_CD,
610 X_CAL_TYPE,
611 X_CI_SEQUENCE_NUMBER,
612 X_ENROLMENT_CAT,
613 X_ENROLLED_DT,
614 X_ENR_FORM_DUE_DT,
615 X_ENR_PCKG_PROD_DT,
616 X_ENR_FORM_RECEIVED_DT,
617 X_MODE);
618 return;
619 end if;
620 close c1;
621 UPDATE_ROW (
622 X_ROWID,
623 X_PERSON_ID,
624 X_COURSE_CD,
625 X_CAL_TYPE,
626 X_CI_SEQUENCE_NUMBER,
627 X_ENROLMENT_CAT,
628 X_ENROLLED_DT,
629 X_ENR_FORM_DUE_DT,
630 X_ENR_PCKG_PROD_DT,
631 X_ENR_FORM_RECEIVED_DT,
632 X_MODE);
633 end ADD_ROW;
634 procedure DELETE_ROW (
635 X_ROWID in VARCHAR2,
636 x_mode IN VARCHAR2) AS
637 begin
638 Before_DML(
639 p_action => 'DELETE',
640 x_rowid => X_ROWID
641 );
642 IF (x_mode = 'S') THEN
643 igs_sc_gen_001.set_ctx('R');
644 END IF;
645 delete from IGS_AS_SC_ATMPT_ENR
646 where ROWID = X_ROWID;
647 if (sql%notfound) then
648 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
649 igs_ge_msg_stack.add;
650 igs_sc_gen_001.unset_ctx('R');
651 app_exception.raise_exception;
652 end if;
653 IF (x_mode = 'S') THEN
654 igs_sc_gen_001.unset_ctx('R');
655 END IF;
656
657
658 end DELETE_ROW;
659 PROCEDURE Check_Constraints (
660 Column_Name IN VARCHAR2 DEFAULT NULL,
661 Column_Value IN VARCHAR2 DEFAULT NULL
662 )
663 AS
664 BEGIN
665 IF column_name is null then
666 NULL;
667 ELSIF upper(Column_name) = 'CAL_TYPE' then
668 new_references.CAL_TYPE := column_value;
669 ELSIF upper(Column_name) = 'COURSE_CD' then
670 new_references.COURSE_CD := column_value;
671 ELSIF upper(Column_name) = 'ENROLMENT_CAT' then
672 new_references.ENROLMENT_CAT := column_value;
673 END IF ;
674 IF upper(column_name) = 'CAL_TYPE' OR
675 column_name is null Then
676 IF new_references.CAL_TYPE <> UPPER(new_references.CAL_TYPE) Then
677 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
678 IGS_GE_MSG_STACK.ADD;
679 APP_EXCEPTION.RAISE_EXCEPTION;
680 END IF;
681 END IF;
682
683 IF upper(column_name) = 'COURSE_CD' OR
684 column_name is null Then
685 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) Then
686 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
687 IGS_GE_MSG_STACK.ADD;
688 APP_EXCEPTION.RAISE_EXCEPTION;
689 END IF;
690 END IF;
691 IF upper(column_name) = 'ENROLMENT_CAT' OR
692 column_name is null Then
693 IF new_references.ENROLMENT_CAT <> UPPER(new_references.ENROLMENT_CAT) Then
694 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
695 IGS_GE_MSG_STACK.ADD;
696 APP_EXCEPTION.RAISE_EXCEPTION;
697 END IF;
698 END IF;
699 END Check_Constraints;
700
701 end IGS_AS_SC_ATMPT_ENR_PKG;