GES Trip Report Template s2

GES Trip Report Template s2

Finance Technical Training Class Notes

Class Notes for Finance Tech Training

Product Table Owners

General GENERAL

General Person SATURN

Finance FIMSMGR

Accounts Receivable TAISMGR

Position Control POSNCTL

Payroll PAYROLL

Student SATURN

Financial Aid FAISMGR

Alumni ALUMNI

Security BANSECR

REMINDER: The Reports and Processes Chapter of the General Technical Reference Manual discusses 3 critical tools.

1. Generating a Data Element Dictionary (DED) with GURPDED.pc. The DED lists each table and each field on each table with a description as to the use of the field. This report is critical in understanding the tables in Banner for modification and support purposes.

2. Generating a report of all HELP Documentation with GURHELP.pc.

3. Generating a report of all Reports and their parameters with GJRRPTS.pc

In addition to the tools listed above, you can use the gindex.sql script stored in the /banner/general/plus directory to generate a report of Indexes. Index knowledge is critical when writing reports and in passing/identifying key parameters to increase reporting performance. In the later releases of Banner, a listing for each product is delivered along with the script as findex.txt or index.txt within the doc directory.

GURDDOC.sql gives you a listing of all functions, packages, and procedures following standard documentation nomenclature. Finance and AR are the only two products at this time guaranteed to follow this format.

Banner General Person

Ø SPRIDEN Table: Name data, multiple rows, but only one “active” where spriden_change_ind is null.

Ø SPRADDR Table: Address data, multiple rows identified by Address Types and sequence numbers. Telephone information is obsolete in this table. 1 to Many relationship between SPRIDEN and SPRADDR. PIDM being the key link.

Ø SPRTELE Table: Telephone data, multiple rows, may be related to SPRADDR by Address Type. 1 to Many relationship between SPRIDEN and SPRTELE. PIDM being the key link.

Banner Finance System Tables and other Prerequisite Knowledge

Ø We discussed the Vendor Relationships in Finance since the Vendor table FTVVEND is linked to the SPRIDEN table via a PIDM

Ø Key System Forms/Tables

FOASYSC/FOBSYSC - Finance System Control Form. This is where you set System wide global controls within Finance.

Approvals Processing Indicator Values

Y Bypass Approvals

I Implicit Approvals

N Explicit Approvals

FTMSDAT/FTVSDAT - System Data Validation Form - a "Table of Tables"

FTMRUCL/FTVRUCL/FTVEDIT/FTVRULP - define rule class codes used by the posting engine, fgractg.pc.

FOASEQN/FOBSEQN - define one-up starting sequence numbers for Finance documents and other miscellaneous sequence numbers.

FOAFSEQ/FOBFSEQ – Define one-up starting sequence numbers to use for Finance Interfaces through gurfeed only this uses a two character prefix.

FTMDTYP/FTVDTYP - This is where the document type sequence number is initially stored for each type of document. You should memorize the most commons documents type used:

1 REQ Requisition

2 PO Purchase Order

3 INV Invoices and Credit Memos

5 CCK Canceled Checks

8 CHK Checks

20 JV Journal Vouchers

25 ENC Encumbrances

60 FAA Fixed Asset Accounting Adjustments

74 SUM General Ledger Summary Records

80 DCR Direct Cash Receipts

90 EOC Encumbrance Open/Close Document

The Entity Translation Tables may assist in conversions and interfaces, and act as a place of reference for personnel who remember their legacy values but not the new Banner Chart codes. One may want to consider using these tables as a cross reference for legacy codes to Banner Finance codes. Since this is not really documented anywhere in the User’s Manual in detail, I have included the setup in the tables as an example:

1. On the External Entity Code Maintenance Form (FTMEENT), define a four-byte code for your legacy system, ‘XWLK’ for example. You only need to do this once for your crosswalk. This populates the FTVEENT table. In this form and all following forms, make sure you use the same effective date. Best to pre-date to the date you are pre-dating on your Chart.

2. On the Internal Element Code Maintenance Form (FTMEELC), define each type of Banner Finance Chart Element you will be trying to crosswalk. There are only 5 valid values. Set one up for the value of ‘FUND’, one for the value of ‘ORGN’, one for the value of ‘ACCT’, and one for the value of ‘PROG’. Again, you will only need to do this once. This populates table FTVEELC putting ‘FUND’ into ftveelc_eelc_code. Same for ‘ORGN’, ‘ACCT’, and ‘PROG’.

3. Next you have two choices. On the External Report Code Maintenance Form (FTMEELI) you can enter all of your legacy codes manually or chose to populate the table directly (ftveeli). Put one in manually online so you can ‘see’ what fields you will need to populate. If your G/L code from Legacy actually represents a combination of Fund and Organization in Banner, then you will have to enter it 2 different times, associating it once with the Internal Element code of ‘FUND’ and once with ‘ORGN’. Thus let’s say your acct is 2-33000. Unrestricted funds Controller’s office. Then it would be represented in the table where

Ftveeli_eent_code = ‘XWLK and

Ftveeli_eelc_code = ‘FUND’ and

Ftveeli_eeli_code = ‘233000’ = Unrestricted - Controller’s Office

Then

Ftveeli_eent_code = ‘XWLK’ and

Ftveeli_eelc_code = ‘ORGN’ and

Ftveeli_eeli_code = ‘233000’

Or

Ftveeli_eent_code = ‘XWLK and

Ftveeli_eelc_code = ‘FUND’ and

Ftveeli_eeli_code = ‘260000’ = Unrestricted - President’s Office

Then

Ftveeli_eent_code = ‘XWLK’ and

Ftveeli_eelc_code = ‘ORGN’ and

Ftveeli_eeli_code = ‘260000’

OR

Ftveeli_eent_code = ‘XWLK and

Ftveeli_eelc_code = ‘FUND’ and

Ftveeli_eeli_code = ‘262000’ = Unrestricted - IT Office

Then

Ftveeli_eent_code = ‘XWLK’ and

Ftveeli_eelc_code = ‘ORGN’ and

Ftveeli_eeli_code = ‘262000’

You might create an extract out of your legacy system and use SQL@Loader to populate the FTVEELI table as you can hardcode all values except your legacy code, which is represented in the ftveeli_eeli_code field.

4. Then you have two more choices. On the External Report Translation Code Maintenance Form (FTMEELT) you can manually enter each Banner Finance Code that relates the Legacy Code by type of internal element. Or, you can use a spreadsheet to load the FTVEELT table via SQL@Loader. At the end you would need to ensure that each of the codes were valid, that is why it is preferable to enter the codes online as the system checks this for you. Ultimately a representation would be a follows:

Ftveelt_eent_code = ‘XWLK’ and

Ftveelt_eelc_code = ‘FUND’ and

Ftveelt_eeli_code = ‘233000’ and

Ftveelt_eelt_code = ‘1110’

Then

Ftveeli_eent_code = ‘XWLK’ and

Ftveeli_eelc_code = ‘ORGN’ and

Ftveeli_eeli_code = ‘233000’ and

Ftveelt_eelt_code = ‘11102’

OR

Ftveelt_eent_code = ‘XWLK’ and

Ftveelt_eelc_code = ‘FUND’ and

Ftveelt_eeli_code = ‘260000’ and

Ftveelt_eelt_code = ‘1110’

Then

Ftveeli_eent_code = ‘XWLK’ and

Ftveeli_eelc_code = ‘ORGN’ and

Ftveeli_eeli_code = ‘260000’ and

Ftveelt_eelt_code = ‘10’

Etc.

Now you have successfully populated the crosswalk table. The only tables you need to access for your interfaces or conversions are the FTVEELT table to crosswalk your Legacy Codes to the Banner Codes. The other advantage is that your end users can use the online FTVEELT form, query on an old Legacy value, and see what the new Banner Value(s) are. This is just one example of how you could use these tables.

The basic tables behind Chart of Accounts are: FTVCOAS, FTVFUND, FTVFTYP, FTVORGN, FTVACCT, FTVATYP, FTVPROG, FTVACTV, and FTVLOCN. Remember that the nchg_date represents the most current effective dated record when the date = 31-DEC-2099. There are several tables left in Finance that do not use the 31-DEC-2099 and instead use a null for the next change date. Some of them are FTVTGRP (Tax Group Table), FTVACTL (Control Account Table), FOBSYSC (System Control Table), FTVSDAT (System Data Maintenance Table), FTVDEPR (Depreciation Codes Maintenance), FORAQUS (Approval Queue Definition Table). Also the Entity Translation Tables FTVEENT, FTVEELI, FTVEENC, FTVEELT.

How Effective Date Processing works in detail with the Effective Date, Next Change Date and Timestamps is critical for data integrity. We used the Program Codes to demonstrate how effective date processing works. Just remember that you can never change an effective dated record, you can only create a new record with the same code and a new effective date to "simulate" a change. That is why you learn to perform an INSERT/DUPLICATE (F6/F4) for your changes on Effective Dated forms.

Information in the Fiscal Year Maintenance Form (FTMFSYR supported by tables FTVFSYR and FTVFSPD) and how it works with the transaction date of documents to set the posting period and fiscal year.

Key Ledger Tables

FGBTRNH: Transaction History Table; source accounting data that posting uses

FGBTRND: Transaction Detail Table; explosion of accounting from posting results and the largest table in Banner Finance

FGBGENL: General Ledger; records assets, liabilities, control accounts, and fund balance

FGBOPAL: Operating Ledger

FGBENCP: Encumbrance Ledger

FRRGRNL: Grant Ledger (just like FGBOPAL only based on Grant Year not Fiscal Year)

FGBBAVL: Budget Availability Ledger

FGRBAKO: Budget Availability Posting Backout - where unposted documents are recorded.

General Notes on Ledgers

No “open the year/period” processes - the fiscal year/period table is manually populated

All Ledger Tables (ENCH/ENCD exceptions) manipulated by the Finance posting process (fgractg). Also, FGRGENL, the general ledger roll process directly updates the fgbtrnh, fgbtrnd, and fgbgenl tables.

Non-Banner access to tables should be read-only

The General Ledger Tables form the basis of most reporting requirements

Finance High Level Security Tables

FOBSYSC Finance Global System Control Table

FOBPROF (FOMPROF): the User Profile Maintenance Form:

Invoice Restrictions Indicator Values (since one can never remember them all)

A All

D Direct Pay Invoices only

G General Encumbrances only

P Purchase Orders only

R Purchase Orders and General Encumbrances

E Direct Pay Invoices and General Encumbrances

F Direct Pay Invoices and Purchase Orders

Null No Invoices

Rule Group Security Tables

FTVRUCL Rule Class Validation

FTVEDIT Rule Class Edits

FTVRULP Rule Class Processes

FTVRUGR Rule Group Definition

FTVRGRC Rule Class to Rule Group Security

FTVRRGH Rule Class to Rule Group History (when records removed)

FORPRRG Process/Form to Rule Group Security

FORPRGH Process/Form to Rule Group History (when records removed)

FORUSRG User to Rule Group Security

FORURGH User to Rule Group Security History (when records removed)

NOTE: This still may not be fixed. If using rule group security, you will need to add the delivered rule group DCRG onto process FGADCSR on form FOMPRRG. Also, 3 new rule classes need to be added to the encumbrance rule group. POPN, POCL, RQCL need to be added to ENCG on from FOMRGRC. Then add the ENCG rule group to form FPAEOCD on form FOMPRRG. Or create a new rule group EOCG and add the new rule classes to them.

Fund/Orgn Security

FORUSFN User to Fund/Fund Type Security

FORUFNH User to Fund/Fund Type History (when records removed)

FORUSOR User to Organization Security

FORUORH User to Organization History (when records removed)

Approval Tables

FTVAPPQ Approval Queue Definition

FORAQUS Approval Queue Users

FORAQRC Approval Queue Routings

FOBUAPP Unapproved Documents

FOBAINP Approvals in Progress

FOBAPPH Approval History

FOBAPPD Approved Documents

Requisition Document Tables

FPBREQH Requisition Header

FPRREQD Requisition Detail

FPRREQA Requisition Accounting

FPRRQTX Requisition Taxes

Agreement Tables

FPBAGRH Agreement Header – associated with vendor FTVVEND

FPRAGRD Agreement Detail – associated with commodity FTVCOMM

Purchase Order Document Tables

FPBPOHD Purchase Order Header

FPRPODT Purchase Order Detail

FPRPODA Purchase Order Accounting

FPRPOXT Purchase Order Commodity Tax Table

FTVRQPO Request/PO Relationship

Fixed Assets and Document Adjustment Tables

FFBMAST Asset Master

FFRMASF Asset Accounting Funding Source

FFRMASA Asset Capitalization and Depreciation Accounting

FFRDEPR Asset Depreciation History

FFBADJH Fixed Assets Adjustments Header

FFRADJD Fixed Assets Adjustments Detail

FFRADJA Fixed Assets Adjustments Accounting

FFREXTC Fixed Assets Origination Tag Extraction Collector (not in ERD)

Purchase Order Open Close Document Tables

FPBEOCD Encumbrance Open Close Document Header

FPREOCD Encumbrance Open Close Document Item

FPREOCC Encumbrance Open Close Document Purchase Order/Req Item Table

FPREOCA Encumbrance Open Close Document Accounting Detail Table

Just a note, the FPREOCD_ENCD_TYPE field on the table FPREOCD is not really used for anything based upon my investigations. So please ignore that field when troubleshooting.

Invoice Document Tables

FABINVH Invoice Header

FARINVC Invoice Detail

FARINVA Invoice Accounting

FARINTX Invoice Taxes

Check Document Tables

FABCHKS Check Summary

FABINCK Invoice to Check Relationship

FABCHKA Check/Invoice Detail

FAB1099 1099 Tax Detail

Check Temporary Tables

FATCKNO Batch Check Number Temporary Table

FATCKDT Batch Check/Invoice Detail Temporary Table

FATCKIN Batch Check/Invoice Temporary Table

Journal Voucher Tables

FGBJVCH Journal Voucher Header

FGBJVCD Journal Voucher Detail

Direct Cash Receipt Tables

FGBCSHH Direct Cash Receipt Header

FGBCSHD Direct Cash Receipt Detail

FGRCSHD Direct Cash Receipt Tax and Rebate Table

General Encumbrance Tables

FGBENCH: Encumbrance Header - confusing fields are listed here as a reminder.

Fgbench_status_ind - represents whether encumbrance closed or open

(C)losed

(O)pen

Fgbench_status - represents whether document is completed or incomplete

(C)omplete

(I)ncomplete or null

FGBENCD: Encumbrance Detail

Fgbencd_status_ind - represents if the encumbrance item is postable or in error.

(P)ostable

(E)rror

Fgbencd_status - represents whether encumbrance closed or open

(C)losed

(O)pen

Key Trouble Shooting Areas

Invoices and Checks: Set up a script as follows to review entire posting history of an Invoice. This allows one to determine if an invoice has had a check posted to it as the invoice number is populated into the document reference number when a check is used to pay the invoice. The check number will be the document number.

select fgbtrnh_doc_seq_code DT,

fgbtrnh_doc_code DOC,

fgbtrnh_fsyr_code FY,

fgbtrnh_posting_period PP,

fgbtrnh_reversal_ind R,

fgbtrnh_submission_number SB,

fgbtrnh_item_num ITEM,

fgbtrnh_seq_num SQN,

fgbtrnh_rucl_code RUCL,

fgbtrnh_bank_code BK,

fgbtrnh_trans_amt AMT,

fgbtrnh_dr_cr_ind D,

fgbtrnh_doc_ref_num DOCREF

from fgbtrnh

where fgbtrnh_doc_code = ‘&inv_code’

or fgbtrnh_doc_ref_num = ‘&inv_code’

order by fgbtrnh_doc_seq_code,

fgbtrnh_doc_code,

fgbtrnh_item_num,

fgbtrnh_seq_num;

Then use the check document number to access FABINCK to review the history of the check and whether or not the tables have it listed as canceled. Use the submission number to determine if this may have been a recurring payable invoice. Check the reversal indicator to determine if invoice was canceled or check was canceled. Review doc_seq_code to see if this is the invoice (3), check (8) or canceled check (5). Add more fields to display if necessary. NOTE: invoices established in the prior fiscal year, canceled in the current fiscal year and reestablished in the prior fiscal will cause and out of balance condition in your control report for accounts payable.

Encumbrance: Set up a script as follows to review entire posting history of an Encumbrance.

select fgbtrnh_doc_seq_code DT,

fgbtrnh_doc_code DOC,

fgbtrnh_fsyr_code FY,

fgbtrnh_posting_period PP,

fgbtrnh_reversal_ind R,

fgbtrnh_submission_number SB,

fgbtrnh_item_num ITEM,

fgbtrnh_seq_num SQN,

fgbtrnh_rucl_code RUCL,

fgbtrnh_trans_amt AMT,

fgbtrnh_dr_cr_ind D,

fgbtrnh_encd_num ENC,

fgbtrnh_encd_item_num EITEM,

fgbtrnh_encd_seq_num ESQN

from fgbtrnh

where fgbtrnh_encd_num = ‘&enc’

order by fgbtrnh_doc_seq_code,

fgbtrnh_doc_code,

fgbtrnh_item_num,

fgbtrnh_seq_num;

COMMENTS:

MATERIALS TO READ:

The most important chapters to read in the General Technical Reference Manual are Chapter 1 and Chapter 3. As a Finance technical support person, you should also read the Finance Technical Reference Manual and the Processing Chapter of the Banner Finance Users manual. I recommend that all personnel working in Finance read the Processing Chapter of the Banner Finance User’s manual to gain a base understanding of the Finance product. This can apply to anyone in general who desires to become more knowledgeable within Finance. There are many tools that are well documented in the manuals. Since I have only listed key tables in this class notes report, please use the Banner Finance Technical Training PowerPoint presentation as reference material. There is a tremendous amount of information within this presentation that you will be able to search for and utilize.