Pavement Structure (Construction History) is for storing the completed construction projects, their linear locations and material layer information of each construction location. Unlike other dataset, there are 4 system tables used for this function out of the box, and the corresponding window can be navigated to by following the steps below:
- Click Asset Inventory
- Select Construction from the drop-down menu
Table and Window Configuration
The tables used for Construction History data include: PMSX_TCPLAN_NO_LOC, PMSX_TCPLAN_TYP_SEC, PMSX_TCPLAN_LOC and PMSX_TASBUILT. They correspond to panes and tabs in the Construction History window, which can be navigated to by following the steps below:
- Click Asset Inventory
- Select Construction from the drop-down menu
- Select Construction History
PMSX_TCPLAN_NO_LOC table is a general table, with CNTRCT_ID as its primary key. It stores the project-level information about each construction project and does not store any location information. The Treatment type and Completion Year information is also stored in this table.
Out of the box, PMSX_TCPLAN_NO_LOC table has these 10 columns:
| COLUMN_NAME | DATA_TYPE | DATA_LENGTH | NULLABLE | COMMENTS |
|---|---|---|---|---|
CNTRCT_ID | NUMBER | 22 | N | Primary Key of the table, identifying each construction project. A T-list column with label from CNTRCT_NAME. |
CNTRCT_NAME | VARCHAR2 | 400 | Y | Project name for each project. Can be used to calculate when the last construction is performed on a section. |
APPROVED | NUMBER | 22 | Y | Whether the record is approved. |
PMS_TREATMENT_ID | NUMBER | 22 | Y | What treatment is performed in the project. This is a T-list column with the label from PMS_TREATMENT_NAME column in MS_TREATMENT table. |
YEAR_COMPLETION | NUMBER | 22 | N | Project complete year. |
PMS_WORK_CODE_ID | NUMBER | 22 | Y | Work Code is a “category” for treatments, for example, Major Rehab, Minor Rehab, etc. They can be used to determine the performance deterioration model after the construction. This is a T-list column with the label from PMS_WORK_CODE_NAME column in PMS_WORK_CODE table. |
COMMENT_STR | VARCHAR2 | 4000 | Y | Comment of the record |
COMMENT_ID | NUMBER | 22 | Y | Attachment field. |
USER_UPDATE | VARCHAR2 | 400 | Y | Last update user of the record |
DATE_UPDATE | DATE | 7 | Y | Last update date of the record |
PMSX_TCPLAN_TYP_SEC table is a general table, with the combination CNTRCT_ID and PMSX_TCPLAN_TYP_SEC_ID as its unique key. CNTRCT_ID column has a Foreign Key to its parent table PMSX_TCPLAN_NO_LOC. This table stores Typical Sections under each project. A Typical Section is defined as the unique composition of a series of material layers within each project. In other words, within each Typical Section, the material layer compositions are expected to be the same for different locations. Each Typical Section can have multiple locations.
Out of the box, PMSX_TCPLAN_TYP_SEC table has these 6 columns:
| COLUMN_NAME | DATA_TYPE | DATA_LENGTH | NULLABLE | COMMENTS |
|---|---|---|---|---|
PMSX_TCPLAN_TYP_SEC_ID | NUMBER | 22 | N | Primary Key for the table |
PMSX_TCPLAN_TYP_SEC_NAME | VARCHAR2 | 2000 | Y | Name of the typical section |
CNTRCT_ID | NUMBER | 22 | N | Foreign Key to the PMS_TCPLAN_NO_LOC table. |
COMMENT_STR | VARCHAR2 | 4000 | Y | Comment of the record |
USER_UPDATE | VARCHAR2 | 400 | Y | Last update user of the record |
DATE_UPDATE | DATE | 7 | Y | Last update date of the record |
PMSX_TCPLAN_LOC table stores the linear location of each Typical Section under each construction project. CNTRCT_ID and PMS_TCPLAN_TYP_SEC_ID columns have a foreign key constraint to PMS_TCPLAN_TYP_SEC table.
Out of the box, PMSX_TCPLAN_LOC table has these 7 columns:
| COLUMN_NAME | DATA_TYPE | DATA_LENGTH | NULLABLE | COMMENTS |
|---|---|---|---|---|
LOC_IDENT | NUMBER | 22 | N | Location # used in SETUP_LOC_IDENT table to store the record’s location in the LRS. This is a system column. |
CNTRCT_ID | NUMBER | 22 | N | Foreign Key to the PMS_TCPLAN_TYP_SEC parent table. |
PMS_TCPLAN_TYP_SEC_ID | NUMBER | 22 | N | Foreign Key to the PMS_TCPLAN_TYP_SEC parent table. |
COMMENT_ID | NUMBER | 22 | Y | Attachment field. |
COMMENT_STR | VARCHAR2 | 4000 | Y | Comment of the record |
USER_UPDATE | VARCHAR2 | 400 | Y | Last update user of the record |
DATE_UPDATE | DATE | 7 | Y | Last update date of the record |
PMSX_TASBUILT table is a general table, with the combination CNTRCT_ID, PMSX_TCPLAN_TYP_SEC_ID, and LAYER_ID as its unique key. This table stores the material layer information for each typical section under the project (i.e., Within each Typical Section, the material layer composition is expected to be the same). In this table, CNTRCT_ID and PMSX_TCPLAN_TYP_SEC_ID columns have a foreign key to PMSX_TCPLAN_TYP_SEC table, and MTRL_CODE_ID column has a foreign key to the SETUP_MTRL_CODE table.
Out of the box, PMS_TASBUILT table has these 9 columns:
| COLUMN_NAME | DATA_TYPE | DATA_LENGTH | NULLABLE | COMMENTS |
|---|---|---|---|---|
CNTRCT_ID | NUMBER | 22 | N | Foreign Key to the PMS_TCPLAN_TYP_SEC parent table. |
PMSX_TCPLAN_TYP_SEC_ID | NUMBER | 22 | Y | Foreign Key to the PMS_TCPLAN_TYP_SEC parent table. |
LAYER_ID | NUMBER | 22 | N | Layer ID within the Typical Section, e.g., 1,2,3. Note: if a layer is removed in the project (such as milling), code LAYER_ID to be 99 |
MTRL_CODE_ID | NUMBER | 22 | Y | Material Code. This is a T-list column with a foreign key to the SETUP_MTRL_CODE table. |
THICKNESS | NUMBER | 22 | Y | Thickness of the layer, usually in the unit of “Inch”. |
COMMENT_ID | NUMBER | 22 | Y | Attachment field. |
COMMENT_STR | VARCHAR2 | 4000 | Y | Comment of the record |
USER_UPDATE | VARCHAR2 | 400 | Y | Last update user of the record |
DATE_UPDATE | DATE | 7 | Y | Last update date of the record |
Similar to other pavement data, the configuration of Construction History dataset starts by expanding the table structure (most notably, PMSX_TCPLAN_NO_LOC table – because different customer tracks different project-level data elements) to include those data element and then add those columns to the window.
Follow the instruction in AgileAssets System Foundation Configuration Manual on how to Create and Import Lookup Values and Add Columns to a Table in and add them into the corresponding table.
After all the columns have been added to the table, follow the instruction in AgileAssets System Foundation Configuration Manual on how to Add Columns to a Data Window to add those columns to the window.
At this point, the system will have an empty construction history tables and the window will display an empty dataset.
