MSDE Transaction Details Report - File Layouts and Data Mapping
The following table lists the field number, field name, description, and data type of the data for the MD MSDE Transaction Details report.
Period 13 is considered period 12.
The default file name is TRANSACTION_DETAILS_XX.tsv
where
XX is the two-digit LEA number.
Field Number | Field Name | Description | Data Type |
---|---|---|---|
1 | LOCAL_TRANSACTION_DETAIL_KEY | A unique key to identify each period balances. It should be a concatenation of School Year + Budget unit + Account + Transaction details date + LEA, separated with a semicolon. | VarChar(200) |
2 | SCHOOL_YEAR | The related school year. The combination of School Year and School Code must be unique. You cannot create multiple records with the same School Year and School Code combination. | VarChar(10) |
3 | DISTRICT_CODE | The district code where the school is located. The value is retrieved from the Local Education Agency field on the Human Resources Profile - State page. | VarChar(20) |
4 | LOCAL_STAFF_KEY | Unique identifier for a staff member within the district applications. This may be a single code value. If multiple codes/values are required to identify a staff member uniquely, these codes should be concatenated. | VarChar(100) |
5 | LOCAL_ENTITY_KEY | The school code. The value is retrieved from the School Number field on the General tab of the Location Table page. The combination of School Year and School Code must be unique. You cannot create multiple records with the same School Year and School Code combination. | VarChar(100) |
6 | LOCAL_GL_ACCOUNT_KEY | A value that uniquely identifies each general ledger account record submitted. This may be a single code value. However, these codes should be concatenated if multiple codes or values are required to uniquely identify a general ledger account within a district, school, and academic year. | VarChar(200) |
7 | TRANSACTION_DETAILS_DATE | The date on which the transaction detail entry was made. The format is MM/DD/YYYY, with a leading 0 for MM and DD. | Date |
8 | FISCAL_PERIOD | The value is retrieved from the Transaction Period field on the MD MSDE Transaction Details Report page. The format is MMYYYY, where YYYY is the second year of the school year combination. For example, if the fiscal year is 2002 - 2003, YYYY = 2003. | VarChar(6) |
9 | TRANSACTION_SOURCE_CODE | The date on which the transaction detail entry was made. The format is MM/DD/YYYY, with a leading 0 for MM and DD. | VarChar(50) |
10 | TRANSACTION_TYPE_CODE | The code for the transaction type as defined in the financial system. The values include CR, IN, JL, PL, etc. | VarChar(50) |
11 | BALANCE_TYPE | In most deployments, this attribute's value is set to Standard. Still, for some financial sources, it is necessary to differentiate between types of balances, such as Balance Forward, and this attribute can be used for that purpose. | VarChar(100) |
12 | TRANSACTION_SOURCE | The code for the transaction source as defined in the financial system. Valid values:
| VarChar(100) |
13 | TRANSACTION_TYPE | A descriptive name of the transaction type as defined in the financial system. For example, Credit Note, Invoice, Journal, Accounts Payable, etc. | VarChar(100) |
14 | TRANSACTION_VALUE | The net value of the Transaction after rolling up. The format is xxxxxxxxxxxx.xxxxx. | Numeric(18) |
15 | TRANSACTION_QUANTITY | The memo quantity of the transaction. This is in mixed units of measure, so it may only be helpful in account-level reports and analysis. | Numeric(18) |
16 | TRANSACTION_REFERENCE | An internal reference value (transaction or session number) for the specific transaction entry. | VarChar(100) |
17 | TRANSACTION_EXTERNAL_REFERENCE | An external reference value (purchase order or invoice numbers) for the specific transaction entry. | VarChar(100) |
18 | TRANSACTION_NOTES | A detailed, memo, or comment value associated with the specific transaction record. It is often used with manual transaction entries to describe the purpose or cause of the manual entry. | VarChar(500) |
19 | TRANSACTION_SYSTEM_REFERENCE | Typically, a posting or batch number or some entry-related data element used to tie this data back to the transaction source record. | VarChar(100) |
20 | TRANSACTION_SEQUENCE | Sequential value, which may optionally be used to uniquely identify the transaction detail record from a set of transactions dependent upon the FMS source system. | Numeric(2) |