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
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
