Child pages
  • Specifications for USAS Audit Extraction Files
Skip to end of metadata
Go to start of metadata

Introduction

This document describes the file structures extracted from the USAS software and provided to the State Auditor's Office for auditing purposes.

General

Alpha-numeric fields may be provided in upper or lower case letters and will be left justified and filled with spaces. If Alpha-numeric fields are not applicable, the field will be filled with spaces. Numeric fields will be right justified and filled with leading zeroes. If the value of a numeric field is not applicable, it will be filled with zeros. The only exceptions are the Alpha-numeric account code fields which will be filled with zeroes when not applicable.

All signed numeric fields will contain a trailing separate sign character. The sign character immediately follows the numeric digits. The sign character will be either a "+" or "-" (ASCII values 44 and 46 respectively). For example, the picture S9(9)V99(s) represents 11 numeric digits followed by a sign field. This field would have a total size of 12 bytes. To make it easier to understand for non-technical users, the sign field has been listed as a separate 1-byte field in the following tables.

Extraction Files

The following files will be provided. The REV13 file is provided exactly as received by ODE/DIMS. All other files will be extracted from the district's USAS files.

  • Revision 13 file
  • Account Master file
  • Vendor Master file
  • Transaction Detail file

The following sections describe these files in detail.

Revision 13 file

Contains valid fund, function, object, instructional level, and receipt codes and their descriptions.

File Type: ASCII Sequential File
Record Contains: 67 Characters

Field Pos Size Definition

Record Type

1-2

X(02)

Indicates the type of account dimension on this record.

FD=Fund
FC=Function
OB=Object
IL=Instructional Level
RC=Receipt

Account Code

3-6

X(04)

Contains the fund, function, object, il, or receipt code depending on the record type. The fund and object will contain one leading zero and the instructional level will contain two leading zeroes.

Full Description

7-36

X(30)

Description of dimension

Abbreviation

37-66

X(30)

Abbreviated description of dimension

Level Flag

67

X(01)

Contains "*" if dimension is not valid at the Expenditure or Revenue account level. Contains blanks for dimensions which are valid at the Expenditure or Revenue account level.

Account Master File

The Account Master File will contain the district's chart of accounts. All fields will come from the district's account master file (acct.idx) with the exception of the fund type which will be generated based on the definitions in the Auditor of State's Revision 13 manual. This file will be located in OECN$DTA, and will be named 'ACCTAUD.SEQ'.

File Type: ASCII Sequential File
Record Contains: 77 Characters

Field Pos Size Definition

Transaction Indicator

1-2

X(02)

Indicates the type of account.

00=Cash Account
01=Appropriation Account
02=Budget Account
03=Revenue Account

Fund

3-5

X(03)

Fund code

Function/Receipt

6-9

X(04)

Contains the function code for appropriation or budget account records. Contains the receipt code for revenue records. Will contain zeroes for cash account records.

Object

10-12

X(03)

Object Code. Contains zeroes for cash and revenue account records.

SCC

13-16

X(04)

Special Cost Center

Subject

17-22

X(06)

Subject Code. Contains zeroes for cash account records.

OPU

23-25

X(03)

Operational Unit Code. Contains zeroes for cash account records.

IL

26-27

X(02)

Instructional Level Code. Contains zeroes for cash and revenue account records.

Job

28-30

X(03)

Job Assignment Code. Contains zeroes for cash and revenue account records.

Account Description

31-75

X(45)

Account Description

Fund Type

76-77

X(02)

Fund Type code

10= Governmental Funds

11=General Fund
12=Special Revenue Fund
13=Debt Service Fund
14=Capital Projects Fund


20=Proprietary Funds

21=Enterprise Funds
22=Internal Service Funds


30=Fiduciary Funds

31=Expendable Trust Fund
32=Non-Expendable Trust Fund
33=Agency Fund

Vendor Master File

The Vendor Master file will contain information about all vendors used by the district. All fields will come directly from the district's vendor file (vendor.idx). This file will be located in OECN$DTA, and will be named 'VENDAUD.SEQ'.

File Type: ASCII Sequential File
Record Contains: 192 Characters

Field Pos Size Definition

Vendor Number

1-6

9(06)

District assigned number for the vendor

Vendor Name

7-36

X(30)

Vendor name

Additional Vendor Name

37-66

X(30)

Additional Vendor Name

Vendor Address

67-96

X(30)

First line of vendor address

Additional Vendor Address

97-126

X(30)

Second line of vendor address

City

127-146

X(20)

City portion of vendor address

State

147-161

X(15)

State portion of vendor address

Country

162-171

X(10)

Country portion of vendor address

Zip Code

172-181

X(10)

Zip Code portion of vendor address. If only five digits of zip-code are provided, they will be in the first five positions and the last four positions will contain spaces.

1099 Status

182

X(01)

Indicates whether the vendor needs a 1099 and if so, which type of 1099.

0=Not Required
1=Nonemployee Compensation
2=Rent
3=Other Income
4=Medical/Health Care Payments
5=Royalty Payments
6=Attorney (Gross Proceeds)

1099 SSN/Tax Id #

183-192

9(10)

Social Security Number or Tax Identification Number used for 1099 reporting

Transaction Detail File

The Transaction Detail file will contain information about transctions processed during the fiscal year. It will also contain beginning fund balance amounts for each cash balance account. This file will be located in OECN$DTA, and will be named 'TRANAUD.SEQ'.

File Type: ASCII Sequential File
Record Contains: 197 Characters

Field Pos Size Source File Definition

Record Type

1-2

X(02)

 

Indicates the type of record.

FB=Fund Balance record
EX=Expenditure record
RC=Receipt
RX=Reduction of Expenditure record

Vendor Name

3-32

X(30)

VENDOR

Name of vendor paid. Applies only to Expenditure records.

Vendor Number

33-38

9(06)

CHECK

District assigned number for the vendor. Applies only to Expenditure records.

Description

39-68

X(30)

POAMT/ RECEIT

Description of the expenditure or receipt

PO Number

69-75

9(07)

CHECK

Purchase order number related to this expenditure

PO Item#

76-79

9(04)

CHECK

Purchase order item number related to this expenditure

PO Date

80-87

9(08)

POHIST

Date purchase order was issued, in YYYYMMDD format

Received Date

88-95

X(08)

INAMT

Date item was received, if entered, in YYYYMMDD format

Invoice Number

96-110

X(15)

CHECK

Invoice number related to this expenditure

Invoice Date

111-118

X(08)

INAMT

Date of the invoice in YYYYMMDD format

Transaction Type

119

X(01)

CHECK/ RECEIT

Indicates the type of transaction.

W=Warrant Check
M=Memo Check
C=Payroll Clearance Check (USPS Package only)
X=Reduction of Expenditure
A=Receipt
B=Refund of Receipt
D=Distribution
T=Transfer
I=Investment
L=Late Distribution (USPS Package only)

Transaction Id

120-125

9(06)

CHECK/ RECEIT

Transaction Identification Number. This will be either a check number or a receipt number. Will be zeroes for FB records.

Trans. Item#

126-129

9(04)

CHECK/ RECEIT

Transaction item number

Transaction Date

130-137

9(8)

CHECK/ RECEIT

Date of the check or receipt in YYYYMMDD format.

Transaction Amount

138-148

s9(9)v99(s)

CHECK/ RECEIT

Amount of the expenditure or receipt. Contains the beginning fund balance for FB records.

Amount Sign Field

149

X(01)

 

Sign for the transaction amount (+ or -)

Fund Type

150-151

X(02)

 

Fund Type code

10= Governmental Funds

11=General Fund
12=Special Revenue Fund
13=Debt Service Fund
14=Capital Projects Fund


20=Proprietary Funds

21=Enterprise Funds
22=Internal Service Funds


30=Fiduciary Funds

31=Expendable Trust Fund
32=Non-Expendable Trust Fund
33=Agency Fund

Transaction Indicator

152-153

X(02)

INAMT/ RECEIT

Transaction indicator portion of account code charged for this
expenditure or receipt.

00=Fund Balance record
03=Operational Receipt
04=Non-operational Receipt
05=Operational Expenditure
06=Non-operational Expenditure
07=Transfer Out
08=Transfer In

Fund

154-156

X(03)

INAMT/ RECEIT

Fund code

Function/Receipt

157-160

X(04)

INAMT/ RECEIT

Function or Receipt code. Will contain zeroes for fund balance record.

Object

161-163

X(03)

INAMT/ RECEIT

Object code. Will contain zeroes for fund balance and receipt records.

SCC

164-167

X(04)

INAMT/ RECEIT

Special Cost Center

Subject

168-173

X(06)

INAMT /RECEIT

Subject Code. Will contain zeroes for fund balance records.

OPU

174-176

X(03)

INAMT/ RECEIT

Operational Unit. Will contain zeroes for fund balance records.

IL

177-178

X(02)

INAMT/ RECEIT

Instructional Level. Will contain zeroes for fund balance and receipt records.

Job

179-181

X(03)

INAMT/ RECEIT

Job Assignment Code. Will contain zeroes for fund balance and receipt records.

Check Status

182

X(01)

CHECK

Indicates the status of the check. Applies to Expenditure records only.

O=Outstanding
R=Reconciled
V=Void

Exp/Rct Flag

183

X(01)

 

Indicates whether the amount should be totaled as an expenditure or as
a receipt. Will be blank for certain types of transaction records which
should not be included as either.

E=Expenditure
R=Receipt

Reconciled/Void Date

184-191

X(08)

CHECK

Date check was reconciled or voided in a YYYYMMDD format. Applies to Expenditure records only.

Link Id

192-197

9(06)

 

Unique number used to link associated records together. This number does not come from the USAS system, rather it is generated by the extract program as a means to group records that belong together. For example, the expenditure and receipt portions of a transfer will be on separate records with a matching link ID.

Summary of Transactions Handling

The following table summarizes what type(s) of records are created for each of the possible USAS transactions, whether this should be included in a total of expenditures/receipts, whether the amount is a positive or negative amount (as based on what is in the USAS records), and whether a link id will be generated.

Transaction Type Record Type Exp/Rct? +/- Link?

Warrant/Memo Checks

EX

Yes

+

No

Payroll Clearance Check (USPS Package only)

EX

Yes

+

No

Reduction of Expenditure

RX

Yes

-

No

Receipt

RC

Yes

+

No

Refund of Receipt (no check)

RC

Yes

-

No

Refund of Receipt (with check)

EX,RC

No,Yes

+,-

Yes

Distribution

RX,EX

Yes,Yes

-,+

Yes

Transfer

EX,RC

Yes,Yes

,

Yes

Investment

EX

No

+

No

Late Distribution (USPS Package only)

EX

No

+

No

  • No labels