IF OBJECT_ID('P_EST_PROD_FAMILIA_DROGASMIL') IS NOT NULL
BEGIN
	DROP PROCEDURE P_EST_PROD_FAMILIA_DROGASMIL
END

GO

CREATE PROCEDURE P_EST_PROD_FAMILIA_DROGASMIL @CD_FILIAL INT
AS
BEGIN

DECLARE @RECORD_COUNT INT;

WITH T AS (
SELECT 
	A.CD_EMP,
	ISNULL(DP.CD_PROD_BANDEIRA,A.CD_PROD) AS CD_PROD,
	A.CD_ARV_MERC_FAMILIA
FROM
	V_EST_PROD_ARV_MERCADOLOGICA A
	LEFT JOIN V_CAD_UNICO_DEPARAS_EST_PROD DP ON A.CD_PROD = DP.CD_PROD AND DP.CD_FILIAL = @CD_FILIAL
WHERE
CD_ARV_MERC_FAMILIA > 0
	
)



SELECT *,(SELECT COUNT(0) FROM T) AS NR_REGISTROS FROM T


END

