SVAAPIZ (Academic Year Annualizer) - Irrelevant for Annual
...
Exception CRNs not reported: Actual CRN with no reportable student attendance hours. 202310 First Primary GEOL 151 17092 0 A COS P Geology of the Mojave Desert
Note
Inputted after submission. Local reporting will show it but it was not counted on the Annual 320 submission for apportionment.
202310 First Primary IA 047AD 16813 0 A COS P Strategies of Sport 202310 First Primary IA 049AD 16815 0 A COS P Performance of Sport 202310 First Primary PTA 400 16232 0 A COS P PTA Skills Lab 202310 First Primary PTA 400 16233 0 A COS P PTA Skills Lab 202310 First Primary PTA 400 16234 0 A COS P PTA Skills Lab
Warning
Emailed instructors, cc’d deans in January. Reminded deans February.
New as of Annual file:
202320 Second Primary ENGL 002 28327 D08 A TUL P Logic and Composition
Hours inputted as of . After this report deadline.
202320 Second Primary ESL 440 27062 0 A COS E Advanced Writing/Grammar 5 202320 Second Primary ESL 442 27066 0 A COS E Adv Listening/Speaking 5
...
SEND "*WARNING* Total CH is not equal to (Length Mult * Std CH)" to Vanessa Escobar/Jenae Prator it's about Attendance Method
Jenae assessed .
Jordan Lamb to ask Dean of Enrollment Management about Accounting Method for TBA/Clinical CRNs
Vanessa assessed .
SVRCALP
...
ATTENDANCE FTES* OF STATE RESIDENTS(and Nonresidents Non-Residents Attending Noncredit courses)
...
2. TEST has data w/ most up to date PROD clone (California Student 8.X Installation Information Release 8.17) (Cloned )
Used this instance of Banner for this reporting.
3. DEVL has data w/ older PROD clone date (California Student 8.X Installation Information Release 8.16) (Cloned ) works.
...
Info
Pull from SVRCAL9
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)
...
Expand
title
Helpful Code Snippets to find cross term/fiscal year CRNs
Code Block
language
sql
--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
--NonResident TSCH are showing up for D/ID for a given term
SELECT SSBSECT.SSBSECT_TERM_CODE
,SSBSECT.SSBSECT_CRN
,SSBSECT.SSBSECT_PTRM_END_DATE
,SSBSECT.SSBSECT_CAMP_CODE
,COUNT(SFRSTCR.SFRSTCR_PIDM)
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
INNER JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
ON SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
AND SFRSTCR_CRN = SSBSECT_CRN
WHERE SFRSTCR.SFRSTCR_TERM_CODE = :term_code
AND SSBSECT_PTRM_END_DATE > CONCAT(:YYYYMMDD) --Follow this format 'YYYY-MM-DD' and include the ' '
AND STVRESD.STVRESD_IN_STATE_IND <> 'I'
AND SSBSECT.SSBSECT_ACCT_CODE IN ('D','ID')
AND SSBSECT_CAMP_CODE <> 'COS'
AND SFRSTCR.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND STVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND
ADD CARRY FORWARD from 2022 RECAL
Added to District Totals & Center Totals
FTES Total
Campus/District
Resident
Resident Ext Hours
Non-Resident
Totals
Hanford
17.90
13.20
0.45
31.54
Tulare
187.04
1.67
188.70
District (includes HAC,TCC)
276.97
25.79
2.50
305.26
By Accounting Method
Contact Hours - Daily
Campus/District
Resident
Resident Ext Hours
Non-Resident
Totals
Hanford
6,561.90
181.70
6,743.60
Tulare
5,409.60
52.90
5,462.50
District (includes HAC,TCC)
30,521.50
6,610.20
439.30
37,571.00
Contact Hours - Independent Daily
Campus/District
Resident
Resident Ext Hours
Non-Resident
Totals
Hanford
2,835.00
6,930.00
52.50
9,817.50
Tulare
92,785.00
822.50
93,607.50
District (includes HAC,TCC)
114,887.50
6,930.00
875.00
122,692.50
Expand
title
Census Date or End Date is > 7/1
Info
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
Remove CARRY FORWARD
No Non-Resident TSCH are showing up for D/ID on Term 202330. Wrote query to find if true.
Code Block
language
sql
--NonResident TSCH are showing up for D/ID for a given term
SELECT SSBSECT.
INVOKE
SSBSECT_
DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
GROUP BY
TERM_CODE
SSBSECT_TERM_CODE,
,SSBSECT.SSBSECT_CRN
, SSBSECT_CAMP_CODE,
,SSBSECT.SSBSECT_PTRM_END_DATE
;
ADD CARRY FORWARD from 2022 RECAL
Added to District Totals & Center Totals
...
FTES Total
...
...
...
...
...
Campus/District
...
Resident
...
Resident Ext Hours
...
Non-Resident
...
Totals
...
Hanford
...
17.90
...
13.20
...
0.45
...
31.54
...
Tulare
...
187.04
...
...
1.67
...
188.70
...
District (includes HAC,TCC)
...
276.97
...
25.79
...
2.50
...
305.26
By Accounting Method
...
Contact Hours - Daily
...
Campus/District
...
Resident
...
Resident Ext Hours
...
Non-Resident
...
Totals
...
Hanford
...
6,561.90
...
181.70
...
6,743.60
...
Tulare
...
5,409.60
...
52.90
...
5,462.50
...
District (includes HAC,TCC)
...
30,521.50
...
6,610.20
...
439.30
...
37,571.00
...
Contact Hours - Independent Daily
...
Campus/District
...
Resident
...
Resident Ext Hours
...
Non-Resident
...
Totals
...
Hanford
...
2,835.00
...
6,930.00
...
52.50
...
9,817.50
...
Tulare
...
92,785.00
...
822.50
...
93,607.50
...
District (includes HAC,TCC)
...
114,887.50
...
6,930.00
...
875.00
...
122,692.50
Expand
title
Census Date or End Date is > 7/1
Info
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
Remove CARRY FORWARD
No NonResident TSCH are showing up for D/ID on Term 202330. Write query to find if true.
,SSBSECT.SSBSECT_CAMP_CODE
,COUNT(SFRSTCR.SFRSTCR_PIDM)
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
INNER JOIN COS_DATALAKEHOUSE.DATALAKE.COSBANNERPROD_SATURN_SSBSECT AS SSBSECT
ON SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
AND SFRSTCR_CRN = SSBSECT_CRN
WHERE SFRSTCR.SFRSTCR_TERM_CODE = :term_code
AND SSBSECT_PTRM_END_DATE > CONCAT(:YYYYMMDD) --Follow this format 'YYYY-MM-DD' and include the ' '
AND STVRESD.STVRESD_IN_STATE_IND <> 'I'
AND SSBSECT.SSBSECT_ACCT_CODE IN ('D','ID')
AND SSBSECT_CAMP_CODE <> 'COS'
AND SFRSTCR.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND STVRESD.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
AND SSBSECT.INVOKE_DATA_DATE = DATEADD(DAY, -1, CURRENT_DATE)
GROUP BY SSBSECT_TERM_CODE, SSBSECT_CRN, SSBSECT_CAMP_CODE,SSBSECT_PTRM_END_DATE;
Wasn’t true so I went looking for defects and found one:
California Release 8.17 has the resolution for above defect. COS locally scheduled go live for 8.17 is in August 2023.
Defect didn’t persist in SVRCALD w/ Student Details so that was used to find those allowable Carry Forward sections and TSCH amounts.
FTES difference between 2023 Carry Forward for Non-Resident students (.33 FTES) and last Recal cycle in 2022 (2.7 FTES) is notable, but can’t find any errors w/ that field.
w/ most up to date