Version 10.2.17

New tables in Expense Management 10.2.17

Modifications to tables in 10.2.17

These tables are modified in Expense Management 10.2.17. Newly added columns are highlighted.

CREATE TABLE ALLOCATION (
LINE_ITEM_ID	int
LINE_ITEM_ID_TYPE	int
PERCENT_ALLOTTED	float
PAID_IN_AMOUNT_CURRENCY	nchar(3)
PAID_IN_AMOUNT	numeric(28,10)
TRANSACTION_AMOUNT_CURRENCY	nchar(3)
TRANSACTION_AMOUNT	numeric(28,10)
IS_APPROVED	smallint
COST_CENTER_ID	int
PROJECT_NUMBER_ID	int
IS_BILLABLE	smallint
CHANGE_TIMESTAMP	datetime
ALLOCATION_DATE	datetime
ALLOCATION_ID	int	Not Null
CONSTRAINT PK555 PRIMARY KEY CLUSTERED ( ALLOCATION_ID )
)

CREATE TABLE CREDIT_CARD_TRANSACTION (
CREDIT_CARD_TRANSACTION_I	int	NOT NULL
CREDIT_CARD_ID	int
MERCHANT_NAME	nvarchar(255)
MERCHANT_CITY	nvarchar(255)
MERCHANT_STATE	nvarchar(255)
MERCHANT_COUNTRY	nvarchar(255)
MERCHANT_GIVEN_SIC_CODE	nchar(20)
MERCHANT_STD_SIC_CODE	nchar(20)
MERCHANT_VAT_NUMBER	nchar(20)
VENDOR_NAME	nvarchar(255)
TRANSACTION_REFERENCE	nvarchar(255)
TRANSACTION_TYPE	smallint
DUE_CC_CO_AMOUNT	numeric(28,10)
DUE_CC_CO_AMOUNT_CURRENCY	nchar(3)
POSTING_DATE	datetime
TRANSACTION_DATE	datetime
VAT_AMOUNT	numeric(28,10)
VAT_AMOUNT_CURRENCY	nchar(3)
TRANSACTION_AMOUNT	numeric(28,10)
TRANSACTION_AMOUNT_CURRENCY	nchar(3)
TRANSACTION_STATUS	int
IMPORT_DATE	datetime
DOCUMENT_ID	int
AIRLINE_DEPARTURE_DATE	datetime
AIRLINE_ROUTING	nvarchar(255)
AIRLINE_CLASS_OF_SERVICE	nvarchar(20)
AIRLINE_TRAVEL_AGENCY	nvarchar(45)
AIRLINE_TICKET_NUMBER	nvarchar(20)
CAR_RENTAL_AGREEMENT_NUMBER	nvarchar(30)
HOTEL_ARRIVAL_DATE	datetime
HOTEL_DEPARTURE_DATE	datetime
HOTEL_STAY_DURATION	int
HOTEL_FOLIO_REF_NUMBER	nvarchar(30)
HOTEL_ROOM_TYPE	nvarchar(30)
HOTEL_ROOM_AMOUNT	numeric(28,10)
HOTEL_CURRENCY	nchar(3)
HOTEL_ROOM_TAX_1	numeric(28,10)
HOTEL_ROOM_TAX_2	numeric(28,10)
HOTEL_PHONE_AMOUNT	numeric(28,10)
HOTEL_PHONE_TAX_1	numeric(28,10)
HOTEL_PHONE_TAX_2	numeric(28,10)
HOTEL_RESTAURANT_AMOUNT	numeric(28,10)
HOTEL_RESTAURANT_TAX_1	numeric(28,10)
HOTEL_RESTAURANT_TAX_2	numeric(28,10)
HOTEL_ROOM_SERVICE_AMOUNT	numeric(28,10)
HOTEL_ROOM_SERVICE_TAX_1	numeric(28,10)
HOTEL_ROOM_SERVICE_TAX_2	numeric(28,10)
HOTEL_TIP_AMOUNT_1	numeric(28,10)
HOTEL_TIP_AMOUNT_2	numeric(28,10)
HOTEL_OTHERS_AMOUNT	numeric(28,10)
HOTEL_OTHERS_TAX_1	numeric(28,10)
HOTEL_OTHERS_TAX_2	numeric(28,10)
HOTEL_ROOM_RATE	numeric(28,10)
HOTEL_ROOM_TAX_DAILY	numeric(28,10)
HOTEL_LAUNDRY_AMOUNT	numeric(28,10)
HOTEL_GIFT_SHOP_AMOUNT	numeric(28,10)
HOTEL_MOVIE_AMOUNT	numeric(28,10)
HOTEL_BUS_CENTER_AMOUNT	numeric(28,10)
HOTEL_HEALTH_CLUB_AMOUNT	numeric(28,10)
HOTEL_TOTAL_TAX	numeric(28,10)
ORIGIN_AIRPORT	nvarchar(10)
DESTINATION_AIRPORT	nvarchar(10)
CHANGE_TIMESTAMP	datetime
TRANSACTION_DESCRIPTION	nvarchar(255)
TRANSACTION_STATUS_INT	int
TRANSACTION_SEQUENCE_NUM	int
TRANSACTION_STATEMENT_DATE	datetime
PRIMARY_ISO_CURRENCY_CODE	nchar(3)
MARKET_CODE	nvarchar(50)
GLOBAL_CLIENT_ORIGIN_ID	nvarchar(50)
LOCAL_COUNTRY_ISO_CODE	nchar(3)
TRANSACTION_TYPE_CODE	nvarchar(10)
TAX1	numeric(28,10)
TAX1_CURRENCY	nchar(3)
TAX2	numeric(28,10)
TAX2_CURRENCY	nchar(3)
TAX1_INCLUDED	smallint
TAX2_INCLUDED	smallint
MERCHANT_ADDRESS	nvarchar(255)
CONSTRAINT PK30 PRIMARY KEY CLUSTERED ( CREDIT_CARD_TRANSACTION_ID )
)

CREATE TABLE EXPENSE_REPORT (
DOCUMENT_ID	int	NOT NULL
TRACKING_NUMBER	nvarchar(255)	NOT NULL
DOCUMENT_STATUS	nvarchar(255)
CURRENT_ACTIVITY_ID	int	NOT NULL
ENTERED_CURRENT_ACTIVITY_DATE	datetime	NOT NULL
ACTIVITY_OWNER_ID	int
HAS_EXCEPTIONS	smallint
HAS_OVERRIDES	smallint
CREATE_DATE	datetime
CREATOR_USER_ID	int
OWNER_USER_ID	int
AUDIT_INDICATOR	nvarchar(255)
RECEIPTS_RECEIVED_DATE	datetime
TITLE	nvarchar(255)
CASH_ADVANCE_AMT	numeric(28,10)
CASH_ADVANCE_AMT_CURRENCY	nchar(3)
COMPANY_CHARGES_AMT	numeric(28,10)
COMPANY_CHARGES_AMT_CURRENCY	nchar(3)
DUE_EMPLOYEE_AMT	numeric(28,10)
DUE_EMPLOYEE_AMT_CURRENCY	nchar(3)
DUE_COMPANY_AMT	numeric(28,10)
DUE_COMPANY_AMT_CURRENCY	nchar(3)
TOTAL_AUTHORIZED_AMT	numeric(28,10)
TOTAL_AUTHORIZED_AMT_CURRENCY	nchar(3)
TOTAL_EXPENSED_AMT	numeric(28,10)
TOTAL_EXPENSED_AMT_CURRENCY	nchar(3)
SUBMITTED_DATE	datetime
APPROVED_DATE	datetime
EXPENSE_FROM_DATE	datetime
EXPENSE_TO_DATE	datetime
IS_APPROVED	smallint
PAID_AMT	numeric(28,10)
PAID_AMT_CURRENCY	nchar(3)
PAID_DATE	datetime
PAYMENT_NUMBER	nvarchar(255)
PURPOSE_TEXT	nvarchar(255)
PURPOSE_CREATOR_ID	int
CORP_CHARGE_TOTAL_AMT	numeric(28,10)
CORP_CHARGE_TOTAL_AMT_CURRENCY	nchar(3)
CORP_CHARGE_PAID_AMT	numeric(28,10)
CORP_CHARGE_PAID_AMT_CURRENCY	nchar(3)
CORP_CHARGE_PAID_DATE	datetime
COMP_PAYS_CARDS_AMT	numeric(28,10)
COMP_PAYS_CARDS_AMT_CURRENCY	nchar(3)
CHANGE_TIMESTAMP	datetime
CREATE_LOCATION_ID	int
FLEX_FIELD_STRING_1	nvarchar(255)
FLEX_FIELD_STRING_2	nvarchar(255)
FLEX_FIELD_STRING_3	nvarchar(255)
FLEX_FIELD_STRING_4	nvarchar(255)
FLEX_FIELD_INTEGER_1	int
FLEX_FIELD_INTEGER_2	int
FLEX_FIELD_DATE_1	datetime
FLEX_FIELD_DATE_2	datetime
FLEX_FIELD_AMOUNT_1	numeric(28,10)
FLEX_FIELD_AMOUNT_1_CURRENCY	nchar(3)
FLEX_FIELD_AMOUNT_2	numeric(28,10)
FLEX_FIELD_AMOUNT_2_CURRENCY	nchar(3)
FLEX_FIELD_LOCATION_1_ID	int
FLEX_FIELD_DDLB_1_CODE	nvarchar(255)
FLEX_FIELD_DDLB_2_CODE	nvarchar(255)
FLEX_FIELD_DDLB_3_CODE	nvarchar(255)
FLEX_FIELD_DDLB_4_CODE	nvarchar(255)
FLEX_FIELD_DDLB_5_CODE	nvarchar(255)
APPLIED_LCF	numeric(28,10)
APPLIED_LCF_CURRENCY	nchar(3)
REMAINING_LCF	numeric(28,10)
REMAINING_LCF_CURRENCY	nchar(3)
GENERATED_LCF	numeric(28,10)
GENERATED_LCF_CURRENCY	nchar(3)
CREATOR_CLIENT_TYPE	int
FLEX_FIELD_DDLB_1_VALUE	nvarchar(255)
FLEX_FIELD_DDLB_2_VALUE	nvarchar(255)
FLEX_FIELD_DDLB_3_VALUE	nvarchar(255)
FLEX_FIELD_DDLB_4_VALUE	nvarchar(255)
FLEX_FIELD_DDLB_5_VALUE	nvarchar(255)
CONSTRAINT PK6 PRIMARY KEY CLUSTERED ( DOCUMENT_ID )
)

CREATE TABLE EXPENSE_REPORT_LINE_ITEM (
LINE_ITEM_ID	int	NOT NULL
DOCUMENT_ID	int	NOT NULL
PAYMENT_TYPE_ID	int
EXPENSE_TYPE_ID	int
VENDOR_NAME	nvarchar(255)
VENDOR_GIVEN_SIC_CODE	nvarchar(255)
VENDOR_CHAIN_CODE	nchar(20)
VENDOR_CREATOR_USER_ID	int
CREDIT_CARD_TRANSACTION_ID	int
LINE_ITEM_DATE	datetime
DESCRIPTION	nvarchar(255)
TRANSACTION_AMT	numeric(28,10)
TRANSACTION_AMT_CURRENCY	nchar(3)
PAID_IN_AMT	numeric(28,10)
PAID_IN_AMT_CURRENCY	nchar(3)
EXCHANGE_RATE	numeric(28,10)
PURPOSE	nvarchar(255)
PURPOSE_CREATOR_ID	int
MILES	numeric(15,5)
MILEAGE_RATE	float
GL_CODE	nchar(20)
APPROVED_AMT	numeric(28,10)
APPROVED_AMT_CURRENCY	nchar(3)
IS_OVERRIDDEN	smallint
HAS_RECEIPT	smallint
HAVE_RECEIPTS_BEEN_CHECKED	smallint
IS_APPROVED	smallint
LOCATION_ID	int
IS_PERSONAL	smallint
APPROVED_EXCHANGE_RATE	numeric(28,10)
DEPARTURE_DATE	datetime
ORIGIN_AIRPORT	nvarchar(10)
DESTINATION_AIRPORT	nvarchar(10)
BOOKING_AGENT_NAME	nvarchar(50)
TICKET_STATUS	nvarchar(255)
TICKET_NUMBER	nvarchar(50)
CAR_RENTAL_AGREEMENT	nvarchar(50)
AIRLINE_CLASS_OF_SERVICE_CODE	nvarchar(4)
AIRLINE_CLASS_OF_SERVICE	nvarchar(255)
CAR_CLASS_OF_SERVICE_CODE	nvarchar(4)
CAR_CLASS_OF_SERVICE	nvarchar(255)
HOTEL_CLASS_OF_SERVICE_CODE	nvarchar(4)
HOTEL_CLASS_OF_SERVICE	nvarchar(255)
BREAKFAST_DEDUCTION	smallint
LUNCH_DEDUCTION	smallint
DINNER_DEDUCTION	smallint
FIRST_DAY_DEDUCTION	smallint
LAST_DAY_DEDUCTION	smallint
OVERNIGHT_STAY_DEDUCTION	smallint
LONG_TERM_STAY_DEDUCTION	smallint
NUMBER_OF_GUESTS	int
ODOMETER_START	numeric(15,5)
ODOMETER_END	numeric(15,5)
PERDIEM_AMOUNT	numeric(28,10)
PERDIEM_AMOUNT_CURRENCY	nchar(3)
CHANGE_TIMESTAMP	datetime
ITEMIZATION_PARENT_ID	int
ITEMIZATION_STATE	smallint	NOT NULL
MANUAL_FOLIO_ITEMIZATION	smallint
NUMBER_OF_PASSENGERS	int
FLEX_FIELD_STRING_1	nvarchar(255)
FLEX_FIELD_STRING_2	nvarchar(255)
FLEX_FIELD_STRING_3	nvarchar(255)
FLEX_FIELD_STRING_4	nvarchar(255)
FLEX_FIELD_INTEGER_1	int
FLEX_FIELD_INTEGER_2	int
FLEX_FIELD_DATE_1	datetime
FLEX_FIELD_DATE_2	datetime
FLEX_FIELD_AMOUNT_1	numeric(28,10)
FLEX_FIELD_AMOUNT_1_CURRENCY	nchar(3)
FLEX_FIELD_AMOUNT_2	numeric(28,10)
FLEX_FIELD_AMOUNT_2_CURRENCY	nchar(3)
FLEX_FIELD_LOCATION_1_ID	int
FLEX_FIELD_DDLB_1_CODE	nvarchar(255)
FLEX_FIELD_DDLB_2_CODE	nvarchar(255)
FLEX_FIELD_DDLB_3_CODE	nvarchar(255)
FLEX_FIELD_DDLB_4_CODE	nvarchar(255)
FLEX_FIELD_DDLB_5_CODE	nvarchar(255)
OPTIONAL_DEDUCTION_1	nvarchar(255)
OPTIONAL_DEDUCTION_2	nvarchar(255)
OPTIONAL_DEDUCTION_3	nvarchar(255)
OPTIONAL_DEDUCTION_4	nvarchar(255)
OPTIONAL_DEDUCTION_5	nvarchar(255)
RECURRING_ID	int
RECURRING_COUNT	int
MILEAGE_ROUTE_ID	int
GUEST_NO_SHOW_COUNT	int
PASSENGER_NAME	nvarchar(255)
AIRLINE_DOMESTIC_OR_INTL	int
COMMUTE_MILES	numeric(26,16)
CREATOR_CLIENT_TYPE	int
MILEAGE_RATE_DESCRIPTION	nvarchar(40)
END_DATE	datetime
EXPORT_STATUS	int
VOID_STATUS	int
REVERSAL_LINE_ITEM_ID	int
FLEX_FIELD_DDLB_1_VALUE	nvarchar(255)
FLEX_FIELD_DDLB_2_VALUE	nvarchar(255)
FLEX_FIELD_DDLB_3_VALUE	nvarchar(255)
FLEX_FIELD_DDLB_4_VALUE	nvarchar(255)
FLEX_FIELD_DDLB_5_VALUE	nvarchar(255)
CONSTRAINT PK7 PRIMARY KEY CLUSTERED ( LINE_ITEM_ID )
)

CREATE TABLE INVOICE_LINE_ITEM (
LINE_ITEM_ID	int	NOT NULL
DOCUMENT_ID	int	NOT NULL
ITEM_NUMBER	nvarchar(255)
ITEM_DESCRIPTION	nvarchar(255)
INVOICED_QUANTITY	numeric(15,5)
UNIT_OF_MEASURE_CODE	nvarchar(255)
UNIT_OF_MEASURE	nvarchar(255)
UNIT_OF_MEASURE_CREATOR_ID	int
UNIT_PRICE	numeric(28,10)
UNIT_PRICE_CURRENCY	nchar(3)
TOTAL_PRICE	numeric(28,10)
TOTAL_PRICE_CURRENCY	nchar(3)
CHECK_REQUEST_TYPE_ID	int
CHANGE_TIMESTAMP	datetime
NUMBER_OF_GUESTS	int
GUEST_NO_SHOW_COUNT	int
INVOICE_DATE	datetime
CREATOR_CLIENT_TYPE	int
EXPORT_STATUS	int
VOID_STATUS	int
REVERSAL_LINE_ITEM_ID	int
CONSTRAINT PK11 PRIMARY KEY CLUSTERED ( LINE_ITEM_ID )
)

CREATE TABLE TRAVEL_LINE_ITEM (
LINE_ITEM_ID	int	NOT NULL
DOCUMENT_ID	int	NOT NULL
TRAVEL_EXPENSE_TYPE_ID	int	NOT NULL
COST_AMOUNT_CURRENCY	nchar(3)
COST_AMOUNT	numeric(28,10)
TAX_AMOUNT_CURRENCY	nchar(3)
TAX_AMOUNT	numeric(28,10)
PURPOSE	nvarchar(255)
PURPOSE_USER_ID	int
VENDOR_NAME	nvarchar(255)
VENDOR_USER_ID	int
FROM_LOCATION_ID	int
FROM_DATE	datetime
FROM_TIME	datetime
TO_LOCATION_ID	int
TO_DATE	datetime
TO_TIME	datetime
CLASS_OF_SERVICE	nvarchar(255)
DAILY_RATE_AMOUNT_CURRENCY	nchar(3)
DAILY_RATE_AMOUNT	numeric(28,10)
CUST_DEF_1	nvarchar(255)
CUST_DEF_2	nvarchar(255)
CUST_DEF_3	nvarchar(255)
DAILY_RATE_NATIVE_AMOUNT_CURR	nchar(3)
DAILY_RATE_NATIVE_AMOUNT	numeric(28,10)
EXCHANGE_RATE	numeric(28,10)
MILES	numeric(15,5)
REIMBURSE_RATE	float
AVERAGE_COST_AMOUNT_CURRENCY	nchar(3)
AVERAGE_COST_AMOUNT	numeric(28,10)
IS_IMPORTED	smallint
DESCRIPTION1	nvarchar(255)
DESCRIPTION2	nvarchar(255)
EXT_BOOKING_KEY	nvarchar(255)
EXCH_RATE_DEFAULT	numeric(28,10)
TRIP_TYPE	nchar(2)
FROM_AIRPORT_CODE	nchar(3)
TO_AIRPORT_CODE	nchar(3)
LOWEST_AIRFARE_AMT	numeric(28,10)
LOWEST_AIRFARE_AMT_CURRENCY	nchar(3)
LOWEST_HOTEL_RATE_AMT	numeric(28,10)
LOWEST_HOTEL_RATE_AMT_CURRENCY	nchar(3)
BOOKING_SOURCE	nchar(2)
AGENCY_NAME	nvarchar(255)
NUMBER_OF_GUESTS	int
CHANGE_TIMESTAMP	datetime
GUEST_NO_SHOW_COUNT	int
CREATOR_CLIENT_TYPE	int
EXPORT_STATUS	int
VOID_STATUS	int
REVERSAL_LINE_ITEM_ID	int
CONSTRAINT PK22 PRIMARY KEY CLUSTERED ( LINE_ITEM_ID )
)

CREATE TABLE TIMESHEET_LINE_ITEM (
LINE_ITEM_ID	int	NOT NULL
DOCUMENT_ID	int
IS_ATTENDANCE_LINE_ITEM	smallint	NOT NULL
HOURS	numeric(14,4)
WORK_DATE	datetime
START_TIME	datetime
END_TIME	datetime
COST_CENTER_ID	int
PROJECT_NUMBER_ID	int
PROJECT_ACTIVITY_CODE	nvarchar(255)
PROJECT_ACTIVITY_NAME	nvarchar(255)
APPROVED_HOURS	numeric(14,4)
HOURLY_CHARGE_TYPE	nchar(40)
CHARGE_TYPE	nvarchar(255)
LOCATION_ID	int
CHARGE_ROLE	nvarchar(255)
APPROVED_HOURLY_CHARGE_TYPE	nchar(40)
BILLABLE_RATE	numeric(28,10)
BILLABLE_RATE_CURRENCY	nchar(3)
DESCRIPTION	nvarchar(255)
ADJUSTED_HOURS	numeric(7,2)
CHANGE_TIMESTAMP	datetime
ATTD_IN1	datetime
ATTD_OUT1	datetime
ATTD_IN2	datetime
ATTD_OUT2	datetime
	datetime
ATTD_OUT3	datetime
ATTD_IN4	datetime
ATTD_OUT4	datetime
ATTD_IN5	datetime
ATTD_OUT5	datetime
ATTD_IN6	datetime
ATTD_OUT6	datetime
CREATOR_CLIENT_TYPE	int
EXPORT_STATUS	int
VOID_STATUS	int
REVERSAL_LINE_ITEM_ID	int
CONSTRAINT PK27 PRIMARY KEY CLUSTERED ( LINE_ITEM_ID )
)

CREATE TABLE CUMULATIVE_MILES (
CUMULATIVE_MILEAGE_ID	int	NOT NULL
USER_ID	int
PERIOD_START_DATE	datetime
PERIOD_END_DATE	datetime
CUMULATIVE_MILES	numeric(15,5)
CHANGE_TIMESTAMP	datetime
CONSTRAINT PK115_1 PRIMARY KEY CLUSTERED ( CUMULATIVE_MILEAGE_ID )
)

CREATE TABLE ORGANIZATION_PERMISSION (
ORGANIZATION_PERMISSION_ID	int	NOT NULL
REPORTING_USER_ID	int	NOT NULL
REPORTING_USER_LOGIN	nvarchar(255)
ORGANIZATION_ID	int
ORGANIZATION_ID_TYPE	int
CHANGE_TIMESTAMP	datetime
CONSTRAINT PK112_1 PRIMARY KEY CLUSTERED ( ORGANIZATION_PERMISSION_ID )
)

CREATE TABLE PROJECT_NUMBER_PERMISSION (
PROJECT_NUMBER_PERMISSION_ID	int	NOT NULL
REPORTING_USER_ID	int	NOT NULL
REPORTING_USER_LOGIN	nvarchar(255)
PROJECT_ID	int	NOT NULL
CHANGE_TIMESTAMP	datetime
CONSTRAINT PK113_1 PRIMARY KEY CLUSTERED ( PROJECT_NUMBER_PERMISSION_ID )
)

CREATE TABLE USER_PERMISSION (
USER_PERMISSION_ID	int	NOT NULL
REPORTING_USER_ID	int	NOT NULL
REPORTING_USER_LOGIN	nvarchar(255)
USER_ID	int	NOT NULL
CHANGE_TIMESTAMP	datetime
CONSTRAINT PK114_1 PRIMARY KEY CLUSTERED ( USER_PERMISSION_ID )
)