#MegaERP - Trigger para não permitir duplicar itens com mesma descrição + Descrição Abreviada que inclua o Código do Item no início da Descrição + Remover Múltiplo espaço em branco no campo de descrição

MegaERP_T_CLI_EST_PRODUTOS.sql

create or replace TRIGGER MGCLI.T_CLI_EST_PRODUTOS 
FOR INSERT OR UPDATE ON MGADM.EST_PRODUTOS 
COMPOUND TRIGGER
    
    TYPE TYP_CLI_RC_PRODUTOS IS RECORD(
        PRO_TAB_IN_CODIGO   MGADM.EST_PRODUTOS.PRO_TAB_IN_CODIGO%TYPE,
        PRO_PAD_IN_CODIGO   MGADM.EST_PRODUTOS.PRO_PAD_IN_CODIGO%TYPE,
        PRO_IN_CODIGO       MGADM.EST_PRODUTOS.PRO_IN_CODIGO%TYPE,
        PRO_ST_DESCRICAO    MGADM.EST_PRODUTOS.PRO_ST_DESCRICAO%TYPE
    );
    
    TYPE TYP_CLI_TB_PRODUTOS IS TABLE OF TYP_CLI_RC_PRODUTOS
        INDEX BY BINARY_INTEGER;
    
    rPRODUTOS TYP_CLI_TB_PRODUTOS;
    
    -- Executed before each row change- :NEW, :OLD are available
    BEFORE EACH ROW IS
        vDATA_LENGTH SYS.ALL_TAB_COLUMNS.DATA_LENGTH%TYPE;
        i INTEGER;
        
        vUSU_IN_CODIGO MGGLO.glo_grupo_usuario.gru_in_codigo%TYPE;
        vGRU_IN_CODIGO MGGLO.glo_grupo_usuario.pai_gru_in_codigo%TYPE;
    BEGIN
        
        SELECT     DATA_LENGTH 
        INTO      vDATA_LENGTH
        FROM      SYS.ALL_TAB_COLUMNS 
        WHERE OWNER       = 'MGADM' 
        AND   TABLE_NAME  = 'EST_PRODUTOS' 
        AND   COLUMN_NAME = 'PRO_ST_DESCRICAOPDV';
        
        IF (:NEW.PRO_ST_DESCRICAOPDV IS NULL OR NVL(:NEW.PRO_ST_DESCRICAOPDV, '') = '') THEN
        :NEW.PRO_ST_DESCRICAOPDV := SUBSTR(
            :NEW.PRO_IN_CODIGO || ' - ' || :NEW.PRO_ST_DESCRICAO,
            1,
            vDATA_LENGTH
            );
        END IF;
        
        SELECT     DATA_LENGTH 
        INTO      vDATA_LENGTH
        FROM      SYS.ALL_TAB_COLUMNS 
        WHERE OWNER       = 'MGADM' 
        AND   TABLE_NAME  = 'EST_PRODUTOS' 
        AND   COLUMN_NAME = 'PRO_ST_DESCRICAOPDV';
        
        IF (:NEW.PRO_ST_DESCRICAONFE IS NULL) THEN
            :NEW.PRO_ST_DESCRICAONFE := SUBSTR(:NEW.PRO_ST_DESCRICAO,1,vDATA_LENGTH);
        END IF;
        
        -- Remover múltiplos espaços em Branco entre as palavras, também no inicio e final
        :NEW.PRO_ST_DESCRICAO     := TRIM(regexp_replace(:NEW.PRO_ST_DESCRICAO     , '[[:space:]]+', chr(32)));
        :NEW.PRO_ST_DESCRICAONFE  := TRIM(regexp_replace(:NEW.PRO_ST_DESCRICAONFE  , '[[:space:]]+', chr(32)));
        :NEW.PRO_ST_DESCRICAOPDV  := TRIM(regexp_replace(:NEW.PRO_ST_DESCRICAOPDV  , '[[:space:]]+', chr(32)));
        
        vUSU_IN_CODIGO := MGGLO.GLO_PCK_CONTEXTO.F_GETVARIAVEL('vUSU_IN_CODIGO');
        
        i := rPRODUTOS.COUNT + 1;
        rPRODUTOS(i).PRO_TAB_IN_CODIGO  := :NEW.PRO_TAB_IN_CODIGO;
        rPRODUTOS(i).PRO_PAD_IN_CODIGO  := :NEW.PRO_PAD_IN_CODIGO;
        rPRODUTOS(i).PRO_IN_CODIGO      := :NEW.PRO_IN_CODIGO;
        rPRODUTOS(i).PRO_ST_DESCRICAO   := :NEW.PRO_ST_DESCRICAO;
        
    END BEFORE EACH ROW;
    
    --Executed after DML statement
    AFTER STATEMENT IS
        vPRO_ST_DESCRICAO_NEW MGADM.EST_PRODUTOS.PRO_ST_DESCRICAO%TYPE;
    BEGIN
        --IF INSERTING OR UPDATING THEN
            FOR i IN NVL(rPRODUTOS.FIRST, 1) .. NVL(rPRODUTOS.LAST,0)
            LOOP
                
                FOR cPRO_HAS_MANY IN (
                    SELECT LISTAGG(PRO_IN_CODIGO, ', ') WITHIN GROUP (ORDER BY PRO_IN_CODIGO) L_PRO_IN_CODIGO
                    FROM MGADM.EST_PRODUTOS P
                    WHERE   P.PRO_TAB_IN_CODIGO =  rPRODUTOS(i).PRO_TAB_IN_CODIGO
                    AND     P.PRO_PAD_IN_CODIGO =  rPRODUTOS(i).PRO_PAD_IN_CODIGO
                    AND     P.PRO_IN_CODIGO     <> rPRODUTOS(i).PRO_IN_CODIGO
                    AND     P.PRO_ST_DESCRICAO  =  rPRODUTOS(i).PRO_ST_DESCRICAO
                    HAVING COUNT (*) >= 1
                ) LOOP
                    RAISE_APPLICATION_ERROR(-20101, 'Descrição do Item ['|| rPRODUTOS(i).PRO_ST_DESCRICAO || CASE WHEN UPDATING THEN ']Cód.['|| rPRODUTOS(i).PRO_IN_CODIGO ELSE '' END ||'] já existe no(s) produto(s) ['|| cPRO_HAS_MANY.L_PRO_IN_CODIGO ||']. [MGCLI.T_CLI_EST_PRODUTOS]');
                END LOOP;
            END LOOP;
            
        --END IF;
    END AFTER STATEMENT;

END;

O código abaixo mostra os itens duplicados com mesma descrição:
SELECT
TRIM(
regexp_replace(
PRO_ST_DESCRICAO
, '[[:space:]]+', chr(32))
) PRO_ST_DESCRICAO, COUNT(*), LISTAGG(PRO_IN_CODIGO, ', ') WITHIN GROUP (ORDER BY PRO_IN_CODIGO) "L_PRO",
LISTAGG(PRO_ST_DEFITEM, ', ') WITHIN GROUP (ORDER BY PRO_ST_DEFITEM) "L_DEF"
FROM MGADM.EST_PRODUTOS
GROUP BY
TRIM(
regexp_replace(
PRO_ST_DESCRICAO --'STEVIA - AG - AG'
, '[[:space:]]+', chr(32))
)
HAVING COUNT(*) > 1
ORDER BY 4
;

O Código abaixo mostra Itens com mesma descrição, removendo espaço em branco entre as palavras para compor uma string só e identificar o conjunto de todos caracteres que resultam em uma mesma cadeia:
SELECT
TRIM(
regexp_replace(
regexp_replace(
PRO_ST_DESCRICAO
, '([[:space:]])', '')
, '[[:space:]]+', chr(32)
)) PRO_ST_DESCRICAO, COUNT(*),
LISTAGG(PRO_IN_CODIGO, ', ') WITHIN GROUP (ORDER BY PRO_IN_CODIGO) "L_PRO",
LISTAGG(PRO_ST_DEFITEM, ', ') WITHIN GROUP (ORDER BY PRO_ST_DEFITEM) "L_DEF"
FROM MGADM.EST_PRODUTOS
GROUP BY
TRIM(
regexp_replace(
regexp_replace(
PRO_ST_DESCRICAO
, '([[:space:]])', '')
, '[[:space:]]+', chr(32)
))
HAVING COUNT(*) > 1
ORDER BY 4
;