EXM_VENDOR_EXPENSES
The table stores expense data from various vendor integration points.
Details
-
Schema: FUSION
-
Object owner: EXM
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
EXM_VENDOR_EXPENSES_PK |
VENDOR_EXPENSE_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| VENDOR_EXPENSE_ID | NUMBER | 18 | Yes | Unique identifier of an expense from a vendor. | |
| PERSON_NUMBER | VARCHAR2 | 30 | Alphanumeric identifier that uniquely identifies an employee within a company. | ||
| PERSON_ID | NUMBER | 18 | Person identifier of the individual on the corporate card. | ||
| PREPARER_ID | NUMBER | 18 | Identifier of the person who created the expense. | ||
| BUSINESS_UNIT_ID | NUMBER | 18 | Indicates the identifier of the business unit associated to the row. | ||
| ASSIGNMENT_ID | NUMBER | 18 | Assignment identifier of the individual on the corporate card. | ||
| VENDOR_EXPENSE_REFERENCE_ID | NUMBER | 18 | Unique identifier of an expense that identifies and groups similar expenses. | ||
| STATUS_CODE | VARCHAR2 | 30 | Processing status of a record. | ||
| SOURCE_TYPE_CODE | VARCHAR2 | 30 | Identifier of the integration source providing expense data. | ||
| PROVIDER_CODE | VARCHAR2 | 30 | Identifier of the vendor providing expense data. | ||
| RAW_EXPENSE_DATA | BLOB | Raw incoming JSON payload from vendor integration points. | |||
| TRANSACTION_TIME | TIMESTAMP | TImeStamp in UTC. This timestamp would be coming from the vendor for example authorization Timestamp. | |||
| RECEIPT_AMOUNT | NUMBER | Amount of the receipt in the receipt currency. | |||
| TIP_AMOUNT | NUMBER | Amount of tip given in the transaction. This helps to manage cases where receipts have different amount because of the tips involved. | |||
| PERSONAL_AMOUNT | NUMBER | Amount of the expense that is marked as personal. | |||
| MERCHANT_NAME | VARCHAR2 | 240 | Name of the merchant where the expense was incurred. | ||
| MERCHANT_ADDRESS | VARCHAR2 | 2000 | Address of the merchant present in the incurred expense. | ||
| MERCHANT_IDENTIFIER | VARCHAR2 | 30 | Identifier of the merchant where the authorization originated. | ||
| MERCHANT_COUNTRY | VARCHAR2 | 80 | Country where the merchant is located. | ||
| MERCHANT_CITY_NAME | VARCHAR2 | 80 | City where the merchant is located. | ||
| MERCHANT_STATE_PROVINCE_CODE | VARCHAR2 | 80 | State or province code where the merchant is located. | ||
| MERCHANT_POSTAL_CODE | VARCHAR2 | 80 | Zip or postal code where the merchant is located. | ||
| CONVERSION_RATE | NUMBER | Rate that represents the amount one currency can be exchanged for another at a specific point in time. | |||
| START_DATE | DATE | Date when the expense occurred or the first day of an expense that spans multiple days. | |||
| END_DATE | DATE | Last day of an expense that spans multiple days. | |||
| CHECKOUT_DATE | DATE | Date when a person departs a place associated with an accommodation expense category. | |||
| CHECKIN_DATE | DATE | Checkin date at the hotel or the scheduled arrival date in case of a no show. | |||
| AUTO_PERSONAL_FLAG | VARCHAR2 | 1 | An indicator of whether an expense is automatically categorized as personal based on the derived expense type. | ||
| EXPENSE_SOURCE_CODE | VARCHAR2 | 30 | Source of an expense item, such as cash or corporate card. | ||
| EXPENSE_CATEGORY_CODE | VARCHAR2 | 30 | Classification of an expense item such as business or personal. | ||
| EXPENSE_TEMPLATE_ID | NUMBER | 18 | Unique identifier of a specific expense template. | ||
| EXPENSE_TYPE_ID | NUMBER | 18 | Unique identifier for a specific expense type. | ||
| EXPENSE_TYPE_CATEGORY_CODE | VARCHAR2 | 30 | Grouping of expense types and additional data fields that are relevant for a particular grouping of expenses. An example is airfare. This grouping allows expense types to include information such as class of fare, merchant, ticket number, location from, and location to. | ||
| LOCATION_ID | NUMBER | 18 | Unique identifier of a specific location. | ||
| LOCATION | VARCHAR2 | 240 | Location where the expense was incurred. | ||
| RECEIPT_CURRENCY_CODE | VARCHAR2 | 15 | Receipt currency of an expense item. | ||
| DESCRIPTION | VARCHAR2 | 240 | Description of an expense item. | ||
| MERCHANT_CATEGORY_CODE | VARCHAR2 | 30 | Code assigned to the merchant by the corporte card provider to classify the type of business. | ||
| NOTIFICATION_TIME | TIMESTAMP | Timestamp in UTC when the notification to the user was sent. | |||
| FILE_HASH | VARCHAR2 | 2000 | Unique identifier to prevent duplicates by ensuring each file is processed only once. | ||
| VENDOR_UNIQUE_IDENTIFIER1 | VARCHAR2 | 150 | Unique identifier sent by the vendor. | ||
| VENDOR_UNIQUE_IDENTIFIER2 | VARCHAR2 | 150 | Additional Unique identifier sent by the vendor. | ||
| VENDOR_UNIQUE_IDENTIFIER3 | VARCHAR2 | 150 | Additional Unique identifier sent by the vendor. | ||
| VENDOR_UNIQUE_IDENTIFIER4 | VARCHAR2 | 150 | Additional Unique identifier sent by the vendor. | ||
| VENDOR_UNIQUE_IDENTIFIER5 | VARCHAR2 | 150 | Additional Unique identifier sent by the vendor. | ||
| VENDOR_CHAR_ATTRIBUTE1 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE2 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE3 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE4 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE5 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE6 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE7 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE8 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE9 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE10 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE11 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE12 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE13 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE14 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE15 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE16 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE17 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE18 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE19 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE20 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE21 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE22 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE23 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE24 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE25 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE26 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE27 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE28 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE29 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_CHAR_ATTRIBUTE30 | VARCHAR2 | 600 | Additional attribute holders needed to store vendor data. | ||
| VENDOR_NUMBER_ATTRIBUTE1 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE2 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE3 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE4 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE5 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE6 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE7 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE8 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE9 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_NUMBER_ATTRIBUTE10 | NUMBER | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE1 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE2 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE3 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE4 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE5 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE6 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE7 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE8 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE9 | DATE | Additional attribute holders needed to store vendor data. | |||
| VENDOR_DATE_ATTRIBUTE10 | DATE | Additional attribute holders needed to store vendor data. | |||
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| EXM_VENDOR_EXPENSES | exm_expense_types | EXPENSE_TYPE_ID |
| EXM_VENDOR_EXPENSES | exm_expense_templates | EXPENSE_TEMPLATE_ID |
| EXM_VENDOR_EXPENSES | per_persons | PERSON_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| EXM_VENDOR_EXPENSES_N1 | Non Unique | Default | PERSON_ID, VENDOR_EXPENSE_REFERENCE_ID |
| EXM_VENDOR_EXPENSES_N2 | Non Unique | Default | VENDOR_UNIQUE_IDENTIFIER1, VENDOR_UNIQUE_IDENTIFIER2, VENDOR_UNIQUE_IDENTIFIER3, VENDOR_UNIQUE_IDENTIFIER4, VENDOR_UNIQUE_IDENTIFIER5 |
| EXM_VENDOR_EXPENSES_U1 | Unique | Default | VENDOR_EXPENSE_ID |