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

Products affected: Confirm



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


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.



UPDATED: May 22, 2019
To see this create a Data Source using this  Structured Query Language (SQL).
 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
 sw_header, sw_notice_header
 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.