IF EXISTS (SELECT
						 *
					 FROM
						 SYS.VIEWS
					 WHERE
						OBJECT_ID = OBJECT_ID(N'[DBO].[V_EST_NF_ENT_DANFE_PROVISORIO_REPOSICAO_PBM]'))
	DROP VIEW [DBO].[V_EST_NF_ENT_DANFE_PROVISORIO_REPOSICAO_PBM]

GO

CREATE VIEW [dbo].[V_EST_NF_ENT_DANFE_PROVISORIO_REPOSICAO_PBM]
AS
	SELECT
		N.CD_EMP
		,n.CD_CTR
		,AVG (CASE WHEN COALESCE( B.QT_IT,P.QT_IT,0) > 0 THEN CASE WHEN ( N.QT_IT /  COALESCE( B.QT_IT,P.QT_IT,0) ) > 1 THEN 1
																					 ELSE ( N.QT_IT / COALESCE( B.QT_IT,P.QT_IT,0))
																					 END * 100.00
					ELSE 0
					END)        AS PERC_ATEND
		,COUNT(N.CD_PROD) QTDE_SKU
	FROM
		(SELECT
			 i.CD_EMP
			 ,i.CD_CTR
			 ,I.CD_PROD
			 ,1 AS QT_IT
		 FROM
			 EST_NF_ENT_DANFE_PROVISORIO D
			 INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_ITENS I ON
				 D.CD_CTR = I.CD_CTR
		 GROUP  BY
			i.CD_EMP
			,i.CD_CTR
			,I.CD_PROD) N
		LEFT OUTER JOIN (SELECT
							c.CD_EMP
							,c.CD_PROD
							,1 AS QT_IT
						FROM
							EST_PROD_RC_CLI_CONV c
							INNER JOIN RC_CLI_CONV_PBM pbm ON
								C.CD_EMP = pbm.CD_EMP AND
								C.CD_CONV = pbm.CD_CONV
						WHERE
							pbm.FLAG_POSSUI_REPOSICAO_PRODUTOS = 1
							AND C.REPOSICAO='S'
						GROUP  BY
							c.CD_EMP
							,c.CD_PROD
						) P ON
			N.CD_PROD = P.CD_PROD AND
			N.CD_EMP = P.CD_EMP
		LEFT JOIN (SELECT DISTINCT
						b.CD_EMP
						,I.CD_CTR
						,b.CD_PROD
						,1 QT_IT
					FROM
						EST_PROD_PRC_BANDEIRA b
						JOIN EST_NF_ENT_DANFE_PROVISORIO_ITENS I ON
							B.CD_EMP = I.CD_EMP AND
							B.CD_PROD = I.CD_PROD
						JOIN EST_NF_ENT_DANFE_PROVISORIO D ON
							I.CD_CTR = D.CD_CTR
						JOIN PRC_BANDEIRA_PRC_FILIAL E ON
							D.CD_FILIAL = E.CD_FILIAL
					WHERE
						B.REPO_PBM    = 1 AND
						B.CD_BANDEIRA = E.CD_BANDEIRA) B ON  
		N.CD_PROD	= B.CD_PROD AND
		N.CD_CTR	= B.CD_CTR AND
		N.CD_EMP	= B.CD_EMP
	GROUP  BY
		N.CD_EMP
		,N.CD_CTR

GO 
