IF NOT OBJECT_ID('P_PDV_VD_IT_VD_PBM_EST_PED_CP') IS NULL
	BEGIN 
		DROP PROCEDURE P_PDV_VD_IT_VD_PBM_EST_PED_CP
	END

GO 
 
CREATE PROCEDURE [dbo].[P_PDV_VD_IT_VD_PBM_EST_PED_CP] (@DT_PROC DATE = NULL)
AS
BEGIN
	BEGIN TRY 

		DECLARE @RS_ITENS TABLE (CD_EMP		INT,
								 CD_FILIAL		INT,
								 CD_VD			INT,
								 CD_IT			INT,
								 CD_PROD		INT,
								 QT_IT			INT,
								 NR_AUTORIZACAO VARCHAR(30),
								 VLR_CUSTO		MONEY,
								 CD_PBM			INT)


		DECLARE @RS_CD_EMP		INT
		DECLARE @RS_CD_FILIAL	INT 
		DECLARE @RS_CD_VD		INT
		DECLARE @RS_CD_IT		INT
		DECLARE @RS_CD_PROD		INT
		DECLARE @RS_QT_IT		INT
		DECLARE @RS_VLR_CUSTO	MONEY
		DECLARE @CD_PBM			INT

		DECLARE @CD_PED			INT 
		DECLARE @CD_FORN		INT
		DECLARE @DT_EMI			DATETIME
		DECLARE @CD_FILIAL_ANT	INT
		DECLARE @CD_PBM_ANT		INT
		DECLARE @CD_NT_MOV		INT
		DECLARE @CD_NT_OP		INT
		DECLARE @CD_COND_PGTO	INT
		DECLARE @SEQ_ITEM		INT
		DECLARE @CD_TRIB		INT
		DECLARE @DT_INI			DATE
		DECLARE @DT_FIM			DATE


		IF @DT_PROC IS NULL
		BEGIN
			SET @DT_INI = CONVERT(DATE, DATEADD(DAY, -3, GETDATE()))
			SET @DT_FIM = CONVERT(DATE, GETDATE())
		END
		ELSE
		BEGIN
			SET @DT_INI = @DT_PROC
			SET @DT_FIM = @DT_PROC
		END


		SET @CD_FORN = 0
		SET @CD_NT_MOV = 0
		SET @CD_NT_OP = 4
		SELECT @CD_FORN = COALESCE(CASE WHEN ISNUMERIC(VALOR) = 1 THEN VALOR ELSE 0 END, 0) FROM PRC_EMP_CONFIG WHERE CD_EMP = 1 AND CD_CHAVE = 'CD_FORN_PADRAO_PEDIDO_PBM'
		SELECT @CD_NT_MOV = COALESCE(CASE WHEN ISNUMERIC(VALOR) = 1 THEN VALOR ELSE 0 END, 0) FROM PRC_EMP_CONFIG WHERE CD_EMP = 1 AND CD_CHAVE = 'CD_MOV_OP_PADRAO_PEDIDO_PBM'

		IF @CD_FORN = 0
		BEGIN
			RAISERROR ('CODIGO DO FORNECEDOR PADRAO NAO CONFIGURADO', 16, 1)
		END

		IF @CD_NT_MOV = 0
		BEGIN
			RAISERROR ('CODIGO DA NATUREZA DE MOVIMENTACAO PADRAO NAO CONFIGURADO', 16, 1)
		END
		
		PRINT 'BUSCANDO ITENS PENDENTES DE GERAÇÃO PARA AS DATAS: ' + CAST(@DT_INI AS VARCHAR) + ' - ' + CAST(@DT_FIM AS VARCHAR)

		INSERT
			@RS_ITENS 
		SELECT DISTINCT 
			IT.CD_EMP, 
			IT.CD_FILIAL, 
			IT.CD_VD, 
			IT.CD_IT, 
			IT.CD_PROD, 
			IT.QT_IT, 
			IT_PBM.NR_AUTORIZACAO, 
			ROUND(CPL.VLR_CUSTO,2) AS VLR_CUSTO,
			IT_PBM.CD_CLI_PBM
		FROM 
			PDV_VD_IT_VD_PBM IT_PBM
			INNER JOIN  PDV_VD_IT IT ON 
				IT_PBM.CD_EMP = IT.CD_EMP
				AND IT_PBM.CD_FILIAL = IT.CD_FILIAL 
				AND IT_PBM.CD_VD = IT.CD_VD
				AND IT_PBM.CD_IT = IT.CD_IT
			INNER JOIN EST_PROD_CPL CPL ON 
				IT.CD_EMP = CPL.CD_EMP
				AND IT.CD_FILIAL = CPL.CD_FILIAL
				AND IT.CD_PROD = CPL.CD_PROD
			INNER JOIN EST_PBM_PROJETO_EST_PROD PBM_PROD ON 
				IT.CD_EMP = PBM_PROD.CD_EMP					
				AND IT.CD_PROD = PBM_PROD.CD_PROD
		WHERE 
			IT.DT_VD BETWEEN @DT_INI AND @DT_FIM
			AND PBM_PROD.FLAG_REPOS_PBM = 1
			AND NOT EXISTS (SELECT 
								I.CD_EMP
							FROM
								PDV_VD_IT_VD_PBM_EST_PED_CP I
							WHERE 
								I.CD_EMP			= IT_PBM.CD_EMP
								AND I.CD_FILIAL	= IT_PBM.CD_FILIAL
								AND I.CD_VD		= IT_PBM.CD_VD
								AND I.CD_IT		= IT_PBM.CD_IT)
			AND NOT EXISTS (SELECT 
								CLI.CD_EMP
							FROM
								RC_CLI CLI
							WHERE
								CLI.CD_EMP = IT_PBM.CD_EMP
								AND CLI.CD_CLI = IT_PBM.CD_CLI_PBM
								AND CLI.ROT_CONV_EXT = 5) --IGNORANDO FARMACIA POPULAR
		AND CD_CLI_PBM > 0
	
		SET @CD_FILIAL_ANT = 0
		SET @CD_PED = 0 
		SET @CD_PBM_ANT = 0
		SET @DT_EMI = CONVERT(VARCHAR(10), GETDATE(),112)
		SET @CD_COND_PGTO = 0
		SET @SEQ_ITEM = 0
		SET @CD_TRIB = 8

		DECLARE 
			RS_RESULT 
		CURSOR FOR 
		SELECT
			CD_EMP,
			CD_FILIAL,
			CD_PROD,
			SUM(QT_IT) AS QT_IT,
			MAX(VLR_CUSTO),
			CD_PBM
		FROM 
			@RS_ITENS
		GROUP BY
			CD_EMP,
			CD_FILIAL,
			CD_PROD,
			CD_PBM
		ORDER BY 
			CD_FILIAL,
			CD_PBM
		 
		--Abrindo cursor
		OPEN RS_RESULT
			
		--Atribuindo valores do select nas variáveis
		FETCH NEXT FROM 
			RS_RESULT 
		INTO  
			@RS_CD_EMP, 
			@RS_CD_FILIAL, 									
			@RS_CD_PROD,
			@RS_QT_IT,
			@RS_VLR_CUSTO,
			@CD_PBM
								
		PRINT 'PROCESSANDO RESULTADO'
			--Iniciando laço
			WHILE @@FETCH_STATUS = 0
				BEGIN
					IF @RS_CD_FILIAL <> @CD_FILIAL_ANT OR  @CD_PBM <> @CD_PBM_ANT
						BEGIN
							IF @CD_PED > 0
								BEGIN 

									PRINT 'ATUALIZANDO VALORS PEDIDO'
									UPDATE
										PED
									SET
										VLR_PED = ISNULL((SELECT SUM(I.QT_IT * I.VLR_IT)
														   FROM EST_PED_CP_IT I
														   WHERE I.CD_EMP = PED.CD_EMP
																AND I.CD_FILIAL = PED.CD_FILIAL
																AND I.CD_PED = PED.CD_PED),0),
										VLR_TOT_PROD = ISNULL((SELECT SUM(I.QT_IT * I.VLR_IT) 
														   FROM EST_PED_CP_IT I 
														   WHERE I.CD_EMP = PED.CD_EMP 
																AND I.CD_FILIAL = PED.CD_FILIAL 
																AND I.CD_PED = PED.CD_PED),0)									
									FROM
										EST_PED_CP PED
									WHERE
										PED.CD_EMP = 1
										AND PED.CD_FILIAL = @CD_FILIAL_ANT
										AND PED.CD_PED = @CD_PED


									INSERT INTO 
										PDV_VD_IT_VD_PBM_EST_PED_CP 
									SELECT
										CD_EMP,
										CD_FILIAL,
										CD_VD,
										CD_IT,
										NR_AUTORIZACAO,
										@CD_PED
									FROM 
										@RS_ITENS
									WHERE 
										CD_EMP = 1
										AND CD_FILIAL = @CD_FILIAL_ANT
								END

							SET @CD_FILIAL_ANT = @RS_CD_FILIAL
							SET @CD_PED = ISNULL((SELECT MAX(CD_PED) FROM EST_PED_CP WHERE CD_EMP = @RS_CD_EMP AND CD_FILIAL = @RS_CD_FILIAL),0) + 1
							SET @SEQ_ITEM =1

							PRINT 'INSERINDO CABEÇALHO DO PEDIDO'
							INSERT INTO EST_PED_CP (CD_EMP,
													CD_FILIAL,
													CD_PED,
													CD_FORN,
													DT_EMI,
													VLR_PED,
													VLR_TOT_PROD,
													VLR_DESC,
													DT_PREV_ENT,
													VLR_ICMS,
													VLR_IPI,
													VLR_BASE_SUBS,
													VLR_ICMS_SUBS,
													CD_NT_MOV,
													STS_PED,
													VLR_BASE_ICMS,
													CD_USU,
													DT_CAD,																	
													CD_PED_FORN,
													CD_COND_PGTO,
													ORIGEM_PED,						
													APLICATIVO,
													VERSAO_APLICATIVO,
													DT_FATURAMENTO)

									SELECT  @RS_CD_EMP AS CD_EMP,
											@RS_CD_FILIAL AS CD_FILIAL,
											@CD_PED AS CD_PED,
											@CD_FORN AS CD_FORN,
											CONVERT(VARCHAR(10), GETDATE(),112),
											0 AS VLR_PED,
											0 AS VLR_TOT_PROD,
											0 AS VLR_DESC,
											@DT_EMI AS DT_PREV_ENT,
											0 AS VLR_ICMS,
											0 AS VLR_IPI,
											0 AS VLR_BASE_SUBS,
											0 AS VLR_ICMS_SUBS,
											@CD_NT_MOV,
											0 AS STS_PED,
											0 AS VLR_BASE_ICMS,
											1 AS CD_USU,
											CONVERT(VARCHAR(10), GETDATE(),112) AS DT_CAD,																	
											0 AS CD_PED_FORN,
											@CD_COND_PGTO,
											0 AS ORIGEM_PED,						
											'PROCEDURE - PBM' AS APLICATIVO,
											'1.0' AS VERSAO_APLICATIVO,
											@DT_EMI AS DT_FATURAMENTO


									INSERT INTO 
										EST_PED_CP_PBM (CD_EMP, CD_FILIAL, CD_PED, CD_PBM)
									VALUES (@RS_CD_EMP, @RS_CD_FILIAL, @CD_PED, @CD_PBM)

						END

					PRINT 'INSERINDO ITENS DO PEDIDO'
					INSERT INTO EST_PED_CP_IT ( CD_EMP,
												CD_FILIAL,
												CD_PED,
												CD_PROD,
												SEQ_ITEM,
												VLR_IT,
												QT_IT,
												CD_TRIB,
												PERC_IPI,
												VLR_CUSTO,
												VLR_BASE_SUBS,
												VLR_ICMS_SUBS,
												ALIQ_ICMS,
												VLR_BASE_ICMS,
												CD_NT_OP,
												VLR_IPI,
												VLR_ICMS,
												PERC_DESC,
												VLR_DESC,
												PERC_IVA,
												QT_EMB)
					
						SELECT  @RS_CD_EMP AS CD_EMP,
								@RS_CD_FILIAL AS CD_FILIAL,
								@CD_PED AS CD_PED,
								@RS_CD_PROD AS CD_PROD,
								@SEQ_ITEM AS SEQ_ITEM,
								@RS_VLR_CUSTO AS VLR_IT,
								@RS_QT_IT,
								@CD_TRIB AS CD_TRIB,
								0 AS PERC_IPI,
								@RS_VLR_CUSTO AS VLR_CUSTO,
								0 AS VLR_BASE_SUBS,
								0 AS VLR_ICMS_SUBS,
								0 AS ALIQ_ICMS,
								0 AS VLR_BASE_ICMS,
								@CD_NT_OP,
								0 AS VLR_IPI,
								0 AS VLR_ICMS,
								0 AS PERC_DESC,
								0 AS VLR_DESC,
								0 AS PERC_IVA,
								1 AS QT_EMB										

					SET @SEQ_ITEM = @SEQ_ITEM + 1


					--Atribuindo valores do select nas variáveis
					FETCH NEXT FROM 
						RS_RESULT 
					INTO  
						@RS_CD_EMP, 
						@RS_CD_FILIAL,
						@RS_CD_PROD,
						@RS_QT_IT,
						@RS_VLR_CUSTO,
						@CD_PBM

				END

		IF @CD_PED > 0 
			BEGIN 
				PRINT 'ATUALIZANDO VALORES DO PEDIDO - 2'
				UPDATE PED SET	
					VLR_PED = ISNULL((SELECT SUM(I.QT_IT * I.VLR_IT) 
										FROM EST_PED_CP_IT I 
										WHERE I.CD_EMP = PED.CD_EMP 
											AND I.CD_FILIAL = PED.CD_FILIAL 
											AND I.CD_PED = PED.CD_PED),0),
					VLR_TOT_PROD = ISNULL((SELECT SUM(I.QT_IT * I.VLR_IT) 
										FROM EST_PED_CP_IT I 
										WHERE I.CD_EMP = PED.CD_EMP 
											AND I.CD_FILIAL = PED.CD_FILIAL 
											AND I.CD_PED = PED.CD_PED),0)									
				FROM 
					EST_PED_CP PED
				WHERE
					PED.CD_EMP = 1
					AND PED.CD_FILIAL = @CD_FILIAL_ANT
					AND PED.CD_PED = @CD_PED

				INSERT INTO 
					PDV_VD_IT_VD_PBM_EST_PED_CP 
				SELECT 
					CD_EMP,
					CD_FILIAL,
					CD_VD,
					CD_IT,
					NR_AUTORIZACAO,
					@CD_PED
				FROM 
					@RS_ITENS
				WHERE 
					CD_EMP = 1
					AND CD_FILIAL = @CD_FILIAL_ANT
			END


		CLOSE RS_RESULT
		DEALLOCATE RS_RESULT	
			
			
		END TRY
		BEGIN CATCH
			CLOSE RS_RESULT
			DEALLOCATE RS_RESULT	


	   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		

