IF OBJECT_ID('P_EST_PROD_CPL_ULT_MOV_PDV_VD_PROC') IS NOT NULL
    BEGIN
	   DROP PROCEDURE P_EST_PROD_CPL_ULT_MOV_PDV_VD_PROC;
    END;
GO

--EXEC P_EST_PROD_CPL_ULT_MOV_PDV_VD_PROC 800,1,5,null
CREATE PROCEDURE P_EST_PROD_CPL_ULT_MOV_PDV_VD_PROC  @QTDE_DIAS INT=180, 
													 @CD_EMP INT, 
													 @CD_FILIAL INT,
													 @DT_PROC DATETIME=NULL
AS
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY 6; 
DECLARE @DT_PROC_FIM DATETIME

IF @DT_PROC IS NULL
BEGIN
	SET @DT_PROC=CONVERT(CHAR(10),getdate()-@QTDE_DIAS,112)
	SET @DT_PROC_FIM= CONVERT(CHAR(10),getdate(),112)
END
ELSE
BEGIN
	SET @DT_PROC_FIM= @DT_PROC
END

CREATE TABLE #RSVDMOVIMENTO (CD_EMP		INT,
							 CD_FILIAL	INT,
							 CD_PROD	INT,
							 CD_VD		INT,
							 DT_VD		DATE,
							 DT_CONSD	DATETIME)
PRINT 'Buscando ultimo movimento - Time ' + convert(varchar(20),getdate(),108) + ' periodo ' +  convert(varchar(20),@DT_PROC,103) + ' a ' + convert(varchar(20),@DT_PROC_FIM,103);

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

INSERT INTO #RsVdMovimento(CD_EMP,CD_FILIAL,CD_PROD,CD_VD,DT_VD,DT_CONSD)
SELECT
       PDV_VD_IT.CD_EMP
     , PDV_VD_IT.CD_FILIAL
     , PDV_VD_IT.CD_PROD
	 , MAX(PDV_VD.CD_VD) CD_VD
     , MAX(PDV_VD_IT.DT_VD) DT_VD     
     , MAX(PDV_VD.DT_CONSD) DT_CONSD
FROM
     pdv_vd_it
     INNER JOIN pdv_vd ON pdv_vd_it.cd_emp = pdv_vd.cd_emp
                          AND pdv_vd_it.cd_filial = pdv_vd.cd_filial
                          AND pdv_vd_it.cd_vd = pdv_vd.cd_vd
                          AND pdv_vd_it.dt_vd = pdv_vd.dt_vd
WHERE pdv_vd_it.cd_emp = @CD_EMP
      AND pdv_vd_it.cd_filial = @CD_FILIAL
      AND pdv_vd_it.st_it = 1
      AND pdv_vd.st_vd = 0
      AND pdv_vd_it.dt_vd BETWEEN @DT_PROC AND @DT_PROC_FIM
GROUP BY
         pdv_vd_it.cd_emp
       , pdv_vd_it.cd_filial
       , pdv_vd_it.cd_prod;
/******************************************************************************************************/
PRINT 'Deletando anteriores - Time '+CONVERT(VARCHAR(20), GETDATE(), 108);

	DELETE ul
	FROM EST_PROD_CPL_ULT_MOV_PDV_VD ul
		 INNER JOIN #RsVdMovimento U ON ul.cd_emp = u.cd_emp
										AND ul.cd_filial = u.cd_filial
										AND ul.cd_prod = u.cd_prod;

/******************************************************************************************************/
  
PRINT 'Incluindo ultimo movimento - Time ' + convert(varchar(20),getdate(),108);
INSERT INTO EST_PROD_CPL_ULT_MOV_PDV_VD(CD_EMP, CD_FILIAL, CD_PROD, CD_VD, DT_CAD, DT_CONSD, DT_HR_CAD)
SELECT
      T.CD_EMP
    , T.CD_FILIAL
    , T.CD_PROD
    , T.CD_VD
    , T.DT_VD
    , T.DT_CONSD
    , GETDATE()
FROM
    #RSVDMOVIMENTO T;  