 IF EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_EST_PROD_DOTZ_PRODUTO]'))
	DROP VIEW [DBO].V_EST_PROD_DOTZ_PRODUTO
GO
/*
=======================================================================================
'N�vel Classifica��o.: INFORMA��O RESTRITA
'Vers�o..............: 2112090800 
'Job.................:  
'Nome................:  V_EST_PROD_DOTZ_PRODUTO
'Objetivo............:  
'Tabelas.............: 
'Dt Cria��o..........: 
'Dt Ult Alterac�o....: 09/12/2021
'Alterado por........: 
'Criado por..........: Marcos Barros   
'=======================================================================================
*/

CREATE VIEW V_EST_PROD_DOTZ_PRODUTO
AS
SELECT  
	pvi.DT_VD,
	PROD.CD_EMP									AS CD_EMP,
	EAMC.CD_ARV_MERC_CATEG						AS CD_ARV_MERC_CATEG,
	PROD.CD_PROD								AS PRODUCT_ID,
	ISNULL(PROD.CD_FABRIC, '')					AS MANUFACTURER_ID,
	ISNULL(EAMC.DS_ARV_MERC_CATEG, '')			AS '1_CATEGORY',
	(SELECT TOP 1 ISNULL(EAMCS.DS_ARV_MERC_CATEG_SUB, '') FROM EST_ARV_MERC_CATEGORIA_SUB AS EAMCS WHERE EAMCS.CD_EMP = PROD.CD_EMP AND EAMCS.CD_ARV_MERC_CATEG = EAMC.CD_ARV_MERC_CATEG) AS '2_CATEGORY',
	ISNULL(EAMF.DS_ARV_MERC_FAMILIA, '')		AS '3_CATEGORY',
	ISNULL(EAML.DS_ARV_MERC_LINHA, '')			AS '4_CATEGORY',
	PROD.DS_PROD								AS 'NAME',
	ISNULL(CONVERT(VARCHAR(33), PROD.DT_CAD, 127), CONVERT(VARCHAR(33), GETDATE(), 127))				AS INTRODUCED_DATE,
	'' AS RETIRED_DATE,
	(CASE
		WHEN PROD.CD_UN_VD = 0 THEN PROD.CD_UN_VD
		ELSE 1
	END)										AS UNIT,
	EMC.DS_MC									AS BRAND,
	''											AS PACKAGE_SIZE,
	'un'										AS PACKAGE_UNIT,
	0											AS PRIVATE_LABEL_FLAG,
	ISNULL((SELECT TOP 1 SKU.CD_BARRA FROM EST_PROD_CD_BARRA AS SKU WHERE SKU.CD_PROD = PROD.CD_PROD), '') AS GTIN
FROM
	EST_PROD AS PROD
	LEFT JOIN EST_PROD_EST_ARV_MERCADOLOGICA AS EPEAM ON (
		EPEAM.CD_PROD = PROD.CD_PROD
	)
	LEFT JOIN EST_PROD_CD_BARRA AS SKU ON(
		SKU.CD_PROD = PROD.CD_PROD
	)
	LEFT JOIN EST_ARV_MERC_CATEGORIA AS EAMC ON (
		EAMC.CD_ARV_MERC_CATEG = EPEAM.CD_ARV_MERC_CATEG
	)
	/*LEFT JOIN EST_ARV_MERC_CATEGORIA_SUB AS EAMCS ON (
	EAMCS.CD_EMP = EAMC.CD_EMP
	AND EAMCS.CD_ARV_MERC_CATEG = EAMC.CD_ARV_MERC_CATEG
	)*/
	LEFT JOIN EST_ARV_MERC_FAMILIA AS EAMF ON (
	EAMF.CD_ARV_MERC_FAMILIA = EPEAM.CD_ARV_MERC_FAMILIA
	)
	LEFT JOIN EST_ARV_MERC_LINHA AS EAML ON (
	EAML.CD_ARV_MERC_LINHA = EPEAM.CD_ARV_MERC_FAMILIA
	)
	INNER JOIN EST_MC AS EMC ON (
	EMC.CD_EMP = PROD.CD_EMP AND EMC.CD_MC = PROD.CD_MC
	)
	INNER JOIN PDV_VD_IT pvi ON (pvi.CD_PROD = PROD.CD_PROD)
WHERE
	pvi.DT_VD >= DATEADD(DAY,-360, GETDATE())
	/*EXISTS (
		SELECT
			1
		FROM
			PDV_VD_IT
		WHERE
				PROD.CD_PROD = PDV_VD_IT.CD_PROD
			AND PDV_VD_IT.DT_VD >= DATEADD(DAY,-360, GETDATE())
		)*/
	AND pvi.CD_USU_CANCEL = 0
GROUP BY
	pvi.DT_VD,
	PROD.CD_EMP,
	EAMC.CD_ARV_MERC_CATEG,
	PROD.CD_PROD,
	PROD.CD_FABRIC,
	EAMC.DS_ARV_MERC_CATEG,
	--EAMCS.DS_ARV_MERC_CATEG_SUB,
	ISNULL(EAMC.DS_ARV_MERC_CATEG, ''),
	EAMF.DS_ARV_MERC_FAMILIA,
	EAML.DS_ARV_MERC_LINHA,
	PROD.DS_PROD,
	PROD.DT_CAD,
	PROD.CD_UN_VD,
	SKU.CD_BARRA,
	EMC.DS_MC;