1 PACKAGE BODY igs_av_val_asuleb AS
2 /* $Header: IGSAV07B.pls 115.7 2003/12/10 07:40:42 nalkumar ship $ */
3
4 G_ITEM_TYPE VARCHAR2(300);
5 -- To validate the basis year advanced standing units or levels.
6 FUNCTION advp_val_basis_year(
7 p_basis_year IN NUMBER ,
8 p_course_cd IN VARCHAR2 ,
9 p_version_number IN NUMBER ,
10 p_message_name OUT NOCOPY VARCHAR2,
11 p_return_type OUT NOCOPY VARCHAR2 )
12 RETURN BOOLEAN IS
13 gv_other_detail VARCHAR2(255);
14 BEGIN -- advp_val_basis_year
15 -- validate the basis year
16 DECLARE
17 v_qualification_recency IGS_PS_VER.qualification_recency%TYPE;
18 CURSOR c_qualification_recency IS
19 SELECT qualification_recency
20 FROM IGS_PS_VER
21 WHERE course_cd = p_course_cd AND
22 version_number = p_version_number;
23 BEGIN
24 p_message_name := null;
25 -- Validate input parameter
26 IF (p_basis_year IS NULL OR
27 p_course_cd IS NULL OR
28 p_version_number IS NULL) THEN
29 RETURN TRUE;
30 END IF;
31 -- Validate that basis_year is not greater than the current year.(E)
32 IF (p_basis_year > TO_NUMBER(SUBSTR(IGS_GE_DATE.IGSCHAR(SYSDATE),1,4))) THEN
33 p_message_name := 'IGS_AV_LYENR_NOTGT_CURYR';
34 p_return_type := 'E';
35 RETURN FALSE;
36 END IF;
37 -- Validate that basis_yr is not outside the recency for the IGS_PS_COURSE version (W)
38 OPEN c_qualification_recency;
39 FETCH c_qualification_recency INTO v_qualification_recency;
40 IF (c_qualification_recency%NOTFOUND) THEN
41 CLOSE c_qualification_recency;
42 RAISE NO_DATA_FOUND;
43 END IF;
44 CLOSE c_qualification_recency;
45 IF (p_basis_year <
46 TO_NUMBER(SUBSTR(IGS_GE_DATE.IGSCHAR(SYSDATE),1,4)) - v_qualification_recency) THEN
47 p_message_name := 'IGS_AV_LRENR_OUTSIDE_QUALIFY';
48 p_return_type := 'W';
49 RETURN FALSE;
50 END IF;
51 RETURN TRUE;
52 EXCEPTION
53 WHEN OTHERS THEN
54 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
55 Fnd_Message.Set_Token('NAME','IGS_AV_VAL_ASULEB.ADVP_VAL_BASIS_YEAR');
56 App_Exception.Raise_Exception;
57 IGS_GE_MSG_STACK.ADD;
58 END;
59 END advp_val_basis_year;
60
61 PROCEDURE create_transcript
62 (
63 document_id IN VARCHAR2,
64 display_type IN VARCHAR2,
65 document IN OUT NOCOPY CLOB,
66 document_type IN OUT NOCOPY VARCHAR2
67 ) IS
68 /*
69 ||==============================================================================||
70 || Created By : Nalin Kumar ||
71 || Created On : 15-Nov-2003 ||
72 || Purpose : To set the value of the Attributes attached to the messages. ||
73 || Added as part of RECR50; Bug# 3270446 ||
74 || ||
75 || Known limitations, enhancements or remarks : ||
76 || Change History : ||
77 || Who When What ||
78 || (reverse chronological order - newest change first) ||
79 ||==============================================================================||
80 */
81 l_item_type VARCHAR2(300);
82 l_item_key VARCHAR2(300);
83 l_item VARCHAR2(32000);
84 l_message VARCHAR2(32000);
85 BEGIN
86 IF document_id IS NOT NULL THEN
87 --Fetch the item Type, Item Key and Item Name from the passed Document ID.
88 l_item_type := substr(document_id, 1, instr(document_id,':')-1);
89 l_item_key := substr (document_id, INSTR(document_id, ':') +1, (INSTR(document_id, '*') - INSTR(document_id, ':'))-1) ;
90 l_item := substr(document_id, instr(document_id,'*')+1);
91 l_message := NULL;
92 IF l_item IS NOT NULL THEN
93 --
94 -- If the Item Name is not null then get the value of the item from the WF
95 -- and return it to the message in a document (CLOB) format.
96 --
97 l_message := wf_engine.GetItemAttrText( itemtype => l_item_type,
98 itemkey => l_item_key,
99 aname => l_item);
100 END IF;
101 END IF;
102 /* Write the header doc into CLOB variable */
103 WF_NOTIFICATION.WriteToClob(document, l_message);
104 EXCEPTION
105 WHEN OTHERS THEN
106 wf_core.context('igs_av_val_asuleb','create_transcript',l_item_type,l_item_key);
107 RAISE;
108 END create_transcript;
109
110 PROCEDURE wf_set_role(
111 itemtype IN VARCHAR2,
112 itemkey IN VARCHAR2,
113 actid IN NUMBER ,
114 funcmode IN VARCHAR2,
115 resultout OUT NOCOPY VARCHAR2) AS
116 /*
117 ||==============================================================================||
118 || Created By : Nalin Kumar ||
119 || Created On : 15-Nov-2003 ||
120 || Purpose : To set the role and decide that which all notification needs ||
121 || to be sent. Added as part of RECR50; Bug# 3270446 ||
122 || ||
123 || Known limitations, enhancements or remarks : ||
124 || Change History : ||
125 || Who When What ||
126 || (reverse chronological order - newest change first) ||
127 ||==============================================================================||
128 */
129 l_notification_flag VARCHAR2(30) := NULL;
130 l_del_basis_dtls_body VARCHAR2(4000);
131 l_mod_basis_dtls_body VARCHAR2(4000);
132 l_new_basis_dtls_body VARCHAR2(4000);
133 l_basis_header VARCHAR2(32000);
134 l_msg_document_plsql_proc VARCHAR2(32000);
135
136 BEGIN
137 IF (funcmode = 'RUN') THEN
138
139 --
140 -- Validate the Advanced Standing Records and set the Parameters Values
141 --
142 get_transcript_data(
143 p_itemtype => itemtype,
144 p_itemkey => itemkey ,
145 p_person_id => wf_engine.getitemattrtext(itemtype,itemkey,'P_STUDENT_ID'),
146 p_education_id => wf_engine.getitemattrtext(itemtype,itemkey,'P_REC_EDUCATION_ID'),
147 p_transcript_id => wf_engine.getitemattrtext(itemtype,itemkey,'P_REC_TRANSCRIPT_ID'));
148
149 wf_engine.setitemattrtext(ItemType => itemtype,
150 ItemKey => itemkey,
151 aname => 'IA_ADHOCROLE',
152 avalue => wf_engine.getitemattrtext(itemtype,itemkey,'IA_REC_ADHOCROLE'));
153
154 l_del_basis_dtls_body := wf_engine.getitemattrtext(itemtype,itemkey,'P_REC_DEL_UDTL');
155 l_mod_basis_dtls_body := wf_engine.getitemattrtext(itemtype,itemkey,'P_REC_MOD_UDTL');
156 l_new_basis_dtls_body := wf_engine.getitemattrtext(itemtype,itemkey,'P_REC_NEW_BDTL');
157
158 -- Based on the value of l_del_basis_dtls_body, l_mod_basis_dtls_body and l_new_basis_dtls_body
159 -- set the lookup code value.
160 IF l_del_basis_dtls_body IS NOT NULL THEN
161 l_notification_flag := 'D';
162 END IF;
163 IF l_mod_basis_dtls_body IS NOT NULL THEN
164 l_notification_flag := l_notification_flag||'M';
165 END IF;
166 IF l_new_basis_dtls_body IS NOT NULL THEN
167 l_notification_flag := l_notification_flag||'N';
168 END IF;
169
170 --
171 --Based on the value of the Notification Flag set the value of the Message Attributes.
172 --
173 IF NVL(l_notification_flag, 'Z') IN ('D', 'DM', 'DN', 'DMN') THEN
174 wf_engine.setitemattrtext(ItemType => itemtype,
175 ItemKey => itemkey,
176 aname => 'P_DEL_UDTL',
177 avalue => 'PLSQLCLOB:igs_av_val_asuleb.create_transcript/'||itemtype||':'||itemkey||'*P_REC_DEL_UDTL');
178 wf_engine.setitemattrtext(ItemType => itemtype,
179 ItemKey => itemkey,
180 aname => 'P_DEL_BDTL',
181 avalue => 'PLSQLCLOB:igs_av_val_asuleb.create_transcript/'||itemtype||':'||itemkey||'*P_REC_DEL_BDTL');
182 END IF;
183 IF NVL(l_notification_flag, 'Z') IN ('DM', 'MN', 'M', 'DMN') THEN
184 wf_engine.setitemattrtext(ItemType => itemtype,
185 ItemKey => itemkey,
186 aname => 'P_MOD_UDTL',
187 avalue => 'PLSQLCLOB:igs_av_val_asuleb.create_transcript/'||itemtype||':'||itemkey||'*P_REC_MOD_UDTL');
188 wf_engine.setitemattrtext(ItemType => itemtype,
189 ItemKey => itemkey,
190 aname => 'P_MOD_BDTL',
191 avalue => 'PLSQLCLOB:igs_av_val_asuleb.create_transcript/'||itemtype||':'||itemkey||'*P_REC_MOD_BDTL');
192 END IF;
193 IF NVL(l_notification_flag, 'Z') IN ('DMN', 'DN', 'MN', 'N') THEN
194 wf_engine.setitemattrtext(ItemType => itemtype,
195 ItemKey => itemkey,
196 aname => 'P_NEW_BDTL',
197 avalue => 'PLSQLCLOB:igs_av_val_asuleb.create_transcript/'||itemtype||':'||itemkey||'*P_REC_NEW_BDTL');
198
199 END IF;
200
201 --
202 --Return the l_notification_flag which will indicate that which all notifications need to be sent.
203 --
204 resultout := 'COMPLETE:'||l_notification_flag;
205 RETURN;
206 END IF;
207 END wf_set_role;
208
209 PROCEDURE validate_transcript(
210 p_person_id IN NUMBER,
211 p_education_id IN NUMBER,
212 p_transcript_id IN NUMBER) IS
213 /*
214 ||==============================================================================||
215 || Created By : Nalin Kumar ||
216 || Created On : 15-Nov-2003 ||
217 || Purpose : To launch the IGSAV001 workflow and set the attributes values. ||
218 || Added as part of RECR50; Bug# 3270446 ||
219 || ||
220 || Known limitations, enhancements or remarks : ||
221 || Change History : ||
222 || Who When What ||
223 || (reverse chronological order - newest change first) ||
224 ||==============================================================================||
225 */
226 l_event_t wf_event_t;
227 l_raise_event VARCHAR2(50);
228 l_seq_val VARCHAR2(100) := 'IGSAV001'||to_char(SYSDATE,'YYYYMMDDHH24MISS');
229 l_parameter_list_t wf_parameter_list_t;
230 BEGIN
231
232 l_raise_event := 'oracle.apps.igs.av.validate_transcript';
233 --
234 -- initialize the wf_event_t object
235 --
236 wf_event_t.initialize(l_event_t);
237
238 --
239 -- Adding the parameters to the parameter list
240 --
241 wf_event.addparametertolist( p_name => 'P_STUDENT_ID',
242 p_value => p_person_id ,
243 p_parameterlist => l_parameter_list_t);
244
245 wf_event.addparametertolist( p_name => 'P_REC_EDUCATION_ID',
246 p_value => p_education_id,
247 p_parameterlist => l_parameter_list_t);
248 wf_event.addparametertolist( p_name => 'P_REC_TRANSCRIPT_ID',
249 p_value => p_transcript_id,
250 p_parameterlist => l_parameter_list_t);
251
252 -- Set this role to the workflow
253 wf_event.addparametertolist( p_name => 'IA_REC_ADHOCROLE',
254 p_value => fnd_global.user_name,
255 p_parameterlist => l_parameter_list_t);
256
257 G_ITEM_TYPE := l_seq_val;
258
259 --Raise the event...
260 wf_event.raise (p_event_name => l_raise_event,
261 p_event_key => l_seq_val,
262 p_parameters => l_parameter_list_t);
263
264 --
265 -- Deleting the Parameter list after the event is raised
266 --
267 l_parameter_list_t.delete;
268 EXCEPTION
269 WHEN OTHERS THEN
270 wf_core.context('IGS_AV_VAL_ASULEB', 'VALIDATE_TRANSCRIPT',
271 l_seq_val,l_raise_event);
272 RAISE;
273 END validate_transcript;
274
275 --
276 -- To validate the Advanced Standing records when a new Transcript is submitted.
277 --
278 PROCEDURE get_transcript_data(
279 p_itemtype IN VARCHAR2,
280 p_itemkey IN VARCHAR2,
281 p_person_id IN NUMBER,
282 p_education_id IN NUMBER,
283 p_transcript_id IN NUMBER) IS
284 /*
285 ||==============================================================================||
286 || Created By : Nalin Kumar ||
287 || Created On : 15-Nov-2003 ||
288 || Purpose : Created this procedure as per RECR050 Build. Bug# 3270446 ||
289 || This is to validate the Advanced Standing records when a new ||
290 || Transcript is submitted. ||
291 || Known limitations, enhancements or remarks : ||
292 || Change History : ||
293 || Who When What ||
294 || (reverse chronological order - newest change first) ||
295 ||==============================================================================||
296 */
297 --
298 CURSOR cur_get_unit_details_id(cp_unit_details_id NUMBER, cp_unit VARCHAR2, cp_term_details_id NUMBER) IS
299 SELECT unit, term_details_id, unit_details_id, cp_attempted, cp_earned, grade, unit_grade_points
300 FROM igs_ad_term_unitdtls
301 WHERE unit_details_id = NVL(cp_unit_details_id, unit_details_id)
302 AND unit = NVL(cp_unit, unit)
303 AND term_details_id = NVL(cp_term_details_id, term_details_id);
304 rec_get_old_unit_details cur_get_unit_details_id%ROWTYPE;
305 rec_get_new_unit_details cur_get_unit_details_id%ROWTYPE;
306
307 --Cursor to fetch all terms attached to the Transcript.
308 CURSOR cur_get_term_dtls(cp_transcript_id NUMBER, cp_term_details_id NUMBER)IS
309 SELECT term_details_id, transcript_id
310 FROM igs_ad_term_details
311 WHERE transcript_id = NVL(cp_transcript_id, transcript_id) AND
312 term_details_id = NVL(cp_term_details_id, term_details_id);
313 rec_get_old_term_dtls cur_get_term_dtls%ROWTYPE;
314
315 --Cursor to get the Advanced Standing records for Student.
316 CURSOR cur_chk_adv(cp_person_id NUMBER, cp_exemption_institution_cd VARCHAR2) IS
317 SELECT DISTINCT rslt.unit_details_id FROM (
318 SELECT DISTINCT unit_details_id
319 FROM igs_av_stnd_unit_all
320 WHERE person_id = cp_person_id
321 AND exemption_institution_cd = cp_exemption_institution_cd
322 AND unit_details_id IS NOT NULL
323 UNION ALL
324 SELECT DISTINCT unit_details_id
325 FROM igs_av_stnd_unit_lvl_all
326 WHERE person_id = cp_person_id
327 AND exemption_institution_cd = cp_exemption_institution_cd
328 AND unit_details_id IS NOT NULL) rslt;
329 rec_chk_adv cur_chk_adv%ROWTYPE;
330
331 nbsp VARCHAR2(10) := fnd_global.local_chr(38) || 'nbsp;';
332 --Cursor to find if there is any new Unit attached to the New Transcript which was not attached to the old transcript.
333 CURSOR cur_chk_new_tran(cp_new_tid NUMBER, cp_old_transcript_id NUMBER) IS
334 SELECT '<td align="center">'||(RPAD(NVL(nt.unit, nbsp), 10))||'</td>' new_unit,
335 '<td align="center">'||(RPAD(NVL(td.term, nbsp), 30))||'</td>' term_completed,
336 '<td align="center">'||(LPAD(NVL(TO_CHAR(nt.cp_earned), nbsp), 7))||'</td>' cp_earned,
337 '<td align="center">'||(RPAD(NVL(nt.grade, nbsp),10))||'</td>' grade
338 FROM igs_ad_term_unitdtls nt, igs_ad_term_details td
339 WHERE nt.term_details_id = cp_new_tid
340 AND td.term_details_id = nt.term_details_id
341 AND NOT EXISTS
342 (SELECT 'x'
343 FROM igs_ad_term_unitdtls ot
344 WHERE ot.term_details_id IN (select term_details_id FROM igs_ad_term_details WHERE transcript_id = cp_old_transcript_id)
345 AND ot.unit = nt.unit);
346
347 --Cursor to fetch the Advanced Standing details which has to be Deleted/Updated.
348 CURSOR cur_del_adv_dtls (cp_person_id NUMBER,
349 cp_unit_details_id NUMBER )IS
350 SELECT '<td align="center">'||(RPAD(NVL(av.cal_type, nbsp), 12))||'</td>' cal_type,
351 '<td align="center">'||(RPAD(NVL(av.unit_cd, nbsp), 15))||'</td>' unit,
352 '<td align="center">'||(RPAD(NVL(av.s_adv_stnd_recognition_type, nbsp), 30))||'</td>' ece_type,
353 '<td align="center">'||(LPAD(NVL(TO_CHAR(av.achievable_credit_points), nbsp), 9))||'</td>' credit_points,
354 '<td align="center">'||(RPAD(NVL(av.s_adv_stnd_granting_status, nbsp), 30))||'</td>' adv_status,
355 '<td align="center">'||(RPAD(NVL(TO_CHAR(av.approved_dt), nbsp), 13))||'</td>' approved_dt,
356 '<td align="center">'||(RPAD(NVL(av.exemption_institution_cd, nbsp), 30))||'</td>' exemption_institution_cd,
357 '<td align="center">'||(RPAD(NVL(atu.unit, nbsp), 10))||'</td>' new_unit,
358 '<td align="center">'||(RPAD(NVL(TO_CHAR(atu.cp_earned), nbsp), 7))||'</td>' new_cp_earned,
359 '<td align="center">'||(RPAD(NVL(atu.grade, nbsp), 10))||'</td>' new_grade,
360 'UNIT' lvl,
361 av.av_stnd_unit_id pk
362 FROM igs_av_stnd_unit_all av,
363 igs_ad_term_unitdtls atu
364 WHERE av.person_id = cp_person_id
365 AND av.unit_details_id = cp_unit_details_id
366 AND atu.unit_details_id = av.unit_details_id
367 UNION ALL
368 SELECT '<td align="center">'||(RPAD(NVL(avl.cal_type, '& '), 12))||'</td>' cal_type,
369 '<td align="center">'||(RPAD(NVL(avl.unit_level, nbsp), 15))||'</td>' unit,
370 '<td align="center">'||(RPAD(nbsp, 30))||'</td>' ece_type,
371 '<td align="center">'||(LPAD(NVL(TO_CHAR(avl.credit_points), nbsp),9))||'</td>' credit_points,
372 '<td align="center">'||(RPAD(NVL(avl.s_adv_stnd_granting_status, nbsp), 30))||'</td>' adv_status,
373 '<td align="center">'||(RPAD(NVL(TO_CHAR(avl.approved_dt), nbsp), 13))||'</td>' approved_dt,
374 '<td align="center">'||(RPAD(NVL(avl.exemption_institution_cd, nbsp), 30))||'</td>' exemption_institution_cd,
375 '<td align="center">'||(RPAD(NVL(atu1.unit, nbsp), 10))||'</td>' new_unit,
376 '<td align="center">'||(RPAD(NVL(TO_CHAR(atu1.cp_earned), nbsp), 7))||'</td>' new_cp_earned,
377 '<td align="center">'||(RPAD(NVL(atu1.grade, nbsp), 10))||'</td>' new_grade,
378 'UNIT LEVEL' lvl,
379 avl.av_stnd_unit_lvl_id pk
380 FROM igs_av_stnd_unit_lvl_all avl,
381 igs_ad_term_unitdtls atu1
382 WHERE avl.person_id = cp_person_id
383 AND avl.unit_details_id = cp_unit_details_id
384 AND atu1.unit_details_id = avl.unit_details_id;
385
386 --Select Advanced Standing Unit records for updation of unit_details_id.
387 CURSOR cur_get_adv_unit_dtls(cp_av_stnd_unit_id NUMBER) IS
388 SELECT unit.rowid,
389 unit.*
390 FROM igs_av_stnd_unit_all unit
391 WHERE unit.av_stnd_unit_id = cp_av_stnd_unit_id
392 FOR UPDATE OF unit_details_id;
393 rec_get_adv_unit_dtls cur_get_adv_unit_dtls%ROWTYPE;
394
395 --Select Advanced Standing Unit Level records for updation of unit_details_id.
396 CURSOR cur_get_adv_unit_lvl_dtls(cp_av_stnd_unit_lvl_id NUMBER) IS
397 SELECT unl_lvl.rowid,
398 unl_lvl.*
399 FROM igs_av_stnd_unit_lvl_all unl_lvl
400 WHERE unl_lvl.av_stnd_unit_lvl_id = cp_av_stnd_unit_lvl_id
401 FOR UPDATE OF unit_details_id;
402 rec_get_adv_unit_lvl_dtls cur_get_adv_unit_lvl_dtls%ROWTYPE;
403
404 --Cursor to fetch the New Term Name
405 CURSOR cur_get_term(cp_term_details_id NUMBER) IS
406 SELECT '<td align="center">'||(RPAD(NVL(td.term, nbsp), 30))||'</td>' term_completed
407 FROM igs_ad_term_details td
408 WHERE td.term_details_id = cp_term_details_id;
409 rec_get_term cur_get_term%ROWTYPE;
410 rec_get_new_term cur_get_term%ROWTYPE;
411
412 --Cursor to fetch the Alt Unit Details for the Unit Advanced Standing.
413 CURSOR cur_get_alt_unit(cp_av_stnd_unit_id NUMBER) IS
414 SELECT alt.rowid
415 FROM igs_av_stnd_alt_unit alt
416 WHERE alt.av_stnd_unit_id = cp_av_stnd_unit_id;
417 rec_get_alt_unit cur_get_alt_unit%ROWTYPE;
418
419 --Cursor to fetch the Basis Details for the Unit Advanced Standing.
420 CURSOR cur_get_bas_dtl(cp_av_stnd_unit_id NUMBER) IS
421 SELECT bas.rowid
422 FROM igs_av_std_unt_basis_all bas
423 WHERE bas.av_stnd_unit_id = cp_av_stnd_unit_id;
424 rec_get_bas_dtl cur_get_bas_dtl%ROWTYPE;
425
426 --Cursor to fetch the Basis Details for the Unit Level Advanced Standing.
427 CURSOR cur_get_bas_lvl(cp_av_stnd_unit_lvl_id NUMBER) IS
428 SELECT lvl.rowid
429 FROM igs_av_std_ulvlbasis_all lvl
430 WHERE lvl.av_stnd_unit_lvl_id = cp_av_stnd_unit_lvl_id;
431 rec_get_bas_lvl cur_get_bas_lvl%ROWTYPE;
432
433 -- Cursor to fetch the Institution Code
434 CURSOR cur_get_inst_cd(cp_education_id NUMBER) IS
435 SELECT institution_code
436 FROM igs_ad_acad_history_v
437 WHERE education_id = cp_education_id;
438 rec_get_inst_cd cur_get_inst_cd%ROWTYPE;
439
440 l_del_unit_details_body VARCHAR2(4000) := NULL;
441 l_del_basis_dtls_body VARCHAR2(4000) := NULL;
442 l_mod_unit_details_body VARCHAR2(4000) := NULL;
443 l_mod_basis_dtls_body VARCHAR2(4000) := NULL;
444 l_new_basis_dtls_body VARCHAR2(4000) := NULL;
445 l_adv_deleted VARCHAR2(1);
446 l_grade_changed VARCHAR2(1);
447 l_new_unit VARCHAR2(1) := 'N';
448 BEGIN
449 --
450 -- Fetch the Institution Code from the Education Code.
451 --
452 OPEN cur_get_inst_cd(p_education_id);
453 FETCH cur_get_inst_cd INTO rec_get_inst_cd;
454 CLOSE cur_get_inst_cd;
455
456 -- o Try to find that if the Student has any Advanced Standing Records for given Institution.
457 FOR rec_chk_adv IN cur_chk_adv(p_person_id, rec_get_inst_cd.institution_code) LOOP
458 --Initilalize the variables...
459 l_adv_deleted := 'N';
460 l_grade_changed := 'N';
461
462 --Get the Old Transcript Details and Unit associated with it...
463 OPEN cur_get_unit_details_id(rec_chk_adv.unit_details_id, NULL, NULL);
464 FETCH cur_get_unit_details_id INTO rec_get_old_unit_details;
465 CLOSE cur_get_unit_details_id;
466
467 -- Fetch all Terms attached to the New Transcript and check if all Unit is
468 -- attached to it, which were attached to the Old Transcript...
469 l_grade_changed := 'N';
470
471 --Fetch Old Transcript Id
472 OPEN cur_get_term_dtls(NULL, rec_get_old_unit_details.term_details_id);
473 FETCH cur_get_term_dtls INTO rec_get_old_term_dtls; --rec_get_old_term_dtls.transcript_id --Old Transcript Id
474 CLOSE cur_get_term_dtls;
475
476 -- Fetch all Terms attached to the New Transcript...
477 FOR rec_get_new_term_id IN cur_get_term_dtls(p_transcript_id, NULL) LOOP --New Terms...
478 -- Fetch New Term Name.
479 OPEN cur_get_term(rec_get_new_term_id.term_details_id);
480 FETCH cur_get_term INTO rec_get_new_term;
481 CLOSE cur_get_term;
482
483 IF l_grade_changed = 'N' THEN
484 OPEN cur_get_unit_details_id(NULL, rec_get_old_unit_details.unit, rec_get_new_term_id.term_details_id);
485 FETCH cur_get_unit_details_id INTO rec_get_new_unit_details;
486 IF cur_get_unit_details_id%FOUND THEN
487 -- The new transcript has the old unit associated with it.
488 -- ooo Check for Grade Details Changed
489 IF NVL(rec_get_old_unit_details.cp_attempted, -1) <> NVL(rec_get_new_unit_details.cp_attempted, -1) OR
490 NVL(rec_get_old_unit_details.cp_earned, -1) <> NVL(rec_get_new_unit_details.cp_earned, -1) OR
491 NVL(rec_get_old_unit_details.grade, 'NULL') <> NVL(rec_get_new_unit_details.grade, 'NULL') OR
492 NVL(rec_get_old_unit_details.unit_grade_points, -1) <> NVL(rec_get_new_unit_details.unit_grade_points, -1) THEN
493 --Put a flag to indicate that there is some Grade differences and it needs to be verified.
494 l_grade_changed := 'Y';
495 ELSE
496 l_grade_changed := 'U';
497 END IF;
498 END IF;
499 CLOSE cur_get_unit_details_id;
500 END IF;
501
502 --Check for New Units...
503 --If the new transcript has units that are not in transcript that the advanced standing
504 --records are based upon, a notification is sent describing the new unit details.
505 IF l_new_unit = 'N' THEN
506 --Fetch all new Units details attached to the New Transcript...
507 FOR rec_chk_new_tran IN cur_chk_new_tran(TO_NUMBER(rec_get_new_term_id.term_details_id),
508 TO_NUMBER(rec_get_old_term_dtls.transcript_id)) LOOP
509 --Populate the Basis Details...
510 l_new_basis_dtls_body := l_new_basis_dtls_body
511 ||'<tr><td align="center">'||(RPAD(rec_get_inst_cd.institution_code,30))||'</td>'||
512 rec_chk_new_tran.new_unit||
513 rec_get_new_term.term_completed||
514 rec_chk_new_tran.cp_earned||
515 rec_chk_new_tran.grade||'</tr>';
516 END LOOP;
517 END IF;
518 END LOOP;
519
520 IF l_new_basis_dtls_body IS NOT NULL THEN
521 --Put a flag to indicate that all new units details has been fetched and
522 --no futher processing is required.
523 l_new_unit := 'Y';
524 END IF;
525
526 IF l_grade_changed NOT IN ('Y', 'U') THEN
527 --Put a flag to indecate that the Advanced Standing Deletion notification needs to be sent.
528 l_adv_deleted := 'Y';
529 END IF;
530
531 -- Fetch the Old Term Name.
532 OPEN cur_get_term(rec_get_old_unit_details.term_details_id);
533 FETCH cur_get_term INTO rec_get_term;
534 CLOSE cur_get_term;
535 --Fetch all Advanced Standing Records
536 IF l_adv_deleted = 'Y' OR l_grade_changed IN ('Y','U') THEN
537 FOR rec_del_adv_dtls IN cur_del_adv_dtls(p_person_id,
538 rec_get_old_unit_details.unit_details_id) LOOP
539
540 IF l_adv_deleted = 'Y' THEN
541 --Populate the Unit Details...
542 l_del_unit_details_body := l_del_unit_details_body||'<tr>'||rec_del_adv_dtls.cal_type||rec_del_adv_dtls.unit||
543 rec_del_adv_dtls.ece_type||rec_del_adv_dtls.credit_points||rec_del_adv_dtls.adv_status||
544 rec_del_adv_dtls.approved_dt||'</tr>';
545 --Populate the Basis Details...
546 l_del_basis_dtls_body := l_del_basis_dtls_body||'<tr>'||rec_del_adv_dtls.exemption_institution_cd||rec_del_adv_dtls.new_unit||
547 rec_get_term.term_completed||rec_del_adv_dtls.new_cp_earned||rec_del_adv_dtls.new_grade||'</tr>';
548 -- ** Delete the Advanced Standing related records for the given p_person_id,
549 -- ** p_transcript_id and rec_get_old_unit_details.unit_details_id
550 --/*
551 IF RTRIM(rec_del_adv_dtls.lvl) = 'UNIT' AND SUBSTR(rec_del_adv_dtls.ece_type, 20, 10) = 'PRECLUSION' THEN
552 --Check if there exists any Alternate Unit Details; If exists then delete it;
553 FOR rec_get_alt_unit IN cur_get_alt_unit(rec_del_adv_dtls.pk) LOOP
554 igs_av_stnd_alt_unit_pkg.delete_row(X_ROWID => rec_get_alt_unit.rowid);
555 END LOOP;
556
557 --Check if there exists any Unit Basis Details; If exists then delete it;
558 FOR rec_get_bas_dtl IN cur_get_bas_dtl(rec_del_adv_dtls.pk) LOOP
559 igs_av_std_unt_basis_pkg.delete_row(X_ROWID => rec_get_bas_dtl.rowid);
560 END LOOP;
561
562 --Fetch the Advanced Standing Unit Record for deletion...
563 OPEN cur_get_adv_unit_dtls(rec_del_adv_dtls.pk);
564 FETCH cur_get_adv_unit_dtls INTO rec_get_adv_unit_dtls;
565 CLOSE cur_get_adv_unit_dtls;
566 igs_av_stnd_unit_pkg.delete_row(X_ROWID => rec_get_adv_unit_dtls.rowid);
567 ELSIF RTRIM(rec_del_adv_dtls.lvl) = 'UNIT' AND SUBSTR(rec_del_adv_dtls.ece_type, 20, 10) <> 'PRECLUSION' THEN
568 --Check if there exists any Unit Basis Details; If exists then delete it;
569 FOR rec_get_bas_dtl IN cur_get_bas_dtl(rec_del_adv_dtls.pk) LOOP
570 igs_av_std_unt_basis_pkg.delete_row(X_ROWID => rec_get_bas_dtl.rowid);
571 END LOOP;
572
573 --Fetch the Advanced Standing Unit Record for deletion...
574 OPEN cur_get_adv_unit_dtls(rec_del_adv_dtls.pk);
575 FETCH cur_get_adv_unit_dtls INTO rec_get_adv_unit_dtls;
576 CLOSE cur_get_adv_unit_dtls;
577 igs_av_stnd_unit_pkg.delete_row(X_ROWID => rec_get_adv_unit_dtls.rowid);
578 ELSIF RTRIM(rec_del_adv_dtls.lvl) = 'UNIT LEVEL' THEN
579 --Check if there exists any Unit Level Basis Details; If exists then delete it;
580 FOR rec_get_bas_lvl IN cur_get_bas_lvl(rec_del_adv_dtls.pk) LOOP
581 igs_av_std_ulvlbasis_pkg.delete_row(X_ROWID => rec_get_bas_lvl.rowid);
582 END LOOP;
583
584 --Fetch the Advanced Standing Unit Level Record for deletion...
585 OPEN cur_get_adv_unit_lvl_dtls(rec_del_adv_dtls.pk);
586 FETCH cur_get_adv_unit_lvl_dtls INTO rec_get_adv_unit_lvl_dtls;
587 CLOSE cur_get_adv_unit_lvl_dtls;
588 igs_av_stnd_unit_lvl_pkg.delete_row(X_ROWID => rec_get_adv_unit_lvl_dtls.rowid);
589 END IF;
590 --*/
591 ELSIF l_grade_changed IN ('Y', 'U') THEN
592 IF l_grade_changed = 'Y' THEN
593 --Populate the Unit Details...
594 l_mod_unit_details_body := l_mod_unit_details_body||'<tr>'||rec_del_adv_dtls.cal_type||rec_del_adv_dtls.unit||
595 rec_del_adv_dtls.ece_type||rec_del_adv_dtls.credit_points||rec_del_adv_dtls.adv_status||
596 rec_del_adv_dtls.approved_dt||'</tr>';
597 --Populate the Basis Details...
598 l_mod_basis_dtls_body := l_mod_basis_dtls_body||'<tr>'||rec_del_adv_dtls.exemption_institution_cd||rec_del_adv_dtls.new_unit||
599 rec_get_term.term_completed||rec_del_adv_dtls.new_cp_earned||rec_del_adv_dtls.new_grade||'</tr>';
600 END IF;
601 -- oooo Update Transcript Details Pointer
602 -- ** Before exiting, the advanced standing record is updated to reflect the
603 -- ** association with new transcript by updating each UNIT_DETAILS_ID in
604 -- ** IGS_AV_ADV_STND_UNIT_ALL and IGS_AV_ADV_STND_LVL_ALL where EXEMPTION_INSTITUTION_CD
605 -- ** equals the EDUCATION_ID parameter to point to the unit under the new transcript.
606
607 IF rec_del_adv_dtls.lvl = 'UNIT' THEN
608 OPEN cur_get_adv_unit_dtls(rec_del_adv_dtls.pk);
609 FETCH cur_get_adv_unit_dtls INTO rec_get_adv_unit_dtls;
610 IF cur_get_adv_unit_dtls%FOUND THEN
611 igs_av_stnd_unit_pkg.update_row(
612 X_ROWID => rec_get_adv_unit_dtls.rowid ,
613 X_PERSON_ID => rec_get_adv_unit_dtls.person_id ,
614 X_AS_COURSE_CD => rec_get_adv_unit_dtls.as_course_cd ,
615 X_AS_VERSION_NUMBER => rec_get_adv_unit_dtls.as_version_number ,
616 X_S_ADV_STND_TYPE => rec_get_adv_unit_dtls.s_adv_stnd_type ,
617 X_UNIT_CD => rec_get_adv_unit_dtls.unit_cd ,
618 X_VERSION_NUMBER => rec_get_adv_unit_dtls.version_number ,
619 X_S_ADV_STND_GRANTING_STATUS => rec_get_adv_unit_dtls.s_adv_stnd_granting_status ,
620 X_CREDIT_PERCENTAGE => NULL ,
621 X_S_ADV_STND_RECOGNITION_TYPE => rec_get_adv_unit_dtls.s_adv_stnd_recognition_type,
622 X_APPROVED_DT => rec_get_adv_unit_dtls.approved_dt ,
623 X_AUTHORISING_PERSON_ID => rec_get_adv_unit_dtls.authorising_person_id ,
624 X_CRS_GROUP_IND => rec_get_adv_unit_dtls.crs_group_ind ,
625 X_EXEMPTION_INSTITUTION_CD => rec_get_adv_unit_dtls.exemption_institution_cd ,
626 X_GRANTED_DT => rec_get_adv_unit_dtls.granted_dt ,
627 X_EXPIRY_DT => rec_get_adv_unit_dtls.expiry_dt ,
628 X_CANCELLED_DT => rec_get_adv_unit_dtls.cancelled_dt ,
629 X_REVOKED_DT => rec_get_adv_unit_dtls.revoked_dt ,
630 X_COMMENTS => rec_get_adv_unit_dtls.comments ,
631 X_AV_STND_UNIT_ID => rec_get_adv_unit_dtls.av_stnd_unit_id ,
632 X_CAL_TYPE => rec_get_adv_unit_dtls.cal_type ,
633 X_CI_SEQUENCE_NUMBER => rec_get_adv_unit_dtls.ci_sequence_number ,
634 X_INSTITUTION_CD => rec_get_adv_unit_dtls.institution_cd ,
635 X_UNIT_DETAILS_ID => rec_get_new_unit_details.unit_details_id , --Change the unit_details_id.
636 X_TST_RSLT_DTLS_ID => rec_get_adv_unit_dtls.tst_rslt_dtls_id ,
637 X_GRADING_SCHEMA_CD => rec_get_adv_unit_dtls.grading_schema_cd ,
638 X_GRD_SCH_VERSION_NUMBER => rec_get_adv_unit_dtls.grd_sch_version_number ,
639 X_GRADE => rec_get_adv_unit_dtls.grade ,
640 X_ACHIEVABLE_CREDIT_POINTS => rec_get_adv_unit_dtls.achievable_credit_points ,
641 X_DEG_AUD_DETAIL_ID => rec_get_adv_unit_dtls.deg_aud_detail_id ,
642 X_MODE => 'R'
643 );
644 END IF;
645 CLOSE cur_get_adv_unit_dtls;
646 ELSE
647 OPEN cur_get_adv_unit_lvl_dtls(rec_del_adv_dtls.pk);
648 FETCH cur_get_adv_unit_lvl_dtls INTO rec_get_adv_unit_lvl_dtls;
649 IF cur_get_adv_unit_lvl_dtls%FOUND THEN
650 igs_av_stnd_unit_lvl_pkg.update_row(
651 X_ROWID => rec_get_adv_unit_lvl_dtls.rowid ,
652 X_PERSON_ID => rec_get_adv_unit_lvl_dtls.person_id ,
653 X_AS_COURSE_CD => rec_get_adv_unit_lvl_dtls.as_course_cd ,
654 X_AS_VERSION_NUMBER => rec_get_adv_unit_lvl_dtls.as_version_number ,
655 X_S_ADV_STND_TYPE => rec_get_adv_unit_lvl_dtls.s_adv_stnd_type ,
656 X_UNIT_LEVEL => rec_get_adv_unit_lvl_dtls.unit_level ,
657 X_CRS_GROUP_IND => rec_get_adv_unit_lvl_dtls.crs_group_ind ,
658 X_EXEMPTION_INSTITUTION_CD => rec_get_adv_unit_lvl_dtls.exemption_institution_cd ,
659 X_S_ADV_STND_GRANTING_STATUS => rec_get_adv_unit_lvl_dtls.s_adv_stnd_granting_status,
660 X_CREDIT_POINTS => rec_get_adv_unit_lvl_dtls.credit_points ,
661 X_APPROVED_DT => rec_get_adv_unit_lvl_dtls.approved_dt ,
662 X_AUTHORISING_PERSON_ID => rec_get_adv_unit_lvl_dtls.authorising_person_id ,
663 X_GRANTED_DT => rec_get_adv_unit_lvl_dtls.granted_dt ,
664 X_EXPIRY_DT => rec_get_adv_unit_lvl_dtls.expiry_dt ,
665 X_CANCELLED_DT => rec_get_adv_unit_lvl_dtls.cancelled_dt ,
666 X_REVOKED_DT => rec_get_adv_unit_lvl_dtls.revoked_dt ,
667 X_COMMENTS => rec_get_adv_unit_lvl_dtls.comments ,
668 X_AV_STND_UNIT_LVL_ID => rec_get_adv_unit_lvl_dtls.av_stnd_unit_lvl_id ,
669 X_CAL_TYPE => rec_get_adv_unit_lvl_dtls.cal_type ,
670 X_CI_SEQUENCE_NUMBER => rec_get_adv_unit_lvl_dtls.ci_sequence_number ,
671 X_INSTITUTION_CD => rec_get_adv_unit_lvl_dtls.institution_cd ,
672 X_UNIT_DETAILS_ID => rec_get_new_unit_details.unit_details_id , --Change the unit_details_id.
673 X_TST_RSLT_DTLS_ID => rec_get_adv_unit_lvl_dtls.tst_rslt_dtls_id ,
674 X_DEG_AUD_DETAIL_ID => rec_get_adv_unit_lvl_dtls.deg_aud_detail_id ,
675 X_QUAL_DETS_ID => rec_get_adv_unit_lvl_dtls.qual_dets_id ,
676 X_MODE => 'R'
677 );
678 END IF;
679 CLOSE cur_get_adv_unit_lvl_dtls;
680 END IF;
681 END IF;
682 END LOOP;
683 END IF;
684 END LOOP;
685 --
686 --Close all html tables
687 --
688 IF l_del_unit_details_body IS NOT NULL THEN
689 l_del_unit_details_body := l_del_unit_details_body||'</table>';
690 l_del_basis_dtls_body := l_del_basis_dtls_body||'</table>';
691 END IF;
692 IF l_mod_unit_details_body IS NOT NULL THEN
693 l_mod_unit_details_body := l_mod_unit_details_body||'</table>';
694 l_mod_basis_dtls_body := l_mod_basis_dtls_body||'</table>';
695 END IF;
696 IF l_new_basis_dtls_body IS NOT NULL THEN
697 l_new_basis_dtls_body := l_new_basis_dtls_body||'</table>';
698 END IF;
699
700 --
701 -- Set the value of the Paramentes in Workflow....
702 --
703 wf_engine.setitemattrtext(ItemType => p_itemtype,
704 ItemKey => p_itemkey,
705 aname => 'P_REC_DEL_UDTL',
706 avalue => l_del_unit_details_body);
707
708 wf_engine.setitemattrtext(ItemType => p_itemtype,
709 ItemKey => p_itemkey,
710 aname => 'P_REC_DEL_BDTL',
711 avalue => l_del_basis_dtls_body);
712
713 wf_engine.setitemattrtext(ItemType => p_itemtype,
714 ItemKey => p_itemkey,
715 aname => 'P_REC_MOD_UDTL',
716 avalue => l_mod_unit_details_body);
717 wf_engine.setitemattrtext(ItemType => p_itemtype,
718 ItemKey => p_itemkey,
719 aname => 'P_REC_MOD_BDTL',
720 avalue => l_mod_basis_dtls_body);
721
722 wf_engine.setitemattrtext(ItemType => p_itemtype,
723 ItemKey => p_itemkey,
724 aname => 'P_REC_NEW_BDTL',
725 avalue => l_new_basis_dtls_body);
726 END get_transcript_data;
727 END igs_av_val_asuleb;
[Home] [Help]