if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFactura_Ent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFactura_Ent]
GO
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].[paInsComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsComFactura]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsComFactura_Uni]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsComFactura_Uni]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomConAuto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomConAuto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsRequisicion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsRequisicion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraRemCntdor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraRemCntdor]
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].[paInsVehiculos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsVehiculos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsVehiculos_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsVehiculos_Sel]
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].[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].[paQryComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFactura]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaDoc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFacturaDoc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFacturaDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFacturaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaNit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFacturaNit]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryComFacturaSal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryComFacturaSal]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEmp_ContratoDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEmp_ContratoDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEntradasNetDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEntradasNetDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexCc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardexCc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryKardexProCc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryKardexProCc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConAuto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConAuto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConAutoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConAutoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomConAutoPen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomConAutoPen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNov_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNov_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomNovCon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomNovCon]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicion]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicion_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicion_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionLtm]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionLtm]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionMtto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionMtto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionOrd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionOrd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionPen]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionPen]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionSal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionSal]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrySalidasNetDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrySalidasNetDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTiposDocAut]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTiposDocAut]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexCc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexCc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDespachosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDespachosLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoOpe]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoOpe]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemCntdor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemCntdor]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemCntdorFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemCntdorFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaCnt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraRemesaCnt]
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].[paQryVehiculos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosLta]
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].[paUpComFactura]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpComFactura]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEmp_Contrato]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpEmp_Contrato]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNiifDetallePla]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNiifDetallePla]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomConAuto]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomConAuto]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpRequisicion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpRequisicion]
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].[paUpVehiculos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpVehiculos]
GO
--nomina Jun 29/2016
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomPrendas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomPrendas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomPrendas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomPrendas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomPrendas]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomPrendas]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomDotacionDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDotacionDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDotacion_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDotacion_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDotacionLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDotacionLta]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehiculosLta] @pmClaseVeh VARCHAR(10)=Null,@pmIdTipoVeh VARCHAR(4)=Null,@pmIdMarca VARCHAR(4)=Null
,@pmIdTipoMot VARCHAR(4)=Null,@pmIdCrceria VARCHAR(4)=Null,@pmModelo VARCHAR(4)=Null,@pmConfig VARCHAR(5)=Null,@pmIdCat VARCHAR(4)=Null
,@pmClaseMat VARCHAR(10)=Null,@pmIdCom VARCHAR(4)=Null,@pmIdLub VARCHAR(4)=Null,@pmIdTlla VARCHAR(4)=Null,@pmIdMarlla VARCHAR(4)=Null
,@pmIdPropietario VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPpd VARCHAR(4)=Null
,@pmIdEstado VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmFecComIni SMALLDATETIME=Null,@pmFecComFin SMALLDATETIME=Null
,@pmIdAdmon VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null
 AS 
 SELECT IdVehiculo,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,TC.TipoCar AS TipoCarr,CL.NomColor AS Color,V.Modelo AS ModeloVeh,FecRep,Config,V.IdCat AS CodCatg,CarrCapac
,PesoVacio,NumMotor,SerieChasis,RQ.IdCrceria AS RmqIdCarr,MR.Marca AS MarcaRmq,CR.NomColor AS ColorRmq,NitEmpresa,NE.RazonSocial AS Empresa
,IdPoseedor,NT.RazonSocial AS Poseedor,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CdTarifa,TarjProp,RespCivil,VigRCivil,VrComcial,V.IdEstado AS CdEstado,Estado,V.Inactivo AS Inactvo,FecPriServ,FecUltServ
,RegNalCarga,FecRegNal,VigRegNal,RevTecMec,FecTecMec,VigTecMec,V.Observacion AS Observ,TipoAfil
,NumVeh,ClaseVeh,V.IdTipoVeh AS CdTipo,V.IdLinea AS CdLinea,LineaVeh,V.IdColor AS CdColor,V.IdTipoMot AS CdTipMotor,TipoMotor
,V.IdCrceria AS CdCarr,VehArtic,NumLlan,NumLlans,Catpeaje,CdCatv,ClaseMat,Cilind,CapTanq,V.IdCom AS CdTipComb,TipoComb,V.IdLub AS CdLub
,TipoLub,V.IdTlla AS CdTipLlantas,TipoLlanta,IdMarlla,ML.Marca AS MarcaLlantas,PesoMax,NumSerie,V.NitProv AS NitProveedor,NPV.RazonSocial AS Proveedor,V.FecCompra AS FechaCompra
,VrAseg,V.VrAvaludo AS VlrAvaludo,V.VidaUtil AS Vida_Util,FecSalida,NContrato,V.IdAdmon AS CdAdmon,TipoAdmon,V.IdNiv AS CdNivel,NivelServicio,V.IdGrupo AS CdGrupo,GrupoProp,CdGrupR,FecIngreso
,FecVigencia,FecRetiro,FecTProp,VigTProp,CdLugTp,LT.Localidad AS LugarTarjProp,Ulttramite,FecRCivil,KmInicial,KmActual,Km2Actual,V.Descripcion AS VehDescripcion,V.CdCenSer AS CodCentro,CentroServ
,V.CdLocal AS CodCiuUbic,LU.Localidad AS CiuUbicacion,LU.IdDep AS CodDpto,Departamento,V.Ubicacion,V.PathFoto,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,FecUltAcc,TieneAcc,FecPagImp
,V.IdUsuario AS CdUsuario,Usuario,V.FechaAdd AS Fec_Add,V.FechaUpdate AS Fec_Upd,EV.NColor AS NumColor,OutDemand,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion
,FecCertMovil,VigCertMovil,CdRutaHab,RQ.IdMarca AS RmqIdMarca,RQ.IdColor AS RmqIdColor,RC.TipoCar AS RmqTipoCar,GPSoperador,GPSUsuario,GPSClave,CantFiltros
--campos libres
,TarjOper,FecTarjOper,VigTarjOper,CertGases,FecCertGas,VigCertGas,DeclaracImp,TipoIngreso,V.IdOrgTra AS CdOrgTra,NomOrgTrans
--Informacion coductor
,V.IdConductor,NC.RazonSocial AS Conductor,NC.IdLugarCed,LCCE.Localidad AS LugCed,CND.TipoSangre,CND.FactorRh,CND.FecNacmto,NC.TelMovil AS CelCnd,NC.Telefono AS TelCnd,NC.e_mail AS Mailcnd,nc.Direccion AS DirCnd
,NC.IdLocal,LCND.Localidad AS LocCnd,CND.Licencia,CND.CatLicencia,CCND.ClaseCuenta AS TipCtaCnd,CND.NumCuenta AS CtaCnd,BCND.Banco AS BanCnd,FECN.Fondo AS FonEps,FPCN.Fondo AS FonPen,FACN.Fondo AS FonArl
--INFORMACION TRAILER Y PROPIETARIO
,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,V.UndCapc AS UndCapacidad,Comptmtos,CapComp,PasjerosPie,PasjerosSen,VR.IdPropietario AS IdPropTra,PT.RazonSocial AS NomPropTra,LPT.Localidad AS LugCedPt
,PT.Direccion AS DirProTra,PT.TelMovil AS CelProTra,PT.Telefono AS TelProTra
--INFORMACION PROPIEATARIO DEL VEHICULO
,V.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,NP.Direccion AS DirProV,NP.TelMovil AS CelularProV,NP.Telefono AS TelProV,V.IdPpd AS CdTipProp,TipoProp,VehPropio,Adquisc
 FROM Vehiculos  AS V INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS L ON V.IdLinea=L.IdLinea
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposMot AS TM ON V.IdTipoMot=TM.IdTipoMot
 INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria INNER JOIN PeajesCat AS CP ON V.IdCat=CP.IdCat
 INNER JOIN TiposFuel AS TF ON V.IdCom=TF.IdCom INNER JOIN TiposLub AS TL ON V.IdLub=TL.IdLub
 INNER JOIN TiposLla AS TLL ON V.IdTlla=TLL.IdTlla INNER JOIN Marcas AS ML ON V.IdMarlla=ML.IdMarca
 INNER JOIN Terceros AS NP ON  V.IdPropietario=NP.IdTercero  INNER JOIN Terceros AS NT ON  V.IdPoseedor=NT.IdTercero
 INNER JOIN Terceros AS NC ON  V.IdConductor=NC.IdTercero INNER JOIN TiposPpt AS TP ON V.IdPpd=TP.IdPpd
 INNER JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado  INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo INNER JOIN TiposAdm AS TA ON V.IdAdmon=TA.IdAdmon
 INNER JOIN TiposNivs AS NV ON V.IdNiv=NV.IdNiv
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NPV ON V.NitProv=NPV.IdTercero
 LEFT JOIN Terceros AS NS ON V.NitEmpSoat =NS.IdTercero LEFT JOIN Localidades AS LT ON V.CdLugTp=LT.IdLocal
 LEFT JOIN CentrosServ AS CS ON V.CdCenSer =CS.IdCenSer 
 LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
 LEFT JOIN VehRemolq AS RQ ON V.CdRemque=RQ.IdRemque
 LEFT JOIN Marcas AS MR ON RQ.IdMarca=MR.IdMarca LEFT JOIN TiposCol AS CR ON RQ.IdColor=CR.IdColor 
 LEFT JOIN TiposCar AS RC ON RQ.IdCrceria=RC.IdCrceria LEFT JOIN OrgTransito AS OG ON V.IdOrgTra=OG.IdOrgTra
 --CONSULTA PARA INFORMACION DEL CONDUCTOR
 LEFT JOIN TercCndtores AS CND ON V.IdConductor=CND.IdConductor LEFT JOIN Localidades AS LCCE ON NC.IdLugarCed=LCCE.IdLocal
 LEFT JOIN Localidades AS LCND ON NC.IdLocal=LCND.IdLocal LEFT JOIN ClaseCta AS CCND ON CND.IdClase=CCND.IdClase
 LEFT JOIN Bancos AS BCND ON CND.IdBanco=BCND.IdBanco LEFT JOIN Fondos AS FECN ON CND.CdFonEps=FECN.IdFondo
 LEFT JOIN Fondos AS FPCN ON CND.CdFonPen=FPCN.IdFondo LEFT JOIN Fondos AS FACN ON CND.CdFonArp=FACN.IdFondo
 --CONSULTA TRAILER
 LEFT JOIN VehRemolq AS VR ON V.CdRemque=VR.IdRemque LEFT JOIN Terceros AS PT ON VR.IdPropietario=PT.IdTercero
 LEFT JOIN Localidades AS LPT ON PT.IdLugarCed=LPT.IdLocal
 WHERE ClaseVeh LIKE ISNULL(@pmClaseVeh,'%') AND V.IdTipoVeh LIKE ISNULL(@pmIdTipoVeh,'%')
 AND V.IdMarca LIKE ISNULL(@pmIdMarca,'%') AND V.IdTipoMot LIKE ISNULL(@pmIdTipoMot,'%')
 AND V.IdCrceria LIKE ISNULL(@pmIdCrceria,'%')  AND V.Modelo LIKE ISNULL(@pmModelo,'%')
 AND Config LIKE ISNULL(@pmConfig,'%') AND V.IdCat LIKE ISNULL(@pmIdCat ,'%')
 AND ClaseMat  LIKE ISNULL(@pmClaseMat,'%') AND V.IdCom LIKE ISNULL(@pmIdCom,'%')
 AND V.IdLub LIKE ISNULL(@pmIdLub,'%') AND V.IdTlla LIKE ISNULL(@pmIdTlla,'%')
 AND IdMarlla LIKE ISNULL(@pmIdMarlla,'%')  AND V.IdPropietario LIKE ISNULL(@pmIdPropietario,'%')
 AND IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')  AND V.IdConductor LIKE ISNULL(@pmIdConductor,'%') 
 AND V.IdPpd LIKE ISNULL(@pmIdPpd,'%') AND V.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND V.IdAdmon LIKE ISNULL(@pmIdAdmon,'%') AND V.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND (V.Inactivo=ISNULL(@pmInactivo,0) or V.Inactivo=ISNULL(@pmInactivo,1))
 AND (V.FecCompra>=ISNULL(@pmFecComIni,CAST('19100101' AS SMALLDATETIME)) AND V.FecCompra<=ISNULL(@pmFecComFin,CAST('20781230' AS SMALLDATETIME)))
ORDER BY  IdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraRemCntdorFmt] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Item,C.IdTipoCon AS CdTipoCon,TipoContndor,NumContdor,PesoNeto,C.IdTipoPre AS CdTipoPrec,Precinto,NumPrecinto,ColorPrecinto,Sellos
,Peligroso,Perecedero,Toxico,Inflamable,Difmanejo,Fragil,DevCont,CdLocCont,Localidad,PatioCont,C.Observacion,NumSalidaSello,CdCiaSalSello,ItemSalSello
,CdProdSalida,NumSerialSello,NumComodato,FecVenceCom
 FROM Trn_TraRemCntdor AS C INNER JOIN TiposCnd AS TC ON C.IdTipoCon=TC.IdTipoCon
 INNER JOIN TiposPcnto AS TP ON C.IdTipoPre=TP.IdTipoPre
 LEFT JOIN Localidades AS L ON C.CdLocCont=L.IdLocal
 WHERE TipDoc=@pmTipDoc AND NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND IdCia=@pmIdCia
 ORDER BY  NumOrden
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemesaCnt] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT R.TipDoc AS CdTipDoc,R.NumOrden AS Num_Orden,R.IdCia AS CdCia,Compania,R.Fecha,FecDespacho,FecEntrega,HoraLlegada,IdCliente,NomCliente,T.RazonSocial
,R.IdAgencia AS CdAgencia,A.Agencia AS NomAgencia,A.CodAgencia AS Cod_Agencia,IdClieFact,IdRemitente,NomRemite,IdDestinatario,NomDestino,IdLocOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdLocDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,IdLocFletes,CF.Localidad AS LugarFletes,R.IdVehiculo AS PlacaVeh,R.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic
--Datos de contendor
,CN.Item,CN.IdTipoCon AS CdTipoCon,TipoContndor,NumContdor,CN.IdTipoPre AS CdTipoPrec,Precinto,NumPrecinto,ColorPrecinto,Sellos,Peligroso,Perecedero,Toxico,Inflamable,Difmanejo,Fragil
,DevCont,CdLocCont,LD.Localidad,PatioCont,CN.Observacion,NumSalidaSello,CdCiaSalSello,ItemSalSello,CN.CdProdSalida,CN.NumSerialSello,CN.NumComodato,CN.FecVenceCom
,Modalidad,TipoTarifa,VrCobro,VrPagos,VrFletes,VrCargue,VrDesCargue,VrEscolta,VrDevContdor,VrTraUrbano,VrEmbalajes,VrCargos,VrDctos,R.VrDeclarado AS ValDeclarado,R.VrSeguro AS ValSeguro,R.Cantidad AS CantTotal,PesoTotal
,R.IdMneda,VrTasa,NumPedido,IdCiaPed,FechaPed,TipDcm,NumDocmto,IdCiaDcm,FechaDcm,NumManif,IdCiaManif,EstCumplido,EstFactura,CdConcepto,Concepto,CdRutaTarif,TipCom,Comprobante,IdCiaCom,SerieGuia,NumGuia,CdForma,NumCausac,CdCiaCausac,EdoCausac
,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario
,RA.IdVend AS CdVend,VN.RazonSocial AS Vendedor,LugarCargue,LugarDescargue,NomContacto,TelContacto,emlContacto,ContacDestino,TelContacDest,emlContacDest,TomadorPoliza,PolizaSeguro
,NitCiaPoliza,NomCiaPoliza,FecVencePol,ManifMintrans,RemMintrans,TipSal,NumSalida,IdCiaSal,TipoRuta,TipoMintrans,CdBodega 
--Datos del cliente
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,NitRepLeg,NomRepLeg,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
,A.DirAgncia AS AgeDireccion,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,A.TelAgncia AS AgeTelefono,A.Referencia AS AgeReferencia
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPoseedor AS NitPoseedor,NPO.RazonSocial AS Poseedor
,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,V.IdGrupo AS CdGrupoPro,GrupoProp
 FROM Trn_TraRemesa AS R INNER JOIN Trn_TraRemAnexo AS RA ON R.TipDoc=RA.TipDoc  AND R.NumOrden=RA.NumOrden AND R.IdCia=RA.IdCia
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN TercCliente AS CLI ON R.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Localidades AS CO ON R.IdLocOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Localidades AS CF ON R.IdLocFletes=CF.IdLocal  INNER JOIN Terceros AS  CDT ON R.IdConductor=CDT.IdTercero 
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario
 INNER JOIN Vehiculos AS V ON R.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Terceros AS VN ON RA.IdVend=VN.IdTercero  LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  LEFT JOIN  Conceptos AS C ON R.CdConcepto=C.IdConcepto
 LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Trn_TraRemCntdor AS CN ON R.TipDoc=CN.TipDoc AND R.NumOrden=CN.NumOrden AND R.IdCia=CN.IdCia
 LEFT JOIN TiposCnd AS TCN ON CN.IdTipoCon=TCN.IdTipoCon LEFT JOIN TiposPcnto AS TPC ON CN.IdTipoPre=TPC.IdTipoPre
 LEFT JOIN Localidades AS LD ON CN.CdLocCont=LD.IdLocal
 WHERE R.TipDoc=@pmTipDoc AND R.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDespachosLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT D.TipDoc AS CdTipDoc,D.Manifiesto AS NumManif,D.IdCia AS CdCia,Compania,D.Fecha AS FecMuc,FecCargue,D.FecDespacho,D.IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,D.IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.IdRuta AS CdRuta,R.Ruta AS DescRuta,D.IdVehiculo AS PlacaVeh,M.nRemolque AS MucRemolque,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,T.TipoId AS PosTipoId,D.IdPoseedor AS NitPoseedor,T.Dv AS PosDv,T.RazonSocial AS NomPoseedor,NitRemite,Remitente,NitDestntario,Destinatario,DescMcia,D.PesoTotal,NumOrden,IdCiaOrd,D.Remesa,D.IdCiaRem
,D.Estado AS EdoDespacho,D.Anulado AS DesAnulado,D.FecDev,D.Observacion AS Observ,D.TimeSys,D.FecUpdate,D.IdUsuario AS CdUsuario,Usuario
--datos del vehíclo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
--datos del MAnifiesto
,M.VrFletes AS ManVrFletes , M.VrRetencion AS ManVrRetencion, M.VrReteIca As ManVrRteIca, M.VrAnticipo AS ManVrAnticipo
,M.VrAntAdic AS ManVrAntAdic, M.VrNeto AS ManVrNeto,M.VrPagos AS ManVrPagos, M.VrCargos AS ManVrCargos
,MA.NumCheque AS ManNumChe,MA.NumAnticipo As ManNumAnt
 FROM Trn_TraDespachos AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Trn_TraManifiesto M ON D.TipDoc =M.TipDoc AND D.Manifiesto =M.Manifiesto AND D.IdCia =M.IdCia
 INNER JOIN Trn_TraManifAnexo MA ON D.TipDoc =MA.TipDoc AND D.Manifiesto =MA.Manifiesto AND D.IdCia =MA.IdCia 
 INNER JOIN Localidades AS CO ON  D.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON D.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON D.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero 
 INNER JOIN Vehiculos AS V ON D.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd  LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN TercCndtores AS CT ON D.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN ( SELECT EO.TipOdp,EO.OrdPago,EO.IdCia ,SUM(EO.VrAbonado) AS ABONO,
 MAX(EO.NumEgreso)AS NumEgreso,MAX(EO.IdCia)AS IdCiaEgr,MAX(EO.TipCom ) AS TiPoegr,MAX(CEO.Fecha) AS FecEgr, max(CEO.FecCheque ) AS FecCheEgr
 FROM  Trn_TraEgrOrden EO  INNER JOIN Trn_Comprobantes CEO ON EO.TipCom =CEO.TipCom AND EO.NumEgreso =CEO.Comprobante AND EO.IdCia =CEO.IdCia
 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCia) AS InFEgr ON M.TipOdp =InFEgr.TipOdp AND M.OrdPago=InFEgr.OrdPago AND M.IdCiaOdp=InFEgr.IdCia 
 WHERE D.TipDoc=@pmTipDoc AND  D.FecDespacho BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_ContratoDso] @pmIdEmpleado VARCHAR(16)=Null,@pmIndefinido BIT=Null,@pmIdTipCon VARCHAR(4)=Null,@pmIdCargo VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmIdSubCos VARCHAR(16)=Null,@pmIdNom VARCHAR(4)=Null,@pmTipoLiquida CHAR(1)=Null
,@pmSalMinimo BIT=Null,@pmSalIntegral BIT=Null,@pmFPnomina VARCHAR(10)=Null,@pmModoRet INT=Null,@pmIdClase VARCHAR(4)=Null,@pmInactivo BIT=Null,@pmIdEstado VARCHAR(4)=Null
,@pmNContrato INT=Null,@pmFecIngresoIni SMALLDATETIME=Null,@pmFecIngresoFin SMALLDATETIME=Null,@pmATFinMes BIT=Null,@pmSegFinMes BIT=Null,@pmDchDotacion BIT=Null
--parametros de empleados
,@pmTipoSangre CHAR(2)=Null,@pmFactorRh CHAR(1)=Null,@pmSexo CHAR(1)=Null,@pmIdEstCivil VARCHAR(4)=Null,@pmClaseLib INT=Null, @pmIdProf VARCHAR(4)=Null,@pmTallaCam CHAR(2)=Null
,@pmTallaPan CHAR(2)=Null,@pmTallaZap CHAR(2)=Null,@pmIdFonPen VARCHAR(8)=Null,@pmIdFonEps VARCHAR(8)=Null,@pmIdFonArp VARCHAR(8)=Null,@pmIdFonCes VARCHAR(8)=Null,@pmIdCajaCom VARCHAR(8)=Null
,@pmIdBanco VARCHAR(4)=Null,@pmEmp_IdEstado VARCHAR(4)=Null,@pmEmp_Inactivo BIT=Null,@pmEsCondtor BIT=Null,@pmFecNacmtoIni SMALLDATETIME=Null,@pmFecNacmtoFin SMALLDATETIME=Null
 AS 

 SELECT  C.IdEmpleado AS IdEmplado,Apellidos,Nombres,C.NContrato AS NCntrato,C.IdTipCon AS CodTCon,TipoContrato,FecIngreso,FecVigencia,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo
,C.IdJornada AS IdJrnada,Jornada,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCCosto AS IdCCto,CCosto,C.IdSubCos AS IdSubcen,SubCosto,C.IdCia AS CodCia,Compania
,C.IdClase AS CodClaRie,ClaseRiesgo,Tarifa,TipoTrabj,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipoLiq,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,FspFinmes,DenyPEN,DenyEPS,DenyARP
,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,DsoSabado,FecFinPrueba,TipoPrendDot,TipoMedSent,BenefPacto,C.IdEstado AS IdEstdCon,EC.Estado AS EstdoCon,EC.NColor AS ConEstcolor,C.Inactivo AS Inactvo,C.FecAdd AS Fec_Add,C.FecUpdate AS Fec_Update
,C.IdUsuario AS IdUsuari,U.Usuario AS Con_Usuario,IdCot,C.IdGrupo AS GrupAR,VrUpc
--empleados
,E.Direccion AS Dirccion,E.IdLocal AS CodCiu,L.Localidad AS Ciudad,Telefono,TelMovil,e_mail,VvdaPropia,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,IdEstCivil,EstCivil,NHijos,TallaCam,TallaPan,TallaZap,UsaGafas,E.IdProf AS CodProf,Profesion
,LibMilitar,ClaseLib,FechaLib,DistMil,CertJudicial,FecJudicial,VigJudicial,Pasporte,VigPaspte,Licencia,E.IdLugar AS CodLugar,LugarLic,CatLicencia,FecLicencia,VigLicencia,IdLugarCed,LC.Localidad AS LugarCed,Conyugue,ConyFecNac,ConyTrabaja
,ConyEmpresa,ConyCargo,ConyFing,ConyTel,E.IdClase AS CodClase,ClaseCuenta,NumCuenta,E.IdBanco AS CodBco,Banco,IdFonPen,FP.Fondo AS FondPen,IdFonEps,FS.Fondo AS FondEps,IdFonArp,FR.Fondo AS FondArp,IdFonCes,FC.Fondo AS FondCes
,IdCajaCom,CC.Fondo AS CajaComp,FecVincula,FecUltAum,FecUltCes,FecUltPri,FecUltVac,FecUltDot,FecUltExm,FecUltCap,NCAnterior,FecIngAnt,FecRetAnt,EsCondtor,pVehiculo,FotoEmp,FotoLic,FotoFirma,Observacion,E.IdEstado AS Emp_IdEstado
,ET.Estado AS Emp_estado,E.Inactivo AS Emp_Inactivo,TipoId,Codigo,ET.NColor AS EmpEstcolor,E.FechaAdd AS EmpFecAdd,E.FechaUpdate AS EmpFecupd,E.IdUsuario AS Emp_IdUsuario,UE.Usuario AS Emp_Usuario,SubTipoCot
 FROM Emp_Contrato AS C INNER JOIN Empleados AS E ON C.IdEmpleado=E.IdEmpleado INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN Dependencias AS D ON C.IdDep=D.IdDep  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala  INNER JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto
 INNER JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EC ON C.IdEstado=EC.IdEstado 
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase
 INNER JOIN Localidades AS L ON E.IdLocal=L.IdLocal INNER JOIN Localidades AS LC ON E.IdLugarCed=LC.IdLocal
 INNER JOIN EstadoCiv AS ECV ON E.IdEstCivil=ECV.IdEstado INNER JOIN ExpLicencias AS LL ON E.IdLugar=LL.IdLugar
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS FC ON E.IdFonCes=FC.IdFondo INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo
 INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase  INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco
 INNER JOIN EstadoTer AS ET ON E.IdEstado=ET.IdEstado INNER JOIN adm_Usuarios AS UE ON E.IdUsuario=UE.IdUsuario
 WHERE C.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND C.IdCargo LIKE ISNULL(@pmIdCargo,'%')
 AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND C.IdSubCos LIKE ISNULL(@pmIdSubCos,'%')  AND C.IdNom LIKE ISNULL(@pmIdNom,'%') 
 AND C.TipoLiquida LIKE ISNULL(@pmTipoLiquida,'%') AND FPnomina LIKE ISNULL(@pmFPnomina,'%')  AND C.IdClase LIKE ISNULL(@pmIdClase,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 AND (C.NContrato>=ISNULL(@pmNContrato,0)  AND C.NContrato<=ISNULL(@pmNContrato,2147483647)) 
 AND (ModoRet>=ISNULL(@pmModoRet,0)  AND ModoRet<=ISNULL(@pmModoRet,2147483647)) 
 AND (SalMinimo=ISNULL(@pmSalMinimo,0) or SalMinimo=ISNULL(@pmSalMinimo,1))
 AND (Indefinido=ISNULL(@pmIndefinido,0) or Indefinido=ISNULL(@pmIndefinido,1))
 AND (SalIntegral=ISNULL(@pmSalIntegral,0) or SalIntegral=ISNULL(@pmSalIntegral,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (ATFinMes=ISNULL(@pmATFinMes,0) or ATFinMes=ISNULL(@pmATFinMes,1))
 AND (SegFinMes=ISNULL(@pmSegFinMes,0) or SegFinMes=ISNULL(@pmSegFinMes,1))
 AND (DchDotacion=ISNULL(@pmDchDotacion,0) or DchDotacion=ISNULL(@pmDchDotacion,1))
 AND (FecIngreso>=ISNULL(@pmFecIngresoIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngresoFin,CAST('20781230' AS SMALLDATETIME)))
 AND TipoSangre LIKE ISNULL(@pmTipoSangre,'%%') AND FactorRh LIKE ISNULL(@pmFactorRh,'%') AND Sexo LIKE ISNULL(@pmSexo,'%')
 AND IdEstCivil LIKE ISNULL(@pmIdEstCivil,'%') AND E.IdProf LIKE ISNULL(@pmIdProf,'%') AND TallaCam LIKE ISNULL(@pmTallaCam,'%%')
 AND TallaPan LIKE ISNULL(@pmTallaPan,'%%') AND TallaZap LIKE ISNULL(@pmTallaZap,'%%') AND IdFonPen LIKE ISNULL(@pmIdFonPen,'%')
 AND IdFonCes LIKE ISNULL(@pmIdFonCes,'%') AND IdFonArp LIKE ISNULL(@pmIdFonArp,'%') AND IdFonEps LIKE ISNULL(@pmIdFonEps,'%')
 AND IdCajaCom LIKE ISNULL(@pmIdCajaCom ,'%') AND E.IdBanco LIKE ISNULL(@pmIdBanco,'%') AND E.IdEstado LIKE ISNULL(@pmEmp_IdEstado,'%')
 AND (ClaseLib>=ISNULL(@pmClaseLib,0)  AND ClaseLib<=ISNULL(@pmClaseLib,2147483647)) 
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (EsCondtor=ISNULL(@pmEsCondtor,0) or EsCondtor=ISNULL(@pmEsCondtor,1))
 AND (FecNacmto>=ISNULL(@pmFecNacmtoIni,CAST('19100101' AS SMALLDATETIME)) AND FecNacmto<=ISNULL(@pmFecNacmtoFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY Apellidos,Nombres,C.NContrato
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomNov_Cr] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT IdPeriodo,N.IdEmpleado AS IdEmpledo,Apellidos,Nombres,E.NContrato AS NumContrato,Item,N.IdConcepto AS IdConcept,Concepto,Detalle,Cantidad,VrUnitario,VrTotal,N.Tarifa AS TrfaBase,VrOrigen,ClaseCon,Unidad,TimeSys
,N.FecUpdate AS FecUpd,N.IdUsuario AS IdUsuari,Usuario,OrigCargue,Num_Contrato
--Datos empleado
,Codigo,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdDep AS CodDep,Dependencia,C.IdInstala AS CodInst,Instlacion,C.IdCCosto AS CodCcto,CCosto
,C.IdSubCos AS CodSubcen,SubCosto,C.IdNom AS CodNom,TipoNomina,C.TipoLiquida AS TipLiq,Asistencia,NoDevenga,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes
,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado AS IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada
 FROM Trn_NomNov AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado  INNER JOIN  NomConceptos AS CN ON N.IdConcepto=CN.IdConcepto
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf   INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco  INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo  INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 LEFT JOIN  Emp_Contrato AS C ON E.NContrato=C.NContrato  LEFT JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 LEFT JOIN Dependencias AS DP ON C.IdDep=DP.IdDep  LEFT JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 LEFT JOIN CentroCosto AS CO ON C.IdCCosto=CO.IdCCosto  LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposNom AS TN ON C.IdNom=TN.IdNom LEFT JOIN EstadoDoc AS EN ON C.IdEstado=EN.IdEstado 
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo LEFT JOIN Areas AS AR ON C.IdArea=AR.IdArea
 LEFT JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase LEFT JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 WHERE IdPeriodo=@pmIdPeriodo AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 ORDER BY  N.IdEmpleado,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionMtto] @pmTipDoc VARCHAR(3),@pmRequisicionIni INT,@pmRequisicionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS CdTipo,TipoDoc,R.Requisicion,R.IdCia AS CdCia,Compania,Fecha,FechaVence,R.IdConcepto AS CdConcepto,Concepto,R.IdRespons AS CdOperario,T.RazonSocial AS NomOperario
,R.IdCCosto AS CdCCosto,CCosto,R.IdSubCos AS CdSubCos,R.IdDep AS CdDep,Dependencia,VrSubTotal,R.Cantidad AS CantTotal,R.NContrato AS NumOrden,R.IdCiaCont AS CdCiaOrden,R.Num_Vehic,R.Num_Trailer
,R.NitCliente AS NitCentServ,CL.RazonSocial AS NomCentServ,R.Modalidad,DirEntrega,R.IdLocEnt,Localidad,Departamento,NumAprob,FecAprob,CdUsuAprob,OrigenAdd,R.Anulado
,NomContacto,TelsContacto,EmailContacto,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,R.TimeSys AS FecCrea,R.FecUpdate,R.IdCiaCrea,R.IdUsuario AS CdUsuario,U.Usuario
,Item,TipoItem,D.IdProducto AS CdProducto,D.Descripcion,DescripProd,D.Cantidad,D.IdUnd AS CdUnd,Unidad,D.VrUnitario,D.CdBodega,Bodega,TipoRef,D.TipOdt,D.NumOrden AS Num_Orden,D.IdCiaOdt,EstadoReq
,D.TipSal,D.NumSalida,D.IdCiaSal,D.CantSalida,D.IdOperario AS DetIdOperario,NP.RazonSocial AS DetOperario,D.CdCenServ,CentroServ,D.NitTercero,NC.RazonSocial
,D.NumVehic,D.NumParte,D.VrPrecioUnd,D.Observacion AS DetObserv,D.FechaCrea AS DetFecCrea,D.IdUsuario AS DetCdUsuario,DU.Usuario AS DetUsuario
,T.TipoId,T.Codigo,T.Dv,T.Telefono,T.TelMovil,T.e_mail
 FROM Trn_Requisicion AS R INNER JOIN Trn_MttoOrdenReq AS D ON R.TipDoc=D.TipReq AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto INNER JOIN Dependencias AS DP ON R.IdDep=DP.IdDep
 INNER JOIN Localidades AS L ON R.IdLocEnt=L.IdLocal INNER JOIN Departamentos AS DT ON L.IdDep=DT.IdDep
 INNER JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero LEFT JOIN Terceros AS NP ON D.IdOperario=NP.IdTercero
 LEFT JOIN Terceros AS NC ON D.NitTercero=NC.IdTercero LEFT JOIN Bodegas AS BG ON D.CdBodega=BG.IdBodega
 LEFT JOIN CentrosServ AS CS ON D.CdCenServ=CS.IdCenSer LEFT JOIN adm_Usuarios AS DU ON D.IdUsuario=DU.IdUsuario 
 WHERE R.TipDoc=@pmTipDoc AND R.Requisicion BETWEEN  @pmRequisicionIni AND @pmRequisicionFin AND R.IdCia=@pmIdCia
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,CodDiaEnt
,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,G.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.CdGruClie AS CdAgeGru,GA.GrupoClie AS GrupoAgenc,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
 LEFT JOIN GruposCli AS GA ON A.CdGruClie=GA.IdGrupo 
 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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryComFacturaLta] @pmSaldo DECIMAL(14,2),@pmFecActual SMALLDATETIME
,@pmIdCuenta VARCHAR(16)=Null,@pmIdProveedor VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
,@pmTipFac VARCHAR(3)=Null,@pmTipDoc VARCHAR(3)=Null,@pmFechaIni SMALLDATETIME=Null,@pmFechaFin SMALLDATETIME=Null
 AS 
 SELECT C.IdCuenta AS CdCuenta,NomCuenta,IdProveedor,RazonSocial,TipFac,TipoDoc,Factura,C.IdCia AS CdCia,Compania
,Item,VrFactura,VrAbonado,VrFactura-VrAbonado AS ValorSaldo,FecEmision,FecVence,DATEDIFF(day,FecVence,@pmFecActual) AS DiasMora
,TipDoc,Documento,IdCiaDoc,TipCom,TipoCom,Comprobante,ItemCom,Detalle,Referencia,pVehiculo,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob
--datos del proveedor
,TipoId,Dv,T.Codigo AS CodigoProv,NomCial,SiglaRaz,T.Direccion AS DirProveedor,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDep,Departamento
,Telefono,Fax,e_mail,SitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte
,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga
,TP.IdClase AS CodClase,NumCuenta,TP.IdBanco AS CdBanco,Banco,Autoret
 FROM Trn_ComFactura AS C INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero
 INNER JOIN Puc AS P ON C.IdCuenta=P.IdCuenta INNER JOIN  Companias AS CI ON C.IdCia=CI.IdCia
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN TiposCom AS TC ON C.TipCom=TC.IdCom  LEFT JOIN Sys_TiposDoc AS TD ON C.TipFac=TD.IdDoc
 LEFT JOIN TercProvee AS TP ON C.IdProveedor=TP.IdProv  LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Bancos AS BP ON TP.IdBanco=BP.IdBanco
 WHERE (VrFactura-VrAbonado)>@pmSaldo AND C.IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
 AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND  C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND TipDoc LIKE ISNULL(@pmTipDoc,'%' ) AND TipFac LIKE ISNULL(@pmTipFac,'%')
 AND (FecEmision>=ISNULL(@pmFechaIni,CAST('19100101' AS SMALLDATETIME)) AND FecEmision<=ISNULL(@pmFechaFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY C.IdCuenta,RazonSocial,FecVence
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryEntradasNetDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT E.TipDoc,E.Entrada,E.IdCia AS CdCia,Compania,E.Fecha,E.IdConcepto AS CdConcepto,Concepto,E.IdProv AS NitPrevee,T.RazonSocial AS NomProveedor,E.Factura,E.FechaFac,E.FechaVence
,E.VrSubTotal,E.VrDescuento,E.VrImpuesto,E.VrRetencion,E.VrReteICA,E.VrReteIVA,E.VrReteCREE,E.VrFletes,E.VrOtros,E.VrSobretasa,E.VrImpGlobal,E.VrCargos,E.VrOtrDcto,E.VrBomberil,E.VrNeto
,E.NitFletes,TF.RazonSocial AS NomFletes,E.VrRetFlete,E.VrIcaFlete,E.FletesCosto,E.TipOdc,E.OCompra,E.IdCiaOdc,E.MulPlazos,E.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,E.CxPagar,E.DocEquiv,E.BaseImp,E.BaseRet
,E.TarifaRet,E.TarifaIca,E.TarifaRiv,E.TarifaRtc,E.TarifaBom,E.VrIvaMvc,E.CdCCosto,CCosto,E.CdSubCos,SubCosto,E.Modalidad
--detalles
 ,K.Item,K.IdProducto AS CdProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,K.Entradas,K.Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,K.VrUnitario
,VrCostoEnt,VrCostoSal,K.TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva,VrIvaEnt,VrIvaSal,K.Sobretasa,K.Soldicom,K.ImpGlobal,K.VrCostProm,K.Unidades,K.Descripcion,K.Referencia,Referencia2
,K.TipOrd,K.NumOrden,K.IdCiaOrd,TipDocDev,K.NumDocDev,OtroImpto,K.VrBruto,K.VrBase,K.Servcios,K.VrImvCosto,K.TarifaIco,K.VrImpCon,K.CantObseq,K.VrIvaObseq,K.CdTanque
,E.TipCom,E.Comprobante,E.IdCiaCom,E.Anulado,E.NumDev,FecDev,E.Observacion AS Observ,E.IdEstado AS CdEstado,Estado,E.TimeSys,E.FecUpdate AS Fec_Update,IdCiaCrea,E.IdUsuario AS CdUsuario,Usuario
--Información del tercero
,T.TipoId AS TercTipoId,T.Dv AS TercDV,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCodLocal,Localidad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS TercCodSector,SectorEco,T.IdRegimen AS TercCodRegimen,Regimen,T.TipEnte AS TercTipoEnte
--Información del proveedor
,TP.IdGrupo AS ProvGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga,Autoret
 FROM Trn_Entradas AS E INNER JOIN Trn_Kardex AS K ON E.TipDoc=K.TipDoc AND E.Entrada=K.Documento AND E.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON E.IdCia=CN.IdCia INNER JOIN Terceros AS T ON E.IdProv=T.IdTercero
 INNER JOIN Conceptos AS C ON E.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON E.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON E.IdUsuario=U.IdUsuario INNER JOIN  Plazos AS PZ ON E.IdPlazo=PZ.IdPlazo
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 LEFT JOIN CentroCosto AS CC ON E.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON E.CdSubCos=SC.IdSubCos
 LEFT JOIN TercProvee AS TP ON E.IdProv=TP.IdProv LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Terceros AS TF ON E.NitFletes=TF.IdTercero
 WHERE E.TipDoc=@pmTipDoc AND E.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND E.IdCia LIKE ISNULL(@pmIdCia,'%%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,Concepto,D.IdProv,T.RazonSocial,D.Factura,E.FechaFac,E.FechaVence
,D.VrSubTotal,D.VrDescuento,D.VrImpuesto,D.VrRetencion,D.VrReteICA,D.VrReteIVA,D.VrReteCREE,D.VrFletes,D.VrOtros,D.VrSobretasa,D.VrImpGlobal,D.VrCargos,D.VrOtrDcto,D.VrBomberil,D.VrNeto
,D.NitFletes,TF.RazonSocial,D.VrRetFlete,D.VrIcaFlete,D.FletesCosto,E.TipOdc,E.OCompra,E.IdCiaOdc,MulPlazos,E.IdPlazo,Plazo,NVmto,DiasPago,D.CxPagar,D.DocEquiv,D.BaseImp,D.BaseRet
,E.TarifaRet,E.TarifaIca,E.TarifaRiv,D.TarifaRtc,D.TarifaBom,D.VrIvaMvc,D.CdCCosto,CCosto,D.CdSubCos,SubCosto,D.Modalidad
,K.Item,K.IdProducto,DescripProd,K.IdBodega,Bodega,K.Entradas,K.Salidas,K.IdUnd,UM.Unidad,K.VrUnitario
,VrCostoEnt,VrCostoSal,K.TarifaDct,VrDctoEnt,VrDctoSal,K.TarifaIva,VrIvaEnt,VrIvaSal,K.Sobretasa,K.Soldicom,K.ImpGlobal,K.VrCostProm,K.Unidades,K.Descripcion,K.Referencia,Referencia2
,K.TipOrd,K.NumOrden,K.IdCiaOrd,TipDocDev,K.NumDocDev,OtroImpto,K.VrBruto,K.VrBase,K.Servcios,K.VrImvCosto,K.TarifaIco,K.VrImpCon,K.CantObseq,K.VrIvaObseq,K.CdTanque
,D.TipCom,D.Comprobante,D.IdCiaCom,0,D.Entrada,FecDoc,D.Observacion,D.IdEstado,Estado,D.TimeSys,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
,T.TipoId,T.Dv,T.Codigo,T.NomCial,T.Direccion,T.IdLocal,Localidad,L.IdDep,Departamento,T.Telefono,T.Fax,T.e_mail,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
,TP.IdGrupo,GrupoClie,NitContac,NomContac,TelContac,emlContac,CargContac,DirOrdComp,DiasEntga,Autoret
 FROM Trn_DevEnt AS D INNER JOIN Trn_Entradas AS E ON D.TipDoc=E.TipDoc AND D.Entrada=E.Entrada AND D.IdCiaDoc=E.IdCia
 INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 INNER JOIN Terceros AS T ON D.IdProv=T.IdTercero INNER JOIN  Conceptos AS CN ON D.IdConcepto=CN.IdConcepto 
 INNER JOIN Companias AS CI ON D.IdCia=CI.IdCia INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal 
 INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen
 INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector INNER JOIN Plazos AS PZ ON E.IdPlazo=PZ.IdPlazo
 INNER JOIN ProdMcias AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 LEFT JOIN CentroCosto AS CC ON D.CdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON D.CdSubCos=SC.IdSubCos
 LEFT JOIN TercProvee AS TP ON D.IdProv=TP.IdProv  LEFT JOIN GruposCli AS GP ON TP.IdGrupo=GP.IdGrupo
 LEFT JOIN Terceros AS TF ON D.NitFletes=TF.IdTercero
 WHERE D.TipDoc=@pmTipDoc AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsVehiculos] @pmIdVehiculo VARCHAR(10),@pmNumVeh VARCHAR(10),@pmClaseVeh VARCHAR(10),@pmIdTipoVeh VARCHAR(4),@pmIdMarca VARCHAR(4),@pmIdLinea VARCHAR(10),@pmIdColor VARCHAR(4),@pmIdTipoMot VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmFecRep SMALLDATETIME,@pmConfig VARCHAR(5),@pmVehArtic BIT,@pmNumLlan INT,@pmNumLlans INT,@pmIdCat VARCHAR(4),@pmCdCatv VARCHAR(4)
,@pmClaseMat VARCHAR(10),@pmCilind DECIMAL(14,4),@pmCapTanq DECIMAL(14,4),@pmIdCom VARCHAR(4),@pmIdLub VARCHAR(4),@pmIdTlla VARCHAR(4),@pmIdMarlla VARCHAR(4),@pmPesoVacio DECIMAL(14,4),@pmPesoMax DECIMAL(14,4),@pmNumMotor VARCHAR(30),@pmSerieChasis VARCHAR(30),@pmNumSerie VARCHAR(30),@pmCdRemque VARCHAR(10),@pmLongitud DECIMAL(14,4),@pmCarrAlto DECIMAL(14,4),@pmCarrAncho DECIMAL(14,4),@pmCarrLargo DECIMAL(14,4)
,@pmCarrCapac DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmComptmtos INT,@pmCapComp VARCHAR(50),@pmPasjerosPie INT,@pmPasjerosSen INT,@pmNitEmpresa VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmIdPpd VARCHAR(4),@pmAdquisc VARCHAR(10),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME,@pmVrComcial MONEY,@pmVrAseg MONEY,@pmVrAvaludo MONEY,@pmVidaUtil INT
,@pmFecSalida SMALLDATETIME,@pmNContrato INT,@pmIdAdmon VARCHAR(4),@pmIdNiv VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmCdGrupR VARCHAR(4),@pmCdTarifa VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmNumSoat VARCHAR(30),@pmFecSoat SMALLDATETIME,@pmVigSoat SMALLDATETIME,@pmNitEmpSoat VARCHAR(16),@pmTarjProp VARCHAR(30),@pmFecTProp SMALLDATETIME
,@pmVigTProp SMALLDATETIME,@pmCdLugTp VARCHAR(8),@pmUlttramite VARCHAR(150),@pmRespCivil VARCHAR(30),@pmFecRCivil SMALLDATETIME,@pmVigRCivil SMALLDATETIME,@pmRegNalCarga VARCHAR(30),@pmFecRegNal SMALLDATETIME,@pmVigRegNal SMALLDATETIME,@pmKmInicial INT,@pmKmActual INT,@pmKm2Actual INT,@pmRegtradora BIT,@pmCentInicial INT,@pmCentFinal INT,@pmVrLmtCred MONEY,@pmVrSaldoAct MONEY,@pmDescripcion VARCHAR(100)
,@pmObservacion VARCHAR(250),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmPathFoto VARCHAR(30),@pmFecPriServ SMALLDATETIME,@pmFecUltServ SMALLDATETIME,@pmFecUltAcc SMALLDATETIME,@pmTieneAcc BIT,@pmFecPagImp SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmInactivo BIT
,@pmTipoAfil VARCHAR(10),@pmRevTecMec VARCHAR(30),@pmFecTecMec SMALLDATETIME,@pmVigTecMec SMALLDATETIME,@pmCertGases VARCHAR(30),@pmFecCertGas SMALLDATETIME,@pmVigCertGas SMALLDATETIME,@pmTarjOper VARCHAR(30),@pmFecTarjOper SMALLDATETIME,@pmVigTarjOper SMALLDATETIME,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
,@pmValorCupo MONEY,@pmObligaTProd BIT,@pmGarantiaAcc BIT,@pmDocCompleta BIT,@pmCertMovilizacion VARCHAR(20),@pmFecCertMovil SMALLDATETIME,@pmVigCertMovil SMALLDATETIME,@pmCdRutaHab VARCHAR(4),@pmDeclaracImp VARCHAR(50),@pmTipoIngreso VARCHAR(4),@pmIdOrgTra VARCHAR(8),@pmGPSoperador VARCHAR(250),@pmGPSUsuario VARCHAR(50),@pmGPSClave VARCHAR(50),@pmCantFiltros DECIMAL(14,4)
 AS 
 INSERT INTO Vehiculos (IdVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq,IdCom,IdLub,IdTlla,IdMarlla,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,IdPpd,Adquisc,NitProv,FecCompra,VrComcial,VrAseg
,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,FechaAdd,IdUsuario,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,GPSoperador,GPSUsuario,GPSClave,CantFiltros)
 VALUES (@pmIdVehiculo,@pmNumVeh,@pmClaseVeh,@pmIdTipoVeh,@pmIdMarca,@pmIdLinea,@pmIdColor,@pmIdTipoMot,@pmIdCrceria,@pmModelo,@pmFecRep,@pmConfig,@pmVehArtic,@pmNumLlan,@pmNumLlans,@pmIdCat,@pmCdCatv,@pmClaseMat,@pmCilind,@pmCapTanq,@pmIdCom,@pmIdLub,@pmIdTlla,@pmIdMarlla,@pmPesoVacio,@pmPesoMax,@pmNumMotor,@pmSerieChasis,@pmNumSerie,@pmCdRemque,@pmLongitud,@pmCarrAlto,@pmCarrAncho,@pmCarrLargo,@pmCarrCapac
,@pmUndCapc,@pmComptmtos,@pmCapComp,@pmPasjerosPie,@pmPasjerosSen,@pmNitEmpresa,@pmIdPropietario,@pmIdPoseedor,@pmIdConductor,@pmIdPpd,@pmAdquisc,@pmNitProv,@pmFecCompra,@pmVrComcial,@pmVrAseg,@pmVrAvaludo,@pmVidaUtil,@pmFecSalida,@pmNContrato,@pmIdAdmon,@pmIdNiv,@pmIdGrupo,@pmCdGrupR,@pmCdTarifa,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmNumSoat,@pmFecSoat,@pmVigSoat,@pmNitEmpSoat,@pmTarjProp,@pmFecTProp
,@pmVigTProp,@pmCdLugTp,@pmUlttramite,@pmRespCivil,@pmFecRCivil,@pmVigRCivil,@pmRegNalCarga,@pmFecRegNal,@pmVigRegNal,@pmKmInicial,@pmKmActual,@pmKm2Actual,@pmRegtradora,@pmCentInicial,@pmCentFinal,@pmVrLmtCred,@pmVrSaldoAct,@pmDescripcion,@pmObservacion,@pmCdCenSer,@pmCdLocal,@pmUbicacion,@pmPathFoto,@pmFecPriServ,@pmFecUltServ,@pmFecUltAcc,@pmTieneAcc,@pmFecPagImp,@pmIdEstado,@pmInactivo
,@pmTipoAfil,@pmRevTecMec,@pmFecTecMec,@pmVigTecMec,@pmCertGases,@pmFecCertGas,@pmVigCertGas,@pmTarjOper,@pmFecTarjOper,@pmVigTarjOper,@pmFechaAdd,@pmIdUsuario,@pmValorCupo,@pmObligaTProd,@pmGarantiaAcc,@pmDocCompleta,@pmCertMovilizacion,@pmFecCertMovil,@pmVigCertMovil,@pmCdRutaHab,@pmDeclaracImp,@pmTipoIngreso,@pmIdOrgTra,@pmGPSoperador,@pmGPSUsuario,@pmGPSClave,@pmCantFiltros)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsVehiculos_Sel] @pmIdVehiculo VARCHAR(10),@pmNewVehiculo VARCHAR(10)
 AS 
 INSERT INTO Vehiculos (IdVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq,IdCom,IdLub,IdTlla,IdMarlla,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,IdPpd,Adquisc,NitProv,FecCompra,VrComcial,VrAseg
,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,FechaAdd,IdUsuario,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,GPSoperador,GPSUsuario,GPSClave,CantFiltros)
SELECT  @pmNewVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq,IdCom,IdLub,IdTlla,IdMarlla,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen,NitEmpresa,IdPropietario,IdPoseedor,IdConductor,IdPpd,Adquisc,NitProv,FecCompra,VrComcial,VrAseg
,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia,FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,FechaAdd,IdUsuario,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,GPSoperador,GPSUsuario,GPSClave,CantFiltros
 FROM Vehiculos WHERE  IdVehiculo=@pmIdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpVehiculos] @pmIdVehiculo VARCHAR(10),@pmNumVeh VARCHAR(10),@pmClaseVeh VARCHAR(10),@pmIdTipoVeh VARCHAR(4),@pmIdMarca VARCHAR(4),@pmIdLinea VARCHAR(10),@pmIdColor VARCHAR(4),@pmIdTipoMot VARCHAR(4),@pmIdCrceria VARCHAR(4),@pmModelo VARCHAR(4),@pmFecRep SMALLDATETIME,@pmConfig VARCHAR(5),@pmVehArtic BIT,@pmNumLlan INT,@pmNumLlans INT,@pmIdCat VARCHAR(4),@pmCdCatv VARCHAR(4)
,@pmClaseMat VARCHAR(10),@pmCilind DECIMAL(14,4),@pmCapTanq DECIMAL(14,4),@pmIdCom VARCHAR(4),@pmIdLub VARCHAR(4),@pmIdTlla VARCHAR(4),@pmIdMarlla VARCHAR(4),@pmPesoVacio DECIMAL(14,4),@pmPesoMax DECIMAL(14,4),@pmNumMotor VARCHAR(30),@pmSerieChasis VARCHAR(30),@pmNumSerie VARCHAR(30),@pmCdRemque VARCHAR(10),@pmLongitud DECIMAL(14,4),@pmCarrAlto DECIMAL(14,4),@pmCarrAncho DECIMAL(14,4),@pmCarrLargo DECIMAL(14,4)
,@pmCarrCapac DECIMAL(14,4),@pmUndCapc VARCHAR(10),@pmComptmtos INT,@pmCapComp VARCHAR(50),@pmPasjerosPie INT,@pmPasjerosSen INT,@pmNitEmpresa VARCHAR(16),@pmIdPropietario VARCHAR(16),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmIdPpd VARCHAR(4),@pmAdquisc VARCHAR(10),@pmNitProv VARCHAR(16),@pmFecCompra SMALLDATETIME,@pmVrComcial MONEY,@pmVrAseg MONEY,@pmVrAvaludo MONEY,@pmVidaUtil INT
,@pmFecSalida SMALLDATETIME,@pmNContrato INT,@pmIdAdmon VARCHAR(4),@pmIdNiv VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmCdGrupR VARCHAR(4),@pmCdTarifa VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmNumSoat VARCHAR(30),@pmFecSoat SMALLDATETIME,@pmVigSoat SMALLDATETIME,@pmNitEmpSoat VARCHAR(16),@pmTarjProp VARCHAR(30),@pmFecTProp SMALLDATETIME
,@pmVigTProp SMALLDATETIME,@pmCdLugTp VARCHAR(8),@pmUlttramite VARCHAR(150),@pmRespCivil VARCHAR(30),@pmFecRCivil SMALLDATETIME,@pmVigRCivil SMALLDATETIME,@pmRegNalCarga VARCHAR(30),@pmFecRegNal SMALLDATETIME,@pmVigRegNal SMALLDATETIME,@pmKmInicial INT,@pmKmActual INT,@pmKm2Actual INT,@pmRegtradora BIT,@pmCentInicial INT,@pmCentFinal INT,@pmVrLmtCred MONEY,@pmVrSaldoAct MONEY,@pmDescripcion VARCHAR(100)
,@pmObservacion VARCHAR(250),@pmCdCenSer VARCHAR(4),@pmCdLocal VARCHAR(8),@pmUbicacion VARCHAR(100),@pmPathFoto VARCHAR(30),@pmFecPriServ SMALLDATETIME,@pmFecUltServ SMALLDATETIME,@pmFecUltAcc SMALLDATETIME,@pmTieneAcc BIT,@pmFecPagImp SMALLDATETIME,@pmIdEstado VARCHAR(4),@pmInactivo BIT
,@pmTipoAfil VARCHAR(10),@pmRevTecMec VARCHAR(30),@pmFecTecMec SMALLDATETIME,@pmVigTecMec SMALLDATETIME,@pmCertGases VARCHAR(30),@pmFecCertGas SMALLDATETIME,@pmVigCertGas SMALLDATETIME,@pmTarjOper VARCHAR(30),@pmFecTarjOper SMALLDATETIME,@pmVigTarjOper SMALLDATETIME,@pmFechaUpdate SMALLDATETIME
,@pmValorCupo MONEY,@pmObligaTProd BIT,@pmGarantiaAcc BIT,@pmDocCompleta BIT,@pmCertMovilizacion VARCHAR(20),@pmFecCertMovil SMALLDATETIME,@pmVigCertMovil SMALLDATETIME,@pmCdRutaHab VARCHAR(4),@pmDeclaracImp VARCHAR(50),@pmTipoIngreso VARCHAR(4),@pmIdOrgTra VARCHAR(8),@pmGPSoperador VARCHAR(250),@pmGPSUsuario VARCHAR(50),@pmGPSClave VARCHAR(50),@pmCantFiltros DECIMAL(14,4)
 AS 
 UPDATE Vehiculos SET NumVeh=@pmNumVeh,ClaseVeh=@pmClaseVeh,IdTipoVeh=@pmIdTipoVeh,IdMarca=@pmIdMarca,IdLinea=@pmIdLinea,IdColor=@pmIdColor,IdTipoMot=@pmIdTipoMot,IdCrceria=@pmIdCrceria,Modelo=@pmModelo,FecRep=@pmFecRep,Config=@pmConfig,VehArtic=@pmVehArtic,NumLlan=@pmNumLlan,NumLlans=@pmNumLlans,IdCat=@pmIdCat,CdCatv=@pmCdCatv,ClaseMat=@pmClaseMat,Cilind=@pmCilind,CapTanq=@pmCapTanq,IdCom=@pmIdCom,IdLub=@pmIdLub
,IdTlla=@pmIdTlla,IdMarlla=@pmIdMarlla,PesoVacio=@pmPesoVacio,PesoMax=@pmPesoMax,NumMotor=@pmNumMotor,SerieChasis=@pmSerieChasis,NumSerie=@pmNumSerie,CdRemque=@pmCdRemque,Longitud=@pmLongitud,CarrAlto=@pmCarrAlto,CarrAncho=@pmCarrAncho,CarrLargo=@pmCarrLargo,CarrCapac=@pmCarrCapac,UndCapc=@pmUndCapc,Comptmtos=@pmComptmtos,CapComp=@pmCapComp,PasjerosPie=@pmPasjerosPie,PasjerosSen=@pmPasjerosSen,NitEmpresa=@pmNitEmpresa
,IdPropietario=@pmIdPropietario,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,IdPpd=@pmIdPpd,Adquisc=@pmAdquisc,NitProv=@pmNitProv,FecCompra=@pmFecCompra,VrComcial=@pmVrComcial,VrAseg=@pmVrAseg,VrAvaludo=@pmVrAvaludo,VidaUtil=@pmVidaUtil,FecSalida=@pmFecSalida,NContrato=@pmNContrato,IdAdmon=@pmIdAdmon,IdNiv=@pmIdNiv,IdGrupo=@pmIdGrupo,CdGrupR=@pmCdGrupR,CdTarifa=@pmCdTarifa,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia
,FecRetiro=@pmFecRetiro,NumSoat=@pmNumSoat,FecSoat=@pmFecSoat,VigSoat=@pmVigSoat,NitEmpSoat=@pmNitEmpSoat,TarjProp=@pmTarjProp,FecTProp=@pmFecTProp,VigTProp=@pmVigTProp,CdLugTp=@pmCdLugTp,Ulttramite=@pmUlttramite,RespCivil=@pmRespCivil,FecRCivil=@pmFecRCivil,VigRCivil=@pmVigRCivil,RegNalCarga=@pmRegNalCarga,FecRegNal=@pmFecRegNal,VigRegNal=@pmVigRegNal,KmInicial=@pmKmInicial,KmActual=@pmKmActual,Km2Actual=@pmKm2Actual
,Regtradora=@pmRegtradora,CentInicial=@pmCentInicial,CentFinal=@pmCentFinal,VrLmtCred=@pmVrLmtCred,VrSaldoAct=@pmVrSaldoAct,Descripcion=@pmDescripcion,Observacion=@pmObservacion,CdCenSer=@pmCdCenSer,CdLocal=@pmCdLocal,Ubicacion=@pmUbicacion,PathFoto=@pmPathFoto,FecPriServ=@pmFecPriServ,FecUltServ=@pmFecUltServ,FecUltAcc=@pmFecUltAcc,TieneAcc=@pmTieneAcc,FecPagImp=@pmFecPagImp,IdEstado=@pmIdEstado,Inactivo=@pmInactivo
,TipoAfil=@pmTipoAfil,RevTecMec=@pmRevTecMec,FecTecMec=@pmFecTecMec,VigTecMec=@pmVigTecMec,CertGases=@pmCertGases,FecCertGas=@pmFecCertGas,VigCertGas=@pmVigCertGas,TarjOper=@pmTarjOper,FecTarjOper=@pmFecTarjOper,VigTarjOper=@pmVigTarjOper,FechaUpdate=@pmFechaUpdate
,ValorCupo=@pmValorCupo,ObligaTProd=@pmObligaTProd,GarantiaAcc=@pmGarantiaAcc,DocCompleta=@pmDocCompleta,CertMovilizacion=@pmCertMovilizacion,FecCertMovil=@pmFecCertMovil,VigCertMovil=@pmVigCertMovil,CdRutaHab=@pmCdRutaHab,DeclaracImp=@pmDeclaracImp,TipoIngreso=@pmTipoIngreso,IdOrgTra=@pmIdOrgTra,GPSoperador=@pmGPSoperador,GPSUsuario=@pmGPSUsuario,GPSClave=@pmGPSClave,CantFiltros=@pmCantFiltros
 WHERE IdVehiculo=@pmIdVehiculo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehiculos] @pmIdVehiculo VARCHAR(10)
 AS 
SELECT IdVehiculo,NumVeh,ClaseVeh,IdTipoVeh,IdMarca,IdLinea,IdColor,IdTipoMot,IdCrceria,Modelo,FecRep,Config,VehArtic,NumLlan,NumLlans,IdCat,CdCatv,ClaseMat,Cilind,CapTanq,IdCom,IdLub,IdTlla
,IdMarlla,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,Longitud,CarrAlto,CarrAncho,CarrLargo,CarrCapac,UndCapc,Comptmtos,CapComp,PasjerosPie,PasjerosSen,NitEmpresa
,IdPropietario,IdPoseedor,IdConductor,IdPpd,Adquisc,NitProv,FecCompra,VrComcial,VrAseg,VrAvaludo,VidaUtil,FecSalida,NContrato,IdAdmon,IdNiv,IdGrupo,CdGrupR,CdTarifa,FecIngreso,FecVigencia
,FecRetiro,NumSoat,FecSoat,VigSoat,NitEmpSoat,TarjProp,FecTProp,VigTProp,CdLugTp,Ulttramite,RespCivil,FecRCivil,VigRCivil,RegNalCarga,FecRegNal,VigRegNal,KmInicial,KmActual,Km2Actual
,Regtradora,CentInicial,CentFinal,VrLmtCred,VrSaldoAct,Descripcion,Observacion,CdCenSer,CdLocal,Ubicacion,PathFoto,FecPriServ,FecUltServ,FecUltAcc,TieneAcc,FecPagImp,IdEstado,Inactivo
,TipoAfil,RevTecMec,FecTecMec,VigTecMec,CertGases,FecCertGas,VigCertGas,TarjOper,FecTarjOper,VigTarjOper,FechaAdd,FechaUpdate,IdUsuario
,ValorCupo,ObligaTProd,GarantiaAcc,DocCompleta,CertMovilizacion,FecCertMovil,VigCertMovil,CdRutaHab,DeclaracImp,TipoIngreso,IdOrgTra,GPSoperador,GPSUsuario,GPSClave,CantFiltros
 FROM Vehiculos
 WHERE IdVehiculo=@pmIdVehiculo
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,Autorizac,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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomConAutoLta] @pmIdConcepto VARCHAR(4)=Null,@pmCedEmpleado VARCHAR(16)=Null,@pmCodNom VARCHAR(4)=Null,@pmCodDep VARCHAR(4)=Null
 ,@pmCodCcto VARCHAR(16)=Null,@pmCodSubCos VARCHAR(16)=Null,@pmInactivo BIT=Null,@pmFecInicioIni SMALLDATETIME=Null,@pmFecInicioFin SMALLDATETIME=Null
,@pmFrecAplica VARCHAR(10)=Null,@pmFecFinalIni SMALLDATETIME=Null,@pmFecFinalFin SMALLDATETIME=Null
 AS 
 SELECT IdReg,A.IdConcepto AS CodConcept,Concepto,ClaseCon,FecInicio,VrAplicar,Modalidad,OrigenBase,FrecAplica,CedEmpleado,Apellidos,Nombres,NContrato,CodNom
,TipoNomina,CodDep,Dependencia,CodCcto,CCosto,CodSubCos,SubCosto,CritSelect,FecFinal,Exceptuar,NumPerMes,A.Observacion AS Observ,A.Inactivo AS Inactvo
,A.NitTercero,RazonSocial,AplicaPrima,AplicaVac,PdoAplicaEsp,VrAplicaEsp,A.FecAdd AS Fecha_Add,A.FecUpdate AS FecUpd,A.IdUsuario AS IdUsuari,Usuario,E.Inactivo AS Emp_Inactivo
 FROM Trn_NomConAuto AS A INNER JOIN NomConceptos AS C ON A.IdConcepto=C.IdConcepto INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 LEFT JOIN Empleados AS E ON A.CedEmpleado=E.IdEmpleado  LEFT JOIN TiposNom AS TN ON A.CodNom=TN.IdNom
 LEFT JOIN Dependencias AS DP ON A.CodDep=DP.IdDep  LEFT JOIN CentroCosto AS CO ON A.CodCcto=CO.IdCCosto
 LEFT JOIN SubCentros AS SC ON A.CodSubCos=SC.IdSubCos LEFT JOIN Terceros AS T ON A.NitTercero=T.IdTercero
 WHERE A.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND CedEmpleado LIKE ISNULL(@pmCedEmpleado,'%') AND CodNom LIKE ISNULL(@pmCodNom,'%')
 AND CodDep LIKE ISNULL(@pmCodDep,'%') AND CodCcto LIKE ISNULL(@pmCodCcto,'%') AND CodSubCos LIKE ISNULL(@pmCodSubCos,'%')
 AND FrecAplica LIKE ISNULL(@pmFrecAplica,'%')
 AND (A.Inactivo=ISNULL(@pmInactivo,0) or A.Inactivo=ISNULL(@pmInactivo,1))
 AND (FecInicio>=ISNULL(@pmFecInicioIni,CAST('19100101' AS SMALLDATETIME)) AND FecInicio<=ISNULL(@pmFecInicioFin,CAST('20781230' AS SMALLDATETIME)))
-- AND (FecFinal>=ISNULL(@pmFecFinalIni,CAST('19100101' AS SMALLDATETIME)) AND FecFinal<=ISNULL(@pmFecFinalFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY IdReg
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomConAutoPen] @pmFecFin SMALLDATETIME,@pmCedEmpleado VARCHAR(16),@pmCodNom VARCHAR(4)
,@pmCodDep VARCHAR(4),@pmCodCcto VARCHAR(16),@pmCodSubCos VARCHAR(16)
 AS 
 SELECT IdReg,IdConcepto,FecInicio,VrAplicar,Modalidad,OrigenBase,FrecAplica,CedEmpleado,CodNom,CodDep,CodCcto
,CodSubCos,CritSelect,FecFinal,Exceptuar,Observacion,Inactivo,NumPerMes,NitTercero
,AplicaPrima,AplicaVac,PdoAplicaEsp,VrAplicaEsp,FecAdd,FecUpdate,IdUsuario
 FROM Trn_NomConAuto
 WHERE FecInicio<=@pmFecFin AND Inactivo=0 AND FrecAplica<>'PRIMA' AND FrecAplica<>'VACACIONES'
 AND ( CedEmpleado=@pmCedEmpleado OR CedEmpleado IS NULL OR LEN(CedEmpleado)=0)
 AND ( CodNom=@pmCodNom OR CodNom  IS NULL OR LEN(CodNom)=0)
 AND ( CodDep=@pmCodDep  OR CodDep  IS NULL OR LEN(CodDep)=0)
 AND ( CodCcto=@pmCodCcto  OR CodCcto  IS NULL OR LEN(CodCcto)=0)
 AND ( CodSubCos=@pmCodSubCos  OR CodSubCos  IS NULL OR LEN(CodSubCos)=0)
 ORDER BY IdConcepto,IdReg
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomConAuto] @pmIdReg INT
 AS 
 SELECT IdReg,IdConcepto,FecInicio,VrAplicar,Modalidad,OrigenBase,FrecAplica,CedEmpleado,CodNom,CodDep,CodCcto
,CodSubCos,CritSelect,FecFinal,Exceptuar,Observacion,Inactivo,NumPerMes,NitTercero,AplicaPrima,AplicaVac,PdoAplicaEsp,VrAplicaEsp,FecAdd,FecUpdate,IdUsuario
 FROM Trn_NomConAuto
 WHERE IdReg=@pmIdReg
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomConAuto] @pmIdReg INT,@pmIdConcepto VARCHAR(4),@pmFecInicio SMALLDATETIME,@pmVrAplicar MONEY,@pmModalidad CHAR(1)
,@pmOrigenBase CHAR(2),@pmFrecAplica VARCHAR(10),@pmCedEmpleado VARCHAR(16),@pmCodNom VARCHAR(4),@pmCodDep VARCHAR(4),@pmCodCcto VARCHAR(16)
,@pmCritSelect VARCHAR(4000),@pmFecFinal SMALLDATETIME,@pmExceptuar VARCHAR(8),@pmObservacion VARCHAR(50),@pmCodSubCos VARCHAR(16),@pmInactivo BIT
,@pmNumPerMes INT,@pmNitTercero VARCHAR(16),@pmAplicaPrima INT,@pmAplicaVac INT,@pmPdoAplicaEsp VARCHAR(8),@pmVrAplicaEsp MONEY,@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomConAuto (IdReg,IdConcepto,FecInicio,VrAplicar,Modalidad,OrigenBase,FrecAplica,CedEmpleado,CodNom,CodDep,CodCcto,CritSelect,FecFinal,Exceptuar,Observacion,CodSubCos
,Inactivo,NumPerMes,FecAdd,IdUsuario,NitTercero,AplicaPrima,AplicaVac,PdoAplicaEsp,VrAplicaEsp)
 VALUES (@pmIdReg,@pmIdConcepto,@pmFecInicio,@pmVrAplicar,@pmModalidad,@pmOrigenBase,@pmFrecAplica,@pmCedEmpleado,@pmCodNom,@pmCodDep,@pmCodCcto,@pmCritSelect,@pmFecFinal,@pmExceptuar
,@pmObservacion,@pmCodSubCos,@pmInactivo,@pmNumPerMes,@pmFecAdd,@pmIdUsuario,@pmNitTercero,@pmAplicaPrima,@pmAplicaVac,@pmPdoAplicaEsp,@pmVrAplicaEsp)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomConAuto] @pmIdReg INT,@pmIdConcepto VARCHAR(4),@pmFecInicio SMALLDATETIME,@pmVrAplicar MONEY,@pmModalidad CHAR(1),@pmOrigenBase CHAR(2),@pmFrecAplica VARCHAR(10)
,@pmCedEmpleado VARCHAR(16),@pmCodNom VARCHAR(4),@pmCodDep VARCHAR(4),@pmCodCcto VARCHAR(16),@pmCritSelect VARCHAR(4000),@pmFecFinal SMALLDATETIME,@pmExceptuar VARCHAR(8),@pmObservacion VARCHAR(50)
,@pmCodSubCos VARCHAR(16),@pmInactivo BIT,@pmNumPerMes INT,@pmNitTercero VARCHAR(16),@pmAplicaPrima INT,@pmAplicaVac INT,@pmPdoAplicaEsp VARCHAR(8),@pmVrAplicaEsp MONEY,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_NomConAuto SET IdConcepto=@pmIdConcepto,FecInicio=@pmFecInicio,VrAplicar=@pmVrAplicar,Modalidad=@pmModalidad,OrigenBase=@pmOrigenBase,FrecAplica=@pmFrecAplica,CedEmpleado=@pmCedEmpleado,CodNom=@pmCodNom
,CodDep=@pmCodDep,CodCcto=@pmCodCcto,CritSelect=@pmCritSelect,FecFinal=@pmFecFinal,Exceptuar=@pmExceptuar,Observacion=@pmObservacion,Inactivo=@pmInactivo,CodSubCos=@pmCodSubCos,NumPerMes=@pmNumPerMes
,NitTercero=@pmNitTercero,AplicaPrima=@pmAplicaPrima,AplicaVac=@pmAplicaVac,PdoAplicaEsp=@pmPdoAplicaEsp,VrAplicaEsp=@pmVrAplicaEsp,FecUpdate=@pmFecUpdate
 WHERE IdReg=@pmIdReg
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrySalidasNetDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
AS 
 SELECT S.TipDoc,S.Salida,S.IdCia AS CdCia,Compania,S.Fecha,S.IdConcepto AS CdConcepto,Concepto,S.IdTercero AS NitTercero,T.RazonSocial AS NomTercero
,S.VrSubTotal,S.VrImpuesto,S.VrCostos,S.VrNeto,S.BaseImp,S.TarifaIva,S.TipOrd,S.NumOrden,S.IdCiaOrd,S.IdCCosto AS CdCentCosto,CCosto,S.IdSubCos AS CdSubCenCost,SubCosto
,S.pVehiculo,S.CdConductor,CDT.RazonSocial AS NomConductor,S.NContrato AS NumContrato,S.IdCiaCont,S.NitCliente,CLI.RazonSocial AS NomCliente
,S.CdAgencia AS IdAgncia,Agencia,S.CdDep AS CdDepend,S.ModRequis,S.DirEntrega,S.IdLocEnt,LE.Localidad AS CiuEntrega,S.Modalidad,S.GuiaComb,S.CdCiaGuia
--Detalles
,K.Item,K.IdProducto AS CdProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,K.Entradas,K.Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMed,K.VrUnitario
,VrCostoEnt,VrCostoSal,K.TarifaIva AS DetTarifIva,VrIvaEnt,VrIvaSal,K.VrCostProm,K.Unidades,K.Descripcion,K.Referencia,Referencia2
,K.TipOrd as DetTipOrd,K.NumOrden AS DetNumOrden,K.IdCiaOrd AS DetIdCiaOrd,K.TipDocDev,K.NumDocDev,K.VrBruto,K.VrBase,K.Servcios,K.EsCombo,K.EsProdBase,K.NumLote,K.FechLote,K.CdTanque
,S.TipCom,S.Comprobante,S.IdCiaCom,S.Anulado,S.NumDev,S.FecDev,S.Observacion AS Observ,S.IdEstado AS CdEstado,Estado,S.TimeSys,S.FecUpdate AS Fec_Update,S.IdCiaCrea,S.IdUsuario AS CdUsuario,Usuario
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,Departamento
,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
 FROM Trn_Salidas AS S INNER JOIN Trn_Kardex AS K ON S.TipDoc=K.TipDoc AND S.Salida=K.Documento AND S.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON S.IdCia=CN.IdCia INNER JOIN Terceros AS T ON S.IdTercero=T.IdTercero 
 INNER JOIN Conceptos AS C ON S.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON S.IdEstado=ED.IdEstado 
 INNER JOIN adm_Usuarios AS U ON S.IdUsuario=U.IdUsuario INNER JOIN CentroCosto AS CC ON S.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 LEFT JOIN SubCentros AS SC ON S.IdSubCos=SC.IdSubCos
 LEFT JOIN Terceros AS CLI ON S.NitCliente=CLI.IdTercero
 LEFT JOIN Terceros AS CDT ON S.CdConductor=CDT.IdTercero
 LEFT JOIN Agencias AS A ON S.CdAgencia=A.IdAgencia
 LEFT JOIN Localidades AS LE ON S.IdLocEnt=LE.IdLocal
 WHERE S.TipDoc=@pmTipDoc  AND S.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND S.IdCia LIKE ISNULL(@pmIdCia,'%%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,Compania,D.Fecha,D.IdConcepto,Concepto,D.IdTercero,T.RazonSocial,D.VrSubTotal
,D.VrImpuesto,D.VrCostos,D.VrNeto,D.BaseImp,S.TarifaIva,D.TipOrd,D.NumOrden,D.IdCiaOrd
,D.IdCCosto,CCosto,D.IdSubCos,SubCosto,D.pVehiculo,S.CdConductor,CDT.RazonSocial,D.NContrato,D.IdCiaCont
,D.NitCliente,CLI.RazonSocial,D.CdAgencia,Agencia,CdDep,D.ModRequis,DirEntrega,IdLocEnt,LE.Localidad,D.Modalidad,GuiaComb,CdCiaGuia
,K.Item,K.IdProducto,DescripProd,K.IdBodega,Bodega,K.Entradas,K.Salidas,K.IdUnd,UM.Unidad,K.VrUnitario
,VrCostoEnt,VrCostoSal,K.TarifaIva,VrIvaEnt,VrIvaSal,K.VrCostProm,K.Unidades,K.Descripcion,K.Referencia,Referencia2
,K.TipOrd,K.NumOrden,K.IdCiaOrd,TipDocDev,K.NumDocDev,K.VrBruto,K.VrBase,K.Servcios,K.EsCombo,K.EsProdBase,K.NumLote,K.FechLote,K.CdTanque
,D.TipCom,D.Comprobante,D.IdCiaCom,0,D.Salida,D.FecDoc,D.Observacion,D.IdEstado,Estado,D.TimeSys,D.FecUpdate,D.IdCiaCrea,D.IdUsuario,Usuario
,T.TipoId,T.Dv,T.Codigo,T.NomCial,T.Direccion,T.IdLocal,L.Localidad,L.IdDep,Departamento
,T.Telefono,T.Fax,T.e_mail,T.IdSector,SectorEco,T.IdRegimen,Regimen,T.TipEnte
 FROM Trn_DevSal AS D INNER JOIN Trn_Salidas AS S ON D.TipDoc=S.TipDoc AND D.Salida=S.Salida AND D.IdCiaDoc=S.IdCia
 INNER JOIN Trn_Kardex AS K ON D.TipDev=K.TipDoc AND D.Devolucion=K.Documento AND D.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN Terceros AS T ON D.IdTercero=T.IdTercero
 INNER JOIN  Conceptos AS C ON D.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON D.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario INNER JOIN CentroCosto AS CC ON D.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 LEFT JOIN Terceros AS CLI ON D.NitCliente=CLI.IdTercero LEFT JOIN Terceros AS CDT ON S.CdConductor=CDT.IdTercero
 LEFT JOIN Agencias AS A ON D.CdAgencia=A.IdAgencia LEFT JOIN Localidades AS LE ON S.IdLocEnt=LE.IdLocal
 LEFT JOIN SubCentros AS SC ON D.IdSubCos=SC.IdSubCos
 WHERE D.TipDoc=@pmTipDoc  AND D.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
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),@pmAutorizac INT
 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,Autorizac=@pmAutorizac
 WHERE Numero=@pmNumero AND IdCia=@pmIdCia
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),@pmAutorizac INT
,@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,Autorizac)
 VALUES (@pmNumero,@pmIdCia,@pmIdVehiculo,@pmIndTurno,@pmFecha,@pmIdConductor,@pmnRemolque,@pmEstado
,@pmTipOrd,@pmNumOrden,@pmIdCiaOrd,@pmFecOrden,@pmIdUsuario,@pmFechaCrea,@pmNitCliente,@pmSedeClie,@pmAutorizac)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicion_Cr] @pmTipDoc VARCHAR(3),@pmRequisicionIni INT,@pmRequisicionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS CdTipo,TipoDoc,R.Requisicion AS NumRequis,R.IdCia AS CdCia,Compania,Fecha,FechaVence,R.IdConcepto AS CdConcepto,Concepto,IdRespons,T.RazonSocial AS NomResponsable
,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,R.IdDep AS CdDep,Dependencia,VrSubTotal,R.Cantidad AS CantTotal,R.NContrato AS NumContrato,IdCiaCont
,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Agencia,CodAgencia,Modalidad,DirEntrega,IdLocEnt,Localidad,Departamento,R.TipSal AS Tip_Sal,NumSalida,R.IdCiaSal AS CdCiaSal
,R.FechaSal AS Fec_Salida,NumAprob,FecAprob,CdUsuAprob,OrigenAdd,Anulado,NomContacto,TelsContacto,EmailContacto,Num_Vehic,Num_Trailer,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario
,Item,CdProducto,Descripcion,D.Cantidad AS CantArt,D.IdUnd AS CdUnid,Unidad,D.IdSubgrupo AS CdSubgrupo,Subgrupo,VrUnitario,TipOrd,NumOrden,IdCiaOrd,D.TipSal AS DetTipoSal,NSalida,D.IdCiaSal AS DetCiaSal
,D.FechaSal AS DetFecSalida,CdBodega,Bodega,D.Referencia
 FROM Trn_Requisicion AS R INNER JOIN Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero
 INNER JOIN  CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN  Conceptos AS C ON R.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia
 INNER JOIN Dependencias AS DP ON R.IdDep=DP.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON D.IdSubgrupo=S.IdSubgrupo
 INNER JOIN Localidades AS L ON R.IdLocEnt=L.IdLocal
 INNER JOIN Departamentos AS DT ON L.IdDep=DT.IdDep
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero
 LEFT JOIN Bodegas AS BG ON D.CdBodega =BG.IdBodega
 LEFT JOIN Agencias AS A ON R.CdAgencia=A.IdAgencia
 WHERE R.TipDoc=@pmTipDoc AND  R.Requisicion BETWEEN  @pmRequisicionIni AND @pmRequisicionFin 
 AND R.IdCia=@pmIdCia 
 ORDER BY  R.Requisicion,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionOrd] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRespons VARCHAR(16)=Null,@pmNitCliente VARCHAR(16)=Null
 AS 
 SELECT R.Requisicion AS NumRequision,R.IdCia AS CdCia,Compania,Fecha,FechaVence,R.IdConcepto AS CdConcepto,Concepto,IdRespons,T.RazonSocial AS Responsable
,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,SubCosto,R.IdDep AS CdDep,Dependencia,R.NContrato AS NumContrato,IdCiaCont,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Modalidad
,VrSubTotal,R.Cantidad AS CantTotal,DirEntrega,IdLocEnt,Localidad,NumAprob,FecAprob,R.Num_Vehic,R.Num_Trailer,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
--detalles
,Item,CdProducto,D.Descripcion AS DescProd,D.IdSubgrupo AS CdSubgrupo,Subgrupo,CdBodega,Bodega,D.Cantidad AS CantArtic,D.IdUnd AS CdUnidad,Unidad,VrUnitario,TipOrd,NumOrden,IdCiaOrd
,D.TipSal AS TipoSal,D.NSalida AS NumSalida,D.IdCiaSal AS CdCiaSal,D.FechaSal AS FecSalida,D.Referencia
--datos del cliente
,CL.TipoId AS TercTipo,CL.Dv AS TercDv,CL.Codigo AS TercCodigo,CL.NomCial AS TercNomCial,CL.SiglaRaz AS TercSigal,CL.Direccion AS TercDireccion
 FROM Trn_Requisicion AS R INNER JOIN Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero  INNER JOIN  CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN Dependencias AS DP ON R.IdDep=DP.IdDep
 INNER JOIN SubGrupos AS SG ON D.IdSubgrupo=SG.IdSubgrupo  INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd 
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero LEFT JOIN TercCliente AS CLI ON R.NitCliente=CLI.IdClie
 LEFT JOIN SubCentros AS SC ON R.IdSubCos=SC.IdSubCos LEFT JOIN Localidades AS LC ON R.IdLocEnt=LC.IdLocal
 LEFT JOIN Bodegas AS B ON D.CdBodega=B.IdBodega
 WHERE R.TipDoc=@pmTipDoc AND R.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND Anulado=0 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdRespons LIKE ISNULL(@pmIdRespons,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%')
 ORDER BY  R.IdCia,R.Requisicion
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionLtm] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmModalidad VARCHAR(10)=Null
AS 
 SELECT R.Requisicion,R.IdCia AS CdCia,Compania,R.Fecha,FechaVence,R.NContrato AS NumOrden,R.IdCiaCont AS IdCiaOdt,R.Modalidad
,R.IdRespons AS CdOperario,T.RazonSocial AS Operario,Num_Vehic,Num_Trailer,R.NitCliente AS NitCentro,CL.RazonSocial AS NomCentroServ
,DirEntrega,IdLocEnt,Localidad,R.IdDep AS CdDep,Dependencia,VrSubTotal,Cantidad,NumAprob,FecAprob,CdUsuAprob
,TipSal,NumSalida,IdCiaSal,FechaSal,R.IdConcepto AS CdConcepto,Concepto,R.IdCCosto AS CdCCosto,CCosto,R.IdSubCos AS CdSubCos
,NomContacto,TelsContacto,EmailContacto,R.Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
,OrigenAdd,R.TimeSys,R.FecUpdate,R.IdCiaCrea,R.IdUsuario AS CdUsuario,Usuario
 FROM Trn_Requisicion AS R INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario INNER JOIN Dependencias AS D ON R.IdDep=D.IdDep
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero LEFT JOIN Localidades AS L ON R.IdLocEnt=L.IdLocal
 WHERE R.TipDoc=@pmTipDoc AND R.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.Modalidad LIKE ISNULL(@pmModalidad,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryComFactura_Ent]  @pmTipFac VARCHAR(3),@pmDocumentoIni INT,@pmDocumentoFin INT,@pmIdCia CHAR(2)
,@pmIdProveedor VARCHAR(16)=Null,@pmFactura VARCHAR(15)=Null
 AS 
 SELECT TipFac,Factura,C.IdCia AS CdCia,Compania,Item,IdProveedor,RazonSocial,C.IdCuenta AS CdCuenta,NomCuenta,TipDoc,Documento,IdCiaDoc
,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob
 FROM Trn_ComFactura AS C INNER JOIN Terceros AS T ON C.IdProveedor=T.IdTercero
 INNER JOIN Puc AS P ON C.IdCuenta=P.IdCuenta INNER JOIN  Companias AS CI ON C.IdCia=CI.IdCia
 WHERE TipFac=@pmTipFac AND TipDoc=@pmTipFac AND Documento BETWEEN @pmDocumentoIni AND @pmDocumentoFin
 AND C.IdCia=@pmIdCia AND IdCiaDoc=@pmIdCia  AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%')
 AND  Factura LIKE ISNULL(@pmFactura,'%')
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionSal] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME
,@pmRequisicionIni INT=Null,@pmRequisicionFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null
,@pmIdCCosto VARCHAR(16)=Null,@pmNitCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null
,@pmIdDep VARCHAR(4)=Null,@pmFechaSalIni SMALLDATETIME=Null,@pmFechaSalFin SMALLDATETIME=Null
 AS 
 SELECT R.Requisicion AS NumRequision,R.IdCia AS CdCia,Compania,Fecha,FechaVence,D.IdSubgrupo AS CodSubgrupo,Subgrupo,SUM(D.Cantidad) AS CantReq
,D.IdUnd AS CdUnidad,Unidad
,TipoSalida,SD.NumSalida AS NroSalida,CdCiaSal,NomCiaSal,FecSalida,NumEntrada,CiaEntrada,SD.CdProducto AS CodProducto,NombreProd,CantEntradas,CantSalidas
,ValorUnit,CostoEntradas,CostoSalidas,TotalUnidades
,D.NumOrden AS NumOCompra,D.IdCiaOrd AS CdCiaOcompra,R.IdConcepto AS CdConcepto,Concepto,IdRespons,T.RazonSocial AS NomResponsable
,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,R.IdDep AS CdDep,Dependencia,NContrato,IdCiaCont,Num_Vehic,Num_Trailer
,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Modalidad,NumAprob,FecAprob,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado
 FROM Trn_Requisicion AS R  INNER JOIN  Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero  INNER JOIN  CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN  Conceptos AS C ON R.IdConcepto=C.IdConcepto INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia INNER JOIN  SubGrupos AS SG ON D.IdSubgrupo=SG.IdSubgrupo
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd 
 INNER JOIN Dependencias AS DP ON R.IdDep=DP.IdDep  LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero
 LEFT JOIN (SELECT TipOrd,NumOrden,IdCiaOrd,K.TipDoc AS TipoSalida,K.Documento AS NumSalida,K.IdCia AS CdCiaSal,KC.Compania AS NomCiaSal
,K.Fecha AS FecSalida,K.Remision AS NumEntrada,K.IdCiaRem AS CiaEntrada,K.IdProducto AS CdProducto,PM.DescripProd AS NombreProd
,PM.IdSubgrupo AS CdSubgrupo,SG.Subgrupo AS NomSubgrupo,SUM(Entradas) AS CantEntradas,SUM(Salidas) AS CantSalidas,MAX(VrUnitario) AS ValorUnit,SUM(VrCostoEnt) AS CostoEntradas,SUM(VrCostoSal) AS CostoSalidas
,SUM(K.Unidades) AS TotalUnidades
 FROM Trn_Kardex AS K INNER JOIN Companias AS KC ON K.IdCia=KC.IdCia  INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto
 INNER JOIN SubGrupos AS SG ON PM.IdSubgrupo=SG.IdSubgrupo  WHERE TipDoc IN ('SAI','DSI','SAL','DVS') AND TipOrd=@pmTipDoc AND NumOrden>0 
 AND (Fecha>=ISNULL(@pmFechaSalIni,CAST('19100101' AS SMALLDATETIME)) AND Fecha<=ISNULL(@pmFechaSalFin,CAST('20781230' AS SMALLDATETIME)))
 GROUP BY TipOrd,NumOrden,IdCiaOrd,K.TipDoc,K.Documento,K.IdCia,KC.Compania,K.Fecha,K.Remision ,K.IdCiaRem,K.IdProducto,PM.DescripProd
,PM.IdSubgrupo,SG.Subgrupo) AS SD ON R.TipDoc=SD.TipOrd AND R.Requisicion=SD.NumOrden AND R.IdCia=SD.IdCiaOrd AND D.IdSubgrupo=SD.CdSubgrupo
 WHERE R.TipDoc=@pmTipDoc AND R.Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND Anulado=0 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND IdRespons LIKE ISNULL(@pmIdRespons,'%')
 AND R.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND R.IdDep LIKE ISNULL(@pmIdDep,'%')
 AND  R.Requisicion BETWEEN ISNULL(@pmRequisicionIni,0) AND ISNULL(@pmRequisicionFin,2147483647)
 GROUP BY  R.Requisicion,R.IdCia,Compania,Fecha,FechaVence,D.IdSubgrupo,Subgrupo,D.IdUnd,Unidad
,TipoSalida,SD.NumSalida,CdCiaSal,NomCiaSal,FecSalida,NumEntrada,CiaEntrada,SD.CdProducto,NombreProd,CantEntradas,CantSalidas
,ValorUnit,CostoEntradas,CostoSalidas,TotalUnidades
,D.NumOrden,D.IdCiaOrd,R.IdConcepto,Concepto,IdRespons,T.RazonSocial,R.IdCCosto,CCosto,R.IdSubCos
,R.IdDep,Dependencia,NContrato,IdCiaCont,Num_Vehic,Num_Trailer,NitCliente,CL.RazonSocial,CdAgencia,Modalidad,NumAprob,FecAprob
,R.Observacion,R.IdEstado,Estado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionPen] @pmRequisicionIni INT=Null,@pmRequisicionFin INT=Null,@pmIdCia CHAR(2)=Null
,@pmIdRespons VARCHAR(16)=Null,@pmNitCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT  R.Requisicion AS NumRequis,R.IdCia AS CdCia,Fecha,FechaVence,IdRespons,T.RazonSocial AS NomResponsable
,Item,CdProducto,Descripcion,D.Cantidad AS CantArt,D.IdUnd AS CdUnid,Unidad,VrUnitario,D.Cantidad*VrUnitario AS ValorTotal
,R.IdConcepto AS CdConcepto,Concepto,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,R.IdDep AS CdDep,Dependencia
,NContrato,IdCiaCont,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,R.Num_Vehic,R.Num_Trailer,Modalidad,DirEntrega,NumAprob,FecAprob,CdUsuAprob
,R.Observacion AS Observ,TipOrd,NumOrden,IdCiaOrd,NomContacto,TelsContacto,EmailContacto,D.Referencia
 FROM Trn_Requisicion AS R INNER JOIN Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero
 INNER JOIN  CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN  Conceptos AS C ON R.IdConcepto=C.IdConcepto
 INNER JOIN Dependencias AS DP ON R.IdDep=DP.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero
 WHERE R.TipDoc='REQ' AND R.Anulado=0 AND R.IdEstado='0001' AND NumAprob>0
 AND  R.Requisicion BETWEEN ISNULL(@pmRequisicionIni,0) AND ISNULL(@pmRequisicionFin,2147483647)
 AND R.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND IdRespons LIKE ISNULL(@pmIdRespons,'%')
 AND NitCliente LIKE ISNULL(@pmNitCliente,'%') AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY R.IdCia,R.Requisicion,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposDocAut]
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('FCR','DFO','ODB','OD2','PR1','NTS','MUC','OSA','OCT') --todas las fac.credito con este codigo
 ORDER BY IdDoc
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsComFactura_Uni] @pmIdProveedor VARCHAR(16),@pmNewProveedor VARCHAR(16)
 AS 
 INSERT INTO Trn_ComFactura (TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob)
 SELECT TipFac,Factura,IdCia,Item,@pmNewProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob
 FROM Trn_ComFactura WHERE IdProveedor=@pmIdProveedor
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE  [dbo].[paQryComFacturaDoc]  @pmTipFac VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
,@pmIdProveedor VARCHAR(16),@pmFactura VARCHAR(15),@pmItemCom INT
 AS 
 SELECT TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante
,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob
 FROM Trn_ComFactura
 WHERE TipFac=@pmTipFac AND TipDoc=@pmTipFac AND Documento=@pmDocumento AND IdCia=@pmIdCia AND IdCiaDoc=@pmIdCia
 AND IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND  Factura LIKE ISNULL(@pmFactura,'%')
 AND (ItemCom>=ISNULL(@pmItemCom,-10)  AND ItemCom<=ISNULL(@pmItemCom,0)) 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryComFacturaSal] @pmFechaAct SMALLDATETIME,@pmSaldo DECIMAL(14,2),@pmIdProveedor VARCHAR(16)=Null, @pmIdCia CHAR(2)=Null
,@pmIdCuenta VARCHAR(16)=Null,@pmTipDoc VARCHAR(3)=Null,@pmTipFac VARCHAR(3)=Null
AS
SELECT  TipFac, Factura, IdCia, Item,VrFactura-VrAbonado AS ValorSaldo,VrFactura,VrAbonado, FecEmision, FecVence,DATEDIFF(day,FecVence,@pmFechaAct) AS DiasMora,TipDoc, Documento, IdCiaDoc 
,IdProveedor,RazonSocial, F.IdCuenta AS IdCuent,NomCuenta,TipCom, Comprobante, ItemCom, Referencia, Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob
 FROM Trn_ComFactura AS F INNER JOIN Terceros AS T ON F.IdProveedor=T.IdTercero  INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta
 WHERE (VrFactura-VrAbonado)>@pmSaldo AND F.IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND  IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
 AND TipDoc LIKE ISNULL(@pmTipDoc,'%' ) AND TipFac LIKE ISNULL(@pmTipFac,'%')
ORDER BY RazonSocial,F.IdCuenta,FecVence,TipFac,IdCia,Factura
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsComFactura] @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmItem INT,@pmIdProveedor VARCHAR(16),@pmIdCuenta VARCHAR(16),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmFecEmision SMALLDATETIME
,@pmFecVence SMALLDATETIME,@pmVrFactura MONEY,@pmVrAbonado MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmItemCom INT,@pmReferencia VARCHAR(50),@pmDetalle VARCHAR(100),@pmpVehiculo VARCHAR(10),@pmVehPropio BIT
,@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmEstadoApr INT,@pmMontoAprob MONEY
 AS 
 INSERT INTO Trn_ComFactura (TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob)
 VALUES (@pmTipFac,@pmFactura,@pmIdCia,@pmItem,@pmIdProveedor,@pmIdCuenta,@pmTipDoc,@pmDocumento,@pmIdCiaDoc,@pmFecEmision,@pmFecVence,@pmVrFactura,@pmVrAbonado,@pmTipCom,@pmComprobante,@pmItemCom
,@pmReferencia,@pmDetalle,@pmpVehiculo,@pmVehPropio,@pmTipRef,@pmDocRef,@pmIdCiaRef,@pmEstadoApr,@pmMontoAprob)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpComFactura] @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmItem INT,@pmIdProveedor VARCHAR(16),@pmIdCuenta VARCHAR(16),@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmFecEmision SMALLDATETIME,@pmFecVence SMALLDATETIME
,@pmVrFactura MONEY,@pmVrAbonado MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmItemCom INT,@pmReferencia VARCHAR(50),@pmDetalle VARCHAR(100),@pmpVehiculo VARCHAR(10),@pmVehPropio BIT
,@pmTipRef VARCHAR(3),@pmDocRef INT,@pmIdCiaRef CHAR(2),@pmEstadoApr INT,@pmMontoAprob MONEY
 AS 
 UPDATE Trn_ComFactura SET IdCuenta=@pmIdCuenta,TipDoc=@pmTipDoc,Documento=@pmDocumento,IdCiaDoc=@pmIdCiaDoc,FecEmision=@pmFecEmision,FecVence=@pmFecVence,VrFactura=@pmVrFactura,VrAbonado=@pmVrAbonado,TipCom=@pmTipCom,Comprobante=@pmComprobante,ItemCom=@pmItemCom
,Referencia=@pmReferencia,Detalle=@pmDetalle,pVehiculo=@pmpVehiculo,VehPropio=@pmVehPropio,TipRef=@pmTipRef,DocRef=@pmDocRef,IdCiaRef=@pmIdCiaRef,EstadoApr=@pmEstadoApr,MontoAprob=@pmMontoAprob
 WHERE TipFac=@pmTipFac AND Factura=@pmFactura AND IdCia=@pmIdCia AND Item=@pmItem AND IdProveedor=@pmIdProveedor
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryComFactura] @pmTipFac VARCHAR(3),@pmFactura VARCHAR(15),@pmIdCia CHAR(2),@pmIdProveedor VARCHAR(16),@pmItem INT
 AS 
 SELECT TipFac,Factura,IdCia,Item,IdProveedor,IdCuenta,TipDoc,Documento,IdCiaDoc,FecEmision,FecVence,VrFactura,VrAbonado,TipCom,Comprobante,ItemCom
,Referencia,Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob
 FROM Trn_ComFactura
 WHERE TipFac=@pmTipFac AND Factura=@pmFactura AND IdCia=@pmIdCia AND IdProveedor=@pmIdProveedor
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647)) 
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryComFacturaNit] @pmIdProveedor VARCHAR(16),@pmIdCia CHAR(2)=Null,@pmIdCuenta VARCHAR(16)=Null
,@pmTipDoc VARCHAR(3)=Null,@pmTipFac VARCHAR(3)=Null,@pmpVehiculo VARCHAR(10)=Null
 AS 
--**** NO cambiar posición de los primeros 4 campos
 SELECT TipFac,Factura,IdCia,Item,VrFactura-VrAbonado AS ValorSaldo,VrFactura,VrAbonado,TipDoc,Documento,IdCiaDoc,TipRef,DocRef,IdCiaRef,FecEmision,FecVence
,F.IdCuenta AS IdCuent,NomCuenta,TipCom,Comprobante,ItemCom,Referencia,Detalle,IdProveedor,RazonSocial,pVehiculo,VehPropio,EstadoApr,MontoAprob
 FROM Trn_ComFactura AS F INNER JOIN Terceros AS T ON F.IdProveedor=T.IdTercero  INNER JOIN Puc AS P ON F.IdCuenta=P.IdCuenta
 WHERE VrFactura>VrAbonado AND IdProveedor=@pmIdProveedor AND  IdCia LIKE ISNULL(@pmIdCia,'%%') AND F.IdCuenta LIKE ISNULL(@pmIdCuenta,'%')
 AND TipFac LIKE ISNULL(@pmTipFac,'%') 
 --AND TipDoc LIKE ISNULL(@pmTipDoc,'%' ) AND pVehiculo LIKE ISNULL(@pmpVehiculo,'%')
ORDER BY FecVence,TipFac,IdCia,Factura
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryComFacturaDso] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmSaldo DECIMAL(14,2),@pmIdCia CHAR(2)=Null,@pmTipFac VARCHAR(3)=Null
,@pmTipDoc VARCHAR(3)=Null,@pmIdProveedor VARCHAR(16)=Null,@pmIdCuenta VARCHAR(16)=Null,@pmpVehiculo VARCHAR(10)=Null,@pmTipCom VARCHAR(3)=Null,@pmComprobante INT=Null
AS
 SELECT  TipFac, Factura, IdCia, Item,IdCuenta,IdProveedor,T.RazonSocial AS Proveedor,TipDoc, Documento, IdCiaDoc, FecEmision, FecVence, VrFactura,VrAbonado,VrFactura-VrAbonado AS ValorSaldo
,TipCom, Comprobante, ItemCom, Referencia, Detalle,pVehiculo,VehPropio,TipRef,DocRef,IdCiaRef,EstadoApr,MontoAprob
 FROM   Trn_ComFactura   AS  V INNER JOIN Terceros AS T ON V.IdProveedor=T.IdTercero
 WHERE  FecVence BETWEEN @pmFechaIni AND @pmFechaFin AND (VrFactura-VrAbonado)>=@pmSaldo AND IdCia like ISNULL(@pmIdCia,'%%') AND TipFac like ISNULL(@pmTipFac,'%')
 AND TipDoc LIKE ISNULL(@pmTipDoc,'%') AND  IdProveedor LIKE ISNULL(@pmIdProveedor,'%') AND IdCuenta LIKE ISNULL(@pmIdCuenta,'%') AND pVehiculo LIKE ISNULL(@pmpVehiculo,'%')
 AND TipCom LIKE ISNULL(@pmTipCom,'%')
 AND (Comprobante>=ISNULL(@pmComprobante,-1)  AND Comprobante<=ISNULL(@pmComprobante,2147483647)) 
ORDER BY TipFac,Factura,Item
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,CodDiaEnt,CdGruClie
 FROM Agencias
 WHERE IdClie=@pmIdClie AND CodAgencia LIKE ISNULL(@pmCodAgencia,'%')
 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),@pmCodDiaEnt VARCHAR(4),@pmCdGruClie 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,CodDiaEnt,CdGruClie)
 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,@pmCodDiaEnt,@pmCdGruClie)
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,CodDiaEnt,CdGruClie
 FROM Agencias
 WHERE IdAgencia=@pmIdAgencia
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),@pmCodDiaEnt VARCHAR(4),@pmCdGruClie 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,CodDiaEnt=@pmCodDiaEnt,CdGruClie=@pmCdGruClie
 WHERE IdAgencia=@pmIdAgencia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomNovCon] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16) 
,@pmIdConcepto VARCHAR(4),@pmOrigCargue VARCHAR(10)=Null
 AS 
 SELECT Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,Tarifa,VrOrigen,IdPeriodo,IdEmpleado
,TimeSys,FecUpdate,IdUsuario,OrigCargue,Num_Contrato
 FROM Trn_NomNov
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado
 AND IdConcepto=@pmIdConcepto
 AND OrigCargue LIKE ISNULL(@pmOrigCargue,'%')
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad DECIMAL(10,4),@pmVrUnitario MONEY
,@pmVrTotal MONEY,@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmOrigCargue VARCHAR(10),@pmNumDocRef VARCHAR(20),@pmNum_Contrato INT,@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NomNov (IdPeriodo,IdEmpleado,Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,VrOrigen,Tarifa,OrigCargue,TimeSys,IdUsuario,NumDocRef,Num_Contrato)
 VALUES (@pmIdPeriodo,@pmIdEmpleado,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantidad,@pmVrUnitario,@pmVrTotal,@pmVrOrigen,@pmTarifa,@pmOrigCargue, @pmTimeSys,@pmIdUsuario,@pmNumDocRef,@pmNum_Contrato)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT
 AS 
 SELECT Item,IdConcepto,Detalle,Cantidad,VrUnitario,VrTotal,Tarifa,VrOrigen,IdPeriodo,IdEmpleado
,TimeSys,FecUpdate,IdUsuario,OrigCargue,NumDocRef,Num_Contrato
 FROM Trn_NomNov
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomNov] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantidad DECIMAL(10,4),@pmVrUnitario MONEY
,@pmVrTotal MONEY,@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmOrigCargue VARCHAR(10),@pmNumDocRef VARCHAR(20),@pmNum_Contrato INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_NomNov SET IdConcepto=@pmIdConcepto,Detalle=@pmDetalle,Cantidad=@pmCantidad,VrUnitario=@pmVrUnitario,VrTotal=@pmVrTotal,VrOrigen=@pmVrOrigen
,Tarifa=@pmTarifa,OrigCargue=@pmOrigCargue,NumDocRef=@pmNumDocRef,Num_Contrato=@pmNum_Contrato,FecUpdate=@pmFecUpdate
 WHERE IdPeriodo=@pmIdPeriodo AND IdEmpleado=@pmIdEmpleado AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpNiifDetallePla] @pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCia CHAR(2)
,@pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCiaDoc CHAR(2),@pmNumCheque VARCHAR(20),@pmItemFac INT,@pmCodCargo VARCHAR(4)
,@pmIdCuenta VARCHAR(16),@pmVrDebito MONEY,@pmIdTercero VARCHAR(16),@pmIdVehiculo VARCHAR(10),@pmCodAgncia VARCHAR(16)
,@pmReferncia VARCHAR(50),@pmFecUpdate SMALLDATETIME,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmCodCuentaCG VARCHAR(16)
 AS 
 UPDATE Trn_NiifDetalle SET IdCuenta=@pmIdCuenta,VrDebito=@pmVrDebito,IdTercero=@pmIdTercero,CodAgncia=@pmCodAgncia,IdVehiculo=@pmIdVehiculo
,Referncia=@pmReferncia,FecUpdate=@pmFecUpdate,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,CodCuentaCG=@pmCodCuentaCG
 WHERE TipCom=@pmTipCom AND Comprobante=@pmComprobante AND IdCia=@pmIdCia
 AND TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCiaDoc=@pmIdCiaDoc 
 AND NumCheque=@pmNumCheque AND ItemFac=@pmItemFac AND CodCargo=@pmCodCargo
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexCc] @pmtmNumero VARCHAR(5),@pmtmIdProducto VARCHAR(16)
 AS 
 SELECT tmNumero,tmItem,tmIdProducto,tmCdCCosto,tmCdSubCos,tmCdLocal,tmCodTarIca,tmCodTarRet
 FROM tm_Kdex
 WHERE tmNumero=@pmtmNumero AND tmIdProducto LIKE ISNULL(@pmtmIdProducto,'%')
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionLta] @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmRequisicionIni INT=Null
,@pmRequisicionFin INT=Null,@pmIdCia CHAR(2)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdRespons VARCHAR(16)=Null
,@pmIdCCosto VARCHAR(16)=Null,@pmNitCliente VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null,@pmAnulado BIT=Null
,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT Requisicion,IdCia,Fecha,FechaVence,R.IdConcepto AS CdConcepto,Concepto,IdRespons,T.RazonSocial AS NomResponsable
,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,R.IdDep AS CdDep,Dependencia,VrSubTotal,Cantidad,NContrato,IdCiaCont
,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Modalidad,DirEntrega,IdLocEnt,TipSal,NumSalida,IdCiaSal,FechaSal,NumAprob,FecAprob,CdUsuAprob
,NomContacto,TelsContacto,EmailContacto,Num_Vehic,Num_Trailer,OrigenAdd,Anulado,FecDev,R.Observacion AS Observ,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario,TipDoc
 FROM Trn_Requisicion AS R INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero
 INNER JOIN  CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN  Conceptos AS C ON R.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN Dependencias AS D ON R.IdDep=D.IdDep
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero
 WHERE TipDoc='REQ' AND Fecha BETWEEN  @pmFechaIni AND @pmFechaFin AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND R.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND IdRespons LIKE ISNULL(@pmIdRespons,'%')
 AND R.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND NitCliente LIKE ISNULL(@pmNitCliente,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND R.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 AND  Requisicion BETWEEN ISNULL(@pmRequisicionIni,0) AND ISNULL(@pmRequisicionFin,2147483647)
 ORDER BY IdCia,Requisicion
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraRemCntdor] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdTipoCon VARCHAR(4),@pmNumContdor VARCHAR(50),@pmPesoNeto DECIMAL(14,4),@pmIdTipoPre VARCHAR(4)
,@pmNumPrecinto VARCHAR(30),@pmColorPrecinto VARCHAR(20),@pmSellos VARCHAR(150),@pmPeligroso BIT,@pmPerecedero BIT,@pmToxico BIT,@pmInflamable BIT,@pmDifmanejo BIT,@pmFragil BIT,@pmDevCont BIT
,@pmCdLocCont VARCHAR(8),@pmPatioCont VARCHAR(50),@pmObservacion VARCHAR(150),@pmNumSalidaSello INT,@pmCdCiaSalSello CHAR(2),@pmItemSalSello INT,@pmCdProdSalida VARCHAR(16),@pmNumSerialSello VARCHAR(30)
,@pmNumComodato VARCHAR(50),@pmFecVenceCom SMALLDATETIME
 AS 
 INSERT INTO Trn_TraRemCntdor (TipDoc,NumOrden,IdCia,Item,IdTipoCon,NumContdor,PesoNeto,IdTipoPre,NumPrecinto,ColorPrecinto,Sellos,Peligroso,Perecedero,Toxico,Inflamable,Difmanejo,Fragil,DevCont,CdLocCont,PatioCont
,Observacion,NumSalidaSello,CdCiaSalSello,ItemSalSello,CdProdSalida,NumSerialSello,NumComodato,FecVenceCom)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmItem,@pmIdTipoCon,@pmNumContdor,@pmPesoNeto,@pmIdTipoPre,@pmNumPrecinto,@pmColorPrecinto,@pmSellos,@pmPeligroso,@pmPerecedero,@pmToxico,@pmInflamable
,@pmDifmanejo,@pmFragil,@pmDevCont,@pmCdLocCont,@pmPatioCont,@pmObservacion,@pmNumSalidaSello,@pmCdCiaSalSello,@pmItemSalSello,@pmCdProdSalida,@pmNumSerialSello,@pmNumComodato,@pmFecVenceCom)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraRemCntdor] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Item,IdTipoCon,NumContdor,PesoNeto,IdTipoPre,NumPrecinto,ColorPrecinto,Sellos,Peligroso,Perecedero,Toxico,Inflamable,Difmanejo,Fragil
,DevCont,CdLocCont,PatioCont,Observacion,NumSalidaSello,CdCiaSalSello,ItemSalSello,CdProdSalida,NumSerialSello,NumComodato,FecVenceCom
 FROM Trn_TraRemCntdor
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
 ORDER BY  Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEmp_Contrato] @pmNContrato INT
 AS 
 SELECT NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdSubCos,IdCia,IdNom
,IdClase,TipoTrabj,IdCot,IdGrupo,VrUpc,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS,DenyARP
,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,DenyDcto,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,SubTipoCot,DsoSabado,FspFinmes,FecFinPrueba
,TipoPrendDot,TipoMedSent,BenefPacto,FecAdd,FecUpdate,IdUsuario
 FROM Emp_Contrato
 WHERE NContrato=@pmNContrato
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsEmp_Contrato] @pmNContrato INT,@pmIdEmpleado VARCHAR(16),@pmIdTipCon VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmIndefinido BIT,@pmIdCargo VARCHAR(4),@pmIdJornada VARCHAR(4)
,@pmIdDep VARCHAR(4),@pmIdArea VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdCia CHAR(2),@pmIdNom VARCHAR(4),@pmTipoLiquida CHAR(1),@pmModPromdio CHAR(1),@pmVrSalario MONEY,@pmVrAuxTrans MONEY,@pmSalMinimo BIT,@pmSalIntegral BIT
,@pmFPnomina VARCHAR(10),@pmModoRet INT,@pmFecAfiSeg SMALLDATETIME,@pmATFinMes BIT,@pmSegFinMes BIT,@pmDenyPEN BIT,@pmDenyEPS BIT,@pmDenyARP BIT,@pmDenyCaj BIT,@pmDenyCes BIT,@pmDenyPri BIT,@pmDenyVac BIT,@pmDenyHex BIT,@pmBasePrest CHAR(2)
,@pmBaseSeg CHAR(2),@pmDchDotacion BIT,@pmDchCafeteria BIT,@pmReclmto VARCHAR(50),@pmNotas VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmIdClase VARCHAR(4),@pmTipoTrabj INT,@pmIdCot VARCHAR(4),@pmVrUpc MONEY,@pmIdGrupo VARCHAR(4)
,@pmDenyDcto BIT,@pmSubTipoCot VARCHAR(3),@pmDsoSabado BIT,@pmFspFinmes BIT,@pmFecFinPrueba SMALLDATETIME,@pmTipoPrendDot VARCHAR(10),@pmTipoMedSent VARCHAR(4),@pmBenefPacto VARCHAR(100),@pmFecAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Emp_Contrato (NContrato,IdEmpleado,IdTipCon,FecIngreso,FecVigencia,FecRetiro,Indefinido,IdCargo,IdJornada,IdDep,IdArea,IdInstala,IdCCosto,IdCia,IdNom,TipoLiquida,ModPromdio,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ModoRet,FecAfiSeg,ATFinMes,SegFinMes,DenyPEN,DenyEPS
,DenyARP,DenyCaj,DenyCes,DenyPri,DenyVac,DenyHex,BasePrest,BaseSeg,DchDotacion,DchCafeteria,Reclmto,Notas,IdEstado,Inactivo,IdSubCos,IdClase,TipoTrabj,FecAdd,IdUsuario,IdCot,VrUpc,IdGrupo,DenyDcto,SubTipoCot,DsoSabado,FspFinmes,FecFinPrueba,TipoPrendDot,TipoMedSent,BenefPacto)
 VALUES (@pmNContrato,@pmIdEmpleado,@pmIdTipCon,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmIndefinido,@pmIdCargo,@pmIdJornada,@pmIdDep,@pmIdArea,@pmIdInstala,@pmIdCCosto,@pmIdCia,@pmIdNom,@pmTipoLiquida,@pmModPromdio,@pmVrSalario,@pmVrAuxTrans,@pmSalMinimo
,@pmSalIntegral,@pmFPnomina,@pmModoRet,@pmFecAfiSeg,@pmATFinMes,@pmSegFinMes,@pmDenyPEN,@pmDenyEPS,@pmDenyARP,@pmDenyCaj,@pmDenyCes,@pmDenyPri,@pmDenyVac,@pmDenyHex,@pmBasePrest,@pmBaseSeg,@pmDchDotacion,@pmDchCafeteria,@pmReclmto,@pmNotas
,@pmIdEstado,@pmInactivo,@pmIdSubCos,@pmIdClase,@pmTipoTrabj,@pmFecAdd,@pmIdUsuario,@pmIdCot,@pmVrUpc,@pmIdGrupo,@pmDenyDcto,@pmSubTipoCot,@pmDsoSabado,@pmFspFinmes,@pmFecFinPrueba,@pmTipoPrendDot,@pmTipoMedSent,@pmBenefPacto)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpEmp_Contrato] @pmNContrato INT,@pmIdEmpleado VARCHAR(16),@pmIdTipCon VARCHAR(4),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmIndefinido BIT,@pmIdCargo VARCHAR(4),@pmIdJornada VARCHAR(4),@pmIdDep VARCHAR(4),@pmIdArea VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdCia CHAR(2),@pmIdNom VARCHAR(4),@pmTipoLiquida CHAR(1),@pmModPromdio CHAR(1),@pmVrSalario MONEY,@pmVrAuxTrans MONEY,@pmSalMinimo BIT,@pmSalIntegral BIT,@pmFPnomina VARCHAR(10),@pmModoRet INT,@pmFecAfiSeg SMALLDATETIME,@pmATFinMes BIT,@pmSegFinMes BIT,@pmDenyPEN BIT,@pmDenyEPS BIT,@pmDenyARP BIT,@pmDenyCaj BIT,@pmDenyCes BIT,@pmDenyPri BIT
,@pmDenyVac BIT,@pmDenyHex BIT,@pmBasePrest CHAR(2),@pmBaseSeg CHAR(2),@pmDchDotacion BIT,@pmDchCafeteria BIT,@pmReclmto VARCHAR(50),@pmNotas VARCHAR(250),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmIdClase VARCHAR(4),@pmTipoTrabj INT,@pmIdCot VARCHAR(4),@pmVrUpc MONEY,@pmIdGrupo VARCHAR(4),@pmDenyDcto BIT,@pmSubTipoCot VARCHAR(3),@pmDsoSabado BIT
,@pmFspFinmes BIT,@pmFecFinPrueba SMALLDATETIME,@pmTipoPrendDot VARCHAR(10),@pmTipoMedSent VARCHAR(4),@pmBenefPacto VARCHAR(100),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Emp_Contrato SET IdEmpleado=@pmIdEmpleado,IdTipCon=@pmIdTipCon,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,Indefinido=@pmIndefinido,IdCargo=@pmIdCargo,IdJornada=@pmIdJornada,IdDep=@pmIdDep,IdArea=@pmIdArea,IdInstala=@pmIdInstala,IdCCosto=@pmIdCCosto,IdCia=@pmIdCia,IdNom=@pmIdNom,TipoLiquida=@pmTipoLiquida,ModPromdio=@pmModPromdio
,VrSalario=@pmVrSalario,VrAuxTrans=@pmVrAuxTrans,SalMinimo=@pmSalMinimo,SalIntegral=@pmSalIntegral,FPnomina=@pmFPnomina,ModoRet=@pmModoRet,FecAfiSeg=@pmFecAfiSeg,ATFinMes=@pmATFinMes,SegFinMes=@pmSegFinMes,DenyPEN=@pmDenyPEN,DenyEPS=@pmDenyEPS,DenyARP=@pmDenyARP,DenyCaj=@pmDenyCaj,DenyCes=@pmDenyCes,DenyPri=@pmDenyPri,DenyVac=@pmDenyVac,DenyHex=@pmDenyHex
,BasePrest=@pmBasePrest,BaseSeg=@pmBaseSeg,DchDotacion=@pmDchDotacion,DchCafeteria=@pmDchCafeteria,Reclmto=@pmReclmto,Notas=@pmNotas,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FecUpdate=@pmFecUpdate,IdSubCos=@pmIdSubCos,IdClase=@pmIdClase,TipoTrabj=@pmTipoTrabj ,IdCot=@pmIdCot,VrUpc=@pmVrUpc,IdGrupo=@pmIdGrupo,DenyDcto=@pmDenyDcto,SubTipoCot =@pmSubTipoCot
,DsoSabado=@pmDsoSabado,FspFinmes=@pmFspFinmes,FecFinPrueba=@pmFecFinPrueba,TipoPrendDot=@pmTipoPrendDot,TipoMedSent=@pmTipoMedSent,BenefPacto=@pmBenefPacto
 WHERE NContrato=@pmNContrato
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryKardexProCc] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
 AS 
 SELECT IdSubgrupo,K.IdProducto AS tmIdProducto,DescripProd,TipoRef,IdProv,CdCCosto,CdSubCos,CodTarIca,CodTarRet
,SUM(VrCostoEnt) AS SCOSENT,SUM(VrCostoSal) AS SCOSSAL
,SUM(VrPrecio*Entradas) AS SVALENT,SUM(VrPrecio*Salidas) AS SVALSAL
,SUM(VrBruto*Entradas) AS SBRUENT,SUM(VrBruto*Salidas) AS SBRUSAL
,SUM(CASE WHEN  Salidas>0 THEN VrIvaSal ELSE VrIvaEnt END) AS SIVA
,SUM(CASE WHEN  Salidas>0 THEN VrDctoSal ELSE VrDctoEnt END) AS SDCT
,SUM(CASE WHEN  Salidas>0 THEN VrReteSal ELSE VrReteEnt END) AS SRET
,SUM(CASE WHEN  Salidas>0 THEN VrIcaSal ELSE VrIcaEnt END) AS SICA
 FROM Trn_Kardex AS K INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 WHERE TipDoc=@pmTipDoc  AND Documento=@pmDocumento AND IdCia=@pmIdCia
 GROUP BY  IdSubgrupo,K.IdProducto,DescripProd,TipoRef,IdProv,CdCCosto,CdSubCos,CodTarIca,CodTarRet
 ORDER BY IdSubgrupo,K.IdProducto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsRequisicion] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdRespons VARCHAR(16),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdDep VARCHAR(4),@pmVrSubTotal MONEY,@pmCantidad DECIMAL(14,4)
,@pmNContrato INT,@pmIdCiaCont CHAR(2),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmModalidad VARCHAR(10),@pmDirEntrega VARCHAR(250),@pmIdLocEnt VARCHAR(8),@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2),@pmFechaSal SMALLDATETIME,@pmNumAprob INT,@pmFecAprob SMALLDATETIME
,@pmCdUsuAprob VARCHAR(11),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(50),@pmEmailContacto VARCHAR(100),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmNum_Vehic VARCHAR(10),@pmNum_Trailer VARCHAR(10),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Requisicion (TipDoc,Requisicion,IdCia,Fecha,FechaVence,IdConcepto,IdRespons,IdCCosto,IdSubCos,IdDep,VrSubTotal,Cantidad,NContrato,IdCiaCont,NitCliente,CdAgencia,Modalidad,DirEntrega,IdLocEnt,TipSal,NumSalida,IdCiaSal,FechaSal,NumAprob,FecAprob,CdUsuAprob,NomContacto,TelsContacto,EmailContacto
,Num_Vehic,Num_Trailer,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmRequisicion,@pmIdCia,@pmFecha,@pmFechaVence,@pmIdConcepto,@pmIdRespons,@pmIdCCosto,@pmIdSubCos,@pmIdDep,@pmVrSubTotal,@pmCantidad,@pmNContrato,@pmIdCiaCont,@pmNitCliente,@pmCdAgencia,@pmModalidad,@pmDirEntrega,@pmIdLocEnt,@pmTipSal,@pmNumSalida,@pmIdCiaSal
,@pmFechaSal,@pmNumAprob,@pmFecAprob,@pmCdUsuAprob,@pmNomContacto,@pmTelsContacto,@pmEmailContacto,@pmNum_Vehic,@pmNum_Trailer,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicion] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Requisicion,IdCia,Fecha,FechaVence,IdConcepto,IdRespons,IdCCosto,IdSubCos,IdDep,VrSubTotal,Cantidad
,NContrato,IdCiaCont,NitCliente,CdAgencia,Modalidad,DirEntrega,IdLocEnt,TipSal,NumSalida,IdCiaSal,FechaSal
,NumAprob,FecAprob,CdUsuAprob,NomContacto,TelsContacto,EmailContacto,Num_Vehic,Num_Trailer,OrigenAdd,Anulado,FecDev,Observacion
,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Requisicion
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpRequisicion] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdRespons VARCHAR(16),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmIdDep VARCHAR(4),@pmVrSubTotal MONEY,@pmCantidad DECIMAL(14,4),@pmNContrato INT,@pmIdCiaCont CHAR(2),@pmNitCliente VARCHAR(16),@pmCdAgencia VARCHAR(16),@pmModalidad VARCHAR(10),@pmDirEntrega VARCHAR(250),@pmIdLocEnt VARCHAR(8)
,@pmTipSal VARCHAR(3),@pmNumSalida INT,@pmIdCiaSal CHAR(2),@pmFechaSal SMALLDATETIME,@pmNumAprob INT,@pmFecAprob SMALLDATETIME,@pmCdUsuAprob VARCHAR(11),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(50),@pmEmailContacto VARCHAR(100),@pmAnulado BIT
,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmNum_Vehic VARCHAR(10),@pmNum_Trailer VARCHAR(10),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Requisicion SET Fecha=@pmFecha,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdRespons=@pmIdRespons,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdDep=@pmIdDep,VrSubTotal=@pmVrSubTotal
,Cantidad=@pmCantidad,NContrato=@pmNContrato,IdCiaCont=@pmIdCiaCont,NitCliente=@pmNitCliente,CdAgencia=@pmCdAgencia,Modalidad=@pmModalidad,DirEntrega=@pmDirEntrega
,IdLocEnt=@pmIdLocEnt,TipSal=@pmTipSal,NumSalida=@pmNumSalida,IdCiaSal=@pmIdCiaSal,FechaSal=@pmFechaSal,NumAprob=@pmNumAprob
,FecAprob=@pmFecAprob,CdUsuAprob=@pmCdUsuAprob,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado
,NomContacto=@pmNomContacto,TelsContacto=@pmTelsContacto,EmailContacto=@pmEmailContacto,Num_Vehic=@pmNum_Vehic,Num_Trailer=@pmNum_Trailer,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryKardexCc] @pmTipDoc VARCHAR(3),@pmDocumento INT,@pmIdCia CHAR(2)
,@pmIdProducto VARCHAR(16)
 AS 
 SELECT IdProducto AS tmIdProducto,CdCCosto AS tmCdCCosto,CdSubCos AS tmCdSubCos
,CdLocal AS tmCdLocal,CodTarIca AS tmCodTarIca,CodTarRet AS tmCodTarRet
 FROM  Trn_Kardex
 WHERE   TipDoc=@pmTipDoc AND Documento=@pmDocumento AND IdCia=@pmIdCia
  AND IdProducto LIKE ISNULL(@pmIdProducto,'%')
 ORDER BY Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoOpe] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT RM.TipDoc AS TipMuc,RM.Manifiesto AS NumManif,RM.IdCia AS CdCia,Compania,M.Fecha AS MucFecha,M.FecDespacho AS MucFecDespacho
,M.IdVehiculo AS PlacaVeh,M.TipoAfiVehic AS TipoAfiVeh,M.IdConductor AS CedCondutor,CDT.RazonSocial AS NomConductor,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,M.nRemolque AS NumRemolque
,RM.TipRem AS TipRem,RM.Remesa AS NumRemesa,RM.IdCiaRem AS CdCiaRem,R.Fecha AS FechaRem,IdMercancia,DescripMcias,D.Cantidad AS Cant,D.PesoNeto AS PesoNetoRem
,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolumenRem,UndVol,Cases,Cajas,Palets,D.TarifClie AS TarifaClie,UndTarifa,RM.TarifPago AS TarifaPago,RM.TarifTabla AS TarifaTabla,UndTarifPago
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,D.IdEmp AS CdEmp,Empaque,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida
,D.IdOrigen AS CdCiuOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,D.IdDestino AS CdCiuDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,M.IdRuta AS CdRuta,T.TipoId AS ClieTipId,IdCliente,T.Dv AS ClieDv,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia,R.Modalidad AS TipoRemesa,NitRemite,Remitente,NitDestntario,Destinatario
,M.VrFletes AS MucVrFletes,dbo.FuncMucListaAntcipos(RM.Manifiesto,RM.IdCia) AS Anticipos,M.VrAnticipo+VrAntAdic AS VrAnticipos,M.VrRetencion AS MucVrRetencion,M.VrReteIca AS MucVrReteIca,M.Observacion AS MucObserv,M.Anulado AS MucAnulado,EstOrden
,M.EstCumplido,D.Cumplido,D.IdCiaCump,D.FechaCump,D.CantidadCump,D.PesoCump,D.TarifCump,D.PagoCump
--datos ordenes de pago
,M.TipOdp AS CdTipOdp,M.OrdPago AS NumOrden,M.IdCiaOdp AS MucCiaOdp,M.FechaOdp AS FecOrden,OP.PesoTotal AS OdpPesoNeto,OP.Unidades AS OdpCantidad,OP.Volumen AS OdpVolumen
,OP.TarifaTabla AS OdpTarifTabla,OP.TarifaFlete AS OdpTarifFlete,OP.UnidTarifa AS OdpUndTarifa,OP.VrTotalFletes AS OdpTotalFletes,OP.VrDescuento AS OdpVrDcto,OP.VrRetencion AS OdpVrRetFte
,OP.VrReteIca AS OdpReteIca,OP.VrAnticipos AS OdpAnticipos,OP.VrFaltantes AS OdpFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,OP.VrNeto AS OdpVrNetoPagar,ODP.Observacion AS OdpObserv
--egresos
,OP.TipEgr,Egreso,IdCiaEgr,EG.Fecha AS FecEgreso,EG.NumCheque,TotalEgresos
--datos remesas
,R.NumPedido AS nPedido,IdCiaPed,FechaPed,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue,R.Observacion AS RemObserv
--Datos del vehic/ poseedor
,NP.TipoId AS TercTipId,NP.Dv AS TercDv,NP.NomCial AS TercNomCial,NP.Direccion AS TercDireccion,NP.IdLocal AS TercCodCiudad,L.Localidad AS NomCiudad,NP.Telefono AS TercTelefono,NP.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,TPR.VehPropio AS VehEsPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia,CDT.Direccion AS CdtDireccion,CDT.Telefono AS CdtTelefono,CDT.e_mail AS CdtEmail,CDT.TelMovil AS CdtCelular
 FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON RM.TipRem=D.TipDoc AND RM.Remesa=D.NumOrden AND RM.IdCiaRem=D.IdCia AND RM.ItemRem=D.Item
 INNER JOIN Trn_TraRemesa AS R ON RM.TipRem=R.TipDoc AND RM.Remesa=R.NumOrden AND RM.IdCiaRem=R.IdCia
 INNER JOIN Companias AS CN ON RM.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero
 INNER JOIN Terceros AS CDT ON M.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 INNER JOIN Localidades AS L ON NP.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor
 INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria  INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero
 LEFT JOIN Trn_TraOrdenPago AS ODP ON M.TipOdp=ODP.TipDoc AND M.OrdPago=ODP.OrdPago AND M.IdCiaOdp=ODP.IdCia
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN Empaques AS EQ ON D.IdEmp=EQ.IdEmp
 LEFT JOIN TercCndtores AS CT ON M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Trn_Comprobantes AS EG ON OP.TipEgr=EG.TipCom AND OP.Egreso=EG.Comprobante AND OP.IdCiaEgr=EG.IdCia
 --total de egresos
 LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
 FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
 WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EGT ON M.TipOdp=EGT.TipOrden AND M.OrdPago=EGT.NumOPago AND M.IdCiaOdp=EGT.CdCiaOpago
WHERE RM.TipDoc='MUC' AND M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
AND  M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
--nomina jun 29/2016
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomDotacionLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT DT.IdCia AS CdCia,CI.Compania,DT.Numero AS NumReg,DT.Fecha AS FechaEnt,TipoDotac,TipoEntrega,PlazoEntrega,FecVence
,TipoReg,DT.Anulado,FechaCrea,DT.IdUsuario AS CdUsuario,Usuario,DT.Observacion AS Observ,CritFiltros
,Item,D.IdEmpleado AS CdEmpleado,Apellidos,Nombres,D.NContrato AS NumCont,D.IdPrend AS CdPrend,DescPrenda,D.Descripcion,Cantidad,Unidad,D.VrUnitario
,C.IdTipCon AS CdTipCon,TipoContrato,FecIngreso,C.IdArea AS CdArea,Area,C.IdDep AS CdDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCargo AS CodCargo,Cargo
,C.IdCia AS ConCdCia,CN.Compania AS ConCompania,C.IdNom AS CdNom,TipoNomina,C.TipoLiquida AS TipoLiq,DchDotacion,VrSalario,VrAuxTrans,SalMinimo,SalIntegral
,E.Codigo,e_mail,E.Sexo,E.IdProf AS CodProf,Profesion,FecUltDot
 FROM Trn_NomDotacion AS DT INNER JOIN Trn_NomDotacionDet AS D ON DT.IdCia=D.IdCia AND DT.Numero=D.Numero
 INNER JOIN Companias AS CI ON DT.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON DT.IdUsuario=U.IdUsuario
 INNER JOIN NomPrendas AS PN ON D.IdPrend=PN.IdPrend
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf 
 LEFT JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato LEFT JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo LEFT JOIN Dependencias AS DP ON C.IdDep=DP.IdDep
 LEFT JOIN Areas AS AR ON C.IdArea=AR.IdArea LEFT JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 LEFT JOIN Companias AS CN ON C.IdCia=CN.IdCia  LEFT JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 WHERE DT.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND DT.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomDotacion_Cr] @pmIdCia CHAR(2),@pmNumeroIni INT,@pmNumeroFin INT
 AS 
 SELECT DT.IdCia AS CdCia,CI.Compania,DT.Numero AS NumReg,DT.Fecha AS FechaEnt,TipoDotac,TipoEntrega,PlazoEntrega,FecVence
,TipoReg,DT.Anulado,FechaCrea,DT.IdUsuario AS CdUsuario,Usuario,DT.Observacion AS Observ,CritFiltros
,Item,D.IdEmpleado AS CdEmpleado,Apellidos,Nombres,D.NContrato AS NumCont,D.IdPrend AS CdPrend,DescPrenda,D.Descripcion,Cantidad,Unidad,D.VrUnitario
,C.IdTipCon AS CdTipCon,TipoContrato,FecIngreso,C.IdArea AS CdArea,Area,C.IdDep AS CdDep,Dependencia,C.IdInstala AS IdInstla,Instlacion,C.IdCargo AS CodCargo,Cargo
,C.IdCia AS ConCdCia,CN.Compania AS ConCompania,C.IdNom AS CdNom,TipoNomina,C.TipoLiquida AS TipoLiq,DchDotacion,VrSalario,VrAuxTrans,SalMinimo,SalIntegral
,E.Codigo,e_mail,E.Sexo,E.IdProf AS CodProf,Profesion,FecUltDot
 FROM Trn_NomDotacion AS DT INNER JOIN Trn_NomDotacionDet AS D ON DT.IdCia=D.IdCia AND DT.Numero=D.Numero
 INNER JOIN Companias AS CI ON DT.IdCia=CI.IdCia INNER JOIN adm_Usuarios AS U ON DT.IdUsuario=U.IdUsuario
 INNER JOIN NomPrendas AS PN ON D.IdPrend=PN.IdPrend
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf 
 LEFT JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato LEFT JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 LEFT JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo LEFT JOIN Dependencias AS DP ON C.IdDep=DP.IdDep
 LEFT JOIN Areas AS AR ON C.IdArea=AR.IdArea LEFT JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 LEFT JOIN Companias AS CN ON C.IdCia=CN.IdCia  LEFT JOIN TiposNom AS TN ON C.IdNom=TN.IdNom
 WHERE DT.IdCia=@pmIdCia AND DT.Numero BETWEEN @pmNumeroIni AND @pmNumeroFin
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomPrendas] @pmIdPrend VARCHAR(4),@pmDescPrenda VARCHAR(100),@pmTipoPrenda VARCHAR(10),@pmVrCostoUnd MONEY,@pmInactivo BIT
 AS 
 INSERT INTO NomPrendas (IdPrend,DescPrenda,TipoPrenda,Inactivo,VrCostoUnd)
 VALUES (@pmIdPrend,@pmDescPrenda,@pmTipoPrenda,@pmInactivo,@pmVrCostoUnd)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomPrendas] @pmIdPrend VARCHAR(4),@pmDescPrenda VARCHAR(100),@pmTipoPrenda VARCHAR(10),@pmVrCostoUnd MONEY,@pmInactivo BIT
 AS 
 UPDATE NomPrendas SET DescPrenda=@pmDescPrenda,TipoPrenda=@pmTipoPrenda,Inactivo=@pmInactivo,VrCostoUnd=@pmVrCostoUnd
 WHERE IdPrend=@pmIdPrend
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomPrendas] @pmIdPrend VARCHAR(4)
 AS 
 SELECT IdPrend,DescPrenda,TipoPrenda,VrCostoUnd,Inactivo
 FROM NomPrendas WHERE IdPrend=@pmIdPrend
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomDotacionDet] @pmIdCia CHAR(2),@pmNumero INT,@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmIdPrend VARCHAR(4)
,@pmDescripcion VARCHAR(250),@pmCantidad DECIMAL(14,4),@pmUnidad VARCHAR(10),@pmVrUnitario MONEY
 AS 
 INSERT INTO Trn_NomDotacionDet (IdCia,Numero,Item,IdEmpleado,NContrato,IdPrend,Descripcion,Cantidad,Unidad,VrUnitario)
 VALUES (@pmIdCia,@pmNumero,@pmItem,@pmIdEmpleado,@pmNContrato,@pmIdPrend,@pmDescripcion,@pmCantidad,@pmUnidad,@pmVrUnitario)
GO