Verba Connect Integration
I think it would not hurt to start posting some of the work I do for my employer. Nothing will be company confidential. There is almost nothing company confidential when you work for a public institution with a few exceptions of FERPA, PII, student records, etc.
One of the tasks that I was asked to do is to integrate data from the SBCC BANNER ERP system with Verba's Inclusive Access system. The project was simple.
Create a population-based on student enrolment
Create a sample file
Create key file for sftp
automate
Currently, I have to do is automate in ISE. This will have to wait till next week when the move Banner to Oracle Cloud project is done.
This is the code that I create (in a procedure for work, this so I can run it manually).
BTW, should this be in a GitHub gist or this good enough? I like GitHub, but there is a part of me that prefers to keep things local.
declare --set up output parameters gbcn_outfilepath CONSTANT VARCHAR2(64) := 'SBCC_OUTGOING'; v_file_name_s VARCHAR2(64) := 'ia_enrollment' || '.csv'; v_fh_s utl_file.file_type; v_outbuf VARCHAR2(4000); v_cat_count INT(2) := 0; CURSOR c_students IS SELECT sfrstcr_term_code catalog_name, ssbsect_subj_code department_name, ssbsect_crse_numb course_number, sfrstcr_crn section_code, gzf_get_id(sfrstcr_pidm) student_identifier, gzf_format_name(sfrstcr_pidm,'PREFF') student_first_name, gzf_format_name(sfrstcr_pidm,'L') student_last_name, gzf_email_addr(sfrstcr_pidm,'SBCC') student_email_address FROM sfrstcr INNER JOIN stvrsts ON sfrstcr_rsts_code = stvrsts_code LEFT OUTER JOIN ssbsect ON ( ssbsect_term_code = sfrstcr_term_code AND ssbsect_crn = sfrstcr_crn ) WHERE sfrstcr_term_code IN ( SELECT stvterm_code FROM stvterm WHERE stvterm_code <> '999999' AND stvterm_end_date > SYSDATE INTERSECT SELECT goriccr_value FROM goriccr WHERE goriccr_icsn_code = 'ACTIVE_TERM' ); BEGIN -- open file v_fh_s := utl_file.fopen(gbcn_outfilepath,v_file_name_s,'w'); --header record utl_file.put_line(v_fh_s,'"catalog_name","department_name","course_number","section_code","student_identifier","student_first_name","student_last_name","student_email_address"'); --write data FOR v_students IN c_students LOOP v_outbuf := '"' || v_students.catalog_name || '",' || '"' || v_students.department_name || '",' || '"' || v_students.course_number || '",' || '"' || v_students.section_code || '",' || '"' || v_students.student_identifier || '",' || '"' || v_students.student_first_name || '",' || '"' || v_students.student_last_name || '",' || '"' || v_students.student_email_address || '"'; utl_file.put_line(v_fh_s,v_outbuf); END LOOP; --close file utl_file.fclose(v_fh_s); end;
