[Home] [Help]
PACKAGE BODY: APPS.IGS_SS_FACULTY_PKG
Source
1 PACKAGE BODY IGS_SS_FACULTY_PKG AS
2 /* $Header: IGSSS08B.pls 115.7 2003/12/03 10:25:29 ijeddy ship $ */
3 FUNCTION assp_ins_get_by_uoo(
4 p_keying_who IN VARCHAR2 ,
5 p_cal_type IN VARCHAR2 ,
6 p_sequence_number IN NUMBER ,
7 p_unit_cd IN VARCHAR2 ,
8 p_location_cd IN VARCHAR2 ,
9 p_unit_class IN VARCHAR2 ,
10 p_include_discont_ind IN VARCHAR2 ,
11 p_sort_by IN VARCHAR2 ,
12 p_keying_time OUT NOCOPY DATE,
13 p_return_status OUT NOCOPY VARCHAR2,
14 p_msg_data OUT NOCOPY VARCHAR2,
15 p_msg_count OUT NOCOPY NUMBER
16
17 ) RETURN VARCHAR2 IS
18 BEGIN
19
20 IF IGS_AS_GEN_004.assp_ins_get(
21 p_keying_who,
22 NULL,
23 p_cal_type,
24 p_sequence_number,
25 p_unit_cd,
26 p_location_cd,
27 NULL,
28 p_unit_class,
29 p_include_discont_ind,
30 p_sort_by,
31 p_keying_time ) THEN
32 return 'TRUE';
33 ELSE
34 p_msg_data :=sqlerrm;
35 p_return_status := fnd_api.g_ret_sts_error;
36 return 'FALSE';
37 END IF;
38
39 END assp_ins_get_by_uoo;
40
41
42
43 PROCEDURE update_suao(
44 p_person_id IN NUMBER,
45 p_cal_type IN VARCHAR2,
46 p_ci_sequence_number IN NUMBER,
47 p_unit_cd IN VARCHAR2,
48 p_course_cd IN VARCHAR2,
49 p_mark IN NUMBER,
50 p_grade IN VARCHAR2,
51 p_grading_schema_cd IN VARCHAR2,
52 p_gs_version_number IN NUMBER,
53 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE
54 ) IS
55 /*
56 | Who When What
57 | knaraset 09-May-03 modified this procedure to add parameter uoo_id which is used in cursors c_suao_chkand c_IGS_AS_SU_STMPTOUT,
58 | also passed the uoo_id in TBH calls of IGS_AS_SU_STMPTOUT_PKG, as part of MUS build bug 2829262
59 |
60 |
61 */
62 CURSOR c_suao_chk (cp_person_id IGS_AS_SU_STMPTOUT.person_id%TYPE,
63 cp_course_cd IGS_AS_SU_STMPTOUT.course_cd%TYPE,
64 cp_uoo_id IGS_AS_SU_STMPTOUT.uoo_id%TYPE) IS
65 SELECT finalised_outcome_ind,
66 mark,
67 grade,
68 grading_schema_cd,
69 version_number,
70 outcome_dt,
71 number_times_keyed
72 FROM IGS_AS_SU_STMPTOUT
73 WHERE person_id = cp_person_id
74 AND course_cd = cp_course_cd
75 AND uoo_id = cp_uoo_id
76 ORDER BY outcome_dt ASC;
77
78 CURSOR c_ci (cp_cal_type IGS_CA_INST.cal_type%TYPE,
79 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
80 SELECT start_dt,
81 end_dt
82 FROM IGS_CA_INST
83 WHERE cal_type = cp_cal_type
84 AND sequence_number = cp_sequence_number;
85
86
87 CURSOR c_IGS_AS_SU_STMPTOUT (cp_person_id IGS_AS_SU_STMPTOUT.person_id%TYPE,
88 cp_course_cd IGS_AS_SU_STMPTOUT.course_cd%TYPE,
89 cp_uoo_id IGS_AS_SU_STMPTOUT.uoo_id%TYPE,
90 cp_outcome_dt IGS_AS_SU_STMPTOUT.outcome_dt%TYPE) IS
91 SELECT ROWID,
92 IGS_AS_SU_STMPTOUT.*
93 FROM IGS_AS_SU_STMPTOUT
94 WHERE person_id = cp_person_id
95 AND course_cd = cp_course_cd
96 AND uoo_id = cp_uoo_id
97 AND outcome_dt = cp_outcome_dt;
98
99
100 v_ci_rec c_ci%ROWTYPE;
101 v_suao1_rec c_suao_chk%ROWTYPE;
102 v_stmptout_rec c_IGS_AS_SU_STMPTOUT%ROWTYPE;
103 v_records_found BOOLEAN;
104 l_rowid VARCHAR2(25);
105 lv_rowid VARCHAR2(25);
106
107 BEGIN
108
109 FOR v_suao_rec IN c_suao_chk( p_person_id,
110 p_course_cd,
111 p_uoo_id) LOOP
112 v_records_found := TRUE;
113 v_suao1_rec := v_suao_rec;
114 END LOOP;
115
116 IF v_records_found AND v_suao1_rec.finalised_outcome_ind = 'N' THEN
117
118 OPEN c_IGS_AS_SU_STMPTOUT(
119 p_person_id,
120 p_course_cd,
121 p_uoo_id,
122 v_suao1_rec.outcome_dt);
123
124 FETCH c_IGS_AS_SU_STMPTOUT INTO v_stmptout_rec;
125
126 IGS_AS_SU_STMPTOUT_PKG.UPDATE_ROW (
127 X_ROWID => v_stmptout_rec.rowid ,
128 X_MODE => 'R',
129 x_person_id => v_stmptout_rec.person_id,
130 x_course_cd => v_stmptout_rec.course_cd,
131 x_unit_cd => v_stmptout_rec.unit_cd,
132 x_cal_type => v_stmptout_rec.cal_type,
133 x_ci_sequence_number => v_stmptout_rec.ci_sequence_number,
134 x_outcome_dt => v_stmptout_rec.OUTCOME_DT,
135 x_ci_start_dt => v_stmptout_rec.CI_START_DT,
136 x_ci_end_dt => v_stmptout_rec.CI_END_DT,
137 X_GRADING_SCHEMA_CD => p_grading_schema_cd,
138 X_VERSION_NUMBER => p_gs_version_number,
139 X_GRADE => p_grade,
140 x_s_grade_creation_method_type => v_stmptout_rec.s_grade_creation_method_type,
141 x_finalised_outcome_ind => v_stmptout_rec.finalised_outcome_ind,
142 x_mark => p_mark,
143 x_number_times_keyed => NVL(v_suao1_rec.number_times_keyed,0)+1,
144 x_translated_grading_schema_cd => v_stmptout_rec.translated_grading_schema_cd,
145 x_translated_version_number => v_stmptout_rec.translated_version_number,
146 x_translated_grade => v_stmptout_rec.translated_grade,
147 x_translated_dt => v_stmptout_rec.translated_dt ,
148 X_ATTRIBUTE_CATEGORY => v_stmptout_rec.attribute_category,
149 X_ATTRIBUTE1 => v_stmptout_rec.attribute1,
150 X_ATTRIBUTE2 => v_stmptout_rec.attribute2,
151 X_ATTRIBUTE3 => v_stmptout_rec.attribute3,
152 X_ATTRIBUTE4 => v_stmptout_rec.attribute4,
153 X_ATTRIBUTE5 => v_stmptout_rec.attribute5,
154 X_ATTRIBUTE6 => v_stmptout_rec.attribute6,
155 X_ATTRIBUTE7 => v_stmptout_rec.attribute7,
156 X_ATTRIBUTE8 => v_stmptout_rec.attribute8,
157 X_ATTRIBUTE9 => v_stmptout_rec.attribute9,
158 X_ATTRIBUTE10 => v_stmptout_rec.attribute10,
159 X_ATTRIBUTE11 => v_stmptout_rec.attribute11,
160 X_ATTRIBUTE12 => v_stmptout_rec.attribute12,
161 X_ATTRIBUTE13 => v_stmptout_rec.attribute13,
162 X_ATTRIBUTE14 => v_stmptout_rec.attribute14,
163 X_ATTRIBUTE15 => v_stmptout_rec.attribute15,
164 X_ATTRIBUTE16 => v_stmptout_rec.attribute16,
165 X_ATTRIBUTE17 => v_stmptout_rec.attribute17,
166 X_ATTRIBUTE18 => v_stmptout_rec.attribute18,
167 X_ATTRIBUTE19 => v_stmptout_rec.attribute19,
168 X_ATTRIBUTE20 => v_stmptout_rec.attribute20,
169 X_UOO_ID => v_stmptout_rec.uoo_id,
170 x_mark_capped_flag => v_stmptout_rec.mark_capped_flag,
171 x_show_on_academic_histry_flag => v_stmptout_rec.show_on_academic_histry_flag,
172 x_release_date => v_stmptout_rec.release_date,
173 x_manual_override_flag => v_stmptout_rec.manual_override_flag,
174 x_incomp_deadline_date => v_stmptout_rec.incomp_deadline_date,
175 x_incomp_grading_schema_cd => v_stmptout_rec.incomp_grading_schema_cd,
176 x_incomp_version_number => v_stmptout_rec.incomp_version_number,
177 x_incomp_default_grade => v_stmptout_rec.incomp_default_grade,
178 x_incomp_default_mark => v_stmptout_rec.incomp_default_mark,
179 x_comments => v_stmptout_rec.comments,
180 x_grading_period_cd => v_stmptout_rec.grading_period_cd
181 );
182
183 CLOSE c_IGS_AS_SU_STMPTOUT;
184
185 ELSE
186 -- get the start/end dates from the calendar instance.
187 OPEN c_ci(p_cal_type, p_ci_sequence_number);
188 FETCH c_ci INTO v_ci_rec;
189 CLOSE c_ci;
190
191 -- Add a new IGS_AS_SU_STMPTOUT record.
192 IGS_AS_SU_STMPTOUT_pkg.INSERT_ROW(
193 x_rowid => l_rowid,
194 x_mode => 'R',
195 x_person_id => p_person_id,
196 x_course_cd => p_course_cd,
197 x_unit_cd => p_unit_cd,
198 x_cal_type => p_cal_type,
199 x_ci_sequence_number => p_ci_sequence_number,
200 x_ci_start_dt => v_ci_rec.start_dt,
201 x_ci_end_dt => v_ci_rec.end_dt,
202 x_outcome_dt => SYSDATE,
203 x_s_grade_creation_method_type => 'KEYED',
204 x_number_times_keyed => 1,
205 x_finalised_outcome_ind => 'N',
206 x_mark => p_mark,
207 x_grade => p_grade,
208 x_grading_schema_cd => p_grading_schema_cd,
209 x_version_number => p_gs_version_number,
210 x_translated_grading_schema_cd => null,
211 x_translated_version_number => null,
212 x_translated_grade => null,
213 x_translated_dt => null,
214 X_ORG_ID => TO_NUMBER(FND_PROFILE.VALUE('ORG_ID')) ,
215 X_ATTRIBUTE_CATEGORY => NULL,
216 X_ATTRIBUTE1 => NULL,
217 X_ATTRIBUTE2 => NULL,
218 X_ATTRIBUTE3 => NULL,
219 X_ATTRIBUTE4 => NULL,
220 X_ATTRIBUTE5 => NULL,
221 X_ATTRIBUTE6 => NULL,
222 X_ATTRIBUTE7 => NULL,
223 X_ATTRIBUTE8 => NULL,
224 X_ATTRIBUTE9 => NULL,
225 X_ATTRIBUTE10 => NULL,
226 X_ATTRIBUTE11 => NULL,
227 X_ATTRIBUTE12 => NULL,
228 X_ATTRIBUTE13 => NULL,
229 X_ATTRIBUTE14 => NULL,
230 X_ATTRIBUTE15 => NULL,
231 X_ATTRIBUTE16 => NULL,
232 X_ATTRIBUTE17 => NULL,
233 X_ATTRIBUTE18 => NULL,
234 X_ATTRIBUTE19 => NULL,
235 X_ATTRIBUTE20 => NULL,
236 X_UOO_ID => p_uoo_id,
237 x_mark_capped_flag => 'N',
238 x_show_on_academic_histry_flag => 'Y',
239 x_release_date => NULL,
240 x_manual_override_flag => 'N',
241 x_incomp_deadline_date => NULL,
242 x_incomp_grading_schema_cd => NULL,
243 x_incomp_version_number => NULL,
244 x_incomp_default_grade => NULL,
245 x_incomp_default_mark => NULL,
246 x_comments => NULL,
247 x_grading_period_cd => 'FINAL'
248 );
249 END IF;
250 END update_suao;
251
252 END IGS_SS_FACULTY_PKG;