InovacaoMediaBrasil/ProcessWorkflowsAndDatabaseScripts

USE SANKHYA_PRODUCAO;

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

DECLARE cTrocas CURSOR FAST_FORWARD FOR
SELECT TOP 10000 H.NUNOTA, H.DHALTER, H.CODUSU
FROM sankhya.TGFHPE AS H WITH (NOLOCK)
WHERE H.EVENTO != 'C'
AND (
    (
        EXISTS (SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCFLUXO AS F WITH (NOLOCK) WHERE F.NUNOTA = H.NUNOTA AND F.OCORRENCIA = 'P') 
        AND NOT EXISTS(SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCFLUXO AS F WITH (NOLOCK) WHERE F.NUNOTA = H.NUNOTA AND F.OCORRENCIA = 'A')
    ) OR (
        EXISTS (SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCREENVIOFLUXO AS F WITH (NOLOCK) WHERE F.NUNOTA = H.NUNOTA AND F.OCORRENCIA = 'P')
        AND NOT EXISTS (SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCREENVIOFLUXO AS F WITH (NOLOCK) WHERE F.NUNOTA = H.NUNOTA AND F.OCORRENCIA = 'A')
    )
)
ORDER BY DHALTER DESC;

OPEN cTrocas;

FETCH NEXT FROM cTrocas INTO
@NUNOTA, @DATA, @CODUSU;

WHILE @@FETCH_STATUS = 0
BEGIN
    
    SELECT    @PEDORIGINAL =    F.PEDORIGINAL,
            @ORDERID =        F.ORDERID            
    FROM sankhya.AD_PEDIDOVTEXSCFLUXO AS F WITH (NOLOCK)
    WHERE F.NUNOTA = @NUNOTA AND F.OCORRENCIA = 'P';

    IF @PEDORIGINAL IS NULL
        SELECT    @PEDORIGINAL =    F.PEDORIGINAL,
                @ORDERID =        F.ORDERID,
                @CODREENVIO =    F.CODREENVIO        
        FROM sankhya.AD_PEDIDOVTEXSCREENVIOFLUXO AS F WITH (NOLOCK)
        WHERE F.NUNOTA = @NUNOTA AND F.OCORRENCIA = 'P';

    IF @PEDORIGINAL IS NULL
    BEGIN
        FETCH NEXT FROM cTrocas INTO
        @NUNOTA, @DATA, @CODUSU;

        CONTINUE;
    END

    IF @CODREENVIO IS NULL
    AND NOT EXISTS (SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCFLUXO AS F WITH (NOLOCK) WHERE F.NUNOTA = @NUNOTA AND OCORRENCIA = 'A')
        INSERT INTO sankhya.AD_PEDIDOVTEXSCFLUXO (CODUSU, DATA, NUNOTA, OCORRENCIA, ORDERID, PEDORIGINAL)
        VALUES (@CODUSU, @DATA, @NUNOTA, 'A', @ORDERID, @PEDORIGINAL);
    ELSE IF @CODREENVIO IS NOT NULL
    AND NOT EXISTS (SELECT 1 FROM sankhya.AD_PEDIDOVTEXSCREENVIOFLUXO AS F WITH (NOLOCK) WHERE F.NUNOTA = @NUNOTA AND OCORRENCIA = 'A')
        INSERT INTO sankhya.AD_PEDIDOVTEXSCREENVIOFLUXO (CODREENVIO, CODUSU, DATA, NUNOTA, OCORRENCIA, ORDERID, PEDORIGINAL)
        VALUES (@CODREENVIO, @CODUSU, @DATA, @NUNOTA, 'A', @ORDERID, @PEDORIGINAL);
    
    FETCH NEXT FROM cTrocas INTO
    @NUNOTA, @DATA, @CODUSU;
END
CLOSE cTrocas;
DEALLOCATE cTrocas;