  
IF EXISTS ( SELECT *
			  FROM SYS.VIEWS
			 WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_EST_NF_SAI_CAB]') 
			 )
BEGIN 
	DROP VIEW [DBO].[V_EST_NF_SAI_CAB]
END
GO

--------------- SQL ---------------
--SELECT * FROM V_EST_NF_SAI_CAB

CREATE VIEW [DBO].[V_EST_NF_SAI_CAB]

WITH ENCRYPTION
AS
	SELECT DBO.EST_NF_SAI.CD_EMP
		  ,DBO.EST_NF_SAI.CD_FILIAL
		  ,DBO.EST_NF_SAI.CD_NF
		  ,DBO.EST_NF_SAI.CD_NF_SERIE
		  ,DBO.EST_NF_SAI.CD_PED
		  ,DBO.EST_NF_SAI.CD_CLI
		  ,RAZAO_SOCIAL_PESSOA AS NM_DEST
		  ,NOME_FANTASIA_PESSOA AS NM_FANT
		  ,CGC_CPF
		  ,INSC_EST
		  ,ENDERECO_PESSOA AS ENDE
		  ,BAIRRO_ENDERECO_PESSOA AS BAIRRO
		  ,CEP_ENDERECO_PESSOA AS CEP
		  ,TELEFONE_PESSOA AS TEL
		  ,DS_CID
		  ,UF
		  ,CD_CID
		  ,CASE 
		  	WHEN GLB_MOV_OP.DEST_ORIG = 0
		  		THEN (
		  				SELECT CD_FILIAL
		  				FROM PRC_FILIAL_RC_CLI
		  				WHERE PRC_FILIAL_RC_CLI.CD_EMP = EST_NF_SAI.CD_EMP
		  					AND PRC_FILIAL_RC_CLI.CD_CLI = EST_NF_SAI.CD_CLI
		  				)
		  	WHEN GLB_MOV_OP.DEST_ORIG = 1
		  		THEN 0
		  	WHEN GLB_MOV_OP.DEST_ORIG = 2
		  		THEN EST_NF_SAI.CD_CLI
		  	END AS CD_CLI_FILIAL
		  ,ENVIAR_XML_NFE
		  ,EMAIL_PESSOA			   AS EMAIL
		  ,DBO.EST_NF_SAI.NF_NF
		  ,DBO.EST_NF_SAI.CD_VEND
		  ,DBO.EST_NF_SAI.CD_COND_PGTO
		  ,DBO.EST_NF_SAI.DT_EMI_NF
		  ,DBO.EST_NF_SAI.HR_EMI_NF
		  ,DBO.EST_NF_SAI.VLR_NF
		  ,DBO.EST_NF_SAI.VLR_TOT_PROD
		  ,DBO.EST_NF_SAI.VLR_IPI
		  ,DBO.EST_NF_SAI.VLR_ICMS
		  ,DBO.EST_NF_SAI.VLR_FRETE
		  ,0					   AS VLR_OUTRAS_DESP
		  ,DBO.EST_NF_SAI.TP_FRETE
		  ,DBO.EST_NF_SAI.VLR_DESC AS VLR_DESC_CORPO
		  ,DBO.EST_NF_SAI.INF_DESCONTO
		  ,DBO.EST_NF_SAI.NM_COMPRADOR
		  ,DBO.EST_NF_SAI.STS_NF
		  ,DBO.EST_NF_SAI.OBS1
		  ,DBO.EST_NF_SAI.OBS2
		  ,DBO.EST_NF_SAI.OBS3
		  ,DBO.EST_NF_SAI.OBS4
		  ,DBO.EST_NF_SAI.OBS5
		  ,DBO.EST_NF_SAI.OBS6
		  ,DBO.EST_NF_SAI.OBS7
		  ,DBO.EST_NF_SAI.OBS8
		  ,DBO.EST_NF_SAI.OBS9
		  ,DBO.EST_NF_SAI.OBS10
		  ,DBO.EST_NF_SAI.OBS11
		  ,DBO.EST_NF_SAI.DS_TRANSP
		  ,DBO.EST_NF_SAI.PLACA_VEIC
		  ,DBO.EST_NF_SAI.PLACA_UF
		  ,DBO.EST_NF_SAI.CGC_CPF_TRANSP
		  ,DBO.EST_NF_SAI.ENDE_TRANSP
		  ,DBO.EST_NF_SAI.DS_CID_TRANSP
		  ,DBO.EST_NF_SAI.UF_TRANSP
		  ,DBO.EST_NF_SAI.INSC_EST_TRANSP
		  ,DBO.EST_NF_SAI.QT_VOL
		  ,DBO.EST_NF_SAI.ESP_VOL  AS ESPCIE_VOL
		  ,DBO.EST_NF_SAI.MC_VOL
		  ,DBO.EST_NF_SAI.NR_VOL
		  ,DBO.EST_NF_SAI.PESO_VOL AS PESO_BRT
		  ,DBO.EST_NF_SAI.PESO_LIQ_VOL AS PES_LIQ
		  ,DBO.EST_NF_SAI.CD_NT_MOV
		  ,DBO.GLB_MOV_OP.CD_MOV_OP
		  ,DBO.GLB_MOV_OP.DS_MOV_OP
		  ,DBO.GLB_MOV_OP.DS_MOV_OP_USUAL
		  ,DBO.GLB_MOV_OP.TP_MOV_OP_TRANSA
		  ,DBO.GLB_MOV_OP.TP_MOV
		  ,DBO.GLB_MOV_OP.BX_EST
		  ,DBO.EST_NF_SAI.DT_CAD
		  ,DBO.EST_NF_SAI.CD_USU
		  ,DBO.EST_NF_SERIE.SERIE
		  ,DBO.EST_NF_SERIE.ESPC
		  ,DBO.EST_NF_SAI.VLR_BASE_ICMS
		  ,DBO.EST_NF_SAI.VLR_BASE_ICMS_SUBS AS VLR_BASE_SUBS_CAB
		  ,DBO.EST_NF_SAI.VLR_ICMS_SUBS AS VLR_ICMS_SUBS_CAB
		  ,DBO.GLB_MOV_OP.DEST_ORIG
		  ,GLB_MOV_OP.TP_ESTQ
		  ,DBO.GLB_MOV_OP.ENVIO_OPERADOR_LOG
		  ,DBO.GLB_MOV_OP.FLAG_RETORNO_OPERADOR_LOG
		  ,DBO.GLB_MOV_OP.FLAG_ENTRADA_OPERADOR_LOG
		  ,EST_NF_SAI.NR_SELO_SAIDA
		  ,EST_NF_SAI.NR_FORMULARIO
		  ,EST_NF_SAI.DT_ULT_ALT
		  ,ISNULL(ISNULL(CL.CD_FILIAL, FO.CD_FILIAL), 0) AS CD_FILIAL_CLI
		  ,ISNULL(EST_NF_SAI.QT_CX_VOL, 0) QT_CX_VOL
		  ,ISNULL(EST_NF_SAI.NF_MANUAL, 0) AS NF_MANUAL
		  ,ISNULL(EST_NF_SERIE.TP_SR_NFE, 0) AS TP_SR_NFE
		  ,DBO.EST_NF_SAI.DT_CANCEL
		  ,ISNULL(EST_NF_SAI.NFE_REJEITADA, 0) AS NFE_REJEITADA
		  ,NFE.NR_AUTORIZADOR
		  ,NFE.NR_PROTOCOLO
		  ,NFE.VS_PROTOCOLO
		  ,NFE.DT_RECBTO
		  ,NFE.DG_VALIDADOR
		  ,NFE.TP_AMB
		  ,NFE.VS_APLIC
		  ,NFE.REG_CONTG
		  ,EST_NF_SAI.VERSAO_APLICATIVO
		  ,EST_NF_SAI.APLICATIVO
		  ,ISNULL(EST_NF_SAI.EMAIL_TRANSP, '') AS EMAIL_TRANSP
		  ,ISNULL(EST_NF_SAI.VLR_ICMS_DESONERADO, 0) AS VLR_ICMS_DESONERADO_CAB
		  ,ISNULL(GLB_MOV_OP.FLAG_MOV_OP_NFE_CPL, 0) AS FLAG_MOV_OP_NFE_CPL
		  ,COD_MODELO
		  ,V_GLB_PESSOA.FLAG_SIMPLES_NACIONAL AS FLAG_MICRO_EMPRESA
		  ,CASE 
		  	WHEN RC_DEB_EST_NF_SAI.CD_NF IS NULL
		  		THEN 'NAO'
		  	ELSE 'SIM'
		  	END AS CONTASRECEBER
		  ,GLB_MOV_OP.GRL_FINAN					AS GERA_DP_RC
		  ,ISNULL(VLR_FECOP_ST, 0)				AS VLR_FECOP_ST
		  ,ISNULL(VLR_FECOP, 0)					AS VLR_FECOP
		  ,ISNULL(VLR_FECOP_RET, 0)				AS VLR_FECOP_RET
		  ,ISNULL(GLB_MOV_OP.FLAG_N_EXPORTAR_CONTABIL, 0) AS FLAG_N_EXPORTAR_CONTABIL
		  ,EST_NF_SAI.UUID
		  ,ISNULL(GLB_MOV_OP.FLAG_DEVOLUCAO, 0) AS FLAG_DEVOLUCAO
		  ,ISNULL(EST_NF_SAI.VLR_BASE_CALC_EFET,0) AS VLR_BASE_CALC_EFET
		  ,ISNULL(EST_NF_SAI.VLR_EFET_ICMS,0) AS VLR_EFET_ICMS
		  ,DBO.GLB_MOV_OP.ATU_EST
	  FROM DBO.EST_NF_SAI
		  INNER JOIN DBO.GLB_MOV_OP ON DBO.EST_NF_SAI.CD_NT_MOV = DBO.GLB_MOV_OP.CD_MOV_OP
		  OUTER APPLY UFD_GLB_PESSOA(EST_NF_SAI.CD_CLI, GLB_MOV_OP.DEST_ORIG) V_GLB_PESSOA
		  INNER JOIN DBO.EST_NF_SERIE ON DBO.EST_NF_SAI.CD_EMP = DBO.EST_NF_SERIE.CD_EMP
							         AND DBO.EST_NF_SAI.CD_NF_SERIE = DBO.EST_NF_SERIE.CD_NF_SERIE
		  LEFT JOIN PRC_FILIAL_RC_CLI CL ON DBO.EST_NF_SAI.CD_EMP = CL.CD_EMP
									 	AND DBO.EST_NF_SAI.CD_CLI = CL.CD_CLI
		  LEFT JOIN PRC_FILIAL_PG_FORN FO ON DBO.EST_NF_SAI.CD_EMP = FO.CD_EMP
		  								 AND DBO.EST_NF_SAI.CD_CLI = FO.CD_FORN
		  LEFT JOIN EST_NF_SAI_NFE NFE ON DBO.EST_NF_SAI.CD_EMP = NFE.CD_EMP
									  AND DBO.EST_NF_SAI.CD_FILIAL = NFE.CD_FILIAL
									  AND DBO.EST_NF_SAI.CD_NF = NFE.CD_NF
		  LEFT OUTER JOIN RC_DEB_EST_NF_SAI ON EST_NF_SAI.CD_EMP = RC_DEB_EST_NF_SAI.CD_EMP
										   AND EST_NF_SAI.CD_FILIAL = RC_DEB_EST_NF_SAI.CD_FILIAL
										   AND EST_NF_SAI.CD_NF = RC_DEB_EST_NF_SAI.CD_NF
	GO
	
	
	