VERIFIED SOLUTION i

Resolve issue where a Street Works Notice is missing from the search list in Confirm

Product Feature: Street Works

 

Issue

There was one permit request which was granted. Unable to find the Permit using "Find Street Works"  

Permit is listed in the Street works report but when trying to open it, we are getting an error message saying “No Street works record found for work reference 457552”:


User-added image

Cause

This is caused by the work_version_no mismatch between the two tables sw_header and sw_notice_header.

Resolution

UPDATED: July 27, 2017

Run the SQL below to check the problem is that the sw_header.work_version_no is not the same as the maximum sw_notice_header.work_version_no.  Restrict the organise_code and works_ref so it only shows the Notice of interest.

SELECT
   sw_header.organise_code,
   sw_header.works_ref,
   sw_header.work_version_no,
   sw_notice_header.work_version_no
FROM
   sw_header,
   sw_notice_header
WHERE
   sw_header.work_header_no = sw_notice_header.work_header_no AND
   sw_header.organise_code = <promoter organisation code> AND
   sw_header.works_ref = <works reference>;

'Find Street Works' only gets Notices where the sw_header.work_version_no is the same as the maximum sw_notice_header.work_version_no.

To see all Notices where the work version number is out of sync run this SQL

SELECT MAX(sw_notice_header.work_version_no),
       sw_header.work_version_no,
       sw_header.work_header_no,
       sw_header.works_ref,
       sw_header.organise_code
  FROM sw_notice_header,
       sw_header
 WHERE sw_header.work_header_no = sw_notice_header.work_header_no AND
       0 < (SELECT MAX(sw_notice_header.work_version_no)
              FROM sw_notice_header snh2
             WHERE snh2.work_header_no = sw_notice_header.work_header_no) AND
       NOT EXISTS(SELECT 1
                    FROM sw_notice_header
                   WHERE sw_header.work_header_no = sw_notice_header.work_header_no
                     AND sw_header.work_version_no = sw_notice_header.work_version_no)
GROUP BY sw_header.work_version_no,
         sw_header.work_header_no,
         sw_header.works_ref,
         sw_header.organise_code;

Send the results to Confirm Technical Support (software.support@pb.com) who will be able to send some SQL to update the records.