Finance 8.10 Technical Release Manual

Banner FinanceTRM SupplementRelease 8.10, Revision 1September 2014/nBanner®, Colleague®, Luminis® and Datatel® ar e trademarks of Ellucian or its affiliat es and are registered in the U.S. and othe r countries. EllucianŽ, PowerCampusŽ, AdvanceŽ, Degree WorksŽ, fsaATLASŽ, Course SignalsŽ, Smar tCallŽ, RecruiterŽ, and ILPŽ are trademarks of Ellucian Company L.P. or its affiliates. Other names may be trademar ks of their respective owners. ©1999-2014 Ellucian Company L.P. and its affiliates. The unauthorized possession, use, reproduction, distribution, display or d isclosure of this material or the information contained herein is prohibited. Contains confidential and proprietary information of Ellucian and its subsidiaries. Use of these materials is limited to Elluci an licensees, and is subject to the terms and conditions of one or more written li cense agreements between Ellucian and the licensee in question. In preparing and providing this publicati on, Ellucian is not rendering legal, accounti ng, or other similar professional service s. Ellucian makes no claims that an institution's use of this publication or the soft ware for which it is provided will guarantee compliance with ap plicable federal or state laws, rules, or regulations. Each organizati on should seek legal, accounting and other similar professional services from compe tent providers of the organization's own choosing. Prepared by: Ellucian 4375 Fair Lakes Court Fairfax, Virginia 22033 United States of America Revision History Publication DateSummary August 2014New version that supports Banner Finance 8.10 software. September 2014Added information for the Receiving/Matching Process (FABMATC). /nContentsBanner Finance 8.10TRM SupplementSeptember 2014 Banner Finance 8.10 iii TRM Supplement ContentsChapter 1Reports and Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1-1 Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1Accounts Payable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1 Budget Development . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-3 Cost Accounting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4 Fixed Assets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-6 General Ledger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-7 Purchasing and Procurement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-13 Stores Inventory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-15 Financial Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-18 Position Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-19 Archive/Purge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-21 Research Accounting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-21 Endowment Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-29 Grant Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-30 Miscellaneous . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-30 Comprehensive Annual Financial Reports (CAFR) . . . . . . . . . . . . . . . . . . 1-30 Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-33 FGBTRND Performance Improvements . . . . . . . . . . . . . . . . . . . . . . . . 1-33 FGRBAVL Rebuild Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-34 FGRGRBD Grant Rebuild Process . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-38 Deferred Grant Account ing Process (FRRGRNT) . . . . . . . . . . . . . . . . . . 1-40 Transaction Interface Process (FGRTRNI) . . . . . . . . . . . . . . . . . . . . . . 1-41 Posting Process (FGRACTG) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-41 NIGP Tape Load Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-44 Finance Check Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-46 /nivBanner Finance 8.10 September 2014 TRM Supplement Contents Receiving/Matching Process (FABMATC) . . . . . . . . . . . . . . . . . . . . . . . 1-47 Year-End Processing Checklist . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-49 Archive/Purge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-50 Reports and Processes Matrix . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-59 Reports and Processes Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . 1-59 Running Oracle Reports with Finance . . . . . . . . . . . . . . . . . . . . . . . . 1-68 SQLPlus Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-69 Sleep/Wake-up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-70 Chapter 2Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2-1 Interfaces with External User Systems . . . . . . . . . . . . . . . . . . . . . . . 2-1 Interfaces within Banner . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-1 Chapter 3Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3-1 Conversion Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1 General Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1 General Ledger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2 Accounts Payable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3 Purchasing and Procurement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-4 General Financial Utilities Š Us er Profile/Security/Approvals . . . . . . . . . . . . 3-5 Budget and Position Control Š Budget Development . . . . . . . . . . . . . . . . 3-5 Endowments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-6 Fixed Assets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-7 Cost Accounting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-8 Stores Inventory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-9 Chapter 4APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4-1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4- 1APIs and Supporting Packages used in Banner . . . . . . . . . . . . . . . . . . 4-1 API Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-3 /nSeptember 2014 Banner Finance 8.10 vTRM Supplement ContentsOther API Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-11 Support Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-14 Validation Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-15 Types of APIs Used for Invoicing . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-16 Business Entity APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-16 Business Process APIs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-18 Chapter 5System-Required Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5-1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5- 1Finance Tables, Group 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2 Finance Tables, Group 1 - System-Required Rows . . . . . . . . . . . . . . . . . . 5-3 Finance Tables, Group 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-11 /nviBanner Finance 8.10 September 2014 TRM Supplement Contents /nSeptember 2014 Banner Finance 8.10 1-1TRM Supplement Reports and Processes 1Reports and Processes This chapter lists the Finance reports a nd processes by module, and provides a brief description of each. The Reports and Processe s matrix provides information about when each report/process can be run. Information about SQL Scripts that help you in producing summary reports is also provided here. NoteFor a detailed description of each report and its parameters, generate the List of Reports and Parameters (GJ RRPTS). This report also includes batch processes. Reports Accounts Payable Report Identifier Report Name Report DescriptionFAB10991099 Forms PrintCreates 1099 forms for selected vendors to report to the IRS. FAB199K1099-K Forms ReportCreates 1099-K forms (Merchant Card and Third Party Network Payments) for selected vendors to report to the IRS. FABCHKDDisbursement Check Report Lists checks you wrote to disbursing agents. FABCHKPCheck Print ProcessPr ints out selected checks. FABCHKRBatch Check Register Report Shows a hardcopy output generated after you execute Batch Check Print processing (FABCHKP), and before you execute the Batch Check Accounting Process (FABCHKA). The Batch Check Register Report contains a listing of only those check numbers that you prin t during the FABCHKP process. FABMATCReceiving Matching Process Matches invoice to recei ving information, and produces an audit report. /n1-2Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FAM10991099 Tape Summary Audit ReportBuilds the magnetic 1099 tape file and a summary audit report. FAPCDIRCanadian Direct Deposit File Creation Process Allows you to create a file of direct deposit transactions to send to your bank/clearing house. FAPDIRDDirect Deposit File Creation Process Creates a file of direct deposit transactions that you will send to your bank/clearing house. FAPTREGDirect Deposit Transmittal Register Prints the transmittal register that will accompany the direct depos it file to the bank. FARAAGEBank Reconciliation- Activity Aging ReportShows outstanding checks or deposits less than 30 days to over 120 days. FARBBALBank Reconciliation Balance ReportShows the bank reconciliation.FARBRECBank Reconciliation Report Shows the actual bank reconciliation items.FARCHKRCheck Register ReportLists checks by check number and date. FARCSHRCash Requirements Report Displays the predicted cash requirements for your installation's banks based on accounts payable data. FARDIRDDirect Deposit Advance Print Process Prints out advice notices for selected direct deposit disbursement. FARIAGEInvoice Aging Report Displays unpaid invoices in date sequence. Invoice totals appear in 30-day periods. FARINVSInvoice Selection ReportDisplays checks to print on next check run. FARIRECInvoices Awaiting Receiver Report Lists invoice, commodity, and receiver information for invoices awaiting the final receiving/matching process. FAROINVOpen Invoice ReportDetails the invoices and credit memos that are open based on the fiAs of Datefl selected by the user. FARVALPVendor Alphabetical Listing Report Lists vendors in alphabetical order. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-3TRM Supplement Reports and Processes Budget Development FARVHSTVendor History ReportDisplays payment activity for a particular activity. FARVNUMVendor Numerical Listing Report Lists vendors in numerical order. FARWHLD1099 Reporting/ Withholding Status ReportIndicates the federal and state government withholding tax and reporting data by vendor, for purchases which are subject to the IRS 1099 reporting requirements. FARWHLY1099 Reporting/ Withholding Audit Report Indicates the federal and state government withholding tax and reporting data by vendor, for purchases which are subject to the IRS 1099 reporting requirements. You can also use this report to verify the accuracy of distributions made to each vendor with income type.FABCHKACheck Accounting Process Takes all check information and creates accounting data which posts in the next posting run. FABCHKS Batch Check ProcessSelects the checks and information that pertains to the current check job. FATCHKS Check Test Pattern Prints out a test check on special forms. FAT10991099 Test PatternPrints out a test 1099. Report Identifier Report Name Report DescriptionFBRAPPDApproved Distributed Budget Report Displays the distributed budget by period, the current year budget, prior year budget, and the variance of the current year to the budget year. FBRAPPRApproved Budget Report Displays the approved phase budget along with the prior year, current year, and the variance of the approved budget to the current amounts. Report Identifier Report Name Report Description/n1-4Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Cost Accounting FBRBDBBBudget Build ProcessBuilds or changes line items for a budget phase. You can create/update line items from another budget phase or from the operating ledger. You can also delete line items by account for fund type. Create the parameters that you use to execute this process on the Budget Process Control Parameters Form (FBABPRC). FBRBDDSBudget Distribution Summary Shows a summary of the offline process you use to distribute amou nts from the budget line item table into peri odic amounts in the distribution table.FBRFEEDFinance Budget Feed Process Moves Position Control Budget information from the Position Control Module to the Finance Budget Tables. FBRMCHGBudget Mass Change Process Changes budget line items by an amount or a percentage. Create the parameters that you use to execute this process on the Mass Budget Change Form (FBAMCHG). FBRWKSHBudget Worksheet Report Displays up to three budget phases with current year and prior year budget amounts from the budget file. FBRBDRLBudget Roll General Accounting Program Rolls budget amounts into the Finance ledgers. Displays parameters and totals inserted into FGBTRNI.Report Identifier Report Name Report DescriptionFCBBILLCost Accounting Billing Report Prints a summary of all billing charges by project and by project rate type for the current billing cycle.FCBEQPTEquipment Rate Rental Calculation Report Displays the results of the equipment rental rate calculations for all employees and equipment within an organization. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-5TRM Supplement Reports and Processes FCBINVTInventory Handling Rate Calculation Report Displays the result of the inventory handling rate calculation for all employee and inventory items associated with an organization. FCBLABRLabor Rate Calculation ReportDisplays results for the calculation of a direct or indirect labor rate for employees within an organization associated with classification codes. Labor rates are printed for each rate code.FCBMATLMaterial Management Rate Calculation ReportDisplays the results of the material management rate calculation process for all employees and material management accounts associated with an organization. FCRBDTRBilling Detail ReportPrints billing details of projects by cost type and rate code. FCRSCHDC/A Rate Schedule ReportPrints all internal and external rates within each rate type by chart of accounts and organization code. FCRVARAC/A Variance Analysis Report Displays the variance between actual and estimated costs by rate type for each employee defined to a cost accounting organization. Report Identifier Report Name Report Description/n1-6Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Fixed Assets Report Identifier Report Name Report DescriptionFFPDEPRDepreciation ProcessCalculates depreciation for any open periods from the Last Depreciation Date up to the Depreciation Date you specify in the process parameters. A variety of parameters enable you to limit the records that will be considered for the process. FFPOEXTFixed Asset Origination Tag Extraction ProcessProvides a consistent method to create origination tag entries for the Asset Master record from the Procurement module and prevents origination ta gs from being generated before the invoice has been approved or when a transaction rolls back in posting or matching process. The report created in this process functions as an audit trail which describes the Origination tags to be created. FFRAGRPFixed Asset/Asset Group Report Selects a specified asset master record or a group of asset master records that are associated as attachments or components. This report shows all of the data elements which appear in the FFRMAST report. FFRDTGAFixed Asset DTAG Aging Report Displays the aging of Pending Dtags as of a given cutoff date. Information displays on the report by document type (Journal Voucher, Direct Cash Receipts, Stores Issues) and provides a breakdown of the account over different periods. FFRDTGTFixed Asset DTAG Transaction Report Displays Dtag transaction information as specified in the para meters of the report. FFRMASTFixed Asset Master Record ReportProduces a printed record of asset master details, funding source information, capitalization information, and depreciation information appearing on the Asset Master record for selected assets. /nSeptember 2014 Banner Finance 8.10 1-7TRM Supplement Reports and Processes General Ledger FFRPROCFixed Asset Procurement Report Produces a single-line printed display of the procurement and payabl e information related to an asset master record for each selected asset. FFRPROPFixed Asset Property ReportUsed when taking a physical inventory of your fixed and moveable assets. It produces a single-line display of the responsible party information from the Asset Master record (FFBMAST) for each selected asset. Report Identifier Report Name Report DescriptionFGPDROLFiscal Period Document Roll Allows completed documents that are still pending for posting in an expiring period to be rolled to a new period. FGPGEXTData Extract ProcessExtract s appropriate information from the General Ledger (FGBGENL) and Operating Ledger (FGBOPAL) tables to a new table (FGWREPT).FGPOCFPOpen Close Fiscal Period Provides the ability to open and close fiscal periods when it is not po ssible to do so using the Fiscal Year Maintenance Form (FTMFSYR). FGRACCIAccount Index ReportDisplays account index information by Account Index code for a specific chart of accounts. FGRACTGPosting ProcessGenerates the po stings to the financial ledgers for those transactions that the system edited, completed, and approv ed. You may execute this process using a sleep/wake routine. This is the only program that updates finance ledgers. FGRACTHAccount Hierarchy ReportDisplays the hierarchi cal structure of the account codes by account types for a specific chart of accounts. Report Identifier Report Name Report Description/n1-8Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FGRACTVActivity Codes Re portHardcopy report so rted by activity code. Displays activity code information within a specific chart of accounts. FGRBAVLAvailable Balance Rebuild ProcessRebuilds the Budget Availability Table when necessary. The system b ases the budget rebuild process on the entire FOAPAL distribution for that budget. FGRGRBDGrant Rebuild Proc essRebuilds the Grant Ledger Table (FRRGRNL) when necessary. The FGRGRBD process also makes extensive updates to the database since it deletes and rebuild s the entire FRRGRNL table.FGRBDRLEnd-of-Year Budget Carry Forward ReportHardcopy report of the remaining budget (prior year) balances that the system carries forward into the current fiscal year. FGRBDSCBudget Status (Current Period) ReportHardcopy report sorted by account within organization. FGRBLSHBalance Sheet ReportHardcopy report sorted by account within fund.FGRBIEXBank Cash Interfund Account Balance Exception Report Balances the cash interfund account for each bank fund to the cash in terfund accounts of the funds which have a claim on the bank fund when run in summary mode. In detail mode, the report prints each document which caused the exception condition.FGRCASHBank Interfund Account Control Report Balances cash interfund account for each bank fund to the interfund acco unts of the funds that have a claim on the bank fund. FGRCOBSCombined Balance Sheet-All Fund Types and Account Groups ReportDisplays a balance sheet for all fund types and account groups. FGRCREFCombined Statement of Revenues, Expenditures, and Changes in Fund Balance ReportDisplays fund type values for all governmental fund types and values for expendable trust funds for revenues, ex penditures and changes. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-9TRM Supplement Reports and Processes FGRCSBACombined Statement of Revenues, Expenditures, and Changes in Fund Balance ReportDisplays budgeted and actual amounts including variances. FGRCSRECombined Statement of Revenues, Expenses, and Changes in Retained Earnings/ Fund Balance Report Displays amounts for all proprietary fund types and trust funds for a specified account level.FGRCBSRCombining Balance Sheets Report Displays values for fund type and fund levels with totals for both current and prior years.FGRCGBSComparative Balance Sheets Report Displays values for a sp ecified fund type for assets, liabilities, and fund balances within the current and prior years. FGRCSSRGeneral Fund Combining Statement of Revenues, Expenditures, and Changes in Fund Balance ReportDisplays values for a sp ecified fund type and totals for both current and prior years. FGRCSCFComparative Statements of Revenues, Expenditures, and Changes in Fund Balances Report Displays values for a sp ecified fund type for current and prior years. FGRCGBAComparative Statements of Revenues, Expenditures, and Changes in Fund Balances Report Š Budgeted and Actual Reports budget and budget values as well as favorable or unfavorable variances. FGRCHFBStatement of Changes in Fund Balance ReportHardcopy report sorted by fund by organization. Report Identifier Report Name Report Description/n1-10 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FGRCHNAStatement of Changes in Net Assets Report In conjunction with FGRCUNA, fulfills the Financial Accounting Standards Board™s requirements for a Statem ent of Activities. The primary purpose of this report is to provide relevant information about the effects of transactions that change the amount and nature of net assets. FGRCLOPClose Operating Accounts ReportClosing the operating ledger for the fiscal year. FGRCSRPCash Receipts ReportPrints header and detail information for selected cash receipts. FGRCSRP can be used to print comprehensive information for one or more cash receipt based on the Document Number, Transaction Date, or Vendor Code. FGRCTRLGL/Subsidiary Ledger Control ReportDisplays the general ledger amount and operating account ledger. FGRCUNAChanges in Unrestricted Net Assets Report In conjunction with FGRCHNA, fulfills the Financial Accounting Standards Board™s requirements for a Statem ent of Activities. The primary purpose of this report is to provide relevant information about the effects of transactions that change the amount and nature of unrestricted net assets. FGRENRLEnd-of-Year Encumbrance Carry Forward Report Hardcopy report of the remaining open encumbrances (prior year) that the system carries forward into the current fiscal year. FGRFAACFund/Account Activity ReportPrints a balance for all funds and/or accounts, a range of funds and/or accounts, or specific funds and/or accounts within a chart of accounts and fiscal year. FGRFBALFund Balance Account ReportHardcopy output of the fund balance account information maintained using the Fund Balance Account Maintenance Form (FTMFBAL). FGRFITDInception to Date Activity ReportHardcopy output including inception to date activity for account codes within funds.Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-11 TRM Supplement Reports and Processes FGRFNDHFund Hierarchy ReportDisplays th e hierarchical structure of the fund code within a fund type for a specified chart of accounts. FGRFPSNStatement of Financial Position Report Provides relevant information about an organization™s assets, lia bilities, and net assets for a specific date. FGRGLRLBalance Forward Processing Report Closes all prior year control account balances into the fund balance. Cr eates open balances in the current fiscal year (accounting period 01). Opens the accrual period in the prior fiscal year. FGRGLTAG/L Transaction Activity ReportDetails accounting tran saction activity and displays beginning and ending account balances for the specified period. This report sorts by account within fund. FGRIDOCIncomplete Document Listing Report Provides the ability to pr int information about incomplete documents. This report provides a simple method of identifying incomplete documents, so that the appropriate action may be taken to complete these documents.FGRJVLRJournal Voucher Listing Report Prints header and detail information for selected pending journal vouchers. This report can be used to print comprehensive information for one or more pending journal vouchers based on the journal voucher number, transaction date , status, journal type, or user ID. FGRLOCHLocation Hierarchy ReportDisplays the hierarchical relationship within the location code.FGRODTAOrganization Detail Activity ReportDisplays expense budg et, and encumbrance activity for the specifie d period. This report sorts by account within organization. Report Identifier Report Name Report Description/n1-12 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FGROPNEOpen Encumbrances ReportPrints header information and remaining encumbered or reserved balance amounts for selected purchase orders, requisitions, and general encumbrances . Only records for documents that are open, completed, approved, and posted are included in the report.FGRORGHOrganization Hierarchy Report Displays the hierarchical relationship within the organization code structure. FGRPDTAProgram Detail Activity ReportDisplays expense, bu dget, and encumbrance activity and shows beginning and ending balances for the specified period. This report sorts by program within organization. FGRPRAPPro Rata Allocation Process Prepares allocations for existing transactions based on user-defined parameters. The process also creates totals for all of the eligible transactions and, if requested, produces an Allocation Control Report.FGRPRARPro Rata Allocation ReportAllows the user to re-create a list of the source transactions for any a llocation transactions produced by the Allocation Process. FGRPRGHProgram Hierarchy ReportDisplays the hierarchical relationship within the program code. FGRREOBStatement of Revenues, Expenditures, Other Changes ReportCompares actual activity for revenues and expenditures to the budget ed activity to date. FGRREOCStatement of Revenues, Expenditures, Other Changes ReportCompares actual activity for revenues and expenditures for the budgeted activity for the prior fiscal year. FGRTAXRStatement of Taxes and Rebates ReportPrints tax and rebate information related to invoices and direct cash receipts in Summary, Detail, or Both modes.FGRTBALTrial Balance ReportPrints a tr ial balance for all funds, a range of funds, or specific fu nds within a chart of account and fiscal year. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-13 TRM Supplement Reports and Processes Purchasing and Procurement FGRTBEXTrial Balance Exception Report Analyzes funds for potential out of balance conditions. When run in summary mode, it displays any out of balance funds for each Chart of Accounts. In detail mode, the report provides a list of all documents that are associated with that out of balance fund. FGRTOFRDue To/Due From Control ReportDisplays the fund code, description, due to balance, due from balance and the difference for a specific chart of account and fiscal year. FGRTRNHDaily Transaction Control ReportDisplays the daily tran saction information by activity date, document type, and number. FGRTRNITransaction Interface Process Processes records in the Transaction Input Table (FGBTRNI). The FGRTRNI process performs data validation and internally documents transactions that have failed budget edits and NSF checking. FGRTRNRTransaction Error ReportDisplays the error messages for those transactions that do not successfully pass edits or the available balance processing in the Transaction Interface Process (FGRTRNI). FORAPPLApproval ProcessIn terrogates the records in the Approvals in Process Table (FOBAINP), as well as the queue and routing information, and sends the entry for the document to the next approver. Report Identifier Report Name Report DescriptionFAPCARDPurchase Card Transactions Process Process (a) performs edits on FATCARD data; (b) generates a Liability Journal Voucher; (c) populates the PCard Invoice tables; and (d) sends notification to account managers FAPINVTInvoice Feed ProcessMoves invo ice transactions directly from the PCard Transaction Interf ace tables into the invoice tables.Report Identifier Report Name Report Description/n1-14 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FARCHKRCheck Register ReportShows check register report sorted by check number. FARCSHRCash Requirements Report Displays predicted cash requirements for the installation™s banks based on accounts payable data. It is sorted by bank. FARVALPVendor Alphabetical Listing Report Lists vendors in alphabetical order. FARVHSTVendor History ReportDisplays payment activity data for a vendor. FARVNUMVendor Numerical Listing Report Lists vendors in numerical order by ID number. FPABIDDBid Print Report Prints either a specific bid or all bids which have been approved but have never been printed.FPACORDPurchase/Change Order Print ReportPrints a specific purchase order which has a change order associated with it or may print all of the change orders which are completed, approved, posted but not yet printed. FPAPORDPurchase/Change Order Print Report Prints a specific Purchase/Change Order or all Orders which have been approved but have never been printed. FPARQSTRequisition Print Report Prints a specific requisition or all requisitions which have been approved but have never been printed.FPPPOBCPurchase Order Batch Close Process Closes all purchase orders with encumbrance balance less than or equal to user defined amount. Liquidates encumbrance balance and sets all required system status values as necessary. Produces an audit report detailing the documents affected by the process. FPRBEVLBid Evaluation ReportSummarizes bid data for comparison of vendor unit pricing on a particular bid. FPRDELVDelivery Log ReportDisplays delivery and routing of received goods. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-15 TRM Supplement Reports and Processes Stores Inventory FPROPNPOpen Purchase Orders Report Shows open purchase orders sorted by purchase order number on a specified date. FPROPNROpen Request ReportShows open requisitions sorted by requisition number as of a particular date. FPRRCDLReceiving/Delivery ReportReport can be used to determine the status of received and delivered commodities versus ordered ones. FPRRCSTPO Receiving Status ReportDisplays purchase orders with packing slips that have not ye t been invoiced. FPRVCATVendor Products Catalog ReportDisplays vendor numbers, names, and prices based on the installation's purchasing history. It is sorted by commodity. FPRVVOLVendor Volumes ReportDisplays commodity purchase history by vendor. Report Identifier Report Name Report DescriptionFSRDTLGStores Daily Transaction Listing Report Shows all activity th at changed overall balances of consumable stock in the Stores Inventory System for a defined date range. The system lists all receipts of stock first. Issues list afterward, with the 'issued to' department or organization. FSRINVLStores Inventory Listing Report Contains on-hand co mmodity quantities by location. Depending upon the parameter values, the report may print: all commodities for all locations sorted by commodity and location, one co mmodity across all locations, one commodity in a subset of location or all commodities for one location. Report Identifier Report Name Report Description/n1-16 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FSRISSTIssue Ticket ReportWhen stoc k is issued from the stockroom, generate this report to accompany the goods. The Issue Ticket Report contains a listing of the commodity code, de scription, quantity, quantity remaining to be issued (backordered), and location. Use this re port to print return issue documents. Generally, you print this report after the issue/re turn has been created and completed online. FSRLWSRStores Low Stock ReportLists all stock items whose on-hand quantity plus on order quantities has fallen below the pre-defined reorder point quantity, or have fallen below a range defined as a percentage of the reorder point quantity. FSROPNROpen Stores Requests ReportDisplays status of requests for stock items, goods, and services that are not fully satisfied. Open requests display as of a specific date and are sorted by requisition number. The system displays all open requisitions unless you use the optional parameters to selectively define the report listing. FSROUTPStores Outstanding Purchase Orders ReportLists all open purchase order items for consumable stock items. You may select one commodity code on which to report or report all commodities. FSRPHYRPhysical Inventory Process Report Automatically marks, as reconciled, all those items whose system in ventory count does not vary from the physical inventory count by an amount greater than the system defined tolerance for that comm odity. Before running this process, assign al l stock commodities an ABC classification on the Stores Inventory Maintenance Form (FTMINVM). Assign a tolerance percentage to the ABC classifications using the System Data Maintenance Form (FTMSDAT). Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-17 TRM Supplement Reports and Processes FSRPICKPick List Lists all open stores requests by a user defined commodity, location or commodity/location combination. Use this report after creating a stores request and print th is report ju st before the issue. This repo rt enables warehouse personnel to pick items to satisfy a request. FSRPIDRPhysical Inventory Discrepancy ReportLists all items whose system inventory count does not match the physical inventory count. The system automatically performs a wildcard search using the sublocati on, if entered. If only the primary location (warehouse) is entered, all discrepancies within that warehouse are reported. If a primary location and sublocation are entered, all locations matching the primary location and sublocatio n (rack/shelf/bin) are returned. FSRPIWSPhysical Inventory Worksheet Lists all items by specific location(s). The purpose of the report is to ease the recording of count quantities. All loca tions print that have not yet been counted or reconciled based upon the values you enter. The system automatically performs a wildcard search using the sublocation, if entered. If only the primary location is entered, a ll discrepancies within that primary location (warehouse) are reported. If a primary location and sublocation (rack/ shelf/bin) are en tered, all locations matching these parameters are reported. FSRPUTLPut List Lists all items from a packing list and their default location in order for warehouse personnel to determine where to put the incoming stores. You may sort this report by receiver document number, packing list, or by commodity code. The system lists all stock locations that may contain the commodity. Report Identifier Report Name Report Description/n1-18 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Financial Utilities FSRSTEXDock to Stock Exceptions Report Lists all items for a specific primary ship to location, that have been received by the central receiving process but have not yet been introduced into the Stores Inventory System. The receiving information that prints is: accepted quantity, remaining dock quantity, receipt date, receiver document number and packing slip number, purchase order number, and the order amount. The system scans the central receiving document for all stock commodities that have not been transferred over to the Stores warehouses. FSRSUPCSupply Catalog ReportLists all stock commodities that are currently active sorted by commodity code. Each commodity is checked to be sure that it is still active and has not terminated. Report Identifier Report Name Report DescriptionFUPLOADFinance Upload to GURFEED Process Evaluates input data based on parameters entered to load internal vendor data to GURFEED.FURFEEDFinance Feed Sweep Process Shows the output of the Finance Feed Sweep Process. FURAPAYStudent Refund Interface Process Draws refund check transactions from the Accounting Feed Accounts Payable Table (GURAPAY), and creates invoices which are ready to post. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-19 TRM Supplement Reports and Processes Position Control Report Identifier Report Name Report DescriptionNBPBROLBudget Roll Process Report Executes budget roll (including fringe benefits) from one fiscal year to another. The system posts results to the Position Totals Block of the Position Form (NBAPOSN). NBPBUDMBudget Maintenance Process Spreads budget amount s to position labor distributions. NBPMASSMass Salary Table Update Report Calculates contract value for the fiscal year you enter. Calculates automatic step increases. Updates groups of Table/Grade combinations by amount or percent, and updates job records with new table/grade combination. NBRBWRKBudget Worksheet Report Displays budget phases with current year and prior year budget amounts from the budget file. Banner Finance so rts this report by organization, fund, prog ram, activity, location, and account. NBRPCLSPosition Class Listing ReportLists defined position classes and salary guidelines. NBRPINCPosition Class Incumbent ReportLists incumbents of pos itions in each position class. NBRPOSNPosition Report Lists active , frozen, and/or cancelled positions for a specified date range. NBRPSTAPosition Status Exception Report Lists exception status positions (i.e., over budget, vacant, canceled, frozen, under- encumbered, or over-filled). NHPFIN1Finance Interface Extract Report Extracts data for Financ e System interface and updates the position tota ls with encumbered amounts. NHPFIN2Finance Interface ReportShows information which you extracted and passed to the Finance System. NHRBDSTBudget Distribution ReportShows budgetary information for selected FOAPALs. /n1-20 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes NHRDISTOrganization Distribution Payroll Distribution Report Shows summary and detail reports. NHRECRTEffort Certification ReportShows FOAPAL inform ation for specified grants or funds. NHREDSTEmployee Distributions Report Shows the sum of selected earnings and/or benefits distributed to each FOAPAL for each employee™s position. Also shows the percent of the selected earnings and/or benefits represented by the reported sum, and the percent of the employee™s total earnings and/or benefits represented by the sum of all earnings and/or benefits distributed to the FOAPAL. NHRSDSTEmployee Payroll Summary by Organization Report Shows up to eight columns of earnings and benefit data for selected FOAPAL distributions. Each column can contain hours or amounts for a specific earning, benefit, or charge back, or for all earnings and/or benefits. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-21 TRM Supplement Reports and Processes Archive/Purge Research Accounting Report Identifier Report Name Report DescriptionFOPARCPArchive/Purge ProcessMethod by which data is copied from the production current tables into another table structure. Data which resides on the archive tables can be purged when it is outdated or no longer needed. The Purge process deletes data from the production current or production archive tables. A database indicator is included among the parameters to determine which database will be purged. FOPARCRRestore ProcessReestablishes pr eviously archived data into the production current tables. The restored data will be removed from the production archive tables as part of the Restore Process. Purged data cannot be restored. Report Identifier Report Name Report DescriptionFRPMESGEvents Messaging Process Provides the ability to monitor events associated with personnel with the Research Accounting module. FRRABUDAgency Budget ReportPrints the proposal budget so that it can be forwarded to the agency. The report can be run in either Audit or Update mode. FRRBUDGGrant Budget ReportDisplays a specific budget, or a series of budget iterations that have been created through the Grant Budget Form for a specific grant. You will see a breakdown of grant budget information by fund, organization, program, activity, and location for each account code budgeted. The report will also display the amounts for in direct costs and cost sharing. /n1-22 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FRREVNGGrant Events Report Displays events or reminders associated to a grant. You may choose to view events for a specific personnel indicator; for a specific time period; or for a range of grants, a specific grant or a selection of grants. FRREVNPProposal Events Repo rt Displays events or re minders associated to a proposal. You may choose to view events for a specific personnel indicator; for a specific time period; or for a range of proposals, a specific proposal, or a selection of proposals. FRRGBFYGrant Budget Status Fiscal Year Report Reflects grant activity within a specific fiscal year. FRRGITDGrant Inception to Date Report Provides grant information from the Grant Ledger in place of the Operating Ledger. It will prompt for a Grant Code to display Operating account activity in hardcopy format. The report has been enhanced to include trail-in and trail-out activity. FRRGRNTDeferred Grant ProcessAllows for Grant calculations of indirect cost, cost share, and reve nue recognition to be performed on a user-defined interval. Also writes the detailed transactions to the history table as it processes them. FRRINDCGrant Indirect Cost ReportProvides the total expend itures for each grant by the FOAPAL. The repo rt lists the direct expenses, then the overhead (indirect cost) and then the total cost. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-23 TRM Supplement Reports and Processes FRRBILLResearch Accounting Billing ProcessProduce bills to the sp onsoring agency. You can run this process for a specific bill format code and for a specific billing period end date. All the unbilled transactions up to the billing period end date will be selected for each grant or payment management system code and will be billed to the agency. This process can be run in audit mode or update mode. When you run this process in update mode, this process creates a document and sends it to posting. All the unbilled transactions will be updated to billed. This process also inserts rows into the appropriate billing tables based on the format code FRRBREVResearch Accounting Billing Reversal Process Reverse the bill that was generated by the billing process (FRRBI LL). This process reverses what the billing process has done. You can run this process for a specific bill invoice number. This process sends a document to posting. This process also updates the transaction to unbille d from billed so that you can run the billing process again. This process also update bill invoice adjusted sequence number. You cannot run reversal process for an invoice for which a payment has been applied. You must unapply the payment and then run the reversal process. FRRGRPTResearch Accounting Report Process Generate reports to the agency. You can run this process by grant or by payment management system code for a specific format code and report end date. This process identifies all the activity for that specific parameters entered and w ill insert data into the appropriate report tables. This process will not update ledgers. Report Identifier Report Name Report Description/n1-24 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FRRBEXCBilling Exception Report Provides a list of all ex ceptions that the billing process has identified wh en it tried to process a bill. You can run this report by grant or by Payment management system code and for a specific billing period end date. This report is sorted by grant. Ideally you run the billing process (FRRBILL) in audit mode and run this report for list of any exceptions against the data that was selected by the billing process for that run.FRRINVSGrant Billing Preview ReportProvides basic information like- billing period start date, end date, cu mulative billed amount, retainage amount and payment withholding amount. You can run this report for a specific date and this process will provide information for a grant sorted by bill format codes. You can run this report befo re running the billing process (FRRBILL) to determine what data would be selected by the billing process when run for the same date FRRBDEXBilling Detail Exception Report Provides the exceptions between the transaction detail table and the billing detail table. It will provide the list of all the documents that exist in FGBTRND table and that do not exist in FRRBDET table. This reported is sorted by FOAPAL with in a grant. FRRCNSFCanadian Standard Report FormPrints grant billing information in a format customized for Canadian institutions. To run this report, Oracle Report s must be installed on your computer. This report will not run from a web-enabled form.You can run this report for a specific Payment management system code or you can run for a specific grant or multiple grants. You can produce one single report for all the grants linke d to the payment management system code or multiple reports for each grant within that payment management system code. You can run this report for a specific period end date. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-25 TRM Supplement Reports and Processes FRRFEXCFund Exception Report Lists all the funds that are linked to the grant but does not have an accrual account and revenue account fields populated on the FTMFUND record. Billin g module design is based on the fact that the revenue recognition occurs as soon the grant expense is incurred. If these fields are not populated on the FTMFUND record, expenditures against the grant will not be inserted into the billing detail table (FRRBDET) and as a result of that you cannot use the billing process to bill the agency FRRGENRGeneric Report Form Prints grant billing information in a format customized by your in stitution on the Billing/ Report Format Maintenance Form (FTMBFRM). To run this report, Oracle Reports must be installed on your computer. This report will not run from a web-enabled form. You can run this report for a specific Payment management system code or you can run for a specific grant or multiple grants. You can produce one single report for all the grants linked to the payment management system code or multiple reports for each grant within that payment management system code. You can run this report for a specific period end date. FRRGRNPDeferred Grant PurgePurges the da ta specified within the date parms from the grant history that is maintained during defer grant calculation process. FRR134BStandar d 1034 Billing Form Prints a bill in the standa rd 1034 format. To run this bill, Oracle Report s must be installed on your computer. This bill will not run from a web-enabled form. You ca n run this bill for a specific Payment management system code or you can run for a specific grant or multiple grants. You can produce one single bill for all the grants linked to the payment management system code or multiple bills for each grant within that payment ma nagement system code. You can run this bill for a specific period end date or a specific bill invoice number Report Identifier Report Name Report Description/n1-26 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FRR269RStandard 269 Report Form Prints grant billing information in the standard 269 format. To run this report, Oracle Reports must be installed on your computer. This report will not run from a web-enabled form. You can run this report for a specific Payment management system code or you can run for a specific grant or multiple grants. You can produce one single report for all the grants linked to the payment management system code or multiple reports for each grant within that payment management system code. You can run this report for a specific period end dateFRR270BStandard 270 Billing Form Prints a bill in the standard 270 format. To run this bill, Oracle Report s must be installed on your computer. This bill will not run from a web-enabled form. You ca n run this bill for a specific Payment management system code or you can run for a specific grant or multiple grants. You can produce one single bill for all the grants linked to the payment management system code or multiple bills for each grant within that payment ma nagement system code. You can run this bills fo r a specific period end date or for a specific bill invoice number. FRR272BStandard 272 Billing Form Prints a bill in the standard 272 format. To run this bill, Oracle Report s must be installed on your computer. This bill will not run from a web-enabled form. You can run this bill for a specific Payment management system code or you can run for a specific grant or multiple grants. You can produce one single bill for all the grants linked to the payment management system code or multiple bills for each grant within that payment management system code. You can run this bill for a specific period end date or for a specific bill invoice number. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-27 TRM Supplement Reports and Processes FRR272RStandard 272 Report Form Prints grant billing information in the standard 272 format. To run this report, Oracle Reports must be installed on your computer. This report will not run from a web-enabled form. You can run this report for a specific Payment management system code or you can run for a specific grant or multiple grants. You can produce one single report for all the grants linked to the payment management system code or multiple reports for each grant within that payment management system code. You can run this report for a specific period end date. FRRGENBGeneric Bill Form Prints bills in a format customized by your institution on the Billing/Report Format Maintenance Form (FTMBFRM). To run this bill, Oracle Reports must be installed on your computer. This bill will not run from a web- enabled form.You can run this bill for a specific Payment management system code or you can run for a specific grant or multiple grants. You can produce one single bill for all the grants linked to the payment management system code or multiple bills for each grant within that payment ma nagement system code. You can run this bill for a specific period end date. FGRGLEXGrant Ledger Exception Report Prints a list of all transactions that exist in the Transaction Detail Table (FGBTRND), but not the Grant Ledger (FRRGRNL). This report prints by grant and with in each grant for a specific FOAPAL. This report also prints exception within each bucket of the grant ledger. TRRAGESGrant Aging Analysis ReportProvides aging of the gr ant receivables. You can run this report for aging of billed receivables and aging of unbilled receivables. You can print 5 different aging buckets Report Identifier Report Name Report Description/n1-28 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes TRRAPPLApplication of Payment Process Applies payments to the charges. This process has no parameters. It applies payments to charges by grant. If a Tpay number or invoice paid number is entered by the user in the Payments Entry Form (FRAAREV), this process first applies the payment to that specific charge transaction number or for that specific bill invoice number. If these two fields are not entered by the user, then the process will apply the payment ag ainst a grant to the oldest charge TRRRCONGrant Reconciliation ReportReconciles data between the Accounts Receivable module and the Finance module for Grant Billing transactions only. You can reconcile by chart or by grant or by payment management system code. You can also print just the grants that are out of balance or just the Payment management system codes that are out of balance. This report reconciles between billed summary and billed detail, Paym ents in AR against payments in General ledger and billed charges in AR against charges in the general ledger. TRRUNAPUnapplication of Payments Process Unapplies payments that have been incorrectly applied to a charge. After you run this process, you can apply the payment to the appropriate charge TRRUNPLUnapplied Payments Listing Lists all payments that have not been applied to charges. It also pr ints the fund codes the exact payment was posted to. This report is sorted by grant. TRRCOLLCollections ReportPrints inform ation about collection efforts that have been entered on the Grant Billing Collections Form (TRACOLL). You can run this report by status i.e., if the bill is open or closed or by user ID or by grant or by agency. Report Identifier Report Name Report Description/nSeptember 2014 Banner Finance 8.10 1-29 TRM Supplement Reports and Processes Endowment Management Report Identifier Report Name Report DescriptionFNPGAINDistribution of Gains/ Losses Process Used to distribute rea lized gains and losses, unrealized gains and losses, and spending formula variance to all endowed funds in a unitized pool. FNPSPNDDistribution of Spendable Income Process Used to distribute spendable income to all endowed funds in a unitized pool. FNPUNTZUnitization ProcessUsed to uniti ze a pool of endowment or similar funds.FNRPRNCPrincipal Fund Activity ReportShows year-to-date information about endowed funds for the beginning of a fiscal year to the date that you enter in the As of Date parameter. FNRSPNCSpendable Endowment Fund History Report Shows information about spendable funds. Spendable funds are so rted by financial manager. Within each financial manager, they are sorted by fund code. FNRHISTEndowment Fund History Report Shows information abou t endowed funds for each fiscal period from the inception date of the fund to the current date. Displays the net change, the cumu lative number of units, and the book value for each fund by unitization period. /n1-30 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Grant Interface Miscellaneous Comprehensive Annual Financial Reports (CAFR) Banner Finance contains nine Comprehensive Annual Financial Repo rts identified in the following list. Following this list are re quired table entries for each report. 1.General Fund Combining Bala nce Sheets Report (FGRCBSR)2.Comparative Statements of Revenues, Expe nditures and Changes in Fund Balances Report (FGRCGBA)3.Comparative Balance Sheets (FGRCGBS) 4.Combined Balance Sheet - All Fund Typ es and Account Groups Report (FGRCOBS) 5.Combined Statement of Revenues, Expend itures and Changes in Fund Balances Report (FGRCREF)Report Identifier Report Name Report DescriptionFRPBINFGrant Budget Interface Process Processes budget data from FRCBUDD and FRCBUDH into the Banner baseline tables FRBBUDG and FRRBUDG FRPGINFGrant Interface ProcessProce sses information loaded from FRCBFIX, FRCEVNG, FRCGLOC, FRCGRNT, FRCGRPI, and FRCGUSN into the Banner baseline tables FRRBFIX, FRBEVNG, FRRGLOC, FRBGRNT, FRRGRPI, and FRRGUSN FRRTRNRGrant/Budget Error Printing Process Produces an error report, based on the contents of FRCTRNR (collector table).Report Identifier Report Name Report DescriptionFEPOEXTEDI Extract ProcessExtracts those Purchase Order and Purchase Order Change documents that are to be sent via EDI./nSeptember 2014 Banner Finance 8.10 1-31 TRM Supplement Reports and Processes 6.Combined Statement of Revenue, Expe nditures and Changes in Fund Balances - Budget/Actual Report (FGRCSBA)7.Special Revenue Funds Comparative Statement of Revenues, Expenditures and Changes in Fund Balances Report (FGRCSCF)8.Combined Statement of Revenue, Expenses and Changes in Reta ined Earnings/Fund Balances Report (FGRCSRE)9.General Fund Combing Statement of Revenue, Expenditures and Changes in Fund Balances Report (FGRCSSR)A record must exist in FTVSDAT for every internal fund type code and fund type group code. Fund type group code descriptions are as follows. ŁFund Type Group Code 01 defines Governmental Funds. ŁFund Type Group Code 02 defines Proprietary Fund Types. ŁFund Type Group Code 03 defines Fiduciary Fund Types. ŁFund Type Group Code 04 defines Account Groups. NoteCombined Statement of Revenues, Expenditures and Changes in Fund Balances Report (FGRCREF) and Combined Statement of Revenue, Expenditures and Changes in Fund Balances - Budget/Actual Report (FGRCSBA) only report on fund type group code 01. The Combined Statement of Revenue, Expen ses and Changes in Retained Earnings/Fund Balances Report (FGRCSRE) only reports on fund type group codes 02 and 03. Specify the following additiona l required entries for the Comb ined Statement of Revenue, Expenses and Changes in Retained Earnings/Fund Balances Report (FGRCSRE). A record must exist in FTVSDAT for every in ternal fund type code associated with the following categories. Table Entry Values FTVSDAT_DATA Valid codes are 01, 02, 03, or 04FTVSDAT_COAS_CODE Chart of Accounts Code FTVSDAT_SDAT_CODE_ENTITY FTVFTYP FTVSDAT_SDAT_CODE_ATTR INTERNAL_FTYP_CODE FTVSDAT_SDAT_CODE_OPT_1 Internal fund type code associated with the appropriate group /n1-32 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes ŁI - For Non-Expendable Trust fund records, FTVSDAT must contain the following entries:ŁII - To select Pension Trust fund(s), same as for non-expendable trust with the following exceptions:ŁIII - For Non-Operating Accounts, same as for non-expendable trust with the following exceptions:ŁIV - For Extraordinary Gain (Loss) Acco unts, same as the non-operating accounts with the following exceptions: Specify the following additional required entries for the Combined Statement of Revenues, Expenditures and Changes in Fund Balances Report (FGRCREF).A record must exist in FTVSDAT for you to define Expendable Trust Funds. Table Entry Values FTVSDAT_COAS_CODE Chart of Accounts Code FTVSDAT_SDAT_CODE_ENTITY FTVFUND FTVSDAT_SDAT_CODE_ATTR NON-EXPENDABLE TRUST FTVSDAT_DATA Fund code for non-expendable trust for all fund levels Table Entry Values FTVSDAT_SDAT_CODE_ATTR PENSION TRUST FTVSDAT_DATA Fund code for pension trust for all fund levelsTable Entry Values FTVSDAT_SDAT_CODE_ENTITY FTVACCT FTVSDAT_SDAT_CODE_ATTR NON-OPERATING ACCOUNT FTVSDAT_SDAT_CODE_OPT_1 Internal account code for non-operating accounts Table Entry Values FTVSDAT_SDAT_CODE_ATTR EXTRAORDINARY GAIN (LOSS) ACCOUNTFTVSDAT_SDAT_CODE_OPT_1 Internal account code for extraordinary gain (loss) account Table Entry Values FTVSDAT_COAS_CODE Chart of Accounts Code FTVSDAT_SDAT_CODE_ENTITY FTVFUND /nSeptember 2014 Banner Finance 8.10 1-33 TRM Supplement Reports and Processes Processes FGBTRND Performance Improvements Alternate Indexes Three separate indexes are provided that ca n improve performance. Which index you use depends on how you access the data for th e Operating Ledger transactions on the Transaction History Detail Table (FGBTRND). The scripts which create these indexes are as follows: fgbtrnd2.sql, fgbtrnd3.sql, and fgbtrnd4.sql .ŁThe fgbtrnd2.sql defines an account index for the Transaction History Detail Table (FGBTRND). The Account Index is the permanent index that Banner Finance maintains. Use this index if when you access the Operating Ledger data on FGBTRND, you enter an account code as a sp ecific request (versus relative). If this is not the case at your installation, you can use two alternate indexes. ŁThe fgbtrnd3.sql defines the first alternate index: an organization index. Use this index if you access the Operating Le dger data on FGBTRND, you enter an organization code, either with a rela tive account or without an account. ŁThe fgbtrnd4.sql defines the second alternate in dex: a fund index. Use this index if any of the fo llowing conditions exist.ŁWhen you access the Operating Ledger data on FGBTRND, you enter a fund code alone. ŁWhen you access the Operating Ledger data on FGBTRND, you enter a fund code with or without an organization code. ŁWhen you access the Operating Ledger data on FGBTRND, you enter a fund code with or without an organization code, and with or without an account code. Choosing an Index It requires a great deal of th ought to determine which index to use in order to improve performance. Following are some hints. ŁAlways choose an index that meets the ma jority of your requests. No one index meets the criteria for every access you can make. FTVSDAT_SDAT_CODE_ATTR EXPENDABLE TRUST FTVSDAT_DATA Fund code to include u nder this category, for example, FGBOPAL_FUND_CODE Table Entry Values /n1-34 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes ŁChoose an index that eliminates the larges t number of records when you access the table. For example, if you normally acce ss data with fund and organization, and there are typically less records on TRND per organization than per fund, choose the organization index. If ther e is a toss-up between the fund and the organization index, do different trials to monitor the response times for accesses to FGBTRND. Change the index that you init ially define, if you desire. ŁLearn in detail how to access the inform ation. You can increase your effectiveness just knowing how to get the data you need. Therefore, once you add an index to the table, learn how to get the most from your queries. For example:ŁWhat components (fund, organization, account, relative or specific) should you always value for the index to provide optimum performance? ŁWhat can you expect when certain comp onents are valued or left blank? ŁWhat happens when you ask for information for the fund with the largest volume of data? Notice that, in the .sql scripts, Banner Finance always drops the account index before it adds either the organization index or the fu nd index. This limits the number of similar indexes on the table, which in turn impr oves response time by not having to maintain indexes that ORACLE may never use. NoteWhen Oracle decides between the use of two possible indexes, it always uses the one that you create most recently. Other Indexes for FGBTRND There is a separate index that you use specif ically for the FGIGLAC query. In addition, there is an index specifically for the FGRODTA report and an index to look specifically at data for encumbrances. The indexes mentioned above do not affect these indexes. All of the indexes include a column, FGBTRND_LEDGER_IND . This column effectively splits the TRND transaction data in to financial ledger components: G for General Ledger data, O for Operating Ledger data, and E for Encumbrance data . This improves the response time for large volume queries in which the data crosses financial ledger component (for exampl e, the fund code).FGRBAVL Rebuild Process This process is run on request and rebuilds the Budget Availability Ledger Table (FGBBAVL) when necessary. The budget rebuild process is based on the entire FOAPAL distribution for that budget. FGRBAVL needs to be run only if the rules controlling the available balance checking process are changed. /nSeptember 2014 Banner Finance 8.10 1-35 TRM Supplement Reports and Processes The Available Balance Rebuild Process (F GRBAVL) requires exclusive access to the Budget Availability Ledger Table (FGBBA VL). The FGRBAVL process also makes extensive updates to the database since it deletes and rebuilds the entire FGBBAVL table. NoteIf you run it with only a single Commit command at the end of the process, you may need an excessive number of rollback segments. If you use multiple commits, there is a risk t hat another available balance related process may update the table before you can re-lock the table. To alleviate this problem, the Available Balance Rebuild Process (FGRBAVL) uses two temporary public synonyms called fiFGBBAVL_WORKfl and fiFGRBAKO_WORKfl. These synonyms are not referenced in any other Banner Finance component. Using this method, FGRBAVL can perform multiple commits without the risk of other processes making updates. During the period this pr ocess runs, no other Finance process or form which references FGBBAVL can run since a ll Banner Finance components access the FGBBAVL and FGRBAKO tables using the public synonyms fiFGBBAVLfl and fiFGRBAKOfl. Because of this restriction, as we ll as the amount of processing that this process requires, you should always run FGRBAVL during off hours when no other Finance process occurs. Finance updates the FGBBAVL table during the normal posting of all accounting transactions by the Posting Process (FGRACTG). You only need to run FGRBAVL if the rules that control the available balance checking process change. Before running FGRBAVL, export the FGBBA VL and FGRBAKO tables. This creates a backup of these tables in case the process terminates abnormally. Warning Remember that you cannot execute any other process or forms which use FGBBAVL or FGRBAKO while you run FGRBAVL. If you do, an Oracle error displays stating that the Table or View does not exist. If the process terminates abnormally, import data from the export tables. Run FGRBAVL again.Running the FGRBAV L Rebuild Process Drop Public Synonyms FG BBAVL and FGRBAKO (Part A) Drop the public synonym FGBBAVL and create a public synonym FGBBAVL_WORK, and drop the public synonym FGRBAKO and create a public synonym FGRBAKO_WORK by executing the script fi msbvl1.sql, located in $BANNER_HOME/ finance/plus. To do this, invoke SQLPlus and run the procedure: sqlplus fimsmgr/password start fimsbvl1 /n1-36 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Run the FGRBAVL Rebuild Process (Part B) Execute the C program FGRBAVL from the co mmand line and respond to the on-screen messages and prompts. For additional inform ation about parameters, refer to the documentation for FGRBAVL in fiChapt er 4: Reports and Processesfl of the Banner Finance User Guide .Locate the program in the directory $BANNER_HOME/general/exe. When entering parameters for the available balance rebuild process, be aware of the following. ŁWhen using a fund or fund type parameter, the fund or funds with that fund type should not be linked to other funds fo r budgeting purposes. That is, the funds should not have a budget pool fund or combination budget control with other funds and organizations. ŁIf budgeting is done on a pool budget fund or hierarchical budget basis, only the chart and fiscal year parameters should be entered. Re-create the Public Synonyms FGBBAVL and FGRBAKO (Part C) Drop the public synonyms FGBBAVL_WOR K and FGRBAKO_WORK and re-create the original public synonyms by executin g the script FIMSBVL2.SQL located in $BANNER_HOME/finance/plus. To do this, in voke SQLPlus and run the procedure. sqlplus fimsmgr/password start fimsbvl2 Compile Objects in an Invalid State (Part D) Run the guraltrb.sql utility script to compil e all BANINST1-owned functions, views, packages and procedures that are in an invalid state. Warning Any time a database object is modified (such as renaming tables FGRBAVL and FGRBAKO, other objects that have interaction with the changed object are marked as Invalid by the database system. You must resolve all such errors.To compile objects which are currently in an invalid state perform the following steps. 1.Invoke SQLPlus and run the procedure. sqlplus general/password start guraltrb 2.At this point you may be prompted to ente r a value for splpref, the spool preference variable. At the prompt Enter value for splpref , simply press Enter without entering a value. If you do enter a valu e, the system either appends the value to the front of the /nSeptember 2014 Banner Finance 8.10 1-37 TRM Supplement Reports and Processes file names shown below, or uses the value as a path to write those files if the value is a valid path.galtrit.sql glisaltr listing 3.You may need to repeat this process several times until all dependencies are validated.NoteThe FGRBAVL rebuild process is now complete. Compilation of FGRBAVL The source code for the Available Balance Rebuild program is composed of two C programs: fgrbavl.pc linked with fgpabal.pc.If the FGRBAVL process needs to be compiled, there is a mass generation script provided with Banner Finance. The script ( fimcmplc.shl) is located in $BANNER_HOME/ finance/misc. This script compiles all Finan ce C programs in a UNIX environment. At the bottom of the script are statements necessary to compile the Available Balance Rebuild Process. They are described below. UNIX#Compile and link BANNER Finance Available Balance Rebuild Process(FGRBAVL)# # # Only those objects that have changed have to compiled. The # LAST Line should always be included to re-link the objects. # # make -f $BANNER_HOME/general/c/sctproc.mk CHECKOPT="sqlcheck=full userid=baninst1/u_pick_it" fgpabal.o # make -f $BANNER_HOME/general/c/sctproc.mk fgrbavl LINKOBJS="$BANNER_HOME/general/exe/fgpabal.o" /n1-38 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FGRGRBD Grant Rebuild Process This process is run on request and rebuild s the Grant Ledger Table (FRRGRNL) when necessary. The Grant Rebuild Process (FGRGRBD) requ ires exclusive access to the Grant Ledger Table (FRRGRNL). The FGRGRBD process also makes extensive updates to the database since it deletes and rebuild s the entire FRRGRNL table. NoteIf you run it with only a single Commit command at the end of the process, you may need an excessive number of rollback segments. If you use multiple commits, there is a risk t hat another grant related process may update the table before you can re-lock the table. To alleviate this problem, the Grant Rebuild Process (FGRGRBD) uses a temporary public synonym called fiFRRGRNL_WORKfl . This public synonym name is not referenced in any other Banner Finance Component. Using this me thod, FGRGRBD can perform multiple commits without the risk of other pr ocesses making updates. During the period this process runs, no other Banner Finance process or form which references FRRGRNL can run since all Banner Finance compon ents access the FRRGRNL table using the original public synonym fiFRRGRNLfl. Because of this restriction, as well as the amount of processing that this process requires, you should always run FGRGRBD during off hours when no other Banner Finance process occurs. Before running FGRGRBD, export the FRRGRNL table. This creates a backup of the table in case the process terminates abnormally. Warning Remember that you cannot execute any other process or forms which use the table FRRGRNL while you run FGRGRBD. If you do, an Oracle error displays stating that the Table or View does not exist. If the process terminates abnormally, import data from the export tables. Run FGRGRBD again.Running the FGRGRB D Rebuild Process Drop Public Synonym FRRGRNL (Part A) Drop the public synonym FRRGRNL and cr eate the public synonym FRRGRNL_WORK by executing the script FGRNBLD1.SQL, located in $BANNER_HOME/finance/plus. To do this, invoke SQLPlus and run the procedure. sqlplus fimsmgr/password start fgrnbld1 /nSeptember 2014 Banner Finance 8.10 1-39 TRM Supplement Reports and Processes Run the FGRGRBD Grant Rebuild Process (Part B) Execute the C program FGRGRBD from the co mmand line and respond to the on-screen messages and prompts. For additional inform ation about parameters, refer to the documentation for FGRGRBD in fiChapt er 4: Reports and Processesfl of the Banner Finance User Guide .Locate the program in the directory $BANNER_HOME/general/exe. Recreate the Public Synonym FRRGRNL (Part C) Drop the public synonym FRRGRNL _WORK and re-create the original public synonym by executing FGRNBLD2.SQL located in $BANNER_HOME/finance/plus. To do this, invoke SQLPlus and run the procedure. sqlplus fimsmgr/password start fgrnbld2 Compile Objects in an Invalid State (Part D) Run the guraltrb.sql utility script to compil e all BANINST1-owned functions, views, packages and procedures that are in an invalid state. Warning Any time a database object is modi fied (such as renaming the table FRRGRNL), other objects that have interaction with the changed object are marked as Invalid by the database system. You must resolve all such errors. To compile objects which ar e currently in an invalid state, do the following: 1.Invoke SQLPlus and run the procedure. sqlplus general/password start guraltrb 2.At this point you may be prompted to ente r a value for splpref, the spool preference variable. At the prompt Enter value for splpref , simply press Enter without entering a value. If you enter a value, the system ei ther appends the value to the front of the file names shown below, or uses the value as a path to write those files if the value is a valid path.galtrit.sql glisaltr listing /n1-40 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes 3.You may need to repeat this process several times until all dependencies are validated.NoteThe FGRGRBD Grant Rebuild Process is now complete. Deferred Grant Accounting Process (FRRGRNT) Compilation of FRRGRNT The source code for the Deferred Grant Accounting Process is composed of two C programs: frrgrnt.pc linked with frpgrnp.pc.If the FRRGRNT process needs to be compiled , there is a mass generation script provided with Banner Finance. The script ( fimcmplc.shl) is located in $BANNER_HOME/ finance/misc. This script will compile all Fi nance C programs in a UNIX environment. At the bottom of the script are statements necessary to compile the Deferred Grant Accounting Process. They are described below. UNIX#Compile and link BANNER Finance Deferred Grant Accounting Process# # # Only those objects that have changed have to compiled. The # LAST Line should always be included to re-link the objects. # # make -f $BANNER_HOME/general/c/sctproc.mk frpgrnp.o # make -f $BANNER_HOME/general/c/sctproc.mk frrgrnt LINKOBJS="$BANNER_HOME/general/exe/frpgrnp.ofl If the process aborts, you must delete the record from the FOBPROC table using the following SQL:EXEC SQL DELETE from fobproc where fobproc_proc_code=™FRRGRNT™ and fobproc_proc_type_code=™C™ /nSeptember 2014 Banner Finance 8.10 1-41 TRM Supplement Reports and Processes Transaction Interfac e Process (FGRTRNI) Compilation of FGRTRNI The source code for the Transaction Interface Process program is composed of two C programs: fgrtrni.pc linked with fgpabal.pc.If the FGRTRNI process needs to be compiled, th ere is a mass generation script provided with BANNER Finance. The script ( fimcmplc.shl) is located in $BANNER_HOME/ finance/misc. This script will compile all Fi nance C programs in a UNIX environment. At the bottom of the script are statements neces sary to compile the Transaction Interface Process. They are described below. UNIX#Compile and link BANNER Finance Transaction Interface Process # # # Only those objects that have changed have to compiled. The # LAST Line should always be included to re-link the objects. # # make -f $BANNER_HOME/general/c/sctproc.mk CHECKOPT=flsqlcheck=full userid=baninst1/u_pick_itfl fgpabal.o # make -f $BANNER_HOME/general/c/sctproc.mk fgrtrni LINKOBJS="$BANNER_HOME/general/exe/fgpabal.ofl Posting Process (FGRACTG) Compilation of FGRACTG The source code for the Posting Process is co mposed of twelve linked C programs and an include file. Łfgpabal.pc Łfgpbody.pc Łfgpexxx.pc Łfgpinvm.pc Łfgpstor.pc Łfgppdoc.pc Łfgpgran.pc /n1-42 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Łfgpfixa.pc Łfrpgrnp.pc Łfgpeocd.pc Łfgractg.pc Łfgaprot.h In addition, some functions are also used from the following general C library programs. Łguaorac.c Łguaorac2.pc Łguarpfe.c Łguastdf.c The general include files are Łguarpfe.h Łguastdf.h Łguaerror.h There are two mass generation scripts provided with BANNER FINANCE. The scripts (fincmplc.shl , fincmplc.pl) are located in $BANNER_HOME/finance/misc. These scripts will compile all Finance C programs in UNIX and Wi ndows environments respectively. At the bottom of each file are statements necessary to compile the posting process. They are described below. UNIXcd $BANNER_LINKS # # Compile and link BANNER Finance C Programs for POSTING # # You only need to compile the Posting objects that have changed. The make line must always be included to re-link all the objects. #Compile and link BANNER Finance POSTING # # # Only those objects that have changed have to compiled. The # LAST Line should always be included to re-link the objects. # make -f $BANNER_HOME/general/c/sctproc.mk CHECKOPT="sqlcheck=full userid=baninst1/u_pick_it" fgpabal.o /nSeptember 2014 Banner Finance 8.10 1-43 TRM Supplement Reports and Processes make -f $BANNER_HOME/general/c/sctproc.mk CHECKOPT="sqlcheck=full userid=baninst1/u_pick_it" fgpinvm.o make -f $BANNER_HOME/general/c/sctproc.mk CHECKOPT="sqlcheck=full userid=baninst1/u_pick_it" frpbill.o make -f $BANNER_HOME/general/c/sctproc.mk fgpbody.o make -f $BANNER_HOME/general/c/sctproc.mk fgpeocd.o make -f $BANNER_HOME/general/c/sctproc.mk fgpexxx.o make -f $BANNER_HOME/general/c/sctproc.mk fgpfixa.o make -f $BANNER_HOME/general/c/sctproc.mk fgpgran.o make -f $BANNER_HOME/general/c/sctproc.mk fgppdoc.o make -f $BANNER_HOME/general/c/sctproc.mk fgpstor.o make -f $BANNER_HOME/general/c/sctproc.mk frpgrnp.o # make -f $BANNER_HOME/general/c/sctproc.mk fgractg LINKOBJS="$BANNER_HOME/general/exe/fgpabal.o $BANNER_HOME/ general/exe/fgpbody.o $BANNER_HOME/general/exe/fgpeocd.o $BANNER_HOME/general/exe/fgpexxx.o $BANNER_HOME/general/exe/ fgpfixa.o $BANNER_HOME/general/exe/fgpgran.o $BANNER_HOME/ general/exe/fgpinvm.o $BANNER_HOME/general/exe/fgppdoc.o $BANNER_HOME/general/exe/fgpstor.o $BANNER_HOME/general/exe/ frpbill.o $BANNER_HOME/general/exe/frpgrnp.o" #Windows use sctcomp; $sctcomp_product_dir = "finance"; $sctcomp_input_file_ref = *DATA; &sctcomp_c_process; # # Compile and link BANNER Finance C Programs for POSTING # # You only need to compile the Posting objects that have changed. The linkjobs # line must always be included to re-link all the objects. # fgpabal.pc -checkopt=full -exetype=obj fgpinvm.pc -checkopt=full -exetype=obj frpbill.pc -checkopt=full -exetype=obj fgpbody.pc -exetype=obj fgpeocd.pc -exetype=obj fgpexxx.pc -exetype=obj fgpfixa.pc -exetype=obj fgpgran.pc -exetype=obj fgppdoc.pc -exetype=obj /n1-44 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes fgpstor.pc -exetype=obj frpgrnp.pc -exetype=obj # fgractg.pc - linkobjs="${banner_exe}${sctban_dirsep}fgpabal.${c_obj_ext} ${banner_exe}${sctban_dirsep}fgpbody.${c_obj_ext} ${banner_exe}${sctban_dirsep}fgpeocd.${c_obj_ext} ${banner_exe}${sctban_dirsep}fgpexxx.${c_obj_ext} ${banner_exe}${sctban_dirsep}fgpfixa.${c_obj_ext} ${banner_exe}${sctban_dirsep}fgpgran.${c_obj_ext} ${banner_exe}${sctban_dirsep}fgpinvm.${c_obj_ext} ${banner_exe}${sctban_dirsep}fgppdoc.${c_obj_ext} ${banner_exe}${sctban_dirsep}fgpstor.${c_obj_ext} ${banner_exe}${sctban_dirsep}frpbill.${c_obj_ext} ${banner_exe}${sctban_dirsep}frpgrnp.${c_obj_ext}" #NIGP Tape Load Process Load the NIGP (National Institute of Governmental Purchasing) tape into the Commodity Code Table (FTVCOMM) and Text Table (FOBTEXT) with the SQLLoader utility provided by ORACLE. The SQLLoader User's Guid e explains how to load external files into ORACLE database tables. A control file is provided ( fimsnigp.ctl ) which contains parameters that you must modify before you execute the load process. AssumptionsŁThe supplied fimsnigp.ctl file loads the digit (class-item-group-detail) NIGP file format.ŁLoad the NIGP tape to a disk file with utilities common to your operating system. The tape is in IBM EBCDIC format, unlabeled and 1600 bpi. ŁRecord length for the class-ite m-group-detail file is 466 characters, block length is 4660 characters. When you load the tape to a disk file, load it in the same format as the tape. ŁData from the NIGP tape will be appe nded (added) to the existing FTVCOMM table. Duplicate commodity codes will be in error. ŁLine numbers for the FOBTEXT table start at 10 and increment by ten. There is a possibility of 9 line items of text for any given NIGP Comm odity Code. Line numbers are defined as constants in the control file in the FOBTEXT_SEQ_NUM field. ŁThe commodity text field on the NIGP tape is 450 characters in length. SQLLoader does not allow the manipulation of data in order to determine when or /nSeptember 2014 Banner Finance 8.10 1-45 TRM Supplement Reports and Processes where text begins or ends. Text is extracte d from the file in 50 character increments (as determined by the FOBTEXT_TEXT field size in the FOBTEXT table). It is possible for words in the commodity te xt to be split between two lines. The FOBTEXT table is not updated if the text for a given commodity is 50 characters or less, since the FTVCOMM table contains a 50 character description. ŁSet the Print Indicator on the FOBTEXT table to Y as defined in the control file (FOBTEXT_PRNT_IND ). This text appears on all Purchase Orders. ŁThe Unit of Measure Code (UOMS) on the NI GP tape is four characters in length. The UOMS code on the FTVCOMM table is three characters in length. Only the first three characters of the NIGP UOMS code is loaded onto the FTVCOMM table. ŁThere are no UOMS codes for NIGP record type 1 (Class Record), type 2 (Item Record) or type 3 (Group Record). Si nce the FTVCOMM table requires a UOMS code, the UOMS code is valued with a constant N/A when loading the FTVCOMM table with these record types. Code N/A must exist on the FTVUOMS table. ŁOnce you load the FTVCOMM and FOBTEXT tables, review and correct all commodity entries, if necessary. This is es pecially important since the text on the FOBTEXT and FTVCOMM tables print on the Purchase Orders. 1.Load the NIGP tape to a xxxxxxxx.dat file. You should appe nd the filename with .dat to indicate a data file. Load only the class-item-group-detail file (record length 466, block length 4600) contained on the tape . Ignore the class-ite m index file (record length 65 block length 6500). 2.Edit the fimsnigp.ctl file and change all date references accordingly. The INFILE parameter should contain the filename that you loaded in Step 1. 3.Sign on to the operating system. 4.Start the SQLLoader by typing: sqlloader userid/password fimsnigp.ctl (ORACLE version prior to 6.0.36)sqlldr userid/password fimsnigp.ctl (ORACLE version 6.0.36 and higher)When SQLLoader begins an execution, it creates a log file ( .log). The log file contains a detailed summary about the execution with the following sections: ŁHeader ŁGlobal InformationŁTable Information ŁData File Information ŁTable Load Information /n1-46 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes ŁSummary Statistics As it executes, SQLLoader creates a file called the bad file ( .bad). This file contains all records which the loader attempts to insert but cannot. It is written in the same format as the data file, so you can load the rejected data with the existing control file after you edit it. Rerun steps 2 and 3 to process the corrected data. Remember, the INFILE parameter in the .ctl file must reflect the name of the corrected data file. The log file indicates the ORACLE error for each rejected record. NoteOnce you load these tables, review the FTVCOMM and FOBTEXT entries, because the text descript ions print on the Purchase Orders. Finance Check Processing Four processes comprise the Banner Finance Check writing process: ŁFABCHKS - selects the invoices to be paid ŁFABCHKP - prints the checks ŁFABCHKR - produces the check register ŁFABCHKA - creates the cash disburseme nts for the checks written in FABCHKP NoteThese procedures are only examples of how to run checks at your installation. Due to differences in releases of the operation system and potential differences in si te specific modification s to the operating system at your installation, these procedures may not run exactly as delivered. Please modify them to meet your site specific standards as necessary. FABCHK1 is a procedure you use, in conjunc tion with job submission, to facilitate the check writing process. There are currently five different types of processes which you can run from the Job Submission system: ŁR = Oracle Report ŁC = ProC process ŁP = Procedure (UNIX or Windows script) ŁE = Executable ŁJ = Java Process /nSeptember 2014 Banner Finance 8.10 1-47 TRM Supplement Reports and Processes FABCHK1 is a procedure which executes ProC. FABCHK1 executes FABCHKS and FABCHKP to select and print checks. You cannot combine all four of the processes into one procedure for the following reasons: ŁA procedure within job submission can only point to one printer; FABCHKP and FABCHKR require different printers becaus e of the special forms requirement of the check print. ŁYou cannot execute FABCHKA unless FABCHKP succeeds. The definition of FABCHK1 appears on the GJAJOBS form as a procedure. When you enter FABCHK1 in the Job Name field, it submits FABCHK1 from the GJAPCTL form. The operating system is dete rmined within job submission prior to when you execute FABCHK1. Job submission realizes the FABCHK1 is a procedure and executes it according to the operating system. Following are the respective procedures that the job submissi on process selects according to the operating system that you use: UNIX Shell Script FABCHK1.shl Windows FABCHK1.plAfter you execute the procedure, if you do no t wish to send the output to the printer (selection on GJAPCTL), the output exists in y our home directory. Review the notes in the procedure as they pertain to your operating system. Receiving/Matching Process (FABMATC) The Control Parameters determine the pr ocessing of the Receiving Goods Form (FPARCVD) and the Invoice Form (FAAINVE) . The Receiving Matching Process uses the information you enter in to the documents and govern s the actual payment of the invoice. As mentioned previously, once you value the Invoice Amount Requiring Receipt field on the System Control Maintena nce Form, the system marks any invoices that exceed this value with a status of R when they are complete. The Receiving/Matching Process (FABMATC) selects all invoices with a status of R, and finds the associated receiving data. Those invoices which have receiving data that is not in suspense have the Complete indicator reset from R to Y. If you turn on approvals for Invo ices, the system forwards the document to the Approvals in Process Table (F OBAINP), and processes it according to the approval queue and routing criteria which apply. If you do not turn on approvals for Invoices, the document is fo rwarded to the Approved Documents Table (FOBAPPD) to be processed in the next posting run. /n1-48 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Those invoices that either do not have suffic ient receiving quantities, or have receiving data that is in suspense retain a status of R, and are evaluated the next time you run the Receiving/Matching Process. The audit report from this process is divided into four sections. ŁThe first section displays a list of those invoices which have been matched and completed. ŁThe second section displays a listing of in voices that have sufficient quantity received for matching, but some or all of the necessary quantity is received after the invoice fiscal period. (This section applies only when the parameter Exclude Future Receipts is Y.) Local procedures must address the correct way to proceed. ŁThe third section of the report displays those invoices for which there is either no receiving information, or insufficient (accepted quantity is less than approved quantity) receiving information. Again, local procedures must address the correct way to proceed. Please see the Banner Finance User Guide fo r more information.ŁThe fourth section, entitled Receiving Analysis Report, displays those invoices for which the receiving data is in suspense. This typically means that there was an over-shipment amount for which the user who processed the re ceiving information was not authorized to approve (see the Set-Up Control Parameters discussion). Local procedures dictate the appropriate action. There may be some invoices with no receiving information because the purchase is for something which is not formally received, like a service. Y our site policy might be to create a packing slip document for the purcha se. Alternatively, if there is no receiving information against a Purchase Order, the invoice form (F AAINVE) enable you to enter the Quantity Accepted field to record that an item has been received for the purpose of completing the invoice. In some cases, there may be a timing issue. For instance, the invoice may be processed in A/P before the goods are received or processed. It might be an expediter's job to follow up, and ensure that the goods are shipped from the vendor, and that receiving is performed. For situations such as this, you must review the invoices on the seco nd and third sections of the report. You cannot mark complete invoices held up because of receiving information in suspense, until you resolve or override the suspen se condition. Invoices with insufficient or no rece iving data remain on the report until you enter the receiving data. This is either because the goods finally arrive, or because you update the invoice itself with the receiving information. You can run this process as often as local pr ocedures can accommodat e. It is to your advantage to run it at least once prior to th e posting run prior to the check run. This ensures that all of the current matched invo ices are posted and selected by the check process. /nSeptember 2014 Banner Finance 8.10 1-49 TRM Supplement Reports and Processes Year-End Processing Checklist The year end procedures are discussed in detail in the Banner Finance User Guide. The following provides a quick reference ch ecklist for the year-end processes. Form Action Required FTMCOASVerify that the Encumbrance, Budget Carry Forward and Document Roll Parameters in th e Parameters Window of this form accurately reflect your site policies. Verify that you have a valid Fund e Account entered on this page as well. Your Chart of Accounts must be in effect prior to the year-end jobs you schedule on the Year End Ledger Maintenance Form (FGAYRLM). FTMACTLTo roll any of or your encumbrances as C(ommitted), you must set up the appropriate Prior Year accounts. FTMSDATThere must be valid System ID entries for the Encumbrance Roll, (ENCROLL), the Budget Carry Forward (BDGTFRWD), and the General Ledger Close (GLCLOSE). There must also be an entry specifying E090 as the FGBTRNI_RUCL_CODE for FGBTRNI. FTMFSYRYou must set up your new fiscal year. Also, make sure that you set the Accrual Period in both the current and future fiscal years to N (Not Opened). FGAYRLMSchedule your year-end jobs. Run these in the order in which they appear on the form. The first four characters of the document number must be unique. Use distinct document numbers for each Chart of Accounts for which you run the jobs. Remember, you do not need to run any of the year-end jobs to run processing in the new year. Run the following processes: 1.Run the Balance Forward Report (FGRGLRL). This rolls the General Ledger account balances forward into the new fiscal year. The Control Accounts from the prior year close to th e Fund Balance in the new fiscal year. This opens the Accrual Period. /n1-50 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Archive/Purge Warning Always back up your data before running the Archive/Purge Process. For additional information about the Archive/Purge process, refer to the Processing chapter in the Banner Finance User Guide .FGAYRLM (cont.)Each of the following processes inserts records into the Transaction Interface Table (FGBTRNI). Therefore, after each process you must run the Inte rface Process (FGRTRNI) to edit the transactions, then the Transaction Error Report (FGRTRNR) to display your errors , if any. Correct your errors, and run the Posting Program (FGRACTG) to post the transactions. 2.Run the Roll Open Encumbrances Report (FGRENRL). This rolls the encumbrances specified on the Chart of Accounts record into the new fiscal year. The Encumbrance Control and related offset accounts are updated in the new year with this process. 3.Run the Roll Remaining Budget Balance Report (FGRBDRL). This is the re port produced through the Budget Carry Forward Process. This brings the remaining budget from the prior year into the new fiscal year according to the parameters defined on the Chart of Accounts record. The Budget Control Accounts are updated in the new year. (This is the budget not already rolled in conjunction with the encumbrance roll.) 4.Run the Close General Ledg er Report (FGRCLOP). This is the report produced through the Close Operating Accounts Process. This closes the Control, Fund Additions and Fund De ductions accounts in the prior year to the Fund Balance account in the prior year. FTMFSYRClose the Accrual Period in the prior fiscal year. Form Action Required /nSeptember 2014 Banner Finance 8.10 1-51 TRM Supplement Reports and Processes Indexes The Archive/Purge Process is time-consuming but can be managed by understanding the indexes involved in the process. This section will discuss the two sets of indexes which are critical to the Archive/Purge Process. Create these indexes prior to running Archive/Purge and delete these indexes at the end of the pro cess. Another consideration is the size of the indexes on the production FGBTRND and FGBTRNH tables. You should examine your intended usage of these tables in the archive database and consider removing them to conserve disk space, if appropriate. The two specific Archive/Purge Process indexes are fgbtrnh_arcpur_index and fgbtrnd_key7_index. The fgbtrnh_arcpur_index contains the following fields: fgbtrnh_cos_code fgbtrnh_fsyr_code This index contains the primary sort paramete rs for the archiving of data and is necessary to optimize performance of the Archive/Purge Process. The fgbtrnd_key7_index contains the following fields: fgbtrnd_doc_code fgbtrnd_doc_sequence_code fgbtrnd_seq_num fgbtrnd_item_num fgbtrnd_submission_num fgbtrnd_reversal_ind fgbtrnd_serial_num The primary purpose of this index is to optimize retrieval of the FGBTRND table. These two indexes contain the additional filters that determine whether or not data is archived. The fgbtrnh_arcpur_index and the fgbtrnd_key7_index are to be created prior to run time of the Archive/Purg e Process. These two indexes will reduce the execution time of the Archive/Purge Process. To minimize disk space utilization and avoid adverse effects to existing production process es, delete these indexes upon completion of the Archive/Purge Process. If your Archive/Purge/Restore processing pr imary access is to the production FGBTRNH and FGBTRND tables, then the instructions in Part A1 an d Part A2 can be used to construct the Archive/Purge specific indexes. If your Archive/Purge/Restore processing /n1-52 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes primary access is to the archive FGBTRNH and FGBTRND tables, then the instructions in Part B1 and Part B2 can be used to co nstruct the Archive/Purge specific indexes. Part A1 To install the fgbtrnh_arcpur_index on FIMSMGR.FGBTRNH :1.Invoke SQLPlus. sqlplus fimsmgr/password 2.Use the Oracle Metadata API (DBMS_MET ADATA) or similar tool to query the storage parameters for the index fgbtrnh_key_index . You can use these saved storage parameters for constructing this fgbtrnh_arcpur_index orre-size to suit your needs. 3.Using the appropriate storage parameters (as determined in Step 1), run the procedure: start fgbtrnh1.sql Part A2 To install the fgbtrnd_key7_index on FIMSMGR.FGBTRND :1.Invoke SQLPlus. sqlplus fimsmgr/password 2.Use the Oracle Metadata API (DBMS_MET ADATA) or similar tool to query the storage parameters for the index fgbtrnd_key_index. You can use the saved storage parameters for constructing this fgbtrnd_key7_index or re-size to suit your needs.3.Using the appropriate storage parameters (as determined in Step 1), run the procedure: start fgbtrnd2.sql Part B1 To install the fgbtrnh_arcpur_index on FIMSARC.FGBTRNH :1.Invoke SQLPlus. sqlplus fimsarc/password 2.Use the Oracle Metadata API (DBMS_MET ADATA) or similar tool to query the storage parameters for the index fgbtrnh_key_index . You can use these saved storage parameters for constructing this fgbtrnh_arcpur_index or re-size to suit your needs./nSeptember 2014 Banner Finance 8.10 1-53 TRM Supplement Reports and Processes 3.Using the appropriate storage parameters (as determined in Step 1), run the procedure: start fgbtrnh1.sql Part B2 To install the fgbtrnd_key7_index on FIMSARC.FGBTRND :1.Invoke SQLPlus. sqlplus fimsarc/password 2.Use the Oracle Metadata API (DBMS_MET ADATA) or similar tool to query the storage parameters for the index fgbtrnd_key_index . You can use the saved storage parameters for constructing this fgbtrnd_key7_index or re-size to suit your needs.3.Using the appropriate storage parameters (as determined in Step 1), run the procedure: start fgbtrnd2.sql Both of the FIMSARC tables (FGBTRNH and FGBTRND) should have indexes related to the following fields before processing begins: ŁDOC CODE ŁDOC SEQ CODEŁSEQ NUMŁITEM NUM ŁSUBMISSION NUMBERŁREVERSAL INDŁSERIAL NUMNoteIf FIMSARC.FGBTRNH does not have such an index (in addition to the fgbtrnh_arcpur_index ) refer to ActionLine FAQ 7418 for assistance in creating the appropriate index. In addition to the Archive/Purge specific in dexes, the existing indexes of the production FGBTRND and FGBTRNH tables must be examined. Database tools can be used to retrieve index information for the production FGBTRND and FGBTRNH tables. The FGBTRND table has several associated inde xes which are used for various performance boosts with posting and online query forms. Whether or not a clone of these production /n1-54 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes indexes is necessary on the archive tables for your site depends upon how often you intend to use the FIMSARC online forms to view your archived data online. NoteRefer to ActionLine FAQ 1-4SRHEG for more information about database tools such as Oracle's Metadata API and its DBMS_METADATA package The indexes used in the Archive/Purge Process were optimized through testing at our early support site. The indexes associated with the archive FGBTRNH and FGBTRND tables (FIMSARC.FGBTRND and FIMSARC.FGBTRNH ) should be utilized based on your site™s expected usage of the archived data. You should also consider the method by which archived data will be retrieved. The early su pport site verified the results using only the fgbtrnd_acct_index. This allowed the data to be verified but only allowed the data to be searched by the accounting FOAPALs. You should verify the most likely method of retrieving the archived data with yo ur users prior to creating each index. Parameter List Archive/Purge Process (FOPARCP) Parameter NameOptional/ Required Entry DescriptionParameter Sequence Number OptionalSystem-generated Hours of Processing Parameter (Number) OptionalIndicates duration of time to process. If null, processing will continue until all relative data is processed. Otherwise, when actual execution time exceeds this parameter va lue, processing will come to a programmatic termination. If a document is currently being processed for archive/purge, then that cycle will complete prior to termination. Action Indicator (A/P) RequiredIndicates which action this process will perform. (A)rchive will copy data from the production tables to the archive ta bles. The data is then removed from the production tables. (P)urge will remove data from the prod uction or archive tables. This value is printed in the report header for every page (Action:). If (P)urge is selected, there will be a confirmation (Y/N) of this parameter. If confirmation of purge is Y, processing continues; otherwise, the program terminates./nSeptember 2014 Banner Finance 8.10 1-55 TRM Supplement Reports and Processes Database Indicator (P/A) OptionalIndicates which tables are to be processed: (P)roduction tables or (A)rchive tables. This parameter is only valued when the Action Indicator equals (P)urge. This process can be used to purge data from either the pr oduction tables or the archive tables. The value of this parameter is printed in the report header for every page (next to literal Source:). Execution Mode (A/U) RequiredDetermines whether the process will be run in (A)udit mode or (U)pdate mode. Audit mode will produce a report deta iling potential database activity; however, no database changes will occur. Update mode will produce the report and update the database. The database is determined by the value of the Action Indicator. This value is printed in the report header for every page (next to literal Mode:). Perform Bank Reconciliation Edit (Y/N) OptionalIndicates whether or not to perform the edits for bank reconciliation data (some Banner Finance sites reconcile check transaction processing and some do not). An entry of Y will perform these edits as part of the archive or purge execution. The default is Y.Perform Grant Edit (Y/N) OptionalIndicates whether or not to perform the edits for grant related data. An entry of Y will perform these edits as part of the archive or purge execution. The default is Y.Grant Purge Years Tolerance (Integer)OptionalThis parameter is asked only if: (1) the grant edit is being performed and (2) a purge is taking place. Government regulations may require that grant data should not be deleted until 5 years after its termination date. The default is 5. Print Mode (D/E)OptionalWhen the Execu tion Mode is set to (A)udit, you may choose the Print Mode. A Print Mode set to (E)xceptions may produce a smaller, more manageable report. An Execution Mode of (U)pdate requires a Print Mode of (D)etail. The detail version of the report will include the exceptions data provided with an exception print mode execution. Parameter NameOptional/ Required Entry Description/n1-56 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Chart of Accounts Code RequiredMultiple Chart of Acco unts codes are allowed. The data in this field is compared to the chart of accounts field in the FGBTRNH table (if archiving or purging from the production database) or FGBTRNH_ARCHIVE table (if purging from the archive database). If the codes match, the associated records from the driver and related tables will be archived or purged. All charts are validated against the Char t of Accounts Validation Table (FTVCOAS). This verifies that the chart(s) exist; there is no effective or termination date checking. Fiscal Year (YY)RequiredMultiple fiscal years are allowed. The data in this field is compared to the fiscal year in the related table. If the fiscal years match, the associated records from the driver and related tables are archived or purged. The fiscal year is used in conjunction with the Ch art of Accounts to determine if the fiscal year is closed. If the fiscal year is not closed, the combination of fiscal year and Chart of Accounts is invalid and the data cannot be archived or purged. The fiscal year cannot be a current year or prior year with regard to concurrent year processing functionality. An archive/purge process can be reissued for the same chart of accounts and fiscal year relative to a document that has been pa rtially archived or to check reconciliation.Number of Printed Lines per Page RequiredThe number of printed lines on a page; if left blank, this parameter defaults to 55.Parameter NameOptional/ Required Entry Description/nSeptember 2014 Banner Finance 8.10 1-57 TRM Supplement Reports and Processes Restore Process (FOPARCR) Parameter NameOptional/ Required EntryDescription Parameter Sequence Number OptionalSystem-generated Hours of Processing Parameter (Number) OptionalIndicates duration of time to process. If null, processing will continue until all relative data is processed. Otherwise, when actual execution time exceeds this parameter value, processi ng will terminate. If a document is currently being processed for restore, then that cycle will complete prior to termination. Execution Mode (A/U) RequiredDetermines whether the process will be run in (A)udit mode or (U)pdate mode. Audit mode will produce a report detailing potential data base activity; however, no database changes will occur. Update mode will produce the report and update the database. The value of this parameter is printed in the re port header for every page (next to literal Mode:).Print Mode (D/E) OptionalWhen the execution mode is set to (A)udit, you may choose the Print Mode. A Print Mode set to (E)xceptions may produce a smaller, more manageable report. An Execution Mode of (U)pdate requires a Print Mode of (D)etail. The detail version of the report will include the exceptions data pr ovided with an exception print mode execution. Chart of Accounts Code RequiredMultiple Chart of Accounts codes are allowed. The data in this field is compared to the chart of accounts field in the related table. If the codes match, the associated records from the driver and related tables will be restored. All charts are validated against the Chart of Accounts Validation Table ( FTVCOAS). This verifies that the chart(s) exist; there is no effective or termination date checking. /n1-58 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Output As you run the Archive/Purge Process, you will see screen output similar to the following: Starting FOPARCP indicates the release version of Banner Finance. Select Count indicates how many FGBTRNH records have been read. Execution Time indicates approximately how long the process has been running. Time Parameter Each time you run the Archive/Purge Process, you will be prompted to enter the Hours of Processing Parameter. The Archive/Purge Process is designed to be run during off-production hours. This para meter enables you to specify a definitive window of time in which to run the process. The purpose of th is parameter is to allow the Archive/Purge Process to run to completion while giving the user the flexibi lity to limit its run time. The limits imposed by your production schedul e can be managed using the Hours of Processing Parameter. The usage of this parameter does not require the Archive/Purge Fiscal Year (YY) RequiredMultiple fiscal years are a llowed. The data in this field is compared to the fiscal year in the related table. If the fiscal years match, the associated records from the driver and related tables are restored. The process checks to make sure that a row exists on FTVFSYR for each chart and fiscal year combination. Number of Printed Lines per Page RequiredThe number of printed lines on a page; if left blank, this parameter defaults to 55.Username: fimsusr Password: Hours of Processing Parameter [Number]: 1 Action Indicator Archive/Purge [A/P]: A Execution Mode Audit/Update [A/U]: A Print Mode Detail/Exceptions [D/E]: D Chart Of Accounts: A Chart Of Accounts: Fiscal Year (YY): 94 Fiscal Year (YY): Perform Bank Reconciliation Edit [Y/N]: N Number of printed lines per page [55]: Starting FOPARCP (Rel 2.0.7) ... Select Count = 0 Execution Time(HH:MM) = 0:0 Starting FOPARCP (Rel 2.0.7) ... Select Count = 5000 Execution Time(HH:MM) = 1:48 Parameter NameOptional/ Required EntryDescription /nSeptember 2014 Banner Finance 8.10 1-59 TRM Supplement Reports and Processes Process to be restarted; you may complete a partial archive and later complete the process based on your production schedule. Reports and Processes Matrix Reports and Processes Attributes Legend Report or Process The report/batch process name Language Identifies the language for the process - COBOL, C, RPT, SQL, or PL/SQL. Update/QueryDoes the process update any tables, or is it strictly a query-only report? AuditCan you run the update process in Audit Mode, so that you can produce the report without an update taking place?Note: Yes appears in this co lumn only if the process permits both update and audit mode. If the report is query only, Yes does not appear in this column. Job Submission Can you run the process via job submission? Sleep/Wake Is the process u sed in conjunction with Sleep/Wake? Off Peak Is it recommended th at you defer this program to an off-peak processing time (late night, weekends) for performance reasons? RestartIf the process aborts or is terminated after the process is initiated, can you restart the process without any adverse consequences? Note: Yes does not appear in this column if the job can be restarted without special procedures. Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart FAB1099CQuery FAB199K CQuery Yes /n1-60 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FABCHKACUpdateYes FABCHKD CUpdateYes Yes FABCHKPCUpdateYesYes FABCHKR CQuery Yes Yes FABCHKSCUpdateYesYes FABMATC CUpdateYes FAM1099CQuery FAPCARD PROC UpdateYes FAPCDIRCUpdateYes Yes FAPDIRD CUpdateYes Yes FAPINVTPROCUpdateYes FAPTREG CUpdateYes Yes FARAAGECQueryYes FARBBAL CQuery Yes FARBRECCUpdateYes FARCHKR CQuery Yes FARCSHRCQuery Yes FARCSHR CQuery Yes FARDIRDCUpdate Yes Yes FARIAGE CQuery Yes FARINVSCQuery Yes FARIREC CQuery Yes FAROINVCQuery Yes Yes FARVALP CQuery Yes Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /nSeptember 2014 Banner Finance 8.10 1-61 TRM Supplement Reports and Processes FARVHSTCQuery Yes FARVNUM CQuery Yes FARWHLDCQuery Yes FARWHLY CQuery Yes FAT1099CQuery Yes FATCHKS CQuery Yes FBRAPPDCQuery Yes FBRAPPR CQuery Yes FBRBDBBCUpdateYes FBRBDDS CUpdateYes FBRBDRLCUpdate FBRFEED CUpdateYes FBRMCHGCUpdateYes FBRWKSH CQuery Yes FCBBILLCUpdateYes FCBEQPT CUpdateYes FCBINVTCUpdateYes FCBLABR CUpdateYes FCBMATLCUpdateYes FCRBDTR CQuery Yes FCRSCHDCQuery Yes FCRVARA CQuery Yes FEPOEXTCUpdateYesYes FFPDEPR CUpdateYes Yes Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /n1-62 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FFPOEXTCUpdateYesYes FFRAGRP CQuery Yes FFRDTGACQuery Yes Yes FFRDTGT CQuery Yes Yes FFRMASTCQueryYes FFRPROCCQuery Yes FFRPROPCQuery Yes FGPDROL JavaUpdateYes Yes Yes FGPGEXTCUpdateYes Yes FGPOCFP JavaUpdateYes FGRACCICQuery Yes FGRACTGCUpdateYes Yes FGRACTHCQuery Yes FGRACTVCQuery Yes FGRBAVLCUpdateYes YesYes FGRBDRLCUpdateYes FGRBDSCCQuery Yes FGRBIEXCQuery Yes FGRBLSHCQuery Yes FGRCASHCQuery Yes FGRCBSRCQuery Yes Yes FGRCGBACQuery Yes Yes FGRCGBSCQuery Yes Yes FGRCHFBCQuery Yes NoReport or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /nSeptember 2014 Banner Finance 8.10 1-63 TRM Supplement Reports and Processes FGRCHNACQuery Yes No FGRCLOPCUpdateYes NoFGRCOBSCQuery Yes Yes FGRCREFCQuery Yes Yes FGRCSBACQuery Yes Yes FGRCSCFCQuery Yes Yes FGRCSRECQuery Yes Yes FGRCSRPCQuery Yes FGRCSSRCQuery Yes Yes FGRCTRLCUpdateYes FGRCUNACQuery Yes FGRENRL CUpdateYes FGRFAACCQuery Yes FGRFBAL CQuery Yes FGRFITDCQuery Yes FGRFNDH CQuery Yes FGRFPSNCQuery Yes FGRGLEX CQuery Yes Yes FGRGLRLCUpdateYes FGRGLTA CUpdateYes FGRGRBDCUpdateYes FGRIDOC CQuery Yes FGRJVLRCQuery Yes FGRLOCH CQuery Yes Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /n1-64 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FGRODTACQuery Yes FGROPNE CQuery Yes FGRORGHCQuery Yes FGRPDTA CQuery Yes FGRPRAPCQuery FGRPRAR CUpdateYes Yes FGRPRGHCQuery Yes FGRREOBCQuery Yes FGRREOCCQuery Yes FGRTAXR CQuery Yes FGRTBALCQuery Yes FGRTBEX CQuery Yes FGRTOFRCQuery Yes FGRTRNH CQuery Yes FGRTRNICUpdateYes FGRTRNR CQuery Yes FIRBVALCQuery Yes FIRDIST CUpdateYes FIRLINKCQuery Yes FIRPVAL CQuery Yes FIRRDSTCUpdateYesYes FNPGAIN CUpdateYes Yes FNPSPNDCUpdateYesYes FNPUNTZ CUpdateYes Yes Yes Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /nSeptember 2014 Banner Finance 8.10 1-65 TRM Supplement Reports and Processes FNRHISTCQueryYesYes Yes FNRPRNC CQuery Yes Yes Yes FNRSPNCCQueryYesYesYes FOPARCR CUpdateYes Yes Yes FORAPPLCUpdateYesYes FPABIDD CUpdateYes FPACORDCUpdateYes FPAPORD CUpdateYes FPARQSTCQuery Yes FPPPOBC CUpdateYes Yes Yes FPRBEVLCQueryYes FPRDELV CQuery Yes FPROPNPCQuery Yes FPROPNR CQuery Yes FPRRCDLCQuery FPRRCST CQuery Yes FPRVCATCQuery Yes FPRVVOL CQuery Yes FRBINFCUpdateYesYes FRPGINF CUpdateYes Yes FRPMESGCUpdate Yes FRR134B OracleQuery Yes Yes FRR269ROracleQueryYesYes FRR270B OracleQuery Yes Yes Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /n1-66 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes FRR272BOracleQuery Yes Yes FRR272R OracleQuery Yes Yes FRRABUDCUpdateYesYesYes FRRBDEX CQuery Yes Yes FRRBEXCCQueryYesYes FRRBILL CUpdateYes Yes Yes Yes FRRBREVCUpdateYesYes FRRBUDG CQuery Yes Yes FRRCNSFOracleQueryYesYes FRREVNG CQuery Yes Yes FRREVNPCQueryYesYes FRRFEXC CQuery Yes Yes FRRGBFYCQueryYesYes FRRGENB OracleQuery Yes Yes FRRGENROracleQueryYesYes FRRGRNP CUpdateYes Yes FRRGITDCQuery Yes Yes FRRGRNT CUpdateYes Yes Yes FRRGRPTCUpdateYesYes FRRINDC CQuery Yes Yes FRRINVSCQueryYesYes FRRTRNR CQuery Yes FSRDTLGCQuery FSRINVL CQuery Yes Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /nSeptember 2014 Banner Finance 8.10 1-67 TRM Supplement Reports and Processes FSRISSTCUpdateYesYes FSRLWSR CQuery Yes FSROPNRCQuery Yes FSROUTP CQuery Yes FSRPHYRCUpdateYes FSRPICK CQuery Yes FSRPIDRCQuery Yes FSRPIWS CQuery Yes FSRPUTLCQuery Yes FSRSTEX CQuery Yes FSRSUPCCQuery Yes FUPLOAD CUpdateYes Yes FURAPAYCUpdateYes FURFEED CUpdateYes NBPBROLCUpdateYes Yes NBPBUDMCUpdateYes Yes Yes Yes NBPMASSCUpdateYesYesYesYes NBRBWRKCQuery Yes Yes NBRPCLSCQueryYesYes NBRPINCCQuery Yes Yes NBRPOSNCQueryYesYes NBRPSTA CQuery Yes Yes NHPFIN1CUpdateYesYesYes NHPFIN2CUpdateYes Yes Yes Yes Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /n1-68 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Running Oracle Reports with Finance When running through Internet Native Banner, Reports is not currently able to access the default role defined for the Report in the bansecr schema. Therefore, product-specific roles are being created with the base privileges needed to run the Repo rts, which must be granted to any user who will run Reports for that product using Internet Native Banner. In the Finance product fiplusfl sub-directory, you will find fimorep.sql. This script will create the necessary role and assign the privileges needed to run the Reports. Please review this script before applying it to your production database. Invoke SQLPlus and run the procedure: sql_cmd system/password [ENTER] start fimorep [ENTER] Next, grant the role to the users who will be running the Reports. For example,sql_cmd system/password [ENTER] NHRBDSTCQueryYesYes YesYes NHRDISTCQuery Yes Yes Yes Yes NHRECRTCUpdateYesYesYesYes NHREDSTCQuery Yes Yes Yes Yes NHRSDSTCUpdateYesYesYesYes TRRAGES CQuery Yes Yes TRRAPPLCUpdateYesYes TRRCOLL CQuery Yes Yes TRRRCONCQueryYesYes TRRUNAP CUpdateYes Yes TRRUNPLCQueryYesYes Report or Process Language Update/ QueryAuditJob SubmissionSleep/ Wake Off PeakRestart /nSeptember 2014 Banner Finance 8.10 1-69 TRM Supplement Reports and Processes grant ban_finance_rep to fiyour_userfl [ENTER] alter user fiyour_userfl default role ban_default_connect, ban_finance_rep NoteThe fialter userfl statement should incl ude all of the user™s default roles. Refer to sys.dba_role_privs for existing roles where default_role =YES for fiyour_userfl as grantee. SQLPlus ScriptsFinance SQLPlus procedures are provided to assist you as you produce summary reports. Refer to the General Technical Reference Manual for additional SQLPlus procedures. When you install Banner Finance, a command file is provided to run sqlload. If your Oracle Version is 6.0.36 or higher, you must edit this command file: fipcusp.shl for UNIX; fipcusp.shl.com for VAX ; and change the command sqlload to sqlldr.NoteIn Oracle Release 6.0.36, the co mmand name you use to access SQLLoader was changed from sqlload to sqlldr.The procedures are as follows: Procedure NameProcedure Description FIMFORGIssues foreign grants from FIMSMGR to an end user account. FIMGENGGrants required General tables to FIMSMGR fiWITH GRANT OPTIONfl. FIMSTUGGrants required Student tables to FIMSMGR fiWITH GRANT OPTIONfl. FINACTGDetermines if any accoun ting records are ready for the background routine to process (used by the sleep wake up routine). FINSHUTTerminates the background Finance accounting routine. FIMFORDRuns all of the scripts that grant FIMSMGR access fiWITH GRANT OPTIONfl to tables owned by other Banner products, and then runs FIMFORG to grant access to the foreign tables to Finance end users. /n1-70 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes Sleep/Wake-up Banner provides two different mechanisms for running jobs in a cyclical or fisleep/wake-upfl manner. Operating systems which do not have sleep commands, or whose sleep commands may not be executed by user programs, must use the first method. Method OneThe first method uses OS command scripts and an SQLPlus script to cause the job to run in a cyclical fashion. These jobs must be submitted from the operatin g system prompt and must be terminated manually. To compile programs to run in this fashion, you must define the NO_SLEEP_SW as a pre-compiler directive to excl ude the code used by the second technique. FIMALUGGrants required ALUMNI tables to FIMSMGR fiWITH GRANT OPTIONfl. FIMPAYGGrants required PAYROLL tables to FIMSMGR fiWITH GRANT OPTIONfl. FIMPOSGGrants required POSNCTL tables to FIMSMGR fiWITH GRANT OPTIONfl. FIMTRAGGrants required TAISMG R tables to FIMSMGR fiWITH GRANT OPTIONfl. FIMRESGGrants required FAISMGR tables to FIMSMGR fiWITH GRANT OPTIONfl. FIMSBVL1Renames FGBBAVL to FGBBAVL_WORK and FGRBAKO to FGRBAKO_WORK before you run FGRBAVL. FIMSBLV2Renames FGBBAVL an d FGRBAKO after you run FGRBAVL. FIMSCOLDeletes records from all collector tables that you use in Banner Finance. FGBTRND3Optional index which s upports a major access to the FGBTRND data through the use of the organization code. FGBTRND4Optional index which s upports a major access to the FGBTRND data through the use of the fund code. Procedure NameProcedure Description /nSeptember 2014 Banner Finance 8.10 1-71 TRM Supplement Reports and Processes UNIXThe first command procedure, sleepunx , prompts for parameters needed by the second procedure and SQLPlus script, sleepunx.shl and sleepunx.sql respectively. This procedure then starts (or submits) sleepunx.shl , which in turn starts sleepunx.sql . The SQLPlus script sleepunx.sql will spool OS-specific co mmands to run the job into a file, provided there is actually work to do as determined by the parameters previously entered. When the SQLPlus script exits, sleepunx.shl executes the fispoolfl file. The parameters needed by the program are contained in a XXXXXXX.dat file which are read via input redirection when the jo b executes. The second command procedure sleepunx.shl then fisleepsfl for the specified interv al, awakes, and loop s back to start the SQLPlus script again. CMSIn this environment, there is only one command script, sleepcms.exec. Each job run in sleep/wake mode must have its own .exec script. The command script sleepcms.exec is provided as the model. You also need to set up separate CMS accounts for each job or you may combine the execs to run se quentially on a single account. The .dat file is read via input redirection. fiSleepingfl is performed using the CP SLEEP command. Method Two The second method has the program issue the OS-specific fisleepfl command. This is the default compile configuration for these jo bs. They may be submitted from either the operating system prompt or from the GJAPCT L form. If they are submitted manually, the parameters for the job are asked for interactively. If submitted from GJAPCTL, the parameters are read from the GJBPRUN table. Pa rameters used to control the interval are stored in the GJRSWPT table and may be changed as required without canceling and resubmitting the job. To stop th ese jobs, manually terminate th em, or set an indicator on the GJASWPT form. A significant change to sl eep/wake-up using Method Two is that all values used as selection identifiers by th e program must be defined on the GTVPRNT form. This is because the program reads GT VPRNT in order to get the actual OS print command. For Method One, the print comm and is passed as a parameter to the second procedure. VAX Sample COM File When the Banner Finance system starts up, the system executes the COM file for the background accounting routine shown below. This routine wakes periodically, and checks if any records are ready to post. If records exist, the process which actually processes the data is executed. You can terminate this process if you run the FINSHUT SQL procedure. This process terminates the next time th e routine wakes up after you execute the FINSHUT SQL. This process must be started by supplying th e userid password at the system prompt. /n1-72 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes DELETE TEMP.COM;SQLPLUS @FIN$PLUS:FINACTG.SQL @TEMP SUBMIT /NOPRINT/LOG=FINACTG.LOG /AFTER="+00:10" FIN$COM:FINACTG Sample UNIX Script There are three processes in Banner Finance that run on a wakeup basis. 1.FGRTRNI, to process deferred edit do cuments, auto journals, and interfaces 2.FORAPPL, to process documents in the approval process 3.FGRACTG, the general accounting posting process In a UNIX environment, a process loads and stays parsed. Before each cycle, including the first, these processes ask fiEnter any character to processfl, or to terminate. In wakeup mode, the system provides a Y response to this prom pt by an echo command which is output piped to the process. The ech o command is in a loop which contains a sleep command. At each wakeup interval, a Y is echoed into the pi pe, and then the process sleeps for the specified interval. Use a mark er file named using the Oracle instance name and the process name to control the wakeup proc ess. As long as the marker file exists, the wakeup process continues. When you remo ve the marker file, the wakeup process terminates. You also use this marker file to determine if the requested process is already running on wakeup. Place the output of the se processes in two files whose names are built from the UNIX process ID of the original user executed wakeup script. The first file is $$.log which contains the displa ys from the scripts and the error output of the process. The second file is $$.lis which is the output repor t. The $$ in both names is replaced by the UNIX process ID. There are two scripts in this process. ŁThe first, which you execute, is fwakeup.shl. It accepts an optional wakeup interval and the name of the process to be run as parameters. The last parameter is taken as the process name, without the .pc su ffix. If the first parameter is null, then the second parameter is taken as the wake up interval in seconds. If no wakeup interval is given, a default in terval of 600 seconds is used. ŁThe second script is fwakebg.shl, which is called by the first script, and starts the background wake process. You should experiment with different wakeup intervals in your environment. Banner has run on our development environment with an interval as short as 30 seconds with little or no noticeable impact on overall system pe rformance. On a short interval, only a few documents are typically handle d in one cycle. If more documents than your environment can handle in one cycle await processing, another Y is in the pipe when the cycle ends, and the next cycle begins immediately. This con tinues until the wakeup pr ocess has caught up /nSeptember 2014 Banner Finance 8.10 1-73 TRM Supplement Reports and Processes and completes a cycle before the end of the wake up interval. Note that the interval is from the start of one cycle to th e start of the next cycle. This process relies on the piping facilities of UNIX to allow a process to sleep without exiting. As we identify techniques on other pl atforms to accomplish this type of wakeup, we will extend the technique to those platforms. The curre nt version is based on the operating system identifier in GENERAL.GUBINST containing UNIX . On any other operating system, the new prompts do not display, and each process executes one cycle and terminates in the sa me manner as before. If you run a process by hand, be sure to prov ide a non-blank character in response to the first Enter any character to process, or to terminate prompt, or the process terminates without any processing. FWAKEUP.SHL :#! /bin/sh # # unix shell to start a process running as a background task that was # designed to rerun itself in response to an .ASK command. # processes that are run in this fashion must be update only. It# can reexecute very rapidly because the RPT does not have to be reparsed. # # FWAKEUP creates a file called OOOO.RRRRRRR where OOOO is the # current ORACLE_SID and RRRRRRR is the process name being executed. # This file indicates the process has started. If this file is removed, # the wakeup process will terminate on its next cycle. # Define all variables that will be used by this procedure WAKE=600# Wake up frequency in seconds PROCESS=""# Process name being started (last parameter) UID=""# ORACLE used id to sign on as PW=""# The ORACLE password MARK_F=""# Marker File Name LOG_DIR=""# Directory where log files will go PID=$$# This process id number # If no parameters were given just display usage: /n1-74 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes if [ "$1" = "" ] then echo 'Usage is wake [-w nn] process™ echo ' [-w nn] - set wake up interval in seconds.' echo ' default is 600.' exit fi# Ask where the log files are to be placed while [ "$LOG_DIR" = "" ] || [ ! -d "$LOG_DIR" ] do echo "Enter the directory where the log file is to be placed" echo "Enter a null response for the current directory ( pwd )" read LOG_DIR if [ "$LOG_DIR" = "" ] then LOG_DIR=pwd fi done# process all parameters entered on the command line while [ ! "$1" = "" ] do case $1 in -w) WAKE=$2;; ) PROCESS=$1;; esac shift done# If the marker file exists the process may be executing MARK_F="${LOG_DIR}/${ORACLE_SID}.${RPT_N}" echo "Marker file for this process is ${MARK_F}" /nSeptember 2014 Banner Finance 8.10 1-75 TRM Supplement Reports and Processes if [ -r ${MARK_F} ] then echo "Marker file exists, the background process may already be executing" exit fi # Prompt for the ORACLE id to sign on with while [ "$UID" = "" ] do echo "Enter user id: " read UID done# Prompt for the ORACLE password while [ "$PW" = "" ] do echo "Enter password for the $UID account: " stty -echo read PW stty echo done# Create the marker file to indicate the process has started touch ${MARK_F} echo "File ${MARK_F} created, remove it to stop this process" \ | tee ${LOG_DIR}/${PID}.log echo "Processing ${RPT_N}.rpt." |tee -a ${LOG_DIR}/${PID}.log echo "Diagnostic output will be saved as ${LOG_DIR}/${PID}.log" \ |tee -a ${LOG_DIR}/${PID}.log # Export all variables so they can be accessed by the started shellexport WAKE# Wake up frequency in seconds export PROCESS# PROCESS name being started (last parameter) export UID# ORACLE used id to sign on as export PW# The ORACLE password /n1-76 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes export MARK_F# Marker File Name export LOG_DIR# Directory where log files will go export PID# This process id number # start the actual process as a background task sh fwakebg.shl & # Undefine all variables that were used by this procedure WAKE="";export WAKE; PROCESS="";export PROCESS_N; UID="";export UID; PW="";export PW; MARK_F="";export MARK_F; LOG_DIR="";export LOG_DIR; PID="";export PID; exitFWAKEBG.SHL :#! /bin/sh # # unix shell to start a process running as a background task that was # designed to rerun itself in response to an .ASK command. # Processes that are run in this fashion must be only update. # The process can reexecute very rapidly because it does not have to be reparsed. #FWAKEBG creates a file called OOOO.RRRRRRR where OOOO is the # current ORACLE_SID and RRRRRRR is the process name being executed. # This file indicates the process has started. If this file is removed, # the wakeup process will terminate on its next cycle. # # Variables are passed to this process using environment variables # so that passwords and user id never appear on the command line.# Switch to the directory where the log files are to be placed /nSeptember 2014 Banner Finance 8.10 1-77 TRM Supplement Reports and Processes cd $LOG_DIR echo "Background routine has started" >>${PID}.log # Log non security sensitive parameters as a reference echo "Wake up interval set to $WAKE" >>${PID}.log echo "Using user id $UID" >>${PID}.log echo "Log file will be placed in the $LOG_DIR directory" >>${PID}.log # loop running the requested rpt until the marker file is removed(while [ -r ${MARK_F} ] do echo 'Y' sleep $WAKE done) | ${BANNER_HOME}/general/exe/${PROCESS}-o ${PID}.lis$ {UID}/ ${PW}/ >/dev/null 2>>${PID}.log; exitFinance Wakeup FGRACTG Accounting posting routine FGRTRNI Transaction interface routine FORAPPL Document approval routine /n1-78 Banner Finance 8.10 September 2014 TRM Supplement Reports and Processes /nSeptember 2014 Banner Finance 8.10 2-1TRM Supplement Interfaces2Interfaces The Banner Finance system interfaces with the following Banner products: ŁBanner Advancement ŁBanner General ŁBanner Human Resources Interfaces with External User Systems Interfaces within Banner GURFEED Table This table contains financia l transactions from Banner applications which are to be processed by the client's Accounting system via a user interface program. GURAPAY Table This table contains single line invoices from Banner applications which are to be processed by the client's Accounts Payable system vi a a user interface program. GURFEED Table This table contains financial transactions from other Banner applications or clie nt-developed applications which are to be processed into Banner Finance using the FURFEED and FGRTRNI processes. GURAPAY Table This table contains single line invoices from other Banner applications or clie nt-developed applications, which are to be processed into Banner using the FURAPAY process. /n2-2Banner Finance 8.10 September 2014 TRM Supplement Interfaces /nSeptember 2014 Banner Finance 8.10 3-1TRM Supplement Conversion 3Conversion To successfully convert your data, you should have a basic understanding of the Banner Data Element Dictionary (DED), and a report called Tables and Views . You should print hard copies of the General DED and the Fi nance DED when each tape cut is delivered. The DED displays the following information: Conversion Tables On the following pages are the conversion tables you need in each module to convert your data for Banner Finance.General Utilities Column NameColumn Description Field Name Field name Type Character or date field indicator Width Field length Scale Number of digits to the right of the decimal point (Oracle Version 6 only). For example, a scale of 5 indicates one-hundredths of a unit, as in .99Nulls Null/Not Null IndicatorColumn CommentsDescription of the column , abbreviation of th e validation table, and so on. Step 1Execute appropriate General modu le forms to build General Utilities tables. /n3-2Banner Finance 8.10 September 2014 TRM Supplement ConversionGeneral Ledger Step 1Set up a crosswalk file from the ol d accounting distribution to the new COAS, fund, organization, account, program, activity, and location. Step 2Using the crosswalk file, build FGBT RNI (Transaction Input Table) with data to be loaded into Banner Finance. Common Rule Codes that are typically used in this process include JE05 (General Ledger Opening Balance) and BD01 (Original Budget). Step 3Using the appropriate validation maintenance forms, enter user-defined values in the following validation tables: FTVACCI (Account Index Validation Table) ( optional)FTVACCT (Account Validation Table) FTVACTL (Control Account Validation Table) FTVACTV (Activity Validation Table) FTVATYP (Account Type Validation Table) GXRBANK (Bank Validation Table) FTVCOAS (Chart of Accounts Validation Table) FTVFSPD (Fiscal Period Validation Table) FTVFSYR (Fiscal Year Validation Table) FTVFTYP (Fund Type Validation Table) FTVFUND (Fund Validation Table) FTVLOCN (Location Validation Table) FTVORGN (Organization Validation Table) FTVPROG (Program Validation Table) /nSeptember 2014 Banner Finance 8.10 3-3TRM Supplement Conversion Accounts Payable Step 4Run the following: ŁFGRTRNI (Transaction Input Proc essing Report) to build the journal voucher tables: ŁFGBJVCH (Journal Voucher Header Table) ŁFGBJVCD (Journal Voucher Detail Table) and ŁFOBAPPD (Approved Docu ment Table) from the FGBTRNI table built in Step 2 or FOAUAPP (Unapproved Document Table) Note: The FGRTRNI process can place th e feed document in the FOBAPPD (Approved Document Table), the FOAUAPP (Unapproved Document Table), or create the document as incomplete so it can be reviewed on the JV form. The outcome is determined by how you utilize the FGRTRNI process approval feature. Step 5Run FGRACTG (Posting Repo rt) to create these ledgers: ŁFGBGENL (General Ledger Table) ŁFGBOPAL (Operating Account Ledger Table) ŁFGBENCH (Encumbrance Ledger Header Table) ŁFGBENCD (Encumbrance Distribution Table) ŁFGBENCP (Encumbrance Period Detail Table) and these transaction history tables: ŁFGBTRNH (Transaction History Table) ŁFGBTRND (Transaction History Detail Table) Step 1Value the following: ŁSTVATYP (General Address Type Table) ŁFTVDISC (Vendor Discount Terms Table) if using discounts ŁFTVTRAT (Vendor Tax Rate Table) if tax codes are used ŁFTVVTYP (Vendor Type Table) Step 2Convert the following: ŁSPRIDEN (General Person Identification Table) ŁSPRADDR (General Person Address Table) /n3-4Banner Finance 8.10 September 2014 TRM Supplement ConversionPurchasing and Procurement Step 3Convert FTVVEND (Vendor Validation Table) Step 4Enter invoices or convert the following: ŁFABINVH (Invoice Header Table) ŁFARINVC (Invoice Commodity Table) ŁFARINVA (Invoice Accounting Table) For each record inserted into FABINVH (Invoice Header Table), a record should be inserted in FOBDINP (Docum ent in Progress Table). This triggers FGRTRNI (Transaction Input Processing Report) to process and validate each invoice.Step 5Run FGRTRNI (Transaction Input Pr ocessing Report) to add records to FOBAPPD (Approved Document Table) for each invoice. Step 6Run FGRACTG (Posting Report) to update financial ledgers and transaction history tables. Step 1Value the following: ŁSTVATYP (General Address Type Table) ŁFTVBUYR (Buyer Verification Table) ŁFTVSHIP (Ship To Verification Table) Step 2Convert the following: ŁSPRIDEN (General Person Identification Table) ŁSPRADDR (General Person Address Table) Step 3Convert FTVVEND (Vendor Validation Table). Step 4Enter Purchase Orders or convert the following: ŁFPBPOHD (Purchase Order Header Table) ŁFPRPODT (Purchase Order Commodity Table) ŁFPRPODA (Purchase Order Accounting Table) For each record inserted into the FPBPOHD table, a record should be inserted into FOBDINP (Document in Progress Table). This triggers FGRTRNI (Transaction Input Processing Report) to process and validate each purchase order. /nSeptember 2014 Banner Finance 8.10 3-5TRM Supplement Conversion General Financial Utilitie s Š User Profile/Security/ Approvals Budget and Position Cont rol Š Budget Development Step 5Run FGRTRNI (Transaction Input Processing Report) to add records to FOBAPPD (Approved Document Table) for each purchase order. Step 6Run FGRACTG (Posting Report) to up date financial ledger and transaction history tables.Step 1Execute appropriate General Financial Utilitie s forms to build the user profile/ security/ approval tables. Step 1Value the following: ŁFTVOBUD (Budget Identifier Table) for every budget ID to be developed. ŁFTVOBPH (Budget Phase Table) for every budget phase to be developed. Step 2Execute the following: ŁFBABDRQ (Budget Request Form) to establish budgeted line items, ŁFBABDRA (Budget Request by Account) to establish budgeted line items by account, or optionally, if the budget is being developed from existing general accounting data, execute the following: ŁFBABPRC (Budget Process Control Form) to specify the COAS and fiscal year or FGBOPAL (Operating Account Ledger Table) records to be rolled Step 3Run FBRBDBB (Build Budget Report) to roll operating account data into a specified budget phase. /n3-6Banner Finance 8.10 September 2014 TRM Supplement ConversionEndowments Step 1Ensure that Step 3 of the Utilities has already been performed. Step 2Using the appropriate validation mainte nance forms, enter user-defined values in the following tables: ŁFNVPOOL (Endowed Pool Definition Table) ŁFTVINCL (Income Classification Validation Table) ŁFNRUCTR (Unitized Funds Market Value Table) ŁFTVSDAT (System Data Maintenance Table) Verify that the entries exist fo r entity FNVENDW and attribute FNVENDW_ENDW_TYPE for your various endowment types. Step 3Load FNVENDW (Endowment Fund Table) manually or build it using standard conversion tools. Loading the endowment summary balances and units is complicated and requires great care and analysis.One method to use is documented in Steps 4-8. Step 4Using the crosswalk file, build FGBTRN I (Transaction Input Table) with data to be loaded into Banner Finance. Common Rule Codes that are typically u sed in this process include CR05 (Cash Receipt Entry). The intent is to load both the units and the amount on the same transaction record. Data is typically ente red with the last day of the fiscal year prior to go-live. Set the balance to zero in the header record to force an error on the load. Store the units in the following field: fgbtrni_doc_ref_num Step 5Run FGRTRNI (Transaction Input Pr ocessing Report) to build the journal voucher tables: ŁFGBJVCH (Journal Voucher Header Table) ŁFGBJVCD (Journal Voucher Detail Table) /nSeptember 2014 Banner Finance 8.10 3-7TRM Supplement Conversion Fixed Assets Step 6Because gift units do not exist as a field in FGBTRNI, use SQL to move fgbjvcd_doc_ref_num to fgbjvcd_emc_units; otherwise, it will be loaded as a gift number in the Endowments module. Recomplete the journal voucher online. This inserts the journal voucher into FOBAPPD (Approved Document Table). Step 7Run FGRACTG (Posting Report) to create the following ledgers: ŁFGBGENL (General Ledger Table) ŁFGBOPAL (Operating Account Ledger Table) ŁFGBTRNH (Transaction History Table) ŁFGBTRND (Transaction History Detail Table) ŁFNBUNTD (Endowment Unitization Collector Table) Step 8Run FNPUNTZ (Unitization Process) for each Chart and Pool. This removes records from FNBUNTD (Endowment Unitization Collector Table) and inserts records into FNR UNIT (Endowment Funds Units Table), FNBUNTH (Endowment Unitization History Table) and updates FNRUCTR (Unitized Funds Market Valu e Table) with valuation period and units posted for the period. Step 1Value the following: ŁFFBSYSC (Fixed Assets System Control Table) ŁFFVACON (Fixed Asset Condition Code Table) ŁFFVACQM (Fixed Asset Acqu isition Method Code Table) ŁFTVASTY (Asset Type Table) ŁFTVDEPR (Depreciation Table) ŁFFVDISP (Fixed Asset Disposal Method Code Table) ŁFFVTTST (Fixed Asset Title-To Code Table) /n3-8Banner Finance 8.10 September 2014 TRM Supplement ConversionCost AccountingStep 2Enter Fixed Assets using FFAMAST (Fixed Asset Master Maintenance Form) and FFADEPR (Fixed Asset Depreciation Form), or convert the following: ŁFFBMAST (Fixed Asset Master Table) ŁFFRMASF (Fixed Assets Accounting Source Table) ŁFFRMASA (Fixed Assets Cap italization and Depreciation Accounting Table) ŁFFRFUSN (Fixed Asset User Defined Attributes Table) Step 1 Value these support ta bles in the following order: ŁFTVRTYP (Rate Rules Table was delivered as seed data) ŁFTVINRT (Internal Rate Code Table) ŁFTVIRTO (Organization/Rate Code Table) ŁFTVCTYP (Cost Type Code Table) ŁFTVCSTO (Organization/Cost Type Table) ŁFTVINRT (Inventory Code Table) ŁFTVEQPT (Equipment Tag Table) ŁFCBRATE (Internal Rate Table) ŁFTVRTCT (Organization Rate Code/Cost Type Table) Step 2Convert the following: ŁSPRIDEN (General Person Identification Table) ŁSPRADDR (General Person Address Table) Step 3Enter Employees and Customers on FCAEMPL and FTMCUST or convert the following:ŁFCBEMPL (Employee Table) ŁFTVCUST (Customer Table) /nSeptember 2014 Banner Finance 8.10 3-9TRM Supplement Conversion Stores Inventory Step 4Enter Projects and Project Charges on FTMPROJ and FTMCHRG or convert the following:ŁFTVPROJ (Project Header Table) ŁFTVCHRG (Project/Customer Charge Table) If the project has customers that are being encumbered (if one customer is encumbered, all customers must be encumber ed), a record should be inserted into FOBAPPD (Approved Document Table). Step 5Run FGRACTG (Posting Report) to update Encumbrance ledgers and transaction history tables. Step 6Enter Cost Accounting Transactions on FCAACCT or convert FCBPRJD (Project Detail Table). Enter Billing process parameters on FC APARM for process fiBILLfl. Run this in database update mode. Run FCBBILL (Billing Report) to process pr oject detail and create transaction records for FGRTRNI to process. Step 7Run FGRTRNI (Transaction Input Processing Report) to add records from FCBBILL to the FOBAPPD (Approve d Document Table), the FOAUAPP (Unapproved Document Table), or create the document as incomplete so it can be reviewed on the JV form. The outcom e is determined by how you utilize the FGRTRNI process approval feature. Step 8Run FGRACTG (Posting Report) to up date financial ledger and transaction history tables.Step 1Value FTVSHIP (Ship To Verification Table) Step 2Convert the following: ŁFTVCOMM (Commodities Verification Table) ŁFTVINVM (Stores Commodity Master) ŁFTVSTKL (Stock Location Table) /n3-10 Banner Finance 8.10 September 2014 TRM Supplement ConversionStep 3Convert Stores Requisitions or the following: ŁFPBREQH (Request Header Table) ŁFPRREQD (Request Detail Table) ŁFPRREQA (Request Accts Table) For each record inserted in to the FPBREQH table, yo u must complete each document online. Step 4Run FGRACTG (Posting) or convert the following: ŁFSBISSH (Issue Header Table) ŁFSRISSD (Issue Commodity Detail Table) ŁFSRISSA (Issue Accounting Table) For each record inserted into the FSBIS SH table, you must complete each document online. Step 5Run FGRACTG (Posting) to update th e financial ledger and transaction history tables. /nSeptember 2014 Banner Finance 8.10 4-1TRM Supplement APIs 4APIs Overview Application Programming Interfaces (APIs) enhance processing and simplify code. The API is actually a program comprised of fo ur different packages: a primary package containing query functions and create, update, and delete procedures, a Rules package containing subprograms that support the primary package, a Strings package containing error messages, and an associated DML package that performs the actual database maintenance of the edited data. In addition to the API packages, other supp orting package types include the following: ŁSupport - Contains supporting programs that may be called by other APIs and error messages related to these edits. ŁValidation - Contains standard functions and procedures for a specific validation table.For additional information, re fer to the API Technical Docu mentation available via the Customer Support Center. APIs and Supporting Packages used in Banner Banner Finance currently includes five cate gories of APIs: Procurement APIs, Invoice APIs, Journal Voucher APIs , Encumbrance APIs, and Ef fort Certification APIs.Procurement APIs Procurement APIs are used by the Purch ase Order Form (FPAPURR), Requisition Form (FPAREQN) and the Self-Service Purchase Order page ( bwfkpurr.P_PurchaseOrder ) to calculate taxes, update the tax table, and display tax calculations. In addition, the interfaces th at enable e-Procurement in tegration between SciQuest HigherMarkets and Banner perform ta x and currency conversion calculations. Invoice APIs Invoice APIs are used for the four major invoice tables: /n4-2Banner Finance 8.10 September 2014 TRM Supplement APIsŁInvoice Header Table (FABINVH) ŁInvoice Commodity Table (FARINVC) ŁInvoice Accounting Table (FARINVA) ŁInvoice Tax Allocation Table (FARINTX) These APIs include query functionality, and ma y be used to create, update, and delete individual rows in the four base invoice ta bles. The logic in the business entity APIs addressed the edits pertaining to each of these tables individually, bu t not to the invoice as a whole. The release of the business process APIs enabled FOAPAL re-defaulting for update, and provided update capability for multiple vendor invoices. For additional information, refer to fiBusiness Entity APIs fl on page4-16 and fiBusiness Process APIs fl on page4-18 .Journal Voucher APIs Journal Voucher APIs are used for the two journal voucher tables: ŁJournal Voucher Header Table (FGBJVCH) ŁJournal Voucher Detail Table (FGBJVCD) These APIs include query func tionality and may be used to create, update, and delete individual rows in the two base journal vo ucher tables. The logic in the business entity APIs addressed the edits pertaining to each of the tables individually, but not to the journal voucher as a whole. The business process API contains the core blocks necessary to create and complete the journal voucher. The Journal Voucher Ma intenance form (FGAJVCD) is the primary example of how to use the lo gic of the Journal Voucher APIs within Banner Finance. Encumbrance APIs Encumbrance APIs are used for the two encumbrance tables: ŁEncumbrance Header Table (FGBENCH) ŁEncumbrance Detail Table (FGBENCD) These APIs include query func tionality and may be used to create, update, and delete individual rows in the two b ase encumbrance tables. The lo gic in the business entity APIs addressed the edits pertaining to each of the tables individually, but not to the encumbrance as a whole. The business process API contains the core blocks necessary to create and complete the encumbrance. The General Encumbrance Ma intenance form (FGAENCB) is the primary example of how to use the logic of the En cumbrance APIs within Banner Finance. /nSeptember 2014 Banner Finance 8.10 4-3TRM Supplement APIs Effort Certification APIs Effort Certification is your institution's proc ess for reviewing, valida ting, and certifying the work effort performed by its faculty and staff in support of sponsored research projects. Effort Certification is a process in Banner Human Resources that is supported by Banner Finance APIs. The Effort Certification APIs can be used to create, update, and delete individual rows in the following table: Grant Effort Certification Rules Table (FRRGREC) For detailed information about the Effort Cer tification enhancement, please refer to the Effort Reporting and Labor Redistributions Handbook .API Packages The following charts identify the API pack ages and supporting pack ages used to process Finance data in Banner. The APIs replace th e corresponding code in the Banner forms. Object Name Entity Name Task Performed Associated Tables Associated Packages fb_accounting_elementACCOUNTING_ELEMENT Contains business interface for IFOAPAL information.FTVCOASfb_accounting_ element_rules Provides supporting subprograms for ACCOUNTING_ ELEMENT .fb_chart_of_ accountsCHART_OF_ ACCOUNTS Contains the standard DML functions for CHART_OF_ ACCOUNTS.FTVCOASfb_chart_of_ accounts_rules Provides supporting subprograms for CHART_OF_ ACCOUNTS ./n4-4Banner Finance 8.10 September 2014 TRM Supplement APIsfb_commodityCOMMODITY Contains the standard DML functions for COMMODITY.FTVCOMMfb_commodity_ rulesProvides supporting subprograms for COMMODITY .fb_commodity_ strings Provides error messages and error message functions for COMMODITY.fb_commonCOMMON Contains common functions, procedures, and variables for Finance. N/A fb_common_ strings Provides error messages and error message functions for COMMON.fb_encumbrance_ detailENCUMBRANCE_ DETAIL Contains the common business interface for the General Encumbrance Detail API (ENCUMBRANCE_ DETAIL).FGBENCDfb_encumbrance_ detail_rules Provides supporting subprograms for ENCUMBRANCE_ DETAIL .fb_encumbrance_ detail_strings Provides error message and error message functions for ENCUMBRANCE_DETAIL .Object Name Entity Name Task Performed Associated Tables Associated Packages /nSeptember 2014 Banner Finance 8.10 4-5TRM Supplement APIs fb_encumbrance_headerENCUMBRANCE_ HEADER Contains the common business interface for the General Encumbrance Header API (ENCUMBRANCE_ HEADER).FGBENCHfb_encumbrance_ header_rules Provides supporting subprograms for ENCUMBRANCE_ HEADER .fb_encumbrance_ header_strings Provides error messages and error message functions for ENCUMBRANCE_HEADER .fb_grant_ effcertGRANT_EFFCERT This package is the common business interface for the Grant Effort Certification API (GRANT_ EFFCERT).FRRGREC fb_grant_ effcert_rule This package contains the business logic processing rules for GRANT_EFFCERT .fb_grant_ effcert_strings This package contains error messages and error message functions for GRANT_EFFCERT .dml_frrgrec Provides the procedures to insert, update, and delete from various Banner tables. Object Name Entity Name Task Performed Associated Tables Associated Packages /n4-6Banner Finance 8.10 September 2014 TRM Supplement APIsfb_invoice_headerINVOICE_ HEADER Contains the common business interface for INVOICE_ HEADER.FABINVH fb_invoice header_rules Provides supporting subprograms for INVOICE_ HEADER .fb_invoice_ header_strings Provides error messages and error message functions for INVOICE_HEADER .dml_fabinvh Provides the procedures to insert, update, and delete from various Banner tables. fb_invoice_itemINVOICE_ITEM Contains the common business interface for INVOICE_ITEM.FARINVC fb_invoice_item rules Provides supporting subprograms for INVOICE_ITEM .fb_invoice_item _strings Provides error messages and error message functions for INVOICE_ITEM.dml_farinvc Provides the procedures to insert, update, and delete from various Banner tables. Object Name Entity Name Task Performed Associated Tables Associated Packages /nSeptember 2014 Banner Finance 8.10 4-7TRM Supplement APIs fb_invoice_acctgINVOICE_ACCTG Contains the Common Business interface for INVOICE_ACCTGFARINVA fb_invoice acctg_rules Provides supporting subprograms for INVOICE_ACCTG .fb_invoice_ acctg_strings Provides error messages and error message functions for INVOICE_ACCTG.dml_farinva Provides the procedures to insert, update, and delete from various Banner tables. fb_invoice_ taxINVOICE_TAX Contains the Common Business interface for INVOICE_TAX.FARINTX fb_invoice_ tax_rules Provides supporting subprograms for INVOICE_TAX .fb_invoice_tax_ strings Provides error messages and error message functions for INVOICE_TAX.dml_farintx Provides the procedures to insert, update, and delete from various Banner tables. Object Name Entity Name Task Performed Associated Tables Associated Packages /n4-8Banner Finance 8.10 September 2014 TRM Supplement APIsfb_jv_headerJV_HEADER Contains the Common Business interface for the Journal Voucher Header API (JV_HEADER).FGBJVCH fb_jv_header_ strings Provides error messages and error message functions for JV_HEADER.fb_jv_header_ rulesProvides supporting subprograms for JV_HEADER .dml_fgbjvch Provides the procedures to insert, update, and delete from various Banner tables. fb_jv_detailJV_DETAIL Contains the Common Business interface for the Journal Voucher Detail API (JV_DETAIL).FGBJVCD fb_jv_detail_ strings Provides error messages and error message functions for JV_DETAIL. fb_jv_detail_ rulesProvides supporting subprograms for JV_DETAIL .dml_fgbjvcd Provides the procedures to insert, update, and delete from various Banner tables. fb_purchase_ orderPURCHASE_ ORDER Contains the Common Business interface for PURCHASE_ ORDER.FPBPOHD FPRPODA FPRPODTfb_purchase_ order_rules Provides supporting subprograms for PURCHASE_ ORDER.Object Name Entity Name Task Performed Associated Tables Associated Packages /nSeptember 2014 Banner Finance 8.10 4-9TRM Supplement APIs fb_po_taxPO_TAX Contains the Common Business interface for the Purchase Order Tax Table (PO_TAX) .FPRPOTXfb_po_tax_rules Provides supporting subprograms for PO_TAX .fb_po_tax_ strings Provides error messages and error message functions for PO_TAX.dml_fprpotx Provides the procedures to insert, update, and delete from various Banner tables. fb_ requisitionREQUISITION Contains the common business interface for REQUISITION.FPBREQH FPRREQD FPRREQAfb_requisition_ rulesProvides supporting subprograms for REQUISITION .fb_req_taxREQ_TAX Contains the Common Business interface for the Requisition Tax Table (REQ_TAX) .FPRRQTXfb_req_tax_ rulesProvides supporting subprograms for REQ_TAX .fb_req_tax_ strings Provides error messages and error message functions for REQ_TAX.dml_fprrqtx Provides the procedures to insert, update, and delete from various Banner tables. Object Name Entity Name Task Performed Associated Tables Associated Packages /n4-10 Banner Finance 8.10 September 2014 TRM Supplement APIsfb_shipSHIP Provides standard DML functions for the FTVSHIP table. FTVSHIPfb_ship_rules Provides supporting subprograms for SHIP.fb_vendorVENDOR Provides the common business interface for VENDOR.FTVVEND fb_vendor_rules Provides supporting subprograms for VENDOR .fb_vendor_ strings Provides error messages and error message functions for VENDOR.fb_vendor_ typeVENDOR_TYPE Provides the common business interface for VENDOR_TYPE.FTVVENT fb_vendor_ type_rules Provides supporting subprograms for VENDOR_TYPE .fb_vendor_ type_strings Provides error messages and error message functions for VENDOR_TYPE.fp_encumbranceENCUMBRANCE Process API for encumbrances (ENCUMBRANCE).fp_encumbrance_ strProvides error message and error message functions for ENCUMBRANCE.Object Name Entity Name Task Performed Associated Tables Associated Packages /nSeptember 2014 Banner Finance 8.10 4-11 TRM Supplement APIs Other API Packages The following packages suppor t the circulation of documents and approval requests using custom events in Banner Workflow. They ar e owned by Finance to enable shared access by the Travel and Expense Management an d eProposal Management modules. These supporting packages operate in the background, are not part of Finance processing, and are not intended for general usage. Therefore they are not included in the Finance API technical documentation. fp_invoiceINVOICE Contains the Common Business interface for the composite entity Invoice (FP_INVOICE).Invoice Tables: FABINVH FARINVA FARINVC FARINTX fp_invoice_ strings Provides error messages and error message functions for INVOICE.fp_journal_ voucherJOURNAL_ VOUCHER Process API for Journal Vouchers (JOURNAL_ VOUCHER).Journal Voucher Tables: FGBJVCH FGBJVCD fp_journal_ voucher_str Provides error messages and error message functions for JOURNAL_VOUCHER .Object Name Entity Name Task Performed Associated Tables Associated Packages /n4-12 Banner Finance 8.10 September 2014 TRM Supplement APIsObject Name Entity Name Task Performed Associated Tables Associated Packages fb_circulation_headerCIRCULATION_HEADER Common Business interface for the CIRCULATION_ HEADER table. FOBCIRHfb_circulation_ header_rules Provides supporting subprograms for CIRCULATION_ HEADER .fb_circulation_ header_strings Provides error messages and error message functions for CIRCULATION_HEADER .dml_fobcirh Standard dml functions and procedures for the Circulation Header table (FOBCIRH). fb_circulation_ noticeCIRCULATION_ NOTICE Common Business interface for CIRCULATION_ NOTICE.FORCIRN fb_circulation_ notice_rules Provides supporting subprograms for CIRCULATION_ NOTICE .fb_circulation_ notice_strings Provides error messages and error message functions for CIRCULATION_NOTICE .dml_fobcirn Standard dml functions and procedures for the Circulation Routing Notification table (FORCIRN). /nSeptember 2014 Banner Finance 8.10 4-13 TRM Supplement APIs fb_circulation_recipCIRCULATION_ RECIP Common Business interface for CIRCULATION_ RECIP.FORCIRR fb_circulation_ recip_rules Provides supporting subprograms for CIRCULATION_ RECIP.fb_circulation_ recip_strings Provides error messages and error message functions for CIRCULATION_RECIP.dml_fobcirr Standard dml functions and procedures for the Routing Recipient table (FORCIRR). Object Name Entity Name Task Performed Associated Tables Associated Packages /n4-14 Banner Finance 8.10 September 2014 TRM Supplement APIsSupport Packages The following packages contain supporting programs that may be called by other APIs and error messages related to these edits. fb_circulation_stepCIRCULATION_STEP Common Business interface for CIRCULATION_ STEP.FORCIRS fb_circulation_ step_rules Provides supporting subprograms for CIRCULATION_ STEP.fb_circulation_ step_strings Provides error messages and error message functions for CIRCULATION_STEP.dml_fobcirs Standard dml functions and procedures for the Routing step table (FORCIRS). fp_circulationCIRCULATION This package contains procedures that facilitate circulation flow. Object Name Entity Name Task Performed Associated Tables Associated Packages Object NameTask Performed Associated Tables ffkedit Consolidates the editing and defaulting for Rule Class edits. FTVEDITffkfoap Edits codes for the following: chart of accounts, fund, organization, account, program, activity, location, project, and bank. FTVCOAS FTVFUND FTVORGN FTVACCT FTVPROG FTVACTV FTVLOCN /nSeptember 2014 Banner Finance 8.10 4-15 TRM Supplement APIs Validation Packages The following packages contain sta ndard functions and procedures. ffkrulp Ensures that all data required by the Rule Process Codes associated with the Rule Class Code in the reco rd is present. FTVRULP foksels Contains functions, record types, and cursors to support selection of various Finance information. foktext Supports functionality of Finance Document text. FOBTEXTfpkpurr Contains all procedures and functions required to create a purchase order in Banner Finance. FPBPOHD FPRPODT FPRPODA FPRPOTX fpkreqn Contains all procedures and functions required to create a requisition in Banner Finance. FPBREQH FPRREQD FPRREQA FPRRQTX fokutil Contains procedures and functions common to all modules in Banner Finance. fpkutil Performs various common routines used by the Finance procurement module. Object NameTask Performed fb_ftvbuyr Contains standard functions and procedures for the Buyer Verification Table (FTVBUYR). fb_ftvcrsn Contains standard functions and procedures for the Closed Document Reason Code Table (FTVCRSN). fb_ftvdisc Contains standard functions and procedures for the Discount Terms Table (FTVDISC). Object NameTask Performed Associated Tables /n4-16 Banner Finance 8.10 September 2014 TRM Supplement APIsTypes of APIs Used for Invoicing This section describes the types of APIs u sed for the invoicing function, and provides detailed information about how the create, upda te, and delete functio ns within the APIs populate tables and process data. Two types of APIs are needed to ensure that the API approach is comparable to the actions of a user performing an action, such as completing an invoice in Banner. ŁfiBusiness Entity APIsfl on page4-16 ŁfiBusiness Process APIs fl on page4-18 Business Entity APIs Banner Business Entity AP Is define the following: ŁCommon operations that the Banne r business entity supports, and ŁCommon types that support data encapsul ation requirements so institutions can manipulate the Business En tity data as a whole. The business entity APIs could be used by a custom interface from an outside source to feed direct pay invoice data to Banner Finance invoice tables. fb_ftvityp Contains standard functions and procedures for the Income Type Table (FTVITYP). fb_ftvtgrp Contains standard functions and procedures for the Tax Group Validation Table (FTVTGRP). fb_ftvtrat Contains standard functions and procedures for the Tax Rate Table (FTVTRAT). fb_ftvuoms Contains standard functions and procedures for the Unit of Measure Table (FTVUOMS). fb_ftvvtyp Contains standard functions and procedures for the Vendor Type Table (FTVVTYP). Object NameTask Performed /nSeptember 2014 Banner Finance 8.10 4-17 TRM Supplement APIs CREATE/UPDATE APIs The business entity CREATE and UPDATE APIs populate data in individual invoice tables. These APIs also perform the following tasks: 1.Validate table values (based on transaction date). NoteIf no vendor ID is present, validate that Vendor Name, Vendor Address (Line 1), City, State and Zip Code are present (at a minimum). ŁVendor ID ŁVendor address types and sequences ŁDiscount Codes ŁTax Group Codes ŁCommodity Codes ŁUnit of Measure ŁCOA and FOAPAL values ŁOpen/closed fiscal years and periods ŁInvoice document has not been previously completed 2.Enforce the following rules:ŁInvoice permissions based on settings in FOMPROF ŁFund/Organization security based on th e user ID and settings in FOASYSC, FOMPROF, FOMUSFN, FOMUSOR ŁNSF handling based on the settings in FTMCOAS, FOASYSC, FTMFTYP, FTMFUND 3.Derive the following amounts: ŁAppropriate rule classes based on transaction data ŁDiscount amounts based on discount codes ŁTaxes based on tax rate values in each tax group ŁCurrency amounts for approved amounts, discount amou nts, additional amounts, and tax amounts based on percentages ŁCurrency conversion amounts 4.Populate default valu es for the following: ŁVendor address and sequence number from FTMVEND /n4-18 Banner Finance 8.10 September 2014 TRM Supplement APIsŁVendor 1099 data from FTMVEND ŁVendor invoice/check preferences from FTMVEND ( single invoice per check or multiple invoice per check) ŁTax groups from settings in FOASYSC and FTMCOMM ŁFOAPAL defaults (and en forcing override settings) for Account Indexes from FTMACCI ŁOPAL defaults from FTMFUND ŁFPAL defaults from FTMORGN ŁAccount code and UOM defaults from FTMCOMM DELETE APIs DELETE APIs must be used so that accounting/tax deletions are processed prior to item delete, and accounting/tax and item deletions are processed prior to header delete. Business entity DELETE APIs can be us ed to perform th e following tasks. ŁDelete specific accounting sequences, which will also delete any associated tax information from FARINTX, if they exist. ŁDelete specific items, if no accountin g tax/records exist for the invoice. ŁDelete of header records, if no item and accounting tax/records exist for the invoice. Business Process APIs Banner Business Process APIs support busi ness processes that sp an calls to multiple Banner Business APIs in the c ontext of a single transaction. The business process APIs are si gnificant because of the role they play in processing wrap up and balancing routines necessary for comp letion of invoices, an d creation of invoice types other than direct pay. For example, the business process APIs aggr egate the rows that were created by the business entity APIs in each of the invoice base tables, evaluate all the data that is required to establish a complete invoice document in Banner Finance, and perform cross-table updates across multiple tables and forms. Th ey provide the data co mplexity needed to reference a Purchase Order item or General Encumbrance in an invoice. /nSeptember 2014 Banner Finance 8.10 5-1TRM Supplement System-Required Data 5System-Required Data Banner is a comprehensive system with many parts that work together to manage your institution™s data and to inter act with users. When any one of the components of the system is missing, some of the system™s func tions may fail or may not work as intended. In some cases, data itself can be considered an essential component of the system. The complete contents of certain tables, and specif ic rows in other tables, must be present for the system to work correctly. This special data is called system-required data . System-required data is a subset of the seed data de livered with a new Banner installation. Banner software releases often include seed data sc ripts that deliver additional system-required data. Generally, Banner forms and processes will prevent you from dele ting system-required data. But when you are using database tools or scripts to delete rows from the databaseŠ for example, during database cleanup to re move sample data before migrating into productionŠthere is nothing to prevent system-required data from being accidentally deleted. In those situations, you should take care not to delete any system-required data. This chapter lists system-required data for Banner Finance. For more information on system-required data, please see the Banner General Technical Reference Manual . Overview This manual summarizes system-requir ed data in the following charts.ŁfiFinance Tables, Group 1 fl on page5-2 - This chart identifies tables for which seed data rows should not be deleted. ŁfiFinance Tables, Group 1 - System-Required Rows fl on page5-3 - This chart identifies specific values in system-required rows for some of the tables in the first group. ŁfiFinance Tables, Group 2 fl on page5-11 - This chart identifies tables for which seed data rows can be modified and deleted as necessary for the business model of the institution./n5-2Banner Finance 8.10 September 2014 TRM Supplement System-Required Data Finance Tables, Group 1 For the validation tables listed below, the delivered seed da ta rows should not be deleted. You can add your own specific data and can also modify delivered dataŠbut exercise caution when doing so. It is recommended that you save a data export of these tables before making any changes. NoteIf you produce GASB reports, then the attributes in the FTVATTT, FTVATTS, and FTRATTS tables may not be deleted. Supplemental informa tion is provided in fiFinance Tables, Group 1 - System-Required Rowsfl on page5-3 for some tables with system-required rows. These tables are flagged with an asterisk (). Because many tables cont ain hundreds of rows, it is not practical to provide a comprehensive list of all ta ble values in this documentation. FEBEDIC EDI Control File (required if using EDI) FEREDIC EDI Transaction Control File (required if using EDI) FOBFSEQEntity System Document Sequence Number Table FOBSEQNDocument Sequence Number Table FORPRRG Form/Process to Rule Group Security Table FRVBFRM Billing Format Code Validation Table FRVCFDA Catalog of Federal Domestic Assistance Codes Table FTRATTS Set and Attribute Type Association Table FTRATTV Chart of Accounts Attributes Value Validation Table FTVATTS Chart of Accounts Attributes Set Validation Table FTVATTT Chart of Accounts Attributes Type Validation Table FTVDTYPDocument Type Table FTVITYP Income Type Code Table (1099) FTVRGRC Rule Class to Rule Group Table FTVRUCL Rule Code Table /nSeptember 2014 Banner Finance 8.10 5-3TRM Supplement System-Required Data Finance Tables, Group 1 - System-Required Rows This section identifies specifi c values in system-required rows for the following tables that should not be deleted. These tables may also contain additional values. ŁEDI Transaction Control File (FEREDIC) ŁEntity System Document Sequ ence Number Table (FOBFSEQ) ŁDocument Sequence Number Table (FOBSEQN) ŁForm/Process to Rule Group Association Table (FORPRRG) ŁBilling Format Code Validation Table (FRVBFRM) ŁChart of Accounts Set and Attribute Type Association Table (FTRATTS) ŁChart of Accounts Attributes Type Validation Table (FTVATTT) ŁChart of Accounts Attributes Set Validation Table (FTVATTS) ŁDocument Type Verification Table (FTVDTYP) Ł1099 Income Type Table (FTVITYP) If Travel and Expense Management is inst alled, the following rows are required: FTVRULP Rule Process Code Table FTVSDAT System Data Maintenance Table FURVERS Version Table FEREDIC EDI Transaction Control File Module CodeTransaction TypeTransaction Status PPC1PPO1 FOBFSEQEntity System Document Sequence Number Table Module CodeTransaction TypeTransaction Status TRAVAUTH TATravel Request Authorization NumberTRAVPORT TPTravel Portfolio Number TRAVREIM TRTravel Reimbursement Number /n5-4Banner Finance 8.10 September 2014 TRM Supplement System-Required Data FOBSEQNDocument Sequence Number Table Document TypeDocument Prefix Document Type DescriptionNote: The FOBSEQN table is maintained on th e Sequence Number Maintenance Form (FOASEQN). Encumbrance Open/Close Document AAAgreements BBBidsKCCommodityDDDocument tag Number (Fixed asset) EEEncumbrance NumberFFInterface Document Number GGDeferred Grant Calculations JV, Grant Billing JV, Indirect Cost Encumbrance JV HHDirect Cash Receipts IIInvoice Code JJJournal Voucher Code LLBudget Line Item MMFixed Assets Accounting AdjustmentsNNPermanent Tag number PPPurchase Order/Change Order QQLedger Reclassification Document RRRequisition Code SSState 1099/AR&Pcard Inv./ Proposal TTOrigination Tag Num (Fixed Assets) UUIssuesWWInventory AdjustmentsXXStores Transfers YYReceiving /nSeptember 2014 Banner Finance 8.10 5-5TRM Supplement System-Required Data FORPRRG Form/Process to Rule Group Association Table Rule Group CodeForm/Process CORGFPACHAR ENCG FGAENCB FADG FFAADJF INVG FAAINVE INVG FAAINVD ISSG FSAISSU JVCGFGAJVCD JVCGFGAJVCQ JVCGFGAJVCM JVCGFBABDMN PORG FPAPDEL PORG FPACDEL PORG FPACHAR PORG FPAPURR REQGFPARDEL REQGFPAREQN SRQG FSAREQN SRQG FPARDEL FRVBFRM Billing Format Code Validation Table Billing Format CodeBilling Format CodeFormat Type CNSFCanadian Standard Report R (Report)272RFederal Cash Transactions ReportR (Report)272B Federal Cash Transactions ReportB (Bill) FFRFederal Financial Report SF-425 R (Report)269R Financial Status Report (short-form)R (Report)1034Public Vouchers for Purchases B (Bill) 270B Request for Advance/ Reimbursement B (Bill) /n5-6Banner Finance 8.10 September 2014 TRM Supplement System-Required Data FTRATTS Chart Of Accounts Set and Attribute Type Association Table Set CodeAssociated Attribute Code Note: This table is applicable only for institutions that pr oduce GASB reports. GASB1020 G4GFA1CA GASB1020G4GFA2CL GASB1020 G4GFA9EX GASB1020G4GWA1CA GASB1020 G4GWA2CL GASB1020G4GWA9EX GASB1020G5BAA1CA GASB1020G5BAA2NA GASB1020G5BAA3CL GASB1020G5BAA4NL GASB1020G5BAA9EX GASB3040G4GFN1NA GASB3040 G4GFN9EX GASB3040G4GWN1NA GASB3040 G4GWN9EX GASB3040G5BAN1NA GASB3040G5BAN9EX GASB5080G4GFR1OR GASB5080 G4GFR2OE GASB5080G4GFR3OS GASB5080 G4GFR4SI GASB5080G4GFR9EX GASB5080 G4GWE1ER GASB5080G4GWE9EX GASB5080 G4GWG1RV GASB5080G4GWG9EX GASB5080 G5BAR1OR GASB5080G5BAR2OE GASB5080 G5BAR3NO GASB5080G5BAR4OT GASB5080 G5BAR9EX GASB9095G4GFN1NA GASB9095 G4GFN9EX GASB9095G4GFR4SI GASB9095 G4GFR9EX GASB9095G4GWG1RV /nSeptember 2014 Banner Finance 8.10 5-7TRM Supplement System-Required Data GASB9095 G4GWG9EX GASB9095G4GWN1NA GASB9095 G4GWN9EX GASB9095G5BAN1NA GASB9095G5BAN9EX GASB9095G5BAR4OT GASB9095 G5BAR9EX GASBFUNDG4GFFUND GASBFUNDG4GWFUND GASBFUNDG5BAFUND GASBPROGG4GFPROG GASBPROGG4GWPROG GASBPROGG5BAPROG FTVATTS Chart Of Accounts Attributes Set Validation Table Note: This table is applicable only for institutions that pr oduce GASB reports. GASB1020 GASB 34 and 35 Asset and Liability Acct Attributes GASB3040GASB 34 and 35 Net Asset Acct AttributesGASB5080 GASB 34 and 35 Operating Acct AttributesGASB9095GASB 34 and 35 Fund Addn/Dedn Acct Attributes GASBFUNDGASB 34 and 35 Fund AttributesGASBPROGGASB 34 and 35 Program AttributesFTVATTT Chart of Accounts Attributes Type Validation Table Note: The values listed here are at tribute values for GASB 34 and 35 reporting. They are created for each chart of accounts in the system. The FTVATTT table is maintained on the Attribute Type Maintenance Form (FTMATTT). This table is applicable only for institutions that produce GASB reports. G4GFA1CA Assets G4GFA2CL Liabilities G4GFA9EX Exclude Assets/LiabilitiesFTRATTS Chart Of Accounts Set and Attribute Type Association Table /n5-8Banner Finance 8.10 September 2014 TRM Supplement System-Required Data G4GFFUND Governmental Funds ColumnG4GFN1NA Fund balances G4GFN9EX Exclude Net Asset G4GFPROG Program code attributes for expensesG4GFR1ORRevenues G4GFR2OE Expenditures G4GFR3OS Other Financing Sources (Uses) G4GFR4SI Special Items G4GFR9EX Exclude Operating Accounts G4GWA1CA Assets G4GWA2CL Liabilities G4GWA9EX Exclude Assets/LiabilitiesG4GWE1ER Column for Expenses/Revenues G4GWE9EX Exclude expenses/revenues G4GWFUND Government Wide Column G4GWG1RV General Revenue G4GWG9EX Exclude General Revenue G4GWN1NA Net Assets G4GWN9EX Exclude Net Asst G4GWPROG Program Activities G5BAA1CA Current assets G5BAA2NA Noncurrent assets G5BAA3CLCurrent liabilities G5BAA4NL Noncurrent liabilitiesG5BAA9EXExclude Assets/LiabilitiesG5BAFUND Public Inst BTA Column G5BAN1NA Net Assets G5BAA2NA Noncurrent assets G5BAA3CLCurrent liabilities G5BAA4NL Noncurrent liabilitiesG5BAA9EXExclude Assets/LiabilitiesG5BAFUND Public Inst BTA Column G5BAN1NA Net Assets G5BAA2NA Noncurrent assets FTVATTT Chart of Accounts Attributes Type Validation Table /nSeptember 2014 Banner Finance 8.10 5-9TRM Supplement System-Required Data FTVDTYP Document Type Verification Table Document Sequence NumberDocument TypeDescription Note: The FTVDTYP table is maintained on the Document Type Maintenance Form (FTMDTYP). 1REQ Requisition2POPurchase Order 3INVI nvoice4BIDRequest for Bid 5CCKCheck Cancellation6RCNReconciliation Items 7RTNR eturns8CHKCheck Disbursement 9FED Federal 1099 Disbursement 10STState 1099 Disbursement 11 CMCredit Memo12IDRIndirect Cost Rate13AGRAgreements 14COMCommodity15PRJ Project 16IDCIndirect Cost Charge 17IDDIndirect Cost Distribution18CSRCost Share Rate 19CSCCost Share Charge 20JVJournal Document 21ISSIssues 22RCVReceiving Documents 23PAK Packing Slips for Receiving 24TRN Stores Transfers 25ENC Encumbrance 26ADJAdjustments to Inventory 27CSDCost Share Distribution 28BASBasis Code29 PRO Proposal Code 30PRP Proposal 31SRQ Special Requirements 32EVT Research Accounting Events40STKStocks 41STO Stock Option /n5-10 Banner Finance 8.10 September 2014 TRM Supplement System-Required Data 42BNDBonds 43MKT Money Market 44CDCertificate of Deposit 45PTY Appreciable Property 50 FND Fund Records 51GRT Research Accounting Grants 52CIPConstruction Projects60FAA Fixed Assets Accounting Adjustments61FASFixed Assets 62TRF Transfers 70 IMG Investment Manager 71BRQBudget Request 72FTVFTV Tables 73FVD Vendor Table 74SUM General Ledger Summary Records75GBLResearch Accounting Grant Billing 80DCRDirect Cash Receipts 85 TAR Travel Authorization 86TRR Travel Reimbursement 90EOC Encumbrance Open/Close Document 91BFMGrant Billing Format 98PCD Purchase Card99 PMS Payment Management System code* This seed data is available when Trav el and Expense Management is installed. FTVITYP 1099 Income Type Table Income Type Internal Sequence NumberIncome Type CodeIncome Type Description Note: The FTVITYP table is maintained on the 1099 Income Type Code Maintenance Form (FTMITYP). 1RER ents2RYRoyalties3PRO ther Income5FBFishing Boat Proceeds FTVDTYP Document Type Verification Table /nSeptember 2014 Banner Finance 8.10 5-11 TRM Supplement System-Required Data Finance Tables, Group 2 The following chart contains a core group of tables for which valid data must be created before the system can be used . Seed data rows can be modified and deleted as necessary for the business model of the institution. Once es tablished, data in these tables must be migrated and retained for subsequent processing. 6MDMedical and Health Care Payments 7NCNonemployee Compensation8SPSub. Paymen ts/Dividends Interest9GPExcess Golden Parachute Payments 10CPCrop Insurance Proceeds 12ATGross Proceeds Paid To Attorney13ADSection 409A Deferrals 14AISection 409A Income FOBPROFUser Profile Table FOBSYSCSystem Control Table FTVACCT Account Code Table FTVACTL Control Account Table FTVATYP Account Type Code Table FTVBUYR Buyer Code Table FTVCOAS Chart of Accounts Code Table FTVFSPDFiscal Period Table FTVFSYRFiscal Year Table FTVFTYP Fund Type Code Table FTVFUND Fund Code Table FTVORGN Organization Code Table FTVPROG Program Code Table FTVITYP 1099 Income Type Table /n5-12 Banner Finance 8.10 September 2014 TRM Supplement System-Required Data FTVSHIP Ship to Address Code Table FTVUOMS Unit of Measure Code Table FTVVENDVendor Table Note: Coordinate with SPRIDEN/ SPRADDR and others. Take care to ensure PIDM numbers are coordinated with the student, faculty, personnel and constituent information being loaded for other Banner systems. /n

Banner_Finance_TRM_Supplement_8.10.pdf (378.4 KB)
Helpful?

Related Articles: