IF EXISTS( SELECT 1 FROM SYS.VIEWS WHERE  OBJECT_ID = OBJECT_ID(N'[DBO].[V_EST_NF_ENT_DANFE_PROVISORIO]') )
DROP VIEW [DBO].[V_EST_NF_ENT_DANFE_PROVISORIO];
GO
CREATE VIEW [dbo].[V_EST_NF_ENT_DANFE_PROVISORIO] 
AS 
  
  SELECT E.CD_CTR, 
         E.CD_EMP, 
         E.CD_FILIAL, 
         ( CASE 
             WHEN COALESCE(EST_NF_ENT_DANFE_PROVISORIO_FILIAIS.CD_FILIAL_ORIG, 0) > 0 THEN( CASE 
                                                                                              WHEN COALESCE(FLAG_GRP_ECON, 0) = 0 THEN E.CD_FORN
                                                                                              ELSE (SELECT CD_FILIAL_ORIG
                                                                                                    FROM   EST_NF_ENT_DANFE_PROVISORIO_FILIAIS
                                                                                                    WHERE  CD_CTR = E.CD_CTR)
                                                                                            END ) 
             ELSE E.CD_FORN 
           END )                                                   CD_FORN, 
         E.NR_NF, 
         E.VLR_NF, 
         E.DT_EMIS_NF, 
         E.DT_CAD, 
         E.CD_PED_FORN, 
         E.NR_AUTORIZADOR, 
         E.VLR_BASE_ICMS                                           AS VLR_BASE_ICMS_CAB, 
         E.VLR_ICMS                                                AS VLR_ICMS_CAB, 
         E.VLR_BASE_SUBS                                           AS VLR_BASE_SUBS_CAB, 
         E.VLR_ICMS_SUBS                                           AS VLR_ICMS_SUBS_CAB, 
         E.VLR_IPI                                                 AS VLR_IPI_CAB, 
         E.VLR_DESCONTO                                            AS VLR_DESCONTO_CAB, 
         E.VLR_OUTRAS_DESP                                         AS VLR_OUTRAS_DESP_CAB, 
         E.VLR_SEGURO                                              AS VLR_SEGURO_CAB, 
         E.VLR_PROD_TOT                                            AS VLR_PROD_TOT, 
         E.VLR_FRETE                                               AS VLR_FRETE_CAB, 
         E.VLR_PIS                                                 AS VLR_PIS_CAB, 
         E.VLR_COFINS                                              AS VLR_COFINS_CAB, 
         E.SERIE, 
         I.CD_PROD, 
         I.DS_PROD, 
         I.NR_LOTE, 
         I.DT_VALID, 
         I.DT_FABRIC, 
         I.NCM, 
         I.QTDE_PROD, 
         I.UNID, 
         I.VLR_PROD_UNIT, 
         COALESCE(I.QTDE_DEVOLUCAO, 0)                             AS QTDE_DEVOLUCAO, 
         I.APRES_QT_CP, 
         I.PERC_PROD_ICMS, 
         ( I.VLR_PROD_UNIT * I.QTDE_PROD )                         AS VLR_PROD_TOTAL, 
         I.VLR_PROD_TOTAL                                          AS VLR_PROD_TOTAL_DANFE, 
         I.VLR_PROD_PMC, 
         CASE 
           WHEN I.VLR_PROD_DESC > 0 THEN 
             CASE 
               WHEN I.PERC_PROD_DESC > 0 THEN I.PERC_PROD_DESC 
               ELSE( VLR_PROD_DESC / VLR_PROD_TOTAL ) * 100 
             END 
           ELSE 0 
         END                                                       AS PERC_PROD_DESC, 
         I.VLR_PROD_BASE_ICMS, 
         COALESCE(I.VLR_PROD_BASE_ST, 0)                           AS VLR_PROD_BASE_ST, 
         COALESCE(I.VLR_PROD_ICMS_ST, 0)                           AS VLR_PROD_ICMS_ST, 
         I.VLR_PROD_ICMS, 
         I.CD_BARRA, 
         I.VLR_PROD_IPI, 
         I.PERC_PROD_IPI, 
         I.CFOP, 
         I.PERC_PROD_IVA_ST, 
         I.PERC_PROD_ICMS_ST, 
         I.VLR_PROD_BASE_IPI, 
         I.CD_CTR_IT, 
         CASE 
           WHEN COALESCE(F.FLAG_FORN_MICRO_EMPRESA, 0) = 1 THEN I.CD_SIT_OPERA_SIMPLES_NACIO 
           ELSE I.CD_TRIB 
         END                                                       AS CD_TRIB, 
         I.VLR_PROD_DESC, 
         COALESCE(I.PERC_PROD_RED_BASE_ST, 0)                      AS PERC_PROD_RED_BASE_ST, 
         COALESCE(I.PERC_PROD_RED_BASE_ICMS, 0)                    AS PERC_PROD_RED_BASE_ICMS, 
         COALESCE(I.VLR_PROD_FRETE, 0)                             AS VLR_PROD_FRETE, 
         COALESCE(I.VLR_BASE_PIS, 0)                               AS VLR_BASE_PIS, 
         COALESCE(I.VLR_PIS, 0)                                    AS VLR_PIS, 
         COALESCE(I.PERC_ALIQ_PIS, 0)                              AS PERC_ALIQ_PIS, 
         COALESCE(I.VLR_BASE_COFINS, 0)                            AS VLR_BASE_COFINS, 
         COALESCE(I.VLR_COFINS, 0)                                 AS VLR_COFINS, 
         COALESCE(I.PERC_ALIQ_COFINS, 0)                           AS PERC_ALIQ_COFINS, 
         COALESCE(I.VLR_BASE_ICMS_RET, 0)                          AS VLR_BASE_ICMS_RET, 
         COALESCE(I.VLR_ICMS_RET, 0)                               AS VLR_ICMS_RET, 
         COALESCE(I.CD_SIT_OPERA_SIMPLES_NACIO, 0)                 AS CD_SIT_OPERA_SIMPLES_NACIO, 
         COALESCE(I.MODALI_BASE_ICMS_SUBST, 0)                     AS MODALI_BASE_ICMS_SUBST, 
         COALESCE(I.MODALI_BASE_ICMS, 0)                           AS MODALI_BASE_ICMS, 
         COALESCE(I.VLR_CRED_BASE_SN, 0)                           AS VLR_CRED_BASE_SN, 
         COALESCE(I.VLR_CRED_ICMS_SN, 0)                           AS VLR_CRED_ICMS_SN, 
         COALESCE(I.FLAG_USAR_EMB_DANFE, 0)                        AS FLAG_USAR_EMB_DANFE, 
         ( CASE 
             WHEN COALESCE(EST_NF_ENT_DANFE_PROVISORIO_FILIAIS.CD_FILIAL_ORIG, 0) > 0 THEN( CASE 
                                                                                              WHEN COALESCE(FLAG_GRP_ECON, 0) = 0 THEN RSPARGRP.VALOR
                                                                                              ELSE RSPAR.VALOR
                                                                                            END ) 
             ELSE COALESCE(E.CD_MOV_OP, 0) 
           END )                                                   AS CD_MOV_OP, 
         COALESCE(I.IND_TOTAL, 1)                                  AS IND_TOTAL, 
         COALESCE(GLB_MOV_OP.FLAG_NAO_REALIZAR_PROC_AUTOMATICO, 0) AS FLAG_NAO_REALIZAR_PROC_AUTOMATICO,
         COALESCE(PED.CD_PED, 0)                                   AS CD_PED_COMPRA, 
         CASE 
           WHEN COALESCE(PAREMP.VALOR, 0) = 0 THEN COALESCE(ORIGEM, 0) 
           ELSE COALESCE(EST_PROD.ORIGEM_PRODUTO, 0) 
         END                                                       AS ORIGEM, 
         CASE 
           WHEN COALESCE(I.VLR_DESC_FINAN, 0) = 0 THEN COALESCE(I.PERC_DESC_FINAN, 0) 
           ELSE( ( I.VLR_DESC_FINAN * 100 ) / ( ( I.VLR_PROD_TOTAL 
                                                  + COALESCE(I.VLR_PROD_ICMS_ST, 0) 
                                                  + COALESCE(I.VLR_PROD_IPI, 0) 
                                                  + COALESCE(I.VLR_PROD_FRETE, 0) ) - COALESCE(I.VLR_PROD_DESC, 0) ) )
         END                                                       AS PERC_DESC_FINAN, 
         CASE 
           WHEN COALESCE(I.VLR_DESC_FINAN, 0) = 0 THEN( ( COALESCE(I.PERC_DESC_FINAN, 0) * ( ( I.VLR_PROD_TOTAL
                                                                                               + COALESCE(I.VLR_PROD_ICMS_ST, 0)
                                                                                               + COALESCE(I.VLR_PROD_IPI, 0)
                                                                                               + COALESCE(I.VLR_PROD_FRETE, 0) ) - COALESCE(I.VLR_PROD_DESC, 0) ) ) / 100 )
           ELSE COALESCE(I.VLR_DESC_FINAN, 0) 
         END                                                       AS VLR_DESC_FINAN, 
         COALESCE(I.VLR_OUTRAS_DESPESAS, 0)                        AS VLR_OUTRAS_DESPESAS_IT, 
         PRC_FILIAL.CTR_LOTE                                       AS CTR_LOTE_FILIAL, 
         0                                                         AS FLAG_ICMS_RETIDO_CALCULADO, 
         0                                                         AS VLR_CUSTO_GERENCIAL_TRANSF, 
         FLAG_LIBERA_ENTRADA_SEM_PEDIDO, 
         EST_PROD.NR_NCM, 
         0                                                         AS VLR_CUSTO_ACERTO, 
         COALESCE(I.VLR_ICMS_DESONERADO, 0)                        AS VLR_ICMS_DESONERADO, 
         COALESCE(I.ICMS_DESONERADO_MOTIVO, 0)                     AS ICMS_DESONERADO_MOTIVO, 
         COALESCE(PAREMP.VALOR, 0)                                 AS MUDAR_ORIGEM_PARA_O_CADASTRO,
         COALESCE(FLAG_GRP_ECON, 0)                                AS FLAG_GRP_ECON, 
         CASE 
           WHEN COALESCE(EST_NF_ENT_DANFE_PROVISORIO_FILIAIS.CD_FILIAL_ORIG, 0) > 0 THEN 1 
           ELSE 0 
         END                                                       XML_TRANSF, 
         COALESCE(EST_PROD_UN_MED.TP_UN, 0)                        AS TP_UN, 
         COALESCE(EST_PROD.PESO_PROD_VD, 0)                        AS PESO_PROD_VD, 
         COALESCE(EST_PROD.PESO_PROD_VD_LIQ, 0)                    AS PESO_PROD_VD_LIQ, 
         COALESCE(I.VLR_ICMS_OP, 0)                                AS VLR_ICMS_OP, 
         COALESCE(I.PERC_DIFERIMENTO, 0)                           AS PERC_DIFERIMENTO, 
         COALESCE(I.VLR_ICMS_DIFERIMENTO, 0)                       AS VLR_ICMS_DIFERIMENTO, 
         COALESCE(E.FLAG_REPOSICAO_PBM, 0)                         AS FLAG_REPOSICAO_PBM, 
         E.CRT, 
         COALESCE(E.VLR_FECOP_ST, 0)                               AS VLR_FECOP_ST_CAB, 
         COALESCE(E.VLR_FECOP_ST_RET, 0)                           AS VLR_FECOP_ST_RET_CAB, 
         COALESCE(I.VLR_BASE_FECOP_ST, 0)                          AS VLR_BASE_FECOP_ST, 
         COALESCE(I.PERC_FECOP_ST, 0)                              AS PERC_FECOP_ST, 
         COALESCE(I.VLR_FECOP_ST, 0)                               AS VLR_FECOP_ST, 
         COALESCE(I.VLR_BASE_FECOP_ST_RET, 0)                      AS VLR_BASE_FECOP_ST_RET, 
         COALESCE(I.PERC_FECOP_ST_RET, 0)                          AS PERC_FECOP_ST_RET, 
         COALESCE(I.VLR_FECOP_ST_RET, 0)                           AS VLR_FECOP_ST_RET, 
         COALESCE(F.FLAG_NAO_ABATER_ICMS_DESONERADO, 0)            AS FLAG_NAO_ABATER_ICMS_DESONERADO,
         COALESCE(F.FLAG_ICMS_SUBST_PAGO_ANTERIOR, 0)              AS FLAG_ICMS_SUBST_PAGO_ANTERIOR,
         COALESCE(I.VLR_BASE_FECOP, 0)                             AS VLR_BASE_FECOP, 
         COALESCE(I.PERC_FECOP, 0)                                 AS PERC_FECOP, 
         COALESCE(I.VLR_FECOP, 0)                                  AS VLR_FECOP, 
         COALESCE(E.VLR_FECOP, 0)                                  AS VLR_FECOP_CAB, 
         COALESCE(I.PERC_ICMS_RET, 0)                              AS PERC_ICMS_RET, 
         COALESCE(E.FLAG_REPOSICAO_PBM, 0) 
         + COALESCE(F.FLAG_NAO_BARRAR_PBM_CRITICAS_ENTRADA, 0)     AS FLAG_NAO_BARRAR_PBM_CRITICAS_ENTRADA,
         COALESCE(PG_FORN_PAINEL_NFE_LIBERADO.CD_EMP, 0)           AS FLAG_NAO_BARRAR_PROCESSO_PAINEL_NFE,
         COALESCE(I.CD_BENEF, '')                                  AS CD_BENEF,
		 cOALESCE(I.CD_BENEF_RED, '')                                  AS CD_BENEF_RED,
         COALESCE(EST_PROD_CPL.TP_PROD, 0 )                        AS TP_PROD,
		 COALESCE(EST_PROD.PROD_LIBERADO, 0)     	               AS PROD_LIBERADO,
		 COALESCE(EST_PROD.FLAG_NAO_CREDITAR_IMPOSTO, 0)		   AS FLAG_NAO_CREDITAR_IMPOSTO,
		 COALESCE(EST_PROD.APRES_QT_CP, 0)						   AS P_APRES_QT_CP,
		 COALESCE(EST_PROD.CTR_PMC, 0)							   AS CTR_PMC,
		 COALESCE(EST_PROD.FLAG_PERMITE_RESSARCIMENTO_ICMS, 0)	   AS FLAG_PERMITE_RESSARCIMENTO_ICMS,
		 COALESCE(EST_PROD.FLAG_ORIG_PROD_NF_ENT, 0) 			   AS FLAG_ORIG_PROD_NF_ENT		,
		 COALESCE(EST_PROD.FLAG_IGNORAR_PMC_SUBST, 0) 			   AS FLAG_IGNORAR_PMC_SUBST		,
		 COALESCE(I.VLR_ICMS_SUBSTITUTO, 0)                        AS VLR_ICMS_SUBSTITUTO,
		 ISNULL(QTDE_ITENS_LOTE,0)                                 AS QTDE_ITENS_LOTE,
		 CAB_DESONERADO.VLR_ICMS_DESONERADO_CAB,
		 CASE WHEN ISNULL( E.ABATER_ICMS_DESONERADO_VALOR_NOTA_FISCAL ,0) = 0 
			  THEN
				 CASE WHEN CAB_DESONERADO.VLR_ICMS_DESONERADO_CAB = 0 THEN
					0
				 ELSE 
					CASE WHEN ( E.VLR_PROD_TOT - ( E.VLR_NF + CAB_DESONERADO.VLR_ICMS_DESONERADO_CAB)) = 0   THEN       
					   1        
					 ELSE    
						0
					 END 
				 END 
		 ELSE E.ABATER_ICMS_DESONERADO_VALOR_NOTA_FISCAL
		 END AS FLAG_VLR_ICMS_DESONERADO_DESCONTO,	 
		 CASE WHEN EST_PROD_CPL.VLR_CUSTO > 0 THEN
		 	EST_PROD_CPL.VLR_CUSTO
		 ELSE
		 	CASE WHEN PRC_FILIAL.REGIME_TRIBUTARIO = 4 THEN
		 		((I.VLR_PROD_TOTAL + COALESCE(I.VLR_PROD_ICMS_ST, 0) + COALESCE(I.VLR_PROD_IPI, 0) + COALESCE(I.VLR_PROD_FRETE, 0)) 
		 		- (COALESCE(I.VLR_PROD_DESC, 0) + COALESCE(I.VLR_PIS, 0) + COALESCE(I.VLR_COFINS, 0))) / I.QTDE_PROD
		 	ELSE
		 		((I.VLR_PROD_TOTAL + COALESCE(I.VLR_PROD_ICMS_ST, 0) + COALESCE(I.VLR_PROD_IPI, 0) + COALESCE(I.VLR_PROD_FRETE, 0)) - COALESCE(I.VLR_PROD_DESC, 0)) / I.QTDE_PROD
		 	END
         END AS VLR_PRC_CUSTO,
		 ISNULL(I.ABATER_ICMS_DESONERADO_ITEM, 0) AS ABATER_ICMS_DESONERADO_ITEM
		 
  FROM   EST_NF_ENT_DANFE_PROVISORIO E 
         INNER JOIN PG_FORN F 
                 ON E.CD_EMP = F.CD_EMP 
                    AND E.CD_FORN = F.CD_FORN 
         INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_ITENS I 
                 ON E.CD_EMP = I.CD_EMP 
                    AND E.CD_CTR = I.CD_CTR 
         LEFT OUTER JOIN GLB_MOV_OP 
                      ON E.CD_MOV_OP = GLB_MOV_OP.CD_MOV_OP 
         LEFT OUTER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP PED 
                      ON E.CD_CTR = PED.CD_CTR 
         INNER JOIN PRC_FILIAL 
                 ON E.CD_EMP = PRC_FILIAL.CD_EMP 
                    AND E.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
         INNER JOIN EST_PROD 
                 ON I.CD_EMP = EST_PROD.CD_EMP 
                    AND I.CD_PROD = EST_PROD.CD_PROD 
         LEFT JOIN EST_PROD_CPL
                 ON I.CD_EMP = EST_PROD_CPL.CD_EMP 
                    AND E.CD_EMP = EST_PROD_CPL.CD_EMP
                    AND E.CD_FILIAL = EST_PROD_CPL.CD_FILIAL
                    AND I.CD_PROD = EST_PROD_CPL.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 PG_FORN_PAINEL_NFE_LIBERADO 
                      ON E.CD_EMP = PG_FORN_PAINEL_NFE_LIBERADO.CD_EMP 
                         AND E.CD_FORN = PG_FORN_PAINEL_NFE_LIBERADO.CD_FORN 
                         AND 0 = PG_FORN_PAINEL_NFE_LIBERADO.STS_FORN_LIB 
         LEFT OUTER JOIN (SELECT CD_EMP, 
                                 VALOR 
                          FROM   PRC_EMP_CONFIG 
                          WHERE  CD_EMP = 1 
                                 AND CD_CHAVE = 'MUDAR_ORIGEM_PROD_IMPORTADO_ENTRADA_NOTA_CADASTRO') PAREMP
                      ON E.CD_EMP = PAREMP.CD_EMP 
         LEFT OUTER JOIN EST_NF_ENT_DANFE_PROVISORIO_FILIAIS 
                      ON E.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_FILIAIS.CD_CTR 
         LEFT OUTER JOIN PRC_FILIAL ENTFILIAL 
                      ON E.CD_EMP = ENTFILIAL.CD_EMP 
                         AND EST_NF_ENT_DANFE_PROVISORIO_FILIAIS.CD_FILIAL_ORIG = ENTFILIAL.CD_FILIAL
         LEFT OUTER JOIN (SELECT CD_EMP, 
                                 CD_FILIAL, 
                                 VALOR 
                          FROM   PRC_FILIAL_CONFIG 
                          WHERE  CD_CHAVE = 'CD_MOV_OP_TRANSF_FILIAIS_ENT') RSPAR 
                      ON E.CD_EMP = RSPAR.CD_EMP 
                         AND E.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_ENT') RSPARGRP 
                      ON E.CD_EMP = RSPARGRP.CD_EMP 
                         AND E.CD_FILIAL = RSPARGRP.CD_FILIAL
						  LEFT OUTER JOIN (SELECT CD_CTR_DANFE CD_CTR , 
                                 CD_EMP, 
                                 SUM(CD_EMP) QTDE_ITENS_LOTE 
                          FROM   EST_NF_ENT_DANFE_PROVISORIO_ITENS_lote 
                           GROUP BY CD_CTR_DANFE,CD_EMP  ) ITENS_LOTE 
                      ON E.CD_EMP = ITENS_LOTE.CD_EMP 
                         AND E.CD_CTR = ITENS_LOTE.CD_CTR
				     LEFT OUTER JOIN (SELECT  CD_CTR , 
                                 CD_EMP, 
                                 SUM(ISNULL(VLR_ICMS_DESONERADO,0)) VLR_ICMS_DESONERADO_CAB 
                          FROM   EST_NF_ENT_DANFE_PROVISORIO_ITENS  
                           GROUP BY CD_CTR,CD_EMP  ) CAB_DESONERADO 
                      ON E.CD_EMP = CAB_DESONERADO.CD_EMP 
                         AND E.CD_CTR = CAB_DESONERADO.CD_CTR			 
						 ;
