Travel & Expense Management Data Collection

The Travel & Expense Management Data Collection is a subset of the University Data Warehouse containing data from Concur Expense, Penn's automated expense management system (in use since Spring 2013). The Travel & Expense Management collection contains current and historic information about faculty, staff and students' expense reports and related details.

For a brief overview of the collection, see Travel & Expense Management Data Collection Quick Reference Guide.

Tables and Data Elements

Tables are listed in alphabetical order. Select Table to obtain the explantion, common uses, primary keys, indexed data elements, and related tables for a table. Select Elements to obtain the definition, list of values, formats, and not null values for each data element in the table. A description of table and data element terminology is in the document What the Warehouse Contains.

Looking for a specific element or caution? Try the searching the collection documentation for particular keywords (search page will open in a new tab).

CT_ALLOCATION
Table   Elements		 CT_DELEGATION
Table   Elements		 CT_POLICY_HIST
Table   Elements
CT_ATTENDEE
Table   Elements		 CT_EXCEPTION
Table   Elements		 CT_POLICY_LANG_HIST
Table   Elements
CT_ATTENDEE_TYPE
Table   Elements		 CT_EXPENSE_CODE
Table   Elements		 CT_REPORT
Table   Elements
CT_AUTHORIZED_APPROVER
Table   Elements		 CT_EXPENSE_COMMENT
Table   Elements		 CT_REPORT_ENTRY
Table   Elements
CT_CAR_LOG_ENTRY
Table   Elements		 CT_EXPENSE_TYPE
Table   Elements		 CT_REPORT_EXCEPTION
Table   Elements
CT_CASH_ADVANCE
Table   Elements		 CT_JOURNAL
Table   Elements		 CT_REPORT_STATUS_AUDIT
Table   Elements
CT_CASH_ADVANCE_COMMENT
Table   Elements		 CT_LIST
Table   Elements		 CT_ROLE
Table   Elements
CT_CASH_ADVANCE_ENTRY
Table   Elements		 CT_LIST_CATEGORY
Table   Elements		 CT_STATUS
Table   Elements
CT_CC_TRANS_CAR_RENTAL
Table   Elements		 CT_LIST_ITEM
Table   Elements		 CT_TA_DAY
Table   Elements
CT_CC_TRANS_LODGING
Table   Elements		 CT_LIST_ITEM_MAP
Table   Elements		 CT_TA_FIXED
Table   Elements
CT_CC_TRANS_PURCHASE
Table   Elements		 CT_LOCATION
Table   Elements		 CT_TA_ITINERARY
Table   Elements
CT_CC_TRANS_TRAVEL
Table   Elements		 CT_MERCHANT_CODE
Table   Elements		 CT_TA_ITINERARY_ROW
Table   Elements
CT_CC_TRANS_TRAVEL_DTL
Table   Elements		 CT_PAYMENT_CODE
Table   Elements		 CT_TA_RATE
Table   Elements
CT_COUNTRY
Table   Elements		 CT_PAYMENT_TYPE
Table   Elements		 CT_WORKFLOW_PROCESS
Table   Elements
CT_CREDIT_CARD_ACCOUNT
Table   Elements		 CT_PERSON
Table   Elements		 CT_WORKFLOW_PROCESS_INSTANCE
Table   Elements
CT_CREDIT_CARD_TRANSACTION
Table   Elements		 CT_PERSON_ROLE_MAP
Table   Elements		 CT_WORKFLOW_STEP
Table   Elements
CT_CTRY_CURRENCY_MAP
Table   Elements		 CT_POL_TR_EXP_CAT_MAP
Table   Elements		 CT_WORKFLOW_STEP_INSTANCE
Table   Elements
CT_CTRY_SUBDIVISION
Table   Elements		 CT_POLICY
Table   Elements		 CT_WORKFLOW_SUBJECT
Table   Elements
CT_CURRENCY
Table   Elements		 CT_POLICY_EXPENSE_TYPE_MAP
Table   Elements		 TEM_SAE_DETAIL
Table   Elements
Related GL reference tables included in the Travel & Expense universe:
CENTER_REF_CODES
Table   Element		 CNAC_CODES
Table   Element		 FUND_CODES
Table   Element
OBJECT_CODES
Table   Element		 ORG_CODES
Table   Element		 PROGRAM_CODES
Table   Element
Related Salary Management tables included in the Travel & Expense universe:
COMP_PERSON
Table   Element		 EMPLOYEE_GENERAL
Table   Element		 JOB_CLASS_GENERAL
Table   Element

The Travel & Expense Management collection is refreshed nightly every day, following the completion of Concur Expense batch processing. Due to the timing of the file transfer from Concur and subsequent processing, the data in the Warehouse is likely to be a full day behind the information available in the Concur Expense transactional system.

Source of the Data
The data in the Data Warehouse that is accessible via the BusinessObjects Travel and Expense Management universe comes from Concur Expense, Penn's automated expense management system. The universes also join to related tables in the General Ledger and Salary Management data collections.

Time Span Covered
The Data Warehouse stores current information on travel and expense and related details. The data goes as far back as Spring 2013, when Concur Expense was implemented, and continues from that point.

The Concur Expense detail data available in the Travel & Expense Management collection is described in this diagram, which illustrates the relationships between the tables in the Business Objects universe. In addition, access to the TEM_SAE_DETAIL data is provided via the same Travel and Expense Management universe, but the Concur detail and SAE data are not joined together.

The BusinessObjects repository contains reports which users can use to retrieve data to which they have access. The reports available vary by data collection and business use, as listed below. You will only be able to view or use reports in the public folders that you have access to. Documents should be renamed and saved to your own "My Favorites" folder on the portal if you need to edit them using Webi. Documents can only be edited in Webi when placed in your "My Favorites" folder.

International Travel: Displays Concur expense report detail for reports associated with travels outside the United States.

TEM Report Inquiry: Displays SAE and Concur expense report detail and GL journal detail for reports in a given processed date and ORG range.

TEM Report Inquiry - Unprocessed reports only: Displays Concur expense report detail for unprocessed reports in a given ORG range. Includes an optional prompt for report name/ID.

TEM Report Processing Flow Review: Displays Concur expense detail report header, comments and workflow information for reports for a given number of days between submission and processing for payment dates.

TEM workflow review: Enter Report Key(s) in response to the prompt to view the full workflow for a TEM report.

Unapplied Cash Advances: Display names and amounts, by person, of cash advances which have not yet been applied to expense reports. Travel begin/end dates are provided where available.

Unassigned and Unsubmitted Credit Card Charges: Displays Concur credit card and expense report detail to facilitate review of charges which have not yet been applied to expense reports, and also charges applied to reports which have not yet been processed (based on charge transaction date range).

Exception reports

Exception reports include multiple tabs to review basic report or report entry information, comments entered, and processing workflow. Reports are available to highlight the following processing exceptions:

  • 1STCLASS: 1st Class/Business Class ticket purchase or the use of Sedan/Limo service requires Supplemental Approval by the Head of the S/C or designated representative
  • AVGCOST: The Average Cost Per Attendee is Greater Than $80.00
  • DAYSOVER: Transaction Dates greater than 182 days require Supplemental Approval by the Head of the S/C or designated representative
  • DINMAX or LUNMAX: Dinner must be $80 or less; or Lunch must be $35 or less
  • HOTELNO: Personal meals are defined as meal expenses incurred when traveling on an out-of-town business trip, which would typically include an overnight stay or extended work day as a result of the travel
  • MRAFLAG: Expense reports that tripped the MRAFLAG warning in Concur (Missing Receipt Affidavit Present.)
  • PENNDUP: This expense entry maybe a duplicate
  • STUDENT: The student/trainee will include in the Business Justification field full detail of scope of assignment including location, dates, activity and provide the name of the specific school/ faculty member/ University organization supporting this assignment. The student/trainee will promote the business objectives of the University and/or consult with and advise the University on matters and/or perform representation relating to the provided information.
  • TA1000: Travel Advance requests greater than $1,000 require the Supplemental Approver to be the Head of the S/C or designated representative
  • TA90DAYS: Travel Advance requests with a trip duration greater than 90 days requires the Supplemental Approver to be the Head of the S/C or designated representative.

Data training for this collection is offered on an ad hoc basis -- please contact Enterprise Information & Analytics for assistance with specific training questions. You can also refer to the table and element documentation for information and cautions about specific data.

In general, individuals should have access to the General Ledger data collection in order to obtain automatic access to the Travel & Expense Management data collection for the same authorized organizations. If a user does not currently have General Ledger collection access, they must complete the Travel & Expense Management Data Access eForm, and obtain supervisor, school access administrator (or senior business officer), and data steward approval.

Releasing Data Outside the University 
The only two offices that may disclose travel expense data outside the University are Penn Travel Services and Financial Systems. If you do not work for those offices and you receive a request for data to be sent outside the University, refer the requester to Financial Systems.

Releasing Data Within the University
Within the University, travel expense data may be disclosed only if it is needed to do the business of the University, and only to those who need to know the information in order to do their jobs. If you are not sure whether to fulfill a request for this information, call Financial Systems.

Query Results
If you save your query results in Excel, pdf, or any other local file type, you must see to it that any sensitive data stored on your peronal computer is safeguarded through physical security, access control software, or encryption. When a computer is left signed to an account, it is easy for someone to gain unauthorized access. Either sign off from your account before you leave your computer or restrict access by some other means (physical security or access control software).

For more information on security and privacy, contact the Office of Information Security.

If you have a question about the Travel & Expense Management data, or if you notice some documentation that needs updating, please contact Enterprise Information & Analytics. For documentation published on the Web, please specify the URL for the documentation that needs to be updated.