Загрузка проданных до внедрения SetMark марок (кодов идентификации) из SR10 в список проданных

Бизнес ценность

Запрет повторной продажи ранее проданных маркированных товаров (марок)
Запрет возврата маркированных товаров (марок), которые ранее не продавались (марки, не принадлежащие юр.лицу)

--====================ВЫГРУЗКА=============================
-- выгрузка данных (кроме алкоголя)
DROP TABLE IF EXISTS _markTypes;
CREATE TEMP TABLE _markTypes (id_retail int, re text, is_group boolean, id_mark int);
INSERT INTO _markTypes VALUES 
  (2, '^01(\d{14})21(\S{13})',  false, 3),
  (3, '^01(\d{14})21(\S{13})',  false, 4),
  (4, '^01(\d{14})21(\S{13})',  false, 5),
  (5, '^01(\d{14})21(\S{13})',  false, 6),
  (10, '^01(\d{14})21(\S{13})', false, 11),
  (6, '^01(\d{14})21(\S{20})',  false, 7),
  (7, '^01(\d{14})21(\S{6})',   false, 8),
  (9, '^01(\d{14})21(\S{13})',  true,  10),
  (1, '^01(\d{14})21(\S{7})',   true,  2);

-- Сохранить результаты запроса в csv файл 
SELECT DISTINCT 
          (SELECT shop FROM od_seller LIMIT 1) AS shop,
           Os.cashnum AS cash,
           Opc.numberfield AS check_number,
           Opc.datecommit AS request_timestamp,
           Opc.datecreate AS creation_timestamp,
           Opt.item, 
           Opt.barcode,
           M.id_mark AS product_type,
           (regexp_matches(Op.excise, M.re, ''))[1] || (regexp_matches(Op.excise, M.re, ''))[2] AS mark,
           Op.rccw,
           Op.gtd_number AS gtd,
           Opp.value AS country,
           (Opp2.value::json)->>'inn' AS inn,
           M.is_group
FROM       Od_position AS Op
INNER JOIN Od_product AS Opt ON Opt.hash = Op.product_hash
INNER JOIN Od_purchase AS Opc ON Opc.id = Op.id_purchase
INNER JOIN Od_shift AS Os ON Os.id = Opc.id_shift
INNER JOIN _markTypes AS M ON M.id_retail = Opt.mark_type
LEFT JOIN  Od_position_properties AS Opp ON Opp.position_id = Op.id AND Opp.name = 'countryName'
LEFT JOIN  Od_position_properties AS Opp2 ON Opp2.position_id = Op.id AND Opp.name = 'counterparty'
WHERE      Opc.operationtype 
  AND      Opc.checkstatus = 0 
  AND NOT EXISTS (SELECT 1
                  FROM   Od_purchase AS Opc2
                  WHERE  Opc2.id_purchaseref = Opc.id);
          
--===========================ЗАГРУЗКА=======================
--промежуточная таюлица для переноса данных
DROP TABLE IF EXISTS _sale_marks_load;
CREATE TABLE _sale_marks_load
(
  shop bigint,
  cash bigint,
  mark text NOT NULL,
  product_type smallint NOT NULL,
  barcode text,
  item text,
  check_number text,
  creation_timestamp text,
  request_timestamp text,
  parent text,
  gtd text,
  rccw text,
  country text,
  inn text,
  alcocode text,
  is_group boolean
);
COPY _sale_marks_load (shop, cash, check_number, request_timestamp, creation_timestamp, 
                        item, barcode, product_type, mark, rccw, gtd, country, inn, is_group)
FROM 'D:/MARK8.csv' WITH DELIMITER ';' CSV HEADER ;

-- негрупповые товары
INSERT INTO sale_marks_common(shop, cash, check_number, request_timestamp, creation_timestamp, 
                              item, barcode, product_type, mark, rccw, gtd, country, inn)
SELECT Sml.shop, Sml.cash, Sml.check_number, Sml.request_timestamp::timestamp, Sml.creation_timestamp::timestamp, 
       Sml.item, Sml.barcode, Sml.product_type, Sml.mark, Sml.rccw, Sml.gtd, Sml.country, Sml.inn
FROM      _sale_marks_load AS Sml
LEFT JOIN sale_marks_common AS Smc ON Smc.mark = Sml.mark
WHERE NOT Sml.is_group
  AND Smc.* IS NULL;

-- групповые товары
INSERT INTO sale_marks_group(shop, cash, check_number, request_timestamp, creation_timestamp, 
                              item, barcode, product_type, mark, rccw, gtd, country, inn)
SELECT Sml.shop, Sml.cash, Sml.check_number, Sml.request_timestamp::timestamp, Sml.creation_timestamp::timestamp, 
       Sml.item, Sml.barcode, Sml.product_type, Sml.mark, Sml.rccw, Sml.gtd, Sml.country, Sml.inn
FROM      _sale_marks_load AS Sml
LEFT JOIN sale_marks_common AS Smc ON Smc.mark = Sml.mark
WHERE Sml.is_group
  AND Smc.* IS NULL;

*Для всех видов маркировки