ALTER TABLE Trn_MudContratos ADD NomContacto VARCHAR(150),TelsContacto VARCHAR(30),emlContacto VARCHAR(150)
GO
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FTGTRFMAI','MAICAT','BAS',30,'Tipos de Responsabilidad Fiscal','FRMTBL','SSSSSSSSSSSSS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FTGTRFTRA','TRACAT','BAS',27,'Tipos de Responsabilidad Fiscal','FRMTBL','SSSSSSSSSSSSS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FTGTRFBUS','BUSCAT','BAS',34,'Tipos de Responsabilidad Fiscal','FRMTBL','S',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMRF','MAICAT','GEN',11,'Clientes/Responsabilidad Fiscal','FRMTERRF','SSSSSSSSSSSSS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMRFTRA','TRACAT','GEN',18,'Clientes/Responsabilidad Fiscal','FRMTERRF','SSSSSSSSSSSSS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMRFBUS','BUSCAT','GEN',25,'Clientes/Responsabilidad Fiscal','FRMTERRF','S',0,'')
GO
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMDREM','EIP','Permitir Editar Items de Pedidos')
GO
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMTER','FIS','Agregar o Quitar Responsabilidades Fiscales')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMTERTRA','FIS','Agregar o Quitar Responsabilidades Fiscales')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMTERBUS','FIS','Agregar o Quitar Responsabilidades Fiscales')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelTiposRespFis]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelTiposRespFis]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudContratos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTiposRespFis]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTiposRespFis]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudContratos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudContratos_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudContratos_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudContratosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudContratosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudContratosRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudContratosRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposRespFis]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposRespFis]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMudContratos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMudContratos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTiposRespFis]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTiposRespFis]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudContratos_Cr] @pmTipDoc VARCHAR(3),@pmContratoIni INT,@pmContratoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT C.TipDoc,TipoDoc,C.NumContrato,C.IdCia AS CdCia,Compania,C.Fecha,C.FechaInicio,C.FechaFinal,C.IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia
,C.IdBodega,B.Bodega,CdBodega2,BA.Bodega AS BodegaAdic,C.VrServicio,C.VrImpuesto,C.VrDeclarado,C.VrSeguro,C.TipOrden,C.NumOrden,C.IdCiaOrden,C.FechaRetiro,C.NomContacto,C.TelsContacto,C.emlContacto
,C.Anulado,C.FecDev,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TarifSeguro,D.TipoRubro,D.CdCuenta,D.CodTarIva,D.CodTarSeg
,C.TimeSys AS FechaCrea,C.FecUpdate,C.IdCiaCrea,C.OrigenAdd,C.IdUsuario AS CdUsuario,Usuario
 FROM Trn_MudContratos AS C INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 INNER JOIN Trn_MudContConc AS D ON C.TipDoc=D.TipDoc AND C.NumContrato=D.NumContrato AND C.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 INNER JOIN AlmBodegas AS B ON C.IdBodega=B.IdBodega 
 LEFT JOIN AlmBodegas AS BA ON C.CdBodega2=BA.IdBodega LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia 
 WHERE C.TipDoc=@pmTipDoc AND C.NumContrato BETWEEN @pmContratoIni AND @pmContratoFin AND C.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudContratos] @pmTipDoc VARCHAR(3),@pmNumContrato INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaInicio SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdBodega VARCHAR(4),@pmCdBodega2 VARCHAR(4),@pmVrServicio MONEY,@pmVrImpuesto MONEY,@pmVrDeclarado MONEY
,@pmVrSeguro MONEY,@pmTipOrden VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrden CHAR(2),@pmFechaRetiro SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(150),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_MudContratos (TipDoc,NumContrato,IdCia,Fecha,FechaInicio,FechaFinal,IdCliente,IdAgencia,IdBodega,CdBodega2,VrServicio,VrImpuesto,VrDeclarado,VrSeguro,TipOrden,NumOrden,IdCiaOrden,FechaRetiro,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,NomContacto,TelsContacto,emlContacto)
 VALUES (@pmTipDoc,@pmNumContrato,@pmIdCia,@pmFecha,@pmFechaInicio,@pmFechaFinal,@pmIdCliente,@pmIdAgencia,@pmIdBodega,@pmCdBodega2,@pmVrServicio,@pmVrImpuesto,@pmVrDeclarado,@pmVrSeguro,@pmTipOrden,@pmNumOrden,@pmIdCiaOrden,@pmFechaRetiro,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNomContacto,@pmTelsContacto,@pmemlContacto)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMudContratos] @pmTipDoc VARCHAR(3),@pmNumContrato INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaInicio SMALLDATETIME,@pmFechaFinal SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdBodega VARCHAR(4),@pmCdBodega2 VARCHAR(4),@pmVrServicio MONEY,@pmVrImpuesto MONEY,@pmVrDeclarado MONEY
,@pmVrSeguro MONEY,@pmTipOrden VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrden CHAR(2),@pmFechaRetiro SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(150),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_MudContratos SET Fecha=@pmFecha,FechaInicio=@pmFechaInicio,FechaFinal=@pmFechaFinal,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdBodega=@pmIdBodega,CdBodega2=@pmCdBodega2,VrServicio=@pmVrServicio,VrImpuesto=@pmVrImpuesto,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,TipOrden=@pmTipOrden,NumOrden=@pmNumOrden,IdCiaOrden=@pmIdCiaOrden
,FechaRetiro=@pmFechaRetiro,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,NomContacto=@pmNomContacto,TelsContacto=@pmTelsContacto,emlContacto=@pmemlContacto,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumContrato=@pmNumContrato AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudContratos] @pmTipDoc VARCHAR(3),@pmNumContrato INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumContrato,IdCia,Fecha,FechaInicio,FechaFinal,IdCliente,IdAgencia,IdBodega,CdBodega2,VrServicio,VrImpuesto,VrDeclarado,VrSeguro,TipOrden,NumOrden,IdCiaOrden,FechaRetiro,NomContacto,TelsContacto,emlContacto
,Anulado,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_MudContratos
 WHERE TipDoc=@pmTipDoc AND NumContrato=@pmNumContrato AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudContratosLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT C.NumContrato,C.IdCia AS CdCia,Compania,C.Fecha,C.FechaInicio,C.FechaFinal,C.IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia
,C.IdBodega,B.Bodega,CdBodega2,BA.Bodega AS BodegaAdic,C.VrServicio,C.VrImpuesto,C.VrDeclarado,C.VrSeguro,C.TipOrden,C.NumOrden,C.IdCiaOrden,C.FechaRetiro,C.NomContacto,C.TelsContacto,C.emlContacto
,C.Anulado,C.FecDev,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,C.TimeSys AS FechaCrea,C.FecUpdate,C.IdCiaCrea,C.OrigenAdd,C.IdUsuario AS CdUsuario,Usuario
 FROM Trn_MudContratos AS C INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN AlmBodegas AS B ON C.IdBodega=B.IdBodega 
 LEFT JOIN AlmBodegas AS BA ON C.CdBodega2=BA.IdBodega
 LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia 
 WHERE C.TipDoc=@pmTipDoc AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudContratosRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT C.NumContrato,C.IdCia AS CdCia,Compania,C.Fecha,C.FechaInicio,C.FechaFinal,C.IdCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,Agencia
,C.IdBodega,B.Bodega,CdBodega2,BA.Bodega AS BodegaAdic,C.VrServicio,C.VrImpuesto,C.VrDeclarado,C.VrSeguro,C.TipOrden,C.NumOrden,C.IdCiaOrden
,O.FecDespacho AS OrdFecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen
,O.IdDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino
,O.IdVend,VN.RazonSocial AS NomVendedor,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipoRuta,O.EstadoBod,C.NomContacto,C.TelsContacto,C.emlContacto
--detalle
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TarifSeguro,D.TipoRubro,D.CdCuenta,D.CodTarIva,D.CodTarSeg
,C.Anulado,C.FecDev,C.Observacion AS Observ,C.IdEstado AS CdEstado,Estado,C.FechaRetiro,C.TimeSys AS FechaCrea,C.FecUpdate,C.IdCiaCrea,C.OrigenAdd,C.IdUsuario AS CdUsuario,Usuario
--datos del cliente
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupo,GrupoClie,CLI.IdPlazo AS CdPlazo,Plazo
,ISNULL(FC.FacNumero,0) AS FacNumero,ISNULL(FC.FacTotal,0) AS FacVrTotal
 FROM Trn_MudContratos AS C INNER JOIN Companias AS CN ON C.IdCia=CN.IdCia INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN TercCliente AS CLI ON C.IdCliente=CLI.IdClie
 INNER JOIN EstadoDoc AS ED ON C.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Trn_MudContConc AS D ON C.TipDoc=D.TipDoc AND C.NumContrato=D.NumContrato AND C.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto INNER JOIN AlmBodegas AS B ON C.IdBodega=B.IdBodega
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 LEFT JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo LEFT JOIN Plazos AS PZ ON CLI.IdPlazo=PZ.IdPlazo
 LEFT JOIN AlmBodegas AS BA ON C.CdBodega2=BA.IdBodega LEFT JOIN Agencias AS A ON C.IdAgencia=A.IdAgencia 
 LEFT JOIN Trn_MudOrdenServ AS O ON C.TipOrden=O.TipDoc AND C.NumOrden=O.NumOrden AND C.IdCiaOrden=O.IdCia
 LEFT JOIN Terceros AS VN ON O.IdVend=VN.IdTercero LEFT JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep LEFT JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 LEFT JOIN (SELECT TipDoc,NumContrato,IdCia,COUNT(Item) AS FacItems,MAX(NumFactura) AS FacNumero,SUM(ValorTotal) AS FacTotal FROM Trn_MudContFact
 WHERE Anulado=0 GROUP BY TipDoc,NumContrato,IdCia) AS FC ON C.TipDoc=FC.TipDoc AND C.NumContrato=FC.NumContrato AND C.IdCia=FC.IdCia
 WHERE C.TipDoc=@pmTipDoc AND C.FechaInicio BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposRespFis] @pmIdResFis VARCHAR(10)
 AS 
 IF @pmIdResFis IS NULL
  SELECT IdResFis,NomResFiscal
  FROM TiposRespFis
  WHERE Inactivo=0
 ELSE
  SELECT IdResFis,NomResFiscal,Inactivo
  FROM TiposRespFis
  WHERE IdResFis=@pmIdResFis
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelTiposRespFis] @pmIdResFis VARCHAR(10)
 AS 
 DELETE FROM TiposRespFis WHERE IdResFis=@pmIdResFis
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTiposRespFis] @pmIdResFis VARCHAR(10),@pmNomResFiscal VARCHAR(250),@pmInactivo BIT
 AS 
 INSERT INTO TiposRespFis (IdResFis,NomResFiscal,Inactivo)
 VALUES (@pmIdResFis,@pmNomResFiscal,@pmInactivo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTiposRespFis] @pmIdResFis VARCHAR(10),@pmNomResFiscal VARCHAR(250),@pmInactivo BIT
 AS 
 UPDATE TiposRespFis SET NomResFiscal=@pmNomResFiscal,Inactivo=@pmInactivo
 WHERE IdResFis=@pmIdResFis
GO