 

/****** Object:  UserDefinedFunction [dbo].[UFD_SPED_PDV_VD]    Script Date: 01/10/2013 14:49:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_SPED_PDV_VD]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_SPED_PDV_VD]
GO
 

/****** Object:  UserDefinedFunction [dbo].[UFD_SPED_PDV_VD]    Script Date: 01/10/2013 14:49:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[UFD_SPED_PDV_VD](
											  @CD_EMP		INT,
											  @FILIAL   INT,
											  @DT_INI      DATETIME,
											  @DT_FIM      DATETIME)
											  
RETURNS @UFD_RETURN TABLE(	CD_DOC       INT ,
							CD_ORIG      INT ,
							COD_MOD      Varchar(2) ,
							
							NR_DOC       VARCHAR(50) ,
							DT_EMI_NF    DATETIME ,
							 							
							VLR_NF       MONEY ,
							IND_PAGTO   INT ,
							VLR_DESC    MONEY ,
							
							VLR_MERC    MONEY ,
							CD_FILIAL  INT ,
							CGC_CPF  VARCHAR (50) ,
							
							STS_NF  INT ,
						 	POSSUI_DUPL  INT ,
							TP_MOV  INT ,
							
							TP_MOV_OP_TRANSA  INT ,
							DEST_ORIG  INT ,
							TIPO_PART VARCHAR(30) ,
							
							NUM_ITEM INT ,  
							COD_ITEM VARCHAR(30) ,  
							TIPO_ITEM INT ,  
							
							DS_PROD VARCHAR(150) ,     
							QT_IT INT , 
							VLR_ITEM MONEY ,  
							
							CST_ICMS VARCHAR(6) ,  
							CFOP VARCHAR(6) ,  
							VLR_BASE_ICMS MONEY ,  
							
							ALIQ_ICMS MONEY , 
							VLR_ICMS_IT MONEY , 
							 						    
						    CST_PIS  INT , 
							CST_COFINS  INT ,   
							VLR_BASE_PIS MONEY ,  
							
							PERC_ALIQ_PIS  MONEY ,  
							VLR_PIS MONEY ,  
							VLR_BASE_COFINS MONEY ,  
							
							PERC_ALIQ_COFINS  MONEY ,  
							VLR_COFINS MONEY , 
							VLR_BASE_ISSQN MONEY ,  
							
							VLR_ISSQN MONEY ,  
							ALIQ_ISSQN MONEY ,  
							COD_PART INT , 
							
							COD_IMP_ALMOX INT , 
							LISTA_PROD INT , 
							VALOR_ITEM MONEY ,  
							
							FLAG_OPERADOR_LOG INT , 
							QTDE_PROD INT , 
							NR_LOTE varchar(30) , 
							
							DT_VALID DATETIME , 
							DT_FABR DATETIME , 
							VLR_TABELA MONEY , 
							
							PROD_MEDICAM INT , 
							TIPO_MEDIC INT,
							NOME_PART VARCHAR(100),
							
							ENDERECO VARCHAR(100),
							BAIRRO VARCHAR (100),
							NUMERO INT ,
							
							INSC_EST VARCHAR(40),
					        COD_IBGE VARCHAR(30),
					        TP_PROD INT,
					        
					        COD_BARRA VARCHAR(50),
					        COD_NCM VARCHAR (15),
					        VLR_DESC_IT MONEY,
					        
					        PIS_NF MONEY,
					        COFINS_NF MONEY,
					        ST_IT INT)


BEGIN
DECLARE @RS_MOV TABLE(

					CD_DOC       INT ,
							CD_ORIG      INT ,
							COD_MOD      Varchar(2) ,
							
							NR_DOC       VARCHAR(50) ,
							DT_EMI_NF    DATETIME ,
							 							
							VLR_NF       MONEY ,
							IND_PAGTO   INT ,
							VLR_DESC    MONEY ,
							
							VLR_MERC    MONEY ,
							CD_FILIAL  INT ,
							CGC_CPF  VARCHAR (50) ,
							
							STS_NF  INT ,
						 	POSSUI_DUPL  INT ,
							TP_MOV  INT ,
							
							TP_MOV_OP_TRANSA  INT ,
							DEST_ORIG  INT ,
							TIPO_PART VARCHAR(30) ,
							
							NUM_ITEM INT ,  
							COD_ITEM VARCHAR(30) ,  
							TIPO_ITEM INT ,  
							
							DS_PROD VARCHAR(100) ,   -- 
							QT_IT INT , 
							VLR_ITEM MONEY ,  
							
							CST_ICMS VARCHAR(6) ,  
							CFOP VARCHAR(6) ,  
							VLR_BASE_ICMS MONEY ,  
							
							ALIQ_ICMS MONEY , 
							VLR_ICMS_IT MONEY , 
							 						    
						    CST_PIS  INT , 
							CST_COFINS  INT ,   
							VLR_BASE_PIS MONEY ,  
							
							PERC_ALIQ_PIS  MONEY ,  
							VLR_PIS MONEY ,  
							VLR_BASE_COFINS MONEY ,  
							
							PERC_ALIQ_COFINS  MONEY ,  
							VLR_COFINS MONEY , 
							VLR_BASE_ISSQN MONEY ,  
							
							VLR_ISSQN MONEY ,  
							ALIQ_ISSQN MONEY ,  
							COD_PART INT , 
							
							COD_IMP_ALMOX INT , 
							LISTA_PROD INT , 
							VALOR_ITEM MONEY ,  
							
							FLAG_OPERADOR_LOG INT , 
							QTDE_PROD INT , 
							NR_LOTE varchar(30) , 
							
							DT_VALID DATETIME , 
							DT_FABR DATETIME , 
							VLR_TABELA MONEY , 
							
							PROD_MEDICAM INT , 
							TIPO_MEDIC INT,
							NOME_PART VARCHAR(100),
							
							ENDERECO VARCHAR(100),
							BAIRRO VARCHAR (100),
							NUMERO INT ,
							
							INSC_EST VARCHAR(40),
					        COD_IBGE VARCHAR(30),
					        TP_PROD INT,
					        
					        COD_BARRA VARCHAR(50),
					        COD_NCM VARCHAR (15),
					        VLR_DESC_IT MONEY,
					        
					        PIS_NF MONEY,
					        COFINS_NF MONEY,
					        ST_IT INT)
INSERT INTO
	
	@RS_MOV	
   
SELECT   PDV_VD.CD_VD AS CD_DOC ,    --  SELECT TOP 2 * FROM PDV_VD_IT
          PDV_VD.CD_CLI AS CD_ORIG        ,
          '0' AS COD_MOD        ,
          
          NR_ECF AS NR_DOC          ,
          PDV_VD.DT_VD AS DT_EMI_NF , 
          
          PDV_VD.VLR_TOT_PROD AS   VLR_NF , 
          0          AS IND_PAGTO, 
          PDV_VD.VLR_DESC ,
           
         (TOT_IT   -   VLR_DESC_TOT_RAT ) AS VLR_MERC , 
          PDV_VD.CD_FILIAL                                                        ,
          RC_CLI.CGC_CPF ,
          
          ST_VD AS STS_NF                                                           ,
          ISNULL(RC_DEB_PDV_VD.CD_EMP,0) AS POSSUI_DUPL   ,
          0  TP_MOV ,
          
          0  TP_MOV_OP_TRANSA ,
          0 DEST_ORIG , 
          'CLI' AS  TIPO_PART,
          
          PDV_VD_IT.CD_IT AS NUM_ITEM,  
            PDV_VD_IT.CD_PROD AS COD_ITEM,  
           EST_PROD_CPL.TP_PROD AS TIPO_ITEM,  
          
          EST_PROD.DS_PROD, 
          PDV_VD_IT.QT_IT,  
          PDV_VD_IT.VLR_IT AS VLR_ITEM,
            
            PDV_VD_IT.CD_TRIB_FC AS CST_ICMS, 
           (CASE WHEN  PDV_VD_IT.CD_TRIB_FC = '010' OR  PDV_VD_IT.CD_TRIB_FC = '060' THEN '5405' ELSE '5102' END) AS CFOP,  
            CASE WHEN  PDV_VD_IT.TX_ICMS_IT > 0 THEN TOT_IT ELSE 0 END AS VLR_BASE_ICMS,
              
            PDV_VD_IT.TX_ICMS_IT, 
           ( PDV_VD_IT.TX_ICMS_IT *  PDV_VD_IT.TOT_IT) / 100 AS  VLR_ICMS_IT,
           
           ISNULL( PDV_VD_IT.CST_PIS,0)CST_PIS,
           ISNULL(  PDV_VD_IT.CST_COFINS,0)CST_COFINS,   
           ISNULL( PDV_VD_IT.VLR_BASE_PIS,0)VLR_BASE_PIS,
             
           ISNULL( PDV_VD_IT.PERC_ALIQ_PIS,0)PERC_ALIQ_PIS ,  
           ISNULL( PDV_VD_IT.VLR_PIS,0)VLR_PIS,  
           ISNULL( PDV_VD_IT.VLR_BASE_COFINS,0)VLR_BASE_COFINS,
             
           ISNULL( PDV_VD_IT.PERC_ALIQ_COFINS,0)PERC_ALIQ_COFINS ,  
           ISNULL( PDV_VD_IT.VLR_COFINS,0)VLR_COFINS, 
           CASE WHEN PDV_VD_IT.ST_IT = 1 
                AND PDV_VD.ST_VD = 0 
                AND EST_PROD.FLAG_PRODUTO_TAXA_ENTREGA = 1
                AND PDV_VD_IT.TX_ICMS_IT = 5 THEN
                ISNULL( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT , 0) 
           ELSE
				0
           END  VLR_BASE_ISSQN,
           
                
           CASE WHEN PDV_VD_IT.ST_IT = 1 
                AND PDV_VD.ST_VD = 0 
                AND EST_PROD.FLAG_PRODUTO_TAXA_ENTREGA = 1
                AND PDV_VD_IT.TX_ICMS_IT = 5 THEN
                (ISNULL( PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT , 0)) * PDV_VD_IT.TX_ICMS_IT /100 
           ELSE
				0
           END  VLR_ISSQN ,   
           CASE WHEN PDV_VD_IT.ST_IT = 1 
                AND PDV_VD.ST_VD = 0 
                AND EST_PROD.FLAG_PRODUTO_TAXA_ENTREGA = 1
                AND PDV_VD_IT.TX_ICMS_IT = 5 THEN
                PDV_VD_IT.TX_ICMS_IT 
           ELSE
				0
           END  ALIQ_ISSQN, 
           PDV_VD.CD_CLI AS COD_PART, 
           
           1 AS COD_IMP_ALMOX, 
           PDV_VD_IT.LISTA_PROD, 
           PDV_VD_IT.QT_IT *  PDV_VD_IT.VLR_IT AS VALOR_ITEM , 
            
          PRC_FILIAL.FLAG_OPERADOR_LOG, 
          PDV_VD_IT_LOTE.QTDE_PROD, 
          ISNULL(EST_PROD_CPL_LOTE.NR_LOTE,0)NR_LOTE, 
          EST_PROD_CPL_LOTE.DT_VALID, 
          EST_PROD_CPL_LOTE.DT_FABR, 
          EST_PROD_PRECO.VLR_TABELA,
           
          CASE WHEN EST_PROD.CTR_PMC = 1 THEN 1 ELSE 0 END AS PROD_MEDICAM, 
          (CASE WHEN EST_ARV_MERC_LINHA.DS_SIGLA_LINHA = 'S' THEN 0   -- siglas: E - etico,   S - similar, G - Generico |0- Similar; 1- Gen�rico; 2- �tico;
                WHEN EST_ARV_MERC_LINHA.DS_SIGLA_LINHA = 'G' THEN 1 
                WHEN EST_ARV_MERC_LINHA.DS_SIGLA_LINHA = 'E' THEN 2 
          ELSE 0 END)TIPO_MEDIC ,   
          RC_CLI.RZ_CLI AS NOME_PART,
          
          RC_CLI.END_CLI AS ENDERECO,
          RC_CLI.BAIRRO,
          ISNULL(RC_CLI.NUMERO, 0 ) NUMERO,
          
          RC_CLI.INSC_EST,
          ISNULL(GLB_MUNICIPIO.COD_MUNICIPIO_SINTEGRA,0) AS COD_IBGE ,
          EST_PROD_CPL.TP_PROD,
          
          ISNULL((SELECT TOP (1) CD_BARRA  
		  FROM EST_PROD_CD_BARRA 
		  WHERE CD_EMP = PDV_VD_IT.CD_EMP 
		  AND CD_PROD = PDV_VD_IT.CD_PROD 
		  ORDER BY CD_BARRA),'') AS COD_BARRA,
		  EST_PROD.NR_NCM  AS COD_NCM,
		  ROUND(PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT *PDV_VD_IT.TX_DESC / 100, 2) -  VLR_DESC_TOT_RAT	AS VLR_DESC_IT,
		  
		  ISNULL(V_PDV_VD_IMPOSTOS_PIS_COFINS.PIS_NF,0)PIS_NF, 
		  ISNULL(V_PDV_VD_IMPOSTOS_PIS_COFINS.COFINS_NF,0)COFINS_NF,
		  PDV_VD_IT.ST_IT 
         
          FROM     PDV_VD INNER JOIN PDV_VD_IT ON
          PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP
			AND PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL
			AND PDV_VD.CD_VD = PDV_VD_IT.CD_VD
          INNER JOIN EST_PROD_CPL  ON  
             EST_PROD_CPL.CD_EMP = PDV_VD_IT.CD_EMP  
             AND EST_PROD_CPL.CD_FILIAL = PDV_VD_IT.CD_FILIAL  
             AND EST_PROD_CPL.CD_PROD = PDV_VD_IT.CD_PROD 
              
          INNER JOIN  PRC_FILIAL ON  
             PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP    
             AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL  
        
          INNER JOIN  EST_PROD ON  
             PDV_VD_IT.CD_EMP = EST_PROD.CD_EMP  
             AND PDV_VD_IT.CD_PROD = EST_PROD.CD_PROD 
             
          INNER JOIN RC_CLI ON  PDV_VD.CD_CLI = RC_CLI.CD_CLI 
        
          LEFT JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON  
                EST_PROD.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD AND 
                EST_PROD.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP  
        
          LEFT JOIN EST_ARV_MERC_LINHA ON  
                EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP AND 
                EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA  
         
          LEFT JOIN   PDV_VD_IT_LOTE ON 
            PDV_VD_IT_LOTE.Cd_Emp = PDV_VD_IT.Cd_Emp 
            AND  PDV_VD_IT_LOTE.CD_FILIAL =PDV_VD_IT.CD_FILIAL 
            AND  PDV_VD_IT_LOTE.CD_VD =PDV_VD_IT.CD_VD 
              
        
          LEFT JOIN  EST_PROD_CPL_LOTE ON 
              PDV_VD_IT_LOTE.Cd_Emp = EST_PROD_CPL_LOTE.Cd_Emp 
            AND  PDV_VD_IT_LOTE.CD_FILIAL =EST_PROD_CPL_LOTE.CD_FILIAL 
            AND  PDV_VD_IT_LOTE.CD_LOTE =EST_PROD_CPL_LOTE.CD_LOTE 
        
          LEFT JOIN  EST_PROD_PRECO ON  
             EST_PROD_PRECO.CD_EMP =  PDV_VD_IT.CD_EMP 
             AND EST_PROD_PRECO.CD_FILIAL =  PDV_VD_IT.CD_FILIAL 
             AND EST_PROD_PRECO.CD_PROD =  PDV_VD_IT.CD_PROD  
             
          LEFT JOIN RC_DEB_PDV_VD  ON 
           RC_DEB_PDV_VD.CD_EMP =  PDV_VD_IT.CD_EMP 
            AND RC_DEB_PDV_VD.CD_FILIAL =   PDV_VD_IT.CD_FILIAL 
           AND RC_DEB_PDV_VD.CD_VD =   PDV_VD_IT.CD_VD
                     
          LEFT JOIN GLB_CID ON RC_CLI.CD_CID = GLB_CID.CD_CID  
          LEFT JOIN GLB_CID_GLB_MUNICIPIO ON GLB_CID_GLB_MUNICIPIO.CD_CID = GLB_CID.CD_CID   
          LEFT JOIN GLB_MUNICIPIO ON  GLB_MUNICIPIO.CD_MUNICIPIO = GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO
        
          LEFT JOIN V_PDV_VD_IMPOSTOS_PIS_COFINS ON   
            V_PDV_VD_IMPOSTOS_PIS_COFINS.CD_EMP = PDV_VD_IT.CD_EMP
            AND V_PDV_VD_IMPOSTOS_PIS_COFINS.CD_FILIAL = PDV_VD_IT.CD_FILIAL
            AND V_PDV_VD_IMPOSTOS_PIS_COFINS.CD_VD = PDV_VD_IT.CD_VD
                
           WHERE PDV_VD.CD_EMP =   @Cd_Emp
           AND PDV_VD.CD_FILIAL =    @Filial
           AND PDV_VD.DT_VD BETWEEN  @DT_INI AND @DT_FIM --'2012-07-01' AND '2012-07-31' 
           
    
    ORDER BY  CD_DOC ,  NUM_ITEM
    
    INSERT INTO @UFD_RETURN
	
	
	SELECT                  CD_DOC         ,
							CD_ORIG        ,
							COD_MOD         ,
							
							NR_DOC          ,
							DT_EMI_NF      ,
							 							
							VLR_NF         ,
							IND_PAGTO     ,
							VLR_DESC      ,
							
							VLR_MERC      ,
							CD_FILIAL    ,
							CGC_CPF      ,
							
							STS_NF    ,
						 	POSSUI_DUPL    ,
							TP_MOV    ,
							
							TP_MOV_OP_TRANSA    ,
							DEST_ORIG    ,
							TIPO_PART    ,
							
							NUM_ITEM   ,  
							COD_ITEM   ,  
							TIPO_ITEM   ,  
							
							DS_PROD    ,   -- 
							QT_IT   , 
							VLR_ITEM   ,  
							
							CST_ICMS   ,  
							CFOP    ,  
							VLR_BASE_ICMS   ,  
							
							ALIQ_ICMS   , 
							VLR_ICMS_IT   , 
							 						    
						    CST_PIS    , 
							CST_COFINS    ,   
							VLR_BASE_PIS   ,  
							
							PERC_ALIQ_PIS    ,  
							VLR_PIS   ,  
							VLR_BASE_COFINS   ,  
							
							PERC_ALIQ_COFINS    ,  
							VLR_COFINS   , 
							VLR_BASE_ISSQN   ,  
							
							VLR_ISSQN   ,  
							ALIQ_ISSQN   ,  
							COD_PART   , 
							
							COD_IMP_ALMOX   , 
							LISTA_PROD   , 
							VALOR_ITEM   ,  
							
							FLAG_OPERADOR_LOG   , 
							QTDE_PROD   , 
							NR_LOTE   , 
							
							DT_VALID   , 
							DT_FABR   , 
							VLR_TABELA   , 
							
							PROD_MEDICAM   , 
							TIPO_MEDIC  ,
							NOME_PART   ,
							
							ENDERECO   ,
							BAIRRO    ,
							NUMERO   ,
							
							INSC_EST   ,
					        COD_IBGE   ,
					        TP_PROD  ,
					        
					        COD_BARRA   ,
					        COD_NCM    ,
					        VLR_DESC_IT  ,
					        
					        PIS_NF  ,
					        COFINS_NF  ,
					        ST_IT  
					
					FROM         
		@RS_MOV
RETURN
END



GO


