[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_LGCY_CONC_PKG
Source
1 PACKAGE BODY igs_ps_lgcy_conc_pkg AS
2 /* $Header: IGSPS87B.pls 120.3 2006/01/24 00:33:31 sarakshi ship $ */
3
4 /******************************************************************
5 Created By :
6 Date Created By :
7 Purpose :
8 Known limitations,
9 enhancements,
10 remarks :
11 Change History
12 Who When What
13 sarakshi 02-sep-2003 Enh#3052452,removed the reference of the column sup_unit_allowed_ind and sub_unit_allowed_ind. Also populated the data for unit section
14 vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Modified legacy_batch_process.
15 ******************************************************************/
16
17 PROCEDURE legacy_batch_process(
18 errbuf OUT NOCOPY VARCHAR2,
19 retcode OUT NOCOPY NUMBER,
20 p_n_batch_id NUMBER,
21 p_c_del_flag VARCHAR2
22 )
23 AS
24 /**********************************************************
25 Created By : jdeekoll
26
27 Date Created By : 29-NOV-2002
28
29 Purpose : For scheduling the Unit Section occurrences
30
31 Know limitations, enhancements or remarks
32
33 Change History
34
35 Who When What
36 sarakshi 12-Jan-2006 bug#4926548, replaced the cursor c_table with the pl-sql table .
37 sommukhe 9-JAN-2006 Bug# 4869737,included call to igs_ge_gen_003.set_org_id.
38 smvk 28-Jul-2004 Bug # 3793580. Allowing the user to import instructors for No Set Day USO.
39 Added column no_set_day_ind to interface table IGS_PS_LGCY_INS_INT.
40 sarakshi 04-May-2004 Enh#3568858,populated columns ovrd_wkld_val_flag, workload_val_code to the unit record type
41 smvk 19-Apr-2004 Bug # 3565536. Picking up multiple unit section grading schemas, Also added order by clause to print the
42 log file information order by interface sequence identifier.
43 sarakshi 16-Feb-2004 Bug#3431844, added owner filter in the cursor c_table and modified its usage accordingly
44 sarakshi 10-Nov-2003 Enh#3116171, added logic related to the newly introduced field BILLING_CREDIT_POINTS in unit and unit section level
45 vvutukur 05-Aug-2003 Enh#3045069.PSP Enh Build. Added code to populate data for column
46 not_multiple_section_flag at unit section level.
47 sarakshi 28-Jun-2003 Enh#2930935,added enrolled and achievable credit points to
48 the unit section pl/sql table
49 smvk 27-Jun-2003 Enh Bug # 2999888. Added Gen_ref_flag in unit reference code.
50 jbegum 02-June-2003 Enh#2972950
51 For Legacy Enhancements TD:
52 Appropriate changes carried out for the inclusion of
53 igs_ps_lgcy_ins_int interface table also added few column in the
54 unit section table to get imported.
55 For PSP Scheduling Enhancements TD:
56 Select preferred_region_code and no_set_day_indicator values from
57 legacy interface table IGS_PS_LGCY_OC_INT into PL/SQL table v_tab_uso.
58 Select unit section occurrence start date/end date and no set day indicator
59 values from legacy interface table IGS_PS_LGCY_UR_INT into PL/SQL table v_tab_unit_ref.
60 smvk 11-Dec-2002 Bug # 2702065. Modified rec_c_unit_sec.version_number,
61 l_unit_ver_rec.contact_hrs_lab and rec_c_unit_sec.unit_cd.
62 smvk 23-Dec-2002 Bug # 2702147. Logging of successful message in the log file and
63 updation of import_status of the record as 'I' only when the
64 overall status (x_return_status of API) is 'S'.
65 smvk 24-Dec-2002 Bug # 2702147. Printing the row head if the value of
66 the variable is l_b_print_row_heading TRUE.
67 smvk 31-Dec-2002 Bug # 2710978. Collecting the statistics of the interface table as per standards.
68 smvk 02-Jan-2002 Bug # 2695956. The process return status is set to Success even if one of the
69 record is successfully imported.(i.e the process status will be error only if all
70 the attempted records to import ends up in error).
71 (reverse chronological order - newest change first)
72 ***************************************************************/
73
74 -- Distinct Unit Versions from all 8 Interface Tables
75 CURSOR c_all_units IS
76 SELECT DISTINCT
77 unit_cd,
78 version_number
79 FROM igs_ps_lgcy_uv_int
80 WHERE batch_id=p_n_batch_id
81 AND import_status IN ('U','R')
82 UNION
83 SELECT DISTINCT
84 unit_cd,
85 version_number
86 FROM igs_ps_lgcy_tr_int
87 WHERE batch_id=p_n_batch_id
88 AND import_status IN ('U','R')
89 UNION
90 SELECT DISTINCT
91 unit_cd,
92 version_number
93 FROM igs_ps_lgcy_ud_int
94 WHERE batch_id=p_n_batch_id
95 AND import_status IN ('U','R')
96 UNION
97 SELECT DISTINCT
98 unit_cd,
99 unit_version_number
100 FROM igs_ps_lgcy_ug_int
101 WHERE batch_id=p_n_batch_id
102 AND import_status IN ('U','R')
103 UNION
104 SELECT DISTINCT
105 unit_cd,
106 version_number
107 FROM igs_ps_lgcy_us_int
108 WHERE batch_id=p_n_batch_id
109 AND import_status IN ('U','R')
110 UNION
111 SELECT DISTINCT
112 unit_cd,
113 version_number
114 FROM igs_ps_lgcy_sg_int
115 WHERE batch_id=p_n_batch_id
116 AND import_status IN ('U','R')
117 UNION
118 SELECT DISTINCT
119 unit_cd,
120 version_number
121 FROM igs_ps_lgcy_oc_int
122 WHERE batch_id=p_n_batch_id
123 AND import_status IN ('U','R')
124 UNION
125 SELECT DISTINCT
126 unit_cd,
127 version_number
128 FROM igs_ps_lgcy_ur_int
129 WHERE batch_id=p_n_batch_id
130 AND import_status IN ('U','R')
131 UNION
132 SELECT DISTINCT
133 unit_cd,
134 version_number
135 FROM igs_ps_lgcy_ins_int
136 WHERE batch_id=p_n_batch_id
137 AND import_status IN ('U','R')
138 ORDER BY unit_cd,version_number;
139
140 -- Unit Version Interface Table cursor
141
142 CURSOR c_unit_ver(cp_c_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,cp_n_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
143 SELECT *
144 FROM igs_ps_lgcy_uv_int
145 WHERE batch_id=p_n_batch_id
146 AND unit_cd=cp_c_unit_cd
147 AND version_number=cp_n_version_number
148 AND import_status IN ('U','R');
149
150 -- Teaching Responsibility Interface Table cursor
151
152 CURSOR c_teach_resp(cp_c_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,cp_n_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
153 SELECT *
154 FROM igs_ps_lgcy_tr_int
155 WHERE batch_id=p_n_batch_id
156 AND unit_cd=cp_c_unit_cd
157 AND version_number=cp_n_version_number
158 AND import_status IN ('U','R')
159 ORDER BY unit_cd,version_number, teach_resp_int_id;
160
161 -- Unit Discplines Interface Table cursor
162
163 CURSOR c_unit_disp(cp_c_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,cp_n_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
164 SELECT *
165 FROM igs_ps_lgcy_ud_int
166 WHERE batch_id=p_n_batch_id
167 AND unit_cd=cp_c_unit_cd
168 AND version_number=cp_n_version_number
169 AND import_status IN ('U','R')
170 ORDER BY unit_cd,version_number, unit_discip_int_id;
171
172
173 -- Grading Schema Interface Table cursor
174
175 CURSOR c_grd_sch(cp_c_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,cp_n_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
176 SELECT *
177 FROM igs_ps_lgcy_ug_int
178 WHERE batch_id=p_n_batch_id
179 AND unit_cd=cp_c_unit_cd
180 AND unit_version_number=cp_n_version_number
181 AND import_status IN ('U','R')
182 ORDER BY unit_cd,unit_version_number, uv_grd_schm_int_id;
183
184 -- Unit Section Interface Table cursor
185
186 CURSOR c_unit_sec(cp_c_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,cp_n_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
187 SELECT *
188 FROM igs_ps_lgcy_us_int
189 WHERE batch_id=p_n_batch_id
190 AND unit_cd=cp_c_unit_cd
191 AND version_number=cp_n_version_number
192 AND import_status IN ('U','R')
193 ORDER BY unit_cd,version_number, unit_section_int_id;
194
195 -- Unit Section Grading Schema Interface Table cursor
196
197 CURSOR c_us_grd_sch(cp_c_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,cp_n_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
198 SELECT *
199 FROM igs_ps_lgcy_sg_int
200 WHERE batch_id=p_n_batch_id
201 AND unit_cd=cp_c_unit_cd
202 AND version_number=cp_n_version_number
203 AND import_status IN ('U','R')
204 ORDER BY unit_cd,version_number, usec_grd_schm_int_id;
205
206 -- Unit Section Occurrences Interface Table cursor
207
208 CURSOR c_uso(cp_c_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,cp_n_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
209 SELECT *
210 FROM igs_ps_lgcy_oc_int
211 WHERE batch_id=p_n_batch_id
212 AND unit_cd=cp_c_unit_cd
213 AND version_number=cp_n_version_number
214 AND import_status IN ('U','R')
215 ORDER BY unit_cd,version_number, usec_occur_int_id;
216
217 -- Unit Reference Codes Interface Table cursor
218
219 CURSOR c_unit_ref_cd(cp_c_unit_cd igs_ps_unit_ver_all.unit_cd%TYPE,cp_n_version_number igs_ps_unit_ver_all.version_number%TYPE) IS
220 SELECT *
221 FROM igs_ps_lgcy_ur_int
222 WHERE batch_id=p_n_batch_id
223 AND unit_cd=cp_c_unit_cd
224 AND version_number=cp_n_version_number
225 AND import_status IN ('U','R')
226 ORDER BY unit_cd,version_number, unit_reference_int_id;
227
228 -- Instructor Interface Table Cursor
229 CURSOR c_ins ( cp_n_batch_id igs_ps_lgcy_ins_int.batch_id%TYPE ,
230 cp_c_unit_cd igs_ps_lgcy_ins_int.unit_cd%TYPE,
231 cp_n_version_number igs_ps_lgcy_ins_int.version_number%TYPE) IS
232 SELECT *
233 FROM igs_ps_lgcy_ins_int
234 WHERE batch_id=cp_n_batch_id
235 AND unit_cd=cp_c_unit_cd
236 AND version_number=cp_n_version_number
237 AND import_status IN ('U','R')
238 ORDER BY unit_cd,version_number, uso_instructor_int_id;
239
240
241 TYPE tabnames IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
242 tablenames_tbl tabnames;
243
244
245 rec_c_all_units c_all_units%ROWTYPE;
246 l_n_chld_cntr NUMBER(7);
247 l_c_return_status VARCHAR2(1);
248 l_n_msg_count NUMBER(10);
249 l_c_msg_data VARCHAR2(2000);
250 l_n_msg_num fnd_new_messages.message_number%TYPE;
251 l_c_msg_txt fnd_new_messages.message_text%TYPE;
252 l_c_msg_name fnd_new_messages.message_name%TYPE;
253 l_appl_name VARCHAR2(30);
254
255 rec_c_unit_ver c_unit_ver%ROWTYPE;
256 l_unit_ver_rec igs_ps_generic_pub.unit_ver_rec_type;
257 v_tab_unit_tr igs_ps_generic_pub.unit_tr_tbl_type;
258 v_tab_unit_dscp igs_ps_generic_pub.unit_dscp_tbl_type;
259 v_tab_unit_gs igs_ps_generic_pub.unit_gs_tbl_type;
260 v_tab_usec igs_ps_generic_pub.usec_tbl_type;
261 v_tab_usec_gs igs_ps_generic_pub.usec_gs_tbl_type;
262 v_tab_uso igs_ps_generic_pub.uso_tbl_type;
263 v_tab_unit_ref igs_ps_generic_pub.unit_ref_tbl_type;
264 v_tab_ins igs_ps_generic_pub.uso_ins_tbl_type;
265 l_n_request_id igs_ps_lgcy_uv_int.request_id%TYPE;
266 l_n_prog_appl_id igs_ps_lgcy_uv_int.program_application_id%TYPE;
267 l_n_prog_id igs_ps_lgcy_uv_int.program_id%TYPE;
268 l_d_prog_upd_dt igs_ps_lgcy_uv_int.program_update_date%TYPE;
269 p_head BOOLEAN ;
270 l_ret_status BOOLEAN ; -- Holds return status, TRUE if all the attempted records to import result in Error.
271 l_c_import_desc VARCHAR2(80);
272 l_c_succ_desc VARCHAR2(80);
273 l_c_status VARCHAR2(1);
274 l_c_industry VARCHAR2(1);
275 l_c_schema VARCHAR2(30);
276 l_b_return BOOLEAN;
277
278 l_b_print_row_heading BOOLEAN ; -- Use for logging the row_head
279
280 /* Procedure to get messages */
281
282 PROCEDURE get_message(p_c_msg_name VARCHAR2,p_n_msg_num OUT NOCOPY NUMBER,p_c_msg_txt OUT NOCOPY VARCHAR2) AS
283
284 CURSOR c_msg(cp_c_msg_name fnd_new_messages.message_name%TYPE ) IS
285 SELECT
286 message_number,
287 message_text
288 FROM fnd_new_messages
289 WHERE application_id=8405
290 AND language_code = USERENV('LANG')
291 AND message_name=cp_c_msg_name;
292
293 rec_c_msg c_msg%ROWTYPE;
294 BEGIN
295 OPEN c_msg(p_c_msg_name);
296 FETCH c_msg INTO rec_c_msg;
297 IF c_msg%FOUND THEN
298 p_n_msg_num := rec_c_msg.message_number;
299 p_c_msg_txt := rec_c_msg.message_text;
300 ELSE
301 p_c_msg_txt := p_c_msg_name;
302 END IF;
303 CLOSE c_msg;
304 END get_message;
305
306 /* Procedure to write log file */
307 PROCEDURE log_file(p_c_text VARCHAR2,p_c_type VARCHAR2) AS
308 /* different types are P -> fnd_file.put, L-> fnd_file.put_line,N -> fnd_file.new_line */
309 BEGIN
310
311 IF p_c_type = 'P' THEN
312 fnd_file.put(fnd_file.log,p_c_text);
313 ELSIF p_c_type = 'L' THEN
314 fnd_file.put_line(fnd_file.log,p_c_text);
315 ELSIF p_c_type = 'N' THEN
316 fnd_file.new_line(fnd_file.log);
317 END IF;
318 END log_file;
319
320 /* Procedure to char - n times */
321 PROCEDURE print_char(p_n_count NUMBER,p_c_char VARCHAR2) AS
322 BEGIN
323 FOR I IN 1..p_n_count
324 LOOP
325 log_file(p_c_char,'P');
326 END LOOP;
327 END print_char;
328
329 /* Get message from Message Stack */
330
331 FUNCTION get_msg_from_stack(l_n_msg_count NUMBER) RETURN VARCHAR2 AS
332 l_c_msg VARCHAR2(3000);
333 l_c_msg_name fnd_new_messages.message_name%TYPE;
334 BEGIN
335 l_c_msg := FND_MSG_PUB.GET(p_msg_index => l_n_msg_count, p_encoded => 'T');
336 FND_MESSAGE.SET_ENCODED (l_c_msg);
337 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_appl_name, l_c_msg_name);
338 RETURN l_c_msg_name;
339 END get_msg_from_stack;
340
341 PROCEDURE row_head(p_c_row_type VARCHAR2,p_n_unit_cd VARCHAR2,p_n_vers_num NUMBER) AS
342 BEGIN
343 log_file(' ','N');
344 get_message('IGS_EN_ROW_TYPE',l_n_msg_num,l_c_msg_txt);
345 log_file(l_c_msg_txt,'P');
346 log_file(' ','P');
347 log_file(igs_ps_validate_lgcy_pkg.get_lkup_meaning(p_c_row_type,'LEGACY_PS_REC_TABLES'),'P');
348 log_file(' ','P');
349 log_file(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_CD','LEGACY_TOKENS'),'P');
350 log_file(' : ','P');
351 log_file(p_n_unit_cd,'P');
352 log_file(' ','P');
353 log_file(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_VER_NUM','LEGACY_TOKENS'),'P');
354 log_file(' : ','P');
355 log_file(p_n_vers_num,'L');
356 log_file(' ','N');
357
358 END row_head;
359
360 PROCEDURE print_heading AS
361 CURSOR c_batch_desc IS
362 SELECT
363 description
364 FROM igs_ps_lgcy_bat_int
365 WHERE batch_id=p_n_batch_id;
366
367 l_c_batch_desc igs_ps_lgcy_bat_int.description%TYPE;
368
369 BEGIN
370 IF p_head THEN
371
372 /****************** Begin Heading **********************/
373
374 print_char(80,'=');
375 log_file(' ','N');
376
377 OPEN c_batch_desc;
378 FETCH c_batch_desc INTO l_c_batch_desc;
379 CLOSE c_batch_desc;
380
381 get_message('IGS_EN_BATCH_ID',l_n_msg_num,l_c_msg_txt);
382 log_file(l_c_msg_txt,'P');
383 log_file(' : ','P');
384 log_file(p_n_batch_id,'P');
385 log_file(' ','P');
386 get_message('IGS_EN_REG_LOG_DESC',l_n_msg_num,l_c_msg_txt);
387 log_file(l_c_msg_txt,'P');
388 log_file(' : ','P');
389 log_file(l_c_batch_desc,'L');
390
391
392 print_char(80,'-');
393 log_file(' ','N');
394 get_message('IGS_EN_INTERFACE_ID',l_n_msg_num,l_c_msg_txt);
395 log_file(l_c_msg_txt,'P');
396 print_char(4,' ');
397
398 get_message('IGS_EN_MESSAGE_NUM',l_n_msg_num,l_c_msg_txt);
399 log_file(l_c_msg_txt,'P');
400 print_char(1,' ');
401
402 get_message('IGS_EN_ROW_STATUS',l_n_msg_num,l_c_msg_txt);
403 log_file(l_c_msg_txt,'P');
404 print_char(1,' ');
405
406 get_message('IGS_EN_MSG_TXT',l_n_msg_num,l_c_msg_txt);
407 log_file(l_c_msg_txt,'L');
408
409 print_char(80,'-');
410 log_file(' ','N');
411
412 p_head := FALSE;
413
414 /******************End Heading**********************/
415
416 END IF;
417 END print_heading;
418
419 BEGIN /* Main Begin */
420
421 igs_ge_gen_003.set_org_id (NULL);
422 -- Initializing the values to overcome file.sql.35 warning.
423 p_head := TRUE;
424 l_ret_status := TRUE; -- Holds return status, TRUE if all the attempted records to import result in Error.
425 l_b_print_row_heading := TRUE; -- Use for logging the row_head
426
427 -- To fetch table schema name for gather statistics
428 l_b_return := fnd_installation.get_app_info('IGS', l_c_status, l_c_industry, l_c_schema);
429
430 -- Collect statistics of the interface table as per standards. Bug # 2710978
431 tablenames_tbl(1) := 'IGS_PS_LGCY_UV_INT';
432 tablenames_tbl(2) := 'IGS_PS_LGCY_TR_INT';
433 tablenames_tbl(3) := 'IGS_PS_LGCY_UD_INT';
434 tablenames_tbl(4) := 'IGS_PS_LGCY_UG_INT';
435 tablenames_tbl(5) := 'IGS_PS_LGCY_US_INT';
436 tablenames_tbl(6) := 'IGS_PS_LGCY_SG_INT';
437 tablenames_tbl(7) := 'IGS_PS_LGCY_OC_INT';
438 tablenames_tbl(8) := 'IGS_PS_LGCY_UR_INT';
439 tablenames_tbl(9) := 'IGS_PS_LGCY_INS_INT';
440
441 FOR i IN 1.. tablenames_tbl.LAST
442 LOOP
443
444 -- Gather statistics of interface tables
445 fnd_stats.gather_table_stats(ownname => l_c_schema,
446 tabname => tablenames_tbl(i),
447 cascade => TRUE
448 );
449 END LOOP;
450
451 -- Set the default status as success
452 retcode := 0;
453
454 /* Setting concurrent program values */
455
456 l_n_request_id := fnd_global.conc_request_id;
457 l_n_prog_appl_id := fnd_global.prog_appl_id;
458 l_n_prog_id := fnd_global.conc_program_id;
459 l_d_prog_upd_dt := SYSDATE;
460 IF l_n_request_id = -1 THEN
461 l_n_request_id := NULL;
462 l_n_prog_appl_id := NULL;
463 l_n_prog_id := NULL;
464 l_d_prog_upd_dt := NULL;
465 END IF;
466
467 get_message('IGS_EN_LGCY_SUCCESS',l_n_msg_num,l_c_succ_desc);
468 l_c_import_desc := igs_ps_validate_lgcy_pkg.get_lkup_meaning('I','LEGACY_STATUS');
469
470 FOR rec_c_all_units IN c_all_units
471 LOOP
472
473 /******************Begin of Unit Version Record ************************/
474
475 OPEN c_unit_ver(rec_c_all_units.unit_cd,rec_c_all_units.version_number);
476 FETCH c_unit_ver INTO rec_c_unit_ver;
477 IF c_unit_ver%FOUND THEN
478
479 print_heading;
480
481 l_unit_ver_rec.unit_cd := rec_c_unit_ver.unit_cd;
482 l_unit_ver_rec.version_number := rec_c_unit_ver.version_number;
483 l_unit_ver_rec.start_dt := rec_c_unit_ver.start_dt;
484 l_unit_ver_rec.review_dt := rec_c_unit_ver.review_dt;
485 l_unit_ver_rec.expiry_dt := rec_c_unit_ver.expiry_dt;
486 l_unit_ver_rec.end_dt := rec_c_unit_ver.end_dt;
487 l_unit_ver_rec.unit_status := rec_c_unit_ver.unit_status;
488 l_unit_ver_rec.title := rec_c_unit_ver.title;
489 l_unit_ver_rec.short_title := rec_c_unit_ver.short_title;
490 l_unit_ver_rec.title_override_ind := rec_c_unit_ver.title_override_ind;
491 l_unit_ver_rec.abbreviation := rec_c_unit_ver.abbreviation;
492 l_unit_ver_rec.unit_level := rec_c_unit_ver.unit_level;
493 l_unit_ver_rec.credit_point_descriptor := rec_c_unit_ver.credit_point_descriptor;
494 l_unit_ver_rec.enrolled_credit_points := rec_c_unit_ver.enrolled_credit_points;
495 l_unit_ver_rec.points_override_ind := rec_c_unit_ver.points_override_ind;
496 l_unit_ver_rec.supp_exam_permitted_ind := rec_c_unit_ver.supp_exam_permitted_ind;
497 l_unit_ver_rec.coord_person_number := rec_c_unit_ver.coord_person_number;
498 l_unit_ver_rec.owner_org_unit_cd := rec_c_unit_ver.owner_org_unit_cd;
499 l_unit_ver_rec.award_course_only_ind := rec_c_unit_ver.award_course_only_ind;
500 l_unit_ver_rec.research_unit_ind := rec_c_unit_ver.research_unit_ind;
501 l_unit_ver_rec.industrial_ind := rec_c_unit_ver.industrial_ind;
502 l_unit_ver_rec.practical_ind := rec_c_unit_ver.practical_ind;
503 l_unit_ver_rec.repeatable_ind := rec_c_unit_ver.repeatable_ind;
504 l_unit_ver_rec.assessable_ind := rec_c_unit_ver.assessable_ind;
505 l_unit_ver_rec.achievable_credit_points := rec_c_unit_ver.achievable_credit_points;
506 l_unit_ver_rec.points_increment := rec_c_unit_ver.points_increment;
507 l_unit_ver_rec.points_min := rec_c_unit_ver.points_min;
508 l_unit_ver_rec.points_max := rec_c_unit_ver.points_max;
509 l_unit_ver_rec.unit_int_course_level_cd := rec_c_unit_ver.unit_int_course_level_cd;
510 l_unit_ver_rec.subtitle_modifiable_flag := rec_c_unit_ver.subtitle_modifiable_flag;
511 l_unit_ver_rec.approval_date := rec_c_unit_ver.approval_date;
512 l_unit_ver_rec.lecture_credit_points := rec_c_unit_ver.lecture_credit_points;
513 l_unit_ver_rec.lab_credit_points := rec_c_unit_ver.lab_credit_points;
514 l_unit_ver_rec.other_credit_points := rec_c_unit_ver.other_credit_points;
515 l_unit_ver_rec.clock_hours := rec_c_unit_ver.clock_hours;
516 l_unit_ver_rec.work_load_cp_lecture := rec_c_unit_ver.work_load_cp_lecture;
517 l_unit_ver_rec.work_load_cp_lab := rec_c_unit_ver.work_load_cp_lab;
518 l_unit_ver_rec.continuing_education_units := rec_c_unit_ver.continuing_education_units;
519 l_unit_ver_rec.enrollment_expected := rec_c_unit_ver.enrollment_expected;
520 l_unit_ver_rec.enrollment_minimum := rec_c_unit_ver.enrollment_minimum;
521 l_unit_ver_rec.enrollment_maximum := rec_c_unit_ver.enrollment_maximum;
522 l_unit_ver_rec.advance_maximum := rec_c_unit_ver.advance_maximum;
523 l_unit_ver_rec.state_financial_aid := rec_c_unit_ver.state_financial_aid;
524 l_unit_ver_rec.federal_financial_aid := rec_c_unit_ver.federal_financial_aid;
525 l_unit_ver_rec.institutional_financial_aid := rec_c_unit_ver.institutional_financial_aid;
526 l_unit_ver_rec.same_teaching_period := rec_c_unit_ver.same_teaching_period;
527 l_unit_ver_rec.max_repeats_for_credit := rec_c_unit_ver.max_repeats_for_credit;
528 l_unit_ver_rec.max_repeats_for_funding := rec_c_unit_ver.max_repeats_for_funding;
529 l_unit_ver_rec.max_repeat_credit_points := rec_c_unit_ver.max_repeat_credit_points;
530 l_unit_ver_rec.same_teach_period_repeats := rec_c_unit_ver.same_teach_period_repeats;
531 l_unit_ver_rec.same_teach_period_repeats_cp := rec_c_unit_ver.same_teach_period_repeats_cp;
532 l_unit_ver_rec.attribute_category := rec_c_unit_ver.attribute_category;
533 l_unit_ver_rec.attribute1 := rec_c_unit_ver.attribute1;
534 l_unit_ver_rec.attribute2 := rec_c_unit_ver.attribute2;
535 l_unit_ver_rec.attribute3 := rec_c_unit_ver.attribute3;
536 l_unit_ver_rec.attribute4 := rec_c_unit_ver.attribute4;
537 l_unit_ver_rec.attribute5 := rec_c_unit_ver.attribute5;
538 l_unit_ver_rec.attribute6 := rec_c_unit_ver.attribute6;
539 l_unit_ver_rec.attribute7 := rec_c_unit_ver.attribute7;
540 l_unit_ver_rec.attribute8 := rec_c_unit_ver.attribute8;
541 l_unit_ver_rec.attribute9 := rec_c_unit_ver.attribute9;
542 l_unit_ver_rec.attribute10 := rec_c_unit_ver.attribute10;
543 l_unit_ver_rec.attribute11 := rec_c_unit_ver.attribute11;
544 l_unit_ver_rec.attribute12 := rec_c_unit_ver.attribute12;
545 l_unit_ver_rec.attribute13 := rec_c_unit_ver.attribute13;
546 l_unit_ver_rec.attribute14 := rec_c_unit_ver.attribute14;
547 l_unit_ver_rec.attribute15 := rec_c_unit_ver.attribute15;
548 l_unit_ver_rec.attribute16 := rec_c_unit_ver.attribute16;
549 l_unit_ver_rec.attribute17 := rec_c_unit_ver.attribute17;
550 l_unit_ver_rec.attribute18 := rec_c_unit_ver.attribute18;
551 l_unit_ver_rec.attribute19 := rec_c_unit_ver.attribute19;
552 l_unit_ver_rec.attribute20 := rec_c_unit_ver.attribute20;
553 l_unit_ver_rec.ivr_enrol_ind := rec_c_unit_ver.ivr_enrol_ind;
554 l_unit_ver_rec.ss_enrol_ind := rec_c_unit_ver.ss_enrol_ind;
555 l_unit_ver_rec.work_load_other := rec_c_unit_ver.work_load_other;
556 l_unit_ver_rec.contact_hrs_lecture := rec_c_unit_ver.contact_hrs_lecture;
557 l_unit_ver_rec.contact_hrs_lab := rec_c_unit_ver.contact_hrs_lab;
558 l_unit_ver_rec.contact_hrs_other := rec_c_unit_ver.contact_hrs_other;
559 l_unit_ver_rec.non_schd_required_hrs := rec_c_unit_ver.non_schd_required_hrs;
560 l_unit_ver_rec.exclude_from_max_cp_limit := rec_c_unit_ver.exclude_from_max_cp_limit;
561 l_unit_ver_rec.record_exclusion_flag := rec_c_unit_ver.record_exclusion_flag;
562 l_unit_ver_rec.ss_display_ind := rec_c_unit_ver.ss_display_ind;
563 l_unit_ver_rec.enrol_load_alt_cd := rec_c_unit_ver.enrol_load_alt_cd;
564 l_unit_ver_rec.offer_load_alt_cd := rec_c_unit_ver.offer_load_alt_cd;
565 l_unit_ver_rec.override_enrollment_max := rec_c_unit_ver.override_enrollment_max;
566 l_unit_ver_rec.repeat_code := rec_c_unit_ver.repeat_code;
567 l_unit_ver_rec.level_code := rec_c_unit_ver.level_code;
568 l_unit_ver_rec.special_permission_ind := rec_c_unit_ver.special_permission_ind;
569 l_unit_ver_rec.rev_account_cd := rec_c_unit_ver.rev_account_cd;
570 l_unit_ver_rec.claimable_hours := rec_c_unit_ver.claimable_hours;
571 l_unit_ver_rec.anon_unit_grading_ind := rec_c_unit_ver.anon_unit_grading_ind;
572 l_unit_ver_rec.anon_assess_grading_ind := rec_c_unit_ver.anon_assess_grading_ind;
573 l_unit_ver_rec.subtitle := rec_c_unit_ver.subtitle;
574 l_unit_ver_rec.subtitle_approved_ind := rec_c_unit_ver.subtitle_approved_ind;
575 l_unit_ver_rec.subtitle_closed_ind := rec_c_unit_ver.subtitle_closed_ind;
576 l_unit_ver_rec.curriculum_id := rec_c_unit_ver.curriculum_id;
577 l_unit_ver_rec.curriculum_description := rec_c_unit_ver.curriculum_description;
578 l_unit_ver_rec.curriculum_closed_ind := rec_c_unit_ver.curriculum_closed_ind;
579 l_unit_ver_rec.auditable_ind := rec_c_unit_ver.auditable_ind;
580 l_unit_ver_rec.audit_permission_ind := rec_c_unit_ver.audit_permission_ind;
581 l_unit_ver_rec.max_auditors_allowed := rec_c_unit_ver.max_auditors_allowed;
582 l_unit_ver_rec.billing_credit_points := rec_c_unit_ver.billing_credit_points;
583 l_unit_ver_rec.interface_id := rec_c_unit_ver.unit_version_int_id;
584 l_unit_ver_rec.ovrd_wkld_val_flag := rec_c_unit_ver.ovrd_wkld_val_flag;
585 l_unit_ver_rec.workload_val_code := rec_c_unit_ver.workload_val_code;
586 l_unit_ver_rec.billing_hrs := rec_c_unit_ver.billing_hrs;
587 END IF;
588 CLOSE c_unit_ver;
589
590 /******************End Unit Version Record ************************/
591
592 /******************Begin Teaching Responsibility **********************/
593
594 l_n_chld_cntr :=1;
595
596 FOR rec_c_teach_resp IN c_teach_resp(rec_c_all_units.unit_cd,rec_c_all_units.version_number)
597 LOOP
598
599 print_heading;
600
601 v_tab_unit_tr(l_n_chld_cntr).unit_cd := rec_c_teach_resp.unit_cd;
602 v_tab_unit_tr(l_n_chld_cntr).version_number := rec_c_teach_resp.version_number;
603 v_tab_unit_tr(l_n_chld_cntr).org_unit_cd := rec_c_teach_resp.org_unit_cd;
604 v_tab_unit_tr(l_n_chld_cntr).percentage := rec_c_teach_resp.percentage;
605 v_tab_unit_tr(l_n_chld_cntr).interface_id := rec_c_teach_resp.teach_resp_int_id;
606
607 l_n_chld_cntr := l_n_chld_cntr+1;
608
609 END LOOP;
610
611 /******************End Teaching Responsibility **********************/
612
613 /******************Begin Unit Disciplines **********************/
614
615 l_n_chld_cntr :=1;
616
617 FOR rec_c_unit_disp IN c_unit_disp(rec_c_all_units.unit_cd,rec_c_all_units.version_number)
618 LOOP
619
620 print_heading;
621
622 v_tab_unit_dscp(l_n_chld_cntr).unit_cd := rec_c_unit_disp.unit_cd;
623 v_tab_unit_dscp(l_n_chld_cntr).version_number := rec_c_unit_disp.version_number;
624 v_tab_unit_dscp(l_n_chld_cntr).discipline_group_cd := rec_c_unit_disp.discipline_group_cd;
625 v_tab_unit_dscp(l_n_chld_cntr).percentage := rec_c_unit_disp.percentage;
626 v_tab_unit_dscp(l_n_chld_cntr).interface_id := rec_c_unit_disp.unit_discip_int_id;
627
628 l_n_chld_cntr := l_n_chld_cntr+1;
629
630 END LOOP;
631
632 /******************End Unit Disciplines **********************/
633
634 /******************Begin Grading Sch **********************/
635
636 l_n_chld_cntr :=1;
637
638 FOR rec_c_grd_sch IN c_grd_sch(rec_c_all_units.unit_cd,rec_c_all_units.version_number)
639 LOOP
640
641 print_heading;
642
643 v_tab_unit_gs(l_n_chld_cntr).unit_cd := rec_c_grd_sch.unit_cd;
644 v_tab_unit_gs(l_n_chld_cntr).version_number := rec_c_grd_sch.unit_version_number;
645 v_tab_unit_gs(l_n_chld_cntr).grading_schema_code := rec_c_grd_sch.grading_schema_code;
646 v_tab_unit_gs(l_n_chld_cntr).grd_schm_version_number := rec_c_grd_sch.grd_schm_version_number;
647 v_tab_unit_gs(l_n_chld_cntr).default_flag := rec_c_grd_sch.default_flag;
648 v_tab_unit_gs(l_n_chld_cntr).interface_id := rec_c_grd_sch.uv_grd_schm_int_id;
649
650 l_n_chld_cntr := l_n_chld_cntr+1;
651
652 END LOOP;
653
654 /******************End Grading Sch **********************/
655
656 /******************Begin Unit Section **********************/
657
658 l_n_chld_cntr :=1;
659
660 FOR rec_c_unit_sec IN c_unit_sec(rec_c_all_units.unit_cd,rec_c_all_units.version_number)
661 LOOP
662
663 print_heading;
664
665 v_tab_usec(l_n_chld_cntr).unit_cd := rec_c_unit_sec.unit_cd;
666 v_tab_usec(l_n_chld_cntr).version_number := rec_c_unit_sec.version_number;
667 v_tab_usec(l_n_chld_cntr).teach_cal_alternate_code := rec_c_unit_sec.teach_cal_alternate_code;
668 v_tab_usec(l_n_chld_cntr).location_cd := rec_c_unit_sec.location_cd;
669 v_tab_usec(l_n_chld_cntr).unit_class := rec_c_unit_sec.unit_class;
670 v_tab_usec(l_n_chld_cntr).ivrs_available_ind := rec_c_unit_sec.ivrs_available_ind;
671 v_tab_usec(l_n_chld_cntr).call_number := rec_c_unit_sec.call_number;
672 v_tab_usec(l_n_chld_cntr).unit_section_status := rec_c_unit_sec.unit_section_status;
673 v_tab_usec(l_n_chld_cntr).unit_section_start_date := rec_c_unit_sec.unit_section_start_date;
674 v_tab_usec(l_n_chld_cntr).unit_section_end_date := rec_c_unit_sec.unit_section_end_date;
675 v_tab_usec(l_n_chld_cntr).offered_ind := rec_c_unit_sec.offered_ind;
676 v_tab_usec(l_n_chld_cntr).state_financial_aid := rec_c_unit_sec.state_financial_aid;
677 v_tab_usec(l_n_chld_cntr).grading_schema_prcdnce_ind := rec_c_unit_sec.grading_schema_prcdnce_ind;
678 v_tab_usec(l_n_chld_cntr).federal_financial_aid := rec_c_unit_sec.federal_financial_aid;
679 v_tab_usec(l_n_chld_cntr).unit_quota := rec_c_unit_sec.unit_quota;
680 v_tab_usec(l_n_chld_cntr).unit_quota_reserved_places := rec_c_unit_sec.unit_quota_reserved_places;
681 v_tab_usec(l_n_chld_cntr).institutional_financial_aid := rec_c_unit_sec.institutional_financial_aid;
682 v_tab_usec(l_n_chld_cntr).grading_schema_cd := rec_c_unit_sec.grading_schema_cd;
683 v_tab_usec(l_n_chld_cntr).gs_version_number := rec_c_unit_sec.gs_version_number;
684 v_tab_usec(l_n_chld_cntr).unit_contact_number := rec_c_unit_sec.unit_contact_number;
685 v_tab_usec(l_n_chld_cntr).ss_enrol_ind := rec_c_unit_sec.ss_enrol_ind;
686 v_tab_usec(l_n_chld_cntr).owner_org_unit_cd := rec_c_unit_sec.owner_org_unit_cd;
687 v_tab_usec(l_n_chld_cntr).attendance_required_ind := rec_c_unit_sec.attendance_required_ind;
688 v_tab_usec(l_n_chld_cntr).reserved_seating_allowed := rec_c_unit_sec.reserved_seating_allowed;
689 v_tab_usec(l_n_chld_cntr).special_permission_ind := rec_c_unit_sec.special_permission_ind;
690 v_tab_usec(l_n_chld_cntr).ss_display_ind := rec_c_unit_sec.ss_display_ind;
691 v_tab_usec(l_n_chld_cntr).rev_account_cd := rec_c_unit_sec.rev_account_cd;
692 v_tab_usec(l_n_chld_cntr).anon_unit_grading_ind := rec_c_unit_sec.anon_unit_grading_ind;
693 v_tab_usec(l_n_chld_cntr).anon_assess_grading_ind := rec_c_unit_sec.anon_assess_grading_ind;
694 v_tab_usec(l_n_chld_cntr).non_std_usec_ind := rec_c_unit_sec.non_std_usec_ind;
695 v_tab_usec(l_n_chld_cntr).auditable_ind := rec_c_unit_sec.auditable_ind;
696 v_tab_usec(l_n_chld_cntr).audit_permission_ind := rec_c_unit_sec.audit_permission_ind;
697 v_tab_usec(l_n_chld_cntr).waitlist_allowed := rec_c_unit_sec.waitlist_allowed;
698 v_tab_usec(l_n_chld_cntr).max_students_per_waitlist := rec_c_unit_sec.max_students_per_waitlist;
699 v_tab_usec(l_n_chld_cntr).minimum_credit_points := rec_c_unit_sec.minimum_credit_points;
700 v_tab_usec(l_n_chld_cntr).maximum_credit_points := rec_c_unit_sec.maximum_credit_points;
701 v_tab_usec(l_n_chld_cntr).variable_increment := rec_c_unit_sec.variable_increment;
702 v_tab_usec(l_n_chld_cntr).lecture_credit_points := rec_c_unit_sec.lecture_credit_points;
703 v_tab_usec(l_n_chld_cntr).lab_credit_points := rec_c_unit_sec.lab_credit_points;
704 v_tab_usec(l_n_chld_cntr).other_credit_points := rec_c_unit_sec.other_credit_points;
705 v_tab_usec(l_n_chld_cntr).clock_hours := rec_c_unit_sec.clock_hours;
706 v_tab_usec(l_n_chld_cntr).work_load_cp_lecture := rec_c_unit_sec.work_load_cp_lecture;
707 v_tab_usec(l_n_chld_cntr).work_load_cp_lab := rec_c_unit_sec.work_load_cp_lab;
708 v_tab_usec(l_n_chld_cntr).continuing_education_units := rec_c_unit_sec.continuing_education_units;
709 v_tab_usec(l_n_chld_cntr).work_load_other := rec_c_unit_sec.work_load_other;
710 v_tab_usec(l_n_chld_cntr).contact_hrs_lecture := rec_c_unit_sec.contact_hrs_lecture;
711 v_tab_usec(l_n_chld_cntr).contact_hrs_lab := rec_c_unit_sec.contact_hrs_lab;
712 v_tab_usec(l_n_chld_cntr).contact_hrs_other := rec_c_unit_sec.contact_hrs_other;
713 v_tab_usec(l_n_chld_cntr).non_schd_required_hrs := rec_c_unit_sec.non_schd_required_hrs;
714 v_tab_usec(l_n_chld_cntr).exclude_from_max_cp_limit := rec_c_unit_sec.exclude_from_max_cp_limit;
715 v_tab_usec(l_n_chld_cntr).claimable_hours := rec_c_unit_sec.claimable_hours;
716 v_tab_usec(l_n_chld_cntr).achievable_credit_points := rec_c_unit_sec.achievable_credit_points;
717 v_tab_usec(l_n_chld_cntr).enrolled_credit_points := rec_c_unit_sec.enrolled_credit_points;
718 v_tab_usec(l_n_chld_cntr).billing_credit_points := rec_c_unit_sec.billing_credit_points;
719 v_tab_usec(l_n_chld_cntr).reference_subtitle := rec_c_unit_sec.reference_subtitle;
720 v_tab_usec(l_n_chld_cntr).reference_short_title := rec_c_unit_sec.reference_short_title;
721 v_tab_usec(l_n_chld_cntr).reference_subtitle_mod_flag := rec_c_unit_sec.reference_subtitle_mod_flag;
722 v_tab_usec(l_n_chld_cntr).reference_class_sch_excl_flag := rec_c_unit_sec.reference_class_sch_excl_flag;
723 v_tab_usec(l_n_chld_cntr).reference_rec_exclusion_flag := rec_c_unit_sec.reference_rec_exclusion_flag;
724 v_tab_usec(l_n_chld_cntr).reference_title := rec_c_unit_sec.reference_title;
725 v_tab_usec(l_n_chld_cntr).reference_attribute_category := rec_c_unit_sec.reference_attribute_category;
726 v_tab_usec(l_n_chld_cntr).reference_attribute1 := rec_c_unit_sec.reference_attribute1;
727 v_tab_usec(l_n_chld_cntr).reference_attribute2 := rec_c_unit_sec.reference_attribute2;
728 v_tab_usec(l_n_chld_cntr).reference_attribute3 := rec_c_unit_sec.reference_attribute3;
729 v_tab_usec(l_n_chld_cntr).reference_attribute4 := rec_c_unit_sec.reference_attribute4;
730 v_tab_usec(l_n_chld_cntr).reference_attribute5 := rec_c_unit_sec.reference_attribute5;
731 v_tab_usec(l_n_chld_cntr).reference_attribute6 := rec_c_unit_sec.reference_attribute6;
732 v_tab_usec(l_n_chld_cntr).reference_attribute7 := rec_c_unit_sec.reference_attribute7;
733 v_tab_usec(l_n_chld_cntr).reference_attribute8 := rec_c_unit_sec.reference_attribute8;
734 v_tab_usec(l_n_chld_cntr).reference_attribute9 := rec_c_unit_sec.reference_attribute9;
735 v_tab_usec(l_n_chld_cntr).reference_attribute10 := rec_c_unit_sec.reference_attribute10;
736 v_tab_usec(l_n_chld_cntr).reference_attribute11 := rec_c_unit_sec.reference_attribute11;
737 v_tab_usec(l_n_chld_cntr).reference_attribute12 := rec_c_unit_sec.reference_attribute12;
738 v_tab_usec(l_n_chld_cntr).reference_attribute13 := rec_c_unit_sec.reference_attribute13;
739 v_tab_usec(l_n_chld_cntr).reference_attribute14 := rec_c_unit_sec.reference_attribute14;
740 v_tab_usec(l_n_chld_cntr).reference_attribute15 := rec_c_unit_sec.reference_attribute15;
741 v_tab_usec(l_n_chld_cntr).reference_attribute16 := rec_c_unit_sec.reference_attribute16;
742 v_tab_usec(l_n_chld_cntr).reference_attribute17 := rec_c_unit_sec.reference_attribute17;
743 v_tab_usec(l_n_chld_cntr).reference_attribute18 := rec_c_unit_sec.reference_attribute18;
744 v_tab_usec(l_n_chld_cntr).reference_attribute19 := rec_c_unit_sec.reference_attribute19;
745 v_tab_usec(l_n_chld_cntr).reference_attribute20 := rec_c_unit_sec.reference_attribute20;
746 v_tab_usec(l_n_chld_cntr).enrollment_expected := rec_c_unit_sec.enrollment_expected;
747 v_tab_usec(l_n_chld_cntr).enrollment_minimum := rec_c_unit_sec.enrollment_minimum;
748 v_tab_usec(l_n_chld_cntr).enrollment_maximum := rec_c_unit_sec.enrollment_maximum;
749 v_tab_usec(l_n_chld_cntr).advance_maximum := rec_c_unit_sec.advance_maximum;
750 v_tab_usec(l_n_chld_cntr).usec_waitlist_allowed := rec_c_unit_sec.usec_waitlist_allowed;
751 v_tab_usec(l_n_chld_cntr).usec_max_students_per_waitlist := rec_c_unit_sec.usec_max_students_per_waitlist;
752 v_tab_usec(l_n_chld_cntr).override_enrollment_maximum := rec_c_unit_sec.override_enrollment_maximum;
753 v_tab_usec(l_n_chld_cntr).max_auditors_allowed := rec_c_unit_sec.max_auditors_allowed;
754 v_tab_usec(l_n_chld_cntr).interface_id := rec_c_unit_sec.unit_section_int_id;
755 v_tab_usec(l_n_chld_cntr).not_multiple_section_flag := rec_c_unit_sec.not_multiple_section_flag;
756 v_tab_usec(l_n_chld_cntr).sup_unit_cd := rec_c_unit_sec.sup_unit_cd;
757 v_tab_usec(l_n_chld_cntr).sup_version_number := rec_c_unit_sec.sup_version_number;
758 v_tab_usec(l_n_chld_cntr).sup_teach_cal_alternate_code := rec_c_unit_sec.sup_teach_cal_alternate_code;
759 v_tab_usec(l_n_chld_cntr).sup_location_cd := rec_c_unit_sec.sup_location_cd;
760 v_tab_usec(l_n_chld_cntr).sup_unit_class := rec_c_unit_sec.sup_unit_class;
761 v_tab_usec(l_n_chld_cntr).default_enroll_flag := rec_c_unit_sec.default_enroll_flag;
762 v_tab_usec(l_n_chld_cntr).billing_hrs := rec_c_unit_sec.billing_hrs;
763
764
765 l_n_chld_cntr := l_n_chld_cntr+1;
766
767 END LOOP;
768
769 /******************End Unit Section **********************/
770
771 /******************Begin Unit Section Grading Sch **********************/
772
773 l_n_chld_cntr :=1;
774
775 FOR rec_c_us_grd_sch IN c_us_grd_sch(rec_c_all_units.unit_cd,rec_c_all_units.version_number)
776 LOOP
777
778 print_heading;
779
780 v_tab_usec_gs(l_n_chld_cntr).unit_cd := rec_c_us_grd_sch.unit_cd;
781 v_tab_usec_gs(l_n_chld_cntr).version_number := rec_c_us_grd_sch.version_number;
782 v_tab_usec_gs(l_n_chld_cntr).teach_cal_alternate_code := rec_c_us_grd_sch.teach_cal_alternate_code;
783 v_tab_usec_gs(l_n_chld_cntr).location_cd := rec_c_us_grd_sch.location_cd;
784 v_tab_usec_gs(l_n_chld_cntr).unit_class := rec_c_us_grd_sch.unit_class;
785 v_tab_usec_gs(l_n_chld_cntr).grading_schema_code := rec_c_us_grd_sch.grading_schema_code;
786 v_tab_usec_gs(l_n_chld_cntr).grd_schm_version_number := rec_c_us_grd_sch.grd_schm_version_number;
787 v_tab_usec_gs(l_n_chld_cntr).default_flag := rec_c_us_grd_sch.default_flag;
788 v_tab_usec_gs(l_n_chld_cntr).interface_id := rec_c_us_grd_sch.usec_grd_schm_int_id;
789
790 l_n_chld_cntr := l_n_chld_cntr+1;
791
792 END LOOP;
793
794 /******************End Unit Section Grading Sch **********************/
795
796 /******************Begin Unit Section Occurrences **********************/
797
798 l_n_chld_cntr :=1;
799
800 -- As part of bug#2972950 for PSP Scheduling Enhancements TD assigning values to the newly
801 -- added fields preferred_region_code and no_set_day_ind of the PL/SQL tables v_tab_uso.
802 FOR rec_c_uso IN c_uso(rec_c_all_units.unit_cd,rec_c_all_units.version_number)
803 LOOP
804
805 print_heading;
806
807 v_tab_uso(l_n_chld_cntr).unit_cd := rec_c_uso.unit_cd;
808 v_tab_uso(l_n_chld_cntr).version_number := rec_c_uso.version_number;
809 v_tab_uso(l_n_chld_cntr).teach_cal_alternate_code := rec_c_uso.teach_cal_alternate_code;
810 v_tab_uso(l_n_chld_cntr).location_cd := rec_c_uso.location_cd;
811 v_tab_uso(l_n_chld_cntr).unit_class := rec_c_uso.unit_class;
812 v_tab_uso(l_n_chld_cntr).occurrence_identifier := rec_c_uso.occurrence_identifier;
813 v_tab_uso(l_n_chld_cntr).to_be_announced := rec_c_uso.to_be_announced;
814 v_tab_uso(l_n_chld_cntr).monday := rec_c_uso.monday;
815 v_tab_uso(l_n_chld_cntr).tuesday := rec_c_uso.tuesday;
816 v_tab_uso(l_n_chld_cntr).wednesday := rec_c_uso.wednesday;
817 v_tab_uso(l_n_chld_cntr).thursday := rec_c_uso.thursday;
818 v_tab_uso(l_n_chld_cntr).friday := rec_c_uso.friday;
819 v_tab_uso(l_n_chld_cntr).saturday := rec_c_uso.saturday;
820 v_tab_uso(l_n_chld_cntr).sunday := rec_c_uso.sunday;
821 v_tab_uso(l_n_chld_cntr).start_date := rec_c_uso.start_date;
822 v_tab_uso(l_n_chld_cntr).end_date := rec_c_uso.end_date;
823 v_tab_uso(l_n_chld_cntr).start_time := rec_c_uso.start_time;
824 v_tab_uso(l_n_chld_cntr).end_time := rec_c_uso.end_time;
825 v_tab_uso(l_n_chld_cntr).building_code := rec_c_uso.building_code;
826 v_tab_uso(l_n_chld_cntr).room_code := rec_c_uso.room_code;
827 v_tab_uso(l_n_chld_cntr).dedicated_building_code := rec_c_uso.dedicated_building_code;
828 v_tab_uso(l_n_chld_cntr).dedicated_room_code := rec_c_uso.dedicated_room_code;
829 v_tab_uso(l_n_chld_cntr).preferred_building_code := rec_c_uso.preferred_building_code;
830 v_tab_uso(l_n_chld_cntr).preferred_room_code := rec_c_uso.preferred_room_code;
831 v_tab_uso(l_n_chld_cntr).no_set_day_ind := rec_c_uso.no_set_day_ind;
832 v_tab_uso(l_n_chld_cntr).preferred_region_code := rec_c_uso.preferred_region_code;
833 v_tab_uso(l_n_chld_cntr).attribute_category := rec_c_uso.attribute_category;
834 v_tab_uso(l_n_chld_cntr).attribute1 := rec_c_uso.attribute1;
835 v_tab_uso(l_n_chld_cntr).attribute2 := rec_c_uso.attribute2;
836 v_tab_uso(l_n_chld_cntr).attribute3 := rec_c_uso.attribute3;
837 v_tab_uso(l_n_chld_cntr).attribute4 := rec_c_uso.attribute4;
838 v_tab_uso(l_n_chld_cntr).attribute5 := rec_c_uso.attribute5;
839 v_tab_uso(l_n_chld_cntr).attribute6 := rec_c_uso.attribute6;
840 v_tab_uso(l_n_chld_cntr).attribute7 := rec_c_uso.attribute7;
841 v_tab_uso(l_n_chld_cntr).attribute8 := rec_c_uso.attribute8;
842 v_tab_uso(l_n_chld_cntr).attribute9 := rec_c_uso.attribute9;
843 v_tab_uso(l_n_chld_cntr).attribute10 := rec_c_uso.attribute10;
844 v_tab_uso(l_n_chld_cntr).attribute11 := rec_c_uso.attribute11;
845 v_tab_uso(l_n_chld_cntr).attribute12 := rec_c_uso.attribute12;
846 v_tab_uso(l_n_chld_cntr).attribute13 := rec_c_uso.attribute13;
847 v_tab_uso(l_n_chld_cntr).attribute14 := rec_c_uso.attribute14;
848 v_tab_uso(l_n_chld_cntr).attribute15 := rec_c_uso.attribute15;
849 v_tab_uso(l_n_chld_cntr).attribute16 := rec_c_uso.attribute16;
850 v_tab_uso(l_n_chld_cntr).attribute17 := rec_c_uso.attribute17;
851 v_tab_uso(l_n_chld_cntr).attribute18 := rec_c_uso.attribute18;
852 v_tab_uso(l_n_chld_cntr).attribute19 := rec_c_uso.attribute19;
853 v_tab_uso(l_n_chld_cntr).attribute20 := rec_c_uso.attribute20;
854 v_tab_uso(l_n_chld_cntr).interface_id := rec_c_uso.usec_occur_int_id;
855
856 l_n_chld_cntr := l_n_chld_cntr+1;
857
858 END LOOP;
859
860 /******************End Unit Section Occurrences **********************/
861
862 /******************Begin Unit Reference Codes **********************/
863
864 l_n_chld_cntr :=1;
865
866 -- As part of bug#2972950 for PSP Scheduling Enhancements TD assigning values
867 -- to the newly added fields uso_start_date,uso_end_date and no_set_day_ind
868 -- of the PL/SQL tables v_tab_unit_ref.
869 FOR rec_c_unit_ref_cd IN c_unit_ref_cd(rec_c_all_units.unit_cd,rec_c_all_units.version_number)
870 LOOP
871
872 print_heading;
873
874 v_tab_unit_ref(l_n_chld_cntr).production_uso_id := rec_c_unit_ref_cd.production_uso_id;
875 v_tab_unit_ref(l_n_chld_cntr).unit_cd := rec_c_unit_ref_cd.unit_cd;
876 v_tab_unit_ref(l_n_chld_cntr).version_number := rec_c_unit_ref_cd.version_number;
877 v_tab_unit_ref(l_n_chld_cntr).data_type := rec_c_unit_ref_cd.data_type;
878 v_tab_unit_ref(l_n_chld_cntr).teach_cal_alternate_code := rec_c_unit_ref_cd.teach_cal_alternate_code;
879 v_tab_unit_ref(l_n_chld_cntr).location_cd := rec_c_unit_ref_cd.location_cd;
880 v_tab_unit_ref(l_n_chld_cntr).unit_class := rec_c_unit_ref_cd.unit_class;
881 v_tab_unit_ref(l_n_chld_cntr).occurrence_identifier := rec_c_unit_ref_cd.occurrence_identifier;
882 v_tab_unit_ref(l_n_chld_cntr).reference_cd_type := rec_c_unit_ref_cd.reference_cd_type;
883 v_tab_unit_ref(l_n_chld_cntr).reference_cd := rec_c_unit_ref_cd.reference_cd;
884 v_tab_unit_ref(l_n_chld_cntr).description := rec_c_unit_ref_cd.description;
885 v_tab_unit_ref(l_n_chld_cntr).gen_ref_flag := rec_c_unit_ref_cd.gen_ref_flag;
886 v_tab_unit_ref(l_n_chld_cntr).interface_id := rec_c_unit_ref_cd.unit_reference_int_id;
887
888 l_n_chld_cntr := l_n_chld_cntr+1;
889
890 END LOOP;
891
892 /******************End Unit Reference Codes **********************/
893
894 /******************Begin Instructor **********************/
895
896 l_n_chld_cntr :=1;
897 FOR rec_ins IN c_ins(p_n_batch_id, rec_c_all_units.unit_cd, rec_c_all_units.version_number)
898 LOOP
899
900 print_heading;
901 v_tab_ins(l_n_chld_cntr).instructor_person_number := rec_ins.instructor_person_number;
902 v_tab_ins(l_n_chld_cntr).production_uso_id := rec_ins.production_uso_id;
903 v_tab_ins(l_n_chld_cntr).unit_cd := rec_ins.unit_cd;
904 v_tab_ins(l_n_chld_cntr).version_number := rec_ins.version_number;
905 v_tab_ins(l_n_chld_cntr).teach_cal_alternate_code := rec_ins.teach_cal_alternate_code;
906 v_tab_ins(l_n_chld_cntr).location_cd := rec_ins.location_cd;
907 v_tab_ins(l_n_chld_cntr).unit_class := rec_ins.unit_class;
908 v_tab_ins(l_n_chld_cntr).occurrence_identifier := rec_ins.occurrence_identifier;
909 v_tab_ins(l_n_chld_cntr).confirmed_flag := rec_ins.confirmed_flag;
910 v_tab_ins(l_n_chld_cntr).wl_percentage_allocation := rec_ins.wl_percentage_allocation;
911 v_tab_ins(l_n_chld_cntr).instructional_load_lecture := rec_ins.instructional_load_lecture;
912 v_tab_ins(l_n_chld_cntr).instructional_load_laboratory := rec_ins.instructional_load_laboratory;
913 v_tab_ins(l_n_chld_cntr).instructional_load_other := rec_ins.instructional_load_other;
914 v_tab_ins(l_n_chld_cntr).lead_instructor_flag := rec_ins.lead_instructor_flag;
915 v_tab_ins(l_n_chld_cntr).interface_id := rec_ins.uso_instructor_int_id;
916
917 l_n_chld_cntr := l_n_chld_cntr+1;
918
919 END LOOP;
920
921 /******************End Instructor **********************/
922
923
924
925 /* Call to Public API */
926
927 igs_ps_unit_lgcy_pvt.create_unit
928 (
929 p_api_version => 1.0,
930 p_init_msg_list => FND_API.G_TRUE,
931 p_commit => FND_API.G_TRUE,
932 p_unit_ver_rec => l_unit_ver_rec,
933 p_unit_tr_tbl => v_tab_unit_tr,
934 p_unit_dscp_tbl => v_tab_unit_dscp,
935 p_unit_gs_tbl => v_tab_unit_gs,
936 p_usec_tbl => v_tab_usec,
937 p_usec_gs_tbl => v_tab_usec_gs,
938 p_uso_tbl => v_tab_uso,
939 p_unit_ref_tbl => v_tab_unit_ref,
940 p_uso_ins_tbl => v_tab_ins,
941 x_return_status => l_c_return_status,
942 x_msg_count => l_n_msg_count,
943 x_msg_data => l_c_msg_data
944 );
945 /* -----------------------------*/
946
947 /* Error out if none of the tables have data */
948
949 IF l_n_msg_count = 1 THEN
950 IF get_msg_from_stack(l_n_msg_count) = 'IGS_PS_LGCY_DATA_NOT_PASSED' THEN
951 get_message('IGS_PS_LGCY_DATA_NOT_PASSED',l_n_msg_num,l_c_msg_txt);
952 log_file(l_c_msg_txt,'L');
953 retcode := 2;
954 RETURN;
955 END IF;
956 END IF;
957
958 IF l_ret_status AND l_c_return_status = 'S' THEN
959 l_ret_status := FALSE;
960 END IF;
961
962 /* -----------------------------------------------*/
963
964 /******************Begin Unit Version Log and Error**********************/
965
966 IF l_unit_ver_rec.status IN ('S','W','E') THEN
967 row_head('PS_UNIT_VERSION',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
968 END IF;
969
970 IF l_unit_ver_rec.status = 'S' AND l_c_return_status = 'S' THEN
971
972 /* Update the interface table */
973
974 UPDATE igs_ps_lgcy_uv_int
975 SET import_status = 'I'
976 WHERE batch_id=p_n_batch_id
977 AND unit_version_int_id = l_unit_ver_rec.interface_id;
978
979 /* Write into log file */
980
981 log_file(RPAD(l_unit_ver_rec.interface_id,31,' '),'P');
982 log_file(RPAD(l_c_import_desc,10,' '),'P');
983 print_char(1,' ');
984 log_file(l_c_succ_desc,'L');
985
986 ELSIF l_unit_ver_rec.status IN ('W','E') THEN
987
988 /* Update the interface table */
989
990 UPDATE igs_ps_lgcy_uv_int
991 SET import_status = l_unit_ver_rec.status
992 WHERE batch_id=p_n_batch_id
993 AND unit_version_int_id = l_unit_ver_rec.interface_id;
994
995 /* Write into log file */
996
997 FOR l_curr_num IN l_unit_ver_rec.msg_from..l_unit_ver_rec.msg_to
998 LOOP
999 l_c_msg_name := get_msg_from_stack(l_curr_num);
1000 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1001 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1002
1003 INSERT INTO igs_ps_lgcy_err_int
1004 (
1005 err_message_id,
1006 int_table_code,
1007 int_table_id,
1008 message_num,
1009 message_text,
1010 created_by,
1011 creation_date,
1012 last_updated_by,
1013 last_update_date,
1014 last_update_login,
1015 request_id,
1016 program_application_id,
1017 program_id,
1018 program_update_date)
1019 VALUES
1020 (
1021 igs_ps_lgcy_err_int_s.nextval,
1022 igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_VERSION','LEGACY_PS_REC_TABLES'),
1023 l_unit_ver_rec.interface_id,
1024 l_n_msg_num,
1025 l_c_msg_txt,
1026 NVL(fnd_global.user_id,-1),
1027 SYSDATE,
1028 NVL(fnd_global.user_id,-1),
1029 SYSDATE,
1030 NVL(fnd_global.login_id,-1),
1031 l_n_request_id,
1032 l_n_prog_appl_id,
1033 l_n_prog_id,
1034 l_d_prog_upd_dt
1035 );
1036
1037 /* Write into log file */
1038
1039 log_file(RPAD(l_unit_ver_rec.interface_id,16,' '),'P');
1040 log_file(RPAD(l_n_msg_num,15,' '),'P');
1041 IF l_n_msg_num IS NULL THEN
1042 print_char(15,' ');
1043 END IF;
1044 log_file(RPAD(igs_ps_validate_lgcy_pkg.get_lkup_meaning(l_unit_ver_rec.status,'LEGACY_STATUS'),10,' '),'P');
1045 print_char(1,' ');
1046 log_file(l_c_msg_txt,'L');
1047
1048 END LOOP;
1049 END IF;
1050 l_unit_ver_rec.status := NULL;
1051
1052 /******************End Unit Version Log and Error***********************/
1053
1054 /******************Begin Teaching Resp Log and Error**********************/
1055 l_b_print_row_heading := TRUE;
1056 IF v_tab_unit_tr.EXISTS(1) THEN
1057 IF l_b_print_row_heading AND l_c_return_status = 'S' THEN
1058 row_head('PS_TEACH_RESP',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1059 l_b_print_row_heading := FALSE ;
1060 END IF;
1061
1062 FOR i IN 1..v_tab_unit_tr.LAST
1063 LOOP
1064
1065 IF v_tab_unit_tr(i).status = 'S' AND l_c_return_status = 'S' THEN
1066
1067 /* Update the interface table */
1068
1069 UPDATE igs_ps_lgcy_tr_int
1070 SET import_status = 'I'
1071 WHERE batch_id=p_n_batch_id
1072 AND teach_resp_int_id = v_tab_unit_tr(i).interface_id;
1073
1074 /* Write into log file */
1075
1076 log_file(RPAD(v_tab_unit_tr(i).interface_id,31,' '),'P');
1077 log_file(RPAD(l_c_import_desc,10,' '),'P');
1078 print_char(1,' ');
1079 log_file(l_c_succ_desc,'L');
1080
1081 ELSIF v_tab_unit_tr(i).status IN ('W','E') THEN
1082
1083 IF l_b_print_row_heading THEN
1084 row_head('PS_TEACH_RESP',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1085 l_b_print_row_heading := FALSE ;
1086 END IF;
1087
1088 /* Update the interface table */
1089
1090 UPDATE igs_ps_lgcy_tr_int
1091 SET import_status = v_tab_unit_tr(i).status
1092 WHERE batch_id=p_n_batch_id
1093 AND teach_resp_int_id = v_tab_unit_tr(i).interface_id;
1094
1095 /* Write into log file */
1096
1097 FOR l_curr_num IN v_tab_unit_tr(i).msg_from..v_tab_unit_tr(i).msg_to
1098 LOOP
1099
1100 l_c_msg_name := get_msg_from_stack(l_curr_num);
1101 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1102 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1103
1104 INSERT INTO igs_ps_lgcy_err_int
1105 (
1106 err_message_id,
1107 int_table_code,
1108 int_table_id,
1109 message_num,
1110 message_text,
1111 created_by,
1112 creation_date,
1113 last_updated_by,
1114 last_update_date,
1115 last_update_login,
1116 request_id,
1117 program_application_id,
1118 program_id,
1119 program_update_date)
1120 VALUES
1121 (
1122 igs_ps_lgcy_err_int_s.nextval,
1123 igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_TEACH_RESP','LEGACY_PS_REC_TABLES'),
1124 v_tab_unit_tr(i).interface_id,
1125 l_n_msg_num,
1126 l_c_msg_txt,
1127 NVL(fnd_global.user_id,-1),
1128 SYSDATE,
1129 NVL(fnd_global.user_id,-1),
1130 SYSDATE,
1131 NVL(fnd_global.login_id,-1),
1132 l_n_request_id,
1133 l_n_prog_appl_id,
1134 l_n_prog_id,
1135 l_d_prog_upd_dt
1136 );
1137
1138 /* Write into log file */
1139
1140 log_file(RPAD(v_tab_unit_tr(i).interface_id,16,' '),'P');
1141 log_file(RPAD(l_n_msg_num,15,' '),'P');
1142 IF l_n_msg_num IS NULL THEN
1143 print_char(15,' ');
1144 END IF;
1145 log_file(RPAD(igs_ps_validate_lgcy_pkg.get_lkup_meaning(v_tab_unit_tr(i).status,'LEGACY_STATUS'),10,' '),'P');
1146 print_char(1,' ');
1147 log_file(l_c_msg_txt,'L');
1148
1149 END LOOP; /* Messages loop */
1150 END IF;
1151 END LOOP; /* Teaching Responsibility Loop */
1152 v_tab_unit_tr.DELETE;
1153 END IF;
1154
1155 /******************End Teaching Resp Log and Error***********************/
1156
1157 /******************Begin Unit Disciplines Log and Error**********************/
1158
1159 l_b_print_row_heading := TRUE;
1160 IF v_tab_unit_dscp.EXISTS(1) THEN
1161
1162 IF l_b_print_row_heading AND l_c_return_status = 'S' THEN
1163 row_head('PS_UNIT_DISCP',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1164 l_b_print_row_heading := FALSE ;
1165 END IF;
1166
1167 FOR i IN 1.. v_tab_unit_dscp.LAST
1168 LOOP
1169
1170 IF v_tab_unit_dscp(i).status = 'S' AND l_c_return_status = 'S' THEN
1171
1172 /* Update the interface table */
1173
1174 UPDATE igs_ps_lgcy_ud_int
1175 SET import_status = 'I'
1176 WHERE batch_id=p_n_batch_id
1177 AND unit_discip_int_id = v_tab_unit_dscp(i).interface_id;
1178
1179 /* Write into log file */
1180
1181 log_file(RPAD(v_tab_unit_dscp(i).interface_id,31,' '),'P');
1182 log_file(RPAD(l_c_import_desc,10,' '),'P');
1183 print_char(1,' ');
1184 log_file(l_c_succ_desc,'L');
1185
1186 ELSIF v_tab_unit_dscp(i).status IN ('W','E') THEN
1187
1188 IF l_b_print_row_heading THEN
1189 row_head('PS_UNIT_DISCP',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1190 l_b_print_row_heading := FALSE ;
1191 END IF;
1192
1193 /* Update the interface table */
1194
1195 UPDATE igs_ps_lgcy_ud_int
1196 SET import_status = v_tab_unit_dscp(i).status
1197 WHERE batch_id=p_n_batch_id
1198 AND unit_discip_int_id = v_tab_unit_dscp(i).interface_id;
1199
1200 /* Write into log file */
1201
1202 FOR l_curr_num IN v_tab_unit_dscp(i).msg_from..v_tab_unit_dscp(i).msg_to
1203 LOOP
1204 l_c_msg_name := get_msg_from_stack(l_curr_num);
1205 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1206 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1207
1208 INSERT INTO igs_ps_lgcy_err_int
1209 (
1210 err_message_id,
1211 int_table_code,
1212 int_table_id,
1213 message_num,
1214 message_text,
1215 created_by,
1216 creation_date,
1217 last_updated_by,
1218 last_update_date,
1219 last_update_login,
1220 request_id,
1221 program_application_id,
1222 program_id,
1223 program_update_date)
1224 VALUES
1225 (
1226 igs_ps_lgcy_err_int_s.nextval,
1227 igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_DISCP','LEGACY_PS_REC_TABLES'),
1228 v_tab_unit_dscp(i).interface_id,
1229 l_n_msg_num,
1230 l_c_msg_txt,
1231 NVL(fnd_global.user_id,-1),
1232 SYSDATE,
1233 NVL(fnd_global.user_id,-1),
1234 SYSDATE,
1235 NVL(fnd_global.login_id,-1),
1236 l_n_request_id,
1237 l_n_prog_appl_id,
1238 l_n_prog_id,
1239 l_d_prog_upd_dt
1240 );
1241
1242 /* Write into log file */
1243
1244 log_file(RPAD(v_tab_unit_dscp(i).interface_id,16,' '),'P');
1245 log_file(RPAD(l_n_msg_num,15,' '),'P');
1246 IF l_n_msg_num IS NULL THEN
1247 print_char(15,' ');
1248 END IF;
1249 log_file(RPAD(igs_ps_validate_lgcy_pkg.get_lkup_meaning(v_tab_unit_dscp(i).status,'LEGACY_STATUS'),10,' '),'P');
1250 print_char(1,' ');
1251 log_file(l_c_msg_txt,'L');
1252
1253 END LOOP; /* Messages loop */
1254 END IF;
1255 END LOOP; /* Unit Disciplines Loop */
1256 v_tab_unit_dscp.DELETE;
1257 END IF;
1258
1259 /******************End Unit Disciplines Log and Error***********************/
1260
1261 /******************Begin Grading Schema Log and Error**********************/
1262 l_b_print_row_heading := TRUE;
1263 IF v_tab_unit_gs.EXISTS(1) THEN
1264
1265 IF l_b_print_row_heading AND l_c_return_status = 'S' THEN
1266 row_head('PS_GRD_SCH',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1267 l_b_print_row_heading := FALSE ;
1268 END IF;
1269
1270 FOR i IN 1.. v_tab_unit_gs.LAST
1271 LOOP
1272
1273 IF v_tab_unit_gs(i).status = 'S' AND l_c_return_status = 'S' THEN
1274
1275 /* Update the interface table */
1276
1277 UPDATE igs_ps_lgcy_ug_int
1278 SET import_status = 'I'
1279 WHERE batch_id=p_n_batch_id
1280 AND uv_grd_schm_int_id = v_tab_unit_gs(i).interface_id;
1281
1282 /* Write into log file */
1283
1284 log_file(RPAD(v_tab_unit_gs(i).interface_id,31,' '),'P');
1285 log_file(RPAD(l_c_import_desc,10,' '),'P');
1286 print_char(1,' ');
1287 log_file(l_c_succ_desc,'L');
1288
1289 ELSIF v_tab_unit_gs(i).status IN ('W','E') THEN
1290
1291 IF l_b_print_row_heading THEN
1292 row_head('PS_GRD_SCH',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1293 l_b_print_row_heading := FALSE ;
1294 END IF;
1295
1296 /* Update the interface table */
1297
1298 UPDATE igs_ps_lgcy_ug_int
1299 SET import_status = v_tab_unit_gs(i).status
1300 WHERE batch_id=p_n_batch_id
1301 AND uv_grd_schm_int_id = v_tab_unit_gs(i).interface_id;
1302
1303 /* Write into log file */
1304
1305 FOR l_curr_num IN v_tab_unit_gs(i).msg_from..v_tab_unit_gs(i).msg_to
1306 LOOP
1307 l_c_msg_name := get_msg_from_stack(l_curr_num);
1308 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1309 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1310
1311 INSERT INTO igs_ps_lgcy_err_int
1312 (
1313 err_message_id,
1314 int_table_code,
1315 int_table_id,
1316 message_num,
1317 message_text,
1318 created_by,
1319 creation_date,
1320 last_updated_by,
1321 last_update_date,
1322 last_update_login,
1323 request_id,
1324 program_application_id,
1325 program_id,
1326 program_update_date)
1327 VALUES
1328 (
1329 igs_ps_lgcy_err_int_s.nextval,
1330 igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_GRD_SCH','LEGACY_PS_REC_TABLES'),
1331 v_tab_unit_gs(i).interface_id,
1332 l_n_msg_num,
1333 l_c_msg_txt,
1334 NVL(fnd_global.user_id,-1),
1335 SYSDATE,
1336 NVL(fnd_global.user_id,-1),
1337 SYSDATE,
1338 NVL(fnd_global.login_id,-1),
1339 l_n_request_id,
1340 l_n_prog_appl_id,
1341 l_n_prog_id,
1342 l_d_prog_upd_dt
1343 );
1344
1345 /* Write into log file */
1346
1347 log_file(RPAD(v_tab_unit_gs(i).interface_id,16,' '),'P');
1348 log_file(RPAD(l_n_msg_num,15,' '),'P');
1349 IF l_n_msg_num IS NULL THEN
1350 print_char(15,' ');
1351 END IF;
1352 log_file(RPAD(igs_ps_validate_lgcy_pkg.get_lkup_meaning(v_tab_unit_gs(i).status,'LEGACY_STATUS'),10,' '),'P');
1353 print_char(1,' ');
1354 log_file(l_c_msg_txt,'L');
1355
1356 END LOOP; /* Messages loop */
1357 END IF;
1358 END LOOP; /* Grading Schema Loop */
1359 v_tab_unit_gs.DELETE;
1360 END IF;
1361
1362 /******************End Grading Schema Log and Error***********************/
1363
1364 /******************Begin Unit Section Log and Error**********************/
1365 l_b_print_row_heading := TRUE;
1366 IF v_tab_usec.EXISTS(1) THEN
1367
1368 IF l_b_print_row_heading AND l_c_return_status = 'S' THEN
1369 row_head('PS_UNIT_REC',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1370 l_b_print_row_heading := FALSE ;
1371 END IF;
1372
1373 FOR i IN 1.. v_tab_usec.LAST
1374 LOOP
1375
1376 IF v_tab_usec(i).status = 'S' AND l_c_return_status = 'S' THEN
1377
1378 /* Update the interface table */
1379
1380 UPDATE igs_ps_lgcy_us_int
1381 SET import_status = 'I'
1382 WHERE batch_id=p_n_batch_id
1383 AND unit_section_int_id = v_tab_usec(i).interface_id;
1384
1385 /* Write into log file */
1386
1387 log_file(RPAD(v_tab_usec(i).interface_id,31,' '),'P');
1388 log_file(RPAD(l_c_import_desc,10,' '),'P');
1389 print_char(1,' ');
1390 log_file(l_c_succ_desc,'L');
1391
1392 ELSIF v_tab_usec(i).status IN ('W','E') THEN
1393
1394 IF l_b_print_row_heading THEN
1395 row_head('PS_UNIT_REC',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1396 l_b_print_row_heading := FALSE ;
1397 END IF;
1398
1399 /* Update the interface table */
1400
1401 UPDATE igs_ps_lgcy_us_int
1402 SET import_status = v_tab_usec(i).status
1403 WHERE batch_id=p_n_batch_id
1404 AND unit_section_int_id = v_tab_usec(i).interface_id;
1405
1406 /* Write into log file */
1407
1408 FOR l_curr_num IN v_tab_usec(i).msg_from..v_tab_usec(i).msg_to
1409 LOOP
1410 l_c_msg_name := get_msg_from_stack(l_curr_num);
1411 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1412 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1413
1414 INSERT INTO igs_ps_lgcy_err_int
1415 (
1416 err_message_id,
1417 int_table_code,
1418 int_table_id,
1419 message_num,
1420 message_text,
1421 created_by,
1422 creation_date,
1423 last_updated_by,
1424 last_update_date,
1425 last_update_login,
1426 request_id,
1427 program_application_id,
1428 program_id,
1429 program_update_date)
1430 VALUES
1431 (
1432 igs_ps_lgcy_err_int_s.nextval,
1433 igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_SEC','LEGACY_PS_REC_TABLES'),
1434 v_tab_usec(i).interface_id,
1435 l_n_msg_num,
1436 l_c_msg_txt,
1437 NVL(fnd_global.user_id,-1),
1438 SYSDATE,
1439 NVL(fnd_global.user_id,-1),
1440 SYSDATE,
1441 NVL(fnd_global.login_id,-1),
1442 l_n_request_id,
1443 l_n_prog_appl_id,
1444 l_n_prog_id,
1445 l_d_prog_upd_dt
1446 );
1447
1448 /* Write into log file */
1449
1450 log_file(RPAD(v_tab_usec(i).interface_id,16,' '),'P');
1451 log_file(RPAD(l_n_msg_num,15,' '),'P');
1452 IF l_n_msg_num IS NULL THEN
1453 print_char(15,' ');
1454 END IF;
1455 log_file(RPAD(igs_ps_validate_lgcy_pkg.get_lkup_meaning(v_tab_usec(i).status,'LEGACY_STATUS'),10,' '),'P');
1456 print_char(1,' ');
1457 log_file(l_c_msg_txt,'L');
1458
1459 END LOOP; /* Messages loop */
1460 END IF;
1461 END LOOP; /* Unit Section Loop */
1462 v_tab_usec.DELETE;
1463 END IF;
1464
1465 /******************End Unit Section Log and Error***********************/
1466
1467 /******************Begin Unit Section Grading Schema Log and Error**********************/
1468
1469 l_b_print_row_heading := TRUE;
1470 IF v_tab_usec_gs.EXISTS(1) THEN
1471
1472 IF l_b_print_row_heading AND l_c_return_status = 'S' THEN
1473 row_head('PS_UNIT_GRD_SCH',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1474 l_b_print_row_heading := FALSE ;
1475 END IF;
1476
1477 FOR i IN 1.. v_tab_usec_gs.LAST
1478 LOOP
1479
1480 IF v_tab_usec_gs(i).status = 'S' AND l_c_return_status = 'S' THEN
1481
1482 /* Update the interface table */
1483
1484 UPDATE igs_ps_lgcy_sg_int
1485 SET import_status = 'I'
1486 WHERE batch_id=p_n_batch_id
1487 AND usec_grd_schm_int_id = v_tab_usec_gs(i).interface_id;
1488
1489 /* Write into log file */
1490
1491 log_file(RPAD(v_tab_usec_gs(i).interface_id,31,' '),'P');
1492 log_file(RPAD(l_c_import_desc,10,' '),'P');
1493 print_char(1,' ');
1494 log_file(l_c_succ_desc,'L');
1495
1496 ELSIF v_tab_usec_gs(i).status IN ('W','E') THEN
1497
1498 IF l_b_print_row_heading THEN
1499 row_head('PS_UNIT_GRD_SCH',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1500 l_b_print_row_heading := FALSE ;
1501 END IF;
1502
1503 /* Update the interface table */
1504
1505 UPDATE igs_ps_lgcy_sg_int
1506 SET import_status = v_tab_usec_gs(i).status
1507 WHERE batch_id=p_n_batch_id
1508 AND usec_grd_schm_int_id = v_tab_usec_gs(i).interface_id;
1509
1510 /* Write into log file */
1511
1512 FOR l_curr_num IN v_tab_usec_gs(i).msg_from..v_tab_usec_gs(i).msg_to
1513 LOOP
1514 l_c_msg_name := get_msg_from_stack(l_curr_num);
1515 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1516 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1517
1518 INSERT INTO igs_ps_lgcy_err_int
1519 (
1520 err_message_id,
1521 int_table_code,
1522 int_table_id,
1523 message_num,
1524 message_text,
1525 created_by,
1526 creation_date,
1527 last_updated_by,
1528 last_update_date,
1529 last_update_login,
1530 request_id,
1531 program_application_id,
1532 program_id,
1533 program_update_date
1534 )
1535 VALUES
1536 (
1537 igs_ps_lgcy_err_int_s.nextval,
1538 igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_GRD_SCH','LEGACY_PS_REC_TABLES'),
1539 v_tab_usec_gs(i).interface_id,
1540 l_n_msg_num,
1541 l_c_msg_txt,
1542 NVL(fnd_global.user_id,-1),
1543 SYSDATE,
1544 NVL(fnd_global.user_id,-1),
1545 SYSDATE,
1546 NVL(fnd_global.login_id,-1),
1547 l_n_request_id,
1548 l_n_prog_appl_id,
1549 l_n_prog_id,
1550 l_d_prog_upd_dt
1551 );
1552
1553 /* Write into log file */
1554
1555 log_file(RPAD(v_tab_usec_gs(i).interface_id,16,' '),'P');
1556 log_file(RPAD(l_n_msg_num,15,' '),'P');
1557 IF l_n_msg_num IS NULL THEN
1558 print_char(15,' ');
1559 END IF;
1560 log_file(RPAD(igs_ps_validate_lgcy_pkg.get_lkup_meaning(v_tab_usec_gs(i).status,'LEGACY_STATUS'),10,' '),'P');
1561 print_char(1,' ');
1562 log_file(l_c_msg_txt,'L');
1563
1564 END LOOP; /* Messages loop */
1565 END IF;
1566 END LOOP; /* Unit Section Grading Schema Loop */
1567 v_tab_usec_gs.DELETE;
1568 END IF;
1569
1570 /******************End Unit Section Grading Schema Log and Error***********************/
1571
1572 /******************Begin Unit Section Occurrences Log and Error**********************/
1573 l_b_print_row_heading := TRUE;
1574 IF v_tab_uso.EXISTS(1) THEN
1575
1576 IF l_b_print_row_heading AND l_c_return_status = 'S' THEN
1577 row_head('PS_UNIT_SEC_OCCUR',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1578 l_b_print_row_heading := FALSE ;
1579 END IF;
1580
1581 FOR i IN 1.. v_tab_uso.LAST
1582 LOOP
1583
1584 IF v_tab_uso(i).status = 'S' AND l_c_return_status = 'S' THEN
1585
1586 /* Update the interface table */
1587
1588 UPDATE igs_ps_lgcy_oc_int
1589 SET import_status = 'I'
1590 WHERE batch_id=p_n_batch_id
1591 AND usec_occur_int_id = v_tab_uso(i).interface_id;
1592
1593 /* Write into log file */
1594
1595 log_file(RPAD(v_tab_uso(i).interface_id,31,' '),'P');
1596 log_file(RPAD(l_c_import_desc,10,' '),'P');
1597 print_char(1,' ');
1598 log_file(l_c_succ_desc,'L');
1599
1600 ELSIF v_tab_uso(i).status IN ('W','E') THEN
1601
1602 IF l_b_print_row_heading THEN
1603 row_head('PS_UNIT_SEC_OCCUR',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1604 l_b_print_row_heading := FALSE ;
1605 END IF;
1606
1607 /* Update the interface table */
1608
1609 UPDATE igs_ps_lgcy_oc_int
1610 SET import_status = v_tab_uso(i).status
1611 WHERE batch_id=p_n_batch_id
1612 AND usec_occur_int_id = v_tab_uso(i).interface_id;
1613
1614 /* Write into log file */
1615
1616 FOR l_curr_num IN v_tab_uso(i).msg_from..v_tab_uso(i).msg_to
1617 LOOP
1618 l_c_msg_name := get_msg_from_stack(l_curr_num);
1619 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1620 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1621
1622 INSERT INTO igs_ps_lgcy_err_int
1623 (
1624 err_message_id,
1625 int_table_code,
1626 int_table_id,
1627 message_num,
1628 message_text,
1629 created_by,
1630 creation_date,
1631 last_updated_by,
1632 last_update_date,
1633 last_update_login,
1634 request_id,
1635 program_application_id,
1636 program_id,
1637 program_update_date)
1638 VALUES
1639 (
1640 igs_ps_lgcy_err_int_s.nextval,
1641 igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_SEC_OCCUR','LEGACY_PS_REC_TABLES'),
1642 v_tab_uso(i).interface_id,
1643 l_n_msg_num,
1644 l_c_msg_txt,
1645 NVL(fnd_global.user_id,-1),
1646 SYSDATE,
1647 NVL(fnd_global.user_id,-1),
1648 SYSDATE,
1649 NVL(fnd_global.login_id,-1),
1650 l_n_request_id,
1651 l_n_prog_appl_id,
1652 l_n_prog_id,
1653 l_d_prog_upd_dt
1654 );
1655
1656 /* Write into log file */
1657
1658 log_file(RPAD(v_tab_uso(i).interface_id,16,' '),'P');
1659 log_file(RPAD(l_n_msg_num,15,' '),'P');
1660 IF l_n_msg_num IS NULL THEN
1661 print_char(15,' ');
1662 END IF;
1663 log_file(RPAD(igs_ps_validate_lgcy_pkg.get_lkup_meaning(v_tab_uso(i).status,'LEGACY_STATUS'),10,' '),'P');
1664 print_char(1,' ');
1665 log_file(l_c_msg_txt,'L');
1666
1667 END LOOP; /* Messages loop */
1668 END IF;
1669 END LOOP; /* Unit Section Occurrences Loop */
1670 v_tab_uso.DELETE;
1671 END IF;
1672
1673 /******************End Unit Section Occurrences Log and Error***********************/
1674
1675 /******************Begin Unit Reference Code Log and Error**********************/
1676 l_b_print_row_heading := TRUE;
1677 IF v_tab_unit_ref.EXISTS(1) THEN
1678
1679 IF l_b_print_row_heading AND l_c_return_status = 'S' THEN
1680 row_head('PS_UNIT_REF',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1681 l_b_print_row_heading := FALSE ;
1682 END IF;
1683
1684 FOR i IN 1.. v_tab_unit_ref.LAST
1685 LOOP
1686
1687 IF v_tab_unit_ref(i).status = 'S' AND l_c_return_status = 'S' THEN
1688
1689 /* Update the interface table */
1690
1691 UPDATE igs_ps_lgcy_ur_int
1692 SET import_status = 'I'
1693 WHERE batch_id=p_n_batch_id
1694 AND unit_reference_int_id = v_tab_unit_ref(i).interface_id;
1695
1696 /* Write into log file */
1697
1698 log_file(RPAD(v_tab_unit_ref(i).interface_id,31,' '),'P');
1699 log_file(RPAD(l_c_import_desc,10,' '),'P');
1700 print_char(1,' ');
1701 log_file(l_c_succ_desc,'L');
1702
1703 ELSIF v_tab_unit_ref(i).status IN ('W','E') THEN
1704
1705 IF l_b_print_row_heading THEN
1706 row_head('PS_UNIT_REF',rec_c_all_units.unit_cd,rec_c_all_units.version_number);
1707 l_b_print_row_heading := FALSE ;
1708 END IF;
1709
1710 /* Update the interface table */
1711
1712 UPDATE igs_ps_lgcy_ur_int
1713 SET import_status = v_tab_unit_ref(i).status
1714 WHERE batch_id=p_n_batch_id
1715 AND unit_reference_int_id = v_tab_unit_ref(i).interface_id;
1716
1717 /* Write into log file */
1718
1719 FOR l_curr_num IN v_tab_unit_ref(i).msg_from..v_tab_unit_ref(i).msg_to
1720 LOOP
1721 l_c_msg_name := get_msg_from_stack(l_curr_num);
1722 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1723 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1724
1725 INSERT INTO igs_ps_lgcy_err_int
1726 (
1727 err_message_id,
1728 int_table_code,
1729 int_table_id,
1730 message_num,
1731 message_text,
1732 created_by,
1733 creation_date,
1734 last_updated_by,
1735 last_update_date,
1736 last_update_login,
1737 request_id,
1738 program_application_id,
1739 program_id,
1740 program_update_date)
1741 VALUES
1742 (
1743 igs_ps_lgcy_err_int_s.nextval,
1744 igs_ps_validate_lgcy_pkg.get_lkup_meaning('PS_UNIT_REF','LEGACY_PS_REC_TABLES'),
1745 v_tab_unit_ref(i).interface_id,
1746 l_n_msg_num,
1747 l_c_msg_txt,
1748 NVL(fnd_global.user_id,-1),
1749 SYSDATE,
1750 NVL(fnd_global.user_id,-1),
1751 SYSDATE,
1752 NVL(fnd_global.login_id,-1),
1753 l_n_request_id,
1754 l_n_prog_appl_id,
1755 l_n_prog_id,
1756 l_d_prog_upd_dt
1757 );
1758
1759 /* Write into log file */
1760
1761 log_file(RPAD(v_tab_unit_ref(i).interface_id,16,' '),'P');
1762 log_file(RPAD(l_n_msg_num,15,' '),'P');
1763 IF l_n_msg_num IS NULL THEN
1764 print_char(15,' ');
1765 END IF;
1766 log_file(RPAD(igs_ps_validate_lgcy_pkg.get_lkup_meaning(v_tab_unit_ref(i).status,'LEGACY_STATUS'),10,' '),'P');
1767 print_char(1,' ');
1768 log_file(l_c_msg_txt,'L');
1769
1770 END LOOP; /* Messages loop */
1771 END IF;
1772 END LOOP; /* Unit References Code Loop */
1773 v_tab_unit_ref.DELETE;
1774 END IF;
1775
1776 /******************End Unit Reference Code Log and Error***********************/
1777
1778 /******************Begin Instructor Log and Error**********************/
1779 l_b_print_row_heading := TRUE;
1780 IF v_tab_ins.EXISTS(1) THEN
1781
1782 IF l_b_print_row_heading AND l_c_return_status = 'S' THEN
1783 log_file(' ','N');
1784 get_message('IGS_EN_ROW_TYPE',l_n_msg_num,l_c_msg_txt);
1785 log_file(l_c_msg_txt,'P');
1786 log_file(' ','P');
1787 fnd_message.set_name('IGS','IGS_PS_USO_INS');
1788 log_file(fnd_message.get,'P');
1789 log_file(' ','P');
1790 log_file(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_CD','LEGACY_TOKENS'),'P');
1791 log_file(' : ','P');
1792 log_file(rec_c_all_units.unit_cd,'P');
1793 log_file(' ','P');
1794 log_file(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_VER_NUM','LEGACY_TOKENS'),'P');
1795 log_file(' : ','P');
1796 log_file(rec_c_all_units.version_number,'L');
1797 log_file(' ','N');
1798 l_b_print_row_heading := FALSE ;
1799 END IF;
1800
1801 FOR i IN 1.. v_tab_ins.LAST LOOP
1802 IF v_tab_ins(i).status = 'S' AND l_c_return_status = 'S' THEN
1803
1804 /* Update the interface table */
1805
1806 UPDATE igs_ps_lgcy_ins_int
1807 SET import_status = 'I'
1808 WHERE batch_id=p_n_batch_id
1809 AND uso_instructor_int_id = v_tab_ins(i).interface_id;
1810
1811 /* Write into log file */
1812
1813 log_file(RPAD(v_tab_ins(i).interface_id,31,' '),'P');
1814 log_file(RPAD(l_c_import_desc,10,' '),'P');
1815 print_char(1,' ');
1816 log_file(l_c_succ_desc,'L');
1817
1818 ELSIF v_tab_ins(i).status IN ('W','E') THEN
1819
1820 IF l_b_print_row_heading THEN
1821 log_file(' ','N');
1822 get_message('IGS_EN_ROW_TYPE',l_n_msg_num,l_c_msg_txt);
1823 log_file(l_c_msg_txt,'P');
1824 log_file(' ','P');
1825 fnd_message.set_name('IGS','IGS_PS_USO_INS');
1826 log_file(fnd_message.get,'P');
1827 log_file(' ','P');
1828 log_file(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_CD','LEGACY_TOKENS'),'P');
1829 log_file(' : ','P');
1830 log_file(rec_c_all_units.unit_cd,'P');
1831 log_file(' ','P');
1832 log_file(igs_ps_validate_lgcy_pkg.get_lkup_meaning('UNIT_VER_NUM','LEGACY_TOKENS'),'P');
1833 log_file(' : ','P');
1834 log_file(rec_c_all_units.version_number,'L');
1835 log_file(' ','N');
1836 l_b_print_row_heading := FALSE ;
1837 END IF;
1838
1839 /* Update the interface table */
1840
1841 UPDATE igs_ps_lgcy_ins_int
1842 SET import_status = v_tab_ins(i).status
1843 WHERE batch_id=p_n_batch_id
1844 AND uso_instructor_int_id = v_tab_ins(i).interface_id;
1845
1846 /* Write into log file */
1847
1848 FOR l_curr_num IN v_tab_ins(I).msg_from..v_tab_ins(I).msg_to LOOP
1849
1850 l_c_msg_name := get_msg_from_stack(l_curr_num);
1851 get_message(l_c_msg_name,l_n_msg_num,l_c_msg_txt);
1852 l_c_msg_txt := fnd_msg_pub.get(l_curr_num,'F');
1853
1854 fnd_message.set_name('IGS','IGS_PS_USO_INS_TBL_NAME');
1855
1856 INSERT INTO igs_ps_lgcy_err_int(
1857 err_message_id,
1858 int_table_code,
1859 int_table_id,
1860 message_num,
1861 message_text,
1862 created_by,
1863 creation_date,
1864 last_updated_by,
1865 last_update_date,
1866 last_update_login,
1867 request_id,
1868 program_application_id,
1869 program_id,
1870 program_update_date
1871 ) VALUES(
1872 igs_ps_lgcy_err_int_s.nextval,
1873 fnd_message.get,
1874 v_tab_ins(i).interface_id,
1875 l_n_msg_num,
1876 l_c_msg_txt,
1877 NVL(fnd_global.user_id,-1),
1878 SYSDATE,
1879 NVL(fnd_global.user_id,-1),
1880 SYSDATE,
1881 NVL(fnd_global.login_id,-1),
1882 l_n_request_id,
1883 l_n_prog_appl_id,
1884 l_n_prog_id,
1885 l_d_prog_upd_dt);
1886
1887 /* Write into log file */
1888
1889 log_file(RPAD(v_tab_ins(i).interface_id,16,' '),'P');
1890 log_file(RPAD(l_n_msg_num,15,' '),'P');
1891
1892 IF l_n_msg_num IS NULL THEN
1893 print_char(15,' ');
1894 END IF;
1895
1896 log_file( RPAD( igs_ps_validate_lgcy_pkg.get_lkup_meaning(v_tab_ins(i).status, 'LEGACY_STATUS'),10,' '),'P');
1897 print_char(1,' ');
1898 log_file(l_c_msg_txt,'L');
1899
1900 END LOOP;
1901 /* Messages loop */
1902 END IF;
1903 END LOOP;
1904 v_tab_ins.DELETE;
1905 END IF;
1906
1907
1908 /******************End Instructor Log and Error***********************/
1909
1910 END LOOP; /* Main loop - c_all_units cursor*/
1911
1912 /* Delete imported records if user wishes to delete by passing the parameter p_delete='Y' */
1913
1914 IF p_c_del_flag='Y' AND l_c_return_status = 'S' THEN
1915
1916 /* Delete from Unit Version Interface Table */
1917
1918 DELETE FROM igs_ps_lgcy_uv_int
1919 WHERE batch_id=p_n_batch_id
1920 AND import_status = 'I';
1921
1922 /* Delete from Teaching Responsibility Interface Table */
1923
1924 DELETE FROM igs_ps_lgcy_tr_int
1925 WHERE batch_id=p_n_batch_id
1926 AND import_status = 'I';
1927
1928 /* Delete from Unit Disciplines Interface Table */
1929
1930 DELETE FROM igs_ps_lgcy_ud_int
1931 WHERE batch_id=p_n_batch_id
1932 AND import_status = 'I';
1933
1934 /* Delete from Grading Schema Interface Table */
1935
1936 DELETE FROM igs_ps_lgcy_ug_int
1937 WHERE batch_id=p_n_batch_id
1938 AND import_status = 'I';
1939
1940 /* Delete from Unit Section Interface Table */
1941
1942 DELETE FROM igs_ps_lgcy_us_int
1943 WHERE batch_id=p_n_batch_id
1944 AND import_status = 'I';
1945
1946 /* Delete from Unit Section Grading Schema Interface Table */
1947
1948 DELETE FROM igs_ps_lgcy_sg_int
1949 WHERE batch_id=p_n_batch_id
1950 AND import_status = 'I';
1951
1952 /* Delete from Unit Section Occurrences Interface Table */
1953
1954 DELETE FROM igs_ps_lgcy_oc_int
1955 WHERE batch_id=p_n_batch_id
1956 AND import_status = 'I';
1957
1958 /* Delete from Unit Reference Codes Table */
1959
1960 DELETE FROM igs_ps_lgcy_ur_int
1961 WHERE batch_id=p_n_batch_id
1962 AND import_status = 'I';
1963
1964 /* Delete from Instructor Table */
1965
1966 DELETE FROM igs_ps_lgcy_ins_int
1967 WHERE batch_id=p_n_batch_id
1968 AND import_status = 'I';
1969
1970 END If;
1971
1972 /* If none of the interface tables has not appropriate data that is to be processed, then set the message and error out */
1973 IF l_c_return_status IS NULL THEN
1974 get_message('IGS_PS_LGCY_DATA_NOT_PASSED',l_n_msg_num,l_c_msg_txt);
1975 log_file(l_c_msg_txt,'L');
1976 retcode := 2;
1977 RETURN;
1978 ELSE
1979 print_char(80,'=');
1980 END IF;
1981
1982 -- Set the concurrent program status to Error if the API return status is Error for all the attempted records
1983
1984 IF l_ret_status THEN
1985 retcode:=2;
1986 END IF;
1987
1988 -- End of Procedure
1989 EXCEPTION
1990 WHEN OTHERS THEN
1991 ROLLBACK;
1992 retcode:=2;
1993 fnd_file.put_line(fnd_file.log,sqlerrm);
1994 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') ;
1995 igs_ge_msg_stack.conc_exception_hndl;
1996
1997 END legacy_batch_process;
1998
1999 END igs_ps_lgcy_conc_pkg;