IF EXISTS
(
    SELECT *
    FROM SYS.PROCEDURES
    WHERE NAME = 'P_EST_PED_ENVIO_SAP_PENDENTE'
)
    BEGIN
        DROP PROCEDURE P_EST_PED_ENVIO_SAP_PENDENTE;
    END;
GO
GO
CREATE PROCEDURE P_EST_PED_ENVIO_SAP_PENDENTE
AS
     BEGIN
         SET NOCOUNT ON;
         CREATE TABLE #RS_PED_ENVIO_PENDENTE
         (CD_EMP    INT NOT NULL,
          CD_FILIAL INT NOT NULL,
          CD_PED    INT NOT NULL,
          STATUS    INT NOT NULL
         );
         CREATE TABLE #RS_PED_ENT
         (CD_EMP       INT NOT NULL,
          CD_FILIAL    INT NOT NULL,
          CD_PED       INT NOT NULL,
          CD_NF        INT NOT NULL,
          NR_PROTOCOLO VARCHAR(200)
         );
         CREATE TABLE #RS_ENT_ORIGEM
         (CD_EMP      INT NOT NULL,
          CD_FILIAL   INT NOT NULL,
          CD_NF       INT NOT NULL,
          DT_EMI_NF   DATE,
          NR_NF       INT,
          SERIE       VARCHAR(10),
          CD_FORN     INT,
          QT_IT_TOTAL MONEY
         );
         CREATE TABLE #RS_ENT_ORIGEM_IT
         (CD_EMP    INT NOT NULL,
          CD_FILIAL INT NOT NULL,
          CD_NF     INT NOT NULL,
          CD_PROD   INT NOT NULL,
          QT_IT     MONEY
         );
         CREATE TABLE #RS_SAI_DEV
         (CD_EMP                INT NOT NULL,
          CD_FILIAL             INT NOT NULL,
          CD_NF                 INT NOT NULL,
          CD_PED                INT NOT NULL,
          DT_EMI_NF             DATE,
          NF_NF                 INT,
          SERIE                 VARCHAR(10),
          FLAG_MOV_PROD_VENCIDO INT,
          VLR_NF                MONEY,
          CD_CLI                INT,
          QT_IT_TOTAL           MONEY
         );
         CREATE TABLE #RS_SAI_DEV_IT
         (CD_EMP       INT NOT NULL,
          CD_FILIAL    INT NOT NULL,
          CD_NF        INT NOT NULL,
          CD_PROD      INT NOT NULL,
          CD_PROD_FORN VARCHAR(200),
          CD_NF_REF    INT,
          NR_LOTE      VARCHAR(200),
          DT_VALID     DATE,
          QT_IT        MONEY,
          QT_EMB       MONEY
         );
         CREATE TABLE #RS_RETURN
         (ID                    INT IDENTITY(1, 1)
                                    NOT NULL,
          CD_PED                INT,
          DT_EMI_NF             DATE,
          CD_NF_ORIG            INT,
          NR_NF                 INT,
          SERIE                 VARCHAR(10),
          CD_FIL_FORN           VARCHAR(200),
          CD_FILIAL             INT,
          CD_NF                 INT,
          CD_PROD               VARCHAR(200),
          CD_PROD_GEST          INT,
          NR_LOTE               VARCHAR(200),
          DT_VALID              DATE,
          QTDE_IT               MONEY,
          QTDE_IT_DEV           MONEY,
          OPERADOR              VARCHAR(200),
          CONTATO               VARCHAR(200),
          DT_EMI_NF_DEV         DATE,
          NR_NF_DEV             INT,
          SERIE_DEV             VARCHAR(10),
          VLR_NF_DEV            MONEY,
          QT_EMB                MONEY,
          STATUS                INT,
          QT_ORIG_TOTAL         MONEY,
          QT_DEV_TOTAL          MONEY,
          FLAG_MOV_PROD_VENCIDO INT,
          NR_PROTOCOLO          VARCHAR(200),
          NR_PROTOCOLO_CONTROLE VARCHAR(200)
         );
         INSERT INTO #RS_PED_ENVIO_PENDENTE
         (CD_EMP,
          CD_FILIAL,
          CD_PED,
          STATUS
         )
                SELECT CD_EMP,
                       CD_FILIAL,
                       CD_PED,
                       STATUS
                FROM EST_PED_VD_ENVIO_PENDENTE;
         ALTER TABLE #RS_PED_ENVIO_PENDENTE
         ADD CONSTRAINT PK_RS_PED_ENVIO_PENDENTE PRIMARY KEY(CD_EMP ASC, CD_FILIAL ASC, CD_PED ASC, STATUS ASC);
         INSERT INTO #RS_PED_ENT
         (CD_EMP,
          CD_FILIAL,
          CD_PED,
          CD_NF,
          NR_PROTOCOLO
         )
                SELECT EST_NF_ENT_EST_PED_VD.CD_EMP,
                       EST_NF_ENT_EST_PED_VD.CD_FILIAL,
                       EST_NF_ENT_EST_PED_VD.CD_PED,
                       EST_NF_ENT_EST_PED_VD.CD_NF,
                       EST_NF_ENT_EST_PED_VD.NR_PROTOCOLO
                FROM EST_NF_ENT_EST_PED_VD
                     INNER JOIN #RS_PED_ENVIO_PENDENTE PEND ON EST_NF_ENT_EST_PED_VD.CD_EMP = PEND.CD_EMP
                                                               AND EST_NF_ENT_EST_PED_VD.CD_FILIAL = PEND.CD_FILIAL
                                                               AND EST_NF_ENT_EST_PED_VD.CD_PED = PEND.CD_PED;
         ALTER TABLE #RS_PED_ENT
         ADD CONSTRAINT PK_RS_PED_ENT PRIMARY KEY(CD_EMP ASC, CD_FILIAL ASC, CD_PED ASC, CD_NF ASC);
         INSERT INTO #RS_ENT_ORIGEM
         (CD_EMP,
          CD_FILIAL,
          CD_NF,
          DT_EMI_NF,
          NR_NF,
          SERIE,
          CD_FORN,
          QT_IT_TOTAL
         )
                SELECT DISTINCT
                       EST_NF_ENT.CD_EMP,
                       EST_NF_ENT.CD_FILIAL,
                       EST_NF_ENT.CD_NF,
                       EST_NF_ENT.DT_EMI_NF,
                       EST_NF_ENT.NR_NF,
                       EST_NF_SERIE.SERIE,
                       EST_NF_ENT.CD_FORN,
                (
                    SELECT SUM(QT_IT)
                    FROM EST_NF_ENT_IT
                    WHERE CD_EMP = EST_NF_ENT.CD_EMP
                          AND CD_FILIAL = EST_NF_ENT.CD_FILIAL
                          AND CD_NF = EST_NF_ENT.CD_NF
                ) AS QT_IT_TOTAL
                FROM EST_NF_ENT
                     INNER JOIN EST_NF_SERIE ON EST_NF_ENT.CD_EMP = EST_NF_SERIE.CD_EMP
                                                AND EST_NF_ENT.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
                     INNER JOIN #RS_PED_ENT PED_ENT ON EST_NF_ENT.CD_EMP = PED_ENT.CD_EMP
                                                       AND EST_NF_ENT.CD_FILIAL = PED_ENT.CD_FILIAL
                                                       AND EST_NF_ENT.CD_NF = PED_ENT.CD_NF;
         ALTER TABLE #RS_ENT_ORIGEM
         ADD CONSTRAINT PK_RS_ENT_ORIGEM PRIMARY KEY(CD_EMP ASC, CD_FILIAL ASC, CD_NF ASC);
         INSERT INTO #RS_ENT_ORIGEM_IT
         (CD_EMP,
          CD_FILIAL,
          CD_NF,
          CD_PROD,
          QT_IT
         )
                SELECT EST_NF_ENT_IT.CD_EMP,
                       EST_NF_ENT_IT.CD_FILIAL,
                       EST_NF_ENT_IT.CD_NF,
                       EST_NF_ENT_IT.CD_PROD,
                       COALESCE(NF_ORIGEM_IT_LOTE.QTDE_PROD, EST_NF_ENT_IT.QT_IT) AS QTDE_IT
                FROM EST_NF_ENT_IT
                     INNER JOIN #RS_ENT_ORIGEM NF_ORIGEM ON EST_NF_ENT_IT.CD_EMP = NF_ORIGEM.CD_EMP
                                                            AND EST_NF_ENT_IT.CD_FILIAL = NF_ORIGEM.CD_FILIAL
                                                            AND EST_NF_ENT_IT.CD_NF = NF_ORIGEM.CD_NF
                     LEFT JOIN
                (
                    SELECT NF_ORIGEM_IT_LOTE.CD_EMP,
                           NF_ORIGEM_IT_LOTE.CD_FILIAL,
                           NF_ORIGEM_IT_LOTE.CD_PROD,
                           NF_ORIGEM_IT_LOTE.CD_NF,
                           SUM(NF_ORIGEM_IT_LOTE.QTDE_PROD) AS QTDE_PROD
                    FROM EST_NF_ENT_IT_EST_PROD_CPL_LOTE AS NF_ORIGEM_IT_LOTE
                         INNER JOIN EST_PROD_CPL_LOTE LOTE ON NF_ORIGEM_IT_LOTE.CD_EMP = LOTE.CD_EMP
                                                              AND NF_ORIGEM_IT_LOTE.CD_FILIAL = LOTE.CD_FILIAL
                                                              AND NF_ORIGEM_IT_LOTE.CD_PROD = LOTE.CD_PROD
                                                              AND NF_ORIGEM_IT_LOTE.CD_LOTE = LOTE.CD_LOTE
                    GROUP BY NF_ORIGEM_IT_LOTE.CD_EMP,
                             NF_ORIGEM_IT_LOTE.CD_FILIAL,
                             NF_ORIGEM_IT_LOTE.CD_PROD,
                             NF_ORIGEM_IT_LOTE.CD_NF
                ) NF_ORIGEM_IT_LOTE ON EST_NF_ENT_IT.CD_EMP = NF_ORIGEM_IT_LOTE.CD_EMP
                                       AND EST_NF_ENT_IT.CD_FILIAL = NF_ORIGEM_IT_LOTE.CD_FILIAL
                                       AND EST_NF_ENT_IT.CD_NF = NF_ORIGEM_IT_LOTE.CD_NF
                                       AND EST_NF_ENT_IT.CD_PROD = NF_ORIGEM_IT_LOTE.CD_PROD;
         ALTER TABLE #RS_ENT_ORIGEM_IT
         ADD CONSTRAINT PK_RS_ENT_ORIGEM_IT PRIMARY KEY(CD_EMP ASC, CD_FILIAL ASC, CD_NF ASC, CD_PROD ASC);
         INSERT INTO #RS_SAI_DEV
         (CD_EMP,
          CD_FILIAL,
          CD_NF,
          CD_PED,
          DT_EMI_NF,
          NF_NF,
          SERIE,
          FLAG_MOV_PROD_VENCIDO,
          VLR_NF,
          CD_CLI,
          QT_IT_TOTAL
         )
                SELECT DISTINCT
                       EST_NF_SAI.CD_EMP,
                       EST_NF_SAI.CD_FILIAL,
                       EST_NF_SAI.CD_NF,
                       NF_PED.CD_PED,
                       EST_NF_SAI.DT_EMI_NF,
                       EST_NF_SAI.NF_NF,
                       EST_NF_SERIE.SERIE,
                       GLB_MOV_OP.FLAG_MOV_PROD_VENCIDO,
                       EST_NF_SAI.VLR_NF,
                       EST_NF_SAI.CD_CLI,
                (
                    SELECT SUM(QT_IT)
                    FROM EST_NF_SAI_IT
                    WHERE CD_EMP = EST_NF_SAI.CD_EMP
                          AND CD_FILIAL = EST_NF_SAI.CD_FILIAL
                          AND CD_NF = EST_NF_SAI.CD_NF
                ) AS QT_IT_TOTAL
                FROM EST_NF_SAI
                     INNER JOIN EST_NF_SERIE ON EST_NF_SAI.CD_EMP = EST_NF_SERIE.CD_EMP
                                                AND EST_NF_SAI.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE
                     INNER JOIN GLB_MOV_OP ON EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
                     INNER JOIN EST_NF_SAI_PED_VD NF_PED ON EST_NF_SAI.CD_EMP = NF_PED.CD_EMP
                                                            AND EST_NF_SAI.CD_FILIAL = NF_PED.CD_FILIAL
                                                            AND EST_NF_SAI.CD_NF = NF_PED.CD_NF
                     INNER JOIN #RS_PED_ENVIO_PENDENTE PEND ON NF_PED.CD_EMP = PEND.CD_EMP
                                                               AND NF_PED.CD_FILIAL = PEND.CD_FILIAL
                                                               AND NF_PED.CD_PED = PEND.CD_PED;
         ALTER TABLE #RS_SAI_DEV
         ADD CONSTRAINT PK_RS_SAI_DEV PRIMARY KEY(CD_EMP ASC, CD_FILIAL ASC, CD_NF ASC);
         INSERT INTO #RS_SAI_DEV_IT
         (CD_EMP,
          CD_FILIAL,
          CD_NF,
          CD_PROD,
          CD_PROD_FORN,
          CD_NF_REF,
          NR_LOTE,
          DT_VALID,
          QT_IT,
          QT_EMB
         )
                SELECT EST_NF_SAI_IT.CD_EMP,
                       EST_NF_SAI_IT.CD_FILIAL,
                       EST_NF_SAI_IT.CD_NF,
                       EST_NF_SAI_IT.CD_PROD,
                       COALESCE(EST_PROD_FORN.CD_PROD_FORN, 'NAO DEF') AS CD_PROD_FORN,
                       PED_IT.CD_NF_REF,
                       CASE
                           WHEN COALESCE(LOTE.NR_LOTE, 'LOT002') = ''
                           THEN 'LOT002'
                           ELSE COALESCE(LOTE.NR_LOTE, 'LOT002')
                       END AS NR_LOTE,
                       COALESCE(LOTE.DT_VALID, '20991231') AS DT_VALID,
                       COALESCE(NF_DEV_IT_LOTE.QTDE_PROD, EST_NF_SAI_IT.QT_IT) AS QTDE_IT_DEV,
                (
                    SELECT QT_EMB_COMPRA
                    FROM UFD_EST_PROD_EMB_APRES(1, EST_NF_SAI_IT.CD_PROD, NF_SAI.CD_CLI)
                ) AS QT_EMB
                FROM EST_NF_SAI_IT
                     INNER JOIN #RS_SAI_DEV NF_SAI ON EST_NF_SAI_IT.CD_EMP = NF_SAI.CD_EMP
                                                      AND EST_NF_SAI_IT.CD_FILIAL = NF_SAI.CD_FILIAL
                                                      AND EST_NF_SAI_IT.CD_NF = NF_SAI.CD_NF
                     INNER JOIN EST_NF_SAI_PED_VD NF_PED ON EST_NF_SAI_IT.CD_EMP = NF_PED.CD_EMP
                                                            AND EST_NF_SAI_IT.CD_FILIAL = NF_PED.CD_FILIAL
                                                            AND EST_NF_SAI_IT.CD_NF = NF_PED.CD_NF
                     INNER JOIN EST_PED_VD_IT PED_IT ON NF_PED.CD_EMP = PED_IT.CD_EMP
                                                        AND NF_PED.CD_FILIAL = PED_IT.CD_FILIAL
                                                        AND NF_PED.CD_PED = PED_IT.CD_PED
                                                        AND EST_NF_SAI_IT.CD_PROD = PED_IT.CD_PROD
                     LEFT JOIN EST_NF_SAI_IT_EST_PROD_CPL_LOTE NF_DEV_IT_LOTE ON EST_NF_SAI_IT.CD_EMP = NF_DEV_IT_LOTE.CD_EMP
                                                                                 AND EST_NF_SAI_IT.CD_FILIAL = NF_DEV_IT_LOTE.CD_FILIAL
                                                                                 AND EST_NF_SAI_IT.CD_NF = NF_DEV_IT_LOTE.CD_NF
                                                                                 AND EST_NF_SAI_IT.CD_PROD = NF_DEV_IT_LOTE.CD_PROD
                     LEFT JOIN EST_PROD_CPL_LOTE LOTE ON NF_DEV_IT_LOTE.CD_EMP = LOTE.CD_EMP
                                                         AND NF_DEV_IT_LOTE.CD_FILIAL = LOTE.CD_FILIAL
                                                         AND NF_DEV_IT_LOTE.CD_PROD = LOTE.CD_PROD
                                                         AND NF_DEV_IT_LOTE.CD_LOTE = LOTE.CD_LOTE
                     LEFT JOIN EST_PROD_FORN ON NF_SAI.CD_EMP = EST_PROD_FORN.CD_EMP
                                                AND NF_SAI.CD_CLI = EST_PROD_FORN.CD_FORN
                                                AND EST_NF_SAI_IT.CD_PROD = EST_PROD_FORN.CD_PROD;
         INSERT INTO #RS_RETURN
                SELECT PED_PENDENTE.CD_PED,
                       NF_ORIGEM.DT_EMI_NF,
                       NF_ORIGEM.CD_NF,
                       NF_ORIGEM.NR_NF,
                       NF_ORIGEM.SERIE,
                       PG_FORN_CD_FIL_FORN.CD_FIL_FORN,
                       NF_DEV.CD_FILIAL,
                       NF_DEV.CD_NF,
                       NF_DEV_IT.CD_PROD_FORN AS CD_PROD,
                       NF_DEV_IT.CD_PROD AS CD_PROD_GEST,
                       NF_DEV_IT.NR_LOTE,
                       NF_DEV_IT.DT_VALID,
                       NF_ORIGEM_IT.QT_IT AS QTDE_IT,
                       NF_DEV_IT.QT_IT AS QTDE_IT_DEV,
                       'ITECGYN' AS OPERADOR,
                       '' AS CONTATO,
                       NF_DEV.DT_EMI_NF AS DT_EMI_NF_DEV,
                       NF_DEV.NF_NF AS NR_NF_DEV,
                       NF_DEV.SERIE AS SERIE_DEV,
                       NF_DEV.VLR_NF AS VLR_NF_DEV,
                       NF_DEV_IT.QT_EMB,
                       PED_PENDENTE.STATUS,
                       NF_ORIGEM.QT_IT_TOTAL AS QT_ORIG_TOTAL,
                       NF_DEV.QT_IT_TOTAL AS QT_DEV_TOTAL,
                       NF_DEV.FLAG_MOV_PROD_VENCIDO,
                       '' AS NR_PROTOCOLO,
                       PED_ENT.NR_PROTOCOLO
                FROM #RS_PED_ENVIO_PENDENTE PED_PENDENTE
                     INNER JOIN #RS_PED_ENT PED_ENT ON PED_PENDENTE.CD_EMP = PED_ENT.CD_EMP
                                                       AND PED_PENDENTE.CD_FILIAL = PED_ENT.CD_FILIAL
                                                       AND PED_PENDENTE.CD_PED = PED_ENT.CD_PED
                     INNER JOIN #RS_ENT_ORIGEM NF_ORIGEM ON PED_ENT.CD_EMP = NF_ORIGEM.CD_EMP
                                                            AND PED_ENT.CD_FILIAL = NF_ORIGEM.CD_FILIAL
                                                            AND PED_ENT.CD_NF = NF_ORIGEM.CD_NF
                     INNER JOIN #RS_ENT_ORIGEM_IT NF_ORIGEM_IT ON NF_ORIGEM.CD_EMP = NF_ORIGEM_IT.CD_EMP
                                                                  AND NF_ORIGEM.CD_FILIAL = NF_ORIGEM_IT.CD_FILIAL
                                                                  AND NF_ORIGEM.CD_NF = NF_ORIGEM_IT.CD_NF
                     INNER JOIN PG_FORN_CD_FIL_FORN ON NF_ORIGEM.CD_EMP = PG_FORN_CD_FIL_FORN.CD_EMP
                                                       AND NF_ORIGEM.CD_FILIAL = PG_FORN_CD_FIL_FORN.CD_FILIAL
                                                       AND NF_ORIGEM.CD_FORN = PG_FORN_CD_FIL_FORN.CD_FORN
                     INNER JOIN #RS_SAI_DEV NF_DEV ON PED_PENDENTE.CD_EMP = NF_DEV.CD_EMP
                                                      AND PED_PENDENTE.CD_FILIAL = NF_DEV.CD_FILIAL
                                                      AND PED_PENDENTE.CD_PED = NF_DEV.CD_PED
                     INNER JOIN #RS_SAI_DEV_IT NF_DEV_IT ON NF_DEV.CD_EMP = NF_DEV_IT.CD_EMP
                                                            AND NF_DEV.CD_FILIAL = NF_DEV_IT.CD_FILIAL
                                                            AND NF_DEV.CD_NF = NF_DEV_IT.CD_NF
                                                            AND NF_ORIGEM_IT.CD_PROD = NF_DEV_IT.CD_PROD
                                                            AND PED_ENT.CD_NF = NF_DEV_IT.CD_NF_REF
                WHERE PED_PENDENTE.STATUS IN(1, 2);
         INSERT INTO #RS_RETURN
                SELECT PED_PENDENTE.CD_PED,
                       NF_ORIGEM.DT_EMI_NF,
                       NF_ORIGEM.CD_NF,
                       NF_ORIGEM.NR_NF,
                       NF_ORIGEM.SERIE,
                       PG_FORN_CD_FIL_FORN.CD_FIL_FORN,
                       NF_DEV.CD_FILIAL,
                       NF_DEV.CD_NF,
                       NF_DEV_IT.CD_PROD_FORN AS CD_PROD,
                       NF_DEV_IT.CD_PROD AS CD_PROD_GEST,
                       NF_DEV_IT.NR_LOTE,
                       NF_DEV_IT.DT_VALID,
                       NF_ORIGEM_IT.QT_IT AS QTDE_IT,
                       NF_DEV_IT.QT_IT AS QTDE_IT_DEV,
                       'ITECGYN' AS OPERADOR,
                       '' AS CONTATO,
                       NF_DEV.DT_EMI_NF AS DT_EMI_NF_DEV,
                       NF_DEV.NF_NF AS NR_NF_DEV,
                       NF_DEV.SERIE AS SERIE_DEV,
                       NF_DEV.VLR_NF AS VLR_NF_DEV,
                       NF_DEV_IT.QT_EMB,
                       PED_PENDENTE.STATUS,
                       NF_ORIGEM.QT_IT_TOTAL AS QT_ORIG_TOTAL,
                       NF_DEV.QT_IT_TOTAL AS QT_DEV_TOTAL,
                       NF_DEV.FLAG_MOV_PROD_VENCIDO,
                       '' AS NR_PROTOCOLO,
                       PED_ENT.NR_PROTOCOLO
                FROM #RS_PED_ENVIO_PENDENTE PED_PENDENTE
                     INNER JOIN #RS_PED_ENT PED_ENT ON PED_PENDENTE.CD_EMP = PED_ENT.CD_EMP
                                                       AND PED_PENDENTE.CD_FILIAL = PED_ENT.CD_FILIAL
                                                       AND PED_PENDENTE.CD_PED = PED_ENT.CD_PED
                     INNER JOIN #RS_ENT_ORIGEM NF_ORIGEM ON PED_ENT.CD_EMP = NF_ORIGEM.CD_EMP
                                                            AND PED_ENT.CD_FILIAL = NF_ORIGEM.CD_FILIAL
                                                            AND PED_ENT.CD_NF = NF_ORIGEM.CD_NF
                     INNER JOIN #RS_ENT_ORIGEM_IT NF_ORIGEM_IT ON NF_ORIGEM.CD_EMP = NF_ORIGEM_IT.CD_EMP
                                                                  AND NF_ORIGEM.CD_FILIAL = NF_ORIGEM_IT.CD_FILIAL
                                                                  AND NF_ORIGEM.CD_NF = NF_ORIGEM_IT.CD_NF
                     INNER JOIN PG_FORN_CD_FIL_FORN ON NF_ORIGEM.CD_EMP = PG_FORN_CD_FIL_FORN.CD_EMP
                                                       AND NF_ORIGEM.CD_FILIAL = PG_FORN_CD_FIL_FORN.CD_FILIAL
                                                       AND NF_ORIGEM.CD_FORN = PG_FORN_CD_FIL_FORN.CD_FORN
                     INNER JOIN #RS_SAI_DEV NF_DEV ON PED_PENDENTE.CD_EMP = NF_DEV.CD_EMP
                                                      AND PED_PENDENTE.CD_FILIAL = NF_DEV.CD_FILIAL
                                                      AND PED_PENDENTE.CD_PED = NF_DEV.CD_PED
                     INNER JOIN #RS_SAI_DEV_IT NF_DEV_IT ON NF_DEV.CD_EMP = NF_DEV_IT.CD_EMP
                                                            AND NF_DEV.CD_FILIAL = NF_DEV_IT.CD_FILIAL
                                                            AND NF_DEV.CD_NF = NF_DEV_IT.CD_NF
                                                            AND NF_ORIGEM_IT.CD_PROD = NF_DEV_IT.CD_PROD
                                                            AND PED_ENT.CD_NF = NF_DEV_IT.CD_NF_REF
                     INNER JOIN EST_NF_SAI_CHECKIN_ENTRADA_CPL NF_DEV_CH ON NF_DEV_IT.CD_EMP = NF_DEV_CH.CD_EMP
                                                                            AND NF_DEV_IT.CD_FILIAL = NF_DEV_CH.CD_FILIAL
                                                                            AND NF_DEV_IT.CD_NF = NF_DEV_CH.CD_NF
                                                                            AND NF_DEV_IT.CD_PROD = NF_DEV_CH.CD_PROD
                WHERE PED_PENDENTE.STATUS = 3
                      AND NF_DEV_IT.QT_IT <> NF_DEV_CH.QT_IT_CHECK;
         INSERT INTO #RS_RETURN
                SELECT PED_PENDENTE.CD_PED,
                       NF_ORIGEM.DT_EMI_NF,
                       NF_ORIGEM.CD_NF,
                       NF_ORIGEM.NR_NF,
                       NF_ORIGEM.SERIE,
                       PG_FORN_CD_FIL_FORN.CD_FIL_FORN,
                       NF_DEV.CD_FILIAL,
                       NF_DEV.CD_NF,
                       NF_DEV_IT.CD_PROD_FORN AS CD_PROD,
                       NF_DEV_IT.CD_PROD AS CD_PROD_GEST,
                       NF_DEV_IT.NR_LOTE AS NR_LOTE,
                       NF_DEV_IT.DT_VALID,
                       NF_ORIGEM_IT.QT_IT AS QTDE_IT,
                       NF_DEV_IT.QT_IT AS QTDE_IT_DEV,
                       'ITECGYN' AS OPERADOR,
                       '' AS CONTATO,
                       NF_DEV.DT_EMI_NF AS DT_EMI_NF_DEV,
                       NF_DEV.NF_NF AS NR_NF_DEV,
                       NF_DEV.SERIE AS SERIE_DEV,
                       NF_DEV.VLR_NF AS VLR_NF_DEV,
                       NF_DEV_IT.QT_EMB,
                       PED_PENDENTE.STATUS,
                       NF_ORIGEM.QT_IT_TOTAL AS QT_ORIG_TOTAL,
                       NF_DEV.QT_IT_TOTAL AS QT_DEV_TOTAL,
                       NF_DEV.FLAG_MOV_PROD_VENCIDO,
                       '' AS NR_PROTOCOLO,
                       PED_ENT.NR_PROTOCOLO
                FROM #RS_PED_ENVIO_PENDENTE PED_PENDENTE
                     INNER JOIN #RS_PED_ENT PED_ENT ON PED_PENDENTE.CD_EMP = PED_ENT.CD_EMP
                                                       AND PED_PENDENTE.CD_FILIAL = PED_ENT.CD_FILIAL
                                                       AND PED_PENDENTE.CD_PED = PED_ENT.CD_PED
                     INNER JOIN #RS_ENT_ORIGEM NF_ORIGEM ON PED_ENT.CD_EMP = NF_ORIGEM.CD_EMP
                                                            AND PED_ENT.CD_FILIAL = NF_ORIGEM.CD_FILIAL
                                                            AND PED_ENT.CD_NF = NF_ORIGEM.CD_NF
                     INNER JOIN #RS_ENT_ORIGEM_IT NF_ORIGEM_IT ON NF_ORIGEM.CD_EMP = NF_ORIGEM_IT.CD_EMP
                                                                  AND NF_ORIGEM.CD_FILIAL = NF_ORIGEM_IT.CD_FILIAL
                                                                  AND NF_ORIGEM.CD_NF = NF_ORIGEM_IT.CD_NF
                     INNER JOIN PG_FORN_CD_FIL_FORN ON NF_ORIGEM.CD_EMP = PG_FORN_CD_FIL_FORN.CD_EMP
                                                       AND NF_ORIGEM.CD_FILIAL = PG_FORN_CD_FIL_FORN.CD_FILIAL
                                                       AND NF_ORIGEM.CD_FORN = PG_FORN_CD_FIL_FORN.CD_FORN
                     INNER JOIN #RS_SAI_DEV NF_DEV ON PED_PENDENTE.CD_EMP = NF_DEV.CD_EMP
                                                      AND PED_PENDENTE.CD_FILIAL = NF_DEV.CD_FILIAL
                                                      AND PED_PENDENTE.CD_PED = NF_DEV.CD_PED
                     INNER JOIN #RS_SAI_DEV_IT NF_DEV_IT ON NF_DEV.CD_EMP = NF_DEV_IT.CD_EMP
                                                            AND NF_DEV.CD_FILIAL = NF_DEV_IT.CD_FILIAL
                                                            AND NF_DEV.CD_NF = NF_DEV_IT.CD_NF
                                                            AND NF_ORIGEM_IT.CD_PROD = NF_DEV_IT.CD_PROD
                                                            AND PED_ENT.CD_NF = NF_DEV_IT.CD_NF_REF --DADOS DA NOTA ANTIGA. SÓ É PRECISO PARA PEGAR A QUANTIDADE DE CHECK-IN
                     INNER JOIN EST_PED_VD_DEVOLVIDA PED_DEVOLVIDA ON PED_PENDENTE.CD_EMP = PED_DEVOLVIDA.CD_EMP
                                                                      AND PED_PENDENTE.CD_FILIAL = PED_DEVOLVIDA.CD_FILIAL
                                                                      AND PED_PENDENTE.CD_PED = PED_DEVOLVIDA.CD_PED_NOVO
                     INNER JOIN EST_NF_SAI_PED_VD PRE_FATURA_NF_ANT ON PED_DEVOLVIDA.CD_EMP = PRE_FATURA_NF_ANT.CD_EMP
                                                                       AND PED_DEVOLVIDA.CD_FILIAL = PRE_FATURA_NF_ANT.CD_FILIAL
                                                                       AND PED_DEVOLVIDA.CD_PED_ANTIGO = PRE_FATURA_NF_ANT.CD_PED
                     INNER JOIN EST_NF_SAI_CHECKIN_ENTRADA_CPL NF_DEV_CH_ANT ON PRE_FATURA_NF_ANT.CD_EMP = NF_DEV_CH_ANT.CD_EMP
                                                                                AND PRE_FATURA_NF_ANT.CD_FILIAL = NF_DEV_CH_ANT.CD_FILIAL
                                                                                AND PRE_FATURA_NF_ANT.CD_NF = NF_DEV_CH_ANT.CD_NF
                                                                                AND NF_DEV_IT.CD_PROD = NF_DEV_CH_ANT.CD_PROD
                     INNER JOIN EST_NF_SAI_IT NF_DEV_IT_ANT ON NF_DEV_CH_ANT.CD_EMP = NF_DEV_IT_ANT.CD_EMP
                                                               AND NF_DEV_CH_ANT.CD_FILIAL = NF_DEV_IT_ANT.CD_FILIAL
                                                               AND NF_DEV_CH_ANT.CD_NF = NF_DEV_IT_ANT.CD_NF
                                                               AND NF_DEV_CH_ANT.CD_PROD = NF_DEV_IT_ANT.CD_PROD
                WHERE PED_PENDENTE.STATUS = 4
                      AND NF_DEV_IT_ANT.QT_IT <> NF_DEV_CH_ANT.QT_IT_CHECK;
         --AND PED_ENT.NR_PROTOCOLO IS NOT NULL




         INSERT INTO #RS_RETURN
                SELECT PED_PENDENTE.CD_PED AS CD_PED,
                       NF_ORIGEM.DT_EMI_NF,
                       NF_ORIGEM.CD_NF,
                       NF_ORIGEM.NR_NF,
                       NF_ORIGEM.SERIE,
                       PG_FORN_CD_FIL_FORN.CD_FIL_FORN,
                       NF_DEV.CD_FILIAL,
                       NF_DEV.CD_NF,
                       NF_DEV_IT.CD_PROD_FORN AS CD_PROD,
                       NF_DEV_IT.CD_PROD AS CD_PROD_GEST,
                       NF_DEV_IT.NR_LOTE AS NR_LOTE,
                       NF_DEV_IT.DT_VALID,
                       NF_ORIGEM_IT.QT_IT AS QTDE_IT,
                       NF_DEV_IT.QT_IT AS QTDE_IT_DEV,
                       'ITECGYN' AS OPERADOR,
                       '' AS CONTATO,
                       NF_DEV.DT_EMI_NF AS DT_EMI_NF_DEV,
                       NF_DEV.NF_NF AS NR_NF_DEV,
                       NF_DEV.SERIE AS SERIE_DEV,
                       NF_DEV.VLR_NF AS VLR_NF_DEV,
                       NF_DEV_IT.QT_EMB,
                       5 AS STATUS,
                       NF_ORIGEM.QT_IT_TOTAL AS QT_ORIG_TOTAL,
                       NF_DEV.QT_IT_TOTAL AS QT_DEV_TOTAL,
                       NF_DEV.FLAG_MOV_PROD_VENCIDO,
                       COALESCE(PED_ENT.NR_PROTOCOLO, 'PROTO') AS NR_PROTOCOLO,
                       PED_ENT.NR_PROTOCOLO
                FROM #RS_PED_ENVIO_PENDENTE PED_PENDENTE
                     INNER JOIN #RS_PED_ENT PED_ENT ON PED_PENDENTE.CD_EMP = PED_ENT.CD_EMP
                                                       AND PED_PENDENTE.CD_FILIAL = PED_ENT.CD_FILIAL
                                                       AND PED_PENDENTE.CD_PED = PED_ENT.CD_PED
                     INNER JOIN #RS_ENT_ORIGEM NF_ORIGEM ON PED_ENT.CD_EMP = NF_ORIGEM.CD_EMP
                                                            AND PED_ENT.CD_FILIAL = NF_ORIGEM.CD_FILIAL
                                                            AND PED_ENT.CD_NF = NF_ORIGEM.CD_NF
                     INNER JOIN #RS_ENT_ORIGEM_IT NF_ORIGEM_IT ON NF_ORIGEM.CD_EMP = NF_ORIGEM_IT.CD_EMP
                                                                  AND NF_ORIGEM.CD_FILIAL = NF_ORIGEM_IT.CD_FILIAL
                                                                  AND NF_ORIGEM.CD_NF = NF_ORIGEM_IT.CD_NF
                     INNER JOIN PG_FORN_CD_FIL_FORN ON NF_ORIGEM.CD_EMP = PG_FORN_CD_FIL_FORN.CD_EMP
                                                       AND NF_ORIGEM.CD_FILIAL = PG_FORN_CD_FIL_FORN.CD_FILIAL
                                                       AND NF_ORIGEM.CD_FORN = PG_FORN_CD_FIL_FORN.CD_FORN
                     INNER JOIN #RS_SAI_DEV NF_DEV ON PED_PENDENTE.CD_EMP = NF_DEV.CD_EMP
                                                      AND PED_PENDENTE.CD_FILIAL = NF_DEV.CD_FILIAL
                                                      AND PED_PENDENTE.CD_PED = NF_DEV.CD_PED
                     INNER JOIN #RS_SAI_DEV_IT NF_DEV_IT ON NF_DEV.CD_EMP = NF_DEV_IT.CD_EMP
                                                            AND NF_DEV.CD_FILIAL = NF_DEV_IT.CD_FILIAL
                                                            AND NF_DEV.CD_NF = NF_DEV_IT.CD_NF
                                                            AND NF_ORIGEM_IT.CD_PROD = NF_DEV_IT.CD_PROD
                                                            AND PED_ENT.CD_NF = NF_DEV_IT.CD_NF_REF --DADOS DA NOTA ANTIGA. SÓ É PRECISO PARA PEGAR A QUANTIDADE DE CHECK-IN
                     INNER JOIN EST_PED_VD_DEVOLVIDA PED_DEVOLVIDA ON PED_PENDENTE.CD_EMP = PED_DEVOLVIDA.CD_EMP
                                                                      AND PED_PENDENTE.CD_FILIAL = PED_DEVOLVIDA.CD_FILIAL
                                                                      AND PED_PENDENTE.CD_PED = PED_DEVOLVIDA.CD_PED_NOVO
                     INNER JOIN EST_NF_SAI_PED_VD PRE_FATURA_NF_ANT ON PED_DEVOLVIDA.CD_EMP = PRE_FATURA_NF_ANT.CD_EMP
                                                                       AND PED_DEVOLVIDA.CD_FILIAL = PRE_FATURA_NF_ANT.CD_FILIAL
                                                                       AND PED_DEVOLVIDA.CD_PED_ANTIGO = PRE_FATURA_NF_ANT.CD_PED
                     INNER JOIN EST_NF_SAI_CHECKIN_ENTRADA_CPL NF_DEV_CH_ANT ON PRE_FATURA_NF_ANT.CD_EMP = NF_DEV_CH_ANT.CD_EMP
                                                                                AND PRE_FATURA_NF_ANT.CD_FILIAL = NF_DEV_CH_ANT.CD_FILIAL
                                                                                AND PRE_FATURA_NF_ANT.CD_NF = NF_DEV_CH_ANT.CD_NF
                                                                                AND NF_DEV_IT.CD_PROD = NF_DEV_CH_ANT.CD_PROD
                     INNER JOIN EST_NF_SAI_IT NF_DEV_IT_ANT ON NF_DEV_CH_ANT.CD_EMP = NF_DEV_IT_ANT.CD_EMP
                                                               AND NF_DEV_CH_ANT.CD_FILIAL = NF_DEV_IT_ANT.CD_FILIAL
                                                               AND NF_DEV_CH_ANT.CD_NF = NF_DEV_IT_ANT.CD_NF
                                                               AND NF_DEV_CH_ANT.CD_PROD = NF_DEV_IT_ANT.CD_PROD
                WHERE PED_PENDENTE.STATUS = 4
                      AND NF_DEV_IT_ANT.QT_IT = NF_DEV_CH_ANT.QT_IT_CHECK;



         --TRATANTO OS PRODUTOS QUE NÃO DERAM DIVERGÊNCIA MAS QUE POSSUEM O PROTOCOLO CANCELADO POR OUTRO PRODUTO
         INSERT INTO #RS_RETURN
                SELECT CD_PED,
                       DT_EMI_NF,
                       CD_NF_ORIG,
                       NR_NF,
                       SERIE,
                       CD_FIL_FORN,
                       CD_FILIAL,
                       CD_NF,
                       CD_PROD,
                       CD_PROD_GEST,
                       NR_LOTE,
                       DT_VALID,
                       QTDE_IT,
                       QTDE_IT_DEV,
                       OPERADOR,
                       CONTATO,
                       DT_EMI_NF_DEV,
                       NR_NF_DEV,
                       SERIE_DEV,
                       VLR_NF_DEV,
                       QT_EMB,
                       4 AS STATUS,
                       QT_ORIG_TOTAL,
                       QT_DEV_TOTAL,
                       FLAG_MOV_PROD_VENCIDO,
                       '' AS NR_PROTOCOLO,
                       NR_PROTOCOLO_CONTROLE
                FROM #RS_RETURN R
                WHERE STATUS = 5
                      AND EXISTS
                (
                    SELECT *
                    FROM #RS_RETURN
                    WHERE NR_PROTOCOLO_CONTROLE = R.NR_PROTOCOLO_CONTROLE
                          AND STATUS = 3
                          AND ID <> R.ID
                );
         UPDATE R
           SET
               STATUS = 3,
               NR_PROTOCOLO = ''
         FROM #RS_RETURN R
         WHERE STATUS = 5
               AND EXISTS
         (
             SELECT *
             FROM #RS_RETURN
             WHERE NR_PROTOCOLO_CONTROLE = R.NR_PROTOCOLO_CONTROLE
                   AND STATUS = 3
                   AND ID <> R.ID
         );
         SET NOCOUNT OFF;
         SELECT *
         FROM #RS_RETURN
         ORDER BY CD_FILIAL,
                  NR_NF,
                  NR_NF_DEV,
                  STATUS;
         DROP TABLE #RS_PED_ENVIO_PENDENTE;
         DROP TABLE #RS_PED_ENT;
         DROP TABLE #RS_ENT_ORIGEM;
         DROP TABLE #RS_ENT_ORIGEM_IT;
         DROP TABLE #RS_SAI_DEV;
         DROP TABLE #RS_SAI_DEV_IT;
         DROP TABLE #RS_RETURN;
     END;