1 PACKAGE BODY igs_uc_ext_marvin AS
2 /* $Header: IGSUC33B.pls 120.3 2006/08/21 03:52:45 jbaber noship $ */
3 /* HISTORY
4 WHO WHEN WHAT
5 nalkumar 09-APR-2002 Created the Document
6 ayedubat 19-NOV-2002 Changed the procedures: create_file,prepare_data,
7 prepare_trailer and check_sec_len for small systems
8 support build. Enhancement Bug # 2643048
9 ayedubat 13-DEC-2002 Changed the procedure,prepare_data for bug # 2711416
10 ayedubat 24-DEC-2002 Changed the procedure,create_file for bug, 2711256
11 pmarada 11-jun-2003 Added ucas_cycle to transaction table removed the
12 adm_systems references as per UCFD203 Multiple cycles,bug 2669208
13 ayedubat 02-JUL-2003 Changed as part of Multiple Cycles Enhancement, 2669208
14 As per the New enhancment only the Transactions for the Configured
15 Cycle will be exported. Also Course Vacancies and Vacancy Options
16 will be exported for SWAS along with the existing FTUG System.
17 Removed the procedure, trunc_tables
18 dsridhar 30-SEP-2003 Bug No: 3156212. Code modified to consider only those transactions
19 for systems configured as MARVIN.
20 jchakrab 07-Sep-2004 Modified for Bug#3872286
21 anwest 18-Jan-2006 Bug# 4950285 R12 Disable OSS Mandate
22 anwest 13-Feb-2006 Bug# 4960517 - Replaced profile IGS_PS_EXP_DIR_PATH with UTL_FILE_OUT
23 jbaber 11-Jul-2006 Modified for UC325 - UCAS 2007 Support
24 */
25
26 l_sequence_number NUMBER(6) DEFAULT 00001;
27 l_total_trans NUMBER(6) DEFAULT 0;
28 l_date VARCHAR2(10) DEFAULT TO_CHAR(SYSDATE,'ddmmyy');
29 l_file_name VARCHAR2(20); --File Name format is AfinUUU.xxxxxx
30 l_file_prm UTL_FILE.FILE_TYPE;
31
32 PROCEDURE open_file ( p_location IN VARCHAR2,
33 p_filename IN VARCHAR2) IS
34 /*
35 || Created By : [email protected]
36 || Created On : 09-APR-2002
37 || Purpose : To open the flat file.
38 || Known limitations, enhancements or remarks :
39 || Change History :
40 || Who When What
41 || (reverse chronological order - newest change first)
42 */
43 BEGIN
44
45 l_file_prm := NULL;
46 l_file_prm:= UTL_FILE.FOPEN (p_location,
47 p_filename,
48 'w');
49 EXCEPTION
50 WHEN OTHERS THEN
51 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_INVALID_PATH');
52 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
53 IGS_GE_MSG_STACK.ADD;
54 App_Exception.Raise_Exception;
55
56 END open_file;
57
58 PROCEDURE put_line (p_line IN VARCHAR2) IS
59 /*
60 || Created By : [email protected]
61 || Created On : 09-APR-2002
62 || Purpose : To put the line into the flat file.
63 || Known limitations, enhancements or remarks :
64 || Change History :
65 || Who When What
66 || (reverse chronological order - newest change first)
67 */
68 BEGIN
69 UTL_FILE.PUT_LINE (l_file_prm, p_line );
70 UTL_FILE.FFLUSH (l_file_prm );
71 END put_line;
72
73 PROCEDURE close_file IS
74 /*
75 || Created By : [email protected]
76 || Created On : 09-APR-2002
77 || Purpose : To close the flat file.
78 || Known limitations, enhancements or remarks :
79 || Change History :
80 || Who When What
81 || (reverse chronological order - newest change first)
82 */
83 BEGIN
84 IF (UTL_FILE.IS_OPEN( l_file_prm )) THEN
85 UTL_FILE.FCLOSE( l_file_prm );
86 END IF;
87 END close_file;
88
89
90 PROCEDURE prepare_header( p_inst_code IN VARCHAR2,
91 p_sys_ind IN VARCHAR2) IS
92 /*
93 || Created By : [email protected]
94 || Created On : 09-APR-2002
95 || Purpose : This procedure loads the header data into the Flat file.
96 || Known limitations, enhancements or remarks :
97 || Change History :
98 || Who When What
99 || Ayedubat 13_DEC-2002 Changed the herader line to add the RPAD to 4 characters
100 || for institution code from 14 to 17 for bug: 2711145
101 || (reverse chronological order - newest change first)
102 */
103 l_header VARCHAR2(100);
104 BEGIN
105 --
106 -- For the Header the sequence Number will be always '00001', because
107 -- will be the first line of the data file. The 'Transaction Type' for
108 -- the header is '*S' and the 'Length' is '1' and there is no 'Error Code'
109 -- for the Header.
110 --
111 l_header := '000019*S 1 '||RPAD(p_inst_code,4)||'I'||l_date||'00000'||p_sys_ind;
112 put_line(l_header);
113
114 EXCEPTION WHEN OTHERS THEN
115 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
116 FND_MESSAGE.SET_TOKEN('NAME','IGS_UC_EXT_MARVIN.PREPARE_HEADER'||' - '||SQLERRM);
117 IGS_GE_MSG_STACK.ADD;
118 App_Exception.Raise_Exception;
119
120 END prepare_header;
121
122 PROCEDURE prepare_trailer(p_inst_code IN VARCHAR2,
123 p_sys_ind IN VARCHAR2) IS
124 /*
125 || Created By : [email protected]
126 || Created On : 09-APR-2002
127 || Purpose : This procedure loads the trailer data into the flat file.
128 || Known limitations, enhancements or remarks :
129 || Change History :
130 || Who When What
131 || ayedubat 19-NOV-2002 Changed the section type to 'E' for
132 || End of Section for bug # 2643048
133 || Ayedubat 13_DEC-2002 Changed the herader line to add the RPAD to 4 characters
134 || for institution code from 14 to 17 for bug: 2711145
135 || (reverse chronological order - newest change first)
136 */
137 l_trailer VARCHAR2(100);
138 BEGIN
139
140 l_trailer := LPAD((l_sequence_number + 1),5,0)||'9*S 1 '||RPAD(p_inst_code,4)||'E'||l_date||LPAD(l_total_trans,5,0)||p_sys_ind;
141 put_line(l_trailer);
142
143 EXCEPTION WHEN OTHERS THEN
144 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
145 FND_MESSAGE.SET_TOKEN('NAME','IGS_UC_EXT_MARVIN.PREPARE_TRAILER'||' - '||SQLERRM);
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148
149 END prepare_trailer;
150
151 PROCEDURE create_directory ( p_directory OUT NOCOPY VARCHAR2 ) AS
152 /**********************************************************
153 Created By : ayedubat
154 Date Created By : 24-DEC-2002
155 Purpose : Private procedure craeted for finding the Directory of the filename.
156 This will check the value of profile 'UTL_FILE_OUT'
157 (output dir for export data file) matches with the value in v$parameter
158 for the name utl_file_dir.
159 Know limitations, enhancements or remarks
160 Change History
161 Who When What
162 (reverse chronological order - newest change first)
163 ***************************************************************/
164
165 l_start_str_index NUMBER := 1;
166 l_end_comma_index NUMBER := 1;
167 l_start_comma_index NUMBER := 1;
168 l_fndvalue VARCHAR2(80);
169 l_dbvalue V$PARAMETER.VALUE%TYPE;
170 l_db_directory V$PARAMETER.VALUE%TYPE;
171
172 CURSOR cur_parameter_value IS
173 SELECT LTRIM(RTRIM(value))
174 FROM V$PARAMETER
175 WHERE name ='utl_file_dir';
176
177 CURSOR cur_db_dir IS
178 SELECT DECODE( INSTR(l_dbvalue,',',l_start_str_index),0,LENGTH(l_dbvalue)+1, INSTR(l_dbvalue,',',l_start_str_index) )
179 FROM DUAL ;
180
181 BEGIN
182
183 -- Initialize the OUT variable
184 p_directory := NULL ;
185
186 -- Fetch the Profile value for the directory name used to export flat file
187 l_fndvalue := LTRIM(RTRIM(FND_PROFILE.VALUE('UTL_FILE_OUT')));
188
189 -- If the profile is NULL, return the procedure by assigning the NULL value to p_directory
190 IF l_fndvalue IS NULL THEN
191 p_directory := NULL ;
192 RETURN ;
193 END IF ;
194
195 -- Fetch the Value of the Database parameter, utl_file_dir
196 -- which contains list of out put directories seperated by comma
197 OPEN cur_parameter_value ;
198 FETCH cur_parameter_value INTO l_dbvalue ;
199
200 IF cur_parameter_value%FOUND AND l_dbvalue IS NOT NULL THEN
201
202 -- Find the starting position of the Profile value with in the database parameter directory's list
203 -- If not found, return the procedure by assigning the NULL value to p_directory
204 l_start_str_index := INSTR(l_dbvalue,l_fndvalue,l_end_comma_index) ;
205
206 IF l_start_str_index = 0 THEN
207 p_directory := NULL ;
208 CLOSE cur_parameter_value ;
209 RETURN ;
210 END IF;
211
212 -- Find the position of the comma, which is just before the
213 -- required directory(Profile value) in the database parameter value
214 -- If no comma found( if it first in the list), then returns 1
215 l_start_comma_index := INSTR(SUBSTR(l_dbvalue,1,l_start_str_index),',',-1) + 1 ;
216
217 -- Find the position of the comma, which is just after the
218 -- required directory(Profile value) in the database parameter value
219 -- If no comma found( if it last in the list), then returns the length of database parameter value
220 OPEN cur_db_dir ;
221 FETCH cur_db_dir INTO l_end_comma_index ;
222 CLOSE cur_db_dir ;
223
224 -- Find the value of the Database parameter value between l_start_comma_index and l_end_comma_index
225 l_db_directory := LTRIM(RTRIM(SUBSTR(l_dbvalue,l_start_comma_index, l_end_comma_index-l_start_comma_index))) ;
226
227 -- If the Profile Value is with in the Database parameter sirectory list, return the value
228 IF l_db_directory = l_fndvalue THEN
229 p_directory := l_fndvalue ;
230 END IF;
231
232 ELSE
233 p_directory := NULL ;
234
235 END IF ;
236 CLOSE cur_parameter_value ;
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 p_directory := NULL ;
241 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
242 FND_MESSAGE.SET_TOKEN('NAME','IGS_UC_EXT_MARVIN.CREATE_DIRECTORY'||' - '||SQLERRM);
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245
246 END create_directory ;
247
248 PROCEDURE check_sec_len(tran_len NUMBER,
249 p_sys_ind IN VARCHAR2) IS
250 /*
251 || Created By : [email protected]
252 || Created On : 09-APR-2002
253 || Purpose : To check if the number of lines in the current section is exceeding the maximum limit
254 || and if it is exceeding the limit then close the current section
255 || and open a new section.
256 || Known limitations, enhancements or remarks :
257 || Change History :
258 || Who When What
259 || ayedubat 19-NOV-2002 Changed the procedure to add p_sys_ind parameter
260 || for the bug # 2643048
261 */
262 CURSOR cur_c1 IS
263 SELECT DISTINCT current_inst_code
264 FROM igs_uc_defaults ;
265
266 l_max_line NUMBER(7) DEFAULT 99999; --DEFAULT 17;
267 l_total_lines NUMBER(7);
268 l_inst_code igs_uc_defaults.current_inst_code%TYPE;
269
270 BEGIN
271
272 -- Get the Institution Code
273 OPEN cur_c1;
274 FETCH cur_c1 INTO l_inst_code;
275 CLOSE cur_c1;
276
277 l_total_lines := tran_len + l_sequence_number + 1; -- 1 for the Trailer.
278 IF l_total_lines > l_max_line THEN
279
280 -- To prepare the Trailer Information.
281 prepare_trailer(p_inst_code => l_inst_code,
282 p_sys_ind => p_sys_ind);
283
284 -- Reset the Sequence Number and the Total Transaction Numbers.
285 l_sequence_number := 00001;
286 l_total_trans := 0;
287
288 -- To prepare the Header Information.
289 prepare_header(p_inst_code => l_inst_code,
290 p_sys_ind => p_sys_ind);
291
292 END IF;
293
294 EXCEPTION WHEN OTHERS THEN
295 Fnd_Message.Set_name('IGS','IGS_GE_UNHANDLED_EXP');
296 FND_MESSAGE.SET_TOKEN('NAME','IGS_UC_EXT_MARVIN.CHECK_SEC_LEN'||' - '||SQLERRM);
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299
300 END check_sec_len;
301
302
303 PROCEDURE prepare_data ( p_sys_ind IN VARCHAR2, p_configured_cycle IN NUMBER,
304 p_current_cycle IN NUMBER, p_current_inst_code IN VARCHAR2) IS
305 /*
306 || Created By : [email protected]
307 || Created On : 09-APR-2002
308 || Purpose : This procedure loads the record data into the flat file.
309 || Known limitations, enhancements or remarks :
310 || Change History :
311 || Who When What
312 || ayedubat 18-NOV-2002 Changed the signature of the package to add a
313 || new parameter,p_sys_ind. As part of small systems support,
314 || now this process is called once for each system.
315 || As part of this change, all the transactions are selecting
316 || from IGS_UC_TRANSACTIONS table instead of IGS_UC_MV_TRANIN.
317 || Created the new 'LE' type of transaction.
318 || Enh bug # 2643048
319 || ayedubat 13-DEC-2002 Changed the LA and LD transactions to write the Course element,
320 || Entry Year and Entry month , Point of Entry, Sumary of Conditions
321 || and Comments in Offer elements, for bug# 2711416
322 || ayedubat 02-JUL-2003 Changed as part of Multiple Cycles Enhancement, 2669208
323 || Replaced the tables igs_uc_mv_uvcrs_vacm, igs_uc_mv_uvcrs_vop and
324 || igs_uc_mv_uvofr_abv with igs_uc_crse_dets, igs_uc_crse_vac_ops and
325 || igs_uc_ref_off_abrv to fecth the Transactions.
326 ||
327 || (reverse chronological order - newest change first)
328 */
329
330 -- Cursor to fetch the pending transactions of a given UCAS System
331 CURSOR cur_transactions( p_system_code IGS_UC_TRANSACTIONS.system_code%TYPE ) IS
332 SELECT
333 ROWID,
334 uc_tran_id,
335 transaction_id,
336 datetimestamp,
337 updater,
338 error_code,
339 transaction_type,
340 app_no,
341 choice_no,
342 decision,
343 program_code,
344 campus,
345 entry_month,
346 entry_year,
347 entry_point,
348 soc,
349 comments_in_offer,
350 return1,
351 return2,
352 hold_flag,
353 created_by,
354 creation_date,
355 last_updated_by,
356 last_update_date,
357 last_update_login,
358 sent_to_ucas,
359 test_cond_cat,
360 test_cond_name,
361 inst_reference ,
362 auto_generated_flag,
363 system_code,
364 ucas_cycle,
365 modular,
366 part_time
367 FROM IGS_UC_TRANSACTIONS
368 WHERE system_code = p_system_code
369 AND ucas_cycle = p_configured_cycle
370 AND sent_to_ucas = 'N'
371 AND hold_flag = 'N'
372 AND transaction_type NOT IN ('XA','XD')
373 ORDER BY transaction_type;
374 cur_transactions_rec cur_transactions%ROWTYPE;
375
376 CURSOR cur_get_mon(p_mon NUMBER) IS
377 SELECT TO_CHAR(TO_DATE(p_mon,'MM'),'MON') entry_month
378 FROM DUAL;
379 l_cur_get_mon cur_get_mon%ROWTYPE;
380
381 -- Fetching Course Vacancy Records to be exported
382 CURSOR cur_uv_crs_vac(cp_system_code igs_uc_crse_dets.system_code%TYPE) IS
383 SELECT
384 crse.ROWID,
385 REPLACE(crse.ucas_program_code,'*',' ') course,
386 REPLACE(crse.ucas_campus, '*', ' ') campus,
387 crse.vacancy_status,
388 crse.no_of_vacancy,
389 crse.score,
390 crse.rb_full,
391 crse.scot_vac
392 FROM igs_uc_crse_dets crse
393 WHERE crse.system_code = cp_system_code AND
394 crse.institute = p_current_inst_code AND
395 crse.sent_to_ucas = 'N' AND
396 NVL(crse.imported,'Y') = 'Y'
397 ORDER BY crse.ucas_program_code , crse.ucas_campus;
398 l_cur_uv_crs_vac cur_uv_crs_vac%ROWTYPE;
399
400 -- Fetching Course Vacancy Record for Update
401 CURSOR upd_crs_vac_cur (cp_rowid VARCHAR2) IS
402 SELECT
403 crse.ROWID,
404 crse.*
405 FROM igs_uc_crse_dets crse
406 WHERE crse.ROWID = cp_rowid;
407
408 -- Fetching Course Vacancy Option Records to be exported
409 CURSOR cur_uv_crs_vac_opt(cp_system_code igs_uc_crse_dets.system_code%TYPE,
410 cp_course IGS_UC_MV_UVCRS_VOP.course%TYPE,
411 cp_campus IGS_UC_MV_UVCRS_VOP.campus%TYPE) IS
412 SELECT
413 crsevac.ROWID,
414 REPLACE(crsevac.ucas_program_code, '*', ' ') course,
415 REPLACE(crsevac.ucas_campus, '*', ' ') campus,
416 REPLACE(crsevac.option_code, '*', ' ') option_code
417 FROM igs_uc_crse_vac_ops crsevac
418 WHERE crsevac.system_code = cp_system_code AND
419 REPLACE(crsevac.ucas_program_code,'*',' ') = cp_course AND
420 REPLACE(crsevac.ucas_campus, '*', ' ') = cp_campus AND
421 crsevac.institute = p_current_inst_code AND
422 crsevac.sent_to_ucas = 'N'
423 ORDER BY crsevac.ucas_program_code , crsevac.ucas_campus , crsevac.option_code;
424 l_cur_uv_crs_vac_opt cur_uv_crs_vac_opt%ROWTYPE;
425
426 -- Fetching Course Vacancy Option Record for Update
427 CURSOR upd_crs_vac_opt_cur (cp_rowid VARCHAR2) IS
428 SELECT
429 crsevac.ROWID,
430 crsevac.*
431 FROM igs_uc_crse_vac_ops crsevac
432 WHERE crsevac.ROWID = cp_rowid;
433
434 -- Fetching Offer Abbreviations records to deleted and to be exported
435 CURSOR cur_uv_offr_abb IS
436 SELECT
437 roa.ROWID,
438 REPLACE(roa.abbrev_code,'*', ' ') abbrev_code,
439 roa.abbrev_text ,
440 REPLACE(roa.letter_format, '*', ' ') letter_format,
441 REPLACE(roa.summary_char, '*', ' ') summary_char,
442 roa.deleted
443 FROM igs_uc_ref_off_abrv roa
444 WHERE roa.sent_to_ucas = 'N' AND roa.deleted = 'N';
445 l_cur_uv_offr_abb cur_uv_offr_abb%ROWTYPE;
446
447 -- Fetching Offer Abbreviations record for Update
448 CURSOR upd_offr_abb_cur(cp_rowid VARCHAR2) IS
449 SELECT
450 roa.ROWID,
451 roa.*
452 FROM igs_uc_ref_off_abrv roa
453 WHERE roa.ROWID = cp_rowid;
454
455 l_record VARCHAR2(1000);
456 l_from NUMBER(3);
457 l_cont_flag NUMBER(2);
458 l_data_set VARCHAR2(1000);
459 l_data VARCHAR2(1000);
460 l_data_per_line NUMBER(3);
461 l_len NUMBER(3);
462 l_len_cd NUMBER(3);
463 l_ext_info_first_line NUMBER(3);
464 l_info VARCHAR2(500);
465 l_tran_form NUMBER(1);
466 l_opt_code VARCHAR2(250);
467 l_tran_type IGS_UC_TRANSACTIONS.transaction_type%TYPE;
468 l_digital_num NUMBER(2);
469 l_choice_no VARCHAR2(1);
470
471 BEGIN
472
473 l_sequence_number := 00001;
474 l_total_trans := 0;
475
476 OPEN cur_transactions(p_sys_ind);
477 LOOP
478
479 FETCH cur_transactions INTO cur_transactions_rec;
480 EXIT WHEN cur_transactions%NOTFOUND;
481
482 -- To get the Check-Digital number
483 l_digital_num := NVL(igs_uc_mv_data_upld.get_check_digit(cur_transactions_rec.app_no),-1);
484
485 -- While generating the Flat file, the Choice Number column has only one character assigned,
486 -- For the systems like GTTR, the choice number can be greater than 9
487 -- So, the choice number should be as A,if it is 10 B for 11, C for 12 like that
488 IF NVL(cur_transactions_rec.choice_no,0) > 9 THEN
489
490 l_choice_no := fnd_global.local_chr( ASCII('A') + (cur_transactions_rec.choice_no - 10) ) ;
491
492 ELSE
493 l_choice_no := LPAD(NVL(cur_transactions_rec.choice_no,0),1,0) ;
494
495 END IF;
496
497 --
498 -- Find the Transaction Type and then form the Data Layout
499 --
500
501 -- Process LA and LD transactions
502 IF cur_transactions_rec.transaction_type IN ('LA','LD') THEN
503
504 l_data_per_line := 67; -- The actual data per line
505 l_ext_info_first_line := 11;
506 l_info := NULL;
507 l_from := 1;
508
509 -- Prepare the Free Format Area section in l_info varaible
510
511 -- For the course element
512 --
513 IF cur_transactions_rec.program_code IS NOT NULL AND cur_transactions_rec.program_code <> '*' THEN
514 l_info := '*'||cur_transactions_rec.program_code;
515 END IF;
516 IF cur_transactions_rec.campus IS NOT NULL AND cur_transactions_rec.campus <> '*' AND l_info IS NOT NULL THEN
517 l_info := l_info||';'||cur_transactions_rec.campus;
518 END IF;
519
520 -- For the Entry Year/Month element
521 --
522 IF cur_transactions_rec.entry_year IS NOT NULL THEN
523 IF l_info IS NOT NULL THEN
524 l_info := l_info||',:'||LPAD(cur_transactions_rec.entry_year,2,0);
525 ELSE
526 l_info := ':'||LPAD(cur_transactions_rec.entry_year,2,0);
527 END IF;
528 END IF;
529
530 IF cur_transactions_rec.entry_month IS NOT NULL THEN
531 OPEN cur_get_mon(cur_transactions_rec.entry_month);
532 FETCH cur_get_mon INTO l_cur_get_mon;
533 IF l_info IS NOT NULL THEN
534 IF cur_transactions_rec.entry_year IS NOT NULL THEN
535 l_info := l_info||l_cur_get_mon.entry_month;
536 ELSE
537 l_info := l_info||',:'||l_cur_get_mon.entry_month;
538 END IF;
539 ELSE
540 l_info := ':'||l_cur_get_mon.entry_month;
541 END IF;
542 CLOSE cur_get_mon;
543 END IF;
544
545 -- For the Point of entry element
546 --
547 IF cur_transactions_rec.entry_point IS NOT NULL THEN
548 IF l_info IS NOT NULL THEN
549 l_info := l_info||',!'||cur_transactions_rec.entry_point;
550 ELSE
551 l_info := '!'||cur_transactions_rec.entry_point;
552 END IF;
553 END IF;
554
555 -- For Summary of Conditions element
556 --
557 IF cur_transactions_rec.soc IS NOT NULL THEN
558 IF l_info IS NOT NULL THEN
559 l_info := l_info||',+'||cur_transactions_rec.soc ;
560 ELSE
561 l_info := '+'||cur_transactions_rec.soc ;
562 END IF;
563 END IF;
564
565 -- For Comment element ( Comments in Offer column )
566 --
567 IF cur_transactions_rec.comments_in_offer IS NOT NULL THEN
568 IF l_info IS NOT NULL THEN
569 l_info := l_info||','||cur_transactions_rec.comments_in_offer ;
570 ELSE
571 l_info := cur_transactions_rec.comments_in_offer ;
572 END IF ;
573 END IF ;
574 --
575 -- End of Free format data creation
576
577 l_data_set := LPAD(cur_transactions_rec.app_no,8,0)||l_digital_num||l_choice_no||
578 NVL(cur_transactions_rec.decision,' ')||l_info ;
579
580 --
581 -- To find the length of the transaction
582 --
583 l_len := NVL(length(l_info),0) + l_ext_info_first_line;
584 l_len_cd := NVL(CEIL(l_len/l_data_per_line),0);
585 l_cont_flag := 1;
586
587 --
588 -- Check the length of the section
589 --
590 check_sec_len(l_len_cd, p_sys_ind);
591
592 FOR I IN 1..l_len_cd LOOP
593
594 l_data := SUBSTR(l_data_set,l_from,l_data_per_line);
595 l_sequence_number := l_sequence_number + 1;
596
597 IF l_cont_flag = 1 THEN
598
599 IF l_cont_flag = l_len_cd THEN
600 l_cont_flag := 9;
601 END IF;
602
603 l_record :=
604 LPAD((l_sequence_number),5,0)||
605 l_cont_flag|| -- Continutuion Flag
606 cur_transactions_rec.transaction_type|| -- Transaction Type
607 ' '|| -- Transaction Form
608 l_len_cd|| -- Length of the Transaction
609 ' '|| -- Error Code
610 l_data;
611
612 ELSE
613
614 IF l_cont_flag = l_len_cd THEN
615 l_cont_flag := 9;
616 END IF;
617 l_record := LPAD(l_sequence_number,5,0)||
618 l_cont_flag|| -- Continutuion Flag
619 ' '|| -- Transaction Type
620 ' '|| -- Transaction Form
621 ' '|| -- Length of the Transaction
622 ' '|| -- Error Code
623 l_data;
624
625 END IF;
626
627 l_from := l_from + l_data_per_line;
628 l_cont_flag := l_cont_flag + 1;
629 put_line(l_record);
630
631 END LOOP;
632 l_total_trans := l_total_trans + 1;
633
634 ELSIF cur_transactions_rec.transaction_type IN ('LC','RA','RD','RE','RQ','RX') THEN
635
636 l_cont_flag := 9;
637 l_len_cd := 1;
638 l_info := NULL;
639 --
640 -- For the course element.
641 --
642 IF cur_transactions_rec.program_code IS NOT NULL AND cur_transactions_rec.program_code <> '*' THEN
643 l_info := '*'||cur_transactions_rec.program_code;
644 END IF;
645 IF cur_transactions_rec.campus IS NOT NULL AND cur_transactions_rec.campus <> '*' AND
646 l_info IS NOT NULL THEN
647 l_info := l_info||';'||cur_transactions_rec.campus;
648 END IF;
649
650 --
651 -- For the Point of entry element.
652 -- The Point of entry element is not required for the transaction type 'RE'
653
654 IF cur_transactions_rec.transaction_type NOT IN ('RE','RQ') THEN
655 IF cur_transactions_rec.entry_point IS NOT NULL THEN
656 IF l_info IS NOT NULL THEN
657 l_info := l_info||',!'||cur_transactions_rec.entry_point;
658 ELSE
659 l_info := '!'||cur_transactions_rec.entry_point;
660 END IF;
661 END IF;
662 END IF;
663
664 --
665 -- For the Year/Month element.
666 -- For 'RE' and 'RQ' transactions the 'Entry Month' is not required.
667 --
668 IF cur_transactions_rec.entry_year IS NOT NULL THEN
669 IF l_info IS NOT NULL THEN
670 l_info := l_info||',:'||LPAD(cur_transactions_rec.entry_year,2,0);
671 ELSE
672 l_info := ':'||LPAD(cur_transactions_rec.entry_year,2,0);
673 END IF;
674 END IF;
675
676 IF cur_transactions_rec.transaction_type NOT IN ('RE','RQ') THEN
677 IF cur_transactions_rec.entry_month IS NOT NULL THEN
678 OPEN cur_get_mon(cur_transactions_rec.entry_month);
679 FETCH cur_get_mon INTO l_cur_get_mon;
680 IF l_info IS NOT NULL THEN
681 IF cur_transactions_rec.entry_year IS NOT NULL THEN
682 l_info := l_info||l_cur_get_mon.entry_month;
683 ELSE
684 l_info := l_info||',:'||l_cur_get_mon.entry_month;
685 END IF;
686 ELSE
687 l_info := ':'||l_cur_get_mon.entry_month;
688 END IF;
689 CLOSE cur_get_mon;
690 END IF;
691 END IF;
692
693 IF cur_transactions_rec.transaction_type = 'LC' THEN
694 l_data := LPAD(cur_transactions_rec.app_no,8,0)||l_digital_num||l_choice_no||l_info;
695 ELSIF cur_transactions_rec.transaction_type IN ('RA','RE','RQ') THEN
696 l_data := LPAD(cur_transactions_rec.app_no,8,0)||l_digital_num||l_info;
697 ELSIF cur_transactions_rec.transaction_type IN ('RD','RX') THEN
698 l_data := LPAD(cur_transactions_rec.app_no,8,0)||l_digital_num||l_choice_no||
699 NVL(cur_transactions_rec.decision,' ')||l_info;
700 END IF;
701
702 -- Check the length of the section.
703 check_sec_len(l_len_cd,p_sys_ind);
704
705 l_sequence_number := l_sequence_number + 1;
706 l_record := LPAD((l_sequence_number),5,0)||
707 l_cont_flag|| -- Continutuion Flag --
708 cur_transactions_rec.transaction_type|| -- Transaction Type --
709 ' '|| -- Transaction Form --
710 l_len_cd|| -- Length of the Transaction
711 ' '|| -- Error Code --
712 l_data;
713 put_line(l_record);
714 l_total_trans := l_total_trans + 1;
715
716 ELSIF cur_transactions_rec.transaction_type IN ('LK','RK') THEN
717
718 l_cont_flag := 9;
719 l_len_cd := 1;
720 l_data := LPAD(cur_transactions_rec.app_no,8,0)||l_digital_num||l_choice_no;
721
722 -- Check the length of the section.
723 check_sec_len(l_len_cd, p_sys_ind);
724
725 l_sequence_number := l_sequence_number + 1;
726 l_record := LPAD((l_sequence_number),5,0)||
727 l_cont_flag|| -- Continutuion Flag --
728 cur_transactions_rec.transaction_type|| -- Transaction Type --
729 ' '|| -- Transaction Form --
730 l_len_cd|| -- Length of the Transaction
731 ' '|| -- Error Code --
732 l_data;
733 put_line(l_record);
734 l_total_trans := l_total_trans + 1;
735
736 ELSIF cur_transactions_rec.transaction_type = 'LE' THEN
737
738 l_cont_flag := 9;
739 l_len_cd := 1 ;
740 l_data := NULL ;
741 l_info := NULL ;
742
743 -- Appending the Application Number and Check Digit
744 l_data := LPAD(cur_transactions_rec.app_no,8,0)||l_digital_num ;
745
746 -- Appending the Extra Passport Number and Copy form Required columns
747 -- These columns are stored in the 'Comments_in_offer' column in the first 7 characters
748 l_data := l_data || RPAD(NVL(SUBSTR(cur_transactions_rec.comments_in_offer,1,7),' '),7,' ') ;
749
750 -- Appending the course and campus elements
751 IF cur_transactions_rec.program_code IS NOT NULL AND cur_transactions_rec.program_code <> '*' THEN
752 l_info := '*'||cur_transactions_rec.program_code ;
753 END IF ;
754
755 IF cur_transactions_rec.campus IS NOT NULL AND cur_transactions_rec.campus <> '*' AND l_info IS NOT NULL THEN
756 l_info := l_info||';'||cur_transactions_rec.campus;
757 END IF;
758 l_data := l_data || l_info ;
759
760 -- Check the length of the section.
761 check_sec_len(l_len_cd, p_sys_ind) ;
762
763 l_sequence_number := l_sequence_number + 1 ;
764
765 -- Create the Transaction record
766 l_record :=
767 LPAD((l_sequence_number),5,0)|| -- Sequence Number
768 l_cont_flag|| -- Continutuion Flag
769 cur_transactions_rec.transaction_type|| -- Transaction Type
770 ' '|| -- Transaction Form
771 l_len_cd|| -- Length of the Transaction
772 ' '|| -- Error Code
773 l_data ; -- Trasaction Data
774
775 -- Write the trasaction record,l_record into Flat file
776 put_line(l_record) ;
777
778 -- Increment the number of trasactions records by one
779 l_total_trans := l_total_trans + 1 ;
780
781 ELSIF cur_transactions_rec.transaction_type IN ('PE','RR','RW') THEN
782
783 l_cont_flag := 9;
784 l_len_cd := 1;
785
786 IF cur_transactions_rec.transaction_type IN ('PE','RW') THEN
787 l_data := LPAD(cur_transactions_rec.app_no,8,0)||l_digital_num;
788 ELSIF cur_transactions_rec.transaction_type = 'RR' THEN
789 l_data := LPAD(cur_transactions_rec.app_no,8,0)||l_digital_num||
790 RPAD(NVL(SUBSTR(cur_transactions_rec.comments_in_offer,1,3),' '),3,' ')
791 ||SUBSTR(cur_transactions_rec.comments_in_offer,4,46);
792 END IF;
793
794 -- Check the length of the section.
795 check_sec_len(l_len_cd, p_sys_ind);
796
797 l_sequence_number := l_sequence_number + 1;
798 l_record := LPAD(l_sequence_number,5,0)||
799 l_cont_flag|| -- Continutuion Flag
800 cur_transactions_rec.transaction_type|| -- Transaction Type
801 ' '|| -- Transaction Form
802 l_len_cd|| -- Length of the Transaction
803 ' '|| -- Error Code
804 l_data;
805 put_line(l_record);
806 l_total_trans := l_total_trans + 1;
807
808 END IF; -- MAIN IF
809
810 -- Update the sent_to_ucas column of the current Transaction record to 'Y' as the trnsactions has
811 -- send to UCAS through flat file
812
813 igs_uc_transactions_pkg.update_row (
814 x_mode => 'R',
815 x_rowid => cur_transactions_rec.ROWID,
816 x_uc_tran_id => cur_transactions_rec.UC_Tran_Id,
817 x_transaction_id => cur_transactions_rec.transaction_id,
818 x_datetimestamp => cur_transactions_rec.datetimestamp,
819 x_updater => cur_transactions_rec.updater,
820 x_error_code => cur_transactions_rec.error_code,
821 x_transaction_type => cur_transactions_rec.transaction_type,
822 x_app_no => cur_transactions_rec.app_no,
823 x_choice_no => cur_transactions_rec.choice_no,
824 x_decision => cur_transactions_rec.decision,
825 x_program_code => cur_transactions_rec.program_code,
826 x_campus => cur_transactions_rec.campus,
827 x_entry_month => cur_transactions_rec.entry_month,
828 x_entry_year => cur_transactions_rec.entry_year,
829 x_entry_point => cur_transactions_rec.entry_point,
830 x_soc => cur_transactions_rec.SOC,
831 x_comments_in_offer => cur_transactions_rec.comments_in_offer,
832 x_return1 => cur_transactions_rec.return1,
833 x_return2 => cur_transactions_rec.return2,
834 x_hold_flag => cur_transactions_rec.hold_flag,
835 x_sent_to_ucas => 'Y',
836 x_test_cond_cat => cur_transactions_rec.test_cond_cat,
837 x_test_cond_name => cur_transactions_rec.test_cond_name,
838 x_inst_reference => cur_transactions_rec.inst_reference ,
839 x_auto_generated_flag => cur_transactions_rec.auto_generated_flag,
840 x_system_code => cur_transactions_rec.system_code,
841 x_ucas_cycle => cur_transactions_rec.ucas_cycle,
842 x_modular => cur_transactions_rec.modular,
843 x_part_time => cur_transactions_rec.part_time);
844
845 END LOOP;
846
847 CLOSE cur_transactions;
848
849 --
850 -- Export 'QC' and 'QF' transactions only for FTUG and SWAS Systems
851 --
852 IF p_sys_ind IN ('U','S') AND p_configured_cycle = p_current_cycle THEN
853
854 OPEN cur_uv_crs_vac(p_sys_ind);
855 LOOP
856 FETCH cur_uv_crs_vac INTO l_cur_uv_crs_vac;
857 EXIT WHEN cur_uv_crs_vac%NOTFOUND;
858
859 --
860 -- Processing for the 'QF' Transaction.
861 --
862 l_cont_flag := 9;
863 l_tran_type := 'QF';
864 l_tran_form := 1;
865 l_from := 1;
866 l_len_cd := 1;
867 l_data := RPAD(l_cur_uv_crs_vac.course,6,' ')||l_cur_uv_crs_vac.campus||RPAD(NVL(l_cur_uv_crs_vac.vacancy_status,' '),1,' ');
868 -- Check the length of the section.
869 check_sec_len(l_len_cd, p_sys_ind);
870
871 l_sequence_number := l_sequence_number + 1;
872 l_record := LPAD((l_sequence_number),5,0)||
873 l_cont_flag|| -- Continutuion Flag
874 l_tran_type|| -- Transaction Type
875 l_tran_form|| -- Transaction Form
876 l_len_cd|| -- Length of the Transaction
877 ' '|| -- Error Code
878 l_data;
879 put_line(l_record);
880 l_total_trans := l_total_trans + 1;
881
882 --
883 -- Processing for the 'QC' Transaction.
884 --
885 l_opt_code := NULL;
886 l_data := RPAD(l_cur_uv_crs_vac.course,6,' ')||l_cur_uv_crs_vac.campus||
887 RPAD(NVL(l_cur_uv_crs_vac.vacancy_status,' '),1,' ')||RPAD(NVL(l_cur_uv_crs_vac.no_of_vacancy,' '),2,' ')||
888 LPAD(NVL(l_cur_uv_crs_vac.score,0),2,0);
889 --
890 -- To get the option code for the 'QC' transaction.
891 --
892 OPEN cur_uv_crs_vac_opt(p_sys_ind, l_cur_uv_crs_vac.course, l_cur_uv_crs_vac.campus);
893 LOOP
894 FETCH cur_uv_crs_vac_opt INTO l_cur_uv_crs_vac_opt;
895 EXIT WHEN cur_uv_crs_vac_opt%NOTFOUND;
896 IF NVL(length(l_opt_code),0) <= 198 THEN
897 l_opt_code := l_opt_code||RPAD(l_cur_uv_crs_vac_opt.option_code,2,' ');
898 END IF;
899
900 -- Updating the Course Vacancy Option Record with sent_to_ucas as 'Y'
901 FOR upd_crs_vac_opt_rec IN upd_crs_vac_opt_cur(l_cur_uv_crs_vac_opt.rowid) LOOP
902
903 igs_uc_crse_vac_ops_pkg.update_row (
904 x_mode => 'R',
905 x_rowid => upd_crs_vac_opt_rec.rowid,
906 x_ucas_program_code => upd_crs_vac_opt_rec.ucas_program_code,
907 x_institute => upd_crs_vac_opt_rec.institute,
908 x_ucas_campus => upd_crs_vac_opt_rec.ucas_campus,
909 x_option_code => upd_crs_vac_opt_rec.option_code,
910 x_updater => upd_crs_vac_opt_rec.updater,
911 x_cl_updated => upd_crs_vac_opt_rec.cl_updated,
912 x_cl_date => upd_crs_vac_opt_rec.cl_date,
913 x_vacancy_status => upd_crs_vac_opt_rec.vacancy_status,
914 x_sent_to_ucas => 'Y' ,
915 x_system_code => upd_crs_vac_opt_rec.system_code );
916
917 END LOOP;
918
919 END LOOP;
920
921 CLOSE cur_uv_crs_vac_opt;
922
923 l_tran_type := 'QC';
924 l_tran_form := 3;
925 l_from := 1;
926 l_data_set := l_data||l_opt_code;
927 l_data_per_line := 67;
928 l_len := LENGTH(l_data_set);
929 l_len_cd := NVL(CEIL(l_len/l_data_per_line),0);
930 l_cont_flag := 1;
931 -- Check the length of the section.
932 check_sec_len(l_len_cd, p_sys_ind);
933
934 FOR I IN 1..l_len_cd LOOP
935 l_data := SUBSTR(l_data_set,l_from,l_data_per_line);
936 l_sequence_number := l_sequence_number + 1;
937 IF l_cont_flag = 1 THEN
938 IF l_cont_flag = l_len_cd THEN
939 l_cont_flag := 9;
940 END IF;
941 l_record := LPAD((l_sequence_number),5,0)||
942 l_cont_flag|| -- Continutuion Flag --
943 l_tran_type|| -- Transaction Type --
944 l_tran_form|| -- Transaction Form --
945 l_len_cd|| -- Length of the Transaction --
946 ' '|| -- Error Code --
947 l_data;
948 ELSE
949 IF l_cont_flag = l_len_cd THEN
950 l_cont_flag := 9;
951 END IF;
952 l_record := LPAD((l_sequence_number),5,0)||
953 l_cont_flag|| -- Continutuion Flag --
954 ' '|| -- Transaction Type --
955 ' '|| -- Transaction Form --
956 ' '|| -- Length of the Transaction --
957 ' '|| -- Error Code --
958 l_data;
959 END IF;
960 l_from := l_from + l_data_per_line;
961 l_cont_flag := l_cont_flag + 1;
962 put_line(l_record);
963 END LOOP;
964 l_total_trans := l_total_trans + 1;
965
966 -- Updating the Course Vacancy Record with sent_to_ucas as 'Y'
967 FOR upd_crs_vac_rec IN upd_crs_vac_cur(l_cur_uv_crs_vac.ROWID) LOOP
968
969 igs_uc_crse_dets_pkg.update_row (
970 x_mode => 'R',
971 x_rowid => upd_crs_vac_rec.rowid,
972 x_ucas_program_code => upd_crs_vac_rec.ucas_program_code,
973 x_oss_program_code => upd_crs_vac_rec.oss_program_code,
974 x_oss_program_version => upd_crs_vac_rec.oss_program_version,
975 x_institute => upd_crs_vac_rec.institute,
976 x_uvcourse_updater => upd_crs_vac_rec.uvcourse_updater,
977 x_uvcrsevac_updater => upd_crs_vac_rec.uvcrsevac_updater,
978 x_short_title => upd_crs_vac_rec.short_title,
979 x_long_title => upd_crs_vac_rec.long_title,
980 x_ucas_campus => upd_crs_vac_rec.ucas_campus,
981 x_oss_location => upd_crs_vac_rec.oss_location,
982 x_faculty => upd_crs_vac_rec.faculty,
983 x_total_no_of_seats => upd_crs_vac_rec.total_no_of_seats,
984 x_min_entry_points => upd_crs_vac_rec.min_entry_points,
985 x_max_entry_points => upd_crs_vac_rec.max_entry_points,
986 x_current_validity => upd_crs_vac_rec.current_validity,
987 x_deferred_validity => upd_crs_vac_rec.deferred_validity,
988 x_term_1_start => upd_crs_vac_rec.term_1_start,
989 x_term_1_end => upd_crs_vac_rec.term_1_end,
990 x_term_2_start => upd_crs_vac_rec.term_2_start,
991 x_term_2_end => upd_crs_vac_rec.term_2_end,
992 x_term_3_start => upd_crs_vac_rec.term_3_start,
993 x_term_3_end => upd_crs_vac_rec.term_3_end,
994 x_term_4_start => upd_crs_vac_rec.term_4_start,
995 x_term_4_end => upd_crs_vac_rec.term_4_end,
996 x_cl_updated => upd_crs_vac_rec.cl_updated,
997 x_cl_date => upd_crs_vac_rec.cl_date,
998 x_vacancy_status => upd_crs_vac_rec.vacancy_status,
999 x_no_of_vacancy => upd_crs_vac_rec.no_of_vacancy,
1000 x_score => upd_crs_vac_rec.score,
1001 x_rb_full => upd_crs_vac_rec.rb_full,
1002 x_scot_vac => upd_crs_vac_rec.scot_vac,
1003 x_sent_to_ucas => 'Y',
1004 x_ucas_system_id => upd_crs_vac_rec.ucas_system_id,
1005 x_oss_attendance_type => upd_crs_vac_rec.oss_attendance_type,
1006 x_oss_attendance_mode => upd_crs_vac_rec.oss_attendance_mode,
1007 x_joint_admission_ind => upd_crs_vac_rec.joint_admission_ind,
1008 x_open_extra_ind => upd_crs_vac_rec.open_extra_ind,
1009 x_clearing_options => upd_crs_vac_rec.clearing_options,
1010 x_imported => NVL(upd_crs_vac_rec.imported,'Y'),
1011 x_system_code => upd_crs_vac_rec.system_code ,
1012 x_keywrds_changed => upd_crs_vac_rec.keywrds_changed);
1013
1014 END LOOP;
1015
1016 END LOOP;
1017 CLOSE cur_uv_crs_vac;
1018
1019 END IF; -- End of QC and QF Transactions
1020
1021 --
1022 -- Export 'QA' transactions only for FTUG System
1023 --
1024 IF p_sys_ind IN ('U') AND p_configured_cycle = p_current_cycle THEN
1025
1026 OPEN cur_uv_offr_abb;
1027 LOOP
1028 FETCH cur_uv_offr_abb INTO l_cur_uv_offr_abb;
1029 EXIT WHEN cur_uv_offr_abb%NOTFOUND;
1030
1031 -- Write the Transaction in flat file and update the table with sent_to_ucas as 'Y'
1032 l_cont_flag := 9;
1033 l_tran_type := 'QA';
1034 l_tran_form := 2;
1035 l_len_cd := 1;
1036 l_data := LPAD(l_cur_uv_offr_abb.abbrev_code,2,0)||l_cur_uv_offr_abb.letter_format||
1037 l_cur_uv_offr_abb.summary_char||RPAD(NVL(l_cur_uv_offr_abb.abbrev_text,' '),57,' ');
1038 -- Check the length of the section.
1039 check_sec_len(l_len_cd, p_sys_ind);
1040
1041 l_sequence_number := l_sequence_number + 1;
1042 l_record := LPAD((l_sequence_number),5,0)||
1043 l_cont_flag|| -- Continutuion Flag --
1044 l_tran_type|| -- Transaction Type --
1045 l_tran_form|| -- Transaction Form --
1046 l_len_cd|| -- Length of the Transaction --
1047 ' '|| -- Error Code --
1048 l_data;
1049 put_line(l_record);
1050 l_total_trans := l_total_trans + 1;
1051
1052 -- Update the Offer Abbreviations Record
1053 FOR upd_offr_abb_rec IN upd_offr_abb_cur(l_cur_uv_offr_abb.ROWID) LOOP
1054
1055 igs_uc_ref_off_abrv_pkg.update_row (
1056 x_mode => 'R',
1057 x_rowid => upd_offr_abb_rec.rowid,
1058 x_abbrev_code => upd_offr_abb_rec.abbrev_code,
1059 x_uv_updater => upd_offr_abb_rec.uv_updater,
1060 x_abbrev_text => upd_offr_abb_rec.abbrev_text,
1061 x_letter_format => upd_offr_abb_rec.letter_format,
1062 x_summary_char => upd_offr_abb_rec.summary_char,
1063 x_uncond => upd_offr_abb_rec.uncond,
1064 x_withdrawal => upd_offr_abb_rec.withdrawal,
1065 x_release => upd_offr_abb_rec.release,
1066 x_imported => upd_offr_abb_rec.imported,
1067 x_sent_to_ucas => 'Y',
1068 x_deleted => upd_offr_abb_rec.deleted,
1069 x_tariff => upd_offr_abb_rec.tariff );
1070
1071 END LOOP;
1072
1073 END LOOP;
1074 CLOSE cur_uv_offr_abb;
1075
1076 END IF; -- End of QA Transaction
1077
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1081 fnd_message.set_token('NAME','IGS_UC_EXT_MARVIN.PREPARE_DATA'||' - '||SQLERRM);
1082 igs_ge_msg_stack.ADD;
1083 app_exception.raise_exception;
1084
1085 END prepare_data;
1086
1087
1088 FUNCTION pending_transactions_exist (p_system_code IN VARCHAR2, p_configured_cycle IN NUMBER,
1089 p_current_cycle IN NUMBER, p_current_inst_code IN VARCHAR2)
1090 RETURN BOOLEAN IS
1091 /*
1092 || Created By : ayedubat
1093 || Created On : 03-JUL-2003
1094 || Purpose : To check whether any pending transactions exist for any/particular system
1095 || depending on the value passed to p_system_code parameter.
1096 || Created as part of Multiple Cycles Enhancement, 2669208
1097 || Known limitations, enhancements or remarks :
1098 || Change History :
1099 || WHO WHEN WHAT
1100 || dsridhar 30-SEP-2003 Bug No: 3156212. Code modified to consider only those transactions
1101 || for systems configured as MARVIN. Cursor cur_pending_trans_exist
1102 || changed to consider only MARVIN interface.
1103 || jchakrab 07-Sep-2004 Modified for Bug#3872286 - simplified logic for checking for pending
1104 || transactions for a system
1105 ||
1106 */
1107 -- Cursor to find whether pending transactions exist to send to UCAS
1108 CURSOR cur_pending_trans_exist(cp_system_code IGS_UC_TRANSACTIONS.system_code%TYPE,
1109 cp_config_cycle IGS_UC_TRANSACTIONS.ucas_cycle%TYPE)IS
1110 SELECT 'X'
1111 FROM IGS_UC_TRANSACTIONS
1112 WHERE
1113 system_code = NVL(cp_system_code,system_code) AND
1114 NVL(cp_system_code,system_code) IN (SELECT system_code
1115 FROM igs_uc_cyc_defaults
1116 WHERE ucas_interface = 'M' AND
1117 ucas_cycle = cp_config_cycle) AND
1118 ucas_cycle = cp_config_cycle AND
1119 sent_to_ucas = 'N' AND
1120 hold_flag = 'N' AND
1121 transaction_type NOT IN ('XA','XD');
1122
1123 -- Cursor to find whether any couse vacancy transactiions exist to send to UCAS
1124 CURSOR cur_uv_crs_vac_exist(cp_system_code IGS_UC_CRSE_DETS.system_code%TYPE,
1125 cp_current_inst IGS_UC_CRSE_DETS.institute%TYPE)IS
1126 SELECT 'X'
1127 FROM igs_uc_crse_dets crse
1128 WHERE
1129 ((cp_system_code IS NOT NULL AND system_code = cp_system_code) OR
1130 (cp_system_code IS NULL AND system_code IN ('U','S'))) AND
1131 crse.institute = cp_current_inst AND
1132 crse.sent_to_ucas = 'N' AND
1133 NVL(crse.imported,'Y') = 'Y' ;
1134
1135 -- Cursor to find whether any offer abbreviations exist to send to UCAS
1136 CURSOR cur_uv_offr_abb_exist IS
1137 SELECT 'X'
1138 FROM igs_uc_ref_off_abrv roa
1139 WHERE roa.sent_to_ucas = 'N' AND roa.deleted = 'N';
1140
1141 l_dummy VARCHAR2(1);
1142 l_status BOOLEAN := FALSE;
1143
1144 BEGIN
1145
1146 -- Check whether any Pending Transactions for the Configured Cycle exist
1147 -- i.e. any record with Sent_To_UCAS ='N' and hold_flag ='N'
1148 OPEN cur_pending_trans_exist(p_system_code, p_configured_cycle) ;
1149 FETCH cur_pending_trans_exist INTO l_dummy ;
1150
1151 -- Check whether any Course Vacancies exist for FTUG and SWAS Systems to send to UCAS
1152 OPEN cur_uv_crs_vac_exist(p_system_code, p_current_inst_code);
1153 FETCH cur_uv_crs_vac_exist INTO l_dummy ;
1154
1155 -- Check whether any Offer Abbreviations exist to send to UCAS
1156 OPEN cur_uv_offr_abb_exist ;
1157 FETCH cur_uv_offr_abb_exist INTO l_dummy ;
1158
1159 -- Modified this for Bug#3872286 - simplified logic
1160 IF p_system_code = 'U' THEN
1161 IF ((cur_uv_crs_vac_exist%FOUND OR cur_uv_offr_abb_exist%FOUND) AND p_configured_cycle = p_current_cycle)
1162 OR cur_pending_trans_exist%FOUND THEN
1163 l_status := TRUE;
1164 END IF;
1165
1166 ELSIF p_system_code = 'S' THEN
1167 IF (cur_uv_crs_vac_exist%FOUND AND p_configured_cycle = p_current_cycle)
1168 OR (cur_pending_trans_exist%FOUND) THEN
1169 l_status := TRUE;
1170 END IF;
1171
1172 ELSIF p_system_code IN ('G','N') THEN
1173 IF cur_pending_trans_exist%FOUND THEN
1174 l_status := TRUE;
1175 END IF;
1176
1177 END IF ;
1178
1179 CLOSE cur_pending_trans_exist ;
1180 CLOSE cur_uv_crs_vac_exist ;
1181 CLOSE cur_uv_offr_abb_exist ;
1182
1183 RETURN l_status;
1184
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1188 fnd_message.set_token('NAME','IGS_UC_EXT_MARVIN.PENDING_TRANSACTIONS_EXIST'||' - '||SQLERRM);
1189 igs_ge_msg_stack.ADD;
1190 app_exception.raise_exception;
1191
1192 END pending_transactions_exist;
1193
1194
1195 /*********** Main Procedure called from the Concurrent Manager ****************/
1196
1197 PROCEDURE create_file(errbuf OUT NOCOPY VARCHAR2,
1198 retcode OUT NOCOPY NUMBER ) IS
1199 /*
1200 || Created By : [email protected]
1201 || Created On : 09-APR-2002
1202 || Purpose : This is the main procedure, which will get called from the
1203 || concurrent manager in order to create the flat file.
1204 || Known limitations, enhancements or remarks :
1205 || Change History :
1206 || Who When What
1207 || ayedubat 15-NOV-2002 Added the Logic to loop through all the systems
1208 || supported by the Institution if any pending transactions
1209 || exist to send to UCAS for the bug # 2643048
1210 || ayedubat 24-DEC-2002 Removed the p_directory parameter from the procedure and
1211 || created a new local procedure, create_directory for finding
1212 || the directory name to write the flat file for bug, 2711256
1213 || ayedubat 02-JUL-2003 Changed as part of Multiple Cycles Enhancement, 2669208
1214 || Created a new procedure,pending_transactions_exist to check
1215 || whether any pending transactions exist or not.
1216 || ayedubat 14-JUL-2003 Changed the procedure to make the reserved words ltrim,substr
1217 || to upper case for the same bug,2669208
1218 || ayedubat 16-JUL-2003 Removed the validation to check whether the configured cycle
1219 || in our System is equal to the entryyear in the UCAS for bug,2669208
1220 || dsridhar 30-SEP-2003 Bug No: 3156212. Code modified to consider only those transactions
1221 || for systems configured as MARVIN. Cursor marvin_setup_cur removed.
1222 || Cursor ucas_systems_cur changed to consider only MARVIN interface.
1223 || jchakrab 07-Sep-2004 Modified for Bug#3872286 - simplified logic for checking for pending
1224 || transactions for a system and generating the marvin file
1225 || anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
1226 || (reverse chronological order - newest change first)
1227 */
1228
1229 -- Find the UCAS System Configured Cycle
1230 CURSOR ucas_cycles_cur IS
1231 SELECT MAX(configured_cycle) configured_cycle,
1232 MAX(current_cycle) current_cycle
1233 FROM IGS_UC_DEFAULTS ;
1234 ucas_cycles_rec ucas_cycles_cur%ROWTYPE;
1235
1236 -- Find the Current Institute Code and Security Key
1237 CURSOR cur_inst_code IS
1238 SELECT DISTINCT current_inst_code, ucas_security_key
1239 FROM IGS_UC_DEFAULTS ;
1240
1241 -- Bug No: 3156212. Cursor changed to consider only MARVIN interface.
1242 -- Fetch all the UCAS Systems setup in the System
1243 CURSOR ucas_systems_cur (cp_ucas_cycle igs_uc_cyc_defaults.ucas_cycle%TYPE) IS
1244 SELECT DISTINCT ucd.system_code system_code,
1245 DECODE(ucd.system_code,'U',1,'G',2,'N',3,'S',4,5)
1246 FROM IGS_UC_DEFAULTS ucd, IGS_UC_CYC_DEFAULTS uccd
1247 WHERE ucd.system_code = uccd.system_code AND
1248 uccd.ucas_cycle = cp_ucas_cycle AND
1249 uccd.ucas_interface = 'M'
1250 ORDER BY 2;
1251
1252
1253 l_inst_code igs_uc_defaults.current_inst_code%TYPE;
1254 l_sec_key igs_uc_defaults.ucas_security_key%TYPE;
1255 l_directory VARCHAR2(240) ;
1256 l_dummy ucas_systems_cur%ROWTYPE;
1257 l_trans_exist BOOLEAN := FALSE; -- addded for bug#3872286
1258
1259 BEGIN
1260
1261 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
1262 IGS_GE_GEN_003.SET_ORG_ID;
1263
1264 SAVEPOINT create_file;
1265
1266 -- Initiliaze the local variables
1267 l_file_name := NULL;
1268 l_sec_key := NULL;
1269 l_inst_code := NULL;
1270 l_directory := NULL ;
1271
1272 /******* Check whether flat file can be generated or not ********/
1273
1274 -- Check whether the Current Cycle and Configured Cycles are defined in the UCAS System
1275 OPEN ucas_cycles_cur;
1276 FETCH ucas_cycles_cur INTO ucas_cycles_rec;
1277 CLOSE ucas_cycles_cur;
1278
1279 IF ucas_cycles_rec.configured_cycle IS NULL OR ucas_cycles_rec.current_cycle IS NULL THEN
1280
1281 fnd_message.set_name('IGS','IGS_UC_CYCLE_NOT_FOUND');
1282 errbuf := fnd_message.get;
1283 fnd_file.put_line(fnd_file.LOG,errbuf);
1284 retcode:=2;
1285 RETURN;
1286
1287 END IF;
1288
1289
1290 -- Get the Institution Code and Security Code and check for the validity
1291 OPEN cur_inst_code ;
1292 FETCH cur_inst_code INTO l_inst_code, l_sec_key;
1293 CLOSE cur_inst_code ;
1294
1295 -- If the Security Code or Institution Code is NULL
1296 -- then log the error message and stop the processing.
1297 IF l_inst_code IS NULL OR LENGTH(l_inst_code) > 3 THEN
1298 --
1299 -- Error message: File cannot be created, as the mentioned Institution Code is invalid.
1300 --
1301 fnd_file.put_line(FND_FILE.LOG, ' ');
1302 fnd_message.set_name('IGS','IGS_UC_INV_INST_CD');
1303 errbuf := fnd_message.get;
1304 fnd_file.put_line(fnd_file.LOG, errbuf);
1305 retcode:=2;
1306 RETURN;
1307
1308 ELSIF l_sec_key IS NULL THEN
1309 --
1310 -- Error message: File cannot be created, as the mentioned Security Key is invalid.
1311 --
1312 fnd_file.put_line(FND_FILE.LOG, ' ');
1313 fnd_message.set_name('IGS','IGS_UC_INV_SEQ_KEY');
1314 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
1315 retcode:=2;
1316 RETURN;
1317
1318 END IF;
1319
1320
1321 -- Check whether any of the Systems have defined the Interface Type as Marvin in the Configured Cycle
1322 -- If systems have setup the Marvin Interface in the Configured Cycle,
1323 -- Then Report the Error and stop processing
1324 -- Bug No: 3156212. Replaced the cursor marvin_setup_cur with ucas_systems_cur.
1325 OPEN ucas_systems_cur(ucas_cycles_rec.configured_cycle);
1326 FETCH ucas_systems_cur INTO l_dummy;
1327 IF ucas_systems_cur%NOTFOUND THEN
1328
1329 CLOSE ucas_systems_cur;
1330 fnd_message.set_name('IGS','IGS_UC_NOT_MARV');
1331 errbuf := fnd_message.get;
1332 fnd_file.put_line(FND_FILE.LOG,errbuf);
1333 retcode:=2;
1334 RETURN;
1335
1336 END IF;
1337 CLOSE ucas_systems_cur;
1338
1339
1340 -- Loop through all the systems in the order of UCAS, GTTR, NMAS and SWAS
1341 -- Bug No: 3156212. Added an argument to the cursor ucas_systems_cur.
1342 FOR uacs_systems_rec IN ucas_systems_cur (ucas_cycles_rec.configured_cycle) LOOP
1343
1344 -- Check whether the system have any pending transactions to export
1345 IF pending_transactions_exist(uacs_systems_rec.system_code, ucas_cycles_rec.configured_cycle,
1346 ucas_cycles_rec.current_cycle, l_inst_code ) THEN
1347
1348 IF NOT l_trans_exist THEN
1349 -- Call the local procedure to find the directory name to store the flat file
1350 create_directory ( l_directory );
1351 -- If the directory is NULL, log a message and return
1352 IF l_directory IS NULL THEN
1353 fnd_message.set_name('IGS','IGS_UC_EXP_INVIAD_DIRECTORY');
1354 errbuf := fnd_message.get;
1355 fnd_file.put_line(FND_FILE.LOG,errbuf);
1356 retcode:=2;
1357 RETURN;
1358 END IF ;
1359
1360 /********** Create the flat file name *********/
1361
1362 -- Create the file name to be sent to UCAS
1363 -- A data file is being created as filename : AfinUuu.xxxxxx
1364
1365 l_file_name := 'Afin'||l_inst_code||'.'||l_sec_key;
1366
1367 -- Print the Flat File Name in the log file
1368 fnd_file.put_line(FND_FILE.LOG, ' ');
1369 fnd_message.set_name('IGS','IGS_UC_FILE_NAME');
1370 fnd_message.set_token('FILE_NAME', l_directory||'/'||l_file_name);
1371 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
1372
1373 /******** Writing the data into the flat file ********/
1374
1375 -- Call the local procedure, open_file to open the file
1376 open_file( p_location => l_directory,
1377 p_filename => l_file_name );
1378
1379 l_trans_exist := TRUE;
1380 END IF;
1381
1382 -- Call local procedure to prepare the Header Information
1383 prepare_header(p_inst_code => l_inst_code,
1384 p_sys_ind => uacs_systems_rec.system_code );
1385
1386 -- Call the local procedure, prepare_data to prepare UCAS System Transactions
1387 prepare_data( p_sys_ind => uacs_systems_rec.system_code,
1388 p_configured_cycle => ucas_cycles_rec.configured_cycle,
1389 p_current_cycle => ucas_cycles_rec.current_cycle,
1390 p_current_inst_code => l_inst_code);
1391
1392 -- Call the procedure to prepare the Trailer Information
1393 prepare_trailer(p_inst_code => l_inst_code,
1394 p_sys_ind => uacs_systems_rec.system_code );
1395
1396 END IF;
1397
1398 END LOOP; /* end of writing data for all systems */
1399
1400 -- Check whether any Pending Transactions exist
1401 -- If no system have any pending transactions send to UCAS
1402 -- Display the Warning Message in the log file and exit the process
1403 IF NOT l_trans_exist THEN
1404 fnd_message.set_name('IGS', 'IGS_UC_NO_PENDING_TRANS_EXIST');
1405 errbuf := fnd_message.get;
1406 fnd_file.put_line(FND_FILE.LOG,errbuf);
1407 retcode:=1;
1408 RETURN;
1409 END IF ;
1410
1411
1412 -- Call the local procedure, to close the file.
1413 close_file;
1414
1415 EXCEPTION
1416
1417 WHEN UTL_FILE.INVALID_PATH THEN
1418 ROLLBACK TO create_file;
1419 fnd_message.set_name('IGS', 'IGS_EN_INVALID_PATH');
1420 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
1421
1422 WHEN UTL_FILE.WRITE_ERROR THEN
1423 ROLLBACK TO create_file;
1424 fnd_message.set_name('IGS', 'IGS_EN_WRITE_ERROR');
1425 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
1426
1427 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1428 ROLLBACK TO create_file;
1429 fnd_message.set_name('IGS', 'IGS_EN_INVALID_FILEHANDLE');
1430 fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
1431
1432 WHEN OTHERS THEN
1433 ROLLBACK;
1434 retcode:=2;
1435 fnd_message.set_name( 'IGS','IGS_GE_UNHANDLED_EXP');
1436 fnd_message.set_token('NAME','IGS_UC_EXT_MARVIN.CREATE_FILE'||' - '||SQLERRM);
1437 errbuf := fnd_message.get;
1438 igs_ge_msg_stack.conc_exception_hndl;
1439
1440 END create_file;
1441
1442 END igs_uc_ext_marvin;
[Home] [Help]