1 PACKAGE BODY IGS_EN_GEN_016 AS
2 /* $Header: IGSENA1B.pls 120.5 2006/01/17 04:06:48 ckasu ship $ */
3
4
5 PROCEDURE enrp_enr_reg_upd (
6 P_BATCH_ID IN NUMBER ,
7 P_ENROLL_MTD_TYPE IN VARCHAR2
8 ) AS
9 /******************************************************************
10 Created By : SVENKATA
11 Date Created By : 31-OCT-02
12 Purpose : Validate Registration details from interface tables and update
13 the corresponding OSS tables with the new Registration Date and Unit
14 Attempt Status.
15 Known limitations,
16 enhancements,
17 remarks :
18 Change History
19 Who When What
20 ckasu 31-DEC-2004 Modified code inorder to show note when stored Transfer exists for the source program and
21 that stored transfer needs to be updated to include newly enrolled units so that this unit
22 can be transfered to the destination when the future dated transfer is processed as a part
23 of bug#4095276
24 ckasu 29-SEP-2004 Modified signature of log_regid_person_dtls procedure by adding
25 p_load_cal_type,p_load_seq_number inorder to print Load cal details
26 and also modified code to do hold,person validations only when Load
27 cal in the context has unit attempts.
28 ckasu 14-SEP-2004 Added Holds,person step validations as a part of Bug# 3823810
29 stutta 11-Feb-2004 Passing new parameter p_enrolled_dt as SYSDATE in
30 call to validate_enroll_validate.
31 rvivekan 3-Aug-2003 Bulk unit upload TD #3049009. Added 'Enrolled' status to the get_prgrm_dtls curosr definition.
32 Added error message logging for igs_en_enroll_wlst.validate_uinit_steps and igs_ss_en_wrappers.validate_enroll_validate
33 svenkata 15-Jan-2003 Bug# 2740746 - The message name IGS_PS_INVALID_PRG_CD was invalid.Changed the message to IGS_PR_INVALID_PRG_CD.
34 svenkata 31-DEC-2002 Bug# 2724288 - added values for WHO columns in the UPDATE stmnts for the interface table.
35 sgurusam 17-Jun-2005 Modified to pass aditional parameter p_calling_obj = 'JOB' p_create_warning='N' in the calls to
36 igs_en_elgbl_person.eval_person_steps
37 Modified to pass aditional parameter p_calling_obj='JOB' in the calls to
38 Igs_En_Enroll_Wlst.Validate_unit_steps
39
40 ******************************************************************/
41
42 --cursor to fetch records from interface table
43 --
44 CURSOR get_int_dtls (p_batch_id IN NUMBER) IS
45 SELECT ROWID , int.*
46 FROM igs_en_reg_upd_int int
47 WHERE int.batch_id = p_batch_id
48 FOR UPDATE NOWAIT ;
49
50 -- cursor to fetch person_id
51 --
52 CURSOR get_person_number (p_person_number IN VARCHAR2 ) IS
53 SELECT pe.person_id
54 FROM igs_pe_person_base_v pe
55 WHERE pe.person_number = p_person_number;
56
57 -- cursor to validate course_cd
58 --
59 CURSOR get_course_cd(p_course_cd IN VARCHAR2) IS
60 SELECT 'x'
61 FROM igs_ps_ver ps
62 WHERE ps.course_cd = p_course_cd ;
63
64 -- cursor to get Academic Calendar Details
65 --
66 CURSOR get_acad_cal_dtls ( p_alternate_code IN VARCHAR2) IS
67 SELECT ca.cal_type , ca.sequence_number , ca.start_dt , ca.end_dt
68 FROM igs_ca_inst ca
69 WHERE ca.alternate_code = p_alternate_code;
70
71 -- Cursor to check if multiple matches are found for a given Altenate Code .
72 --
73 CURSOR get_acad_cnt( p_alternate_code IN VARCHAR2) IS
74 SELECT COUNT(*)
75 FROM igs_ca_inst ca
76 WHERE ca.alternate_code = p_alternate_code;
77
78 --cursor to get all INACTIVE program attempts
79 --
80 CURSOR get_prgm_dtls (p_course_cd IN VARCHAR2 , p_cal_type IN VARCHAR2 , p_person_id IN NUMBER) IS
81 SELECT sca.course_cd , sca.version_number , sca.cal_type,sca.course_attempt_status
82 FROM igs_en_stdnt_ps_att_all sca
83 WHERE sca.course_cd = NVL(p_course_cd,sca.course_cd) AND
84 sca.course_attempt_status IN ('INACTIVE','ENROLLED') AND
85 sca.cal_type = p_cal_type AND
86 sca.person_id = p_person_id ;
87
88
89 -- cursor to get all Load Calendar instances for a given Academic Calendar.
90 --
91 CURSOR get_load_cal_inst (p_acad_cal_type IN VARCHAR2 , p_acad_ci_sequence_number IN NUMBER ,
92 p_start_dt IN DATE , p_end_dt IN DATE )IS
93 SELECT ci.cal_type , ci.start_dt , ci.end_dt , ci.sequence_number
94 FROM igs_ca_inst ci , igs_ca_type ct , igs_ca_stat cs , igs_ca_inst_rel cir
95 WHERE ct.cal_type = ci.cal_type
96 AND ct.s_cal_cat = 'LOAD'
97 AND cir.sup_cal_type = p_acad_cal_type
98 AND cir.sup_ci_sequence_number = p_acad_ci_sequence_number
99 AND cir.sub_cal_type = ci.cal_type
100 AND cir.suB_ci_sequence_number = ci.sequence_number
101 AND ci.cal_status = cs. cal_status
102 AND cs.s_cal_status = 'ACTIVE'
103 AND ci.start_dt >= p_start_dt
104 AND ci.end_dt <= p_end_dt;
105
106 -- cursor to get all Unit Attempts whose Teaching Calendar fall within the given Load Calendar
107 --
108 CURSOR get_sua (p_person_id IN NUMBER , p_course_cd IN VARCHAR2, p_load_cal_type IN VARCHAR2 , p_load_ci_sequence_number IN NUMBER ) IS
109 SELECT sua.uoo_id , sua.unit_cd , sua.version_number , sua.cal_type , sua.ci_sequence_number
110 FROM igs_en_su_attempt_all sua , igs_ca_load_to_teach_v calt
111 where sua.unit_attempt_status = 'UNCONFIRM'
112 AND sua.course_cd = p_course_cd
113 AND sua.cal_type = calt.teach_cal_type
114 AND sua.person_id = p_person_id
115 AND sua.ci_sequence_number = calt.teach_ci_sequence_number
116 AND calt.load_cal_type = p_load_cal_type
117 AND calt.load_ci_sequence_number = p_load_ci_sequence_number
118 ORDER BY sua.sup_unit_cd DESC;
119
120
121 --cursor to get SUA Administrative details
122 --
123 CURSOR get_as_sc_atmpt_enr (p_start_dt IN DATE , p_end_dt IN DATE , p_person_id IN NUMBER , p_course_cd IN VARCHAR2)IS
124 SELECT asa.ROWID , asa.*
125 FROM igs_as_sc_atmpt_enr asa , igs_ca_inst ci
126 WHERE ci.start_dt >= p_start_dt AND ci.end_dt <= p_end_dt
127 AND asa.cal_type = ci.cal_type
128 AND asa.ci_sequence_number = ci.sequence_number
129 AND asa.course_cd = p_course_cd
130 AND asa.person_id = p_person_id
131 FOR UPDATE NOWAIT ;
132
133
134 -- cursor to get SUA records to update Enrollment Date.
135 --
136 CURSOR get_sua_upd ( p_person_id IN NUMBER,p_course_cd IN VARCHAR, p_uoo_id IN NUMBER ) IS
137 SELECT sua.ROWID , sua.*
138 FROM igs_en_su_attempt_all sua
139 WHERE sua.person_id = p_person_id
140 AND sua.course_cd = p_course_cd
141 AND sua.uoo_id = p_uoo_id
142 FOR UPDATE NOWAIT ;
143
144
145 --Declare all local variables .
146 --
147 l_course_cd_chk VARCHAR2(1);
148 l_reg_update_int_rec igs_en_reg_upd_int%ROWTYPE;
149 l_person_id igs_pe_person.person_id%TYPE;
150 l_cnt NUMBER ;
151 l_message_name VARCHAR2(2000) := NULL;
152
153 l_message_text VARCHAR2(2000) := NULL ;
154 l_message igs_en_reg_upd_int.errors%TYPE := NULL ;
155 l_deny_warn igs_en_cpd_ext.notification_flag%TYPE;
156
157 l_prgm_inactive_chk VARCHAR2(1) := 'N';
158
159 l_acad_cal_type igs_ca_type.cal_type%TYPE;
160 l_acad_ci_Sequence_number igs_ca_inst.sequence_number%TYPE;
161 l_acad_cal_Start_dt igs_ca_inst.start_dt%TYPE;
162 l_acad_cal_end_dt igs_ca_inst.end_dt%TYPE;
163 l_load_cal_type igs_ca_type.cal_type%TYPE;
164 l_load_ci_Sequence_number igs_ca_inst.sequence_number%TYPE;
165 l_teach_cal_type igs_ca_type.cal_type%TYPE;
166 l_teach_ci_Sequence_number igs_ca_inst.sequence_number%TYPE;
167
168 l_error_status VARCHAR2(1);
169 l_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE;
170 l_course_version_number igs_en_stdnt_ps_att_all.version_number%TYPE;
171 l_del_int_rec VARCHAR2(1) := 'Y';
172
173 l_return_status VARCHAR2(10) ;
174 l_prgm_val_pass VARCHAR2(1) := 'N' ;
175 l_prgm_val_int_pass VARCHAR2(1) := 'N' ;
176 l_unit_val_pass VARCHAR2(1) := 'N' ;
177 l_uoo_ids VARCHAR2(2000) := NULL;
178 l_unitcd_and_uooid_str VARCHAR2(3000) := NULL;
179 l_pass_uoo_ids VARCHAR2(2000) := NULL;
180 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE := NULL;
181 l_get_sua_upd_rec get_sua_upd%ROWTYPE;
182 l_boolean BOOLEAN := TRUE;
183
184 l_creation_date igs_en_reg_upd_int.creation_date%TYPE;
185 l_last_update_date igs_en_reg_upd_int.last_update_date%TYPE;
186 l_created_by igs_en_reg_upd_int.created_by%TYPE;
187 l_last_updated_by igs_en_reg_upd_int.last_updated_by%TYPE;
188 l_last_update_login igs_en_reg_upd_int.last_update_login%TYPE;
189 l_request_id igs_en_reg_upd_int.request_id%TYPE;
190 l_program_id igs_en_reg_upd_int.program_id%TYPE;
191 l_program_application_id igs_en_reg_upd_int.program_application_id%TYPE;
192 l_program_update_date igs_en_reg_upd_int.program_update_date%TYPE;
193
194 -- added by ckasu as a part of bug 3823810
195 l_enrolment_cat IGS_PS_TYPE.ENROLMENT_CAT%TYPE;
196 l_processed BOOLEAN;
197 l_dummy VARCHAR2(100);
198
199 l_commencement_type VARCHAR2(20) DEFAULT NULL;
200 l_en_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
201 l_en_ci_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
202 l_person_type IGS_PE_PERSON_TYPES.person_type_code%TYPE;
203 l_sua_rec get_sua%ROWTYPE;
204
205
206
207 PROCEDURE log_error_message(
208 p_message_name VARCHAR2,
209 p_del VARCHAR2) AS
210
211 /******************************************************************
212 Created By : RVIVEKAN
213 Date Created By : 3-aug-03
214 Purpose : To Split the concatenated error messages and log them separately
215 Change History
216 Who When What
217
218 ******************************************************************************/
219 l_messages VARCHAR2(2000) := p_message_name;
220 l_mesg_name VARCHAR2(2000);
221 l_mesg_txt VARCHAR2(2000);
222 l_msg_len NUMBER ;
223 l_msg_token VARCHAR2(100);
224 l_str_place NUMBER(3);
225 BEGIN --log_error_message
226
227 IF SUBSTR(l_messages,1,1) = p_del THEN
228 l_messages := SUBSTR(l_messages,2);
229 END IF;
230 IF SUBSTR(l_messages,-1,1) <> p_del THEN
231 l_messages := l_messages||p_del;
232 END IF;
233 l_mesg_name := NULL;
234 l_msg_len:= LENGTH(l_messages);
235 FOR i IN 1 .. l_msg_len
236 LOOP
237 IF SUBSTR(l_messages,i,1) = p_del THEN
238 --Following codes checks whether message has token or not.
239 --If any call to igs_ss_en_wrappers results in a exception
240 --Then l_mesg_name contains the exception text and should not be translated
241 --Message texts are generally more than 30 chars and even if they are less
242 --than 30 chars, fnd_message returns the same text if it cant find a match.
243 --If messages larger than 30 chars (max size if msg_name field) are passed to
244 --fnd_message, an plsql exception is thrown.Hence the check for 30 characters size.
245 IF LENGTH(l_mesg_name)<=30 THEN
246 l_str_place :=INSTR(l_mesg_name,'*');
247 IF l_str_place <> 0 THEN
248 l_msg_token:= SUBSTR(l_mesg_name,l_str_place+1);
249 l_mesg_name:= SUBSTR(l_mesg_name,1,l_str_place-1);
250 fnd_message.set_name('IGS',l_mesg_name);
251 fnd_message.set_token('UNIT_CD',l_msg_token);
252 ELSE
253 fnd_message.set_name('IGS',l_mesg_name);
254 END IF;
255 l_mesg_txt:=fnd_message.get;
256 ELSE
257 l_mesg_txt:=l_mesg_name;
258 END IF; --30 character message name
259
260 Fnd_File.PUT_LINE(Fnd_File.LOG,l_mesg_txt);
261 l_mesg_name := NULL;
262 ELSE
263 l_mesg_name := l_mesg_name||SUBSTR(l_messages,i,1);
264 END IF;
265 END LOOP;
266 END log_error_message;
267
268 PROCEDURE log_regid_person_dtls(p_regid NUMBER,
269 p_person_number VARCHAR2,
270 p_load_cal_type VARCHAR2,
271 p_load_seq_number NUMBER) AS
272
273 /******************************************************************
274 Created By : CKASU
275 Date Created By : 23-SEP-04
276 Purpose : To Log the Registration id and Person number in Log file before
277 the message whenever a person fails in Deny all holds or Person
278 Validations steps or Unit validation steps.
279 Change History
280 Who When What
281 ckasu 29-SEP-2004 added parameters p_load_cal_type,p_load_seq_number
282 inorder to print Load cal details
283
284 ******************************************************************************/
285 l_regid_person_dtls VARCHAR2(1000);
286
287 BEGIN
288
289 Fnd_Message.SET_NAME ('IGS','IGS_EN_REG_LOG_REG');
290 l_regid_person_dtls := fnd_message.get || ' : ' || p_regid || ' ';
291 Fnd_Message.SET_NAME ('IGS','IGS_PR_PERSON_ID');
292 l_regid_person_dtls := l_regid_person_dtls || fnd_message.get || ' : ' || p_person_number || ' ';
293
294 FND_MESSAGE.SET_NAME('IGS','IGS_FI_CAL_BALANCES_LOG');
295 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME','LOAD CAL ');
296 FND_MESSAGE.SET_TOKEN('PARAMETER_VAL' ,p_load_cal_type||'*'||p_load_seq_number) ;
297
298 l_regid_person_dtls := l_regid_person_dtls || fnd_message.get;
299 Fnd_File.PUT_LINE(Fnd_File.LOG,l_regid_person_dtls);
300
301 END log_regid_person_dtls;
302
303 --
304 -- Logic of the below procedure is briefly explained below
305 --
306 -- 1. If all Program Attempts and Unit Attempts fail the validations,
307 -- the interface table is updated with an error message . None of the OSS tables are updated .
308 -- 2. If atleast one Program Attempt pass the validation and One or more Unit Attempts fail the validations , the interface
309 -- table is updated with a warning message. The details of Program / Unit Attempts that have passed the validations are
310 -- updated in the OSS tables.
311 -- 3. If all Program and Unit validations are passed , the record is deleted from the Interface Table. The details of Program/
312 -- Unit Attempts that have passed the validations are updated in the OSS tables.
313 --
314 -- l_prgm_inactive_chk :: This variable is set to 'N' for every record in the interface table , indicating that
315 -- No Inactive Program Attempts have been found. If an INACTIVE Program Attempt is found , this variable is set to Y.
316 --
317 -- l_del_int_rec :: This variable is set to 'Y' for every interface record. When a Unit step / Program Step
318 -- Validation Fails , the value is set to 'N' , indicating that the record in the interface table should not be deleted.
319 --
320 -- l_prgm_val_int_pass :: This variable is set to 'N' indicating that none of the Program Step Valdns have passed . If
321 -- atleast one Prgm Step valdn is passed , the value is set to 'Y'. Reset at the interface record level.
322 --
323 -- l_unit_val_pass :: This initial value of the variable is set to 'Y'.If one or more Unit Step valdn fail, the value
324 -- is set to 'N'.Reset at the interface record level.This variable is used to determine if Warning / Error message should
325 -- be shown to the user.
326 --
327 -- l_prgm_val_pass :: This variable is set to 'N' at the start of every Program loop , indicating that the Program
328 -- has not passed the validation. When the Prgm passes the validation , this variable is set to Y. Based on the value of
329 -- l_prgm_val_pass, the Administrative Unit records are updated for that Program
330 --
331 -- If l_prgm_val_int_pass = Y and l_prgm_val_int_pass = N , it indicates that atleast one Program has passed all rge
332 -- validations , and the record has been successfully updated. Warning message is shown to the user.
333 --
334 -- If l_prgm_val_int_pass = N , none of the Programs have passed the valdn , and hence Error message is shown to the user.
335 --
336 --
337
338
339 BEGIN
340
341 l_creation_date := SYSDATE;
342 l_created_by := FND_GLOBAL.USER_ID;
343 l_last_update_date := SYSDATE;
344 l_last_updated_by := FND_GLOBAL.USER_ID;
345 l_last_update_login :=FND_GLOBAL.LOGIN_ID;
346
347 IF l_created_by IS NULL THEN
348 l_created_by := -1;
349 END IF;
350
351 IF l_last_updated_by IS NULL THEN
352 l_last_updated_by := -1;
353 END IF;
354
355 IF l_last_update_login IS NULL THEN
356 l_last_update_login := -1;
357 END IF;
358
359 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
360 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
361 l_program_application_id := FND_GLOBAL.PROG_APPL_ID;
362
363 IF (l_request_id = -1) THEN
364 l_request_id := NULL;
365 l_program_id := NULL;
366 l_program_application_id := NULL;
367 l_program_update_date := NULL;
368 ELSE
369 l_program_update_date := SYSDATE;
370 END IF;
371
372
373 -- Process all the records that match the batch_id
374
375 FOR l_reg_update_int_rec IN get_int_dtls (p_batch_id)
376 LOOP
377
378 BEGIN
379
380 SAVEPOINT interface_rec;
381 l_message_text := NULL;
382 l_message := NULL;
383 l_del_int_rec := 'Y';
384 l_prgm_val_int_pass := 'N' ;
385 l_unit_val_pass := 'Y';
386 l_prgm_inactive_chk := 'N';
387
388 --Validate Person Number
389 OPEN get_person_number (l_reg_update_int_rec.person_number ) ;
390
391 FETCH get_person_number into l_person_id ;
392 IF get_person_number%NOTFOUND THEN
393
394 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_PERSON_NUMBER');
395 l_message := FND_MESSAGE.GET;
396 l_message_text := l_message ;
397
398 END IF ;
399 CLOSE get_person_number ;
400
401 -- Validate Program Code
402 IF l_reg_update_int_rec.course_cd IS NOT NULL THEN
403 OPEN get_course_cd(l_reg_update_int_rec.course_cd );
404 FETCH get_course_cd into l_course_cd_chk;
405 IF get_course_cd%NOTFOUND THEN
406
407 FND_MESSAGE.SET_NAME('IGS','IGS_PR_INVALID_PRG_CD');
408 l_message := FND_MESSAGE.GET;
409
410 IF l_message_text IS NOT NULL THEN
411 l_message_text := l_message_text || ';' || l_message ;
412 ELSE
413 l_message_text := l_message ;
414 END IF;
415
416 END IF ;
417 CLOSE get_course_cd ;
418 END IF;
419
420 -- Validate Academic Calendar
421 OPEN get_acad_cnt (l_reg_update_int_rec.alternate_code);
422 FETCH get_acad_cnt INTO l_cnt;
423 CLOSE get_acad_cnt ;
424
425 -- If NO matching Academic Cal is found , or if multiple matches are found , error out.
426 IF l_cnt > 1 OR l_cnt = 0 THEN
427
428 FND_MESSAGE.SET_NAME('IGS','IGS_EN_INVLD_ACA_CD');
429 l_message := FND_MESSAGE.GET;
430
431 IF l_message_text IS NOT NULL THEN
432 l_message_text := l_message_text || ';' || l_message ;
433 ELSE
434 l_message_text := l_message ;
435 END IF;
436
437 ELSE
438 OPEN get_acad_cal_dtls (l_reg_update_int_rec.alternate_code);
439 FETCH get_acad_cal_dtls INTO l_acad_cal_type,l_acad_ci_Sequence_number,l_acad_cal_Start_dt, l_acad_cal_end_dt ;
440 CLOSE get_acad_cal_dtls ;
441
442 END IF ;
443
444 -- If any of the parameters have failed the validation , stop further processing for the record.
445 IF l_message_text IS NOT NULL THEN
446 -- Update the record with error messages in the interface table.
447 UPDATE igs_en_reg_upd_int SET errors = l_message_text ,
448 creation_date = l_creation_date ,
449 last_update_date = l_last_update_date ,
450 created_by = l_created_by ,
451 last_updated_by = l_last_updated_by ,
452 last_update_login = l_last_update_login ,
453 request_id = l_request_id ,
454 program_id = l_program_id ,
455 program_application_id = l_program_application_id,
456 program_update_date = l_program_update_date
457 WHERE ROWID = l_reg_update_int_rec.ROWID ;
458
459 END IF ;
460
461 IF l_message_text is NULL THEN
462
463 -- Get all Program attempts of status INACTIVE
464 FOR l_prgm_dtls_rec IN get_prgm_dtls ( NVL(l_reg_update_int_rec.course_cd , NULL) , l_acad_cal_type , l_person_id)
465 LOOP
466
467 l_prgm_inactive_chk := 'Y';
468 l_prgm_val_pass := 'N' ;
469
470 --
471 -- Process the Program Attempts. The value is defaulted at the start of processing. If any of the validations fail , it is set to 'N'.
472 -- Loop for all Load Calendar Instances that fall bet. the Acad Cal Start and End dates.
473 --
474 FOR l_get_load_cal_inst_rec IN get_load_cal_inst ( l_acad_cal_type , l_acad_ci_Sequence_number ,l_acad_cal_Start_dt ,l_acad_cal_end_dt )
475 LOOP
476
477 OPEN get_sua(l_person_id , l_prgm_dtls_rec.course_cd ,l_get_load_cal_inst_rec.cal_type ,l_get_load_cal_inst_rec.sequence_number) ;
478 FETCH get_sua INTO l_sua_rec;
479 IF get_sua%FOUND THEN
480 CLOSE get_sua;
481
482 BEGIN
483
484 SAVEPOINT load_prgm_rec ;
485 l_uoo_ids := NULL;
486 l_unitcd_and_uooid_str := NULL;
487 -- added by ckasu as a part of bug 3823810
488 l_person_type := Igs_En_Gen_008.enrp_get_person_type(p_course_cd =>NULL);
489 -- Determine the Enrollment method , Enrollment Commencement type.
490 l_dummy := NULL;
491 l_deny_warn := NULL;
492 l_processed := TRUE;
493 l_enrolment_cat:=IGS_EN_GEN_003.Enrp_Get_Enr_Cat(p_person_id =>l_person_id,
494 p_course_cd =>l_prgm_dtls_rec.course_cd,
495 p_cal_type =>l_acad_cal_type,
496 p_ci_sequence_number =>l_acad_ci_Sequence_number,
497 p_session_enrolment_cat =>NULL,
498 p_enrol_cal_type =>l_en_cal_type,
499 p_enrol_ci_sequence_number =>l_en_ci_seq_num,
500 p_commencement_type =>l_commencement_type,
501 p_enr_categories =>l_dummy);
502
503
504
505 -- deny all hold validation added as part of Bug 3823810
506 -- when l_deny_warn equals 'E' then person steps are not validated.
507 igs_en_elgbl_person.eval_ss_deny_all_hold (
508 p_person_id =>l_person_id,
509 p_person_type =>l_person_type,
510 p_course_cd =>l_prgm_dtls_rec.course_cd,
511 p_load_calendar_type =>l_get_load_cal_inst_rec.cal_type,
512 p_load_cal_sequence_number =>l_get_load_cal_inst_rec.sequence_number,
513 p_status =>l_deny_warn,
514 p_message =>l_message_name);
515
516
517 IF l_deny_warn='E' THEN --deny all hold validation
518 l_processed := FALSE;
519 log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
520 log_error_message(l_message_name,';');
521 END IF;
522 --person steps not validated when deny all hold fails added for Bug 3823810
523 l_message_name :=NULL;
524 l_deny_warn := NULL;
525
526 --Following function will do the all person step validations for the context person.
527 --when person validations are passed successfully then Unit step validations are done
528 --l_processed set to FALSE when any one of person steps for a context person evaluates
529 --to FALSE.
530 IF l_processed THEN
531 IF NOT igs_en_elgbl_person.eval_person_steps( p_person_id =>l_person_id,
532 p_person_type =>l_person_type,
533 p_load_calendar_type =>l_get_load_cal_inst_rec.cal_type,
534 p_load_cal_sequence_number =>l_get_load_cal_inst_rec.sequence_number,
535 p_program_cd =>l_prgm_dtls_rec.course_cd,
536 p_program_version =>l_prgm_dtls_rec.version_number,
537 p_enrollment_category =>l_enrolment_cat,
538 p_comm_type =>l_commencement_type,
539 p_enrl_method =>p_enroll_mtd_type,
540 p_message =>l_message_name,
541 p_deny_warn =>l_deny_warn,
542 p_calling_obj =>'JOB',
543 p_create_warning =>'N') THEN
544 --function returns the error then log all the error message and abort the further processing for the context person.
545 log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
546 log_error_message(l_message_name,';');
547 l_processed := FALSE;
548 l_del_int_rec := 'N';
549
550 ELSE
551 IF l_message_name IS NOT NULL AND l_deny_warn = 'WARN' THEN
552 log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
553 log_error_message(l_message_name,';');
554
555 END IF;
556 END IF; --NOT igs_en_elgbl_person.eval_person_steps
557 END IF;
558
559
560 -- Process all Units for a given Program .
561
562 -- when all Person steps steps are passed then Unit step validations are done.
563 IF l_processed THEN
564 FOR l_get_sua_rec IN get_sua (l_person_id , l_prgm_dtls_rec.course_cd ,l_get_load_cal_inst_rec.cal_type ,l_get_load_cal_inst_rec.sequence_number)
565 LOOP
566
567 l_message_name:=NULL;
568 -- Call the Unit validation steps for each of the unit records that has been fetched.
569 l_boolean := igs_en_enroll_wlst.validate_unit_steps (p_person_id => l_person_id ,
570 p_cal_type => l_get_load_cal_inst_rec.cal_type,
571 p_ci_sequence_number => l_get_load_cal_inst_rec.sequence_number,
572 p_uoo_id => l_get_sua_rec.uoo_id ,
573 p_course_cd => l_prgm_dtls_rec.course_cd ,
574 p_enr_method_type => p_enroll_mtd_type ,
575 p_message_name => l_message_name ,
576 p_deny_warn => l_deny_warn,
577 p_calling_obj => 'JOB'
578 ) ;
579
580 IF l_message_name IS NOT NULL THEN
581 log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
582 log_error_message(l_message_name,';');
583 END IF;
584
585
586 IF NOT l_boolean AND l_deny_warn = 'DENY' then
587 l_unit_val_pass := 'N';
588 l_del_int_rec := 'N';
589 ELSE
590 -- Concatenate the uoo_id of the Unit section that passed the validations.
591 -- l_unitcd_and_uooid_str contains unit_cd*uoo_id delimted by comma inorder
592 -- to print unit information on log file as a prt of bug#4095276
593 IF l_uoo_ids IS NULL THEN
594 l_uoo_ids := l_get_sua_rec.uoo_id ;
595 l_unitcd_and_uooid_str := l_get_sua_rec.unit_cd || '*' || l_get_sua_rec.uoo_id;
596 ELSE
597 l_uoo_ids := l_uoo_ids || ',' || l_get_sua_rec.uoo_id;
598 l_unitcd_and_uooid_str := l_unitcd_and_uooid_str || ',' || l_get_sua_rec.unit_cd || '*' || l_get_sua_rec.uoo_id;
599 END IF ;
600 END IF;
601
602 END LOOP; -- All SUA records for a Program.
603 END IF; -- end of If l_processed
604
605
606 IF l_uoo_ids IS NOT NULL THEN
607
608 --code added by ckasu as apart of bug#4095276
609 --validate if this program is a primary/secondary in this term
610 -- if it is secondary then show a warning asking the user to update the stored transfer to include the newly enrolled
611 -- unit attempts in the transfer.
612 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' AND
613 igs_en_spa_terms_api.get_spat_primary_prg(l_person_id,
614 l_prgm_dtls_rec.course_cd ,
615 l_get_load_cal_inst_rec.cal_type,
616 l_get_load_cal_inst_rec.sequence_number ) = 'SECONDARY'
617 THEN
618 log_regid_person_dtls(l_reg_update_int_rec.registration_id,l_reg_update_int_rec.person_number,
619 l_get_load_cal_inst_rec.cal_type,l_get_load_cal_inst_rec.sequence_number);
620 fnd_message.set_name('IGS','IGS_EN_UPD_FUT_TRANS');
621 fnd_message.set_token('COURSE_CD',l_prgm_dtls_rec.course_cd);
622 fnd_message.set_token('UNIT_CD',l_unitcd_and_uooid_str);
623 fnd_file.put_line(fnd_file.log, fnd_message.get);
624 END IF ; -- end of igs_en_spa_terms_api.get_spat_primary_prg
625 --end of code added by ckasu as apart of bug#4095276
626
627 l_message_name:=NULL;
628 BEGIN
629
630 igs_ss_en_wrappers.Validate_enroll_validate (
631 p_person_id => l_person_id ,
632 p_load_cal_type => l_get_load_cal_inst_rec.cal_type,
633 p_load_ci_sequence_number => l_get_load_cal_inst_rec.sequence_number,
634 p_uoo_ids => l_uoo_ids ,
635 p_program_cd => l_prgm_dtls_rec.course_cd ,
636 p_message_name => l_message_name ,
637 p_deny_warn => l_deny_warn ,
638 p_return_status => l_return_status,
639 p_enr_method => p_enroll_mtd_type,
640 p_enrolled_dt => SYSDATE);
641 EXCEPTION WHEN OTHERS THEN
642 IF IGS_GE_MSG_STACK.COUNT_MSG <> 0 THEN
643 l_message_name := FND_MESSAGE.GET;
644 ELSE
645 l_message_name := SQLERRM;
646 END IF;
647 l_deny_warn := 'DENY';
648 l_return_status := 'FALSE';
649 END;
650 IF l_message_name IS NOT NULL THEN
651 log_error_message(l_message_name,';');
652 END IF;
653
654 IF l_return_status = 'FALSE' THEN
655 l_del_int_rec := 'N';
656 ROLLBACK TO load_prgm_rec;
657
658 ELSE
659 l_prgm_val_pass := 'Y' ;
660 l_prgm_val_int_pass := 'Y';
661 l_load_cal_type := l_get_load_cal_inst_rec.cal_type ;
662 l_load_ci_Sequence_number := l_get_load_cal_inst_rec.sequence_number ;
663
664 -- Update the Enrolled_date of all the Unit Attempts that have passed the Unit step validation
665 l_pass_uoo_ids := l_uoo_ids;
666
667 WHILE l_pass_uoo_ids IS NOT NULL LOOP
668 IF(instr(l_pass_uoo_ids,',',1) = 0) THEN
669 l_uoo_id := TO_NUMBER(l_pass_uoo_ids);
670 ELSE
671 l_uoo_id := TO_NUMBER(substr(l_pass_uoo_ids,0,instr(l_pass_uoo_ids,',',1)-1)) ;
672 END IF;
673
674 OPEN get_sua_upd(l_person_id , l_prgm_dtls_rec.course_cd, l_uoo_id);
675 FETCH get_sua_upd INTO l_get_sua_upd_rec;
676 CLOSE get_sua_upd ;
677 UPDATE IGS_EN_SU_ATTEMPT_ALL SET ENROLLED_DT = l_reg_update_int_rec.enr_form_received_dt WHERE ROWID = l_get_sua_upd_rec.ROWID;
678 IF(instr(l_pass_uoo_ids,',',1) = 0) THEN
679 l_pass_uoo_ids := NULL;
680 ELSE
681 l_pass_uoo_ids := substr(l_pass_uoo_ids,instr(l_pass_uoo_ids,',',1)+1);
682 END IF;
683 END LOOP;
684
685 END IF;
686 ELSE
687 -- If No UOO_IDS are found for the given Program
688 l_del_int_rec := 'N';
689 ROLLBACK TO load_prgm_rec;
690 END IF ;
691
692 -- IF an Exception is encountered while processing a Program , skip the Program - Proceed with processing the next Program.
693 --
694 EXCEPTION
695 WHEN OTHERS THEN
696 l_del_int_rec := 'N';
697 ROLLBACK TO load_prgm_rec ;
698 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PGM_STP_FAIL');
699 l_message_text := l_message_text || ',' || FND_MESSAGE.GET;
700 UPDATE igs_en_reg_upd_int
701 SET errors = l_message_text ,
702 creation_date = l_creation_date ,
703 last_update_date = l_last_update_date ,
704 created_by = l_created_by ,
705 last_updated_by = l_last_updated_by ,
706 last_update_login = l_last_update_login ,
707 request_id = l_request_id ,
708 program_id = l_program_id ,
709 program_application_id = l_program_application_id,
710 program_update_date = l_program_update_date
711 WHERE ROWID = l_reg_update_int_rec.ROWID ;
712 END ;
713
714 ELSE -- if no unit attempts exists for the Load Calendar in context
715 CLOSE get_sua;
716 END IF;
717
718 END LOOP; -- All Load Calendars for a given Teach Calendar
719
720 IF l_prgm_val_pass = 'Y' AND l_prgm_dtls_rec.course_attempt_status='INACTIVE' THEN
721
722 FOR l_as_sc_atmpt_enr_rec IN get_as_sc_atmpt_enr ( l_acad_cal_Start_dt ,l_acad_cal_end_dt ,l_person_id , l_prgm_dtls_rec.course_cd )
723 LOOP
724
725 -- Update all Administrative Program Details for the Programs that passed the validations.
726 igs_as_sc_atmpt_enr_pkg .update_row (
727 X_ROWID => l_as_sc_atmpt_enr_rec.ROWID,
728 x_person_id => l_as_sc_atmpt_enr_rec.person_id,
729 x_course_cd => l_as_sc_atmpt_enr_rec.course_cd,
730 x_cal_type => l_as_sc_atmpt_enr_rec.cal_type,
731 x_ci_sequence_number => l_as_sc_atmpt_enr_rec.ci_sequence_number,
732 x_enrolment_cat => l_as_sc_atmpt_enr_rec.enrolment_cat,
733 x_enrolled_dt => l_reg_update_int_rec.enr_form_received_dt,
734 x_enr_form_due_dt => l_as_sc_atmpt_enr_rec.enr_form_due_dt ,
735 x_enr_pckg_prod_dt => l_as_sc_atmpt_enr_rec.enr_pckg_prod_dt ,
736 x_enr_form_received_dt => l_reg_update_int_rec.enr_form_received_dt ,
737 x_mode => 'R' );
738
739 END LOOP ; -- All matching SPA Administration records.
740 END IF ;
741
742 END LOOP; -- All INACTIVE Program Attempts.
743
744 --
745 -- If no INACTIVE Program Attempts are found, log the error message and start processing the next record.
746 IF l_prgm_inactive_chk = 'N'
747 THEN
748 -- Update the records with error message in the interface table
749 FND_MESSAGE.SET_NAME('IGS','IGS_EN_INACT_PGM');
750 l_message_text := FND_MESSAGE.GET;
751 UPDATE igs_en_reg_upd_int
752 SET errors = l_message_text ,
753 creation_date = l_creation_date ,
754 last_update_date = l_last_update_date ,
755 created_by = l_created_by ,
756 last_updated_by = l_last_updated_by ,
757 last_update_login = l_last_update_login ,
758 request_id = l_request_id ,
759 program_id = l_program_id ,
760 program_application_id = l_program_application_id,
761 program_update_date = l_program_update_date
762 WHERE ROWID = l_reg_update_int_rec.ROWID ;
763
764 --
765 -- Update FAIL error messages for records that failed all validations.
766 ELSIF l_del_int_rec = 'N' AND l_prgm_val_int_pass = 'N'
767 THEN
768 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PGM_STP_FAIL');
769 l_message_text := FND_MESSAGE.GET;
770 UPDATE igs_en_reg_upd_int
771 SET errors = l_message_text ,
772 creation_date = l_creation_date ,
773 last_update_date = l_last_update_date ,
774 created_by = l_created_by ,
775 last_updated_by = l_last_updated_by ,
776 last_update_login = l_last_update_login ,
777 request_id = l_request_id ,
778 program_id = l_program_id ,
779 program_application_id = l_program_application_id,
780 program_update_date = l_program_update_date
781 WHERE ROWID = l_reg_update_int_rec.ROWID ;
782
783 --
784 -- Update WARN error messages for records that partially passed the validations.
785 ELSIF l_del_int_rec = 'N' AND l_prgm_val_int_pass = 'Y' AND l_unit_val_pass = 'N'
786 THEN
787 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PGM_STP_WARN');
788 l_message_text := FND_MESSAGE.GET;
789 UPDATE igs_en_reg_upd_int
790 SET errors = l_message_text ,
791 creation_date = l_creation_date ,
792 last_update_date = l_last_update_date ,
793 created_by = l_created_by ,
794 last_updated_by = l_last_updated_by ,
795 last_update_login = l_last_update_login ,
796 request_id = l_request_id ,
797 program_id = l_program_id ,
798 program_application_id = l_program_application_id,
799 program_update_date = l_program_update_date
800 WHERE ROWID = l_reg_update_int_rec.ROWID ;
801
802 --
803 -- Delete all the records that have been succesfully processed .
804 ELSIF l_del_int_rec ='Y'
805 THEN
806 DELETE FROM igs_en_reg_upd_int
807 WHERE ROWID = l_reg_update_int_rec.ROWID;
808
809 END IF ;
810 END IF;
811
812 --
813 -- If an unhandled exception is raised while processing a record of the Interface table, the error message
814 -- should be updated in the interface table, and processing should continue for the next record.
815 EXCEPTION
816 WHEN OTHERS THEN
817 ROLLBACK TO interface_rec;
818 l_message_text := FND_MESSAGE.GET;
819 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
820 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_GEN_016.enrp_batch_reg_upd');
821 l_message_text := l_message_text || ',' || FND_MESSAGE.GET;
822 UPDATE igs_en_reg_upd_int
823 SET errors = l_message_text ,
824 creation_date = l_creation_date ,
825 last_update_date = l_last_update_date ,
826 created_by = l_created_by ,
827 last_updated_by = l_last_updated_by ,
828 last_update_login = l_last_update_login ,
829 request_id = l_request_id ,
830 program_id = l_program_id ,
831 program_application_id = l_program_application_id,
832 program_update_date = l_program_update_date
833 WHERE ROWID = l_reg_update_int_rec.ROWID ;
834 END ;
835
836 END LOOP; -- All records in inetrface Table
837 COMMIT;
838 END enrp_enr_reg_upd ;
839
840 PROCEDURE enrp_batch_reg_upd(
841 errbuf OUT NOCOPY VARCHAR2,
842 retcode OUT NOCOPY NUMBER,
843 p_batch_id IN VARCHAR2,
844 p_enr_method_type IN VARCHAR2
845 ) AS
846 /*------------------------------------------------------------------
847 --Created by : knaraset, Oracle IDC
848 --Date created: 07-Nov-2002
849 --
850 -- Purpose: This job will confirm(Enroll) the unit attempts of all students
851 -- in the interface table Igs_En_Reg_Upd_Int after successful completion of
852 -- the unit and program step validations.
853 -- And also generates the log for all the failed records.
854 -- If both the batch id and enrollment method type are given then it first
855 -- process all the records in the interface table igs_en_reg_upd_int for
856 -- the given batch id and generates the log for failed records.
857 -- If only batch id is given then only log file of existing failed records
858 -- will be generated.
859 --
860 --Known limitations/enhancements and/or remarks:
861 --
862 --Change History:
863 --Who When What
864 --ckasu 14-SEP-2004 added new variable l_later_cnt and modified procedure as
865 -- a part of Bug# 3823810
866 --rvivekan 04-Aug-2003 Bulk unit upload TD bug#3049009. Removed country code validation.
867 --svenkata 31-DEC-2002 Bug# 2724288 - NO message shown when ran with Invalid data.Wrote a query to check if data exists in the
868 -- interface table for a given batch id.
869 -- ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
870 ------------------------------------------------------------------ */
871 --
872 -- cursor to fetch the batch description
873 CURSOR cur_batch_desc IS
874 SELECT batch_description
875 FROM igs_en_reg_btch_int
876 WHERE batch_id = p_batch_id;
877
878 --
879 -- cursor to fetch the prefered person ID type
880 CURSOR cur_pref_per_type IS
881 SELECT person_id_type
882 FROM igs_pe_person_id_typ
883 WHERE preferred_ind = 'Y';
884
885 --
886 -- get the person details
887 CURSOR cur_person_dtl(p_person_number VARCHAR2) IS
888 SELECT person_id,full_name
889 FROM igs_pe_person_base_v
890 WHERE person_number = p_person_number;
891
892 --
893 -- cursor to fetch value for prefered person id type.
894 CURSOR cur_alt_pers_id(p_pers_id_type VARCHAR2,p_person_id NUMBER) IS
895 SELECT api_person_id
896 FROM igs_pe_alt_pers_id
897 WHERE person_id_type = p_pers_id_type AND
898 pe_person_id = p_person_id;
899
900 --
901 -- cursor to fetch all the students in the interface table whoes records failed to import/update
902 CURSOR cur_enr_reg_err IS
903 SELECT registration_id,person_number,course_cd,alternate_code,errors
904 FROM igs_en_reg_upd_int
905 WHERE batch_id = p_batch_id AND
906 errors IS NOT NULL;
907
908 --cursor to fetch records from interface table
909 --
910 CURSOR get_int_cnt IS
911 SELECT count(*)
912 FROM igs_en_reg_upd_int int
913 WHERE int.batch_id = p_batch_id ;
914
915 l_batch_desc igs_en_reg_btch_int.batch_description%TYPE;
916 l_pref_per_type igs_pe_person_id_typ.person_id_type%TYPE;
917 l_person_id igs_pe_person_base_v.person_id%TYPE;
918 l_full_name igs_pe_person_base_v.full_name%TYPE;
919 l_alt_pers_id igs_pe_alt_pers_id.api_person_id%TYPE;
920 l_batch_id_label VARCHAR2(200);
921 l_batch_desc_label VARCHAR2(200);
922 l_error_rec VARCHAR2(1):= 'N' ;
923 l_int_cnt NUMBER := 0;
924 --added by ckasu
925 l_later_cnt NUMBER := 0;
926 --end of addition by ckasu
927
928
929 BEGIN
930 --added by ckasu as apart of bug# 4958173
931 igs_ge_gen_003.set_org_id(NULL);
932
933 OPEN get_int_cnt ;
934 FETCH get_int_cnt INTO l_int_cnt;
935 CLOSE get_int_cnt ;
936
937 -- If method type specified then call the procedure to process the records in interface table.
938 IF p_enr_method_type IS NOT NULL THEN
939 igs_en_gen_016.enrp_enr_reg_upd(p_batch_id,p_enr_method_type);
940 END IF;
941
942 --
943 -- Start of log file creation
944 --
945
946 -- Get the description of the passed in Batch Id
947 OPEN cur_batch_desc;
948 FETCH cur_batch_desc INTO l_batch_desc;
949 CLOSE cur_batch_desc;
950
951 -- Get the prefered person id type
952 OPEN cur_pref_per_type;
953 FETCH cur_pref_per_type INTO l_pref_per_type;
954 CLOSE cur_pref_per_type;
955
956 --
957 -- Get the labels for the batch details
958 Fnd_Message.SET_NAME ('IGS','IGS_EN_REG_LOG_BATCH');
959 l_batch_id_label := fnd_message.get;
960 Fnd_Message.SET_NAME ('IGS','IGS_EN_REG_LOG_DESC');
961 l_batch_desc_label := fnd_message.get;
962 --
963 -- log the Batch details into log file
964 Fnd_File.PUT_LINE(Fnd_File.LOG,'=============================================================================');
965 Fnd_File.PUT_LINE(Fnd_File.LOG,l_batch_id_label||' : '||RPAD(p_batch_id,20,' ')||l_batch_desc_label||' : '||l_batch_desc);
966 Fnd_File.PUT_LINE(Fnd_File.LOG,'=============================================================================');
967
968 OPEN get_int_cnt ;
969 FETCH get_int_cnt INTO l_later_cnt;
970 CLOSE get_int_cnt ;
971
972 IF l_int_cnt = 0 THEN
973 Fnd_Message.SET_NAME ('IGS','IGS_EN_NO_DATA_IMP');
974 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get);
975 RETURN;
976 ELSIF l_later_cnt = l_int_cnt THEN
977 Fnd_Message.SET_NAME ('IGS','IGS_EN_NO_DATA_IMP');
978 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get);
979 END IF ;
980
981 --
982 -- Get all the records in Interface table where 'errors' column has value.
983 -- and log the student details and errors in log file.
984 --
985 FOR cur_enr_reg_err_rec IN cur_enr_reg_err LOOP
986
987
988 -- Set the variable indicating that error(s) were encountered while processing for the given batch_id
989 l_error_rec := 'Y';
990
991 -- Get the person details
992 OPEN cur_person_dtl(cur_enr_reg_err_rec.person_number);
993 FETCH cur_person_dtl INTO l_person_id,l_full_name;
994 CLOSE cur_person_dtl;
995 -- Get the alternate person ID for the prefered person ID type
996 OPEN cur_alt_pers_id(l_pref_per_type,l_person_id);
997 FETCH cur_alt_pers_id INTO l_alt_pers_id;
998 CLOSE cur_alt_pers_id;
999
1000 Fnd_Message.SET_NAME ('IGS','IGS_EN_REG_LOG_REG');
1001 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get||' : '||cur_enr_reg_err_rec.registration_id);
1002 Fnd_Message.SET_NAME ('IGS','IGS_PR_PERSON_ID');
1003 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get||' : '||cur_enr_reg_err_rec.person_number);
1004 Fnd_Message.SET_NAME ('IGS','IGS_EN_REG_LOG_NAME');
1005 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get||' : '||l_full_name);
1006 Fnd_File.PUT_LINE(Fnd_File.LOG,l_pref_per_type||' : '||l_alt_pers_id);
1007 Fnd_Message.SET_NAME ('IGS','IGS_EN_REG_LOG_PGM');
1008 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get||' : '||cur_enr_reg_err_rec.course_cd);
1009 Fnd_Message.SET_NAME ('IGS','IGS_EN_REG_LOG_ACAD');
1010 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get||' : '||cur_enr_reg_err_rec.alternate_code);
1011 Fnd_Message.SET_NAME ('IGS','IGS_EN_REG_LOG_ERR');
1012 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get||' : '||cur_enr_reg_err_rec.errors);
1013
1014 Fnd_File.PUT_LINE(Fnd_File.LOG,'-----------------------------------------------------------------------------');
1015
1016 END LOOP;
1017
1018 --
1019 -- Check if the records of the Interface table has been successfully processed and Deleted .
1020 -- If No error record(s) were found , show this message indicating that all records were processed ,
1021 -- Data imported into OSS tables and records in Interface Table deleted.
1022 --
1023 IF l_error_rec = 'N' THEN
1024 Fnd_Message.Set_name('IGS','IGS_EN_REG_UPD_SUCCESS');
1025 Fnd_File.PUT_LINE(Fnd_File.LOG,fnd_message.get);
1026 Fnd_File.PUT_LINE(Fnd_File.LOG,'-----------------------------------------------------------------------------');
1027 END IF;
1028
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 retcode:=2;
1032 Fnd_File.PUT_LINE(Fnd_File.LOG,SQLERRM);
1033 ERRBUF := Fnd_Message.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1034 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1035
1036 END enrp_batch_reg_upd;
1037
1038 END igs_en_gen_016;
[Home] [Help]