September 2022 Production Update
This chapter describes the new enhancements for Expense Management version 2022.09
Note: If you access Expense Management using Infor Ming.le in a
multi-tenant environment, a new widget is available that notifies you of upcoming Expense
Management updates. This widget provides highlights of new features and improvements for your
products. A link provides access to release documentation on the Infor Support Portal. The
What's New widget does not include information for patch releases and emergency updates.
If you are authorized to access the What's New widget, you can add it from the Widget Catalog to a homepage. See the information about the What's New widget in the Infor Ming.le Cloud Edition Online Help.
General enhancements
This section describes the enhancements related to all modules of the application:
- The Expense Management application is enhanced to automatically remove a locked entry
when a BOD is processed again from ION Tools in the SAT application. The locked entry is
created in the EXTN_BODS_LOCK table of the central database when a BOD encounters a
processing error.Note: For details, see KB 2244586
- The Infor Expense Management application user interface is enhanced to display the
rejected documents or the documents that have been returned for more information in bold,
to identify the documents easily.Note: For details, see KB 2223403
- The Infor Expense Management application provides the ability to configure background
processes to run on specific days of a month. Note: For details, see KB 2196736
- The Infor Expense Management application is enhanced to include the billing period when
importing credit card transactions for VISA VCF 4.0.Note: For details, see KB 2241123
- The HTML SAT application enables administrators to configure additional table columns
using the HTML Grid Column Editor tool. Additional fields can be displayed on the Credit
Card or Document Management tabs, without opening each item to view that data.Note: For details, see KB 2127916
- The Expense Management application is enhanced to notify users (Administrators) when a
BKG process moves to the DIED status. This enhancement enables to remediate the problem
immediately.Note: For details, see KB 2185650
- Cost center assigned from GHR is automatically added as a valid cost center for user.
This enhancement is applicable only when the cost center of the user is not found in the
application. You must enable these parameters to configure this functionality:
- ion.costCenterSegmentSeparator: The character must be specified for separating the segments in the SyncPersonnal BOD, when Cost Center codes are received. By default, the character is a period (.)
- ion.acceptNewCostCenterAsValid: The value of this parameter is set to True to create Cost Center from GHR through SyncPersonnal BOD
Note: For details, see KB 2198484 - A tooltip is now displayed to indicate a document status when you rest pointer on the
color bar corresponding to a document. This enhancement is applicable to the My Documents and the Documents for Review tab.Note: For details, see KB 2225843
- The Expense Management application provides you the ability to delete multiple receipts
from the Receipt Store.Note: For details, see KB 2226256
- The HTML SAT ION INBOX and OUTBOX pages are enhanced to include the From Date, To Date,
From Time, and To Time fields. You can now specify a date or a time range to review
information related to a specific BOD.Note: For details, see KB 2232075
- The HTML SAT ION INBOX and OUTBOX pages do not display data when accessed for the first
time. The screen displays information related to a BOD only when search criteria are
specified in the Primary Information section. Note: For details, see KB 2232078
- The Message Type field in the HTML SAT ION
INBOX and OUTBOX pages now display a list of the INBOX or OUTBOX BODs, respectively. You
can select the required BOD to review the related information.Note: For details, see KB 2232080
- The BCG cloud is now upgraded. You can now use the resource key values that now fetched
from the property files, in all the .jsp pages.Note: For details, see KB 2236873
- The Expense Management application is enhanced to change the mouse icon(arrow) to an
hourglass icon indicating that the application is busy processing a request. This
enhancement is applicable when there is a pause in the activity or movement to the next
action while accessing a functionality in the application.Note: For details, see KB 2237754
- The Expense Management Cleanup Tool is enhanced to clean the OCR related data.Note: For details, see KB 2252417
XMDBTool utility enhancements
The XMDBTool utility does not consider the inactive alex_xmdbtool rows.
Note: For details, see KB 2243756
Expense Report enhancements
This section describes the enhancements related to expense reports:
- The Infor Expense Management application now provides you the ability to delete receipts
attached to the line items in an expense report.Note: For details, see KB 2237204
- The Expense Management application does not route an expense report to a manager for
approval. This enhancement is applicable when an expense report is created by the manager
who is assigned to the role of a Creator Proxy for one of the employees.Note: For details, see KB 2175987
- The GenerateERFromCCBkg process is enhanced to create a new expense report by including
the unattached credit card transactions that have been imported into the application after
an expense report is already submitted.Note: For details, see KB 2246206
Business Rule enhancements
This section describes the enhancements related to business rules:
- The Compliance Confirmation business rule is enhanced to update an existing compliance
note instead of creating a new note each time an expense report is submitted. A new
br.complianceConfirmationSingleNote application parameter is introduced for this
enhancement. These are the possible scenarios when this parameter is configured:
- If the value of this application parameter is set to True, a single compliance note is maintained. The date and user ID is reset each time a user clicks Yes based on the response of the rule.
- If the value of this application parameter is set to False, compliance notes are created each time the compliance is triggered.
Note: For details, see KB 1994797 - The Previous Timesheets Must Be Submitted business rule is introduced in the Expense
Management application. This business rule ensures that a new timesheet is submitted only
if the previous timesheet is already submitted.Note: For details, see KB 2238524
New Staging Tables
These staging tables are added for Expense Management version 2022.09.
| CREATE TABLE alex_import_flex_ddlb_11 ( | ||
|---|---|---|
| external_key | nvarchar(255) | NOT NULL |
| flex_ddlb_code | nvarchar(255) | |
| flex_ddlb_value | nvarchar(255) | |
| user_id | int | |
| active_status | smallint | NOT NULL |
| CONSTRAINT alex_import_flex_ddlb_11_PK PRIMARY KEY CLUSTERED ( external_key ) | ||
| ) | ||
| go | ||
| CREATE TABLE alex_import_flex_ddlb_12 ( | ||
|---|---|---|
| external_key | nvarchar(255) | NOT NULL |
| flex_ddlb_code | nvarchar(255) | |
| flex_ddlb_value | nvarchar(255) | |
| user_id | int | |
| active_status | smallint | NOT NULL |
| CONSTRAINT alex_import_flex_ddlb_12_PK PRIMARY KEY CLUSTERED ( external_key ) | ||
| ) | ||
| go | ||
| CREATE TABLE alex_import_flex_ddlb_13 ( | ||
|---|---|---|
| external_key | nvarchar(255) | NOT NULL |
| flex_ddlb_code | nvarchar(255) | |
| flex_ddlb_value | nvarchar(255) | |
| user_id | int | |
| active_status | smallint | NOT NULL |
| CONSTRAINT alex_import_flex_ddlb_13_PK PRIMARY KEY CLUSTERED ( external_key ) | ||
| ) | ||
| go | ||
| CREATE TABLE alex_import_flex_ddlb_14 ( | ||
|---|---|---|
| external_key | nvarchar(255) | NOT NULL |
| flex_ddlb_code | nvarchar(255) | |
| flex_ddlb_value | nvarchar(255) | |
| user_id | int | |
| active_status | smallint | NOT NULL |
| CONSTRAINT alex_import_flex_ddlb_14_PK PRIMARY KEY CLUSTERED ( external_key ) | ||
| ) | ||
| go | ||
| CREATE TABLE alex_import_flex_ddlb_15 ( | ||
|---|---|---|
| external_key | nvarchar(255) | NOT NULL |
| flex_ddlb_code | nvarchar(255) | |
| flex_ddlb_value | nvarchar(255) | |
| user_id | int | |
| active_status | smallint | NOT NULL |
| CONSTRAINT alex_import_flex_ddlb_15_PK PRIMARY KEY CLUSTERED ( external_key ) | ||
| ) | ||
| go | ||
Modified Staging Tables
These staging tables are modified in 2022.09. The new columns are highlighted.
| CREATE TABLE aler_expense_export_line ( | ||
|---|---|---|
| export_er_line_id | int | NOT NULL |
| export_er_hdr_id | int | NOT NULL |
| exli_exp_line_item_id | int | NOT NULL |
| export_er_line_status | smallint | |
| export_line_status_datetime | datetime | |
| exli_line_allocation_id | int | NOT NULL |
| exli_alloc_base_amt | numeric(28,10) | |
| exli_alloc_base_amt_currency | nvarchar(10) | |
| exli_alloc_native_amt | numeric(28,10) | |
| exli_alloc_native_amt_curr | nvarchar(10) | |
| exli_expense_item_date | datetime | |
| exli_erli_exch_rate | numeric(26,16) | |
| exli_alloc_percent_allotted | numeric(26,16) | |
| exli_alloc_cost_center_name | nvarchar(255) | |
| exli_alloc_cost_center_code | nvarchar(255) | |
| exli_alloc_project_code | nvarchar(255) | |
| exli_alloc_parent_proj_code | nvarchar(255) | |
| exli_alloc_top_project_code | nvarchar(255) | |
| exli_line_payment_method_code | int | |
| exli_line_is_personal_flag | smallint | |
| exli_expense_type_label | nvarchar(255) | |
| exli_expense_category_label | nvarchar(255) | |
| exli_expense_gl_code | nvarchar(255) | |
| exli_expense_category_class | smallint | |
| exli_expense_special_code | int | |
| exli_major_account | nvarchar(255) | |
| exli_expense_external_sysid | nvarchar(255) | |
| exli_erli_amt_paid | numeric(28,10) | |
| exli_erli_native_amt | numeric(28,10) | |
| exli_erli_amt_paid_currency | nvarchar(10) | |
| exli_erli_native_amt_currency | nvarchar(10) | |
| exli_vendor_name | nvarchar(255) | |
| exli_vendor_given_sic | nvarchar(255) | |
| exli_purpose | nvarchar(255) | |
| exli_expense_description | nvarchar(255) | |
| exli_city | nvarchar(255) | |
| exli_state | nvarchar(255) | |
| exli_country | nvarchar(255) | |
| exli_cc_card_number | nvarchar(255) | |
| exli_cc_issuer | nvarchar(255) | |
| exli_payment_type_label | nvarchar(255) | |
| exli_alloc_project_charge_code | nvarchar(255) | |
| exli_miles | numeric(15,5) | |
| exli_odometer_start | numeric(15,5) | |
| exli_odometer_end | numeric(15,5) | |
| exli_perdiem_amount | numeric(28,10) | |
| exli_perdiem_amount_currency | nvarchar(10) | |
| exli_cct_reference | nvarchar(255) | |
| exli_alloc_is_billable | smallint | |
| exli_county | nvarchar(255) | |
| exli_suburb | nvarchar(255) | |
| exli_street | nvarchar(255) | |
| alloc_base_currency_ekey | nvarchar(255) | |
| alloc_native_currency_ekey | nvarchar(255) | |
| alloc_cost_center_ekey | nvarchar(255) | |
| alloc_project_ekey | nvarchar(255) | |
| payment_type_ekey | nvarchar(255) | |
| expense_type_ekey | nvarchar(255) | |
| amt_paid_currency_ekey | nvarchar(255) | |
| native_amt_currency_ekey | nvarchar(255) | |
| vendor_ekey | nvarchar(255) | |
| purpose_ekey | nvarchar(255) | |
| location_ekey | nvarchar(255) | |
| perdiem_amount_currency_ekey | nvarchar(255) | |
| cc_transaction_id | int | |
| expense_item_depart_date | datetime | |
| origin_airport | nvarchar(10) | |
| dest_airport | nvarchar(10) | |
| booking_agent_name | nvarchar(50) | |
| booking_agent_description | nvarchar(255) | |
| booking_agent_ekey | nvarchar(255) | |
| ticket_status_name | nvarchar(255) | |
| ticket_status_ekey | nvarchar(255) | |
| ticket_number | nvarchar(50) | |
| car_rental_agreement | nvarchar(50) | |
| air_service_class_code | nvarchar(4) | |
| air_service_class_name | nvarchar(255) | |
| air_service_class_ekey | nvarchar(255) | |
| car_service_class_code | nvarchar(4) | |
| car_service_class_name | nvarchar(255) | |
| car_service_class_ekey | nvarchar(255) | |
| hotel_service_class_code | nvarchar(4) | |
| hotel_service_class_name | nvarchar(255) | |
| hotel_service_class_ekey | nvarchar(255) | |
| mileage_rate_name | nvarchar(40) | |
| mileage_rate_ekey | nvarchar(255) | |
| mileage_passenger_num | int | |
| alloc_department_name | nvarchar(255) | |
| alloc_department_code | nvarchar(255) | |
| alloc_division_name | nvarchar(255) | |
| alloc_division_code | nvarchar(255) | |
| alloc_company_name | nvarchar(255) | |
| alloc_company_code | nvarchar(255) | |
| cc_level3_detail_id | int | |
| guest_count | int | |
| paid_amount | numeric(28,10) | |
| paid_amount_currency | nvarchar(10) | |
| 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_amt_1 | numeric(28,10) | |
| flex_field_amt_1_currency | nvarchar(10) | |
| flex_field_amt_2 | numeric(28,10) | |
| flex_field_amt_2_currency | nvarchar(10) | |
| flex_field_loc_1_ekey | nvarchar(255) | |
| flex_field_loc_1_county | nvarchar(255) | |
| flex_field_loc_1_state | nvarchar(255) | |
| flex_field_loc_1_country | nvarchar(255) | |
| flex_field_loc_1_city | nvarchar(255) | |
| flex_field_loc_1_suburb | nvarchar(255) | |
| flex_field_loc_1_street | nvarchar(255) | |
| flex_field_ddlb_1_code | nvarchar(255) | |
| flex_field_ddlb_1_value | nvarchar(255) | |
| flex_field_ddlb_2_code | nvarchar(255) | |
| flex_field_ddlb_2_value | nvarchar(255) | |
| flex_field_ddlb_3_code | nvarchar(255) | |
| flex_field_ddlb_3_value | nvarchar(255) | |
| flex_field_ddlb_4_code | nvarchar(255) | |
| flex_field_ddlb_4_value | nvarchar(255) | |
| flex_field_ddlb_5_code | nvarchar(255) | |
| flex_field_ddlb_5_value | nvarchar(255) | |
| exli_pci_token | nvarchar(255) | |
| alloc_segment1_name | nvarchar(255) | |
| alloc_segment1_code | nvarchar(255) | |
| alloc_segment2_name | nvarchar(255) | |
| alloc_segment2_code | nvarchar(255) | |
| alloc_segment3_name | nvarchar(255) | |
| alloc_segment3_code | nvarchar(255) | |
| alloc_segment4_name | nvarchar(255) | |
| alloc_segment4_code | nvarchar(255) | |
| alloc_segment5_name | nvarchar(255) | |
| alloc_segment5_code | nvarchar(255) | |
| alloc_segment6_name | nvarchar(255) | |
| alloc_segment6_code | nvarchar(255) | |
| alloc_segment7_name | nvarchar(255) | |
| alloc_segment7_code | nvarchar(255) | |
| alloc_segment8_name | nvarchar(255) | |
| alloc_segment8_code | nvarchar(255) | |
| alloc_segment9_name | nvarchar(255) | |
| alloc_segment9_code | nvarchar(255) | |
| segment_count | int | |
| alloc_review_segment_name | nvarchar(255) | |
| alloc_review_segment_code | nvarchar(255) | |
| guest_entry_mode | smallint | |
| guest_amounts_equal | smallint | |
| guest_no_show_count | int | |
| passenger_name | nvarchar(255) | |
| export_status | int | |
| void_status | int | |
| reversal_line_item_id | int | |
| export_date | datetime | |
| tax1 | numeric(28,10) | |
| tax1_id | int | |
| tax2 | numeric(28,10) | |
| tax2_id | int | |
| billing_period | int | |
| CONSTRAINT pk__ale19aacf41 PRIMARY KEY CLUSTERED ( export_er_line_id ) | ||
| ) | ||
| go | ||
| CREATE TABLE alex_charge_role ( | ||
|---|---|---|
| external_key | nvarchar(255) | NOT NULL |
| charge_role_name | nvarchar(255) | |
| charge_role_active_status | smallint | NOT NULL |
| charge_role_code | nvarchar(255) | |
| CONSTRAINT PK111_1 PRIMARY KEY CLUSTERED ( external_key ) | ||
| ) | ||
| go | ||
| CREATE TABLE alex_import_er_line_item ( | ||
|---|---|---|
| external_key | nvarchar(255) | NOT NULL |
| user_ekey | nvarchar(255) | NOT NULL |
| import_status | smallint | DEFAULT 1 NOT NULL |
| import_error_message | nvarchar(255) | |
| import_change_timestamp | datetime | |
| parent_type | smallint | DEFAULT 0 NOT NULL |
| parent_ekey | nvarchar(255) | |
| payment_type_id_ekey | nvarchar(255) | |
| expense_type_id_ekey | nvarchar(255) | |
| vendor_id_ekey | nvarchar(255) | |
| currency_fmt_defn_id_ekey | nvarchar(255) | |
| cc_transaction_id_ekey | nvarchar(255) | |
| expense_item_date | datetime | |
| expense_description | nvarchar(255) | |
| erli_native_amt_id_ekey | nvarchar(255) | |
| erli_native_amt | numeric(28,10) | |
| purpose_id_ekey | nvarchar(255) | |
| odometer_start | numeric(15,5) | |
| odometer_end | numeric(15,5) | |
| miles | numeric(15,5) | |
| reimburs_rate | numeric(18,7) | |
| erli_exch_rate | numeric(26,16) | |
| item_gl_code | nchar(20) | |
| erli_amt_approv_id_ekey | nvarchar(255) | |
| erli_amt_approv | numeric(28,10) | |
| has_receipt | smallint | |
| receipt_checked | smallint | |
| location_id_ekey | nvarchar(255) | |
| is_personal | smallint | |
| approved_exchange_rate | numeric(26,16) | |
| vat_amount_override | numeric(28,10) | |
| vat_amount_override_id_ekey | nvarchar(255) | |
| vat_miles_override | numeric(15,5) | |
| vat_guests_override | int | |
| expense_item_depart_date | datetime | |
| origin_airport | nvarchar(10) | |
| dest_airport | nvarchar(10) | |
| booking_agent_ekey | nvarchar(255) | |
| ticket_status_ekey | nvarchar(255) | |
| ticket_number | nvarchar(50) | |
| car_rental_agreement | nvarchar(50) | |
| airline_class_of_service_ekey | nvarchar(255) | |
| car_class_of_service_ekey | nvarchar(255) | |
| hotel_class_of_service_ekey | nvarchar(255) | |
| mileage_rate_id_ekey | nvarchar(255) | |
| mileage_passenger_num | int | |
| pst_amount_override | numeric(28,10) | |
| pst_amount_override_id_ekey | nvarchar(255) | |
| guest_count | 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_id_ekey | nvarchar(255) | |
| flex_field_amount_2 | numeric(28,10) | |
| flex_field_amount_2_id_ekey | nvarchar(255) | |
| flex_field_location_1_id_ekey | nvarchar(255) | |
| flex_field_ddlb_1_id_ekey | nvarchar(255) | |
| flex_field_ddlb_2_id_ekey | nvarchar(255) | |
| flex_field_ddlb_3_id_ekey | nvarchar(255) | |
| flex_field_ddlb_4_id_ekey | nvarchar(255) | |
| flex_field_ddlb_5_id_ekey | nvarchar(255) | |
| mileage_route_id_ekey | nvarchar(255) | |
| commute_miles | numeric(26,16) | |
| guest_entry_mode | smallint | DEFAULT 0 NOT NULL |
| guest_amounts_equal | smallint | DEFAULT 1 NOT NULL |
| airline_domestic_or_intl | int | |
| guest_no_show_count | int | |
| passenger_name | nvarchar(255) | |
| keyword_alloc_cost_center_ekey | nvarchar(255) | |
| keyword_alloc_project_num_ekey | nvarchar(255) | |
| keyword_guest_ekeys | nvarchar(max) | |
| keyword_note | nvarchar(max) | |
| imported_read_only | smallint | |
| recurring_count | int | |
| end_date | datetime | |
| dest_location | nvarchar(255) | |
| round_trip | smallint | |
| tax1 | numeric(28,10) | |
| tax_id_ekey | nvarchar(255) | |
| tax2 | numeric(28,10) | |
| tax2_id_ekey | nvarchar(255) | |
| CONSTRAINT PK_alt3CBF0154ERLI PRIMARY KEY CLUSTERED ( external_key ) | ||
| ) | ||
| go | ||
Reporting Database Schema
These tables are modified in the Reporting Database Schema. The new columns are highlighted and the deleted columns are marked with the * symbol.
| CREATE TABLE CREDIT_CARD ( | ||
|---|---|---|
| CREDIT_CARD_ID | int | NOT NULL |
| PROCESSOR_NAME | nvarchar(255) | |
| PAYMENT_TYPE_ID | int | |
| USER_ID | int | |
| ACCOUNT_NUMBER | nvarchar(255) | |
| NAME_LINE_1 | nvarchar(255) | |
| NAME_LINE_2 | nvarchar(255) | |
| ADDRESS_LINE_1 | nvarchar(255) | |
| ADDRESS_LINE_2 | nvarchar(255) | |
| IS_PAID_BY_COMPANY | smallint | |
| SPENDING_LIMIT_AMOUNT_CURRENCY | nchar(3) | |
| SPENDING_LIMIT_AMOUNT | numeric(28,10) | |
| CARD_ISSUER | nvarchar(255) | |
| IS_CARD_ACTIVE | smallint | |
| DEACTIVATION_DATE | datetime | |
| IS_PR_CARD | smallint | |
| PR_PAYMENT_TYPE | nvarchar(255) | |
| CARD_TYPE | nvarchar(255) | |
| COST_CENTER_ID | int | |
| PROJECT_NUMBER_ID | int | |
| GLCODE | nvarchar(255) | |
| CHANGE_TIMESTAMP | datetime | |
| SOURCE_CARD_ID | int | |
| CONSTRAINT PK29 PRIMARY KEY CLUSTERED ( CREDIT_CARD_ID ) | ||
| ) | ||
| go | ||
| CREATE TABLE CREDIT_CARD_TRANSACTION ( | ||
|---|---|---|
| CREDIT_CARD_TRANSACTION_ID | 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 | nvarchar(20) | |
| MERCHANT_STD_SIC_CODE | nvarchar(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) | |
| ASSIGNED_TRANSACTION_USER_ID | int | |
| SOURCE_TRANSACTION_ID | int | |
| BILLING_PERIOD | int | |
| CONSTRAINT PK30 PRIMARY KEY CLUSTERED ( CREDIT_CARD_TRANSACTION_ID ) | ||
| ) | ||
| go | ||
| 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) | |
| BILLING_PERIOD | int | |
| CONSTRAINT PK7 PRIMARY KEY CLUSTERED ( LINE_ITEM_ID ) | ||
| ) | ||
| go | ||