/****** OBJECT:  STOREDPROCEDURE [DBO].[P_INCLUI_PENDENCIA_WMS]    SCRIPT DATE: 10/04/2013 16:32:52 ******/
IF EXISTS( SELECT * FROM SYS.OBJECTS
			WHERE  OBJECT_ID = OBJECT_ID(N'[DBO].[P_INCLUI_PENDENCIA_WMS]') AND TYPE IN(N'P', N'PC'))
DROP PROCEDURE [DBO].[P_INCLUI_PENDENCIA_WMS];
GO
GO
GO

--EXECUTE P_INCLUI_PENDENCIA_WMS
--SELECT * FROM EST_PROD_TRANSF_PEND_WMS
--SELECT * FROM EST_PROD_TRANSF_EST_WMS_SAI ORDER BY CD_PED_WMS
--SELECT * FROM EST_WMS_SAI ORDER BY CD_PED_WMS

CREATE PROCEDURE [DBO].[P_INCLUI_PENDENCIA_WMS]
AS
	 BEGIN
	
		 SET NOCOUNT ON;

		 PRINT 'Processar retorno de saida de volumes no WMS';
		 EXEC P_EST_WMS_SAI_VOLUME_PEND_EXECUTE_ALL;

		 PRINT 'Processar retorno de saida no WMS';
		 EXEC P_EST_WMS_SAI_RET_PEND_PROCESSAR;

		 PRINT 'Processar retorno de entrada nota fiscal transferencia no WMS';
		 EXEC P_EST_NF_SAI_EST_WMS_ENT_RET;
		
		 PRINT 'Processar retorno de entrada nota fiscal ';
		 EXEC P_EST_WMS_ENT_RET_RETORNO;

		 Declare @NaoUtilizaWMS_X3 tinyint
		 
		 set @NaoUtilizaWMS_X3 = isnull((Select Valor from prc_emp_config where cd_emp=1 and cd_chave='NAO_UTILIZA_INTEGRACAO_WMS_X3'),0);
	 begin try
--GERANDO SAIDA PARA O WMS PENDENCIA
		 PRINT 'INICIO DO CRIACAO DE PENDENCIA WMS SAIDA';
		 INSERT INTO                         EST_PROD_TRANSF_PEND_WMS(
				CD_EMP
			  , CD_FILIAL
			  , CD_CTR
			  , DT_CAD
			  , PEND_ENVIO_WMS)
		 SELECT DISTINCT
				T.CD_EMP
			  , T.CD_FILIAL
			  , T.CD_CTR
			  , GETDATE()
			  , 0
		 FROM
			 EST_PROD_TRANSF T
			 INNER JOIN EST_PROD_TRANSF_IT IT ON T.CD_EMP = IT.CD_EMP
												 AND T.CD_FILIAL = IT.CD_FILIAL
												 AND T.CD_CTR = IT.CD_CTR
			 INNER JOIN EST_PROD_CPL ARV ON IT.CD_EMP = ARV.CD_EMP
											AND IT.CD_FILIAL = ARV.CD_FILIAL
											AND IT.CD_PROD = ARV.CD_PROD
			 LEFT OUTER JOIN EST_PROD_TRANSF_CROSS_DOCKING ON T.CD_EMP = EST_PROD_TRANSF_CROSS_DOCKING.CD_EMP
															  AND T.CD_FILIAL = EST_PROD_TRANSF_CROSS_DOCKING.CD_FILIAL
															  AND T.CD_CTR = EST_PROD_TRANSF_CROSS_DOCKING.CD_CTR
			 INNER JOIN
(
	SELECT
		   CD_EMP
		 , CD_FILIAL
		 , VALOR
	FROM
		PRC_FILIAL_CONFIG
	WHERE  CD_CHAVE = 'FLAG_UTILIZA_WMS'
		   AND VALOR = '1'
) P ON T.CD_EMP = P.CD_EMP
	   AND T.CD_FILIAL = P.CD_FILIAL
		 WHERE T.CD_EMP = 1
			   AND EST_PROD_TRANSF_CROSS_DOCKING.CD_CTR IS NULL
	-- REMOVIDO POIS EXISTEM CLIENTES QUE GERAM O RESSUP. UM DIA ANTES.
			   AND T.STS_TRANSF = 0
			   AND COALESCE(T.FLAG_NAO_GERAR_WMS, 0) = 0
			   AND NOT EXISTS
(
	SELECT
		   CD_EMP
	FROM
		EST_PROD_TRANSF_EST_WMS_SAI W
	WHERE  T.CD_EMP = W.CD_EMP
		   AND T.CD_FILIAL = W.CD_FILIAL
		   AND T.CD_CTR = W.CD_CTR
)
			   AND NOT EXISTS
(
	SELECT
		   CD_EMP
	FROM
		EST_PROD_TRANSF_PEND_WMS PEND
	WHERE  T.CD_EMP = PEND.CD_EMP
		   AND T.CD_FILIAL = PEND.CD_FILIAL
		   AND T.CD_CTR = PEND.CD_CTR
);

--GERANDO PEDIDO WMS PRE-FATURA

		 DECLARE @CD_EMP INT;
		 DECLARE @CD_FILIAL INT;
		 DECLARE @CD_PED INT;
		 CREATE TABLE #RSPED
(CD_EMP    INT
, CD_FILIAL INT
, CD_PED    INT
);
	create nonclustered index [IDX_#RSPED_CD_EMP_FILIAL_PED] on [#RSPED] (CD_EMP ASC,CD_FILIAL ASC,CD_PED ASC)

		 INSERT INTO       #RSPED(
				CD_EMP
			  , CD_FILIAL
			  , CD_PED)
		 SELECT
				EST_PED_VD.CD_EMP
			  , EST_PED_VD.CD_FILIAL
			  , EST_PED_VD.CD_PED
		 FROM
			 EST_PED_VD
			 INNER JOIN GLB_MOV_OP ON
				EST_PED_VD.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
			 INNER JOIN EST_PED_VD_PENDENCIA ON EST_PED_VD.CD_EMP = EST_PED_VD_PENDENCIA.CD_EMP
												AND EST_PED_VD.CD_FILIAL = EST_PED_VD_PENDENCIA.CD_FILIAL
												AND EST_PED_VD.CD_PED = EST_PED_VD_PENDENCIA.CD_PED
			 LEFT OUTER JOIN EST_PROD_TRANSF_PED_VD ON EST_PED_VD.CD_EMP = EST_PROD_TRANSF_PED_VD.CD_EMP
													   AND EST_PED_VD.CD_FILIAL = EST_PROD_TRANSF_PED_VD.CD_FILIAL
													   AND EST_PED_VD.CD_PED = EST_PROD_TRANSF_PED_VD.CD_PED
			 LEFT OUTER JOIN EST_PED_VD_EST_WMS_SAI ON EST_PED_VD.CD_EMP = EST_PED_VD_EST_WMS_SAI.CD_EMP
													   AND EST_PED_VD.CD_FILIAL = EST_PED_VD_EST_WMS_SAI.CD_FILIAL
													   AND EST_PED_VD.CD_PED = EST_PED_VD_EST_WMS_SAI.CD_PED
			 INNER JOIN
				(
					SELECT
						CD_EMP
						, CD_FILIAL
						, VALOR
					FROM
						PRC_FILIAL_CONFIG
					WHERE  CD_CHAVE = 'FLAG_UTILIZA_WMS'
						   AND VALOR = '1') P ON 
				EST_PED_VD.CD_EMP = P.CD_EMP
				AND EST_PED_VD.CD_FILIAL = P.CD_FILIAL
		 WHERE 
			EST_PROD_TRANSF_PED_VD.CD_PED IS NULL
			AND EST_PED_VD_EST_WMS_SAI.CD_PED IS NULL
			AND GLB_MOV_OP.ATU_EST + GLB_MOV_OP.BX_EST > 0
			AND EST_PED_VD.DT_TRANSACAO < DATEADD(MINUTE, -5, GETDATE()); --pois estava pegando registros muito recentes e duplicando no wms por nao haver vinculo ainda na est_prod_transf_ped_vd, gerados pelo nf server



		 DELETE FROM #RSPED
		 FROM #RSPED
			  INNER JOIN RC_DEB_RC_CONV_EST_PED_VD V ON #RSPED.CD_EMP = V.CD_EMP
														AND #RSPED.CD_FILIAL = V.CD_FILIAL
														AND #RSPED.CD_PED = V.CD_PED;
		 DELETE FROM #RSPED
		 FROM #RSPED
			  INNER JOIN PDV_VD_EST_PED_VD_AGRUPAMENTO A ON #RSPED.CD_EMP = A.CD_EMP
															AND #RSPED.CD_FILIAL = A.CD_FILIAL
															AND #RSPED.CD_PED = A.CD_PED;
		 DELETE FROM #RSPED
		 FROM #RSPED
			  INNER JOIN EST_PROD_IMP_EST_PED_VD I ON #RSPED.CD_EMP = I.CD_EMP
													  AND #RSPED.CD_FILIAL = I.CD_FILIAL
													  AND #RSPED.CD_PED = I.CD_PED;


/*****************************************************************************************/

/*****************************************************************************************/

		 DECLARE prefaturaproc CURSOR FAST_FORWARD
		 FOR SELECT
					CD_EMP
				  , CD_FILIAL
				  , CD_PED
			 FROM
				 #RSPED;
		 OPEN prefaturaproc;
		 FETCH NEXT FROM prefaturaproc INTO @CD_EMP
										  , @CD_FILIAL
										  , @CD_PED;
		 WHILE @@FETCH_STATUS = 0
			 BEGIN
	--INSERINDO WMS CABECALHO	
				 INSERT INTO            EST_WMS_SAI(
						DT_PED
					  , CD_USU
					  , DT_CAD
					  , DT_ULT_ALT
					  , STS_PED
					  , CD_ROTA
					  , CD_DEST_ORIG
					  , NM_DEST_ORIG
					  , CNPJ_DEST
					  , CROSSDOCKING
					  , CD_DEPO_ORIG)
				 SELECT
						DT_EMI_PED
					  , CD_USU
					  , GETDATE()
					  , NULL
					  , 0
					  , CD_CLI
					  , CASE WHEN V_EST_PED_VD.DEST_ORIG=0 THEN  --Cliente
							CAST ('0' + RIGHT('00000000' + CAST(CD_CLI AS VARCHAR), 8) AS NUMERIC) 
						WHEN V_EST_PED_VD.DEST_ORIG=1 THEN --Fornecedor
							CAST ('1' + RIGHT('00000000' + CAST(CD_CLI AS VARCHAR), 8) AS NUMERIC) 
						WHEN V_EST_PED_VD.DEST_ORIG=2 THEN --Filial
							CAST ('2' + RIGHT('00000000' + CAST(CD_CLI AS VARCHAR), 8) AS NUMERIC) 
						END AS CD_DEST_ORIG
					  , NM_FANT
					  , CNPJ
					  , 0
					  , 0
				 FROM
					 V_EST_PED_VD
				 WHERE  CD_EMP = @CD_EMP
						AND CD_FILIAL = @CD_FILIAL
						AND CD_PED = @CD_PED;

	--INSERINDO OS DADOS DOS PRODUTOS
				 INSERT INTO               EST_WMS_SAI_IT(
						CD_PED_WMS
					  , CD_PROD
					  , QT_PROD
					  , CD_EMP
					  , VLR_IT)
				 SELECT
						IDENT_CURRENT('EST_WMS_SAI')
					  , CD_PROD
					  , QT_IT
					  , CD_EMP
					  , VLR_IT
				 FROM
					 EST_PED_VD_IT
				 WHERE  CD_EMP = @CD_EMP
						AND CD_FILIAL = @CD_FILIAL
						AND CD_PED = @CD_PED;

	--INSERINDO OS DADOS DO VINCULO COM A PRE-FATURA
				 INSERT INTO                       EST_PED_VD_EST_WMS_SAI(
						CD_EMP
					  , CD_FILIAL
					  , CD_PED
					  , CD_PED_WMS)
				 SELECT
						@CD_EMP
					  , @CD_FILIAL
					  , @CD_PED
					  , IDENT_CURRENT('EST_WMS_SAI');
				 FETCH NEXT FROM prefaturaproc INTO @CD_EMP
												  , @CD_FILIAL
												  , @CD_PED;
			 END;
		 CLOSE prefaturaproc;
		 DEALLOCATE prefaturaproc;

/*****************************************************************************************/

/*****************************************************************************************/
		
		if @NaoUtilizaWMS_X3 =0
		begin
			exec P_GERA_EST_WMS_SAI;
		end
/*****************************************************************************************/
		 DROP TABLE #RSPED;
--GERANDO PEDIDO DE ENVIO PARA O WMS ENTRADA
		 EXEC P_EST_NF_SAI_EST_WMS_ENT_INSERIR;
		 EXEC P_PROCESSA_RETORNO_EST_PED_VD_WMS;
	end try
	begin catch
		 
		DECLARE @ErrorMessage VARCHAR(4000)=ERROR_MESSAGE();  
		DECLARE @ErrorSeverity INT=ERROR_SEVERITY();  
		DECLARE @ErrorState INT=ERROR_STATE();  

	   INSERT INTO ADM_ERRO (numero, severidade, estado, rotina, linha, mensagem, dtErro )
        SELECT ERROR_NUMBER() AS numero,
                @ErrorSeverity AS severidade,
                @ErrorState AS estado,
                ERROR_PROCEDURE() AS rotina,
                ERROR_LINE() AS linha,
                @ErrorMessage AS mensagem,
                GETDATE() AS dtErro;
	   
	   RAISERROR (@ErrorMessage, 
			    @ErrorSeverity,
			    @ErrorState );
	end catch
END;