1 / 48

Finance Miscellany Bill Pearce Ventura County CCD

Explore enhancements to the finance transaction summary process in Ventura County CCD, including web-based cashiering and JV upload via Microsoft Access.

williamkelly
Download Presentation

Finance Miscellany Bill Pearce Ventura County CCD

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 2005 CONFERENCE April 5th & 6th Finance MiscellanyBill PearceVentura County CCD

  2. Topics • Finance Transaction Summary – Beyond Summarized Posting • Web-based Cashiering • Miscellaneous AR Enhancements • JV Upload via Microsoft Access 3CBG Conference - April 5th & 6th 2005

  3. What you’re about to see…

  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. 3CBG Conference - April 5th & 6th 2005

  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 3CBG Conference - April 5th & 6th 2005

  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 3CBG Conference - April 5th & 6th 2005

  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 3CBG Conference - April 5th & 6th 2005

  8. Finance Transaction Summary – Beyond Summarized Posting Baseline = FGRACTG 3CBG Conference - April 5th & 6th 2005

  9. FYSTSUM.sql 6.0 Testing DB - 11/14/04 November17,2004 PEARCE 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

  10. 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 3CBG Conference - April 5th & 6th 2005

  11. A Couple of Handy Database Triggers • Automatic release of holds when payment is made • Calculate Student Center Fee based on Academic Year 3CBG Conference - April 5th & 6th 2005

  12. TZRSCFE_PIDM: 2863 TZRSCFE_ACYR_CODE: 0304 TZRSCFE_TERM_CODE: 200403 TZRSCFE_DETAIL_CODE: SCR3 TZRSCFE_AMOUNT: 10 TZRSCFE_PIDM: 2863 TZRSCFE_ACYR_CODE: 0405 TZRSCFE_TERM_CODE: 200407 TZRSCFE_DETAIL_CODE: SCR3 TZRSCFE_AMOUNT: 8.5 TZRSCFE_PIDM: 2863 TZRSCFE_ACYR_CODE: 0405 TZRSCFE_TERM_CODE: 200503 TZRSCFE_DETAIL_CODE: SCR3 TZRSCFE_AMOUNT: 1.5

  13. 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 3CBG Conference - April 5th & 6th 2005

  14. CSV Layout 3CBG Conference - April 5th & 6th 2005

  15. 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 3CBG Conference - April 5th & 6th 2005

  16. 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 3CBG Conference - April 5th & 6th 2005

  17. 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. 3CBG Conference - April 5th & 6th 2005

  18. 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. 3CBG Conference - April 5th & 6th 2005

  19. Run the Banner processes… • FURFEED • FGRTRNI • FGRTRNR AND YOU’RE DONE! 3CBG Conference - April 5th & 6th 2005

  20. Questions? 3CBG Conference - April 5th & 6th 2005

More Related