Verba Connect Integration

Michael Ghens

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;

Comments