Tail Mark on Current register appears as "Available"

Owned by kurt.martin
Sept 29, 2022
1 min read

See PBI#197422 - August 2022

GKMA was in use on the current register. It also appeared on the list of available tail marks when queried.

Query Available Marks

This condition had occurred in June 2021, but was not noticed until August 2022.

Page 31 of the developers guide provided a solution. GKMA had a Mark_status code = 1, where it should have been a 3. So it was corrected.

However, closer inspection of the entire register showed 10 other marks having the same condition.

The newest occurrence was April 2022, by “Cleanup” - allocated by way of package CARS.PK_SCHEDULED_JOBS → CARS_DAILY_RESET_SPF .

These marks were also corrected but we continue to monitor for new occurrences.

This query will return the records that are on the current register where mark_status = 1 (mark is available)

SELECT X., Y.
FROM CARS_CANADIAN_MARKS X, CARS_DAILY_CAIS_MASTER Y
WHERE MARK_STATUS = 1
AND TRIM(X.MARK) = TRIM(Y.MARK) ;

This query will return the most recent records affected by “Cleanup” in CARS_DAILY_RESET_SPF


Select * from CARS_CANADIAN_MARKS
where MODIFIED_DATE is not null
AND MODIFIED_BY = 'Cleanup'
ORDER BY MODIFIED_DATE DESC;