IF EXISTS ( SELECT * FROM SYS.OBJECTS
			WHERE NAME ='P_ATUALIZA_SETORIZACAO')
	BEGIN 
		DROP PROCEDURE P_ATUALIZA_SETORIZACAO
	END
GO

CREATE PROCEDURE [dbo].[P_ATUALIZA_SETORIZACAO] AS
INSERT INTO EST_SETORIZACAO_END_PDV_VD_IT 
SELECT 
	DISTINCT  
		IT.CD_EMP, 
		IT.CD_FILIAL, 
		IT.CD_VD, 
		IT.CD_IT,
		E.CD_END 
	FROM 
		PDV_VD INNER JOIN PDV_VD_IT IT ON  
		PDV_VD.CD_EMP = IT.CD_EMP AND
		PDV_VD.CD_FILIAL = IT.CD_FILIAL AND
		PDV_VD.CD_VD = IT.CD_VD
		INNER JOIN PDV_VD_IT_REC ON  
		PDV_VD_IT_REC.CD_EMP = IT.CD_EMP   AND 
		PDV_VD_IT_REC.CD_FILIAL = IT.CD_FILIAL  AND 
		PDV_VD_IT_REC.CD_VD = IT.CD_VD  AND 
		PDV_VD_IT_REC.CD_IT = IT.CD_IT   
		INNER JOIN EST_PROD ON
		IT.CD_EMP = EST_PROD.CD_EMP AND
		IT.CD_PROD = EST_PROD.CD_PROD
		INNER JOIN EST_SETORIZACAO_END_EST_PROD_FABRIC ON 
		EST_SETORIZACAO_END_EST_PROD_FABRIC.CD_EMP = EST_PROD.CD_EMP AND 
		EST_SETORIZACAO_END_EST_PROD_FABRIC.CD_FABRIC = EST_PROD.CD_FABRIC 
		INNER JOIN EST_SETORIZACAO_END E ON 
		E.CD_EMP = EST_SETORIZACAO_END_EST_PROD_FABRIC.CD_EMP AND
		E.CD_END = EST_SETORIZACAO_END_EST_PROD_FABRIC.CD_END AND
		E.CD_CTR = PDV_VD_IT_REC.CD_MEDICO_REC 
		INNER JOIN GLB_MEDICOS ON 
		GLB_MEDICOS.CD_EMP = E.CD_EMP AND 
		GLB_MEDICOS.CD_CTR = E.CD_CTR 
	WHERE 
		PDV_VD.DT_VD >= E.DT_CAD 
		AND PDV_VD.ST_VD = 0
		AND IT.ST_IT = 1
		AND NOT EXISTS(SELECT 
						CD_EMP 
				  FROM 
					EST_SETORIZACAO_END_PDV_VD_IT 
				  WHERE 
					EST_SETORIZACAO_END_PDV_VD_IT.CD_EMP = IT.CD_EMP AND
					EST_SETORIZACAO_END_PDV_VD_IT.CD_FILIAL = IT.CD_FILIAL AND
					EST_SETORIZACAO_END_PDV_VD_IT.CD_IT = IT.CD_IT AND
					EST_SETORIZACAO_END_PDV_VD_IT.CD_VD = IT.CD_VD AND
					EST_SETORIZACAO_END_PDV_VD_IT.CD_END = E.CD_END)
