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;

SBYC 2020 Wet Wednesday Summer Series #6

Sail Log

Date

Vessel Name/Type/Model

Escape/ J/70

Course/Sea Miles

Sailed dock to dock: mi

Course: nm

Notes

Who sailed or my position on the vessel

Weather

FZUS56 KLOX 081547
CWFLOX
Expires:202007082230;;521637

Coastal Waters Forecast
National Weather Service Los Angeles/Oxnard CA
847 AM PDT Wed Jul 8 2020

Point Piedras Blancas to San Mateo Point CA out 60 NM including the
Channel Islands National Marine Sanctuary


PZZ650-082230-
East Santa Barbara Channel from Pt. Conception to Pt. Mugu CA
including Santa Cruz Island-
847 AM PDT Wed Jul 8 2020

.TODAY...Winds variable 10 kt or less, becoming SW 10 to 15 kt in
the afternoon. Wind waves 2 ft or less. W swell 3 to 5 ft at
9 seconds.
.TONIGHT...W winds 10 to 15 kt, becoming 5 to 10 kt after midnight.
Wind waves 2 ft or less. W swell 2 to 4 ft at 9 seconds.
.THU...Winds variable 10 kt or less, becoming SW 10 to 15 kt in the
afternoon. Wind waves 2 ft or less. W swell 2 to 4 ft at 9 seconds.
Patchy fog in the morning.

Sail Trace (Strava)

Video/Pictures

SBYC 2020 Margarita Series #2

Sail Log

Date

Vessel Name/Type/Model

Escape/ J/70

Course/Sea Miles

Sailed dock to dock: mi

Course: nm

Notes

Who sailed or my position on the vessel

  1. Boat Owner/Skipper
    1. Me

  2. Crew
    1. Jenny

    2. Max

Weather

Santa Barbara to Leo: Weak winds unless the eddy fails to fully develop.

FZUS56 KLOX 051602
CWFLOX

PZZ650-052230-
East Santa Barbara Channel from Pt. Conception to Pt. Mugu CA
including Santa Cruz Island-
902 AM PDT Sun Jul 5 2020

...SMALL CRAFT ADVISORY IN EFFECT FROM 3 PM PDT THIS AFTERNOON
THROUGH LATE TONIGHT...

.TODAY...Winds variable 10 kt or less, becoming W 15 to 20 kt with
local gusts to 25 kt in the afternoon. Combined seas 2 to 4 ft
dominant period 9 seconds, building to 5 to 7 ft dominant period 9
seconds in the afternoon.
.TONIGHT...Western portion, NW winds 15 to 25 kt. Eastern portion, W
winds 5 to 10 kt. Combined seas 6 to 8 ft dominant period 9 seconds.

Sail Trace

Video/Pictures

SBYC Wet Wednesday 2020 Summer 5

Sail Log

Date

Vessel Name/Type/Model

Escape/ J/70

Course/Sea Miles

Sailed dock to dock: 12.94 mi

Course: A-10 5.63 nm

Notes

Who sailed or my position on the vessel

  1. Boat Owner/Skipper
    1. Me

  2. Crew
    1. Erik

    2. Max

Weather

Start: Mostly cloudy, 68°F, Feels like 67°F, Humidity 67%, Wind 8mph from WSW
End: Partly sunny, 67°F, Feels like 67°F, Humidity 70%, Wind 10mph from WSW - by Klimat.app

Sail Trace (Strava)

Race Qs for WetWednesday

Video/Pictures

John 8:7

John 8:7 NABRE But when they continued asking him, he straightened up and said to them, “Let the one among you who is without sin be the first to throw a stone at her.”[1]

References

Pretending it is Wet Wednesday 20200401 (Single Handing again)

Sail Log

Date

April 1, 2020

Vessel Name/Type/Model

Escape J/70

Course/Sea Miles

Sailed dock to dock: 8.07 mi

Notes

Sort of swelly (yes that is a technical term). Light winds. Single handed again.

There is no such thing as social distancing on a J/70. It is single handed or not at all. As one sailor said, it is BYOB (bring your own boat).

Weather

Forecast
WHUS76 KLOX 011611
MWWLOX

PZZ645-020015-
/O.EXT.KLOX.SC.Y.0048.000000T0000Z-200402T1700Z/
Point Piedras Blancas to Point Sal westward out to 10 NM-
911 AM PDT Wed Apr 1 2020

...SMALL CRAFT ADVISORY NOW IN EFFECT UNTIL 10 AM PDT THURSDAY...

* WHAT...Hazardous sea conditions.

* WHERE...Point Piedras Blancas to Point Sal westward out to 10
  NM.

* WHEN...Until 10 AM PDT Thursday.

* IMPACTS...Conditions will be hazardous to small craft.

* ADDITIONAL DETAILS...See the Coastal Waters Forecast (CWFLOX).

PRECAUTIONARY/PREPAREDNESS ACTIONS...

Inexperienced mariners, especially those operating smaller
vessels, should avoid navigating in hazardous conditions.
...SMALL CRAFT ADVISORY IN EFFECT FROM 3 PM PDT THIS AFTERNOON
THROUGH LATE TONIGHT...

.TODAY...Western portion, NW winds 10 to 20 kt becoming 20 to 30 kt
in the afternoon. Eastern portion, S winds 5 to 10 kt becoming SW 10
to 15 kt in the afternoon. Combined seas 3 to 5 ft dominant period 8
seconds, building to 5 to 7 ft in the afternoon.
.TONIGHT...Western portion, NW winds 20 to 30 kt becoming 10 to 20
kt late. Eastern portion, SW winds 10 to 15 kt becoming variable 10
kt or less after midnight. Combined seas 5 to 7 ft dominant period 8
seconds. Patchy fog after midnight.
.THU...N winds 5 to 15 kt , becoming W 15 to 25 kt in the afternoon.
Combined seas 5 to 7 ft dominant period 9 seconds. Patchy fog in the
morning.
Sort of Actual

Start: Sunny, 62°F, Feels like 66°F, Humidity 80%, Wind 12mph from SE

End: Sunny, 63°F, Feels like 65°F, Humidity 75%, Wind 8mph from SE

Sail Trace (Strava)

Video/Pictures

/images/2020/sdwb.thumbnail.jpg

Monday Hooky Single handed sail 20200323

Sail Log

Date

March 23, 2020

Vessel Name/Type/Model

Escape/ J/70

Course/Sea Miles

Sailed dock to dock: mi 7.78

Notes

I was going to take Escape out of the ocean, but weather was nice. Good personal skill building since I had to do all tasks myself. As a boat owner/skipper, it is important to be able to do it all. This is making realise how much I depend on my crew.

Weather

(not available)

Sail Trace (Strava)

/images/2020/sdwb.thumbnail.jpg

Sat 3/21 Single handing goodness

Sail Log

Date

March 21, 2020

Vessel Name/Type/Model

Escape/ J/70

Course/Sea Miles

Sailed dock to dock: 10.12 mi

Notes

With the COVID-19 (Wuhan Virus). Sailing may be over. Except for some very small boats such as harbors, lasers, RSs it is hard to do social distancing.

As I tell my crew, unless you are willing to get cozy, you have no business being on a J/70 while racing.

What was good about this sail was a renewed appreactiation for seamanship, expecting perfection in rigging, boat trim.

I brought up the topic of rigging. I made too mistakes. I did not put the backstay trim under the traverler. Also, the starbord jib sheet (yes, I know the jib sheet is continuous) went throught the rachet block the wrong way.

Who sailed or my position on the vessel

Just me! Single handed the boat.

Weather

FZUS56 KLOX 211504
CWFLOX
Expires:202003212230;;840660

Coastal Waters Forecast
National Weather Service Los Angeles/Oxnard CA
804 AM PDT Sat Mar 21 2020

Point Piedras Blancas to San Mateo Point CA out 60 NM including the
Channel Islands National Marine Sanctuary


PZZ650-212230-
East Santa Barbara Channel from Pt. Conception to Pt. Mugu CA
including Santa Cruz Island-
804 AM PDT Sat Mar 21 2020

.TODAY...Winds variable 10 kt or less, becoming S 10 kt in the
afternoon. Wind waves 2 ft or less. W swell 2 ft at 7 seconds.
.TONIGHT...SW winds 5 to 10 kt, becoming E after midnight. Wind
waves 2 ft or less. W swell 2 ft at 10 seconds. Slight chance of
rain after midnight.

Sail Trace (Strava)

Video/Pictures

/images/2020/sdwb.thumbnail.jpg