SET TERM ^ ;

CREATE OR ALTER TRIGGER TBPDV_CONFERENCIA_ITEM_B01UD FOR TBPDV_CONFERENCIA_ITEM
ACTIVE BEFORE UPDATE OR DELETE POSITION 0
AS
DECLARE VARIABLE CONFERENCIA_CODIGO    VARCHAR(500);
DECLARE VARIABLE QTD_TOTAL_CONFERENCIA NUMERIC(18,4);
DECLARE VARIABLE SALDO_ITEM            NUMERIC(18,4);
DECLARE VARIABLE QTD_ALOCADA           NUMERIC(18,4);
DECLARE VARIABLE NF_ITEM_ID            INTEGER;
DECLARE VARIABLE CP_ITEM_ID            INTEGER;
BEGIN

    IF(UPDATING) THEN
    BEGIN
       IF(OLD.PDV_CONFERENCIA_ID <> NEW.PDV_CONFERENCIA_ID)THEN
       BEGIN
          EXCEPTION "..." 'ATENCAO<BR> ESSA OPERACAO TENTOU VINCULAR O ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' AO PRE-PEDIDO '||FN_LPAD(NEW.PDV_CONFERENCIA_ID,4,0)||'<BR> PRE-PEDIDO ATUAL: '||FN_LPAD(OLD.PDV_CONFERENCIA_ID,4,0)||'. OPERACAO CANCELADA!';
       END

       IF(((OLD.PRODUTO_ID <> NEW.PRODUTO_ID )
       OR (OLD.TAMANHO        <> NEW.TAMANHO        )
       OR (OLD.QUANTIDADE     <> NEW.QUANTIDADE     )
       OR (OLD.QTD_ESTOQUE IS DISTINCT FROM NEW.QTD_ESTOQUE AND NEW.QTD_ESTOQUE < OLD.QTD_ESTOQUE AND NEW.QTD_ESTOQUE = 0 AND NEW.SITUACAO <> 3))) THEN
       BEGIN

           IF((OLD.QTD_ESTOQUE IS DISTINCT FROM NEW.QTD_ESTOQUE AND NEW.QTD_ESTOQUE < OLD.QTD_ESTOQUE AND NEW.QTD_ESTOQUE = 0 AND NEW.SITUACAO <> 3))THEN
           BEGIN
              :QTD_ALOCADA = (OLD.QTD_ESTOQUE);
           END

           FOR
           SELECT
             Y.TABELA_ID,
             Y.QTD,
             Y.SALDO,
             0 AS CONTROLE_NF_ITEM
             FROM (
                SELECT
                    COALESCE(LIST(FN_LPAD(B.TABELA_ID, 8, '0')||B.SEQUENCIA, ', '), '') AS TABELA_ID,
                    COALESCE(SUM(A.QTD_CONFERIDA), 0) AS CONFERIDO,
                    COALESCE(SUM(A.QTD_TOTAL), 0) AS QTD,
                    COALESCE(SUM(C.QUANTIDADE), 0) AS SALDO
                FROM TBCONFERENCIA_ITEM A, TBCONFERENCIA B, TBPDV_CONFERENCIA_ITEM_SALDO C
                WHERE   A.CONFERENCIA_ID          = B.ID
                AND     (B.TABELA                 = 'PRE')
                AND     B.TABELA_ID               = OLD.PDV_CONFERENCIA_ID
                AND     B.STATUS                 >= 2
                AND     COALESCE(A.STATUS,0)      = 0
                AND     A.TABELA_ITEM_ID          = OLD.ID
                AND     C.PDV_CONFERENCIA_ITEM_ID = OLD.ID
                AND     C.PDV_CONFERENCIA_ID      = OLD.PDV_CONFERENCIA_ID
            ) Y
            INTO :CONFERENCIA_CODIGO, :QTD_TOTAL_CONFERENCIA, :SALDO_ITEM, :NF_ITEM_ID DO
            BEGIN
            END

                /* :CP_ITEM_ID = COALESCE((SELECT FIRST 1
                    FCP.ID
                FROM TBPDV_CFE_ITEM FCP, TBPDV_CFE CP
                WHERE   CP.ID                      = FCP.PDV_CFE_ID
                AND     FCP.CONFERENCIA_ITEM_ID    = NEW.ID
                AND     FCP.CONFERENCIA_ID         = NEW.PDV_CONFERENCIA_ID
                AND     CP.SITUACAO                = 2),0) AS CONTROLE_CP_ITEM */
                :CP_ITEM_ID = 0;

                IF (OLD.PRODUTO_ID <> NEW.PRODUTO_ID AND :CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'. OPERACAO CANCELADA!';
                END

                IF (OLD.TAMANHO <> NEW.TAMANHO AND :CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'. OPERACAO CANCELADA!';
                END
    
                IF (OLD.QUANTIDADE <> NEW.QUANTIDADE AND :CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    IF (NEW.QUANTIDADE < :QTD_TOTAL_CONFERENCIA) THEN
                    BEGIN
                        EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'. OPERACAO CANCELADA!';
                    END
                END

                IF (OLD.QTD_ESTOQUE IS DISTINCT FROM NEW.QTD_ESTOQUE AND :NF_ITEM_ID = 0 AND :CP_ITEM_ID = 0 AND NEW.QTD_ESTOQUE = 0 AND NEW.SITUACAO <> 3 AND :CONFERENCIA_CODIGO <> '') THEN
                BEGIN
                    EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'.<BR> OPERACAO DE DESLOCACAO CANCELADA!';
                END

        END
    END

    IF(DELETING)THEN
    BEGIN
          SELECT
              COALESCE(LIST(FN_LPAD(B.TABELA_ID, 8, '0')||B.SEQUENCIA, ', '), ''),
              COALESCE(SUM(A.QTD_TOTAL), 0)
            FROM TBCONFERENCIA_ITEM A, TBCONFERENCIA B
            WHERE A.CONFERENCIA_ID = B.ID
            AND ((B.TABELA         = 'PRE'))
            AND B.TABELA_ID        = OLD.PDV_CONFERENCIA_ID
            AND B.STATUS           >= 1
            AND A.TABELA_ITEM_ID   = OLD.ID
            INTO :CONFERENCIA_CODIGO, :QTD_TOTAL_CONFERENCIA;

            IF (:CONFERENCIA_CODIGO <> '') THEN
            BEGIN
                EXCEPTION "..." 'ITEM: '||FN_LPAD(OLD.SEQUENCIA, 3, '0')||' JA VINCULADO NA(S) CONFERENCIA(S): '||:CONFERENCIA_CODIGO||'. OPERACAO CANCELADA!';
            END        
    END
   
END^

SET TERM ; ^

