GMS Accounting Online Help
  • Home
  • Contents
  • Index
  • Search
WELCOME TO GMS ACCOUNTING ONLINE HELP
The help you need at your fingertips.

You are here: Listing of Tables > List of Tables and Fields in GMS Accounting Database
Listing of Tables and Fields in GMS Accounting Database
Tables used in General Ledger
GL Master File
tblGLTransaction


GLCode............................................................................................. number
 
 
Description........................................................................................ text
 
 
GlBalance.......................................................................................... number ‘ prior year GL balance
 
 
CurrYGLBalance................................................................................. number ‘ not used
 
 
CurrMGlBalance................................................................................. number ‘ not used
 
 
Gltype............................................................................................... memo ‘ use to store bank signature file associated with the account#

 
Active............................................................................................... yes/no
 
 
Allocate............................................................................................. yes/no ‘ terminated status
 
 
Percentage........................................................................................ number ‘ not used
 

Dfrom............................................................................................... date ‘ if is project, need start date

 
Dto................................................................................................... date ‘ if is project, need end date
 
 
Program Element Master File
tblProgElement


ProgCode........................................................................................... number
 
 
Description........................................................................................ number


ProjectCode....................................................................................... number
 
 
Active............................................................................................... yes/no
 
 
Allocate............................................................................................. yes/no ‘ terminated status
 
 
Percentage........................................................................................ number ‘ this is not used
 
 
General Journal
tblGJBatch


TransDate......................................................................................... date
 
 
GJ..................................................................................................... text ‘ GJ
 
 
EntryNum......................................................................................... number ‘ GJ#
 
 
Blank................................................................................................ text ‘ not used
 
 
Desc1................................................................................................ text
 
 
Credit................................................................................................ number/double
 
 
Element............................................................................................ number/long
 
 
TransCode......................................................................................... number/long
 
 
Debit................................................................................................ number/double
 
 
ProjCode........................................................................................... number/long
 
 
GLCode............................................................................................. number/long

 
BatchNum......................................................................................... number/long ‘ match batchnum in tblGJBatchDetail

 
Desc2................................................................................................ text ‘ used if the GJ has been cleared in the bank rec
 
 
Posted............................................................................................... yes/no
 
 
tblGJBatchDetail

TotalAmt........................................................................................... currency
 
 
TotalElement..................................................................................... double
 
 
TotalProject....................................................................................... double
 
 
TotalGLCode...................................................................................... double

 
BatchNum......................................................................................... long
 
 
Postperiod......................................................................................... date
 
 
Posted...............................................................................................      yes/no
 
 
BatchDesc......................................................................................... text
 
 
Monthly Processing
Timesheet Reports (tblTIBatch and tblTimesheet Batch are listed under Payroll)

tblTimesheetbyEmp...........................................................................
 
 
EmpCode........................................................................................... number
 
 
Name................................................................................................ text
 
 
Elem................................................................................................. number/long
 
 
Project.............................................................................................. number/long
 
 
Hours................................................................................................ number/long
 
 
Paycode............................................................................................. text
 
 
Amt.................................................................................................. currency
 
 
EndPeriod.......................................................................................... date
 
 
class.................................................................................................. number
 
 
General Ledger Listings
tblGLDetail ‘ this is the GL info for all the batches posted

ID..................................................................................................... autonumber
 
 
ID1................................................................................................... number ‘ this is not used
 
 
GLCode............................................................................................. number
 
 
Program............................................................................................ number
 
 
Project.............................................................................................. number
 
 
Class................................................................................................. number ‘ this is not used
 
 
Date1................................................................................................ date ‘ transaction date
 
 
Period................................................................................................ date ‘ posting period


Credit................................................................................................ currency
 
 
Debit................................................................................................. currency
 
 
User1................................................................................................ text ‘ user’s login name
 
 
Journal.............................................................................................. text ‘ like CD,PR,GJ,AP,AR
 
 
Descript............................................................................................. text
 
 
DocID................................................................................................ text ‘
 
 
Batchno............................................................................................ number
 
 
Hours................................................................................................ number ‘ this is not used
 
 
‘ all the following tables are for report purposes

tblGLReport...............................................................................
 
 
tblGLReporttemp.......................................................................
 
 
tblGlFinancialReport...................................................................
 
 
tblTempGlCurrentYear................................................................
 
 
Cost Allocation

tblLeaveRateComp..................................................................... ‘temp table used for the Leave Rate Comp & Analysis
 
 
tblTotalLeaveCost...................................................................... ‘ stores current YTD cost allocation
 

tblTotalLeaveCosttemp....................................................................... ‘ stores prior months cost allocation

 
tblTotalLeaveCostCurrent................................................................... ‘ temp table used in creating reports
 
 
tblIndirectCostClass........................................................................... ‘ indirect/common cost exclude classes
 
 
tblIndirectCostProj............................................................................. ‘ indirect/common cost exclude element, project and gl
 

tblFringeAlloc.....................................................................................  ‘temp table used in creating Fringe Benefit Analysis

 
tblFringeallocdestroy.......................................................................... ‘ not used
 
 
tblFringeBenefit................................................................................. ‘stores current & YTD info for Fringe Analysis
 
 
tblFringeBenefitCalc........................................................................... ‘store the base, class & % for Fringe Analysis
 
 
tblDualIndirectMG.............................................................................. ‘ MG setup table
 
 
tblDualIndirectProj............................................................................. ‘ MG exclude element, project, and GL
 
 
tblDualIndirectCLass.......................................................................... ‘ MG exclude classes
 
 
tblCostCenterPool.............................................................................. ‘ monthly cost center pool setup
 
 
tblCostCenterBase............................................................................. ‘ cost center base setup

 
tblCostCenterBaseTemp..................................................................... ‘ temp table for cost center report

 
tblCostAllocationLocks........................................................................ ‘ cost allocation locks setup
 
 
tblCostAllocLocksReport...................................................................... ‘ temp table for cost allocation locks report
 
 
tblCostAllocSuppCurr.......................................................................... ‘ table for cost allocation supp current month
 
 
tblCostAllocSuppPrior......................................................................... ‘ table for cost allocation supp prior months
 
 
tblServiceUnitPool.............................................................................. ‘ service unit pool setup
 
 
tblServiceUnitBase............................................................................. ‘ service unit base setup
 
 
tblSpecAlloc....................................................................................... ‘ special allocation setup
 
 
tblSpecAllocClass............................................................................... ‘special allocation exclude classes
 
 
tblSpecAllocPool................................................................................. ‘special allocation pool setup
 
 
tblSpecAllocProj................................................................................. ‘special allocation base elements & curr info

 
tblSpecAllocReport............................................................................. ‘temp table for special allocation report

 
tblSpecGL.......................................................................................... ‘GL codes included in base for spec allocations
 
 
Tables used in Cost Allocation Setup
 
tblClass1

classnum.......................................................................................... number
 
 
classdescription................................................................................. text
 
 
YearHr............................................................................................... number ‘ like 2080, 1040
 
 
PPHr................................................................................................. Number ‘ hours pay period
 
 
PPYear.............................................................................................. number ‘ pay period per year
 
 
SS.................................................................................................... yes/no ‘ withhold SS tax
 
 
Med.................................................................................................. yes/no ‘ withhold Medicare tax
 
 
.Fed.................................................................................................. yes/no ‘ withhold federal tax
 
 
State................................................................................................ yes/no ‘ withhold state tax
 
 
Local................................................................................................. yes/no ‘ withhold local tax
 
 
Salary............................................................................................... number ‘ GL code for salary, mostly 50000
 
 
Fringet.............................................................................................. number ‘ GL code for fringe, mostly 50500
 
 
Leave................................................................................................ number ‘ GL code for leave, mostly 50000
 
 
tblLeaveAllocation

classnum....................................................................................... number
 
 
Rate.............................................................................................. text ‘ can be fixed or current
 
 
Leavetype....................................................................................... text ‘ like AL,SL
 
 
Accrue............................................................................................. yes/no
 
 
Accruerate....................................................................................... number‘ %
 
 
Opening........................................................................................... number‘ opening leave balance
 
 
Max................................................................................................. yes/no
 
 
Maxamt........................................................................................... currency
 
 
Allocation......................................................................................... text ‘ can be YTD or Month TD

 
RT...................................................................................................  yes/no ‘ include RT
 
 
OT...................................................................................................  yes/no ‘ include OT
 
 
CE...................................................................................................  yes/no ‘ include CE
 
 
Elements......................................................................................... yes/no
 
 
Indirect............................................................................................ yes/no
 
 
Fixedrate......................................................................................... number ‘ if fixed, % needs to be entered here
 
 
tblFringeAllocation

classnum......................................................................................... number
 
 
description....................................................................................... text

 
actualRate....................................................................................... text ’can be fixed rate or actual rate

 
Rate................................................................................................ number ‘ if fixed % needs to be entered here
 
 
GLCode............................................................................................ number ‘ reserved gl code can be 50500-52000
 
 
Allocate............................................................................................ yes/no
 
 
Allocation........................................................................................ text ‘ can be YTD or month TD
 
 
RT................................................................................................... yes/no ‘include RT
 
 
OT................................................................................................... yes/no ‘ include OT
 
 
CE................................................................................................... yes/no ‘ include CE
 
 
LeaveCost....................................................................................... yes/no ‘ include leave
 
 
Elements......................................................................................... yes/no
 
 
Indirect........................................................................................... yes/no
 
 
Amount........................................................................................... currency
 
 
tblIndirectAlloc

Actual.............................................................................................. text ‘ can be fixed or actual
 
 
Salary.............................................................................................. text ‘ can be salary base or hourly base

 
RT...................................................................................................  yes/no ‘include RT
 
 
OT...................................................................................................  yes/no ‘ include OT
 
 
CE...................................................................................................  yes/no ‘ include CE
 
 
Leave.............................................................................................. yes/no ‘ include leave
 
 
Fringe............................................................................................. yes/no ‘ include fringe
 
 
YTD................................................................................................. text ‘ can YTD or month TD
 
 
Direct.............................................................................................. yes/no
 
 
Rate................................................................................................ number’ if fixed, % needs to be entered here
 
 
Tables Used in Cost Allocation
Leave Rate Comp Analysis
tblEmployeePayRate
tblEmployeePayRateTemp
 
Tables used in Budget
tblBudget

BudgetID......................................................................................... autonumber
 
 
Budgetname.................................................................................... text
 
 
EntryDate....................................................................................... date
 
 
User1.............................................................................................. text
 
 
Type............................................................................................... number‘ this is single or multi year
 
 
Period............................................................................................. date
 
 
Project............................................................................................ number
 
 
Element.......................................................................................... number
 

tblBudgettype ‘ this is a static table 
 
Type................................................................................................ number ‘ single or multi year and this match type in tblBudget

 
Description...................................................................................... text


tblBudgetDetail
 
 
BudLineItem................................................................................... autonumber ‘ reference
 
 
BudgetID......................................................................................... number‘ this matches BudgetID in tblBudgetDetail
 
 
Project............................................................................................ number
 
 
Element......................................................................................... number
 
 
Glcode............................................................................................ number 

 
EmployeeNum................................................................................ number ‘ this is for timesheet budget

 
BudAmount.................................................................................... currency
 
 
PriorYear......................................................................................... currency

 
Empclass........................................................................................ number‘ this is for timesheet budget

 
Year End
tblGLPriorYear
tblREPriorYear 

 
Cost Summary (Supplement #337)
tblCostSummarySupplement
 
Consolidated Cost Allocation Detail (Supplement #399)
tblCostAllocationDetailAnalysis
tblCostAllocationDetailCurrent
tblCostAllocationDetailPrior
tblGLFinancialReportDetail tblIndirectCostRate
 
R & E Report Designer (Supplement #412)
tblCombinedProjectElement
tblCostCategories
tblCostCategoryAssign
tblGLFinancialReportSupp
 
Shift Indirect (Supplement #383)
tblShiftIndirect
 
Supp #314 Board of Directors’ Reports and Supp# 412 R&E Report Designer
tblCostCategories
tblCostCategoryAssign
tblCombinedProjectElement

tblReportDesignerFormat
tblReportDesignerProject
tblBalanceSheetCategories
tblBalanceSheetCategoryAssign
tblAgencywidebyCategory 
 
Salary, Leave and Fringe Detail by Employee (Supplement #354)
tblSalaryLeaveFringeDetail
 
Year End Closing Entries (Supplement #398)
tblGLPriorYear
tblREPriorYear
tblYearEndClosing
tblGLReportYearEnd
tblSpecialAllocationPrior
tblServiceUnitBasePrior
tblShiftIndirectPrior
tblLeaveAllocationPrior
tblFringeAllocationPrior
tblIndirectCostPrior
tblDualIndirectPrior
tblCostAllocationLocksPrior
tblRevenueRecognition
tblCurrentLeaveBalance 
 
Tables used in the AP Process:
Vendor Master File
tblVendor

VendorCode..................................................................................... number
 
 
VendorName................................................................................... text
 
 
Address1......................................................................................... text
 
 
Address2......................................................................................... text
 
 
Address3......................................................................................... text
 
 
AlphaCode....................................................................................... text
 
 
Telephone....................................................................................... text
 
 
Acct................................................................................................ text
 
 
FedID.............................................................................................. text
 
 
Paidytd............................................................................................ number/double text


Location.......................................................................................... text ‘ reference


Discount......................................................................................... number
 
 
City................................................................................................ text
 
 
State.............................................................................................. text
 
 
Zip.................................................................................................. text
 
 
Active............................................................................................. yes/no
 
 
Onetime.......................................................................................... yes/no
 
 
Transcode....................................................................................... text
 
 
Vendorbanknum.............................................................................. text
 
 
VendorBankAcctNum....................................................................... text
 
 
Box................................................................................................. number ‘ 1099 box#
 
 
Eligible............................................................................................ Yes/no
 
 
Vouchers 

tblAPBatch ‘ this table stores Voucher batch control information
 
 
BatchNum....................................................................................... autonumber ‘ this batchnum matches batchnum in tblVoucher

 
BatchTotal....................................................................................... currency
 
 
Vouchers......................................................................................... number
 
 
ProgramEl....................................................................................... number
 
 
Trancode......................................................................................... number
 
 
PostingPeriod.................................................................................. date
 
 
Posted............................................................................................ yes/no
 
 
TransDate....................................................................................... date
 
 
PO.................................................................................................. number

 
VenNum......................................................................................... number
 
 
BatchDesc....................................................................................... text
 
 
tblVoucher ‘ this is the table with voucher data

VoucherNum................................................................................... number
 
 
BatchNum...................................................................................... number
 
 
VoucherDate................................................................................... Date
 
 
PO.................................................................................................. number
 
 
Amount.......................................................................................... currency
 
 
VendorCode.................................................................................... number
 
 
InvoiceNum.................................................................................... text
 
 
InvoiceDate.................................................................................... Date
 
 
PayDueDate.................................................................................... Date
 

Voucher......................................................................................... text ‘1 means separate check for the vendor

 
Description.................................................................................... text
 
 
tblLineItem ‘ this is the table with voucher and line item distribution

Linenum........................................................................................ Autonumber
 
 
VoucherNum.................................................................................. number
 
 
GLCode.......................................................................................... number
 
 
Element......................................................................................... number
 
 
Debit.............................................................................................. currency
 
 
Credit............................................................................................. currency
 
 
PO.................................................................................................. number
 
 
ProjectCode.................................................................................... number
 
 
trancode........................................................................................ number ‘ this is not used
 
 
PolineNum..................................................................................... number

 
Batchno.........................................................................................
 
 
Cash Disbursements
tblCheck ‘ this is the check table for A/P and Payroll

ID..................................................................................................... autonumber
 
 
CheckNum........................................................................................ number
 
 
CheckDate........................................................................................ date
 
 
Amount............................................................................................. currency
 
 
ClearDate.......................................................................................... date
 
 
ClearAmount..................................................................................... currency
 
 
BankAccount..................................................................................... number
 
 
CheckRun......................................................................................... number
 
 
PostingPeriod.................................................................................... Date ‘only used for payroll checks
 
 
void.................................................................................................. Yes/No
 
 
Status............................................................................................... text
 
 
Batchno............................................................................................ number ‘ this batchno match batchno in tblAppmts
 
 
Directdeposit..................................................................................... Yes/No ‘ only checked for payroll checks
 
 
tblAPPmts ‘ this is the detail payment info for A/P checks

VoucherNum..................................................................................... number
 
 
LineNum........................................................................................... number
 
 
Amount............................................................................................ currency
 
 
Checkrun.......................................................................................... number
 
 
Description........................................................................................ text
 
 
Batchno............................................................................................ number ‘ this batchno matches batchno in tblCheck
 
 
Purchase Orders 
tblPo ‘ this is the PO main table

PO.................................................................................................... autonumber
 
 
PoDate.............................................................................................. date
 
 
Status............................................................................................... text
 
 
VendorCode....................................................................................... number
 
 
Shipto............................................................................................... text
 
 
Shipviatext........................................................................................
 
 
Emp.................................................................................................. text
 
 
Remarks........................................................................................... text
 
 
DateReq............................................................................................ text
 
 
Tax................................................................................................... currency
 
 
Shipping............................................................................................ currency
 
 
TotalAmt........................................................................................... currency
 
 
tblPoItem ‘ this is the line item for each PO

PO.................................................................................................... number
 
 
Quan................................................................................................ number
 
 
Description........................................................................................ text
 
 
Unitprice........................................................................................... currency
 
 
Amt.................................................................................................. currency
 
 
tblPoPayment ‘ this is the PO’s vouchered info

PaymentNum.................................................................................... autonumber
 
 
VoucherNum..................................................................................... number
 
 
VoucherLineNum............................................................................... number
 
 
PONum............................................................................................. number
 
 
POLinenum....................................................................................... number
 
 
Amount............................................................................................ currency

 
Complete 1099’s (Supplement #330)

tblAP1099.........................................................................................  ‘ this is the 1099 info
 
 
tblAP1099......................................................................................... Temp
 
 
tblConversion1099............................................................................
 
 
Voucher Import (Supplement #413)
tblVoucherImportSetup
tblVoucherImportException
tblVoucherImportBilling
tblVoucherImport
tblVoucherImportTemp 

 
Tables used in Payroll
Setups
tblDeductionCode

deductioncode................................................................................... number
 
 
Description........................................................................................ text
 
 
DeferFica........................................................................................... yes/no
 
 
DefterMFica....................................................................................... yes/no ‘ medicare
 
 
DeferFed........................................................................................... yes/no
 
 
Defterstate........................................................................................ yes/no
 

 
Amount............................................................................................ text ‘ a for Amount or % for Percentage

 
YTD.................................................................................................. yes/no
 
 
PPMax............................................................................................... yes/no ‘ this is for pay period max
 
 
MinNet.............................................................................................  yes/no ‘ this is for the min net pay amount
 
 
Dincome...........................................................................................  yes/no ‘ this is used for defer
local
 
 
Active............................................................................................... yes/no
 
 
Glcode..............................................................................................  number ‘ this is the GL code for PR journal number ‘ this is the                                                                                                                           element for

Element............................................................................................ PR Journal
 
 
Unemployment................................................................................. Yes/no
 

 
W2box.............................................................................................. text ‘ this allows deduction to be reported on W2 form
 

tblPayCode


PayCode............................................................................................ text ‘ like RT,OT,CE
 
 
Description........................................................................................ text
 

Elem................................................................................................ number ‘ allows to put element code for Leaves ‘ 995100-996900 are                                                                                                                reserved to be used here

CE.................................................................................................... number ‘ allows to enter a rate for CE and OT
 
 
OT.................................................................................................... number/double
 
 
CT.................................................................................................... number/double
 
 
Subtract...........................................................................................  text ‘ the leave type to subtract from
 
 
tblPositionCode

code.................................................................................................. number/long
 
 
Description........................................................................................ text
 
 
Active............................................................................................... yes/no
 
 
tblStateTaxCode

code................................................................................................. number/long
 
 
Description....................................................................................... text
 
 
Account............................................................................................ text
 
 
Active............................................................................................... yes/no
 
 
tblLocalTaxCode

code................................................................................................. number/long
 
 
Description........................................................................................ text
 
 
Rate................................................................................................. number/double
 
 
Active............................................................................................... yes/no

 
tblPersonnelBoxCode

code................................................................................................. number/long
 
 
Description....................................................................................... text
 
 
Type................................................................................................. text ‘ date,number,text,
 
 
Active............................................................................................... yes/no
 
 
tblWorkCompCode

code................................................................................................. number/long
 
 
Description....................................................................................... text
 
 
Rate................................................................................................. number/double
 
 
Active............................................................................................... yes/no
 
 
YTDMax............................................................................................ number/double
 
 
tblDepartmentCodes

code................................................................................................. number/long
 
 
Description....................................................................................... text
 
 
Active............................................................................................... yes/no
 
 
tblSiteCodesDescriptions

code................................................................................................. number/long
 
 
Description....................................................................................... text
 
 
Active............................................................................................... yes/no

tblSpecialPay

code................................................................................................. number/long
 
 
Description....................................................................................... text
 
 
Amt.................................................................................................  text ‘ A for amount, % for percentage
 
 
Net..................................................................................................  yes/no ‘ affect net pay or not
 
 
FICA................................................................................................ yes/no
 
 
MFICA..............................................................................................  yes/no ‘ medicare
 
 
Fed..................................................................................................  yes/no ‘ federal tax

 
State................................................................................................ yes/no
 
 
Local................................................................................................ yes/no
 
 
UnEmp............................................................................................ yes/no
 
 
WorkerCom...................................................................................... yes/no
 
 
Retire.............................................................................................. yes/no
 
 
Active.............................................................................................. yes/no
 
 
tblLeaveType

leavetype......................................................................................... text

Description....................................................................................... text

Active.............................................................................................. yes/no

 
tblEmpMultiplePayRate

EmpCode......................................................................................... number
 
 
Rate................................................................................................. number/double
 
 
Description....................................................................................... text
 
 
tblWorkersCompBase

RT.................................................................................................... Yes/no
 
 
OT................................................................................................... yes/no
 
 
Percentage....................................................................................... number/double’ % of OT
 
 
SpecialPay........................................................................................ Yes/no ‘ used to include leave taken
 
 
Fica................................................................................................. yes/no
 
 
Hours.............................................................................................. yes/no
 
 
PaidHour.......................................................................................... yes/no
 
 
WorkHour........................................................................................ yes/no
 
 
totalHour......................................................................................... yes/no
 
 
tblKYLocalTax

Code................................................................................................  number ‘ must be 1000 and above
 
 
Description....................................................................................... text


Rate................................................................................................ double
 
 
MinWage......................................................................................... double
 
 
maxWage........................................................................................ double
 
 
Active............................................................................................. yes/no


tblGarnishment


DeCode........................................................................................... number

 
Defer............................................................................................... number’ deduction code that is deferred from garnishment

 
Employee Master File
tblEmpMaster


EmployeeCode................................................................................. number/long
 
 
LName............................................................................................ text
 
 
FName............................................................................................ text
 
 
Middle.............................................................................................. text
 
 
Jr.................................................................................................... text
 
 
Rate................................................................................................ number ‘ hourly, annually,ppd rate
 
 
AH.................................................................................................. text ‘ Annually, hourly or PPD
 
 
Class............................................................................................... number
 
 
Status............................................................................................. text ‘ Active or Terminate
 
 
EmployeePic.................................................................................... text ‘ this field is not used
 
 
StateStatus..................................................................................... text ‘ S, M or B
 
 
FedStatus........................................................................................ text ‘ S, M or B
 
 
FedDep............................................................................................ number
 
 
FedWithhold.................................................................................... Currency ‘ this is used for additional Fed
 
 
StateDep......................................................................................... number
 
 
StateRes......................................................................................... text ‘ this is used in state tax calculation
 
 
StateWithhold.................................................................................. currency ‘ this is used for additional state
 
 
DirDepositYCS.................................................................................. currency ‘ this field is not used
 
 
ExpReimb........................................................................................ currency ‘ employee reimbursement
 
 
SS...................................................................................................  text ‘ SSN
 
 
Address1......................................................................................... text
 
 
Address2......................................................................................... text
 
 
City................................................................................................. text
 
 
State............................................................................................... text
 
 
Zip.................................................................................................. text
 
 
Phone............................................................................................. text
 
 
BirthDate........................................................................................ text
 
 
HireDate.......................................................................................... text
 
 
TerminationDate.............................................................................. text
 
 
LastReview...................................................................................... text
 
 
NextReview..................................................................................... text
 
 
Position........................................................................................... number’ this match code in tblPositionCode

 
Department..................................................................................... number’ this match code in tblDepartmentCode

 
WorkersCompCode........................................................................... number ‘ this match code in tblWorkersCompCode
 
 
SiteCode.......................................................................................... number ‘ this match code in tblSiteCodeDescription
 
 
Sex................................................................................................. text
 
 
SpecialPay1..................................................................................... number ‘ this match code in tblSpecialPay

 
SpecialPayAmt1............................................................................... currency
 
 
SpecialPay2..................................................................................... number ‘ this match code in tblSpecialPay
 
 
SpecialPayAmt2............................................................................... currency
 
 
Schooldistrict................................................................................... number’ Ohio school district code, match tblOHSchoolDistrict
 
 
exempded....................................................................................... currency ‘ this is for state MS
 
 
AskPay............................................................................................ yes/no ‘ this is for ask for hours
 
 
FedExempt...................................................................................... yes/no
 
 
StateExempt................................................................................... yes/no
 
 
tblEmpYTD


EmployeeCode................................................................................. number
 
 
YTDRegHours................................................................................... number
 
 
YTDRegEarnings.............................................................................. currency
 
 
YTDOTHours.................................................................................... number
 
 
YTDOTEarnings................................................................................ currency
 
 
YTDAv1........................................................................................... currency ‘ this field is not used
 

YTDAv2........................................................................................... currency ‘ this is special pay amount to affect net pay

 
YTDExp........................................................................................... currency ‘ this is expense reimbursement
 
 
YTDAv3........................................................................................... currency ‘ this field is not used
 
 
YTDAv4........................................................................................... currency ‘ this is special pay not to affect net pay
 
 
YTDFedl........................................................................................... currency ‘ this field for federal tax
 
 
YTDSt.............................................................................................. currency ‘ this field for state tax
 
 
YTDLocal......................................................................................... currency ‘ this field is for local tax
 
 
YTDFICA.......................................................................................... currency ‘ this field is for fica
 
 
YTDMFICA....................................................................................... currency ‘ this field is formedicare
 
 
YTDEIC............................................................................................ currency
 
 
QTDRegHours.................................................................................. number
 
 
QTDRegEarnings.............................................................................. currency
 
 
QTDOTHours................................................................................... number
 
 
QTDOTEarnings............................................................................... currency
 
 
QTDAv1.......................................................................................... currency ‘ this field is not used

 
QTDAv2.......................................................................................... currency ‘ this is special pay amount

 
QTDExp.......................................................................................... currency ‘ this is expense reimbursement
 
 
QTDAv3.......................................................................................... currency ‘ this field is not used
 
 
QTDAv4.......................................................................................... currency ‘ this field is not used
 
 
QTDFed........................................................................................... currency ‘ this field for federal tax
 
 
QTDSt............................................................................................. currency ‘ this field for state tax
 
 
QTDLocal........................................................................................ currency ‘ this field is for local tax
 
 
QTDFICA......................................................................................... currency ‘ this field is for fica
 
 
QTDMFICA....................................................................................... currency ‘ this field is for medicare
 
 
QTDEIC........................................................................................... currency
 
 
tblEmpLeave

EmpCode........................................................................................ number
 
 
Leavetype....................................................................................... text ‘ AL, SL,PL
 
 
PPD................................................................................................ number ‘ leave earn per pay period


LeaveEarn...................................................................................... number
 
 
LeaveTaken.................................................................................... number
 
 
Balance.......................................................................................... number
 
 
LeaveMax....................................................................................... number
 
 
tblEmpDeduction

Empcode........................................................................................ number
 
 
Decode........................................................................................... number
 
 
Amt............................................................................................... number
 
 
YTDMax.......................................................................................... currency
 
 
PPDMax.......................................................................................... currency
 
 
MinNet........................................................................................... currency
 
 
YTDDeductioncurrency....................................................................
 
 
QTDDeductioncurrency...................................................................
 
 
AcctNum........................................................................................ text ‘for direct deposit purpose
 
 
AcctType........................................................................................ number ‘ 22/23 for checking, 32/33 for saving
 
 
Bank.............................................................................................. text ‘ bank account for direct deposit
 
 
tblEmpLocalTax

EmpCode....................................................................................... number

 
LocalCode....................................................................................... number ‘ match code in tblLocalTaxCodes

 
Rate............................................................................................... number
 
 
tblEmpPersonnel

EmpCode....................................................................................... number
 
 
Box Code........................................................................................ number ‘ optional question number
 
 
Type.............................................................................................. text ‘ Alpha, Numeric, Date, $ dollar amt or % percentage 
 
 
Reponse......................................................................................... Text ‘answer
 
 
Timesheets....................................................................................
 
 
tblTiBatch

BatchID......................................................................................... autonumber ‘ match batchnum in tblTimseheetBatch
 
 
StartPeriod..................................................................................... date
 
 
StartPeriod..................................................................................... date
 
 
PostPeriod...................................................................................... date ‘ this is not used
 
 
Element......................................................................................... number
 
 
Timesheet...................................................................................... number
 
 
Hours............................................................................................. number
 
 
Posted........................................................................................... yes/no
 
 
BatchDesc...................................................................................... text
 
 
tblTimesheetBatch

Empnum........................................................................................ number
 
 
ElementCode................................................................................. number
 
 
Paycode......................................................................................... text ‘ match tblPayCode
 
 
Hours............................................................................................ number
 
 
EmpName...................................................................................... text
 
 
ProjectCode.................................................................................... number
 
 
Rate............................................................................................... number
 
 
DistribAmount................................................................................ currency ‘ this amount include CE amt
 
 
EmpClass....................................................................................... number
 
 
BatchNum..................................................................................... number
 
 
PayrollAmt..................................................................................... currency

 
Payroll Adjustments
payrolladjustment


Record........................................................................................... number
 
 
EmpCode....................................................................................... number
 
 
TransType...................................................................................... text ‘ CADJ,CURR,PADJ
 
 
RegHours....................................................................................... number
 
 
RegEarning.................................................................................... number
 
 
OvertimeHour................................................................................ number
 
 
OverTimeEarning........................................................................... number
 
 
Available1...................................................................................... number
 
 
Available2...................................................................................... number ‘special pay
 
 
Expenses....................................................................................... currency
 
 
LocalTaxCode................................................................................. number
 
 
CheckDate..................................................................................... date
 
 
Federal.......................................................................................... currency
 
 
State............................................................................................. currency
 
 
Fica............................................................................................... currency
 
 
MFICA............................................................................................ currency
 
 
CheckNumber................................................................................ number
 
 
EmpName..................................................................................... text
 
 
Payroll........................................................................................... yes/no ‘ Yes means adjustment is included in PR
 
 
AffectNetPay.................................................................................. currency’ special pay
 
 
NotAffectNetPay............................................................................ currency ‘ special pay
 
 
EIC................................................................................................ currency
 
 
Payrolladjustment.......................................................................... Deduction

 
Record........................................................................................... number
 
 
EmpCode...................................................................................... number
 
 
Trans............................................................................................ text ‘PADJ,CADJ,CURR
 
 
DeCode......................................................................................... number
 
 
Amt.............................................................................................. currency
 
 
YTDMax........................................................................................ currency
 
 
PPDMax........................................................................................ currency
 
 
MinNet......................................................................................... currency
 
 
YTDDeduction............................................................................... currency
 
 
QTDDeduction............................................................................... currency
 
 
Expense Reimbursements
tblTR ‘ this is the expense reimbursement

Batchno........................................................................................ number
 
 
Emp............................................................................................. number
 
 
Trans............................................................................................ number
 
 
Amount........................................................................................ currency
 
 
Elem............................................................................................. number
 
 
Blank............................................................................................ text ‘ this field is not used
 
 
Temp............................................................................................ text ‘ this field is not used
 
 
Posted........................................................................................... yes/no
 
 
tblTRBatch

BatchID........................................................................................ number
 
 
StartPeriod.................................................................................... date
 
 
EndPeriod..................................................................................... date
 
 
PostPeriod..................................................................................... date
 
 
GLCode......................................................................................... number


Element........................................................................................ number
 
 
GLTotal......................................................................................... double
 
 
Employee..................................................................................... double
 
 
Hours........................................................................................... double
 
 
Posted.......................................................................................... Yes/no
 
 
Payroll.......................................................................................... Yes/no
 
 
Emp............................................................................................. double
 
 
BatchDesc.................................................................................... text
 
 
PostGL.......................................................................................... Yes/no
 
 
Leave Processing
tblLeaveMatrix


LeaveType.................................................................................... text ‘ match leavetype field in tblLeaveType
 
 
Classnum..................................................................................... number ‘ match classnum in tblClass1
 
 
Flag.............................................................................................. yes/no
 
 
FlagHour...................................................................................... number
 
 
Maxi............................................................................................. number
 
 
Emp............................................................................................. yes/no
 
 
Class............................................................................................ yes/no
 
 
MaxHour....................................................................................... number
 
 
Fixed............................................................................................ text ‘ fixed or prorated
 
 
RT................................................................................................ yes/no
 
 
OT................................................................................................ yes/no
 
 
LeaveTaken.................................................................................. yes/no
 
 
PPHour......................................................................................... yes/no ‘ allow to exceed earning hour in employee file

 
tblLeaveEarn 
 
LeaveType.................................................................................... text ‘ this is all the leave type that can earn leaves in the matrix

 
Description................................................................................... text’ not used
 
 
tblEmpCurrentLeave

EmpCode..................................................................................... number
 
 
Leavetype.................................................................................... text ‘ AL, SL,PL
 
 
PPD.............................................................................................. number ‘ leave earn per pay period
 
 
LeaveEarn.................................................................................... number
 
 
LeaveTaken.................................................................................. number
 
 
Balance........................................................................................ number
 
 
LeaveMax..................................................................................... number
 
 
PP................................................................................................ Date ‘ end period
 
 
StartP.......................................................................................... Date ‘ start period
 
 
tblEmpCurrentLeaveTemp ‘ this is used in yearend leave arrual

EmpCode..................................................................................... number
 
 
Leavetype.................................................................................... text ‘ AL, SL,PL
 
 
PPD............................................................................................. number ‘ leave earned per pay period
 
 
LeaveEarn................................................................................... number
 
 
LeaveTaken................................................................................. number
 
 
Balance....................................................................................... number
 
 
LeaveMax.................................................................................... number
 
 
PP............................................................................................... Date ‘ end period
 
 
StartP.......................................................................................... Date ‘ start period
 
 
Record......................................................................................... number ‘ this tells TI process order

 
Tables used in prepare current payroll
tblEmpcurrentPayroll


EmployeeCode............................................................................. Number/long
 
 
Transtype.................................................................................... text ‘ CURR,PADJ,CADJ
 
 
LName......................................................................................... text
 
 
FName......................................................................................... text
 
 
Middle.......................................................................................... text
 
 
Jr................................................................................................. text
 
 
Rate............................................................................................ number ‘ hourly, annually,ppd
rate
 
 
AH............................................................................................... text ‘ Annually, hourly or PPD
 
 
Class........................................................................................... number
 
 
Status......................................................................................... text ‘ Active or Terminate
 
 
EmployeePic................................................................................ text ‘ this field is not used
 
 
StateStatus................................................................................. text ‘ S, M or B
 
 
FedStatus.................................................................................... text ‘ S, M or B
 
 
FedDep........................................................................................ number
 
 
FedWithhold................................................................................. Currency ‘ this is used for additional Fed

 
StateDep..................................................................................... number
 

 
StateRes...................................................................................... text ‘ this is used in state tax calculation

 
EIC.............................................................................................. number/double
 
 
EICAmt....................................................................................... currency
 

 
StateWithhold.............................................................................. currency ‘ this is used for additional state

 
DirDepositYCS.............................................................................. currency ‘ this is special pay not to affect net pay
 
 
ExpReimb.................................................................................... currency ‘ employee reimbursement
 
 
LocalWithhold.............................................................................. currency ; this is for additional local
 
 
FICA............................................................................................ currency ‘ fica withhold
 
 
SS............................................................................................... text ‘ SSN
 
 
Address1..................................................................................... text
 
 
Address2...................................................................................... text
 
 
City............................................................................................. text
 
 
State........................................................................................... text
 
 
Zip............................................................................................... text
 
 
Phone.......................................................................................... text
 
 
BirthDate.....................................................................................  text
 
 
HireDate......................................................................................  text
 
 
TerminationDate.......................................................................... text
 
 
LastReview.................................................................................. text
 
 
NextReview................................................................................. text

 
Position....................................................................................... number’ this match code in tblPositionCode

 
Department................................................................................. number’ this match code in tblDepartmentCode
 
 
WorkersCompCode....................................................................... number ‘ this match code in tblWorkersCompCode
 
 
SiteCode...................................................................................... number ‘ this match code in tblSiteCodeDescription
 
 
Sex............................................................................................. text
 
 
SpecialPayAmt............................................................................. this is special pay to affect net pay
 
 
Paycode....................................................................................... text
 
 
RegHour...................................................................................... number‘RT
 
 
CurAmt....................................................................................... currency ‘RT earning
 
 
RTHour........................................................................................ number‘OT
 
 
OTAmt......................................................................................... currency ‘OT earning
 

 
Deduction.................................................................................... currency ‘ total deduction amount

 
Netpay........................................................................................ currency
 
 
Ppyear......................................................................................... number ‘ pay period per year
 
 
Checknum................................................................................... number
 
 
Checkdate................................................................................... date
 
 
DeferFed..................................................................................... currency
 
 
DeferState................................................................................... currency
 
 
DeferFICA.................................................................................... currency
 
 
DeferMFICA................................................................................. currency
 
 
DeferLocal................................................................................... currency
 
 
FedTax........................................................................................ currency
 
 
StateTax...................................................................................... currency
 
 
MFICA......................................................................................... currency
 
 
LocalTax...................................................................................... currency
 
 
Adj.............................................................................................. yes/no ‘ this is used to see if this payroll has been printed
 
 
Batchno...................................................................................... number
 
 
StartPeriod.................................................................................. date
 
 
EndPeriod....................................................................................  date
 
 
Schooldistrict............................................................................... number ‘ this field is not used

 
Unemployment............................................................................ currency
 
 
tblEmpCurrentDeduction

Empcode..................................................................................... number
 
 
Decode........................................................................................ number
 
 
Amt............................................................................................ number
 
 
YTDDeduction.............................................................................. currency
 
 
QTDDeduction............................................................................. currency
 
 
YTDMax....................................................................................... currency
 
 
AcctNum.....................................................................................  text ‘for direct deposit purpose
 
 
AcctType.....................................................................................  number ‘ 22/23 for checking, 32/33 for saving
 
 
LName........................................................................................ text
 
 
Fname........................................................................................ text
 
 
PP............................................................................................... date ‘ pay period
 
 
Bank........................................................................................... text ‘ bank account for direct deposit
 
 
Transtype.................................................................................... text ‘PADJ,CURR,CADJ
 
 
Tables used for taxes

tblTaxRange................................................................................ ‘state tax range
 
 
tblStatudDed............................................................................... ‘ standard deduction
tblDependentDed
tblOHSchoolDistrict
tblMDLocalTaxRange
tblFedTaxRange
tblEICRange
tblCTExemptions......................................................................... ‘ Connecticut state
tblCTDecRate
tblFICA
 
 
Payroll Checks
tblPayrollCheck


EmpCode..................................................................................... number
 
 
CheckNum.................................................................................. number ‘ if direct deposit, checknum is 0


CheckDate.................................................................................. date


Amount...................................................................................... currency
 
 
PostingPeriod............................................................................... date
 
 
AcctNum..................................................................................... number
 
 
Batchno...................................................................................... number
 

YTD Payroll and Deductions Register 
 

tblEmpYTDPayroll......................................................................... ‘This table looks the same as tblEmpCurrentPayroll, it stores YTD                                                                                                                      payroll data

tblEmpYTDDeduction................................................................... ‘ this table is almost the same as tblEmpDeduction except it has                                                                                                                         payperiod. So all the YTD payroll deductions will be here.

 
tbltempytddeductionregister........................................................ ‘ this table is used for YTD deduction register
 
 
Unemployment Analysis
tblUnemplRate


Rate............................................................................................ ‘Unemployment rate entered by user
 
 
LimitAmt..................................................................................... ‘Maximum unemployment wages

 
tblUnemplDetail

EmpID........................................................................................ ‘Employee code
 
 
SS............................................................................................... ‘Social Security number
 
 
LName........................................................................................ ‘Last name
 
 
FName........................................................................................ ‘First name
 
 
CurWages.................................................................................... ‘Taxable unemployment wages
 
 
CurWages1.................................................................................. ‘Gross wages before deferrals
 
 
WagesUnderLimit......................................................................... ‘Taxable Unemployment wages
 
 
UnemplAmt................................................................................. ‘taxable wages times rate

 

Workers Comp Analysis
tblWorkerCompAnalysis


ID............................................................................................... ‘Employee code
 
 
SS............................................................................................... ‘Social Security number
 
 
LName........................................................................................ ‘Last name
 
 
FName........................................................................................ ‘First name
 
 
Wages......................................................................................... ‘Gross wages
 
 
WorkersComp

Code....................................................................... ‘WC code from employee master file
 
 
Rate............................................................................................ ‘WC rate from setup file
 
 
Local Tax Analysis
tblLocalTaxAnalysis


empcode..................................................................................... ‘Employee code
 
 
Empname................................................................................... ‘Employee name
 
 
Code........................................................................................... ‘local code-text field
 
 
WageAmt.................................................................................... ‘taxable local wages-currency
 
 
TaxAmt....................................................................................... ‘local tax amount-currency
 
 
PayPeriod.................................................................................... ‘this is check date-date field
 
 
tblLocalTax.................................................................................. ‘temp table for report local tax analysis
 
 
State Tax Analysis

tblStateTaxAnalysis..................................................................... ‘ this has wages and taxes for each payroll period
 
 
empcode..................................................................................... number
 
 
Empname................................................................................... text
 
 
Code........................................................................................... ‘ state abbreviations- text
 
 
WageAmt.................................................................................... ‘State Taxable wages - currency
 
 
TaxAmt....................................................................................... ‘State Tax - currency
 
 
PayPeriod.................................................................................... ‘this is check date- date field

 
tblStateTax................................................................................. 'temptablefor state tax/Ohio school district report


Ohio School Tax Analysis (Supplement #602-required for W2’s)
tblSchoolTaxAnalysis

empcode..................................................................................... number
 
 
Empname................................................................................... text
 

 
Code........................................................................................... text ‘ Ohio school district code abbreviations

 
WageAmt.................................................................................... currency
 
 
TaxAmt....................................................................................... currency
 
 
PayPeriod.................................................................................... date ‘ this is check date
 
 
Retirement Report (Supplement #517)
tblRetirementSetup


Plannum..................................................................................... ‘Plan 1,2, or 3
 
 
PlanFrom.................................................................................... ‘Starting date of Plan year
 
 
PlanTo......................................................................................... ‘Ending date of Plan year
 
 
Class........................................................................................... ‘Class # if eligibility is by class
 
 
Deduction................................................................................... ‘Deduction # if eligibility is by deduction
 
 
Personnel.................................................................................... ‘Optional field if eligibility is by option field
 
 
FlatRate...................................................................................... ‘Checked if employer contrib. is flat rate

 
FRate.......................................................................................... ‘Rate for employer flat rate contribution

 
PersonnelField............................................................................. ‘Option field if employer share varies for employees
 
 
Ratio........................................................................................... ‘Ratio of employer optional to employee optional
 
 
MaxAmt...................................................................................... ‘Employer maximum contribution
 
 
tblRetirementSetupDed
 
Plannum..................................................................................... ‘Plan 1,2, or 3
 
 
Dedcode...................................................................................... ‘Deduction code
 

AdjusWages................................................................................ ‘Checked for deductions that affect Retirement Wages

 
RequireEmp................................................................................ ‘Checked for deductions used for required employee cont
 
 
OptEmp...................................................................................... ‘Checked for deductions used for optional employee cont
 
 
LoanPay...................................................................................... ‘Checked for deductions used for loan payments
 
 
tblRetirement ‘for report

ID............................................................................................... ‘autonumber
 
 
Plannum..................................................................................... ‘Plan 1,2 or 3
 
 
EmpCode.................................................................................... ‘Employee Code
 
 
CurrWages.................................................................................. ‘taxable current wages
 
 
CurrAdj....................................................................................... ‘taxable wages from current payroll adjustment

 
CurrRetire................................................................................... ‘total of taxable wages and adjustments
 
 
CurrEmpRe................................................................................. ‘Employee required contribution
 
 
currEmpOpt................................................................................ ‘Employee optional contribution
 
 
currEmperRe............................................................................... ‘Employer required contribution
 
 
currEmperOpt............................................................................. ‘Employer optional contribution
 
 
currLoan..................................................................................... ‘current loan amount deducted
 
 
YTDWages................................................................................... ‘taxable YTD wages
 
 
YTDAdj........................................................................................ ‘taxable YTD payroll adjustments
 
 
YTDRetire.................................................................................... ‘total
 
 
YTDEmpRe.................................................................................. ‘YTD employee required contribution

 
YTDEmpOpt................................................................................ ‘YTD employee optional contribution
 
 
YTDLoan..................................................................................... ‘YTD loan amount deducted
 
 
Eligible........................................................................................ ‘Employees checked are eligible
 
 
StartPay..................................................................................... ‘Pay period starting date
 
 
EndPay....................................................................................... ‘Pay period ending date
 
 
RetirementCurrent...................................................................... ‘All current payroll *** take this out
 
 
CurrHours................................................................................... double
 
 
YTDHours.................................................................................... double
 
 
tblRetirementCurrent ‘Current Payroll Only

ID............................................................................................... ‘autonumber
 
 
Plannum..................................................................................... ‘Plan 1,2 or 3
 
 
EmpCode.................................................................................... ‘Employee Code
 
 
CurrWages.................................................................................. ‘taxable current wages
 
 
CurrAdj....................................................................................... ‘taxable wages from current payroll adjustment
 
 
CurrRetire................................................................................... ‘total of taxable wages and adjustments
 
 
CurrEmpRe................................................................................. ‘Employee required contribution
 
 
currEmpOpt................................................................................ ‘Employee optional contribution
 
 
currEmperRe.............................................................................. ‘Employer required contribution
 
 
currEmperOpt............................................................................. ‘Employer optional contribution
 
 
currLoan..................................................................................... ‘current loan amount deducted
 
 
StartPay.................................................................................... ‘Pay period starting date
 
 
EndPay....................................................................................... ‘Pay period ending date
 
 
Eligible........................................................................................ ‘Employees checked are eligible

 
CheckDate.................................................................................. Date
 
 
TransType................................................................................... Text ‘ CURR,CADJ or PADJ
 
 
Hours.......................................................................................... double
 
 
tblRetirementYTD ‘All payrolls since beginning of plan year

Plannum..................................................................................... ‘Plan 1,2 or 3
 
 
EmpCode.................................................................................... ‘Employee Code
 
 
CurrWages.................................................................................. ‘taxable current wages
 
 
CurrAdj....................................................................................... ‘taxable wages from current payroll adjustment
 
 
CurrRetire................................................................................... ‘total of taxable wages and adjustments
 
 
CurrEmpRe................................................................................. ‘Employee required contribution
 
 
currEmpOpt................................................................................ ‘Employee optional contribution
 
 
currEmperRe............................................................................... ‘Employer required contribution
 
 
currEmperOpt............................................................................. ‘Employer optional contribution
 
 
currLoan..................................................................................... ‘current loan amount deducted
 
 
StartPay...................................................................................... ‘Pay period starting date
 
 
EndPay....................................................................................... ‘Pay period ending date
 
 
Eligible........................................................................................ ‘Employees checked are eligible
 
 
CheckDate.................................................................................. date
 
 
Hours.......................................................................................... double
 
 
PRNumber.................................................................................. number
 
 
PRType........................................................................................ text’ C or P
 
 
FY............................................................................................... number ‘ year
 
 
Payroll Journal Entry
tblPayrollJournalSetup


ID............................................................................................... autonumber

 
Regular....................................................................................... currency
 
 
G1.............................................................................................. number ‘GL code for Regular amount
 
 
E1.............................................................................................. number ‘ element code for regular amount
 
 
Overtime.................................................................................... currency
 
 
G2.............................................................................................. number ‘GL code for OT amount
 
 
E2.............................................................................................. number ‘ element code for OT amount
 
 
SpecialPay................................................................................... currency
 

 
G3.............................................................................................. number ‘GL code for SpecialPay amount

 
E3.............................................................................................. number ‘ element code for SpecialPay amount
 
 
FICA........................................................................................... currency
 
 
G4.............................................................................................. number ‘GL code for FICA amount
 
 
E4.............................................................................................. number ‘ element code for FICA amount
 
 
MFICA......................................................................................... currency
 
 
G5.............................................................................................. number ‘GL code for MFICA amount

 
E5.............................................................................................. number ‘ element code for MFICA amount

 
Federal....................................................................................... currency

 
G6.............................................................................................. number ‘GL code for MFICA amount

 
E6.............................................................................................. number ‘ element code for MFICA amount
 
 
State.......................................................................................... currency number ‘GL code for State


G7.............................................................................................. amount
 
 
E7.............................................................................................. number ‘ element code for State amount
 
 
Local.......................................................................................... currency
 
 
G8.............................................................................................. number ‘GL code for Local amount
 
 
E8.............................................................................................. number ‘ element code for Local amount
 
 
Expenses.................................................................................... currency
 
 
G9.............................................................................................. number ‘GL code for Expenses amount

 
E9.............................................................................................. number ‘ element code for Expenses amount

 
EIC............................................................................................. currency
 

 
G10............................................................................................ number ‘GL code for EIC amount

 
E10............................................................................................ number ‘ element code for EIC amount
 
 
NetPay....................................................................................... currency
 
 
G11............................................................................................ number ‘GL code for NetPay amount
 
 
E11............................................................................................ number ‘ element code for NetPay amount
 
 
DDNetPay................................................................................... currency ‘ DD amount
 
 
G12............................................................................................ number ‘ GL code for DD amount
 

E12............................................................................................ number ‘ element code for DD amount

 
Quarterly
tblQtrReportingReg ‘temp table used for quarterly reporting
register
 
 
Annual
W2’s ‘ all the tables here are used in W2 processing

 
tblEmpW2
tblEmpYTDW2
tblEmpYTDDeductionW2
tblLocalTaxAnalysisW2
tblStateTaxAnalysisW2
tblSchoolTaxAnalysisW2
 
 
Leave History (Supplement #313)
tblLeaveHistoryNew (this has all the leave for each payroll period)

EmpNum.................................................................................... number
 
 
LeaveType.................................................................................. text
 
 
LDate......................................................................................... date ‘ check date
 
 
Opening...................................................................................... number
 
 
Earned........................................................................................ number
 
 
Taken......................................................................................... number
 
 
Balance....................................................................................... number
tblLeaveHistoryDetail This is a temp table 
 
Personnel History (Supplement #366)
tblPersonnelHistory

EmpCode.................................................................................... number
 
 
Pcode.......................................................................................... number‘ match code in tblPersonnelBoxCode
 
 
Description................................................................................. text
 
 
Date1......................................................................................... date ‘ record date
 
 
YTD Workers Comp Analysis & Monthly Direct Workers Comp
(Supplement #311)
tblYTDWorkerCompPlanYear
tblWorkersCompBase
tblWorkersCompGLCode
tblWorkersCompSpecialRate
tblWorkersCompTotal
tblWorkerCompAnalysis
tblYTDWorkersCompYTD
tblWorkersCompCurrent
 
 
Tables Used In Accounts Receivable (Supplement #336)
Customer Setup   
tblCustomer ‘ customer table
 
 
CustID........................................................................................autonumber
 
 
Name.........................................................................................text
 
 
Alpha.......................................................................................... text
 
 
Addr1......................................................................................... text
 
 
Addr2......................................................................................... text
 
 
City............................................................................................ text
 
 
State..........................................................................................text
 
 
Zip.............................................................................................text
 
 
Email.......................................................................................... text
 
 
Contact...................................................................................... text
 
 
Telephone................................................................................... text
 
 
Fax............................................................................................. text
 
 
Active......................................................................................... yes/no
 
 
LRate......................................................................................... double’ this is the late fee rate
 
 
Invoices
tblARBatch


BatchID...................................................................................... number
 
 
BatchDate................................................................................... date
 
 
Amount...................................................................................... currency
 
 
PostPeriod................................................................................... date
 
 
Invoice....................................................................................... number
 
 
Vendor....................................................................................... number
 
 
Element..................................................................................... number
 
 
Tr............................................................................................... number
 
 
Project........................................................................................ number


yes/no ARGL.......................................................................................... number


BatchDesc.................................................................................. text
 
 
tblArInvoice ‘ invoice table

InvNumber................................................................................. number
 
 
Date1......................................................................................... Date
 
 
CustomerNum............................................................................ number
 
 
Customer................................................................................... text
 
 
Order1........................................................................................ text
 
 
Amount...................................................................................... currency
 
 
Period......................................................................................... date
 
 
BatchID...................................................................................... number ‘ this batchid matches batchid in tblARBatch
 
 
Description................................................................................. text
 
 
tblARLineItem ‘ invoice line item

LineNum.................................................................................... autonumber
 
 
InvNum..................................................................................... number‘ this invoice number matches Invnumber in tblArInv
 
 
Element..................................................................................... number
 
 
Project........................................................................................ number
 
 
GlCode........................................................................................ number
 
 
Descript...................................................................................... number
 
 
Amount...................................................................................... number
 
 
BatchID...................................................................................... number‘ this matches batchid in tblArInvoice, tblArBatch
 
 
Cash Receipts
tblDeposit................................................................................. ‘ this is cash receipt batch
 
 
DepUnique................................................................................. autonumber ‘ reference purposes


DepositDate.............................................................................. date


Amount...................................................................................... currency
 
 
Period........................................................................................ date
 
 
Posted........................................................................................ yes/no
 
 
Bank.......................................................................................... number
 
 
DepositID................................................................................... number‘ this is batch control number
 
 
GL.............................................................................................. number
 
 
Element..................................................................................... number
 
 
Project....................................................................................... number
 
 
BatchDesc..................................................................................text
 
 
tblDepositItem ‘ this is the cash receipt main table

CheckID..................................................................................... autonumber ‘ this is reference purpose
 
 
Depositid.................................................................................... number‘ this number matches DepositID in tblDeposit

 
Customer................................................................................... number‘ this matches customer number in tblCustomer

 
CustomerName.......................................................................... text
 
 
Amount..................................................................................... currency
 
 
CheckDate................................................................................. date
 

Type........................................................................................... number ‘ 1 means the CR has been cleared

 
CheckNum................................................................................. text
 
 
Description................................................................................. text
 
 
GLCode...................................................................................... number ‘ not used
 
 
Elem.......................................................................................... number‘ not used
 
 
Proj............................................................................................ number‘ not used
 
 
tblDepositLineItem ‘ this is the cash receipt line item

ChkLineItem.............................................................................. autonumber ‘ reference purposes
 
 
CheckID..................................................................................... number‘ this matches checked in tblDepositItem

 
DepositID................................................................................... number‘ this matches DepositID in tblDeposit, tblDepositItem

 
LineNum.................................................................................... number
 
 
InvNumber................................................................................. number‘ this matches invoice number in tblARInvoice, tblArLineItem

 
Amount...................................................................................... Currency
 
 
GLCode...................................................................................... number
 
 
Elem.......................................................................................... number
 
 
Proj............................................................................................ number
 
 
Tables Used in Fixed Assets
tblFAGL


GLCode...................................................................................... number
 
 
Description................................................................................. text


tblFAFund

Code.......................................................................................... number
 
 
Description................................................................................. text
 
 
Restriction.................................................................................. yes/no
 
 
tblFALocation

Code.......................................................................................... number
 
 
Description................................................................................ text
 
 
Active........................................................................................ yes/no
 
 
tblFADescription

Code.......................................................................................... number


Description................................................................................. text
 
 
tblFADepreciaton
 
DeprecCode................................................................................ number
 
 
Months...................................................................................... number
 
 
tblFAFundSource

ID.............................................................................................. autonumber ‘ reference purposes
 
 
Facode.......................................................................................number‘ this matches FA number in tblFAInventory
 
 
Code.......................................................................................... number
 
 
FSCode...................................................................................... number‘ matches code in tblFAFund
 
 
Percentage................................................................................. number
 
 
Price.......................................................................................... currency
 
 
tblFAInventoryFA

Code...................................................................................... number‘ this matches FSCode in tblFAFundSource
 
 
Description.................................................................................text
 
 
Status....................................................................................... text
 
 
Pdate......................................................................................... date
 
 
Price........................................................................................... currency
 
 
Serial......................................................................................... text
 
 
Contract..................................................................................... text
 
 
Remark...................................................................................... text
 
 
GLCode...................................................................................... number‘ this matches GLCode in tblFAGL
 
 
LC............................................................................................. number‘ this matches code in tblFALocation
 
 
DC............................................................................................. number‘ this matches code in tblFADescription

 
FS1............................................................................................ yes/no ‘ this tells if it has multiple funding sources

 
DeprecCode................................................................................ number‘ this matches DeprecCode in tblFADepreciation

 
Totalmonth................................................................................ number‘ number of month to be depreciated

 
AmtMonth................................................................................. currency
 
 
AmtYear..................................................................................... Currency
 
 
AmtTDcurrency..........................................................................
 
 
Month........................................................................................ number ‘ number of month already depreciated
 
 
Disposition................................................................................. text ‘ reference purposes
 
 
Require...................................................................................... text ‘ reference purposes
 
 
Ddate......................................................................................... text
 
 
Dprice........................................................................................ currency
 
 
Method...................................................................................... text
 
 
DepreciationDate........................................................................ date
 
 
TagNumber................................................................................ text
 
 
txtCondition............................................................................... text
 

tblFALocationHistory 
 

Code.......................................................................................... number ‘ this is FA Code , matches Facode in tblFAInventory]

 
LcCode...................................................................................... number ‘ matches code in tblFALocation

 
Description................................................................................ text
 
 
StartPeriod................................................................................ date
 
 
Endperiod.................................................................................. date
 
 
Active........................................................................................ yes/no
 
 
tblFADeprHistory ‘ this table stores all the depreciation history

NewID....................................................................................... autonumber ‘reference


ID............................................................................................. number‘reference
 
 
DeprDate................................................................................... Date
 
 
FaCode...................................................................................... number‘ this matches FACode in tblFAInventory]
 
 
Description................................................................................ text
 
 
Acquired.................................................................................... date ‘ purchase date
 
 
GLCode..................................................................................... number
 
 
Location.................................................................................... number
 
 
DS............................................................................................. number
 
 
Dp............................................................................................. number
 
 
PurchAmt.................................................................................. currency
 
 
MonthsDep................................................................................ number
 
 
CurrentDep............................................................................... number
 
 
NewDepr................................................................................... number
 
 
NewTotalDep............................................................................. number
 
 
NewYTDDep............................................................................... number
 
 
NewBookValue........................................................................... number
 
 
Status....................................................................................... text

GMS, Inc.
COPYRIGHT © 2020 GRANTS MANAGEMENT SYSTEMS, INC. (GMS)
10559 Metroplitan Avenue - Kensington, MD 20895
800.933.3501

​www.gmsactg.com 
  • Home
  • Contents
  • Index
  • Search