Data Warehouse

ADDRESSEE

The last and first name of the addressee: normally, the Administrative Officer, Financial Officer, or other departmental administrator responsible for monitoring expenses.

VARCHAR2

30

ADDRESSEE_ROOM

The MIT address, building and room number, of the addressee.

VARCHAR2

10

ADMIN_FLAG

A two character code identifying how a cost collector is administered: AL for allocation cost objects; DP for DLC administered; FC for Faculty administered; RA for RA support; RT for Research Telephone; NS for NSF Shortfall; SP for service provider (facilities); CG for Core Grant (administrative component); CR for Custodial (central) responsibility.

VARCHAR2

2

AGREEMENT_TYPE

A two digit code for the type of Sponsored Research agreement, e.g. 01 for Grant, 02 for Contract, etc.

VARCHAR2

2

AGREEMENT_TYPE_DESCRIPTION

The Sponsored Research Agreement Type, e.g., Grant, Contract, Fellowship, Fixed Price Agreement, etc.

VARCHAR2

40

AUTHORIZED_TOTAL_AMOUNT

The amount authorized to be expended for a cost collector, usually by a WBS project sponsor.

NUMBER

9

BILLING_TYPE

Type of billing associated with this project: (Examples: cost reimbursement, letter of credit, scheduled billing, cash advance, internal, etc.)

VARCHAR2

30

CALC_CODE

Classic system term relating to overhead. Not in use in SAP.

VARCHAR2

1

CFDANO

Catalog of Federal Domestic Assistance number. The Catalog of Federal Domestic Assistance (CFDA) is a government-wide database of U.S. Federal programs, services, and activities. This database details assistance or benefits available to the American public, which includes research grants, community development programs, and scholarships.

VARCHAR2

7

COMPANY_CODE

Shows to which "company" in SAP a cost collector is attached. MIT uses separate "companies" in SAP to differentiate financial transactions for different legal entities with differing business rules or reporting requirements. There are currently three company codes, "CUR" for main campus, "TECR" for MIT's alumni magazine, Technology Review, and "LCP1" for Lincoln Laboratory.

VARCHAR2

4

COSTING_SHEET_DESCRIPTION

Description of the Costing Sheet associated with this Cost Collector. In SAP, the costing sheet is the mechanism used to calculate and post secondary costs such as allocations, employee benefts, and facilities and administration charges to cost collectors.

VARCHAR2

30

COSTING_SHEET_PROCEDURE

Code designating the Costing Sheet associated with this Cost Collector.

VARCHAR2

6

COST_COLLECTOR_CATEGORY_CODE

Code identifying the categories of Cost Collectors: AUXIL (auxiliary), FNDNS (fund-nonsponsored), FNDSP (fund-sponsored), GENER (general), OTHER (other), RSRCH (research). See also COST_COLLECTOR_CATEGORY-DESC.

VARCHAR2

5

COST_COLLECTOR_CATEGORY_DESC

Description identifying the categories of Cost Collectors: Auxiliary, Fund -- Not Sponsored, Fund -- Sponsored, General, Other, Research. AUXILIARY cost centers are used by auxiliary enterprises (MIT Press, Housing & Dining, etc.) which are budgeted centrally but required to break even. FUNDS--NOT SPONSORED: those with non-sponsored revenue sources and contain MIT's endowments, loan funds, discretionary funds, and other accumulated net assets. All are internal orders. FUNDS--SPONSORED: have an external sponsor as the revenue source. Sponsored activity in the Fund range is non-organized research. All are WBS elements. GENERAL : All MIT Operating Cost Centers (cost objects budgeted centrally) except the Auxiliary cost centers defined above. RESEARCH: All of MIT's sponsored research activity is in this category. Revenue is assumed to be equal to expenses. All are WBS elements. OTHER: Internal Orders and WBS elements that hold activity that should not be part of MIT's revenues and expenses. This includes activity which will be distributed to General, Funds, and Research in the future using allocations, depreciation, or billings for services rendered, and activity being administered by MIT on behalf of others.

VARCHAR2

30

COST_COLLECTOR_FP_KEY

Key used to join historical Cost Collector tables and a fact table (Balances by Fiscal Period, Summary Statement Detail) when you want to bring up the cost collector data matching the fiscal period on the fact table. For the current fiscal period, the most recent data will be brought in.

VARCHAR2

12

COST_COLLECTOR_FY_KEY

Key used to join historical Cost Collector tables and a fact table (Balances, Balances by Fiscal Period, Summary Statement Detail) when you want to bring in cost collector data as it was at the end of fiscal year of the fact data. For the current fiscal year, the most recent data will be brought in.

VARCHAR2

12

COST_COLLECTOR_KEY

Key used to join the Cost Collector to the financial transaction, and to get the master data that were current at the time of the transaction. This field should only be used for joining data, NOT for reporting.

VARCHAR2

12

COST_SHARE

XXX

VARCHAR2

1

CUSTOMER_KEY

This key is to be used to join the FINANCIAL_DETAIL and the PROJECT or PROJECT_OSP tables. This field should only be used for linking tables together, NOT for reporting.

VARCHAR2

11

DFAFS

On all DHHS grants, contracts and fellowships, this DFAFS number is needed by CAO to comply with quarterly financial reporting requirements.

VARCHAR2

13

EB_ADJUSTMENT

Description associated with EB Adjustment Key.

VARCHAR2

30

EB_ADJUSTMENT_KEY

IN SAP keys are used to specify particular EB, F&A, or Allocation rates to be associated with a Cost Collector. When a key is blank, MIT's standard EB or F&A rates apply, and there is no allocation. If the key is filled out, the special rates noted in the key description apply.

VARCHAR2

6

FISCAL_PERIOD

The fiscal year and month. For example: 199709 = March 1997.

VARCHAR2

6

FISCAL_YEAR

The 4 digit year. The fiscal year at MIT runs from July 1st through June 30th. (e.g.: June 30, 1997 would be fiscal year 1997, July 1, 1997 would be fiscal year 1998)

VARCHAR2

4

FI_CODE

XXX

VARCHAR2

1

FS_CODE

Used to identify the restriction category for MIT's fund cost collectors. U is unrestricted, T is temporarily restricted, and P is permanently restricted. These categories are used in the preparation of MIT's annual financial statements, included in the Treasurer's Report.

VARCHAR2

1

FUNDING_CLASS

For OSP accounts (20000-49999), fund class 616 is used. For fund accounts (20000-49999), 644 is used for fellowship accounts; otherwise 616 is usually used.

VARCHAR2

3

FUND_CENTER_ID

Six-digit number identifying the fund center (example: 100088 Music, 100152 Division of Toxicology).

VARCHAR2

6

FUND_ID

Identification number associated with the Fund (Example: 6779500)

VARCHAR2

7

GOVERNMENT_CONTRACT_NUMBER

The sponsor- assigned identification number for a federal reserach project. This field is also used for non-federal sponsors.

VARCHAR2

24

IS_ACCOUNT_ASSIGNMENT_ELEMENT

Flag identifying whether or not this is an account assignment element (Values: Y or N).

VARCHAR2

1

IS_BILLING_ELEMENT

Y= this is a billing element.

VARCHAR2

1

IS_CLOSED_PROJECT_WBS

Y= this PROJECT_WBS is closed. N= this PROJECT_WBS is open.

VARCHAR2

1

IS_PLANNING_ELEMENT

Y= this is a planning element.

VARCHAR2

1

LAB_ALLOCATION

Description associated with Lab Allocation Key.

VARCHAR2

30

LAB_ALLOCATION_KEY

Code associated with a Cost Collector which points to an allocation rate which will be applied to this Cost Collector.

VARCHAR2

6

MAIL_CODE

Numerical codes describing distribution of Cost Collector statements. 1: Supervisor gets a copy of the Statement and the backup for the Cost Collector; Addressee gets a copy of the Statement only. 2: Supervisor gets a copy of the Statement only. Addressee gets a copy of the Statement and the backup for the Cost Collector. 3: Supervisor gets nothing; Addressee gets the ONLY copy of the Statement and the backup for the Cost Collector. 4: Supervisor gets the ONLY copy of the Statement and the backup for the Cost Collector; Addressee gets nothing. 5: Supervisor does NOT get Statement; Addressee does NOT get Statement but will get backup for the Cost Collector if there were charges.

VARCHAR2

1

MASTER_PROJECT_NAME

Name (Project Title) of the top level (level 1) WBS Id in a WBS Hierarchy structure. Select this field if a roll-up of the activity on an entire WBS Hierarchy is desired. Colloquially known as the Parent in a Parent-Child hierarchy.

VARCHAR2

40

MASTER_PROJECT_NUMBER

WBS Id of the top level WBS Id (level 1) in a WBS Hierarchy structure. Select this field if a roll-up of the activity on an entire WBS Hierarchy is desired. Colloquially known as the Parent in a Parent-Child hierarchy.

VARCHAR2

2

OH_ADJUSTMENT

Description associated with OH Adjustment Key.

VARCHAR2

30

OH_ADJUSTMENT_KEY

Code associated with a Cost Collector which points to an overhead rate which will be applied to this Cost Collector.

VARCHAR2

6

ON_OFF_CAMPUS_STATUS

Indicates where research is performed: On Campus or Off Campus

VARCHAR2

10

ORGANIZATION_ID

The department, for example, Accounting.

VARCHAR2

6

ORGANIZATION_NAME

The name of the MIT Department or Unit to which the project is assigned, usually the home unit or the Principal Investigator or Co-Investigator.

VARCHAR2

40

OVERRUN_FLAG

Flag indicating whether cost collector is overrun. Values: Y and N.

VARCHAR2

1

PENDING_CODE

A "P" indicates that the cost object is in pending status

VARCHAR2

1

PLANNING

XXX

VARCHAR2

30

PLANNING_KEY

XXX

VARCHAR2

6

POOL_CODE

Indicates which investment pool the funds should placed in.

VARCHAR2

1

POOL_UNIT

The number of pool units; supplied by the Treasurer's Office.

VARCHAR2

10

PRIME_SPONSOR_ADMIN_ACTIVITY

This field is no longer maintained by OSP and should not be used in queries.

VARCHAR2

30

PRIME_SPONSOR_CODE

Primary Sponsor code. OSP accounts are coded. Fund accounts are coded if there are restrictions in the award that make accounting system controls desirable. Contracts or grants from federal government and other restrictive sponsors are coded.

VARCHAR2

5

PRIME_SPONSOR_COUNTRY

Country of the primary sponsor.

VARCHAR2

40

PRIME_SPONSOR_KEY

XXX

VARCHAR2

10

PRIME_SPONSOR_NAME

The name used to identify a primary sponsor associated with a particular sponsor code.

VARCHAR2

60

PRIME_SPONSOR_STATE

State of the primary sponsor.

VARCHAR2

30

PRIME_SPONSOR_TYPE

Type of primary sponsor (Example: Federal, Private - Pofit, Private - Non-Profit, State, etc..)

VARCHAR2

100

PROFIT_CENTER_ID

A unique key that identifies the profit center.

VARCHAR2

7

PROFIT_CENTER_KEY

Key used to join the Profit Center to the financial transaction, and to get the master data that were current at the time of the transaction. This field should only be used for joining data, NOT for reporting.

VARCHAR2

11

PROFIT_CENTER_NAME

The text name of the profit center.

VARCHAR2

40

PROJECT_WBS_COUNTER

Used to count records withing a group on a report. Always contains a value of 1.

NUMBER

1

PROJECT_WBS_CREATE_DATE

The date the sales document was created.

DATE

8

PROJECT_WBS_CREATE_USER_ID

The name of the user who created the sales document.

VARCHAR2

12

PROJECT_WBS_EFFECTIVE_DATE

Begin date of the work breakdown structure for this project.

DATE

8

PROJECT_WBS_EXPIRATION_DATE

End date of the work breakdown structure for this project.

DATE

8

PROJECT_WBS_ID

Project work breakdown structure element identifier.

VARCHAR2

7

PROJECT_WBS_NAME

Work breakdown structure name.

VARCHAR2

40

RECORD_TYPE

This field can contain different values in different tables. In the Summary Statement Detail table, it contains either "Posted" or "Calculated", referring to the nature of the record. In the Estimated Surcharges table, field values are "ACT"(actual) or "EST" (estimated).

VARCHAR2

3

SECTION_CODE

A code which classifies cost collectors into sections for financial statement reporting purposes

VARCHAR2

3

SECTION_NAME

Description associated with the Section Code.

VARCHAR2

60

SOURCE_OF_FUNDING_FY_KEY

XXX

VARCHAR2

14

SOURCE_OF_FUNDING_KEY

XXX

VARCHAR2

10

SPONSOR_ADMIN_ACTIVITY

This field is no longer maintained by OSP and should not be used in queries.

VARCHAR2

30

SPONSOR_CODE

Sponsor code. OSP accounts are coded. Fund accounts are coded if there are restrictions in the award that make accounting system controls desirable. Contracts or grants from federal government and other restrictive sponsors are coded.

VARCHAR2

6

SPONSOR_COUNTRY

Country where sponsor is located.

VARCHAR2

40

SPONSOR_FY_KEY

XXX

VARCHAR2

14

SPONSOR_KEY

Key identifying an entry in the Sponsor master table. This field should only be used for linking tables together, not for reporting.

VARCHAR2

10

SPONSOR_NAME

In the PROJECT and SPONSOR tables, identifies the sponsor, associated with a particular sponsor code, providing funding for a sponsored research project. In the personnel APPOINTMENT tables, identifies the sponsor providing financial support for a Fellow; this field is not currently populated by Human Resources.

VARCHAR2

60

SPONSOR_STATE

State where sponsor is located.

VARCHAR2

30

SPONSOR_TYPE

The type of sponsor (Example: Federal, Private - Pofit, Private - Non-Profit, State, etc..)

VARCHAR2

100

SUB_PLAN

"Y" = subplan "N" = Not a subplan

VARCHAR2

1

SUPERVISOR

The name of the person responsible for this project, cost center, internal order, or cost center.

VARCHAR2

30

SUPERVISOR_MIT_ID

The 9 digit MIT identification number of the person responsible for this project, cost center, internal order, or cost center.

VARCHAR2

9

SUPERVISOR_ROOM

The building and room number assigned to the person responsible for this project, center, internal order, or cost center.

VARCHAR2

10

TERM_CODE

** For FINANCIAL DATA: the Term Code identifies the status of the cost collector. Blank - open for charges between start and end dates. Term Code = 1 - Closed. Pre-end date charges allowed. Term Code = 2 - No longer in use. Term Code = 3 - Terminated. Charges not allowed. ** For STUDENT DATA: the Term Code identifies one of the terms in an academic year; for example: 1999FA==>Fall Term of the 1998-99 academic year, and 1999SP==>Spring Term of the 1998-99 academic year.

VARCHAR2

6

TERM_CODE_DESCRIPTION

Description of the academic term.

VARCHAR2

40

TREASURER_REPORT

Not in use in SAP.

VARCHAR2

3

WAREHOUSE_LOAD_DATE

The date the record was loaded into the Data Warehouse from the source system.

DATE

8

WBS_ELEMENT_CONTINUED_FROM

The Cost Collector ID of this project's predecessor project (usually WBS).

VARCHAR2

7

WBS_ELEMENT_CONTINUED_TO

The Cost Collector ID of this project's sucessor or continuation cost collector.

VARCHAR2

7

WBS_ELEMENT_PARENT_ID

The Cost Collector ID of the "parent" of this Cost Collector. Normally found in WBS Elements, it is usually the next level up in the WBS Hierarchy Structure.

VARCHAR2

7

WBS_TYPE

XXX

VARCHAR2

1