IF EXISTS (SELECT * FROM SYS.PROCEDURES WHERE NAME = 'P_EST_PED_ENVIO_SAP_PENDENTE_ALL')
BEGIN
	DROP PROCEDURE P_EST_PED_ENVIO_SAP_PENDENTE_ALL;
END;

GO
GO

CREATE PROCEDURE [dbo].[P_EST_PED_ENVIO_SAP_PENDENTE_ALL]
AS
     BEGIN
        
		SET NOCOUNT ON;
        CREATE TABLE #RS_PED_ENVIO_PENDENTE_TMP (
			CD_EMP    INT NOT NULL,
			CD_FILIAL INT NOT NULL,
			CD_PED    INT NOT NULL);

        CREATE TABLE #RS_RETURN_FINAL(
			ID                    INT 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,
			STATUS_DEL            INT,
			QT_ORIG_TOTAL         MONEY,
			QT_DEV_TOTAL          MONEY,
			FLAG_MOV_PROD_VENCIDO INT,
			NR_PROTOCOLO          VARCHAR(200),
			NR_PROTOCOLO_CONTROLE VARCHAR(200),
			NR_VOL_GUARDA		  INT);

		DECLARE @CD_FILIAL INT
		DECLARE @CD_PED INT

		INSERT INTO 
			#RS_PED_ENVIO_PENDENTE_TMP (CD_EMP, CD_FILIAL, CD_PED)
        SELECT 
			EST_PED_VD_ENVIO_PENDENTE.CD_EMP,
            EST_PED_VD_ENVIO_PENDENTE.CD_FILIAL,
            EST_PED_VD_ENVIO_PENDENTE.CD_PED
        FROM 
			EST_PED_VD_ENVIO_PENDENTE
			INNER JOIN EST_NF_SAI_PED_VD ON
				EST_PED_VD_ENVIO_PENDENTE.CD_EMP = EST_NF_SAI_PED_VD.CD_EMP
				AND EST_PED_VD_ENVIO_PENDENTE.CD_FILIAL = EST_NF_SAI_PED_VD.CD_FILIAL
				AND EST_PED_VD_ENVIO_PENDENTE.CD_PED = EST_NF_SAI_PED_VD.CD_PED;
         
		ALTER TABLE #RS_PED_ENVIO_PENDENTE_TMP ADD CONSTRAINT PK_RS_PED_ENVIO_PENDENTE PRIMARY KEY(CD_EMP ASC, CD_FILIAL ASC, CD_PED ASC);

		DECLARE 
			CURSOR_PEDIDOS_PENDENTES 
		CURSOR FOR
		SELECT
			CD_FILIAL,
			CD_PED
		FROM
			#RS_PED_ENVIO_PENDENTE_TMP
		ORDER BY
			CD_FILIAL,
			CD_PED

		OPEN CURSOR_PEDIDOS_PENDENTES

		FETCH NEXT FROM CURSOR_PEDIDOS_PENDENTES INTO @CD_FILIAL, @CD_PED

		WHILE @@FETCH_STATUS = 0
		BEGIN

			INSERT INTO
				#RS_RETURN_FINAL
			EXEC P_EST_PED_ENVIO_SAP_PENDENTE_FILIAL_PED @CD_FILIAL, @CD_PED

			FETCH NEXT FROM CURSOR_PEDIDOS_PENDENTES INTO @CD_FILIAL, @CD_PED
		END

		SET NOCOUNT OFF;
		SELECT 
			*
		FROM 
			#RS_RETURN_FINAL

		DROP TABLE #RS_PED_ENVIO_PENDENTE_TMP;
		DROP TABLE #RS_RETURN_FINAL;
	END;

GO


