CREATE OR ALTER procedure SPD_CONFERENCIA_ITEM (
    CONFERENCIA integer not null)
as
declare variable HISTORICO varchar(5000);
declare variable CONFERENCIA_ID integer;
declare variable TABELA_ITEM_ID bigint;
declare variable COUNT_DUPS integer;
declare variable MAX_ID integer;
declare variable PEDIDO integer;
declare variable ITEM_SEQ varchar(10);
declare variable TABELA varchar(10);
BEGIN

    FOR
        SELECT
            Y.CONFERENCIA_ID,
            Y.TABELA_ITEM_ID,
            Y.TABELA,
            Y.COUNT_DUPS,
            Y.MAX_ID
        
        FROM (
            SELECT
                X.CONFERENCIA_ID,
                X.TABELA_ITEM_ID,
                X.TABELA,
                X.COUNT_DUPS,
                X.MAX_ID
            
            FROM (
                SELECT
                    T.ID,
                    T.CONFERENCIA_ID,
                    T.PRODUTO_ID,
                    T.TAMANHO,
                    T.QTD_TOTAL,
                    T.QTD_CONFERIDA,
                    T.TABELA_ITEM_ID,
                    T.QTD_ALOCADA,
                    T.QTD_DISPONIVEL,
                    T.STATUS,
                    T.NF_ID,
                    A.TABELA,
                    COALESCE((SELECT COUNT(X.TABELA_ITEM_ID) FROM TBCONFERENCIA_ITEM X WHERE X.TABELA_ITEM_ID = T.TABELA_ITEM_ID AND X.CONFERENCIA_ID = A.ID), 0) AS COUNT_DUPS,
                    COALESCE((SELECT MAX(X.ID)               FROM TBCONFERENCIA_ITEM X WHERE X.TABELA_ITEM_ID = T.TABELA_ITEM_ID AND X.CONFERENCIA_ID = A.ID AND X.STATUS = '0'), 0) AS MAX_ID
                
                FROM TBCONFERENCIA_ITEM T
                INNER JOIN TBCONFERENCIA A ON A.ID = T.CONFERENCIA_ID
                WHERE T.ID > 0
                AND T.TABELA_ITEM_ID > 0
                AND T.STATUS = '0'
                AND A.STATUS IN (1, 2, 3)
        
                AND A.ID = :CONFERENCIA
            
            ) X
            WHERE X.COUNT_DUPS > 1
            
            ORDER BY X.TABELA_ITEM_ID
        
        ) Y
        
        GROUP BY
            Y.CONFERENCIA_ID,
            Y.TABELA_ITEM_ID,
            Y.TABELA,
            Y.COUNT_DUPS,
            Y.MAX_ID

    INTO
        :CONFERENCIA_ID,
        :TABELA_ITEM_ID,
        :TABELA,
        :COUNT_DUPS,
        :MAX_ID

    DO
    BEGIN

        :HISTORICO = '';

        IF (:MAX_ID > 0 AND :CONFERENCIA_ID > 0 AND :TABELA = 'PED') THEN
        BEGIN
            /* Exclusão de Duplicata */
            DELETE FROM TBCONFERENCIA_ITEM T WHERE T.CONFERENCIA_ID = :CONFERENCIA_ID AND T.ID = :MAX_ID;

            /* Histórico de Exclusão */
            SELECT FIRST 1
                P.PEDIDO,
                FN_LPAD(PI.SEQUENCIA, 3, '0') AS ITEM_SEQ

            FROM TBPEDIDO_ITEM PI
            INNER JOIN TBPEDIDO P ON P.PEDIDO = PI.PEDIDO
            WHERE PI.CONTROLE = :TABELA_ITEM_ID INTO :PEDIDO, :ITEM_SEQ;


            :HISTORICO = 'ITEM: '|| :ITEM_SEQ ||' EXCLUIDO NA CONFERENCIA: '|| FN_LPAD(:CONFERENCIA_ID, 6, '0') ||'DO PEDIDO: '|| :PEDIDO ||'. EXCLUSAO DE DUPLICATAS AUTOMATICA QTD.DUP: '|| :COUNT_DUPS ||'';

            EXECUTE PROCEDURE SPI_HISTORICO('TBCONFERENCIA_ITEM', :CONFERENCIA_ID, FN_ELIPSE(:HISTORICO, 500));
        END

        

        IF (:MAX_ID > 0 AND :CONFERENCIA_ID > 0 AND :TABELA = 'PRE') THEN
        BEGIN
            /* Exclusão de Duplicata */
            DELETE FROM TBCONFERENCIA_ITEM T WHERE T.CONFERENCIA_ID = :CONFERENCIA_ID AND T.ID = :MAX_ID;

            /* Histórico de Exclusão */
            SELECT FIRST 1
                P.ID,
                FN_LPAD(PI.SEQUENCIA, 3, '0') AS ITEM_SEQ

            FROM TBPDV_CONFERENCIA_ITEM PI
            INNER JOIN TBPDV_CONFERENCIA P ON P.ID = PI.PDV_CONFERENCIA_ID
            WHERE PI.ID = :TABELA_ITEM_ID INTO :PEDIDO, :ITEM_SEQ;

            :HISTORICO = 'ITEM: '|| :ITEM_SEQ ||' EXCLUIDO NA CONFERENCIA: '|| FN_LPAD(:CONFERENCIA_ID, 6, '0') ||'DO PRE-PEDIDO: '|| :PEDIDO ||'. EXCLUSAO DE DUPLICATAS AUTOMATICA QTD.DUP: '|| :COUNT_DUPS ||'';

            EXECUTE PROCEDURE SPI_HISTORICO('TBCONFERENCIA_ITEM', :CONFERENCIA_ID, FN_ELIPSE(:HISTORICO, 500));
        END

    END
END