IF EXISTS
(
	SELECT *
	FROM sys.objects
	WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PARAM_COMPRAS_PERFIL_PRC_FILIAL_APLICAR]')
		  AND type IN(N'P', N'PC')
)
	DROP PROCEDURE [dbo].[P_EST_PARAM_COMPRAS_PERFIL_PRC_FILIAL_APLICAR];
GO
GO
--EXEC P_EST_PARAM_COMPRAS_PERFIL_PRC_FILIAL_APLICAR 
CREATE PROCEDURE P_EST_PARAM_COMPRAS_PERFIL_PRC_FILIAL_APLICAR (@CD_CTR INT=0, @REAPLICAR INT=0)
AS
SET NOCOUNT ON;
CREATE TABLE #RsEstProd (CD_EMP INT, 
						 CD_FILIAL INT, 
						 CD_PERFIL_DEMOGRAFICO INT,
						 CD_PERFIL_TAMANHO INT,
						 CD_USU INT, 
						 DT_CAD DATETIME,
						 DT_ULT_ALT DATETIME)

CREATE TABLE #RsLoteProc (CD_CTR INT, CD_USU INT)

INSERT INTO #RsLoteProc
SELECT
	DISTINCT L.CD_CTR,
	L.CD_USU
FROM
	EST_PARAM_COMPRAS_PERFIL_LOTE_TRANSACAO L INNER JOIN EST_PARAM_COMPRAS_PERFIL_LOTE_TRANSACAO_PRC_FILIAL P ON
	L.CD_CTR = P.CD_CTR
WHERE
	L.STS_LOTE = CASE WHEN @REAPLICAR = 1 THEN L.STS_LOTE ELSE 0 END
	AND L.CD_CTR = CASE WHEN @CD_CTR =0 THEN L.CD_CTR ELSE @CD_CTR END

INSERT INTO #RsEstProd
SELECT
	PR.CD_EMP,
	PR.CD_FILIAL,
	P.CD_PERFIL_DEMOGRAFICO,
	P.CD_PERFIL_TAMANHO,
	L.CD_USU,
	GETDATE(),
	NULL
FROM
	#RsLoteProc L INNER JOIN EST_PARAM_COMPRAS_PERFIL_LOTE_TRANSACAO_PERFIL P ON
	L.CD_CTR = P.CD_CTR 
	INNER JOIN EST_PARAM_COMPRAS_PERFIL_LOTE_TRANSACAO_PRC_FILIAL PR ON
	L.CD_CTR = PR.CD_CTR 

--Alterando perfil filial
UPDATE	
	P
SET 
	P.CD_PERFIL_DEMOGRAFICO=PE.CD_PERFIL_DEMOGRAFICO,
	P.CD_PERFIL_TAMANHO=PE.CD_PERFIL_TAMANHO,
	P.CD_USU=PE.CD_USU,
	P.DT_CAD  =PE.DT_CAD
FROM
	EST_PARAM_COMPRAS_PERFIL_PRC_FILIAL p  INNER JOIN #RsEstProd pe on
	pe.CD_EMP = p.CD_EMP AND
	pe.CD_FILIAL = p.CD_FILIAL


--Gravando dados do produto
INSERT INTO EST_PARAM_COMPRAS_PERFIL_PRC_FILIAL
(	
CD_EMP,
CD_FILIAL,
CD_PERFIL_DEMOGRAFICO,
CD_PERFIL_TAMANHO,
CD_USU,
DT_CAD
)
SELECT
	pe.CD_EMP,
	pe.CD_FILIAL,
	pe.CD_PERFIL_DEMOGRAFICO,
	pe.CD_PERFIL_TAMANHO,
	pe.CD_USU,
	pe.DT_CAD
FROM
	#RsEstProd pe LEFT OUTER JOIN EST_PARAM_COMPRAS_PERFIL_PRC_FILIAL p on
	pe.CD_EMP = p.CD_EMP AND
	pe.CD_FILIAL = p.CD_FILIAL
where
	p.CD_FILIAL is null

--Marcado lote como processado
update
	LP
set 
	LP.STS_LOTE=1
from
	#RsLoteProc L INNER JOIN EST_PARAM_COMPRAS_PERFIL_LOTE_TRANSACAO LP ON
	L.CD_CTR = LP.CD_CTR 

DROP TABLE #RsEstProd;
DROP TABLE #RsLoteProc;