ALTER TABLE Trn_GatProrratVeh ADD Num_CasoSin INT DEFAULT(0) NOT NULL,Cia_CasoSin CHAR(2)
,VrAprobActa MONEY DEFAULT(0) NOT NULL,Estado_Reg INT DEFAULT(0) NOT NULL
GO
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('APA','01','Resumen de Aportes Asociados - Hz','CrApoRes.rpt',1,2,1,0,1,'paQryGatAportesRes','','Listados de Aportes')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('APA','10','Saldos de Aportes Asociados - Hz','CrApoTot.rpt',1,2,1,0,1,'paQryGatAportesRtl','','Listados de Aportes')
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('APA','20','Aportes de Asociados /Movimientos- Hz','CrApoDet.rpt',1,2,1,0,1,'paQryGatAportesRpt','','Listados de Aportes')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsGatProrratVeh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsGatProrratVeh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatAportesRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatAportesRes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatAportesRpt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatAportesRpt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatAportesRtl]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatAportesRtl]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatProrrateosFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatProrrateosFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatProrrateosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatProrrateosLta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsGatProrratVeh] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdAsociado VARCHAR(16),@pmUndVehiculo CHAR(1),@pmIdVehiculo VARCHAR(10),@pmTarifaLiq DECIMAL(16,6)
,@pmVrAportes MONEY,@pmImporte MONEY,@pmVrRetiros MONEY,@pmVrAvaluo MONEY,@pmTipoBaseLiq CHAR(1),@pmCodTarifa VARCHAR(4),@pmObservacion VARCHAR(500),@pmFecLiquida SMALLDATETIME,@pmFecIngreso SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmEdoVincula INT
,@pmNum_CasoSin INT,@pmCia_CasoSin CHAR(2),@pmVrAprobActa MONEY,@pmEstado_Reg INT
 AS 
 INSERT INTO Trn_GatProrratVeh (TipDoc,NumProceso,IdCia,Item,IdAsociado,UndVehiculo,IdVehiculo,TarifaLiq,VrAportes,Importe,VrRetiros,VrAvaluo,TipoBaseLiq,CodTarifa,Observacion,FecLiquida,FecIngreso,FecRetiro,EdoVincula,Num_CasoSin,Cia_CasoSin,VrAprobActa,Estado_Reg)
 VALUES (@pmTipDoc,@pmNumProceso,@pmIdCia,@pmItem,@pmIdAsociado,@pmUndVehiculo,@pmIdVehiculo,@pmTarifaLiq,@pmVrAportes,@pmImporte,@pmVrRetiros,@pmVrAvaluo,@pmTipoBaseLiq,@pmCodTarifa,@pmObservacion,@pmFecLiquida,@pmFecIngreso,@pmFecRetiro,@pmEdoVincula,@pmNum_CasoSin,@pmCia_CasoSin,@pmVrAprobActa,@pmEstado_Reg)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatAportesRpt] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT T.RazonSocial AS NomAsociado,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.nRemolque,A.FecEmision AS Fecha,A.TipDoc,TD.TipoDoc,A.NumRegistro,A.IdCia,CI.Compania,A.Item,A.Concepto,A.Importe,A.VrDevolucion,A.Descripcion,A.FecVence
,A.TipoBaseLiq,A.VrAvaluo,A.VrComercial,A.VrAsegurado,A.TarifaApo,A.Cantidad,A.CantDiasAnt,A.VrDiasAnt,A.Referencia,A.TipDocRef,A.NumDocRef,A.TipCom,A.Comprobante,A.FecLiqApo,A.FechaCrea
,V.NumVeh,V.IdTipoVeh,TV.TipoVehiculo,V.IdMarca,Marca,V.Modelo,V.IdCrceria,TipoCar,V.TipoAfil,V.FecIngreso,V.FecRetiro,V.EdoVincula,V.Inactivo
,T.TipoId,T.Dv,T.Direccion AS TercDireccion,T.IdLocal AS TercIdCiu,L.Localidad AS TercNomCiudad,L.IdDep,DP.Departamento,T.Telefono,T.TelMovil,T.e_mail,T.IdLugarCed,LC.Localidad AS LugarExpCed,T.FecExpCed,T.Asociado AS EsAsociado,T.Inactivo AS TercInactivo
,T.CdGrupoTerc,GC.GrupoClie
FROM Trn_GatAportes AS A INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON A.TipDoc=TD.IdDoc
INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero 
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH
UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo
LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
LEFT JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
WHERE A.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatProrrateosLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null,@pmIdCia CHAR(2)=Null
 AS 
 SELECT P.TipDoc,P.NumProceso,P.IdCia,Compania,P.Fecha,P.IdConcepto,CN.Concepto,P.Modalidad,P.TipDocActa,P.NumDocActa,P.IdCiaActa,P.FecSuceso,P.IdAsociado,T.RazonSocial AS NomAsociado,P.UndVehiculo,P.IdVehiculo
,P.nRemolque,P.VrAprobado,P.VrAvaluo,P.TipoAfiVeh,P.Referencia,P.TipCom,TipoCom,P.Comprobante,P.IdCiaCom,P.Anulado,P.NumDev,P.FechaDev,P.Observacion,P.IdUsuario,Usuario,P.FechaCrea,P.IdCiaCrea,P.ModuloCrea,TD.TipoDoc
,D.Item,D.IdAsociado AS DetNitAsociado,NA.RazonSocial AS DetNomAsociado,D.UndVehiculo AS DetUndVehic,D.IdVehiculo AS DetIdVehiculo,D.VrAvaluo AS DetVrAvaluo,D.TipoBaseLiq,D.TarifaLiq,D.VrAportes,D.Importe,D.VrRetiros,D.CodTarifa
,D.Observacion AS DetObservacion,D.FecLiquida,D.FecIngreso AS DetFecIngreso,D.FecRetiro AS DetFecRetiro,D.EdoVincula AS DetEdoVincula,D.Num_CasoSin,D.Cia_CasoSin,D.VrAprobActa,D.Estado_Reg
,A.NumActa,A.FechaActa AS ActFecha,A.ValorTotal AS ActVrAprobado,A.Observacion AS ActObservacion
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
FROM Trn_GatProrrateos AS P INNER JOIN Trn_GatProrratVeh AS D ON P.TipDoc=D.TipDoc AND P.NumProceso=D.NumProceso AND P.IdCia=D.IdCia
INNER JOIN Trn_JurActas AS A ON P.TipDocActa=A.TipDoc AND P.NumDocActa=A.NumProceso AND P.IdCiaActa=A.IdCia
INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
INNER JOIN Conceptos AS CN ON P.IdConcepto=CN.IdConcepto INNER JOIN Terceros AS T ON P.IdAsociado=T.IdTercero
INNER JOIN Terceros AS NA ON D.IdAsociado=NA.IdTercero
LEFT JOIN QryVehiculosGat AS QV ON D.IdVehiculo=QV.IdVehiculo LEFT JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
LEFT JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON P.TipCom=TCO.IdCom
WHERE P.TipDoc=@pmTipDoc AND P.Fecha BETWEEN ISNULL(@pmFechaIni,CAST('19900101' AS SMALLDATETIME)) AND ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME))
 AND (@pmIdCia IS NULL OR P.IdCia=@pmIdCia)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatProrrateosFmt] @pmTipDoc VARCHAR(3),@pmNumProcesoIni INT,@pmNumProcesoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT P.TipDoc,P.NumProceso,P.IdCia,Compania,P.Fecha,P.IdConcepto,CN.Concepto,P.Modalidad,P.TipDocActa,P.NumDocActa,P.IdCiaActa,P.FecSuceso,P.IdAsociado,T.RazonSocial AS NomAsociado,P.UndVehiculo,P.IdVehiculo
,P.nRemolque,P.VrAprobado,P.VrAvaluo,P.TipoAfiVeh,P.Referencia,P.TipCom,TipoCom,P.Comprobante,P.IdCiaCom,P.Anulado,P.NumDev,P.FechaDev,P.Observacion,P.IdUsuario,Usuario,P.FechaCrea,P.IdCiaCrea,P.ModuloCrea,TD.TipoDoc
,D.Item,D.IdAsociado AS DetNitAsociado,NA.RazonSocial AS DetNomAsociado,D.UndVehiculo AS DetUndVehic,D.IdVehiculo AS DetIdVehiculo,D.VrAvaluo AS DetVrAvaluo,D.TipoBaseLiq,D.TarifaLiq,D.VrAportes,D.Importe,D.VrRetiros,D.CodTarifa
,D.Observacion AS DetObservacion,D.FecLiquida,D.FecIngreso AS DetFecIngreso,D.FecRetiro AS DetFecRetiro,D.EdoVincula AS DetEdoVincula,D.Num_CasoSin,D.Cia_CasoSin,D.VrAprobActa,D.Estado_Reg
,A.NumActa,A.FechaActa AS ActFecha,A.ValorTotal AS ActVrAprobado,A.Observacion AS ActObservacion
,QV.NumVeh,QV.IdTipoVeh,TipoVehiculo,QV.IdMarca,Marca,QV.IdColor,NomColor,QV.IdCrceria,TipoCar,QV.Modelo,QV.IdPoseedor,NP.RazonSocial AS NomPoseedor,QV.IdPpd,TipoProp,QV.TipoAfil,QV.SerieChasis,QV.VrAvaludo AS VrAvaluoAct
,QV.EdoVincula,QV.FecIngreso,QV.FecVigencia,QV.FecRetiro,QV.IdEstado,EV.Estado,QV.Inactivo
FROM Trn_GatProrrateos AS P INNER JOIN Trn_GatProrratVeh AS D ON P.TipDoc=D.TipDoc AND P.NumProceso=D.NumProceso AND P.IdCia=D.IdCia
INNER JOIN Trn_JurActas AS A ON P.TipDocActa=A.TipDoc AND P.NumDocActa=A.NumProceso AND P.IdCiaActa=A.IdCia
INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
INNER JOIN Companias AS CI ON P.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON P.TipDoc=TD.IdDoc
INNER JOIN Conceptos AS CN ON P.IdConcepto=CN.IdConcepto INNER JOIN Terceros AS T ON P.IdAsociado=T.IdTercero
INNER JOIN Terceros AS NA ON D.IdAsociado=NA.IdTercero
LEFT JOIN QryVehiculosGat AS QV ON D.IdVehiculo=QV.IdVehiculo LEFT JOIN Marcas AS M ON QV.IdMarca=M.IdMarca
LEFT JOIN TiposCar AS TC ON QV.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NP ON QV.IdPoseedor=NP.IdTercero
LEFT JOIN TiposCol AS CL ON QV.IdColor=CL.IdColor LEFT JOIN TiposVeh AS TV ON QV.IdTipoVeh=TV.IdTipoVeh
LEFT JOIN TiposPpt AS TP ON QV.IdPpd=TP.IdPpd LEFT JOIN EstadoVeh AS EV ON QV.IdEstado=EV.IdEstado
LEFT JOIN TiposCom AS TCO ON P.TipCom=TCO.IdCom
WHERE P.TipDoc=@pmTipDoc AND P.NumProceso BETWEEN @pmNumProcesoIni AND @pmNumProcesoFin AND P.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatAportesRtl] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT T.RazonSocial AS NomAsociado,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,ISNULL(dbo.FuncAcuAportesSan(YEAR(@pmFechaIni),MONTH(@pmFechaIni),A.IdCia,A.IdAsociado,A.IdVehiculo),0) AS SaldoAnterior,SA.VrAportes,SA.VrVincula
,ISNULL(SA.VrAportes,0)+ISNULL(SA.VrVincula,0) AS TotalAportes,SA.VrGarantia,SA.VrRetAportes,SA.VrRetVincula,ISNULL(SA.VrRetAportes,0)+ISNULL(SA.VrRetVincula,0) AS TotalRetAportes,SA.VrRetGarantia
,ISNULL(dbo.FuncAcuAportesSan(YEAR(@pmFechaIni),MONTH(@pmFechaIni),A.IdCia,A.IdAsociado,A.IdVehiculo),0)+(ISNULL(SA.VrAportes,0)+ISNULL(SA.VrVincula,0)-ISNULL(SA.VrGarantia,0))-(ISNULL(SA.VrRetAportes,0)+ISNULL(SA.VrRetVincula,0)-ISNULL(SA.VrRetGarantia,0)) AS SaldoAportes,SUM(A.Importe) AS Total
,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
,ISNULL(dbo.FuncAcuAportesSan(YEAR(@pmFechaIni),MONTH(@pmFechaIni),Null,A.IdAsociado,A.IdVehiculo),0) AS SaldoAntVeh
FROM Trn_GatAportes AS A INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH
UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo
LEFT JOIN (SELECT IdAsociado,UndVehiculo,IdVehiculo,IdCia,SUM(CASE WHEN (Concepto IN ('APORTE','ADICIONAL','TRASLADO_ING') AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE (CASE WHEN (Concepto='TRASLADO' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) END) AS VrAportes
,SUM(CASE WHEN (Concepto='VINCULACION' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) AS VrVincula
,SUM(CASE WHEN (Concepto='GARANTIA' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) AS VrGarantia
,SUM(CASE WHEN (Concepto IN ('APORTE','ADICIONAL','TRASLADO_ING') AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE (CASE WHEN (Concepto='TRASLADO' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) END) AS VrRetAportes
,SUM(CASE WHEN (Concepto='VINCULACION' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) AS VrRetVincula
,SUM(CASE WHEN (Concepto='GARANTIA' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) AS VrRetGarantia 
FROM Trn_GatAportes WHERE FecEmision BETWEEN @pmFechaIni AND @pmFechaFin GROUP BY IdAsociado,UndVehiculo,IdVehiculo,IdCia) AS SA ON A.IdAsociado=SA.IdAsociado AND A.UndVehiculo=SA.UndVehiculo AND A.IdVehiculo=SA.IdVehiculo AND A.IdCia=SA.IdCia
LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
WHERE A.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
GROUP BY T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,SA.VrAportes,SA.VrVincula,SA.VrGarantia,SA.VrRetAportes,SA.VrRetVincula,SA.VrRetGarantia,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
--saldos no movimiento
UNION ALL 
SELECT T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,SUM(CASE WHEN A.Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN A.SaldoAnt*-1 ELSE A.SaldoAnt END),0,0,0,0,0,0,0,0,SUM(CASE WHEN A.Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN A.SaldoAnt*-1 ELSE A.SaldoAnt END),0
,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
,0
FROM AcuAportes AS A INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH
UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo
LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
WHERE A.nAnno=YEAR(@pmFechaIni) AND A.nMes=MONTH(@pmFechaIni) AND A.SaldoAnt<>0 AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
AND NOT EXISTS (SELECT IdAsociado,UndVehiculo,IdVehiculo,IdCia,SUM(Importe) AS Total FROM Trn_GatAportes WHERE FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND Trn_GatAportes.IdCia=A.IdCia AND Trn_GatAportes.IdAsociado=A.IdAsociado AND Trn_GatAportes.UndVehiculo=A.UndVehiculo AND Trn_GatAportes.IdVehiculo=A.IdVehiculo AND YEAR(Trn_GatAportes.FecEmision)=A.nAnno AND MONTH(Trn_GatAportes.FecEmision)=A.nMes
GROUP BY IdAsociado,UndVehiculo,IdVehiculo,IdCia)
GROUP BY T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatAportesRes] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
SELECT T.RazonSocial AS NomAsociado,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,YEAR(A.FecEmision) AS YearNum,MONTH(A.FecEmision) AS MonthNum
,ISNULL(dbo.FuncAcuAportesSan(YEAR(A.FecEmision),MONTH(A.FecEmision),A.IdCia,A.IdAsociado,A.IdVehiculo),0) AS SaldoAnterior,SA.VrAportes,SA.VrVincula,ISNULL(SA.VrAportes,0)+ISNULL(SA.VrVincula,0) AS TotalAportes,SA.VrGarantia,SA.VrRetAportes,SA.VrRetVincula,ISNULL(SA.VrRetAportes,0)+ISNULL(SA.VrRetVincula,0) AS TotalRetAportes,SA.VrRetGarantia
,ISNULL(dbo.FuncAcuAportesSan(YEAR(A.FecEmision),MONTH(A.FecEmision),A.IdCia,A.IdAsociado,A.IdVehiculo),0)+(ISNULL(SA.VrAportes,0)+ISNULL(SA.VrVincula,0)-ISNULL(SA.VrGarantia,0))-(ISNULL(SA.VrRetAportes,0)+ISNULL(SA.VrRetVincula,0)-ISNULL(SA.VrRetGarantia,0)) AS SaldoAportes,SUM(A.Importe) AS Total
,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
,ISNULL(dbo.FuncAcuAportesSan(YEAR(@pmFechaIni),MONTH(@pmFechaIni),Null,A.IdAsociado,A.IdVehiculo),0) AS SaldoAntVeh
 FROM Trn_GatAportes AS A INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
 INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH 
 UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo
 LEFT JOIN (SELECT IdAsociado,UndVehiculo,IdVehiculo,IdCia,YEAR(FecEmision) AS YearNum,MONTH(FecEmision) AS MonthNum,SUM(CASE WHEN (Concepto IN ('APORTE','ADICIONAL','TRASLADO_ING') AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE (CASE WHEN (Concepto='TRASLADO' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) END) AS VrAportes
,SUM(CASE WHEN (Concepto='VINCULACION' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) AS VrVincula
,SUM(CASE WHEN (Concepto='GARANTIA' AND TipDoc<>'RAP' AND TipDoc<>'DRA') THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) AS VrGarantia
,SUM(CASE WHEN (Concepto IN ('APORTE','ADICIONAL','TRASLADO_ING') AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE (CASE WHEN (Concepto='TRASLADO' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN VrDevolucion>0 THEN VrDevolucion*-1 ELSE Importe END) ELSE 0 END) END) AS VrRetAportes
,SUM(CASE WHEN (Concepto='VINCULACION' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) AS VrRetVincula
,SUM(CASE WHEN (Concepto='GARANTIA' AND (TipDoc='RAP' OR TipDoc='DRA')) THEN (CASE WHEN Importe>0 THEN Importe*-1 ELSE VrDevolucion END) ELSE 0 END) AS VrRetGarantia
 FROM Trn_GatAportes WHERE FecEmision BETWEEN @pmFechaIni AND @pmFechaFin GROUP BY IdAsociado,UndVehiculo,IdVehiculo,IdCia,YEAR(FecEmision),MONTH(FecEmision)) AS SA ON A.IdAsociado=SA.IdAsociado AND A.UndVehiculo=SA.UndVehiculo AND A.IdVehiculo=SA.IdVehiculo AND A.IdCia=SA.IdCia AND YEAR(A.FecEmision)=SA.YearNum AND MONTH(A.FecEmision)=SA.MonthNum 
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
 WHERE A.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
 GROUP BY T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,YEAR(A.FecEmision),MONTH(A.FecEmision),SA.VrAportes,SA.VrVincula,SA.VrGarantia,SA.VrRetAportes,SA.VrRetVincula,SA.VrRetGarantia,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
 --Saldos anteriores  sin movimientos
 UNION ALL SELECT T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,A.nAnno,A.nMes,SUM(CASE WHEN A.Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN A.SaldoAnt*-1 ELSE A.SaldoAnt END),0,0,0,0,0,0,0,0,SUM(CASE WHEN A.Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN A.SaldoAnt*-1 ELSE A.SaldoAnt END),0
 ,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie,0
 FROM AcuAportes AS A INNER JOIN Terceros AS T ON A.IdAsociado=T.IdTercero INNER JOIN Companias AS CI ON A.IdCia=CI.IdCia
 INNER JOIN (SELECT 'V' AS TipoUnd,VH.IdVehiculo,VH.NumVeh,VH.IdTipoVeh,VH.IdMarca,VH.IdCrceria,VH.Modelo,VH.TipoAfil,VH.FecIngreso,VH.FecRetiro,VH.EdoVincula,VH.Inactivo FROM Vehiculos AS VH 
 UNION ALL SELECT 'R',R.IdRemque,'','0',R.IdMarca,R.IdCrceria,R.Modelo,R.Tipo_Afil,R.FechaVincula,R.FechaRetiro,R.EdoVincula,R.Inactivo FROM VehRemolq AS R) AS V ON A.IdVehiculo=V.IdVehiculo 
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 LEFT JOIN GruposCli AS GC ON T.CdGrupoTerc=GC.IdGrupo
 WHERE A.nAnno=YEAR(@pmFechaIni) AND A.nMes=MONTH(@pmFechaIni) AND A.SaldoAnt<>0 AND (A.IdCia=@pmIdCia OR @pmIdCia IS NULL)
 AND NOT EXISTS (SELECT IdAsociado,UndVehiculo,IdVehiculo,IdCia,YEAR(FecEmision) AS YearNum,MONTH(FecEmision) AS MonthNum,SUM(Importe) AS Total FROM Trn_GatAportes
 WHERE FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND Trn_GatAportes.IdCia=A.IdCia AND Trn_GatAportes.IdAsociado=A.IdAsociado AND Trn_GatAportes.UndVehiculo=A.UndVehiculo AND Trn_GatAportes.IdVehiculo=A.IdVehiculo AND YEAR(Trn_GatAportes.FecEmision)=A.nAnno AND MONTH(Trn_GatAportes.FecEmision)=A.nMes 
 GROUP BY IdAsociado,UndVehiculo,IdVehiculo,IdCia,YEAR(FecEmision),MONTH(FecEmision))
 GROUP BY T.RazonSocial,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.IdCia,CI.Compania,A.nAnno,A.nMes,V.NumVeh,TipoVehiculo,Marca,TipoCar,V.Modelo,V.EdoVincula,V.FecIngreso,V.FecRetiro,V.Inactivo,T.CdGrupoTerc,GC.GrupoClie
GO