VERIFIED SOLUTION i

The Find Street Works filter not showing a Street Works Notice that is in Confirm

Products affected: Confirm


 

Issue

When using the 'Find Street Works' functionality in Confirm®, no Street Works Notice is found although it is in the database.

Cause

This is caused when the Works Version number on both the 'sw_header' and the highest value on the 'sw_notice_header' tables are out of sync.

 

Resolution

UPDATED: May 22, 2019
To see this create a Data Source using this  Structured Query Language (SQL).
select
 sw_header.works_ref as WorksRef,
 sw_notice_header.work_header_no as HeaderNo,
 sw_header.work_version_no as HeaderWorkVersionNo,
 sw_notice_header.work_version_no as NoticeWorkVersionNo
from
 sw_header, sw_notice_header
where
 sw_notice_header.work_header_no = sw_header.work_header_no

Run the SQL and you will see the version for the parent (sw_header.work_version_no) is not the same as the largest one child record (   sw_notice_header.work_version_no). Select one of the SQL results she can drill down to the Notice in the Street Works table.

When you run the Data Source qualify it using the Organisation Department for Transport (DfT) Ref 2 and the Works Ref of the Notice you wish to find.

If the highest value for the work version number in the sw_notice_header does not match the work version number in the sw_header table contact Pitney Bowes Technical Support who will supply SQL to fix the issue.