Data Extracts
Table of Contents
In order to facilitate customer’s various needs, including additional integrations and information needs, Lextegrity provides data extracts for a variety of data objects from both the Approvals & Disclosures and Third Party Management applications.
What is Provided?
The following data objects are provided in a delta manner - with the exception of the initial extract - which is a full extract - regardless of the delta period. Please see Appendix A below for the fields provided and relationships amongst the data objects.
| Data Object | Description | Delta Type |
|---|---|---|
| Actions | A listing of all actions associated with the request, activities, and / or parties. | Request Patterns |
| Activities | A supportive listing of activities associated with a request. | Request Pattern |
| Parties | The foundational details for parties. | Party Pattern |
| Party Addresses | Addresses associated with parties. | Party Pattern |
| Party Details | The available attributes about the party. | Party Pattern |
| Requests | The core data object which represents the basic details of the request. | Request Pattern |
| Requests Activities Parties | The data which allows connectivity between the request’s activities and the associated parties and responses | Request Pattern |
| Responses | A listing of all questions and responses associated with the request, activities, and / or parties. | Request Pattern |
| Users | A listing of users of the system and the based attributes associated with the user. | Party Pattern (less fair market value concerns) |
| Role Privileges | A listing of privileges per role. | User Privileges Pattern |
| User Groups | A listing of group assignments per user. | User Privileges Pattern |
| User Privileges | A listing of privileges per user. | User Privileges Pattern |
| User Roles | A listing of roles per user. | User Privileges Pattern |
| Tasks | A listing of tasks that is or not associated with a request. | Task Pattern |
| Request File Attachments | A listing of file attachments per request. | Request Pattern |
| Risk Scoring | The basic risk scoring details associated with a request for which risk scoring is enabled. | Request Pattern |
| Risk Scoring Criterias | The risk scoring criteria that is associated with a risk scoring record. | Request Pattern |
| Request Activity Approval Steps | A listing of approval steps for activity. | Request Pattern |
| Request Recipient Approval Steps | A listing of approval steps for recipients. | Request Pattern |
Request Pattern records are shared when the associated request meets any of the following conditions:
- New Request within delta time period.
- New Request version within delta time period.
- Any action (submit, approve, reject, etc.) taken on the request.
Party Pattern records are shared when the associated request meets any of the following conditions:
- New Party within delta time period.
- New / updated party information attributes within delta time period.
- New / updated party fair market value (FMV) details within delta time period.
Scope and Format of Data
Customers will be provided with SFTP details where the files will be dropped.
The files begin running at the following times and are dropped into the STFP when done. (Approximately 30 minutes from start time.)
- Stage extracts are run daily at 9:30 AM UTC.
- Production extracts are run daily at 2:00 AM UTC.
Formatting
The files generated will be done so with the following characteristics:
- CSV Extension.
- Comma utilized as the delimiter.
- Column headers provided with each file which are lowercase with no special characters or spaces (underscores used).
- Values separated by the comma (“,”) delimiter.
- All text fields are quoted with the double quote character.
- Any literal double quote characters embedded are escaped with another double quote character.
- Utf-8 encoding.
File Naming
Structure - [customer_name]_[appliciation]_[data_object]_[run_date]_[extract_type]_[data_from]_[data_to].[extension]
e.g., acme_approvals_requests_20220721180505_delta_20220720_20220720.csv
- customer_name - single word name of the customer
- application - single word name of the application
- data_area - single word name of the data object
- run_date - the date when the extract was run in the following format: YYYYMMDDHHMMSS
- extract_type - either full or delta
- data_from - the date for the start of data in the following format: YYYYMMDD
- data_to - the date for the end of data in the following format: YYYYMMDD
- extension - the value for the extension (currently only csv)
SFTP Details
Within the SFTP directory, the following folder structures for both stage and prod will exist:
- /[customer_name]/extracts/ - Root directory where any extract documentation will be stored, in addition to the directories that will store the extract files themselves.
- /[customer_name]/extracts/_full - Directory where full sets of extracts (provided out of the typical periodic processing of extracts) will be placed.
- /[customer_name]/extracts/[YYYY-MM]* - The directory which will be dynamically created based on the date of the data extracted (not the run date).
As an example, the files generated on 7/01/2022 for data created/modified on 6/30/2022 will have the following file paths and names:
/acme/extracts/202206/acme_approvals_requests_20220701180505_delta_20220630_20220630.csv
/acme/extracts/202206/acme_approvals_parties_20220701180505_delta_20220630_20220630.csv
Appendix A: Data Dictionary
Actions
| Field | Order | Definition |
|---|---|---|
| action_id | 1 | The unique ID for the action. |
| action_datetime | 2 | The datetime when the action took place. |
| actioned_by_party_id | 3 | The actioner's unique ID which types to users / parties data objects. |
| actioned_by_name | 4 | The name of the actioner. |
| actioned_by_role | 5 | The actioner's role when performing the action. |
| action_type | 6 | The action type (e.g., submit, update, approve). |
| request_id | 7 | The unique ID value for the request which links to the requests data object. |
| formatted_request_id | 8 | The unique ID - formatted - for the request. |
| actioned_object_type | 9 | The type of object that was actioned upon (e.g., Activity, Party, Questionnaire, Request) |
| actioned_object_id | 10 | The unique ID value for the actioned_object_type. |
| request_step_order | 11 | A integer, which when sorted, allows the understanding of the order of the desired request step. |
| request_step_name | 12 | The name of the request step. |
| comment | 13 | The comment provided alongside the action (per the actioned_by_party_id), if available. |
| requires_mitigation | 14 | The user denoted that mitigation is required. |
| voided | 15 | The user voided their action. |
| modified_datetime | 16 | The short value/code identifying the specific customer. |
| customer_id | 17 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
Activities
| Field | Order | Definition |
|---|---|---|
| activity_id | 1 | |
| request_id | 2 | |
| formatted_request_id | 3 | The unique ID - formatted - for the request. |
| is_primary_activity | 4 | |
| activity_status | 5 | |
| is_active | 6 | |
| activity_name | 7 | |
| amount | 8 | |
| currency_code | 9 | |
| modified_datetime | 10 | |
| customer_id | 11 |
Parties
| Field | Order | Definition |
|---|---|---|
| party_id | 1 | |
| name | 2 | |
| 3 | ||
| 4 | ||
| 5 | ||
| 6 | ||
| 7 | ||
| 8 | ||
| 9 | ||
| 10 | ||
| 11 | ||
| 12 | ||
| 13 | ||
| 14 | ||
| 15 | ||
| 16 | ||
| 17 | ||
| 18 | ||
| 19 | ||
| 20 | ||
| 21 | ||
| modified_datetime | 22 | |
| 23 | The email associated with the party. | |
| erp_id | 24 | An ID for the party used by external system. |
| country | 25 | The country of the party. |
| is_active | 26 | Whether or not the party is active. |
| is_blocked | 27 | Whether or not the party is blocked. |
| note | 28 | Any notes about this party. |
Party Addresses
| Field | Order | Definition |
|---|---|---|
| party_id | 1 | The unique ID for the party. |
| type | 2 | The type of address (i.e. home, business). |
| address_line_1 | 3 | The first line of the address. |
| address_line_2 | 4 | The second line of the address. |
| city | 5 | The city of the address. |
| state | 6 | The state of the address. |
| country | 7 | The country of the address. |
| postal_code | 8 | The postal code of the address. |
| modified_datetime | 9 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 10 | The short value/code identifying the specific customer. |
Party Details
| Field | Order | Definition |
|---|---|---|
| 1 | ||
| 2 | ||
| 3 | ||
| 4 | ||
| 5 |
Requests
| Field | Order | Definition |
|---|---|---|
| 1 | ||
| 2 | ||
| 3 | ||
| 4 | ||
| 5 | ||
| 6 | ||
| 7 | ||
| 8 | ||
| 9 | ||
| 10 | ||
| 11 | ||
| 12 | ||
| 13 | ||
| 14 | ||
| 15 | ||
| 16 | ||
| 17 | ||
| parent_request_id | 18 | The ID for the parent request (if it exists). Associates a request to a composite request (ex. campaign) |
| forms_of_support | 19 | Comma separated list of values that denote the forms of support selected within the request |
| sub_type | 20 | Comma separated list of values that denote the request subtypes selected within the request |
Requests Activities Parties Linkage
| Field | Order | Definition |
|---|---|---|
| 1 | ||
| formatted_request_id | 2 | The unique ID - formatted - for the request. |
| 3 | ||
| activity_status | 4 | The status of the activity (in_progress, approved, etc). |
| 5 | ||
| party_status | 6 | The party’s status (e.g. awaiting_approval, approved, canceled) |
| 7 | ||
| 8 | ||
| activity_party_status | 9 | The status of the activity_party (in_progress, approved, etc). |
| 10 | ||
| 11 | ||
| local_amount | 12 | The amount allocated to the party for that activity (may be in tenant’s default currency, or a user selected currency, when currency differs from tenant default) |
| local_currency_code | 13 |
The local currency associated with the amount field. (may be in tenant’s default currency, or a user selected currency, when currency differs from tenant default) |
| request_party_id | 14 | The ID which uniquely identifies a party within a specific request. |
Responses
| Field | Order | Definition |
|---|---|---|
| request_id | 1 | The ID which relates to the requests data object (id field). |
| formatted_request_id | 2 | The unique ID - formatted - for the request. |
| activity_id | 3 | The ID which relates to the specific request activity. |
| party_id | 4 | The party ID when party relevant. |
| question_code | 5 | A Lextegrity code used to identify the exact question (e.g., q_company_active_negotiations_to_expand_business_04). |
| library_item_code | 6 | A Lextegrity code used to identify the general question (e.g., company_active_negotiations_to_expand_business). |
| question_default_text | 7 | The default text assigned for the question. |
| response_data | 8 | The response data value is a single field with varying types. |
| response_type | 9 | The response type column is provided to support any parsing needs. |
| response_order | 10 | A number which represents the placement question in the questionnaire. |
| attachment_names | 11 | When the response has associated files attached, the filenames are available in this field. When multiple files, the names are delimited by the characters **. |
| 12 | ||
| 13 | ||
| request_party_id | 14 | The ID which uniquely identifies a party within a specific request. |
| request_questionnaire_id | 15 | The ID which uniquely identifies a questionnaire within a specific request. |
Users
| Field | Order | Definition |
|---|---|---|
| party_id | 1 | |
| name | 2 | |
| status | 3 | |
| function | 4 | |
| division | 5 | User division. |
| business_area | 6 | User business_area. |
| subfunction | 7 | User subfuction. |
| 8 | The user's email address. | |
| username | 9 | The Lextegrity username used to log into the application. |
| external_id | 10 | The user's external (company) ID, when available. |
| country | 11 | The country name denoted for the employee. |
| is_active | 12 | Whether or not the user is active. |
| is_blocked | 13 | Whether or not the user is blocked. |
| created_datetime | 14 | |
| modified_datetime | 15 | |
| customer_id | 16 | |
| user_id | 17 | The user ID uniquely identifying the user. |
| company_name | 18 | Company name. |
| manager _id | 19 | ID for Manager. |
| hr_manager_id | 20 | ID for HR Manager. |
| approver_manager_one_id | 21 | ID for Approver Manager One. |
| approver_manager_two_id | 22 | ID for Approver Manager Two. |
| approver_manager_three_id | 23 | ID for Approver Manager Three. |
| approver_manager_four_id | 24 | ID for Approver Manager Four. |
Role Privileges
| Field | Order | Definition |
|---|---|---|
| role_code | 1 | Codified name of role. |
| privilege_code | 2 | Codified name of privilege. |
| modified_datetime | 3 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 4 | The short value/code identifying the specific customer. |
User Groups
| Field | Order | Definition |
|---|---|---|
| user_id | 1 | The user ID uniquely identifying the user. |
| group_id | 2 | The user ID uniquely identifying the group. |
| group_code | 3 | The codified name of the group. |
| group_name | 4 | The plain text name of the group. |
| is_active | 5 | Whether of not the user has an active assignment to that group. |
| modified_datetime | 6 | The datetime the user group assignment was last modified. |
| customer_id | 7 | The short value/code identifying the specific customer. |
User Privileges
| Field | Order | Definition |
|---|---|---|
| user_id | 1 | The user ID uniquely identifying the user. |
| privilege_code | 2 | Codified name of privilege. |
| is_grant | 3 | Boolean that explicitly allows or disallows this privilege. False value is primarily used when a user_role is granted but a privilege within that role has been removed for that user. |
| modified_datetime | 4 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 5 | The short value/code identifying the specific customer. |
User Roles
| Field | Order | Definition |
|---|---|---|
| user_id | 1 | The user ID uniquely identifying the user. |
| role_code | 2 | Codified name of role. |
| modified_datetime | 3 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 4 | The short value/code identifying the specific customer. |
Tasks
| Field | Order | Definition |
|---|---|---|
| task_id | 1 | The unique ID for the task. |
| task_formatted_id | 2 | The unique ID - formatted - for the task. |
| title | 3 | The title for the task. |
| due_date | 4 | The due date for the task. |
| status_code | 5 | The tasks status (e.g. draft, in_progress, completed, canceled). |
| category | 6 | A category for the task that is specific to the customer (e.g. mitigation, ad_hoc). |
| type_code | 7 | A type for the task that is specific to the customer (e.g. documentation, consult with and individual, other). |
| submitted_by_user_id | 8 | The user ID of the user who submitted the task. |
| assigned_to_user_id | 9 | The user ID for the user who is assigned to the task. |
| request_id | 10 | The request’s unique identifier that is associated with this task. |
| modified_datetime | 11 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 12 | The short value/code identifying the specific customer. |
| details | 13 | Details of the task. |
| formatted_fk_id | 14 | The formatted id of the foreign key of an entity related to this task (request activity, monitoring request, etc). |
| fk_table_list | 15 | The formatted id of the foreign key of an entity related to this task. |
| fk_table | 16 | The entity table name of the foreign key related to this task. |
| fk_id | 17 | The unique identifier of the foreign key of an entity related to this task. |
| template_code | 18 | The request template code (if associated to a request). |
| automated_task_template_id | 19 | The id of an automated task template if task is an automatically created task. |
| product_code | 20 | The matching request product code (if associated to a request). |
| task_response_id | 21 | The unique identifier of assignee’s response entity. |
| assignee_response | 22 | The assignee’s response. |
Request File Attachments
| Field | Order | Definition |
|---|---|---|
| id | 1 | Unique identifier for the file attachment. |
| fk_table | 2 | The table name associated with the file attachment (request table). |
| fk_id | 3 | The request’s unique identifier that is associated with this file attachment. |
| user_id | 4 | The unique identifier of the user. |
| uploaded_filename | 5 | The filename. |
| uploaded_file_timestamp | 6 | When the filename was created. |
| uploaded_file_size | 7 | The size of the file. |
| uploaded_file_type | 8 | The file type. |
| catalog_filename | 9 | Filename identification. |
| attach_timestamp | 10 | Timestamp of when the file was attached. |
| modified_datetime | 11 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 12 | The short value/code identifying the specific customer. |
| request_id | 13 | The ID which relates to the requests data object (id field). |
| formatted_request_id | 14 | The unique ID - formatted - for the associated request. |
| task_id | 15 | The ID which relates to the tasks data object (id field). |
| formatted_task_id | 16 | The unique ID - formatted - for the associated task. |
Risk Scoring
| Field | Order | Definition |
|---|---|---|
| request_id | 1 | The request’s unique identifier that is associated with this risk score. |
| risk_level | 2 | Overall risk score (e.g. low, medium, high). |
| risk_score_value | 3 | The risk score (decimal point value out of the max risk score value). |
| max_risk_score_value | 4 | The highest risk level possible for this score. |
| modified_datetime | 5 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 6 | The short value/code identifying the specific customer. |
Risk Scoring Criterias
| Field | Order | Definition |
|---|---|---|
| request_id | 1 | The request’s unique identifier that is associated with this risk scoring criteria. |
| default_title | 2 | The label/title of the request’s questionnaire that is related to this individual risk criteria. |
| importance | 3 | Importance weight of this risk criteria for the overall score. |
| potential_value_achievement | 4 | The individual risk criteria’s risk score value, that is used to calculate the overall risk score. |
| response | 5 | The request’s questionnaire response that is related to this individual risk criteria. |
| risk_result | 6 | The individual risk criteria risk level score. |
| template_code | 7 | The Lextegrity template code for the type of request. |
| template_item_code | 8 | The Lextegrity template item code for the request. |
| template_questionnaire_code | 9 | The Lextegrity template questionnaire code for the request. |
| modified_datetime | 10 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 11 | The short value/code identifying the specific customer. |
| risk_area | 12 | The risk area of this risk scoring criteria. |
Risk Scoring Adjustment
| Field | Order | Definition |
|---|---|---|
| request_id | 1 | The request’s unique identifier that is associated with this risk scoring adjustment. |
| customer_id | 2 | The short value/code identifying the specific customer. |
| risk_adjustment_id | 3 | The unique identifier of the risk scoring adjustment. |
| risk_area | 4 | The risk area of this particular adjustment. |
| adjustment_type | 5 | The type of adjustment. |
| categories | 6 | Categories for this adjustment. |
| score_impact | 7 | The score impact that will affect the associated risk. |
| user_id | 8 | The identifier of the user who created this manual risk scoring adjustment. |
| comments | 9 | Any comments a user added when creating this manual adjustment. |
| status | 10 | If this adjustment is active or inactive (boolean of true or false). |
| modified_datetime | 11 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
Request Activity Approval Steps
| Field | Order | Definition |
|---|---|---|
| request_id | 1 | The request’s unique identifier that is associated with this activity approval step record. |
| formatted_request_id | 2 | The request’s unique identifier that is associated with this activity approval step record. |
| primary_activity_status | 3 | Status of the primary activity (approved, canceled, etc). |
| approver_user_id | 4 | The user id of an approver of this activity. |
| approver_is_active | 5 | Indicates if the user is active or has been reassigned (false if they were reassigned). |
| workflow_step_id | 6 | The identifier of the workflow step of approval. |
| workflow_action_type | 7 | The action taken by the approver, empty if they have not taken an action. |
| workflow_action_datetime | 8 | When the action of the approver was taken (empty if no action was taken). |
| request_step_id | 9 | The request step identifier. |
| request_step_order | 10 | Order of request steps, used to determine the order of each approver. |
| request_step_name | 11 | Title of the approver group for this primary activity approval step. |
| modified_datetime | 12 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 13 | The short value/code identifying the specific customer. |
| workflow_user_id | 14 | The identifier of the workflow user. |
| workflow_collaborator_id | 15 | The identifier of the workflow collaborator. |
Request Recipient Approval Steps
| Field | Order | Definition |
|---|---|---|
| request_id | 1 | The request’s unique identifier that is associated with this activity approval step record. |
| formatted_request_id | 2 | The request’s unique identifier that is associated with this activity approval step record. |
| request_party_id | 3 | The request party id associated with this recipient. |
| request_party_status | 4 | The recipient’s overall approval status. |
| recipient_party_id | 5 | The recipient's party id. |
| approver_user_id | 6 | The user id of an approver of this activity. |
| approver_is_active | 7 | Indicates if the user is active or has been reassigned (false if they were reassigned). |
| workflow_step_id | 8 | The identifier of the workflow step of approval. |
| workflow_action_type | 9 | The action taken by the approver, empty if they have not taken an action. |
| workflow_action_datetime | 10 | When the action of the approver was taken (empty if no action was taken). |
| request_step_id | 11 | The request step identifier. |
| request_step_order | 12 | Order of request steps, used to determine the order of each approver. |
| request_step_name | 13 | Title of the approver group for this recipient approval step. |
| modified_datetime | 14 | The date which was used when determining the records to extract. Is the max value between the records insert or update timestamp. |
| customer_id | 15 | The short value/code identifying the specific customer. |
| workflow_user_id | 16 | The identifier of the workflow user. |
| workflow_collaborator_id | 17 | The identifier of the workflow collaborator. |
Extract Diagram

Appendix B: Change Log
Appendix B: Change Log
| Date | Version | Description |
|---|---|---|
| December 1, 2022 | 1.0 | Original version |
| Jan 23, 2023 | 1.0 |
|
| March 28th, 2023 | 1.1 |
Removed Appendix A |
| May 30, 2023 | 1.2 |
Added a new extract for extracting party addresses: party_address These changes make alterations to existing column names and will require updates to downstream consumption.
Added new table extracts:
Updated the following existing table:
|
| June 29, 2023 | 1.3 |
Added new table extracts:
Updated the following existing tables:
|
| Nov 10, 2023 | 1.4 |
Added blank responses from optional questions. Only includes responses going forward.
Added new table extract:
Added risk_area to risk_scoring_criteria extract Added user_id to users extract |
| Dec 20, 2023 | 1.5 | Added forms_of_support and sub_type to the requests extract |
| Feb, 2024 | 1.6 | Added user_groups category extract |
| March 8, 2024 | 1.7 | Added local_amount and local_currency_code to request_activity_parties_linkage |
| March 15, 2024 | 1.8 | Add request_id and formatted_request_id to the request_file_attachments data extract entity. |
| April 26, 2024 | 1.9 | Added request_party_id to the request_activity_parties extract. |
| May 16, 2024 | 2.0 | Added company_name, manager_id, hr_manager_id, approver_manager_one_id, approver_manager_two_id, approver_manager_three_id, and the approver_manager_four_id to the users extract data entity. |
| July 19th, 2024 | 3.5 |
The following fields have been added to the tasks extract details: The following fields have been added to the request file attachments extract: |
| May 30, 2025 | 3.6 |
Added the following fields to the responses extract: request_party_id request_questionnaire_id |
| October 24, 2025 | 4.4 |
Added the following fields to the responses extract: workflow_user_id workflow_collaborator_id |