/****** Object:  View [dbo].[V_EST_NF_SAI_SINTEGRA]    Script Date: 10/31/2013 15:56:02 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_NF_SAI_SINTEGRA]'))
DROP VIEW [dbo].[V_EST_NF_SAI_SINTEGRA]
GO
GO
GO
--Versao 2756
--SELECT * FROM V_EST_NF_SAI_SINTEGRA WHERE CD_EMP = 1	AND CD_FILIAL = 999 AND  cd_nf=2173903
CREATE          VIEW [dbo].[V_EST_NF_SAI_SINTEGRA]
AS
SELECT     
	EST_NF_SAI.CD_EMP, 
	EST_NF_SAI.CD_FILIAL, 
	EST_NF_SAI.CD_NF, 
	CGC_CPF = CASE WHEN DEST_ORIG = 0 THEN
                          (SELECT     
										CGC_CPF
                            FROM          
										RC_CLI
                            WHERE      
										RC_CLI.CD_EMP = EST_NF_SAI.CD_EMP AND 
										RC_CLI.CD_CLI = EST_NF_SAI.CD_CLI) 
				  WHEN DEST_ORIG = 1 THEN
                          (SELECT     
										CGC_CPF
                            FROM          
										PG_FORN
                            WHERE      
										PG_FORN.CD_EMP = EST_NF_SAI.CD_EMP AND 
										PG_FORN.CD_FORN = EST_NF_SAI.CD_CLI) 
				   WHEN DEST_ORIG = 2 THEN
                          (SELECT     
										CGC
                            FROM          
										PRC_FILIAL
                            WHERE      
										PRC_FILIAL.CD_EMP = EST_NF_SAI.CD_EMP AND 
										PRC_FILIAL.CD_FILIAL = EST_NF_SAI.CD_CLI) 
                      ELSE 'SEM CLASSIFICACAO' END,
	INSC_EST = CASE WHEN DEST_ORIG = 0 THEN
                          (SELECT     
										INSC_EST
                            FROM          
										RC_CLI
                            WHERE      
										RC_CLI.CD_EMP = EST_NF_SAI.CD_EMP AND 
										RC_CLI.CD_CLI = EST_NF_SAI.CD_CLI) 
						  WHEN DEST_ORIG = 1 THEN
	                        (SELECT     
										INSC_EST
                            FROM          
										PG_FORN
                            WHERE      
										PG_FORN.CD_EMP = EST_NF_SAI.CD_EMP AND 
										PG_FORN.CD_FORN = EST_NF_SAI.CD_CLI) 
						   WHEN DEST_ORIG = 2 THEN
                          (SELECT     
										INSC_EST
                            FROM          
										PRC_FILIAL
                            WHERE      
										PRC_FILIAL.CD_EMP = EST_NF_SAI.CD_EMP AND 
										PRC_FILIAL.CD_FILIAL = EST_NF_SAI.CD_CLI) 
                      ELSE 'SEM CLASSIFICACAO' END,
   UF = CASE WHEN DEST_ORIG = 0 THEN
           (SELECT     
					UF
            FROM         
					RC_CLI,
					GLB_CID
	         WHERE      
					RC_CLI.CD_EMP = EST_NF_SAI.CD_EMP AND 
					RC_CLI.CD_CLI = EST_NF_SAI.CD_CLI AND
					RC_CLI.CD_CID = GLB_CID.CD_CID) 
			WHEN DEST_ORIG = 1 THEN
           	(SELECT     
						UF
             FROM          
						PG_FORN,
						GLB_CID
             WHERE      
						PG_FORN.CD_EMP = EST_NF_SAI.CD_EMP  AND 
						PG_FORN.CD_FORN = EST_NF_SAI.CD_CLI AND 
						PG_FORN.CD_CID = GLB_CID.CD_CID) 
			WHEN DEST_ORIG = 2 THEN
              (SELECT     
						UF
               FROM          
						PRC_FILIAL,
						GLB_CID
               WHERE      
						PRC_FILIAL.CD_EMP = EST_NF_SAI.CD_EMP 		AND 
						PRC_FILIAL.CD_FILIAL = EST_NF_SAI.CD_CLI  AND 
						PRC_FILIAL.CD_CID = GLB_CID.CD_CID) 
    		ELSE 
					'SEM CLASSIFICACAO' 
			END,
	EST_NF_SERIE.SERIE AS SERIE_NF, 
	EST_NF_SAI.NF_NF AS NR_NF, 
	EST_NF_SAI.DT_EMI_NF, 	
	EST_NF_SAI.STS_NF,	
	EST_NF_SAI.VLR_DESC,	
	EST_NF_SAI_IT.CD_PROD, 
	EST_NF_SAI_IT.QT_IT, 

	(EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT)- EST_NF_SAI_IT.VLR_DESC_IT AS VLR_TOT_IT,   
	CASE WHEN EST_TRIB.ICMS =0 AND EST_TRIB.SUBS_TRIB = 0 THEN
		0
	ELSE
		EST_NF_SAI_IT.VLR_BASE_ICMS_IT
	END VLR_ICMS_BASE,

	CASE WHEN EST_TRIB.ICMS =0 AND EST_TRIB.SUBS_TRIB = 0 THEN
		0
	ELSE
	   EST_NF_SAI_IT.VLR_ICMS_IT
    END VLR_ICMS, 
	
	CASE WHEN EST_TRIB.ICMS =0 AND EST_TRIB.SUBS_TRIB = 0 THEN
		0
	ELSE	
		EST_NF_SAI_IT.ALIQ_ICMS
	END ALIQ_ICMS, 


	CASE WHEN EST_TRIB.ICMS =0 AND EST_TRIB.SUBS_TRIB = 0 AND EST_TRIB.ISENTO=0 AND EST_TRIB.SUBS_TRIB_ANT=0  THEN
		0
	ELSE	
		CASE WHEN EST_TRIB.ICMS=0 AND EST_TRIB.ISENTO=0 THEN
			0
		ELSE
			CASE WHEN EST_TRIB.ISENTO=0 THEN
				CASE WHEN  EST_TRIB.SUBS_TRIB = 0 THEN
					case when ((EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT)- EST_NF_SAI_IT.VLR_DESC_IT) - EST_NF_SAI_IT.VLR_BASE_ICMS_IT>0 then
					((EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT)- EST_NF_SAI_IT.VLR_DESC_IT) - EST_NF_SAI_IT.VLR_BASE_ICMS_IT
					else
						0
					end
				ELSE
					0
				END
			ELSE
				(round((EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT)  - EST_NF_SAI_IT.VLR_DESC_IT,2)) 
			END
		END
	END VLR_ISENTO, 

	CASE WHEN EST_TRIB.SUBS_TRIB=1 THEN
		(round((EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT)  - EST_NF_SAI_IT.VLR_DESC_IT,2)) 
	ELSE
		0
	END AS VLR_TOT_IT_SUBS,
   	EST_NF_SAI_IT.VLR_BASE_SUBS, 
	EST_NF_SAI_IT.VLR_ICMS_SUBS, 
	EST_NF_SAI_IT.VLR_DESC_IT, 
	(((EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT)- EST_NF_SAI_IT.VLR_DESC_IT) * EST_NF_SAI_IT.PERC_IPI) / 100.00 AS VLR_IPI,
	EST_TRIB.CD_TRIB_FC,
	GLB_NT_OP.CD_NT_OP_FC AS CFOP,
	EST_NF_SERIE.TP_SR_NFE,
	EST_NF_SERIE.COD_MODELO,
	1 AS FLAG_EMISSAO_PROPRIA,

	ISNULL(VLR_OUTROS_DESPESAS,0) AS VLR_OUTROS_DESPESAS,

	--CASE WHEN  (EST_TRIB.ICMS) = 0 AND  (EST_TRIB.SUBS_TRIB) = 0 AND (EST_TRIB.ISENTO)= 0 THEN
	--	VLR_TOT_IT
	--ELSE
	--	0
	--END + 
	--CASE WHEN  (EST_TRIB.SUBS_TRIB) > 0 THEN
 
	--  ((EST_NF_SAI_IT.VLR_TOT_IT )  +  EST_NF_SAI_IT.VLR_ICMS_SUBS ) -(EST_NF_SAI_IT.VLR_ICMS_SUBS  +  EST_NF_SAI_IT.VLR_BASE_ICMS_it + EST_NF_SAI_IT.VLR_ISENTO )

	--ELSE
	--	0
	--END + (VLR_OUTROS_DESPESAS )  AS VLR_OUTROS_DESPESAS, 

	ISNULL(EST_NF_SAI_IT.VLR_ICMS_DESONERADO,0) AS VLR_ICMS_DESONERADO
FROM         
	EST_NF_SAI INNER JOIN EST_NF_SAI_IT ON 
	EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND 
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
    EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF AND
	EST_NF_SAI.DT_EMI_NF = EST_NF_SAI_IT.DT_EMI_NF 
	INNER JOIN GLB_NT_OP ON
	EST_NF_SAI_IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP 
	INNER JOIN GLB_MOV_OP ON 
	EST_NF_SAI.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP 
	INNER JOIN EST_NF_SERIE ON
	EST_NF_SAI.CD_EMP = EST_NF_SERIE.CD_EMP AND
    EST_NF_SAI.CD_NF_SERIE = EST_NF_SERIE.CD_NF_SERIE 
	INNER JOIN EST_TRIB ON
	EST_NF_SAI_IT.CD_TRIB = EST_TRIB.CD_TRIB 
WHERE
	EST_NF_SAI.STS_NF =0

	
