[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_LGCY_PRC
Source
1 PACKAGE BODY igs_en_lgcy_prc AS
2 /* $Header: IGSEN99B.pls 120.6 2006/04/13 01:55:01 smaddali ship $ */
3
4 /*****************************************************************************
5 Who When What
6 amuthu 21-NOV-2002 Modified as per the Legacy Import prcess
7 TD for EN and REC
8 ptandon 26-SEP-2003 Modified the procedure process_as_uotcm to pass the value of fields
9 LOCATION_CD and UNIT_CLASS in lr_as_uotcm_rec parameter in call to procedure
10 igs_as_suao_lgcy_pub.create_unit_outcome. Bug# 3149520.
11 rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
12 added as part of Prevent Dropping Core Units. Enh Bug# 3052432
13 vkarthik 12-DEC-2003 Added process_en_spat to proces term records from the interface table
14 and necessary call
15 ptandon 16-DEC-2003 Modified procedure process_en_sua to log warning messages in case of
16 successful unit attempts also so as to show warnings if term record
17 creation fails. Term Based Fee Calc build. Bug# 2829263.
18 smaddali 19-OCT-2004 Modified procedure log_err_messages for performance issues
19 jtmathew 12-JAN-2005 Modified procedures process_he_spa and process_he_susa
20 to add additional fields to lr_he_spa_rec and lr_he_susa_rec
21 for changes described by HEFD350.
22 jhanda 15-July-2005 Changed for build 4327991
23 ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) in LEGACY_BATCH_PROCESS
24 procedure as a part of bug#4958173
25 smaddali 10-apr-06 Modified for bug#5091858 BUILD EN324
26 ******************************************************************************/
27
28 -- cursor for find the lookup meaning for a given lookup code and type
29 CURSOR gc_lkups (cp_lookup_code igs_lookup_values.lookup_code%TYPE,
30 cp_lookup_type igs_lookup_values.lookup_type%TYPE) IS
31 SELECT meaning
32 FROM igs_lookup_values
33 WHERE lookup_code = cp_lookup_code
34 AND lookup_type = cp_lookup_type;
35
36 -- Table Constants
37 g_cst_en_spat CONSTANT VARCHAR2(30) := 'EN_SPAT';
38 g_cst_all CONSTANT VARCHAR2(30) := 'ALL';
39 g_cst_en_spa CONSTANT VARCHAR2(30) := 'EN_SPA';
40 g_cst_en_susa CONSTANT VARCHAR2(30) := 'EN_SUSA';
41 g_cst_en_spi CONSTANT VARCHAR2(30) := 'EN_SPI';
42 g_cst_en_spaa CONSTANT VARCHAR2(30) := 'EN_SPAA';
43 g_cst_re_sprvsr CONSTANT VARCHAR2(30) := 'RE_SPRVSR';
44 g_cst_re_the CONSTANT VARCHAR2(30) := 'RE_THE';
45 g_cst_en_sua CONSTANT VARCHAR2(30) := 'EN_SUA';
46 g_cst_he_spa CONSTANT VARCHAR2(30) := 'HE_SPA';
47 g_cst_he_susa CONSTANT VARCHAR2(30) := 'HE_SUSA';
48 g_cst_av_avstdl CONSTANT VARCHAR2(30) := 'AV_AVSTDL';
49 g_cst_av_untstd CONSTANT VARCHAR2(30) := 'AV_UNTSTD';
50 g_cst_as_uotcm CONSTANT VARCHAR2(30) := 'AS_UOTCM';
51 g_cst_pr_out CONSTANT VARCHAR2(30) := 'PR_OUT';
52 g_cst_pr_cr CONSTANT VARCHAR2(30) := 'PR_CR';
53 g_cst_gr_grd CONSTANT VARCHAR2(30) := 'GR_GRD';
54 -- anilk, transcript comments
55 g_cst_as_trncmt CONSTANT VARCHAR2(30) := 'AS_TRNCMT';
56 -- bradhakr , reference codes
57 g_cst_as_suarc CONSTANT VARCHAR2(30) := 'AS_SUARC';
58 -- constant for the lookup type
59 g_cst_tbl_lkup_type CONSTANT VARCHAR2(30) := 'LEGACY_EN_REC_TABLES';
60 g_cst_lgcy_status CONSTANT VARCHAR2(30) := 'LEGACY_STATUS';
61 g_cst_en_spi_rcond CONSTANT VARCHAR2(30) := 'EN_SPIRC';
62 -- resource busy exception and its associated pragma
63 g_resource_busy EXCEPTION;
64 PRAGMA EXCEPTION_INIT(g_resource_busy,-00054);
65
66
67 -------------------------------------------------------------------------------
68 -- function to return last updated by
69 FUNCTION get_last_updated_by RETURN NUMBER AS
70 BEGIN
71 IF FND_GLOBAL.USER_ID IS NULL THEN
72 RETURN -1;
73 ELSE
74 RETURN FND_GLOBAL.USER_ID;
75 END IF;
76 END get_last_updated_by ;
77
78 -------------------------------------------------------------------------------
79 -- fucntion to return last update date
80 FUNCTION get_last_update_date RETURN DATE AS
81 BEGIN
82 RETURN SYSDATE;
83 END get_last_update_date ;
84
85 -------------------------------------------------------------------------------
86 -- function ro return last update login
87 FUNCTION get_last_update_login RETURN NUMBER AS
88 BEGIN
89 IF FND_GLOBAL.LOGIN_ID IS NULL THEN
90 RETURN -1;
91 ELSE
92 RETURN FND_GLOBAL.LOGIN_ID;
93 END IF;
94 END get_last_update_login;
95
96 -------------------------------------------------------------------------------
97 -- function to return the request id
98 FUNCTION get_request_id RETURN NUMBER AS
99 BEGIN
100
101 IF FND_GLOBAL.CONC_REQUEST_ID = -1 THEN
102 RETURN NULL;
103 ELSE
104 RETURN FND_GLOBAL.CONC_REQUEST_ID;
105 END IF;
106 END get_request_id;
107
108 -------------------------------------------------------------------------------
109 -- function ro get the program application id
110 FUNCTION get_program_application_id RETURN NUMBER AS
111 BEGIN
112 IF (FND_GLOBAL.CONC_REQUEST_ID = -1) THEN
113 RETURN NULL;
114 ELSE
115 RETURN FND_GLOBAL.PROG_APPL_ID;
116 END IF;
117 END get_program_application_id;
118
119 -------------------------------------------------------------------------------
120 -- function to return the program id
121 FUNCTION get_program_id RETURN NUMBER AS
122 BEGIN
123 IF (FND_GLOBAL.CONC_REQUEST_ID = -1) THEN
124 RETURN NULL;
125 ELSE
126 RETURN FND_GLOBAL.CONC_PROGRAM_ID;
127 END IF;
128 END get_program_id;
129
130 -------------------------------------------------------------------------------
131 -- function to return the program update date
132 FUNCTION get_program_update_date RETURN DATE AS
133 BEGIN
134 IF (FND_GLOBAL.CONC_REQUEST_ID = -1) THEN
135 RETURN NULL;
136 ELSE
137 RETURN SYSDATE;
138 END IF;
139 END get_program_update_date;
140
141
142 -------------------------------------------------------------------------------
143 -- prcedure to delete error messages
144 procedure delete_err_messages(
145 p_int_table_code IN VARCHAR2,
146 p_int_table_id IN NUMBER
147 ) AS
148 BEGIN
149 /*
150 This procedure deletes the error message
151 records for the particualr interface table corresponding
152 to the p_int_table_code and p_int_table_id
153 */
154 DELETE FROM igs_en_lgcy_err_int
155 WHERE int_table_id = p_int_table_id
156 AND int_table_code = p_int_table_code;
157 END;
158 -------------------------------------------------------------------------------
159 PROCEDURE log_resource_busy(p_int_table_code IN VARCHAR2) AS
160 l_msg_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
161 l_table_meaning igs_lookup_values.meaning%TYPE;
162 l_meaning igs_lookup_values.meaning%TYPE;
163 BEGIN
164
165 FND_MESSAGE.SET_NAME('FND','FND_LOCK_RECORD_ERROR');
166 l_msg_text := FND_MESSAGE.GET;
167
168 OPEN gc_lkups (p_int_table_code,g_cst_tbl_lkup_type);
169 FETCH gc_lkups INTO l_table_meaning;
170 CLOSE gc_lkups;
171
172 OPEN gc_lkups ('E',g_cst_lgcy_status);
173 FETCH gc_lkups INTO l_meaning;
174 CLOSE gc_lkups;
175
176 FND_FILE.PUT_LINE(FND_FILE.LOG,l_meaning || ' ' || l_table_meaning || ' ' || l_msg_text);
177
178 END log_resource_busy;
179
180 -------------------------------------------------------------------------------
181 PROCEDURE log_no_data_exists(p_log IN BOOLEAN) AS
182 l_msg_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
183 BEGIN
184 IF p_log THEN
185 FND_MESSAGE.SET_NAME('FND','FND_DEF_ALTERNATE_TEXT');
186 l_msg_text := FND_MESSAGE.GET;
187 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ' || l_msg_text);
188 END IF;
189 END log_no_data_exists;
190
191 -------------------------------------------------------------------------------
192 PROCEDURE log_headers(
193 p_int_table_code IN VARCHAR2
194 ) AS
195 l_msg_text5 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
196 l_table_meaning igs_lookup_values.meaning%TYPE;
197
198 l_msg_text1 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
199 l_msg_text2 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
200 l_msg_text3 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
201 l_msg_text4 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
202
203
204 BEGIN
205 /*
206 This to log the header for each interface table that is processed
207 The header would be logged in the following format
208 "Row Type Student Program Attempt"
209 The "Row Type" Part comes from a message the table name
210 part of it comes from the Lookup meaning of the int_table_cd
211 */
212
213 FND_MESSAGE.SET_NAME('IGS','IGS_EN_INTERFACE_ID');
214 l_msg_text1 := FND_MESSAGE.GET;
215
216 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MESSAGE_NUM');
217 l_msg_text2 := FND_MESSAGE.GET;
218
219 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ROW_STATUS');
220 l_msg_text3 := FND_MESSAGE.GET;
221
222 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MSG_TXT');
223 l_msg_text4 := FND_MESSAGE.GET;
224
225 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ROW_TYPE');
226 l_msg_text5 := FND_MESSAGE.GET;
227
228 OPEN gc_lkups (p_int_table_code,g_cst_tbl_lkup_type);
229 FETCH gc_lkups INTO l_table_meaning;
230 CLOSE gc_lkups;
231
232 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
233 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
234 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------------------------------------------');
235 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_text5 || ' ' || l_table_meaning);
236 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------------------------------------------');
237 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_text1 || ' ' || l_msg_text2 || ' ' || l_msg_text3 || ' ' || l_msg_text4 );
238 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------------------------------------------');
239
240 END log_headers;
241
242 -------------------------------------------------------------------------------
243 PROCEDURE log_suc_message(
244 P_int_table_id IN NUMBER
245 ) AS
246 l_msg_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
247 l_meaning igs_lookup_values.meaning%TYPE;
248 BEGIN
249
250 FND_MESSAGE.SET_NAME('IGS','IGS_EN_LGCY_SUCCESS');
251 l_msg_text := FND_MESSAGE.GET;
252
253 OPEN gc_lkups ('I',g_cst_lgcy_status);
254 FETCH gc_lkups INTO l_meaning;
255 CLOSE gc_lkups;
256
257 -- log the error message in the concurrent log
258 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(p_int_table_id,15,' ')
259 || ' '
260 || ' ' || RPAD(l_meaning,22,' ')
261 || ' ' || l_msg_text);
262
263 END log_suc_message;
264
265 -------------------------------------------------------------------------------
266 PROCEDURE insert_log_err_msgs(
267 p_int_table_code IN VARCHAR2,
268 P_int_table_id IN NUMBER,
269 p_err_msg_id IN NUMBER,
270 p_msg_ret_status IN VARCHAR2,
271 p_msg_text IN VARCHAR2,
272 p_msg_number IN NUMBER
273 ) AS
274 l_created_by NUMBER;
275 l_creation_date DATE;
276 l_last_update_date DATE ;
277 l_last_updated_by NUMBER;
278 l_last_update_login NUMBER;
279 l_request_id NUMBER;
280 l_program_id NUMBER;
281 l_program_application_id NUMBER;
282 l_program_update_date DATE;
283
284 l_status_mean igs_lookup_values.meaning%TYPE;
285 l_msg_ret_status VARCHAR2(1);
286 BEGIN
287
288 IF p_msg_ret_status = 'W' THEN
289 l_msg_ret_status := 'W';
290 ELSE
291 l_msg_ret_status := 'E';
292 END IF;
293
294 OPEN gc_lkups (l_msg_ret_status,g_cst_lgcy_status);
295 FETCH gc_lkups INTO l_status_mean;
296 CLOSE gc_lkups;
297
298 l_created_by := get_last_updated_by;
299 l_creation_date := SYSDATE;
300 l_last_updated_by := get_last_updated_by;
301 l_last_update_date := get_last_update_date;
302 l_last_update_login := get_last_update_login;
303 l_request_id := get_request_id;
304 l_program_application_id := get_program_application_id;
305 l_program_id := get_program_id;
306 l_program_update_date := get_program_update_date;
307 INSERT INTO igs_en_lgcy_err_int
308 (
309 err_message_id,
310 int_table_code,
311 int_table_id,
312 message_num,
313 message_text,
314 created_by,
315 creation_date,
316 last_updated_by,
317 last_update_date,
318 last_update_login,
319 request_id,
320 program_application_id,
321 program_id,
322 program_update_date
323 )
324 VALUES
325 (
326 p_err_msg_id,
327 p_int_table_code,
328 p_int_table_id,
329 p_msg_number,
330 p_msg_text,
331 l_created_by,
332 l_creation_date,
333 l_last_updated_by ,
334 l_last_update_date ,
335 l_last_update_login ,
336 l_request_id,
337 l_program_application_id,
338 l_program_id,
339 l_program_update_date
340 );
341
342 /* example of the out put
343 --------------------------------------------------------------------------------
344 Interface ID Message Number Row Status Message Text
345 --------------------------------------------------------------------------------
346 ..
347 ..
348 --------------------------------------------------------------------------------
349 Row Type : Research Thesis
350 999 Warning Thesis already exists for the Student.
351 ..
352 ..
353 --------------------------------------------------------------------------------
354 */
355 -- log the error message in the concurrent log
356 FND_FILE.PUT_LINE(FND_FILE.LOG,RPAD(p_int_table_id,15,' ')
357 || ' ' || RPAD(NVL(TO_CHAR(p_msg_number),' '),9,' ')
358 || ' ' || RPAD(l_status_mean,22,' ')
359 || ' ' || p_msg_text);
360
361 END insert_log_err_msgs;
362 -------------------------------------------------------------------------------
363 PROCEDURE log_err_messages(
364 p_int_table_code IN VARCHAR2,
365 P_int_table_id IN NUMBER,
366 p_msg_count IN NUMBER,
367 p_msg_data IN VARCHAR2,
368 p_msg_ret_status IN VARCHAR2
369 ) AS
370 /*----------------------------------------------------------------------------
371 || Created By :
372 || Created On :
373 || Purpose :The error message for teh particular row is passed bye the Called API
374 || using the two standard parametere p_msg_data and p_msg_count. The two
375 || values are passed to this procedure. In this procedure the error messages
376 || are decoded/parsed and put into the concurrent manager log file and
377 || the legacy error message table. For each row in the interface table a new
378 || error message id would be generated. It is the users responsibility to
379 || clean up the message table as and when necessary.
380 ||
381 || In case the deletion flag is passed as 'Y' and the record is successfully
382 || imported then all the associated error messages would also be deleted.
383 || But the logic for that is coded in the individual procedure
384 || Known limitations, enhancements or remarks :
385 || Change History :
386 || Who When What
387 || kkillams 20-12-2002 Added new validation inside the FOR LOOP
388 || which sets the l_err_msg_id variable with the
389 || next "legacy error" sequence number, w.r.t. bug :2717455
390 || smaddali 19-oct-04 Modified this procedure for bug#3930425, performance issue with cursor c_msg_text_num
391 ------------------------------------------------------------------------------*/
392
393 --smaddali: bug#3930425, modified this cursor for performance, to be based on appl_id and message_name as this is the PK
394 CURSOR c_msg_text_num (cp_appl_name FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE ,
395 cp_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE) IS
396 SELECT message_number
397 FROM FND_NEW_MESSAGES msg ,fnd_application apl
398 WHERE apl.application_id = msg.application_id
399 AND apl.application_short_name = cp_appl_name
400 AND message_name = cp_message_name;
401
402 l_msg_count NUMBER(4);
403 l_msg_data VARCHAR2(4000);
404 l_enc_msg VARCHAR2(2000);
405 l_msg_index NUMBER(4);
406 l_appl_name FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE;
407 l_msg_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
408 l_msg_number FND_NEW_MESSAGES.MESSAGE_NUMBER%TYPE;
409 l_msg_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
410 l_err_msg_id igs_en_lgcy_err_int.err_message_id%TYPE;
411 l_app VARCHAR2(10);
412 BEGIN
413
414 l_msg_count := p_msg_count;
415 l_msg_data := p_msg_data;
416
417 IF l_msg_count =1 THEN
418 SELECT igs_en_lgcy_err_int_s.NEXTVAL INTO l_err_msg_id FROM DUAL;
419 FND_MESSAGE.SET_ENCODED(l_msg_data);
420 l_msg_text := FND_MESSAGE.GET;
421
422 l_msg_number := NULL;
423 FND_MESSAGE.PARSE_ENCODED(l_msg_data,l_app,l_msg_name);
424 OPEN c_msg_text_num (l_app, l_msg_name);
425 FETCH c_msg_text_num INTO l_msg_number;
426 CLOSE c_msg_text_num;
427
428 insert_log_err_msgs(
429 p_int_table_code => p_int_table_code,
430 p_int_table_id => p_int_table_id,
431 p_err_msg_id => l_err_msg_id,
432 p_msg_ret_status => p_msg_ret_status,
433 p_msg_text => l_msg_text,
434 p_msg_number => l_msg_number
435 );
436 ELSIF l_msg_count > 1 THEN
437 FOR l_index IN 1..NVL(l_msg_count,0)
438 LOOP
439 SELECT igs_en_lgcy_err_int_s.NEXTVAL INTO l_err_msg_id FROM DUAL;
440 FND_MSG_PUB.GET(FND_MSG_PUB.G_FIRST,
441 FND_API.G_TRUE,
442 l_enc_msg,
443 l_msg_index);
444 FND_MESSAGE.SET_ENCODED(l_enc_msg);
445 l_msg_text := FND_MESSAGE.GET;
446 FND_MSG_PUB.DELETE_MSG(l_msg_index);
447
448 l_msg_number := NULL;
449 FND_MESSAGE.PARSE_ENCODED(l_enc_msg,l_app,l_msg_name);
450 OPEN c_msg_text_num (l_app,l_msg_name);
451 FETCH c_msg_text_num INTO l_msg_number;
452 CLOSE c_msg_text_num;
453 insert_log_err_msgs(
454 p_int_table_code => p_int_table_code,
455 p_int_table_id => p_int_table_id,
456 p_err_msg_id => l_err_msg_id,
457 p_msg_ret_status => p_msg_ret_status,
458 p_msg_text => l_msg_text,
459 p_msg_number => l_msg_number);
460 END LOOP;
461 END IF;
462
463 END log_err_messages;
464
465
466 PROCEDURE process_as_suarc(
467 p_batch_id IN NUMBER,
468 p_deletion_flag IN BOOLEAN
469 ) AS
470
471 CURSOR c_as_suarc IS
472 SELECT suarc.*
473 FROM igs_as_lgcy_suarc_int suarc
474 WHERE batch_id = p_batch_id AND import_status IN ('U', 'R')
475 ORDER BY suarc.person_number ASC
476 FOR UPDATE NOWAIT;
477
478 lr_as_suarefcd_rec IGS_AS_SUARC_LGCY_PUB.sua_refcd_rec_type;
479 l_return_status VARCHAR2(1);
480 l_msg_count NUMBER(4);
481 l_msg_data VARCHAR2(4000);
482
483 l_last_update_date DATE ;
484 l_last_updated_by NUMBER;
485 l_last_update_login NUMBER;
486 l_request_id NUMBER;
487 l_program_id NUMBER;
488 l_program_application_id NUMBER;
489 l_program_update_date DATE;
490 l_not_found BOOLEAN;
491
492 BEGIN
493 /*
494 Process the records in the Student Unit attempt reference code interface table
495 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
496
497 */
498
499 log_headers(g_cst_as_suarc);
500 l_not_found := TRUE;
501
502 FOR l_as_suarefcd_rec IN c_as_suarc LOOP
503 BEGIN
504
505 -- create a save point. if there are any errors returned by
506 -- the API then rollback to this savepoint before processing
507 -- then next record.
508 SAVEPOINT savepoint_as_suarc;
509 l_not_found := FALSE;
510
511
512 -- populate the record variable to pass to the API
513 lr_as_suarefcd_rec.person_number := l_as_suarefcd_rec.person_number ;
514 lr_as_suarefcd_rec.program_cd := l_as_suarefcd_rec.program_cd ;
515 lr_as_suarefcd_rec.unit_cd := l_as_suarefcd_rec.unit_cd ;
516 lr_as_suarefcd_rec.version_number := l_as_suarefcd_rec.version_number ;
517 lr_as_suarefcd_rec.teach_cal_alt_code := l_as_suarefcd_rec.teach_cal_alt_code ;
518 lr_as_suarefcd_rec.location_cd := l_as_suarefcd_rec.location_cd ;
519 lr_as_suarefcd_rec.unit_class := l_as_suarefcd_rec.unit_class ;
520 lr_as_suarefcd_rec.reference_cd_type := l_as_suarefcd_rec.reference_cd_type ;
521 lr_as_suarefcd_rec.reference_cd := l_as_suarefcd_rec.reference_cd ;
522 lr_as_suarefcd_rec.applied_program_cd := l_as_suarefcd_rec.applied_program_cd ;
523
524 l_return_status := NULL;
525 l_msg_count := NULL;
526 l_msg_data := NULL;
527 IGS_AS_SUARC_LGCY_PUB.create_suarc(
528 P_API_VERSION => 1.0,
529 P_INIT_MSG_LIST => FND_API.G_TRUE,
530 P_COMMIT => FND_API.G_FALSE,
531 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
532 p_suarc_dtls_rec => lr_as_suarefcd_rec,
533 X_RETURN_STATUS => l_return_status,
534 X_MSG_COUNT => l_msg_count,
535 X_MSG_DATA => l_msg_data
536 );
537
538
539 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
540 ROLLBACK TO savepoint_as_suarc;
541 -- log the error message in the error message interface table
542 log_err_messages(
543 g_cst_as_suarc,
544 l_as_suarefcd_rec.legacy_suar_int_id,
545 l_msg_count,
546 l_msg_data,
547 l_return_status
548 );
549 ELSE
550 -- log the success message in the concurrent manager log.
551 log_suc_message( l_as_suarefcd_rec.LEGACY_SUAR_INT_ID);
552 END IF;
553
554 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
555 -- delete any records in the error message interface table
556 delete_err_messages(
557 p_int_table_code => g_cst_en_sua,
558 p_int_table_id => l_as_suarefcd_rec.LEGACY_SUAR_INT_ID
559 );
560 -- delete the interface record
561 DELETE FROM igs_as_lgcy_suarc_int
562 WHERE LEGACY_SUAR_INT_ID = l_as_suarefcd_rec.LEGACY_SUAR_INT_ID;
563 ELSE
564 l_last_updated_by := get_last_updated_by;
565 l_last_update_date := get_last_update_date;
566 l_last_update_login := get_last_update_login;
567 l_request_id := get_request_id;
568 l_program_application_id := get_program_application_id;
569 l_program_id := get_program_id;
570 l_program_update_date := get_program_update_date;
571 UPDATE igs_as_lgcy_suarc_int
572 SET import_status = DECODE(l_return_status,
573 'S','I',
574 'U','E',
575 'W','W',
576 'E'),
577 last_update_date = l_last_update_date,
578 last_updated_by = l_last_updated_by,
579 last_update_login = l_last_update_login,
580 request_id = l_request_id,
581 program_id = l_program_id,
582 program_application_id = l_program_application_id,
583 program_update_date = l_program_update_date
584 WHERE LEGACY_SUAR_INT_ID = l_as_suarefcd_rec.LEGACY_SUAR_INT_ID;
585 END IF;
586 EXCEPTION
587 WHEN OTHERS THEN
588 ROLLBACK TO savepoint_en_suarefcd;
589 END;
590 END LOOP;
591
592 log_no_data_exists(l_not_found);
593
594 -- This will commit the changes to the interface table record.
595 -- if an error was encountered then all the unwanted changes
596 -- would have been rolled back by the rollback to savepoint.
597 COMMIT WORK;
598
599 EXCEPTION
600 WHEN g_resource_busy THEN
601 log_resource_busy(g_cst_en_sua);
602 WHEN OTHERS THEN
603 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
604 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_as_suarc');
605 IGS_GE_MSG_STACK.ADD;
606 APP_EXCEPTION.RAISE_EXCEPTION;
607
608
609 END process_as_suarc;
610
611 --- end as_suarc
612
613 PROCEDURE process_en_spa(
614 p_batch_id IN NUMBER,
615 p_deletion_flag IN BOOLEAN
616 ) AS
617 /*----------------------------------------------------------------------------
618 || Created By :
619 || Created On :
620 || Purpose :
621 || Known limitations, enhancements or remarks :
622 || Change History :
623 || Who When What
624 || kkillams 20-12-2002 Removed the if clause before calling "ROLLBACK TO savepoint_en_spa;"
625 || which bybass the rollback if return status is 'W', w.r.t. bug no :2717455
626 ------------------------------------------------------------------------------*/
627 CURSOR c_en_spa IS
628 SELECT *
629 FROM igs_en_lgcy_spa_int
630 WHERE batch_id = p_batch_id
631 AND import_status IN ('U','R')
632 ORDER BY person_number ASC,
633 NVL(key_program,'N') DESC,
634 NVL(primary_program_type,'SECONDARY') ASC,
635 commencement_dt ASC
636 FOR UPDATE NOWAIT;
637
638
639 lr_en_spa_rec igs_en_spa_lgcy_pub.sca_re_rec_type;
640 l_return_status VARCHAR2(1);
641 l_msg_count NUMBER(4);
642 l_msg_data VARCHAR2(4000);
643
644 l_last_update_date DATE ;
645 l_last_updated_by NUMBER;
646 l_last_update_login NUMBER;
647 l_request_id NUMBER;
648 l_program_id NUMBER;
649 l_program_application_id NUMBER;
650 l_program_update_date DATE;
651 l_not_found BOOLEAN;
652
653 BEGIN
654 /*
655 Process the records in the Student program attempt interface table
656 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
657
658 The Order by Clause in the c_en_spa, orders the records in such a way that
659 all key programs are processed first and within the key programs all primary
660 programs are processed first.
661 */
662 -- log the header in the format shown below
663 -- "Row Type Student program Attempt"
664 log_headers(g_cst_en_spa);
665 l_not_found := TRUE;
666
667 FOR l_en_spa_cur_rec IN c_en_spa LOOP
668 BEGIN
669
670 -- create a save point. if there are any errors returned by
671 -- the API then rollback to this savepoint before processing
672 -- then next record.
673 SAVEPOINT savepoint_en_spa;
674 l_not_found := FALSE;
675
676 -- populate the record variable to pass to the API
677 lr_en_spa_rec.person_number := l_en_spa_cur_rec.person_number ;
678 lr_en_spa_rec.program_cd := l_en_spa_cur_rec.program_cd ;
679 lr_en_spa_rec.version_number := l_en_spa_cur_rec.version_number ;
680 lr_en_spa_rec.cal_type := l_en_spa_cur_rec.cal_type ;
681 lr_en_spa_rec.location_cd := l_en_spa_cur_rec.location_cd ;
682 lr_en_spa_rec.attendance_mode := l_en_spa_cur_rec.attendance_mode ;
683 lr_en_spa_rec.attendance_type := l_en_spa_cur_rec.attendance_type ;
684 lr_en_spa_rec.student_confirmed_ind := l_en_spa_cur_rec.student_confirmed_ind ;
685 lr_en_spa_rec.commencement_dt := l_en_spa_cur_rec.commencement_dt ;
686 lr_en_spa_rec.primary_program_type := l_en_spa_cur_rec.primary_program_type ;
687 lr_en_spa_rec.primary_prog_type_source := l_en_spa_cur_rec.primary_prog_type_source ;
688 lr_en_spa_rec.key_program := l_en_spa_cur_rec.key_program ;
689 lr_en_spa_rec.provisional_ind := l_en_spa_cur_rec.provisional_ind ;
690 lr_en_spa_rec.discontinued_dt := l_en_spa_cur_rec.discontinued_dt ;
691 lr_en_spa_rec.discontinuation_reason_cd := l_en_spa_cur_rec.discontinuation_reason_cd ;
692 lr_en_spa_rec.lapsed_dt := l_en_spa_cur_rec.lapsed_dt ;
693 lr_en_spa_rec.funding_source := l_en_spa_cur_rec.funding_source ;
694 lr_en_spa_rec.exam_location_cd := l_en_spa_cur_rec.exam_location_cd ;
695 lr_en_spa_rec.nominated_completion_yr := l_en_spa_cur_rec.nominated_completion_yr ;
696 lr_en_spa_rec.nominated_completion_perd := l_en_spa_cur_rec.nominated_completion_perd ;
697 lr_en_spa_rec.rule_check_ind := l_en_spa_cur_rec.rule_check_ind ;
698 lr_en_spa_rec.waive_option_check_ind := l_en_spa_cur_rec.waive_option_check_ind ;
699 lr_en_spa_rec.last_rule_check_dt := l_en_spa_cur_rec.last_rule_check_dt ;
700 lr_en_spa_rec.publish_outcomes_ind := l_en_spa_cur_rec.publish_outcomes_ind ;
701 lr_en_spa_rec.course_rqrmnt_complete_ind := l_en_spa_cur_rec.course_rqrmnt_complete_ind ;
702 lr_en_spa_rec.course_rqrmnts_complete_dt := l_en_spa_cur_rec.course_rqrmnts_complete_dt ;
703 lr_en_spa_rec.s_completed_source_type := l_en_spa_cur_rec.s_completed_source_type ;
704 lr_en_spa_rec.advanced_standing_ind := l_en_spa_cur_rec.advanced_standing_ind ;
705 lr_en_spa_rec.fee_cat := l_en_spa_cur_rec.fee_cat ;
706 lr_en_spa_rec.correspondence_cat := l_en_spa_cur_rec.correspondence_cat ;
707 lr_en_spa_rec.self_help_group_ind := l_en_spa_cur_rec.self_help_group_ind ;
708 lr_en_spa_rec.adm_admission_appl_number := l_en_spa_cur_rec.adm_admission_appl_number ;
709 lr_en_spa_rec.adm_nominated_course_cd := l_en_spa_cur_rec.adm_nominated_course_cd ;
710 lr_en_spa_rec.adm_sequence_number := l_en_spa_cur_rec.adm_sequence_number ;
711 lr_en_spa_rec.class_standing_override := l_en_spa_cur_rec.class_standing_override ;
712 lr_en_spa_rec.catalog_cal_alternate_code := l_en_spa_cur_rec.catalog_cal_alternate_code ;
713 lr_en_spa_rec.override_cmpl_dt := l_en_spa_cur_rec.override_cmpl_dt ;
714 lr_en_spa_rec.manual_ovr_cmpl_dt_ind := l_en_spa_cur_rec.manual_ovr_cmpl_dt_ind ;
715 lr_en_spa_rec.attribute_category := l_en_spa_cur_rec.attribute_category ;
716 lr_en_spa_rec.attribute1 := l_en_spa_cur_rec.attribute1 ;
717 lr_en_spa_rec.attribute2 := l_en_spa_cur_rec.attribute2 ;
718 lr_en_spa_rec.attribute3 := l_en_spa_cur_rec.attribute3 ;
719 lr_en_spa_rec.attribute4 := l_en_spa_cur_rec.attribute4 ;
720 lr_en_spa_rec.attribute5 := l_en_spa_cur_rec.attribute5 ;
721 lr_en_spa_rec.attribute6 := l_en_spa_cur_rec.attribute6 ;
722 lr_en_spa_rec.attribute7 := l_en_spa_cur_rec.attribute7 ;
723 lr_en_spa_rec.attribute8 := l_en_spa_cur_rec.attribute8 ;
724 lr_en_spa_rec.attribute9 := l_en_spa_cur_rec.attribute9 ;
725 lr_en_spa_rec.attribute10 := l_en_spa_cur_rec.attribute10 ;
726 lr_en_spa_rec.attribute11 := l_en_spa_cur_rec.attribute11 ;
727 lr_en_spa_rec.attribute12 := l_en_spa_cur_rec.attribute12 ;
728 lr_en_spa_rec.attribute13 := l_en_spa_cur_rec.attribute13 ;
729 lr_en_spa_rec.attribute14 := l_en_spa_cur_rec.attribute14 ;
730 lr_en_spa_rec.attribute15 := l_en_spa_cur_rec.attribute15 ;
731 lr_en_spa_rec.attribute16 := l_en_spa_cur_rec.attribute16 ;
732 lr_en_spa_rec.attribute17 := l_en_spa_cur_rec.attribute17 ;
733 lr_en_spa_rec.attribute18 := l_en_spa_cur_rec.attribute18 ;
734 lr_en_spa_rec.attribute19 := l_en_spa_cur_rec.attribute19 ;
735 lr_en_spa_rec.attribute20 := l_en_spa_cur_rec.attribute20 ;
736 lr_en_spa_rec.re_attendance_percentage := l_en_spa_cur_rec.re_attendance_percentage ;
737 lr_en_spa_rec.re_govt_type_of_activity_cd := l_en_spa_cur_rec.re_govt_type_of_activity_cd ;
738 lr_en_spa_rec.re_max_submission_dt := l_en_spa_cur_rec.re_max_submission_dt ;
739 lr_en_spa_rec.re_min_submission_dt := l_en_spa_cur_rec.re_min_submission_dt ;
740 lr_en_spa_rec.re_research_topic := l_en_spa_cur_rec.re_research_topic ;
741 lr_en_spa_rec.re_industry_links := l_en_spa_cur_rec.re_industry_links ;
742
743 l_return_status := NULL;
744 l_msg_count := NULL;
745 l_msg_data := NULL;
746 igs_en_spa_lgcy_pub.create_spa(
747 P_API_VERSION => 1.0,
748 P_INIT_MSG_LIST => FND_API.G_TRUE,
749 P_COMMIT => FND_API.G_FALSE,
750 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
751 P_SCA_RE_REC => lr_en_spa_rec,
752 X_RETURN_STATUS => l_return_status,
753 X_MSG_COUNT => l_msg_count,
754 X_MSG_DATA => l_msg_data
755 );
756
757 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
758 ROLLBACK TO savepoint_en_spa;
759 -- log the error message in the error message interface table
760 log_err_messages(
761 g_cst_en_spa,
762 l_en_spa_cur_rec.legacy_en_spa_int_id,
763 l_msg_count,
764 l_msg_data,
765 l_return_status);
766 ELSE
767 -- log the warning messages in the concurrent manager log.
768 -- spa is a special case since the program attempt may not insert since
769 -- that record is already present hence return a warning and the thesis details
770 -- would have to be inserted or visa versa.
771 IF l_msg_count > 0 THEN
772 log_err_messages(
773 g_cst_en_spa,
774 l_en_spa_cur_rec.legacy_en_spa_int_id,
775 l_msg_count,
776 l_msg_data,
777 'W');
778 END IF;
779 -- log the success message in the concurrent manager log.
780 log_suc_message(l_en_spa_cur_rec.legacy_en_spa_int_id);
781 END IF;
782
783 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
784 -- delete any records in the error message interface table
785 delete_err_messages(
786 p_int_table_code => g_cst_en_spa,
787 p_int_table_id => l_en_spa_cur_rec.legacy_en_spa_int_id
788 );
789 -- delete the interface record
790 DELETE FROM igs_en_lgcy_spa_int
791 WHERE legacy_en_spa_int_id = l_en_spa_cur_rec.legacy_en_spa_int_id;
792 ELSE
793 l_last_updated_by := get_last_updated_by;
794 l_last_update_date := get_last_update_date;
795 l_last_update_login := get_last_update_login;
796 l_request_id := get_request_id;
797 l_program_application_id := get_program_application_id;
798 l_program_id := get_program_id;
799 l_program_update_date := get_program_update_date;
800 UPDATE igs_en_lgcy_spa_int
801 SET import_status = DECODE(l_return_status,
802 'S','I',
803 'U','E',
804 'W','W',
805 'E'),
806 last_update_date = l_last_update_date,
807 last_updated_by = l_last_updated_by,
808 last_update_login = l_last_update_login,
809 request_id = l_request_id,
810 program_id = l_program_id,
811 program_application_id = l_program_application_id,
812 program_update_date = l_program_update_date
813 WHERE legacy_en_spa_int_id = l_en_spa_cur_rec.legacy_en_spa_int_id;
814 END IF;
815
816 EXCEPTION -- for the begin end block with in the For loop
817 WHEN OTHERS THEN
818 ROLLBACK TO savepoint_en_spa;
819 END;
820 END LOOP;
821
822 log_no_data_exists(l_not_found);
823
824 -- This will commit the changes to the interface table record.
825 -- if an error was encountered then all the unwanted changes
826 -- would have been rolled back by the rollback to savepoint.
827 COMMIT WORK;
828
829 EXCEPTION
830 WHEN g_resource_busy THEN
831 log_resource_busy(g_cst_en_spa);
832 WHEN OTHERS THEN
833 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
834 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_en_spa');
835 IGS_GE_MSG_STACK.ADD;
836 APP_EXCEPTION.RAISE_EXCEPTION;
837 END process_en_spa;
838
839
840 -------------------------------------------------------------------------------------
841 PROCEDURE process_en_spat(
842 p_batch_id IN NUMBER,
843 p_deletion_flag IN BOOLEAN) AS
844 /*----------------------------------------------------------------------------
845 || Created By : vkarthik
846 || Created On : 5/Dec/2003
847 || Purpose : to proces term records from the interface table
848 || Known limitations, enhancements or remarks :
849 || Change History :
850 || Who When What
851 ||
852 ------------------------------------------------------------------------------*/
853
854 CURSOR c_lgy_spat_int IS
855 -- cursor to pick out all records with input batch_id and status = 'ready or unprocessed'
856 SELECT * FROM igs_en_lgy_spat_int
857 WHERE
858 batch_id = p_batch_id AND
859 import_status IN ('U', 'R')
860 ORDER BY
861 person_number ASC,
862 program_cd ASC,
863 legacy_en_spat_int_id ASC
864 FOR UPDATE NOWAIT;
865
866 l_not_found BOOLEAN;
867 l_return_status VARCHAR2(1);
868 l_msg_count NUMBER(4);
869 l_msg_data VARCHAR2(4000);
870 p_spat_rec igs_en_spat_lgcy_pub.spat_rec_type;
871 l_last_updated_by NUMBER;
872 l_last_update_date DATE;
873 l_last_update_login NUMBER;
874 l_request_id NUMBER;
875 l_program_application_id NUMBER;
876 l_program_id NUMBER;
877 l_program_update_date DATE;
878
879 BEGIN
880 log_headers(g_cst_en_spat);
881 l_not_found := TRUE;
882 FOR lc_lgy_spat_int IN c_lgy_spat_int
883 LOOP
884 BEGIN
885 -- create a savepoint so that in case of error, the state can be rolled back
886 SAVEPOINT process_en_spat_save;
887 l_not_found := FALSE;
888 -- populate the record variable to pass to the api
889 p_spat_rec.person_number := lc_lgy_spat_int.person_number;
890 p_spat_rec.program_cd := lc_lgy_spat_int.program_cd;
891 p_spat_rec.program_version := lc_lgy_spat_int.program_version;
892 p_spat_rec.key_program_flag := lc_lgy_spat_int.key_program_flag;
893 p_spat_rec.acad_cal_type := lc_lgy_spat_int.acad_cal_type;
894 p_spat_rec.location_cd := lc_lgy_spat_int.location_cd;
895 p_spat_rec.attendance_mode := lc_lgy_spat_int.attendance_mode;
896 p_spat_rec.attendance_type := lc_lgy_spat_int.attendance_type;
897 p_spat_rec.class_standing := lc_lgy_spat_int.class_standing;
898 p_spat_rec.fee_cat := lc_lgy_spat_int.fee_cat;
899 p_spat_rec.term_cal_alternate_cd:= lc_lgy_spat_int.term_cal_alternate_cd;
900 p_spat_rec.attribute_category := lc_lgy_spat_int.attribute_category;
901 p_spat_rec.attribute1 := lc_lgy_spat_int.attribute1;
902 p_spat_rec.attribute2 := lc_lgy_spat_int.attribute2;
903 p_spat_rec.attribute3 := lc_lgy_spat_int.attribute3;
904 p_spat_rec.attribute4 := lc_lgy_spat_int.attribute4;
905 p_spat_rec.attribute5 := lc_lgy_spat_int.attribute5;
906 p_spat_rec.attribute6 := lc_lgy_spat_int.attribute6;
907 p_spat_rec.attribute7 := lc_lgy_spat_int.attribute7;
908 p_spat_rec.attribute8 := lc_lgy_spat_int.attribute8;
909 p_spat_rec.attribute9 := lc_lgy_spat_int.attribute9;
910 p_spat_rec.attribute10 := lc_lgy_spat_int.attribute10;
911 p_spat_rec.attribute11 := lc_lgy_spat_int.attribute11;
912 p_spat_rec.attribute12 := lc_lgy_spat_int.attribute12;
913 p_spat_rec.attribute13 := lc_lgy_spat_int.attribute13;
914 p_spat_rec.attribute14 := lc_lgy_spat_int.attribute14;
915 p_spat_rec.attribute15 := lc_lgy_spat_int.attribute15;
916 p_spat_rec.attribute16 := lc_lgy_spat_int.attribute16;
917 p_spat_rec.attribute17 := lc_lgy_spat_int.attribute17;
918 p_spat_rec.attribute18 := lc_lgy_spat_int.attribute18;
919 p_spat_rec.attribute19 := lc_lgy_spat_int.attribute19;
920 p_spat_rec.attribute20 := lc_lgy_spat_int.attribute20;
921
922 l_return_status := NULL;
923 l_msg_count := NULL;
924 l_msg_data := NULL;
925
926 -- begin processing the record
927 igs_en_spat_lgcy_pub.create_spa_t (
928 p_api_version => 1.0,
929 p_init_msg_list => FND_API.G_TRUE,
930 p_commit => FND_API.G_FALSE,
931 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
932 p_spat_rec => p_spat_rec,
933 x_return_status => l_return_status,
934 x_msg_count => l_msg_count,
935 x_msg_data => l_msg_data );
936
937 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR
938 l_return_status IS NULL THEN
939 -- rollback due to failure
940 ROLLBACK TO process_en_spat_save;
941 -- logging error messages
942 log_err_messages (
943 g_cst_en_spat,
944 lc_lgy_spat_int.legacy_en_spat_int_id,
945 l_msg_count,
946 l_msg_data,
947 l_return_status);
948 ELSE
949 --logging success message
950 log_suc_message(lc_lgy_spat_int.legacy_en_spat_int_id);
951 END IF;
952
953 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
954 -- delete error messages and record from spat if flag set and the process, a success
955 delete_err_messages (
956 p_int_table_code => g_cst_en_spat,
957 p_int_table_id => lc_lgy_spat_int.legacy_en_spat_int_id );
958 DELETE FROM igs_en_lgy_spat_int
959 WHERE legacy_en_spat_int_id = lc_lgy_spat_int.legacy_en_spat_int_id;
960 ELSE
961 -- get values for WHO columns
962 l_last_updated_by := get_last_updated_by;
963 l_last_update_date := get_last_update_date;
964 l_last_update_login := get_last_update_login;
965 l_request_id := get_request_id;
966 l_program_id := get_program_id;
967 l_program_application_id:= get_program_application_id;
968 l_program_update_date := get_program_update_date;
969 UPDATE igs_en_lgy_spat_int
970 SET import_status =
971 DECODE (l_return_status, 'S', 'I',
972 'U', 'E',
973 'W', 'W',
974 'E'),
975 last_updated_by = l_last_updated_by,
976 last_update_date = l_last_update_date,
977 last_update_login = l_last_update_login,
978 request_id = l_request_id,
979 program_id = l_program_id,
980 program_application_id = l_program_application_id,
981 program_update_date = l_program_update_date
982 WHERE
983 legacy_en_spat_int_id = lc_lgy_spat_int.legacy_en_spat_int_id;
984 END IF;
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 ROLLBACK TO process_en_spat_save;
989
990 END;
991 END LOOP;
992 log_no_data_exists(l_not_found);
993 COMMIT WORK;
994
995 EXCEPTION
996 WHEN g_resource_busy THEN
997 log_resource_busy(g_cst_en_spat);
998 WHEN OTHERS THEN
999 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_UNHANDLED_EXP');
1000 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_EN_LGCY_PRC.LEGACY_BATCH_PROCESS.PROCESS_EN_SPAT');
1001 IGS_GE_MSG_STACK.ADD;
1002 APP_EXCEPTION.RAISE_EXCEPTION;
1003 END process_en_spat;
1004
1005 -------------------------------------------------------------------------------
1006
1007 PROCEDURE process_en_susa(
1008 p_batch_id IN NUMBER,
1009 p_deletion_flag IN BOOLEAN
1010 ) AS
1011 CURSOR c_en_susa IS
1012 SELECT *
1013 FROM igs_en_lgy_susa_int
1014 WHERE batch_id = p_batch_id
1015 AND import_status IN ('U','R')
1016 ORDER BY person_number ASC,
1017 program_cd ASC
1018 FOR UPDATE NOWAIT;
1019
1020
1021 lr_en_susa_rec igs_en_susa_lgcy_pub.susa_rec_type ;
1022 l_return_status VARCHAR2(1);
1023 l_msg_count NUMBER(4);
1024 l_msg_data VARCHAR2(4000);
1025
1026 l_last_update_date DATE ;
1027 l_last_updated_by NUMBER;
1028 l_last_update_login NUMBER;
1029 l_request_id NUMBER;
1030 l_program_id NUMBER;
1031 l_program_application_id NUMBER;
1032 l_program_update_date DATE;
1033 l_not_found BOOLEAN;
1034
1035 BEGIN
1036 /*
1037 Process the records in the Student Unit Set attempt interface table
1038 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
1039 */
1040
1041
1042 log_headers(g_cst_en_susa);
1043 l_not_found := TRUE;
1044
1045 FOR l_en_susa_cur_rec IN c_en_susa LOOP
1046 BEGIN
1047
1048 -- create a save point. if there are any errors returned by
1049 -- the API then rollback to this savepoint before processing
1050 -- then next record.
1051 SAVEPOINT savepoint_en_susa;
1052 l_not_found := FALSE;
1053
1054 -- populate the record variable to pass to the API
1055 lr_en_susa_rec.person_number := l_en_susa_cur_rec.person_number ;
1056 lr_en_susa_rec.program_cd := l_en_susa_cur_rec.program_cd ;
1057 lr_en_susa_rec.unit_set_cd := l_en_susa_cur_rec.unit_set_cd ;
1058 lr_en_susa_rec.us_version_number := l_en_susa_cur_rec.us_version_number ;
1059 lr_en_susa_rec.selection_dt := l_en_susa_cur_rec.selection_dt ;
1060 lr_en_susa_rec.student_confirmed_ind := l_en_susa_cur_rec.student_confirmed_ind ;
1061 lr_en_susa_rec.end_dt := l_en_susa_cur_rec.end_dt ;
1062 lr_en_susa_rec.parent_unit_set_cd := l_en_susa_cur_rec.parent_unit_set_cd ;
1063 lr_en_susa_rec.primary_set_ind := l_en_susa_cur_rec.primary_set_ind ;
1064 lr_en_susa_rec.voluntary_end_ind := l_en_susa_cur_rec.voluntary_end_ind ;
1065 lr_en_susa_rec.authorised_person_number := l_en_susa_cur_rec.authorised_person_number ;
1066 lr_en_susa_rec.authorised_on := l_en_susa_cur_rec.authorised_on ;
1067 lr_en_susa_rec.override_title := l_en_susa_cur_rec.override_title ;
1068 lr_en_susa_rec.rqrmnts_complete_ind := l_en_susa_cur_rec.rqrmnts_complete_ind ;
1069 lr_en_susa_rec.rqrmnts_complete_dt := l_en_susa_cur_rec.rqrmnts_complete_dt ;
1070 lr_en_susa_rec.s_completed_source_type := l_en_susa_cur_rec.s_completed_source_type ;
1071 lr_en_susa_rec.catalog_cal_alternate_code := l_en_susa_cur_rec.catalog_cal_alternate_code ;
1072 lr_en_susa_rec.attribute_category := l_en_susa_cur_rec.attribute_category ;
1073 lr_en_susa_rec.attribute1 := l_en_susa_cur_rec.attribute1 ;
1074 lr_en_susa_rec.attribute2 := l_en_susa_cur_rec.attribute2 ;
1075 lr_en_susa_rec.attribute3 := l_en_susa_cur_rec.attribute3 ;
1076 lr_en_susa_rec.attribute4 := l_en_susa_cur_rec.attribute4 ;
1077 lr_en_susa_rec.attribute5 := l_en_susa_cur_rec.attribute5 ;
1078 lr_en_susa_rec.attribute6 := l_en_susa_cur_rec.attribute6 ;
1079 lr_en_susa_rec.attribute7 := l_en_susa_cur_rec.attribute7 ;
1080 lr_en_susa_rec.attribute8 := l_en_susa_cur_rec.attribute8 ;
1081 lr_en_susa_rec.attribute9 := l_en_susa_cur_rec.attribute9 ;
1082 lr_en_susa_rec.attribute10 := l_en_susa_cur_rec.attribute10 ;
1083 lr_en_susa_rec.attribute11 := l_en_susa_cur_rec.attribute11 ;
1084 lr_en_susa_rec.attribute12 := l_en_susa_cur_rec.attribute12 ;
1085 lr_en_susa_rec.attribute13 := l_en_susa_cur_rec.attribute13 ;
1086 lr_en_susa_rec.attribute14 := l_en_susa_cur_rec.attribute14 ;
1087 lr_en_susa_rec.attribute15 := l_en_susa_cur_rec.attribute15 ;
1088 lr_en_susa_rec.attribute16 := l_en_susa_cur_rec.attribute16 ;
1089 lr_en_susa_rec.attribute17 := l_en_susa_cur_rec.attribute17 ;
1090 lr_en_susa_rec.attribute18 := l_en_susa_cur_rec.attribute18 ;
1091 lr_en_susa_rec.attribute19 := l_en_susa_cur_rec.attribute19 ;
1092 lr_en_susa_rec.attribute20 := l_en_susa_cur_rec.attribute20 ;
1093
1094
1095 l_return_status := NULL;
1096 l_msg_count := NULL;
1097 l_msg_data := NULL;
1098 igs_en_susa_lgcy_pub.create_unit_set_atmpt(
1099 P_API_VERSION => 1.0,
1100 P_INIT_MSG_LIST => FND_API.G_TRUE,
1101 P_COMMIT => FND_API.G_FALSE,
1102 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1103 p_susa_rec => lr_en_susa_rec,
1104 X_RETURN_STATUS => l_return_status,
1105 X_MSG_COUNT => l_msg_count,
1106 X_MSG_DATA => l_msg_data
1107 );
1108
1109 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
1110 ROLLBACK TO savepoint_en_susa;
1111 -- log the error message in the error message interface table
1112 log_err_messages(
1113 g_cst_en_susa ,
1114 l_en_susa_cur_rec.legacy_en_susa_int_id,
1115 l_msg_count,
1116 l_msg_data,
1117 l_return_status
1118 );
1119 ELSE
1120 -- log the success message in the concurrent manager log.
1121 log_suc_message(l_en_susa_cur_rec.legacy_en_susa_int_id);
1122 END IF;
1123
1124 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1125 -- delete any records in the error message interface table
1126 delete_err_messages(
1127 p_int_table_code => g_cst_en_susa,
1128 p_int_table_id => l_en_susa_cur_rec.legacy_en_susa_int_id
1129 );
1130 -- delete the interface record
1131 DELETE FROM igs_en_lgy_susa_int
1132 WHERE legacy_en_susa_int_id = l_en_susa_cur_rec.legacy_en_susa_int_id;
1133 ELSE
1134 l_last_updated_by := get_last_updated_by;
1135 l_last_update_date := get_last_update_date;
1136 l_last_update_login := get_last_update_login;
1137 l_request_id := get_request_id;
1138 l_program_application_id := get_program_application_id;
1139 l_program_id := get_program_id;
1140 l_program_update_date := get_program_update_date;
1141 UPDATE igs_en_lgy_susa_int
1142 SET import_status = DECODE(l_return_status,
1143 'S','I',
1144 'U','E',
1145 'W','W',
1146 'E'),
1147 last_update_date = l_last_update_date,
1148 last_updated_by = l_last_updated_by,
1149 last_update_login = l_last_update_login,
1150 request_id = l_request_id,
1151 program_id = l_program_id,
1152 program_application_id = l_program_application_id,
1153 program_update_date = l_program_update_date
1154 WHERE legacy_en_susa_int_id = l_en_susa_cur_rec.legacy_en_susa_int_id;
1155 END IF;
1156
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159 ROLLBACK TO savepoint_en_susa;
1160 END;
1161 END LOOP;
1162
1163 log_no_data_exists(l_not_found);
1164
1165 -- This will commit the changes to the interface table record.
1166 -- if an error was encountered then all the unwanted changes
1167 -- would have been rolled back by the rollback to savepoint.
1168 COMMIT WORK;
1169
1170 EXCEPTION
1171 WHEN g_resource_busy THEN
1172 log_resource_busy(g_cst_en_susa);
1173 WHEN OTHERS THEN
1174 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1175 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_en_susa');
1176 IGS_GE_MSG_STACK.ADD;
1177 APP_EXCEPTION.RAISE_EXCEPTION;
1178 END process_en_susa;
1179
1180
1181
1182 -------------------------------------------------------------------------------
1183 PROCEDURE process_en_spi(
1184 p_batch_id IN NUMBER,
1185 p_deletion_flag IN BOOLEAN
1186 ) AS
1187 CURSOR c_en_spi IS
1188 SELECT *
1189 FROM igs_en_lgcy_spi_int
1190 WHERE batch_id = p_batch_id
1191 AND import_status IN ('U','R')
1192 ORDER BY person_number ASC,
1193 program_cd ASC
1194 FOR UPDATE NOWAIT;
1195
1196
1197 lr_en_spi_rec igs_en_spi_lgcy_pub.en_spi_rec_type ;
1198 l_return_status VARCHAR2(1);
1199 l_msg_count NUMBER(4);
1200 l_msg_data VARCHAR2(4000);
1201
1202 l_last_update_date DATE ;
1203 l_last_updated_by NUMBER;
1204 l_last_update_login NUMBER;
1205 l_request_id NUMBER;
1206 l_program_id NUMBER;
1207 l_program_application_id NUMBER;
1208 l_program_update_date DATE;
1209 l_not_found BOOLEAN;
1210
1211 BEGIN
1212 /*
1213 Process the records in the Student program intermission interface table
1214 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
1215 */
1216
1217 log_headers(g_cst_en_spi);
1218 l_not_found := TRUE;
1219
1220 FOR l_en_spi_cur_rec IN c_en_spi LOOP
1221 BEGIN
1222
1223 -- create a save point. if there are any errors returned by
1224 -- the API then rollback to this savepoint before processing
1225 -- then next record.
1226 SAVEPOINT savepoint_en_spi;
1227 l_not_found := FALSE;
1228
1229 -- populate the record variable to pass to the API
1230 lr_en_spi_rec.person_number := l_en_spi_cur_rec.person_number ;
1231 lr_en_spi_rec.program_cd := l_en_spi_cur_rec.program_cd ;
1232 lr_en_spi_rec.start_dt := l_en_spi_cur_rec.start_dt ;
1233 lr_en_spi_rec.end_dt := l_en_spi_cur_rec.end_dt ;
1234 lr_en_spi_rec.voluntary_ind := l_en_spi_cur_rec.voluntary_ind ;
1235 lr_en_spi_rec.comments := l_en_spi_cur_rec.comments ;
1236 lr_en_spi_rec.intermission_type := l_en_spi_cur_rec.intermission_type ;
1237 lr_en_spi_rec.approved := l_en_spi_cur_rec.approved ;
1238 lr_en_spi_rec.institution_name := l_en_spi_cur_rec.institution_name ;
1239 lr_en_spi_rec.max_credit_pts := l_en_spi_cur_rec.max_credit_pts ;
1240 lr_en_spi_rec.max_terms := l_en_spi_cur_rec.max_terms ;
1241 lr_en_spi_rec.anticipated_credit_points := l_en_spi_cur_rec.anticipated_credit_points ;
1242 lr_en_spi_rec.approver_person_number := l_en_spi_cur_rec.approver_person_number ;
1243 lr_en_spi_rec.attribute_category := l_en_spi_cur_rec.attribute_category ;
1244 lr_en_spi_rec.attribute1 := l_en_spi_cur_rec.attribute1 ;
1245 lr_en_spi_rec.attribute2 := l_en_spi_cur_rec.attribute2 ;
1246 lr_en_spi_rec.attribute3 := l_en_spi_cur_rec.attribute3 ;
1247 lr_en_spi_rec.attribute4 := l_en_spi_cur_rec.attribute4 ;
1248 lr_en_spi_rec.attribute5 := l_en_spi_cur_rec.attribute5 ;
1249 lr_en_spi_rec.attribute6 := l_en_spi_cur_rec.attribute6 ;
1250 lr_en_spi_rec.attribute7 := l_en_spi_cur_rec.attribute7 ;
1251 lr_en_spi_rec.attribute8 := l_en_spi_cur_rec.attribute8 ;
1252 lr_en_spi_rec.attribute9 := l_en_spi_cur_rec.attribute9 ;
1253 lr_en_spi_rec.attribute10 := l_en_spi_cur_rec.attribute10 ;
1254 lr_en_spi_rec.attribute11 := l_en_spi_cur_rec.attribute11 ;
1255 lr_en_spi_rec.attribute12 := l_en_spi_cur_rec.attribute12 ;
1256 lr_en_spi_rec.attribute13 := l_en_spi_cur_rec.attribute13 ;
1257 lr_en_spi_rec.attribute14 := l_en_spi_cur_rec.attribute14 ;
1258 lr_en_spi_rec.attribute15 := l_en_spi_cur_rec.attribute15 ;
1259 lr_en_spi_rec.attribute16 := l_en_spi_cur_rec.attribute16 ;
1260 lr_en_spi_rec.attribute17 := l_en_spi_cur_rec.attribute17 ;
1261 lr_en_spi_rec.attribute18 := l_en_spi_cur_rec.attribute18 ;
1262 lr_en_spi_rec.attribute19 := l_en_spi_cur_rec.attribute19 ;
1263 lr_en_spi_rec.attribute20 := l_en_spi_cur_rec.attribute20 ;
1264 lr_en_spi_rec.COND_RETURN_FLAG := l_en_spi_cur_rec.cond_return_flag;
1265
1266
1267 l_return_status := NULL;
1268 l_msg_count := NULL;
1269 l_msg_data := NULL;
1270 igs_en_spi_lgcy_pub.create_student_intm(
1271 P_API_VERSION => 1.0,
1272 P_INIT_MSG_LIST => FND_API.G_TRUE,
1273 P_COMMIT => FND_API.G_FALSE,
1274 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1275 p_intermiss_rec => lr_en_spi_rec,
1276 X_RETURN_STATUS => l_return_status,
1277 X_MSG_COUNT => l_msg_count,
1278 X_MSG_DATA => l_msg_data
1279 );
1280
1281 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
1282 ROLLBACK TO savepoint_en_spi;
1283 -- log the error message in the error message interface table
1284 log_err_messages(
1285 g_cst_en_spi ,
1286 l_en_spi_cur_rec.legacy_en_spi_int_id,
1287 l_msg_count,
1288 l_msg_data,
1289 l_return_status
1290 );
1291 ELSE
1292 -- log the success message in the concurrent manager log.
1293 log_suc_message(l_en_spi_cur_rec.legacy_en_spi_int_id );
1294 END IF;
1295
1296 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1297 -- delete any records in the error message interface table
1298 delete_err_messages(
1299 p_int_table_code => g_cst_en_spi,
1300 p_int_table_id => l_en_spi_cur_rec.legacy_en_spi_int_id
1301 );
1302
1303 -- delete the interface record
1304 DELETE FROM igs_en_lgcy_spi_int
1305 WHERE legacy_en_spi_int_id = l_en_spi_cur_rec.legacy_en_spi_int_id ;
1306 ELSE
1307 l_last_updated_by := get_last_updated_by;
1308 l_last_update_date := get_last_update_date;
1309 l_last_update_login := get_last_update_login;
1310 l_request_id := get_request_id;
1311 l_program_application_id := get_program_application_id;
1312 l_program_id := get_program_id;
1313 l_program_update_date := get_program_update_date;
1314 UPDATE igs_en_lgcy_spi_int
1315 SET import_status = DECODE(l_return_status,
1316 'S','I',
1317 'U','E',
1318 'W','W',
1319 'E'),
1320 last_update_date = l_last_update_date,
1321 last_updated_by = l_last_updated_by,
1322 last_update_login = l_last_update_login,
1323 request_id = l_request_id,
1324 program_id = l_program_id,
1325 program_application_id = l_program_application_id,
1326 program_update_date = l_program_update_date
1327 WHERE legacy_en_spi_int_id = l_en_spi_cur_rec.legacy_en_spi_int_id ;
1328 END IF;
1329
1330 EXCEPTION
1331 WHEN OTHERS THEN
1332 ROLLBACK TO savepoint_en_spi;
1333 END;
1334 END LOOP;
1335
1336 log_no_data_exists(l_not_found);
1337
1338 -- This will commit the changes to the interface table record.
1339 -- if an error was encountered then all the unwanted changes
1340 -- would have been rolled back by the rollback to savepoint.
1341 COMMIT WORK;
1342
1343 EXCEPTION
1344 WHEN g_resource_busy THEN
1345 log_resource_busy(g_cst_en_spi);
1346 WHEN OTHERS THEN
1347 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1348 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_en_spi');
1349 IGS_GE_MSG_STACK.ADD;
1350 APP_EXCEPTION.RAISE_EXCEPTION;
1351 END process_en_spi;
1352
1353
1354 -------------------------------------------------------------------------------
1355 --anilk, modified as per Program Completion Validation build, Bug# 3129913
1356 PROCEDURE process_en_spaa(
1357 p_batch_id IN NUMBER,
1358 p_deletion_flag IN BOOLEAN
1359 ) AS
1360 CURSOR c_en_spaa IS
1361 SELECT *
1362 FROM igs_en_lgy_spaa_int
1363 WHERE batch_id = p_batch_id
1364 AND import_status IN ('U','R')
1365 ORDER BY person_number ASC,
1366 program_cd ASC
1367 FOR UPDATE NOWAIT;
1368
1369
1370 lr_en_spaa_rec igs_en_spaa_lgcy_pub.awd_aim_rec_type;
1371 l_return_status VARCHAR2(1);
1372 l_msg_count NUMBER(4);
1373 l_msg_data VARCHAR2(4000);
1374
1375 l_last_update_date DATE ;
1376 l_last_updated_by NUMBER;
1377 l_last_update_login NUMBER;
1378 l_request_id NUMBER;
1379 l_program_id NUMBER;
1380 l_program_application_id NUMBER;
1381 l_program_update_date DATE;
1382 l_not_found BOOLEAN;
1383
1384 BEGIN
1385 /*
1386 Process the records in the Student program award aim interface table
1387 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
1388 */
1389
1390
1391 log_headers(g_cst_en_spaa);
1392 l_not_found := TRUE;
1393
1394 FOR l_en_spaa_cur_rec IN c_en_spaa LOOP
1395 BEGIN
1396
1397 -- create a save point. if there are any errors returned by
1398 -- the API then rollback to this savepoint before processing
1399 -- then next record.
1400 SAVEPOINT savepoint_en_spaa;
1401 l_not_found := FALSE;
1402
1403 -- populate the record variable to pass to the API
1404 lr_en_spaa_rec.person_number := l_en_spaa_cur_rec.person_number ;
1405 lr_en_spaa_rec.program_cd := l_en_spaa_cur_rec.program_cd ;
1406 lr_en_spaa_rec.award_cd := l_en_spaa_cur_rec.award_cd ;
1407 lr_en_spaa_rec.start_dt := l_en_spaa_cur_rec.start_dt ;
1408 lr_en_spaa_rec.end_dt := l_en_spaa_cur_rec.end_dt ;
1409 lr_en_spaa_rec.complete_ind := l_en_spaa_cur_rec.complete_ind ;
1410 lr_en_spaa_rec.conferral_dt := l_en_spaa_cur_rec.conferral_dt ;
1411 lr_en_spaa_rec.award_mark := l_en_spaa_cur_rec.award_mark ;
1412 lr_en_spaa_rec.award_grade := l_en_spaa_cur_rec.award_grade ;
1413 lr_en_spaa_rec.grading_schema_cd := l_en_spaa_cur_rec.grading_schema_cd ;
1414 lr_en_spaa_rec.gs_version_number := l_en_spaa_cur_rec.gs_version_number ;
1415
1416
1417 l_return_status := NULL;
1418 l_msg_count := NULL;
1419 l_msg_data := NULL;
1420 igs_en_spaa_lgcy_pub.create_student_awd_aim(
1421 P_API_VERSION => 1.0,
1422 P_INIT_MSG_LIST => FND_API.G_TRUE,
1423 P_COMMIT => FND_API.G_FALSE,
1424 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1425 p_awd_aim_rec => lr_en_spaa_rec,
1426 X_RETURN_STATUS => l_return_status,
1427 X_MSG_COUNT => l_msg_count,
1428 X_MSG_DATA => l_msg_data
1429 );
1430
1431 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
1432 ROLLBACK TO savepoint_en_spaa;
1433 -- log the error message in the error message interface table
1434 log_err_messages(
1435 g_cst_en_spaa,
1436 l_en_spaa_cur_rec.legacy_en_spaa_int_id,
1437 l_msg_count,
1438 l_msg_data,
1439 l_return_status
1440 );
1441 ELSE
1442 -- log the success message in the concurrent manager log.
1443 log_suc_message(l_en_spaa_cur_rec.legacy_en_spaa_int_id);
1444 END IF;
1445
1446 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1447 -- delete any records in the error message interface table
1448 delete_err_messages(
1449 p_int_table_code => g_cst_en_spaa,
1450 p_int_table_id => l_en_spaa_cur_rec.legacy_en_spaa_int_id
1451 );
1452 -- delete the interface record
1453 DELETE FROM igs_en_lgy_spaa_int
1454 WHERE legacy_en_spaa_int_id = l_en_spaa_cur_rec.legacy_en_spaa_int_id;
1455 ELSE
1456 l_last_updated_by := get_last_updated_by;
1457 l_last_update_date := get_last_update_date;
1458 l_last_update_login := get_last_update_login;
1459 l_request_id := get_request_id;
1460 l_program_application_id := get_program_application_id;
1461 l_program_id := get_program_id;
1462 l_program_update_date := get_program_update_date;
1463 UPDATE igs_en_lgy_spaa_int
1464 SET import_status = DECODE(l_return_status,
1465 'S','I',
1466 'U','E',
1467 'W','W',
1468 'E'),
1469 last_update_date = l_last_update_date,
1470 last_updated_by = l_last_updated_by,
1471 last_update_login = l_last_update_login,
1472 request_id = l_request_id,
1473 program_id = l_program_id,
1474 program_application_id = l_program_application_id,
1475 program_update_date = l_program_update_date
1476 WHERE legacy_en_spaa_int_id = l_en_spaa_cur_rec.legacy_en_spaa_int_id;
1477 END IF;
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 ROLLBACK TO savepoint_en_spaa;
1482 END;
1483 END LOOP;
1484
1485 log_no_data_exists(l_not_found);
1486
1487 -- This will commit the changes to the interface table record.
1488 -- if an error was encountered then all the unwanted changes
1489 -- would have been rolled back by the rollback to savepoint.
1490 COMMIT WORK;
1491
1492 EXCEPTION
1493 WHEN g_resource_busy THEN
1494 log_resource_busy(g_cst_en_spaa);
1495 WHEN OTHERS THEN
1496 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1497 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_en_spaa');
1498 IGS_GE_MSG_STACK.ADD;
1499 APP_EXCEPTION.RAISE_EXCEPTION;
1500 END process_en_spaa;
1501
1502
1503
1504 -------------------------------------------------------------------------------
1505 PROCEDURE process_re_sprvsr(
1506 p_batch_id IN NUMBER,
1507 p_deletion_flag IN BOOLEAN
1508 ) AS
1509 CURSOR c_re_sprvsr IS
1510 SELECT *
1511 FROM igs_re_lgcy_spr_int
1512 WHERE batch_id = p_batch_id
1513 AND import_status IN ('U','R')
1514 ORDER BY ca_person_number ASC,
1515 START_DT ASC
1516 FOR UPDATE NOWAIT;
1517
1518
1519 lr_re_sprvsr_rec igs_re_sprvsr_lgcy_pub.sprvsr_dtls_rec_type;
1520 l_return_status VARCHAR2(1);
1521 l_msg_count NUMBER(4);
1522 l_msg_data VARCHAR2(4000);
1523
1524 l_last_update_date DATE ;
1525 l_last_updated_by NUMBER;
1526 l_last_update_login NUMBER;
1527 l_request_id NUMBER;
1528 l_program_id NUMBER;
1529 l_program_application_id NUMBER;
1530 l_program_update_date DATE;
1531 l_not_found BOOLEAN;
1532
1533 BEGIN
1534 /*
1535 Process the records in the Research Supervisor interface table
1536 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
1537 */
1538
1539 log_headers(g_cst_re_sprvsr);
1540 l_not_found := TRUE;
1541
1542 FOR l_re_sprvsr_cur_rec IN c_re_sprvsr LOOP
1543 BEGIN
1544
1545 -- create a save point. if there are any errors returned by
1546 -- the API then rollback to this savepoint before processing
1547 -- then next record.
1548 SAVEPOINT savepoint_re_sprvsr;
1549 l_not_found := FALSE;
1550
1551 -- populate the record variable to pass to the API
1552 lr_re_sprvsr_rec.ca_person_number := l_re_sprvsr_cur_rec.ca_person_number ;
1553 lr_re_sprvsr_rec.program_cd := l_re_sprvsr_cur_rec.program_cd ;
1554 lr_re_sprvsr_rec.person_number := l_re_sprvsr_cur_rec.person_number ;
1555 lr_re_sprvsr_rec.start_dt := l_re_sprvsr_cur_rec.start_dt ;
1556 lr_re_sprvsr_rec.end_dt := l_re_sprvsr_cur_rec.end_dt ;
1557 lr_re_sprvsr_rec.research_supervisor_type := l_re_sprvsr_cur_rec.research_supervisor_type ;
1558 lr_re_sprvsr_rec.supervisor_profession := l_re_sprvsr_cur_rec.supervisor_profession ;
1559 lr_re_sprvsr_rec.supervision_percentage := l_re_sprvsr_cur_rec.supervision_percentage ;
1560 lr_re_sprvsr_rec.funding_percentage := l_re_sprvsr_cur_rec.funding_percentage ;
1561 lr_re_sprvsr_rec.org_unit_cd := l_re_sprvsr_cur_rec.org_unit_cd ;
1562 lr_re_sprvsr_rec.replaced_person_number := l_re_sprvsr_cur_rec.replaced_person_number ;
1563 lr_re_sprvsr_rec.comments := l_re_sprvsr_cur_rec.comments ;
1564
1565
1566 l_return_status := NULL;
1567 l_msg_count := NULL;
1568 l_msg_data := NULL;
1569 igs_re_sprvsr_lgcy_pub.create_sprvsr(
1570 P_API_VERSION => 1.0,
1571 P_INIT_MSG_LIST => FND_API.G_TRUE,
1572 P_COMMIT => FND_API.G_FALSE,
1573 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1574 p_sprvsr_dtls_rec => lr_re_sprvsr_rec,
1575 X_RETURN_STATUS => l_return_status,
1576 X_MSG_COUNT => l_msg_count,
1577 X_MSG_DATA => l_msg_data
1578 );
1579
1580 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
1581 ROLLBACK TO savepoint_re_sprvsr;
1582 -- log the error message in the error message interface table
1583 log_err_messages(
1584 g_cst_re_sprvsr,
1585 l_re_sprvsr_cur_rec.legacy_re_sprvsr_int_id,
1586 l_msg_count,
1587 l_msg_data,
1588 l_return_status
1589 );
1590 ELSE
1591 -- log the success message in the concurrent manager log.
1592 log_suc_message(l_re_sprvsr_cur_rec.legacy_re_sprvsr_int_id);
1593 END IF;
1594
1595 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1596 -- delete any records in the error message interface table
1597 delete_err_messages(
1598 p_int_table_code => g_cst_re_sprvsr,
1599 p_int_table_id => l_re_sprvsr_cur_rec.legacy_re_sprvsr_int_id
1600 );
1601 -- delete the interface record
1602 DELETE FROM igs_re_lgcy_spr_int
1603 WHERE legacy_re_sprvsr_int_id = l_re_sprvsr_cur_rec.legacy_re_sprvsr_int_id;
1604 ELSE
1605 l_last_updated_by := get_last_updated_by;
1606 l_last_update_date := get_last_update_date;
1607 l_last_update_login := get_last_update_login;
1608 l_request_id := get_request_id;
1609 l_program_application_id := get_program_application_id;
1610 l_program_id := get_program_id;
1611 l_program_update_date := get_program_update_date;
1612 UPDATE igs_re_lgcy_spr_int
1613 SET import_status = DECODE(l_return_status,
1614 'S','I',
1615 'U','E',
1616 'W','W',
1617 'E'),
1618 last_update_date = l_last_update_date,
1619 last_updated_by = l_last_updated_by,
1620 last_update_login = l_last_update_login,
1621 request_id = l_request_id,
1622 program_id = l_program_id,
1623 program_application_id = l_program_application_id,
1624 program_update_date = l_program_update_date
1625 WHERE legacy_re_sprvsr_int_id = l_re_sprvsr_cur_rec.legacy_re_sprvsr_int_id;
1626 END IF;
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 ROLLBACK TO savepoint_re_sprvsr;
1631 END;
1632 END LOOP;
1633
1634 log_no_data_exists(l_not_found);
1635
1636 -- This will commit the changes to the interface table record.
1637 -- if an error was encountered then all the unwanted changes
1638 -- would have been rolled back by the rollback to savepoint.
1639 COMMIT WORK;
1640
1641 EXCEPTION
1642 WHEN g_resource_busy THEN
1643 log_resource_busy(g_cst_re_sprvsr);
1644 WHEN OTHERS THEN
1645 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1646 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_re_sprvsr');
1647 IGS_GE_MSG_STACK.ADD;
1648 APP_EXCEPTION.RAISE_EXCEPTION;
1649 END process_re_sprvsr;
1650
1651
1652
1653 -------------------------------------------------------------------------------
1654 PROCEDURE process_re_the(
1655 p_batch_id IN NUMBER,
1656 p_deletion_flag IN BOOLEAN
1657 ) AS
1658 CURSOR c_re_the IS
1659 SELECT *
1660 FROM igs_re_lgcy_the_int
1661 WHERE batch_id = p_batch_id
1662 AND import_status IN ('U','R')
1663 ORDER BY person_number ASC,
1664 program_cd ASC
1665 FOR UPDATE NOWAIT;
1666
1667
1668 lr_re_the_rec igs_re_the_lgcy_pub.the_dtls_rec_type;
1669 l_return_status VARCHAR2(1);
1670 l_msg_count NUMBER(4);
1671 l_msg_data VARCHAR2(4000);
1672
1673 l_last_update_date DATE ;
1674 l_last_updated_by NUMBER;
1675 l_last_update_login NUMBER;
1676 l_request_id NUMBER;
1677 l_program_id NUMBER;
1678 l_program_application_id NUMBER;
1679 l_program_update_date DATE;
1680 l_not_found BOOLEAN;
1681
1682 BEGIN
1683 /*
1684 Process the records in the Research Thesis interface table
1685 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
1686 */
1687
1688 log_headers(g_cst_re_the);
1689 l_not_found := TRUE;
1690
1691 FOR l_re_the_cur_rec IN c_re_the LOOP
1692 BEGIN
1693
1694 -- create a save point. if there are any errors returned by
1695 -- the API then rollback to this savepoint before processing
1696 -- then next record.
1697 SAVEPOINT savepoint_re_the;
1698 l_not_found := FALSE;
1699
1700 -- populate the record variable to pass to the API
1701 lr_re_the_rec.person_number := l_re_the_cur_rec.person_number ;
1702 lr_re_the_rec.program_cd := l_re_the_cur_rec.program_cd ;
1703 lr_re_the_rec.title := l_re_the_cur_rec.title ;
1704 lr_re_the_rec.final_title_ind := l_re_the_cur_rec.final_title_ind ;
1705 lr_re_the_rec.short_title := l_re_the_cur_rec.short_title ;
1706 lr_re_the_rec.abbreviated_title := l_re_the_cur_rec.abbreviated_title ;
1707 lr_re_the_rec.final_thesis_result_cd := l_re_the_cur_rec.final_thesis_result_cd ;
1708 lr_re_the_rec.expected_submission_dt := l_re_the_cur_rec.expected_submission_dt ;
1709 lr_re_the_rec.library_lodgement_dt := l_re_the_cur_rec.library_lodgement_dt ;
1710 lr_re_the_rec.library_catalogue_number := l_re_the_cur_rec.library_catalogue_number ;
1711 lr_re_the_rec.embargo_expiry_dt := l_re_the_cur_rec.embargo_expiry_dt ;
1712 lr_re_the_rec.thesis_format := l_re_the_cur_rec.thesis_format ;
1713 lr_re_the_rec.embargo_details := l_re_the_cur_rec.embargo_details ;
1714 lr_re_the_rec.thesis_topic := l_re_the_cur_rec.thesis_topic ;
1715 lr_re_the_rec.citation := l_re_the_cur_rec.citation ;
1716 lr_re_the_rec.comments := l_re_the_cur_rec.comments ;
1717 lr_re_the_rec.submission_dt := l_re_the_cur_rec.submission_dt ;
1718 lr_re_the_rec.thesis_exam_type := l_re_the_cur_rec.thesis_exam_type ;
1719 lr_re_the_rec.thesis_panel_type := l_re_the_cur_rec.thesis_panel_type ;
1720 lr_re_the_rec.thesis_result_cd := l_re_the_cur_rec.thesis_result_cd ;
1721
1722
1723 l_return_status := NULL;
1724 l_msg_count := NULL;
1725 l_msg_data := NULL;
1726 igs_re_the_lgcy_pub.create_the(
1727 P_API_VERSION => 1.0,
1728 P_INIT_MSG_LIST => FND_API.G_TRUE,
1729 P_COMMIT => FND_API.G_FALSE,
1730 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1731 p_the_dtls_rec => lr_re_the_rec,
1732 X_RETURN_STATUS => l_return_status,
1733 X_MSG_COUNT => l_msg_count,
1734 X_MSG_DATA => l_msg_data
1735 );
1736
1737 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
1738 ROLLBACK TO savepoint_re_the;
1739 -- log the error message in the error message interface table
1740 log_err_messages(
1741 g_cst_re_the,
1742 l_re_the_cur_rec.legacy_re_the_int_id,
1743 l_msg_count,
1744 l_msg_data,
1745 l_return_status
1746 );
1747 ELSE
1748 -- log the success message in the concurrent manager log.
1749 log_suc_message(l_re_the_cur_rec.legacy_re_the_int_id);
1750 END IF;
1751
1752 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1753 -- delete any records in the error message interface table
1754 delete_err_messages(
1755 p_int_table_code => g_cst_re_the,
1756 p_int_table_id => l_re_the_cur_rec.legacy_re_the_int_id
1757 );
1758 -- delete the interface record
1759 DELETE FROM igs_re_lgcy_the_int
1760 WHERE legacy_re_the_int_id = l_re_the_cur_rec.legacy_re_the_int_id ;
1761 ELSE
1762 l_last_updated_by := get_last_updated_by;
1763 l_last_update_date := get_last_update_date;
1764 l_last_update_login := get_last_update_login;
1765 l_request_id := get_request_id;
1766 l_program_application_id := get_program_application_id;
1767 l_program_id := get_program_id;
1768 l_program_update_date := get_program_update_date;
1769 UPDATE igs_re_lgcy_the_int
1770 SET import_status = DECODE(l_return_status,
1771 'S','I',
1772 'U','E',
1773 'W','W',
1774 'E'),
1775 last_update_date = l_last_update_date,
1776 last_updated_by = l_last_updated_by,
1777 last_update_login = l_last_update_login,
1778 request_id = l_request_id,
1779 program_id = l_program_id,
1780 program_application_id = l_program_application_id,
1781 program_update_date = l_program_update_date
1782 WHERE legacy_re_the_int_id = l_re_the_cur_rec.legacy_re_the_int_id ;
1783 END IF;
1784
1785 EXCEPTION
1786 WHEN OTHERS THEN
1787 ROLLBACK TO savepoint_re_the;
1788 END;
1789 END LOOP;
1790
1791 log_no_data_exists(l_not_found);
1792
1793 -- This will commit the changes to the interface table record.
1794 -- if an error was encountered then all the unwanted changes
1795 -- would have been rolled back by the rollback to savepoint.
1796 COMMIT WORK;
1797
1798 EXCEPTION
1799 WHEN g_resource_busy THEN
1800 log_resource_busy(g_cst_re_the);
1801 WHEN OTHERS THEN
1802 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1803 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_re_the');
1804 IGS_GE_MSG_STACK.ADD;
1805 APP_EXCEPTION.RAISE_EXCEPTION;
1806 END process_re_the;
1807
1808
1809
1810 -------------------------------------------------------------------------------
1811 PROCEDURE process_en_sua(
1812 p_batch_id IN NUMBER,
1813 p_deletion_flag IN BOOLEAN
1814 ) AS
1815 -----------------------------------------------------------------------------------------------------
1816 --rvangala 07-OCT-2003 Value for CORE_INDICATOR_CODE passed to IGS_EN_SUA_API.UPDATE_UNIT_ATTEMPT
1817 -- added as part of Prevent Dropping Core Units. Enh Bug# 3052432
1818 --ptandon 16-DEC-2003 Added code to log warning messages in the concurrent log in case of
1819 -- successful unit attempts also so as to show warnings if term record
1820 -- creation fails. Term Based Fee Calc build. Bug# 2829263.
1821 -----------------------------------------------------------------------------------------------------
1822 CURSOR c_en_sua IS
1823 SELECT suai.*
1824 FROM igs_en_lgcy_sua_int suai,
1825 igs_ca_inst ci
1826 WHERE batch_id = p_batch_id
1827 AND import_status IN ('U','R')
1828 AND suai.teach_calendar_alternate_code = ci.alternate_code(+)
1829 ORDER BY suai.person_number ASC,
1830 suai.transfer_dt DESC,
1831 ci.start_dt ASC
1832 FOR UPDATE NOWAIT;
1833
1834
1835 lr_en_sua_rec igs_en_sua_lgcy_pub.sua_dtls_rec_type;
1836 l_return_status VARCHAR2(1);
1837 l_msg_count NUMBER(4);
1838 l_msg_data VARCHAR2(4000);
1839
1840 l_last_update_date DATE ;
1841 l_last_updated_by NUMBER;
1842 l_last_update_login NUMBER;
1843 l_request_id NUMBER;
1844 l_program_id NUMBER;
1845 l_program_application_id NUMBER;
1846 l_program_update_date DATE;
1847 l_not_found BOOLEAN;
1848
1849 BEGIN
1850 /*
1851 Process the records in the Student Unit attempt interface table
1852 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
1853
1854 The Order by Clause in the c_en_sua, orders the records in such a way that
1855 First all the records that do not have any tranfer details are processed first
1856 only then the record with the transfer details are imported.
1857
1858 Then the records are sorted by the start date of the teaching calender of the unit.
1859 The teaching calendar is dereived using the alternated code. It is possible that
1860 the alternate codes is wrong. If is wrong then the record would still be
1861 fetched since there is an outer join. but this record would cause errors
1862 to be thrown out by the API since the alternate code is a mandatory field
1863 and it is validated in the API.
1864 */
1865
1866 log_headers(g_cst_en_sua);
1867 l_not_found := TRUE;
1868
1869 FOR l_en_sua_cur_rec IN c_en_sua LOOP
1870 BEGIN
1871
1872 -- create a save point. if there are any errors returned by
1873 -- the API then rollback to this savepoint before processing
1874 -- then next record.
1875 SAVEPOINT savepoint_en_sua;
1876 l_not_found := FALSE;
1877
1878 -- populate the record variable to pass to the API
1879 lr_en_sua_rec.person_number := l_en_sua_cur_rec.person_number ;
1880 lr_en_sua_rec.program_cd := l_en_sua_cur_rec.program_cd ;
1881 lr_en_sua_rec.unit_cd := l_en_sua_cur_rec.unit_cd ;
1882 lr_en_sua_rec.version_number := l_en_sua_cur_rec.version_number ;
1883 lr_en_sua_rec.teach_calendar_alternate_code := l_en_sua_cur_rec.teach_calendar_alternate_code ;
1884 lr_en_sua_rec.location_cd := l_en_sua_cur_rec.location_cd ;
1885 lr_en_sua_rec.unit_class := l_en_sua_cur_rec.unit_class ;
1886 lr_en_sua_rec.enrolled_dt := l_en_sua_cur_rec.enrolled_dt ;
1887 lr_en_sua_rec.waitlisted_dt := l_en_sua_cur_rec.waitlisted_dt ;
1888 lr_en_sua_rec.dropped_ind := l_en_sua_cur_rec.dropped_ind ;
1889 lr_en_sua_rec.discontinued_dt := l_en_sua_cur_rec.discontinued_dt ;
1890 lr_en_sua_rec.administrative_unit_status := l_en_sua_cur_rec.administrative_unit_status ;
1891 lr_en_sua_rec.dcnt_reason_cd := l_en_sua_cur_rec.dcnt_reason_cd ;
1892 lr_en_sua_rec.no_assessment_ind := l_en_sua_cur_rec.no_assessment_ind ;
1893 lr_en_sua_rec.override_enrolled_cp := l_en_sua_cur_rec.override_enrolled_cp ;
1894 lr_en_sua_rec.override_achievable_cp := l_en_sua_cur_rec.override_achievable_cp ;
1895 lr_en_sua_rec.grading_schema_code := l_en_sua_cur_rec.grading_schema_code ;
1896 lr_en_sua_rec.gs_version_number := l_en_sua_cur_rec.gs_version_number ;
1897 lr_en_sua_rec.subtitle := l_en_sua_cur_rec.subtitle ;
1898 lr_en_sua_rec.student_career_transcript := l_en_sua_cur_rec.student_career_transcript ;
1899 lr_en_sua_rec.student_career_statistics := l_en_sua_cur_rec.student_career_statistics ;
1900 lr_en_sua_rec.transfer_dt := l_en_sua_cur_rec.transfer_dt ;
1901 lr_en_sua_rec.transfer_program_cd := l_en_sua_cur_rec.transfer_program_cd ;
1902 lr_en_sua_rec.outcome_dt := l_en_sua_cur_rec.outcome_dt ;
1903 lr_en_sua_rec.mark := l_en_sua_cur_rec.mark ;
1904 lr_en_sua_rec.outcome_grading_schema_code := l_en_sua_cur_rec.outcome_grading_schema_code ;
1905 lr_en_sua_rec.outcome_gs_version_number := l_en_sua_cur_rec.outcome_gs_version_number ;
1906 lr_en_sua_rec.grade := l_en_sua_cur_rec.grade ;
1907 lr_en_sua_rec.incomp_deadline_date := l_en_sua_cur_rec.incomp_deadline_date ;
1908 lr_en_sua_rec.incomp_default_grade := l_en_sua_cur_rec.incomp_default_grade ;
1909 lr_en_sua_rec.incomp_default_mark := l_en_sua_cur_rec.incomp_default_mark ;
1910 lr_en_sua_rec.attribute_category := l_en_sua_cur_rec.attribute_category ;
1911 lr_en_sua_rec.attribute1 := l_en_sua_cur_rec.attribute1 ;
1912 lr_en_sua_rec.attribute2 := l_en_sua_cur_rec.attribute2 ;
1913 lr_en_sua_rec.attribute3 := l_en_sua_cur_rec.attribute3 ;
1914 lr_en_sua_rec.attribute4 := l_en_sua_cur_rec.attribute4 ;
1915 lr_en_sua_rec.attribute5 := l_en_sua_cur_rec.attribute5 ;
1916 lr_en_sua_rec.attribute6 := l_en_sua_cur_rec.attribute6 ;
1917 lr_en_sua_rec.attribute7 := l_en_sua_cur_rec.attribute7 ;
1918 lr_en_sua_rec.attribute8 := l_en_sua_cur_rec.attribute8 ;
1919 lr_en_sua_rec.attribute9 := l_en_sua_cur_rec.attribute9 ;
1920 lr_en_sua_rec.attribute10 := l_en_sua_cur_rec.attribute10 ;
1921 lr_en_sua_rec.attribute11 := l_en_sua_cur_rec.attribute11 ;
1922 lr_en_sua_rec.attribute12 := l_en_sua_cur_rec.attribute12 ;
1923 lr_en_sua_rec.attribute13 := l_en_sua_cur_rec.attribute13 ;
1924 lr_en_sua_rec.attribute14 := l_en_sua_cur_rec.attribute14 ;
1925 lr_en_sua_rec.attribute15 := l_en_sua_cur_rec.attribute15 ;
1926 lr_en_sua_rec.attribute16 := l_en_sua_cur_rec.attribute16 ;
1927 lr_en_sua_rec.attribute17 := l_en_sua_cur_rec.attribute17 ;
1928 lr_en_sua_rec.attribute18 := l_en_sua_cur_rec.attribute18 ;
1929 lr_en_sua_rec.attribute19 := l_en_sua_cur_rec.attribute19 ;
1930 lr_en_sua_rec.attribute20 := l_en_sua_cur_rec.attribute20 ;
1931 -- CORE_INDICATOR added by rvangala 07-OCT-2003. Enh Bug# 3052432
1932 lr_en_sua_rec.core_indicator := l_en_sua_cur_rec.core_indicator_code;
1933
1934 l_return_status := NULL;
1935 l_msg_count := NULL;
1936 l_msg_data := NULL;
1937 igs_en_sua_lgcy_pub.create_sua(
1938 P_API_VERSION => 1.0,
1939 P_INIT_MSG_LIST => FND_API.G_TRUE,
1940 P_COMMIT => FND_API.G_FALSE,
1941 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1942 p_sua_dtls_rec => lr_en_sua_rec,
1943 X_RETURN_STATUS => l_return_status,
1944 X_MSG_COUNT => l_msg_count,
1945 X_MSG_DATA => l_msg_data
1946 );
1947
1948 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
1949 ROLLBACK TO savepoint_en_sua;
1950 -- log the error message in the error message interface table
1951 log_err_messages(
1952 g_cst_en_sua,
1953 l_en_sua_cur_rec.legacy_en_sua_int_id,
1954 l_msg_count,
1955 l_msg_data,
1956 l_return_status
1957 );
1958 ELSE
1959
1960 -- Log the warning messages in the concurrent manager log.
1961 -- Since the term record creation might have failed while the unit attempt
1962 -- was successfully imported.
1963 IF l_msg_count > 0 THEN
1964 log_err_messages(
1965 g_cst_en_sua,
1966 l_en_sua_cur_rec.legacy_en_sua_int_id,
1967 l_msg_count,
1968 l_msg_data,
1969 'W');
1970 END IF;
1971
1972 -- log the success message in the concurrent manager log.
1973 log_suc_message(l_en_sua_cur_rec.legacy_en_sua_int_id);
1974 END IF;
1975
1976 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1977 -- delete any records in the error message interface table
1978 delete_err_messages(
1979 p_int_table_code => g_cst_en_sua,
1980 p_int_table_id => l_en_sua_cur_rec.legacy_en_sua_int_id
1981 );
1982 -- delete the interface record
1983 DELETE FROM igs_en_lgcy_sua_int
1984 WHERE legacy_en_sua_int_id = l_en_sua_cur_rec.legacy_en_sua_int_id;
1985 ELSE
1986 l_last_updated_by := get_last_updated_by;
1987 l_last_update_date := get_last_update_date;
1988 l_last_update_login := get_last_update_login;
1989 l_request_id := get_request_id;
1990 l_program_application_id := get_program_application_id;
1991 l_program_id := get_program_id;
1992 l_program_update_date := get_program_update_date;
1993 UPDATE igs_en_lgcy_sua_int
1994 SET import_status = DECODE(l_return_status,
1995 'S','I',
1996 'U','E',
1997 'W','W',
1998 'E'),
1999 last_update_date = l_last_update_date,
2000 last_updated_by = l_last_updated_by,
2001 last_update_login = l_last_update_login,
2002 request_id = l_request_id,
2003 program_id = l_program_id,
2004 program_application_id = l_program_application_id,
2005 program_update_date = l_program_update_date
2006 WHERE legacy_en_sua_int_id = l_en_sua_cur_rec.legacy_en_sua_int_id;
2007 END IF;
2008
2009 EXCEPTION
2010 WHEN OTHERS THEN
2011 ROLLBACK TO savepoint_en_sua;
2012 END;
2013 END LOOP;
2014
2015 log_no_data_exists(l_not_found);
2016
2017 -- This will commit the changes to the interface table record.
2018 -- if an error was encountered then all the unwanted changes
2019 -- would have been rolled back by the rollback to savepoint.
2020 COMMIT WORK;
2021
2022 EXCEPTION
2023 WHEN g_resource_busy THEN
2024 log_resource_busy(g_cst_en_sua);
2025 WHEN OTHERS THEN
2026 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2027 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_en_sua');
2028 IGS_GE_MSG_STACK.ADD;
2029 APP_EXCEPTION.RAISE_EXCEPTION;
2030 END process_en_sua;
2031
2032
2033
2034 -------------------------------------------------------------------------------
2035 PROCEDURE process_he_spa(
2036 p_batch_id IN NUMBER,
2037 p_deletion_flag IN BOOLEAN
2038 ) AS
2039 -----------------------------------------------------------------------------------------------------
2040 --jtmathew 21-SEP-2004 Added additional fields to lr_he_spa_rec for
2041 -- changes described by HEFD350.
2042 -----------------------------------------------------------------------------------------------------
2043 CURSOR c_he_spa IS
2044 SELECT *
2045 FROM igs_he_lgcy_spa_int
2046 WHERE batch_id = p_batch_id
2047 AND import_status IN ('U','R')
2048 ORDER BY person_number ASC,
2049 program_cd ASC
2050 FOR UPDATE NOWAIT;
2051
2052
2053 lr_he_spa_rec igs_he_spa_lgcy_pub.hesa_spa_rec_type ;
2054 l_return_status VARCHAR2(1);
2055 l_msg_count NUMBER(4);
2056 l_msg_data VARCHAR2(4000);
2057
2058 l_last_update_date DATE ;
2059 l_last_updated_by NUMBER;
2060 l_last_update_login NUMBER;
2061 l_request_id NUMBER;
2062 l_program_id NUMBER;
2063 l_program_application_id NUMBER;
2064 l_program_update_date DATE;
2065 l_not_found BOOLEAN;
2066
2067 BEGIN
2068 /*
2069 Process the records in the HESA Student program attempt statistics interface table
2070 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
2071 */
2072
2073 log_headers(g_cst_he_spa);
2074 l_not_found := TRUE;
2075
2076 FOR l_he_spa_cur_rec IN c_he_spa LOOP
2077 BEGIN
2078
2079 -- create a save point. if there are any errors returned by
2080 -- the API then rollback to this savepoint before processing
2081 -- then next record.
2082 SAVEPOINT savepoint_he_spa;
2083 l_not_found := FALSE;
2084
2085 -- populate the record variable to pass to the API
2086 lr_he_spa_rec.person_number := l_he_spa_cur_rec.person_number ;
2087 lr_he_spa_rec.program_cd := l_he_spa_cur_rec.program_cd ;
2088 lr_he_spa_rec.fe_student_marker := l_he_spa_cur_rec.fe_student_marker ;
2089 lr_he_spa_rec.domicile_cd := l_he_spa_cur_rec.domicile_cd ;
2090 lr_he_spa_rec.highest_qual_on_entry := l_he_spa_cur_rec.highest_qual_on_entry ;
2091 lr_he_spa_rec.occupation_code := l_he_spa_cur_rec.occupation_code ;
2092 lr_he_spa_rec.commencement_dt := l_he_spa_cur_rec.commencement_dt ;
2093 lr_he_spa_rec.special_student := l_he_spa_cur_rec.special_student ;
2094 lr_he_spa_rec.student_qual_aim := l_he_spa_cur_rec.student_qual_aim ;
2095 lr_he_spa_rec.student_fe_qual_aim := l_he_spa_cur_rec.student_fe_qual_aim ;
2096 lr_he_spa_rec.teacher_train_prog_id := l_he_spa_cur_rec.teacher_train_prog_id ;
2097 lr_he_spa_rec.itt_phase := l_he_spa_cur_rec.itt_phase ;
2098 lr_he_spa_rec.bilingual_itt_marker := l_he_spa_cur_rec.bilingual_itt_marker ;
2099 lr_he_spa_rec.teaching_qual_gain_sector := l_he_spa_cur_rec.teaching_qual_gain_sector ;
2100 lr_he_spa_rec.teaching_qual_gain_subj1 := l_he_spa_cur_rec.teaching_qual_gain_subj1 ;
2101 lr_he_spa_rec.teaching_qual_gain_subj2 := l_he_spa_cur_rec.teaching_qual_gain_subj2 ;
2102 lr_he_spa_rec.teaching_qual_gain_subj3 := l_he_spa_cur_rec.teaching_qual_gain_subj3 ;
2103 lr_he_spa_rec.student_inst_number := l_he_spa_cur_rec.student_inst_number ;
2104 lr_he_spa_rec.destination := l_he_spa_cur_rec.destination ;
2105 lr_he_spa_rec.itt_prog_outcome := l_he_spa_cur_rec.itt_prog_outcome ;
2106 lr_he_spa_rec.associate_ucas_number := l_he_spa_cur_rec.associate_ucas_number ;
2107 lr_he_spa_rec.associate_scott_cand := l_he_spa_cur_rec.associate_scott_cand ;
2108 lr_he_spa_rec.associate_teach_ref_num := l_he_spa_cur_rec.associate_teach_ref_num ;
2109 lr_he_spa_rec.associate_nhs_reg_num := l_he_spa_cur_rec.associate_nhs_reg_num ;
2110 lr_he_spa_rec.nhs_funding_source := l_he_spa_cur_rec.nhs_funding_source ;
2111 lr_he_spa_rec.ufi_place := l_he_spa_cur_rec.ufi_place ;
2112 lr_he_spa_rec.postcode := l_he_spa_cur_rec.postcode ;
2113 lr_he_spa_rec.social_class_ind := l_he_spa_cur_rec.social_class_ind ;
2114 lr_he_spa_rec.occcode := l_he_spa_cur_rec.occcode ;
2115 lr_he_spa_rec.nhs_employer := l_he_spa_cur_rec.nhs_employer ;
2116 lr_he_spa_rec.return_type := l_he_spa_cur_rec.return_type ;
2117 lr_he_spa_rec.subj_qualaim1 := l_he_spa_cur_rec.subj_qualaim1 ;
2118 lr_he_spa_rec.subj_qualaim2 := l_he_spa_cur_rec.subj_qualaim2 ;
2119 lr_he_spa_rec.subj_qualaim3 := l_he_spa_cur_rec.subj_qualaim3 ;
2120 lr_he_spa_rec.qualaim_proportion := l_he_spa_cur_rec.qualaim_proportion ;
2121 lr_he_spa_rec.dependants_cd := l_he_spa_cur_rec.dependants_cd ;
2122 lr_he_spa_rec.implied_fund_rate := l_he_spa_cur_rec.implied_fund_rate ;
2123 lr_he_spa_rec.gov_initiatives_cd := l_he_spa_cur_rec.gov_initiatives_cd ;
2124 lr_he_spa_rec.units_for_qual := l_he_spa_cur_rec.units_for_qual ;
2125 lr_he_spa_rec.disadv_uplift_elig_cd := l_he_spa_cur_rec.disadv_uplift_elig_cd ;
2126 lr_he_spa_rec.franch_partner_cd := l_he_spa_cur_rec.franch_partner_cd ;
2127 lr_he_spa_rec.units_completed := l_he_spa_cur_rec.units_completed ;
2128 lr_he_spa_rec.franch_out_arr_cd := l_he_spa_cur_rec.franch_out_arr_cd ;
2129 lr_he_spa_rec.employer_role_cd := l_he_spa_cur_rec.employer_role_cd ;
2130 lr_he_spa_rec.disadv_uplift_factor := l_he_spa_cur_rec.disadv_uplift_factor ;
2131 lr_he_spa_rec.enh_fund_elig_cd := l_he_spa_cur_rec.enh_fund_elig_cd ;
2132
2133
2134 l_return_status := NULL;
2135 l_msg_count := NULL;
2136 l_msg_data := NULL;
2137 igs_he_spa_lgcy_pub.create_hesa_spa(
2138 P_API_VERSION => 1.0,
2139 P_INIT_MSG_LIST => FND_API.G_TRUE,
2140 P_COMMIT => FND_API.G_FALSE,
2141 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2142 p_hesa_spa_stats_rec => lr_he_spa_rec,
2143 X_RETURN_STATUS => l_return_status,
2144 X_MSG_COUNT => l_msg_count,
2145 X_MSG_DATA => l_msg_data
2146 );
2147
2148 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
2149 ROLLBACK TO savepoint_he_spa;
2150 -- log the error message in the error message interface table
2151 log_err_messages(
2152 g_cst_he_spa,
2153 l_he_spa_cur_rec.legacy_hesa_spa_int_id,
2154 l_msg_count,
2155 l_msg_data,
2156 l_return_status
2157 );
2158 ELSE
2159 -- log the success message in the concurrent manager log.
2160 log_suc_message(l_he_spa_cur_rec.legacy_hesa_spa_int_id);
2161 END IF;
2162
2163 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2164 -- delete any records in the error message interface table
2165 delete_err_messages(
2166 p_int_table_code => g_cst_he_spa,
2167 p_int_table_id => l_he_spa_cur_rec.legacy_hesa_spa_int_id
2168 );
2169 -- delete the interface record
2170 DELETE FROM igs_he_lgcy_spa_int
2171 WHERE legacy_hesa_spa_int_id = l_he_spa_cur_rec.legacy_hesa_spa_int_id;
2172 ELSE
2173 l_last_updated_by := get_last_updated_by;
2174 l_last_update_date := get_last_update_date;
2175 l_last_update_login := get_last_update_login;
2176 l_request_id := get_request_id;
2177 l_program_application_id := get_program_application_id;
2178 l_program_id := get_program_id;
2179 l_program_update_date := get_program_update_date;
2180 UPDATE igs_he_lgcy_spa_int
2181 SET import_status = DECODE(l_return_status,
2182 'S','I',
2183 'U','E',
2184 'W','W',
2185 'E'),
2186 last_update_date = l_last_update_date,
2187 last_updated_by = l_last_updated_by,
2188 last_update_login = l_last_update_login,
2189 request_id = l_request_id,
2190 program_id = l_program_id,
2191 program_application_id = l_program_application_id,
2192 program_update_date = l_program_update_date
2193 WHERE legacy_hesa_spa_int_id = l_he_spa_cur_rec.legacy_hesa_spa_int_id;
2194 END IF;
2195
2196 EXCEPTION
2197 WHEN OTHERS THEN
2198 ROLLBACK TO savepoint_he_spa;
2199 END;
2200 END LOOP;
2201
2202 log_no_data_exists(l_not_found);
2203
2204 -- This will commit the changes to the interface table record.
2205 -- if an error was encountered then all the unwanted changes
2206 -- would have been rolled back by the rollback to savepoint.
2207 COMMIT WORK;
2208
2209 EXCEPTION
2210 WHEN g_resource_busy THEN
2211 log_resource_busy(g_cst_he_spa);
2212 WHEN OTHERS THEN
2213 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2214 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_he_spa');
2215 IGS_GE_MSG_STACK.ADD;
2216 APP_EXCEPTION.RAISE_EXCEPTION;
2217 END process_he_spa;
2218
2219
2220
2221 -------------------------------------------------------------------------------
2222 PROCEDURE process_he_susa(
2223 p_batch_id IN NUMBER,
2224 p_deletion_flag IN BOOLEAN
2225 ) AS
2226 -----------------------------------------------------------------------------------------------------
2227 --jtmathew 21-SEP-2004 Added additional fields to lr_he_susa_rec for
2228 -- changes described by HEFD350.
2229 -----------------------------------------------------------------------------------------------------
2230 CURSOR c_he_susa IS
2231 SELECT *
2232 FROM igs_he_lgy_susa_int
2233 WHERE batch_id = p_batch_id
2234 AND import_status IN ('U','R')
2235 ORDER BY person_number ASC,
2236 program_cd ASC
2237 FOR UPDATE NOWAIT;
2238
2239
2240 lr_he_susa_rec igs_he_susa_lgcy_pub.hesa_susa_rec_type ;
2241 l_return_status VARCHAR2(1);
2242 l_msg_count NUMBER(4);
2243 l_msg_data VARCHAR2(4000);
2244
2245 l_last_update_date DATE ;
2246 l_last_updated_by NUMBER;
2247 l_last_update_login NUMBER;
2248 l_request_id NUMBER;
2249 l_program_id NUMBER;
2250 l_program_application_id NUMBER;
2251 l_program_update_date DATE;
2252 l_not_found BOOLEAN;
2253
2254 BEGIN
2255 /*
2256 Process the records in the HESA Student unitset attempt statistics interface table
2257 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
2258 */
2259
2260 log_headers(g_cst_he_susa);
2261 l_not_found := TRUE;
2262
2263 FOR l_he_susa_cur_rec IN c_he_susa LOOP
2264 BEGIN
2265
2266 -- create a save point. if there are any errors returned by
2267 -- the API then rollback to this savepoint before processing
2268 -- then next record.
2269 SAVEPOINT savepoint_he_susa;
2270 l_not_found := FALSE;
2271
2272 -- populate the record variable to pass to the API
2273 lr_he_susa_rec.person_number := l_he_susa_cur_rec.person_number ;
2274 lr_he_susa_rec.program_cd := l_he_susa_cur_rec.program_cd ;
2275 lr_he_susa_rec.unit_set_cd := l_he_susa_cur_rec.unit_set_cd ;
2276 lr_he_susa_rec.new_he_entrant_cd := l_he_susa_cur_rec.new_he_entrant_cd ;
2277 lr_he_susa_rec.term_time_accom := l_he_susa_cur_rec.term_time_accom ;
2278 lr_he_susa_rec.disability_allow := l_he_susa_cur_rec.disability_allow ;
2279 lr_he_susa_rec.additional_sup_band := l_he_susa_cur_rec.additional_sup_band ;
2280 lr_he_susa_rec.sldd_discrete_prov := l_he_susa_cur_rec.sldd_discrete_prov ;
2281 lr_he_susa_rec.study_mode := l_he_susa_cur_rec.study_mode ;
2282 lr_he_susa_rec.study_location := l_he_susa_cur_rec.study_location ;
2283 lr_he_susa_rec.fte_perc_override := l_he_susa_cur_rec.fte_perc_override ;
2284 lr_he_susa_rec.franchising_activity := l_he_susa_cur_rec.franchising_activity ;
2285 lr_he_susa_rec.completion_status := l_he_susa_cur_rec.completion_status ;
2286 lr_he_susa_rec.good_stand_marker := l_he_susa_cur_rec.good_stand_marker ;
2287 lr_he_susa_rec.complete_pyr_study_cd := l_he_susa_cur_rec.complete_pyr_study_cd ;
2288 lr_he_susa_rec.credit_value_yop1 := l_he_susa_cur_rec.credit_value_yop1 ;
2289 lr_he_susa_rec.credit_value_yop2 := l_he_susa_cur_rec.credit_value_yop2 ;
2290 lr_he_susa_rec.credit_value_yop3 := l_he_susa_cur_rec.credit_value_yop3 ;
2291 lr_he_susa_rec.credit_value_yop4 := l_he_susa_cur_rec.credit_value_yop4 ;
2292 lr_he_susa_rec.credit_level_achieved1 := l_he_susa_cur_rec.credit_level_achieved1 ;
2293 lr_he_susa_rec.credit_level_achieved2 := l_he_susa_cur_rec.credit_level_achieved2 ;
2294 lr_he_susa_rec.credit_level_achieved3 := l_he_susa_cur_rec.credit_level_achieved3 ;
2295 lr_he_susa_rec.credit_level_achieved4 := l_he_susa_cur_rec.credit_level_achieved4 ;
2296 lr_he_susa_rec.credit_pt_achieved1 := l_he_susa_cur_rec.credit_pt_achieved1 ;
2297 lr_he_susa_rec.credit_pt_achieved2 := l_he_susa_cur_rec.credit_pt_achieved2 ;
2298 lr_he_susa_rec.credit_pt_achieved3 := l_he_susa_cur_rec.credit_pt_achieved3 ;
2299 lr_he_susa_rec.credit_pt_achieved4 := l_he_susa_cur_rec.credit_pt_achieved4 ;
2300 lr_he_susa_rec.credit_level1 := l_he_susa_cur_rec.credit_level1 ;
2301 lr_he_susa_rec.credit_level2 := l_he_susa_cur_rec.credit_level2 ;
2302 lr_he_susa_rec.credit_level3 := l_he_susa_cur_rec.credit_level3 ;
2303 lr_he_susa_rec.credit_level4 := l_he_susa_cur_rec.credit_level4 ;
2304 lr_he_susa_rec.grad_sch_grade := l_he_susa_cur_rec.grad_sch_grade ;
2305 lr_he_susa_rec.mark := l_he_susa_cur_rec.mark ;
2306 lr_he_susa_rec.teaching_inst1 := l_he_susa_cur_rec.teaching_inst1 ;
2307 lr_he_susa_rec.teaching_inst2 := l_he_susa_cur_rec.teaching_inst2 ;
2308 lr_he_susa_rec.pro_not_taught := l_he_susa_cur_rec.pro_not_taught ;
2309 lr_he_susa_rec.fundability_code := l_he_susa_cur_rec.fundability_code ;
2310 lr_he_susa_rec.fee_eligibility := l_he_susa_cur_rec.fee_eligibility ;
2311 lr_he_susa_rec.fee_band := l_he_susa_cur_rec.fee_band ;
2312 lr_he_susa_rec.non_payment_reason := l_he_susa_cur_rec.non_payment_reason ;
2313 lr_he_susa_rec.student_fee := l_he_susa_cur_rec.student_fee ;
2314 lr_he_susa_rec.fte_intensity := l_he_susa_cur_rec.fte_intensity ;
2315 lr_he_susa_rec.calculated_fte := l_he_susa_cur_rec.calculated_fte ;
2316 lr_he_susa_rec.fte_calc_type := l_he_susa_cur_rec.fte_calc_type ;
2317 lr_he_susa_rec.type_of_year := l_he_susa_cur_rec.type_of_year ;
2318 lr_he_susa_rec.year_stu := l_he_susa_cur_rec.year_stu ;
2319 lr_he_susa_rec.enh_fund_elig_cd := l_he_susa_cur_rec.enh_fund_elig_cd ;
2320 lr_he_susa_rec.additional_sup_cost := l_he_susa_cur_rec.additional_sup_cost ;
2321 lr_he_susa_rec.disadv_uplift_factor := l_he_susa_cur_rec.disadv_uplift_factor ;
2322
2323 l_return_status := NULL;
2324 l_msg_count := NULL;
2325 l_msg_data := NULL;
2326 igs_he_susa_lgcy_pub.create_hesa_susa(
2327 P_API_VERSION => 1.0,
2328 P_INIT_MSG_LIST => FND_API.G_TRUE,
2329 P_COMMIT => FND_API.G_FALSE,
2330 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2331 p_hesa_susa_rec => lr_he_susa_rec,
2332 X_RETURN_STATUS => l_return_status,
2333 X_MSG_COUNT => l_msg_count,
2334 X_MSG_DATA => l_msg_data
2335 );
2336
2337 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
2338 ROLLBACK TO savepoint_he_susa;
2339 -- log the error message in the error message interface table
2340 log_err_messages(
2341 g_cst_he_susa,
2342 l_he_susa_cur_rec.legacy_hesa_susa_int_id,
2343 l_msg_count,
2344 l_msg_data,
2345 l_return_status
2346 );
2347 ELSE
2348 -- log the success message in the concurrent manager log.
2349 log_suc_message(l_he_susa_cur_rec.legacy_hesa_susa_int_id);
2350 END IF;
2351
2352 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2353 -- delete any records in the error message interface table
2354 delete_err_messages(
2355 p_int_table_code => g_cst_he_susa,
2356 p_int_table_id => l_he_susa_cur_rec.legacy_hesa_susa_int_id
2357 );
2358 -- delete the interface record
2359 DELETE FROM igs_he_lgy_susa_int
2360 WHERE legacy_hesa_susa_int_id = l_he_susa_cur_rec.legacy_hesa_susa_int_id ;
2361 ELSE
2362 l_last_updated_by := get_last_updated_by;
2363 l_last_update_date := get_last_update_date;
2364 l_last_update_login := get_last_update_login;
2365 l_request_id := get_request_id;
2366 l_program_application_id := get_program_application_id;
2367 l_program_id := get_program_id;
2368 l_program_update_date := get_program_update_date;
2369 UPDATE igs_he_lgy_susa_int
2370 SET import_status = DECODE(l_return_status,
2371 'S','I',
2372 'U','E',
2373 'W','W',
2374 'E'),
2375 last_update_date = l_last_update_date,
2376 last_updated_by = l_last_updated_by,
2377 last_update_login = l_last_update_login,
2378 request_id = l_request_id,
2379 program_id = l_program_id,
2380 program_application_id = l_program_application_id,
2381 program_update_date = l_program_update_date
2382 WHERE legacy_hesa_susa_int_id = l_he_susa_cur_rec.legacy_hesa_susa_int_id ;
2383 END IF;
2384
2385 EXCEPTION
2386 WHEN OTHERS THEN
2387 ROLLBACK TO savepoint_he_susa;
2388 END;
2389 END LOOP;
2390
2391 log_no_data_exists(l_not_found);
2392
2393 -- This will commit the changes to the interface table record.
2394 -- if an error was encountered then all the unwanted changes
2395 -- would have been rolled back by the rollback to savepoint.
2396 COMMIT WORK;
2397
2398 EXCEPTION
2399 WHEN g_resource_busy THEN
2400 log_resource_busy(g_cst_he_susa);
2401 WHEN OTHERS THEN
2402 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2403 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_he_susa');
2404 IGS_GE_MSG_STACK.ADD;
2405 APP_EXCEPTION.RAISE_EXCEPTION;
2406 END process_he_susa;
2407
2408
2409
2410 -------------------------------------------------------------------------------
2411 PROCEDURE process_av_avstdl(
2412 p_batch_id IN NUMBER,
2413 p_deletion_flag IN BOOLEAN
2414 ) AS
2415 CURSOR c_av_avstdl IS
2416 SELECT *
2417 FROM igs_av_lgcy_lvl_int
2418 WHERE batch_id = p_batch_id
2419 AND import_status IN ('U','R')
2420 FOR UPDATE NOWAIT;
2421
2422
2423 lr_av_avstdl_rec igs_av_lvl_lgcy_pub.lgcy_adstlvl_rec_type ;
2424 l_return_status VARCHAR2(1);
2425 l_msg_count NUMBER(4);
2426 l_msg_data VARCHAR2(4000);
2427
2428 l_last_update_date DATE ;
2429 l_last_updated_by NUMBER;
2430 l_last_update_login NUMBER;
2431 l_request_id NUMBER;
2432 l_program_id NUMBER;
2433 l_program_application_id NUMBER;
2434 l_program_update_date DATE;
2435 l_not_found BOOLEAN;
2436
2437 BEGIN
2438 /*
2439 Process the records in the Advance Standing at Unit Level interface table
2440 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
2441 */
2442
2443 log_headers(g_cst_av_avstdl);
2444 l_not_found := TRUE;
2445
2446 FOR l_av_avstdl_cur_rec IN c_av_avstdl LOOP
2447 BEGIN
2448
2449 -- create a save point. if there are any errors returned by
2450 -- the API then rollback to this savepoint before processing
2451 -- then next record.
2452 SAVEPOINT savepoint_av_avstdl;
2453 l_not_found := FALSE;
2454
2455 -- populate the record variable to pass to the API
2456 lr_av_avstdl_rec.person_number := l_av_avstdl_cur_rec.person_number ;
2457 lr_av_avstdl_rec.program_cd := l_av_avstdl_cur_rec.program_cd ;
2458 lr_av_avstdl_rec.total_exmptn_approved := l_av_avstdl_cur_rec.total_exmptn_approved ;
2459 lr_av_avstdl_rec.total_exmptn_granted := l_av_avstdl_cur_rec.total_exmptn_granted ;
2460 lr_av_avstdl_rec.total_exmptn_perc_grntd := l_av_avstdl_cur_rec.total_exmptn_perc_grntd ;
2461 lr_av_avstdl_rec.exemption_institution_cd := l_av_avstdl_cur_rec.exemption_institution_cd ;
2462 lr_av_avstdl_rec.unit_level := l_av_avstdl_cur_rec.unit_level ;
2463 lr_av_avstdl_rec.prog_group_ind := l_av_avstdl_cur_rec.prog_group_ind ;
2464 lr_av_avstdl_rec.load_cal_alt_code := l_av_avstdl_cur_rec.load_cal_alt_code ;
2465 lr_av_avstdl_rec.institution_cd := l_av_avstdl_cur_rec.institution_cd ;
2466 lr_av_avstdl_rec.s_adv_stnd_granting_status := l_av_avstdl_cur_rec.s_adv_stnd_granting_status ;
2467 lr_av_avstdl_rec.credit_points := l_av_avstdl_cur_rec.credit_points ;
2468 lr_av_avstdl_rec.approved_dt := trunc(l_av_avstdl_cur_rec.approved_dt) ;
2469 lr_av_avstdl_rec.authorising_person_number := l_av_avstdl_cur_rec.authorising_person_number ;
2470 lr_av_avstdl_rec.granted_dt := trunc(l_av_avstdl_cur_rec.granted_dt) ;
2471 lr_av_avstdl_rec.expiry_dt := trunc(l_av_avstdl_cur_rec.expiry_dt) ;
2472 lr_av_avstdl_rec.cancelled_dt := trunc(l_av_avstdl_cur_rec.cancelled_dt) ;
2473 lr_av_avstdl_rec.revoked_dt := trunc(l_av_avstdl_cur_rec.revoked_dt) ;
2474 lr_av_avstdl_rec.comments := rtrim(l_av_avstdl_cur_rec.comments) ;
2475 lr_av_avstdl_rec.qual_exam_level := l_av_avstdl_cur_rec.qual_exam_level ;
2476 lr_av_avstdl_rec.qual_subject_code := l_av_avstdl_cur_rec.qual_subject_code ;
2477 lr_av_avstdl_rec.qual_year := l_av_avstdl_cur_rec.qual_year ;
2478 lr_av_avstdl_rec.qual_sitting := l_av_avstdl_cur_rec.qual_sitting ;
2479 lr_av_avstdl_rec.qual_awarding_body := l_av_avstdl_cur_rec.qual_awarding_body ;
2480 lr_av_avstdl_rec.approved_result := l_av_avstdl_cur_rec.approved_result ;
2481 lr_av_avstdl_rec.prev_unit_cd := l_av_avstdl_cur_rec.prev_unit_cd ;
2482 lr_av_avstdl_rec.prev_term := l_av_avstdl_cur_rec.prev_term ;
2483 lr_av_avstdl_rec.start_date := l_av_avstdl_cur_rec.start_date ;
2484 lr_av_avstdl_rec.end_date := l_av_avstdl_cur_rec.end_date ;
2485 lr_av_avstdl_rec.tst_admission_test_type := l_av_avstdl_cur_rec.tst_admission_test_type ;
2486 lr_av_avstdl_rec.tst_test_date := l_av_avstdl_cur_rec.tst_test_date ;
2487 lr_av_avstdl_rec.test_segment_name := l_av_avstdl_cur_rec.test_segment_name ;
2488 lr_av_avstdl_rec.basis_program_type := l_av_avstdl_cur_rec.basis_program_type ;
2489 lr_av_avstdl_rec.basis_year := l_av_avstdl_cur_rec.basis_year ;
2490 lr_av_avstdl_rec.basis_completion_ind := l_av_avstdl_cur_rec.basis_completion_ind ;
2491 lr_av_avstdl_rec.unit_level_mark := l_av_avstdl_cur_rec.unit_level_mark;
2492
2493
2494 l_return_status := NULL;
2495 l_msg_count := NULL;
2496 l_msg_data := NULL;
2497 igs_av_lvl_lgcy_pub.create_adv_stnd_level(
2498 P_API_VERSION => 1.0,
2499 P_INIT_MSG_LIST => FND_API.G_TRUE,
2500 P_COMMIT => FND_API.G_FALSE,
2501 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2502 p_lgcy_adstlvl_rec => lr_av_avstdl_rec,
2503 X_RETURN_STATUS => l_return_status,
2504 X_MSG_COUNT => l_msg_count,
2505 X_MSG_DATA => l_msg_data
2506 );
2507
2508 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
2509 ROLLBACK TO savepoint_av_avstdl;
2510 -- log the error message in the error message interface table
2511 log_err_messages(
2512 g_cst_av_avstdl,
2513 l_av_avstdl_cur_rec.legacy_lvl_int_id,
2514 l_msg_count,
2515 l_msg_data,
2516 l_return_status
2517 );
2518 ELSE
2519 -- log the success message in the concurrent manager log.
2520 log_suc_message(l_av_avstdl_cur_rec.legacy_lvl_int_id);
2521 END IF;
2522
2523 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2524 -- delete any records in the error message interface table
2525 delete_err_messages(
2526 p_int_table_code => g_cst_av_avstdl,
2527 p_int_table_id => l_av_avstdl_cur_rec.legacy_lvl_int_id
2528 );
2529 -- delete the interface record
2530 DELETE FROM igs_av_lgcy_lvl_int
2531 WHERE legacy_lvl_int_id = l_av_avstdl_cur_rec.legacy_lvl_int_id;
2532 ELSE
2533 l_last_updated_by := get_last_updated_by;
2534 l_last_update_date := get_last_update_date;
2535 l_last_update_login := get_last_update_login;
2536 l_request_id := get_request_id;
2537 l_program_application_id := get_program_application_id;
2538 l_program_id := get_program_id;
2539 l_program_update_date := get_program_update_date;
2540 UPDATE igs_av_lgcy_lvl_int
2541 SET import_status = DECODE(l_return_status,
2542 'S','I',
2543 'U','E',
2544 'W','W',
2545 'E'),
2546 last_update_date = l_last_update_date,
2547 last_updated_by = l_last_updated_by,
2548 last_update_login = l_last_update_login,
2549 request_id = l_request_id,
2550 program_id = l_program_id,
2551 program_application_id = l_program_application_id,
2552 program_update_date = l_program_update_date
2553 WHERE legacy_lvl_int_id = l_av_avstdl_cur_rec.legacy_lvl_int_id;
2554 END IF;
2555
2556 EXCEPTION
2557 WHEN OTHERS THEN
2558 ROLLBACK TO savepoint_av_avstdl;
2559 END;
2560 END LOOP;
2561
2562 log_no_data_exists(l_not_found);
2563
2564 -- This will commit the changes to the interface table record.
2565 -- if an error was encountered then all the unwanted changes
2566 -- would have been rolled back by the rollback to savepoint.
2567 COMMIT WORK;
2568
2569 EXCEPTION
2570 WHEN g_resource_busy THEN
2571 log_resource_busy(g_cst_av_avstdl);
2572 WHEN OTHERS THEN
2573 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2574 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_av_avstdl');
2575 IGS_GE_MSG_STACK.ADD;
2576 APP_EXCEPTION.RAISE_EXCEPTION;
2577 END process_av_avstdl;
2578
2579
2580
2581 -------------------------------------------------------------------------------
2582 PROCEDURE process_av_untstd(
2583 p_batch_id IN NUMBER,
2584 p_deletion_flag IN BOOLEAN
2585 ) AS
2586 CURSOR c_av_untstd IS
2587 SELECT *
2588 FROM igs_av_lgcy_unt_int
2589 WHERE batch_id = p_batch_id
2590 AND import_status IN ('U','R')
2591 FOR UPDATE NOWAIT;
2592
2593
2594 lr_av_untstd_rec igs_av_unt_lgcy_pub.lgcy_adstunt_rec_type ;
2595 l_return_status VARCHAR2(1);
2596 l_msg_count NUMBER(4);
2597 l_msg_data VARCHAR2(4000);
2598
2599 l_last_update_date DATE ;
2600 l_last_updated_by NUMBER;
2601 l_last_update_login NUMBER;
2602 l_request_id NUMBER;
2603 l_program_id NUMBER;
2604 l_program_application_id NUMBER;
2605 l_program_update_date DATE;
2606 l_not_found BOOLEAN;
2607
2608 BEGIN
2609 /*
2610 Process the records in the Advance Standing Unit interface table
2611 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
2612 */
2613
2614 log_headers(g_cst_av_untstd);
2615 l_not_found := TRUE;
2616
2617 FOR l_av_untstd_cur_rec IN c_av_untstd LOOP
2618 BEGIN
2619
2620 -- create a save point. if there are any errors returned by
2621 -- the API then rollback to this savepoint before processing
2622 -- then next record.
2623 SAVEPOINT savepoint_av_untstd;
2624 l_not_found := FALSE;
2625
2626 -- populate the record variable to pass to the API
2627 lr_av_untstd_rec.person_number := l_av_untstd_cur_rec.person_number ;
2628 lr_av_untstd_rec.program_cd := l_av_untstd_cur_rec.program_cd ;
2629 lr_av_untstd_rec.total_exmptn_approved := l_av_untstd_cur_rec.total_exmptn_approved ;
2630 lr_av_untstd_rec.total_exmptn_granted := l_av_untstd_cur_rec.total_exmptn_granted ;
2631 lr_av_untstd_rec.total_exmptn_perc_grntd := l_av_untstd_cur_rec.total_exmptn_perc_grntd ;
2632 lr_av_untstd_rec.exemption_institution_cd := l_av_untstd_cur_rec.exemption_institution_cd ;
2633 lr_av_untstd_rec.unit_cd := l_av_untstd_cur_rec.unit_cd ;
2634 lr_av_untstd_rec.version_number := l_av_untstd_cur_rec.version_number ;
2635 lr_av_untstd_rec.institution_cd := l_av_untstd_cur_rec.institution_cd ;
2636 lr_av_untstd_rec.approved_dt := l_av_untstd_cur_rec.approved_dt ;
2637 lr_av_untstd_rec.authorising_person_number := l_av_untstd_cur_rec.authorising_person_number ;
2638 lr_av_untstd_rec.prog_group_ind := l_av_untstd_cur_rec.prog_group_ind ;
2639 lr_av_untstd_rec.granted_dt := l_av_untstd_cur_rec.granted_dt ;
2640 lr_av_untstd_rec.expiry_dt := l_av_untstd_cur_rec.expiry_dt ;
2641 lr_av_untstd_rec.cancelled_dt := l_av_untstd_cur_rec.cancelled_dt ;
2642 lr_av_untstd_rec.revoked_dt := l_av_untstd_cur_rec.revoked_dt ;
2643 lr_av_untstd_rec.comments := l_av_untstd_cur_rec.comments ;
2644 lr_av_untstd_rec.credit_percentage := NULL ;
2645 lr_av_untstd_rec.s_adv_stnd_granting_status := l_av_untstd_cur_rec.s_adv_stnd_granting_status ;
2646 lr_av_untstd_rec.s_adv_stnd_recognition_type := l_av_untstd_cur_rec.s_adv_stnd_recognition_type ;
2647 lr_av_untstd_rec.load_cal_alt_code := l_av_untstd_cur_rec.load_cal_alt_code ;
2648 lr_av_untstd_rec.grading_schema_cd := l_av_untstd_cur_rec.grading_schema_cd ;
2649 lr_av_untstd_rec.grd_sch_version_number := l_av_untstd_cur_rec.grd_sch_version_number ;
2650 lr_av_untstd_rec.grade := l_av_untstd_cur_rec.grade ;
2651 lr_av_untstd_rec.achievable_credit_points := l_av_untstd_cur_rec.achievable_credit_points ;
2652 lr_av_untstd_rec.prev_unit_cd := l_av_untstd_cur_rec.prev_unit_cd ;
2653 lr_av_untstd_rec.prev_term := l_av_untstd_cur_rec.prev_term ;
2654 lr_av_untstd_rec.tst_admission_test_type := l_av_untstd_cur_rec.tst_admission_test_type ;
2655 lr_av_untstd_rec.tst_test_date := l_av_untstd_cur_rec.tst_test_date ;
2656 lr_av_untstd_rec.test_segment_name := l_av_untstd_cur_rec.test_segment_name ;
2657 lr_av_untstd_rec.alt_unit_cd := l_av_untstd_cur_rec.alt_unit_cd ;
2658 lr_av_untstd_rec.alt_version_number := l_av_untstd_cur_rec.alt_version_number ;
2659 lr_av_untstd_rec.optional_ind := l_av_untstd_cur_rec.optional_ind ;
2660 lr_av_untstd_rec.basis_program_type := l_av_untstd_cur_rec.basis_program_type ;
2661 lr_av_untstd_rec.basis_year := l_av_untstd_cur_rec.basis_year ;
2662 lr_av_untstd_rec.basis_completion_ind := l_av_untstd_cur_rec.basis_completion_ind ;
2663 lr_av_untstd_rec.start_date := l_av_untstd_cur_rec.start_date ;
2664 lr_av_untstd_rec.end_date := l_av_untstd_cur_rec.end_date ;
2665 lr_av_untstd_rec.reference_cd_type := l_av_untstd_cur_rec.reference_cd_type;
2666 lr_av_untstd_rec.reference_cd := l_av_untstd_cur_rec.reference_cd ;
2667 lr_av_untstd_rec.applied_program_cd := l_av_untstd_cur_rec.applied_program_cd;
2668
2669
2670 l_return_status := NULL;
2671 l_msg_count := NULL;
2672 l_msg_data := NULL;
2673 igs_av_unt_lgcy_pub.create_adv_stnd_unit(
2674 P_API_VERSION => 1.0,
2675 P_INIT_MSG_LIST => FND_API.G_TRUE,
2676 P_COMMIT => FND_API.G_FALSE,
2677 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2678 p_lgcy_adstunt_rec => lr_av_untstd_rec,
2679 X_RETURN_STATUS => l_return_status,
2680 X_MSG_COUNT => l_msg_count,
2681 X_MSG_DATA => l_msg_data
2682 );
2683
2684 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
2685 ROLLBACK TO savepoint_av_untstd;
2686 -- log the error message in the error message interface table
2687 log_err_messages(
2688 g_cst_av_untstd,
2689 l_av_untstd_cur_rec.legacy_unt_int_id,
2690 l_msg_count,
2691 l_msg_data,
2692 l_return_status
2693 );
2694 ELSE
2695 -- log the success message in the concurrent manager log.
2696 log_suc_message(l_av_untstd_cur_rec.legacy_unt_int_id);
2697 END IF;
2698
2699 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2700 -- delete any records in the error message interface table
2701 delete_err_messages(
2702 p_int_table_code => g_cst_av_untstd,
2703 p_int_table_id => l_av_untstd_cur_rec.legacy_unt_int_id
2704 );
2705 -- delete the interface record
2706 DELETE FROM igs_av_lgcy_unt_int
2707 WHERE legacy_unt_int_id = l_av_untstd_cur_rec.legacy_unt_int_id;
2708 ELSE
2709 l_last_updated_by := get_last_updated_by;
2710 l_last_update_date := get_last_update_date;
2711 l_last_update_login := get_last_update_login;
2712 l_request_id := get_request_id;
2713 l_program_application_id := get_program_application_id;
2714 l_program_id := get_program_id;
2715 l_program_update_date := get_program_update_date;
2716 UPDATE igs_av_lgcy_unt_int
2717 SET import_status = DECODE(l_return_status,
2718 'S','I',
2719 'U','E',
2720 'W','W',
2721 'E'),
2722 last_update_date = l_last_update_date,
2723 last_updated_by = l_last_updated_by,
2724 last_update_login = l_last_update_login,
2725 request_id = l_request_id,
2726 program_id = l_program_id,
2727 program_application_id = l_program_application_id,
2728 program_update_date = l_program_update_date
2729 WHERE legacy_unt_int_id = l_av_untstd_cur_rec.legacy_unt_int_id;
2730 END IF;
2731
2732 EXCEPTION
2733 WHEN OTHERS THEN
2734 ROLLBACK TO savepoint_av_untstd;
2735 END;
2736 END LOOP;
2737
2738 log_no_data_exists(l_not_found);
2739
2740 -- This will commit the changes to the interface table record.
2741 -- if an error was encountered then all the unwanted changes
2742 -- would have been rolled back by the rollback to savepoint.
2743 COMMIT WORK;
2744
2745 EXCEPTION
2746 WHEN g_resource_busy THEN
2747 log_resource_busy(g_cst_av_untstd);
2748 WHEN OTHERS THEN
2749 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2750 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_av_untstd');
2751 IGS_GE_MSG_STACK.ADD;
2752 APP_EXCEPTION.RAISE_EXCEPTION;
2753 END process_av_untstd;
2754
2755
2756
2757 -------------------------------------------------------------------------------
2758 PROCEDURE process_as_uotcm (
2759 p_batch_id IN NUMBER,
2760 p_deletion_flag IN BOOLEAN
2761 ) AS
2762 CURSOR c_as_uotcm IS
2763 SELECT *
2764 FROM igs_as_lgcy_suo_int
2765 WHERE batch_id = p_batch_id
2766 AND import_status IN ('U','R')
2767 FOR UPDATE NOWAIT;
2768
2769
2770 lr_as_uotcm_rec igs_as_suao_lgcy_pub.lgcy_suo_rec_type ;
2771 l_return_status VARCHAR2(1);
2772 l_msg_count NUMBER(4);
2773 l_msg_data VARCHAR2(4000);
2774
2775 l_last_update_date DATE ;
2776 l_last_updated_by NUMBER;
2777 l_last_update_login NUMBER;
2778 l_request_id NUMBER;
2779 l_program_id NUMBER;
2780 l_program_application_id NUMBER;
2781 l_program_update_date DATE;
2782 l_not_found BOOLEAN;
2783
2784 BEGIN
2785 /*
2786 Process the records in the Assessment Outcome interface table
2787 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
2788 */
2789
2790 log_headers(g_cst_as_uotcm );
2791 l_not_found := TRUE;
2792
2793 FOR l_as_uotcm_cur_rec IN c_as_uotcm LOOP
2794 BEGIN
2795
2796 -- create a save point. if there are any errors returned by
2797 -- the API then rollback to this savepoint before processing
2798 -- then next record.
2799 SAVEPOINT savepoint_as_uotcm ;
2800 l_not_found := FALSE;
2801
2802 -- populate the record variable to pass to the API
2803 lr_as_uotcm_rec.person_number := l_as_uotcm_cur_rec.person_number ;
2804 lr_as_uotcm_rec.program_cd := l_as_uotcm_cur_rec.program_cd ;
2805 lr_as_uotcm_rec.unit_cd := l_as_uotcm_cur_rec.unit_cd ;
2806 lr_as_uotcm_rec.teach_cal_alt_code := l_as_uotcm_cur_rec.teach_cal_alt_code ;
2807 lr_as_uotcm_rec.outcome_dt := l_as_uotcm_cur_rec.outcome_dt ;
2808 lr_as_uotcm_rec.grading_schema_cd := l_as_uotcm_cur_rec.grading_schema_cd ;
2809 lr_as_uotcm_rec.version_number := l_as_uotcm_cur_rec.version_number ;
2810 lr_as_uotcm_rec.grade := l_as_uotcm_cur_rec.grade ;
2811 lr_as_uotcm_rec.s_grade_creation_method_type := l_as_uotcm_cur_rec.s_grade_creation_method_type ;
2812 lr_as_uotcm_rec.finalised_outcome_ind := l_as_uotcm_cur_rec.finalised_outcome_ind ;
2813 lr_as_uotcm_rec.mark := l_as_uotcm_cur_rec.mark ;
2814 lr_as_uotcm_rec.incomp_deadline_date := l_as_uotcm_cur_rec.incomp_deadline_date ;
2815 lr_as_uotcm_rec.incomp_grading_schema_cd := l_as_uotcm_cur_rec.incomp_grading_schema_cd ;
2816 lr_as_uotcm_rec.incomp_version_number := l_as_uotcm_cur_rec.incomp_version_number ;
2817 lr_as_uotcm_rec.incomp_default_grade := l_as_uotcm_cur_rec.incomp_default_grade ;
2818 lr_as_uotcm_rec.incomp_default_mark := l_as_uotcm_cur_rec.incomp_default_mark ;
2819 lr_as_uotcm_rec.comments := l_as_uotcm_cur_rec.comments ;
2820 lr_as_uotcm_rec.grading_period_cd := l_as_uotcm_cur_rec.grading_period_cd ;
2821 lr_as_uotcm_rec.attribute_category := l_as_uotcm_cur_rec.attribute_category ;
2822 lr_as_uotcm_rec.attribute1 := l_as_uotcm_cur_rec.attribute1 ;
2823 lr_as_uotcm_rec.attribute2 := l_as_uotcm_cur_rec.attribute2 ;
2824 lr_as_uotcm_rec.attribute3 := l_as_uotcm_cur_rec.attribute3 ;
2825 lr_as_uotcm_rec.attribute4 := l_as_uotcm_cur_rec.attribute4 ;
2826 lr_as_uotcm_rec.attribute5 := l_as_uotcm_cur_rec.attribute5 ;
2827 lr_as_uotcm_rec.attribute6 := l_as_uotcm_cur_rec.attribute6 ;
2828 lr_as_uotcm_rec.attribute7 := l_as_uotcm_cur_rec.attribute7 ;
2829 lr_as_uotcm_rec.attribute8 := l_as_uotcm_cur_rec.attribute8 ;
2830 lr_as_uotcm_rec.attribute9 := l_as_uotcm_cur_rec.attribute9 ;
2831 lr_as_uotcm_rec.attribute10 := l_as_uotcm_cur_rec.attribute10 ;
2832 lr_as_uotcm_rec.attribute11 := l_as_uotcm_cur_rec.attribute11 ;
2833 lr_as_uotcm_rec.attribute12 := l_as_uotcm_cur_rec.attribute12 ;
2834 lr_as_uotcm_rec.attribute13 := l_as_uotcm_cur_rec.attribute13 ;
2835 lr_as_uotcm_rec.attribute14 := l_as_uotcm_cur_rec.attribute14 ;
2836 lr_as_uotcm_rec.attribute15 := l_as_uotcm_cur_rec.attribute15 ;
2837 lr_as_uotcm_rec.attribute16 := l_as_uotcm_cur_rec.attribute16 ;
2838 lr_as_uotcm_rec.attribute17 := l_as_uotcm_cur_rec.attribute17 ;
2839 lr_as_uotcm_rec.attribute18 := l_as_uotcm_cur_rec.attribute18 ;
2840 lr_as_uotcm_rec.attribute19 := l_as_uotcm_cur_rec.attribute19 ;
2841 lr_as_uotcm_rec.attribute20 := l_as_uotcm_cur_rec.attribute20 ;
2842 lr_as_uotcm_rec.location_cd := l_as_uotcm_cur_rec.location_cd ;
2843 lr_as_uotcm_rec.unit_class := l_as_uotcm_cur_rec.unit_class ;
2844
2845 l_return_status := NULL;
2846 l_msg_count := NULL;
2847 l_msg_data := NULL;
2848 igs_as_suao_lgcy_pub.create_unit_outcome(
2849 P_API_VERSION => 1.0,
2850 P_INIT_MSG_LIST => FND_API.G_TRUE,
2851 P_COMMIT => FND_API.G_FALSE,
2852 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2853 p_lgcy_suo_rec => lr_as_uotcm_rec,
2854 X_RETURN_STATUS => l_return_status,
2855 X_MSG_COUNT => l_msg_count,
2856 X_MSG_DATA => l_msg_data
2857 );
2858
2859 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
2860 ROLLBACK TO savepoint_as_uotcm;
2861 -- log the error message in the error message interface table
2862 log_err_messages(
2863 g_cst_as_uotcm,
2864 l_as_uotcm_cur_rec.legacy_suao_int_id,
2865 l_msg_count,
2866 l_msg_data,
2867 l_return_status
2868 );
2869 ELSE
2870 -- log the success message in the concurrent manager log.
2871 log_suc_message(l_as_uotcm_cur_rec.legacy_suao_int_id);
2872 END IF;
2873
2874 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2875 -- delete any records in the error message interface table
2876 delete_err_messages(
2877 p_int_table_code => g_cst_as_uotcm,
2878 p_int_table_id => l_as_uotcm_cur_rec.legacy_suao_int_id
2879 );
2880 -- delete the interface record
2881 DELETE FROM igs_as_lgcy_suo_int
2882 WHERE legacy_suao_int_id = l_as_uotcm_cur_rec.legacy_suao_int_id;
2883 ELSE
2884 l_last_updated_by := get_last_updated_by;
2885 l_last_update_date := get_last_update_date;
2886 l_last_update_login := get_last_update_login;
2887 l_request_id := get_request_id;
2888 l_program_application_id := get_program_application_id;
2889 l_program_id := get_program_id;
2890 l_program_update_date := get_program_update_date;
2891 UPDATE igs_as_lgcy_suo_int
2892 SET import_status = DECODE(l_return_status,
2893 'S','I',
2894 'U','E',
2895 'W','W',
2896 'E'),
2897 last_update_date = l_last_update_date,
2898 last_updated_by = l_last_updated_by,
2899 last_update_login = l_last_update_login,
2900 request_id = l_request_id,
2901 program_id = l_program_id,
2902 program_application_id = l_program_application_id,
2903 program_update_date = l_program_update_date
2904 WHERE legacy_suao_int_id = l_as_uotcm_cur_rec.legacy_suao_int_id;
2905 END IF;
2906
2907 EXCEPTION
2908 WHEN OTHERS THEN
2909 ROLLBACK TO savepoint_as_uotcm ;
2910 END;
2911 END LOOP;
2912
2913 log_no_data_exists(l_not_found);
2914
2915 -- This will commit the changes to the interface table record.
2916 -- if an error was encountered then all the unwanted changes
2917 -- would have been rolled back by the rollback to savepoint.
2918 COMMIT WORK;
2919
2920 EXCEPTION
2921 WHEN g_resource_busy THEN
2922 log_resource_busy(g_cst_as_uotcm);
2923 WHEN OTHERS THEN
2924 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2925 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_as_uotcm');
2926 IGS_GE_MSG_STACK.ADD;
2927 APP_EXCEPTION.RAISE_EXCEPTION;
2928 END process_as_uotcm ;
2929
2930
2931
2932 -------------------------------------------------------------------------------
2933 PROCEDURE process_pr_out (
2934 p_batch_id IN NUMBER,
2935 p_deletion_flag IN BOOLEAN
2936 ) AS
2937 CURSOR c_pr_out IS
2938 SELECT *
2939 FROM igs_pr_lgcy_spo_int
2940 WHERE batch_id = p_batch_id
2941 AND import_status IN ('U','R')
2942 FOR UPDATE NOWAIT;
2943
2944
2945 lr_pr_out_rec igs_pr_prout_lgcy_pub.lgcy_prout_rec_type;
2946 l_return_status VARCHAR2(1);
2947 l_msg_count NUMBER(4);
2948 l_msg_data VARCHAR2(4000);
2949
2950 l_last_update_date DATE ;
2951 l_last_updated_by NUMBER;
2952 l_last_update_login NUMBER;
2953 l_request_id NUMBER;
2954 l_program_id NUMBER;
2955 l_program_application_id NUMBER;
2956 l_program_update_date DATE;
2957 l_not_found BOOLEAN;
2958 BEGIN
2959 /*
2960 Process the records in the Progression Outcome interface table
2961 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
2962 */
2963
2964 log_headers(g_cst_pr_out);
2965 l_not_found := TRUE;
2966
2967 FOR l_pr_out_cur_rec IN c_pr_out LOOP
2968 BEGIN
2969
2970 -- create a save point. if there are any errors returned by
2971 -- the API then rollback to this savepoint before processing
2972 -- then next record.
2973 SAVEPOINT savepoint_pr_out;
2974 l_not_found := FALSE;
2975
2976 -- populate the record variable to pass to the API
2977 lr_pr_out_rec.person_number := l_pr_out_cur_rec.person_number ;
2978 lr_pr_out_rec.program_cd := l_pr_out_cur_rec.program_cd ;
2979 lr_pr_out_rec.prg_cal_alternate_code := l_pr_out_cur_rec.prg_cal_alternate_code ;
2980 lr_pr_out_rec.progression_outcome_type := l_pr_out_cur_rec.progression_outcome_type ;
2981 lr_pr_out_rec.duration := l_pr_out_cur_rec.duration ;
2982 lr_pr_out_rec.duration_type := l_pr_out_cur_rec.duration_type ;
2983 lr_pr_out_rec.decision_status := l_pr_out_cur_rec.decision_status ;
2984 lr_pr_out_rec.decision_dt := l_pr_out_cur_rec.decision_dt ;
2985 lr_pr_out_rec.decision_org_unit_cd := l_pr_out_cur_rec.decision_org_unit_cd ;
2986 lr_pr_out_rec.show_cause_expiry_dt := l_pr_out_cur_rec.show_cause_expiry_dt ;
2987 lr_pr_out_rec.show_cause_dt := l_pr_out_cur_rec.show_cause_dt ;
2988 lr_pr_out_rec.show_cause_outcome_dt := l_pr_out_cur_rec.show_cause_outcome_dt ;
2989 lr_pr_out_rec.show_cause_outcome_type := l_pr_out_cur_rec.show_cause_outcome_type ;
2990 lr_pr_out_rec.appeal_expiry_dt := l_pr_out_cur_rec.appeal_expiry_dt ;
2991 lr_pr_out_rec.appeal_dt := l_pr_out_cur_rec.appeal_dt ;
2992 lr_pr_out_rec.appeal_outcome_dt := l_pr_out_cur_rec.appeal_outcome_dt ;
2993 lr_pr_out_rec.appeal_outcome_type := l_pr_out_cur_rec.appeal_outcome_type ;
2994 lr_pr_out_rec.encmb_program_group_cd := l_pr_out_cur_rec.encmb_program_group_cd ;
2995 lr_pr_out_rec.restricted_enrolment_cp := l_pr_out_cur_rec.restricted_enrolment_cp ;
2996 lr_pr_out_rec.restricted_attendance_type := l_pr_out_cur_rec.restricted_attendance_type ;
2997 lr_pr_out_rec.comments := l_pr_out_cur_rec.comments ;
2998 lr_pr_out_rec.show_cause_comments := l_pr_out_cur_rec.show_cause_comments ;
2999 lr_pr_out_rec.appeal_comments := l_pr_out_cur_rec.appeal_comments ;
3000 lr_pr_out_rec.expiry_dt := l_pr_out_cur_rec.expiry_dt ;
3001 lr_pr_out_rec.award_cd := l_pr_out_cur_rec.award_cd ;
3002 lr_pr_out_rec.spo_program_cd := l_pr_out_cur_rec.spo_program_cd ;
3003 lr_pr_out_rec.unit_cd := l_pr_out_cur_rec.unit_cd ;
3004 lr_pr_out_rec.s_unit_type := l_pr_out_cur_rec.s_unit_type ;
3005 lr_pr_out_rec.unit_set_cd := l_pr_out_cur_rec.unit_set_cd ;
3006 lr_pr_out_rec.us_version_number := l_pr_out_cur_rec.us_version_number ;
3007 --anilk, Bug# 3021236, adding fund_code
3008 lr_pr_out_rec.fund_code := l_pr_out_cur_rec.fund_code ;
3009
3010
3011 l_return_status := NULL;
3012 l_msg_count := NULL;
3013 l_msg_data := NULL;
3014 igs_pr_prout_lgcy_pub.create_outcome(
3015 P_API_VERSION => 1.0,
3016 P_INIT_MSG_LIST => FND_API.G_TRUE,
3017 P_COMMIT => FND_API.G_FALSE,
3018 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3019 p_lgcy_prout_rec => lr_pr_out_rec,
3020 X_RETURN_STATUS => l_return_status,
3021 X_MSG_COUNT => l_msg_count,
3022 X_MSG_DATA => l_msg_data
3023 );
3024
3025 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
3026 ROLLBACK TO savepoint_pr_out;
3027 -- log the error message in the error message interface table
3028 log_err_messages(
3029 g_cst_pr_out,
3030 l_pr_out_cur_rec.legacy_pr_spo_int_id,
3031 l_msg_count,
3032 l_msg_data,
3033 l_return_status
3034 );
3035 ELSE
3036 -- log the success message in the concurrent manager log.
3037 log_suc_message(l_pr_out_cur_rec.legacy_pr_spo_int_id);
3038 END IF;
3039
3040 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3041 -- delete any records in the error message interface table
3042 delete_err_messages(
3043 p_int_table_code => g_cst_pr_out,
3044 p_int_table_id => l_pr_out_cur_rec.legacy_pr_spo_int_id
3045 );
3046 -- delete the interface record
3047 DELETE FROM igs_pr_lgcy_spo_int
3048 WHERE legacy_pr_spo_int_id = l_pr_out_cur_rec.legacy_pr_spo_int_id;
3049 ELSE
3050 l_last_updated_by := get_last_updated_by;
3051 l_last_update_date := get_last_update_date;
3052 l_last_update_login := get_last_update_login;
3053 l_request_id := get_request_id;
3054 l_program_application_id := get_program_application_id;
3055 l_program_id := get_program_id;
3056 l_program_update_date := get_program_update_date;
3057 UPDATE igs_pr_lgcy_spo_int
3058 SET import_status = DECODE(l_return_status,
3059 'S','I',
3060 'U','E',
3061 'W','W',
3062 'E'),
3063 last_update_date = l_last_update_date,
3064 last_updated_by = l_last_updated_by,
3065 last_update_login = l_last_update_login,
3066 request_id = l_request_id,
3067 program_id = l_program_id,
3068 program_application_id = l_program_application_id,
3069 program_update_date = l_program_update_date
3070 WHERE legacy_pr_spo_int_id = l_pr_out_cur_rec.legacy_pr_spo_int_id;
3071 END IF;
3072
3073 EXCEPTION
3074 WHEN OTHERS THEN
3075 ROLLBACK TO savepoint_pr_out;
3076 END;
3077 END LOOP;
3078
3079 log_no_data_exists(l_not_found);
3080
3081 -- This will commit the changes to the interface table record.
3082 -- if an error was encountered then all the unwanted changes
3083 -- would have been rolled back by the rollback to savepoint.
3084 COMMIT WORK;
3085
3086 EXCEPTION
3087 WHEN g_resource_busy THEN
3088 log_resource_busy(g_cst_pr_out);
3089 WHEN OTHERS THEN
3090 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3091 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_pr_out');
3092 IGS_GE_MSG_STACK.ADD;
3093 APP_EXCEPTION.RAISE_EXCEPTION;
3094 END process_pr_out;
3095
3096 -------------------------------------------------------------------------------
3097 PROCEDURE process_pr_cr(
3098 p_batch_id IN NUMBER,
3099 p_deletion_flag IN BOOLEAN
3100 ) AS
3101 CURSOR c_pr_cr IS
3102 SELECT *
3103 FROM igs_pr_lgy_clsr_int
3104 WHERE batch_id = p_batch_id
3105 AND import_status IN ('U','R')
3106 FOR UPDATE NOWAIT;
3107
3108
3109 lr_pr_cr_rec igs_pr_clsrnk_lgcy_pub.lgcy_clsrnk_rec_type;
3110 l_return_status VARCHAR2(1);
3111 l_msg_count NUMBER(4);
3112 l_msg_data VARCHAR2(4000);
3113
3114 l_last_update_date DATE ;
3115 l_last_updated_by NUMBER;
3116 l_last_update_login NUMBER;
3117 l_request_id NUMBER;
3118 l_program_id NUMBER;
3119 l_program_application_id NUMBER;
3120 l_program_update_date DATE;
3121 l_not_found BOOLEAN;
3122
3123 BEGIN
3124 /*
3125 Process the records in the Class Rank interface table
3126 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
3127 */
3128
3129 log_headers(g_cst_pr_cr);
3130 l_not_found := TRUE;
3131
3132 FOR l_pr_cr_cur_rec IN c_pr_cr LOOP
3133 BEGIN
3134
3135 -- create a save point. if there are any errors returned by
3136 -- the API then rollback to this savepoint before processing
3137 -- then next record.
3138 SAVEPOINT savepoint_pr_cr;
3139 l_not_found := FALSE;
3140
3141 -- populate the record variable to pass to the API
3142 lr_pr_cr_rec.person_number := l_pr_cr_cur_rec.person_number ;
3143 lr_pr_cr_rec.program_cd := l_pr_cr_cur_rec.program_cd ;
3144 lr_pr_cr_rec.cohort_name := l_pr_cr_cur_rec.cohort_name ;
3145 lr_pr_cr_rec.calendar_alternate_code := l_pr_cr_cur_rec.calendar_alternate_code ;
3146 lr_pr_cr_rec.cohort_rank := l_pr_cr_cur_rec.cohort_rank ;
3147 lr_pr_cr_rec.cohort_override_rank := l_pr_cr_cur_rec.cohort_override_rank ;
3148 lr_pr_cr_rec.comments := l_pr_cr_cur_rec.comments ;
3149 lr_pr_cr_rec.as_of_rank_gpa := l_pr_cr_cur_rec.as_of_rank_gpa ;
3150
3151
3152 l_return_status := NULL;
3153 l_msg_count := NULL;
3154 l_msg_data := NULL;
3155 igs_pr_clsrnk_lgcy_pub.create_class_rank(
3156 P_API_VERSION => 1.0,
3157 P_INIT_MSG_LIST => FND_API.G_TRUE,
3158 P_COMMIT => FND_API.G_FALSE,
3159 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3160 p_lgcy_clsrnk_rec => lr_pr_cr_rec,
3161 X_RETURN_STATUS => l_return_status,
3162 X_MSG_COUNT => l_msg_count,
3163 X_MSG_DATA => l_msg_data
3164 );
3165
3166 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
3167 ROLLBACK TO savepoint_pr_cr;
3168 -- log the error message in the error message interface table
3169 log_err_messages(
3170 g_cst_pr_cr,
3171 l_pr_cr_cur_rec.legacy_cls_rank_int_id,
3172 l_msg_count,
3173 l_msg_data,
3174 l_return_status
3175 );
3176 ELSE
3177 -- log the success message in the concurrent manager log.
3178 log_suc_message(l_pr_cr_cur_rec.legacy_cls_rank_int_id);
3179 END IF;
3180
3181 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3182 -- delete any records in the error message interface table
3183 delete_err_messages(
3184 p_int_table_code => g_cst_pr_cr,
3185 p_int_table_id => l_pr_cr_cur_rec.legacy_cls_rank_int_id
3186 );
3187 -- delete the interface record
3188 DELETE FROM igs_pr_lgy_clsr_int
3189 WHERE legacy_cls_rank_int_id = l_pr_cr_cur_rec.legacy_cls_rank_int_id;
3190 ELSE
3191 l_last_updated_by := get_last_updated_by;
3192 l_last_update_date := get_last_update_date;
3193 l_last_update_login := get_last_update_login;
3194 l_request_id := get_request_id;
3195 l_program_application_id := get_program_application_id;
3196 l_program_id := get_program_id;
3197 l_program_update_date := get_program_update_date;
3198 UPDATE igs_pr_lgy_clsr_int
3199 SET import_status = DECODE(l_return_status,
3200 'S','I',
3201 'U','E',
3202 'W','W',
3203 'E'),
3204 last_update_date = l_last_update_date,
3205 last_updated_by = l_last_updated_by,
3206 last_update_login = l_last_update_login,
3207 request_id = l_request_id,
3208 program_id = l_program_id,
3209 program_application_id = l_program_application_id,
3210 program_update_date = l_program_update_date
3211 WHERE legacy_cls_rank_int_id = l_pr_cr_cur_rec.legacy_cls_rank_int_id;
3212 END IF;
3213
3214 EXCEPTION
3215 WHEN OTHERS THEN
3216 ROLLBACK TO savepoint_pr_cr;
3217 END;
3218 END LOOP;
3219
3220 log_no_data_exists(l_not_found);
3221
3222 -- This will commit the changes to the interface table record.
3223 -- if an error was encountered then all the unwanted changes
3224 -- would have been rolled back by the rollback to savepoint.
3225 COMMIT WORK;
3226
3227 EXCEPTION
3228 WHEN g_resource_busy THEN
3229 log_resource_busy(g_cst_pr_cr);
3230 WHEN OTHERS THEN
3231 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3232 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_pr_cr');
3233 IGS_GE_MSG_STACK.ADD;
3234 APP_EXCEPTION.RAISE_EXCEPTION;
3235 END process_pr_cr;
3236
3237
3238
3239 -------------------------------------------------------------------------------
3240 PROCEDURE process_gr_grd(
3241 p_batch_id IN NUMBER,
3242 p_deletion_flag IN BOOLEAN
3243 ) AS
3244 CURSOR c_gr_grd IS
3245 SELECT *
3246 FROM igs_gr_lgcy_grd_int
3247 WHERE batch_id = p_batch_id
3248 AND import_status IN ('U','R')
3249 FOR UPDATE NOWAIT;
3250
3251
3252 lr_gr_grd_rec igs_gr_grd_lgcy_pub.lgcy_grd_rec_type;
3253 l_return_status VARCHAR2(1);
3254 l_msg_count NUMBER(4);
3255 l_msg_data VARCHAR2(4000);
3256
3257 l_last_update_date DATE ;
3258 l_last_updated_by NUMBER;
3259 l_last_update_login NUMBER;
3260 l_request_id NUMBER;
3261 l_program_id NUMBER;
3262 l_program_application_id NUMBER;
3263 l_program_update_date DATE;
3264 l_not_found BOOLEAN;
3265
3266 BEGIN
3267 /*
3268 Process the records in the Graduand interface table
3269 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
3270 */
3271
3272 log_headers(g_cst_gr_grd);
3273 l_not_found := TRUE;
3274
3275 FOR l_gr_grd_cur_rec IN c_gr_grd LOOP
3276 BEGIN
3277
3278 -- create a save point. if there are any errors returned by
3279 -- the API then rollback to this savepoint before processing
3280 -- then next record.
3281 SAVEPOINT savepoint_gr_grd;
3282 l_not_found := FALSE;
3283
3284 -- populate the record variable to pass to the API
3285 lr_gr_grd_rec.person_number := l_gr_grd_cur_rec.person_number ;
3286 lr_gr_grd_rec.create_dt := l_gr_grd_cur_rec.create_dt ;
3287 lr_gr_grd_rec.grd_cal_alt_code := l_gr_grd_cur_rec.grd_cal_alt_code ;
3288 lr_gr_grd_rec.program_cd := l_gr_grd_cur_rec.program_cd ;
3289 lr_gr_grd_rec.award_program_cd := l_gr_grd_cur_rec.award_program_cd ;
3290 lr_gr_grd_rec.award_prog_version_number := l_gr_grd_cur_rec.award_prog_version_number ;
3291 lr_gr_grd_rec.award_cd := l_gr_grd_cur_rec.award_cd ;
3292 lr_gr_grd_rec.graduand_status := l_gr_grd_cur_rec.graduand_status ;
3293 lr_gr_grd_rec.graduand_appr_status := l_gr_grd_cur_rec.graduand_appr_status ;
3294 lr_gr_grd_rec.s_graduand_type := l_gr_grd_cur_rec.s_graduand_type ;
3295 lr_gr_grd_rec.graduation_name := l_gr_grd_cur_rec.graduation_name ;
3296 lr_gr_grd_rec.proxy_award_person_number := l_gr_grd_cur_rec.proxy_award_person_number ;
3297 lr_gr_grd_rec.previous_qualifications := l_gr_grd_cur_rec.previous_qualifications ;
3298 lr_gr_grd_rec.convocation_membership_ind := l_gr_grd_cur_rec.convocation_membership_ind ;
3299 lr_gr_grd_rec.sur_for_program_cd := l_gr_grd_cur_rec.sur_for_program_cd ;
3300 lr_gr_grd_rec.sur_for_prog_version_number := l_gr_grd_cur_rec.sur_for_prog_version_number ;
3301 lr_gr_grd_rec.sur_for_award_cd := l_gr_grd_cur_rec.sur_for_award_cd ;
3302 lr_gr_grd_rec.comments := l_gr_grd_cur_rec.comments ;
3303 lr_gr_grd_rec.attribute_category := l_gr_grd_cur_rec.attribute_category ;
3304 lr_gr_grd_rec.attribute1 := l_gr_grd_cur_rec.attribute1 ;
3305 lr_gr_grd_rec.attribute2 := l_gr_grd_cur_rec.attribute2 ;
3306 lr_gr_grd_rec.attribute3 := l_gr_grd_cur_rec.attribute3 ;
3307 lr_gr_grd_rec.attribute4 := l_gr_grd_cur_rec.attribute4 ;
3308 lr_gr_grd_rec.attribute5 := l_gr_grd_cur_rec.attribute5 ;
3309 lr_gr_grd_rec.attribute6 := l_gr_grd_cur_rec.attribute6 ;
3310 lr_gr_grd_rec.attribute7 := l_gr_grd_cur_rec.attribute7 ;
3311 lr_gr_grd_rec.attribute8 := l_gr_grd_cur_rec.attribute8 ;
3312 lr_gr_grd_rec.attribute9 := l_gr_grd_cur_rec.attribute9 ;
3313 lr_gr_grd_rec.attribute10 := l_gr_grd_cur_rec.attribute10 ;
3314 lr_gr_grd_rec.attribute11 := l_gr_grd_cur_rec.attribute11 ;
3315 lr_gr_grd_rec.attribute12 := l_gr_grd_cur_rec.attribute12 ;
3316 lr_gr_grd_rec.attribute13 := l_gr_grd_cur_rec.attribute13 ;
3317 lr_gr_grd_rec.attribute14 := l_gr_grd_cur_rec.attribute14 ;
3318 lr_gr_grd_rec.attribute15 := l_gr_grd_cur_rec.attribute15 ;
3319 lr_gr_grd_rec.attribute16 := l_gr_grd_cur_rec.attribute16 ;
3320 lr_gr_grd_rec.attribute17 := l_gr_grd_cur_rec.attribute17 ;
3321 lr_gr_grd_rec.attribute18 := l_gr_grd_cur_rec.attribute18 ;
3322 lr_gr_grd_rec.attribute19 := l_gr_grd_cur_rec.attribute19 ;
3323 lr_gr_grd_rec.attribute20 := l_gr_grd_cur_rec.attribute20 ;
3324
3325
3326 l_return_status := NULL;
3327 l_msg_count := NULL;
3328 l_msg_data := NULL;
3329 igs_gr_grd_lgcy_pub.create_graduand(
3330 P_API_VERSION => 1.0,
3331 P_INIT_MSG_LIST => FND_API.G_TRUE,
3332 P_COMMIT => FND_API.G_FALSE,
3333 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3334 p_lgcy_grd_rec => lr_gr_grd_rec,
3335 X_RETURN_STATUS => l_return_status,
3336 X_MSG_COUNT => l_msg_count,
3337 X_MSG_DATA => l_msg_data
3338 );
3339
3340 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
3341 ROLLBACK TO savepoint_gr_grd;
3342 -- log the error message in the error message interface table
3343 log_err_messages(
3344 g_cst_gr_grd,
3345 l_gr_grd_cur_rec.legacy_gr_int_id,
3346 l_msg_count,
3347 l_msg_data,
3348 l_return_status
3349 );
3350 ELSE
3351 -- log the success message in the concurrent manager log.
3352 log_suc_message(l_gr_grd_cur_rec.legacy_gr_int_id);
3353 END IF;
3354
3355 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3356 -- delete any records in the error message interface table
3357 delete_err_messages(
3358 p_int_table_code => g_cst_gr_grd,
3359 p_int_table_id => l_gr_grd_cur_rec.legacy_gr_int_id
3360 );
3361 -- delete the interface record
3362 DELETE FROM igs_gr_lgcy_grd_int
3363 WHERE legacy_gr_int_id = l_gr_grd_cur_rec.legacy_gr_int_id;
3364 ELSE
3365 l_last_updated_by := get_last_updated_by;
3366 l_last_update_date := get_last_update_date;
3367 l_last_update_login := get_last_update_login;
3368 l_request_id := get_request_id;
3369 l_program_application_id := get_program_application_id;
3370 l_program_id := get_program_id;
3371 l_program_update_date := get_program_update_date;
3372 UPDATE igs_gr_lgcy_grd_int
3373 SET import_status = DECODE(l_return_status,
3374 'S','I',
3375 'U','E',
3376 'W','W',
3377 'E'),
3378 last_update_date = l_last_update_date,
3379 last_updated_by = l_last_updated_by,
3380 last_update_login = l_last_update_login,
3381 request_id = l_request_id,
3382 program_id = l_program_id,
3383 program_application_id = l_program_application_id,
3384 program_update_date = l_program_update_date
3385 WHERE legacy_gr_int_id = l_gr_grd_cur_rec.legacy_gr_int_id;
3386 END IF;
3387
3388 EXCEPTION
3389 WHEN OTHERS THEN
3390 ROLLBACK TO savepoint_gr_grd;
3391 END;
3392 END LOOP;
3393
3394 log_no_data_exists(l_not_found);
3395
3396 -- This will commit the changes to the interface table record.
3397 -- if an error was encountered then all the unwanted changes
3398 -- would have been rolled back by the rollback to savepoint.
3399 COMMIT WORK;
3400
3401 EXCEPTION
3402 WHEN g_resource_busy THEN
3403 log_resource_busy(g_cst_gr_grd);
3404 WHEN OTHERS THEN
3405 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3406 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_gr_grd');
3407 IGS_GE_MSG_STACK.ADD;
3408 APP_EXCEPTION.RAISE_EXCEPTION;
3409 END process_gr_grd;
3410
3411 -------------------------------------------------------------------------------
3412 -- anilk, transcript comments
3413 PROCEDURE process_as_trncmt(
3414 p_batch_id IN NUMBER,
3415 p_deletion_flag IN BOOLEAN
3416 ) AS
3417 CURSOR c_as_trncmt IS
3418 SELECT *
3419 FROM igs_as_lgcy_stc_int
3420 WHERE batch_id = p_batch_id
3421 AND import_status IN ('U','R')
3422 FOR UPDATE NOWAIT;
3423
3424
3425 lr_as_trncmt_rec igs_as_trncmt_lgcy_pub.lgcy_trncmt_rec_type;
3426 l_return_status VARCHAR2(1);
3427 l_msg_count NUMBER(4);
3428 l_msg_data VARCHAR2(4000);
3429
3430 l_last_update_date DATE ;
3431 l_last_updated_by NUMBER;
3432 l_last_update_login NUMBER;
3433 l_request_id NUMBER;
3434 l_program_id NUMBER;
3435 l_program_application_id NUMBER;
3436 l_program_update_date DATE;
3437 l_not_found BOOLEAN;
3438
3439 BEGIN
3440 /*
3441 Process the records in the Student Transcript Comments interface table
3442 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
3443 */
3444
3445 log_headers(g_cst_as_trncmt);
3446 l_not_found := TRUE;
3447
3448 FOR l_as_trncmt_cur_rec IN c_as_trncmt LOOP
3449 BEGIN
3450
3451 -- create a save point. if there are any errors returned by
3452 -- the API then rollback to this savepoint before processing
3453 -- then next record.
3454 SAVEPOINT savepoint_as_trncmt;
3455 l_not_found := FALSE;
3456
3457 -- populate the record variable to pass to the API
3458 lr_as_trncmt_rec.comment_type_code := l_as_trncmt_cur_rec.comment_type_code;
3459 lr_as_trncmt_rec.comment_txt := l_as_trncmt_cur_rec.comment_txt;
3460 lr_as_trncmt_rec.person_number := l_as_trncmt_cur_rec.person_number;
3461 lr_as_trncmt_rec.program_cd := l_as_trncmt_cur_rec.program_cd;
3462 lr_as_trncmt_rec.program_type := l_as_trncmt_cur_rec.program_type;
3463 lr_as_trncmt_rec.award_cd := l_as_trncmt_cur_rec.award_cd;
3464 lr_as_trncmt_rec.load_cal_alternate_cd := l_as_trncmt_cur_rec.load_cal_alternate_cd;
3465 lr_as_trncmt_rec.unit_set_cd := l_as_trncmt_cur_rec.unit_set_cd;
3466 lr_as_trncmt_rec.us_version_number := l_as_trncmt_cur_rec.us_version_number;
3467 lr_as_trncmt_rec.unit_cd := l_as_trncmt_cur_rec.unit_cd;
3468 lr_as_trncmt_rec.version_number := l_as_trncmt_cur_rec.version_number;
3469 lr_as_trncmt_rec.teach_cal_alternate_cd := l_as_trncmt_cur_rec.teach_cal_alternate_cd;
3470 lr_as_trncmt_rec.location_cd := l_as_trncmt_cur_rec.location_cd;
3471 lr_as_trncmt_rec.unit_class := l_as_trncmt_cur_rec.unit_class;
3472
3473 l_return_status := NULL;
3474 l_msg_count := NULL;
3475 l_msg_data := NULL;
3476 igs_as_trncmt_lgcy_pub.create_trncmt(
3477 p_api_version => 1.0,
3478 p_init_msg_list => FND_API.G_TRUE,
3479 p_commit => FND_API.G_FALSE,
3480 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3481 p_lgcy_trncmt_rec => lr_as_trncmt_rec,
3482 x_return_status => l_return_status,
3483 x_msg_count => l_msg_count,
3484 x_msg_data => l_msg_data
3485 );
3486
3487 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
3488 ROLLBACK TO savepoint_as_trncmt;
3489 -- log the error message in the error message interface table
3490 log_err_messages(
3491 g_cst_as_trncmt,
3492 l_as_trncmt_cur_rec.legacy_cmts_int_id,
3493 l_msg_count,
3494 l_msg_data,
3495 l_return_status
3496 );
3497 ELSE
3498 -- log the success message in the concurrent manager log.
3499 log_suc_message(l_as_trncmt_cur_rec.legacy_cmts_int_id);
3500 END IF;
3501
3502 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3503 -- delete any records in the error message interface table
3504 delete_err_messages(
3505 p_int_table_code => g_cst_as_trncmt,
3506 p_int_table_id => l_as_trncmt_cur_rec.legacy_cmts_int_id
3507 );
3508 -- delete the interface record
3509 DELETE FROM igs_as_lgcy_stc_int
3510 WHERE legacy_cmts_int_id = l_as_trncmt_cur_rec.legacy_cmts_int_id;
3511 ELSE
3512 l_last_updated_by := get_last_updated_by;
3513 l_last_update_date := get_last_update_date;
3514 l_last_update_login := get_last_update_login;
3515 l_request_id := get_request_id;
3516 l_program_application_id := get_program_application_id;
3517 l_program_id := get_program_id;
3518 l_program_update_date := get_program_update_date;
3519 UPDATE igs_as_lgcy_stc_int
3520 SET import_status = DECODE(l_return_status,
3521 'S','I',
3522 'U','E',
3523 'W','W',
3524 'E'),
3525 last_update_date = l_last_update_date,
3526 last_updated_by = l_last_updated_by,
3527 last_update_login = l_last_update_login,
3528 request_id = l_request_id,
3529 program_id = l_program_id,
3530 program_application_id = l_program_application_id,
3531 program_update_date = l_program_update_date
3532 WHERE legacy_cmts_int_id = l_as_trncmt_cur_rec.legacy_cmts_int_id;
3533 END IF;
3534
3535 EXCEPTION
3536 WHEN OTHERS THEN
3537 ROLLBACK TO savepoint_as_trncmt;
3538 END;
3539 END LOOP;
3540
3541 log_no_data_exists(l_not_found);
3542
3543 EXCEPTION
3544 WHEN g_resource_busy THEN
3545 log_resource_busy(g_cst_as_trncmt);
3546 WHEN OTHERS THEN
3547 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3548 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_as_trncmt');
3549 IGS_GE_MSG_STACK.ADD;
3550 APP_EXCEPTION.RAISE_EXCEPTION;
3551 END process_as_trncmt;
3552
3553
3554
3555 ----------------------------------------------------------------------------------------------
3556 PROCEDURE process_en_spi_rcond(
3557 p_batch_id IN NUMBER,
3558 p_deletion_flag IN BOOLEAN
3559 ) AS
3560
3561 CURSOR c_en_spi_rcond IS
3562 SELECT * from IGS_EN_SPI_RCOND_INTS
3563 WHERE import_status IN ('U','R')
3564 AND batch_id = p_batch_id
3565 FOR UPDATE NOWAIT;
3566
3567
3568 lr_en_spi_rcond_rec IGS_EN_SPI_RCOND_LGCY_PUB.en_spi_rcond_rec_type ;
3569 l_return_status VARCHAR2(1);
3570 l_msg_count NUMBER(4);
3571 l_msg_data VARCHAR2(4000);
3572
3573 l_last_update_date DATE ;
3574 l_last_updated_by NUMBER;
3575 l_last_update_login NUMBER;
3576 l_request_id NUMBER;
3577 l_program_id NUMBER;
3578 l_program_application_id NUMBER;
3579 l_program_update_date DATE;
3580 l_not_found BOOLEAN;
3581
3582 BEGIN
3583 /*
3584 Process the records in the Student program intermission return conditions interface table
3585 where the import status is either 'U' - Unproccessed or 'R' ready for reporccessing
3586 */
3587
3588 log_headers(g_cst_en_spi_rcond);
3589 l_not_found := TRUE;
3590
3591 FOR l_en_spi_rcond_cur_rec IN c_en_spi_rcond LOOP
3592 BEGIN
3593
3594 -- create a save point. if there are any errors returned by
3595 -- the API then rollback to this savepoint before processing
3596 -- then next record.
3597 SAVEPOINT savepoint_en_spi_rcond;
3598 l_not_found := FALSE;
3599
3600 -- populate the record variable to pass to the API
3601 lr_en_spi_rcond_rec.person_number := l_en_spi_rcond_cur_rec.person_number ;
3602 lr_en_spi_rcond_rec.program_cd := l_en_spi_rcond_cur_rec.program_cd ;
3603 lr_en_spi_rcond_rec.start_dt := l_en_spi_rcond_cur_rec.start_dt ;
3604 lr_en_spi_rcond_rec.RETURN_CONDITION := l_en_spi_rcond_cur_rec.RETURN_CONDITION;
3605 lr_en_spi_rcond_rec.status_code := l_en_spi_rcond_cur_rec.status_code ;
3606 lr_en_spi_rcond_rec.approved_dt := l_en_spi_rcond_cur_rec.approved_dt ;
3607 lr_en_spi_rcond_rec.approver_number := l_en_spi_rcond_cur_rec.approver_number ;
3608
3609 l_return_status := NULL;
3610 l_msg_count := NULL;
3611 l_msg_data := NULL;
3612 IGS_EN_SPI_RCOND_LGCY_PUB.create_student_intm_rcond(
3613 P_API_VERSION => 1.0,
3614 P_INIT_MSG_LIST => FND_API.G_TRUE,
3615 P_COMMIT => FND_API.G_FALSE,
3616 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3617 p_intm_rcond_rec => lr_en_spi_rcond_rec,
3618 X_RETURN_STATUS => l_return_status,
3619 X_MSG_COUNT => l_msg_count,
3620 X_MSG_DATA => l_msg_data
3621 );
3622
3623 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_return_status IS NULL THEN
3624 ROLLBACK TO savepoint_en_spi_rcond;
3625 -- log the error message in the error message interface table
3626 log_err_messages(
3627 g_cst_en_spi_rcond ,
3628 l_en_spi_rcond_cur_rec.LGCY_EN_SPI_RCONS_INT_ID,
3629 l_msg_count,
3630 l_msg_data,
3631 l_return_status
3632 );
3633 ELSE
3634 -- log the success message in the concurrent manager log.
3635 log_suc_message(l_en_spi_rcond_cur_rec.LGCY_EN_SPI_RCONS_INT_ID );
3636 END IF;
3637
3638 IF p_deletion_flag AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3639 -- delete any records in the error message interface table
3640 delete_err_messages(
3641 p_int_table_code => g_cst_en_spi_rcond,
3642 p_int_table_id => l_en_spi_rcond_cur_rec.LGCY_EN_SPI_RCONS_INT_ID
3643 );
3644
3645 -- delete the interface record
3646 DELETE FROM IGS_EN_SPI_RCOND_INTS
3647 WHERE LGCY_EN_SPI_RCONS_INT_ID = l_en_spi_rcond_cur_rec.LGCY_EN_SPI_RCONS_INT_ID ;
3648 ELSE
3649 l_last_updated_by := get_last_updated_by;
3650 l_last_update_date := get_last_update_date;
3651 l_last_update_login := get_last_update_login;
3652 l_request_id := get_request_id;
3653 l_program_application_id := get_program_application_id;
3654 l_program_id := get_program_id;
3655 l_program_update_date := get_program_update_date;
3656 UPDATE IGS_EN_SPI_RCOND_INTS
3657 SET import_status = DECODE(l_return_status,
3658 'S','I',
3659 'U','E',
3660 'W','W',
3661 'E'),
3662 last_update_date = l_last_update_date,
3663 last_updated_by = l_last_updated_by,
3664 last_update_login = l_last_update_login,
3665 request_id = l_request_id,
3666 program_id = l_program_id,
3667 program_application_id = l_program_application_id,
3668 program_update_date = l_program_update_date
3669 WHERE LGCY_EN_SPI_RCONS_INT_ID = l_en_spi_rcond_cur_rec.LGCY_EN_SPI_RCONS_INT_ID ;
3670 END IF;
3671
3672 EXCEPTION
3673 WHEN OTHERS THEN
3674 ROLLBACK TO savepoint_en_spi_rcond;
3675 END;
3676 END LOOP;
3677
3678 log_no_data_exists(l_not_found);
3679
3680 -- This will commit the changes to the interface table record.
3681 -- if an error was encountered then all the unwanted changes
3682 -- would have been rolled back by the rollback to savepoint.
3683 COMMIT WORK;
3684
3685 EXCEPTION
3686 WHEN g_resource_busy THEN
3687 log_resource_busy(g_cst_en_spi_rcond);
3688 WHEN OTHERS THEN
3689 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
3690 FND_MESSAGE.SET_TOKEN('NAME','igs_en_lgcy_prc.legacy_batch_process.process_en_spi_rcond');
3691 IGS_GE_MSG_STACK.ADD;
3692 APP_EXCEPTION.RAISE_EXCEPTION;
3693 END process_en_spi_rcond;
3694
3695 -------------------------------------------------------------------------------
3696 PROCEDURE legacy_batch_process(
3697 errbuf OUT NOCOPY VARCHAR2,
3698 retcode OUT NOCOPY NUMBER,
3699 p_batch_id IN NUMBER,
3700 p_table_code IN VARCHAR2,
3701 p_delete_flag IN VARCHAR2
3702 ) AS
3703
3704 CURSOR c_batch_desc IS
3705 SELECT description
3706 FROM igs_en_lgcy_bat_int
3707 WHERE batch_id = p_batch_id;
3708
3709 l_description igs_en_lgcy_bat_int.description%TYPE;
3710 l_deletion_flag BOOLEAN;
3711 l_msg_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
3712 l_msg_text0 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
3713 BEGIN
3714 /*
3715 This batch process has two modes.
3716 1. When the p_table_code is passed as 'All'
3717 2. when the p_table_code is passed any other value
3718
3719 In the first case all the interface tables will processed in the
3720 order in which the "IF" statments appear. This sequence is very
3721 important since the student unit attempt cannot be imported
3722 before the student program attempt is imported.
3723
3724 It is also import to sort the individual records within the
3725 interface tables for processing. If a unit is transfered
3726 from another program then the original unit must be imported
3727 first and then the one to which it was transfered.
3728 These are taken care of in the individual procedure written in
3729 this package by way of the "Order by" clause.
3730
3731 In the second case mentioned above, only one interface table
3732 would be processed corresponding to the table code that is passed
3733 */
3734 -- initializing the boolean value depending on the p_delete_flag
3735 -- if the p_delete_flag is 'Y' then set the boolean value to true
3736 -- other wise set the boolean value to false.
3737
3738 igs_ge_gen_003.set_org_id(NULL);
3739
3740 l_deletion_flag := FALSE;
3741 IF p_delete_flag IS NOT NULL AND p_delete_flag = 'Y' THEN
3742 l_deletion_flag := TRUE;
3743 END IF;
3744
3745 OPEN c_batch_desc;
3746 FETCH c_batch_desc INTO l_description;
3747 CLOSE c_batch_desc;
3748
3749 FND_MESSAGE.SET_NAME('IGS','IGS_EN_BATCH_ID');
3750 l_msg_text := FND_MESSAGE.GET;
3751
3752 FND_MESSAGE.SET_NAME('IGS','IGS_EN_REG_LOG_DESC');
3753 l_msg_text0 := FND_MESSAGE.GET;
3754
3755 FND_FILE.PUT_LINE(FND_FILE.LOG,'================================================================================');
3756 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3757 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_text || ': ' || p_batch_id || ' ' || l_msg_text0 || ': ' || l_description);
3758 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3759 FND_FILE.PUT_LINE(FND_FILE.LOG,'================================================================================');
3760
3761 -- Call the procedure to process Student Program Attempt interface records
3762 IF p_table_code = g_cst_all OR p_table_code = g_cst_en_spa THEN
3763 process_en_spa(
3764 p_batch_id,
3765 l_deletion_flag
3766 );
3767 END IF;
3768
3769 -- Call the procedure to process term records interface records
3770 IF p_table_code = g_cst_all OR p_table_code = g_cst_en_spat THEN
3771 process_en_spat (
3772 p_batch_id,
3773 l_deletion_flag
3774 );
3775 END IF;
3776 -- Call the procedure to process student unit attempt reference codes.
3777
3778 IF p_table_code = g_cst_all OR p_table_code = g_cst_as_suarc THEN
3779 process_as_suarc(
3780 p_batch_id,
3781 l_deletion_flag
3782 );
3783 END IF;
3784
3785 -- Call the procedure to process unit set attempt interface records.
3786 IF p_table_code = g_cst_all OR p_table_code = g_cst_en_susa THEN
3787 process_en_susa(
3788 p_batch_id,
3789 l_deletion_flag
3790 );
3791 END IF;
3792
3793 -- Call the procedure to process progarm intermission interface records.
3794 IF p_table_code = g_cst_all OR p_table_code = g_cst_en_spi THEN
3795 process_en_spi(
3796 p_batch_id,
3797 l_deletion_flag
3798 );
3799 END IF;
3800
3801 -- Call the procedure to process intermission return conditions
3802 IF p_table_code = g_cst_all OR p_table_code = g_cst_en_spi_rcond THEN
3803 process_en_spi_rcond(
3804 p_batch_id,
3805 l_deletion_flag
3806 );
3807 END IF;
3808
3809 -- Call the procedure to process program award aim interface records.
3810 IF p_table_code = g_cst_all OR p_table_code = g_cst_en_spaa THEN
3811 process_en_spaa(
3812 p_batch_id,
3813 l_deletion_flag
3814 );
3815 END IF;
3816
3817 -- Call the procedure to process research supervisor interface records.
3818 IF p_table_code = g_cst_all OR p_table_code = g_cst_re_sprvsr THEN
3819 process_re_sprvsr(
3820 p_batch_id,
3821 l_deletion_flag
3822 );
3823 END IF;
3824
3825 -- Call the procedure to process research thesis interface records.
3826 IF p_table_code = g_cst_all OR p_table_code = g_cst_re_the THEN
3827 process_re_the(
3828 p_batch_id,
3829 l_deletion_flag
3830 );
3831 END IF;
3832
3833 -- Call the procedure to process unit attempt interface records.
3834 IF p_table_code = g_cst_all OR p_table_code = g_cst_en_sua THEN
3835 process_en_sua(
3836 p_batch_id,
3837 l_deletion_flag
3838 );
3839 END IF;
3840
3841 -- Call the procedure to process HESA program attempt interface records.
3842 IF p_table_code = g_cst_all OR p_table_code = g_cst_he_spa THEN
3843 process_he_spa(
3844 p_batch_id,
3845 l_deletion_flag
3846 );
3847 END IF;
3848
3849 -- Call the procedure to process HESA Unit set attempt interface records.
3850 IF p_table_code = g_cst_all OR p_table_code = g_cst_he_susa THEN
3851 process_he_susa(
3852 p_batch_id,
3853 l_deletion_flag
3854 );
3855 END IF;
3856
3857 -- Call the procedure to process advance standing level interface records.
3858 IF p_table_code = g_cst_all OR p_table_code = g_cst_av_avstdl THEN
3859 process_av_avstdl(
3860 p_batch_id,
3861 l_deletion_flag
3862 );
3863 END IF;
3864
3865 -- Call the procedure to process advance standing unit interface records.
3866 IF p_table_code = g_cst_all OR p_table_code = g_cst_av_untstd THEN
3867 process_av_untstd(
3868 p_batch_id,
3869 l_deletion_flag
3870 );
3871 END IF;
3872
3873 -- Call the procedure to process unit outcome interface records.
3874 IF p_table_code = g_cst_all OR p_table_code = g_cst_as_uotcm THEN
3875 process_as_uotcm(
3876 p_batch_id,
3877 l_deletion_flag
3878 );
3879 END IF;
3880
3881 -- Call the procedure to process progression outcome interface records.
3882 IF p_table_code = g_cst_all OR p_table_code = g_cst_pr_out THEN
3883 process_pr_out(
3884 p_batch_id,
3885 l_deletion_flag
3886 );
3887 END IF;
3888
3889 -- Call the procedure to process class rank interface records.
3890 IF p_table_code = g_cst_all OR p_table_code = g_cst_pr_cr THEN
3891 process_pr_cr(
3892 p_batch_id,
3893 l_deletion_flag
3894 );
3895 END IF;
3896
3897 -- Call the procedure to process graduan interface records.
3898 IF p_table_code = g_cst_all OR p_table_code = g_cst_gr_grd THEN
3899 process_gr_grd(
3900 p_batch_id,
3901 l_deletion_flag
3902 );
3903 END IF;
3904
3905 -- anilk, transcript comments
3906 -- Call the procedure to process transcript comments interface records.
3907 IF p_table_code = g_cst_all OR p_table_code = g_cst_as_trncmt THEN
3908 process_as_trncmt(
3909 p_batch_id,
3910 l_deletion_flag
3911 );
3912 END IF;
3913
3914
3915 EXCEPTION
3916 WHEN OTHERS THEN
3917 retcode:=2;
3918 Fnd_File.PUT_LINE(Fnd_File.LOG,SQLERRM);
3919 ERRBUF := Fnd_Message.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
3920 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
3921 END legacy_batch_process;
3922
3923
3924 END igs_en_lgcy_prc;