 /****** Object:  StoredProcedure [dbo].[P_EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE_EST_NF_ENT]    Script Date: 02/11/2014 17:19:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE_EST_NF_ENT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE_EST_NF_ENT]
GO
-- exec P_EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE_EST_NF_ENT '2015-01-01','2015-08-14'
CREATE PROCEDURE P_EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE_EST_NF_ENT @DT_INI DATETIME=NULL, @DT_FIM DATETIME=NULL AS
UPDATE
	p
SET 
	p.DT_PROC=@DT_INI,
	p.DT_CAD=GETDATE()
FROM
	EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE p 
	INNER JOIN EST_NF_ENT_IT i ON
	p.CD_EMP = i.CD_EMP AND
	p.CD_FILIAL = i.CD_FILIAL AND
	p.CD_PROD = i.CD_PROD		
	INNER JOIN EST_NF_ENT e ON
	i.CD_EMP = e.CD_EMP AND 
	i.CD_FILIAL = e.CD_FILIAL AND
	i.CD_NF = e.CD_NF 
	INNER JOIN GLB_MOV_OP m ON
	e.CD_NT_MOV = m.CD_MOV_OP
WHERE
	m.ATU_EST=1
	AND E.DT_ENT BETWEEN @DT_INI AND @DT_FIM
	and p.DT_PROC > e.DT_ENT;

INSERT INTO EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE
	  (CD_EMP,
	  CD_FILIAL,
	  CD_PROD,
	  DT_PROC,
	  DT_CAD,
	  DT_PROC_ATUAL)
SELECT
	DISTINCT i.CD_EMP,
	i.CD_FILIAL,
	i.CD_PROD,
	MIN(i.DT_ENT),
	GETDATE(),
	NULL
FROM
	EST_NF_ENT_IT i LEFT OUTER JOIN EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE p ON
	i.CD_EMP= p.CD_EMP AND
	i.CD_FILIAL = p.CD_FILIAL AND
	i.CD_PROD = p.CD_PROD
WHERE
	i.DT_ENT BETWEEN @DT_INI AND @DT_FIM
	AND p.CD_PROD IS NULL
GROUP BY
	i.CD_EMP,
	i.CD_FILIAL,
	i.CD_PROD

UPDATE
	p
SET 
	p.DT_PROC=@DT_INI,
	p.DT_CAD=GETDATE()
FROM
	EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE p INNER JOIN EST_PROD_IMP imp ON
	p.CD_EMP = imp.CD_EMP AND
	p.CD_FILIAL = imp.CD_FILIAL AND
	p.CD_PROD = imp.CD_PROD
	INNER JOIN EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP i ON   
	imp.CD_EMP = i.CD_EMP AND   
	imp.CD_FILIAL = i.CD_FILIAL_DEST AND   
	imp.CD_IMP = i.CD_IMP AND   
	imp.CD_PROD = i.CD_PROD  
WHERE
	imp.DT_CAD BETWEEN  @DT_INI AND @DT_FIM
	and p.DT_PROC > imp.DT_CAD;

INSERT INTO EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE
	  (CD_EMP,
	  CD_FILIAL,
	  CD_PROD,
	  DT_PROC,
	  DT_CAD,
	  DT_PROC_ATUAL)
SELECT
	DISTINCT i.CD_EMP,
	i.CD_FILIAL_DEST,
	i.CD_PROD,
	MIN(imp.DT_CAD),
	GETDATE(),
	NULL
FROM
	EST_PROD_CPL_CUSTO_MEDIO_PROCESSAMENTO_PENDENTE p INNER JOIN EST_PROD_IMP imp ON
	p.CD_EMP = imp.CD_EMP AND
	p.CD_FILIAL = imp.CD_FILIAL AND
	p.CD_PROD = imp.CD_PROD
	LEFT OUTER JOIN EST_PROD_REQ_TRANSF_LIB_ITENS_ENT_EST_PROD_IMP i ON   
	imp.CD_EMP = i.CD_EMP AND   
	imp.CD_FILIAL = i.CD_FILIAL_DEST AND   
	imp.CD_IMP = i.CD_IMP AND   
	imp.CD_PROD = i.CD_PROD 
WHERE
	imp.DT_CAD BETWEEN @DT_INI AND @DT_FIM
	AND p.CD_PROD IS NULL
GROUP BY
	i.CD_EMP,
	i.CD_FILIAL_DEST,
	i.CD_PROD