/****** Object:  View [dbo].[V_ITEC_SUITE_EST_PROD_SUPRIMENTO_ENTREGA_LOJA_DEMANDA]    Script Date: 01/22/2013 09:45:54 ******/
IF EXISTS (SELECT * FROM sys.views
					 WHERE
						object_id = OBJECT_ID(N'[dbo].[V_ITEC_SUITE_EST_PROD_SUPRIMENTO_ENTREGA_LOJA_DEMANDA]'))
	DROP VIEW [dbo].[V_ITEC_SUITE_EST_PROD_SUPRIMENTO_ENTREGA_LOJA_DEMANDA]

GO 
--select * from V_ITEC_SUITE_EST_PROD_SUPRIMENTO_ENTREGA_LOJA_DEMANDA where cd_emp = 1 and cd_usu =1
CREATE VIEW V_ITEC_SUITE_EST_PROD_SUPRIMENTO_ENTREGA_LOJA_DEMANDA AS
SELECT 
	E.CD_EMP,
	E.CD_USU,
	E.CD_FILIAL,
	LJ.NM_FILIAL_LISTA + ' ' + LJ.DS_SIGLA_FILIAL_CP               AS DS_FILIAL,
	LJ.BAIRRO,
	Min(S.DS_SIGLA)                       AS DS_SIGLA,
	Sum(E.QTDE_PROD)                      AS QTDE_PROD,
	Round(Sum(QTDE_PROD * E.VLR_PROD), 2) AS VLR_PROD,
	Round(Sum(CASE WHEN Isnull(E.PERC_DESC, 0) > 0 THEN 
		( QTDE_PROD * E.VLR_PROD ) * E.PERC_DESC / 100
	ELSE 
		( QTDE_PROD * E.VLR_PROD ) * Isnull(P.PERC_DESC_FABRICA, 0) / 100
	END), 2) AS VLR_DESC
FROM   
	EST_PROD_SUPRIMENTO_ENTREGA_LOJA E
	INNER JOIN PRC_FILIAL LJ
		ON E.CD_EMP = LJ.CD_EMP
			AND E.CD_FILIAL = LJ.CD_FILIAL
	LEFT JOIN PRC_BANDEIRA_PRC_FILIAL BLJ
		ON LJ.CD_EMP = BLJ.CD_EMP
			AND LJ.CD_FILIAL = BLJ.CD_FILIAL
	INNER JOIN EST_PROD P
		ON E.CD_EMP = P.CD_EMP
			AND E.CD_PROD = P.CD_PROD
	INNER JOIN EST_PROD_FABRIC F
		ON P.CD_FABRIC = F.CD_FABRIC
	INNER JOIN EST_PROD_CURVA_SIGLA S
		ON E.CD_SIGLA = S.CD_SIGLA
	INNER JOIN EST_PROD_CPL PL
		ON e.CD_EMP = PL.CD_EMP
			AND e.CD_PROD = PL.CD_PROD
			AND e.CD_FILIAL = pl.cd_filial
GROUP  BY 
	E.CD_EMP,
	E.CD_USU,
	E.CD_FILIAL,
	LJ.NM_FILIAL_LISTA,
	LJ.DS_SIGLA_FILIAL_CP,
	LJ.BAIRRO