1 PACKAGE BODY igs_en_gen_018 AS
2 /* $Header: IGSENA8B.pls 120.5 2006/02/23 05:50:59 ckasu ship $ */
3
4
5 TYPE r_msg_rec IS RECORD(upload_id igs_en_blk_sua_ints.upload_id%TYPE,
6 type VARCHAR(1),
7 message VARCHAR2(2000));
8
9 TYPE l_msg_tab IS TABLE OF r_msg_rec INDEX BY BINARY_INTEGER;
10 l_message_table l_msg_tab;
11 l_msg_rec_ind INTEGER :=0;
12
13
14
15
16 FUNCTION enrpl_get_msg (p_message_in IN VARCHAR2)
17 -----------------------------------------------------------------------------------
18 --Created by : rvivekan ( Oracle IDC)
19 --Date created: 12-AUG-2003
20 --
21 --Purpose: This functions accepts semicoln separated concatenated message names and
22 -- returns a semicolon separated concatenated messages string
23 --Known limitations/enhancements and/or remarks:
24 --
25 --Change History:
26 --Who When What
27 ------------------------------------------------------------------------------------
28 RETURN VARCHAR2 AS
29 l_return_msg VARCHAR2(2000) :=NULL;
30 p_messages VARCHAR2(2000) :=p_message_in;
31 l_mesg_name VARCHAR2(2000) :=NULL;
32 l_mesg_text VARCHAR2(2000) :=NULL;
33 l_msg_token VARCHAR2(2000) :=NULL;
34 l_msg_len INTEGER;
35 l_str_place INTEGER;
36 cst_delimiter VARCHAR2(1):=';' ;
37
38 BEGIN
39 IF SUBSTR(p_messages,1,1) = cst_delimiter THEN
40 p_messages := SUBSTR(p_messages,2);
41 END IF;
42 IF SUBSTR(p_messages,-1,1) <> cst_delimiter THEN
43 p_messages := p_messages||cst_delimiter;
44 END IF;
45 l_mesg_name := NULL;
46 l_msg_len:= LENGTH(p_messages);
47 FOR i IN 1 .. l_msg_len
48 LOOP
49 IF SUBSTR(p_messages,i,1) = cst_delimiter THEN
50 --If any call to igs_ss_en_wrappers results in a exception
51 --Then l_mesg_name contains the exception text and should not be translated
52 --Message texts are generally more than 30 chars and even if they are less
53 --than 30 chars, fnd_message returns the same text if it cant find a match.
54 --If messages larger than 30 chars (max size if msg_name field) are passed to
55 --fnd_message, an plsql exception is thrown.Hence the check for 30 characters size.
56 IF LENGTH(l_mesg_name)<=30 THEN
57 l_str_place :=INSTR(l_mesg_name,'*');
58 IF l_str_place <> 0 THEN
59 l_msg_token:= SUBSTR(l_mesg_name,l_str_place+1);
60 l_mesg_name:= SUBSTR(l_mesg_name,1,l_str_place-1);
61 fnd_message.set_name('IGS',l_mesg_name);
62 fnd_message.set_token('UNIT_CD',l_msg_token);
63 ELSE
64 fnd_message.set_name('IGS',l_mesg_name);
65 END IF;
66 l_mesg_text:=fnd_message.get;
67 ELSE
68 l_mesg_text:=l_mesg_name;
69 END IF; --30 character message name
70
71 IF l_return_msg IS NULL THEN
72 l_return_msg:= l_mesg_text;
73 ELSE
74 l_return_msg:= l_return_msg||';'||l_mesg_text;
75 END IF;
76 l_mesg_name := NULL;
77 ELSE
78 l_mesg_name := l_mesg_name||SUBSTR(p_messages,i,1);
79 END IF;
80 END LOOP;
81 RETURN l_return_msg;
82 END enrpl_get_msg;
83
84
85
86
87
88 PROCEDURE enrpl_log_msg(
89 p_level VARCHAR2,
90 p_message VARCHAR2,
91 p_batch_id igs_en_blk_sua_ints.batch_id%TYPE ,
92 p_person_number igs_en_blk_sua_ints.person_number%TYPE DEFAULT NULL ,
93 p_program_cd igs_en_blk_sua_ints.program_cd%TYPE DEFAULT NULL,
94 p_program_ver igs_en_blk_sua_ints.program_ver_num%TYPE DEFAULT NULL,
95 p_load_alt_code igs_en_blk_sua_ints.alternate_cd%TYPE DEFAULT NULL
96 )
97 AS
98 -----------------------------------------------------------------------------------
99 --Created by : rvivekan ( Oracle IDC)
100 --Date created: 12-AUG-2003
101 --
102 --Purpose: This procedure Updates the interface table with group level errors
103 -- and outputs the same to the fnd_log as well
104 --
105 --Known limitations/enhancements and/or remarks:
106
107 --Change History:
108 --Who When What
109 ------------------------------------------------------------------------------------
110
111 l_cid INTEGER;
112 l_exec INTEGER;
113 l_sql VARCHAR2(2000);
114 l_fcount NUMBER:=1;
115 l_lcount NUMBER:=1;
116 l_message VARCHAR2(2000);
117
118 BEGIN
119 l_message:=enrpl_get_msg(p_message);
120
121 UPDATE igs_en_blk_sua_ints
122 SET ERROR_TXT=l_message,
123 status_flag=DECODE(p_level,'E','E','W1',status_flag,'S'), -- W1 is grp level warning..changing to S here will prevent c_bulk_suas from picking this upload
124 last_updated_by=fnd_global.user_id, last_update_login=fnd_global.login_id, last_update_date=SYSDATE,
125 request_id=fnd_global.conc_request_id, program_id=fnd_global.conc_program_id ,
126 program_application_id=fnd_global.prog_appl_id, program_update_date=SYSDATE
127 WHERE batch_id=p_batch_id
128 AND status_flag IN ('U','R','S')
129 AND person_number =p_person_number
130 AND program_cd = NVL(p_program_cd ,program_cd)
131 AND program_ver_num = NVL(p_program_ver,program_ver_num)
132 AND ((p_load_alt_code IS NULL) OR
133 ((p_load_alt_code IS NOT NULL)AND
134 alternate_cd IN ( SELECT teach_alternate_code FROM igs_ca_load_to_teach_v
135 WHERE load_alternate_code=p_load_alt_code)));
136
137 IF p_load_alt_code IS NULL THEN -- group header has not yet been put..teach code will be not null only in case of teach calendar errors or relationship errors
138 --group header
139 fnd_file.put_line(Fnd_File.LOG, rpad(' ',80,'_'));
140 IF p_program_cd IS NULL THEN
141 fnd_message.set_name('IGS','IGS_EN_BLK_PER_MSG');
142 ELSE
143 fnd_message.set_name('IGS','IGS_EN_BLK_PER_PRO');
144 fnd_message.set_token('PRGCD',NVL(p_program_cd,''' '''));
145 END IF;
146 fnd_message.set_token('PERNUM', p_person_number);
147 fnd_file.put_line(Fnd_File.LOG,fnd_message.get);
148 fnd_file.put_line(Fnd_File.LOG, rpad(' ',80,'_'));
149 END IF;
150 WHILE l_fcount < LENGTH(l_message) LOOP
151 l_lcount:=INSTR(l_message,';',l_fcount);
152 IF l_lcount=0 THEN
153 fnd_file.put_line(fnd_file.LOG, SUBSTR(l_message,l_fcount));
154 l_fcount:=LENGTH(l_message)+1;
155 ELSE
156 fnd_file.put_line(fnd_file.LOG, SUBSTR(l_message,l_fcount,l_lcount-l_fcount));
157 l_fcount:=l_lcount+1;
158 END IF;
159 END LOOP;
160 END enrpl_log_msg;
161
162
163
164
165 PROCEDURE enrpl_save_unit_msg(p_message VARCHAR2) AS
166 -----------------------------------------------------------------------------------
167 --Created by : rvivekan ( Oracle IDC)
168 --Date created: 12-AUG-2003
169 --
170 --Purpose: To ouput the saved unit level error messages in the plsql table to the fnd_log
171 -- and to update the interface table with the same information.
172 -- If a group level error has occured, the group level error message is concatenated
173 -- to the unit section message and the status_flag is set to 'E'
174 --
175 --Known limitations/enhancements and/or remarks:
176 --
177 --Change History:
178 --Who When What
179 ------------------------------------------------------------------------------------
180
181 l_sql VARCHAR2(2000);
182 l_fcount NUMBER:=1;
183 l_lcount NUMBER:=1;
184 l_message VARCHAR2(2000);
185
186
187 BEGIN
188 fnd_file.put_line(Fnd_File.LOG, rpad(' ',80,'_'));
189 fnd_file.put_line(Fnd_File.LOG, rpad(fnd_message.get_string('IGS','IGS_EN_BLK_GRP_UID'),20,' ')||fnd_message.get_string('IGS','IGS_EN_BLK_GRP_UMSG'));
190 fnd_file.put_line(Fnd_File.LOG, rpad(' ',80,'-'));
191 FOR i IN 0.. l_msg_rec_ind-1
192 LOOP
193
194 IF l_message_table(i).type='S' AND p_message IS NOT NULL THEN
195 l_message_table(i).message:=p_message; --overwrite with group level error message.
196 l_message_table(i).type:='E' ;
197 END IF;
198 IF l_message_table(i).type= 'W' AND p_message IS NOT NULL THEN
199 l_message_table(i).message:=l_message_table(i).message||';'||p_message; --append with group level error message.
200 l_message_table(i).type:='E';
201 ELSE
202 IF l_message_table(i).type= 'W' THEN
203 l_message_table(i).type:='S';
204 l_message_table(i).message:=l_message_table(i).message||';'||'IGS_EN_UA_SECCESS_ADDED_STUD';
205 END IF;
206 END IF;
207 l_message:=enrpl_get_msg(l_message_table(i).message);
208 UPDATE igs_en_blk_sua_ints SET ERROR_TXT=l_message , STATUS_FLAG=l_message_table(i).type,
209 last_updated_by = fnd_global.user_id, last_update_login =fnd_global.login_id,last_update_date=SYSDATE,
210 request_id=fnd_global.conc_request_id, program_id=fnd_global.conc_program_id ,program_application_id=fnd_global.prog_appl_id,
211 program_update_date=SYSDATE
212 WHERE upload_id=l_message_table(i).upload_id;
213
214 l_fcount:=1;l_lcount:=1;
215 WHILE l_fcount < LENGTH(l_message) LOOP
216 l_lcount:=INSTR(l_message,';',l_fcount);
217 IF l_lcount=0 THEN
218 fnd_file.put_line(fnd_file.LOG, rpad(nvl(to_char(l_message_table(i).upload_id),' '),20,' ')||SUBSTR(l_message,l_fcount));
219 l_fcount:=LENGTH(l_message)+1;
220 ELSE
221 fnd_file.put_line(fnd_file.LOG, rpad(nvl(to_char(l_message_table(i).upload_id),' '),20,' ')||SUBSTR(l_message,l_fcount,l_lcount-l_fcount));
222 l_message_table(i).upload_id:=null;
223 l_fcount:=l_lcount+1;
224 END IF;
225 END LOOP;
226 END LOOP;
227 END enrpl_save_unit_msg;
228
229
230
231 PROCEDURE enrpl_unit_msg(
232 p_level VARCHAR2,
233 p_upload_id igs_en_blk_sua_ints.upload_id%TYPE,
234 p_message VARCHAR2
235 ) AS
236 -----------------------------------------------------------------------------------
237 --Created by : rvivekan ( Oracle IDC)
238 --Date created: 12-AUG-2003
239 --
240 --Purpose: To add unit level error messages to a plsql table
241 --
242 --Known limitations/enhancements and/or remarks:
243 --
244 --Change History:
245 --Who When What
246 ------------------------------------------------------------------------------------
247
248 BEGIN
249 --If a new upload is being processed, add a new record to the table else concatenate the message to existing record
250 IF l_msg_rec_ind=0 OR l_message_table(l_msg_rec_ind-1).upload_id<>p_upload_id THEN
251 l_message_table(l_msg_rec_ind).upload_id:=p_upload_id;
252 l_message_table(l_msg_rec_ind).message:=p_message;
253 l_message_table(l_msg_rec_ind).type:=p_level;
254 l_msg_rec_ind:=l_msg_rec_ind+1;
255 ELSE
256 l_message_table(l_msg_rec_ind-1).message:=l_message_table(l_msg_rec_ind-1).message||';'||p_message;
257 l_message_table(l_msg_rec_ind-1).type:=p_level;
258 END IF;
259 END enrpl_unit_msg;
260
261
262
263
264
265
266 PROCEDURE enrl_upd_core_ind (p_person_id IN NUMBER,
267 p_course_cd IN VARCHAR2,
268 p_uoo_id IN VARCHAR2,
269 p_core_ind IN VARCHAR2) AS
270 ------------------------------------------------------------------
271 --Created by : rvivekan, Oracle IDC
272 --Date created: 3-Aug-2003
273 --
274 --Purpose: This procedure is called to update the core_indicator_code column
275 -- in the student unit attempt
276 --
277 --
278 --Known limitations/enhancements and/or remarks:
279 --
280 --Change History:
281 --Who When What
282 --
283 -------------------------------------------------------------------
284
285 CURSOR c_get_sua (cp_person_id NUMBER, cp_course_cd VARCHAR2, cp_uoo_id VARCHAR2) IS
286 SELECT *
287 FROM IGS_EN_SU_ATTEMPT
288 WHERE person_id=cp_person_id
289 AND course_cd=cp_course_cd
290 AND uoo_id=cp_uoo_id;
291
292 l_sua c_get_sua%ROWTYPE;
293 BEGIN
294 OPEN c_get_sua (p_person_id,p_course_cd, p_uoo_id);
295 FETCH c_get_sua INTO l_sua;
296 CLOSE c_get_sua;
297 igs_en_sua_api.update_unit_attempt (
298 X_ROWID => l_sua.row_id ,
299 X_PERSON_ID => l_sua.person_id ,
300 X_COURSE_CD => l_sua.course_cd ,
301 X_UNIT_CD => l_sua.unit_cd ,
302 X_CAL_TYPE => l_sua.cal_type ,
303 X_CI_SEQUENCE_NUMBER => l_sua.ci_sequence_number ,
304 X_VERSION_NUMBER => l_sua.version_number ,
305 X_LOCATION_CD => l_sua.location_cd ,
306 X_UNIT_CLASS => l_sua.unit_class ,
307 X_CI_START_DT => l_sua.ci_Start_dt ,
308 X_CI_END_DT => l_sua.ci_end_dt ,
309 X_UOO_ID => l_sua.uoo_id,
310 X_ENROLLED_DT => l_sua.enrolled_dt ,
311 X_UNIT_ATTEMPT_STATUS => l_sua.unit_attempt_status ,
312 X_ADMINISTRATIVE_UNIT_STATUS => l_sua.administrative_unit_status ,
313 X_DISCONTINUED_DT => l_sua.discontinued_dt,
314 X_RULE_WAIVED_DT => l_sua.rule_waived_dt ,
315 X_RULE_WAIVED_PERSON_ID => l_sua.rule_waived_person_id ,
316 X_NO_ASSESSMENT_IND => l_sua.no_assessment_ind ,
317 X_SUP_UNIT_CD => l_sua.sup_unit_cd ,
318 X_SUP_VERSION_NUMBER => l_sua.sup_version_number ,
319 X_EXAM_LOCATION_CD => l_sua.exam_location_cd ,
320 X_ALTERNATIVE_TITLE => l_sua.alternative_title ,
321 X_OVERRIDE_ENROLLED_CP => l_sua.override_enrolled_cp ,
322 X_OVERRIDE_EFTSU => l_sua.override_eftsu ,
323 X_OVERRIDE_ACHIEVABLE_CP => l_sua.override_achievable_cp ,
324 X_OVERRIDE_OUTCOME_DUE_DT => l_sua.override_outcome_due_dt ,
325 X_OVERRIDE_CREDIT_REASON => l_sua.override_credit_reason ,
326 X_ADMINISTRATIVE_PRIORITY => l_sua.administrative_priority ,
327 X_WAITLIST_DT => l_sua.waitlist_dt ,
328 X_DCNT_REASON_CD => l_sua.dcnt_reason_cd ,
329 X_MODE => 'R' ,
330 X_GS_VERSION_NUMBER => l_sua.gs_version_number ,
331 X_ENR_METHOD_TYPE => l_sua.enr_method_type ,
332 X_FAILED_UNIT_RULE => l_sua.failed_unit_rule ,
333 X_CART => l_sua.cart ,
334 X_RSV_SEAT_EXT_ID => l_sua.rsv_seat_ext_id ,
335 X_ORG_UNIT_CD => l_sua.org_unit_cd ,
336 X_GRADING_SCHEMA_CODE => l_sua.grading_schema_code,
337 X_SUBTITLE => l_sua.subtitle ,
338 X_SESSION_ID => l_sua.session_id ,
339 X_DEG_AUD_DETAIL_ID => l_sua.deg_aud_detail_id ,
340 X_STUDENT_CAREER_TRANSCRIPT => l_sua.student_career_transcript ,
341 X_STUDENT_CAREER_STATISTICS => l_sua.student_career_statistics ,
342 X_WAITLIST_MANUAL_IND => l_sua.waitlist_manual_ind ,
343 X_ATTRIBUTE_CATEGORY => l_sua.attribute_category ,
344 X_ATTRIBUTE1 => l_sua.attribute1 ,
345 X_ATTRIBUTE2 => l_sua.attribute2 ,
346 X_ATTRIBUTE3 => l_sua.attribute3 ,
347 X_ATTRIBUTE4 => l_sua.attribute4 ,
348 X_ATTRIBUTE5 => l_sua.attribute5 ,
349 X_ATTRIBUTE6 => l_sua.attribute6 ,
350 X_ATTRIBUTE7 => l_sua.attribute7 ,
351 X_ATTRIBUTE8 => l_sua.attribute8 ,
352 X_ATTRIBUTE9 => l_sua.attribute9 ,
353 X_ATTRIBUTE10 => l_sua.attribute10 ,
354 X_ATTRIBUTE11 => l_sua.attribute11 ,
355 X_ATTRIBUTE12 => l_sua.attribute12 ,
356 X_ATTRIBUTE13 => l_sua.attribute13 ,
357 X_ATTRIBUTE14 => l_sua.attribute14 ,
358 X_ATTRIBUTE15 => l_sua.attribute15 ,
359 X_ATTRIBUTE16 => l_sua.attribute16 ,
360 X_ATTRIBUTE17 => l_sua.attribute17 ,
361 X_ATTRIBUTE18 => l_sua.attribute18 ,
362 X_ATTRIBUTE19 => l_sua.attribute19 ,
363 X_ATTRIBUTE20 => l_sua.attribute20 ,
364 X_WLST_PRIORITY_WEIGHT_NUM => l_sua.wlst_priority_weight_num,
365 X_WLST_PREFERENCE_WEIGHT_NUM => l_sua.wlst_preference_weight_num,
366 X_CORE_INDICATOR_CODE => p_core_ind ); -- Set core indicator to new value from interface table
367 END enrl_upd_core_ind;
368
369
370
371 PROCEDURE enrp_batch_sua_upload(
372 Errbuf OUT NOCOPY VARCHAR2,
373 Retcode OUT NOCOPY NUMBER,
374 p_batch_id IN NUMBER,
375 p_dflt_unit_confirmed IN VARCHAR2,
376 p_ovr_enr_method IN VARCHAR2,
377 p_deletion_flag IN VARCHAR2) AS
378 ------------------------------------------------------------------
379 --Created by : rvivekan, Oracle IDC
380 --Date created: 3-Aug-2003
381 --
382 --Purpose: This is procedure corresponding to the bulk unit upload concurrent job
383 --
384 --
385 --
386 --Known limitations/enhancements and/or remarks:
387 --
388 --Change History:
389 --Who When What
390 --stutta 11-Feb-2004 Passing new parameter p_enrolled_dt as SYSDATE in
391 -- call to validate_enroll_validate.
392 -- ckasu 30-DEC-2004 modified code inorder to consider Term Records while
393 -- getting primary program type as a part of bug#4095276
394 -- ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
395 -------------------------------------------------------------------
396
397 --Fetches the distinct persons in the batch
398 CURSOR c_bulk_persons (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE) IS
399 SELECT DISTINCT person_number
400 FROM igs_en_blk_sua_ints
401 WHERE batch_id=cp_batch_id
402 AND status_flag IN ('U','R')
403 ORDER BY person_number;
404
405 --Fetches the distinct program attempts for the person
406 CURSOR c_bulk_programs (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE, cp_person_number igs_pe_person.person_number%TYPE) IS
407 SELECT DISTINCT program_cd, program_ver_num
408 FROM igs_en_blk_sua_ints
409 WHERE batch_id=cp_batch_id
410 AND person_number=cp_person_number
411 AND status_flag IN ('U','R');
412
413
414 --Fetches the relevant load calendars depending upon the teach alt codes specified
415 --int the interface table. The person and program attempt are in the context.
416 CURSOR c_bulk_load (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE,
417 cp_person_number igs_pe_person.person_number%TYPE,
418 cp_program_cd igs_ps_ofr_opt.course_cd%TYPE,
419 cp_program_ver_num igs_en_blk_sua_ints.program_ver_num%TYPE) IS
420 SELECT DISTINCT ttol.load_cal_type cal_type, ttol.load_ci_sequence_number
421 seq_num, ttol.load_alternate_code alt_cd,ttol.load_start_dt
422 FROM igs_en_blk_sua_ints sui, igs_ca_teach_to_load_v ttol
423 WHERE batch_id=cp_batch_id
424 AND person_number=cp_person_number
425 AND status_flag IN ('U','R')
426 AND program_cd=cp_program_cd
427 AND program_ver_num=cp_program_ver_num
428 AND ttol.teach_alternate_code=sui.alternate_cd
429 AND ttol.load_start_dt=
430 (SELECT MIN(cttol.load_start_dt) FROM igs_ca_teach_to_load_v cttol
431 WHERE cttol.teach_cal_type=ttol.teach_cal_type
432 AND cttol.teach_ci_sequence_number=ttol.teach_ci_sequence_number)
433 ORDER BY ttol.load_start_dt;
434
435 --Fetches the Teach calendar details
436 CURSOR c_get_teach_cal_dtls(cp_load_cal_type igs_ca_inst.cal_type%TYPE,
437 cp_load_seq_num igs_ca_inst.sequence_number%TYPE
438 ) IS
439 SELECT teach_cal_type,
440 teach_ci_sequence_number,
441 teach_alternate_code
442 FROM igs_ca_teach_to_load_v ttol
443 WHERE ttol.load_cal_type=cp_load_cal_type
444 AND ttol.load_ci_sequence_number=cp_load_seq_num;
445
446 --Fetches the induvidual sua records from the interface table
447 CURSOR c_bulk_suas (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE,
448 cp_person_number igs_pe_person.person_number%TYPE,
449 cp_program_cd igs_ps_ofr_opt.course_cd%TYPE,
450 cp_program_ver_num igs_en_blk_sua_ints.program_ver_num%TYPE,
451 cp_teach_cal_type igs_ca_inst.cal_type%TYPE,
452 cp_teach_seq_num igs_ca_inst.sequence_number%TYPE,
453 cp_teach_alternate_cd igs_ca_inst.alternate_code%TYPE
454 ) IS
455 SELECT sui.upload_id,
456 sui.unit_cd,
457 sui.unit_ver_num,
458 sui.location_cd,
459 sui.unit_class,
460 sui.alternate_cd,
461 sui.status_flag,
462 sui.audit_flag,
463 sui.grading_sch_cd,
464 sui.grading_sch_ver_num,
465 sui.variable_cr_point,
466 sui.unit_sec_sub_title ,
467 sui.alternate_cd alt_cd,
468 sui.core_indicator_code,
469 sui.update_core_flag,
470 enrp_get_uoo_info(sui.unit_cd,sui.unit_ver_num,cp_teach_cal_type,cp_teach_seq_num,sui.location_cd,sui.unit_class) uoo_info
471 FROM igs_en_blk_sua_ints sui
472 WHERE batch_id=cp_batch_id
473 AND person_number=cp_person_number
474 AND status_flag IN ('U','R')
475 AND program_cd=cp_program_cd
476 AND program_ver_num=cp_program_ver_num
477 AND sui.alternate_cd = cp_teach_alternate_cd
478 ORDER BY uoo_info DESC;
479
480
481 --Used to check if the student has the relevant program attempt
482 CURSOR c_stdnt_ps_att (cp_person_id igs_pe_person.person_id%TYPE,
483 cp_program_cd igs_ps_ofr_opt.course_cd%TYPE,
484 cp_program_ver_num igs_en_blk_sua_ints.program_ver_num%TYPE) IS
485 SELECT course_attempt_status, primary_program_type FROM igs_en_stdnt_ps_att
486 WHERE person_id = cp_person_id
487 AND course_cd = cp_program_cd
488 AND version_number= cp_program_ver_num;
489
490 --Checks if the student has already attempted the specified uoo
491 CURSOR c_sua_exists (cp_person_id igs_pe_person.person_id%TYPE,
492 cp_program_cd igs_ps_ofr_opt.course_cd%TYPE,
493 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
494 SELECT 'Y' FROM igs_en_su_attempt
495 WHERE person_id =cp_person_id
496 AND course_cd = cp_program_cd
497 AND uoo_id = cp_uoo_id
498 AND unit_attempt_status IN ('ENROLLED','UNCONFIRM','WAITLISTED');
499
500
501 --Retrieves the interface records with invalid teach alt codes
502 -- replaced view igs_ca_inst with igs_ca_inst_all for perf bug#4961316
503 CURSOR c_invalid_teach (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE) IS
504 SELECT sui.upload_id, sui.alternate_cd alt_cd
505 FROM igs_en_blk_sua_ints sui,igs_ca_inst_all cal
506 WHERE sui.batch_id=cp_batch_id
507 AND status_flag IN ('U','R')
508 AND cal.alternate_code(+)=sui.alternate_cd
509 AND cal.alternate_code IS NULL;
510
511 --Retrieves the interface records with invalid teach 2 load relationship
512 CURSOR c_invalid_teach2load (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE) IS
513 SELECT sui.upload_id, sui.alternate_cd alt_cd,cal.cal_type,cal.sequence_number
514 FROM igs_en_blk_sua_ints sui,
515 igs_ca_inst cal
516 WHERE sui.alternate_cd=cal.alternate_code
517 AND batch_id=cp_batch_id
518 AND status_flag IN ('U','R')
519 AND NOT EXISTS(SELECT load_cal_type,load_ci_sequence_number
520 FROM igs_ca_teach_to_load_v ttol
521 WHERE cal.cal_type=ttol.teach_cal_type
522 AND cal.sequence_number=ttol.teach_ci_sequence_number);
523
524 CURSOR c_batch_desc (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE) IS
525 SELECT batch_desc
526 FROM igs_en_bat_sua_ints
527 WHERE batch_id=cp_batch_id;
528
529 CURSOR c_delete_bat (cp_batch_id igs_en_blk_sua_ints.batch_id%TYPE) IS
530 SELECT '1'
531 FROM igs_en_blk_sua_ints
532 WHERE batch_id=cp_batch_id;
533
534
535 cst_discontin VARCHAR2(40) :='DISCONTIN';
536 cst_completed VARCHAR2(40) :='COMPLETED';
537 cst_unconfirm VARCHAR2(40) :='UNCONFIRM';
538 cst_intermit VARCHAR2(40):='INTERMIT';
539 cst_invalid VARCHAR2(40):='INVALID';
540 cst_multiple VARCHAR2(40):='MULTIPLE';
541 cst_planned VARCHAR2(40):='PLANNED';
542 cst_cancelled VARCHAR2(40):='CANCELLED';
543 cst_notofr VARCHAR2(40):='NOT_OFFERED';
544 cst_primary VARCHAR2(40):='PRIMARY';
545
546 l_person_id igs_pe_person.person_id%TYPE;
547 l_person_type VARCHAR2(30);
548 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
549 l_rel_type igs_ps_unit_ofr_opt.relation_type%TYPE;
550 l_usec_status igs_ps_unit_ver.unit_status%TYPE;
551 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
552 l_acad_seq_num igs_ca_inst.sequence_number%TYPE;
553 l_acad_alt_cd igs_ca_inst.alternate_code%TYPE;
554 l_enr_cal_type igs_ca_inst.cal_type%TYPE;
555 l_enr_seq_num igs_ca_inst.sequence_number%TYPE;
556 l_acad_start_dt igs_ca_inst.start_dt%TYPE;
557 l_acad_end_dt igs_ca_inst.start_dt%TYPE;
558 l_enr_method VARCHAR2(100);
559 l_enr_category VARCHAR2(100);
560 l_commencement_type VARCHAR2(100);
561 l_enr_categories VARCHAR2(100);
562 l_uoo_ids_list VARCHAR2(2000):=null;
563 l_error_message VARCHAR2(2000);
564 l_ret_status VARCHAR2(2000);
565 l_stdnt_ps_att c_stdnt_ps_att%ROWTYPE;
566 l_waitlist_ind VARCHAR2(30);
567 l_sua_exists VARCHAR(2);
568 l_deny_warn VARCHAR2(30);
569 l_abort_loop BOOLEAN :=FALSE;
570 l_dummy VARCHAR(10);
571 l_chk_au_allowed VARCHAR(10);
572 l_pos NUMBER;
573 l_pos1 NUMBER;
574 l_pos2 NUMBER;
575 l_processed_rec BOOLEAN := FALSE; -- Boolean to indicate if any records where processed
576 l_batch_desc VARCHAR2(100);
577 l_core_indicator_code igs_en_su_attempt.core_indicator_code%TYPE;
578 l_sup_units_list VARCHAR2(2000);
579 l_sup_unit igs_ps_unit_ofr_opt.uoo_id%TYPE;
580 l_sub_success VARCHAR2(2000);
581 l_sub_waitlist VARCHAR2(2000);
582 l_sub_failed VARCHAR2(2000);
583 l_save_message_no NUMBER;
584
585
586 BEGIN
587
588 igs_ge_gen_003.set_org_id(NULL);
589
590 igs_en_gen_017.g_invoke_source:='JOB';
591 retcode:=0;
592 SAVEPOINT blk_sua_job;
593 l_ret_status:=null;l_error_message:=null;
594
595 --Derive the --enrollment method
596 IF p_ovr_enr_method IS NULL THEN
597 igs_en_gen_017.enrp_get_enr_method (l_enr_method,l_error_message,l_ret_status);
598 IF l_ret_status='FALSE' THEN
599 Fnd_message.set_name('IGS','IGS_SS_EN_NOENR_METHOD');
600 Fnd_file.put_line(fnd_file.log,fnd_message.get);
601 retcode:=2;
602 RETURN;
603 END IF;
604 ELSE
605 l_enr_method:=p_ovr_enr_method;
606 END IF;
607 l_ret_status:=null;l_error_message:=null;
608 l_person_type:=igs_en_gen_008.enrp_get_person_type(p_course_cd =>NULL);
609
610 --Log header
611 OPEN c_batch_desc(p_batch_id);
612 FETCH c_batch_desc INTO l_batch_desc;
613 CLOSE c_batch_desc;
614 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_PE_CURR_DT')||':'||SYSDATE);
615 fnd_message.set_name('IGS','IGS_EN_BLK_GRP_HD');
616 fnd_message.set_token('BATCHID', p_batch_id);
617 fnd_message.set_token('BATCHDESC',l_batch_desc);
618 fnd_message.set_token('ENRMETHOD',l_enr_method);
619 fnd_file.put_line(Fnd_File.LOG,fnd_message.get);
620
621 fnd_message.set_name('IGS','IGS_EN_BLK_JOB_PARAM');
622 fnd_message.set_token('PARAM1', p_dflt_unit_confirmed);
623 fnd_message.set_token('PARAM2',p_ovr_enr_method);
624 fnd_message.set_token('PARAM3',p_deletion_flag);
625 fnd_file.put_line(Fnd_File.LOG,fnd_message.get);
626
627
628 FOR l_bulk_persons IN c_bulk_persons(p_batch_id)
629 LOOP --for each person
630 l_processed_rec:=TRUE;
631 l_person_id:= igs_ge_gen_003.get_person_id(l_bulk_persons.person_number);
632 IF l_person_id IS NULL THEN
633 enrpl_log_msg(p_level =>'E',
634 p_message =>'IGS_GE_INVALID_PERSON_NUMBER',
635 p_batch_id => p_batch_id,
636 p_person_number=>l_bulk_persons.person_number );
637 ELSE --valid person number
638 FOR l_bulk_programs IN c_bulk_programs(p_batch_id,l_bulk_persons.person_number)
639 LOOP --for each program attempt
640
641 --Check if the stundent has a relevant program attempt
642 OPEN c_stdnt_ps_att (l_person_id,l_bulk_programs.program_cd,l_bulk_programs.program_ver_num);
643 FETCH c_stdnt_ps_att INTO l_stdnt_ps_att;
644
645 IF c_stdnt_ps_att%NOTFOUND THEN
646 l_error_message :='IGS_EN_NO_ACTIVE_PROGRAM';
647 ELSIF l_stdnt_ps_att.course_attempt_status=cst_unconfirm THEN
648 l_error_message :='IGS_EN_FAIL_SUA_DUE_PROG_UNCR';
649 ELSIF l_stdnt_ps_att.course_attempt_status=cst_intermit THEN
650 l_error_message :='IGS_EN_FAIL_SUA_DUE_PROG_INT';
651 ELSIF l_stdnt_ps_att.course_attempt_status IN (cst_discontin,cst_completed) THEN
652 l_error_message :='IGS_EN_SUA_SPA_MISMTCH';
653 ELSE
654 l_error_message:=null;
655 END IF;
656
657 IF l_error_message IS NOT NULL THEN
658 --Applicable Program attempt not found
659 enrpl_log_msg(p_level =>'E',
660 p_message =>l_error_message,
661 p_batch_id => p_batch_id,
662 p_person_number=>l_bulk_persons.person_number,
663 p_program_cd=>l_bulk_programs.program_cd,
664 p_program_ver=>l_bulk_programs.program_ver_num);
665 ELSE -- student has prog attempt
666
667 FOR l_bulk_load IN c_bulk_load (p_batch_id,l_bulk_persons.person_number,l_bulk_programs.program_cd,l_bulk_programs.program_ver_num) LOOP --load calendars
668 SAVEPOINT blk_sc_load_lvl;
669 --group header
670 fnd_file.put_line(Fnd_File.LOG, rpad(' ',80,'_'));
671 fnd_message.set_name('IGS','IGS_EN_BLK_GRP_INF');
672 fnd_message.set_token('PERNUM', l_bulk_persons.person_number);
673 fnd_message.set_token('PRGCD',l_bulk_programs.program_cd);
674 fnd_message.set_token('LOADALTCD',l_bulk_load.alt_cd);
675 fnd_file.put_line(Fnd_File.LOG,fnd_message.get);
676 fnd_file.put_line(Fnd_File.LOG, rpad(' ',80,'_'));
677
678 l_abort_loop:=FALSE;
679 l_error_message:=null;
680 -- check whether the program is PRIMARY or not by considering Term Records
681 -- If it's not an PRIMARY program in career mode then log message and stop processing
682 -- for the current unit by assigning l_abort_loop to TRUE and proceed for the unit for
683 -- the corresponding load cal type and sequence number.
684 -- added by ckasu as a part of bug # 4095479
685 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' AND
686 igs_en_spa_terms_api.get_spat_primary_prg(l_person_id,
687 l_bulk_programs.program_cd,
688 l_bulk_load.cal_type,
689 l_bulk_load.seq_num) <> cst_primary THEN
690 l_error_message :='IGS_EN_ENR_UNT_SEC_PRGM';
691 END IF;
692
693 IF l_error_message IS NOT NULL THEN
694 enrpl_log_msg(p_level =>'E',
695 p_message =>l_error_message,
696 p_batch_id => p_batch_id,
697 p_person_number =>l_bulk_persons.person_number,
698 p_program_cd =>l_bulk_programs.program_cd,
699 p_program_ver =>l_bulk_programs.program_ver_num,
700 p_load_alt_code =>l_bulk_load.alt_cd);
701
702 l_abort_loop:=TRUE;
703 END IF; -- end if statement that logs message when program type is not an Pprimary program
704
705 l_error_message:=null;
706
707 IF l_abort_loop=FALSE THEN
708
709 l_acad_alt_cd := igs_en_gen_002.enrp_get_acad_alt_cd(p_cal_type =>l_bulk_load.cal_type,
710 p_ci_sequence_number =>l_bulk_load.seq_num,
711 p_acad_cal_type => l_acad_cal_type,
712 p_acad_ci_sequence_number =>l_acad_seq_num,
713 p_acad_ci_start_dt => l_acad_start_dt,
714 p_acad_ci_end_dt => l_acad_end_dt,
715 p_message_name => l_error_message);
716 IF l_error_message IS NOT NULL THEN
717 enrpl_log_msg(p_level =>'E',
718 p_message =>l_error_message,
719 p_batch_id =>p_batch_id,
720 p_person_number =>l_bulk_persons.person_number,
721 p_program_cd =>l_bulk_programs.program_cd,
722 p_program_ver =>l_bulk_programs.program_ver_num,
723 p_load_alt_code =>l_bulk_load.alt_cd);
724
725 l_abort_loop:=TRUE;
726 END IF; --found/not found acad calendar
727
728 END IF; -- end of IF THEN getting Academic calendar
729
730 l_ret_status:=null;l_error_message:=null;
731
732 IF l_abort_loop=FALSE THEN
733 --derive enrollment category and commencement type
734 l_enr_category :=igs_en_gen_003.enrp_get_enr_cat( p_person_id =>l_person_id,
735 p_course_cd =>l_bulk_programs.program_cd,
736 p_cal_type =>l_acad_cal_type,
737 p_ci_sequence_number =>l_acad_seq_num,
738 p_session_enrolment_cat =>NULL,
739 p_enrol_cal_type =>l_enr_cal_type,
740 p_enrol_ci_sequence_number =>l_enr_seq_num,
741 p_commencement_type =>l_commencement_type,
742 p_enr_categories =>l_enr_categories);
743
744 --Validate the applicable advising holds
745 igs_en_elgbl_person.eval_ss_deny_all_hold (p_person_id =>l_person_id,
746 p_person_type =>l_person_type,
747 p_course_cd =>l_bulk_programs.program_cd,
748 p_load_calendar_type =>l_bulk_load.cal_type,
749 p_load_cal_sequence_number =>l_bulk_load.seq_num,
750 p_status =>l_ret_status,
751 p_message =>l_error_message);
752
753 IF l_ret_status='E' THEN --person steps validations
754 enrpl_log_msg(p_level =>'E',
755 p_message =>l_error_message,
756 p_batch_id => p_batch_id,
757 p_person_number =>l_bulk_persons.person_number,
758 p_program_cd =>l_bulk_programs.program_cd,
759 p_program_ver =>l_bulk_programs.program_ver_num,
760 p_load_alt_code =>l_bulk_load.alt_cd);
761 l_abort_loop:=TRUE;
762 END IF;
763 l_ret_status:=null;l_error_message:=null;
764 END IF; --l_abort_loop
765
766
767 IF l_abort_loop=FALSE THEN
768 --Evaluate the applicable person steps
769 IF FALSE=igs_en_elgbl_person.eval_person_steps ( p_person_id =>l_person_id,
770 p_person_type =>l_person_type,
771 p_load_calendar_type =>l_bulk_load.cal_type,
772 p_load_cal_sequence_number =>l_bulk_load.seq_num,
773 p_program_cd =>l_bulk_programs.program_cd,
774 p_program_version =>l_bulk_programs.program_ver_num,
775 p_enrollment_category =>l_enr_category,
776 p_comm_type =>l_commencement_type,
777 p_enrl_method =>l_enr_method,
778 p_message =>l_error_message,
779 p_deny_warn =>l_deny_warn,
780 p_calling_obj =>'JOB',
781 p_create_warning =>'N') THEN
782 enrpl_log_msg(p_level =>'E',
783 p_message =>l_error_message,
784 p_batch_id => p_batch_id,
785 p_person_number =>l_bulk_persons.person_number,
786 p_program_cd =>l_bulk_programs.program_cd,
787 p_program_ver =>l_bulk_programs.program_ver_num,
788 p_load_alt_code =>l_bulk_load.alt_cd);
789 l_abort_loop:=TRUE;
790 ELSE
791 IF l_error_message IS NOT NULL THEN
792 --log enrollment category validation warnings and continue
793 enrpl_log_msg(p_level =>'W1', --W1 is group level warning before the sua is inserted..thus status should not be changed
794 p_message =>l_error_message,
795 p_batch_id => p_batch_id,
796 p_person_number =>l_bulk_persons.person_number,
797 p_program_cd =>l_bulk_programs.program_cd,
798 p_program_ver =>l_bulk_programs.program_ver_num,
799 p_load_alt_code =>l_bulk_load.alt_cd);
800 END IF;
801 END IF;
802 l_ret_status:=null;l_error_message:=null;
803 END IF; --l_abort_loop
804
805 l_sup_units_list:=NULL;
806 l_msg_rec_ind:=0;
807 l_uoo_ids_list:=NULL;
808
809 IF l_abort_loop=FALSE THEN
810
811 FOR l_teach_cal_dtls IN c_get_teach_cal_dtls(l_bulk_load.cal_type,l_bulk_load.seq_num) LOOP
812 FOR l_bulk_suas IN c_bulk_suas (p_batch_id,l_bulk_persons.person_number,l_bulk_programs.program_cd,l_bulk_programs.program_ver_num,
813 l_teach_cal_dtls.teach_cal_type,l_teach_cal_dtls.teach_ci_sequence_number,l_teach_cal_dtls.teach_alternate_code)
814 LOOP --Student unit attempts
815 l_abort_loop:=FALSE;
816 l_core_indicator_code:=NULL;
817 --If an appropriate uoo is not found then uoo_info column will be null
818 IF l_bulk_suas.uoo_info IS NULL THEN
819 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_UNIT_OFR_OPT_NT_FND');
820 l_abort_loop:=TRUE;
821 END IF;
822
823 --Derive all unit offering option from the concatenated string
824 enrp_decode_uoo_info (l_bulk_suas.uoo_info,l_uoo_id,l_rel_type,l_chk_au_allowed,l_usec_status,l_sup_unit);
825
826 --Validate the core_indicator_code column if update_core_flag is set
827 IF l_abort_loop=FALSE AND l_bulk_suas.update_core_flag='Y' THEN
828 IF FALSE=igs_lookups_view_pkg.get_pk_for_validation ('IGS_PS_CORE_IND', l_bulk_suas.core_indicator_code) THEN
829 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_CORE_IND_INVALID');
830 l_abort_loop:=TRUE;
831 END IF;
832 END IF;
833
834 --Check the status of the unit ofr option
835 IF l_abort_loop=FALSE THEN
836 IF l_usec_status IN (cst_planned,cst_cancelled,cst_notofr) THEN
837 l_abort_loop:=TRUE;
838 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_FAIL_SUA_DUE_INV_SUA');
839 END IF;
840 END IF; --l_abort_loop
841
842
843 IF l_abort_loop=FALSE THEN
844 --is uoo already attempted
845 OPEN c_sua_exists(l_person_id,l_bulk_programs.program_cd,l_uoo_id);
846 FETCH c_sua_exists INTO l_sua_exists;
847 CLOSE c_sua_exists;
848 IF l_sua_exists='Y' THEN
849 IF l_bulk_suas.update_core_flag='Y' THEN
850 IF NVL(FND_PROFILE.VALUE('IGS_EN_CORE_VAL'),'N') = 'Y' THEN
851 --If profile is set then Derive core indicator from POS and log warning if it is being overriden
852 l_core_indicator_code:=Igs_En_Gen_009.enrp_check_usec_core (p_person_id =>l_person_id,
853 p_program_cd =>l_bulk_programs.program_cd,
854 p_uoo_id =>l_uoo_id);
855 IF l_core_indicator_code <> l_bulk_suas.core_indicator_code THEN
856 enrpl_unit_msg('W',l_bulk_suas.upload_id,'IGS_EN_POS_MATCH');
857 END IF;
858 END IF;--profile check
859 --Override the core indicator for the existing unit attempt
860 enrl_upd_core_ind (p_person_id => l_person_id,
861 p_course_cd => l_bulk_programs.program_cd,
862 p_uoo_id => l_uoo_id ,
863 p_core_ind => l_bulk_suas.core_indicator_code);
864 enrpl_unit_msg('S',l_bulk_suas.upload_id,'IGS_EN_SUA_UPDATE_SUCC');
865 l_abort_loop:=TRUE;
866 ELSE
867 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_UNT_ATMPT_EXTS');
868 l_abort_loop:=TRUE;
869 END IF;
870
871 ELSIF --uoo is not attempted -- Variation Window Validation
872 FALSE= igs_en_gen_008.enrp_get_var_window(p_cal_type => l_teach_cal_dtls.teach_cal_type,
873 p_ci_sequence_number => l_teach_cal_dtls.teach_ci_sequence_number,
874 p_effective_dt => SYSDATE,
875 p_uoo_id => l_uoo_id ) THEN
876 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_CANT_UPD_OUTS_ENRL');
877 l_abort_loop:=TRUE;
878 ELSE --uoo is not attempted -- Variation Window Validation passed-- Validation for the Enrollment window
879 IF FALSE=igs_en_gen_004.enrp_get_rec_window (p_cal_type => l_teach_cal_dtls.teach_cal_type,
880 p_ci_sequence_number => l_teach_cal_dtls.teach_ci_sequence_number,
881 p_effective_date => SYSDATE,
882 p_uoo_id => l_uoo_id,
883 p_message_name => l_error_message ) THEN
884 enrpl_unit_msg ('E',l_bulk_suas.upload_id,l_error_message);
885 l_abort_loop:=TRUE;
886 END IF;
887 END IF;
888 l_ret_status:=null;l_error_message:=null;
889 END IF; --l_abort_loop
890
891
892 IF l_abort_loop=FALSE THEN
893 --uoo is not attempted - Variation Window Validation and Enrollment window Validation passed
894 --get Availability
895 l_usec_status:=null;
896 igs_en_gen_015.get_usec_status(p_uoo_id =>l_uoo_id,
897 p_person_id =>l_person_id,
898 p_unit_section_status =>l_usec_status,
899 p_waitlist_ind =>l_waitlist_ind,
900 p_load_cal_type =>l_bulk_load.cal_type,
901 p_load_ci_sequence_number =>l_bulk_load.seq_num,
902 p_course_cd =>l_bulk_programs.program_cd);
903 IF l_waitlist_ind IS NULL THEN
904 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_FAIL_SUA_NO_SEATS');
905 l_abort_loop:=TRUE;
906 END IF;
907 END IF; --l_abort_loop
908
909 IF l_abort_loop=FALSE AND l_bulk_suas.audit_flag='Y' THEN
910 IF l_chk_au_allowed='N' THEN
911 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_CANNOT_AUDIT');
912 l_abort_loop:=TRUE;
913 END IF;
914 END IF;
915
916 IF l_abort_loop=FALSE AND
917 l_bulk_suas.variable_cr_point IS NOT NULL THEN --checking variable credit point
918 IF l_bulk_suas.audit_flag='Y' THEN
919 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_OVR_CP_AUD');
920 l_abort_loop:=TRUE;
921 ELSIF 'N'=igs_en_gen_008.enrp_val_chg_cp (p_person_id =>l_person_id, --override allowed?
922 p_uoo_id =>l_uoo_id,
923 p_cal_type =>l_teach_cal_dtls.teach_cal_type,
924 p_ci_sequence_number =>l_teach_cal_dtls.teach_ci_sequence_number) THEN
925 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_FAIL_SUA_VARCP_NOT_AWD');
926 l_abort_loop:=TRUE;
927 ELSE
928 IF FALSE=igs_en_val_sua.enrp_val_sua_ovrd_cp(p_unit_cd =>l_bulk_suas.unit_cd, --within min/max CP?
929 p_version_number =>l_bulk_suas.unit_ver_num,
930 p_override_enrolled_cp =>l_bulk_suas.variable_cr_point,
931 p_override_achievable_cp => NULL,
932 p_override_eftsu => NULL,
933 p_message_name => l_error_message,
934 p_uoo_id =>l_uoo_id,
935 p_no_assessment_ind =>l_bulk_suas.audit_flag) THEN
936 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_FAIL_SUA_VARCP_NOT_VLD');
937 l_abort_loop:=TRUE;
938 END IF;
939 END IF;
940 l_ret_status:=null;l_error_message:=null;
941 END IF; --checking variable credit point --l_abort_loop
942
943
944 IF l_abort_loop=FALSE AND -- var_cr_point validation was passed or not applicable
945 l_bulk_suas.grading_sch_cd IS NOT NULL THEN -- checking grading schema
946 IF FALSE=igs_en_gen_008.enrp_val_chg_grd_sch (p_uoo_id =>l_uoo_id,
947 p_cal_type =>l_teach_cal_dtls.teach_cal_type,
948 p_ci_sequence_number =>l_teach_cal_dtls.teach_ci_sequence_number,
949 p_message_name =>l_error_message) THEN
950 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_FAIL_SUA_VARGRD_NOT_AWD');
951 l_abort_loop:=TRUE;
952 ELSE
953 IF FALSE=igs_ss_en_wrappers.enr_val_grad_usec(p_uoo_ids => l_uoo_id,
954 p_grading_schema_code => l_bulk_suas.grading_sch_cd,
955 p_gs_version_number => l_bulk_suas.grading_sch_ver_num) THEN
956 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_FAIL_SUA_VARGRD_NOT_VLD');
957 l_abort_loop:=TRUE;
958 END IF;
959 END IF;
960 l_ret_status:=null;l_error_message:=null;
961 END IF;-- checking grading schema --l_abort_loop
962
963
964 IF l_abort_loop=FALSE AND --Grading schema validation was passed or not applicable
965 l_bulk_suas.unit_sec_sub_title IS NOT NULL THEN
966 IF 'N'= igs_ss_enr_details.enrp_val_subttl_chg(p_person_id => l_person_id,p_uoo_id =>l_uoo_id) THEN
967 l_abort_loop:=TRUE;
968 enrpl_unit_msg ('E',l_bulk_suas.upload_id,'IGS_EN_FAIL_SUA_SUBTIT_NOT_AWD');
969 END IF; --subtitle validation
970 END IF; --l_abort_loop
971
972 IF l_abort_loop=FALSE THEN --Subtitile validation was passed or not applicable
973 --populate core indicator from POS if profile is set
974 IF NVL(FND_PROFILE.VALUE('IGS_EN_CORE_VAL'),'N') = 'Y' THEN
975 l_core_indicator_code:=Igs_En_Gen_009.enrp_check_usec_core (p_person_id =>l_person_id,
976 p_program_cd =>l_bulk_programs.program_cd,
977 p_uoo_id =>l_uoo_id);
978 END IF;
979 --override from interface table if flag is set
980 IF l_bulk_suas.update_core_flag='Y' THEN
981 IF l_core_indicator_code <> l_bulk_suas.core_indicator_code AND NVL(FND_PROFILE.VALUE('IGS_EN_CORE_VAL'),'N') = 'Y' THEN
982 enrpl_unit_msg('W',l_bulk_suas.upload_id,'IGS_EN_POS_MATCH');
983 END IF;
984 l_core_indicator_code:=l_bulk_suas.core_indicator_code;
985 END IF;
986 END IF; --l_abort_loop
987
988 IF l_abort_loop=FALSE THEN --Core indicator populated appropriately, proceed to insert sua
989 SAVEPOINT bulk_sua_upload;
990 BEGIN
991 igs_ss_en_wrappers.insert_into_enr_worksheet(p_person_number =>l_bulk_persons.person_number,
992 p_course_cd =>l_bulk_programs.program_cd,
993 p_uoo_id =>l_uoo_id,
994 p_waitlist_ind =>l_waitlist_ind,
995 p_session_id =>NULL,
996 p_return_status =>l_ret_status,
997 p_message =>l_error_message,
998 p_cal_type =>l_bulk_load.cal_type,
999 p_ci_sequence_number =>l_bulk_load.seq_num,
1000 p_audit_requested =>l_bulk_suas.audit_flag,
1001 p_enr_method =>l_enr_method,
1002 p_override_cp =>l_bulk_suas.variable_cr_point,
1003 p_subtitle =>l_bulk_suas.unit_sec_sub_title,
1004 p_gradsch_cd =>l_bulk_suas.grading_sch_cd,
1005 p_gs_version_num =>l_bulk_suas.grading_sch_ver_num,
1006 p_core_indicator_code =>l_core_indicator_code,
1007 p_calling_obj =>'JOB');
1008 EXCEPTION WHEN OTHERS THEN
1009 --When an exception is raised..get the exception text and store it in l_error_message
1010 IF IGS_GE_MSG_STACK.COUNT_MSG <> 0 THEN
1011 l_error_message := FND_MESSAGE.GET;
1012 ELSE
1013 l_error_message := SQLERRM;
1014 END IF;
1015 l_ret_status := 'D';
1016 END;
1017
1018 --Removing the coreq warning message from the error messages
1019 l_pos:=INSTR(l_error_message,'IGS_SS_WARN_COREQ',1);
1020 IF l_pos=0 THEN
1021 l_pos:=INSTR(l_error_message,'IGS_SS_DENY_COREQ',1);
1022 END IF;
1023 IF l_pos<>0 THEN
1024 l_pos1:=INSTR(l_error_message,';',l_pos);
1025 IF l_pos1<>0 THEN
1026 l_error_message:=SUBSTR(l_error_message,1,l_pos-1)||SUBSTR(l_error_message,INSTR(l_error_message,';',l_pos+1)+1);
1027 ELSE
1028 l_error_message:=SUBSTR(l_error_message,1,l_pos-1); --COREQ is the last message
1029 END IF;
1030 END IF;
1031
1032
1033 IF l_ret_status='D' THEN
1034 enrpl_unit_msg ('E',l_bulk_suas.upload_id,l_error_message);
1035 ROLLBACK TO bulk_sua_upload;
1036 l_abort_loop:=TRUE;
1037 ELSIF l_error_message IS NOT NULL THEN
1038 enrpl_unit_msg ('W',l_bulk_suas.upload_id,l_error_message);
1039 ELSE
1040 enrpl_unit_msg ('S',l_bulk_suas.upload_id,'IGS_EN_UA_SECCESS_ADDED_STUD');
1041 END IF;
1042 IF l_ret_status<>'D' AND l_waitlist_ind='N' AND p_dflt_unit_confirmed='Y' THEN
1043 IF l_uoo_ids_list IS NOT NULL THEN
1044 l_uoo_ids_list:=l_uoo_ids_list||',';
1045 END IF;
1046 l_uoo_ids_list:=l_uoo_ids_list||l_uoo_id;
1047 END IF;
1048 l_ret_status:=null;l_error_message:=null;
1049 END IF; --l_abort_loop
1050
1051 IF l_rel_type='SUPERIOR' AND l_abort_loop=FALSE THEN
1052 --Add to the list of superiors for which default enroll is to be done
1053 --The commas on either side are necessary to allow proper string search
1054 --The msg_rec_ind is included so that the subordinate success/failure messages
1055 --can be logged against the same upload
1056 l_sup_units_list:=NVL(l_sup_units_list,',')||l_uoo_id||'*'||(l_msg_rec_ind-1)||',';
1057 END IF;
1058 IF l_rel_type='SUBORDINATE' THEN
1059 --Remove its superior as default enroll should not be done if even one subordinate is given by the user.
1060 --Note no l_abort_loop check as even if user TRIES to enroll subordinate and it Errors out
1061 --no default subs are processed
1062 l_pos:=INSTR(l_sup_units_list,','||l_sup_unit||'*');
1063 IF l_pos<>0 THEN
1064 l_pos1:=INSTR(l_sup_units_list,',',l_pos+1);
1065 l_sup_units_list:=SUBSTR(l_sup_units_list,1,l_pos)||SUBSTR(l_sup_units_list,l_pos1+1); --Note..one of the commas is retained
1066 END IF;
1067 END IF;
1068 l_abort_loop:=FALSE;
1069 END LOOP; --Student unit attempts
1070 END LOOP; -- end of FOR l_teach_cal_dtls IN c_get_teach_cal_dtls THEN
1071 END IF; --l_abort_loop before the l_bulk_Suas main for loop
1072
1073 --All the suas have been processed. Now for each of the superiors for which no
1074 --subordinate was imported,enroll the default subordinates
1075 IF l_abort_loop =FALSE THEN
1076 l_pos1:=1;
1077 --processing superior uooids from concatenated string
1078 LOOP
1079 l_pos:=l_pos1;
1080 l_pos1:=INSTR(l_sup_units_list,',',l_pos+1);
1081 EXIT WHEN NVL(l_pos1,0)=0;
1082 l_pos2:=INSTR(l_sup_units_list,'*',l_pos+1);
1083 l_uoo_id:=SUBSTR (l_sup_units_list, --search the list eg: uooid=1009,msg_rec=5 then record is '%,1009*5,%'
1084 l_pos+1, --starting in the current comma separated record
1085 l_pos2-l_pos-1); --ending just before the '*' after which l_msg_rec_ind appears
1086
1087 --get the record number in the l_message_table table
1088 l_save_message_no:=SUBSTR(l_sup_units_list, --search the list
1089 l_pos2+1, --starting just after '*'
1090 l_pos1-l_pos2-1); --ending just before the ','
1091
1092 l_sub_success:=NULL;l_sub_waitlist:=NULL;l_sub_failed:=NULL;
1093 igs_en_val_sua.enr_sub_units(
1094 p_person_id => l_person_id,
1095 p_course_cd => l_bulk_programs.program_cd,
1096 p_uoo_id => l_uoo_id,
1097 p_waitlist_flag => l_waitlist_ind,
1098 p_load_cal_type => l_bulk_load.cal_type,
1099 p_load_seq_num => l_bulk_load.seq_num,
1100 p_enrollment_date => SYSDATE,
1101 p_enrollment_method => l_enr_method,
1102 p_enr_uoo_ids => NULL,
1103 p_uoo_ids => l_sub_success,
1104 p_waitlist_uoo_ids => l_sub_waitlist,
1105 p_failed_uoo_ids => l_sub_failed);
1106 IF l_sub_success IS NOT NULL THEN
1107 l_message_table(l_save_message_no).message:= l_message_table(l_save_message_no).message||';'||'IGS_EN_BLK_SUB_SUCCESS*'||igs_en_gen_018.enrp_get_unitcds(l_sub_success);
1108 --Since this message reads "Subordinates have been added", the message needs to be overwritten if
1109 --validate_enroll_validate fails. If 'W' is the status, then the error message is only appended to the list of warnings
1110 --If 'S' is the status, the current messages are replaced with the error message.IGS_EN_BLK_SUB_SUCCESS needs to be replaced
1111 IF l_message_table(l_save_message_no).type='W' THEN
1112 l_message_table(l_save_message_no).message:= l_message_table(l_save_message_no).message||';IGS_EN_UA_SECCESS_ADDED_STUD';
1113 l_message_table(l_save_message_no).type:='S';
1114 END IF;
1115 IF l_uoo_ids_list IS NOT NULL THEN
1116 l_uoo_ids_list:=l_uoo_ids_list||',';
1117 END IF;
1118 l_uoo_ids_list:=l_uoo_ids_list||l_sub_success;
1119 END IF;
1120 IF l_sub_failed IS NOT NULL THEN
1121 l_message_table(l_save_message_no).message:= l_message_table(l_save_message_no).message||';'||'IGS_EN_BLK_SUB_FAILED*'||igs_en_gen_018.enrp_get_unitcds(l_sub_failed);
1122 END IF;
1123 END LOOP;--processing superior uooids from concatenated string
1124 END IF; --l_abort_loop
1125
1126 --Program Level Validations
1127 IF p_dflt_unit_confirmed='Y' AND l_abort_loop=FALSE THEN
1128 --Confirm the sua if so specified
1129 BEGIN
1130 igs_ss_en_wrappers.validate_enroll_validate ( p_person_id => l_person_id,
1131 p_load_cal_type =>l_bulk_load.cal_type,
1132 p_load_ci_sequence_number =>l_bulk_load.seq_num,
1133 p_uoo_ids =>l_uoo_ids_list,
1134 p_program_cd =>l_bulk_programs.program_cd,
1135 p_message_name =>l_error_message,
1136 p_deny_warn =>l_deny_warn,
1137 p_return_status =>l_ret_status,
1138 p_enr_method =>l_enr_method,
1139 p_enrolled_dt =>SYSDATE);
1140 EXCEPTION WHEN OTHERS THEN
1141 --If an exception is thrown, get the error message text and save it in the message name variable
1142 IF IGS_GE_MSG_STACK.COUNT_MSG <> 0 THEN
1143 l_error_message := FND_MESSAGE.GET;
1144 ELSE
1145 l_error_message := SQLERRM;
1146 END IF;
1147 l_deny_warn := 'DENY';
1148 l_ret_status := 'FALSE';
1149 END;
1150
1151 IF l_ret_status='FALSE' AND l_deny_warn ='DENY' THEN
1152 --Progam level validations failed
1153 enrpl_log_msg(p_level =>'E',
1154 p_message =>l_error_message,
1155 p_batch_id => p_batch_id,
1156 p_person_number =>l_bulk_persons.person_number,
1157 p_program_cd =>l_bulk_programs.program_cd,
1158 p_program_ver =>l_bulk_programs.program_ver_num,
1159 p_load_alt_code =>l_bulk_load.alt_cd);
1160 ROLLBACK TO blk_sc_load_lvl;
1161 enrpl_save_unit_msg(l_error_message); --override the messages in successfully processed uploads as well
1162 ELSE
1163 --Warning in program level validations
1164 IF l_error_message IS NOT NULL THEN
1165 enrpl_log_msg(p_level =>'W',
1166 p_message =>l_error_message,
1167 p_batch_id => p_batch_id,
1168 p_person_number =>l_bulk_persons.person_number,
1169 p_program_cd =>l_bulk_programs.program_cd,
1170 p_program_ver =>l_bulk_programs.program_ver_num,
1171 p_load_alt_code =>l_bulk_load.alt_cd);
1172 END IF;
1173 enrpl_save_unit_msg(NULL);
1174 END IF;
1175 ELSE
1176 -- dflt_unit_confirmed not set...just save upload ids statuses and error messages
1177 IF l_abort_loop=FALSE THEN
1178 enrpl_save_unit_msg(NULL);
1179 END IF;
1180 END IF;--person level validations done only if p_dflt_unit_confirmed is set --l_abort_loop
1181 l_ret_status:=null;l_error_message:=null;
1182 END LOOP; --bulk loadcals
1183 END IF; -- program attempt exist validation
1184 CLOSE c_stdnt_ps_att;
1185 END LOOP; --programs
1186 END IF;-- if l_personid_ is null
1187 END LOOP; --persons
1188
1189 --Log entries with invalid teach alt codes
1190 l_msg_rec_ind:=0;
1191 FOR l_invalid_teach IN c_invalid_teach (p_batch_id)
1192 LOOP
1193 enrpl_unit_msg ('E',l_invalid_teach.upload_id,'IGS_EN_ALT_CD_NO_CAL_FND');
1194 END LOOP;
1195
1196 --Log entries with invalid load cal relationships
1197 FOR l_invalid_t2l IN c_invalid_teach2load (p_batch_id)
1198 LOOP
1199 enrpl_unit_msg ('E',l_invalid_t2l.upload_id,'IGS_EN_BULK_E_D_NO_CAL_REL');
1200 END LOOP;
1201 IF l_msg_rec_ind>0 THEN
1202 enrpl_save_unit_msg(NULL);
1203 l_processed_rec:=TRUE;
1204 END IF;
1205
1206 IF l_processed_rec=FALSE THEN
1207 fnd_file.put_line(Fnd_File.LOG, rpad(' ',80,'_'));
1208 fnd_file.put_line(Fnd_File.LOG,fnd_message.get_string('IGS','IGS_EN_BLK_NO_RECS'));
1209 fnd_file.put_line(Fnd_File.LOG, rpad(' ',80,'_'));
1210 END IF;
1211
1212 --Delete the successfully processed records from the interface table if required
1213 IF p_deletion_flag='Y' THEN
1214 DELETE FROM igs_en_blk_sua_ints WHERE batch_id=p_batch_id AND status_flag='S';
1215 OPEN c_delete_bat (p_batch_id);
1216 FETCH c_delete_bat INTO l_dummy;
1217 IF c_delete_bat%NOTFOUND THEN
1218 DELETE FROM igs_en_bat_sua_ints WHERE batch_id=p_batch_id;
1219 END IF;
1220 CLOSE c_delete_bat ;
1221 END IF;
1222
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 retcode:=2;
1226 ROLLBACK TO blk_sua_job;
1227 fnd_file.put_line(fnd_file.LOG,SQLERRM);
1228 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
1229 FND_MESSAGE.SET_TOKEN('NAME','Igs_En_Gen_018.Enrp_batch_sua_upload');
1230 IGS_GE_MSG_STACK.ADD;
1231 igs_ge_msg_stack.conc_exception_hndl;
1232 END enrp_batch_sua_upload;
1233
1234 FUNCTION enrp_get_uoo_info (
1235 p_unit_cd IN VARCHAR2,
1236 p_unit_ver IN NUMBER,
1237 p_cal_type IN VARCHAR2,
1238 p_ci_sequence_number IN NUMBER,
1239 p_location_cd IN VARCHAR2,
1240 p_unit_class IN VARCHAR2)
1241 RETURN VARCHAR2 AS
1242 ------------------------------------------------------------------
1243 --Created by : rvivekan, Oracle IDC
1244 --Date created: 22-oct-2003
1245 --
1246 --Purpose: This Function returns various uoo information in the form of
1247 -- and encoded string RelationType*UooId*UnitSectionStatus*AuditableInd*SuperiorUooId
1248 --
1249 --
1250 --Known limitations/enhancements and/or remarks:
1251 --
1252 --Change History:
1253 --Who When What
1254 --
1255 -------------------------------------------------------------------
1256
1257 CURSOR c_get_uoo ( cp_unit_cd IN VARCHAR2,
1258 cp_unit_ver IN NUMBER,
1259 cp_cal_type IN VARCHAR2,
1260 cp_ci_sequence_number IN NUMBER,
1261 cp_location_cd IN VARCHAR2,
1262 cp_unit_class IN VARCHAR2) IS
1263 SELECT NVL(relation_type,'NONE')||'*'||uoo_id||'*'||unit_section_status||'*'||NVL(auditable_ind,'N')||'*'||sup_uoo_id uoo_info
1264 -- Gives all required uoo information
1265 FROM igs_ps_unit_ofr_opt
1266 WHERE unit_cd = cp_unit_cd AND version_number = cp_unit_ver
1267 AND cal_type = cp_cal_type AND ci_sequence_number = cp_ci_sequence_number
1268 AND location_cd = cp_location_cd AND unit_class = cp_unit_class;
1269
1270 l_uoo_info VARCHAR2(500);
1271 BEGIN
1272 l_uoo_info:=NULL;
1273 OPEN c_get_uoo (p_unit_cd,p_unit_ver,p_cal_type,p_ci_sequence_number,p_location_cd,p_unit_class);
1274 FETCH c_get_uoo INTO l_uoo_info;
1275 CLOSE c_get_uoo;
1276 RETURN l_uoo_info;
1277 END enrp_get_uoo_info ;
1278
1279
1280
1281 PROCEDURE enrp_decode_uoo_info (
1282 p_uoo_info IN VARCHAR2,
1283 p_uoo_id OUT NOCOPY NUMBER,
1284 p_rel_type OUT NOCOPY VARCHAR2,
1285 p_audit_allowed OUT NOCOPY VARCHAR2,
1286 p_usec_status OUT NOCOPY VARCHAR2,
1287 p_sup_unit OUT NOCOPY VARCHAR2
1288 ) AS
1289 ------------------------------------------------------------------
1290 --Created by : rvivekan, Oracle IDC
1291 --Date created: 22-oct-2003
1292 --
1293 --Purpose: This procedure decodes the string returned by enrp_get_uoo_info
1294 --
1295 --
1296 --
1297 --Known limitations/enhancements and/or remarks:
1298 --
1299 --Change History:
1300 --Who When What
1301 --
1302 -------------------------------------------------------------------
1303 l_pos NUMBER;
1304 l_pos1 NUMBER;
1305
1306 BEGIN
1307 l_pos:=INSTR(p_uoo_info,'*');
1308 l_pos1:=INSTR(p_uoo_info,'*',l_pos+1);
1309 p_rel_type:=SUBSTR(p_uoo_info,1,l_pos-1); -- Relation type Superior/subordinate/none
1310 p_uoo_id:=SUBSTR(p_uoo_info,l_pos+1,l_pos1-1-l_pos); -- Uoo Id
1311 l_pos:=INSTR(p_uoo_info,'*',l_pos1+1);
1312 p_usec_status:=SUBSTR(p_uoo_info,l_pos1+1,l_pos-1-l_pos1); -- Uoo Status OPEN/FULLWAITOK etc
1313 p_audit_allowed:=SUBSTR(p_uoo_info,l_pos+1,1); -- Audit allowed
1314 p_sup_unit:=SUBSTR(p_uoo_info,l_pos+3); -- Superior Unit Cd
1315 END enrp_decode_uoo_info ;
1316
1317
1318
1319
1320 FUNCTION enrp_get_unitcds ( p_uoo_ids IN VARCHAR2)
1321 RETURN VARCHAR2 IS
1322 ------------------------------------------------------------------
1323 --Created by : rvivekan, Oracle IDC
1324 --Date created: 22-oct-2003
1325 --
1326 --Purpose: to translate comma separated uooids to comma separated unitcds
1327 --
1328 --
1329 --
1330 --Known limitations/enhancements and/or remarks:
1331 --
1332 --Change History:
1333 --Who When What
1334 --
1335 -------------------------------------------------------------------
1336
1337
1338 CURSOR c_get_unit_cd (cp_uoo_id NUMBER) IS
1339 SELECT unit_cd
1340 FROM igs_ps_unit_ofr_opt
1341 WHERE uoo_id=cp_uoo_id;
1342
1343
1344 l_uoo_ids_list VARCHAR2(2000);
1345 l_unit_cds VARCHAR2(2000);
1346 l_unitcd VARCHAR2(500);
1347 l_uoo_id NUMBER;
1348 l_pos NUMBER;
1349 l_pos1 NUMBER;
1350 BEGIN
1351 l_uoo_ids_list:=','||p_uoo_ids||',';
1352 l_pos1:=1;
1353 LOOP
1354 l_pos:=l_pos1;
1355 l_pos1:=INSTR(l_uoo_ids_list,',',l_pos+1);
1356 EXIT WHEN NVL(l_pos1,0)=0;
1357 l_uoo_id:=SUBSTR(l_uoo_ids_list,l_pos+1,l_pos1-l_pos-1);
1358
1359 --This done as if the list contains an invalid uooid, this will prevent the
1360 --previous unitcd from getting concatenated again.
1361 l_unitcd:=NULL;
1362
1363 OPEN c_get_unit_cd(l_uoo_id);
1364 FETCH c_get_unit_cd INTO l_unitcd;
1365 CLOSE c_get_unit_Cd;
1366 IF l_unit_cds IS NOT NULL THEN
1367 l_unit_cds:=l_unit_cds||',';
1368 END IF;
1369 l_unit_cds:=l_unit_cds||l_unitcd;
1370 END LOOP;
1371 RETURN l_unit_cds;
1372 END enrp_get_unitcds;
1373
1374 END igs_en_gen_018;
1375
[Home] [Help]