InovacaoMediaBrasil/ProcessWorkflowsAndDatabaseScripts

USE SANKHYA_PRODUCAO;

DECLARE 
@ORDERID VARCHAR(100),
@PEDORIGINAL INT,
@CODREENVIO INT,
@NUNOTA INT,
@CODUSU INT,
@DATA DATETIME,
@CODVEND INT;

DECLARE cBonificado CURSOR LOCAL FAST_FORWARD FORWARD_ONLY FOR
    SELECT TOP 200 P.ORDERID, P.PEDORIGINAL, C.CODVEND, C.CODUSUINC, C.NUNOTA, C.DTFATUR, C.AD_CODREENVIO
    FROM sankhya.TGFCAB AS C WITH (NOLOCK)
    INNER JOIN sankhya.AD_PEDIDOVTEXSC AS P WITH (NOLOCK)
    ON C.AD_PEDORIGINAL = P.PEDORIGINAL
    WHERE C.CODTIPOPER = 504
    AND C.AD_BONIFICADO = 'S'
    AND C.AD_CODREENVIO = 0;

OPEN cBonificado;

FETCH NEXT FROM cBonificado INTO @ORDERID, @PEDORIGINAL, @CODVEND, @CODUSU, @NUNOTA, @DATA, @CODREENVIO;

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @CODREENVIO = COUNT(*) + 1 
    FROM sankhya.AD_PEDIDOVTEXSCREENVIO WITH (NOLOCK)
    WHERE PEDORIGINAL = @PEDORIGINAL;

    PRINT @PEDORIGINAL;

    IF NOT EXISTS (
        SELECT 1 
        FROM sankhya.AD_PEDIDOVTEXSCREENVIOFLUXO WITH (NOLOCK)
        WHERE NUNOTA = @NUNOTA)
    BEGIN
        INSERT INTO sankhya.AD_PEDIDOVTEXSCREENVIO (ORDERID, PEDORIGINAL, CODREENVIO, CODVEND)
        VALUES (@ORDERID, @PEDORIGINAl, @CODREENVIO, @CODVEND);

        INSERT INTO sankhya.AD_PEDIDOVTEXSCREENVIOFLUXO (ORDERID, PEDORIGINAL, CODREENVIO, NUNOTA, OCORRENCIA, DATA, CODUSU)
        VALUES (@ORDERID, @PEDORIGINAl, @CODREENVIO, @NUNOTA, 'P', @DATA, @CODUSU);
    END

    DELETE FROM sankhya.AD_PEDIDOVTEXSCFLUXO WHERE NUNOTA = @NUNOTA;
    DELETE FROM sankhya.AD_PEDIDOVTEXSCREENVIOFLUXO WHERE NUNOTA = @NUNOTA AND CODREENVIO != @CODREENVIO;
    ALTER TABLE sankhya.TGFCAB DISABLE TRIGGER TRG_UPD_TGFCAB_BONIFICADO_INVALIDO;
    UPDATE sankhya.TGFCAB SET AD_CODREENVIO = @CODREENVIO WHERE NUNOTA = @NUNOTA;
    ALTER TABLE sankhya.TGFCAB ENABLE TRIGGER TRG_UPD_TGFCAB_BONIFICADO_INVALIDO; 
    
    FETCH NEXT FROM cBonificado INTO @ORDERID, @PEDORIGINAL, @CODVEND, @CODUSU, @NUNOTA, @DATA, @CODREENVIO;
END

CLOSE cBonificado;
DEALLOCATE cBonificado;