ALEX_IMPORT_ER_LINE_ITEM (Staging Table)
| Column name | Data type | Required | Default update table | Data target | Comments |
|---|---|---|---|---|---|
| external_key** | VARCHAR (255) | Yes* | No | aler_expense_line_item.exp_line_item_id | The external key -- Primary Key |
| user_ekey | VARCHAR(255) | Yes* | No | The user the entry belongs to | |
| import_status | SMALLINT | Yes* | Yes | The import command/status (0=dont import, 1=do import, 2=success, 100+=re-process, -1=failed) | |
| import_error _message | VARCHAR(255) | No | No | The import error message | |
| import_change _timestamp | DATETIME | No | No | The import timestamp | |
| parent_type | SMALLINT | Yes | No | The type of parent to attach the line item to (0=document whose ekey is in parent_ekey, 1=line item whose ekey is in parent_ekey, 2=document whose tracking number is in parent_ekey, 3=unattached... no parent, 4=current active document, 5=current active document and create if necessary) | |
| parent_ekey | VARCHAR(255) | No | No | The parents ekey | |
| payment_type _id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.payment_type_id | Payment type |
| expense_type _id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.expense_type_id | Type of expense |
| vendor_id _ekey | VARCHAR(255) | No | No | aler_expense_line_item.vendor_id | The merchant |
| currency_fmt _defn_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.currency_fmt_defn_id | Currency of the item was paid in |
| cc_transaction _id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.cc_transaction_id | Credit card transaction |
| expense_item _date | DATETIME | No | No | aler_expense_line_item.expense_item_date | The date the expense was incurred |
| expense_ description | VARCHAR(255) | No | No | aler_expense_line_item.expense_description | Room for additional description of the expense |
| erli_native _amt_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.erli_native_amt_id | Currency for actual expense |
| erli_native _amt | NUMERIC(28,10) | No | No | aler_expense_line_item.erli_native_amt | The amount entered by the user, in native currency |
| purpose_id _ekey | VARCHAR(255) | No | No | aler_expense_line_item.purpose_id | Purpose |
| odometer _start | NUMERIC(15,5) | No | No | aler_expense_line_item.odometer_start | For mileage expense type - the odometer starting point |
| odometer_end | NUMERIC(15, 5) | No | No | aler_expense_line_item.odometer_end | For mileage expense type - the odometer ending point |
| miles | NUMERIC(15,5) | No | No | aler_expense_line_item.miles | For mileage expense type - the miles traveled |
| reimburs_rate | NUMERIC(18,7) | No | No | aler_expense_line_item.reimburs_rate | Reimbursement rte for mileage, e.g. $1.00 per mile |
| erli_exch_rate | NUMERIC(26,16) | No | No | aler_expense_line_item.erli_exch_rate | Currency exchange rate |
| item_gl_code | CHAR(20) | No | No | aler_expense_line_item.item_gl_code | G/L (General Ledger) code for this expense |
| erli_amt
_approv_id _ekey |
VARCHAR(255) | No | No | aler_expense_line_item.erli_amt_approv_id | Currency for approved amount |
| erli_amt _approv | NUMERIC(28,10) | No | No | aler_expense_line_item.erli_amt_approv | Amount that has been approved for reimbursement |
| has_receipt | SMALLINT | No | No | aler_expense_line_item.has_receipt | Is there a receipt for this expense |
| receipt _checked | SMALLINT | No | No | aler_expense_line_item.receipt_checked | Has manager approved the receipt? |
| location_id _ekey | VARCHAR(255) | No | No | aler_expense_line_item.location_id | Location of expense |
| is_personal | SMALLINT | No | No | aler_expense_line_item.is_personal | Was the expense personal |
| approved _exchange_rate | NUMERIC(26,16) | No | No | aler_expense_line_item.approved_exchange_rate | Foreign exchange rate that approver can change when reviewing expense report. |
| vat_amount _override | NUMERIC(28,10) | No | No | aler_expense_line_item.vat_amount_override | The amount overrides vat_amount. Can be set by reviewer when reviewing VAT amount. |
| vat_amount
_override_id _ekey |
VARCHAR(255) | No | No | aler_expense_line_item.vat_amount_override_id | Currency for vat_amount_override |
| vat_miles _override | NUMERIC(15,5) | No | No | aler_expense_line_item.vat_miles_override | This overrides miles. |
| vat_guests _override | INTEGER | No | No | aler_expense_line_item.vat_guests_override | This overrides the number of guests. |
| expense_item _depart_date | DATETIME | No | No | aler_expense_line_item.expense_item_depart_date | Line item element |
| origin_airport | VARCHAR(10) | No | No | aler_expense_line_item.origin_airport | Line item element |
| dest_airport | VARCHAR(10) | No | No | aler_expense_line_item.dest_airport | Line item element |
| booking_agent _ekey | VARCHAR(255) | No | No | aler_expense_line_item.booking_agent | Line item element |
| ticket_status _ekey | VARCHAR(255) | No | No | aler_expense_line_item.ticket_status | Line item element |
| ticket_number | VARCHAR(50) | No | No | aler_expense_line_item.ticket_number | Line item element |
| car_rental _agreement | VARCHAR(50) | No | No | aler_expense_line_item.car_rental_agreement | Line item element |
| airline_class
_of_service _ekey |
VARCHAR(255) | No | No | aler_expense_line_item.airline_class_of_service | Line item element |
| car_class
_of_service _ekey |
VARCHAR(255) | No | No | aler_expense_line_item.car_class_of_service | Line item element |
| hotel_class
_of_service _ekey |
VARCHAR(255) | No | No | aler_expense_line_item.hotel_class_of_service | Line item element |
| mileage_rate_ id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.mileage_rate_id | Line item element |
| mileage_
passenger _num |
INTEGER | No | No | aler_expense_line_item.mileage_passenger_num | Specifies how many passengers are on the same rental vehicle. |
| pst_amount_ override | NUMERIC(28,10) | No | No | aler_expense_line_item.pst_amount_override | If pst_amount is overridden by an auditor, the override amount is stored here. |
| pst_amount_
override_id _ekey |
VARCHAR(255) | No | No | aler_expense_line_item.pst_amount_override_id | Currency for pst_amount_override |
| guest_count | INTEGER | No | No | aler_expense_line_item.guest_count | Line item total guest count |
| flex_field_ string_1 | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_string_1 | Flex field in string type |
| flex_field_ string_2 | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_string_2 | Flex field in string type |
| flex_field_ string_3 | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_string_3 | Flex field in string type |
| flex_field_ string_4 | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_string_4 | Flex field in string type |
| flex_field_ integer_1 | INTEGER | No | No | aler_expense_line_item.flex_field_integer_1 | Flex field in integer type |
| flex_field_ integer_2 | INTEGER | No | No | aler_expense_line_item.flex_fiedl_integer_2 | Flex field in integer type |
| flex_field_date _1 | DATETIME | No | No | aler_expense_line_item.flex_field_date_1 | Flex field in date type |
| flex_field_date _2 | DATETIME | No | No | aler_expense_line_item.flex_field_date_2 | Flex field in date type |
| flex_field_ amount_1 | NUMERIC(28,10) | No | No | aler_expense_line_item.flex_field_amount_1 | Flex field for amount |
| flex_field_
amount_1_ id_ekey |
VARCHAR(255) | No | No | aler_expense_line_item.flex_field_amount_1_id | Currency of flex field amount |
| flex_field_ amount_2 | NUMERIC(28,10) | No | No | aler_expense_line_item.flex_field_amount_2 | Flex field for amount |
| flex_field_
amount_2_ id_ekey |
VARCHAR(255) | No | No | aler_expense_line_item.flex_field_amount_2_id | Currency of flex field amount |
| flex_field_
location_1 _id_ekey |
VARCHAR(255) | No | No | aler_expense_line_item.flex_field_location_1_id | Flex field location |
| flex_field_ddlb _1_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_ddlb_1_id | Flex field for DDLB |
| flex_field_ddlb _2_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_ddlb_2_id | Flex field for DDLB |
| flex_field_ddlb _3_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_ddlb_3_id | Flex field for DDLB |
| flex_field_ddlb _4_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_ddlb_4_id | Flex field for DDLB |
| flex_field_ddlb _5_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.flex_field_ddlb_5_id | Flex field for DDLB |
| mileage_route _id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.mileage_route_id | Mileage route |
| commute_ miles | NUMERIC(26,16) | No | No | aler_expense_line_item.commute_miles | The number of miles that the traveler commutes to work per day. This will typically be subtracted from the total miles before multiplying by the mileage rate. |
| guest_entry_ mode | SMALLINT | Yes* | No | aler_expense_line_item.guest_entry_mode | The guest entry mode (Count, Guests, or Both) |
| guest_amounts _equal | SMALLINT | Yes* | No | aler_expense_line_item.guest_amounts_equal | Whether the line item amount is equally split across guests |
| airline_
domestic_or _intl |
INTEGER | No | No | aler_expense_line_item.airline_domestic_or_intl | 1 = domestic flight, 2 = international |
| guest_no_ show_count | INTEGER | No | No | aler_expense_line_item.guest_no_show_count | The guest no-show count |
| passenger_ name | VARCHAR(255) | No | No | aler_expense_line_item.passenger_name | Passenger name used on airfare transactions. |
| keyword_alloc
_cost_center _ekey |
VARCHAR(255) | No | No | The line item's allocation cost center | |
| keyword_alloc
_project_num _ekey |
VARCHAR(255) | No | No | The line item's allocation project | |
| keyword_ guest_ekeys | TEXT | No | No | The line item's guests | |
| keyword_note | TEXT | No | No | The line item's note | |
| imported_read_only | NUMERIC(5) | No | Yes | aler_expense_line_item.imported_read_only | Holds the read only status of imported line items. |
| recurring_count | INTEGER | No | No | aler_expense_line_item.recurring_count | The per diem is applied over this many days. Corresponds to recurring_count from aler_expense_line_item. |
| end_date | DATETIME | No | No | aler_expense_line_item.end_date | The end date. Used for generating per diems over multiple days. |
| dest_location | VARCHAR(255) | No | No | aler_expense_line_item.dest_location | The destination location. |
| round_trip | SMALLINT | No | No | aler_expense_line_item.round_trip | If set to 1, a mileage expense is assumed to be a round trip. |
| tax1 | NUMERIC(28,10) | No | No | aler_expense_line_item.tax1 | Tax 1 on receipt |
| tax_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.tax1_id | The currency ekey of tax1 |
| tax2 | NUMERIC(28,10) | No | No | aler_expense_line_item.tax2 | Tax 2 on receipt |
| tax2_id_ekey | VARCHAR(255) | No | No | aler_expense_line_item.tax2_id | The currency ekey of tax2 |