LATUN

Fix stock code for non-QMS tunas

Fishstock codes for non-QMS tuna should all be area 1, which is the entire NZ EEZ. However some landings are suffixed with the general FMA number.

For fix to be applied, require that the species_code is correct as a guard against typos in the fishstock_code rather than simply incorrect areas

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LATUN', 'edw', 'landings', 'Fix', 'Correct stock code for non-QMS tunas');

CREATE OR REPLACE FUNCTION LATUN() RETURNS VOID AS $$

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LATUN', 'landings', 'fishstock_code', id, fishstock_code,
       'ALB1'
FROM   groomed.landings
WHERE  fishstock_code IN ('ALB2','ALB3','ALB4','ALB5','ALB6','ALB7','ALB8','ALB9','ALB10')
  AND  species_code = 'ALB';

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LATUN', 'landings', 'fishstock_code', id, fishstock_code,
       'ALB1E'
FROM   groomed.landings
WHERE  fishstock_code IN ('ALB2E','ALB3E','ALB4E','ALB5E','ALB6E','ALB7E','ALB8E','ALB9E','ALB10E')
  AND  species_code = 'ALB';

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LATUN', 'landings', 'fishstock_code', id, fishstock_code,
       'SKJ1'
FROM   groomed.landings
WHERE  fishstock_code IN ('SKJ2','SKJ3','SKJ4','SKJ5','SKJ6','SKJ7','SKJ8','SKJ9','SKJ10')
  AND  species_code = 'SKJ';

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LATUN', 'landings', 'fishstock_code', id, fishstock_code,
       'SKJ1E'
FROM   groomed.landings
WHERE  fishstock_code IN ('SKJ2E','SKJ3E','SKJ4E','SKJ5E','SKJ6E','SKJ7E','SKJ8E','SKJ9E','SKJ10E')
  AND  species_code = 'SKJ';

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LATUN', 'landings', 'fishstock_code', id, fishstock_code,
       'STU1'
FROM   groomed.landings
WHERE  fishstock_code IN ('STU2','STU3','STU4','STU5','STU6','STU7','STU8','STU9','STU10')
  AND  species_code = 'STU';

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LATUN', 'landings', 'fishstock_code', id, fishstock_code,
       'STU1E'
FROM   groomed.landings
WHERE  fishstock_code IN ('STU2E','STU3E','STU4E','STU5E','STU6E','STU7E','STU8E','STU9E','STU10E')
  AND  species_code = 'STU';

UPDATE groomed.landings
SET    checks = array_append(checks,'LATUN'),
       fishstock_code = u.new
FROM   groomed.checks u
WHERE  u.code = 'LATUN'
AND    u.column = 'fishstock_code'
AND    groomed.landings.id = u.id
AND    groomed.landings.fishstock_code = u.orig;

$$ LANGUAGE SQL VOLATILE;

SELECT LATUN();

Effects of grooming

Figure 1: The number of records affected by the LATUN rule, by year and source.

Figure 2: The proportion of records affected by the LATUN rule, by form type, for all form types where some records are groomed by the LATUN rule.