Finance Potpourri

1. Finance Potpourri Dave Krajcar Krajcar@sigcorp.com

2. Introduction • Discussion of Tips, Tricks and Traps in Banner Finance/AR. • Some handy SQL scripts that I use for reports and cleanup functions.

3. Trap: Check amount doesn’tmatch invoiced amount • Invoice has multiple sequences but only one paid on the check OR • Check has multiple invoices but this particular invoice was selected and the amount that was paid = zero. • Check for mismatched bank codes –header bank code doesn’t match one or more of the accounting bank codes.

4. Trap: Receiving • Receiving completed – why does invoice still display “R”? • Check the suspense indicator on each sequence of the receiving document – system should not allow these docs to complete but it is BANNER – must use SQL to change the suspense indicator. • Perhaps there were earlier invoices under the receiving limit. Once you hit your threshold, you must post receivings for the previous docs as well.

5. Tip: What table is that anyway? • To identify the table behind a field: • Put your cursor in the field. • Press Ctrl/F1. • What? System doesn’t allow you to click in the field? Try this: • Press Query (F7) and see if you can tab to the desired field; press Ctrl/F1.

6. Trick: JVs, errors and datechanges You have a large JV with errors AND you need to fix (change) the transaction date: FIRST: correct any error sequences; then change the date, the system will then automatically run the edit check on each sequence. If you correct the date first, and then correct any error sequences, you will be forced to touch a record on every sequence beyond the first error as Banner will set subsequent sequence posting indicators to null.

7. Tip: Position Control Related • If you are feeding in Position Control into OPAL, NO ONE should be making position control budget entries – wreaks havoc on the numbers. • Remember to update the foapals in position control when any changes are made to your chart.

8. Trap: Disappearing Documents FURFEED completes successfully, so you run FGRTRNI…the document you saw on the FURFEED output is nowhere to be found… Check the system ID set up on FTVSDAT – likely it is set to reject documents with errors.

9. Trap: More Disappearing Documents You complete a document and it never makes it into approvals….. Try running FORAPPL manually and look for messages about documents not being processed or not having a queue to cover them.

10. Trap: Running multipleinstances of processes (jobs) Wait until the first job has completed before re-running any process/job. For example: if you re-run phpfexp before the first run has finished, you’ll end up with double postings in Finance.

11. TRICK: COA COMPONENTS When building COA components: • roll all effective dates to the future. • work on it. • then roll them back.

12. Tip: Data conversion Liberal use of Microsoft Access for data conversion

13. Tip: COA Hierarchy • Build your chart with “levels” for the various components. All DE components should wind up at the same level, even if you need to create dummy hierarchies.

14. Trick: Encumbrances When all else fails….E032 it!

15. Tip: Approvals Use a “Super Approver” in every queue so that documents never get stuck. Make the Super approver a unique user code.

16. Tip: Reporting Views, views, views for MS access and Oracle reports, including fund/orgn security

17. Tip: Out of Balance? If you are using summary posting, never run more than one copy of FGRCTRL at a time (unless you have the special session mod). If you get out of balance, look in table FGRGENC for any unposted summary entries.

18. Trap: Zero Posting Rule codes – zero posting indicator

19. Trick: Summarized Posting • Get the economy sized TRNDZILLA! • Mod to avoid out of balance

20. Trick: 1099 View for 1099 extract and combined filing, including defect correction

21. Trick: Detail Codes/DCAT Codes Naming and coding conventions: • Allows reports and processes to be written and created without hard coding all appropriate detail codes. • Aging report can be run for a number of detail codes at one time. • DCAT codes and financial aid awards.

22. Trick: Auto-Release of AR Hold • Database trigger – works in all forms of registration (in-person, VR, Web). • Expires hold if balance is zero.

23. Tip: Fabchka Error Resolution Error: No data available for this process (FABCHKA). Check successful completion of previous check processes • Cause: Missing address seq num on a particular inv; check cuts but check number doesn’t get written to fatckdt and fatckin • Update table with check info; insert row into FABINCK • Re-run FABCHKA

24. Tip: Clear Field Function Keys Unlike other keystrokes – Ctrl/Delete will delete the contents of a field EVERY time

25. Trap: Editing baseline rulecodes • Baseline rule code changes: • Keep track of what the original version looked like • Upgrade scripts can overwrite your local mods to Baseline rule codes • Better idea: • Clone baseline rule code • Rename • Then apply your mods

26. Tip: Banner codes in youraccounts (FGIBDST) • Requisitions • REQP - records req'ns • POLQ - subtracts req'ns when PO issued • Purchase Orders • C = committed - from prior year budget • U = uncommitted - current year budget • PORD - when a PO issued • CORD - for a change order • INEI-ENC relieves the PO encumbrance

27. Banner codes (cont’d) • E032 - closes the PO • E020 - reduction for invoices paid on DDR • Invoices • INNI - DDR style invoice • INEI - invoice payment on a PO • INNC - cancel DDR style invoice • INEC - cancel invoice payment on a PO • ISSU - warehouse, parts, printing exp.

28. Banner codes (cont’d) • Checks • DNNI - DDR checks (no $ effect) • DNEI - PO checks (no $ effect) • CNNI - cancel DDR check (no $ effect) • CNEI - cancel PO check (no $ effect) • Budget • BD01 - your initial budget • BD02 - your budget adjustments • J020 - budget carry forward

29. Banner codes (cont’d) • Miscellaneous • CR05 - revenues coming in • JE15 - moves expenses, revenues • EX01- expenses paid via monthly JE

30. Tip: Daily Control Reports • FGRCTRL, FGRTBEX, FGRTOFR FGRCASH, FGRBIEX, TGRRCON • Monitor all control reports DAILY to make sure everything is staying in balance. • Any discrepancies need to be found and corrected immediately.

31. Trick: Advance Query How to use the Advanced Query to find blanks • Press F7 to query. • Enter a colon ( : ) in the desired field field. • Press F8. This opens a Query/Where window. • In that window, type " : is null ". • Press F10 or OK button.

32. Tip: Read • Release guides • Upgrade guides • Handbooks provided

33. Tip: Report Selection Dates The selection dates on reports may define a period, not necessarily the as-of date you enter.

34. Tip: Budget Periods • Budget period is different from posting period. • Posting period updates accounted budget. • Change BDxx rule codes to default budget period if you have annual budget.

35. Tip: “Orphan” Requisition Items • Create a special Vendor Code • Assign “Orphan” Req items to a PO for this vendor each month • Reduce the item to 1 and cost to .01, and change the accounting to a purchasing FOAPAL to free up Req. • Complete, post and liquidate with E032 (canceling will re-open req items)

36. Tip: Do you really need term-based detail codes? If you’re only using them because of deferred revenue, consider creating a deferral journal at year-end. Much simpler!

37. Tip: Accounting on detail codes NEVER change account codes behind an AR detail code after transactions have been fed for that detail code. Use the effective dates instead.

38. Trick(y): Installment plans When a student pays off their balance early, that payment will be applied to the balance of the installment plan charges. The tricky part comes in when producing bills. While the student may have a zero balance, TSRCBIL will show the future dated charge transactions as a future balance but with a zero amount due. This prompts phone calls from parents. To get around that, you could choose not to print the future charges (parameters 19 and 20 on TSRCBIL).

39. Trick: Use Defaults Liberal use of defaults in FOAPAL’s, bank codes, etc. will reduce keying time and errors.

40. Trick: Scripts in Job Submission Get the mods that allow your site developed scripts and Oracle reports to run under job submission. Provides easy way to distribute new reports. Uses Banner Security.

41. Trick(s): Useful SQL Scripts • acct_det.sql = lists AR transaction details associated with the prompted finance feed number. • appl_det.sql = lists AR application of payment details with the prompted finance feed number. • depo_det.sql = shows deposits

42. Trick(s): Useful SQL Scripts cont. • misd_det.sql = lists miscellaneous details with the prompted finance feed number. • fysdbko.sql = create delete commands for fgrbako. Run/audit prior to BAVL rebuild process.

43. Trick(s): Useful SQL Scripts cont. • fysdprg.sql = Purges incomplete/ unapproved documents with a transaction date less than or equal to the parameter date. Also deletes orphan rows for documents. Checks documents to be deleted against FGBTRNH, and will not delete if a row exists.

44. Trick(s): Useful SQL Scripts cont. • fysbyoc.sql = Lists orgn codes not assigned to buyers on FTMBUYR. • fysclze.sql = Close zero balance encumbrances. • fyscpjv.sql = clones posted journal, allows for reversal and new date.

45. Trick(s): Useful SQL Scripts cont. • fyserau.sql = checks for errors (bad FOAPAL, wtg approval, etc.) before encumb roll. • fyslqnp.sql = create EOY trans to close blanket PO’s. • fyslqpo.sql = create EOY trans to close all open PO’s.

46. Trick(s): Useful SQL Scripts cont. • fysopiv.sql = open invoice report for balancing. • fyspoby.sql = Lists incomplete PO’s by buyer. • fysvnda.sql = list vendors and addresses if payments in fy over certain amount.

47. Trick(s): Useful SQL Scripts cont. • tysaudt.sql = List AR balances for students within fund/account. Used to audit balances in accounts that shouldn't have balances (i.e. refund receivables, BOGG payments, etc.) • tysfabl.sql = Prints A/R balances within Fund and Account for a specified as-of-date.

48. Trick(s): Useful SQL Scripts cont. • tysmspk.sql = lists missing parking decals by term. • tysmwho.sql = lists who entered a misc. receipt on TFAMISC. • tysofbl.sql = This program will offset balances in student accounts by term within limits.

49. Trick(s): Useful SQL Scripts cont. • tysunap.sql = Unapply fees for students with credit balances by term. • tysdetl.sql = List activity for specified students, detail codes, keying user and/or term code.

50. Tip: COTOP Chancellor’s Office Tax Offset Program • List students eligible for COTOP • Mailers to eligible students • File to state