[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_INCOMP_GRADE
Source
1 PACKAGE BODY IGS_AS_INCOMP_GRADE AS
2 /* $Header: IGSAS41B.pls 120.2 2006/01/18 22:56:28 swaghmar ship $ */
3
4 PROCEDURE incomp_grade_process(
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER )
7 IS
8
9 BEGIN -- incomp_grade_process
10 -- Process to convert the incomplete grades recorded to the default grades
11 -- entered once the deadline date is passed and the student has not completed.
12 -- the incomplete grade.
13 -- This process is run every day by the system automatically
14
15 DECLARE
16
17 l_wf_event_t WF_EVENT_T;
18 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
19 l_key NUMBER;
20 l_internal_name VARCHAR2(100);
21 l_sysdate DATE;
22 x_rowid VARCHAR2(25);
23
24 CURSOR c_suao IS
25 SELECT suao.person_id,
26 suao.course_cd,
27 suao.unit_cd,
28 suao.cal_type,
29 suao.ci_sequence_number,
30 -- anilk, 22-Apr-2003, Bug# 2829262
31 suao.uoo_id,
32 suao.ci_start_dt,
33 suao.ci_end_dt,
34 suao.grade,
35 suao.incomp_grading_schema_cd,
36 suao.incomp_version_number,
37 suao.incomp_default_grade,
38 suao.incomp_default_mark,
39 uv.title,
40 uv.short_title,
41 ci.description
42 FROM igs_as_suaoa_v suao,
43 igs_en_su_attempt sua,
44 igs_ps_unit_ver uv,
45 igs_ca_inst ci
46 WHERE suao.person_id = sua.person_id
47 AND suao.course_cd = sua.course_cd
48 -- anilk, 22-Apr-2003, Bug# 2829262
49 AND suao.uoo_id = sua.uoo_id
50 AND sua.unit_cd = uv.unit_cd
51 AND sua.version_number = uv.version_number
52 AND sua.cal_type = ci.cal_type
53 AND sua.ci_sequence_number = ci.sequence_number
54 AND TRUNC(suao.incomp_deadline_date) <= TRUNC(SYSDATE)
55 AND suao.finalised_outcome_ind = 'Y'
56 AND suao.grading_period_cd = 'FINAL';
57
58 BEGIN
59
60
61 retcode := 0;
62 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
63 SAVEPOINT s_before_insert;
64
65 FOR v_suao_rec IN c_suao LOOP
66
67 l_sysdate := SYSDATE;
68
69 -- Call table handler to insert new Student Unit Attempt Outcome
70 IGS_AS_SU_STMPTOUT_PKG.INSERT_ROW(
71 X_ROWID => x_rowid,
72 X_ORG_ID => NULL,
73 X_PERSON_ID => v_suao_rec.person_id,
74 X_COURSE_CD => v_suao_rec.course_cd,
75 X_UNIT_CD => v_suao_rec.unit_cd,
76 X_CAL_TYPE => v_suao_rec.cal_type,
77 X_CI_SEQUENCE_NUMBER => v_suao_rec.ci_sequence_number,
78 X_OUTCOME_DT => l_sysdate,
79 X_CI_START_DT => v_suao_rec.ci_start_dt,
80 X_CI_END_DT => v_suao_rec.ci_end_dt,
81 X_GRADING_SCHEMA_CD => v_suao_rec.incomp_grading_schema_cd,
82 X_VERSION_NUMBER => v_suao_rec.incomp_version_number,
83 X_GRADE => v_suao_rec.incomp_default_grade,
84 X_S_GRADE_CREATION_METHOD_TYPE => 'SYSTEM',
85 X_FINALISED_OUTCOME_IND => 'N',
86 X_MARK => v_suao_rec.incomp_default_mark,
87 X_NUMBER_TIMES_KEYED => NULL,
88 X_TRANSLATED_GRADING_SCHEMA_CD => NULL,
89 X_TRANSLATED_VERSION_NUMBER => NULL,
90 X_TRANSLATED_GRADE => NULL,
91 X_TRANSLATED_DT => NULL,
92 X_MODE => 'R',
93 X_GRADING_PERIOD_CD => 'FINAL',
94 X_ATTRIBUTE_CATEGORY => NULL,
95 X_ATTRIBUTE1 => NULL,
96 X_ATTRIBUTE2 => NULL,
97 X_ATTRIBUTE3 => NULL,
98 X_ATTRIBUTE4 => NULL,
99 X_ATTRIBUTE5 => NULL,
100 X_ATTRIBUTE6 => NULL,
101 X_ATTRIBUTE7 => NULL,
102 X_ATTRIBUTE8 => NULL,
103 X_ATTRIBUTE9 => NULL,
104 X_ATTRIBUTE10 => NULL,
105 X_ATTRIBUTE11 => NULL,
106 X_ATTRIBUTE12 => NULL,
107 X_ATTRIBUTE13 => NULL,
108 X_ATTRIBUTE14 => NULL,
109 X_ATTRIBUTE15 => NULL,
110 X_ATTRIBUTE16 => NULL,
111 X_ATTRIBUTE17 => NULL,
112 X_ATTRIBUTE18 => NULL,
113 X_ATTRIBUTE19 => NULL,
114 X_ATTRIBUTE20 => NULL,
115 X_INCOMP_DEADLINE_DATE => NULL,
116 X_INCOMP_GRADING_SCHEMA_CD => NULL,
117 X_INCOMP_VERSION_NUMBER => NULL,
118 X_INCOMP_DEFAULT_GRADE => NULL,
119 X_INCOMP_DEFAULT_MARK => NULL,
120 X_COMMENTS => NULL,
121 -- anilk, 22-Apr-2003, Bug# 2829262
122 X_UOO_ID => v_suao_rec.uoo_id,
123 x_mark_capped_flag => 'N',
124 x_show_on_academic_histry_flag => 'Y',
125 x_release_date => NULL,
126 x_manual_override_flag => 'N'
127 );
128
129 -- Call the finalization process for this record
130 --ijeddy, 25-Aug-2005 bug fix for bug 4371745. pass uoo_id instead of NULL
131 IGS_AS_FINALIZE_GRADE.finalize_process( v_suao_rec.uoo_id,
132 v_suao_rec.person_id,
133 v_suao_rec.course_cd,
134 v_suao_rec.unit_cd,
135 v_suao_rec.cal_type,
136 v_suao_rec.ci_sequence_number );
137
138
139 -- Raise business event for Incomplete Grade to be used for Notification.
140 IGS_AS_GRD_ATT_BE_PKG.wf_inform_admin_incgrd( v_suao_rec.person_id,
141 v_suao_rec.course_cd,
142 v_suao_rec.unit_cd,
143 v_suao_rec.cal_type,
144 v_suao_rec.ci_sequence_number,
145 l_sysdate,
146 v_suao_rec.grade,
147 v_suao_rec.incomp_default_grade);
148
149
150 END LOOP
151
152 COMMIT;
153
154 EXCEPTION
155 WHEN OTHERS THEN
156 ROLLBACK TO s_before_insert;
157
158 FND_MESSAGE.SET_TOKEN('NAME','IGS_AS_INCOMP_GRADE.incomp_grade_process');
159 IGS_GE_MSG_STACK.ADD;
160 retcode := 2;
161 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXP');
162 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
163 END;
164 END incomp_grade_process;
165
166 END IGS_AS_INCOMP_GRADE ;