Identification of Utility Conflicts and Solutions (2012)

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

136 Prototype Database Queries Introduction This appendix describes the process followed to replicate the following utility conflict matrix (UCM) examples: • Prototype UCM; • Alaska Department of Transportation and Public Facili- ties (Alaska DOT&PF) UCM; • California DOT (Caltrans) UCM; • Georgia DOT (GDOT) UCM; and • Texas DOT (TxDOT) UCM. Replicating a UCM involved developing several queries. The purpose of the queries was to demonstrate the feasibility of the data model and to illustrate conceptually the basic steps to replicate the UCMs. As a result, the queries might not be optimized for a production-level database implementa- tion. Readers should also note that the queries described below constitute a very small sample of the nearly endless options that might be available for developing queries and reports to satisfy the typical needs of a state DOT. Prototype UCM Example The prototype UCM in Figure D.1 includes 23 data items (eight data items in the header and 15 data items in the main body). The prototype UCM subsheet in Figure D.2 includes 25 data items (13 data items in the header and 12 data items in the subsheet body). Replicating the prototype UCM and subsheets for three sample utility conflicts involved develop- ing 10 queries and four reports. Step 1: Create “UCM 1 Estimated Completion Date” Query This query retrieves utility conflicts that have been identified (utility conflict event type = 0) or that have an estimated adjustment completion date (utility conflict event type = 16) for a specific project (Figure D.3). The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT_EVNT.UTIL_CNFLT_ID, UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID, UTIL_CNFLT_EVNT.UTIL_ CNFLT_EVNT_DT FROM UTIL_CNFLT INNER JOIN UTIL_CNFLT_EVNT ON UTIL_CNFLT.UTIL_ CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=123456789) AND ((UTIL_CNFLT_ EVNT.UTIL_CNFLT_EVNT_TYPE_ID)=0 Or (UTIL_CNFLT_EVNT.UTIL_CNFLT_ EVNT_TYPE_ID)=16)); Step 2: Create “UCM 2 Estimated Completion Date” Query This query uses the query created in Step 1 to display every utility conflict for a project with the estimated completion date if that date was entered for the utility conflict. If the esti- mated completion date was not entered for a utility conflict, the query displays the utility conflict record without an entry in the utility conflict event date column (Figure D.4). The SQL statement is as follows: SELECT UC1.DOT_PROJ_NBR, UC1.UTIL_CNFLT_ID, UC1.UTIL_CNFLT_ EVNT_TYPE_ID, IIf(UC1.UTIL_CNFLT_EVNT_TYPE_ID=16,UC1.UTIL_ CNFLT_EVNT_DT,'') AS UTIL_CNFLT_EVNT_DT FROM [UCM 1 Estimated Completion Date] AS UC1 INNER JOIN (SELECT [UCM 1 Estimated Completion Date].DOT_PROJ_NBR, [UCM 1 Estimated Completion Date].UTIL_CNFLT_ID, Max([UCM 1 Estimated Completion Date].UTIL_CNFLT_EVNT_TYPE_ID) AS MaxOfUTIL_CNFLT_EVNT_TYPE_ID FROM [UCM 1 Estimated Completion Date] GROUP BY [UCM 1 Estimated Completion Date].DOT_PROJ_NBR, [UCM 1 Estimated Completion Date].UTIL_CNFLT_ID) AS UC2 ON (UC1.UTIL_ CNFLT_ID = UC2.UTIL_CNFLT_ID) AND (UC1.UTIL_CNFLT_EVNT_TYPE_ID = UC2.MaxOfUTIL_CNFLT_EVNT_TYPE_ID); Step 3: Create “UCM 1 Multiple Utility Conflict Status” Query This query selects all utility conflicts associated with a project that have a utility conflict event type ID equal to 0, 2, 3, or 28 (Figure D.5). The corresponding utility conflict event type names are utility conflict created, utility owner informed of utility conflict, utility conflict resolved, and utility conflict resolution strategy selected. (Table C.3 in Appendix C lists all the valid utility conflict event types in the data model.) This a P P E n d I x d

137 Figure D.1. Prototype UCM.

138 Figure D.2. Prototype UCM subsheet: Cost estimate analysis for utility conflict resolution alternatives. Project Owner: Cost Estimate Analysis Developed/Revised By Project No. : Date Project Description: Reviewed By Highway or Route: Date Utility Conflict: Utility Owner: Utility Type: Size and/or Material: Project Phase: Alternative Number Alternative Advantage Alternative Disadvantage Responsible Party Engineering Cost (Utility) Direct Cost (Utility) Engineering Cost (DOT) Direct Cost (DOT) Total Cost Feasibility Decision Alternative Description group of utility conflict event types serves as an example of how a state DOT might track the status of a utility conflict (other combinations are certainly possible). The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_ CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID, UTIL_CNFLT_EVNT.UTIL_ CNFLT_EVNT_TS, UTIL_CNFLT_EVNT_TYPE.UTIL_CNFLT_EVNT_TYPE_NM FROM UTIL_CNFLT INNER JOIN (UTIL_CNFLT_EVNT_TYPE INNER JOIN UTIL_ CNFLT_EVNT ON UTIL_CNFLT_EVNT_TYPE.UTIL_CNFLT_EVNT_TYPE_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID) ON UTIL_CNFLT.UTIL_ CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID GROUP BY UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_ CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID, UTIL_CNFLT_EVNT.UTIL_ CNFLT_EVNT_TS, UTIL_CNFLT_EVNT_TYPE.UTIL_CNFLT_EVNT_TYPE_NM HAVING (((UTIL_CNFLT.DOT_PROJ_NBR)=123456789) AND ((UTIL_CNFLT_ EVNT.UTIL_CNFLT_EVNT_TYPE_ID)=0 Or (UTIL_CNFLT_EVNT.UTIL_CNFLT_ EVNT_TYPE_ID)=2 Or (UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID)=3 Or (UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID)=28)); Step 4: Create “UCM 1 Plan Document Sheet Number” Query This query retrieves the sheet number and sheet group ID for plan documents that are associated with utility conflicts in a project (Figure D.6). The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT.DOT_PROJ_NBR, PLAN_ DCMNT.PLAN_DCMNT_SHT_NBR, PLAN_DCMNT.SHT_GRP_ID FROM UTIL_CNFLT INNER JOIN (UTIL_CNFLT_EVNT INNER JOIN ((DCMNT INNER JOIN PLAN_DCMNT ON DCMNT.DCMNT_ID = PLAN_DCMNT. DCMNT_ID) INNER JOIN UTIL_CNFLT_EVNT_DCMNT ON DCMNT.DCMNT_ ID = UTIL_CNFLT_EVNT_DCMNT.DCMNT_ID) ON UTIL_CNFLT_EVNT.UTIL_ CNFLT_EVNT_NBR = UTIL_CNFLT_EVNT_DCMNT.UTIL_CNFLT_EVNT_NBR) ON UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=123456789)); Step 5: Create “UCM 2 Utility Conflict Status” Query This query uses the query from Step 3 to retrieve the highest utility conflict event type order code, which specifies the order of events for a utility conflict status, and the corresponding utility conflict event date for each utility conflict (Figure D.7). The SQL statement is as follows: SELECT [UCM 1 Multiple Utility Conflict Status].UTIL_CNFLT_ID, [UCM 1 Multiple Utility Conflict Status].UTIL_CNFLT_EVNT_TYPE_ID, [UCM 1 Multiple Utility Conflict Status].UC_EVNT_TYPE_ORDR_CD, [UCM 1 Multiple Utility Conflict Status].UTIL_CNFLT_EVNT_DT, [UCM 1 Multiple Utility Conflict Status]. UTIL_CNFLT_EVNT_TYPE_NM

139 Figure D.4. Design view of “UCM 2 estimated completion date” query. Figure D.3. Design view of “UCM 1 estimated completion date” query. FROM [UCM 1 Multiple Utility Conflict Status] WHERE ((([UCM 1 Multiple Utility Conflict Status].UC_EVNT_TYPE_ORDR_ CD)=(SELECT MAX (UC2.UC_EVNT_TYPE_ORDR_CD) FROM [UCM 1 Multiple Utility Conflict Status] AS UC2 WHERE [UCM 1 Multiple Utility Conflict Status].UTIL_CNFLT_ID = UC2. UTIL_CNFLT_ID))); Step 6: Create “UCM 3” Query This query uses the queries from Steps 2, 4, and 5 to retrieve a list of utility conflicts for a specific project (Figure D.8). The SQL statement is as follows: SELECT CMPNY.CMPNY_NM, DOT_PROJ.DOT_PROJ_NBR, UTIL_CNFLT. UTIL_CNFLT_ID, UTIL_CNFLT.UTIL_CNFLT_NBR, UTIL_FCLTY_TYPE. UTIL_FCLTY_TYPE_NM, UTIL_CNFLT.UTIL_CNFLT_START_STATN_MS, UTIL_CNFLT.UTIL_CNFLT_END_STATN_MS, UTIL_CNFLT.UTIL_CNFLT_ START_OFFST_MS, UTIL_CNFLT.UTIL_CNFLT_END_OFFST_MS, UTIL_ CNFLT_RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_DSCR, UTIL_ CNFLT_RESOLN_ALTERNAT.UCR_ALTERNAT_DCSN_ID, STATE.STATE_ DOT_NM, DOT_PROJ.DOT_PROJ_NBR, DOT_PROJ.DOT_PROJ_ DSCR, HWY_SYS.HWY_SYS_NM, HWY_FUNCL_CLASS.HWY_FUNCL_ CLASS_CD, HWY_SYS.HWY_SYS_NBR, UTIL_FCLTY_MTRL.UTIL_ FCLTY_MTRL_NM, UTIL_CNFLT_INVESTIGATION_NEED_TYPE.UC_ INVESTIGATION_NEED_TYPE_NM, UTIL_CNFLT.UITH_ID, UTIL_ CNFLT_RESOLN_ALTERNAT_RSPNBL.UCR_ALTERNAT_RSPNBL_CD, [UCM 2 Estimated Completion Date].UTIL_CNFLT_EVNT_DT, [UCM 2 Utility Conflict Status].UTIL_CNFLT_EVNT_TYPE_NM, [UCM 1 Plan Document Sheet Number].PLAN_DCMNT_SHT_NBR, UTIL_CNFLT.UTIL_ CNFLT_WRK_DSCR FROM (STATE INNER JOIN (UTIL_FCLTY_TYPE INNER JOIN (UTIL_FCLTY_ MTRL INNER JOIN ((CMPNY INNER JOIN UTIL_FCLTY ON CMPNY.

140 Figure D.5. Design view of “UCM 1 multiple utility conflict status” query. Figure D.6. Design view of “UCM 1 plan document sheet number” query.

141 UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_RESOLN_ALTERNAT.UTIL_ CNFLT_ID WHERE (((UTIL_CNFLT_RESOLN_ALTERNAT.UCR_ALTERNAT_DCSN_ID)=1) AND ((DOT_PROJ.DOT_PROJ_NBR)=123456789)); Step 7: Create “UCM 1 Estimates Crosstab” Query This query produces a list of all estimates for a utility conflict resolution alternative in one row, adding more rows to the out- put for each utility conflict resolution alternative that exists for a utility conflict. Multiplying the total number of alternatives by the number of utility conflicts for a project equals the total num- ber of rows in this query. Figure D.9 provides a design view of the query; the estimate type name is the column heading in this query, and all other data items are selected as row headings. CMPNY_ID = UTIL_FCLTY.CMPNY_ID) INNER JOIN (UTIL_CNFLT_ INVESTIGATION_NEED_TYPE INNER JOIN (HWY_FUNCL_CLASS INNER JOIN (HWY_SYS INNER JOIN (((UTIL_CNFLT INNER JOIN [UCM 2 Utility Conflict Status] ON UTIL_CNFLT.UTIL_CNFLT_ID = [UCM 2 Utility Conflict Status].UTIL_CNFLT_ID) INNER JOIN [UCM 1 Plan Document Sheet Number] ON UTIL_CNFLT.UTIL_CNFLT_ID = [UCM 1 Plan Document Sheet Number].UTIL_CNFLT_ID) INNER JOIN (DOT_PROJ INNER JOIN [UCM 2 Estimated Completion Date] ON DOT_PROJ.DOT_PROJ_NBR = [UCM 2 Estimated Completion Date].DOT_PROJ_NBR) ON (DOT_PROJ. DOT_PROJ_NBR = UTIL_CNFLT.DOT_PROJ_NBR) AND (UTIL_CNFLT. UTIL_CNFLT_ID = [UCM 2 Estimated Completion Date].UTIL_CNFLT_ID)) ON HWY_SYS.HWY_SYS_ID = DOT_PROJ.HWY_SYS_ID) ON HWY_ FUNCL_CLASS.HWY_FUNCL_CLASS_ID = HWY_SYS.HWY_FUNCL_ CLASS_ID) ON UTIL_CNFLT_INVESTIGATION_NEED_TYPE.UC_ INVESTIGATION_NEED_TYPE_ID = UTIL_CNFLT.UC_INVESTIGATION_ NEED_TYPE_ID) ON UTIL_FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_ FCLTY_ID) ON UTIL_FCLTY_MTRL.UTIL_FCLTY_MTRL_ID = UTIL_FCLTY. UTIL_FCLTY_MTRL_ID) ON UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_TYPE_ID) ON STATE.STATE_ID = DOT_PROJ. STATE_ID) INNER JOIN (UTIL_CNFLT_RESOLN_ALTERNAT_RSPNBL INNER JOIN UTIL_CNFLT_RESOLN_ALTERNAT ON UTIL_CNFLT_ RESOLN_ALTERNAT_RSPNBL.UCR_ALTERNAT_RSPNBL_ID = UTIL_ CNFLT_RESOLN_ALTERNAT.UCR_ALTERNAT_RSPNBL_ID) ON Figure D.7. Design view of “UCM 2 utility conflict status” query. Figure D.8. Design view of “UCM 3” query.

142 RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_FSBL_FLAG, UTIL_ CNFLT_RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_ADVANTAGE_ TXT, UTIL_CNFLT_RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_ DISADVANTAGE_TXT, UTIL_CNFLT_RESOLN_ALTERNAT_DCSN.UCR_ ALTERNAT_DCSN_NM, UTIL_CNFLT_RESOLN_ALTERNAT.UC_RESOLN_ ALTERNAT_RSPNBL_CD, UTIL_CNFLT_RESOLN_ALTERNAT_RSPNBL. UCR_ALTERNAT_RSPNBL_NM PIVOT ESTMT_TYPE.ESTMT_TYPE_NM; Step 8: Create “UCM 2 Alternative Analysis UC34” Query This query produces a list of utility conflict resolution alter- natives and related cost estimates for a specific conflict, in this case utility conflict number 34 (UC34). This query effectively reduces the list of alternatives and cost estimates for all utility conflicts that was created in Step 7 to a list of alternatives and cost estimates for one conflict. This query is the foundation of a subsheet that will be linked to the main UCM. Thus, a separate query like the one created in this step will need to be created for every utility conflict in the UCM. Figure D.10 shows a design view of the query. For illustration purposes, two additional queries are included in the database that fol- low the same structure, one for utility conflict number 2 (“UCM 2 alternative analysis UC35”), and one for utility The SQL statement is as follows: TRANSFORM Sum(ESTMT.ESTMT_COST_AMT) AS SumOfESTMT_COST_ AMT SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_ CNFLT_RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_NBR, UTIL_CNFLT_ RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_DSCR, PROJ_DVLP_PRCS_ PHASE.PROJ_DVLP_PRCS_PHASE_NM, UTIL_CNFLT_RESOLN_ALTERNAT. UC_RESOLN_ALTERNAT_FSBL_FLAG, UTIL_CNFLT_RESOLN_ALTERNAT. UC_RESOLN_ALTERNAT_ADVANTAGE_TXT, UTIL_CNFLT_RESOLN_ ALTERNAT.UC_RESOLN_ALTERNAT_DISADVANTAGE_TXT, UTIL_CNFLT_ RESOLN_ALTERNAT_DCSN.UCR_ALTERNAT_DCSN_NM, UTIL_CNFLT_ RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_RSPNBL_CD, UTIL_CNFLT_ RESOLN_ALTERNAT_RSPNBL.UCR_ALTERNAT_RSPNBL_NM FROM UTIL_CNFLT INNER JOIN ((UTIL_CNFLT_RESOLN_ALTERNAT_RSPNBL INNER JOIN (UTIL_CNFLT_RESOLN_ALTERNAT_DCSN INNER JOIN UTIL_ CNFLT_RESOLN_ALTERNAT ON UTIL_CNFLT_RESOLN_ALTERNAT_DCSN. UCR_ALTERNAT_DCSN_ID = UTIL_CNFLT_RESOLN_ALTERNAT.UCR_ ALTERNAT_DCSN_ID) ON UTIL_CNFLT_RESOLN_ALTERNAT_RSPNBL. UCR_ALTERNAT_RSPNBL_ID = UTIL_CNFLT_RESOLN_ALTERNAT.UCR_ ALTERNAT_RSPNBL_ID) INNER JOIN (PROJ_DVLP_PRCS_PHASE INNER JOIN (ESTMT_TYPE INNER JOIN ESTMT ON ESTMT_TYPE.ESTMT_TYPE_ ID = ESTMT.ESTMT_TYPE_ID) ON PROJ_DVLP_PRCS_PHASE.PROJ_ DVLP_PRCS_PHASE_ID = ESTMT.PROJ_DVLP_PRCS_PHASE_ID) ON UTIL_CNFLT_RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_ID = ESTMT. UTIL_CNFLT_RESOLN_ALTERNAT_ID) ON (UTIL_CNFLT.UTIL_CNFLT_ ID = UTIL_CNFLT_RESOLN_ALTERNAT.UTIL_CNFLT_ID) AND (UTIL_ CNFLT.UTIL_CNFLT_ID = ESTMT.UTIL_CNFLT_ID) WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=123456789)) GROUP BY UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT_RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_NBR, UTIL_ CNFLT_RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_DSCR, PROJ_ DVLP_PRCS_PHASE.PROJ_DVLP_PRCS_PHASE_NM, UTIL_CNFLT_ Figure D.9. Design view of “UCM 1 estimates crosstab” query.

143 CMPNY_ID = UTIL_FCLTY.CMPNY_ID) INNER JOIN (HWY_FUNCL_CLASS INNER JOIN (HWY_SYS INNER JOIN (DOT_PROJ INNER JOIN ([UCM 1 Estimates Crosstab] INNER JOIN UTIL_CNFLT ON [UCM 1 Estimates Crosstab].UTIL_CNFLT_ID = UTIL_CNFLT.UTIL_CNFLT_ID) ON (DOT_PROJ. DOT_PROJ_NBR = UTIL_CNFLT.DOT_PROJ_NBR) AND (DOT_PROJ.DOT_ PROJ_NBR = [UCM 1 Estimates Crosstab].DOT_PROJ_NBR)) ON HWY_ SYS.HWY_SYS_ID = DOT_PROJ.HWY_SYS_ID) ON HWY_FUNCL_CLASS. HWY_FUNCL_CLASS_ID = HWY_SYS.HWY_FUNCL_CLASS_ID) ON UTIL_ FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_FCLTY_ID) ON UTIL_FCLTY_ MTRL.UTIL_FCLTY_MTRL_ID = UTIL_FCLTY.UTIL_FCLTY_MTRL_ID) ON UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_ TYPE_ID) ON STATE.STATE_ID = DOT_PROJ.STATE_ID WHERE (((UTIL_CNFLT.UTIL_CNFLT_NBR)=1)); Step 9: Create “UCM” Subsheet Report This report, which is shown in Figure D.11, replicates the sub- sheet for the prototype UCM based on the results of the query from Step 8. All data items provided in the report are selected from database entries, with the exception of the total cost field, which calculates a summation on the fly, and the names (and corresponding dates) of the individuals who developed, revised, or reviewed the UCM report. These data items could be inserted dynamically through a dialog box at the time of preparing, revising, or reviewing the document. The report also formats certain data items—for example, the project number stored as 123456789 in the database becomes 1234-56-789 in the report. conflict number 11 (“UCM 2 alternative analysis UC44”). Note that the query includes a total cost field that does not retrieve data from the database, but calculates data on the fly to give a summation of the following estimates: direct cost to utility plus direct cost to DOT plus engineering cost to utility plus engineering cost to DOT. The SQL statement is as follows: SELECT [UCM 1 Estimates Crosstab].DOT_PROJ_NBR, STATE.STATE_DOT_NM, UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_NM, DOT_PROJ.DOT_PROJ_DSCR, UTIL_FCLTY_MTRL.UTIL_FCLTY_MTRL_NM, CMPNY.CMPNY_NM, HWY_ SYS.HWY_SYS_NBR, HWY_SYS.HWY_SYS_NM, HWY_FUNCL_CLASS. HWY_FUNCL_CLASS_CD, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT.UTIL_ CNFLT_NBR, [UCM 1 Estimates Crosstab].UC_RESOLN_ALTERNAT_DSCR AS [Alternative Description], [UCM 1 Estimates Crosstab].UC_RESOLN_ ALTERNAT_NBR AS [Alternative Number], [UCM 1 Estimates Crosstab].[Direct Cost to Utility Estimate], [UCM 1 Estimates Crosstab].[Engineering Cost to Utility Estimate], [UCM 1 Estimates Crosstab].[Direct Cost to DOT Estimate], [UCM 1 Estimates Crosstab].[Engineering Cost to DOT Estimate], [Direct Cost to Utility Estimate]+[Engineering Cost to Utility Estimate]+[Direct Cost to DOT Estimate]+[Engineering Cost to DOT Estimate] AS [Total Cost], [UCM 1 Estimates Crosstab].PROJ_DVLP_PRCS_PHASE_NM AS [Project Phase], [UCM 1 Estimates Crosstab].UC_RESOLN_ALTERNAT_FSBL_FLAG AS Feasibility, [UCM 1 Estimates Crosstab].UC_RESOLN_ALTERNAT_ ADVANTAGE_TXT AS [Alternative Advantage], [UCM 1 Estimates Crosstab]. UC_RESOLN_ALTERNAT_DISADVANTAGE_TXT AS [Alternative Disadvantage], [UCM 1 Estimates Crosstab].UCR_ALTERNAT_DCSN_NM AS Decision, [UCM 1 Estimates Crosstab].UC_RESOLN_ALTERNAT_ RSPNBL_CD, [UCM 1 Estimates Crosstab].UCR_ALTERNAT_RSPNBL_NM FROM STATE INNER JOIN (UTIL_FCLTY_TYPE INNER JOIN (UTIL_FCLTY_ MTRL INNER JOIN ((CMPNY INNER JOIN UTIL_FCLTY ON CMPNY. Figure D.10. Design view of “UCM 2 alternative analysis UC34” query.

144 Figure D.11. Utility conflict resolution alternatives: Cost estimate analysis (using hypothetical data from TxDOT Katy Freeway project).

145 The SQL statement is as follows: TRANSFORM Sum(ESTMT.ESTMT_COST_AMT) AS SumOfESTMT_COST_AMT SELECT UTIL_CNFLT.UTIL_CNFLT_ID FROM (UTIL_FCLTY_TYPE INNER JOIN (UTIL_FCLTY INNER JOIN UTIL_CNFLT ON UTIL_FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_FCLTY_ID) ON UTIL_ FCLTY_TYPE.UTIL_FCLTY_TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_TYPE_ID) INNER JOIN (ESTMT_TYPE INNER JOIN ESTMT ON ESTMT_TYPE.ESTMT_ TYPE_ID = ESTMT.ESTMT_TYPE_ID) ON UTIL_CNFLT.UTIL_CNFLT_ID = ESTMT.UTIL_CNFLT_ID WHERE (((UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID)=2) AND ((UTIL_CNFLT. DOT_PROJ_NBR)=50898)) GROUP BY UTIL_CNFLT.UTIL_CNFLT_ID PIVOT ESTMT_TYPE.ESTMT_TYPE_NM; Step 3: Create “Alaska 2 Total Distribution” Query This query uses the Step 1 crosstab query and produces a total of the adjustment cost and engineering cost estimates for each distribution utility conflict (Figure D.14). The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, [Adjustment Cost Estimate]+[Engineering Cost Estimate] AS [Total Cost] FROM UTIL_CNFLT INNER JOIN [Alaska 1 Distribution Crosstab] ON UTIL_ CNFLT.UTIL_CNFLT_ID = [Alaska 1 Distribution Crosstab].UTIL_CNFLT_ID; Step 4: Create “Alaska 2 Total Transmission” Query This query uses the Step 2 crosstab query and produces a total of the adjustment cost and engineering cost estimates for each transmission utility conflict. The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, [Adjustment Cost Estimate]+[Engineering Cost Estimate] AS [Total Cost] FROM UTIL_CNFLT INNER JOIN [Alaska 1 Transmission Crosstab] ON UTIL_ CNFLT.UTIL_CNFLT_ID = [Alaska 1 Transmission Crosstab].UTIL_CNFLT_ID; Step 5: Ceate “Alaska 3 Distribution Cost” Query This query uses the queries from Steps 1 and 3 to retrieve engineering cost and adjustment cost estimates, as well as total electric distribution utility conflict costs (Figure D.15). The query selects all utility conflicts with a utility conflict event type = 28, which indicates a utility conflict resolution strategy has been selected for a utility conflict (see Table C.3 in Appendix C). The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, [Alaska 1 Distribution Crosstab]. [Adjustment Cost Estimate], [Alaska 1 Distribution Crosstab].[Engineering Cost Estimate], [Alaska 2 Total Distribution].[Total Cost] FROM ((UTIL_CNFLT INNER JOIN [Alaska 2 Total Distribution] ON UTIL_CNFLT. UTIL_CNFLT_ID = [Alaska 2 Total Distribution].UTIL_CNFLT_ID) INNER JOIN [Alaska 1 Distribution Crosstab] ON UTIL_CNFLT.UTIL_CNFLT_ID = [Alaska 1 Distribution Crosstab].UTIL_CNFLT_ID) INNER JOIN UTIL_CNFLT_EVNT ON UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID WHERE (((UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID)=28)) GROUP BY UTIL_CNFLT.UTIL_CNFLT_ID, [Alaska 1 Distribution Crosstab]. [Adjustment Cost Estimate], [Alaska 1 Distribution Crosstab].[Engineering Cost Estimate], [Alaska 2 Total Distribution].[Total Cost]; Step 10: Create “UCM” Report This report replicates the prototype UCM based on the results of the query from Step 8 and is shown in Figure D.12. All data items provided in the report are selected from database entries. The only exception is the names (and corresponding dates) of the individuals who developed, revised, or reviewed the UCM report. These data items could be inserted dynami- cally through a dialog box at the time of preparing, revising, or reviewing the document. The report also formats certain data items—for example, a station stored as 2100 in the data- base becomes 21+00 in the report. alaska department of Transportation and Public Facilities UCM Example The sample UCM provided by Alaska DOT&PF is included in Appendix B (Figure B.1). This UCM, which includes 19 data items (five data items in the header and 14 data items in the main body), is an example of a UCM with an average number of data items. The Alaska UCM offered a number of unique challenges. First, it included totals, subtotals, and grand totals of cost data elements, and it was of interest to derive these values dynamically through query calculations. Second, this UCM grouped cost data according to whether the utility installations involved were distribution or trans- mission facilities. Replicating the sample UCM involved developing 13 queries and three reports. Step 1: Create “Alaska 1 Distribution Crosstab” Query This query selects two types of electric distribution utility facilities for a specific project and produces a list of the adjust- ment cost estimate and the engineering cost estimate by utility conflict ID (Figure D.13). The SQL statement is as follows: TRANSFORM Sum(ESTMT.ESTMT_COST_AMT) AS SumOfESTMT_COST_AMT SELECT UTIL_CNFLT.UTIL_CNFLT_ID FROM (UTIL_FCLTY_TYPE INNER JOIN (UTIL_FCLTY INNER JOIN UTIL_CNFLT ON UTIL_FCLTY.UTIL_FCLTY_ID=UTIL_CNFLT.UTIL_FCLTY_ID) ON UTIL_ FCLTY_TYPE.UTIL_FCLTY_TYPE_ID=UTIL_FCLTY.UTIL_FCLTY_TYPE_ID) INNER JOIN (ESTMT_TYPE INNER JOIN ESTMT ON ESTMT_TYPE.ESTMT_ TYPE_ID=ESTMT.ESTMT_TYPE_ID) ON UTIL_CNFLT.UTIL_CNFLT_ ID=ESTMT.UTIL_CNFLT_ID WHERE (((UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID)=0 Or (UTIL_FCLTY_TYPE. UTIL_FCLTY_TYPE_ID)=1) AND ((UTIL_CNFLT.DOT_PROJ_NBR)=50898)) GROUP BY UTIL_CNFLT.UTIL_CNFLT_ID PIVOT ESTMT_TYPE.ESTMT_TYPE_NM; Step 2: Create “Alaska 1 Transmission Crosstab” Query This query is similar to the previous query except that it selects electricity transmission utility facilities.

146 Figure D.12. Prototype UCM report.

147 The SQL statement is as follows: SELECT Sum([Alaska 3 Distribution Cost].[Adjustment Cost Estimate]) AS [SumOfAdjustment Cost Estimate], Sum([Alaska 3 Distribution Cost]. [Engineering Cost Estimate]) AS [SumOfEngineering Cost Estimate], Sum([Alaska 3 Distribution Cost].[Total Cost]) AS [SumOfTotal Cost] FROM [Alaska 3 Distribution Cost]; Step 8: Create “Alaska 4 Transmission Subtotal” Query This query is similar to the previous query, except that it uses the query from Step 6 in connection with transmission utility conflicts. The SQL statement is as follows: SELECT Sum([Alaska 3 Transmission Cost].[Adjustment Cost Estimate]) AS [SumOfAdjustment Cost Estimate], Sum([Alaska 3 Transmission Cost]. [Engineering Cost Estimate]) AS [SumOfEngineering Cost Estimate], Sum([Alaska 3 Transmission Cost].[Total Cost]) AS [SumOfTotal Cost] FROM [Alaska 3 Transmission Cost]; Step 9: Create “Alaska 5 AC Total” Query This query uses the queries from Steps 7 and 8 to retrieve totals of adjustment costs for both distribution and transmission utility conflicts. The total adjustment cost is calculated by Step 6: Create “Alaska 3 Transmission Cost” Query This query is similar to the previous query except that it uses the queries from Steps 2 and 4 in connection with electric transmis- sion utility conflicts. The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, [Alaska 1 Transmission Crosstab]. [Adjustment Cost Estimate], [Alaska 1 Transmission Crosstab].[Engineering Cost Estimate], [Alaska 2 Total Transmission].[Total Cost] FROM ((UTIL_CNFLT INNER JOIN [Alaska 2 Total Transmission] ON UTIL_ CNFLT.UTIL_CNFLT_ID = [Alaska 2 Total Transmission].UTIL_CNFLT_ID) INNER JOIN [Alaska 1 Transmission Crosstab] ON UTIL_CNFLT.UTIL_ CNFLT_ID = [Alaska 1 Transmission Crosstab].UTIL_CNFLT_ID) INNER JOIN UTIL_CNFLT_EVNT ON UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_EVNT. UTIL_CNFLT_ID WHERE (((UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID)=28)) GROUP BY UTIL_CNFLT.UTIL_CNFLT_ID, [Alaska 1 Transmission Crosstab]. [Adjustment Cost Estimate], [Alaska 1 Transmission Crosstab].[Engineering Cost Estimate], [Alaska 2 Total Transmission].[Total Cost]; Step 7: Create “Alaska 4 Distribution Subtotal” Query This query uses the query from Step 5 to calculate subtotals of adjustment and engineering cost estimates, as well as the total cost of electric distribution utility conflicts (Figure D.16). Figure D.13. Design view of “Alaska 1 distribution crosstab” query.

148 Figure D.14. Design view of “Alaska 2 total distribution” query. adding total distribution adjustment cost and total transmis- sion adjustment cost (Figure D.17). The SQL statement is as follows: SELECT [Alaska 4 Distribution Subtotal].[SumOfAdjustment Cost Estimate] AS [Distribution Adjustment Cost], [Alaska 4 Transmission Subtotal].[SumOf Adjustment Cost Estimate] AS [Transmission Adjustment Cost], [Alaska 4 Distribution Subtotal.SumOfAdjustment Cost Estimate]+[Alaska 4 Transmission Subtotal.SumOfAdjustment Cost Estimate] AS [Total Adjustment Cost] FROM [Alaska 4 Transmission Subtotal], [Alaska 4 Distribution Subtotal]; Step 10: Create “Alaska 5 EC Total” Query This query uses the queries from Steps 7 and 8 and produces totals of engineering costs for both distribution and transmis- sion utility conflicts. The total engineering cost is calculated by adding the distribution engineering cost total to the transmis- sion engineering cost total (Figure D.18). The SQL statement is as follows: SELECT [Alaska 4 Distribution Subtotal].[SumOfEngineering Cost Estimate] AS [Distribution Engineering], [Alaska 4 Distribution Subtotal.SumOfEngineering Cost Estimate]+[Alaska 4 Transmission Subtotal.SumOfEngineering Cost Estimate] AS [Total Engineering Cost] FROM [Alaska 4 Transmission Subtotal], [Alaska 4 Distribution Subtotal]; Step 11: Create “Alaska 6 Grand Total” Query This query uses the queries from Steps 9 and 10 to produce the grand total utility cost, which is the total adjustment cost plus the total engineering cost (Figure D.19). The SQL statement is as follows: SELECT [Alaska 5 AC Total].[Total Adjustment Cost], [Alaska 5 EC Total].[Total Engineering Cost], [Total Adjustment Cost]+[Total Engineering Cost] AS [Grand Total] FROM [Alaska 5 AC Total], [Alaska 5 EC Total]; Step 12: Create “Alaska 7 UCM Distribution” Query This query uses the query from Step 5 and numerous other tables of the data model to produce a tabulation of electric distribution utility conflict data (Figure D.20). The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_START_STATN_MS, UTIL_CNFLT.UTIL_ CNFLT_START_OFFST_MS, UTIL_CNFLT.UTIL_CNFLT_END_STATN_MS, UTIL_CNFLT.UTIL_CNFLT_END_OFFST_MS, UTIL_FCLTY_SUBTYPE.UTIL_ FCLTY_SUBTYPE_NM, UTIL_FCLTY_LOCN_TYPE.UFL_TYPE_ACRNM_TXT, UTIL_CNFLT.UTIL_CNFLT_LNGTH_MS, UTIL_CNFLT_TYPE.UTIL_CNFLT_

149 Figure D.15. Design view of “Alaska 3 distribution cost” query. Figure D.16. Design view of “Alaska 4 distribution subtotal” query.

150 Figure D.19. Design view of “Alaska 6 grand total” query. Figure D.17. Design view of “Alaska 5 AC total” query. Figure D.18. Design view of “Alaska 5 EC total” query.

151 The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_START_STATN_MS, UTIL_CNFLT.UTIL_ CNFLT_START_OFFST_MS, UTIL_CNFLT.UTIL_CNFLT_END_STATN_MS, UTIL_CNFLT.UTIL_CNFLT_END_OFFST_MS, UTIL_FCLTY_SUBTYPE.UTIL_ FCLTY_SUBTYPE_NM, UTIL_FCLTY_LOCN_TYPE.UFL_TYPE_ACRNM_TXT, UTIL_CNFLT.UTIL_CNFLT_LNGTH_MS, UTIL_CNFLT_TYPE.UTIL_CNFLT_ TYPE_NM, UTIL_CNFLT_RESOLN_STRTGY_TYPE.UC_RESOLN_STRTGY_ TYPE_NM, [Alaska 3 Transmission Cost].[Adjustment Cost Estimate], [Alaska 3 Transmission Cost].[Engineering Cost Estimate], [Alaska 3 Transmission Cost].[Total Cost] FROM UTIL_FCLTY_SUBTYPE INNER JOIN ((UTIL_CNFLT_RESOLN_ STRTGY_TYPE INNER JOIN (UTIL_FCLTY_TYPE INNER JOIN (UTIL_ FCLTY_LOCN_TYPE INNER JOIN (UTIL_FCLTY INNER JOIN (UTIL_ CNFLT_TYPE INNER JOIN (DOT_PROJ INNER JOIN (UTIL_CNFLT INNER JOIN [Alaska 3 Transmission Cost] ON UTIL_CNFLT.UTIL_CNFLT_ID = [Alaska 3 Transmission Cost].UTIL_CNFLT_ID) ON DOT_PROJ.DOT_ PROJ_NBR = UTIL_CNFLT.DOT_PROJ_NBR) ON UTIL_CNFLT_TYPE. UTIL_CNFLT_TYPE_ID = UTIL_CNFLT.UTIL_CNFLT_TYPE_ID) ON UTIL_ FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_FCLTY_ID) ON UTIL_FCLTY_ LOCN_TYPE.UTIL_FCLTY_LOCN_TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_ LOCN_TYPE_ID) ON UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID = UTIL_ FCLTY.UTIL_FCLTY_TYPE_ID) ON UTIL_CNFLT_RESOLN_STRTGY_TYPE. UC_RESOLN_STRTGY_TYPE_ID = UTIL_CNFLT.UC_RESOLN_STRTGY_ TYPE_ID) INNER JOIN UTIL_CNFLT_EVNT ON UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID) ON UTIL_FCLTY_SUBTYPE.UTIL_ FCLTY_SUBTYPE_ID = UTIL_FCLTY_TYPE.UTIL_FCLTY_SUBTYPE_ID WHERE (((DOT_PROJ.DOT_PROJ_NBR)=50898) AND ((UTIL_FCLTY.UTIL_FCLTY_ TYPE_ID)=2) AND ((UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID)=28)) ORDER BY UTIL_CNFLT.UTIL_CNFLT_ID; Step 14: Create “Alaska Subreport Transmission” Report This report provides a list of utility conflicts along with costs for adjustment, engineering, and total costs per transmission TYPE_NM, UTIL_CNFLT_RESOLN_STRTGY_TYPE.UC_RESOLN_STRTGY_ TYPE_NM, [Alaska 3 Distribution Cost].[Adjustment Cost Estimate], [Alaska 3 Distribution Cost].[Engineering Cost Estimate], [Alaska 3 Distribution Cost]. [Total Cost], DOT_PROJ.DOT_PROJ_NBR, DOT_PROJ.DOT_PROJ_CITY_ NM, STATE.STATE_NM, DOT_PROJ.DOT_PROJ_DSCR, CMPNY.CMPNY_ ACRNM_TXT FROM UTIL_FCLTY_SUBTYPE INNER JOIN ((UTIL_CNFLT_RESOLN_STRTGY_ TYPE INNER JOIN (UTIL_FCLTY_TYPE INNER JOIN (UTIL_FCLTY_LOCN_ TYPE INNER JOIN ((CMPNY INNER JOIN UTIL_FCLTY ON CMPNY. CMPNY_ID = UTIL_FCLTY.CMPNY_ID) INNER JOIN (UTIL_CNFLT_TYPE INNER JOIN (((STATE INNER JOIN DIST ON STATE.STATE_ID = DIST.STATE_ ID) INNER JOIN DOT_PROJ ON DIST.DIST_ID = DOT_PROJ.DIST_ID) INNER JOIN (UTIL_CNFLT INNER JOIN [Alaska 3 Distribution Cost] ON UTIL_ CNFLT.UTIL_CNFLT_ID = [Alaska 3 Distribution Cost].UTIL_CNFLT_ID) ON DOT_PROJ.DOT_PROJ_NBR = UTIL_CNFLT.DOT_PROJ_NBR) ON UTIL_ CNFLT_TYPE.UTIL_CNFLT_TYPE_ID = UTIL_CNFLT.UTIL_CNFLT_TYPE_ID) ON UTIL_FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_FCLTY_ID) ON UTIL_ FCLTY_LOCN_TYPE.UTIL_FCLTY_LOCN_TYPE_ID = UTIL_FCLTY.UTIL_ FCLTY_LOCN_TYPE_ID) ON UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_TYPE_ID) ON UTIL_CNFLT_RESOLN_STRTGY_ TYPE.UC_RESOLN_STRTGY_TYPE_ID = UTIL_CNFLT.UC_RESOLN_ STRTGY_TYPE_ID) INNER JOIN UTIL_CNFLT_EVNT ON UTIL_CNFLT.UTIL_ CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID) ON UTIL_FCLTY_SUB-TYP- EUTIL_FCLTY_SUBTYPE_ID = UTIL_FCLTY_TYPE.UTIL_FCLTY_SUBTYPE_ID WHERE (((DOT_PROJ.DOT_PROJ_NBR)=50898) AND ((UTIL_FCLTY.UTIL_ FCLTY_TYPE_ID)=0 Or (UTIL_FCLTY.UTIL_FCLTY_TYPE_ID)=1) AND ((UTIL_ CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID)=28)) ORDER BY UTIL_CNFLT.UTIL_CNFLT_ID; Step 13: Create “Alaska 7 UCM Transmission” Query This query uses the query from Step 6 and numerous other tables of the data model to produce a tabulation of electric transmission utility conflict data (Figure D.21). Figure D.20. Design view of “Alaska 7 UCM distribution” query.

152 California department of Transportation UCM Example The sample UCM provided by Caltrans (shown in Appendix B, Figure B.4) includes 24 data items (four data items in the header and 20 data items in the main body) and is an example of a detailed UCM with a large number of data items. This UCM was also interesting because it included data items that were included in the prototype UCM, including utility sheet number, utility conflict investigation type, utility relocation strategy type, and utility relocation responsible party. Replicating the sample UCM involved developing seven queries and one report. Step 1: Create “CA 1 Date Last Revised” Query This query selects the latest time stamp of a utility conflict event that is associated with a specific project (Figure D.23). utility conflict and totals for all utility conflicts. This report is based on the query developed in Step 13. Step 15: Create "Alaska Subreport Grand Total" Report This report provides a total adjustment cost, total engineer- ing cost, and the grand total of both distribution and trans- mission costs based on the query from Step 11. Step 16: Create "Alaska UCM" Report This report uses the data from the query in Step 12 and incor- porates the subreports from Steps 14 and 15 into one report (Figure D.22). The report includes other data items from the database, such as project number and description. It also for- mats certain data items—for example, a station stored as 3640 in the database becomes 36+40 in the report. Figure D.21. Design view of “Alaska 7 UCM transmission” query.

153 Figure D.22. “Alaska UCM” report.

154 Figure D.23. Design view of “CA 1 date last revised” query. Step 3: Create “CA 1 Required Completion Date” Query This query produces the date by which a utility conflict is required to be completed in order for a project to proceed to the construction phase without delays (Figure D.25). For a specific project, the query selects utility conflicts with an associated utility conflict event of “required adjustment com- pletion” (utility conflict event type = 15). The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT_EVNT.UTIL_CNFLT_ID, UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID, UTIL_CNFLT_EVNT.UTIL_ CNFLT_EVNT_DT FROM UTIL_CNFLT INNER JOIN UTIL_CNFLT_EVNT ON UTIL_CNFLT.UTIL_ CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=122401) AND ((UTIL_CNFLT_EVNT. UTIL_CNFLT_EVNT_TYPE_ID)=15)); Step 4: Create “CA 1 UC Comment” Query For a specific project, this query provides a list of utility con- flicts that have an associated comment (Figure D.26). The SQL statement is as follows: SELECT DOT_PROJ.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, CMNT. CMNT_BODY_TXT The SQL statement is as follows: SELECT DOT_PROJ.DOT_PROJ_NBR, Max(UTIL_CNFLT_EVNT.UTIL_CNFLT_ EVNT_TS) AS MaxOfUTIL_CNFLT_EVNT_TS FROM (DOT_PROJ INNER JOIN UTIL_CNFLT ON DOT_PROJ.DOT_PROJ_NBR = UTIL_CNFLT.DOT_PROJ_NBR) INNER JOIN UTIL_CNFLT_EVNT ON UTIL_ CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID GROUP BY DOT_PROJ.DOT_PROJ_NBR HAVING (((DOT_PROJ.DOT_PROJ_NBR)=122401)); Step 2: Create “CA 1 Plan Document Sheet Number” Query This query retrieves the sheet number of a plan document in which a utility conflict is mentioned (Figure D.24). The query selects a project number and then lists the sheet numbers by utility conflict. The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT.DOT_PROJ_NBR, PLAN_ DCMNT.PLAN_DCMNT_SHT_NBR, PLAN_DCMNT.SHT_GRP_ID FROM UTIL_CNFLT INNER JOIN (UTIL_CNFLT_EVNT INNER JOIN ((DCMNT INNER JOIN PLAN_DCMNT ON DCMNT.DCMNT_ID = PLAN_DCMNT. DCMNT_ID) INNER JOIN UTIL_CNFLT_EVNT_DCMNT ON DCMNT.DCMNT_ ID = UTIL_CNFLT_EVNT_DCMNT.DCMNT_ID) ON UTIL_CNFLT_EVNT.UTIL_ CNFLT_EVNT_NBR = UTIL_CNFLT_EVNT_DCMNT.UTIL_CNFLT_EVNT_NBR) ON UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=122401));

155 Figure D.24. Design view of “CA 1 plan document sheet number” query. FROM (DOT_PROJ INNER JOIN UTIL_CNFLT ON DOT_PROJ.DOT_PROJ_NBR = UTIL_CNFLT.DOT_PROJ_NBR) INNER JOIN (UTIL_CNFLT_EVNT INNER JOIN CMNT ON UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_NBR = CMNT. UTIL_CNFLT_EVNT_NBR) ON UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_ EVNT.UTIL_CNFLT_ID WHERE (((DOT_PROJ.DOT_PROJ_NBR)=122401)); Step 5: Create “CA 2 Required Completion Date Outer Join” Query This query produces a list of utility conflicts with required completion dates for a specific project, including those utility conflicts that do not have a required completion date. The query creates an outer join of the utility conflict ID between the utility conflict table and the query from Step 3, with the following conditions: include all records from the utility conflict table and include only those records from the Step 3 query in which the joined fields are equal (Figure D.27). The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, [CA 1 Required Completion Date].UTIL_CNFLT_EVNT_DT FROM UTIL_CNFLT LEFT JOIN [CA 1 Required Completion Date] ON UTIL_ CNFLT.UTIL_CNFLT_ID = [CA 1 Required Completion Date].UTIL_ CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=122401)); Step 6: Create “CA 2 Utility Conflict Comment Outer Join” Query This query produces a list of utility conflicts with comments for a specific project, including those utility conflicts that do not have a comment. The query creates an outer join of the utility conflict ID between the utility conflict table and the query from Step 4, with the following conditions: include all records from the utility conflict table and include only those records from the Step 4 query in which the joined fields are equal (Figure D.28). The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, [CA 1 UC comment].CMNT_BODY_TXT FROM [CA 1 UC comment] RIGHT JOIN UTIL_CNFLT ON [CA 1 UC comment]. UTIL_CNFLT_ID = UTIL_CNFLT.UTIL_CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=122401)); Step 7: Create “CA 3 UCM” Query This query uses the queries from Steps 1, 5, and 6 to retrieve utility conflict data that provide the basis for the Caltrans UCM report (Figure D.29). The query parameter is the project number.

156 Figure D.25. Design view of “CA 1 required completion date” query. Figure D.26. Design view of “CA 1 UC comment” query.

157 PROJ_NBR = UTIL_CNFLT.DOT_PROJ_NBR) AND (DOT_PROJ.DOT_ PROJ_NBR = [CA 2 Required Completion Date Outer Join].DOT_PROJ_ NBR)) INNER JOIN [CA 2 Utility Conflict Comment Outer Join] ON (DOT_PROJ.DOT_PROJ_NBR = [CA 2 Utility Conflict Comment Outer Join].DOT_PROJ_NBR) AND (UTIL_CNFLT.UTIL_CNFLT_ID = [CA 2 Utility Conflict Comment Outer Join].UTIL_CNFLT_ID)) INNER JOIN PROJ_DCMNT ON DOT_PROJ.DOT_PROJ_NBR = PROJ_DCMNT. DOT_PROJ_NBR) ON UTIL_CNFLT_INVESTIGATION_NEED_TYPE.UC_ INVESTIGATION_NEED_TYPE_ID = UTIL_CNFLT.UC_INVESTIGATION_ NEED_TYPE_ID) ON UTIL_FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT. UTIL_FCLTY_ID) ON UTIL_INVESTIGATION_TEST_HOLE.UTIL_ INVESTIGATION_TEST_HOLE_ID = UTIL_CNFLT.UTIL_INVESTIGATION_ TEST_HOLE_ID) ON UTIL_FCLTY_MTRL.UTIL_FCLTY_MTRL_ID = UTIL_ FCLTY.UTIL_FCLTY_MTRL_ID) ON UTIL_FCLTY_TYPE.UTIL_FCLTY_ TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_TYPE_ID) ON UTIL_CNFLT_ RESOLN_STRTGY_TYPE.UC_RESOLN_STRTGY_TYPE_ID = UTIL_ CNFLT.UC_RESOLN_STRTGY_TYPE_ID) ON UTIL_FCLTY_SUBTYPE. UTIL_FCLTY_SUBTYPE_ID = UTIL_FCLTY_TYPE.UTIL_FCLTY_ SUBTYPE_ID WHERE (((DOT_PROJ.DOT_PROJ_NBR)=122401)); Step 8: Create “California UCM” Report This report, which is shown in Figure D.30, uses data from the query in Step 7. All data items provided in the report are selected from database entries. The only exception is the name of the document preparer, which could be inserted dynami- cally through a dialog box when the document is prepared. The The SQL statement is as follows: SELECT DOT_PROJ.DOT_PROJ_DSCR, UTIL_CNFLT.UTIL_CNFLT_ALTERNAT_ NBR, UTIL_INVESTIGATION_TEST_HOLE.UTIL_INVESTIGATION_TEST_ HOLE_NBR, UTIL_INVESTIGATION_TEST_HOLE.UTIL_INVESTIGATION_ TEST_HOLE_LOCN, CMPNY.CMPNY_ACRNM_TXT, UTIL_FCLTY.UTIL_ FCLTY_SZ, UTIL_FCLTY_SUBTYPE.UTIL_FCLTY_SUBTYPE_NM, UTIL_ FCLTY_TYPE.UTIL_FCLTY_TYPE_NM, UTIL_FCLTY.UTIL_FCLTY_DSCR, UTIL_CNFLT.UTIL_CNFLT_START_STATN_MS, UTIL_CNFLT.UTIL_CNFLT_ END_STATN_MS, UTIL_CNFLT.UTIL_CNFLT_ALTERNAT_LOCN_DSCR, UTIL_CNFLT.UTIL_CNFLT_WRK_DSCR, UTIL_CNFLT_INVESTIGATION_ NEED_TYPE.UC_INVESTIGATION_NEED_TYPE_NM, UTIL_FCLTY.UTIL_ FCLTY_DEPTH, UTIL_CNFLT.UC_CNFLT_IMPCT_FLAG, UTIL_CNFLT_ RESOLN_STRTGY_TYPE.UCR_STRTGY_TYPE_ACRNM_TXT, UTIL_CNFLT. UC_RESOLN_RSPNBL_CD, DOT_PROJ.DOT_PROJ_NBR, [CA 1 Date Last Revised].MaxOfUTIL_CNFLT_EVNT_TS, [CA 1 Plan Document Sheet Number]. PLAN_DCMNT_SHT_NBR, [CA 2 Required Completion Date Outer Join]. UTIL_CNFLT_EVNT_DT, [CA 2 Utility Conflict Comment Outer Join].CMNT_ BODY_TXT FROM UTIL_FCLTY_SUBTYPE INNER JOIN (UTIL_CNFLT_RESOLN_STRTGY_ TYPE INNER JOIN (UTIL_FCLTY_TYPE INNER JOIN (UTIL_FCLTY_MTRL INNER JOIN (UTIL_INVESTIGATION_TEST_HOLE INNER JOIN ((CMPNY INNER JOIN UTIL_FCLTY ON CMPNY.CMPNY_ID = UTIL_FCLTY.CMPNY_ ID) INNER JOIN (UTIL_CNFLT_INVESTIGATION_NEED_TYPE INNER JOIN ((((DOT_PROJ INNER JOIN [CA 1 Date Last Revised] ON DOT_ PROJ.DOT_PROJ_NBR = [CA 1 Date Last Revised].DOT_PROJ_NBR) INNER JOIN ((UTIL_CNFLT INNER JOIN [CA 1 Plan Document Sheet Number] ON UTIL_CNFLT.UTIL_CNFLT_ID = [CA 1 Plan Document Sheet Number].UTIL_CNFLT_ID) INNER JOIN [CA 2 Required Completion Date Outer Join] ON UTIL_CNFLT.UTIL_CNFLT_ID = [CA 2 Required Completion Date Outer Join].UTIL_CNFLT_ID) ON (DOT_PROJ.DOT_ Figure D.27. Design view of “CA 2 required completion date outer join” query.

158 report also formats certain data items—for example, a station stored as 16555 in the database becomes 165+55 in the report. Georgia department of Transportation UCM Example The sample UCM provided by GDOT is included in Appen- dix B (Figure B.11). This UCM, which includes 10 data items (two data items in the header and eight data items in the main body), is an example of a UCM with a small number of data items. This UCM was of particular interest because it has been used extensively in the past and is the product of several iterations of improvements resulting from experience with previous versions. A unique challenge in the development of the UCM report from a data management perspective was the combination of the utility owner acronym with the facility type in certain cases. Replicating this sample UCM involved developing five queries and one report. Step 1: Create “GDOT 1 Station” Query This query selects all conflicts for a project ID and formats the value stored in the utility conflict start station measure- ment field using typical stationing format. The query then combines the value stored in the utility conflict start station measurement field with the value stored in the utility conflict start offset measurement field and stores the combined value in a temporary field called Sta (Figure D.31). Figure D.28. Design view of “CA 2 utility conflict comment outer join” query. Figure D.29. Design view of “CA 3 UCM” query.

159 Figure D.30. “California UCM” report.

160 called UtilA (Figure D.32). The result of this query is a list of all utility conflicts for which a utility owner acronym was provided that can be combined with a utility facility type acronym. If no utility owner acronym is available, there is no entry for that utility conflict in the UtilA field, and the record is not displayed in this query. The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_ CNFLT.UTIL_CNFLT_ALTERNAT_NBR, [CMPNY_ACRNM_TXT] & Format([UTIL_FCLTY_TYPE_ACRNM_TXT], “-@@@”) AS UtilA FROM UTIL_FCLTY_TYPE INNER JOIN ((CMPNY INNER JOIN UTIL_FCLTY ON CMPNY.CMPNY_ID = UTIL_FCLTY.CMPNY_ID) INNER JOIN UTIL_ CNFLT ON UTIL_FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_FCLTY_ID) ON UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID = UTIL_FCLTY.UTIL_ FCLTY_TYPE_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=987654321) AND ((CMPNY.CMPNY_ ID) Is Not Null)); The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_ CNFLT.UTIL_CNFLT_ALTERNAT_NBR, Format([UTIL_CNFLT_START_ STATN_MS], “#+00”) & “,” & [UTIL_CNFLT_START_OFFST_MS] & “,” & [UTIL_CNFLT_ALTERNAT_LOCN_DSCR] AS Sta FROM UTIL_CNFLT WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=987654321)); Step 2: Create “GDOT 1 Utility Column— Company” Query This query selects all conflicts for a given project number, displays the value in utility conflict alternative number, com- bines the value in company acronym text with the value in utility facility type acronym text if the value in company ID is not null, and stores this combination in a temporary field Figure D.31. Design view of “GDOT 1 station” query.

161 Step 4: Create “GDOT 2 Utility Column Concatenate” Query This query selects all conflicts for a given project number, displays the value in utility conflict alternative number, combines the values in the temporary fields UtilA and UtilB, and stores the combined value in a temporary field called UtilC. This combined value is later displayed in the UCM report as the utility field of the Georgia UCM (Figure D.34). The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT.DOT_PROJ_NBR, UTIL_ CNFLT.UTIL_CNFLT_ALTERNAT_NBR, [UtilA] & [UtilB] AS UtilC FROM (UTIL_CNFLT LEFT JOIN [GDOT Q2] ON UTIL_CNFLT.UTIL_CNFLT_ID = [GDOT Q2].UTIL_CNFLT_ID) LEFT JOIN [GDOT Q1] ON UTIL_CNFLT.UTIL_ CNFLT_ID = [GDOT Q1].UTIL_CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=987654321)); Step 5: Create “Georgia UCM” Query This query uses the queries from Steps 1 through 4 and com- bines the data into one datasheet that is used as the foundation for the report that creates the Georgia UCM in Step 6. In addi- tion to the queries from Steps 1 through 4, this query uses the tables utility conflict, utility facility, utility facility type, utility investigation test hole, and company (Figure D.35). Step 3: Create “GDOT 1 Utility Column— Facility Size” Query This query selects all conflicts for a given project number, displays the value in utility conflict alternative number, combines the value in utility facility size with the value in utility facility type acronym text if the value in utility facility size is not null, and stores this combination in a temporary field called UtilB (Figure D.33). The result of this query is a list of all utility conflicts for which a utility facility size was provided that can be combined with a utility facility type acronym. If no utility facility size is available, there is no entry for that utility conflict in the UtilB field, and the record is not displayed in this query. The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_ CNFLT.UTIL_CNFLT_ALTERNAT_NBR, [UTIL_FCLTY_SZ] & [UTIL_FCLTY_ TYPE_ACRNM_TXT] AS UtilB FROM UTIL_FCLTY_TYPE INNER JOIN (UTIL_FCLTY INNER JOIN UTIL_ CNFLT ON UTIL_FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_FCLTY_ID) ON UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID = UTIL_FCLTY.UTIL_ FCLTY_TYPE_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=987654321) AND ((UTIL_FCLTY.UTIL_ FCLTY_SZ) Is Not Null)); Figure D.32. Design view of “GDOT 1 utility column—company” query.

162 All data items provided in the report are selected from database entries, including additional data items such as project number, date, and time that appear in the header of the UCM. Names of individuals who developed, revised, or reviewed the UCM report could be added to the header as needed using an insertion mechanism such as a dialog box at the time of preparing, revising, or reviewing the document. Texas department of Transportation UCM Example The sample UCM provided by TxDOT is included in Appen- dix B (Figure B.29). This UCM, which includes 19 data items (five data items in the header and 14 data items in the main body), is an example of a UCM with an average number of data items. The UCM was of particular interest because it offered a number of unique challenges. First, it combined the The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_NBR, [GDOT 1 Station].Sta, [GDOT 2 Utility Column Concatenate].UtilC, UTIL_CNFLT.UTIL_CNFLT_DSCR, UTIL_ INVESTIGATION_TEST_HOLE.UITH_NBR, UTIL_CNFLT.UTIL_CNFLT_WRK_ DSCR, UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT_RESOLN_ALTERNAT. UC_RESOLN_ALTERNAT_DSCR, UTIL_CNFLT_RESOLN_ALTERNAT.UC_ RESOLN_ALTERNAT_ADVANTAGE_TXT, UTIL_CNFLT_RESOLN_ALTERNAT. UCR_ALTERNAT_DCSN_ID FROM (UTIL_FCLTY_TYPE INNER JOIN ((CMPNY RIGHT JOIN UTIL_FCLTY ON CMPNY.CMPNY_ID = UTIL_FCLTY.CMPNY_ID) INNER JOIN (UTIL_ INVESTIGATION_TEST_HOLE RIGHT JOIN ((UTIL_CNFLT INNER JOIN [GDOT 1 Station] ON UTIL_CNFLT.UTIL_CNFLT_ID = [GDOT 1 Station].UTIL_ CNFLT_ID) INNER JOIN [GDOT 2 Utility Column Concatenate] ON UTIL_ CNFLT.UTIL_CNFLT_ID = [GDOT 2 Utility Column Concatenate].UTIL_CNFLT_ ID) ON UTIL_INVESTIGATION_TEST_HOLE.UITH_ID = UTIL_CNFLT.[UITH_ ID]) ON UTIL_FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_FCLTY_ID) ON UTIL_FCLTY_TYPE.UTIL_FCLTY_TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_ TYPE_ID) INNER JOIN UTIL_CNFLT_RESOLN_ALTERNAT ON UTIL_CNFLT. UTIL_CNFLT_ID = UTIL_CNFLT_RESOLN_ALTERNAT.UTIL_CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=987654321) AND ((UTIL_CNFLT_ RESOLN_ALTERNAT.UCR_ALTERNAT_DCSN_ID)=1)); Step 6: Create “Georgia UCM” Report This report, which is shown in Figure D.36, replicates the Georgia UCM based on the results of the query from Step 5. Figure D.33. Design view of “GDOT 1 utility column—facility size” query.

163 The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT.DOT_PROJ_NBR, UTIL_ AGRMT.UA_ID, Last(UTIL_AGRMT_DT_TYPE.UA_DT_TYPE_NM) AS Last OfUA_DT_TYPE_NM, Last(UTIL_AGRMT_DT.UA_DT) AS LastOfUA_DT FROM (UTIL_AGRMT_DT_TYPE INNER JOIN (UTIL_AGRMT INNER JOIN UTIL_ AGRMT_DT ON UTIL_AGRMT.UA_ID = UTIL_AGRMT_DT.UA_ID) ON UTIL_ AGRMT_DT_TYPE.UA_DT_TYPE_ID = UTIL_AGRMT_DT.UA_DT_TYPE_ID) INNER JOIN UTIL_CNFLT ON UTIL_AGRMT.UA_ID = UTIL_CNFLT.UA_ID GROUP BY UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT.DOT_PROJ_NBR, UTIL_ AGRMT.UA_ID HAVING (((UTIL_CNFLT.DOT_PROJ_NBR)=2802081 Or (UTIL_CNFLT.DOT_ PROJ_NBR)=50801166)); Step 2: Create “TxDOT 1 Agreement Submittal Date” Query This query selects the utility agreement submittal date, which is the utility agreement date type = 1, for a utility agreement that is related to one or more utility conflicts. The query only displays those utility conflicts for a particular project that have a utility agreement submittal date (Figure D.38). utility conflicts from two projects into one UCM, as can be seen by the two project numbers (CSJ numbers) and project descriptions in the header. Second, the UCM included some unusual fields, such as agreement status and sheet number. Replicating this sample UCM involved developing seven que- ries and one report. Step 1: Create “TxDOT 1 Agreement Status” Query This query selects the utility conflicts for two project IDs and groups the data by the utility agreement ID for each utility conflict. The query then displays the last entry in the utility agreement date field and the associated utility agreement date type of that entry. The result of this query is the latest date and date type for a utility agreement, which is essentially the current utility agreement status (Figure D.37). Figure D.34. Design view of “GDOT 2 utility column concatenate” query.

164 The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, Last(UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_NBR) AS LastOfUTIL_CNFLT_ EVNT_NBR, Last(UTIL_CNFLT_EVNT_TYPE.UTIL_CNFLT_EVNT_TYPE_NM) AS LastOfUTIL_CNFLT_EVNT_TYPE_NM FROM UTIL_CNFLT INNER JOIN (UTIL_CNFLT_EVNT_TYPE INNER JOIN UTIL_ CNFLT_EVNT ON UTIL_CNFLT_EVNT_TYPE.UTIL_CNFLT_EVNT_TYPE_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID) ON UTIL_CNFLT.UTIL_ CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID GROUP BY UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID HAVING (((UTIL_CNFLT.DOT_PROJ_NBR)=2802081 Or (UTIL_CNFLT.DOT_ PROJ_NBR)=50801166)) ORDER BY UTIL_CNFLT.UTIL_CNFLT_ID; Step 4: Create “TxDOT 1 Estimated Resolution Date” Query This query selects the utility conflict event type = 16, which is the estimated conflict resolution date, and returns the associ- ated date value, if such an event exists. If no estimated conflict The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT.DOT_PROJ_NBR, UTIL_ AGRMT.UA_ID, UTIL_AGRMT_DT.UA_DT_TYPE_ID, UTIL_AGRMT_ DT.UA_DT FROM (UTIL_AGRMT_DT_TYPE INNER JOIN (UTIL_AGRMT INNER JOIN UTIL_AGRMT_DT ON UTIL_AGRMT.UA_ID = UTIL_AGRMT_DT.UA_ID) ON UTIL_AGRMT_DT_TYPE.UA_DT_TYPE_ID = UTIL_AGRMT_DT.UA_DT_ TYPE_ID) INNER JOIN UTIL_CNFLT ON UTIL_AGRMT.UA_ID = UTIL_ CNFLT.UA_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=2802081 Or (UTIL_CNFLT.DOT_ PROJ_NBR)=50801166) AND ((UTIL_AGRMT_DT.UA_DT_TYPE_ID)=1)); Step 3: Create “TxDOT 1 Conflict Status” Query This query produces the utility conflict status for all utility conflicts of a particular project. The utility conflict status is essentially the latest utility conflict event that is stored in the utility conflict event table (Figure D.39). Figure D.35. Design view of “Georgia UCM” query.

165 xirtaM tcilfnoC ytilitU TOD aigroeG ,yadseuT rebmetpeS ,81 210235:42:5 MPTODG tcejorP :rebmuN 123456789 tcilfnoC noitatS dna tesffO ytilitU deifitnedI tcilfnoC elohtseT dedeeN ytilitU tcapmI htiw tsoC sA"( - )"dengised tifeneB fo *noituloseRdednemmoceR noituloseR 1C ,50+001 '12 ,L ht41 tS .rtsnoC LB LGA - OFB desoporP mrots erutcurts dna gnitsixe .OFB etacoleR 0511 FL fo OFB - TCUD .)000,19$( evaS tsoc ot etacoler OFB - TCUD .)000,19$(etacoleR desoporp mrots eganiard otni .teerts esU s'ID taht niard drawot .yawdaor 2C ,66+001 '12 ,L ht41 tS .rtsnoC LB LGA - OFB desoporP mrots erutcurts dna gnitsixe .OFB etacoleR 0511 FL fo OFB - TCUD .)000,19$( evaS tsoc ot etacoler OFB - TCUD .)000,19$(etacoleR desoporp mrots eganiard otni .teerts esU s'ID taht niard drawot .yawdaor 3C ,83+001 '42 ,R ht41 tS .rtsnoC LB KNU - KNU desoporP "81 mrots dna nwonknu ytilitu .eet HT 1 etacoleR nwonknu epyt dna noitcnuf .ytilitu etanimilE elbissop yaled gnirud .noitcurtsnocHT ot yfitnedi ytilitu dna .tcilfnoc 4C ,65+001 '52 ,R ht41 tS .rtsnoC LB W"8 desoporP "81 mrots dna gnitsixe "8 .W HT 2 etacoleR "8 W .)005,7$( evaS tsoc ot etacoler "8 W .)000,6$(HT no "8 ,W tsujda htped fo desoporp mrots .eganiard 5C ,16+001 '52 ,R ht41 tS .rtsnoC LB W"8 desoporP "81 mrots dna gnitsixe "8 .W HT 3 etacoleR "8 W .)005,7$( evaS tsoc ot etacoler "8 W .)000,6$(HT no "8 ,W tsujda htped fo desoporp mrots .eganiard 6C ,28+001 '82 ,R ht41 tS .rtsnoC LB G"4 desoporP mrots erutcurts dna gnitsixe "4 .G HT 4 etacoleR 02 FL fo "4 G .)000,6$( evaS tsoc ot etacoler "4 G .)005,4$(HT no "4 ,G tsujda htped fo desoporp mrots .erutcurts 7C ,22+101 '72 ,R ht41 tS .rtsnoC LB G"4 desoporP '81 dna gnitsixe "4 yb "2 sag .eet HT 5 etacoleR "2 G dna "4 G eeT .)005,21$( evaS tsoc ot etacoler G senil .)000,11$(HT no G ,senil tsujda htped fo desoporp mrots .erutcurts 8C ,10+101 '82 ,L ht41 tS .rtsnoC LB G"61 desoporP mrots erutcurts dna gnitsixe "61 .G HT 6 etacoleR "61 G .)000,01$( evaS tsoc ot etacoler "61 G .)005,8$(HT no "61 ,G tsujda htped fo desoporp mrots .erutcurts 9C ,52+101 '14 ,L ht41 tS .rtsnoC LB KNU - TB - TCUD desoporP mrots erutcurts dna owt TB .stcud HT 7 etacoleR TB - TCUD dna "2 G .)000,11$( evaS tsoc ot etacoler TB tcud dna "2 G .)005,01$( HT no TB - TCUD dna "2 ,G tsujda htped fo desoporp mrots .erutcurts 01C ,73+101 '14 ,L ht41 tS .rtsnoC LB W"6 desoporP "81 mrots dna gnitsixe "6 .W HT 8 etacoleR "6 W .)000,5$( evaS tsoc ot etacoler "6 W .)005,3$(HT no "6 ,W tsujda htped fo desoporp mrots .eganiard 11C ,75+101 '72 ,L ht41 tS .rtsnoC LB G"61 desoporP "81 mrots dna gnitsixe "61 .G HT 9 etacoleR "61 G .)000,01$( evaS tsoc ot etacoler "61 G .)005,8$(HT no "61 ,G tsujda htped fo desoporp mrots .erutcurts 21C ,85+101 '22 ,L ht41 tS .rtsnoC LB LGA - OFB desoporP mrots erutcurts dna gnitsixe .OFB etacoleR 0511 FL fo OFB - TCUD .)000,19$( evaS tsoc ot etacoler OFB - TCUD .)000,19$(etacoleR desoporp mrots eganiard otni .teerts esU s'ID taht niard drawot .yawdaor 31C ,09+101 '22 ,L ht41 tS .rtsnoC LB LGA - OFB desoporP mrots erutcurts dna gnitsixe .OFB etacoleR 0511 FL fo OFB - TCUD .)000,19$( evaS tsoc ot etacoler OFB - TCUD .)000,19$(etacoleR desoporp mrots eganiard otni .teerts esU s'ID taht niard drawot .yawdaor 41C ,02+201 '72 ,R ht41 tS .rtsnoC LB G"4 desoporP mrots erutcurts dna gnitsixe "4 .G etacoleR "4 G .)005,4$( etanimilE tcilfnoc htiw desoporp .IDetacoleR "4 .G 51C ,63+201 "42 ,L ht41 tS .rtsnoC LB LGA - OFB desoporP mrots erutcurts dna gnitsixe .OFB etacoleR 0511 FL fo OFB - TCUD .)000,19$( evaS tsoc ot etacoler OFB - TCUD .)000,19$(etacoleR desoporp mrots eganiard otni .teerts esU s'ID taht niard drawot .yawdaor * esaelP edulcni lla stifeneb derrucni gnidulcni ,emit ,stsoc dna ytefas stnemevorpmi CA - sotsebsA etercnoC TO - daehrevO enohpeleT EB - deiruB cirtcelE R - thgiR OFB - deiruB rebiF citpO PCR - decrofnieR etercnoC epiP TB - deiruB enohpeleT W - retaW G - saG MW - retaW niaM L - tfeL HT - tseT eloH SEM - deretiM dnE noitceS KNU - nwonknU LGA atnaltA saG thgiL EB aigroeG rewoP TB lleB htuoS 3L leveL 3 snoitacinummoC NFM aidemorteM rebiF krowteN NAS notluF ytnuoC cilbuP skroW W ytiC fo atnaltA :yeK ytilitU :renwO Figure D.36. “Georgia UCM” report.

166 Figure D.37. Design view of “TxDOT 1 agreement status” query. Figure D.38. Design view of “TxDOT 1 agreement submittal date” query.

167 field, which is a concatenation of the start station and utility conflict alternate location description fields. The query con- catenates the two if both fields have an entry; otherwise, it displays either field content (Figure D.41). The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_CNFLT.DOT_PROJ_NBR, Format(UTIL_CNFLT_START_STATN_MS,‘#+00’) AS Start_Stn, [Start_Stn] & IIf([Start_Stn]<>" And [UTIL_CNFLT_ALTERNAT_LOCN_DSCR]<>",', ',") & [UTIL_CNFLT_ALTERNAT_LOCN_DSCR] AS LOCATION FROM UTIL_CNFLT WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=2802081 Or (UTIL_CNFLT.DOT_ PROJ_NBR)=50801166)); Step 6: Create “TxDOT 1 Sheet Number” Query This query selects all plan documents associated with docu- ments that are related to utility conflicts. This query allows the system to determine which plan sheet a particular utility conflict is located on (Figure D.42). resolution date is found in the database, the query does not display the utility conflict record (Figure D.40). The SQL statement is as follows: SELECT UTIL_CNFLT.DOT_PROJ_NBR, UTIL_CNFLT.UTIL_CNFLT_ID, UTIL_ CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID, UTIL_CNFLT_EVNT.UTIL_ CNFLT_EVNT_DT FROM UTIL_CNFLT INNER JOIN (UTIL_CNFLT_EVNT_TYPE INNER JOIN UTIL_ CNFLT_EVNT ON UTIL_CNFLT_EVNT_TYPE.UTIL_CNFLT_EVNT_TYPE_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_TYPE_ID) ON UTIL_CNFLT.UTIL_ CNFLT_ID = UTIL_CNFLT_EVNT.UTIL_CNFLT_ID WHERE (((UTIL_CNFLT.DOT_PROJ_NBR)=2802081 Or (UTIL_CNFLT.DOT_ PROJ_NBR)=50801166) AND ((UTIL_CNFLT_EVNT.UTIL_CNFLT_EVNT_ TYPE_ID)=16)); Step 5: Create “TxDOT 1 Multiple Projects” Query This query selects all conflicts for two project IDs, formats the value stored in the utility conflict start station measurement field using typical stationing format, and displays the value in the start station field. The query further creates a location Figure D.39. Design view of “TxDOT 1 conflict status” query.

168 The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_NBR, CMPNY.CMPNY_NM, UTIL_FCLTY_ TYPE.UTIL_FCLTY_TYPE_NM, [UTIL_FCLTY_SZ] & ≤ ≤ & [UTIL_FCLTY_DSCR] AS [Utility Size Material], [TxDOT 1 Multiple Projects].LOCATION, UTIL_FCLTY_ LOCN_TYPE.UTIL_FCLTY_LOCN_TYPE_NM, UTIL_CNFLT.UTIL_CNFLT_ DSCR, [TxDOT 1 Sheet Number].PLAN_DCMNT_CMNT, [TxDOT 1 Conflic Status].LastOfUTIL_CNFLT_EVNT_TYPE_NM, [TxDOT 1 Estimated Resolution Date].UTIL_CNFLT_EVNT_DT, IIf([UTIL_CNFLT_REIMBABLE_FLAG]=‘Y’,‘JUA A’,‘JUA B’) AS AGREEMENT, [TxDOT 1 Agreement Status].LastOfUA_DT_ TYPE_NM, [TxDOT 1 Agreement Submittal Date].UA_DT, UTIL_CNFLT_ RESOLN_ALTERNAT.UC_RESOLN_ALTERNAT_CMNT, UTIL_CNFLT_ RESOLN_ALTERNAT.UCR_ALTERNAT_DCSN_ID, UTIL_CNFLT.DOT_PROJ_ NBR, DOT_PROJ.DIST_ID, DIST.DIST_NM, DOT_PROJ.DOT_PROJ_DSCR FROM (UTIL_FCLTY_TYPE INNER JOIN (UTIL_FCLTY_LOCN_TYPE INNER JOIN ((CMPNY INNER JOIN UTIL_FCLTY ON CMPNY.CMPNY_ID = UTIL_ FCLTY.CMPNY_ID) INNER JOIN (DIST INNER JOIN (DOT_PROJ INNER JOIN ((((((UTIL_CNFLT INNER JOIN [TxDOT 1 Multiple Projects] ON UTIL_ CNFLT.UTIL_CNFLT_ID = [TxDOT 1 Multiple Projects].UTIL_CNFLT_ID) LEFT JOIN [TxDOT 1 Sheet Number] ON UTIL_CNFLT.UTIL_CNFLT_ID = [TxDOT 1 Sheet Number].UTIL_CNFLT_ID) INNER JOIN [TxDOT 1 Conflict Status] ON UTIL_CNFLT.UTIL_CNFLT_ID = [TxDOT 1 Conflict Status].UTIL_CNFLT_ID) LEFT JOIN [TxDOT 1 Estimated Resolution Date] ON UTIL_CNFLT.UTIL_ CNFLT_ID = [TxDOT 1 Estimated Resolution Date].UTIL_CNFLT_ID) LEFT JOIN [TxDOT 1 Agreement Status] ON UTIL_CNFLT.UTIL_CNFLT_ID = [TxDOT 1 Agreement Status].UTIL_CNFLT_ID) LEFT JOIN [TxDOT 1 The SQL statement is as follows: SELECT UTIL_CNFLT.UTIL_CNFLT_ID, DCMNT.DCMNT_ID, PLAN_DCMNT. PLAN_DCMNT_CMNT FROM UTIL_CNFLT INNER JOIN (UTIL_CNFLT_EVNT INNER JOIN ((DCMNT INNER JOIN PLAN_DCMNT ON DCMNT.DCMNT_ID = PLAN_DCMNT. DCMNT_ID) INNER JOIN UTIL_CNFLT_EVNT_DCMNT ON DCMNT. DCMNT_ID = UTIL_CNFLT_EVNT_DCMNT.DCMNT_ID) ON UTIL_CNFLT_ EVNT.UTIL_CNFLT_EVNT_NBR = UTIL_CNFLT_EVNT_DCMNT.UTIL_ CNFLT_EVNT_NBR) ON UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_ EVNT.UTIL_CNFLT_ID; Step 7: Create “TxDOT 2 UCM” Query This query uses the queries from Steps 1 through 6 and com- bines the data into one datasheet that is used as the founda- tion for the report that creates the Texas UCM in Step 8. In addition to the queries from Steps 1 through 6, this query uses the tables utility conflict, DOT project, district, utility facility, utility facility type, utility facility location type, and company (Figure D.43). Figure D.40. Design view of “TxDOT 1 estimated resolution date” query.

169 Figure D.41. Design view of “TxDOT 1 multiple projects” query. Figure D.42. Design view of “TxDOT 1 sheet number” query.

170 lbCSJ2.Caption = GetProjCSJ(sSQL, “DOT_PROJ_NBR”) sSQL = “SELECT DP.DOT_PROJ_DSCR FROM DOT_PROJ DP INNER JOIN [TxDOT 2 UCM] TQ ON DP.DOT_PROJ_NBR = TQ.DOT_PROJ_NBR WHERE UTIL_CNFLT_NBR < 4 ” lbDesc1.Caption = GetProjDesc(sSQL, “DOT_PROJ_DSCR”) sSQL = “SELECT DP.DOT_PROJ_DSCR FROM DOT_PROJ DP INNER JOIN [TxDOT 2 UCM] TQ ON DP.DOT_PROJ_NBR = TQ.DOT_PROJ_NBR WHERE UTIL_CNFLT_NBR >= 4 ” lbDesc2.Caption = GetProjDesc(sSQL, “DOT_PROJ_DSCR”) End Sub Private Function GetProjCSJ(strSQL As String, OutputFld As String) Dim db As Database Dim rs As DAO.Recordset Dim sSQL As String Dim sResult As String Set db = CurrentDb() Set rs = db.OpenRecordset(strSQL) sResult = rs(OutputFld) rs.Close Set rs = Nothing GetProjCSJ = FormatCSJ(sResult) End Function Public Function FormatCSJ(CSJ As String) As String Dim TempCSJ As String TempCSJ = Right(“0000000000” & CSJ, 9) TempCSJ = Left(TempCSJ, 3) & “-” & Mid(TempCSJ, 4, 2) & “-” & Right(TempCSJ, 4) FormatCSJ = TempCSJ End Function Private Function GetProjDesc(strSQL As String, OutputFld As String) Dim db As Database Dim rs As DAO.Recordset Dim sSQL As String Dim sResult As String Set db = CurrentDb() Set rs = db.OpenRecordset(strSQL) sResult = rs(OutputFld) rs.Close Set rs = Nothing GetProjDesc = sResult End Function Agreement Submittal Date] ON UTIL_CNFLT.UTIL_CNFLT_ID = [TxDOT 1 Agreement Submittal Date].UTIL_CNFLT_ID) ON (DOT_PROJ.DOT_PROJ_ NBR = UTIL_CNFLT.DOT_PROJ_NBR) AND (DOT_PROJ.DOT_PROJ_NBR = [TxDOT 1 Multiple Projects].DOT_PROJ_NBR)) ON DIST.DIST_ID = DOT_ PROJ.DIST_ID) ON UTIL_FCLTY.UTIL_FCLTY_ID = UTIL_CNFLT.UTIL_ FCLTY_ID) ON UTIL_FCLTY_LOCN_TYPE.UTIL_FCLTY_LOCN_TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_LOCN_TYPE_ID) ON UTIL_FCLTY_TYPE.UTIL_ FCLTY_TYPE_ID = UTIL_FCLTY.UTIL_FCLTY_TYPE_ID) INNER JOIN (UTIL_ CNFLT_RESOLN_ALTERNAT_DCSN INNER JOIN UTIL_CNFLT_RESOLN_ ALTERNAT ON UTIL_CNFLT_RESOLN_ALTERNAT_DCSN.UCR_ALTERNAT_ DCSN_ID = UTIL_CNFLT_RESOLN_ALTERNAT.UCR_ALTERNAT_DCSN_ID) ON UTIL_CNFLT.UTIL_CNFLT_ID = UTIL_CNFLT_RESOLN_ALTERNAT. UTIL_CNFLT_ID WHERE (((UTIL_CNFLT_RESOLN_ALTERNAT.UCR_ALTERNAT_DCSN_ID)=1) AND ((UTIL_CNFLT.DOT_PROJ_NBR)=2802081 Or (UTIL_CNFLT.DOT_ PROJ_NBR)=50801166)) ORDER BY UTIL_CNFLT.UTIL_CNFLT_NBR; Step 8: Create “Texas UCM” Report This report, which is shown in Figure D.44, uses the data from the query in Step 7. The report includes other data items from the database, including project numbers, project descriptions, and TxDOT district. One particular issue was to display both project numbers on the UCM report. The research team solved this issue by writing functions in VBA code, which is included below: Option Compare Database Private Sub Report_Open(Cancel As Integer) Dim sSQL As String Dim result As String sSQL = “SELECT DOT_PROJ_NBR FROM [TxDOT 2 UCM] WHERE UTIL_ CNFLT_NBR < 4 ” ' lbCSJ1.SetFocus lbCSJ1.Caption = GetProjCSJ(sSQL, “DOT_PROJ_NBR”) sSQL = “SELECT DOT_PROJ_NBR FROM [TxDOT 2 UCM] WHERE UTIL_ CNFLT_NBR >= 4 ” Figure D.43. Design view of “TxDOT 2 UCM” query.

171 Figure D.44. “Texas UCM” report.