Runs

Preliminary: Dec 19, 2024

Run 1: Dec 20, 2024

Certified:

https://sequoias.atlassian.net/browse/AP-517

Setup

Evaluate Banner Setup for Known Defects

PD0016062 - CALB 320 SVRCALD Resident and Non Resident Contact Hours for Actual Part IV do not match to SVRCALS Contact Hours.

WORKAROUND: Use SVRCALS numbers for reporting to Chancellor's Office.

Becomes an issue if we need to do any deeper analysis.

PD0017242 - SVRCALU CCFS 320 Supplemental Report Centers Section needs additional sorting to facilitate entry into the Chancellor's Office Reporting Site.

P1 doesn’t need Center information, so we have dodged this.

PD0017246 - SVRCALU 320 Supplemental Report Centers Sections includes sections not in a Center when Disp entries with zero values is Y.

P1 doesn’t need Center information, so we have dodged this.

GUAABOT vs. Customer Center

Run SSASECT Incorrect Reporting Year - Argos Web Viewer by Evisions (cos.edu) and resolve any errors for CRNs that aren’t represented correctly. Watch for double reporting.

Completed by @Jordan Lamb Dec 19, 2024. 202430 had records but those were previous decisions to push forward FTES.

Baseline Reports

SVAAPIZ (Academic Year Annualizer) - Irrelevant for Annual

Populate Academic Year

District: 561

Reporting Period:

P1 - 2 (Historically)

P2 - 1 (Historically)

BOTH the 320 report out of Banner AND the CO website use the annualizers to multiply the FTES portions of the report. They do this independently but follow the same logic for Positive/Actual Attendance.

The annualizers do not multiply against the Contact Hours which is what was throwing me for a loop.

Decide on an annualizer for each of these:

Weekly Census:
Daily Census:
Actual Noncredit:
Actual Credit:
Independent Study Weekly Census:
Independent Study Daily Census:
Independent Study Noncredit:
Noncredit Enhanced Funding:

Then process it all the same way and both systems do the Contact Hours to FTES calculations for you.

SVRCALX

Runtime ~5 minutes

04 – Include Exceptions - "Y"

Sort through the list and resolve errors prior to next step.

Where SSTS code <> A it is safe to ignore. Where SSTS code = A, it might be nice to inform Scheduling Coordinator (or related schedulers) if they want to Cancel/Inactive/etc. or delete prior to term roll.

W/IW (Weekly/Independent Weekly) is only allowed for full, primary term sections.

202430 Intersession NURS 220 30327 0 N COS P Perioperative Nursing

202510 First Primary ENGL 001 16310 D23 C TUL P College Reading & Composition

202510 First Primary ENGL 001 16311 D24 C TUL P College Reading & Composition

202510 First Primary GEOL 153 17796 0 C COS P Granite, Glaciers, and Gold

202510 First Primary GEOL 153 17938 0 A COS P Granite, Glaciers, and Gold

202510 First Primary HLTH 405 15173 0 C COS P Rehabilitation Aide

202510 First Primary LA 425 17453 0 A TCC E Supervised Tutoring

202510 First Primary LA 425 17454 0 A HAC E Supervised Tutoring

202510 First Primary NURS 143 17290 0 R COS P Concepts of Pediatric Nursing

202510 First Primary NURS 143 17291 0 R COS P Concepts of Pediatric Nursing

202510 First Primary PTA 400 16232 0 A COS P PTA Skills Lab

202510 First Primary PTA 400 17451 0 N COS P PTA Skills Lab

https://sequoias.atlassian.net/browse/AP-512

Students w/ DR registration code do not have grades. Confirmed w/ Velia 1/6/2022.

SFAALST is a helpful screen.

https://sequoias.atlassian.net/browse/AP-513

SFAALST is a helpful screen.

https://sequoias.atlassian.net/browse/AP-514

SVRCALD (Student Details and Non)

04 (Include Student Details) - Y

Runtime ~1 minutes

04 (Include Student Details) - N

Runtime ~6 minutes

I wonder why no Student Details takes longer?

Good to have for backup. Not necessary for subsequent Banner reports/extracts.

05 (Standard or Apprenticeship Rpt) - S

As of Jul 7, 2023 we do not have Apprenticeship courses.

SEND "*WARNING* Total CH is not equal to (Length Mult * Std CH)" to Vanessa Escobar (Non- Nursing) / Jenae Prator (Nursing) it's about Attendance Method

202510 1 AGTC 201 17889 (small issue with .5 contact hour needed for lab)

SVRCALP

Runtime <1 minutes

(PE, Concurrent)

04 – Exceptions Only - "Y"

05 – Page break – just formatting

Send “*WARNING* Concurrent enrollment is greater than 10%.” to Regina

202510 1 DANC 070 16700 0 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 N Y Y N E
202510 1 PEAC 021 14264 01 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 N Y Y N E
202510 1 PEAC 021 14824 03 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 Y Y Y N E
202510 1 PEAC 052 16833 0 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 N Y Y N E
202510 1 PEAC 064 17076 0 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 N Y Y N E

202510 1 PEAC 070 16683 0 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 N Y Y N E
202510 1 PEAC 074 14385 09 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 N Y Y N E
202510 1 PEAC 075 17469 0 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 N Y Y N E
202510 1 PEAC 075 17470 0 COS W 07 12-AUG-2024 26-AUG-2024 13-DEC-2024 17.50 2.000 35.000 N Y Y N E

SVRCALU

04 - Disp entries with zero values “N”

05 - Standard Rpt “S”

Runtime 7-10 minutes

SVRCALS

04 - Standard Rpt “S”

Runtime 7-10 minutes

SVRCAL9 (Resident Codes of A, B, D as of 2023)

Runtime ~5 minutes

Regarding drops. Sometimes Argos doesn't show dropped students but Banner does. Work with Velia but usually Banner is king.

Compare summary to previous Annual

Way different. Don’t know if it’s because of using the Banner annualizers.

Chancellor’s Office: CCFS-320 Reporting Portal

Form 320 Login (cccco.edu)

COLLEGE REPORTS

SVRCALU feeds "Supplemental"

ATTENDANCE FTES* OF STATE RESIDENTS(and Non-Residents Attending Noncredit courses)

SVRCALS feeds Flex-Time Activities | F Factor

DISTRICT REPORTS

Part IX

Pull from SVRCAL9 for AB 540 and Special Admit

Pull from SVRCALD (with Apprenticeship Parameter 05 = 'A') for Apprenticeship

NON CREDIT, NON-RESIDENT

Part IV (in college forms) (see SVRCALS to see values prior to combining)

CDCP section (page has a short timeout. save as you go)

SVRCALU had a defect this time where multiple lines showed up and had different section counts. Hypothesis is that it duplicated by center codes. Verified that the sections needed summing but the duplicative FTES amounts did not. Fun!

SVRCALU populates this.

For noncredit FTES (including CDCP noncredit) the attendance of both residents and nonresidents is reported in the Residents column of Part IV and in the CDCP section of the report. - Natalie Wagner (CO)

COCI drives the CDCP portion.

Manual Edits

Documented in Backup Spreadsheet but tracked here.

Add the Tutorial Services FTES.

Jira ticket to load the same into Banner after 320 deadline.https://sequoias.atlassian.net/browse/AP-986

Look for crossover PS and make sure they are included.

Probably into Summer Intersession (the leading/first one)

Past example:

TERM	CRN	SUBJ_CODE	CRSE_NUMB	ACCT_CODE	RESIDENT Total Contact	Non Resident Total Contact	In 2023-24 Annual?
202420	25484	PS	200M1	P	20,644.75	1286.25	N

--Cross Term Positive Attendance Sections
SELECT DISTINCT
            SSBSECT_TERM_CODE
            ,SSBSECT.SSBSECT_CRN
            ,SSBSECT.SSBSECT_SUBJ_CODE
            ,SSBSECT.SSBSECT_CRSE_NUMB
            ,SSBSECT.SSBSECT_ACCT_CODE
            ,SSBSECT_ENRL
            ,TO_VARCHAR(SSBSECT_PTRM_END_DATE, 'YYYY-MM-DD') AS SSBSECT_PTRM_END_DATE
FROM        COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
JOIN        COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVTERM ON SSBSECT_TERM_CODE = STVTERM_CODE
JOIN        COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVACCT ON SSBSECT_ACCT_CODE = STVACCT_CODE
WHERE       SSBSECT_PTRM_END_DATE > STVTERM_END_DATE
    AND     STVACCT_ACTUAL_IND = 'Y'
    AND     SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
    AND     SSBSECT_TERM_CODE = :term_code
ORDER BY    SSBSECT_TERM_CODE DESC
;

--Positive Attendance Sections where course ends in Fiscal Year (320 Reporting Cycle) different than STVTERM_END_DATE
SELECT DISTINCT
    SSBSECT.SSBSECT_TERM_CODE,
    SSBSECT.SSBSECT_CRN,
    SSBSECT.SSBSECT_SUBJ_CODE,
    SSBSECT.SSBSECT_CRSE_NUMB,
    SSBSECT.SSBSECT_ENRL,
    SSBSECT_PTRM_CODE,
    SSBSECT_ACCT_CODE,
    TO_VARCHAR(SSBSECT_PTRM_END_DATE, 'YYYY-MM-DD') AS SSBSECT_PTRM_END_DATE,
    STVTERM_ACYR_CODE
FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVTERM
    ON SSBSECT_TERM_CODE = STVTERM_CODE
JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVACCT
    ON SSBSECT_ACCT_CODE = STVACCT_CODE
WHERE SSBSECT_PTRM_END_DATE > (
    SELECT (FTVFSYR_END_DATE)
    FROM COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_FIMSMGR_FTVFSYR
    WHERE FTVFSYR_FSYR_CODE = :fiscal_year
        AND INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
)
    AND STVACCT_ACTUAL_IND = 'Y'
    AND SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
--    AND STVTERM_ACYR_CODE < CONCAT('20', :fiscal_year)+'1' --Removes future CRNs based off their future Academic Year
--    AND SSBSECT_TERM_CODE < CONCAT('20', :fiscal_year,'30') --Removes trailing summer that is included in future 320 cycles
--    AND SSBSECT_TERM_CODE = :term_code
ORDER BY SSBSECT_TERM_CODE DESC;
;

--Registration activity and contact hours w/ CA residency
SELECT          SFRSTCR.*
                ,RESIDENT.*
FROM            COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SFRSTCR SFRSTCR
LEFT JOIN       DEV_DB.JOSHUAME.FV_DEMO_GET_RESIDENCY RESIDENT ON SFRSTCR.SFRSTCR_PIDM = RESIDENT.PIDM
    AND         SFRSTCR.SFRSTCR_TERM_CODE = RESIDENT.TERM_CODE
LEFT JOIN       COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVRESD AS STVRESD
    ON RESIDENT.SGBSTDN_RESD_CODE = STVRESD_CODE
WHERE           SFRSTCR.SFRSTCR_TERM_CODE = :term_code
AND             SFRSTCR.SFRSTCR_CRN = :CRN
AND             SFRSTCR.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND             STVRESD.STVRESD_IN_STATE_IND = 'I'
AND             STVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE);

--Sum of contact hours divided by 525 for FTES amount
SELECT          SUM(SFRSTCR.SFRSTCR_ATTEND_HR)/525
FROM            COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SFRSTCR AS SFRSTCR
LEFT JOIN       DEV_DB.JOSHUAME.FV_DEMO_GET_RESIDENCY RESIDENT
    ON SFRSTCR.SFRSTCR_PIDM = RESIDENT.PIDM
           AND SFRSTCR.SFRSTCR_TERM_CODE = RESIDENT.TERM_CODE
LEFT JOIN       COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_STVRESD AS STVRESD
    ON RESIDENT.SGBSTDN_RESD_CODE = STVRESD_CODE
WHERE           SFRSTCR.SFRSTCR_TERM_CODE = :term_code
AND             SFRSTCR.SFRSTCR_CRN = :CRN
AND             SFRSTCR.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND             STVRESD.STVRESD_IN_STATE_IND = 'I'
AND             STVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE);

--D or ID ACCT_CODE for NC Sections
SELECT DISTINCT
            SSBSECT_TERM_CODE
            ,SSBSECT.SSBSECT_CRN
            ,SSBSECT.SSBSECT_SUBJ_CODE
            ,SSBSECT.SSBSECT_CRSE_NUMB
            ,SSBSECT.SSBSECT_ACCT_CODE
            ,SSBSECT_ENRL
FROM        COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
WHERE       SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
    AND     SSBSECT_TERM_CODE = :term_code
    AND     SSBSECT_ACCT_CODE IN ('D','ID')
    AND     SSBSECT_CRSE_NUMB LIKE '4%'
ORDER BY    SSBSECT_TERM_CODE DESC

SQL Update statement to change the Reporting Year SSBSECT_ACYR_CODE the trailing Summer.

Get list of CRNs from Dean of Enrollment Management

Not relevant for P1.

Sorry for the delay on this. I’m inclined to say these Alternative Attendance Accounting Procedure-Daily Census courses also have the summer shift reporting option. Title 5 section 58010(a) states “Full-time equivalent student for courses using census procedure may be reported in either the fiscal year in which the census day procedure is completed or in which the course ends.” I know that this provision does not apply to positive attendance courses (those must be reported in the period in which the course ends), however, I do not see any reason that it would not apply to courses on the alternative attendance accounting procedure- daily census.

As is the case with regular daily census courses, you would want to make sure that appropriate records are maintained to show that FTES were claimed appropriately and not claimed in both fiscal years.

-Natalie Wagner 7/15/2021

Functional

Workspace 2024-25, P1