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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsDisciplinario]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsDisciplinario]
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].[paInsJurCasosHis]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsJurCasosHis]
GO

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

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

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryDisciplinarioLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryDisciplinarioLta]
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].[paQryJurCasosHis]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryJurCasosHis]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryJurCasosHisLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryJurCasosHisLta]
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].[paQryJurCasosPen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryJurCasosPen]
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpDisciplinario]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpDisciplinario]
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 dbo.sysobjects where id = object_id(N'[dbo].[paUpJurCasosCpt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpJurCasosCpt]
GO

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

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

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelJurCausales @pmIdCausal VARCHAR(4)
 AS 
 DELETE FROM JurCausales
 WHERE IdCausal=@pmIdCausal

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paDelJurRecOfrecmto @pmTipDoc VARCHAR(3),@pmReclamac INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_JurRecOfrecmto
 WHERE TipDoc=@pmTipDoc AND Reclamac=@pmReclamac AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelJurReclamac @pmTipDoc VARCHAR(3),@pmReclamac INT,@pmIdCia CHAR(2)
 AS 
 DELETE FROM Trn_JurReclamac
 WHERE TipDoc=@pmTipDoc AND Reclamac=@pmReclamac AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paDelJurTiposRec @pmIdTipoRec VARCHAR(4)
 AS 
 DELETE FROM JurTiposRec
 WHERE IdTipoRec=@pmIdTipoRec

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsDisciplinario  @pmTipDoc VARCHAR(3),@pmNumero INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecSuceso SMALLDATETIME,@pmIdVehiculo VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdConductor VARCHAR(16),@pmIdRuta VARCHAR(4),@pmLugar VARCHAR(150),@pmNomReporta VARCHAR(150)
,@pmCedReporta VARCHAR(16),@pmVehiculoRep VARCHAR(10),@pmRepCond BIT,@pmCoordinador VARCHAR(150),@pmDespachador VARCHAR(150),@pmCitaDescargos BIT,@pmFecCita SMALLDATETIME,@pmMemoCita VARCHAR(50),@pmImputacion VARCHAR(2500),@pmDescargos VARCHAR(3000),@pmFecDesc SMALLDATETIME,@pmRecibeDesc VARCHAR(150)
,@pmTipoSancion VARCHAR(10),@pmDescSancion VARCHAR(1000),@pmFecSancion SMALLDATETIME,@pmCantSancion DECIMAL(14,4),@pmCodInfracc INT,@pmReferencia VARCHAR(50),@pmObservacion VARCHAR(250),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmTipoInf VARCHAR(10)
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Disciplinario (TipDoc,Numero,IdCia,Fecha,FecSuceso,IdVehiculo,IdPropietario,IdConductor,IdRuta,TipoInf,Lugar,NomReporta,CedReporta,VehiculoRep,RepCond,Coordinador,Despachador,CitaDescargos,FecCita,MemoCita,Imputacion,Descargos,FecDesc,RecibeDesc,TipoSancion,DescSancion,FecSancion,CantSancion,CodInfracc,Referencia,Observacion,Anulado,NumDev,FecDev,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmNumero,@pmIdCia,@pmFecha,@pmFecSuceso,@pmIdVehiculo,@pmIdPropietario,@pmIdConductor,@pmIdRuta,@pmTipoInf,@pmLugar,@pmNomReporta,@pmCedReporta,@pmVehiculoRep,@pmRepCond,@pmCoordinador,@pmDespachador,@pmCitaDescargos,@pmFecCita,@pmMemoCita,@pmImputacion,@pmDescargos,@pmFecDesc,@pmRecibeDesc,@pmTipoSancion
,@pmDescSancion,@pmFecSancion,@pmCantSancion,@pmCodInfracc,@pmReferencia,@pmObservacion,@pmAnulado,@pmNumDev,@pmFecDev,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE 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),@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)
 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)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paInsJurCasosHis @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT,@pmFechaUp SMALLDATETIME,@pmHistorial VARCHAR(3500),@pmEstadoAct VARCHAR(3500)
,@pmCierreProc BIT,@pmArchivoProc VARCHAR(100),@pmNombreCparte VARCHAR(100),@pmDirCparte VARCHAR(100),@pmTelCparte VARCHAR(20),@pmDanosCParte MONEY,@pmDanosEmp MONEY
,@pmValorDeducible MONEY,@pmAseguradora VARCHAR(100),@pmAuxilioMutuo BIT,@pmIdEstado VARCHAR(4),@pmIdUsuario VARCHAR(11),@pmCedContparte VARCHAR(16)
 AS 
 INSERT INTO Trn_JurCasosHis (TipDoc,CasoID,IdCia,ActualizaID,FechaUp,Historial,EstadoAct,CierreProc,ArchivoProc,NombreCparte,DirCparte,TelCparte,DanosCParte,DanosEmp,ValorDeducible,Aseguradora,AuxilioMutuo,IdEstado,IdUsuario,CedContparte)
 VALUES (@pmTipDoc,@pmCasoID,@pmIdCia,@pmActualizaID,@pmFechaUp,@pmHistorial,@pmEstadoAct,@pmCierreProc,@pmArchivoProc,@pmNombreCparte,@pmDirCparte,@pmTelCparte,@pmDanosCParte,@pmDanosEmp
,@pmValorDeducible,@pmAseguradora,@pmAuxilioMutuo,@pmIdEstado,@pmIdUsuario,@pmCedContparte)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsJurCausales @pmIdCausal VARCHAR(4),@pmCausal VARCHAR(50),@pmIdTipoPro VARCHAR(4),@pmInactivo BIT
 AS 
 INSERT INTO JurCausales (IdCausal,Causal,IdTipoPro,Inactivo)
 VALUES (@pmIdCausal,@pmCausal,@pmIdTipoPro,@pmInactivo)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsJurInvestiga @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmRadicacion VARCHAR(20),@pmIdAbogado VARCHAR(16),@pmIdAuxiliar VARCHAR(16),@pmIdAutoridad VARCHAR(4),@pmIdEmpresa VARCHAR(16)
,@pmObservacion VARCHAR(250),@pmContraparte VARCHAR(100),@pmDirContraparte VARCHAR(100),@pmTelContraparte VARCHAR(20),@pmArchivoCaso VARCHAR(100),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmCedtraparte VARCHAR(16),@pmCdTipoPro VARCHAR(4)
,@pmCdCausal VARCHAR(4),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_JurInvestiga (TipDoc,CasoID,IdCia,Fecha,Radicacion,IdAbogado,IdAuxiliar,IdAutoridad,IdEmpresa,Observacion,Contraparte,DirContraparte,TelContraparte,ArchivoCaso,Anulado,FecDev,IdEstado,TimeSys,IdCiaCrea,IdUsuario,Cedtraparte,CdTipoPro,CdCausal)
 VALUES (@pmTipDoc,@pmCasoID,@pmIdCia,@pmFecha,@pmRadicacion,@pmIdAbogado,@pmIdAuxiliar,@pmIdAutoridad,@pmIdEmpresa,@pmObservacion,@pmContraparte,@pmDirContraparte,@pmTelContraparte,@pmArchivoCaso,@pmAnulado,@pmFecDev
,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmCedtraparte,@pmCdTipoPro,@pmCdCausal)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsJurInvesHis @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT,@pmFechaUp SMALLDATETIME,@pmHistorial VARCHAR(3000),@pmEstadoAct VARCHAR(3000),@pmPruebas VARCHAR(1200)
,@pmDocumntos VARCHAR(250),@pmCierreProc BIT,@pmArchivoProc VARCHAR(100),@pmNombreCparte VARCHAR(100),@pmDirCparte VARCHAR(100),@pmTelCparte VARCHAR(20),@pmIdEstado VARCHAR(4),@pmCedContparte VARCHAR(16),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_JurInvesHis (TipDoc,CasoID,IdCia,ActualizaID,FechaUp,Historial,EstadoAct,Pruebas,Documntos,CierreProc,ArchivoProc,NombreCparte,DirCparte,TelCparte,IdEstado,IdUsuario,CedContparte)
 VALUES (@pmTipDoc,@pmCasoID,@pmIdCia,@pmActualizaID,@pmFechaUp,@pmHistorial,@pmEstadoAct,@pmPruebas,@pmDocumntos,@pmCierreProc,@pmArchivoProc,@pmNombreCparte,@pmDirCparte,@pmTelCparte,@pmIdEstado,@pmIdUsuario,@pmCedContparte)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsJurReclamac @pmTipDoc VARCHAR(3),@pmReclamac INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaRec SMALLDATETIME,@pmTipSin VARCHAR(3),@pmSiniestro INT,@pmIdCiaSin CHAR(2)
,@pmIdReclamnte VARCHAR(16),@pmIdTipoRec VARCHAR(4),@pmVrReclamac MONEY,@pmObservacion VARCHAR(1000),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_JurReclamac (TipDoc,Reclamac,IdCia,Fecha,FechaRec,TipSin,Siniestro,IdCiaSin,IdReclamnte,IdTipoRec,VrReclamac,Observacion,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmReclamac,@pmIdCia,@pmFecha,@pmFechaRec,@pmTipSin,@pmSiniestro,@pmIdCiaSin,@pmIdReclamnte,@pmIdTipoRec,@pmVrReclamac,@pmObservacion,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsJurRecOfrecmto @pmTipDoc VARCHAR(3),@pmReclamac INT,@pmIdCia CHAR(2),@pmItem INT,@pmFechaOfrec SMALLDATETIME,@pmVrOfrecmto MONEY,@pmOfcAceptado BIT,@pmFecAceptado SMALLDATETIME
,@pmOfcPagado BIT,@pmNumSolGiro VARCHAR(30),@pmComentarios VARCHAR(1000),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_JurRecOfrecmto (TipDoc,Reclamac,IdCia,Item,FechaOfrec,VrOfrecmto,OfcAceptado,FecAceptado,OfcPagado,NumSolGiro,Comentarios,TimeSys,IdUsuario)
 VALUES (@pmTipDoc,@pmReclamac,@pmIdCia,@pmItem,@pmFechaOfrec,@pmVrOfrecmto,@pmOfcAceptado,@pmFecAceptado,@pmOfcPagado,@pmNumSolGiro,@pmComentarios,@pmTimeSys,@pmIdUsuario)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paInsJurTiposRec @pmIdTipoRec VARCHAR(4),@pmTipReclamante VARCHAR(50),@pmInactivo BIT
 AS 
 INSERT INTO JurTiposRec (IdTipoRec,TipReclamante,Inactivo)
 VALUES (@pmIdTipoRec,@pmTipReclamante,@pmInactivo)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryDisciplinario @pmTipDoc VARCHAR(3),@pmNumero INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Numero,IdCia,Fecha,FecSuceso,IdVehiculo,IdPropietario,IdConductor,IdRuta,Lugar,NomReporta,CedReporta,VehiculoRep,RepCond
,Coordinador,Despachador,CitaDescargos,FecCita,MemoCita,Imputacion,Descargos,FecDesc,RecibeDesc,TipoSancion,DescSancion,FecSancion,CantSancion
,CodInfracc,Referencia,Observacion,Anulado,NumDev,FecDev,IdEstado,TipoInf,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Disciplinario
 WHERE TipDoc=@pmTipDoc AND Numero=@pmNumero 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 paQryDisciplinarioFmt @pmTipDoc VARCHAR(3),@pmNumeroIni INT,@pmNumeroFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.Numero AS NumInf,D.IdCia AS CdCia,Compania,Fecha,FecSuceso,D.IdVehiculo AS PlacaVeh,NumVeh,D.IdPropietario AS NitPropietario,P.RazonSocial AS Propietario
,D.IdConductor AS CedConductor,C.RazonSocial AS Conductor,D.IdRuta AS CdRuta,Ruta,Lugar,NomReporta,CedReporta,VehiculoRep,RepCond
,Coordinador,Despachador,CitaDescargos,FecCita,MemoCita,Imputacion,Descargos,FecDesc,RecibeDesc,TipoSancion,DescSancion,FecSancion,CantSancion
,CodInfracc,I.Infraccion AS NomInfracc,Referencia,D.Observacion AS Observ,TipoInf,Anulado,NumDev,FecDev,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
,Item,DT.Infraccion AS DescInfracc
--datos del vehículo
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo
,C.Codigo AS CondCodigo,C.Direccion AS CondDireccion,C.IdLocal AS CondCdCiudad,L.Localidad AS CondCiudad,L.IdDep AS CondCdDpto,DP.Departamento AS CondDpto
,C.Telefono AS CondTelefono,C.Fax AS CondFax,C.TelMovil AS CondTelMovil,C.e_mail AS CondEmail,C.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,C.FecExpCed AS CondFechaExpCedula
,CT.IdModd AS CondCdMod,MC.Modalidad AS ModConductor,CT.CertJudicial AS CondCertJudicial,CT.VigJudicial AS CondVigJudicial,CT.Licencia AS CondLicencia
,CT.CatLicencia AS CondCategoria,CT.VigLicencia AS CondVigLicencia,CT.IdLugar AS CdLugarLic,LugarLic
 FROM Trn_Disciplinario AS D INNER JOIN Companias  AS CI ON D.IdCia=CI.IdCia
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN Terceros AS P ON D.IdPropietario=P.IdTercero
 INNER JOIN Terceros AS C ON D.IdConductor=C.IdTercero INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Trn_DisInfraccion AS DT ON D.TipDoc=DT.TipDoc   AND  D.Numero=DT.Numero  AND  D.IdCia=DT.IdCia 
 INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor
 INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 LEFT JOIN Infracciones AS I ON D.CodInfracc=I.NumInf LEFT JOIN Rutas AS R ON D.IdRuta=R.IdRuta
 LEFT JOIN  Localidades AS LCE ON C.IdLugarCed=LCE.IdLocal
 LEFT JOIN TercCndtores AS CT ON D.IdConductor=CT.IdConductor  LEFT JOIN TiposMod AS MC ON CT.IdModd=MC.IdModd
 LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar
 WHERE D.TipDoc=@pmTipDoc AND D.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin  AND D.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 paQryDisciplinarioLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT D.Numero AS NumInf,D.IdCia AS CdCia,Compania,Fecha,FecSuceso,D.IdVehiculo AS PlacaVeh,NumVeh,D.IdPropietario AS NitPropietario,P.RazonSocial AS Propietario
,D.IdConductor AS CedConductor,C.RazonSocial AS Conductor,D.IdRuta AS CdRuta,Ruta,Lugar,NomReporta,CedReporta,VehiculoRep,RepCond
,Coordinador,Despachador,CitaDescargos,FecCita,MemoCita,Imputacion,Descargos,FecDesc,RecibeDesc,TipoSancion,DescSancion,FecSancion,CantSancion
,CodInfracc,I.Infraccion AS NomInfracc,Referencia,D.Observacion AS Observ,TipoInf,Anulado,NumDev,FecDev,D.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,D.IdUsuario AS CdUsuario,Usuario
--datos del vehículo
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo
,C.Codigo AS CondCodigo,C.Direccion AS CondDireccion,C.IdLocal AS CondCdCiudad,L.Localidad AS CondCiudad,L.IdDep AS CondCdDpto,DP.Departamento AS CondDpto
,C.Telefono AS CondTelefono,C.Fax AS CondFax,C.TelMovil AS CondTelMovil,C.e_mail AS CondEmail,C.IdLugarCed AS CondCdLugarCed,LCE.Localidad AS CondLugarCed,C.FecExpCed AS CondFechaExpCedula
,CT.IdModd AS CondCdMod,MC.Modalidad AS ModConductor,CT.CertJudicial AS CondCertJudicial,CT.VigJudicial AS CondVigJudicial,CT.Licencia AS CondLicencia
,CT.CatLicencia AS CondCategoria,CT.VigLicencia AS CondVigLicencia,CT.IdLugar AS CdLugarLic,LugarLic
 FROM Trn_Disciplinario AS D INNER JOIN Companias  AS CI ON D.IdCia=CI.IdCia
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN Terceros AS P ON D.IdPropietario=P.IdTercero
 INNER JOIN Terceros AS C ON D.IdConductor=C.IdTercero INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
 INNER JOIN TiposCol AS CO ON V.IdColor=CO.IdColor
 INNER JOIN Localidades AS L ON C.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 LEFT JOIN Infracciones AS I ON D.CodInfracc=I.NumInf LEFT JOIN Rutas AS R ON D.IdRuta=R.IdRuta
 LEFT JOIN  Localidades AS LCE ON C.IdLugarCed=LCE.IdLocal
 LEFT JOIN TercCndtores AS CT ON D.IdConductor=CT.IdConductor  LEFT JOIN TiposMod AS MC ON CT.IdModd=MC.IdModd
 LEFT JOIN ExpLicencias AS ELC ON CT.IdLugar=ELC.IdLugar
 WHERE D.TipDoc=@pmTipDoc AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND D.IdEstado LIKE ISNULL(@pmIdEstado,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE 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,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_JurCasos
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID 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 paQryJurCasos_Cr @pmTipDoc VARCHAR(3),@pmCasoIDIni INT,@pmCasoIDFin INT,@pmIdCia CHAR(2)
AS 
SELECT C.CasoID AS NumCaso, C.IdCia AS CodCia,Compania,Fecha, Radicacion,C.IdVehiculo AS PlacaVeh,C.IdPropietario AS NitPropiterio,C.IdConductor AS CedConductor,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,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,IdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,C.TipDoc AS CodTipo,TipoDoc,FecUpdate,TimeSys
--Datos del vehículo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,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,FecIngreso,FecVigencia,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
 FROM Trn_JurCasos AS C  INNER JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo 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 Rutas AS R ON C.IdRuta=R.IdRuta 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 TiposCol AS CO ON V.IdColor=CO.IdColor 
  INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
  INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER 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 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
 WHERE  C.TipDoc=@pmTipDoc AND C.CasoID BETWEEN @pmCasoIDIni AND @pmCasoIDFin
 AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
ORDER BY C.IdCia,C.CasoID,Item
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurCasos_Crh @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT=Null
AS 
SELECT C.CasoID AS NumCaso, C.IdCia AS CodCia,Compania,Fecha, Radicacion,C.IdVehiculo AS PlacaVeh,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, NombreCparte, DirCparte, TelCparte,DanosCParte,DanosEmp, ValorDeducible, Aseguradora
,Reclamaciones,CdCausal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,AuxilioMutuo,H.IdEstado AS His_IdEstado,H.IdUsuario AS His_IdUsuario,UH.Usuario AS His_Usuario,Anulado, FecDev,C.IdEstado AS IdEstad,E.Estado AS EstadDocm,IdCiaCrea,C.IdUsuario AS IdUsuari,U.Usuario AS Cas_Usuario,C.TipDoc AS CodTipo,TipoDoc,FecUpdate,TimeSys
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,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,FecIngreso,FecVigencia,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
 FROM Trn_JurCasos AS C  INNER JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo 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 Rutas AS R ON C.IdRuta=R.IdRuta 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 TiposCol AS CO ON V.IdColor=CO.IdColor 
  INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
  INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
  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 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
 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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,Fecha,Radicacion,C.IdVehiculo AS PlacaVeh,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,Reclamaciones,CdCausal,Causal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg
,Anulado,FecDev,C.IdEstado AS IdEstad,E.Estado AS EstadDocm,C.IdUsuario AS IdUsuari,Usuario,Compania,TipDoc,IdCiaCrea,FecUpdate,TimeSys
--Datos del vehículo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,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,FecIngreso,FecVigencia,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
 FROM Trn_JurCasos AS C  INNER JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo 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 Rutas AS R ON C.IdRuta=R.IdRuta 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 TiposCol AS CO ON V.IdColor=CO.IdColor 
  INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
  INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER 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
 WHERE 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE 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,Fecha,Radicacion,C.IdVehiculo AS PlacaVeh,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,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,FecUpdate,TimeSys
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,Marca,V.IdColor AS CdColor,NomColor,Modelo,FecRep,ClaseMat,V.IdCom AS CdComb,TipoComb,NumMotor,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,FecIngreso,FecVigencia,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
 FROM Trn_JurCasos AS C  INNER JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo 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 Rutas AS R ON C.IdRuta=R.IdRuta 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 TiposCol AS CO ON V.IdColor=CO.IdColor 
 INNER JOIN TiposVeh AS TV  ON V.IdTipoVeh=TV.IdTipoVeh INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca
  INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
  INNER JOIN Terceros AS VNP ON V.IdPropietario=VNP.IdTercero INNER JOIN Terceros AS VNC ON V.IdConductor=VNC.IdTercero
 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
  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 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 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
 WHERE 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurCasosHis @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT
 AS 
 SELECT TipDoc,CasoID,IdCia,ActualizaID,FechaUp,Historial,EstadoAct,CierreProc,ArchivoProc,NombreCparte,DirCparte,TelCparte,DanosCParte,DanosEmp,ValorDeducible,Aseguradora,AuxilioMutuo,CedContparte,IdEstado,IdUsuario
 FROM Trn_JurCasosHis
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID AND IdCia=@pmIdCia
 AND (ActualizaID>=ISNULL(@pmActualizaID,0)  AND ActualizaID<=ISNULL(@pmActualizaID,2147483647))
 ORDER BY ActualizaID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurCasosHisLta  @pmTipDoc VARCHAR(3), @pmCasoID INT=Null, @pmIdCia CHAR(2)=Null, @pmActualizaID INT=Null
,@pmFechaUp SMALLDATETIME=Null,@pmCierreProc BIT=Null,@pmIdEstado VARCHAR(4)=Null
AS
 SELECT  CasoID, ActualizaID, FechaUp, Historial, EstadoAct, CierreProc, ArchivoProc,CedContparte, NombreCparte, DirCparte, TelCparte
, DanosCParte,DanosEmp, ValorDeducible, Aseguradora, AuxilioMutuo,H.IdEstado AS IdEstad,Estado, H.IdUsuario AS IdUsuari,Usuario,TipDoc,IdCia
 FROM  Trn_JurCasosHis AS H INNER JOIN JurEstados AS E ON H.IdEstado=E.IdEstado INNER JOIN adm_Usuarios AS U ON H.IdUsuario=U.IdUsuario
 WHERE  TipDoc= @pmTipDoc  AND (CasoID>=ISNULL(@pmCasoID,0)  AND CasoID<=ISNULL(@pmCasoID,2147483647))
  AND IdCia LIKE ISNULL(@pmIdCia,'%%') 
  AND (ActualizaID>=ISNULL(@pmActualizaID,0)  AND ActualizaID<=ISNULL(@pmActualizaID,2147483647))
  AND (FechaUp>=ISNULL(@pmFechaUp,CAST('19100101' AS SMALLDATETIME)) AND FechaUp<=ISNULL(@pmFechaUp,CAST('20781230' AS SMALLDATETIME)))
 AND (CierreProc=ISNULL(@pmCierreProc,0) or CierreProc=ISNULL(@pmCierreProc,1))
 AND H.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY IdCia,CasoID,ActualizaID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurCasosLta @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmAnulado BIT=Null,@pmIdVehiculo VARCHAR(10)=Null
AS 
SELECT CasoID, IdCia, Fecha, Radicacion,C.IdVehiculo AS PlacaVeh,NumVeh,C.IdPropietario AS NitPropiterio,T.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
, Cedtraparte,Contraparte, DirContraparte,TelContraparte,ValDanosCParte, ValDanosEmp, ValDeducible, EmpAsegdora, AuxMutuo, ArchivoCaso, Documntos, Croquis,Reclamaciones,CdCausal,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,Anulado, FecDev,C.IdEstado AS IdEstad,Estado
,IdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,TipDoc,FecUpdate,TimeSys
 FROM Trn_JurCasos AS C  INNER JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo 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 Rutas AS R ON C.IdRuta=R.IdRuta 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
 WHERE  TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  IdCia like ISNULL(@pmIdCia,'%%')
 AND C.IdVehiculo like ISNULL(@pmIdVehiculo,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
ORDER BY IdCia,CasoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurCasosPen @pmTipDoc VARCHAR(3),@pmIdCia CHAR(2)=Null,@pmAnulado BIT=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPropietario VARCHAR(16)=Null,@pmIdTipoPro VARCHAR(4)=Null
,@pmIdClase VARCHAR(4)=Null
AS 
SELECT CasoID, IdCia, Fecha, Radicacion,C.IdVehiculo AS PlacaVeh,NumVeh,C.IdPropietario AS NitPropiterio,T.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, Anulado, FecDev,C.IdEstado AS IdEstad,Estado,Reclamaciones,CdCausal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg
,IdCiaCrea,C.IdUsuario AS IdUsuari,Usuario,TipDoc,FecUpdate,TimeSys
 FROM Trn_JurCasos AS C  INNER JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo 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 Rutas AS R ON C.IdRuta=R.IdRuta 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
WHERE  TipDoc=@pmTipDoc AND C.IdEstado<>'9998' AND IdCia LIKE ISNULL(@pmIdCia,'%%') 
  AND C.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND C.IdPropietario LIKE ISNULL(@pmIdPropietario,'%') AND C.IdTipoPro LIKE ISNULL(@pmIdTipoPro,'%')
 AND C.IdClase LIKE ISNULL(@pmIdClase,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
ORDER BY IdCia,CasoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurCasosRad @pmTipDoc VARCHAR(3),@pmRadicacion VARCHAR(20), @pmIdCia CHAR(2)=Null
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, Reclamaciones
,CdCausal,Cedtraparte,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg,Anulado, FecDev,IdEstado, TimeSys, FecUpdate, IdCiaCrea, IdUsuario
 FROM Trn_JurCasos
 WHERE  TipDoc=@pmTipDoc AND Radicacion=@pmRadicacion  AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY  IdCia,CasoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryJurCausales @pmIdCausal VARCHAR(4)
 AS 
 SELECT IdCausal,Causal,IdTipoPro,Inactivo
 FROM JurCausales
 WHERE IdCausal=@pmIdCausal

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurCausalesDso @pmIdTipoPro VARCHAR(4)=Null
 AS 
 SELECT IdCausal,Causal,Causal+' ('+ TipoProceso +') '+IdCausal AS DsCau
 FROM JurCausales AS C INNER JOIN JurTipos AS T ON C.IdTipoPro=T.IdTipoPro
 WHERE C.IdTipoPro LIKE ISNULL(@pmIdTipoPro,'%')  AND  C.Inactivo=0
 ORDER BY Causal

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurCausalesLta 
 AS 
 SELECT IdCausal,Causal,C.IdTipoPro AS CdTipoProc,TipoProceso
 FROM JurCausales AS C INNER JOIN JurTipos AS T ON C.IdTipoPro=T.IdTipoPro
 WHERE C.Inactivo=0
 ORDER BY Causal
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurInvesHis @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT
 AS 
 SELECT TipDoc,CasoID,IdCia,ActualizaID,FechaUp,Historial,EstadoAct,Pruebas,Documntos,CierreProc,ArchivoProc
,NombreCparte,DirCparte,TelCparte,CedContparte,IdEstado,IdUsuario
 FROM Trn_JurInvesHis
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID AND IdCia=@pmIdCia
 AND (ActualizaID>=ISNULL(@pmActualizaID,0)  AND ActualizaID<=ISNULL(@pmActualizaID,2147483647))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurInvesHisDso @pmTipDoc VARCHAR(3), @pmCasoID INT=Null, @pmIdCia CHAR(2)=Null, @pmActualizaID INT=Null
,@pmFechaUp SMALLDATETIME=Null,@pmCierreProc BIT=Null,@pmIdEstado VARCHAR(4)=Null
AS
SELECT CasoID, ActualizaID, FechaUp, Historial, EstadoAct, Pruebas, Documntos, CierreProc, ArchivoProc,CedContparte, NombreCparte, DirCparte
, TelCparte,H.IdEstado AS IdEstad,Estado,H.IdUsuario AS IdUsuari ,Usuario,TipDoc,IdCia
 FROM  Trn_JurInvesHis AS H INNER JOIN JurEstados AS E ON H.IdEstado=E.IdEstado INNER JOIN adm_Usuarios AS U ON H.IdUsuario=U.IdUsuario
 WHERE TipDoc=@pmTipDoc 
 AND (CasoID>=ISNULL(@pmCasoID,0)  AND CasoID<=ISNULL(@pmCasoID,2147483647))
 AND IdCia like ISNULL(@pmIdCia,'%%') 
 AND (ActualizaID>=ISNULL(@pmActualizaID,0)  AND ActualizaID<=ISNULL(@pmActualizaID,2147483647))
 AND (FechaUp>=ISNULL(@pmFechaUp,CAST('19100101' AS SMALLDATETIME)) AND FechaUp<=ISNULL(@pmFechaUp,CAST('20781230' AS SMALLDATETIME)))
 AND  (CierreProc=ISNULL(@pmCierreProc,0) or CierreProc=ISNULL(@pmCierreProc,1))
 AND H.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 ORDER BY IdCia,CasoID,ActualizaID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryJurInvestiga @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,CasoID,IdCia,Fecha,Radicacion,IdAbogado,IdAuxiliar,IdAutoridad,IdEmpresa,Observacion,Contraparte,DirContraparte,TelContraparte
,ArchivoCaso,Anulado,FecDev,IdEstado,Cedtraparte,CdTipoPro,CdCausal,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_JurInvestiga
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID 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  paQryJurInvestiga_Cr  @pmTipDoc VARCHAR(3),@pmCasoIDIni INT,@pmCasoIDFin INT,@pmIdCia CHAR(2)
AS
SELECT CasoID, C.IdCia AS CodCia, Fecha, Radicacion, IdAbogado,A.RazonSocial AS Abogado, IdAuxiliar,AX.RazonSocial AS Auxiliar, C.IdAutoridad AS IdAutordad,NomAutoridad,IdEmpresa,T.RazonSocial AS Empresa,T.Dv AS Emp_DvNit
,CdTipoPro,TipoProceso,CdCausal,Causal,C.Observacion AS Observ, Cedtraparte,Contraparte, DirContraparte,TelContraparte,ArchivoCaso, Anulado, FecDev, C.IdEstado AS IdEstad,Estado,C.IdUsuario AS IdUsuari,Usuario
,TipDoc,TipoDoc,Compania,IdCiaCrea,FecUpdate,TimeSys
 FROM Trn_JurInvestiga AS C  INNER JOIN  Terceros AS T ON C.IdEmpresa=T.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 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
 LEFT JOIN JurTipos AS TP ON C.CdTipoPro=TP.IdTipoPro LEFT JOIN JurCausales AS CS ON C.CdCausal=CS.IdCausal
 WHERE  TipDoc=@pmTipDoc AND CasoID BETWEEN  @pmCasoIDIni AND @pmCasoIDFin 
 AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
ORDER BY C.IdCia,CasoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQryJurInvestiga_Crh  @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT=Null
AS
SELECT C.CasoID AS NumCaso, C.IdCia AS CodCia, Fecha, Radicacion, IdAbogado,A.RazonSocial AS Abogado, IdAuxiliar,AX.RazonSocial AS Auxiliar, C.IdAutoridad AS IdAutordad,NomAutoridad,IdEmpresa,T.RazonSocial AS Empresa,T.Dv AS Emp_DvNit
,CdTipoPro,TipoProceso,CdCausal,Causal,Cedtraparte,Contraparte, DirContraparte,TelContraparte,ArchivoCaso,C.Observacion AS Observ,ActualizaID, FechaUp, Historial, EstadoAct, Pruebas, Documntos, CierreProc, ArchivoProc,CedContparte, NombreCparte, DirCparte
, TelCparte,H.IdEstado AS His_IdEstado,HE.Estado AS His_Estado,H.IdUsuario AS His_IdUsuario ,UH.Usuario AS His_Usuario, Anulado, FecDev, C.IdEstado AS IdEstad,E.Estado AS EstadDoc,C.IdUsuario AS IdUsuari,U.Usuario AS Dcm_Usuario
,C.TipDoc AS TipDcm,TipoDoc,Compania,IdCiaCrea,FecUpdate,TimeSys
 FROM Trn_JurInvestiga AS C INNER JOIN Trn_JurInvesHis AS H ON C.TipDoc=H.TipDoc AND C.CasoID=H.CasoID AND C.IdCia=H.IdCia
 INNER JOIN  Terceros AS T ON C.IdEmpresa=T.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 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 JurEstados AS HE ON H.IdEstado=HE.IdEstado INNER JOIN adm_Usuarios  AS UH ON H.IdUsuario=UH.IdUsuario 
 LEFT JOIN JurTipos AS TP ON C.CdTipoPro=TP.IdTipoPro LEFT JOIN JurCausales AS CS ON C.CdCausal=CS.IdCausal
 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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQryJurInvestiga_Crr @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdAbogado VARCHAR(16)=Null
,@pmIdAuxiliar VARCHAR(16)=Null,@pmIdEmpresa VARCHAR(16)=Null,@pmIdAutoridad VARCHAR(4)=Null,@pmRadicacion VARCHAR(20)=Null,@pmCasoID INT=Null
,@pmAnulado BIT=Null,@pmIdEstado VARCHAR(4)=Null
AS
SELECT CasoID, C.IdCia AS CodCia, Fecha, Radicacion, IdAbogado,A.RazonSocial AS Abogado, IdAuxiliar,AX.RazonSocial AS Auxiliar, C.IdAutoridad AS IdAutordad,NomAutoridad,IdEmpresa,T.RazonSocial AS Empresa,T.Dv AS Emp_DvNit
,C.Observacion AS Observ, CdTipoPro,TipoProceso,CdCausal,Causal,Cedtraparte,Contraparte, DirContraparte,TelContraparte,ArchivoCaso, Anulado, FecDev, C.IdEstado AS IdEstad,Estado,C.IdUsuario AS IdUsuari,Usuario,TipDoc,Compania,IdCiaCrea,FecUpdate,TimeSys
 FROM Trn_JurInvestiga AS C  INNER JOIN  Terceros AS T ON C.IdEmpresa=T.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 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
 LEFT JOIN JurTipos AS TP ON C.CdTipoPro=TP.IdTipoPro LEFT JOIN JurCausales AS CS ON C.CdCausal=CS.IdCausal
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdAbogado LIKE ISNULL(@pmIdAbogado,'%')
 AND IdAuxiliar LIKE ISNULL(@pmIdAuxiliar,'%') AND IdEmpresa LIKE ISNULL(@pmIdEmpresa,'%') AND C.IdAutoridad LIKE ISNULL(@pmIdAutoridad,'%')
 AND (CasoID>=ISNULL(@pmCasoID,0)  AND CasoID<=ISNULL(@pmCasoID,2147483647))
 AND Radicacion LIKE ISNULL(@pmRadicacion,'%') 
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%')
ORDER BY C.IdCia,CasoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQryJurInvestiga_CrrH  @pmTipDoc VARCHAR(3), @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdAbogado VARCHAR(16)=Null
,@pmIdAuxiliar VARCHAR(16)=Null,@pmIdEmpresa VARCHAR(16)=Null,@pmIdAutoridad VARCHAR(4)=Null,@pmRadicacion VARCHAR(20)=Null,@pmCasoID INT=Null
,@pmAnulado BIT=Null,@pmIdEstado VARCHAR(4)=Null,@pmActualizaID INT=Null
AS
SELECT C.CasoID AS NumCaso, C.IdCia AS CodCia, Fecha, Radicacion, IdAbogado,A.RazonSocial AS Abogado, IdAuxiliar,AX.RazonSocial AS Auxiliar, C.IdAutoridad AS IdAutordad,NomAutoridad,IdEmpresa,T.RazonSocial AS Empresa,T.Dv AS Emp_DvNit
,C.Observacion AS Observ, CdTipoPro,TipoProceso,CdCausal,Causal,Cedtraparte,Contraparte, DirContraparte,TelContraparte,ArchivoCaso,ActualizaID, FechaUp, Historial, EstadoAct, Pruebas, Documntos, CierreProc, ArchivoProc,CedContparte, NombreCparte, DirCparte, TelCparte
,H.IdEstado AS His_IdEstado,HE.Estado AS His_Estado,H.IdUsuario AS His_IdUsuario ,UH.Usuario AS His_Usuario
,Anulado, FecDev, C.IdEstado AS IdEstad,E.Estado AS EstadoDcm,C.IdUsuario AS IdUsuari,U.Usuario AS UsuarioDcm,C.TipDoc AS TipDcm,Compania,IdCiaCrea,FecUpdate,TimeSys
 FROM Trn_JurInvestiga  AS C INNER JOIN Trn_JurInvesHis AS H ON C.TipDoc=H.TipDoc AND C.CasoID=H.CasoID AND C.IdCia=H.IdCia
 INNER JOIN  Terceros AS T ON C.IdEmpresa=T.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 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 JurEstados AS HE ON H.IdEstado=HE.IdEstado
 INNER JOIN adm_Usuarios AS UH ON H.IdUsuario=UH.IdUsuario 
 LEFT JOIN JurTipos AS TP ON C.CdTipoPro=TP.IdTipoPro LEFT JOIN JurCausales AS CS ON C.CdCausal=CS.IdCausal
 WHERE C.TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdAbogado LIKE ISNULL(@pmIdAbogado,'%')
 AND IdAuxiliar LIKE ISNULL(@pmIdAuxiliar,'%') AND IdEmpresa LIKE ISNULL(@pmIdEmpresa,'%') AND C.IdAutoridad LIKE ISNULL(@pmIdAutoridad,'%')
 AND (C.CasoID>=ISNULL(@pmCasoID,0)  AND C.CasoID<=ISNULL(@pmCasoID,2147483647))
 AND Radicacion LIKE ISNULL(@pmRadicacion,'%') 
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQryJurInvestigaLta  @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmAnulado BIT=Null,@pmIdAbogado VARCHAR(16)=Null,@pmIdEmpresa VARCHAR(16)=Null
AS
SELECT CasoID, C.IdCia AS CodCia, Fecha, Radicacion, IdAbogado,A.RazonSocial AS Abogado, IdAuxiliar,AX.RazonSocial AS Auxiliar, C.IdAutoridad AS IdAutordad,NomAutoridad,IdEmpresa,T.RazonSocial AS Empresa
,C.Observacion AS Observ,CdTipoPro,TipoProceso,CdCausal,Causal,Cedtraparte,Contraparte, DirContraparte,TelContraparte,ArchivoCaso, Anulado, FecDev, C.IdEstado AS IdEstad,Estado,C.IdUsuario AS IdUsuari,Usuario,TipDoc,Compania,IdCiaCrea,FecUpdate,TimeSys
 FROM Trn_JurInvestiga AS C  INNER JOIN  Terceros AS T ON C.IdEmpresa=T.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 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
 LEFT JOIN JurTipos AS TP ON C.CdTipoPro=TP.IdTipoPro LEFT JOIN JurCausales AS CS ON C.CdCausal=CS.IdCausal
 WHERE  TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdAbogado like ISNULL(@pmIdAbogado,'%') AND IdEmpresa LIKE ISNULL(@pmIdEmpresa,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
ORDER BY C.IdCia,CasoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  paQryJurInvestigaRad  @pmTipDoc VARCHAR(3),@pmRadicacion VARCHAR(20), @pmIdCia CHAR(2)=Null
AS
SELECT TipDoc, CasoID, IdCia, Fecha, Radicacion, IdAbogado, IdAuxiliar, IdAutoridad, IdEmpresa, Observacion, Contraparte, DirContraparte
, TelContraparte,Cedtraparte,CdTipoPro,CdCausal,ArchivoCaso, Anulado, FecDev, IdEstado, TimeSys, FecUpdate, IdCiaCrea, IdUsuario
 FROM   Trn_JurInvestiga
 WHERE  TipDoc=@pmTipDoc AND Radicacion=@pmRadicacion  AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 ORDER BY  IdCia,CasoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryJurReclamac @pmTipDoc VARCHAR(3),@pmReclamac INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Reclamac,IdCia,Fecha,FechaRec,TipSin,Siniestro,IdCiaSin,IdReclamnte,IdTipoRec,VrReclamac,Observacion,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario
 FROM Trn_JurReclamac
 WHERE TipDoc=@pmTipDoc AND Reclamac=@pmReclamac 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 paQryJurReclamacFmt @pmTipDoc VARCHAR(3),@pmReclamacIni INT,@pmReclamacFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS TipRec,TipoDoc,R.Reclamac AS NumReclam,R.IdCia AS CdCia,Compania,R.Fecha AS FechaReclam,FechaRec,TipSin,Siniestro,IdCiaSin,FechaSuc,IdReclamnte,T.RazonSocial AS NomReclamante
,R.IdTipoRec AS CdTipoRec,TipReclamante,VrReclamac,R.Observacion AS Observ,OrigenAdd,R.TimeSys AS FechaCreacion,R.IdCiaCrea AS CdCiaCrea,R.IdUsuario AS CdUsuario,Usuario
,Radicacion,C.IdVehiculo AS PlacaVeh,NumVeh,C.IdPropietario AS NitPropiterio,NP.RazonSocial AS Propietario,C.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,SinRecorrido,TipCar,Cartulina,IdCiaCar
,IdAbogado,A.RazonSocial AS Abogado,C.IdTipoPro AS IdTProc,TipoProceso,C.IdClase AS IdClaAcc,ClaseAccdte, C.IdCalifica AS IdCalf,CalificaCaso, LugarAcc, CausaAcc, Comentario, C.Observacion AS SntroObserv
,Reclamaciones,PolizaResCivil,VigencResCivil,NitEmpAseg,NombreAseg
--detalles
,Item,FechaOfrec,VrOfrecmto,OfcAceptado,FecAceptado,OfcPagado,NumSolGiro,Comentarios
 FROM Trn_JurReclamac AS R INNER JOIN Companias  AS CI ON R.IdCia=CI.IdCia INNER JOIN Terceros AS T ON R.IdReclamnte=T.IdTercero
 INNER JOIN JurTiposRec AS TR ON R.IdTipoRec=TR.IdTipoRec  INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN Sys_TiposDoc  AS TD ON R.TipDoc=TD.IdDoc 
 INNER JOIN  Trn_JurCasos AS C ON R.TipSin=C.TipDoc AND R.Siniestro=C.CasoID AND R.IdCiaSin=C.IdCia
 INNER JOIN Vehiculos AS V ON C.IdVehiculo=V.IdVehiculo 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 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
 LEFT JOIN Trn_JurRecOfrecmto AS RO ON R.TipDoc=RO.TipDoc AND R.Reclamac=RO.Reclamac AND R.IdCia=RO.IdCia
 WHERE R.TipDoc=@pmTipDoc AND R.Reclamac BETWEEN @pmReclamacIni AND @pmReclamacFin AND R.IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paQryJurTiposRec @pmIdTipoRec VARCHAR(4)
 AS 
 IF @pmIdTipoRec IS NULL 
  SELECT IdTipoRec,TipReclamante
  FROM JurTiposRec
  WHERE Inactivo=0
 ORDER BY IdTipoRec
 ELSE
  SELECT IdTipoRec,TipReclamante,Inactivo
  FROM JurTiposRec
  WHERE IdTipoRec=@pmIdTipoRec


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpDisciplinario  @pmTipDoc VARCHAR(3),@pmNumero INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecSuceso SMALLDATETIME,@pmIdVehiculo VARCHAR(10),@pmIdPropietario VARCHAR(16),@pmIdConductor VARCHAR(16)
,@pmIdRuta VARCHAR(4),@pmLugar VARCHAR(150),@pmNomReporta VARCHAR(150),@pmCedReporta VARCHAR(16),@pmVehiculoRep VARCHAR(10),@pmRepCond BIT,@pmCoordinador VARCHAR(150),@pmDespachador VARCHAR(150),@pmCitaDescargos BIT
,@pmFecCita SMALLDATETIME,@pmMemoCita VARCHAR(50),@pmImputacion VARCHAR(2500),@pmDescargos VARCHAR(3000),@pmFecDesc SMALLDATETIME,@pmRecibeDesc VARCHAR(150),@pmTipoSancion VARCHAR(10),@pmDescSancion VARCHAR(1000)
,@pmFecSancion SMALLDATETIME,@pmCantSancion DECIMAL(14,4),@pmCodInfracc INT,@pmReferencia VARCHAR(50),@pmObservacion VARCHAR(250),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmTipoInf VARCHAR(10),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Disciplinario SET Fecha=@pmFecha,FecSuceso=@pmFecSuceso,IdVehiculo=@pmIdVehiculo,IdPropietario=@pmIdPropietario,IdConductor=@pmIdConductor,IdRuta=@pmIdRuta,Lugar=@pmLugar,NomReporta=@pmNomReporta,CedReporta=@pmCedReporta
,VehiculoRep=@pmVehiculoRep,RepCond=@pmRepCond,Coordinador=@pmCoordinador,Despachador=@pmDespachador,CitaDescargos=@pmCitaDescargos,FecCita=@pmFecCita,MemoCita=@pmMemoCita,Imputacion=@pmImputacion,Descargos=@pmDescargos
,FecDesc=@pmFecDesc,RecibeDesc=@pmRecibeDesc,TipoSancion=@pmTipoSancion,DescSancion=@pmDescSancion,FecSancion=@pmFecSancion,CantSancion=@pmCantSancion,CodInfracc=@pmCodInfracc,Referencia=@pmReferencia,Observacion=@pmObservacion
,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,TipoInf=@pmTipoInf
 WHERE TipDoc=@pmTipDoc AND Numero=@pmNumero AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE 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),@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,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 QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpJurCasosCpt @pmTipDoc VARCHAR(3), @pmCasoID INT, @pmIdCia CHAR(2),@pmContraparte VARCHAR(100), @pmDirContraparte VARCHAR(100),@pmTelContraparte VARCHAR(20)
,@pmValDanosCParte MONEY,@pmValDanosEmp MONEY, @pmValDeducible MONEY, @pmEmpAsegdora VARCHAR(100), @pmAuxMutuo BIT, @pmArchivoCaso VARCHAR(100),@pmIdEstado VARCHAR(4),@pmFecUpdate SMALLDATETIME
,@pmCedtraparte VARCHAR(16)
AS 
 UPDATE Trn_JurCasos SET  Contraparte=@pmContraparte, DirContraparte=@pmDirContraparte,TelContraparte=@pmTelContraparte,ValDanosCParte=@pmValDanosCParte, ValDanosEmp=@pmValDanosEmp, ValDeducible=@pmValDeducible
, EmpAsegdora=@pmEmpAsegdora, AuxMutuo=@pmAuxMutuo,Cedtraparte=@pmCedtraparte,FecUpdate=@pmFecUpdate,ArchivoCaso=ISNULL(@pmArchivoCaso,ArchivoCaso),IdEstado=ISNULL(@pmIdEstado,IdEstado)
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID  AND  IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paUpJurCasosHis @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT,@pmFechaUp SMALLDATETIME,@pmHistorial VARCHAR(3500),@pmEstadoAct VARCHAR(3500),@pmCierreProc BIT,@pmArchivoProc VARCHAR(100),@pmNombreCparte VARCHAR(100),@pmDirCparte VARCHAR(100),@pmTelCparte VARCHAR(20),@pmDanosCParte MONEY,@pmDanosEmp MONEY,@pmValorDeducible MONEY,@pmAseguradora VARCHAR(100)
,@pmAuxilioMutuo BIT,@pmIdEstado VARCHAR(4),@pmCedContparte VARCHAR(16)
 AS 
 UPDATE Trn_JurCasosHis SET FechaUp=@pmFechaUp,Historial=@pmHistorial,EstadoAct=@pmEstadoAct,CierreProc=@pmCierreProc,ArchivoProc=@pmArchivoProc,NombreCparte=@pmNombreCparte,DirCparte=@pmDirCparte,TelCparte=@pmTelCparte,DanosCParte=@pmDanosCParte,DanosEmp=@pmDanosEmp,ValorDeducible=@pmValorDeducible,Aseguradora=@pmAseguradora,AuxilioMutuo=@pmAuxilioMutuo,IdEstado=@pmIdEstado,CedContparte=@pmCedContparte
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID AND IdCia=@pmIdCia AND ActualizaID=@pmActualizaID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpJurCausales @pmIdCausal VARCHAR(4),@pmCausal VARCHAR(50),@pmIdTipoPro VARCHAR(4),@pmInactivo BIT
 AS 
 UPDATE JurCausales SET Causal=@pmCausal,IdTipoPro=@pmIdTipoPro,Inactivo=@pmInactivo
 WHERE IdCausal=@pmIdCausal

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpJurInvesHis @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmActualizaID INT,@pmFechaUp SMALLDATETIME,@pmHistorial VARCHAR(3000),@pmEstadoAct VARCHAR(3000),@pmPruebas VARCHAR(1200),@pmDocumntos VARCHAR(250)
,@pmCierreProc BIT,@pmArchivoProc VARCHAR(100),@pmNombreCparte VARCHAR(100),@pmDirCparte VARCHAR(100),@pmTelCparte VARCHAR(20),@pmCedContparte VARCHAR(16),@pmIdEstado VARCHAR(4) 
 AS 
 UPDATE Trn_JurInvesHis SET FechaUp=@pmFechaUp,Historial=@pmHistorial,EstadoAct=@pmEstadoAct,Pruebas=@pmPruebas,Documntos=@pmDocumntos,CierreProc=@pmCierreProc,ArchivoProc=@pmArchivoProc,NombreCparte=@pmNombreCparte,DirCparte=@pmDirCparte
,TelCparte=@pmTelCparte,CedContparte=@pmCedContparte,IdEstado=@pmIdEstado
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID AND IdCia=@pmIdCia AND ActualizaID=@pmActualizaID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpJurInvestiga @pmTipDoc VARCHAR(3),@pmCasoID INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmRadicacion VARCHAR(20),@pmIdAbogado VARCHAR(16),@pmIdAuxiliar VARCHAR(16),@pmIdAutoridad VARCHAR(4),@pmIdEmpresa VARCHAR(16)
,@pmObservacion VARCHAR(250),@pmContraparte VARCHAR(100),@pmDirContraparte VARCHAR(100),@pmTelContraparte VARCHAR(20),@pmArchivoCaso VARCHAR(100),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmIdEstado VARCHAR(4)
,@pmCedtraparte VARCHAR(16),@pmCdTipoPro VARCHAR(4),@pmCdCausal VARCHAR(4),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_JurInvestiga SET Fecha=@pmFecha,Radicacion=@pmRadicacion,IdAbogado=@pmIdAbogado,IdAuxiliar=@pmIdAuxiliar,IdAutoridad=@pmIdAutoridad,IdEmpresa=@pmIdEmpresa,Observacion=@pmObservacion,Contraparte=@pmContraparte,DirContraparte=@pmDirContraparte,TelContraparte=@pmTelContraparte
,ArchivoCaso=@pmArchivoCaso,Anulado=@pmAnulado,FecDev=@pmFecDev,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,Cedtraparte=@pmCedtraparte,CdTipoPro=@pmCdTipoPro,CdCausal=@pmCdCausal
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID 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  paUpJurInvestigaCpt  @pmTipDoc VARCHAR(3), @pmCasoID INT, @pmIdCia CHAR(2),@pmContraparte VARCHAR(100), @pmDirContraparte VARCHAR(100), @pmTelContraparte VARCHAR(20)
,@pmArchivoCaso VARCHAR(100),@pmIdEstado VARCHAR(4), @pmFecUpdate SMALLDATETIME,@pmCedtraparte VARCHAR(16)
AS
 UPDATE  Trn_JurInvestiga  SET Contraparte=@pmContraparte, DirContraparte=@pmDirContraparte,TelContraparte=@pmTelContraparte
,Cedtraparte=@pmCedtraparte,ArchivoCaso=ISNULL(@pmArchivoCaso,ArchivoCaso),IdEstado=ISNULL(@pmIdEstado,IdEstado), FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND CasoID=@pmCasoID AND IdCia=@pmIdCia
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpJurReclamac @pmTipDoc VARCHAR(3),@pmReclamac INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaRec SMALLDATETIME,@pmTipSin VARCHAR(3),@pmSiniestro INT,@pmIdCiaSin CHAR(2),@pmIdReclamnte VARCHAR(16)
,@pmIdTipoRec VARCHAR(4),@pmVrReclamac MONEY,@pmObservacion VARCHAR(1000)
 AS 
 UPDATE Trn_JurReclamac SET Fecha=@pmFecha,FechaRec=@pmFechaRec,TipSin=@pmTipSin,Siniestro=@pmSiniestro,IdCiaSin=@pmIdCiaSin,IdReclamnte=@pmIdReclamnte,IdTipoRec=@pmIdTipoRec,VrReclamac=@pmVrReclamac,Observacion=@pmObservacion
 WHERE TipDoc=@pmTipDoc AND Reclamac=@pmReclamac 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 paUpJurRecOfrecmto @pmTipDoc VARCHAR(3),@pmReclamac INT,@pmIdCia CHAR(2),@pmItem INT,@pmFechaOfrec SMALLDATETIME,@pmVrOfrecmto MONEY,@pmOfcAceptado BIT
,@pmFecAceptado SMALLDATETIME,@pmOfcPagado BIT,@pmNumSolGiro VARCHAR(30),@pmComentarios VARCHAR(1000)
 AS 
 UPDATE Trn_JurRecOfrecmto SET FechaOfrec=@pmFechaOfrec,VrOfrecmto=@pmVrOfrecmto,OfcAceptado=@pmOfcAceptado,FecAceptado=@pmFecAceptado,OfcPagado=@pmOfcPagado
,NumSolGiro=@pmNumSolGiro,Comentarios=@pmComentarios
 WHERE TipDoc=@pmTipDoc AND Reclamac=@pmReclamac AND IdCia=@pmIdCia AND Item=@pmItem
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE paUpJurTiposRec @pmIdTipoRec VARCHAR(4),@pmTipReclamante VARCHAR(50),@pmInactivo BIT
 AS 
 UPDATE JurTiposRec SET TipReclamante=@pmTipReclamante,Inactivo=@pmInactivo
 WHERE IdTipoRec=@pmIdTipoRec

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[paQryCausacVenceAbb] @pmIdVehiculo VARCHAR(10)
AS
 SELECT  TipDoc,Documento,IdCia,Item,ValorFactura,ValorAbono,FecEmision,FecVence,V.IdCargo AS IdCargo,V.IdClase AS CodClase
,ClaseCuenta,Prioridad,Detalle,TipoDcto,DctoPpago,OrdenAbono
 FROM  Trn_CausacVence AS V INNER JOIN BusClaseCue AS C ON V.IdClase=C.IdClase
 LEFT JOIN Fijos AS F ON V.IdCargo=F.IdCargo
 WHERE  IdVehiculo=@pmIdVehiculo AND ValorFactura>ValorAbono
 ORDER BY TipDoc,FecVence,OrdenAbono