[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_STU_OU_HIST_PKG
Source
1 PACKAGE BODY igs_pr_stu_ou_hist_pkg AS
2 /* $Header: IGSQI28B.pls 115.10 2002/12/23 07:34:00 ddey ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_pr_stu_ou_hist_all%RowType;
5 new_references igs_pr_stu_ou_hist_all%RowType;
6
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_sequence_number IN NUMBER DEFAULT NULL,
13 x_hist_start_dt IN DATE DEFAULT NULL,
14 x_appeal_comments IN VARCHAR2 DEFAULT NULL,
15 x_appeal_dt IN DATE DEFAULT NULL,
16 x_appeal_expiry_dt IN DATE DEFAULT NULL,
17 x_appeal_outcome_dt IN DATE DEFAULT NULL,
18 x_appeal_outcome_type IN VARCHAR2 DEFAULT NULL,
19 x_applied_dt IN DATE DEFAULT NULL,
20 x_comments IN VARCHAR2 DEFAULT NULL,
21 x_decision_dt IN DATE DEFAULT NULL,
22 x_decision_org_unit_cd IN VARCHAR2 DEFAULT NULL,
23 x_decision_ou_start_dt IN DATE DEFAULT NULL,
24 x_decision_status IN VARCHAR2 DEFAULT NULL,
25 x_duration IN NUMBER DEFAULT NULL,
26 x_duration_type IN VARCHAR2 DEFAULT NULL,
27 x_encmb_course_group_cd IN VARCHAR2 DEFAULT NULL,
28 x_expiry_dt IN DATE DEFAULT NULL,
29 x_hist_end_dt IN DATE DEFAULT NULL,
30 x_hist_who IN VARCHAR2 DEFAULT NULL,
31 x_pra_sequence_number IN NUMBER DEFAULT NULL,
32 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
33 x_prg_ci_sequence_number IN NUMBER DEFAULT NULL,
34 x_progression_outcome_type IN VARCHAR2 DEFAULT NULL,
35 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
36 x_pro_pra_sequence_number IN NUMBER DEFAULT NULL,
37 x_pro_sequence_number IN NUMBER DEFAULT NULL,
38 x_restricted_attendance_type IN VARCHAR2 DEFAULT NULL,
39 x_restricted_enrolment_cp IN NUMBER DEFAULT NULL,
40 x_rule_check_dt IN DATE DEFAULT NULL,
41 x_show_cause_comments IN VARCHAR2 DEFAULT NULL,
42 x_show_cause_dt IN DATE DEFAULT NULL,
43 x_show_cause_expiry_dt IN DATE DEFAULT NULL,
44 x_show_cause_outcome_dt IN DATE DEFAULT NULL,
45 x_show_cause_outcome_type IN DATE DEFAULT NULL,
46 x_creation_date IN DATE DEFAULT NULL,
47 x_created_by IN NUMBER DEFAULT NULL,
48 x_last_update_date IN DATE DEFAULT NULL,
49 x_last_updated_by IN NUMBER DEFAULT NULL,
50 x_last_update_login IN NUMBER DEFAULT NULL,
51 x_org_id IN NUMBER DEFAULT NULL
52 ) AS
53
54 /*************************************************************
55 Created By :
56 Date Created By :
57 Purpose :
58 Know limitations, enhancements or remarks
59 Change History
60 Who When What
61
62 (reverse chronological order - newest change first)
63 ***************************************************************/
64
65 CURSOR cur_old_ref_values IS
66 SELECT *
67 FROM IGS_PR_STU_OU_HIST_ALL
68 WHERE rowid = x_rowid;
69
70 BEGIN
71
72 l_rowid := x_rowid;
73
74 -- Code for setting the Old and New Reference Values.
75 -- Populate Old Values.
76 Open cur_old_ref_values;
77 Fetch cur_old_ref_values INTO old_references;
78 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
79 Close cur_old_ref_values;
80 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
81 IGS_GE_MSG_STACK.ADD;
82 App_Exception.Raise_Exception;
83 Return;
84 END IF;
85 Close cur_old_ref_values;
86
87 -- Populate New Values.
88 new_references.person_id := x_person_id;
89 new_references.course_cd := x_course_cd;
90 new_references.sequence_number := x_sequence_number;
91 new_references.hist_start_dt := x_hist_start_dt;
92 new_references.appeal_comments := x_appeal_comments;
93 new_references.appeal_dt := x_appeal_dt;
94 new_references.appeal_expiry_dt := x_appeal_expiry_dt;
95 new_references.appeal_outcome_dt := x_appeal_outcome_dt;
96 new_references.appeal_outcome_type := x_appeal_outcome_type;
97 new_references.applied_dt := x_applied_dt;
98 new_references.comments := x_comments;
99 new_references.decision_dt := x_decision_dt;
100 new_references.decision_org_unit_cd := x_decision_org_unit_cd;
101 new_references.decision_ou_start_dt := x_decision_ou_start_dt;
102 new_references.decision_status := x_decision_status;
103 new_references.duration := x_duration;
104 new_references.duration_type := x_duration_type;
105 new_references.encmb_course_group_cd := x_encmb_course_group_cd;
106 new_references.expiry_dt := x_expiry_dt;
107 new_references.hist_end_dt := x_hist_end_dt;
108 new_references.hist_who := x_hist_who;
109 new_references.pra_sequence_number := x_pra_sequence_number;
110 new_references.prg_cal_type := x_prg_cal_type;
111 new_references.prg_ci_sequence_number := x_prg_ci_sequence_number;
112 new_references.progression_outcome_type := x_progression_outcome_type;
113 new_references.progression_rule_cat := x_progression_rule_cat;
114 new_references.pro_pra_sequence_number := x_pro_pra_sequence_number;
115 new_references.pro_sequence_number := x_pro_sequence_number;
116 new_references.restricted_attendance_type := x_restricted_attendance_type;
117 new_references.restricted_enrolment_cp := x_restricted_enrolment_cp;
118 new_references.rule_check_dt := x_rule_check_dt;
119 new_references.show_cause_comments := x_show_cause_comments;
120 new_references.show_cause_dt := x_show_cause_dt;
121 new_references.show_cause_expiry_dt := x_show_cause_expiry_dt;
122 new_references.show_cause_outcome_dt := x_show_cause_outcome_dt;
123 new_references.show_cause_outcome_type := x_show_cause_outcome_type;
124 IF (p_action = 'UPDATE') THEN
125 new_references.creation_date := old_references.creation_date;
126 new_references.created_by := old_references.created_by;
127 ELSE
128 new_references.creation_date := x_creation_date;
129 new_references.created_by := x_created_by;
130 END IF;
131 new_references.last_update_date := x_last_update_date;
132 new_references.last_updated_by := x_last_updated_by;
133 new_references.last_update_login := x_last_update_login;
134 new_references.org_id := x_org_id;
135
136 END Set_Column_Values;
137
138 PROCEDURE Check_Constraints (
139 Column_Name IN VARCHAR2 DEFAULT NULL,
140 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
141 /*************************************************************
142 Created By :
143 Date Created By :
144 Purpose :
145 Know limitations, enhancements or remarks
146 Change History
147 Who When What
148
149 (reverse chronological order - newest change first)
150 ***************************************************************/
151
152 BEGIN
153
154 IF column_name IS NULL THEN
155 NULL;
156 ELSIF UPPER(column_name) = 'COURSE_CD' THEN
157 new_references.course_cd := column_value;
158 ELSIF UPPER(column_name) = 'APPEAL_OUTCOME_TYPE' THEN
159 new_references.appeal_outcome_type := column_value;
160 ELSIF UPPER(column_name) = 'DECISION_ORG_UNIT_CD' THEN
161 new_references.decision_org_unit_cd := column_value;
162 ELSIF UPPER(column_name) = 'DECISION_STATUS' THEN
163 new_references.decision_status := column_value;
164 ELSIF UPPER(column_name) = 'DURATION_TYPE' THEN
165 new_references.duration_type := column_value;
166 ELSIF UPPER(column_name) = 'ENCMB_COURSE_GROUP_CD' THEN
167 new_references.encmb_course_group_cd := column_value;
168 ELSIF UPPER(column_name) = 'HIST_WHO' THEN
169 new_references.hist_who := column_value;
170 ELSIF UPPER(column_name) = 'PRG_CAL_TYPE' THEN
171 new_references.prg_cal_type := column_value;
172 ELSIF UPPER(column_name) = 'PROGRESSION_OUTCOME_TYPE' THEN
173 new_references.progression_outcome_type := column_value;
174 ELSIF UPPER(column_name) = 'PROGRESSION_RULE_CAT' THEN
175 new_references.progression_rule_cat := column_value;
176 ELSIF UPPER(column_name) = 'RESTRICTED_ATTENDANCE_TYPE' THEN
177 new_references.restricted_attendance_type := column_value;
178 NULL;
179 END IF;
180
181
182
183 IF UPPER(Column_Name) = 'COURSE_CD' OR
184 Column_Name IS NULL THEN
185 IF new_references.COURSE_CD <> UPPER(new_references.course_cd) THEN
186 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;
191
192 IF UPPER(Column_Name) = 'APPEAL_OUTCOME_TYPE' OR
193 Column_Name IS NULL THEN
194 IF new_references.APPEAL_OUTCOME_TYPE <> UPPER(new_references.appeal_outcome_type) THEN
195 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
196 IGS_GE_MSG_STACK.ADD;
197 App_Exception.Raise_Exception;
198 END IF;
199 END IF;
200
201
202 IF UPPER(Column_Name) = 'DECISION_STATUS' OR
203 Column_Name IS NULL THEN
204 IF new_references.DECISION_STATUS <> UPPER(new_references.decision_status) THEN
205 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
206 IGS_GE_MSG_STACK.ADD;
207 App_Exception.Raise_Exception;
208 END IF;
209 END IF;
210
211 IF UPPER(Column_Name) = 'DURATION_TYPE' OR
212 Column_Name IS NULL THEN
213 IF new_references.DURATION_TYPE <> UPPER(new_references.duration_type) THEN
214 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 END IF;
218 END IF;
219
220 IF UPPER(Column_Name) = 'ENCMB_COURSE_GROUP_CD' OR
221 Column_Name IS NULL THEN
222 IF new_references.ENCMB_COURSE_GROUP_CD <> UPPER(new_references.encmb_course_group_cd) THEN
223 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
224 IGS_GE_MSG_STACK.ADD;
225 App_Exception.Raise_Exception;
226 END IF;
227 END IF;
228
229 IF UPPER(Column_Name) = 'HIST_WHO' OR
230 Column_Name IS NULL THEN
231 IF new_references.HIST_WHO <> UPPER(new_references.hist_who) THEN
232 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
233 IGS_GE_MSG_STACK.ADD;
234 App_Exception.Raise_Exception;
235 END IF;
236 END IF;
237
238 IF UPPER(Column_Name) = 'PRG_CAL_TYPE' OR
239 Column_Name IS NULL THEN
240 IF new_references.PRG_CAL_TYPE <> UPPER(new_references.prg_cal_type) THEN
241 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
242 IGS_GE_MSG_STACK.ADD;
243 App_Exception.Raise_Exception;
244 END IF;
245 END IF;
246
247 IF UPPER(Column_Name) = 'PROGRESSION_OUTCOME_TYPE' OR
248 Column_Name IS NULL THEN
249 IF new_references.PROGRESSION_OUTCOME_TYPE <> UPPER(new_references.progression_outcome_type) THEN
250 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254 END IF;
255
256 IF UPPER(Column_Name) = 'PROGRESSION_RULE_CAT' OR
257 Column_Name IS NULL THEN
258 IF new_references.PROGRESSION_RULE_CAT <> UPPER(new_references.progression_rule_cat) THEN
259 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
260 IGS_GE_MSG_STACK.ADD;
261 App_Exception.Raise_Exception;
262 END IF;
263 END IF;
264
265 IF UPPER(Column_Name) = 'RESTRICTED_ATTENDANCE_TYPE' OR
266 Column_Name IS NULL THEN
267 IF new_references.RESTRICTED_ATTENDANCE_TYPE <> UPPER(new_references.restricted_attendance_type) THEN
268 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 END IF;
272 END IF;
273
274
275 END Check_Constraints;
276
277 PROCEDURE Check_Parent_Existance AS
278 /*************************************************************
279 Created By :
280 Date Created By :
281 Purpose :
282 Know limitations, enhancements or remarks
283 Change History
284 Who When What
285
286 (reverse chronological order - newest change first)
287 ***************************************************************/
288
289 BEGIN
290
291 IF (((old_references.person_id = new_references.person_id) AND
292 (old_references.course_cd = new_references.course_cd) AND
293 (old_references.sequence_number = new_references.sequence_number)) OR
294 ((new_references.person_id IS NULL) OR
295 (new_references.course_cd IS NULL) OR
296 (new_references.sequence_number IS NULL))) THEN
297 NULL;
298 ELSIF NOT Igs_Pr_Stdnt_Pr_Ou_Pkg.Get_PK_For_Validation (
299 new_references.person_id,
300 new_references.course_cd,
301 new_references.sequence_number
302 ) THEN
303 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 END IF;
307
308 END Check_Parent_Existance;
309
310 FUNCTION Get_PK_For_Validation (
311 x_person_id IN NUMBER,
312 x_course_cd IN VARCHAR2,
313 x_sequence_number IN NUMBER,
314 x_hist_start_dt IN DATE
315 ) RETURN BOOLEAN AS
316
317 /*************************************************************
318 Created By :
319 Date Created By :
320 Purpose :
321 Know limitations, enhancements or remarks
322 Change History
323 Who When What
324
325 (reverse chronological order - newest change first)
326 ***************************************************************/
327
328 CURSOR cur_rowid IS
329 SELECT rowid
330 FROM igs_pr_stu_ou_hist_all
331 WHERE person_id = x_person_id
332 AND course_cd = x_course_cd
333 AND sequence_number = x_sequence_number
334 AND hist_start_dt = x_hist_start_dt
335 FOR UPDATE NOWAIT;
336
337 lv_rowid cur_rowid%RowType;
338
339 BEGIN
340
341 Open cur_rowid;
342 Fetch cur_rowid INTO lv_rowid;
343 IF (cur_rowid%FOUND) THEN
344 Close cur_rowid;
345 Return(TRUE);
346 ELSE
347 Close cur_rowid;
348 Return(FALSE);
349 END IF;
350 END Get_PK_For_Validation;
351
352 PROCEDURE Get_FK_Igs_Pr_Stdnt_Pr_Ou (
353 x_person_id IN NUMBER,
354 x_course_cd IN VARCHAR2,
355 x_sequence_number IN NUMBER
356 ) AS
357
358 /*************************************************************
359 Created By :
360 Date Created By :
361 Purpose :
362 Know limitations, enhancements or remarks
363 Change History
364 Who When What
365
366 (reverse chronological order - newest change first)
367 ***************************************************************/
368
369 CURSOR cur_rowid IS
370 SELECT rowid
371 FROM igs_pr_stu_ou_hist_all
372 WHERE person_id = x_person_id
373 AND course_cd = x_course_cd
374 AND sequence_number = x_sequence_number ;
375
376 lv_rowid cur_rowid%RowType;
377
378 BEGIN
379
380 Open cur_rowid;
381 Fetch cur_rowid INTO lv_rowid;
382 IF (cur_rowid%FOUND) THEN
383 Close cur_rowid;
384 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
385 IGS_GE_MSG_STACK.ADD;
386 App_Exception.Raise_Exception;
387 Return;
388 END IF;
389 Close cur_rowid;
390
391 END Get_FK_Igs_Pr_Stdnt_Pr_Ou;
392
393 PROCEDURE Before_DML (
394 p_action IN VARCHAR2,
395 x_rowid IN VARCHAR2 DEFAULT NULL,
396 x_person_id IN NUMBER DEFAULT NULL,
397 x_course_cd IN VARCHAR2 DEFAULT NULL,
398 x_sequence_number IN NUMBER DEFAULT NULL,
399 x_hist_start_dt IN DATE DEFAULT NULL,
400 x_appeal_comments IN VARCHAR2 DEFAULT NULL,
401 x_appeal_dt IN DATE DEFAULT NULL,
402 x_appeal_expiry_dt IN DATE DEFAULT NULL,
403 x_appeal_outcome_dt IN DATE DEFAULT NULL,
404 x_appeal_outcome_type IN VARCHAR2 DEFAULT NULL,
405 x_applied_dt IN DATE DEFAULT NULL,
406 x_comments IN VARCHAR2 DEFAULT NULL,
407 x_decision_dt IN DATE DEFAULT NULL,
408 x_decision_org_unit_cd IN VARCHAR2 DEFAULT NULL,
409 x_decision_ou_start_dt IN DATE DEFAULT NULL,
410 x_decision_status IN VARCHAR2 DEFAULT NULL,
411 x_duration IN NUMBER DEFAULT NULL,
412 x_duration_type IN VARCHAR2 DEFAULT NULL,
413 x_encmb_course_group_cd IN VARCHAR2 DEFAULT NULL,
414 x_expiry_dt IN DATE DEFAULT NULL,
415 x_hist_end_dt IN DATE DEFAULT NULL,
416 x_hist_who IN VARCHAR2 DEFAULT NULL,
417 x_pra_sequence_number IN NUMBER DEFAULT NULL,
418 x_prg_cal_type IN VARCHAR2 DEFAULT NULL,
419 x_prg_ci_sequence_number IN NUMBER DEFAULT NULL,
420 x_progression_outcome_type IN VARCHAR2 DEFAULT NULL,
421 x_progression_rule_cat IN VARCHAR2 DEFAULT NULL,
422 x_pro_pra_sequence_number IN NUMBER DEFAULT NULL,
423 x_pro_sequence_number IN NUMBER DEFAULT NULL,
424 x_restricted_attendance_type IN VARCHAR2 DEFAULT NULL,
425 x_restricted_enrolment_cp IN NUMBER DEFAULT NULL,
426 x_rule_check_dt IN DATE DEFAULT NULL,
427 x_show_cause_comments IN VARCHAR2 DEFAULT NULL,
428 x_show_cause_dt IN DATE DEFAULT NULL,
429 x_show_cause_expiry_dt IN DATE DEFAULT NULL,
430 x_show_cause_outcome_dt IN DATE DEFAULT NULL,
431 x_show_cause_outcome_type IN DATE DEFAULT NULL,
432 x_creation_date IN DATE DEFAULT NULL,
433 x_created_by IN NUMBER DEFAULT NULL,
434 x_last_update_date IN DATE DEFAULT NULL,
435 x_last_updated_by IN NUMBER DEFAULT NULL,
436 x_last_update_login IN NUMBER DEFAULT NULL,
437 x_org_id IN NUMBER DEFAULT NULL
438 ) AS
439 /*************************************************************
440 Created By :
441 Date Created By :
442 Purpose :
443 Know limitations, enhancements or remarks
444 Change History
445 Who When What
446
447 (reverse chronological order - newest change first)
448 ***************************************************************/
449
450 BEGIN
451
452 Set_Column_Values (
453 p_action,
454 x_rowid,
455 x_person_id,
456 x_course_cd,
457 x_sequence_number,
458 x_hist_start_dt,
459 x_appeal_comments,
460 x_appeal_dt,
461 x_appeal_expiry_dt,
462 x_appeal_outcome_dt,
463 x_appeal_outcome_type,
464 x_applied_dt,
465 x_comments,
466 x_decision_dt,
467 x_decision_org_unit_cd,
468 x_decision_ou_start_dt,
469 x_decision_status,
470 x_duration,
471 x_duration_type,
472 x_encmb_course_group_cd,
473 x_expiry_dt,
474 x_hist_end_dt,
475 x_hist_who,
476 x_pra_sequence_number,
477 x_prg_cal_type,
478 x_prg_ci_sequence_number,
479 x_progression_outcome_type,
480 x_progression_rule_cat,
481 x_pro_pra_sequence_number,
482 x_pro_sequence_number,
483 x_restricted_attendance_type,
484 x_restricted_enrolment_cp,
485 x_rule_check_dt,
486 x_show_cause_comments,
487 x_show_cause_dt,
488 x_show_cause_expiry_dt,
489 x_show_cause_outcome_dt,
490 x_show_cause_outcome_type,
491 x_creation_date,
492 x_created_by,
493 x_last_update_date,
494 x_last_updated_by,
495 x_last_update_login,
496 x_org_id
497 );
498
499 IF (p_action = 'INSERT') THEN
500 -- Call all the procedures related to Before Insert.
501 Null;
502 IF Get_Pk_For_Validation(
503 new_references.person_id,
504 new_references.course_cd,
505 new_references.sequence_number,
506 new_references.hist_start_dt) THEN
507 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
508 IGS_GE_MSG_STACK.ADD;
509 App_Exception.Raise_Exception;
510 END IF;
511 Check_Constraints;
512 Check_Parent_Existance;
513 ELSIF (p_action = 'UPDATE') THEN
514 -- Call all the procedures related to Before Update.
515 Null;
516 Check_Constraints;
517 Check_Parent_Existance;
518 ELSIF (p_action = 'DELETE') THEN
519 -- Call all the procedures related to Before Delete.
520 Null;
521 ELSIF (p_action = 'VALIDATE_INSERT') THEN
522 -- Call all the procedures related to Before Insert.
523 IF Get_PK_For_Validation (
524 new_references.person_id,
525 new_references.course_cd,
526 new_references.sequence_number,
527 new_references.hist_start_dt) THEN
528 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
529 IGS_GE_MSG_STACK.ADD;
530 App_Exception.Raise_Exception;
531 END IF;
532 Check_Constraints;
533 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
534 Check_Constraints;
535 ELSIF (p_action = 'VALIDATE_DELETE') THEN
536 Null;
537 END IF;
538
539 END Before_DML;
540
541 PROCEDURE After_DML (
542 p_action IN VARCHAR2,
543 x_rowid IN VARCHAR2
544 ) IS
545 /*************************************************************
546 Created By :
547 Date Created By :
548 Purpose :
549 Know limitations, enhancements or remarks
550 Change History
551 Who When What
552
553 (reverse chronological order - newest change first)
554 ***************************************************************/
555
556 BEGIN
557
558 l_rowid := x_rowid;
559
560 IF (p_action = 'INSERT') THEN
561 -- Call all the procedures related to After Insert.
562 Null;
563 ELSIF (p_action = 'UPDATE') THEN
564 -- Call all the procedures related to After Update.
565 Null;
566 ELSIF (p_action = 'DELETE') THEN
567 -- Call all the procedures related to After Delete.
568 Null;
569 END IF;
570
571 END After_DML;
572
573 procedure INSERT_ROW (
574 X_ROWID in out NOCOPY VARCHAR2,
575 x_PERSON_ID IN NUMBER,
576 x_COURSE_CD IN VARCHAR2,
577 x_SEQUENCE_NUMBER IN NUMBER,
578 x_HIST_START_DT IN DATE,
579 x_APPEAL_COMMENTS IN VARCHAR2,
580 x_APPEAL_DT IN DATE,
581 x_APPEAL_EXPIRY_DT IN DATE,
582 x_APPEAL_OUTCOME_DT IN DATE,
583 x_APPEAL_OUTCOME_TYPE IN VARCHAR2,
584 x_APPLIED_DT IN DATE,
585 x_COMMENTS IN VARCHAR2,
586 x_DECISION_DT IN DATE,
587 x_DECISION_ORG_UNIT_CD IN VARCHAR2,
588 x_DECISION_OU_START_DT IN DATE,
589 x_DECISION_STATUS IN VARCHAR2,
590 x_DURATION IN NUMBER,
591 x_DURATION_TYPE IN VARCHAR2,
592 x_ENCMB_COURSE_GROUP_CD IN VARCHAR2,
593 x_EXPIRY_DT IN DATE,
594 x_HIST_END_DT IN DATE,
595 x_HIST_WHO IN VARCHAR2,
596 x_PRA_SEQUENCE_NUMBER IN NUMBER,
597 x_PRG_CAL_TYPE IN VARCHAR2,
598 x_PRG_CI_SEQUENCE_NUMBER IN NUMBER,
599 x_PROGRESSION_OUTCOME_TYPE IN VARCHAR2,
600 x_PROGRESSION_RULE_CAT IN VARCHAR2,
601 x_PRO_PRA_SEQUENCE_NUMBER IN NUMBER,
602 x_PRO_SEQUENCE_NUMBER IN NUMBER,
603 x_RESTRICTED_ATTENDANCE_TYPE IN VARCHAR2,
604 x_RESTRICTED_ENROLMENT_CP IN NUMBER,
605 x_RULE_CHECK_DT IN DATE,
606 x_SHOW_CAUSE_COMMENTS IN VARCHAR2,
607 x_SHOW_CAUSE_DT IN DATE,
608 x_SHOW_CAUSE_EXPIRY_DT IN DATE,
609 x_SHOW_CAUSE_OUTCOME_DT IN DATE,
610 x_SHOW_CAUSE_OUTCOME_TYPE IN DATE,
611 X_MODE in VARCHAR2 default 'R',
612 X_ORG_ID IN NUMBER
613 ) AS
614 /*************************************************************
615 Created By :
616 Date Created By :
617 Purpose :
618 Know limitations, enhancements or remarks
619 Change History
620 Who When What
621
622 (reverse chronological order - newest change first)
623 ***************************************************************/
624
625 cursor C is select ROWID from IGS_PR_STU_OU_HIST_ALL
626 where PERSON_ID= X_PERSON_ID
627 and COURSE_CD = X_COURSE_CD
628 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
629 and HIST_START_DT = X_HIST_START_DT
630 ;
631 X_LAST_UPDATE_DATE DATE ;
632 X_LAST_UPDATED_BY NUMBER ;
633 X_LAST_UPDATE_LOGIN NUMBER ;
634 begin
635 X_LAST_UPDATE_DATE := SYSDATE;
636 if(X_MODE = 'I') then
637 X_LAST_UPDATED_BY := 1;
638 X_LAST_UPDATE_LOGIN := 0;
639 elsif (X_MODE = 'R') then
640 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
641 if X_LAST_UPDATED_BY is NULL then
642 X_LAST_UPDATED_BY := -1;
643 end if;
644 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
645 if X_LAST_UPDATE_LOGIN is NULL then
646 X_LAST_UPDATE_LOGIN := -1;
647 end if;
648 else
649 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
650 IGS_GE_MSG_STACK.ADD;
651 app_exception.raise_exception;
652 end if;
653 Before_DML(
654 p_action=>'INSERT',
655 x_rowid=>X_ROWID,
656 x_person_id=>X_PERSON_ID,
657 x_course_cd=>X_COURSE_CD,
658 x_sequence_number=>X_SEQUENCE_NUMBER,
659 x_hist_start_dt=>X_HIST_START_DT,
660 x_appeal_comments=>X_APPEAL_COMMENTS,
661 x_appeal_dt=>X_APPEAL_DT,
662 x_appeal_expiry_dt=>X_APPEAL_EXPIRY_DT,
663 x_appeal_outcome_dt=>X_APPEAL_OUTCOME_DT,
664 x_appeal_outcome_type=>X_APPEAL_OUTCOME_TYPE,
665 x_applied_dt=>X_APPLIED_DT,
666 x_comments=>X_COMMENTS,
667 x_decision_dt=>X_DECISION_DT,
668 x_decision_org_unit_cd=>X_DECISION_ORG_UNIT_CD,
669 x_decision_ou_start_dt=>X_DECISION_OU_START_DT,
670 x_decision_status=>X_DECISION_STATUS,
671 x_duration=>X_DURATION,
672 x_duration_type=>X_DURATION_TYPE,
673 x_encmb_course_group_cd=>X_ENCMB_COURSE_GROUP_CD,
674 x_expiry_dt=>X_EXPIRY_DT,
675 x_hist_end_dt=>X_HIST_END_DT,
676 x_hist_who=>X_HIST_WHO,
677 x_pra_sequence_number=>X_PRA_SEQUENCE_NUMBER,
678 x_prg_cal_type=>X_PRG_CAL_TYPE,
679 x_prg_ci_sequence_number=>X_PRG_CI_SEQUENCE_NUMBER,
680 x_progression_outcome_type=>X_PROGRESSION_OUTCOME_TYPE,
681 x_progression_rule_cat=>X_PROGRESSION_RULE_CAT,
682 x_pro_pra_sequence_number=>X_PRO_PRA_SEQUENCE_NUMBER,
683 x_pro_sequence_number=>X_PRO_SEQUENCE_NUMBER,
684 x_restricted_attendance_type=>X_RESTRICTED_ATTENDANCE_TYPE,
685 x_restricted_enrolment_cp=>X_RESTRICTED_ENROLMENT_CP,
686 x_rule_check_dt=>X_RULE_CHECK_DT,
687 x_show_cause_comments=>X_SHOW_CAUSE_COMMENTS,
688 x_show_cause_dt=>X_SHOW_CAUSE_DT,
689 x_show_cause_expiry_dt=>X_SHOW_CAUSE_EXPIRY_DT,
690 x_show_cause_outcome_dt=>X_SHOW_CAUSE_OUTCOME_DT,
691 x_show_cause_outcome_type=>X_SHOW_CAUSE_OUTCOME_TYPE,
692 x_creation_date=>X_LAST_UPDATE_DATE,
693 x_created_by=>X_LAST_UPDATED_BY,
694 x_last_update_date=>X_LAST_UPDATE_DATE,
695 x_last_updated_by=>X_LAST_UPDATED_BY,
696 x_last_update_login=>X_LAST_UPDATE_LOGIN,
697 x_org_id=>igs_ge_gen_003.get_org_id);
698 insert into IGS_PR_STU_OU_HIST_ALL (
699 PERSON_ID
700 ,COURSE_CD
701 ,SEQUENCE_NUMBER
702 ,HIST_START_DT
703 ,APPEAL_COMMENTS
704 ,APPEAL_DT
705 ,APPEAL_EXPIRY_DT
706 ,APPEAL_OUTCOME_DT
707 ,APPEAL_OUTCOME_TYPE
708 ,APPLIED_DT
709 ,COMMENTS
710 ,DECISION_DT
711 ,DECISION_ORG_UNIT_CD
712 ,DECISION_OU_START_DT
713 ,DECISION_STATUS
714 ,DURATION
715 ,DURATION_TYPE
716 ,ENCMB_COURSE_GROUP_CD
717 ,EXPIRY_DT
718 ,HIST_END_DT
719 ,HIST_WHO
720 ,PRA_SEQUENCE_NUMBER
721 ,PRG_CAL_TYPE
722 ,PRG_CI_SEQUENCE_NUMBER
723 ,PROGRESSION_OUTCOME_TYPE
724 ,PROGRESSION_RULE_CAT
725 ,PRO_PRA_SEQUENCE_NUMBER
726 ,PRO_SEQUENCE_NUMBER
727 ,RESTRICTED_ATTENDANCE_TYPE
728 ,RESTRICTED_ENROLMENT_CP
729 ,RULE_CHECK_DT
730 ,SHOW_CAUSE_COMMENTS
731 ,SHOW_CAUSE_DT
732 ,SHOW_CAUSE_EXPIRY_DT
733 ,SHOW_CAUSE_OUTCOME_DT
734 ,SHOW_CAUSE_OUTCOME_TYPE
735 ,CREATION_DATE
736 ,CREATED_BY
737 ,LAST_UPDATE_DATE
738 ,LAST_UPDATED_BY
739 ,LAST_UPDATE_LOGIN
740 ,ORG_ID
741 ) values (
742 NEW_REFERENCES.PERSON_ID
743 ,NEW_REFERENCES.COURSE_CD
744 ,NEW_REFERENCES.SEQUENCE_NUMBER
745 ,NEW_REFERENCES.HIST_START_DT
746 ,NEW_REFERENCES.APPEAL_COMMENTS
747 ,NEW_REFERENCES.APPEAL_DT
748 ,NEW_REFERENCES.APPEAL_EXPIRY_DT
749 ,NEW_REFERENCES.APPEAL_OUTCOME_DT
750 ,NEW_REFERENCES.APPEAL_OUTCOME_TYPE
751 ,NEW_REFERENCES.APPLIED_DT
752 ,NEW_REFERENCES.COMMENTS
753 ,NEW_REFERENCES.DECISION_DT
754 ,NEW_REFERENCES.DECISION_ORG_UNIT_CD
755 ,NEW_REFERENCES.DECISION_OU_START_DT
756 ,NEW_REFERENCES.DECISION_STATUS
757 ,NEW_REFERENCES.DURATION
758 ,NEW_REFERENCES.DURATION_TYPE
759 ,NEW_REFERENCES.ENCMB_COURSE_GROUP_CD
760 ,NEW_REFERENCES.EXPIRY_DT
761 ,NEW_REFERENCES.HIST_END_DT
762 ,NEW_REFERENCES.HIST_WHO
763 ,NEW_REFERENCES.PRA_SEQUENCE_NUMBER
764 ,NEW_REFERENCES.PRG_CAL_TYPE
765 ,NEW_REFERENCES.PRG_CI_SEQUENCE_NUMBER
766 ,NEW_REFERENCES.PROGRESSION_OUTCOME_TYPE
767 ,NEW_REFERENCES.PROGRESSION_RULE_CAT
768 ,NEW_REFERENCES.PRO_PRA_SEQUENCE_NUMBER
769 ,NEW_REFERENCES.PRO_SEQUENCE_NUMBER
770 ,NEW_REFERENCES.RESTRICTED_ATTENDANCE_TYPE
771 ,NEW_REFERENCES.RESTRICTED_ENROLMENT_CP
772 ,NEW_REFERENCES.RULE_CHECK_DT
773 ,NEW_REFERENCES.SHOW_CAUSE_COMMENTS
774 ,NEW_REFERENCES.SHOW_CAUSE_DT
775 ,NEW_REFERENCES.SHOW_CAUSE_EXPIRY_DT
776 ,NEW_REFERENCES.SHOW_CAUSE_OUTCOME_DT
777 ,NEW_REFERENCES.SHOW_CAUSE_OUTCOME_TYPE
778 ,X_LAST_UPDATE_DATE
779 ,X_LAST_UPDATED_BY
780 ,X_LAST_UPDATE_DATE
781 ,X_LAST_UPDATED_BY
782 ,X_LAST_UPDATE_LOGIN
783 ,NEW_REFERENCES.ORG_ID
784 );
785 open c;
786 fetch c into X_ROWID;
787 if (c%notfound) then
788 close c;
789 raise no_data_found;
790 end if;
791 close c;
792 After_DML (
793 p_action => 'INSERT' ,
794 x_rowid => X_ROWID );
795 end INSERT_ROW;
796 procedure LOCK_ROW (
797 X_ROWID in VARCHAR2,
798 x_PERSON_ID IN NUMBER,
799 x_COURSE_CD IN VARCHAR2,
800 x_SEQUENCE_NUMBER IN NUMBER,
801 x_HIST_START_DT IN DATE,
802 x_APPEAL_COMMENTS IN VARCHAR2,
803 x_APPEAL_DT IN DATE,
804 x_APPEAL_EXPIRY_DT IN DATE,
805 x_APPEAL_OUTCOME_DT IN DATE,
806 x_APPEAL_OUTCOME_TYPE IN VARCHAR2,
807 x_APPLIED_DT IN DATE,
808 x_COMMENTS IN VARCHAR2,
809 x_DECISION_DT IN DATE,
810 x_DECISION_ORG_UNIT_CD IN VARCHAR2,
811 x_DECISION_OU_START_DT IN DATE,
812 x_DECISION_STATUS IN VARCHAR2,
813 x_DURATION IN NUMBER,
814 x_DURATION_TYPE IN VARCHAR2,
815 x_ENCMB_COURSE_GROUP_CD IN VARCHAR2,
816 x_EXPIRY_DT IN DATE,
817 x_HIST_END_DT IN DATE,
818 x_HIST_WHO IN VARCHAR2,
819 x_PRA_SEQUENCE_NUMBER IN NUMBER,
820 x_PRG_CAL_TYPE IN VARCHAR2,
821 x_PRG_CI_SEQUENCE_NUMBER IN NUMBER,
822 x_PROGRESSION_OUTCOME_TYPE IN VARCHAR2,
823 x_PROGRESSION_RULE_CAT IN VARCHAR2,
824 x_PRO_PRA_SEQUENCE_NUMBER IN NUMBER,
825 x_PRO_SEQUENCE_NUMBER IN NUMBER,
826 x_RESTRICTED_ATTENDANCE_TYPE IN VARCHAR2,
827 x_RESTRICTED_ENROLMENT_CP IN NUMBER,
828 x_RULE_CHECK_DT IN DATE,
829 x_SHOW_CAUSE_COMMENTS IN VARCHAR2,
830 x_SHOW_CAUSE_DT IN DATE,
831 x_SHOW_CAUSE_EXPIRY_DT IN DATE,
832 x_SHOW_CAUSE_OUTCOME_DT IN DATE,
833 x_SHOW_CAUSE_OUTCOME_TYPE IN DATE
834 ) AS
835 /*************************************************************
836 Created By :
837 Date Created By :
838 Purpose :
839 Know limitations, enhancements or remarks
840 Change History
841 Who When What
842
843 (reverse chronological order - newest change first)
844 ***************************************************************/
845
846 cursor c1 is select
847 APPEAL_COMMENTS
848 , APPEAL_DT
849 , APPEAL_EXPIRY_DT
850 , APPEAL_OUTCOME_DT
851 , APPEAL_OUTCOME_TYPE
852 , APPLIED_DT
853 , COMMENTS
854 , DECISION_DT
855 , DECISION_ORG_UNIT_CD
856 , DECISION_OU_START_DT
857 , DECISION_STATUS
858 , DURATION
859 , DURATION_TYPE
860 , ENCMB_COURSE_GROUP_CD
861 , EXPIRY_DT
862 , HIST_END_DT
863 , HIST_WHO
864 , PRA_SEQUENCE_NUMBER
865 , PRG_CAL_TYPE
866 , PRG_CI_SEQUENCE_NUMBER
867 , PROGRESSION_OUTCOME_TYPE
868 , PROGRESSION_RULE_CAT
869 , PRO_PRA_SEQUENCE_NUMBER
870 , PRO_SEQUENCE_NUMBER
871 , RESTRICTED_ATTENDANCE_TYPE
872 , RESTRICTED_ENROLMENT_CP
873 , RULE_CHECK_DT
874 , SHOW_CAUSE_COMMENTS
875 , SHOW_CAUSE_DT
876 , SHOW_CAUSE_EXPIRY_DT
877 , SHOW_CAUSE_OUTCOME_DT
878 , SHOW_CAUSE_OUTCOME_TYPE
879 from IGS_PR_STU_OU_HIST_ALL
880 where ROWID = X_ROWID
881 for update nowait;
882 tlinfo c1%rowtype;
883 begin
884 open c1;
885 fetch c1 into tlinfo;
886 if (c1%notfound) then
887 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
888 IGS_GE_MSG_STACK.ADD;
889 close c1;
890 app_exception.raise_exception;
891 return;
892 end if;
893 close c1;
894 if ( ( (tlinfo.APPEAL_COMMENTS = X_APPEAL_COMMENTS)
895 OR ((tlinfo.APPEAL_COMMENTS is null)
896 AND (X_APPEAL_COMMENTS is null)))
897 AND ((tlinfo.APPEAL_DT = X_APPEAL_DT)
898 OR ((tlinfo.APPEAL_DT is null)
899 AND (X_APPEAL_DT is null)))
900 AND ((tlinfo.APPEAL_EXPIRY_DT = X_APPEAL_EXPIRY_DT)
901 OR ((tlinfo.APPEAL_EXPIRY_DT is null)
902 AND (X_APPEAL_EXPIRY_DT is null)))
903 AND ((tlinfo.APPEAL_OUTCOME_DT = X_APPEAL_OUTCOME_DT)
904 OR ((tlinfo.APPEAL_OUTCOME_DT is null)
905 AND (X_APPEAL_OUTCOME_DT is null)))
906 AND ((tlinfo.APPEAL_OUTCOME_TYPE = X_APPEAL_OUTCOME_TYPE)
907 OR ((tlinfo.APPEAL_OUTCOME_TYPE is null)
908 AND (X_APPEAL_OUTCOME_TYPE is null)))
909 AND ((tlinfo.APPLIED_DT = X_APPLIED_DT)
910 OR ((tlinfo.APPLIED_DT is null)
911 AND (X_APPLIED_DT is null)))
912 AND ((tlinfo.COMMENTS = X_COMMENTS)
913 OR ((tlinfo.COMMENTS is null)
914 AND (X_COMMENTS is null)))
915 AND ((tlinfo.DECISION_DT = X_DECISION_DT)
916 OR ((tlinfo.DECISION_DT is null)
917 AND (X_DECISION_DT is null)))
918 AND ((tlinfo.DECISION_ORG_UNIT_CD = X_DECISION_ORG_UNIT_CD)
919 OR ((tlinfo.DECISION_ORG_UNIT_CD is null)
920 AND (X_DECISION_ORG_UNIT_CD is null)))
921 AND ((tlinfo.DECISION_OU_START_DT = X_DECISION_OU_START_DT)
922 OR ((tlinfo.DECISION_OU_START_DT is null)
923 AND (X_DECISION_OU_START_DT is null)))
924 AND ((tlinfo.DECISION_STATUS = X_DECISION_STATUS)
925 OR ((tlinfo.DECISION_STATUS is null)
926 AND (X_DECISION_STATUS is null)))
927 AND ((tlinfo.DURATION = X_DURATION)
928 OR ((tlinfo.DURATION is null)
929 AND (X_DURATION is null)))
930 AND ((tlinfo.DURATION_TYPE = X_DURATION_TYPE)
931 OR ((tlinfo.DURATION_TYPE is null)
932 AND (X_DURATION_TYPE is null)))
933 AND ((tlinfo.ENCMB_COURSE_GROUP_CD = X_ENCMB_COURSE_GROUP_CD)
934 OR ((tlinfo.ENCMB_COURSE_GROUP_CD is null)
935 AND (X_ENCMB_COURSE_GROUP_CD is null)))
936 AND ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
937 OR ((tlinfo.EXPIRY_DT is null)
938 AND (X_EXPIRY_DT is null)))
939 AND (tlinfo.HIST_END_DT = X_HIST_END_DT)
940 AND (tlinfo.HIST_WHO = X_HIST_WHO)
941 AND ((tlinfo.PRA_SEQUENCE_NUMBER = X_PRA_SEQUENCE_NUMBER)
942 OR ((tlinfo.PRA_SEQUENCE_NUMBER is null)
943 AND (X_PRA_SEQUENCE_NUMBER is null)))
944 AND ((tlinfo.PRG_CAL_TYPE = X_PRG_CAL_TYPE)
945 OR ((tlinfo.PRG_CAL_TYPE is null)
946 AND (X_PRG_CAL_TYPE is null)))
947 AND ((tlinfo.PRG_CI_SEQUENCE_NUMBER = X_PRG_CI_SEQUENCE_NUMBER)
948 OR ((tlinfo.PRG_CI_SEQUENCE_NUMBER is null)
949 AND (X_PRG_CI_SEQUENCE_NUMBER is null)))
950 AND ((tlinfo.PROGRESSION_OUTCOME_TYPE = X_PROGRESSION_OUTCOME_TYPE)
951 OR ((tlinfo.PROGRESSION_OUTCOME_TYPE is null)
952 AND (X_PROGRESSION_OUTCOME_TYPE is null)))
953 AND ((tlinfo.PROGRESSION_RULE_CAT = X_PROGRESSION_RULE_CAT)
954 OR ((tlinfo.PROGRESSION_RULE_CAT is null)
955 AND (X_PROGRESSION_RULE_CAT is null)))
956 AND ((tlinfo.PRO_PRA_SEQUENCE_NUMBER = X_PRO_PRA_SEQUENCE_NUMBER)
957 OR ((tlinfo.PRO_PRA_SEQUENCE_NUMBER is null)
958 AND (X_PRO_PRA_SEQUENCE_NUMBER is null)))
959 AND ((tlinfo.PRO_SEQUENCE_NUMBER = X_PRO_SEQUENCE_NUMBER)
960 OR ((tlinfo.PRO_SEQUENCE_NUMBER is null)
961 AND (X_PRO_SEQUENCE_NUMBER is null)))
962 AND ((tlinfo.RESTRICTED_ATTENDANCE_TYPE = X_RESTRICTED_ATTENDANCE_TYPE)
963 OR ((tlinfo.RESTRICTED_ATTENDANCE_TYPE is null)
964 AND (X_RESTRICTED_ATTENDANCE_TYPE is null)))
965 AND ((tlinfo.RESTRICTED_ENROLMENT_CP = X_RESTRICTED_ENROLMENT_CP)
966 OR ((tlinfo.RESTRICTED_ENROLMENT_CP is null)
967 AND (X_RESTRICTED_ENROLMENT_CP is null)))
968 AND ((tlinfo.RULE_CHECK_DT = X_RULE_CHECK_DT)
969 OR ((tlinfo.RULE_CHECK_DT is null)
970 AND (X_RULE_CHECK_DT is null)))
971 AND ((tlinfo.SHOW_CAUSE_COMMENTS = X_SHOW_CAUSE_COMMENTS)
972 OR ((tlinfo.SHOW_CAUSE_COMMENTS is null)
973 AND (X_SHOW_CAUSE_COMMENTS is null)))
974 AND ((tlinfo.SHOW_CAUSE_DT = X_SHOW_CAUSE_DT)
975 OR ((tlinfo.SHOW_CAUSE_DT is null)
976 AND (X_SHOW_CAUSE_DT is null)))
977 AND ((tlinfo.SHOW_CAUSE_EXPIRY_DT = X_SHOW_CAUSE_EXPIRY_DT)
978 OR ((tlinfo.SHOW_CAUSE_EXPIRY_DT is null)
979 AND (X_SHOW_CAUSE_EXPIRY_DT is null)))
980 AND ((tlinfo.SHOW_CAUSE_OUTCOME_DT = X_SHOW_CAUSE_OUTCOME_DT)
981 OR ((tlinfo.SHOW_CAUSE_OUTCOME_DT is null)
982 AND (X_SHOW_CAUSE_OUTCOME_DT is null)))
983 AND ((tlinfo.SHOW_CAUSE_OUTCOME_TYPE = X_SHOW_CAUSE_OUTCOME_TYPE)
984 OR ((tlinfo.SHOW_CAUSE_OUTCOME_TYPE is null)
985 AND (X_SHOW_CAUSE_OUTCOME_TYPE is null)))
986 ) then
987 null;
988 else
989 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
990 IGS_GE_MSG_STACK.ADD;
991 app_exception.raise_exception;
992 end if;
993 return;
994 end LOCK_ROW;
995 Procedure UPDATE_ROW (
996 X_ROWID in VARCHAR2,
997 x_PERSON_ID IN NUMBER,
998 x_COURSE_CD IN VARCHAR2,
999 x_SEQUENCE_NUMBER IN NUMBER,
1000 x_HIST_START_DT IN DATE,
1001 x_APPEAL_COMMENTS IN VARCHAR2,
1002 x_APPEAL_DT IN DATE,
1003 x_APPEAL_EXPIRY_DT IN DATE,
1004 x_APPEAL_OUTCOME_DT IN DATE,
1005 x_APPEAL_OUTCOME_TYPE IN VARCHAR2,
1006 x_APPLIED_DT IN DATE,
1007 x_COMMENTS IN VARCHAR2,
1008 x_DECISION_DT IN DATE,
1009 x_DECISION_ORG_UNIT_CD IN VARCHAR2,
1010 x_DECISION_OU_START_DT IN DATE,
1011 x_DECISION_STATUS IN VARCHAR2,
1012 x_DURATION IN NUMBER,
1013 x_DURATION_TYPE IN VARCHAR2,
1014 x_ENCMB_COURSE_GROUP_CD IN VARCHAR2,
1015 x_EXPIRY_DT IN DATE,
1016 x_HIST_END_DT IN DATE,
1017 x_HIST_WHO IN VARCHAR2,
1018 x_PRA_SEQUENCE_NUMBER IN NUMBER,
1019 x_PRG_CAL_TYPE IN VARCHAR2,
1020 x_PRG_CI_SEQUENCE_NUMBER IN NUMBER,
1021 x_PROGRESSION_OUTCOME_TYPE IN VARCHAR2,
1022 x_PROGRESSION_RULE_CAT IN VARCHAR2,
1023 x_PRO_PRA_SEQUENCE_NUMBER IN NUMBER,
1024 x_PRO_SEQUENCE_NUMBER IN NUMBER,
1025 x_RESTRICTED_ATTENDANCE_TYPE IN VARCHAR2,
1026 x_RESTRICTED_ENROLMENT_CP IN NUMBER,
1027 x_RULE_CHECK_DT IN DATE,
1028 x_SHOW_CAUSE_COMMENTS IN VARCHAR2,
1029 x_SHOW_CAUSE_DT IN DATE,
1030 x_SHOW_CAUSE_EXPIRY_DT IN DATE,
1031 x_SHOW_CAUSE_OUTCOME_DT IN DATE,
1032 x_SHOW_CAUSE_OUTCOME_TYPE IN DATE,
1033 X_MODE in VARCHAR2 default 'R'
1034 ) AS
1035 /*************************************************************
1036 Created By :
1037 Date Created By :
1038 Purpose :
1039 Know limitations, enhancements or remarks
1040 Change History
1041 Who When What
1042
1043 (reverse chronological order - newest change first)
1044 ***************************************************************/
1045
1046 X_LAST_UPDATE_DATE DATE ;
1047 X_LAST_UPDATED_BY NUMBER ;
1048 X_LAST_UPDATE_LOGIN NUMBER ;
1049 begin
1050 X_LAST_UPDATE_DATE := SYSDATE;
1051 if(X_MODE = 'I') then
1052 X_LAST_UPDATED_BY := 1;
1053 X_LAST_UPDATE_LOGIN := 0;
1054 elsif (X_MODE = 'R') then
1055 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1056 if X_LAST_UPDATED_BY is NULL then
1057 X_LAST_UPDATED_BY := -1;
1058 end if;
1059 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1060 if X_LAST_UPDATE_LOGIN is NULL then
1061 X_LAST_UPDATE_LOGIN := -1;
1062 end if;
1063 else
1064 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1065 IGS_GE_MSG_STACK.ADD;
1066 app_exception.raise_exception;
1067 end if;
1068 Before_DML(
1069 p_action=>'UPDATE',
1070 x_rowid=>X_ROWID,
1071 x_person_id=>X_PERSON_ID,
1072 x_course_cd=>X_COURSE_CD,
1073 x_sequence_number=>X_SEQUENCE_NUMBER,
1074 x_hist_start_dt=>X_HIST_START_DT,
1075 x_appeal_comments=>X_APPEAL_COMMENTS,
1076 x_appeal_dt=>X_APPEAL_DT,
1077 x_appeal_expiry_dt=>X_APPEAL_EXPIRY_DT,
1078 x_appeal_outcome_dt=>X_APPEAL_OUTCOME_DT,
1079 x_appeal_outcome_type=>X_APPEAL_OUTCOME_TYPE,
1080 x_applied_dt=>X_APPLIED_DT,
1081 x_comments=>X_COMMENTS,
1082 x_decision_dt=>X_DECISION_DT,
1083 x_decision_org_unit_cd=>X_DECISION_ORG_UNIT_CD,
1084 x_decision_ou_start_dt=>X_DECISION_OU_START_DT,
1085 x_decision_status=>X_DECISION_STATUS,
1086 x_duration=>X_DURATION,
1087 x_duration_type=>X_DURATION_TYPE,
1088 x_encmb_course_group_cd=>X_ENCMB_COURSE_GROUP_CD,
1089 x_expiry_dt=>X_EXPIRY_DT,
1090 x_hist_end_dt=>X_HIST_END_DT,
1091 x_hist_who=>X_HIST_WHO,
1092 x_pra_sequence_number=>X_PRA_SEQUENCE_NUMBER,
1093 x_prg_cal_type=>X_PRG_CAL_TYPE,
1094 x_prg_ci_sequence_number=>X_PRG_CI_SEQUENCE_NUMBER,
1095 x_progression_outcome_type=>X_PROGRESSION_OUTCOME_TYPE,
1096 x_progression_rule_cat=>X_PROGRESSION_RULE_CAT,
1097 x_pro_pra_sequence_number=>X_PRO_PRA_SEQUENCE_NUMBER,
1098 x_pro_sequence_number=>X_PRO_SEQUENCE_NUMBER,
1099 x_restricted_attendance_type=>X_RESTRICTED_ATTENDANCE_TYPE,
1100 x_restricted_enrolment_cp=>X_RESTRICTED_ENROLMENT_CP,
1101 x_rule_check_dt=>X_RULE_CHECK_DT,
1102 x_show_cause_comments=>X_SHOW_CAUSE_COMMENTS,
1103 x_show_cause_dt=>X_SHOW_CAUSE_DT,
1104 x_show_cause_expiry_dt=>X_SHOW_CAUSE_EXPIRY_DT,
1105 x_show_cause_outcome_dt=>X_SHOW_CAUSE_OUTCOME_DT,
1106 x_show_cause_outcome_type=>X_SHOW_CAUSE_OUTCOME_TYPE,
1107 x_creation_date=>X_LAST_UPDATE_DATE,
1108 x_created_by=>X_LAST_UPDATED_BY,
1109 x_last_update_date=>X_LAST_UPDATE_DATE,
1110 x_last_updated_by=>X_LAST_UPDATED_BY,
1111 x_last_update_login=>X_LAST_UPDATE_LOGIN );
1112 update IGS_PR_STU_OU_HIST_ALL set
1113 APPEAL_COMMENTS = NEW_REFERENCES.APPEAL_COMMENTS,
1114 APPEAL_DT = NEW_REFERENCES.APPEAL_DT,
1115 APPEAL_EXPIRY_DT = NEW_REFERENCES.APPEAL_EXPIRY_DT,
1116 APPEAL_OUTCOME_DT = NEW_REFERENCES.APPEAL_OUTCOME_DT,
1117 APPEAL_OUTCOME_TYPE = NEW_REFERENCES.APPEAL_OUTCOME_TYPE,
1118 APPLIED_DT = NEW_REFERENCES.APPLIED_DT,
1119 COMMENTS = NEW_REFERENCES.COMMENTS,
1120 DECISION_DT = NEW_REFERENCES.DECISION_DT,
1121 DECISION_ORG_UNIT_CD = NEW_REFERENCES.DECISION_ORG_UNIT_CD,
1122 DECISION_OU_START_DT = NEW_REFERENCES.DECISION_OU_START_DT,
1123 DECISION_STATUS = NEW_REFERENCES.DECISION_STATUS,
1124 DURATION = NEW_REFERENCES.DURATION,
1125 DURATION_TYPE = NEW_REFERENCES.DURATION_TYPE,
1126 ENCMB_COURSE_GROUP_CD = NEW_REFERENCES.ENCMB_COURSE_GROUP_CD,
1127 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
1128 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
1129 HIST_WHO = NEW_REFERENCES.HIST_WHO,
1130 PRA_SEQUENCE_NUMBER = NEW_REFERENCES.PRA_SEQUENCE_NUMBER,
1131 PRG_CAL_TYPE = NEW_REFERENCES.PRG_CAL_TYPE,
1132 PRG_CI_SEQUENCE_NUMBER = NEW_REFERENCES.PRG_CI_SEQUENCE_NUMBER,
1133 PROGRESSION_OUTCOME_TYPE = NEW_REFERENCES.PROGRESSION_OUTCOME_TYPE,
1134 PROGRESSION_RULE_CAT = NEW_REFERENCES.PROGRESSION_RULE_CAT,
1135 PRO_PRA_SEQUENCE_NUMBER = NEW_REFERENCES.PRO_PRA_SEQUENCE_NUMBER,
1136 PRO_SEQUENCE_NUMBER = NEW_REFERENCES.PRO_SEQUENCE_NUMBER,
1137 RESTRICTED_ATTENDANCE_TYPE = NEW_REFERENCES.RESTRICTED_ATTENDANCE_TYPE,
1138 RESTRICTED_ENROLMENT_CP = NEW_REFERENCES.RESTRICTED_ENROLMENT_CP,
1139 RULE_CHECK_DT = NEW_REFERENCES.RULE_CHECK_DT,
1140 SHOW_CAUSE_COMMENTS = NEW_REFERENCES.SHOW_CAUSE_COMMENTS,
1141 SHOW_CAUSE_DT = NEW_REFERENCES.SHOW_CAUSE_DT,
1142 SHOW_CAUSE_EXPIRY_DT = NEW_REFERENCES.SHOW_CAUSE_EXPIRY_DT,
1143 SHOW_CAUSE_OUTCOME_DT = NEW_REFERENCES.SHOW_CAUSE_OUTCOME_DT,
1144 SHOW_CAUSE_OUTCOME_TYPE = NEW_REFERENCES.SHOW_CAUSE_OUTCOME_TYPE,
1145 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1146 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1147 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1148 where ROWID = X_ROWID;
1149 if (sql%notfound) then
1150 raise no_data_found;
1151 end if;
1152
1153 After_DML (
1154 p_action => 'UPDATE' ,
1155 x_rowid => X_ROWID
1156 );
1157 end UPDATE_ROW;
1158 procedure ADD_ROW (
1159 X_ROWID in out NOCOPY VARCHAR2,
1160 x_PERSON_ID IN NUMBER,
1161 x_COURSE_CD IN VARCHAR2,
1162 x_SEQUENCE_NUMBER IN NUMBER,
1163 x_HIST_START_DT IN DATE,
1164 x_APPEAL_COMMENTS IN VARCHAR2,
1165 x_APPEAL_DT IN DATE,
1166 x_APPEAL_EXPIRY_DT IN DATE,
1167 x_APPEAL_OUTCOME_DT IN DATE,
1168 x_APPEAL_OUTCOME_TYPE IN VARCHAR2,
1169 x_APPLIED_DT IN DATE,
1170 x_COMMENTS IN VARCHAR2,
1171 x_DECISION_DT IN DATE,
1172 x_DECISION_ORG_UNIT_CD IN VARCHAR2,
1173 x_DECISION_OU_START_DT IN DATE,
1174 x_DECISION_STATUS IN VARCHAR2,
1175 x_DURATION IN NUMBER,
1176 x_DURATION_TYPE IN VARCHAR2,
1177 x_ENCMB_COURSE_GROUP_CD IN VARCHAR2,
1178 x_EXPIRY_DT IN DATE,
1179 x_HIST_END_DT IN DATE,
1180 x_HIST_WHO IN VARCHAR2,
1181 x_PRA_SEQUENCE_NUMBER IN NUMBER,
1182 x_PRG_CAL_TYPE IN VARCHAR2,
1183 x_PRG_CI_SEQUENCE_NUMBER IN NUMBER,
1184 x_PROGRESSION_OUTCOME_TYPE IN VARCHAR2,
1185 x_PROGRESSION_RULE_CAT IN VARCHAR2,
1186 x_PRO_PRA_SEQUENCE_NUMBER IN NUMBER,
1187 x_PRO_SEQUENCE_NUMBER IN NUMBER,
1188 x_RESTRICTED_ATTENDANCE_TYPE IN VARCHAR2,
1189 x_RESTRICTED_ENROLMENT_CP IN NUMBER,
1190 x_RULE_CHECK_DT IN DATE,
1191 x_SHOW_CAUSE_COMMENTS IN VARCHAR2,
1192 x_SHOW_CAUSE_DT IN DATE,
1193 x_SHOW_CAUSE_EXPIRY_DT IN DATE,
1194 x_SHOW_CAUSE_OUTCOME_DT IN DATE,
1195 x_SHOW_CAUSE_OUTCOME_TYPE IN DATE,
1196 X_MODE in VARCHAR2 default 'R',
1197 x_ORG_ID IN NUMBER
1198 ) AS
1199 /*************************************************************
1200 Created By :
1201 Date Created By :
1202 Purpose :
1203 Know limitations, enhancements or remarks
1204 Change History
1205 Who When What
1206
1207 (reverse chronological order - newest change first)
1208 ***************************************************************/
1209
1210 cursor c1 is select ROWID from IGS_PR_STU_OU_HIST_ALL
1211 where PERSON_ID= X_PERSON_ID
1212 and COURSE_CD = X_COURSE_CD
1213 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1214 and HIST_START_DT = X_HIST_START_DT
1215 ;
1216 begin
1217 open c1;
1218 fetch c1 into X_ROWID;
1219 if (c1%notfound) then
1220 close c1;
1221 INSERT_ROW (
1222 X_ROWID,
1223 X_PERSON_ID,
1224 X_COURSE_CD,
1225 X_SEQUENCE_NUMBER,
1226 X_HIST_START_DT,
1227 X_APPEAL_COMMENTS,
1228 X_APPEAL_DT,
1229 X_APPEAL_EXPIRY_DT,
1230 X_APPEAL_OUTCOME_DT,
1231 X_APPEAL_OUTCOME_TYPE,
1232 X_APPLIED_DT,
1233 X_COMMENTS,
1234 X_DECISION_DT,
1235 X_DECISION_ORG_UNIT_CD,
1236 X_DECISION_OU_START_DT,
1237 X_DECISION_STATUS,
1238 X_DURATION,
1239 X_DURATION_TYPE,
1240 X_ENCMB_COURSE_GROUP_CD,
1241 X_EXPIRY_DT,
1242 X_HIST_END_DT,
1243 X_HIST_WHO,
1244 X_PRA_SEQUENCE_NUMBER,
1245 X_PRG_CAL_TYPE,
1246 X_PRG_CI_SEQUENCE_NUMBER,
1247 X_PROGRESSION_OUTCOME_TYPE,
1248 X_PROGRESSION_RULE_CAT,
1249 X_PRO_PRA_SEQUENCE_NUMBER,
1250 X_PRO_SEQUENCE_NUMBER,
1251 X_RESTRICTED_ATTENDANCE_TYPE,
1252 X_RESTRICTED_ENROLMENT_CP,
1253 X_RULE_CHECK_DT,
1254 X_SHOW_CAUSE_COMMENTS,
1255 X_SHOW_CAUSE_DT,
1256 X_SHOW_CAUSE_EXPIRY_DT,
1257 X_SHOW_CAUSE_OUTCOME_DT,
1258 X_SHOW_CAUSE_OUTCOME_TYPE,
1259 X_MODE,
1260 X_ORG_ID );
1261 return;
1262 end if;
1263 close c1;
1264 UPDATE_ROW (
1265 X_ROWID,
1266 X_PERSON_ID,
1267 X_COURSE_CD,
1268 X_SEQUENCE_NUMBER,
1269 X_HIST_START_DT,
1270 X_APPEAL_COMMENTS,
1271 X_APPEAL_DT,
1272 X_APPEAL_EXPIRY_DT,
1273 X_APPEAL_OUTCOME_DT,
1274 X_APPEAL_OUTCOME_TYPE,
1275 X_APPLIED_DT,
1276 X_COMMENTS,
1277 X_DECISION_DT,
1278 X_DECISION_ORG_UNIT_CD,
1279 X_DECISION_OU_START_DT,
1280 X_DECISION_STATUS,
1281 X_DURATION,
1282 X_DURATION_TYPE,
1283 X_ENCMB_COURSE_GROUP_CD,
1284 X_EXPIRY_DT,
1285 X_HIST_END_DT,
1286 X_HIST_WHO,
1287 X_PRA_SEQUENCE_NUMBER,
1288 X_PRG_CAL_TYPE,
1289 X_PRG_CI_SEQUENCE_NUMBER,
1290 X_PROGRESSION_OUTCOME_TYPE,
1291 X_PROGRESSION_RULE_CAT,
1292 X_PRO_PRA_SEQUENCE_NUMBER,
1293 X_PRO_SEQUENCE_NUMBER,
1294 X_RESTRICTED_ATTENDANCE_TYPE,
1295 X_RESTRICTED_ENROLMENT_CP,
1296 X_RULE_CHECK_DT,
1297 X_SHOW_CAUSE_COMMENTS,
1298 X_SHOW_CAUSE_DT,
1299 X_SHOW_CAUSE_EXPIRY_DT,
1300 X_SHOW_CAUSE_OUTCOME_DT,
1301 X_SHOW_CAUSE_OUTCOME_TYPE,
1302 X_MODE );
1303 end ADD_ROW;
1304 procedure DELETE_ROW (
1305 X_ROWID in VARCHAR2
1306 ) AS
1307 /*************************************************************
1308 Created By :
1309 Date Created By :
1310 Purpose :
1311 Know limitations, enhancements or remarks
1312 Change History
1313 Who When What
1314
1315 (reverse chronological order - newest change first)
1316 ***************************************************************/
1317
1318 begin
1319 Before_DML (
1320 p_action => 'DELETE',
1321 x_rowid => X_ROWID
1322 );
1323 delete from IGS_PR_STU_OU_HIST_ALL
1324 where ROWID = X_ROWID;
1325 if (sql%notfound) then
1326 raise no_data_found;
1327 end if;
1328 After_DML (
1329 p_action => 'DELETE',
1330 x_rowid => X_ROWID
1331 );
1332 end DELETE_ROW;
1333 END igs_pr_stu_ou_hist_pkg;