[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SU_ATMPTOUT_H_PKG
Source
1 PACKAGE BODY igs_as_su_atmptout_h_pkg AS
2 /* $Header: IGSDI05B.pls 115.8 2003/12/11 09:50:36 kdande ship $ */
3 l_rowid VARCHAR2 (25);
4 old_references igs_as_su_atmptout_h_all%ROWTYPE;
5 new_references igs_as_su_atmptout_h_all%ROWTYPE;
6
7 PROCEDURE set_column_values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_course_cd IN VARCHAR2 DEFAULT NULL,
13 x_unit_cd IN VARCHAR2 DEFAULT NULL,
14 x_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_ci_sequence_number IN NUMBER DEFAULT NULL,
16 x_outcome_dt IN DATE DEFAULT NULL,
17 x_hist_start_dt IN DATE DEFAULT NULL,
18 x_hist_end_dt IN DATE DEFAULT NULL,
19 x_hist_who IN NUMBER DEFAULT NULL,
20 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
21 x_version_number IN NUMBER DEFAULT NULL,
22 x_grade IN VARCHAR2 DEFAULT NULL,
23 x_s_grade_creation_method_type IN VARCHAR2 DEFAULT NULL,
24 x_finalised_outcome_ind IN VARCHAR2 DEFAULT NULL,
25 x_mark IN NUMBER DEFAULT NULL,
26 x_number_times_keyed IN NUMBER DEFAULT NULL,
27 x_translated_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
28 x_translated_version_number IN NUMBER DEFAULT NULL,
29 x_translated_grade IN VARCHAR2 DEFAULT NULL,
30 x_translated_dt IN DATE DEFAULT NULL,
31 x_creation_date IN DATE DEFAULT NULL,
32 x_created_by IN NUMBER DEFAULT NULL,
33 x_last_update_date IN DATE DEFAULT NULL,
34 x_last_updated_by IN NUMBER DEFAULT NULL,
35 x_last_update_login IN NUMBER DEFAULT NULL,
36 x_uoo_id IN NUMBER DEFAULT NULL,
37 x_mark_capped_flag IN VARCHAR2 DEFAULT NULL,
38 x_show_on_academic_histry_flag IN VARCHAR2 DEFAULT NULL,
39 x_release_date IN DATE DEFAULT NULL,
40 x_manual_override_flag IN VARCHAR2 DEFAULT NULL
41 ) AS
42 CURSOR cur_old_ref_values IS
43 SELECT *
44 FROM igs_as_su_atmptout_h_all
45 WHERE ROWID = x_rowid;
46 BEGIN
47 l_rowid := x_rowid;
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 OPEN cur_old_ref_values;
51 FETCH cur_old_ref_values INTO old_references;
52
53 IF (cur_old_ref_values%NOTFOUND)
54 AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
55 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
56 igs_ge_msg_stack.ADD;
57 CLOSE cur_old_ref_values;
58 app_exception.raise_exception;
59 RETURN;
60 END IF;
61
62 CLOSE cur_old_ref_values;
63 -- Populate New Values.
64 new_references.org_id := x_org_id;
65 new_references.person_id := x_person_id;
66 new_references.course_cd := x_course_cd;
67 new_references.unit_cd := x_unit_cd;
68 new_references.cal_type := x_cal_type;
69 new_references.ci_sequence_number := x_ci_sequence_number;
70 new_references.outcome_dt := x_outcome_dt;
71 new_references.hist_start_dt := x_hist_start_dt;
72 new_references.hist_end_dt := x_hist_end_dt;
73 new_references.hist_who := x_hist_who;
74 new_references.grading_schema_cd := x_grading_schema_cd;
75 new_references.version_number := x_version_number;
76 new_references.grade := x_grade;
77 new_references.s_grade_creation_method_type := x_s_grade_creation_method_type;
78 new_references.finalised_outcome_ind := x_finalised_outcome_ind;
79 new_references.mark := x_mark;
80 new_references.number_times_keyed := x_number_times_keyed;
81 new_references.translated_grading_schema_cd := x_translated_grading_schema_cd;
82 new_references.translated_version_number := x_translated_version_number;
83 new_references.translated_grade := x_translated_grade;
84 new_references.translated_dt := x_translated_dt;
85 new_references.uoo_id := x_uoo_id;
86 new_references.mark_capped_flag := x_mark_capped_flag;
87 new_references.show_on_academic_histry_flag := x_show_on_academic_histry_flag;
88 new_references.release_date := x_release_date;
89 new_references.manual_override_flag := x_manual_override_flag;
90
91 IF (p_action = 'UPDATE') THEN
92 new_references.creation_date := old_references.creation_date;
93 new_references.created_by := old_references.created_by;
94 ELSE
95 new_references.creation_date := x_creation_date;
96 new_references.created_by := x_created_by;
97 END IF;
98
99 new_references.last_update_date := x_last_update_date;
100 new_references.last_updated_by := x_last_updated_by;
101 new_references.last_update_login := x_last_update_login;
102 END set_column_values;
103
104 -------------------------------------------------------------------------------------------
105 --Change History:
106 --Who When What
107 --svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
108 -------------------------------------------------------------------------------------------
109 FUNCTION get_pk_for_validation (
110 x_person_id IN NUMBER,
111 x_course_cd IN VARCHAR2,
112 x_outcome_dt IN DATE,
113 x_hist_start_dt IN DATE,
114 x_uoo_id IN NUMBER
115 )
116 RETURN BOOLEAN AS
117 CURSOR cur_rowid IS
118 SELECT ROWID
119 FROM igs_as_su_atmptout_h_all
120 WHERE person_id = x_person_id
121 AND course_cd = x_course_cd
122 AND outcome_dt = x_outcome_dt
123 AND hist_start_dt = x_hist_start_dt
124 AND uoo_id = x_uoo_id
125 FOR UPDATE NOWAIT;
126
127 lv_rowid cur_rowid%ROWTYPE;
128 BEGIN
129 OPEN cur_rowid;
130 FETCH cur_rowid INTO lv_rowid;
131
132 IF (cur_rowid%FOUND) THEN
133 CLOSE cur_rowid;
134 RETURN (TRUE);
135 ELSE
136 CLOSE cur_rowid;
137 RETURN (FALSE);
138 END IF;
139 END get_pk_for_validation;
140
141 PROCEDURE before_dml (
142 p_action IN VARCHAR2,
143 x_rowid IN VARCHAR2 DEFAULT NULL,
144 x_org_id IN NUMBER DEFAULT NULL,
145 x_person_id IN NUMBER DEFAULT NULL,
146 x_course_cd IN VARCHAR2 DEFAULT NULL,
147 x_unit_cd IN VARCHAR2 DEFAULT NULL,
148 x_cal_type IN VARCHAR2 DEFAULT NULL,
149 x_ci_sequence_number IN NUMBER DEFAULT NULL,
150 x_outcome_dt IN DATE DEFAULT NULL,
151 x_hist_start_dt IN DATE DEFAULT NULL,
152 x_hist_end_dt IN DATE DEFAULT NULL,
153 x_hist_who IN NUMBER DEFAULT NULL,
154 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
155 x_version_number IN NUMBER DEFAULT NULL,
156 x_grade IN VARCHAR2 DEFAULT NULL,
157 x_s_grade_creation_method_type IN VARCHAR2 DEFAULT NULL,
158 x_finalised_outcome_ind IN VARCHAR2 DEFAULT NULL,
159 x_mark IN NUMBER DEFAULT NULL,
160 x_number_times_keyed IN NUMBER DEFAULT NULL,
161 x_translated_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
162 x_translated_version_number IN NUMBER DEFAULT NULL,
163 x_translated_grade IN VARCHAR2 DEFAULT NULL,
164 x_translated_dt IN DATE DEFAULT NULL,
165 x_creation_date IN DATE DEFAULT NULL,
166 x_created_by IN NUMBER DEFAULT NULL,
167 x_last_update_date IN DATE DEFAULT NULL,
168 x_last_updated_by IN NUMBER DEFAULT NULL,
169 x_last_update_login IN NUMBER DEFAULT NULL,
170 x_uoo_id IN NUMBER DEFAULT NULL,
171 x_mark_capped_flag IN VARCHAR2 DEFAULT NULL,
172 x_show_on_academic_histry_flag IN VARCHAR2 DEFAULT NULL,
173 x_release_date IN DATE DEFAULT NULL,
174 x_manual_override_flag IN VARCHAR2 DEFAULT NULL
175 ) AS
176 BEGIN
177 set_column_values (
178 p_action,
179 x_rowid,
180 x_org_id,
181 x_person_id,
182 x_course_cd,
183 x_unit_cd,
184 x_cal_type,
185 x_ci_sequence_number,
186 x_outcome_dt,
187 x_hist_start_dt,
188 x_hist_end_dt,
189 x_hist_who,
190 x_grading_schema_cd,
191 x_version_number,
192 x_grade,
193 x_s_grade_creation_method_type,
194 x_finalised_outcome_ind,
195 x_mark,
196 x_number_times_keyed,
197 x_translated_grading_schema_cd,
198 x_translated_version_number,
199 x_translated_grade,
200 x_translated_dt,
201 x_creation_date,
202 x_created_by,
203 x_last_update_date,
204 x_last_updated_by,
205 x_last_update_login,
206 x_uoo_id,
207 x_mark_capped_flag,
208 x_show_on_academic_histry_flag,
209 x_release_date,
210 x_manual_override_flag
211 );
212
213 IF (p_action = 'INSERT') THEN
214 -- Call all the procedures related to Before Insert.
215 IF get_pk_for_validation (
216 new_references.person_id,
217 new_references.course_cd,
218 new_references.outcome_dt,
219 new_references.hist_start_dt,
220 new_references.uoo_id
221 ) THEN
222 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
223 igs_ge_msg_stack.ADD;
224 app_exception.raise_exception;
225 END IF;
226 check_constraints;
227 ELSIF (p_action = 'UPDATE') THEN
228 -- Call all the procedures related to Before Update.
229 check_constraints;
230 ELSIF (p_action = 'VALIDATE_INSERT') THEN
231 IF get_pk_for_validation (
232 new_references.person_id,
233 new_references.course_cd,
234 new_references.outcome_dt,
235 new_references.hist_start_dt,
236 new_references.uoo_id
237 ) THEN
238 fnd_message.set_name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
239 igs_ge_msg_stack.ADD;
240 app_exception.raise_exception;
241 END IF;
242 check_constraints;
243 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
244 check_constraints;
245 END IF;
246 END before_dml;
247
248 PROCEDURE insert_row (
249 x_rowid IN OUT NOCOPY VARCHAR2,
250 x_org_id IN NUMBER,
251 x_person_id IN NUMBER,
252 x_course_cd IN VARCHAR2,
253 x_unit_cd IN VARCHAR2,
254 x_cal_type IN VARCHAR2,
255 x_ci_sequence_number IN NUMBER,
256 x_outcome_dt IN DATE,
257 x_hist_start_dt IN DATE,
258 x_hist_end_dt IN DATE,
259 x_hist_who IN NUMBER,
260 x_grading_schema_cd IN VARCHAR2,
261 x_version_number IN NUMBER,
262 x_grade IN VARCHAR2,
263 x_s_grade_creation_method_type IN VARCHAR2,
264 x_finalised_outcome_ind IN VARCHAR2,
265 x_mark IN NUMBER,
266 x_number_times_keyed IN NUMBER,
267 x_translated_grading_schema_cd IN VARCHAR2,
268 x_translated_version_number IN NUMBER,
269 x_translated_grade IN VARCHAR2,
270 x_translated_dt IN DATE,
271 x_mode IN VARCHAR2 DEFAULT 'R',
272 x_uoo_id IN NUMBER,
273 x_mark_capped_flag IN VARCHAR2 DEFAULT 'N',
274 x_show_on_academic_histry_flag IN VARCHAR2 DEFAULT 'Y',
275 x_release_date IN DATE DEFAULT NULL,
276 x_manual_override_flag IN VARCHAR2 DEFAULT 'N'
277 ) IS
278 CURSOR c IS
279 SELECT ROWID
280 FROM igs_as_su_atmptout_h_all
281 WHERE person_id = x_person_id
282 AND course_cd = x_course_cd
283 AND uoo_id = x_uoo_id
284 AND outcome_dt = x_outcome_dt
285 AND hist_start_dt = x_hist_start_dt;
286
287 x_last_update_date DATE;
288 x_last_updated_by NUMBER;
289 x_last_update_login NUMBER;
290 BEGIN
291 x_last_update_date := SYSDATE;
292
293 IF (x_mode = 'I') THEN
294 x_last_updated_by := 1;
295 x_last_update_login := 0;
296 ELSIF (x_mode = 'R') THEN
297 x_last_updated_by := fnd_global.user_id;
298
299 IF x_last_updated_by IS NULL THEN
300 x_last_updated_by := -1;
301 END IF;
302
303 x_last_update_login := fnd_global.login_id;
304
305 IF x_last_update_login IS NULL THEN
306 x_last_update_login := -1;
307 END IF;
308 ELSE
309 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
310 igs_ge_msg_stack.ADD;
311 app_exception.raise_exception;
312 END IF;
313
314 before_dml (
315 p_action => 'INSERT',
316 x_rowid => x_rowid,
317 x_org_id => igs_ge_gen_003.get_org_id,
318 x_cal_type => x_cal_type,
319 x_ci_sequence_number => x_ci_sequence_number,
320 x_course_cd => x_course_cd,
321 x_finalised_outcome_ind => x_finalised_outcome_ind,
322 x_grade => x_grade,
323 x_grading_schema_cd => x_grading_schema_cd,
324 x_hist_end_dt => x_hist_end_dt,
325 x_hist_start_dt => x_hist_start_dt,
326 x_hist_who => x_hist_who,
327 x_mark => x_mark,
328 x_number_times_keyed => x_number_times_keyed,
329 x_outcome_dt => x_outcome_dt,
330 x_person_id => x_person_id,
331 x_s_grade_creation_method_type => x_s_grade_creation_method_type,
332 x_translated_dt => x_translated_dt,
333 x_translated_grade => x_translated_grade,
334 x_translated_grading_schema_cd => x_translated_grading_schema_cd,
335 x_translated_version_number => x_translated_version_number,
336 x_unit_cd => x_unit_cd,
337 x_version_number => x_version_number,
338 x_creation_date => x_last_update_date,
339 x_created_by => x_last_updated_by,
340 x_last_update_date => x_last_update_date,
341 x_last_updated_by => x_last_updated_by,
342 x_last_update_login => x_last_update_login,
343 x_uoo_id => x_uoo_id,
344 x_mark_capped_flag => x_mark_capped_flag,
345 x_show_on_academic_histry_flag => x_show_on_academic_histry_flag,
346 x_release_date => x_release_date,
347 x_manual_override_flag => x_manual_override_flag
348 );
349
350 INSERT INTO igs_as_su_atmptout_h_all
351 (org_id, person_id, course_cd, unit_cd,
352 cal_type, ci_sequence_number, outcome_dt,
356 number_times_keyed, translated_grading_schema_cd,
353 hist_start_dt, hist_end_dt, hist_who,
354 grading_schema_cd, version_number, grade,
355 s_grade_creation_method_type, finalised_outcome_ind, mark,
357 translated_version_number, translated_grade,
358 translated_dt, creation_date, created_by, last_update_date,
359 last_updated_by, last_update_login, uoo_id, mark_capped_flag,
360 show_on_academic_histry_flag, release_date, manual_override_flag)
361 VALUES (new_references.org_id, new_references.person_id, new_references.course_cd, new_references.unit_cd,
362 new_references.cal_type, new_references.ci_sequence_number, new_references.outcome_dt,
363 new_references.hist_start_dt, new_references.hist_end_dt, new_references.hist_who,
364 new_references.grading_schema_cd, new_references.version_number, new_references.grade,
365 new_references.s_grade_creation_method_type, new_references.finalised_outcome_ind, new_references.mark,
366 new_references.number_times_keyed, new_references.translated_grading_schema_cd,
367 new_references.translated_version_number, new_references.translated_grade,
368 new_references.translated_dt, x_last_update_date, x_last_updated_by, x_last_update_date,
369 x_last_updated_by, x_last_update_login, new_references.uoo_id,
370 new_references.mark_capped_flag, new_references.show_on_academic_histry_flag,
371 new_references.release_date, new_references.manual_override_flag);
372
373 OPEN c;
374 FETCH c INTO x_rowid;
375
376 IF (c%NOTFOUND) THEN
377 CLOSE c;
378 RAISE NO_DATA_FOUND;
379 END IF;
380
381 CLOSE c;
382 END insert_row;
383
384 PROCEDURE lock_row (
385 x_rowid IN VARCHAR2,
386 x_person_id IN NUMBER,
387 x_course_cd IN VARCHAR2,
388 x_unit_cd IN VARCHAR2,
389 x_cal_type IN VARCHAR2,
390 x_ci_sequence_number IN NUMBER,
391 x_outcome_dt IN DATE,
392 x_hist_start_dt IN DATE,
393 x_hist_end_dt IN DATE,
394 x_hist_who IN NUMBER,
395 x_grading_schema_cd IN VARCHAR2,
396 x_version_number IN NUMBER,
397 x_grade IN VARCHAR2,
398 x_s_grade_creation_method_type IN VARCHAR2,
399 x_finalised_outcome_ind IN VARCHAR2,
400 x_mark IN NUMBER,
401 x_number_times_keyed IN NUMBER,
402 x_translated_grading_schema_cd IN VARCHAR2,
403 x_translated_version_number IN NUMBER,
404 x_translated_grade IN VARCHAR2,
405 x_translated_dt IN DATE,
406 x_uoo_id IN NUMBER,
407 x_mark_capped_flag IN VARCHAR2 DEFAULT 'N',
408 x_show_on_academic_histry_flag IN VARCHAR2 DEFAULT 'Y',
409 x_release_date IN DATE DEFAULT NULL,
410 x_manual_override_flag IN VARCHAR2 DEFAULT 'N'
411 ) IS
412 CURSOR c1 IS
413 SELECT hist_end_dt,
414 hist_who,
415 grading_schema_cd,
416 version_number,
417 grade,
418 s_grade_creation_method_type,
419 finalised_outcome_ind,
420 mark,
421 number_times_keyed,
422 translated_grading_schema_cd,
423 translated_version_number,
424 translated_grade,
425 translated_dt,
426 mark_capped_flag,
427 show_on_academic_histry_flag,
428 release_date,
429 manual_override_flag
430 FROM igs_as_su_atmptout_h_all
431 WHERE ROWID = x_rowid
432 FOR UPDATE NOWAIT;
433
434 tlinfo c1%ROWTYPE;
435 BEGIN
436 OPEN c1;
437 FETCH c1 INTO tlinfo;
438
439 IF (c1%NOTFOUND) THEN
440 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
441 igs_ge_msg_stack.ADD;
442 app_exception.raise_exception;
443 CLOSE c1;
444 RETURN;
445 END IF;
446
447 CLOSE c1;
448
449 IF ((tlinfo.hist_end_dt = x_hist_end_dt)
450 AND (tlinfo.hist_who = x_hist_who)
451 AND ((tlinfo.grading_schema_cd = x_grading_schema_cd)
452 OR ((tlinfo.grading_schema_cd IS NULL)
453 AND (x_grading_schema_cd IS NULL)
454 )
455 )
456 AND ((tlinfo.version_number = x_version_number)
457 OR ((tlinfo.version_number IS NULL)
458 AND (x_version_number IS NULL)
459 )
460 )
461 AND ((tlinfo.grade = x_grade)
462 OR ((tlinfo.grade IS NULL)
463 AND (x_grade IS NULL)
464 )
465 )
469 )
466 AND ((tlinfo.s_grade_creation_method_type = x_s_grade_creation_method_type)
467 OR ((tlinfo.s_grade_creation_method_type IS NULL)
468 AND (x_s_grade_creation_method_type IS NULL)
470 )
471 AND ((tlinfo.finalised_outcome_ind = x_finalised_outcome_ind)
472 OR ((tlinfo.finalised_outcome_ind IS NULL)
473 AND (x_finalised_outcome_ind IS NULL)
474 )
475 )
476 AND ((tlinfo.mark = x_mark)
477 OR ((tlinfo.mark IS NULL)
478 AND (x_mark IS NULL)
479 )
480 )
481 AND ((tlinfo.number_times_keyed = x_number_times_keyed)
482 OR ((tlinfo.number_times_keyed IS NULL)
483 AND (x_number_times_keyed IS NULL)
484 )
485 )
486 AND ((tlinfo.translated_grading_schema_cd = x_translated_grading_schema_cd)
487 OR ((tlinfo.translated_grading_schema_cd IS NULL)
488 AND (x_translated_grading_schema_cd IS NULL)
489 )
490 )
491 AND ((tlinfo.translated_version_number = x_translated_version_number)
492 OR ((tlinfo.translated_version_number IS NULL)
493 AND (x_translated_version_number IS NULL)
494 )
495 )
496 AND ((tlinfo.translated_grade = x_translated_grade)
497 OR ((tlinfo.translated_grade IS NULL)
498 AND (x_translated_grade IS NULL)
499 )
500 )
501 AND ((tlinfo.translated_dt = x_translated_dt)
502 OR ((tlinfo.translated_dt IS NULL)
503 AND (x_translated_dt IS NULL)
504 )
505 )
506 AND ((tlinfo.mark_capped_flag = x_mark_capped_flag)
507 OR ((tlinfo.mark_capped_flag IS NULL)
508 AND (x_mark_capped_flag IS NULL)
509 )
510 )
511 AND ((tlinfo.show_on_academic_histry_flag = x_show_on_academic_histry_flag)
512 OR ((tlinfo.show_on_academic_histry_flag IS NULL)
513 AND (x_show_on_academic_histry_flag IS NULL)
514 )
515 )
516 AND ((tlinfo.release_date = x_release_date)
517 OR ((tlinfo.release_date IS NULL)
518 AND (x_release_date IS NULL)
519 )
520 )
521 AND ((tlinfo.manual_override_flag = x_manual_override_flag)
522 OR ((tlinfo.manual_override_flag IS NULL)
523 AND (x_manual_override_flag IS NULL)
524 )
525 )
526 ) THEN
527 NULL;
528 ELSE
529 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
530 igs_ge_msg_stack.ADD;
531 app_exception.raise_exception;
532 END IF;
533
534 RETURN;
535 END lock_row;
536
537 PROCEDURE update_row (
538 x_rowid IN VARCHAR2,
539 x_person_id IN NUMBER,
540 x_course_cd IN VARCHAR2,
541 x_unit_cd IN VARCHAR2,
542 x_cal_type IN VARCHAR2,
543 x_ci_sequence_number IN NUMBER,
544 x_outcome_dt IN DATE,
545 x_hist_start_dt IN DATE,
546 x_hist_end_dt IN DATE,
547 x_hist_who IN NUMBER,
548 x_grading_schema_cd IN VARCHAR2,
549 x_version_number IN NUMBER,
550 x_grade IN VARCHAR2,
551 x_s_grade_creation_method_type IN VARCHAR2,
552 x_finalised_outcome_ind IN VARCHAR2,
553 x_mark IN NUMBER,
554 x_number_times_keyed IN NUMBER,
555 x_translated_grading_schema_cd IN VARCHAR2,
556 x_translated_version_number IN NUMBER,
557 x_translated_grade IN VARCHAR2,
558 x_translated_dt IN DATE,
559 x_mode IN VARCHAR2 DEFAULT 'R',
560 x_uoo_id IN NUMBER,
561 x_mark_capped_flag IN VARCHAR2 DEFAULT 'N',
562 x_show_on_academic_histry_flag IN VARCHAR2 DEFAULT 'Y',
563 x_release_date IN DATE DEFAULT NULL,
564 x_manual_override_flag IN VARCHAR2 DEFAULT 'N'
565 ) IS
566 x_last_update_date DATE;
567 x_last_updated_by NUMBER;
568 x_last_update_login NUMBER;
569 BEGIN
570 x_last_update_date := SYSDATE;
571
572 IF (x_mode = 'I') THEN
573 x_last_updated_by := 1;
574 x_last_update_login := 0;
575 ELSIF (x_mode = 'R') THEN
576 x_last_updated_by := fnd_global.user_id;
577
578 IF x_last_updated_by IS NULL THEN
579 x_last_updated_by := -1;
580 END IF;
581
582 x_last_update_login := fnd_global.login_id;
583
584 IF x_last_update_login IS NULL THEN
585 x_last_update_login := -1;
586 END IF;
587 ELSE
588 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
592
589 igs_ge_msg_stack.ADD;
590 app_exception.raise_exception;
591 END IF;
593 before_dml (
594 p_action => 'UPDATE',
595 x_rowid => x_rowid,
596 x_cal_type => x_cal_type,
597 x_ci_sequence_number => x_ci_sequence_number,
598 x_course_cd => x_course_cd,
599 x_finalised_outcome_ind => x_finalised_outcome_ind,
600 x_grade => x_grade,
601 x_grading_schema_cd => x_grading_schema_cd,
602 x_hist_end_dt => x_hist_end_dt,
603 x_hist_start_dt => x_hist_start_dt,
604 x_hist_who => x_hist_who,
605 x_mark => x_mark,
606 x_number_times_keyed => x_number_times_keyed,
607 x_outcome_dt => x_outcome_dt,
608 x_person_id => x_person_id,
609 x_s_grade_creation_method_type => x_s_grade_creation_method_type,
610 x_translated_dt => x_translated_dt,
611 x_translated_grade => x_translated_grade,
612 x_translated_grading_schema_cd => x_translated_grading_schema_cd,
613 x_translated_version_number => x_translated_version_number,
614 x_unit_cd => x_unit_cd,
615 x_version_number => x_version_number,
616 x_creation_date => x_last_update_date,
617 x_created_by => x_last_updated_by,
618 x_last_update_date => x_last_update_date,
619 x_last_updated_by => x_last_updated_by,
620 x_last_update_login => x_last_update_login,
621 x_uoo_id => x_uoo_id,
622 x_mark_capped_flag => x_mark_capped_flag,
623 x_show_on_academic_histry_flag => x_show_on_academic_histry_flag,
624 x_release_date => x_release_date,
625 x_manual_override_flag => x_manual_override_flag
626 );
627
628 UPDATE igs_as_su_atmptout_h_all
629 SET hist_end_dt = new_references.hist_end_dt,
630 hist_who = new_references.hist_who,
631 grading_schema_cd = new_references.grading_schema_cd,
632 version_number = new_references.version_number,
633 grade = new_references.grade,
634 s_grade_creation_method_type = new_references.s_grade_creation_method_type,
635 finalised_outcome_ind = new_references.finalised_outcome_ind,
636 mark = new_references.mark,
637 number_times_keyed = new_references.number_times_keyed,
638 translated_grading_schema_cd = new_references.translated_grading_schema_cd,
639 translated_version_number = new_references.translated_version_number,
640 translated_grade = new_references.translated_grade,
641 translated_dt = new_references.translated_dt,
642 last_update_date = x_last_update_date,
643 last_updated_by = x_last_updated_by,
644 last_update_login = x_last_update_login,
645 mark_capped_flag = x_mark_capped_flag,
646 show_on_academic_histry_flag = x_show_on_academic_histry_flag,
647 release_date = x_release_date,
648 manual_override_flag = x_manual_override_flag
649 WHERE ROWID = x_rowid;
650
651 IF (SQL%NOTFOUND) THEN
652 RAISE NO_DATA_FOUND;
653 END IF;
654 END update_row;
655
656 PROCEDURE add_row (
657 x_rowid IN OUT NOCOPY VARCHAR2,
658 x_org_id IN NUMBER,
659 x_person_id IN NUMBER,
660 x_course_cd IN VARCHAR2,
661 x_unit_cd IN VARCHAR2,
662 x_cal_type IN VARCHAR2,
663 x_ci_sequence_number IN NUMBER,
664 x_outcome_dt IN DATE,
665 x_hist_start_dt IN DATE,
666 x_hist_end_dt IN DATE,
667 x_hist_who IN NUMBER,
668 x_grading_schema_cd IN VARCHAR2,
669 x_version_number IN NUMBER,
670 x_grade IN VARCHAR2,
671 x_s_grade_creation_method_type IN VARCHAR2,
672 x_finalised_outcome_ind IN VARCHAR2,
673 x_mark IN NUMBER,
674 x_number_times_keyed IN NUMBER,
675 x_translated_grading_schema_cd IN VARCHAR2,
676 x_translated_version_number IN NUMBER,
677 x_translated_grade IN VARCHAR2,
678 x_translated_dt IN DATE,
679 x_mode IN VARCHAR2 DEFAULT 'R',
680 x_uoo_id IN NUMBER,
681 x_mark_capped_flag IN VARCHAR2 DEFAULT 'N',
682 x_show_on_academic_histry_flag IN VARCHAR2 DEFAULT 'Y',
683 x_release_date IN DATE DEFAULT NULL,
684 x_manual_override_flag IN VARCHAR2 DEFAULT 'N'
685 ) IS
686 CURSOR c1 IS
687 SELECT ROWID
688 FROM igs_as_su_atmptout_h_all
689 WHERE person_id = x_person_id
690 AND course_cd = x_course_cd
691 AND uoo_id = x_uoo_id
692 AND outcome_dt = x_outcome_dt
693 AND hist_start_dt = x_hist_start_dt;
694 BEGIN
695 OPEN c1;
696 FETCH c1 INTO x_rowid;
697
698 IF (c1%NOTFOUND) THEN
702 x_org_id,
699 CLOSE c1;
700 insert_row (
701 x_rowid,
703 x_person_id,
704 x_course_cd,
705 x_unit_cd,
706 x_cal_type,
707 x_ci_sequence_number,
708 x_outcome_dt,
709 x_hist_start_dt,
710 x_hist_end_dt,
711 x_hist_who,
712 x_grading_schema_cd,
713 x_version_number,
714 x_grade,
715 x_s_grade_creation_method_type,
716 x_finalised_outcome_ind,
717 x_mark,
718 x_number_times_keyed,
719 x_translated_grading_schema_cd,
720 x_translated_version_number,
721 x_translated_grade,
722 x_translated_dt,
723 x_mode,
724 x_uoo_id,
725 x_mark_capped_flag,
726 x_show_on_academic_histry_flag,
727 x_release_date,
728 x_manual_override_flag
729 );
730 RETURN;
731 END IF;
732
733 CLOSE c1;
734 update_row (
735 x_rowid,
736 x_person_id,
737 x_course_cd,
738 x_unit_cd,
739 x_cal_type,
740 x_ci_sequence_number,
741 x_outcome_dt,
742 x_hist_start_dt,
743 x_hist_end_dt,
744 x_hist_who,
745 x_grading_schema_cd,
746 x_version_number,
747 x_grade,
748 x_s_grade_creation_method_type,
749 x_finalised_outcome_ind,
750 x_mark,
751 x_number_times_keyed,
752 x_translated_grading_schema_cd,
753 x_translated_version_number,
754 x_translated_grade,
755 x_translated_dt,
756 x_mode,
757 x_uoo_id,
758 x_mark_capped_flag,
759 x_show_on_academic_histry_flag,
760 x_release_date,
761 x_manual_override_flag
762 );
763 END add_row;
764
765 PROCEDURE delete_row (x_rowid IN VARCHAR2) IS
766 BEGIN
767 before_dml (p_action => 'DELETE', x_rowid => x_rowid);
768
769 DELETE FROM igs_as_su_atmptout_h_all
770 WHERE ROWID = x_rowid;
771
772 IF (SQL%NOTFOUND) THEN
773 RAISE NO_DATA_FOUND;
774 END IF;
775 END delete_row;
776
777 PROCEDURE check_constraints (column_name IN VARCHAR2 DEFAULT NULL, column_value IN VARCHAR2 DEFAULT NULL) IS
778 BEGIN
779 IF column_name IS NULL THEN
780 NULL;
781 ELSIF UPPER (column_name) = 'FINALISED_OUTCOME_IND' THEN
782 new_references.finalised_outcome_ind := column_value;
783 ELSIF UPPER (column_name) = 'CAL_TYPE' THEN
784 new_references.cal_type := column_value;
785 ELSIF UPPER (column_name) = 'COURSE_CD' THEN
786 new_references.course_cd := column_value;
787 ELSIF UPPER (column_name) = 'FINALISED_OUTCOME_IND' THEN
788 new_references.finalised_outcome_ind := column_value;
789 ELSIF UPPER (column_name) = 'GRADE' THEN
790 new_references.grade := column_value;
791 ELSIF UPPER (column_name) = 'GRADING_SCHEMA_CD' THEN
792 new_references.grading_schema_cd := column_value;
793 ELSIF UPPER (column_name) = 'S_GRADE_CREATION_METHOD_TYPE' THEN
794 new_references.s_grade_creation_method_type := column_value;
795 ELSIF UPPER (column_name) = 'TRANSLATED_GRADE' THEN
796 new_references.translated_grade := column_value;
797 ELSIF UPPER (column_name) = 'TRANSLATED_GRADING_SCHEMA_CD' THEN
798 new_references.translated_grading_schema_cd := column_value;
799 ELSIF UPPER (column_name) = 'UNIT_CD' THEN
800 new_references.unit_cd := column_value;
801 ELSIF UPPER (column_name) = 'CI_SEQUENCE_NUMBER' THEN
802 new_references.ci_sequence_number := igs_ge_number.to_num (column_value);
803 END IF;
804
805 IF UPPER (column_name) = 'FINALISED_OUTCOME_IND'
806 OR column_name IS NULL THEN
807 IF new_references.finalised_outcome_ind NOT IN ('Y', 'N') THEN
808 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
809 igs_ge_msg_stack.ADD;
810 app_exception.raise_exception;
811 END IF;
812 END IF;
813
814 IF UPPER (column_name) = 'CAL_TYPE'
818 igs_ge_msg_stack.ADD;
815 OR column_name IS NULL THEN
816 IF new_references.cal_type <> UPPER (new_references.cal_type) THEN
817 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
819 app_exception.raise_exception;
820 END IF;
821 END IF;
822
823 IF UPPER (column_name) = 'COURSE_CD'
824 OR column_name IS NULL THEN
825 IF new_references.course_cd <> UPPER (new_references.course_cd) THEN
826 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
827 igs_ge_msg_stack.ADD;
828 app_exception.raise_exception;
829 END IF;
830 END IF;
831
832 IF UPPER (column_name) = 'FINALISED_OUTCOME_IND'
833 OR column_name IS NULL THEN
834 IF new_references.finalised_outcome_ind <> UPPER (new_references.finalised_outcome_ind) THEN
835 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
836 igs_ge_msg_stack.ADD;
837 app_exception.raise_exception;
838 END IF;
839 END IF;
840
841 IF UPPER (column_name) = 'GRADE'
842 OR column_name IS NULL THEN
843 IF new_references.grade <> UPPER (new_references.grade) THEN
844 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
845 igs_ge_msg_stack.ADD;
846 app_exception.raise_exception;
847 END IF;
848 END IF;
849
850 IF UPPER (column_name) = 'GRADING_SCHEMA_CD'
851 OR column_name IS NULL THEN
852 IF new_references.grading_schema_cd <> UPPER (new_references.grading_schema_cd) THEN
853 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
854 igs_ge_msg_stack.ADD;
855 app_exception.raise_exception;
856 END IF;
857 END IF;
858
859 IF UPPER (column_name) = 'S_GRADE_CREATION_METHOD_TYPE'
860 OR column_name IS NULL THEN
861 IF new_references.s_grade_creation_method_type <> UPPER (new_references.s_grade_creation_method_type) THEN
862 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
863 igs_ge_msg_stack.ADD;
864 app_exception.raise_exception;
865 END IF;
866 END IF;
867
868 IF UPPER (column_name) = 'TRANSLATED_GRADING_SCHEMA_CD'
869 OR column_name IS NULL THEN
870 IF new_references.translated_grading_schema_cd <> UPPER (new_references.translated_grading_schema_cd) THEN
871 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
872 igs_ge_msg_stack.ADD;
873 app_exception.raise_exception;
874 END IF;
875 END IF;
876
877 IF UPPER (column_name) = 'UNIT_CD'
878 OR column_name IS NULL THEN
879 IF new_references.unit_cd <> UPPER (new_references.unit_cd) THEN
880 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
881 igs_ge_msg_stack.ADD;
882 app_exception.raise_exception;
883 END IF;
884 END IF;
885
886 IF UPPER (column_name) = 'TRANSLATED_GRADE'
887 OR column_name IS NULL THEN
888 IF new_references.translated_grade <> UPPER (new_references.translated_grade) THEN
889 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
890 igs_ge_msg_stack.ADD;
891 app_exception.raise_exception;
892 END IF;
893 END IF;
894
895 IF UPPER (column_name) = 'CI_SEQUENCE_NUMBER'
896 OR column_name IS NULL THEN
897 IF new_references.ci_sequence_number < 1
898 OR new_references.ci_sequence_number > 99999 THEN
899 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
900 igs_ge_msg_stack.ADD;
901 app_exception.raise_exception;
902 END IF;
903 END IF;
904 END check_constraints;
905 END igs_as_su_atmptout_h_pkg;