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 is located under Pavement Analyst > Asset Inventory > Construction menu.

Table and Window Configuration

The tables used for Construction History data include: PMS_TCPLAN_NO_LOC, PMS_TCPLAN_TYP_SEC, PMS_TCPLAN_LOC and PMS_TASBUILT. They correspond to panes and tabs in the Pavement Analyst > Asset Inventory > Construction > Construction History window.

PMS_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, PMS_TCPLAN_NO_LOC table has these 10 columns:

COLUMN_NAMEDATA_TYPEDATA_LENGTHNULLABLECOMMENTS

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

PMS_TCPLAN_TYP_SEC table is a general table, with the combination CNTRCT_ID and PMS_TCPLAN_TYP_SEC_ID as its unique key. CNTRCT_ID column has a Foreign Key to its parent table PMS_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, PMS_TCPLAN_TYP_SEC table has these 6 columns:

COLUMN_NAMEDATA_TYPEDATA_LENGTHNULLABLECOMMENTS

PMS_TCPLAN_TYP_SEC_ID

NUMBER

22

N

Primary Key for the table

PMS_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

PMS_TCPLAN_LOC is a strictly Location-referenced data table, i.e., the data is identified strictly by a location in the LRS network (route with "from" and "to" point boundaries) and has LOC_IDENT column. This 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, PMS_TCPLAN_LOC table has these 7 columns:

COLUMN_NAMEDATA_TYPEDATA_LENGTHNULLABLECOMMENTS

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

Different from other tables, PMS_TCPLAN_LOC has a system trigger placed on the table. With this trigger, whenever a new record is insert into this table, the system will perform a finest partition between the newly entered location record and all other existing location record in the table, and save the results into PMS_PVMNT_FINEST_PARTITION table. The data in this table will then be used in Pavement Structure (Profile / Cross Section) window (located at Pavement Analyst > Asset Inventory > Construction > Pavement Structure (Profile / Cross Section) menu).

PMS_TASBUILT table is a general table, with the combination CNTRCT_ID,  PMS_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 PMS_TCPLAN_TYP_SEC_ID columns have a foreign key to PMS_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_NAMEDATA_TYPEDATA_LENGTHNULLABLECOMMENTS

CNTRCT_ID

NUMBER

22

N

Foreign Key to the PMS_TCPLAN_TYP_SEC parent table.

PMS_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, PMS_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 detailed in “Create and Import Lookup Values” and “Add Columns to a Table”  Chapter in “AgileAssets System Foundation Configuration Guide” and add them into the corresponding table.

After all the columns have been added to the table, follow the instruction detailed in “Add Columns to a Data Window” section in “AgileAssets System Foundation Configuration Guide” 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.

Pavement Structure Graph

Pavement Structure Graph window allows the user to specify a location (a segment of roadway), and displays all the historical construction projects on this location, both along the route and across the route. The window is located under Pavement Analyst > Asset Inventory > Construction > Pavement Structure (Profile / Cross Section) menu.

The window has 3 panes at the top:

  • Location pane, where the use enters the location.
  • Partition pane, where the list of Finest Partitioned project locations that fall within the location entered are displayed. In other words, each partition is homogenous with regard to construction history projects and will not cross different projects or layer compositions.
  • Layers pane, where the Material Layer information from each selected construction project is displayed.

The window has 2 charts at the bottom:

  • Along the Route chart, which graphically displays the layers using the defined color along the route;
  • Across the Route chart, which displays the layer in the cross-section (i.e., different direction of the route) for the selected segment along the route.

Import Data

The next step is to create an Import Configuration and load the data into the table.

Different from Roadway Inventory, Pavement Condition or Traffic data, Construction History import involves multiple steps. Out of the box, the imports are configured as below:

StepImport Target TableDescription

1.      

PMS_TCPLAN_BUFFER table import

This is the buffer table that temporarily holds the data before it is being transformed and imported into the corresponding target tables.


The import uses a csv file with a specific format (detailed below).

2.      

PMS_TCPLAN_NO_LOC table import

This import the "project-level" information for construction history. The import uses the view PMS_TCPLAN_NO_LOC_BUFFER_VW made from PMS_TCPLAN_BUFFER.

3.      

PMS_TCPLAN_TYP_SEC table import

This imports the "Typical Section" for each project. "Typical Section" is the distinctive layer compositions under each project. The import uses the view PMS_TCPLAN_TYP_SEC_BUFFER_VW made from PMS_TCPLAN_BUFFER and PMS_TCPLAN_NO_LOC.

4.      

PMS_TASBUILT table import

This imports the layer composition for each Typical Section. Layer composition include Layer ID (1,2,3, etc.), Material Code and Thickness. The import uses the view PMS_TASBUILT_BUFFER_VW made from PMS_TCPLAN_BUFFER, PMS_TCPLAN_NO_LOC and PMS_TCPLAN_TYP_SEC. Most notably, the view pivots the material data presented in columns into rows.

5.      

PMS_TCPLAN_LOC table import

This imports the locations under one typical section. The import uses the view PMS_TCPLAN_LOC_BUFFER_VW made from PMS_TCPLAN_BUFFER, PMS_TCPLAN_NO_LOC, PMS_TCPLAN_TYP_SEC and PMS_TASBUILT.

6.      

NETWORK_LANES_COUNT table import

This is a system location table that holds all the valid number of lanes and directions at valid directions at all locations in the network (i.e., from a Start Measure to an End Measure on a given route).  The system uses this data to plot the Pavement Structure Graph (detailed in 3.4.3).


The import uses a csv file with a specific format (detailed below).

7.      

Run system job “Pavement Analyst: Update Network_Line_Directions and Network_Line_Lanes”

This system job uses the data from NETWORK_LANES_COUNT to populate Update Network_Line_Directions table, which stores the valid directions at all locations in the LRS network, and Network_Line_Lanes, which stores the valid lanes at all locations in the LRS network.

8.      

Run system job “Pavement Analyst: Recreate Pavement Structure (Profile / Cross Section) Partitions”

Because the locations from each construction history projects do not always line up, this system job creates the partitions  from all the locations in PMS_TCPLAN_LOC table (a Finest Partition process), and allow them to be plotted on the Pavement Structure Graph using homogenous sections.


A system trigger exists on PMS_TCPLAN_LOC table that ensures whenever a new record in manually entered through the Construction History window, this partition process is performed on the new record. This system job can recalculate using the entire dataset when the data is corrupted.

The csv file format for Step 1 above is as follows:

Column HeaderData TypeDescription

APPROVED

Number

1 for Approved.

0 or empty for not approved.


This is a project-level field and maps to the APPROVED field in PMS_TCPLAN_NO_LOC table.

CONTRACT_NAME

String

Contract name.


This is a project-level field and maps to the CNTRCT_NAME field in PMS_TCPLAN_NO_LOC table.

YEAR_COMPLETION

Number

Project name for each project. Can be used to calculate when the last construction is performed on a section.


This is a project-level field and maps to the YEAR_COMPLETION field in PMS_TCPLAN_NO_LOC table.

TREATMENT

String

Treatment of the project – must be in the list of Treatment Library (see Chapter 6 Treatment Library for details).


This is a project-level field and maps to the PMS_TREATMENT_ID field in PMS_TCPLAN_NO_LOC table.

WORK_CODE

String

Work Code of the project – must be in the list from available work code (defined in Pavement Analyst > Setup > Inventory Setup > Work Code window).


This is a project-level field and maps to the PMS_WORK_CODE_ID field in PMS_TCPLAN_NO_LOC table.

ROUTE_NAME

String

Route Identifier in the LRS network

LANE_DIR

Number

1 for Ascending (Cardinal or Primary) direction.

2 for Descending (Non-cardinal or Reverse or Non-Primary) direction.

0 for both directions.

LANE_ID

Number

0 for all lanes. Otherwise the specific lane number (1,2,3, etc.)

OFFSET_FROM

Number

Start Measure of the location

OFFSET_TO

Number

End Measure of the location

MILLING

String

“Milling” if a layer is milled in this project/location. Empty if no milling was performed.

MILLING_THICKNESS

Number

Thickness of the milling if milling is performed.

LAYER_1_MTRL_CODE

String

Layer 1’s material code (defined in Pavement Analyst > Setup > Inventory Setup > Material Code)

LAYER_1_THICKNESS

Number

Layer 1’s thickness.

LAYER_2_MTRL_CODE

String

Layer 2’s material code. Empty if there is no layer 2.

LAYER_2_THICKNESS

Number

Layer 2’s thickness. Empty if there is no layer 2.

LAYER_3_MTRL_CODE

String

Layer 3’s material code. Empty if there is no layer 3.

LAYER_3_THICKNESS

Number

Layer 3’s thickness. Empty if there is no layer 3.

LAYER_4_MTRL_CODE

String

Layer 4’s material code. Empty if there is no layer 4.

LAYER_4_THICKNESS

Number

Layer 4’s thickness. Empty if there is no layer 4.

LAYER_5_MTRL_CODE

String

Layer 5’s material code. Empty if there is no layer 5.

LAYER_5_THICKNESS

Number

Layer 5’s thickness. Empty if there is no layer 5.

LAYER_6_MTRL_CODE

String

Layer 6’s material code. Empty if there is no layer 6.

LAYER_6_THICKNESS

Number

Layer 6’s thickness. Empty if there is no layer 6.

LAYER_7_MTRL_CODE

String

Layer 7’s material code. Empty if there is no layer 7.

LAYER_7_THICKNESS

Number

Layer 7’s thickness. Empty if there is no layer 7.

LAYER_8_MTRL_CODE

String

Layer 8’s material code. Empty if there is no layer 8.

LAYER_8_THICKNESS

Number

Layer 8’s thickness. Empty if there is no layer 8.

LAYER_9_MTRL_CODE

String

Layer 9’s material code. Empty if there is no layer 9.

LAYER_9_THICKNESS

Number

Layer 9’s thickness. Empty if there is no layer 9.

LAYER_10_MTRL_CODE

String

Layer 10’s material code. Empty if there is no layer 10.

LAYER_10_THICKNESS

Number

Layer 10’s thickness. Empty if there is no layer 10.

If there is client-specific configuration to PMS_TCPLAN_NO_LOC table to track additional project-level data, the template can be expanded to include those fields. In addition, the following objects needs to be updated to reflect those changes to ensure all the steps in the import are running correctly:

  • PMS_TCPLAN_BUFFER table and window
  • Import configuration into the PMS_TCPLAN_BUFFER table
  • PMS_TCPLAN_NO_LOC table and window
  • PMS_TCPLAN_NO_LOC_BUFFER_VW view’s logic
  • Import configuration into PMS_TCPLAN_NO_LOC table
  • PMS_TCPLAN_TYP_SEC_BUFFER_VW view’s logic
  • PMS_TASBUILT_BUFFER_VW view’s logic
  • PMS_TCPLAN_LOC_BUFFER_VW’s logic

The csv file format for Step 6 above is as follows:

Column HeaderData TypeDescription

ROUTE_NAME

String

Route Identifier in the LRS network

LANE_DIR

Number

1 for Ascending (Cardinal or Primary) direction.

2 for Descending (Non-cardinal or Reverse or Non-Primary) direction.

Note: Because this dataset identifies all the validation directions in the LRS network, 0 is not allowed in this dataset.

LANE_ID

Number

Use 0 for all lines. Do not include specific lane ID in this dataset.

OFFSET_FROM

Number

Start Measure of the location

OFFSET_TO

Number

End Measure of the location

NUMBER_OF_LANES

String

Total number of lanes in the specified Lane_Dir. If a location (from a Start Measure to an End Measure on a given route) does not have lanes (e.g., divided highway), do not include the location in the dataset.

After running the import and system jobs, the system will have a populated these table and the window will display the corresponding data.

  • No labels

Provide Feedback