
/****** Object:  Trigger [TR_PDV_VD_IT_VLR_DESC_TOT_RAT]    Script Date: 08/19/2013 11:12:03 ******/
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_PDV_VD_IT_VLR_DESC_TOT_RAT]'))
DROP TRIGGER [dbo].[TR_PDV_VD_IT_VLR_DESC_TOT_RAT]
GO

/****** Object:  Trigger [dbo].[TR_PDV_VD_IT_VLR_DESC_TOT_RAT]    Script Date: 08/19/2013 11:12:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[TR_PDV_VD_IT_VLR_DESC_TOT_RAT] ON [dbo].[PDV_VD_IT] FOR INSERT, UPDATE, DELETE AS  
    IF UPDATE(TOT_IT) OR NOT EXISTS (SELECT * FROM INSERTED)  
      begin  
        
			--    UPDATE i 
			--    SET 
			--			VLR_DESC_TOT_RAT =  i.TOT_IT * (v.VLR_DESC/(SELECT SUM(TOT_IT) 
			--											FROM PDV_VD_IT  
			--											WHERE 
			--												CD_EMP=v.CD_EMP AND 
			--												CD_FILIAL=v.CD_FILIAL AND 
			--												CD_VD=v.CD_VD AND ST_IT = 1   ))  
			--        FROM 
			--			   PDV_VD_IT i  
			--            INNER JOIN PDV_VD v ON   
			--            i.CD_EMP=v.CD_EMP AND   
			--            i.CD_FILIAL=v.CD_FILIAL AND   
			--            i.CD_VD=v.CD_VD  
			--        WHERE   
			--v.VLR_DESC<>0  
			--and i.ST_IT = 1  
			--AND (EXISTS (SELECT * FROM INSERTED WHERE CD_EMP=v.CD_EMP AND CD_FILIAL=v.CD_FILIAL AND CD_VD=v.CD_VD)  
			--OR EXISTS (SELECT * FROM DELETED 
			--				WHERE 
			--					CD_EMP=v.CD_EMP AND CD_FILIAL=v.CD_FILIAL AND CD_VD=v.CD_VD))  
									 
			DECLARE @VLR_DESC AS MONEY
			DECLARE @VLR_RAT AS MONEY  
			select   
					I.CD_EMP,
					i.CD_FILIAL,
					i.CD_VD,
					I.CD_PROD, 
					i.CD_IT,
					i.TOT_IT,
					ROUND( i.TOT_IT * (v.VLR_DESC/(SELECT SUM(TOT_IT) 
															FROM PDV_VD_IT  
															WHERE 
																CD_EMP    = v.CD_EMP AND 
																CD_FILIAL = v.CD_FILIAL AND 
																CD_VD	  = v.CD_VD 
																AND ST_IT = 1)) ,2) VLR_RAT
				 INTO #TBL_RATEIO
			FROM 
				PDV_VD_IT i  
                INNER JOIN PDV_VD v ON   
                i.CD_EMP=v.CD_EMP AND   
                i.CD_FILIAL=v.CD_FILIAL AND   
                i.CD_VD=v.CD_VD  
            WHERE   
				v.VLR_DESC<>0  
				and i.ST_IT = 1  
				AND (EXISTS (SELECT * FROM INSERTED WHERE CD_EMP=v.CD_EMP AND CD_FILIAL=v.CD_FILIAL AND CD_VD=v.CD_VD)  
				OR EXISTS (SELECT * FROM DELETED WHERE CD_EMP=v.CD_EMP AND CD_FILIAL=v.CD_FILIAL AND CD_VD=v.CD_VD)) ;
				
			UPDATE I   
			set
				i.VLR_DESC_TOT_RAT = r.VLR_RAT  
			FROM 
				PDV_VD_IT i  
                INNER JOIN #TBL_RATEIO R ON   
                i.CD_EMP	=R.CD_EMP AND   
                i.CD_FILIAL	=R.CD_FILIAL AND   
                i.CD_VD		=R.CD_VD  AND
                I.CD_IT		=R.CD_IT AND
                I.CD_PROD	=R.CD_PROD;
				
			IF EXISTS (SELECT * FROM INSERTED)  
				BEGIN
					SET @VLR_DESC =( SELECT TOP 1 VLR_DESC 	
										FROM 
											INSERTED i  
											INNER JOIN PDV_VD v ON   
											i.CD_EMP	=v.CD_EMP AND   
											i.CD_FILIAL	=v.CD_FILIAL AND   
											i.CD_VD		=v.CD_VD);
				END
			ELSE
				BEGIN
				
					SET @VLR_DESC =( SELECT TOP 1 VLR_DESC 	
											FROM 
												DELETED i  
												INNER JOIN PDV_VD v ON   
												i.CD_EMP	=v.CD_EMP AND   
												i.CD_FILIAL	=v.CD_FILIAL AND   
												i.CD_VD		=v.CD_VD); 
				END
			
			
			
									 
			SET @VLR_RAT  = (SELECT SUM(VLR_RAT) FROM #TBL_RATEIO);
			
			IF @VLR_DESC <> @VLR_RAT
			BEGIN
				
				set @VLR_RAT = @VLR_DESC - @VLR_RAT; 
				
				UPDATE I   
				set
					i.VLR_DESC_TOT_RAT = VLR_DESC_TOT_RAT+ @VLR_RAT 
				FROM  
					PDV_VD_IT i  
					INNER JOIN (select top 1	
											CD_EMP,
											CD_FILIAL,
											CD_VD,
											CD_PROD,
											CD_IT 
										from
											#TBL_RATEIO
										order by 
											TOT_IT desc) R ON   
					i.CD_EMP	=R.CD_EMP AND   
					i.CD_FILIAL	=R.CD_FILIAL AND   
					i.CD_VD		=R.CD_VD  AND
					I.CD_IT		=R.CD_IT AND
					I.CD_PROD	=R.CD_PROD  ;
			END		 
      end

GO


