LAFLA
Fishstock code is for a flatfish species
Occasionally (and now exclusively) flatfish landings are recorded using the species specific code (e.g. GFL) instead of the general flatfish code (FLA). This check is done early so that total flatfish landings are checked in subsequent landings checks.
The original version of this rule considered the following as FLA:
- QMS species: ‘BFL’,‘BRI’,‘ESO’,‘GFL’,‘LSO’,‘SFL’,‘TUR’,‘YBF’
- Assumed misspellings: ‘BLF’,‘FLO’,‘SOL’,‘GBL’,‘SLS’
- Non QMS flatfish: ‘WIT’,‘BOT’,‘MAN’,‘SDF’
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LAFLA', 'edw', 'landings', 'Fix', 'Correct landings using a flatfish species code to FLA');
CREATE OR REPLACE FUNCTION LAFLA() RETURNS VOID AS $$
INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LAFLA', 'landings', 'species_code', id, species_code, 'FLA'
FROM groomed.landings
WHERE substring(species_code from 1 for 3) IN ('BFL', 'BRI', 'GFL', 'LSO', 'ESO', 'SFL', 'TUR', 'YBF');
INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LAFLA', 'landings', 'fishstock_code', id, fishstock_code,
'FLA' || substring(fishstock_code from 4 for 2)
FROM groomed.landings
WHERE substring(fishstock_code from 1 for 3) IN ('BFL', 'BRI', 'GFL', 'LSO', 'ESO', 'SFL', 'TUR', 'YBF');
UPDATE groomed.landings
SET checks = array_append(checks,'LAFLA'),
species_code = u.new
FROM groomed.checks u
WHERE u.code = 'LAFLA'
AND u.column = 'species_code'
AND groomed.landings.id = u.id
AND groomed.landings.species_code = u.orig;
UPDATE groomed.landings
SET checks = array_append(checks,'LAFLA'),
fishstock_code = u.new
FROM groomed.checks u
WHERE u.code = 'LAFLA'
AND u.column = 'fishstock_code'
AND groomed.landings.id = u.id
AND groomed.landings.fishstock_code = u.orig;
$$ LANGUAGE SQL VOLATILE;
SELECT LAFLA();Effects of grooming
