Managing Incremental Updates

Textura Data Service supports fetching incremental updates on a daily basis as an alternative to full data fetches. You are responsible for maintaining a local copy of your whole data set.

Notes:

Textura Data Service is unavailable from 12:30 AM to 6:00 AM CT. All calls to the Data Service must be scheduled outside of this window.
All time zones used in the data service and this documentation is Central Time.

DATE_UPDATED and DATE_DELETED Values and Filtering

The DATE_UPDATED columns are used to indicate the last date a record was either updated or added. Only the most current version of any record is provided; a complete log of all updates, additions, and deletions are not available. If a record existed and currently does not, its primary key (or unique key for tables not having a primary key) can be found in the W_T_DELETED_D table.

The DATE_UPDATED and DATE_DELETED values in the Textura Data Service data warehouse have both a date and time (CT). They represent when data has been updated within our internal Data Service ETL (typically early morning hours), not the time at which the data has changed in the upstream source TPM database.

The value passed by the client to Textura Data Service to filter on DATE_UPDATED and DATE_DELETED only supports a date value (the time is set to beginning of the day). The date format is represented as follows: DD-MON-YYYY (e.g. 31-Dec-2021).

The following is an example timeline of events on the Oracle Textura side and suggested logic and timing on the client side:

A new contract is created on 10-Nov-2021.
The overnight Textura Data Service batch process runs the morning of Nov 11th and the DATE_UPDATED for that record is set to 11-Nov-2021 4:00 AM. The exact time depends on when our batch process runs.
Processing is finished and this new data is available to clients at 6:00 AM CT.
The client can fetch data on 11-Nov-2021 with a filter of DATE_UPDATED GREATER_THAN_OR_EQUALS 11-Nov-2021 to return this new record, and any other new or updated records.
The contract is updated later on 11-Nov-2021.
The next overnight Textura Data Service batch process runs the morning of 12-Nov-2021 and DATE_UPDATED for the record is updated to 12-Nov-2021 4:00 AM.
Fetch data with a filter of DATE_UPDATED GREATER_THAN_OR_EQUALS 12-Nov-2021 to return this new record, and any other new or updated records. If incremental changes are needed and the code is not run until a week later, you should filter for changes greater than or equal to 12-Nov-2021.

How to Use Incremental Data Queries

Use the DATE_UPDATED column to fetch records that have been updated or inserted since your last data extract.

For example, your local data warehouse was last updated with a full data extract from the Data Service on 11-Nov-2021. If today's date is 12-Nov-2021, you would fetch all data where the DATE_UPDATED is greater than or equal to 12-Nov-2021 and apply all changes to your local data warehouse.

Example: To fetch all records from W_BILLING_F that have been updated or inserted since the start of Nov. 12, 2021 use the following:

{

"name": "W_BILLING_F",

"pageSize": "100",

"tables": [

{

"tableName": "W_BILLING_F",

"columns": [

"BILLING_OBJECT_ID",

"DATE_UPDATED",

"CONTRACT_OBJECT_ID",

…

"condition": {

"operator": "AND",

"conditions": [

{

"columnName": "DATE_UPDATED",

"operator": "GREATER_THAN_OR_EQUALS",

"value1": "12-Nov-2021"

}

]

}

]

}

For every record that is returned, you will need to implement a merging logic in your data warehouse. Insert or update records by looking for the record in your local data warehouse using the primary or unique key for the given table (See topic Available Data Points in Data Service to view the primary or unique keys for each table). If the record already exists, it is an update. If the record does not exist in your local data warehouse, then do an insert. The data in the Textura Data Service database is only updated once a day, but writing the logic this way is useful in case processing is aborted midway due to any client side, server side or network errors.

Note: Data changes to the following fields CURR_DAYS_ON_HOLD, DAYS_PAST_DUE do not trigger a change to the DATE_UPDATED value.

Deletions

Records that are deleted in Textura are stored in W_T_DELETED_D. You will need to retrieve the records in this table and delete them in your data warehouse to keep data in sync. This table contains all records that have been deleted from all tables. It contains the following columns: TABLE_NAME, DATE_DELETED, OBJECT_ID, OBJECT_ID2, OBJECT_ID3, TIMESTAMP_COL.

Example: To retrieve a list of all records that have been deleted since 12-Nov-2021 use an API such as:

{

"name": "W_T_DELETED_D",

"pageSize": "100",

"tables": [

{

"tableName": "W_T_DELETED_D",

"columns": [

"TABLE_NAME",

"DATE_DELETED",

"OBJECT_ID",

"OBJECT_ID2",

"OBJECT_ID3",

"TIMESTAMP_COL"

"condition": {

"operator": "AND",

"conditions": [

{

"columnName": "DATE_DELETED",

"operator": "GREATER_THAN_OR_EQUALS",

"value1": "12-Nov-2021"

}

]

}

]

}

Every record returned represents a record that has been deleted. TABLE_NAME indicates the table that the record was deleted from. DATE_DELETED indicates the date that it was deleted. The column or columns in the primary or unique key of the record to be deleted maps to columns in W_T_DELETED using the following table:

Columns in Primary/Unique Key	Corresponding W_T_DELETED Columns
W_BILLING_F.BILLING_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_BUDGET_F.CHANGE_ORDER_OBJECT_ID	W_T_DELETED_D.OBJECT_ID2
W_BUDGET_F.CONTRACT_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_CHANGE_ORDER_D.CHANGE_ORDER_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_CONTRACT_D.CONTRACT_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_INVOICE_D.INVOICE_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_INVOICE_HOLD_D.HOLD_PLACED_TIMESTAMP	W_T_DELETED_D.TIMESTAMP_COL
W_INVOICE_HOLD_D.HOLD_TYPE_OBJECT_ID	W_T_DELETED_D.OBJECT_ID2
W_INVOICE_HOLD_D.INVOICE_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_INVOICE_HOLD_D.MANUAL_CONTRACTOR_ID	W_T_DELETED_D.OBJECT_ID3
W_PAYMENT_D.PAYMENT_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_PAYMENT_F.PAYMENT_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_PAYMENT_PROGRAM_D.SETTLEMENT_OBJECT_ID	W_T_DELETED_D.OBJECT_ID
W_T_PROJECT_D.PROJECT_OBJECT_ID	W_T_DELETED_D.OBJECT_ID

Note that there are two tables (W_INVOICE_HOLD_D and W_BUDGET_F) with a unique key consisting of multiple columns.

Deleted Projects

Deletion of projects (W_T_PROJECT_D) are handled differently than other data, and are not tracked in the W_T_DELETED_D table. Projects can be deleted in TPM if they have no draws or subcontracts. Projects are identified with a Deleted status. We recommend you maintain the deletion status in your data warehouse or you can remove deleted projects from your data warehouse.

Last Published Tuesday, February 6, 2024