if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsAgencias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraTurn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraTurn]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsVehPartes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsVehPartes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAgencias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasCli]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAgenciasCli]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasCod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAgenciasCod]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAgenciasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAgenciasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaApo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaApo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraTurnLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraTurnLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehPartes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehPartes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehPartesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehPartesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpAgencias]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpAgencias]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraTurn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraTurn]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpVehPartes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpVehPartes]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaApo] @pmMes VARCHAR(6),@pmIdPeriodo VARCHAR(8)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmCodFondo VARCHAR(8)=Null,@pmIdCia CHAR(2)=Null
AS 
 SELECT N.KeyRegistro AS KeyReg,N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumContto,FecInicial,FecFinal,N.IdDep AS CodDep,N.IdInstala AS CodInstala,N.IdCCosto AS CodCcto
,N.IdSubCos AS CodSubcc,N.IdNom AS CodNom,N.pVehiculo AS PlacaVeh,N.VrSalario AS Salbasico,EsRetiro,NLiqRet,IdFonPen,IdFonEps,IdFonArp,IdFonCes,IdCajaCom,FecIngreso,FecRetiro,IdArea,C.IdClase AS CodClaRie
,CR.Tarifa AS TarRie,TipoTrabj,VrAuxTrans,VrUpc,SalIntegral,SalMinimo,C.Inactivo AS Con_Inctivo,D.IdConcepto AS CodConcepto,Concepto,ClaseCon,CN.Tarifa AS TarifDft,ClaseLiq,CantDevg,CantDed,VrTotDevg,VrTotDed,VrOrigen,D.Tarifa AS Tarfa
,DiasCalc,DiasNov,DiasCalc-DiasNov AS DiasNeto,CodFondo,IdCot,SubTipoCot,DenyPEN,DenyEPS,DenyARP,DenyCaj,DenyDcto,LiqEspSalud,DctoSalud,PagoSalud,D.VrBaseLiq
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado  INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase  INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 WHERE  SUBSTRING(N.IdPeriodo,1,6)=@pmMes AND NLiqRet>=0 AND D.IdConcepto IN ('PEN','RIE','SAL','FSP','FSU','SEN','ICB','COM','DCP','DCS','AVP','AVA')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND N.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND IdArea LIKE ISNULL(@pmIdArea,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND CodFondo LIKE ISNULL(@pmCodFondo,'%')
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 UNION ALL
 SELECT D.IdPeriodo,V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.FecInicial,V.FecFinal,C.IdDep,C.IdInstala,C.IdCCosto,C.IdSubCos,C.IdNom,V.pVehiculo
,V.VrSalario,0,0,IdFonPen,E.IdFonEps,IdFonArp,IdFonCes,IdCajaCom,C.FecIngreso,C.FecRetiro,C.IdArea,C.IdClase,0,C.TipoTrabj,C.VrAuxTrans,C.VrUpc
,C.SalIntegral,C.SalMinimo,C.Inactivo,D.IdConcepto,Concepto,ClaseCon,CN.Tarifa,'NOMINA',0,D.Cantidad,D.VrDevgado,D.VrDeducido,D.VrBaseLiq,D.Tarifa
,D.Cantidad,0,D.Cantidad,E.IdFonEps,C.IdCot,C.SubTipoCot,C.DenyPEN,C.DenyEPS,C.DenyARP,C.DenyCaj,C.DenyDcto,LiqEspSalud,DctoSalud,PagoSalud,D.VrBaseLiq
 FROM Trn_NomVac AS V INNER JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero
 INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
 WHERE SUBSTRING(D.IdPeriodo,1,6)=@pmMes AND D.IdConcepto IN ('DCP','DCS','FSP','FSU') AND V.Anulado=0
 AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%') AND C.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 ORDER BY  N.IdEmpleado,N.NContrato,D.IdConcepto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryAgenciasLta] @pmIdClie VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null,@pmIdGrupo VARCHAR(4)=Null
,@pmIdZona VARCHAR(4)=Null,@pmIdSzona VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
,@pmIdSector VARCHAR(8)=Null,@pmInactivo BIT=Null
 AS 
 SELECT A.IdClie AS NitCliente,RazonSocial,IdAgencia,CodAgencia,Agencia,DirAgncia,A.IdLocal AS CdCiuAgncia,L.Localidad AS CiudadAgncia
,L.IdDep AS CdDptoAgncia,D.Departamento AS DptoAgncia,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,VrCupoCre,VrSaldoAct,A.FecUpCupo AS FechaUpCupo,Referencia
,CodSicom,A.NContrato AS NroCont,CiaCont,A.FecIngreso AS Fec_Ingreso,NomCial,SiglaRaz,SZA.IdZona AS Age_CdZona,ZA.Zona AS Age_Zona,A.IdSzona AS Age_IdSubzona,SZA.Subzona AS Age_Subzona
,A.IdPlazo AS CdPlazo,Plazo,NVmto,A.CdPlazoComb AS CdPlazoComb,A.IdForma AS CdForma,FormaPago,A.IdVend AS NitVend,A.CdCms AS CodCms,A.CdDct AS CodDct,IntMora,A.DiasGracia AS Dias_gracia,FactFletes,A.FactSold AS FacturaSold
,A.CupoGalones AS CupoGals,A.CodRuta,Ruta
,TipoId,Dv,Direccion,T.IdLocal AS CdCiuClie,LT.Localidad AS CiuClie,LT.IdDep AS CdDptoClie,DT.Departamento AS DptoClie
,NitRepLeg,NomRepLeg,NitContac,NomContac,C.IdGrupo AS CdGrupo,GrupoClie,SZ.IdZona AS CdZona,Z.Zona AS CliZona,C.IdSzona AS CdSubzona,SZ.Subzona AS CliSubZona
,A.CdBandera AS CodBandera,TipoBandera,C.IdEstrato AS CdEstrato,Estrato,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRgmen,Regimen,TipEnte,VrCupo,VrSaldo,Telefono,Fax
,TelMovil,e_mail,C.IdEstado AS CdEstado,Estado,C.Inactivo AS Clie_inactivo,A.IdEstado AS Age_Estado,A.Inactivo AS Age_inactivo,IniStgNom,A.FechaAdd AS FecAdd
,TipoZona,Comentarios,TipoFactFletes,A.FechaUpdate AS FecUpd,A.IdUsuario AS IdUsuari,Usuario
--datos nuevos
,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando,CdCCBonif,CdSubCCBonif
 FROM Agencias AS A INNER JOIN Terceros AS T ON A.IdClie=T.IdTercero
 INNER JOIN TercCliente AS C ON A.IdClie=C.IdClie INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS L ON A.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN Subzonas AS SZA ON A.IdSzona=SZA.IdSzona INNER JOIN Zonas AS ZA ON SZA.IdZona=ZA.IdZona
 INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal  INNER JOIN Departamentos AS DT ON LT.IdDep=DT.IdDep
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN Estratos AS EC ON C.IdEstrato=EC.IdEstrato
 INNER JOIN SectoresEco AS S ON T.IdSector=S.IdSector INNER JOIN RegimenDian AS R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS E ON C.IdEstado=E.IdEstado INNER JOIN Plazos AS PZ ON A.IdPlazo=PZ.IdPlazo
 INNER JOIN Formaspago AS FP ON A.IdForma=FP.IdForma
 LEFT JOIN TiposBan AS TB ON A.CdBandera=TB.IdBandera LEFT JOIN Rutas AS RT ON A.CodRuta=RT.IdRuta
 WHERE  A.IdClie LIKE ISNULL(@pmIdClie,'%') AND A.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND C.IdGrupo LIKE ISNULL(@pmIdGrupo ,'%') AND SZA.IdZona LIKE ISNULL(@pmIdZona,'%') AND A.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%') AND A.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND T.IdSector LIKE ISNULL(@pmIdSector,'%')
 AND (A.Inactivo=ISNULL(@pmInactivo,0) or  A.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY RazonSocial,Agencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraTurnLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdVehiculo VARCHAR(10)=Null 
 AS  
SELECT T.Numero,T.IdCia AS CdCia,Compania,T.IdVehiculo AS PlacaVeh,V.NumVeh AS NumVehiculo,T.nRemolque
,T.IndTurno AS Ind_Turno,T.Fecha,T.IdConductor AS CedConductor,NC.RazonSocial AS NomConductor,T.Estado
,CASE T.Estado WHEN 1 THEN 'EN ESPERA' WHEN 2 THEN 'EN TRANSITO' ELSE 'INACTIVO' END AS DescEstado
,TipOrd,NumOrden,IdCiaOrd,FecOrden,NitCliente,CLI.RazonSocial AS Cliente,T.FechaCrea,SedeClie,T.IdUsuario AS CdUsuario,Usuario
,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.TipoAfil,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdColor AS CdColor,NomColor,V.Modelo
,Config,V.IdCat AS CdCateg,Catpeaje,CarrCapac,UndCapc,V.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,NumSoat,FecSoat,VigSoat,KmActual
,CdLocal,CU.Localidad,V.Ubicacion
,NC.TelMovil AS Cond_Celular,NC.Telefono AS Cond_Telefono,NC.e_mail AS Cond_Email,CND.Licencia AS NumLicencia,CND.CatLicencia,VigLicencia
FROM Trn_TraTurn AS T INNER JOIN Vehiculos AS V ON T.IdVehiculo=V.IdVehiculo INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
INNER JOIN Terceros AS NC ON T.IdConductor=NC.IdTercero INNER JOIN Companias AS CN ON T.IdCia=CN.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 CL ON V.IdColor=CL.IdColor
INNER JOIN Terceros AS NP ON V.IdPoseedor=NP.IdTercero
LEFT JOIN PeajesCat AS CP ON V.IdCat=CP.IdCat LEFT JOIN Localidades AS CU ON V.CdLocal=CU.IdLocal
LEFT JOIN TercCndtores AS CND ON T.IdConductor=CND.IdConductor LEFT JOIN Terceros AS CLI ON T.NitCliente=CLI.IdTercero
WHERE T.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND T.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehPartesLta] @pmIdVehiculo VARCHAR(10)=Null,@pmIdSistema VARCHAR(4)=Null
 AS 
 SELECT TipoUnidad,P.IdVehiculo AS PlacaVeh,NumParte,DescripParte,P.IdSistema AS CdSistema,Sistema,TipoParte,TipoControl
,Intervalo_km,Intervalo_dias,Intervalo_Horas,P.kmActual,kmManual,kmSat,P.kmCambio,kmCambioMan,kmCambioSat,FechaCambio,HorasTrab,HsUltCambio,CdProdRep,DescripProd
,P.IdEstado AS CdEstado,E.Estado,E.NColor,P.Inactivo,FechaRetiro,MarcaParte,P.ModeloParte,P.NumSerie,P.FechaCrea,P.IdUsuario AS CdUsuario,Usuario
--datos del vehículo
,NumVeh,V.IdTipoVeh AS CdTipo,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdColor AS CdColor,CL.NomColor AS Color
,V.IdLinea AS CdLinea,V.Modelo,FecRep,SerieChasis,CdRemque,V.IdPropietario,NP.RazonSocial AS Propietario,FecIngreso,V.KmInicial,V.KmActual AS kmsActual
,V.Km2Actual,NumSoat,FecSoat,VigSoat,V.Descripcion,V.IdEstado AS VehCodEstado,EV.Estado AS VehEstado,EV.NColor AS VehEdoColor,V.Inactivo AS VehInactivo
 FROM VehPartes AS P INNER JOIN MttoSistemas AS S ON P.IdSistema=S.IdSistema
 INNER JOIN EstadoSer AS E ON P.IdEstado=E.IdEstado INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario
 LEFT JOIN ProdMcias AS M ON P.CdProdRep=M.IdProducto
 LEFT JOIN Vehiculos AS V ON P.IdVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor
 LEFT JOIN Terceros AS NP ON V.IdPropietario=NP.IdTercero LEFT JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado
 WHERE P.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND P.IdSistema LIKE ISNULL(@pmIdSistema,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraTurn] @pmNumero INT,@pmIdCia CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIndTurno INT,@pmFecha SMALLDATETIME
,@pmIdConductor VARCHAR(16),@pmnRemolque VARCHAR(10),@pmEstado INT,@pmTipOrd VARCHAR(3)
,@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmFecOrden SMALLDATETIME,@pmNitCliente VARCHAR(16),@pmSedeClie VARCHAR(10)
,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraTurn (Numero,IdCia,IdVehiculo,IndTurno,Fecha,IdConductor,nRemolque,Estado,TipOrd,NumOrden,IdCiaOrd,FecOrden,IdUsuario,FechaCrea,NitCliente,SedeClie)
 VALUES (@pmNumero,@pmIdCia,@pmIdVehiculo,@pmIndTurno,@pmFecha,@pmIdConductor,@pmnRemolque,@pmEstado
,@pmTipOrd,@pmNumOrden,@pmIdCiaOrd,@pmFecOrden,@pmIdUsuario,@pmFechaCrea,@pmNitCliente,@pmSedeClie)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraTurn] @pmNumero INT,@pmIdCia CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIndTurno INT,@pmFecha SMALLDATETIME,@pmIdConductor VARCHAR(16)
,@pmnRemolque VARCHAR(10),@pmEstado INT,@pmTipOrd VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrd CHAR(2),@pmFecOrden SMALLDATETIME,@pmNitCliente VARCHAR(16),@pmSedeClie VARCHAR(10)
 AS 
 UPDATE Trn_TraTurn SET IdVehiculo=@pmIdVehiculo,IndTurno=@pmIndTurno,Fecha=@pmFecha,IdConductor=@pmIdConductor,nRemolque=@pmnRemolque,Estado=@pmEstado
,TipOrd=@pmTipOrd,NumOrden=@pmNumOrden,IdCiaOrd=@pmIdCiaOrd,FecOrden=@pmFecOrden,NitCliente=@pmNitCliente,SedeClie=@pmSedeClie
 WHERE Numero=@pmNumero AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsVehPartes] @pmIdVehiculo VARCHAR(10),@pmNumParte VARCHAR(20),@pmDescripParte VARCHAR(250),@pmIdSistema VARCHAR(4),@pmTipoControl INT,@pmIntervalo_km INT,@pmIntervalo_dias INT,@pmkmActual INT,@pmkmCambio INT
,@pmFechaCambio SMALLDATETIME,@pmCdProdRep VARCHAR(16),@pmTipoUnidad CHAR(1),@pmTipoParte VARCHAR(10),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFechaRetiro SMALLDATETIME,@pmIntervalo_Horas INT
,@pmkmManual INT,@pmkmSat INT,@pmkmCambioMan INT,@pmkmCambioSat INT,@pmHorasTrab INT,@pmHsUltCambio INT,@pmMarcaParte VARCHAR(20),@pmModeloParte VARCHAR(20),@pmNumSerie VARCHAR(30),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO VehPartes (IdVehiculo,NumParte,DescripParte,IdSistema,TipoControl,Intervalo_km,Intervalo_dias,Intervalo_Horas,kmActual,kmManual,kmSat,kmCambio,kmCambioMan,kmCambioSat,FechaCambio,CdProdRep,TipoUnidad,TipoParte
,IdEstado,Inactivo,FechaRetiro,FechaCrea,IdUsuario,HorasTrab,HsUltCambio,MarcaParte,ModeloParte,NumSerie)
 VALUES (@pmIdVehiculo,@pmNumParte,@pmDescripParte,@pmIdSistema,@pmTipoControl,@pmIntervalo_km,@pmIntervalo_dias,@pmIntervalo_Horas,@pmkmActual,@pmkmManual,@pmkmSat,@pmkmCambio,@pmkmCambioMan,@pmkmCambioSat,@pmFechaCambio
,@pmCdProdRep,@pmTipoUnidad,@pmTipoParte,@pmIdEstado,@pmInactivo,@pmFechaRetiro,@pmFechaCrea,@pmIdUsuario,@pmHorasTrab,@pmHsUltCambio,@pmMarcaParte,@pmModeloParte,@pmNumSerie)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehPartes] @pmIdVehiculo VARCHAR(10),@pmNumParte VARCHAR(20)
 AS 
 SELECT IdVehiculo,NumParte,DescripParte,IdSistema,TipoControl,Intervalo_km,Intervalo_dias,Intervalo_Horas,kmActual
,kmManual,kmSat,kmCambio,kmCambioMan,kmCambioSat,FechaCambio,CdProdRep
,TipoUnidad,TipoParte,IdEstado,Inactivo,FechaRetiro,HorasTrab,HsUltCambio,MarcaParte,ModeloParte,NumSerie,FechaCrea,IdUsuario
 FROM VehPartes
 WHERE IdVehiculo=@pmIdVehiculo AND NumParte=@pmNumParte
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpVehPartes] @pmIdVehiculo VARCHAR(10),@pmNumParte VARCHAR(20),@pmDescripParte VARCHAR(250),@pmIdSistema VARCHAR(4),@pmTipoControl INT,@pmIntervalo_km INT,@pmIntervalo_dias INT,@pmkmActual INT,@pmkmCambio INT
,@pmFechaCambio SMALLDATETIME,@pmCdProdRep VARCHAR(16),@pmTipoUnidad CHAR(1),@pmTipoParte VARCHAR(10),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFechaRetiro SMALLDATETIME,@pmIntervalo_Horas INT
,@pmkmManual INT,@pmkmSat INT,@pmkmCambioMan INT,@pmkmCambioSat INT,@pmHorasTrab INT,@pmHsUltCambio INT,@pmMarcaParte VARCHAR(20),@pmModeloParte VARCHAR(20),@pmNumSerie VARCHAR(30)
 AS 
 UPDATE VehPartes SET DescripParte=@pmDescripParte,IdSistema=@pmIdSistema,TipoControl=@pmTipoControl,Intervalo_km=@pmIntervalo_km,Intervalo_dias=@pmIntervalo_dias,kmActual=@pmkmActual,kmCambio=@pmkmCambio,FechaCambio=@pmFechaCambio,CdProdRep=@pmCdProdRep
,TipoUnidad=@pmTipoUnidad,TipoParte=@pmTipoParte,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FechaRetiro=@pmFechaRetiro,Intervalo_Horas=@pmIntervalo_Horas
,kmManual=@pmkmManual,kmSat=@pmkmSat,kmCambioMan=@pmkmCambioMan,kmCambioSat=@pmkmCambioSat,HorasTrab=@pmHorasTrab,HsUltCambio=@pmHsUltCambio,MarcaParte=@pmMarcaParte,ModeloParte=@pmModeloParte,NumSerie=@pmNumSerie
 WHERE IdVehiculo=@pmIdVehiculo AND NumParte=@pmNumParte
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryAgenciasCli] @pmIdClie VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,RazonSocial,Codigo,Dv,Agencia,DirAgncia,A.IdLocal AS CdCiudad
,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,VrCupoCre,VrSaldoAct,Referencia,CodSicom
,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,FecUpCupo,NContrato,CiaCont,FecIngreso,CupoGalones,A.Inactivo AS Inactvo,A.IdEstado AS Id_Estado,Comentarios,TipoZona,CodRuta
,A.FechaAdd AS FecAdd,A.FechaUpdate AS FecUpd,A.IdUsuario AS IdUsuari
 FROM Agencias AS A INNER JOIN Terceros AS T ON A.IdClie=T.IdTercero
 WHERE A.Inactivo=0 AND IdClie=@pmIdClie
 ORDER BY IdAgencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsAgencias] @pmIdAgencia VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmIdClie VARCHAR(16),@pmAgencia VARCHAR(150),@pmDirAgncia VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelAgncia VARCHAR(20),@pmFaxAgncia VARCHAR(20),@pmNitCont VARCHAR(16)
,@pmNomCont VARCHAR(150),@pmemlCont VARCHAR(100),@pmCargoCont VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmCdPlazoComb VARCHAR(4),@pmIdForma VARCHAR(4),@pmCdBandera VARCHAR(4),@pmIdVend VARCHAR(16),@pmCdCms VARCHAR(4),@pmCdDct VARCHAR(4),@pmIntMora DECIMAL(14,4)
,@pmDiasGracia INT,@pmFactFletes BIT,@pmFactSold BIT,@pmVrCupoCre MONEY,@pmVrSaldoAct MONEY,@pmFecUpCupo SMALLDATETIME,@pmNContrato INT,@pmCiaCont CHAR(2),@pmFecIngreso SMALLDATETIME,@pmReferencia VARCHAR(50),@pmComentarios VARCHAR(250),@pmCupoGalones DECIMAL(14,4)
,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoZona VARCHAR(10),@pmCodSicom VARCHAR(20),@pmCdTipAgenc VARCHAR(4),@pmCdSubAgenc VARCHAR(4),@pmCdGrupAgenc VARCHAR(4), @pmCdSubGrupAgenc VARCHAR(4), @pmCdFntePago VARCHAR(4), @pmPlazoSobtasa VARCHAR(20)
,@pmDocFinanc VARCHAR(3), @pmVolContMes DECIMAL(14,4), @pmVolContTotal DECIMAL(14,4), @pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4), @pmFecPriVenta SMALLDATETIME, @pmInvCtbObra MONEY, @pmInvCtbCapital MONEY, @pmInvCtbEfectivo MONEY, @pmInvCtbImagen MONEY, @pmInvCtbMntmto MONEY
,@pmFecDesemb SMALLDATETIME, @pmInvContObra MONEY, @pmInvContCapital MONEY, @pmInvContEfectivo MONEY, @pmInvContImagen MONEY, @pmCrtlAprovInv INT, @pmCrtlAprovImg INT,@pmFecVigCond SMALLDATETIME,@pmTasaIntArranq DECIMAL(14,4),@pmAgencOperando BIT
,@pmTipoFactFletes INT,@pmCdCCBonif VARCHAR(16),@pmCdSubCCBonif VARCHAR(16),@pmCodRuta VARCHAR(4),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Agencias (IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,CodSicom,FecIngreso,Referencia,Comentarios
,CupoGalones,TipoZona,IdEstado,Inactivo,FechaAdd,IdUsuario,CdTipAgenc, CdSubAgenc, CdGrupAgenc, CdSubGrupAgenc, CdFntePago, PlazoSobtasa, DocFinanc, VolContMes, VolContTotal, VolViabMes,VolViabTotal, FecPriVenta, InvCtbObra, InvCtbCapital, InvCtbEfectivo, InvCtbImagen, InvCtbMntmto, FecDesemb, InvContObra, InvContCapital
,InvContEfectivo, InvContImagen, CrtlAprovInv, CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando,TipoFactFletes,CdCCBonif,CdSubCCBonif,CodRuta)
 VALUES (@pmIdAgencia,@pmCodAgencia,@pmIdClie,@pmAgencia,@pmDirAgncia,@pmIdLocal,@pmTelAgncia,@pmFaxAgncia,@pmNitCont,@pmNomCont,@pmemlCont,@pmCargoCont,@pmIdSzona,@pmIdPlazo,@pmCdPlazoComb,@pmIdForma,@pmCdBandera,@pmIdVend
,@pmCdCms,@pmCdDct,@pmIntMora,@pmDiasGracia,@pmFactFletes,@pmFactSold,@pmVrCupoCre,@pmVrSaldoAct,@pmFecUpCupo,@pmNContrato,@pmCiaCont,@pmCodSicom,@pmFecIngreso,@pmReferencia,@pmComentarios,@pmCupoGalones,@pmTipoZona,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario
,@pmCdTipAgenc, @pmCdSubAgenc,@pmCdGrupAgenc,@pmCdSubGrupAgenc,@pmCdFntePago,@pmPlazoSobtasa,@pmDocFinanc,@pmVolContMes,@pmVolContTotal,@pmVolViabMes,@pmVolViabTotal,@pmFecPriVenta
,@pmInvCtbObra, @pmInvCtbCapital,@pmInvCtbEfectivo,@pmInvCtbImagen,@pmInvCtbMntmto,@pmFecDesemb,@pmInvContObra,@pmInvContCapital,@pmInvContEfectivo,@pmInvContImagen,@pmCrtlAprovInv,@pmCrtlAprovImg,@pmFecVigCond,@pmTasaIntArranq,@pmAgencOperando,@pmTipoFactFletes,@pmCdCCBonif,@pmCdSubCCBonif,@pmCodRuta)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpAgencias] @pmIdAgencia VARCHAR(16),@pmCodAgencia VARCHAR(16),@pmIdClie VARCHAR(16),@pmAgencia VARCHAR(150),@pmDirAgncia VARCHAR(250),@pmIdLocal VARCHAR(8),@pmTelAgncia VARCHAR(20),@pmFaxAgncia VARCHAR(20),@pmNitCont VARCHAR(16),@pmNomCont VARCHAR(150)
,@pmemlCont VARCHAR(100),@pmCargoCont VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmCdPlazoComb VARCHAR(4),@pmIdForma VARCHAR(4),@pmCdBandera VARCHAR(4),@pmIdVend VARCHAR(16),@pmCdCms VARCHAR(4),@pmCdDct VARCHAR(4),@pmIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmFactFletes BIT,@pmFactSold BIT
,@pmVrCupoCre MONEY,@pmVrSaldoAct MONEY,@pmFecUpCupo SMALLDATETIME,@pmNContrato INT,@pmCiaCont CHAR(2),@pmFecIngreso SMALLDATETIME,@pmReferencia VARCHAR(50),@pmComentarios VARCHAR(250),@pmCupoGalones DECIMAL(14,4),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoZona VARCHAR(10),@pmCodSicom VARCHAR(20)
,@pmCdTipAgenc VARCHAR(4),@pmCdSubAgenc VARCHAR(4),@pmCdGrupAgenc VARCHAR(4), @pmCdSubGrupAgenc VARCHAR(4), @pmCdFntePago VARCHAR(4), @pmPlazoSobtasa VARCHAR(20)
,@pmDocFinanc VARCHAR(3), @pmVolContMes DECIMAL(14,4), @pmVolContTotal DECIMAL(14,4), @pmVolViabMes DECIMAL(14,4),@pmVolViabTotal DECIMAL(14,4), @pmFecPriVenta SMALLDATETIME, @pmInvCtbObra MONEY, @pmInvCtbCapital MONEY, @pmInvCtbEfectivo MONEY, @pmInvCtbImagen MONEY, @pmInvCtbMntmto MONEY
,@pmFecDesemb SMALLDATETIME, @pmInvContObra MONEY, @pmInvContCapital MONEY, @pmInvContEfectivo MONEY, @pmInvContImagen MONEY, @pmCrtlAprovInv INT, @pmCrtlAprovImg INT,@pmFecVigCond SMALLDATETIME,@pmTasaIntArranq DECIMAL(14,4),@pmAgencOperando BIT,@pmTipoFactFletes INT,@pmCdCCBonif VARCHAR(16),@pmCdSubCCBonif VARCHAR(16),@pmCodRuta VARCHAR(4),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Agencias SET CodAgencia=@pmCodAgencia,IdClie=@pmIdClie,Agencia=@pmAgencia,DirAgncia=@pmDirAgncia,IdLocal=@pmIdLocal,TelAgncia=@pmTelAgncia,FaxAgncia=@pmFaxAgncia,NitCont=@pmNitCont,NomCont=@pmNomCont,emlCont=@pmemlCont,CargoCont=@pmCargoCont,IdSzona=@pmIdSzona
,IdPlazo=@pmIdPlazo,CdPlazoComb=@pmCdPlazoComb,IdForma=@pmIdForma,CdBandera=@pmCdBandera,IdVend=@pmIdVend,CdCms=@pmCdCms,CdDct=@pmCdDct,IntMora=@pmIntMora,DiasGracia=@pmDiasGracia,FactFletes=@pmFactFletes,FactSold=@pmFactSold,VrCupoCre=@pmVrCupoCre,VrSaldoAct=@pmVrSaldoAct
,FecUpCupo=@pmFecUpCupo,FecIngreso=@pmFecIngreso,Referencia=@pmReferencia,Comentarios=@pmComentarios,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,NContrato=@pmNContrato,CiaCont=@pmCiaCont,CupoGalones=@pmCupoGalones,FechaUpdate=@pmFechaUpdate,TipoZona=@pmTipoZona,CodSicom=@pmCodSicom
,CdTipAgenc=@pmCdTipAgenc,CdSubAgenc=@pmCdSubAgenc,CdGrupAgenc=@pmCdGrupAgenc,CdSubGrupAgenc=@pmCdSubGrupAgenc,CdFntePago=@pmCdFntePago,PlazoSobtasa=@pmPlazoSobtasa,DocFinanc=@pmDocFinanc,VolContMes=@pmVolContMes,VolContTotal=@pmVolContTotal,VolViabMes=@pmVolViabMes,VolViabTotal=@pmVolViabTotal
,FecPriVenta=@pmFecPriVenta,InvCtbObra=@pmInvCtbObra,InvCtbCapital=@pmInvCtbCapital,InvCtbEfectivo=@pmInvCtbEfectivo,InvCtbImagen=@pmInvCtbImagen,InvCtbMntmto=@pmInvCtbMntmto,FecDesemb=@pmFecDesemb,InvContObra=@pmInvContObra,InvContCapital=@pmInvContCapital,InvContEfectivo=@pmInvContEfectivo
,InvContImagen=@pmInvContImagen,CrtlAprovInv=@pmCrtlAprovInv,CrtlAprovImg=@pmCrtlAprovImg,FecVigCond=@pmFecVigCond,TasaIntArranq=@pmTasaIntArranq,AgencOperando=@pmAgencOperando,TipoFactFletes=@pmTipoFactFletes,CdCCBonif=@pmCdCCBonif,CdSubCCBonif=@pmCdSubCCBonif,CodRuta=@pmCodRuta
 WHERE IdAgencia=@pmIdAgencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryAgencias] @pmIdAgencia VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont
,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,FecIngreso,CodSicom,Referencia,Comentarios,CupoGalones
,IdEstado,Inactivo,TipoZona,FechaAdd,FechaUpdate,IdUsuario,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando,TipoFactFletes,CdCCBonif,CdSubCCBonif,CodRuta
 FROM Agencias
 WHERE IdAgencia=@pmIdAgencia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryAgenciasCod] @pmIdClie VARCHAR(16),@pmCodAgencia VARCHAR(16)
 AS 
 SELECT IdAgencia,CodAgencia,IdClie,Agencia,DirAgncia,IdLocal,TelAgncia,FaxAgncia,NitCont,NomCont,emlCont
,CargoCont,IdSzona,IdPlazo,CdPlazoComb,IdForma,CdBandera,IdVend,CdCms,CdDct,IntMora,DiasGracia,FactFletes,FactSold
,VrCupoCre,VrSaldoAct,FecUpCupo,NContrato,CiaCont,FecIngreso,CodSicom,Referencia,Comentarios,CupoGalones
,TipoZona,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
,CdTipAgenc,CdSubAgenc,CdGrupAgenc,CdSubGrupAgenc,CdFntePago,PlazoSobtasa,DocFinanc,VolContMes
,VolContTotal,VolViabMes,VolViabTotal,FecPriVenta,InvCtbObra,InvCtbCapital,InvCtbEfectivo,InvCtbImagen,InvCtbMntmto,FecDesemb,InvContObra,InvContCapital,InvContEfectivo
,InvContImagen,CrtlAprovInv,CrtlAprovImg,FecVigCond,TasaIntArranq,AgencOperando,TipoFactFletes,CdCCBonif,CdSubCCBonif,CodRuta
 FROM Agencias
 WHERE IdClie=@pmIdClie AND CodAgencia LIKE ISNULL(@pmCodAgencia,'%')
 ORDER BY IdAgencia
GO