/****** Object:  View [dbo].[V_EST_PROD_TRANSF_FATURAMENTO_AUTOMATICO]    Script Date: 02/03/2014 19:11:29 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_PROD_TRANSF_FATURAMENTO_AUTOMATICO]'))
DROP VIEW [dbo].[V_EST_PROD_TRANSF_FATURAMENTO_AUTOMATICO]
GO
GO
GO
GO
--select * from V_EST_PROD_TRANSF_FATURAMENTO_AUTOMATICO WHERE CD_RESSUP = 1213115
--Versao 3988
CREATE VIEW [dbo].[V_EST_PROD_TRANSF_FATURAMENTO_AUTOMATICO] AS 
SELECT
	DISTINCT EST_PROD_TRANSF.CD_EMP,
	EST_PROD_TRANSF.CD_FILIAL,
	ISNULL(PRC_FILIAL.QT_IT_NF,1) AS QTDE_ITENS_NF,
	ISNULL(PRC_FILIAL.CTR_LOTE,0) AS CTR_LOTE_FILIAL,
	ISNULL(EST_ROTAS_ENTREGA.DS_ROTA,'') AS DS_ROTA,
	ISNULL(EST_ROTAS_ENTREGA.CD_TRANSP,0) AS CD_TRANSP,
	EST_PROD_TRANSF.CD_CTR AS CD_RESSUP,
	ISNULL(rsparVenctoPreFatura.VALOR,10) AS QTDE_DIAS_VENCTO_PRE,
	CASE WHEN GrpEconFilialFat.CD_FILIAL IS NULL THEN --Nao informado Filial de faturamento diferente do destino do ressuprimento
		CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialDest.CD_GRP_ECON THEN
			0
		ELSE
			1
		END 
	ELSE
		CASE WHEN EST_PROD_TRANSF_CROSS_DOCKING.CD_CTR IS NULL THEN --Ressuprimento nao e  proveniente de cross
			CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialFat.CD_GRP_ECON THEN
				0
			ELSE
				1
			END 
		ELSE
			CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialDest.CD_GRP_ECON THEN
				0
			ELSE
				1
			END 
		END
	END AS GRUPO_ECON,
	0 AS CD_PED,
	CASE WHEN GrpEconFilialFat.CD_FILIAL IS NULL THEN --Nao informado Filial de faturamento diferente do destino do ressuprimento
		CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialDest.CD_GRP_ECON THEN
			PRC_FILIAL_RC_CLI.CD_CLI
		ELSE
			EST_PROD_TRANSF.CD_FIL_DEST
		END 
	ELSE
		CASE WHEN EST_PROD_TRANSF_CROSS_DOCKING.CD_CTR IS NULL THEN --Ressuprimento nao e  proveniente de cross
			CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialFat.CD_GRP_ECON THEN
				PrcFilialRcCliFat.CD_CLI
			ELSE
				PRC_FILIAL_FAT_RESSUPRIMENTO.CD_FILIAL_FATURAMENTO
			END 
		ELSE
			CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialDest.CD_GRP_ECON THEN
				PRC_FILIAL_RC_CLI.CD_CLI
			ELSE
				EST_PROD_TRANSF.CD_FIL_DEST
			END 
		END
	END AS CD_CLI,
	0 AS CD_VEND,
	CASE WHEN GrpEconFilialFat.CD_FILIAL IS NULL THEN --Nao informado Filial de faturamento diferente do destino do ressuprimento
		CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialDest.CD_GRP_ECON THEN
			case when isnull(EST_PROD_TRANSF.TP_REQ_TRANSF,0) =1  then
				rsparConsvenda.valor
			else rsparvenda.valor end 
		ELSE
			case when isnull(EST_PROD_TRANSF.TP_REQ_TRANSF,0) =1  then
				rsparCons.valor
			else rspar.valor end 
		END 
	ELSE
		CASE WHEN EST_PROD_TRANSF_CROSS_DOCKING.CD_CTR IS NULL THEN --Ressuprimento nao e  proveniente de cross
			CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialFat.CD_GRP_ECON THEN
				case when isnull(EST_PROD_TRANSF.TP_REQ_TRANSF,0) =1  then
					rsparConsvenda.valor
				else rsparvenda.valor end 
			ELSE
				case when isnull(EST_PROD_TRANSF.TP_REQ_TRANSF,0) =1  then
					rsparCons.valor
				else rspar.valor end 
			END 
		ELSE
			CASE WHEN GrpEconDepo.CD_GRP_ECON <> GrpEconFilialDest.CD_GRP_ECON THEN
				case when isnull(EST_PROD_TRANSF.TP_REQ_TRANSF,0) =1  then
					rsparConsvenda.valor
				else rsparvenda.valor end 
			ELSE
				case when isnull(EST_PROD_TRANSF.TP_REQ_TRANSF,0) =1  then
					rsparCons.valor
				else rspar.valor end 
			END 
		END
	END	AS CD_NT_MOV,
	CONVERT(CHAR(10),GETDATE(),112) AS DT_EMI_PED,
	0 AS VLR_TOT_PED,
	0 AS CD_COND_PGTO,
	'' AS NM_COMPRADOR,
	'' AS NM_TRANSP,
	0 AS FRETE,
	'' AS OBS,
	1 CD_USU,
	0 AS STS_PED,
	CONVERT(CHAR(10),GETDATE(),112) AS DT_FAT,
	0 AS VLR_TOT_PROD,
	0 AS VLR_DESC,
	CONVERT(CHAR(10),GETDATE() + CAST(ISNULL(rsparVenctoPreFatura.VALOR,2) AS INT),112) AS DT_VALID,
	CONVERT(CHAR(10),GETDATE(),112) AS DT_CAD,
	NULL AS DT_ULT_ATU,
	NULL AS DT_BX,
	0 AS TP_BX,
	GETDATE() AS HORA_CAD,
	0 AS TP_PED,
	0 AS VLR_FRETE,
	'' AS OBS_NF1,
	'' AS OBS_NF2,
	ISNULL(EST_ROTAS_ENTREGA.OBS_NF,0) AS OBS_NF9,
	0 AS PRZ_ENTRG,
	0 AS PRZ_VLD,
	0 AS COB,
	ISNULL (EST_PROD_TRANSF.QTDE_VOLUMES,0) AS QT_VOL,
	GETDATE() AS DT_GRAVACAO,
	0 BLOQ_FATURAMENTO,
	0 AS VLR_ICMS_SUBS,
	0 AS VLR_ICMS_RETIDO,
	0 AS VLR_OUTROS_CAB,
	0 AS FLAG_VALID_TRIB,
	i.CD_PROD,
	ISNULL(EST_PROD.CTR_LOTE,0) AS CTR_LOTE_PROD,
	ISNULL(i.QT_AT, 0) AS QT_IT ,
	EST_PROD_CPL.QT_EST,
	ISNULL(i.VLR_CUSTO, 0) AS VLR_IT,
	ISNULL(((i.QT_AT * i.VLR_CUSTO) * i.PERC_DESC) / 100, 0)AS VLR_DESC_IT,
	EST_PROD_CPL.QT_EST AS QT_EST_ANT_IT,
	0 AS PERC_IPI_IT,
	ISNULL(i.PERC_DESC, 0) AS PERC_DESC_IT,
	0 AS VLR_ICMS_SUBS_FUT_IT,
	ISNULL((i.QT_AT * i.VLR_CUSTO),0) AS VLR_PROD_PED_IT,
	0 AS VLR_SUFRAMA_IT,
	0 AS ORD_GRAVA_IT,
	0 AS PERC_DESC_CTR_IT,
	0 AS PERC_COMI_IT,
	0 AS PERC_COMI_CTR_IT,
	0 AS VALIDADE_IT,
	0 AS CD_TRIB_IT,
	ISNULL(EST_PROD_PRECO_TBL.VLR_VD,0) AS VLR_PMC_IT,
	0 AS VLR_IMPOSTO_REA_IT,
	0 AS VLR_ICMS_SUBS_IT,
	0 AS VLR_ICMS_RETIDO_IT,
	0 AS VLR_OUTROS_IT,
	0 AS CTR_PMC,
	EST_PROD.DS_PROD,
	ISNULL(EST_PROD.CONTROLE_SNGPC,0) AS CONTROLE_SNGPC,
	TP_TRANSF =CASE WHEN filialOrig.FILIAL_MTZ = 1 THEN
						ISNULL((SELECT   
								CASE WHEN PRC_FILIAL.FILIAL_COPIA_MTZ IN (1,3) THEN
									1									
								ELSE								
									PRC_FILIAL.FLAG_TP_RESSUPRIMENTO
								END
						FROM         
							PRC_FILIAL 
						WHERE
							PRC_FILIAL.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
							PRC_FILIAL.CD_FILIAL = EST_PROD_TRANSF.CD_FIL_DEST),0)
					WHEN filialOrig.FILIAL_COPIA_MTZ = 1 THEN
						ISNULL((SELECT   
								CASE WHEN PRC_FILIAL.FILIAL_MTZ = 1 THEN
									1
								ELSE								
									PRC_FILIAL.FLAG_TP_RESSUPRIMENTO
								END
						FROM         
							PRC_FILIAL 									
						WHERE
							PRC_FILIAL.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
							PRC_FILIAL.CD_FILIAL = EST_PROD_TRANSF.CD_FIL_DEST),0)
					ELSE
						--Validando se a filial de destino 
						--e copida da matriz e somente a matriz envia por requisicao
						--quando o status 2 tiver ativo.
						ISNULL((SELECT   
								CASE WHEN PRC_FILIAL.FILIAL_COPIA_MTZ = 2 THEN
									0
								ELSE								
									PRC_FILIAL.FLAG_TP_RESSUPRIMENTO
								END
						FROM         
							PRC_FILIAL 
						WHERE
							PRC_FILIAL.CD_EMP = EST_PROD_TRANSF.CD_EMP AND 
							PRC_FILIAL.CD_FILIAL = EST_PROD_TRANSF.CD_FIL_DEST),0)								
					END,
	ISNULL(compVinc.CD_CTR,0) AS NF_COMPARTILHADA,
	EST_PROD.NR_NCM,
	ISNULL(i.VLR_CUSTO_ACERTO,0) AS VLR_CUSTO_ACERTO,
	0 AS VLR_ICMS_DESONERADO,
	0 AS ICMS_DESONERADO_MOTIVO,
	ISNULL(EST_PROD_UN_MED.TP_UN,0) AS TP_UN,
	ISNULL(EST_PROD.PESO_PROD_VD,0) AS PESO_PROD_VD, 
	ISNULL(EST_PROD.PESO_PROD_VD_LIQ,0) AS PESO_PROD_VD_LIQ,
	ISNULL(I.ALIQ_ICMS_ENTRADA,0) AS ALIQ_ICMS_ENTRADA,
    ISNULL(I.VLR_CUSTO_PROD_ORIG,0) AS VLR_CUSTO_PROD_ORIG -- FARMAGOI-3249
FROM
	EST_PROD_TRANSF INNER JOIN EST_PROD_TRANSF_CONF_FASES ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_CONF_FASES.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_CONF_FASES.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_CONF_FASES.CD_CTR 
	INNER JOIN EST_PROD_TRANSF_CONF ON
	EST_PROD_TRANSF_CONF_FASES.CD_EMP = EST_PROD_TRANSF_CONF.CD_EMP AND
	EST_PROD_TRANSF_CONF_FASES.CD_CTR_FASES = EST_PROD_TRANSF_CONF.CD_CTR_FASES AND
	EST_PROD_TRANSF_CONF_FASES.CD_CONTROLE = EST_PROD_TRANSF_CONF.CD_CONTROLE
	INNER JOIN EST_PROD_TRANSF_IT i ON
	EST_PROD_TRANSF.CD_EMP = i.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL  = i.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = i.CD_CTR
	INNER JOIN EST_PROD_TRANSF_PENDENCIA ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR 
	INNER JOIN PRC_FILIAL ON
	EST_PROD_TRANSF.CD_EMP = PRC_FILIAL.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = PRC_FILIAL.CD_FILIAL
    INNER JOIN   EST_PROD_CPL ON
    i.CD_EMP = EST_PROD_CPL.CD_EMP AND 
    i.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND 
    i.CD_PROD = EST_PROD_CPL.CD_PROD 
    LEFT OUTER JOIN V_EST_PROD_PRECO_TBL EST_PROD_PRECO_TBL ON
    EST_PROD_CPL.CD_EMP = EST_PROD_PRECO_TBL.CD_EMP AND 
    EST_PROD_CPL.CD_FILIAL = EST_PROD_PRECO_TBL.CD_FILIAL AND 
    EST_PROD_CPL.CD_PROD = EST_PROD_PRECO_TBL.CD_PROD 
	INNER JOIN EST_PROD ON
	i.CD_EMP = EST_PROD.CD_EMP AND
	i.CD_PROD = EST_PROD.CD_PROD
	INNER JOIN EST_PROD_UN_MED ON
	EST_PROD.CD_EMP = EST_PROD_UN_MED.CD_EMP AND
	EST_PROD.CD_UN_CP = EST_PROD_UN_MED.CD_CTR_UN
    LEFT OUTER JOIN EST_ROTAS_ENTREGA_PRC_FILIAL ON
    EST_PROD_TRANSF.CD_EMP = EST_ROTAS_ENTREGA_PRC_FILIAL.CD_EMP AND 
    EST_PROD_TRANSF.CD_FIL_DEST = EST_ROTAS_ENTREGA_PRC_FILIAL.CD_FILIAL 
    LEFT OUTER JOIN EST_ROTAS_ENTREGA ON
    EST_ROTAS_ENTREGA_PRC_FILIAL.CD_ROTA = EST_ROTAS_ENTREGA.CD_ROTA
	INNER JOIN PRC_GRP_ECON_PRC_FILIAL GrpEconDepo ON
	PRC_FILIAL.CD_EMP = GrpEconDepo.CD_EMP AND
	PRC_FILIAL.CD_FILIAL = GrpEconDepo.CD_FILIAL 
	INNER JOIN PRC_GRP_ECON_PRC_FILIAL GrpEconFilialDest ON
	EST_PROD_TRANSF.CD_EMP = GrpEconFilialDest.CD_EMP AND
	EST_PROD_TRANSF.CD_FIL_DEST = GrpEconFilialDest.CD_FILIAL 
	LEFT OUTER JOIN (SELECT 
					CD_EMP,
					CD_FILIAL,
					VALOR 
				FROM 
					PRC_FILIAL_CONFIG 
				WHERE 
					CD_CHAVE = 'CD_MOV_OP_TRANSF_FILIAIS') rspar ON
	EST_PROD_TRANSF.CD_EMP = rspar.CD_EMP	AND
	EST_PROD_TRANSF.CD_FILIAL = rspar.CD_FILIAL		

	LEFT OUTER JOIN (SELECT 
					CD_EMP,
					CD_FILIAL,
					VALOR 
				FROM 
					PRC_FILIAL_CONFIG 
				WHERE 
					CD_CHAVE = 'CD_MOV_OP_VENDA_GRP_ECON') rsparvenda ON
	EST_PROD_TRANSF.CD_EMP = rsparvenda.CD_EMP	AND
	EST_PROD_TRANSF.CD_FILIAL = rsparvenda.CD_FILIAL

	LEFT OUTER JOIN (SELECT 
					CD_EMP,
					CD_FILIAL,
					VALOR 
				FROM 
					PRC_FILIAL_CONFIG 
				WHERE 
					CD_CHAVE = 'CD_MOV_OP_TRANSF_FILIAIS_CONSUMO') rsparCons ON
	EST_PROD_TRANSF.CD_EMP = rsparCons.CD_EMP	AND
	EST_PROD_TRANSF.CD_FILIAL = rsparCons.CD_FILIAL		
	
	LEFT OUTER JOIN (SELECT 
					CD_EMP,
					CD_FILIAL,
					VALOR 
				FROM 
					PRC_FILIAL_CONFIG 
				WHERE 
					CD_CHAVE = 'CD_MOV_OP_VENDA_GRP_ECON_CONSUMO') rsparConsvenda ON
	EST_PROD_TRANSF.CD_EMP = rsparConsvenda.CD_EMP	AND
	EST_PROD_TRANSF.CD_FILIAL = rsparConsvenda.CD_FILIAL
	
	LEFT OUTER JOIN (SELECT 
					CD_EMP,
					CD_FILIAL,
					VALOR 
				FROM 
					PRC_FILIAL_CONFIG 
				WHERE 
					CD_CHAVE = 'QTDE_DIAS_VENCIMENTO_PRE_FATURA') rsparVenctoPreFatura ON
	EST_PROD_TRANSF.CD_EMP = rsparvenda.CD_EMP	AND
	EST_PROD_TRANSF.CD_FILIAL = rsparvenda.CD_FILIAL
	
	INNER JOIN PRC_FILIAL_RC_CLI ON
	EST_PROD_TRANSF.CD_EMP = PRC_FILIAL_RC_CLI.CD_EMP AND
	EST_PROD_TRANSF.CD_FIL_DEST = PRC_FILIAL_RC_CLI.CD_FILIAL	
	LEFT OUTER JOIN 
	(SELECT
		DISTINCT CD_EMP,
		CD_FILIAL,
		CD_CTR
	  FROM
		EF_NF_CTR_NR_COMPARTILHADO) comp ON
	EST_PROD_TRANSF.CD_EMP = comp.CD_EMP AND
	EST_PROD_TRANSF.CD_FIL_DEST = comp.CD_FILIAL 
	LEFT OUTER JOIN EF_NF_CTR_NR_COMPARTILHADO compVinc ON
	comp.CD_EMP = compVinc.CD_EMP AND
	comp.CD_CTR = compVinc.CD_CTR AND
	EST_PROD_TRANSF.CD_FIL_DEST = compVinc.CD_FILIAL
	INNER JOIN PRC_FILIAL filialOrig ON
	EST_PROD_TRANSF.CD_EMP = filialOrig.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = filialOrig.CD_FILIAL
	
	LEFT OUTER JOIN EST_PROD_TRANSF_CROSS_DOCKING ON
	EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_CROSS_DOCKING.CD_EMP AND
	EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_CROSS_DOCKING.CD_FILIAL AND
	EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_CROSS_DOCKING.CD_CTR
		
	--Filial de faturamento quando o mesmo foi diferente da do destino
	LEFT OUTER JOIN PRC_FILIAL_FAT_RESSUPRIMENTO ON
	EST_PROD_TRANSF.CD_EMP = PRC_FILIAL_FAT_RESSUPRIMENTO.CD_EMP AND
	EST_PROD_TRANSF.CD_FIL_DEST = PRC_FILIAL_FAT_RESSUPRIMENTO.CD_FILIAL
	LEFT OUTER JOIN PRC_GRP_ECON_PRC_FILIAL GrpEconFilialFat ON
	PRC_FILIAL_FAT_RESSUPRIMENTO.CD_EMP = GrpEconFilialFat.CD_EMP AND
	PRC_FILIAL_FAT_RESSUPRIMENTO.CD_FILIAL_FATURAMENTO = GrpEconFilialFat.CD_FILIAL 
	LEFT OUTER JOIN PRC_FILIAL_RC_CLI PrcFilialRcCliFat ON
	PRC_FILIAL_FAT_RESSUPRIMENTO.CD_EMP = PrcFilialRcCliFat.CD_EMP AND
	PRC_FILIAL_FAT_RESSUPRIMENTO.CD_FILIAL_FATURAMENTO = PrcFilialRcCliFat.CD_FILIAL	
	--Fim
WHERE
	PRC_FILIAL.FLAG_FATURAMENTO_AUTOMATICO_HABILITADO IN (1,2)
	AND i.QT_AT > 0