if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAcuAportesSan]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAcuAportesSan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsGatAjustesPror]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsGatAjustesPror]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsGatDevAjuPror]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsGatDevAjuPror]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsGatDevProrr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsGatDevProrr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsGatProrrateos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsGatProrrateos]
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].[paInsJurActas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsJurActas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsJurCasos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsJurCasos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraLiqCausac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraLiqCausac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraLiqCausac_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_TraLiqCausac_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatAjustesPror]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatAjustesPror]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevAjuPror]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevAjuPror]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatDevProrr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatDevProrr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGatProrrateos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryGatProrrateos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryJurActas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryJurActas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryJurCasos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryJurCasos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryJurCasos_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryJurCasos_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryJurCasos_Crh]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryJurCasos_Crh]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryJurCasos_Crr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryJurCasos_Crr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryJurCasos_CrrH]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryJurCasos_CrrH]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryJurCasosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryJurCasosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAcuAportesSan]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAcuAportesSan]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpGatAjustesPror]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpGatAjustesPror]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpGatAjustesProrDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpGatAjustesProrDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpGatDevAjuPror]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpGatDevAjuPror]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpGatDevProrr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpGatDevProrr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpGatProrrateos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpGatProrrateos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpGatProrrateosDev]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpGatProrrateosDev]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpJurActas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpJurActas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpJurCasos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpJurCasos]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FuncAcuAportesSan]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FuncAcuAportesSan]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryJurCasosLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmAnulado BIT=Null,@pmIdVehiculo VARCHAR(10)=Null
AS 
SELECT C.CasoID,C.IdCia,C.Fecha,C.Radicacion,C.UndVehiculo,C.IdVehiculo AS PlacaVeh,V.NumVeh,C.NumRemolque,C.IdPropietario AS NitPropiterio,T.RazonSocial AS Propietario,C.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,SinRecorrido,C.TipCar,C.Cartulina,C.IdCiaCar,C.FechaSuc,C.IdRuta AS CodRuta,Ruta
,IdAbogado,A.RazonSocial AS Abogado,IdAuxiliar,AX.RazonSocial AS Auxiliar,C.IdAutoridad AS IdAutrdad,NomAutoridad, C.IdTipoPro AS IdTProc,TipoProceso,C.IdClase AS IdClaAcc,ClaseAccdte, C.IdCalifica AS IdCalf,CalificaCaso,C.LugarAcc,CausaAcc,C.Comentario,C.Observacion AS Observ
,Cedtraparte,Contraparte,DirContraparte,TelContraparte,ValDanosCParte, ValDanosEmp, ValDeducible, EmpAsegdora,C.AuxMutuo,C.ArchivoCaso,C.Documntos,C.Croquis,Reclamaciones,CdCausal,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,TipActaJur,NumActaJur,CiaActaJur,EstadoProc,C.Anulado,C.FecDev,C.IdEstado AS IdEstad,Estado
,C.IdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,TipDoc,C.FecUpdate,C.TimeSys
 FROM Trn_JurCasos AS C INNER JOIN Terceros AS T ON C.IdPropietario=T.IdTercero INNER JOIN Terceros AS NC ON C.IdConductor=NC.IdTercero
 INNER JOIN Terceros AS A ON C.IdAbogado=A.IdTercero INNER JOIN Terceros AS AX ON C.IdAuxiliar=AX.IdTercero 
 INNER JOIN JurAutoridades AS AU ON C.IdAutoridad=AU.IdAutoridad INNER JOIN JurTipos AS TP ON C.IdTipoPro=TP.IdTipoPro
 INNER JOIN JurClaseAcc AS CA ON C.IdClase=CA.IdClase INNER JOIN JurCalifica AS CL ON C.IdCalifica=CL.IdCalifica
 INNER JOIN JurEstados AS E ON C.IdEstado=E.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 LEFT JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo
 LEFT JOIN Rutas AS R ON C.IdRuta=R.IdRuta 
 WHERE C.TipDoc=@pmTipDoc AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND C.IdCia like ISNULL(@pmIdCia,'%%')
 AND C.IdVehiculo like ISNULL(@pmIdVehiculo,'%')
 AND (C.Anulado=ISNULL(@pmAnulado,0) or C.Anulado=ISNULL(@pmAnulado,1))
ORDER BY IdCia,CasoID
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryJurCasos_Cr] @pmTipDoc VARCHAR(3),@pmCasoIDIni INT,@pmCasoIDFin INT,@pmIdCia CHAR(2)
AS 
SELECT C.CasoID AS NumCaso,C.IdCia AS CodCia,Compania,C.Fecha,C.Radicacion,C.UndVehiculo,C.IdVehiculo AS PlacaVeh,C.NumRemolque,C.IdPropietario AS NitPropiterio,C.IdConductor AS CedConductor,SinRecorrido,C.TipCar,C.Cartulina,C.IdCiaCar,C.FechaSuc,C.IdRuta AS CodRuta,R.Ruta
,IdAbogado,A.RazonSocial AS Abogado,IdAuxiliar,AX.RazonSocial AS Auxiliar,C.IdAutoridad AS IdAutrdad,NomAutoridad, C.IdTipoPro AS IdTProc,TipoProceso, C.IdClase AS IdClaAcc,ClaseAccdte, C.IdCalifica AS IdCalf,CalificaCaso,C.LugarAcc,C.CausaAcc,C.Comentario,C.Observacion AS Observ
, Contraparte, DirContraparte,TelContraparte,ValDanosCParte, ValDanosEmp, ValDeducible, EmpAsegdora, AuxMutuo, ArchivoCaso, Documntos, Croquis,Item,QT.IdPregunta AS IdPregnta,PreguntaBas,QT.Descripcion AS RptaPregunta,Anulado, FecDev,C.IdEstado AS IdEstad,E.Estado AS EstadDocm
,Reclamaciones,CdCausal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,C.TipActaJur,C.NumActaJur,C.CiaActaJur,C.EstadoProc,C.IdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,C.TipDoc AS CodTipo,TipoDoc,C.FecUpdate,C.TimeSys
--Datos del vehículo
,V.NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,V.Modelo,V.FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,V.NumMotor,V.SerieChasis,V.PasjerosPie,V.PasjerosSen
,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,V.TipoAfil,V.NContrato,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,V.EdoVincula,V.FecIngreso,V.FecVigencia,V.FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado
,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo
--datos del propietario
,NP.RazonSocial AS Propietario,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep
,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil
--datos de conductor
,NC.RazonSocial AS Conductor,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto
,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula
--Datos remolque
,RQ.Modelo AS RemModelo,RQ.IdCrceria AS RemIdCar,TCR.TipoCar AS RemTipoCar,RQ.NumSerial,RQ.IdPropietario AS RemIdProp,PR.RazonSocial AS RemPropietario,RQ.VrComercial AS RemVrComercial,RQ.VrAvaludo AS RemVrAvaluo,RQ.VrAsegurado AS RemVrAsegurado,RQ.Descripcion AS RemDescripcion
,RQ.Tipo_Afil,RQ.EdoVincula AS RemEdoVincula,RQ.FechaVincula,RQ.FechaRetiro AS RemFecRetiro
 FROM Trn_JurCasos AS C INNER JOIN Terceros AS NP ON C.IdPropietario=NP.IdTercero INNER JOIN Terceros AS NC ON C.IdConductor=NC.IdTercero
 INNER JOIN Terceros AS A ON C.IdAbogado=A.IdTercero INNER JOIN Terceros AS AX ON C.IdAuxiliar=AX.IdTercero 
 INNER JOIN JurAutoridades AS AU ON C.IdAutoridad=AU.IdAutoridad INNER JOIN JurTipos AS TP ON C.IdTipoPro=TP.IdTipoPro
 INNER JOIN JurClaseAcc AS CA ON C.IdClase=CA.IdClase INNER JOIN JurCalifica AS CL ON C.IdCalifica=CL.IdCalifica
 INNER JOIN JurEstados AS E ON C.IdEstado=E.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep
 INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 LEFT JOIN Rutas AS R ON C.IdRuta=R.IdRuta LEFT JOIN Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal 
 LEFT JOIN Trn_JurCasosQtn AS QT ON C.TipDoc=QT.TipDoc AND C.CasoID=QT.CasoID AND C.IdCia=QT.IdCia
 LEFT JOIN JurPreguntas AS JP ON QT.IdPregunta=JP.IdPregunta
 --tablas vehículo
 LEFT JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo LEFT JOIN TiposCol AS CO ON V.IdColor=CO.IdColor
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo LEFT JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
 LEFT JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero LEFT JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
 LEFT JOIN TiposFuel AS TC ON  V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR  ON V.IdPpd=TPR.IdPpd
 LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv
 LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa
 --REMOLQUE
 LEFT JOIN VehRemolq AS RQ ON C.NumRemolque=RQ.IdRemque LEFT JOIN TiposCar AS TCR ON RQ.IdCrceria=TCR.IdCrceria
 LEFT JOIN Terceros AS PR ON RQ.IdPropietario=PR.IdTercero 
 WHERE C.TipDoc=@pmTipDoc AND C.CasoID BETWEEN @pmCasoIDIni AND @pmCasoIDFin
 AND (@pmIdCia IS NULL OR C.IdCia=@pmIdCia)
 ORDER BY C.IdCia,C.CasoID,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryJurCasos_CrrH] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPropietario VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
,@pmIdAbogado VARCHAR(16)=Null,@pmIdAuxiliar VARCHAR(16)=Null,@pmIdAutoridad VARCHAR(4)=Null,@pmIdTipoPro VARCHAR(4)=Null,@pmIdClase VARCHAR(4)=Null,@pmIdCalifica VARCHAR(4)=Null,@pmSinRecorrido BIT=Null
,@pmRadicacion VARCHAR(20)=Null,@pmCasoID INT=Null,@pmCartulina INT=Null,@pmFechaSuc SMALLDATETIME=Null,@pmIdRuta VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmIdEstado VARCHAR(4)=Null,@pmActualizaID INT=Null
AS 
SELECT C.CasoID AS NumCaso,C.IdCia AS CodCia,C.Fecha,C.Radicacion,C.UndVehiculo,C.IdVehiculo AS PlacaVeh,C.NumRemolque,C.IdPropietario AS NitPropiterio,NP.RazonSocial AS Propietario,C.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,SinRecorrido, TipCar, Cartulina, IdCiaCar, FechaSuc, C.IdRuta AS CodRuta,Ruta
,IdAbogado,A.RazonSocial AS Abogado,IdAuxiliar,AX.RazonSocial AS Auxiliar,C.IdAutoridad AS IdAutrdad,NomAutoridad, C.IdTipoPro AS IdTProc,TipoProceso,C.IdClase AS IdClaAcc,ClaseAccdte, C.IdCalifica AS IdCalf,CalificaCaso, LugarAcc, CausaAcc, Comentario, C.Observacion AS Observ
, Contraparte, DirContraparte,TelContraparte,ValDanosCParte, ValDanosEmp, ValDeducible, EmpAsegdora, AuxMutuo, ArchivoCaso, Documntos, Croquis,ActualizaID, FechaUp, Historial, EstadoAct, CierreProc, ArchivoProc,CedContparte, NombreCparte, DirCparte, TelCparte,DanosCParte,DanosEmp, ValorDeducible, Aseguradora
,AuxilioMutuo,Reclamaciones,CdCausal,Causal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,C.TipActaJur,C.NumActaJur,C.CiaActaJur,C.EstadoProc,H.IdEstado AS His_IdEstado,EH.Estado AS His_Estado,H.IdUsuario AS His_IdUsuario,UH.Usuario AS His_Usuario
,Anulado,FecDev,C.IdEstado AS IdEstad,E.Estado AS EstadDocm,C.IdUsuario AS IdUsuari,U.Usuario AS Usuario_Doc,Compania,C.TipDoc AS IdTipDcm,IdCiaCrea,C.FecUpdate,C.TimeSys
,V.NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,V.Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,V.NumMotor,V.SerieChasis,PasjerosPie,PasjerosSen
,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,V.EdoVincula,V.FecIngreso,V.FecVigencia,V.FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado
,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo
--datos del propietario
,NP.RazonSocial AS Propietario,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep
,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil
--datos de conductor
,NC.RazonSocial AS Conductor,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto
,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula
,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic
--Datos remolque
,RQ.Modelo AS RemModelo,RQ.IdCrceria AS RemIdCar,TCR.TipoCar AS RemTipoCar,RQ.NumSerial,RQ.IdPropietario AS RemIdProp,PR.RazonSocial AS RemPropietario,RQ.VrComercial AS RemVrComercial,RQ.VrAvaludo AS RemVrAvaluo,RQ.VrAsegurado AS RemVrAsegurado,RQ.Descripcion AS RemDescripcion
,RQ.Tipo_Afil,RQ.EdoVincula AS RemEdoVincula,RQ.FechaVincula,RQ.FechaRetiro AS RemFecRetiro
 FROM Trn_JurCasos AS C INNER JOIN Terceros AS NP ON C.IdPropietario=NP.IdTercero INNER JOIN Terceros AS NC ON C.IdConductor=NC.IdTercero
 INNER JOIN Terceros AS A ON C.IdAbogado=A.IdTercero INNER JOIN Terceros AS AX ON C.IdAuxiliar=AX.IdTercero
 INNER JOIN JurAutoridades AS AU ON C.IdAutoridad=AU.IdAutoridad INNER JOIN JurTipos AS TP ON C.IdTipoPro=TP.IdTipoPro
 INNER JOIN JurClaseAcc AS CA ON C.IdClase=CA.IdClase INNER JOIN JurCalifica AS CL ON C.IdCalifica=CL.IdCalifica
 INNER JOIN JurEstados AS E ON C.IdEstado=E.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep
 INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 INNER JOIN Trn_JurCasosHis AS H ON C.TipDoc=H.TipDoc AND C.CasoID=H.CasoID AND C.IdCia=H.IdCia
 INNER JOIN adm_Usuarios AS UH ON H.IdUsuario=UH.IdUsuario INNER JOIN JurEstados AS EH ON H.IdEstado=EH.IdEstado
 LEFT JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo LEFT JOIN TiposCol AS CO ON V.IdColor=CO.IdColor 
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo LEFT JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
 LEFT JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero LEFT JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
 LEFT JOIN TiposFuel AS TC ON  V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv
 LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa  LEFT JOIN JurCausales AS CS ON C.CdCausal=CS.IdCausal
 LEFT JOIN Rutas AS R ON C.IdRuta=R.IdRuta LEFT JOIN TercCndtores AS CT ON C.IdConductor=CT.IdConductor
 LEFT JOIN Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd
 LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar
 LEFT JOIN VehRemolq AS RQ ON C.NumRemolque=RQ.IdRemque LEFT JOIN TiposCar AS TCR ON RQ.IdCrceria=TCR.IdCrceria
 LEFT JOIN Terceros AS PR ON RQ.IdPropietario=PR.IdTercero 
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  C.IdCia like ISNULL(@pmIdCia,'%%') AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 AND C.IdPropietario LIKE ISNULL(@pmIdPropietario,'%') AND C.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND IdAbogado LIKE ISNULL(@pmIdAbogado,'%')
 AND IdAuxiliar LIKE ISNULL(@pmIdAuxiliar,'%') AND C.IdAutoridad LIKE ISNULL(@pmIdAutoridad,'%') AND C.IdTipoPro LIKE ISNULL(@pmIdTipoPro,'%')
 AND  C.IdClase LIKE ISNULL(@pmIdClase,'%') AND C.IdCalifica LIKE ISNULL(@pmIdCalifica,'%')   AND Radicacion LIKE ISNULL(@pmRadicacion,'%')
 AND (C.CasoID>=ISNULL(@pmCasoID,0)  AND C.CasoID<=ISNULL(@pmCasoID,2147483647))
  AND (Cartulina>=ISNULL(@pmCartulina,0)  AND Cartulina<=ISNULL(@pmCartulina,2147483647))
  AND (FechaSuc>=ISNULL(@pmFechaSuc,CAST('19100101' AS SMALLDATETIME)) AND FechaSuc<=ISNULL(@pmFechaSuc,CAST('20781230' AS SMALLDATETIME)))
  AND C.IdRuta LIKE ISNULL(@pmIdRuta,'%') 
AND  (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
  AND  (SinRecorrido=ISNULL(@pmSinRecorrido,0) or SinRecorrido=ISNULL(@pmSinRecorrido,1))
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%')
  AND (ActualizaID>=ISNULL(@pmActualizaID,0)  AND ActualizaID<=ISNULL(@pmActualizaID,2147483647))
 ORDER BY C.IdCia,C.CasoID,ActualizaID
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryJurCasos_Crh] @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT=Null
AS 
SELECT C.CasoID AS NumCaso, C.IdCia AS CodCia,Compania,C.Fecha,C.Radicacion,C.UndVehiculo,C.IdVehiculo AS PlacaVeh,C.NumRemolque,C.IdPropietario AS NitPropiterio,NP.RazonSocial AS Propietario,C.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,SinRecorrido, TipCar, Cartulina, IdCiaCar, FechaSuc, C.IdRuta AS CodRuta,Ruta
,C.IdAbogado,A.RazonSocial AS Abogado,IdAuxiliar,AX.RazonSocial AS Auxiliar,C.IdAutoridad AS IdAutrdad,NomAutoridad, C.IdTipoPro AS IdTProc,TipoProceso, C.IdClase AS IdClaAcc,ClaseAccdte, C.IdCalifica AS IdCalf,CalificaCaso, LugarAcc, CausaAcc, Comentario, C.Observacion AS Observ
,C.Contraparte,C.DirContraparte,TelContraparte,ValDanosCParte, ValDanosEmp, ValDeducible, EmpAsegdora, AuxMutuo, ArchivoCaso, Documntos, Croquis,ActualizaID, FechaUp, Historial, EstadoAct, CierreProc, ArchivoProc, NombreCparte, DirCparte, TelCparte,DanosCParte,DanosEmp,ValorDeducible,Aseguradora
,C.Reclamaciones,CdCausal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,AuxilioMutuo,C.TipActaJur,C.NumActaJur,C.CiaActaJur,C.EstadoProc,H.IdEstado AS His_IdEstado,H.IdUsuario AS His_IdUsuario,UH.Usuario AS His_Usuario,C.Anulado,C.FecDev
,C.IdEstado AS IdEstad,E.Estado AS EstadDocm,IdCiaCrea,C.IdUsuario AS IdUsuari,U.Usuario AS Cas_Usuario,C.TipDoc AS CodTipo,TipoDoc,C.FecUpdate,C.TimeSys
,V.NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,V.Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,V.NumMotor,V.SerieChasis,V.PasjerosPie,V.PasjerosSen
,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,V.EdoVincula,V.FecIngreso,V.FecVigencia,V.FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado
,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo
--datos del propietario
,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep
,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil
--datos de conductor
,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto
,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula
--Datos remolque
,RQ.Modelo AS RemModelo,RQ.IdCrceria AS RemIdCar,TCR.TipoCar AS RemTipoCar,RQ.NumSerial,RQ.IdPropietario AS RemIdProp,PR.RazonSocial AS RemPropietario,RQ.VrComercial AS RemVrComercial,RQ.VrAvaludo AS RemVrAvaluo,RQ.VrAsegurado AS RemVrAsegurado,RQ.Descripcion AS RemDescripcion
,RQ.Tipo_Afil,RQ.EdoVincula AS RemEdoVincula,RQ.FechaVincula,RQ.FechaRetiro AS RemFecRetiro
 FROM Trn_JurCasos AS C INNER JOIN Terceros AS NP ON C.IdPropietario=NP.IdTercero 
 INNER JOIN Terceros AS NC ON C.IdConductor=NC.IdTercero INNER JOIN Terceros AS A ON C.IdAbogado=A.IdTercero
 INNER JOIN Terceros AS AX ON C.IdAuxiliar=AX.IdTercero INNER JOIN JurAutoridades AS AU ON C.IdAutoridad=AU.IdAutoridad
 INNER JOIN JurTipos AS TP ON C.IdTipoPro=TP.IdTipoPro
 INNER JOIN JurClaseAcc AS CA ON C.IdClase=CA.IdClase INNER JOIN JurCalifica AS CL ON C.IdCalifica=CL.IdCalifica
 INNER JOIN JurEstados AS E ON C.IdEstado=E.IdEstado INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario
 INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON C.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep
 INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 INNER JOIN Trn_JurCasosHis AS H ON C.TipDoc=H.TipDoc AND C.CasoID=H.CasoID AND C.IdCia=H.IdCia
 INNER JOIN adm_Usuarios AS UH ON H.IdUsuario=UH.IdUsuario 
 LEFT JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo LEFT JOIN TiposCol AS CO ON V.IdColor=CO.IdColor 
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo LEFT JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
 LEFT JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero LEFT JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
 LEFT JOIN TiposFuel AS TC ON  V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR  ON V.IdPpd=TPR.IdPpd
 LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv
 LEFT JOIN TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN  Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal
 LEFT JOIN Rutas AS R ON C.IdRuta=R.IdRuta
 LEFT JOIN VehRemolq AS RQ ON C.NumRemolque=RQ.IdRemque LEFT JOIN TiposCar AS TCR ON RQ.IdCrceria=TCR.IdCrceria
 LEFT JOIN Terceros AS PR ON RQ.IdPropietario=PR.IdTercero 
 WHERE  C.TipDoc=@pmTipDoc
 AND ( C.CasoID>=ISNULL(@pmCasoID,0)  AND  C.CasoID<=ISNULL(@pmCasoID,2147483647))
 AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND (ActualizaID>=ISNULL(@pmActualizaID,0)  AND  ActualizaID<=ISNULL(@pmActualizaID,2147483647))
ORDER BY C.IdCia,C.CasoID,ActualizaID
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryJurCasos_Crr] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPropietario VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null
,@pmIdAbogado VARCHAR(16)=Null,@pmIdAuxiliar VARCHAR(16)=Null,@pmIdAutoridad VARCHAR(4)=Null,@pmIdTipoPro VARCHAR(4)=Null,@pmIdClase VARCHAR(4)=Null,@pmIdCalifica VARCHAR(4)=Null,@pmSinRecorrido BIT=Null
,@pmRadicacion VARCHAR(20)=Null,@pmCasoID INT=Null,@pmCartulina INT=Null,@pmFechaSuc SMALLDATETIME=Null,@pmIdRuta VARCHAR(4)=Null,@pmAnulado BIT=Null,@pmIdEstado VARCHAR(4)=Null
AS 
SELECT CasoID,C.IdCia AS CodCia,C.Fecha,C.Radicacion,C.UndVehiculo,C.IdVehiculo AS PlacaVeh,C.NumRemolque,C.IdPropietario AS NitPropiterio,NP.RazonSocial AS Propietario,C.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,SinRecorrido, TipCar, Cartulina, IdCiaCar, FechaSuc, C.IdRuta AS CodRuta,Ruta
,C.IdAbogado,A.RazonSocial AS Abogado,IdAuxiliar,AX.RazonSocial AS Auxiliar,C.IdAutoridad AS IdAutrdad,NomAutoridad, C.IdTipoPro AS IdTProc,TipoProceso,C.IdClase AS IdClaAcc,ClaseAccdte, C.IdCalifica AS IdCalf,CalificaCaso, LugarAcc, CausaAcc, Comentario, C.Observacion AS Observ
, Contraparte, DirContraparte,TelContraparte,ValDanosCParte, ValDanosEmp, ValDeducible, EmpAsegdora, AuxMutuo, ArchivoCaso, Documntos, Croquis,Reclamaciones,CdCausal,Causal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,C.TipActaJur,C.NumActaJur,C.CiaActaJur,C.EstadoProc
,Anulado,FecDev,C.IdEstado AS IdEstad,E.Estado AS EstadDocm,C.IdUsuario AS IdUsuari,Usuario,Compania,TipDoc,C.IdCiaCrea,C.FecUpdate,C.TimeSys
--Datos del vehículo
,V.NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,V.Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,V.NumMotor,V.SerieChasis,PasjerosPie,PasjerosSen
,V.IdPropietario AS VehNitProp,VNP.RazonSocial AS VehPropietario,V.IdConductor AS VehCedConductor,VNC.RazonSocial AS VehConductor,V.IdPpd AS CdTipProp,TipoProp,TipoAfil,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon
,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdTarifa,TB.Descripcion AS TipoTarifa,V.EdoVincula,V.FecIngreso,V.FecVigencia,V.FecRetiro,NumSoat,FecSoat,VigSoat,KmActual,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,ObligaTProd,V.IdEstado AS VehCdEstado
,EV.Estado AS VehEstado,V.Inactivo AS VehInactivo
--datos del propietario
,NP.Codigo AS PropCodigo,NP.TipoId AS PropTipoId,NP.Dv AS PropDv,NP.Direccion AS PropDireccion,NP.IdLocal AS PropCdCiudad,LP.Localidad AS PropCiudad,LP.IdDep AS PropCdDep
,DP.Departamento AS PropDpato,NP.Telefono AS PropTelefono,NP.Fax AS PropFax,NP.TelMovil AS PropTelMovil
--datos de conductor
,NC.Codigo AS CondCodigo,NC.Direccion AS CondDireccion,NC.IdLocal AS CondCdCiudad,LC.Localidad AS CondCiudad,LC.IdDep AS CondCdDpto,DC.Departamento AS CondDpto
,NC.Telefono AS CondTelefono,NC.Fax AS CondFax,NC.TelMovil AS CondTelMovil,NC.e_mail AS CondEmail,NC.EsPropietario AS CondEspropietario,NC.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,NC.FecExpCed AS CondFechaExpCedula
,CT.IdModd AS CondCdMod,MCD.Modalidad AS ModConductor,TipoSangre,FactorRh,FecNacmto,LugarNacmto,Sexo,CertJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,CT.IdLugar AS CdLugarLic,LugarLic
--Datos remolque
,RQ.Modelo AS RemModelo,RQ.IdCrceria AS RemIdCar,TCR.TipoCar AS RemTipoCar,RQ.NumSerial,RQ.IdPropietario AS RemIdProp,PR.RazonSocial AS RemPropietario,RQ.VrComercial AS RemVrComercial,RQ.VrAvaludo AS RemVrAvaluo,RQ.VrAsegurado AS RemVrAsegurado,RQ.Descripcion AS RemDescripcion
,RQ.Tipo_Afil,RQ.EdoVincula AS RemEdoVincula,RQ.FechaVincula,RQ.FechaRetiro AS RemFecRetiro
 FROM Trn_JurCasos AS C INNER JOIN Terceros AS NP ON C.IdPropietario=NP.IdTercero 
 INNER JOIN Terceros AS NC ON C.IdConductor=NC.IdTercero INNER JOIN Terceros AS A ON C.IdAbogado=A.IdTercero
 INNER JOIN Terceros AS AX ON C.IdAuxiliar=AX.IdTercero INNER JOIN JurAutoridades AS AU ON C.IdAutoridad=AU.IdAutoridad
 INNER JOIN JurTipos AS TP ON C.IdTipoPro=TP.IdTipoPro INNER JOIN JurClaseAcc AS CA ON C.IdClase=CA.IdClase
 INNER JOIN JurCalifica AS CL ON C.IdCalifica=CL.IdCalifica INNER JOIN JurEstados AS E ON C.IdEstado=E.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Localidades AS LP ON NP.IdLocal=LP.IdLocal INNER JOIN Departamentos AS DP ON LP.IdDep=DP.IdDep
 INNER JOIN Localidades AS LC ON NC.IdLocal=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 LEFT JOIN Rutas AS R ON C.IdRuta=R.IdRuta
 LEFT JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo LEFT JOIN TiposCol AS CO ON V.IdColor=CO.IdColor 
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo LEFT JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
 LEFT JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero LEFT JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
 LEFT JOIN TiposFuel AS TC ON  V.IdCom=TC.IdCom LEFT JOIN TiposPpt AS TPR  ON V.IdPpd=TPR.IdPpd
 LEFT JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon LEFT JOIN TiposNivs AS VNS ON V.IdNiv=VNS.IdNiv
 LEFT JOIN  TarifBuses AS TB ON V.CdTarifa =TB.IdTarifa LEFT JOIN TercCndtores AS CT ON C.IdConductor=CT.IdConductor
 LEFT JOIN  Localidades AS LCE ON NC.IdLugarCed=LCE.IdLocal LEFT JOIN TiposMod AS MCD ON CT.IdModd=MCD.IdModd
 LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar LEFT JOIN JurCausales AS CS ON C.CdCausal=CS.IdCausal
 LEFT JOIN VehRemolq AS RQ ON C.NumRemolque=RQ.IdRemque LEFT JOIN TiposCar AS TCR ON RQ.IdCrceria=TCR.IdCrceria
 LEFT JOIN Terceros AS PR ON RQ.IdPropietario=PR.IdTercero 
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  C.IdCia like ISNULL(@pmIdCia,'%%') AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 AND C.IdPropietario LIKE ISNULL(@pmIdPropietario,'%') AND C.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND IdAbogado LIKE ISNULL(@pmIdAbogado,'%')
 AND IdAuxiliar LIKE ISNULL(@pmIdAuxiliar,'%') AND C.IdAutoridad LIKE ISNULL(@pmIdAutoridad,'%') AND C.IdTipoPro LIKE ISNULL(@pmIdTipoPro,'%')
 AND  C.IdClase LIKE ISNULL(@pmIdClase,'%') AND C.IdCalifica LIKE ISNULL(@pmIdCalifica,'%')   AND Radicacion LIKE ISNULL(@pmRadicacion,'%')
 AND (CasoID>=ISNULL(@pmCasoID,0)  AND CasoID<=ISNULL(@pmCasoID,2147483647))
  AND (Cartulina>=ISNULL(@pmCartulina,0)  AND Cartulina<=ISNULL(@pmCartulina,2147483647))
  AND (FechaSuc>=ISNULL(@pmFechaSuc,CAST('19100101' AS SMALLDATETIME)) AND FechaSuc<=ISNULL(@pmFechaSuc,CAST('20781230' AS SMALLDATETIME)))
  AND C.IdRuta LIKE ISNULL(@pmIdRuta,'%') 
 AND  (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
  AND  (SinRecorrido=ISNULL(@pmSinRecorrido,0) or SinRecorrido=ISNULL(@pmSinRecorrido,1))
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY C.IdCia,CasoID
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
 AS 
 INSERT INTO Trn_GatProrratVeh (TipDoc,NumProceso,IdCia,Item,IdAsociado,UndVehiculo,IdVehiculo,TarifaLiq,VrAportes,Importe,VrRetiros,VrAvaluo,TipoBaseLiq,CodTarifa,Observacion,FecLiquida,FecIngreso,FecRetiro,EdoVincula)
 VALUES (@pmTipDoc,@pmNumProceso,@pmIdCia,@pmItem,@pmIdAsociado,@pmUndVehiculo,@pmIdVehiculo,@pmTarifaLiq,@pmVrAportes,@pmImporte,@pmVrRetiros,@pmVrAvaluo,@pmTipoBaseLiq,@pmCodTarifa,@pmObservacion,@pmFecLiquida,@pmFecIngreso,@pmFecRetiro,@pmEdoVincula)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryJurActas] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumProceso,IdCia,Fecha,NumActa,FechaActa,Modalidad,ValorTotal,Referencia,EstadoPror,TipPror,NumPror,IdCiaPror,Anulado,NumDev,FechaDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea
 FROM Trn_JurActas
 WHERE TipDoc=@pmTipDoc AND NumProceso=@pmNumProceso AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsJurActas] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmNumActa VARCHAR(20),@pmFechaActa SMALLDATETIME,@pmModalidad VARCHAR(10),@pmValorTotal MONEY,@pmReferencia VARCHAR(50),@pmEstadoPror INT,@pmTipPror VARCHAR(3),@pmNumPror INT,@pmIdCiaPror CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFechaDev SMALLDATETIME,@pmObservacion VARCHAR(500),@pmIdUsuario VARCHAR(11),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmModuloCrea VARCHAR(10)
 AS 
 INSERT INTO Trn_JurActas (TipDoc,NumProceso,IdCia,Fecha,NumActa,FechaActa,Modalidad,ValorTotal,Referencia,EstadoPror,TipPror,NumPror,IdCiaPror,Anulado,NumDev,FechaDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea)
 VALUES (@pmTipDoc,@pmNumProceso,@pmIdCia,@pmFecha,@pmNumActa,@pmFechaActa,@pmModalidad,@pmValorTotal,@pmReferencia,@pmEstadoPror,@pmTipPror,@pmNumPror,@pmIdCiaPror,@pmAnulado,@pmNumDev,@pmFechaDev,@pmObservacion,@pmIdUsuario,@pmFechaCrea,@pmIdCiaCrea,@pmModuloCrea)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpJurActas] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmNumActa VARCHAR(20),@pmFechaActa SMALLDATETIME,@pmModalidad VARCHAR(10),@pmValorTotal MONEY,@pmReferencia VARCHAR(50),@pmEstadoPror INT,@pmTipPror VARCHAR(3),@pmNumPror INT,@pmIdCiaPror CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFechaDev SMALLDATETIME,@pmObservacion VARCHAR(500)
 AS 
 UPDATE Trn_JurActas SET Fecha=@pmFecha,NumActa=@pmNumActa,FechaActa=@pmFechaActa,Modalidad=@pmModalidad,ValorTotal=@pmValorTotal,Referencia=@pmReferencia,EstadoPror=@pmEstadoPror,TipPror=@pmTipPror,NumPror=@pmNumPror,IdCiaPror=@pmIdCiaPror,Anulado=@pmAnulado,NumDev=@pmNumDev,FechaDev=@pmFechaDev,Observacion=@pmObservacion
 WHERE TipDoc=@pmTipDoc AND NumProceso=@pmNumProceso AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpGatDevAjuPror] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumAjuste INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmModalidad VARCHAR(20),@pmVrDevolucion MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(500)
 AS 
 UPDATE Trn_GatDevAjuPror SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,NumAjuste=@pmNumAjuste,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,Modalidad=@pmModalidad,VrDevolucion=@pmVrDevolucion,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,ModdDev=@pmModdDev,Observacion=@pmObservacion
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevAjuPror] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumAjuste,IdCiaDoc,FecDoc,Modalidad,VrDevolucion,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea
 FROM Trn_GatDevAjuPror
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsGatDevAjuPror] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumAjuste INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmModalidad VARCHAR(20),@pmVrDevolucion MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(500),@pmIdUsuario VARCHAR(11),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmModuloCrea VARCHAR(10)
 AS 
 INSERT INTO Trn_GatDevAjuPror (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumAjuste,IdCiaDoc,FecDoc,Modalidad,VrDevolucion,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmNumAjuste,@pmIdCiaDoc,@pmFecDoc,@pmModalidad,@pmVrDevolucion,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmModdDev,@pmObservacion,@pmIdUsuario,@pmFechaCrea,@pmIdCiaCrea,@pmModuloCrea)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[paInstm_TraLiqCausac_Sel] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2)
 AS 
 INSERT INTO tm_TraLiqCausac (tmNumero,tmItem,tmTipCau,tmCausacion,tmIdCiaCau,tmItemCau,tmFecha,tmIdConcepto,tmDescripcion,tmVrAbono,tmNit,tmVehiculo,tmTipOdp,tmOrdenPago,tmIdCiaOdp
,tmTipMuc,tmManifiesto,tmIdCiaMuc,tmItemFact,tmVrSaldo,tmEsVence,tmCdCuenta,tmCuePagar,tmFactura,tmConcValor,tmConcAbono,tmCdCCosto,tmCdSubCos,tmTipoFac,tmReferencia)
 SELECT @pmtmNumero,D.Item,D.TipCau,D.Causacion,D.IdCiaCau,D.ItemCau,D.FechaCau,D.IdConcepto,D.Descripcion,D.VrAbono,D.NitPoseedor,D.pVehiculo,D.TipOdp,D.OrdenPago,D.IdCiaOdp
,D.TipMuc,D.Manifiesto,D.IdCiaMuc,1,D.VrSaldoOrden,D.EsVence,D.CdCuenta,D.CdCuePagar,D.NumFactura,0,0,D.CdCCosto,D.CdSubCos,D.TipOdp,OP.IdVehiculo
 FROM Trn_TraLiquidaDed AS D LEFT JOIN (SELECT TipDoc,OrdPago,IdCia,IdVehiculo,IdPoseedor FROM Trn_TraOrdenManif
 UNION ALL SELECT TipDoc,Liquidacion,IdCia,IdVehiculo,IdPoseedor FROM Trn_TraOrdenLiq WHERE TipDoc='OPT') AS OP ON D.TipOdp=OP.TipDoc AND D.OrdenPago=OP.OrdPago AND D.IdCiaOdp=OP.IdCia
 WHERE D.TipDoc=@pmTipDoc AND D.Liquidacion=@pmLiquidacion AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsGatDevProrr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmModalidad VARCHAR(10),@pmIdAsociado VARCHAR(16),@pmUndVehiculo CHAR(1),@pmIdVehiculo VARCHAR(10)
,@pmnRemolque VARCHAR(10),@pmVrDevolucion MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(500),@pmIdUsuario VARCHAR(11),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmModuloCrea VARCHAR(10)
 AS 
 INSERT INTO Trn_GatDevProrr (TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumProceso,IdCiaDoc,FecDoc,Modalidad,IdAsociado,UndVehiculo,IdVehiculo,nRemolque,VrDevolucion,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea)
 VALUES (@pmTipDev,@pmDevolucion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipDoc,@pmNumProceso,@pmIdCiaDoc,@pmFecDoc,@pmModalidad,@pmIdAsociado,@pmUndVehiculo,@pmIdVehiculo,@pmnRemolque,@pmVrDevolucion,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmModdDev,@pmObservacion,@pmIdUsuario,@pmFechaCrea,@pmIdCiaCrea,@pmModuloCrea)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpGatDevProrr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCiaDoc CHAR(2),@pmFecDoc SMALLDATETIME,@pmModalidad VARCHAR(10),@pmIdAsociado VARCHAR(16),@pmUndVehiculo CHAR(1),@pmIdVehiculo VARCHAR(10)
,@pmnRemolque VARCHAR(10),@pmVrDevolucion MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmModdDev VARCHAR(10),@pmObservacion VARCHAR(500)
 AS 
 UPDATE Trn_GatDevProrr SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipDoc=@pmTipDoc,NumProceso=@pmNumProceso,IdCiaDoc=@pmIdCiaDoc,FecDoc=@pmFecDoc,Modalidad=@pmModalidad,IdAsociado=@pmIdAsociado,UndVehiculo=@pmUndVehiculo,IdVehiculo=@pmIdVehiculo,nRemolque=@pmnRemolque,VrDevolucion=@pmVrDevolucion
,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,ModdDev=@pmModdDev,Observacion=@pmObservacion
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatDevProrr] @pmTipDev VARCHAR(3),@pmDevolucion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDev,Devolucion,IdCia,Fecha,IdConcepto,TipDoc,NumProceso,IdCiaDoc,FecDoc,Modalidad,IdAsociado,UndVehiculo,IdVehiculo,nRemolque,VrDevolucion,TipCom,Comprobante,IdCiaCom,ModdDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea
 FROM Trn_GatDevProrr
 WHERE TipDev=@pmTipDev AND Devolucion=@pmDevolucion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpJurCasos] @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmRadicacion VARCHAR(20),@pmIdVehiculo VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdConductor VARCHAR(16),@pmSinRecorrido BIT,@pmTipCar VARCHAR(3),@pmCartulina INT,@pmIdCiaCar CHAR(2),@pmFechaSuc SMALLDATETIME,@pmIdRuta VARCHAR(4),@pmIdAbogado VARCHAR(16),@pmIdAuxiliar VARCHAR(16),@pmIdAutoridad VARCHAR(4),@pmIdTipoPro VARCHAR(4),@pmIdClase VARCHAR(4),@pmIdCalifica VARCHAR(4),@pmLugarAcc VARCHAR(100),@pmCausaAcc VARCHAR(3300)
,@pmComentario VARCHAR(3300),@pmObservacion VARCHAR(250),@pmContraparte VARCHAR(100),@pmDirContraparte VARCHAR(100),@pmTelContraparte VARCHAR(20),@pmValDanosCParte MONEY,@pmValDanosEmp MONEY,@pmValDeducible MONEY,@pmEmpAsegdora VARCHAR(100),@pmAuxMutuo BIT,@pmArchivoCaso VARCHAR(100),@pmDocumntos VARCHAR(250),@pmCroquis VARCHAR(20),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmReclamaciones VARCHAR(1000)
,@pmCdCausal VARCHAR(4),@pmCedtraparte VARCHAR(16),@pmPolizaResCivil VARCHAR(30),@pmVigencResCivil SMALLDATETIME,@pmNitEmpAseg VARCHAR(16),@pmNombreAseg VARCHAR(150),@pmUndVehiculo CHAR(1),@pmNumRemolque VARCHAR(10),@pmTipActaJur VARCHAR(3),@pmNumActaJur INT,@pmCiaActaJur CHAR(2),@pmEstadoProc INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_JurCasos SET Fecha=@pmFecha,Radicacion=@pmRadicacion,IdVehiculo=@pmIdVehiculo,IdPropietario=@pmIdPropietario,IdConductor=@pmIdConductor,SinRecorrido=@pmSinRecorrido,TipCar=@pmTipCar,Cartulina=@pmCartulina,IdCiaCar=@pmIdCiaCar,FechaSuc=@pmFechaSuc,IdRuta=@pmIdRuta,IdAbogado=@pmIdAbogado,IdAuxiliar=@pmIdAuxiliar,IdAutoridad=@pmIdAutoridad,IdTipoPro=@pmIdTipoPro,IdClase=@pmIdClase,IdCalifica=@pmIdCalifica,LugarAcc=@pmLugarAcc,CausaAcc=@pmCausaAcc,Comentario=@pmComentario,Observacion=@pmObservacion,Contraparte=@pmContraparte,DirContraparte=@pmDirContraparte
,TelContraparte=@pmTelContraparte,ValDanosCParte=@pmValDanosCParte,ValDanosEmp=@pmValDanosEmp,ValDeducible=@pmValDeducible,EmpAsegdora=@pmEmpAsegdora,AuxMutuo=@pmAuxMutuo,ArchivoCaso=@pmArchivoCaso,Documntos=@pmDocumntos,Croquis=@pmCroquis,Anulado=@pmAnulado,FecDev=@pmFecDev,Reclamaciones=@pmReclamaciones,IdEstado=@pmIdEstado,UndVehiculo=@pmUndVehiculo,NumRemolque=@pmNumRemolque,TipActaJur=@pmTipActaJur,NumActaJur=@pmNumActaJur,CiaActaJur=@pmCiaActaJur,EstadoProc=@pmEstadoProc,FecUpdate=@pmFecUpdate
,CdCausal=@pmCdCausal,Cedtraparte=@pmCedtraparte,PolizaResCivil=@pmPolizaResCivil,VigencResCivil=@pmVigencResCivil,NitEmpAseg=@pmNitEmpAseg,NombreAseg=@pmNombreAseg
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryJurCasos] @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,CasoID,IdCia,Fecha,Radicacion,IdVehiculo,IdPropietario,IdConductor,SinRecorrido,TipCar,Cartulina,IdCiaCar,FechaSuc,IdRuta,IdAbogado,IdAuxiliar,IdAutoridad,IdTipoPro,IdClase,IdCalifica,LugarAcc,CausaAcc
,Comentario,Observacion,Contraparte,DirContraparte,TelContraparte,ValDanosCParte,ValDanosEmp,ValDeducible,EmpAsegdora,AuxMutuo,ArchivoCaso,Documntos,Croquis,Anulado,FecDev,IdEstado,Reclamaciones
,CdCausal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,UndVehiculo,NumRemolque,TipActaJur,NumActaJur,CiaActaJur,EstadoProc,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_JurCasos
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsJurCasos] @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmRadicacion VARCHAR(20),@pmIdVehiculo VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdConductor VARCHAR(16),@pmSinRecorrido BIT,@pmTipCar VARCHAR(3)
,@pmCartulina INT,@pmIdCiaCar CHAR(2),@pmFechaSuc SMALLDATETIME,@pmIdRuta VARCHAR(4),@pmIdAbogado VARCHAR(16),@pmIdAuxiliar VARCHAR(16),@pmIdAutoridad VARCHAR(4),@pmIdTipoPro VARCHAR(4),@pmIdClase VARCHAR(4),@pmIdCalifica VARCHAR(4),@pmLugarAcc VARCHAR(100)
,@pmCausaAcc VARCHAR(3300),@pmComentario VARCHAR(3300),@pmObservacion VARCHAR(250),@pmContraparte VARCHAR(100),@pmDirContraparte VARCHAR(100),@pmTelContraparte VARCHAR(20),@pmValDanosCParte MONEY,@pmValDanosEmp MONEY,@pmValDeducible MONEY
,@pmEmpAsegdora VARCHAR(100),@pmAuxMutuo BIT,@pmArchivoCaso VARCHAR(100),@pmDocumntos VARCHAR(250),@pmCroquis VARCHAR(20),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmReclamaciones VARCHAR(1000)
,@pmCdCausal VARCHAR(4),@pmCedtraparte VARCHAR(16),@pmPolizaResCivil VARCHAR(30),@pmVigencResCivil SMALLDATETIME,@pmNitEmpAseg VARCHAR(16),@pmNombreAseg VARCHAR(150)
,@pmUndVehiculo CHAR(1),@pmNumRemolque VARCHAR(10),@pmTipActaJur VARCHAR(3),@pmNumActaJur INT,@pmCiaActaJur CHAR(2),@pmEstadoProc INT,@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_JurCasos (TipDoc,CasoID,IdCia,Fecha,Radicacion,IdVehiculo,IdPropietario,IdConductor,SinRecorrido,TipCar,Cartulina,IdCiaCar,FechaSuc,IdRuta,IdAbogado,IdAuxiliar,IdAutoridad,IdTipoPro,IdClase,IdCalifica,LugarAcc,CausaAcc,Comentario,Observacion,Contraparte,DirContraparte,TelContraparte
,ValDanosCParte,ValDanosEmp,ValDeducible,EmpAsegdora,AuxMutuo,ArchivoCaso,Documntos,Croquis,Anulado,FecDev,IdEstado,TimeSys,IdCiaCrea,IdUsuario,Reclamaciones,CdCausal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,UndVehiculo,NumRemolque,TipActaJur,NumActaJur,CiaActaJur,EstadoProc)
 VALUES (@pmTipDoc,@pmCasoID,@pmIdCia,@pmFecha,@pmRadicacion,@pmIdVehiculo,@pmIdPropietario,@pmIdConductor,@pmSinRecorrido,@pmTipCar,@pmCartulina,@pmIdCiaCar,@pmFechaSuc,@pmIdRuta,@pmIdAbogado,@pmIdAuxiliar,@pmIdAutoridad,@pmIdTipoPro,@pmIdClase,@pmIdCalifica
,@pmLugarAcc,@pmCausaAcc,@pmComentario,@pmObservacion,@pmContraparte,@pmDirContraparte,@pmTelContraparte,@pmValDanosCParte,@pmValDanosEmp,@pmValDeducible,@pmEmpAsegdora,@pmAuxMutuo,@pmArchivoCaso,@pmDocumntos,@pmCroquis,@pmAnulado,@pmFecDev,@pmIdEstado
,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmReclamaciones,@pmCdCausal,@pmCedtraparte,@pmPolizaResCivil,@pmVigencResCivil,@pmNitEmpAseg,@pmNombreAseg,@pmUndVehiculo,@pmNumRemolque,@pmTipActaJur,@pmNumActaJur,@pmCiaActaJur,@pmEstadoProc)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_TraLiqCausac] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmTipCau VARCHAR(3),@pmtmCausacion INT,@pmtmIdCiaCau CHAR(2),@pmtmItemCau INT,@pmtmFecha SMALLDATETIME,@pmtmIdConcepto VARCHAR(4),@pmtmDescripcion VARCHAR(250)
,@pmtmVrAbono MONEY,@pmtmNit VARCHAR(16),@pmtmVehiculo VARCHAR(10),@pmtmTipOdp VARCHAR(3),@pmtmOrdenPago INT,@pmtmIdCiaOdp CHAR(2),@pmtmTipMuc VARCHAR(3),@pmtmManifiesto INT,@pmtmIdCiaMuc CHAR(2),@pmtmItemFact INT,@pmtmVrSaldo MONEY
,@pmtmEsVence BIT,@pmtmCdCuenta VARCHAR(16),@pmtmCuePagar VARCHAR(16),@pmtmFactura VARCHAR(20),@pmtmConcValor MONEY,@pmtmConcAbono MONEY,@pmtmCdCCosto VARCHAR(16),@pmtmCdSubCos VARCHAR(16),@pmtmTipoFac VARCHAR(3),@pmtmReferencia VARCHAR(50)
 AS 
 INSERT INTO tm_TraLiqCausac (tmNumero,tmItem,tmTipCau,tmCausacion,tmIdCiaCau,tmItemCau,tmFecha,tmIdConcepto,tmDescripcion,tmVrAbono,tmNit,tmVehiculo,tmTipOdp,tmOrdenPago,tmIdCiaOdp,tmTipMuc,tmManifiesto,tmIdCiaMuc
,tmItemFact,tmVrSaldo,tmEsVence,tmCdCuenta,tmCuePagar,tmFactura,tmConcValor,tmConcAbono,tmCdCCosto,tmCdSubCos,tmTipoFac,tmReferencia)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmTipCau,@pmtmCausacion,@pmtmIdCiaCau,@pmtmItemCau,@pmtmFecha,@pmtmIdConcepto,@pmtmDescripcion,@pmtmVrAbono,@pmtmNit,@pmtmVehiculo,@pmtmTipOdp,@pmtmOrdenPago,@pmtmIdCiaOdp
,@pmtmTipMuc,@pmtmManifiesto,@pmtmIdCiaMuc,@pmtmItemFact,@pmtmVrSaldo,@pmtmEsVence,@pmtmCdCuenta,@pmtmCuePagar,@pmtmFactura,@pmtmConcValor,@pmtmConcAbono,@pmtmCdCCosto,@pmtmCdSubCos,@pmtmTipoFac,@pmtmReferencia)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsAcuAportesSan] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT,@pmIdCia CHAR(2)=Null,@pmIdAsociado VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
AS
 INSERT INTO AcuAportes (nAnno,nMes,IdCia,IdAsociado,UndVehiculo,IdVehiculo,Concepto,SaldoAnt,VrAporte,VrDeduccion)
 SELECT @pmnAnno,@pmnMes,A.IdCia,A.IdAsociado,A.UndVehiculo,A.IdVehiculo,A.Concepto,A.SaldoAnt+A.VrAporte-A.VrDeduccion,0,0
 FROM AcuAportes AS A WHERE A.nAnno=@pmAnnoAnt AND A.nMes=@pmMesAnt
 AND NOT EXISTS (SELECT * FROM AcuAportes AS AP WHERE AP.nAnno=@pmnAnno AND AP.nMes=@pmnMes  
 AND AP.IdCia=A.IdCia AND AP.IdAsociado=A.IdAsociado AND AP.UndVehiculo=A.UndVehiculo AND AP.IdVehiculo=A.IdVehiculo AND AP.Concepto=A.Concepto)
 AND (@pmIdCia IS NULL OR A.IdCia=@pmIdCia) AND (@pmIdAsociado IS NULL OR A.IdAsociado=@pmIdAsociado) AND (@pmIdVehiculo IS NULL OR A.IdVehiculo=@pmIdVehiculo)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpAcuAportesSan] @pmnAnno INT,@pmnMes INT,@pmAnnoAnt INT,@pmMesAnt INT,@pmIdCia CHAR(2)=Null,@pmIdAsociado VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
AS
 UPDATE AcuAportes SET AcuAportes.SaldoAnt=ISNULL(SA.NuevoSaldo,0)
 FROM AcuAportes INNER JOIN (SELECT nAnno,nMes,IdCia,IdAsociado,UndVehiculo,IdVehiculo,Concepto,SaldoAnt+VrAporte-VrDeduccion AS NuevoSaldo
 FROM AcuAportes WHERE nAnno=@pmAnnoAnt AND nMes=@pmMesAnt) AS SA ON AcuAportes.IdCia=SA.IdCia AND AcuAportes.IdAsociado=SA.IdAsociado
 AND AcuAportes.UndVehiculo=SA.UndVehiculo AND AcuAportes.IdVehiculo=SA.IdVehiculo AND AcuAportes.Concepto=SA.Concepto
 WHERE AcuAportes.nAnno=@pmnAnno AND AcuAportes.nMes=@pmnMes
 AND (@pmIdCia IS NULL OR AcuAportes.IdCia=@pmIdCia) AND (@pmIdAsociado IS NULL OR AcuAportes.IdAsociado=@pmIdAsociado) 
 AND (@pmIdVehiculo IS NULL OR AcuAportes.IdVehiculo=@pmIdVehiculo)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Diego Romero>
-- Create date: <7/11/2025>
-- Description:	<Saldo Anterior de Aportes>
-- =============================================
CREATE FUNCTION [dbo].[FuncAcuAportesSan] (@pmAnnoAct INT,@pmMesAct INT,@pmIdCia CHAR(2),@pmIdAsociado VARCHAR(16),@pmIdVehiculo VARCHAR(10))
RETURNS MONEY
 AS  
BEGIN
 DECLARE @SaldoAnt MONEY, @nAnno INT, @nMes INT;
 IF @pmMesAct=1
  BEGIN
   SET @nMes=12;
   SET @nAnno=@pmAnnoAct-1;
  END
 ELSE
  BEGIN
   SET @nMes=@pmMesAct-1;
   SET @nAnno=@pmAnnoAct;
  END
 SET  @SaldoAnt=(SELECT SUM(CASE WHEN Concepto IN ('VINCULACION','GARANTIA','TRASLADO') THEN SaldoAnt+VrDeduccion-VrAporte ELSE SaldoAnt+VrAporte-VrDeduccion END) 
 FROM AcuAportes WHERE nAnno=@nAnno AND nMes=@nMes AND IdAsociado=@pmIdAsociado AND (@pmIdVehiculo IS NULL OR IdVehiculo=@pmIdVehiculo)
 AND (@pmIdCia IS NULL OR IdCia=@pmIdCia))
 RETURN @SaldoAnt
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpGatAjustesProrDev] @pmTipDoc VARCHAR(3),@pmNumAjuste INT,@pmIdCia CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFechaDev SMALLDATETIME,@pmObservacion VARCHAR(500)
 AS 
 UPDATE Trn_GatAjustesPror SET Anulado=@pmAnulado,NumDev=@pmNumDev,FechaDev=@pmFechaDev,Observacion=@pmObservacion
 WHERE TipDoc=@pmTipDoc AND NumAjuste=@pmNumAjuste AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsGatAjustesPror] @pmTipDoc VARCHAR(3),@pmNumAjuste INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmModalidad VARCHAR(20),@pmTipPror VARCHAR(3),@pmNumProceso INT,@pmIdCiaPror CHAR(2),@pmVrAjustes MONEY,@pmReferencia VARCHAR(50),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmAnulado BIT,@pmNumDev INT,@pmFechaDev SMALLDATETIME,@pmObservacion VARCHAR(500),@pmIdUsuario VARCHAR(11),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmModuloCrea VARCHAR(10)
 AS 
 INSERT INTO Trn_GatAjustesPror (TipDoc,NumAjuste,IdCia,Fecha,IdConcepto,Modalidad,TipPror,NumProceso,IdCiaPror,VrAjustes,Referencia,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FechaDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea)
 VALUES (@pmTipDoc,@pmNumAjuste,@pmIdCia,@pmFecha,@pmIdConcepto,@pmModalidad,@pmTipPror,@pmNumProceso,@pmIdCiaPror,@pmVrAjustes,@pmReferencia,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFechaDev,@pmObservacion,@pmIdUsuario,@pmFechaCrea,@pmIdCiaCrea,@pmModuloCrea)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpGatAjustesPror] @pmTipDoc VARCHAR(3),@pmNumAjuste INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmModalidad VARCHAR(20),@pmTipPror VARCHAR(3),@pmNumProceso INT,@pmIdCiaPror CHAR(2),@pmVrAjustes MONEY,@pmReferencia VARCHAR(50),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmAnulado BIT,@pmNumDev INT,@pmFechaDev SMALLDATETIME,@pmObservacion VARCHAR(500)
 AS 
 UPDATE Trn_GatAjustesPror SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,Modalidad=@pmModalidad,TipPror=@pmTipPror,NumProceso=@pmNumProceso,IdCiaPror=@pmIdCiaPror,VrAjustes=@pmVrAjustes,Referencia=@pmReferencia,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FechaDev=@pmFechaDev,Observacion=@pmObservacion
 WHERE TipDoc=@pmTipDoc AND NumAjuste=@pmNumAjuste AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatAjustesPror] @pmTipDoc VARCHAR(3),@pmNumAjuste INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumAjuste,IdCia,Fecha,IdConcepto,Modalidad,TipPror,NumProceso,IdCiaPror,VrAjustes,Referencia,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FechaDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea
 FROM Trn_GatAjustesPror
 WHERE TipDoc=@pmTipDoc AND NumAjuste=@pmNumAjuste AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryGatProrrateos] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumProceso,IdCia,Fecha,IdConcepto,Modalidad,TipDocActa,NumDocActa,IdCiaActa,FecSuceso,IdAsociado,UndVehiculo,IdVehiculo,nRemolque,VrAprobado,VrAvaluo,TipoAfiVeh,Referencia,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FechaDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea
 FROM Trn_GatProrrateos
 WHERE TipDoc=@pmTipDoc AND NumProceso=@pmNumProceso AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsGatProrrateos] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmModalidad VARCHAR(10),@pmTipDocActa VARCHAR(3),@pmNumDocActa INT,@pmIdCiaActa CHAR(2),@pmFecSuceso SMALLDATETIME,@pmIdAsociado VARCHAR(16),@pmUndVehiculo CHAR(1),@pmIdVehiculo VARCHAR(10),@pmnRemolque VARCHAR(10),@pmVrAprobado MONEY,@pmVrAvaluo MONEY,@pmTipoAfiVeh VARCHAR(10),@pmReferencia VARCHAR(50)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFechaDev SMALLDATETIME,@pmObservacion VARCHAR(500),@pmIdUsuario VARCHAR(11),@pmFechaCrea SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmModuloCrea VARCHAR(10)
 AS 
 INSERT INTO Trn_GatProrrateos (TipDoc,NumProceso,IdCia,Fecha,IdConcepto,Modalidad,TipDocActa,NumDocActa,IdCiaActa,FecSuceso,IdAsociado,UndVehiculo,IdVehiculo,nRemolque,VrAprobado,VrAvaluo,TipoAfiVeh,Referencia,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FechaDev,Observacion,IdUsuario,FechaCrea,IdCiaCrea,ModuloCrea)
 VALUES (@pmTipDoc,@pmNumProceso,@pmIdCia,@pmFecha,@pmIdConcepto,@pmModalidad,@pmTipDocActa,@pmNumDocActa,@pmIdCiaActa,@pmFecSuceso,@pmIdAsociado,@pmUndVehiculo,@pmIdVehiculo,@pmnRemolque,@pmVrAprobado,@pmVrAvaluo,@pmTipoAfiVeh,@pmReferencia,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFechaDev,@pmObservacion,@pmIdUsuario,@pmFechaCrea,@pmIdCiaCrea,@pmModuloCrea)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpGatProrrateos] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmModalidad VARCHAR(10),@pmTipDocActa VARCHAR(3),@pmNumDocActa INT,@pmIdCiaActa CHAR(2),@pmFecSuceso SMALLDATETIME,@pmIdAsociado VARCHAR(16),@pmUndVehiculo CHAR(1),@pmIdVehiculo VARCHAR(10),@pmnRemolque VARCHAR(10),@pmVrAprobado MONEY,@pmVrAvaluo MONEY,@pmTipoAfiVeh VARCHAR(10),@pmReferencia VARCHAR(50)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFechaDev SMALLDATETIME,@pmObservacion VARCHAR(500)
 AS 
 UPDATE Trn_GatProrrateos SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,Modalidad=@pmModalidad,TipDocActa=@pmTipDocActa,NumDocActa=@pmNumDocActa,IdCiaActa=@pmIdCiaActa,FecSuceso=@pmFecSuceso,IdAsociado=@pmIdAsociado,UndVehiculo=@pmUndVehiculo,IdVehiculo=@pmIdVehiculo,nRemolque=@pmnRemolque,VrAprobado=@pmVrAprobado,VrAvaluo=@pmVrAvaluo,TipoAfiVeh=@pmTipoAfiVeh,Referencia=@pmReferencia,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FechaDev=@pmFechaDev,Observacion=@pmObservacion
 WHERE TipDoc=@pmTipDoc AND NumProceso=@pmNumProceso AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpGatProrrateosDev] @pmTipDoc VARCHAR(3),@pmNumProceso INT,@pmIdCia CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFechaDev SMALLDATETIME,@pmObservacion VARCHAR(500)
 AS 
 UPDATE Trn_GatProrrateos SET Anulado=@pmAnulado,NumDev=@pmNumDev,FechaDev=@pmFechaDev,Observacion=@pmObservacion
 WHERE TipDoc=@pmTipDoc AND NumProceso=@pmNumProceso AND IdCia=@pmIdCia
GO