ALTER TABLE TBPEDIDO_ITEM ADD ALTERANDO_CONFERENCIA INTEGER DEFAULT 0;

CREATE OR ALTER TRIGGER TTBPEDIDO_ITEM_B01UD FOR TBPEDIDO_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.PRODUTO_CODIGO <> NEW.PRODUTO_CODIGO )
       OR (OLD.TAMANHO        <> NEW.TAMANHO        )
       OR (OLD.QUANTIDADE     <> NEW.QUANTIDADE     )
       OR (OLD.QTD_EST IS DISTINCT FROM NEW.QTD_EST AND NEW.QTD_EST < OLD.QTD_EST AND NEW.QTD_EST = 0 AND NEW.SITUACAO <> 2)
       OR (OLD.QTD_AGP IS DISTINCT FROM NEW.QTD_AGP AND NEW.QTD_AGP < OLD.QTD_AGP AND NEW.QTD_AGP = 0 AND NEW.SITUACAO <> 2))
       AND NEW.ALTERANDO_CONFERENCIA <> 1) THEN
       BEGIN

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

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

           FOR
           SELECT
             Y.TABELA_ID,
             Y.QTD,
             Y.SALDO,
             COALESCE((SELECT FIRST 1
                    FT.CONTROLE
                FROM TBNFS_ITEM FT, TBNFS N
                WHERE   N.CONTROLE                 = FT.NFS_CONTROLE
                AND     FT.PEDIDO_ITEM_PE_CONTROLE = NEW.CONTROLE
                AND     FT.PEDIDO                  = NEW.PEDIDO
                AND     N.SITUACAO                 = 2
                AND     FT.QUANTIDADE              = (Y.CONFERIDO)),0) AS CONTROLE_NF_ITEM,
             COALESCE((SELECT FIRST 1
                    FCP.ID
                FROM TBPDV_CFE_ITEM FCP, TBPDV_CFE CP
                WHERE   CP.ID                      = FCP.PDV_CFE_ID
                AND     FCP.PEDIDO_ITEM_ID         = NEW.CONTROLE
                AND     FCP.PEDIDO                 = NEW.PEDIDO
                AND     CP.SITUACAO                = 2),0) AS CONTROLE_CP_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, TBPEDIDO_ITEM_SALDO C
                WHERE   A.CONFERENCIA_ID       = B.ID
                AND     ((B.TABELA             = 'PED')
                OR       (B.TABELA             = 'COM'))
                AND     B.TABELA_ID            = OLD.PEDIDO
                AND     B.STATUS               >= 2
                and     coalesce(a.status,0)   = 0
                AND     A.TABELA_ITEM_ID       = OLD.CONTROLE
                AND     C.PEDIDO_ITEM_CONTROLE = OLD.CONTROLE
                AND     C.PEDIDO               = OLD.PEDIDO
            ) Y
            INTO :CONFERENCIA_CODIGO, :QTD_TOTAL_CONFERENCIA, :SALDO_ITEM, :NF_ITEM_ID, :CP_ITEM_ID DO
            BEGIN
            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_EST IS DISTINCT FROM NEW.QTD_EST AND :NF_ITEM_ID = 0 AND :CP_ITEM_ID = 0 AND NEW.QTD_EST = 0 AND NEW.SITUACAO = 1 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
    
                IF (OLD.QTD_AGP IS DISTINCT FROM NEW.QTD_AGP AND :NF_ITEM_ID = 0 AND :CP_ITEM_ID = 0 AND NEW.QTD_AGP = 0 AND NEW.SITUACAO = 1 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         = 'PED')
            OR   (B.TABELA         = 'COM'))
            AND B.TABELA_ID        = OLD.PEDIDO
            AND B.STATUS           >= 1
            AND A.TABELA_ITEM_ID   = OLD.CONTROLE
            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;