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

SELECT
	EPC.CD_FILIAL					AS ID_FILIAL,
	CASE 
		WHEN LEN(PRCFIL.CNPJ_FARMACIA_POPULAR) <= 11 THEN (LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,3)+'.'+ 
			RIGHT(LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,6),3)+'.'+ 
		    RIGHT(LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,9),3)+'-'+ 
		    RIGHT(LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,12),2))
		WHEN LEN(PRCFIL.CNPJ_FARMACIA_POPULAR) >  11 THEN (LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,2)+'.'+ 
		    RIGHT(LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,5),3)+'.'+ 
			RIGHT(LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,8),3)+'/'+ 
			RIGHT(LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,12),4)+'-'+ 
			RIGHT(LEFT (PRCFIL.CNPJ_FARMACIA_POPULAR,14),2))
	END								AS CNPJ_FILIAL,
	EPC.CD_PROD						AS EAN,
	EP.DS_PROD						AS DESCRICAO_PRODUTO,
	EPC.QT_EST						AS QUANTIDADE,
	EPP.VLR_TABELA 					AS PRECO,
	EPP.VLR_OFERTA					AS PRECO_PROMOCAO

FROM 
	EST_PROD_CPL EPC
    INNER JOIN EST_PROD EP ON (
        EP.CD_PROD    		= EPC.CD_PROD
        AND EP.CD_FILIAL 	= EPC.CD_FILIAL
        AND EP.CD_EMP    	= EPC.CD_EMP
	)

	INNER JOIN EST_PROD_PRECO EPP ON (
		EPP.CD_EMP		= EPC.CD_EMP
		AND EPP.CD_FILIAL	= EPC.CD_FILIAL
		AND EPP.CD_PROD		= EPC.CD_PROD
	)
	INNER JOIN PRC_FILIAL PRCFIL ON (
		PRCFIL.CD_EMP		= EP.CD_EMP
		AND PRCFIL.CD_FILIAL	= EP.CD_FILIAL
	)