IF EXISTS( SELECT * FROM SYS.procedures
			WHERE NAME = 'P_EST_NF_ENT_IT_PRECO_TRANSFERENCIA')
    BEGIN
        DROP PROCEDURE P_EST_NF_ENT_IT_PRECO_TRANSFERENCIA;
    END;
GO

--P_EST_NF_ENT_IT_PRECO_TRANSFERENCIA 1,1,'2019-08-18',0

CREATE PROCEDURE P_EST_NF_ENT_IT_PRECO_TRANSFERENCIA (@CD_EMP      INT 
														, @CD_CONFIG   INT 
														, @DT_PROC     DATETIME 
														, @CD_PROD_PAR INT      = 0 )
WITH ENCRYPTION
AS
     SET NOCOUNT ON;
     DECLARE @FLAG_TIPO_PRECO INT;
	 DECLARE @VLR_CUSTO_IT_ORIGEM_ULTIMA_ENTRADA TINYINT =0, @CALCULO_CREDITO_ICMS TINYINT =0;
	 
	 SET @VLR_CUSTO_IT_ORIGEM_ULTIMA_ENTRADA = ISNULL((SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_CHAVE= 'PRECO_TRANSF_VLR_CUSTO_IT_ORIGEM_ULTIMA_ENTRADA'),0)
	 SET @CALCULO_CREDITO_ICMS= ISNULL((SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_CHAVE= 'PRECO_TRANSF_CALCULO_CREDITO_ICMS'),0)

     SET @FLAG_TIPO_PRECO = 0;
     PRINT 'P_EST_NF_ENT_IT_PRECO_TRANSFERENCIA SET 1 '+CONVERT(VARCHAR, GETDATE(), 108);

     CREATE TABLE #RSPRODUTOPROC
			(
				  CD_EMP                INT
				, CD_FILIAL             INT
				, CD_CONFIG             INT
				, CD_PROD               INT
				, DS_PROD               VARCHAR(250)
				, CD_TRIB_FC            VARCHAR(4)
				, VLR_IT                MONEY
				, VLR_DESC              MONEY
				, VLR_ICMS              MONEY
				, VLR_ICMS_SUBST        MONEY
				, VLR_ICMS_RET          MONEY
				, VLR_PROD_TRANSF       MONEY
				, VLR_PROD_TRANSF_ATUAL MONEY
				, CD_NF                 INT
				, NR_NF                 INT
				, NM_FORNECEDOR         VARCHAR(250)
				, ALIQ_ICMS_ENTRADA     MONEY
				, VLR_IPI               MONEY
				, VLR_OUTRAS_DESPESAS   MONEY
				, VLR_CUSTO_IT				MONEY
			);

     CREATE TABLE #RsFiliaisProc ( CD_EMP    INT , CD_FILIAL INT );

     DECLARE @CD_FILIAL INT;
     DECLARE @CD_PROD INT;
     DECLARE @FLAG_PRECO_LIQUIDO INT;
     PRINT 'INSERT 2 '+CONVERT(VARCHAR, GETDATE(), 108);
     INSERT INTO #RsFiliaisProc
            SELECT
                   CD_EMP
                 , CD_FILIAL
            FROM
                 V_PRC_FILIAL_RELACAO_DEPOSITOS D
                 INNER JOIN GLB_CONFIG_PRECO_RESSUP C ON D.CD_CTR_DEPO = C.CD_CTR_DEPO
            WHERE C.cd_config = @cd_config;
     SELECT
            @FLAG_TIPO_PRECO = ISNULL(FLAG_TIPO_PRECO, 0)
          , @FLAG_PRECO_LIQUIDO = ISNULL(FLAG_PRECO_LIQUIDO, 0)
     FROM
          GLB_CONFIG_PRECO_RESSUP
     WHERE CD_CONFIG = @cd_config;


     DECLARE CPRODUTOSPROC CURSOR FAST_FORWARD
     FOR SELECT DISTINCT
                C.CD_FILIAL
              , I.CD_PROD
         FROM
              V_EST_NF_ENT_CAB C
              INNER JOIN EST_NF_ENT_IT I ON C.CD_EMP = I.CD_EMP
                                            AND C.CD_FILIAL = I.CD_FILIAL
                                            AND C.CD_NF = I.CD_NF
              INNER JOIN #RsFiliaisProc d ON c.CD_EMP = d.CD_EMP
                                             AND c.CD_FILIAL = d.CD_FILIAL
         WHERE C.CD_EMP = @CD_EMP
               AND C.DT_ENT = @DT_PROC
               AND C.TP_MOV = 0
               AND I.CD_PROD = CASE
                                   WHEN @CD_PROD_PAR = 0 THEN I.CD_PROD
                                   ELSE @CD_PROD_PAR
                               END;
     OPEN CPRODUTOSPROC;
     FETCH NEXT FROM CPRODUTOSPROC INTO @CD_FILIAL , @CD_PROD;



     WHILE @@FETCH_STATUS = 0
         BEGIN
             PRINT 'INSERT 3 '+CONVERT(VARCHAR, GETDATE(), 108);
             INSERT INTO #RSPRODUTOPROC ( CD_EMP
												, CD_FILIAL
												, CD_CONFIG
												, CD_PROD
												, DS_PROD
												, CD_TRIB_FC
												, VLR_IT
												, VLR_DESC
												, VLR_ICMS
												, VLR_ICMS_SUBST
												, VLR_ICMS_RET
												, VLR_PROD_TRANSF
												, VLR_PROD_TRANSF_ATUAL
												, CD_NF
												, NR_NF
												, NM_FORNECEDOR
												, ALIQ_ICMS_ENTRADA
												, VLR_IPI
												, VLR_OUTRAS_DESPESAS
												, VLR_CUSTO_IT
											)
            SELECT
                    C.CD_EMP
                    , C.CD_FILIAL
                    , @CD_CONFIG
                    , I.CD_PROD
                    , P.DS_PROD
                    , T.CD_TRIB_FC
                    , I.VLR_IT
                    , CASE
                        WHEN I.QT_IT = 0 THEN 0
                        ELSE I.VLR_DESC / I.QT_IT
                    END AS VLR_DESC
                    , CASE
                        WHEN I.QT_IT = 0 THEN 0
                        ELSE I.VLR_ICMS_IT / I.QT_IT
                    END AS VLR_ICMS_IT
                    , CASE
                        WHEN I.QT_IT = 0 THEN 0
                        ELSE I.VLR_ICMS_SUBS / I.QT_IT
                    END AS VLR_ICMS_SUBS
                    , CASE
                        WHEN I.QT_IT = 0 THEN 0
                        ELSE(ISNULL(I.VLR_ICMS_RET, 0) / I.QT_IT)
                    END AS VLR_ICMS_RET
                    , CASE
                        WHEN I.QT_IT = 0 THEN 0
						ELSE CASE
								 WHEN @CALCULO_CREDITO_ICMS = 1 THEN 
									(I.VLR_IT + (I.VLR_ICMS_SUBS /I.QT_IT) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)) --Custo
										- ((ISNULL(I.VLR_ICMS_RET, 0) + I.VLR_IPI + I.VLR_ICMS_IT)/I.QT_IT)
										+ (I.VLR_ICMS_IT/I.QT_IT)  

                                WHEN SUBS_TRIB_ANT = 1 THEN CASE
                                                                WHEN ISNULL(I.VLR_BASE_ICMS_OP, 0) > 0 THEN CASE WHEN @FLAG_TIPO_PRECO = 0 THEN --ICMS ST
                                                                                                            CASE
                                                                                                                WHEN @FLAG_PRECO_LIQUIDO = 0 THEN --Preco liquido
                                                                                                            ((I.VLR_IT - (I.VLR_DESC / I.QT_IT)) - (ISNULL(I.VLR_ICMS_RET, 0) / I.QT_IT)) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                                                                                                ELSE(I.VLR_IT - (ISNULL(I.VLR_ICMS_RET, 0) / I.QT_IT)) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                                                                                            END
                                                                                                                ELSE --Preco liquido + valor icms retido
                                                                                                                CASE
                                                                                                                    WHEN @FLAG_PRECO_LIQUIDO = 0 THEN --Preco liquido
                                                                                                                (I.VLR_IT - (I.VLR_DESC / I.QT_IT)) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                                                                                                    ELSE I.VLR_IT
                                                                                                                END
                                                                                                            END
                                                                ELSE CASE
                                                                            WHEN @FLAG_PRECO_LIQUIDO = 0 THEN --Preco liquido
                                                                        (I.VLR_IT - (I.VLR_DESC / I.QT_IT)) + (I.VLR_IPI / I.QT_IT) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                                                            ELSE I.VLR_IT + (I.VLR_IPI / I.QT_IT) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                                                        END
                                                            END
                                ELSE CASE
                                            WHEN @FLAG_TIPO_PRECO = 0 THEN --Nao ICMS ST
                                        CASE
                                            WHEN @FLAG_PRECO_LIQUIDO = 0 THEN --Preco liquido
                                        (I.VLR_IT - (I.VLR_DESC / I.QT_IT)) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                            ELSE I.VLR_IT + (I.VLR_IPI / I.QT_IT) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                        END
                                            ELSE --Preco liquido + valor substituicao
                                            CASE
                                                WHEN @FLAG_PRECO_LIQUIDO = 0 THEN --Preco liquido
                                            (I.VLR_IT - (I.VLR_DESC / I.QT_IT)) + (I.VLR_ICMS_SUBS / I.QT_IT) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                                ELSE I.VLR_IT + (I.VLR_ICMS_SUBS / I.QT_IT) + (I.VLR_IPI / I.QT_IT) + (I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                                            END
                                        END
                            END
                    END
                    , 0
                    , I.CD_NF
                    , C.NR_NF
                    , C.NM_ORIG
                    , CASE
                        WHEN ISNULL(I.PERC_ICMS_OP, 0) > 0 THEN I.PERC_ICMS_OP
                        WHEN ISNULL(I.PERC_ICMS_RET, 0) > 0 THEN I.PERC_ICMS_RET
                        ELSE I.ALIQ_ICMS
                    END AS ALIQ_ICMS_ENTRADA
                    , CASE
                        WHEN I.QT_IT = 0 THEN 0
                        ELSE(I.VLR_IPI / I.QT_IT)
                    END AS VLR_IPI
                    , CASE
                        WHEN I.QT_IT = 0 THEN 0
                        ELSE(I.VLR_OUTRAS_DESPESAS / I.QT_IT)
                    END AS VLR_OUTRAS_DESPESAS
					,CASE WHEN @VLR_CUSTO_IT_ORIGEM_ULTIMA_ENTRADA  = 1 THEN I.VLR_PRC_CUSTO
						ELSE  dbo.F_VLR_CUSTO_EST_FECHA_PROC_MOV(i.CD_EMP,I.CD_FILIAL,I.CD_PROD,GETDATE(),0) END VLR_PRC_CUSTO
						
            FROM
                    V_EST_NF_ENT_CAB C
                    INNER JOIN EST_NF_ENT_IT I ON C.CD_EMP = I.CD_EMP
                                                AND C.CD_FILIAL = I.CD_FILIAL
                                                AND C.CD_NF = I.CD_NF
                    INNER JOIN EST_PROD P ON I.CD_EMP = P.CD_EMP
                                            AND I.CD_PROD = P.CD_PROD
                    INNER JOIN EST_TRIB T ON I.CD_TRIB = T.CD_TRIB
            WHERE C.CD_EMP = @CD_EMP
                    AND C.CD_FILIAL = @CD_FILIAL
                    AND C.DT_ENT = @DT_PROC
                    AND C.TP_MOV = 0
                    AND I.CD_PROD = @CD_PROD
                    AND C.CD_NF = ( SELECT MAX(C.CD_NF)
										FROM
											 V_EST_NF_ENT_CAB C
											 INNER JOIN EST_NF_ENT_IT I ON C.CD_EMP = I.CD_EMP
																		   AND C.CD_FILIAL = I.CD_FILIAL
																		   AND C.CD_NF = I.CD_NF
										WHERE C.CD_EMP = @CD_EMP
											  AND C.CD_FILIAL = @CD_FILIAL
											  AND C.DT_ENT = @DT_PROC
											  AND C.TP_MOV = 0
											  AND I.CD_PROD = @CD_PROD
									);


             FETCH NEXT FROM CPRODUTOSPROC INTO @CD_FILIAL , @CD_PROD;

         END;
     CLOSE CPRODUTOSPROC;
     DEALLOCATE CPRODUTOSPROC;


     CREATE UNIQUE CLUSTERED INDEX #ix_#RSPRODUTOPROC ON #RSPRODUTOPROC(CD_PROD, CD_FILIAL, CD_EMP);
     SELECT
            P.CD_EMP
          , P.CD_FILIAL
          , P.CD_CONFIG
          , P.CD_PROD
          , p.DS_PROD
          , P.CD_TRIB_FC
          , P.VLR_IT
          , P.VLR_DESC
          , P.VLR_ICMS
          , P.VLR_ICMS_SUBST
          , P.VLR_ICMS_RET
          , P.VLR_PROD_TRANSF
          , ISNULL(G.VLR_TRANSF, 0) AS VLR_PROD_TRANSF_ATUAL
          , P.CD_NF
          , P.NR_NF
          , P.NM_FORNECEDOR
          , EST_PROD.DS_PROD
          , p.ALIQ_ICMS_ENTRADA
          , p.VLR_IPI
          , p.VLR_OUTRAS_DESPESAS
		  , P.VLR_CUSTO_IT
     FROM
          #RSPRODUTOPROC p
          LEFT OUTER JOIN GLB_CONFIG_PRECO_RESSUP_EST_PROD g ON p.CD_EMP = g.CD_EMP
                                                                AND p.CD_PROD = g.CD_PROD
                                                                AND p.CD_CONFIG = g.CD_CONFIG
          INNER JOIN EST_PROD ON p.CD_EMP = EST_PROD.CD_EMP
                                 AND p.CD_PROD = EST_PROD.CD_PROD;

     DROP TABLE #RSPRODUTOPROC;
     PRINT 'FINALIZADA!';