/****** Object:  StoredProcedure [dbo].[P_EST_ARV_MERC_MIX_ANALISE_ESTOQUE]    Script Date: 02/26/2014 19:52:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_ARV_MERC_MIX_ANALISE_ESTOQUE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_ARV_MERC_MIX_ANALISE_ESTOQUE]
GO
GO 
CREATE PROCEDURE P_EST_ARV_MERC_MIX_ANALISE_ESTOQUE AS 
--EXEC P_EST_ARV_MERC_MIX_ANALISE_ESTOQUE
DECLARE @MES INT
DECLARE @ANO INT
DECLARE @DT_ATUAL DATETIME

SET @DT_ATUAL=GETDATE()
SET @MES=MONTH(@DT_ATUAL)
SET @ANO=YEAR(@DT_ATUAL)

--LIMPANDO REGISTROS
TRUNCATE TABLE EST_ARV_MERC_MIX_ANALISE_ESTOQUE

--INSERINDO REGISTROS
INSERT INTO EST_ARV_MERC_MIX_ANALISE_ESTOQUE
	(CD_EMP,
	CD_PROD,
	ANO,
	MES,
	QTDE_ESTOQUE_REDE,
	QT_PEND_ENT_REDE,
	QT_PEND_SAIDA_REDE,
	QTDE_MEDIAF,
	QTDE_MEDIAP,
	CD_SIGLA,
	DT_CAD,
	VLR_TABELA)
SELECT
	CD_EMP,
	CD_PROD,
	ANO,
	MES,
	SUM(QTDE_ESTOQUE_REDE),
	SUM(QT_PEND_ENT_REDE),
	SUM(QT_PEND_SAIDA_REDE),
	SUM(QTDE_MEDIAF_REDE),
	SUM(QTDE_MEDIAP_REDE),
	SUM(CD_SIGLA),
	DT_ATUAL,
	SUM(VLR_TABELA)
FROM
(SELECT 
	EST.CD_EMP,
	EST.CD_PROD,
	@ANO ANO,
	@MES MES,
	QT_EST QTDE_ESTOQUE_REDE,
	QT_PEND_ENT QT_PEND_ENT_REDE,
	QT_PEND_SAI QT_PEND_SAIDA_REDE,
	0 AS QTDE_MEDIAF_REDE,
	0 AS QTDE_MEDIAP_REDE,
	0 AS CD_SIGLA,
	@DT_ATUAL AS DT_ATUAL,
	0 AS VLR_TABELA
FROM
	V_EST_PROD_CPL_ESTOQUE EST INNER JOIN PRC_FILIAL FIL ON 
	EST.CD_EMP = FIL.CD_EMP AND 
	EST.CD_FILIAL = FIL.CD_FILIAL
WHERE 
	FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
	AND FIL.CD_EMP =1
UNION ALL
SELECT 
	f.CD_EMP,
	f.CD_PROD,
	@ANO,
	@MES,
	0 AS QTDE_ESTOQUE_REDE,
	0 AS QT_PEND_ENT_REDE,
	0 AS QT_PEND_SAIDA_REDE,
	QTDE_MEDIAF AS QTDE_MEDIAF_REDE,
	0 AS QTDE_MEDIAP_REDE,
	0 AS CD_SIGLA,
	@DT_ATUAL,
	0 AS VLR_TABELA
FROM
	EST_PROD_CPL_MEDIAF F INNER JOIN PRC_FILIAL FIL ON 
	F.CD_EMP = FIL.CD_EMP AND 
	F.CD_FILIAL = FIL.CD_FILIAL
	LEFT OUTER JOIN PRC_FILIAL_DEPOSITO d ON
	F.CD_EMP = d.CD_EMP AND
	F.CD_FILIAL = d.CD_FILIAL 
WHERE 
	FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
	AND FIL.CD_EMP =1
	and d.CD_FILIAL is null
UNION ALL
SELECT 
	P.CD_EMP,
	P.CD_PROD,
	@ANO,
	@MES,
	0 AS QTDE_ESTOQUE_REDE,
	0 AS QT_PEND_ENT_REDE,
	0 AS QT_PEND_SAIDA_REDE,
	0 AS QTDE_MEDIAF_REDE,
	QTDE_MEDIAP AS QTDE_MEDIAP_REDE,
	0 AS CD_SIGLA,
	@DT_ATUAL,
	0 AS VLR_TABELA
FROM
	EST_PROD_CPL_MEDIAP P INNER JOIN PRC_FILIAL FIL ON 
	P.CD_EMP = FIL.CD_EMP AND 
	P.CD_FILIAL = FIL.CD_FILIAL
	LEFT OUTER JOIN PRC_FILIAL_DEPOSITO d ON
	P.CD_EMP = d.CD_EMP AND
	P.CD_FILIAL = d.CD_FILIAL 
WHERE 
	FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
	AND FIL.CD_EMP =1
	and d.CD_FILIAL is null	
UNION ALL
SELECT 
	C.CD_EMP,
	C.CD_PROD,
	@ANO,
	@MES,
	0 AS QTDE_ESTOQUE_REDE,
	0 AS QT_PEND_ENT_REDE,
	0 AS QT_PEND_SAIDA_REDE,
	0 AS QTDE_MEDIAF_REDE,
	0 AS QTDE_MEDIAP_REDE,
	CD_SIGLA,
	@DT_ATUAL,
	VLR_TABELA
FROM
	EST_PROD_CPL C INNER JOIN PRC_FILIAL FIL ON 
	C.CD_EMP = FIL.CD_EMP AND 
	C.CD_FILIAL = FIL.CD_FILIAL
	INNER JOIN PRC_FILIAL_DEPOSITO d ON
	C.CD_EMP = d.CD_EMP AND
	C.CD_FILIAL = d.CD_FILIAL 
	INNER JOIN EST_PROD_PRECO ON
	C.CD_EMP = EST_PROD_PRECO.CD_EMP AND
	C.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL AND
	C.CD_PROD = EST_PROD_PRECO.CD_PROD 
WHERE 
	FIL.FLAG_FILIAL_NAO_PARTICP_MIX = 0
	AND FIL.CD_EMP =1
	) RS
GROUP BY
	CD_EMP,
	CD_PROD,
	ANO,
	MES,
	DT_ATUAL


