VERIFIED SOLUTION i

Understanding numerical sequencing for Fixed Penalty Noticing in Confirm

Product affected: Confirm®
Product feature: Street Works
Database: Oracle
 
For Confirm Oracle customers the Oracle sequence SEQUENCE_PENALTIES_KEY corresponds with the offence_key column in the offence table and the sequence SEQUENCE_PENALTIES_REF corresponds with the offence_ref column in the offence table.

The offence_key value from the table and the offence_ref value from the table should be lower than the next value to be allocated in the corresponding Oracle sequence or, in other words, at least equal to the current value in the sequence.

The offence table can be queried as follows to find the current offense_key and the current offense_ref:
 
SELECT offence_key, 
offence_ref , 
offence_date, 
work_header_no, 
work_version no 
FROM offence 
WHERE offence_key IN (SELECT Max (offence 
key)
FROM offence);

These two queries:
 
SELECT last_number
FROM all_sequences
WHERE sequence_name = 'SEQUENCE_PENALTIES_KEY'
AND sequence_owner  = '<SEQUENCE OWNER>';

 
SELECT last_number
FROM all_sequences
WHERE sequence_name = 'SEQUENCE_PENALTIES_REF'
AND sequence_owner = '<SEQUENCE OWNER>';

 
...give the CURRENT sequence values. The sequences are designed to increment in steps of 1. If the table is a central table, the central schema owner name needs to be provided (this is true for the 'offence' table). You will need to substitute an actual value into <SEQUENCE OWNER>. This has to be run directly against the database; it cannot be run from a Data Source in Confirm.

To find the owner/value, run the query:
 
SELECT asset_type_id, asset_description
FROM asset_type
WHERE asset_description = 'Central Tables';

The maximum value of offence_key and offence_ref in the offence table should be lower than or equal to the current value in the corresponding sequence. If higher, then the next sequence number in line will not be high enough. In this scenario we would need to increment the corresponding sequence the required number of times until its current value matches the maximum value of the corresponding column in the offence table. Please raise a new case with the Confirm Technical Support team for assistance, providing them with the results of the above queries.
UPDATED:  July 24, 2019