finance miscellany bill pearce ventura county ccd
TRANSCRIPT
![Page 1: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/1.jpg)
Finance MiscellanyBill Pearce
Ventura County CCD
![Page 2: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/2.jpg)
2
Topics
• Finance Transaction Summary – Beyond Summarized Posting
• Web-based Cashiering
• Miscellaneous AR Enhancements
• JV Upload via Microsoft Access
![Page 3: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/3.jpg)
3
What you’re about to see…
![Page 4: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/4.jpg)
4
What is a “Bolt-On”
• Enhancement to Banner that has no impact on baseline (well, minimal impact…)
• Can be “bolted on” to future releases easily.
• Rule-driven (GTVSDAX)
• Mods required only if baseline table changes.
![Page 5: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/5.jpg)
5
Finance Transaction Summary – Beyond Summarized Posting
VCCCD used baseline summarized posting since going live in 1998:
• Summarizes selected G/L accounts by posting run
• Hard to determine out-of-balances• Needed local mod to avoid creating OOB’s
(document time stamp, collector table)• Because of short posting interval,
summary was only effective for large feeds
![Page 6: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/6.jpg)
6
Finance Transaction Summary – Beyond Summarized Posting
Local Summarization Process• Baseline summarized posting turned off• Summarizes the same as baseline, but for
the entire month• Process will not run if:
– Already run for the fiscal year/period– Chart, Cash, or Funds are OOB (FGRCTRL,
FGRCASH, FGRTBEX logic)– Period is not Closed
![Page 7: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/7.jpg)
7
Finance Transaction Summary – Beyond Summarized Posting
Local Summarization Process (continued)• Detail transactions are deleted• Saves original transactions for one period• Creates Summary document TSUMyypp in
FGBTRNH, FGBTRND• Updates/replaces sum_post_doc_code in
FGBTRNH, FGBTRND• Produces control report with hash totals• Uses FTMSDAT to define accounts
![Page 8: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/8.jpg)
8
Finance Transaction Summary – Beyond Summarized Posting
Baseline =FGRACTG
Baseline =FGRACTG
![Page 9: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/9.jpg)
9
FYSTSUM.sql 6.0 Testing DB - 11/14/04 November17,2004PEARCE Transaction Summary Process 16:18 Page 1 Chart: 1 Fisc Yr: 05 Period: 05 Document: TSUM0505
*** REPORT CONTROL INFORMATION ***
SEQUENCE NUMBER: 1325688
Chart Code 1
Fiscal Year (YY) 05
Fiscal Period (PP) 05
COUNT/HASH TOTAL OF WORK TABLE TRANSACTIONS: 37132/33623174.61
COUNT/HASH TOTAL OF SUMMARIZED TRANSACTIONS: 1189/33623174.61
ALL HASH TOTALS SHOULD EQUAL
Possible Errors:PROCESS NOT RUN - ALREADY RUN FOR THIS FY/PERD PROCESS NOT RUN - FGRCTRL, FGRCASH, FGRTBEX OOB PROCESS NOT RUN - PERIOD NOT CLOSED
![Page 10: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/10.jpg)
10
Fisc Year TRNH
Before TRNH
After Count
Diff Percent
Diff TRND
Before TRND
After Count
Diff Percent
Diff
99 489 472 -17 -3.48 1,377 758 -619 -44.95
00 961,301 776,734 -184,567 -19.20 1,157,229 971,721 -185,508 -16.03
01 1,140,358 873,989 -266,369 -23.36 1,405,927 1,138,410 -267,517 -19.03
02 1,219,396 946,218 -273,178 -22.40 1,394,753 1,118,035 -276,718 -19.84
03 1,190,779 937,658 -253,121 -21.26 1,339,854 1,086,729 -253,125 -18.89
04 1,079,318 835,043 -244,275 -22.63 1,221,710 977,435 -244,275 -19.99
05 (perd 01-03) 264,017 204,886 -59,131 -22.40 346,885 273,949 -72,936 -21.03
05 (perd 04) 68,358 50,890 -17,468 -25.55 112,077 78,310 -33,767 -30.13
05 (perd 05) 42,614 44,222 1,608 3.77 177,949 57,570 -120,379 -67.65
05 (perd 06) 49,498 50,987 1,489 3.01 189,674 56,739 -132,935 -70.09
Total 6,016,128 4,721,099 -1,295,029 -21.53 7,347,435 5,759,656 -1,587,779 -21.61
![Page 11: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/11.jpg)
11
Web-based Cashiering
Purpose:
• Original idea was to replace separate card-swipers, emulate Student payment
• After original demo, SBO’s suggested adding optional fees
• Evolved into almost total replacement for TSAAREV
![Page 12: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/12.jpg)
12
![Page 13: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/13.jpg)
13
![Page 14: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/14.jpg)
14
![Page 15: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/15.jpg)
15
![Page 16: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/16.jpg)
16
![Page 17: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/17.jpg)
17
![Page 18: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/18.jpg)
18
![Page 19: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/19.jpg)
19
![Page 20: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/20.jpg)
20
![Page 21: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/21.jpg)
21
![Page 22: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/22.jpg)
22
![Page 23: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/23.jpg)
23
![Page 24: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/24.jpg)
24
![Page 25: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/25.jpg)
25
![Page 26: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/26.jpg)
26
![Page 27: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/27.jpg)
27
![Page 28: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/28.jpg)
28
![Page 29: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/29.jpg)
29
![Page 30: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/30.jpg)
30
![Page 31: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/31.jpg)
31
A Couple of Handy Database Triggers
• Automatic release of holds when payment is made
• Calculate Student Center Fee based on Academic Year
![Page 32: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/32.jpg)
32
![Page 33: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/33.jpg)
33
![Page 34: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/34.jpg)
34
![Page 35: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/35.jpg)
35
![Page 36: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/36.jpg)
36
![Page 37: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/37.jpg)
37
![Page 38: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/38.jpg)
38
![Page 39: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/39.jpg)
39
TZRSCFE_PIDM: 2863TZRSCFE_ACYR_CODE: 0304TZRSCFE_TERM_CODE: 200403TZRSCFE_DETAIL_CODE: SCR3TZRSCFE_AMOUNT: 10
TZRSCFE_PIDM: 2863TZRSCFE_ACYR_CODE: 0405TZRSCFE_TERM_CODE: 200407TZRSCFE_DETAIL_CODE: SCR3TZRSCFE_AMOUNT: 8.5
TZRSCFE_PIDM: 2863TZRSCFE_ACYR_CODE: 0405TZRSCFE_TERM_CODE: 200503TZRSCFE_DETAIL_CODE: SCR3TZRSCFE_AMOUNT: 1.5
![Page 40: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/40.jpg)
40
JV Upload via Microsoft Access
• Loads third-party transactions from external systems to journal voucher documents in Banner
• Transactions are extracted in a comma-delimited flat file (.csv)
• CSV file is uploaded by Microsoft Access via an ODBC connection to a locally-defined table, and are processed into the GURFEED table with a sql*plus script
• Processed by FURFEED and FGRTRNI
![Page 41: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/41.jpg)
41
CSV Layout
![Page 42: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/42.jpg)
42
CSV File Conditions
• Doc Code should be 6 characters or less, and can be blank. If entered, will be prefixed with Z1. If blank, will use "J" sequence number
• Doc Ref should be 8 characters or less, and can be blank
• Amount can be signed, include 2 decimal places, no commas
• Debit or Credit Indicator - D or C • Description – max 35 characters• Account Index - can be blank
![Page 43: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/43.jpg)
43
CSV File Conditions (continued)
• Fund - can be blank. Can default either from Account Index or Org
• Org - can be blank. Can default either from Account Index or Fund
• Acct - can be blank. Can default from Account Index
• Prog - can be blank. Can default from Account Index, Fund if non-blank, or Org if non-blank
• Actv - can be blank• Locn - can be blank• Baseline or local rule class
![Page 44: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/44.jpg)
44
MS Access Macro 1…
Loads the CSV transactions using a macro, which:• Clears a local MS Access table (IFTR)• Imports the transactions from the .csv file into
the MS Access table IFTR• Displays the following summary report of the
transactions loaded. This should be checked against the control report from the file extract on the external system before continuing.
![Page 45: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/45.jpg)
45
![Page 46: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/46.jpg)
46
MS Access Macro 2…
Loads the MS Access local table to a Banner table
• Requires sql*plus on the PC• Appends the transactions to the Oracle
table FZRIFTR. • Creates GURFEED rows from any
interfaced transactions that have been loaded in Step 1. The system ID that is used for the feed is IFTR in FTMSDAT.
![Page 47: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/47.jpg)
47
Run the Banner processes…
• FURFEED
• FGRTRNI
• FGRTRNR
AND YOU’RE DONE!
![Page 48: Finance Miscellany Bill Pearce Ventura County CCD](https://reader035.vdocuments.us/reader035/viewer/2022062802/56649e8f5503460f94b9295d/html5/thumbnails/48.jpg)
48
Questions?