ALTER TABLE Trn_Vencimientos ADD FecPlazoCal SMALLDATETIME 
GO
ALTER TABLE tm_CxCobrar ADD tmFecPlazo SMALLDATETIME 
GO
UPDATE Trn_Vencimientos SET FecPlazoCal=FechaVence
GO
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMVEHMAI','MAICAT','TRA',18,'Catálogo de Vehículos','FRMVEH','NNSNNNNNNNNNNNS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMVTP','MAIPRO','TRA',2,'Traspasos de Vehículos','FRMVTP','NNSSNNNNNNNNNNS',0,'')
INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMCALMAI','MAICAT','BAS',24,'Calendarios','FRMCAL','SSSSSSSSSSSSS',0,'')
GO
INSERT INTO Sys_Roles  (IdObj,IdRole,Funcion)
VALUES ('FRMVTP','FEC','Fecha de traspaso Abierta')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','COM','Modificar datos de compra')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','CON','Modificar datos del contrato')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','EST','Modificar Estado del Vehículo')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','HAB','Habilitar Vehículos Inactivos')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','MIC','Modificar información de capacidades y carrocería')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','MIT','Modificar Información Trascendental')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','POL','Modificar información de pólizas y vencimientos')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','RET','Permitir retirar vehículos')
INSERT INTO Sys_Roles (IdObj,IdRole,Funcion)
 VALUES ('FRMVEHMAI','UBI','Permitir cambio de ubicación ')
GO 
UPDATE Sys_ObjetosApp SET Permisos='NNSSSNNNNNNNN' WHERE IdObj='FRMVHC'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_CxCobrar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_CxCobrar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_CxCobrar_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_CxCobrar_Sel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsVencimientos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsVencimientos]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVencimientos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryVencimientos]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVencimientos_Crd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryVencimientos_Crd]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVencimientosDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryVencimientosDoc]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_CxCobrar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_CxCobrar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVencAbonosLrc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryVencAbonosLrc]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_CxCobrar @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmFecVence SMALLDATETIME,@pmtmVrFactura MONEY,@pmtmTarifDcto DECIMAL(14,4)
,@pmtmTipoDcto CHAR(1),@pmtmTipoBase VARCHAR(10),@pmtmFecLmtDcto SMALLDATETIME,@pmtmReferencia VARCHAR(50),@pmtmDetalle VARCHAR(150)
,@pmtmTipRef VARCHAR(3),@pmtmDocRef INT,@pmtmIdCiaRef CHAR(2),@pmtmTotAbono MONEY,@pmtmFecPlazo SMALLDATETIME
 AS 
 INSERT INTO tm_CxCobrar (tmNumero,tmItem,tmFecVence,tmVrFactura,tmTarifDcto,tmTipoDcto,tmTipoBase,tmFecLmtDcto,tmReferencia,tmDetalle,tmTipRef,tmDocRef,tmIdCiaRef,tmTotAbono,tmFecPlazo)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmFecVence,@pmtmVrFactura,@pmtmTarifDcto,@pmtmTipoDcto,@pmtmTipoBase,@pmtmFecLmtDcto,@pmtmReferencia
,@pmtmDetalle,@pmtmTipRef,@pmtmDocRef,@pmtmIdCiaRef,@pmtmTotAbono,@pmtmFecPlazo)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInstm_CxCobrar_Sel @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_CxCobrar (tmNumero,tmItem,tmFecVence,tmVrFactura,tmTarifDcto,tmTipoDcto,tmTipoBase,tmFecLmtDcto,tmReferencia,tmDetalle,tmTipRef,tmDocRef,tmIdCiaRef,tmTotAbono,tmFecPlazo)
 SELECT @pmtmNumero,Item,FechaVence,VrFactura,TarifDcto,TipoDcto,TipoBase,FecLmtDcto,Referencia,Detalle,TipRef,DocRef,IdCiaRef,0,FecPlazoCal
 FROM Trn_Vencimientos 
 WHERE TipDoc=@pmTipDoc  AND Factura=@pmFactura  AND  IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsVencimientos @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT,@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmVrFactura MONEY,@pmVrAbonado MONEY
,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmComision DECIMAL(14,4),@pmTarifDcto DECIMAL(14,4),@pmTipoDcto CHAR(1),@pmTipoBase VARCHAR(10),@pmFecLmtDcto SMALLDATETIME
,@pmIdConcepto VARCHAR(4),@pmReferencia VARCHAR(50),@pmDetalle VARCHAR(150),@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmFecUltPago SMALLDATETIME,@pmNumNota INT,@pmIdCiaNot CHAR(2)
,@pmTarifInt DECIMAL(14,4),@pmFecLiqMora SMALLDATETIME,@pmFecPlazoCal SMALLDATETIME
 AS 
 INSERT INTO Trn_Vencimientos (TipDoc,Factura,IdCia,Item,Fecha,FechaVence,VrFactura,VrAbonado,IdCliente,IdAgencia,IdVend,Comision,TarifDcto,TipoDcto,TipoBase,FecLmtDcto,IdConcepto,Referencia,Detalle
,TipRef,DocRef,IdCiaRef,FecUltPago,NumNota,IdCiaNot,TarifInt,FecLiqMora,FecPlazoCal)
 VALUES (@pmTipDoc,@pmFactura,@pmIdCia,@pmItem,@pmFecha,@pmFechaVence,@pmVrFactura,@pmVrAbonado,@pmIdCliente,@pmIdAgencia,@pmIdVend,@pmComision,@pmTarifDcto,@pmTipoDcto
,@pmTipoBase,@pmFecLmtDcto,@pmIdConcepto,@pmReferencia,@pmDetalle,@pmTipRef,@pmDocRef,@pmIdCiaRef,@pmFecUltPago,@pmNumNota,@pmIdCiaNot,@pmTarifInt,@pmFecLiqMora,@pmFecPlazoCal)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVencimientos @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2),@pmItem INT
 AS 
 SELECT TipDoc,Factura,IdCia,Item,Fecha,FechaVence,VrFactura,VrAbonado,IdCliente,IdAgencia,IdVend,Comision,TarifDcto,TipoDcto
,TipoBase,FecLmtDcto,IdConcepto,Referencia,Detalle,TipRef,DocRef,IdCiaRef,FecUltPago,NumNota,IdCiaNot,TarifInt,FecLiqMora,FecPlazoCal
 FROM Trn_Vencimientos
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVencimientos_Crd @pmTipDoc VARCHAR(3),@pmFacturaIni INT,@pmFacturaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,TipoDoc,Factura,V.IdCia AS CdCia,Compania,Item,Fecha,FechaVence,VrFactura,VrAbonado
,IdCliente,T.RazonSocial AS NombCliente,V.IdAgencia AS IdAgenc,Agencia,CodAgencia,V.IdVend AS CdVend,VN.RazonSocial AS NomVendedor,Comision,TarifDcto,TipoDcto
,TipoBase,FecLmtDcto,V.IdConcepto AS CdConcepto,Concepto,V.Referencia AS Referncia,Detalle,TipRef,DocRef,IdCiaRef,FecUltPago,NumNota,IdCiaNot,TarifInt,FecLiqMora,FecPlazoCal
 FROM Trn_Vencimientos AS V INNER JOIN Terceros AS T ON V.IdCliente=T.IdTercero
 INNER JOIN Companias AS CN ON V.IdCia=CN.IdCia INNER JOIN Terceros AS VN ON V.IdVend=VN.IdTercero
 INNER JOIN Sys_TiposDoc AS TD ON V.TipDoc=TD.IdDoc INNER JOIN Conceptos AS C ON V.IdConcepto=C.IdConcepto
 LEFT JOIN Agencias AS A ON V.IdAgencia=A.IdAgencia 
 WHERE TipDoc=@pmTipDoc AND Factura BETWEEN @pmFacturaIni AND @pmFacturaFin AND V.IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVencimientosDoc @pmTipDoc VARCHAR(3),@pmFactura INT,@pmIdCia CHAR(2)
 AS 
 SELECT  Item,Fecha,FechaVence,VrFactura,VrAbonado,VrFactura-VrAbonado AS TotalSaldo,IdVend,Comision,TarifDcto,TipoDcto
,TipoBase,FecLmtDcto,IdConcepto,Referencia,Detalle,TipRef,DocRef,IdCiaRef,IdCliente,IdAgencia
,FecUltPago,NumNota,IdCiaNot,TarifInt,FecLiqMora,TipDoc,Factura,IdCia,FecPlazoCal
 FROM Trn_Vencimientos
 WHERE TipDoc=@pmTipDoc AND Factura=@pmFactura AND IdCia=@pmIdCia
 ORDER BY Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQrytm_CxCobrar @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmItem,tmFecVence,tmVrFactura,tmDetalle,tmReferencia,tmTarifDcto,tmTipoDcto
,tmTipoBase,tmFecLmtDcto,tmTipRef,tmDocRef,tmIdCiaRef,tmNumero,tmTotAbono,tmFecPlazo
 FROM tm_CxCobrar
 WHERE tmNumero=@pmtmNumero
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY tmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryVencAbonosLrc @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmFecActual SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmFechaFacIni SMALLDATETIME=Null,@pmFechaFacFin SMALLDATETIME=Null,@pmIdZona VARCHAR(4)=Null
,@pmIdSzona VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=NULL
 AS 
 SELECT  VA.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,VA.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,IdCiaFac,CI.Compania AS FactCompania,VA.TipDoc AS TipFact,TD.TipoDoc AS TipoFactura,VA.Factura AS NumFactura,VA.ItemFac AS NumVence
,V.Fecha AS FechaFact,FechaVence,FecPlazoCal,DATEDIFF(day,ISNULL(FecPlazoCal,FechaVence),@pmFecActual) AS DiasMora,VrFactura,VrAbonado,VrFactura-VrAbonado AS SaldoFactura
--recibo
,TipRec,TR.TipoDoc AS TipoRecibo,VA.Recibo AS NumRecibo,VA.IdCia AS RecIdCia,CR.Compania AS RecCompania,VA.Item AS RecItem,VA.FecPago AS FechaPago
,DATEDIFF(day,ISNULL(FecPlazoCal,V.FechaVence),VA.FecPago) AS DiasPago,VrAbono
,CASE WHEN VrFactura<>0 AND VrAbono>0 THEN (DATEDIFF(day,V.Fecha,VA.FecPago) *VrAbono)/VrFactura ELSE 0 END  AS PromDias,VA.Detalle AS RecDetalle,TipoAplica
,VA.IdVend AS RecCdVend,VR.RazonSocial AS RecVendedor,VrBaseCms,VA.Comision AS TarifComsCobro,VrDescto,VA.Fecha AS RecFecha
,V.IdAgencia AS FactIdAgencia,AF.Agencia AS FactAgencia,V.IdVend AS FactIdVend,VN.RazonSocial AS FactVendedor,V.Comision AS FactComision,V.Referencia AS FactReferencia,V.Detalle AS FactDetalle,TipRef,DocRef,IdCiaRef
,V.IdConcepto AS CdConcepto,Concepto
--información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad
,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,T.IdSector AS CdSector,SectorEco
--información del cliente
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato,CLI.CdBandera AS CliCdBandera,TB.TipoBandera AS CliBandera
,A.CodAgencia AS Cod_agencia,A.DirAgncia AS AgeDireccion,A.TelAgncia AS AgeTelefono,A.FaxAgncia AS AgeFax,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep
,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia,A.NContrato AS AgeNumContrato,A.CiaCont AS AgeCiaCont,A.CdBandera AS AgeIdBandera,TBA.TipoBandera AS AgeBandera
 FROM  Trn_VencAbonos AS VA INNER JOIN Trn_Vencimientos AS V  ON  VA.TipDoc=V.TipDoc AND VA.Factura=V.Factura AND VA.IdCiaFac=V.IdCia AND VA.ItemFac=V.Item
 INNER JOIN Companias AS CR ON VA.IdCia=CR.IdCia  INNER JOIN  Companias AS CI ON VA.IdCiaFac=CI.IdCia
 INNER JOIN Sys_TiposDoc AS TD ON VA.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON VA.IdCliente=T.IdTercero INNER JOIN Agencias AS A ON VA.IdAgencia=A.IdAgencia
 INNER JOIN Terceros AS VR ON VA.IdVend=VR.IdTercero INNER JOIN Agencias AS AF ON V.IdAgencia=AF.IdAgencia 
 INNER JOIN Terceros AS VN ON V.IdVend=VN.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliente AS CLI ON VA.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 LEFT JOIN Conceptos AS C ON V.IdConcepto=C.IdConcepto LEFT JOIN Sys_TiposDoc AS TR ON VA.TipRec=TR.IdDoc
 LEFT JOIN  TiposBan AS TB ON CLI.CdBandera=TB.IdBandera LEFT JOIN TiposBan AS TBA ON A.CdBandera=TBA.IdBandera
 WHERE  FecPago BETWEEN @pmFechaIni AND @pmFechaFin  AND  VA.IdCiaFac LIKE ISNULL(@pmIdCia,'%%')
 AND VA.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND V.IdAgencia LIKE ISNULL(@pmIdAgencia,'%')
 AND V.IdVend LIKE ISNULL(@pmIdVend,'%')  AND CLI.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND  SZ.IdZona LIKE ISNULL(@pmIdZona,'%') AND CLI.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND (V.Fecha>=ISNULL(@pmFechaFacIni,CAST('19100101' AS SMALLDATETIME)) AND V.Fecha<=ISNULL(@pmFechaFacFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY T.RazonSocial,VA.FecPago,VA.Factura,VA.ItemFac
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO