2024_StateDW_DataDictionary.xlsx

	A	B	C	D	F	G
1	StateDW Table and Field Matrix		current as of	1/17/2024	Contact financesupport@utah.gov with any questions.
2	In order to maintain data integrity you only have viewer access. To expand the grouped rows, make a copy of the document.
3	Table Names, click + on the left, to expand/collapse				COMMENTS	JOINS (minimum recommendations)
4	ABS_DOC_ACTG	Field Name	Type	Size	ABS tables contain data for documents such as GAX*'s, GAE, GMA. Information in this table can be used to query both open and closed payment transactions.	DOC_CD, DOC_DEPT_CD, DOC_ID and DOD_VERS_NO. There are time its necessary to join using the DOC_VEND_LN_NO as well.
63
64	ABS_DOC_HDR	Field Name	Type	Size		DOC_CD, DOC_DEPT_CD, DOC_ID and DOD_VERS_NO.
93
94	ABS_DOC_VEND	Field Name	Type	Size		DOC_CD, DOC_DEPT_CD, DOC_ID and DOD_VERS_NO. There are time its necessary to join using the DOC_VEND_LN_NO as well.
120
121	ACA_Payroll	Field Name	Type	Size	No personal information is contained in this table, EIN, but no names
128
129	Accounting_Period	Field Name	Type	Size	This table contains data for reporting, to translate a calendar date, into the logic needed for Fiscal Year and months, July 1, to June 30th.
139
140	Account Type	Field Name	Type	Size	Account_Type, consolidates Posting Codes from FINET Transactions.
143
144	Accounting Journal	Field Name	Type	Size	Accounting Journal provides the end user detail, at the line item level, for transactions within FINET. Do not join the accounting journal table with the accounting journal summary table.
243
244	Accounting_Journal_Summary	Field Name	Type	Size	Accounting Journal Summary contains nearly all transactions from FINET. Make sure to use as much criteria within your query as possible. The Accounting Journal Summary will provide detail at the transaction level. If you want more details such as each line from a transaction, you must use the Accounting Journal. Running a query without using criteria with the accounting journal will crash your system, it is an extremely large table.	Doc_Department, Document_Code, Document_ID, Document_Version_Number: these are the minimum joins. However there are many additional fields, depending upon you other table(s) where additional fields can be used in a join.
298
299	Activity	Field Name	Type	Size	Defined by each agency/department. See Activity Table for values. Can be any combination of letters and numbers.
315
316	AD_DOC_HDR	Field Name	Type	Size	Automatic Disbursements, payment details.
341
342	Address_Type	Field Name	Type	Size
345
346	AP_PD_CHK_AND_AP_CHK_RECON	Field Name	Type	Size	AP_PD_CHK_AND_AP_CHK_RECON is a unique custom table, combining two key tables to all you to search for a payment and that payments status.
363
364	Appropriation_Class	Field Name	Type	Size	Appropriation Class table contains the Appropriation Class structure
372
373	Appropriation_Unit	Field Name	Type	Size	Also refered as "Appropriation Unit". The Legislature authorizes spending by "Appropriaiton Line items" in Legislative Bills each year. Those Line Items are then broken down into Units which represent programs within the Line Item. All budgeted funds have a Alphanumeric Appoprioation Unit. All other funds use the fund number as the appropraiton unit. Appropriaiton units are established by the LFA for budgeting.
386
387	Balance Sheet Account	Field Name	Type	Size	0xxx-4xxx - Assets, 5xxx-6xxx - liabilities, 7xxx - Fund balance/equity
400
401	Balance_Sheet_Group	Field Name	Type	Size
407
408	Balance Sheet Type	Field Name	Type	Size
414
415	Bank Account Balance	Field Name	Type	Size
428
429	Beginning Balance	Field Name	Type	Size
439
440	BGAA_DOC_LN	Field Name	Type	Size
473
474	Bud_Stru_31_Lvl_1	Field Name	Type	Size
524
525	Budget_Structure_Approp	Field Name	Type	Size	Budget Struction Appropriation provides data from multiple FINET tables, to summarize budget details.
578
579	Budget_Structure_Reimb_Grants	Field Name	Type	Size	Budget Struction Reimbursable Grants provides data from multiple FINET tables, to summarize budget details for Grant specifically. This table combines the following tables from FINET (BUD_STRU_36_LVL2, BUD_STRU_37_LVL2, BUD_STRU_37_LVL3, BUD_STRU_39_LVL1, BUD_STRU_39_LVL2, BUD_STRU_40_LVL1, BUD_STRU_40_LVL2)
626
627	Budget_Structure_Rev_Exp	Field Name	Type	Size	Budget Struction Revenue and Expense provides data from multiple FINET tables, to summarize budget details. Allowing you to see either Revenue, Expenditures, etc. This table combines the following tables from FINET (BUD_STRU_44_LVL2, BUD_STRU_44_LVL3, BUD_STRU_45_LVL2, BUD_STRU_45_LVL3)
648
649	Charge_Type_Lookup	Field Name	Type	Size	Charge Type Lookup provides a matrix to match FINET Object Codes, to Payroll Charge Types.
653
654	CL_Document Accounting	Field Name	Type	Size
716
717	CL_Document_Header	Field Name	Type	Size
751
752	CL_Document_Vendor	Field Name	Type	Size
800
801	Cost_Accounting_Journal	Field Name	Type	Size	Cost Accounting Journal provides FINET transactions down to the level of detail for cost accounting elements. Transactions can also be found in acccounting journal as well, however cost accounting goes a few levels deeper for chart of account coding.
880
881	Cost_Accounting_Summary	Field Name	Type	Size	Cost Accounting Summaryl provides FINET transactions down to the level of detail for cost accounting elements.
907
908	CR_Document_Accounting	Field Name	Type	Size	Cash Receipts
967
968	CR_Document_Header	Field Name	Type	Size	Cash Receipts
1004
1005	CR_Document_Vendor	Field Name	Type	Size	Cash Receipts
1058
1059	Customer_Account_Option	Field Name	Type	Size	Displays details for Customers, when joined with a table such as Vendor Customer, to see multiple addresses, such as for billing, vs. other.
1073
1074	Department	Field Name	Type	Size	Use the Department table if you want to return the Name of the Department
1078
1079	Department_Unit	Field Name	Type	Size	Use the Department Unit table if you want to return the units associated within a department, etc.
1101
1102	Doc_Function_Code	Field Name	Type	Size	Doc Function Codes, 1=New, 2=Modification, 3=Cancellation
1105
1106	Doc_Phase_Code	Field Name	Type	Size	Doc Phase Codes, Data warehouse will only contain final and historical final. If a transaction is still draft or pending, it can only be accessed within finet. 3=Final, 5=Historical Final.
1109
1110	Function_Lookup	Field Name	Type	Size	Function Lookup, will provide you the function codes associated with each department.
1124
1125	Fund_Department	Field Name	Type	Size	Fund Department, will provide you the Fund codes associated with each department.
1129
1130	Funding_Line	Field Name	Type	Size	The Funding Line table includes all federal, state and other funding sources established by agencies as part of their adavnced cost accounting setup for grants and projects. This is only used by agencies that have set up "Front-End Split" Major Programs.
1154
1155	GAX_Document_Accounting	Field Name	Type	Size	Query only GAX documents using this table
1213
1214	GAX_Document_Header	Field Name	Type	Size	Query only GAX documents using this table
1246
1247	GAX_Document_Vendor	Field Name	Type	Size	Query only GAX documents using this table
1273
1274	Group_	Field Name	Type	Size	Group table
1280
1281	IA_DOC_ACTG	Field Name	Type	Size	Inventory table
1325
1326	IA_DOC_COMMLN	Field Name	Type	Size	Inventory Table
1342
1343	IA_DOC_HDR	Field Name	Type	Size	Inventory Table
1359
1360	Internal_Cost_Rate	Field Name	Type	Size	This table lists all rates established by UDOT for equipment usage/rental and materials testing. Those rates are used in the Internal Costing process.