/****** Object:  View [dbo].[V_EST_PROD_ICMS_SUBS_RESSARCIMENTO]    Script Date: 07/04/2013 16:57:47 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_ICMS_SUBS_RESSARCIMENTO]'))
DROP VIEW [dbo].[V_EST_PROD_ICMS_SUBS_RESSARCIMENTO]
GO
--SELECT * FROM V_EST_PROD_ICMS_SUBS_RESSARCIMENTO WHERE CD_EMP =1 AND DT_MOV BETWEEN '2018-07-01' AND '2018-07-31'
CREATE VIEW V_EST_PROD_ICMS_SUBS_RESSARCIMENTO AS
SELECT
	EST_NF_ENT_IT.CD_EMP,
	EST_NF_ENT_IT.CD_FILIAL,
	EST_NF_ENT.NR_NF,
	0 AS CD_CX,
	EST_NF_ENT.DT_ENT AS DT_MOV,
	EST_NF_ENT_IT.CD_PROD,
	EST_PROD.DS_PROD,
	EST_TRIB.CD_TRIB_FC,
	EST_NF_ENT_IT.QT_IT AS QT_IT_MOVIMENTO,
	EST_NF_ENT_IT.VLR_ICMS_SUBS AS VLR_SUBS_MOVIMENTO,
	ISNULL(EST_NF_ENT_IT.VLR_ICMS_RET,0) AS VLR_ICMS_RET_MOVIMENTO,
	EST_NF_ENT_IT.VLR_PROD_PMC AS VLR_PMC,
	0 AS VLR_DESC_MOVIMENTO,
	'ENTRADA' AS OPERACAO,
	CASE WHEN EST_PROD.CTR_PMC = 1 THEN
		'SIM'
	ELSE
		'NAO'
	END PMC,
	EST_NF_ENT_IT.CST_PIS, 
	EST_NF_ENT_IT.VLR_BASE_PIS, 
	EST_NF_ENT_IT.PERC_ALIQ_PIS,
	EST_NF_ENT_IT.VLR_PIS, 
	EST_NF_ENT_IT.VLR_BASE_COFINS,
	EST_NF_ENT_IT.PERC_ALIQ_COFINS,
	EST_NF_ENT_IT.VLR_COFINS,
	DS_ARV_MERC_LINHA
FROM
	EST_NF_ENT_IT INNER JOIN EST_TRIB ON
	EST_NF_ENT_IT.CD_TRIB = EST_TRIB.CD_TRIB 
	INNER JOIN EST_NF_ENT ON
	EST_NF_ENT_IT.CD_EMP = EST_NF_ENT.CD_EMP AND
	EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND
	EST_NF_ENT_IT.CD_NF = EST_NF_ENT.CD_NF AND
	EST_NF_ENT_IT.DT_ENT = EST_NF_ENT.DT_ENT 
	INNER JOIN EST_PROD ON
	EST_NF_ENT_IT.CD_EMP = EST_PROD.CD_EMP AND
	EST_NF_ENT_IT.CD_PROD = EST_PROD.CD_PROD 
	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON
	EST_NF_ENT_IT.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND
	EST_NF_ENT_IT.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
	LEFT OUTER JOIN EST_ARV_MERC_LINHA ON     
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA AND    
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP
UNION ALL
SELECT
	PDV_VD.CD_EMP,
	PDV_VD.CD_FILIAL,
	PDV_VD.NR_ECF,
	PDV_VD.CD_CX,
	PDV_VD.DT_VD,
	PDV_VD_IT.CD_PROD,
	EST_PROD.DS_PROD,
	PDV_VD_IT.CD_TRIB_FC,
	PDV_VD_IT.QT_IT AS QT_IT_VD,
	0 AS VLR_ICMS_SUBS,
	0 AS VLR_ICMS_RET,
	PDV_VD_IT.VLR_PMC,
	ROUND(PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT * PDV_VD_IT.TX_DESC / 100, 2) AS VLR_DESC_IT,
	'VENDA' AS OPERACAO,
	CASE WHEN EST_PROD.CTR_PMC = 1 THEN
		'SIM'
	ELSE
		'NAO'
	END PMC,
	PDV_VD_IT.CST_PIS, 
	PDV_VD_IT.VLR_BASE_PIS, 
	PDV_VD_IT.PERC_ALIQ_PIS,
	PDV_VD_IT.VLR_PIS, 
	PDV_VD_IT.VLR_BASE_COFINS,
	PDV_VD_IT.PERC_ALIQ_COFINS,
	PDV_VD_IT.VLR_COFINS,
	DS_ARV_MERC_LINHA
FROM
	PDV_VD INNER JOIN PDV_VD_IT ON 
	PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP AND 
	PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND 
	PDV_VD.CD_VD = PDV_VD_IT.CD_VD AND 
	PDV_VD.DT_VD = PDV_VD_IT.DT_VD 
	INNER JOIN EST_PROD ON
	PDV_VD_IT.CD_EMP = EST_PROD.CD_EMP AND
	PDV_VD_IT.CD_PROD = EST_PROD.CD_PROD 
	INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON
	PDV_VD_IT.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND
	PDV_VD_IT.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
	LEFT OUTER JOIN EST_ARV_MERC_LINHA ON     
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA AND    
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP
WHERE
	PDV_VD.ST_VD =0 
	AND PDV_VD_IT.ST_IT=1 