if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paDelEstadoOper]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paDelEstadoOper]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsEstadoOper]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsEstadoOper]
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].[paInsNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomDetalle_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomLiqDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomLiqDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsReqDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsReqDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTerceros]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTerceros_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTerceros_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Liquida]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Liquida]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Liquida_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Liquida_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Nomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Nomina_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Nomina_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_ReqDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_ReqDetalle_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryEstadoOper]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryEstadoOper]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoOrdenRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoOrdenRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoOrdenRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoOrdenRelDet]
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].[paQryNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaInt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaInt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomLiqDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomLiqDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryReqDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryReqDetalle]
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].[paQryRequisicionDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionDet]
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].[paQryTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTerceros]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosBus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosBus]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosCod]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosCod]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosUbi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosUbi]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Liquida]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Liquida]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_LiquidaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_LiquidaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Nomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Nomina_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Nomina_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NominaLInt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_NominaLInt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_ReqDetalleDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_ReqDetalleDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryVehiculosUbi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryVehiculosUbi]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpEstadoOper]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpEstadoOper]
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].[paUpNomDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTerceros]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTerceros]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptm_Liquida]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUptm_Liquida]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptm_Nomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUptm_Nomina]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantDevg DECIMAL(10,4),@pmCantDed DECIMAL(10,4),@pmVrUnitario MONEY,@pmVrTotDevg MONEY,@pmVrTotDed MONEY
,@pmUnidad VARCHAR(10),@pmClaseLiq VARCHAR(10),@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmDiasCalc DECIMAL(10,4),@pmDiasNov DECIMAL(10,4),@pmCodFondo VARCHAR(8),@pmNPrestamo INT,@pmNCuota INT,@pmOrigCargue VARCHAR(10),@pmVrBaseLiq MONEY,@pmVrBasExceso MONEY,@pmNitTercero VARCHAR(16)
 AS 
 INSERT INTO Trn_NomDetalle (IdPeriodo,KeyRegistro,IdEmpleado,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero)
 VALUES (@pmIdPeriodo,@pmKeyRegistro,@pmIdEmpleado,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantDevg,@pmCantDed,@pmVrUnitario,@pmVrTotDevg,@pmVrTotDed,@pmUnidad,@pmClaseLiq,@pmVrOrigen,@pmTarifa
,@pmDiasCalc,@pmDiasNov,@pmCodFondo,@pmNPrestamo,@pmNCuota,@pmOrigCargue,@pmVrBaseLiq,@pmVrBasExceso,@pmNitTercero)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUpNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmCantDevg DECIMAL(10,4),@pmCantDed DECIMAL(10,4),@pmVrUnitario MONEY,@pmVrTotDevg MONEY
,@pmVrTotDed MONEY,@pmUnidad VARCHAR(10),@pmClaseLiq VARCHAR(10),@pmVrOrigen MONEY,@pmTarifa DECIMAL(10,4),@pmDiasCalc DECIMAL(10,4),@pmDiasNov DECIMAL(10,4),@pmCodFondo VARCHAR(8),@pmNPrestamo INT,@pmNCuota INT,@pmOrigCargue VARCHAR(10),@pmVrBaseLiq MONEY,@pmVrBasExceso MONEY,@pmNitTercero VARCHAR(16)
 AS 
 UPDATE Trn_NomDetalle SET IdEmpleado=@pmIdEmpleado,IdConcepto=@pmIdConcepto,Detalle=@pmDetalle,CantDevg=@pmCantDevg,CantDed=@pmCantDed,VrUnitario=@pmVrUnitario,VrTotDevg=@pmVrTotDevg,VrTotDed=@pmVrTotDed,Unidad=@pmUnidad,ClaseLiq=@pmClaseLiq
,VrOrigen=@pmVrOrigen,Tarifa=@pmTarifa,DiasCalc=@pmDiasCalc,DiasNov=@pmDiasNov,CodFondo=@pmCodFondo,NPrestamo=@pmNPrestamo,NCuota=@pmNCuota,OrigCargue=@pmOrigCargue,VrBaseLiq=@pmVrBaseLiq,VrBasExceso=@pmVrBasExceso,NitTercero=@pmNitTercero
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomDetalle] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmItem INT
 AS 
 SELECT IdPeriodo,KeyRegistro,Item,IdEmpleado,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc
,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero
 FROM Trn_NomDetalle
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro 
  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].[paInsNomDetalle_Sel] @pmIdEmpleado VARCHAR(16),@pmNewCedula VARCHAR(16)
 AS 
 INSERT INTO Trn_NomDetalle (IdPeriodo,KeyRegistro,IdEmpleado,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen
,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero)
 SELECT D.IdPeriodo,@pmNewCedula +CAST(NContrato AS VARCHAR(10)) +CASE EsRetiro WHEN 1 THEN '_1' ELSE '_0'  END
,@pmNewCedula,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen
,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero
 FROM Trn_NomDetalle AS D INNER JOIN Trn_Nomina AS N ON D.IdPeriodo=N.IdPeriodo AND D.KeyRegistro=N.KeyRegistro
 WHERE D.IdEmpleado=@pmIdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Nomina_Sel] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad
,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo,tmVrPension,tmVrSalud,tmBasExceso,tmNitTercero)
 SELECT @pmtmNumero,Item,D.IdEmpleado,D.IdConcepto,ClaseCon,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad,ClaseLiq,VrOrigen,D.Tarifa,DiasCalc
,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,NContrato,FecInicial,FecFinal,D.IdPeriodo,0,0,VrBasExceso,NitTercero
 FROM Trn_NomDetalle AS D INNER JOIN NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 INNER JOIN Trn_Nomina AS N ON D.IdPeriodo=N.IdPeriodo AND D.KeyRegistro=N.KeyRegistro
 WHERE D.IdPeriodo=@pmIdPeriodo AND D.KeyRegistro=@pmKeyRegistro 
 AND  ClaseLiq  IN  ('NOMINA','NOVEDAD')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNominaInt] @pmIdPeriodo VARCHAR(8),@pmIdNom VARCHAR(4),@pmEsRetiro BIT=Null
 AS 
 SELECT N.IdPeriodo AS IdPdo,N.KeyRegistro AS IdKeyReg,N.IdEmpleado AS IdEmpldo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido,N.NContrato AS NCntto,IdDep,IdInstala,IdCCosto
,IdSubCos,IdNom,VrSalario,N.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom,EsRetiro,NLiqRet,TipoLiq,Apellidos,Nombres,EsCondtor,E.pVehiculo AS VehiculoDef
--detalle de la nomina
,Item,D.IdConcepto AS IdConcep,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,ClaseLiq,VrOrigen,D.Tarifa AS Tarfa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,NitTercero
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D  ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN  NomConceptos AS C ON D.IdConcepto=C.IdConcepto
 WHERE N.IdPeriodo=@pmIdPeriodo AND IdNom like ISNULL(@pmIdNom,'%')
 AND  (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 ORDER BY N.IdEmpleado,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoOrdenRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null
AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,Fecha,FechaOrden,FechaVence,O.IdVehiculo,NumVeh,kmVehiculo,nRemolque,kmRemolque,O.IdTipoOdt AS CdTipoOdt,TipoOrden,O.IdCenSer AS CdCenSer,CentroServ
,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,O.IdOperario AS NitOperario,T.RazonSocial AS NomOperario,IdSupervisor,NSU.RazonSocial AS Supervisor,O.Modalidad
,VrServicios,VrRepuestos,VrManoObra,VrOtros,FechaCierre,NitCenSer,O.CdEstadoVeh,EV.Estado AS EstadoVeh,EV.NColor AS EdvNColor,EV.OutDemand,O.IdEstado AS CdEstado,ED.Estado AS EstadoDoc,ED.NColor AS EdNumColor
,O.Anulado,NumDev,FecDev,O.Observacion AS Observ,O.TimeSys AS FechaCrea,O.FecUpdate AS FecActualiza,O.IdCiaCrea AS CdCIaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario,Leyenda
,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
,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,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,V.Descripcion AS VehDescripcion,KmInicial,KmActual,Km2Actual,V.Ubicacion
,V.IdEstado AS VehCdEstado,EVH.Estado AS VehEstado,EVH.NColor AS VehEstColor,EVH.OutDemand AS VehNoDisponible,V.Inactivo AS VehInactivo,V.IdGrupo AS CdGrupo,GrupoProp
,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_MttoOrden AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MttoTiposOdt AS TOT ON O.IdTipoOdt=TOT.IdTipoOdt INNER JOIN CentrosServ AS CS ON O.IdCenSer=CS.IdCenSer
 INNER JOIN Terceros AS T ON O.IdOperario=T.IdTercero INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NSU ON O.IdSupervisor=NSU.IdTercero
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 LEFT JOIN EstadoVeh AS EV ON O.CdEstadoVeh=EV.IdEstado 
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
 LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero
 LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN EstadoVeh AS EVH ON V.IdEstado=EVH.IdEstado LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoOrdenRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null
AS 
 SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,Fecha,FechaOrden,FechaVence,O.IdVehiculo,NumVeh,kmVehiculo,nRemolque,kmRemolque,O.IdTipoOdt AS CdTipoOdt,TipoOrden
,O.IdCenSer AS CdCenSer,CentroServ,NitCenSer,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,IdSupervisor,NSU.RazonSocial AS Supervisor
--<campos de detalles
,D.Item,D.TipoItem,D.IdConc AS CdConc,D.Descripcion,D.Cantidad,D.IdUnd,D.UndMed,D.VrUnitario,D.TipoServ,D.IdOperario AS NitOperario,T.RazonSocial AS Operario,D.IdEstado AS CdEstadoServ,ESV.Estado AS EstadoServ,ESV.NColor AS ES_NumColor
,D.CdMtto,TipoMtto,C.IdSistema AS CdSistema,Sistema,Seccion,Concepto,D.IdProducto,DescripProd,D.FechaServ,D.NumParte,D.TipReq,D.Requisicion,D.IdCiaReq,TipSal,NumSalida,IdCiaSal,CantSalida,D.EstadoReq
,CASE ISNULL(D.TipoReg,0) WHEN 0 THEN 'SERVICIO' ELSE 'REQ' END AS Tipo_Reg
--campos de detalles>
,O.Modalidad,VrServicios,VrRepuestos,VrManoObra,VrOtros,O.IdEstado AS CdEstado,ED.Estado AS EstadoDoc,ED.NColor AS EdNumColor,FechaCierre,O.Anulado,NumDev,FecDev
,O.Observacion AS Observ,O.TimeSys AS FechaCrea,O.FecUpdate AS FecActualiza,O.IdCiaCrea AS CdCIaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario,Leyenda
--campos de vehículo
,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
,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,V.IdGrupo AS CdGrupo,GrupoProp
,PesoVacio,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,V.Descripcion AS VehDescripcion,KmInicial,KmActual,Km2Actual AS kmRuta,CentFinal AS KmOtro,V.Ubicacion
,V.IdEstado AS VehCdEstado,EV.Estado AS VehEstado,EV.NColor AS VehEstColor,EV.OutDemand AS VehNoDisponible,V.Inactivo AS VehInactivo
,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_MttoOrden AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MttoTiposOdt AS TOT ON O.IdTipoOdt=TOT.IdTipoOdt INNER JOIN CentrosServ AS CS ON O.IdCenSer=CS.IdCenSer
 INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NSU ON O.IdSupervisor=NSU.IdTercero
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
  --<subconsulta detalles
 INNER JOIN (SELECT DS.TipOdt,DS.NumOrden,DS.IdCia,DS.Item,DS.FechaServ,DS.IdConc,DS.Descripcion,DS.Cantidad,DS.VrUnitario,'0' AS IdUnd,DS.UndMed,DS.CdMtto,DS.IdEstado,DS.IdOperario
 ,DS.NumVehic,DS.NumParte,DS.TipoServ,DS.FechaCrea,DS.IdUsuario,'0' AS TipReq,0 AS Requisicion,'00' AS IdCiaReq
 ,CASE DS.TipoServ WHEN 'EXTERNA' THEN 'MO' ELSE 'REPUESTO' END AS TipoItem,'0' AS IdProducto,0 AS EstadoReq,'0' AS TipSal,0 AS NumSalida,'00' AS IdCiaSal,0 AS CantSalida,0 AS TipoReg
 FROM Trn_MttoOrdenDet AS DS INNER JOIN Trn_MttoOrden AS O ON DS.TipOdt=O.TipDoc AND DS.NumOrden=O.NumOrden AND DS.IdCia=O.IdCia
 WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND DS.IdCia LIKE ISNULL(@pmIdCia,'%%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 UNION ALL
 SELECT R.TipOdt,R.NumOrden,R.IdCiaOdt,R.Item,R.FechaCrea,R.CdConcServ,R.Descripcion,R.Cantidad,R.VrUnitario,R.IdUnd,Unidad,C.IdMtto
 ,CASE R.EstadoReq WHEN -1 THEN '9999' WHEN 0 THEN '0001' WHEN 1 THEN '9998' ELSE '0001' END,R.IdOperario
 ,R.NumVehic,R.NumParte,'INTERNA',R.FechaCrea,R.IdUsuario,R.TipReq,R.Requisicion,R.IdCia,R.TipoItem,R.IdProducto,R.EstadoReq
 ,R.TipSal,R.NumSalida,R.IdCiaSal,R.CantSalida,1 AS TipoReg
 FROM Trn_MttoOrdenReq AS R INNER JOIN Trn_MttoOrden AS O ON R.TipOdt=O.TipDoc AND R.NumOrden=O.NumOrden AND R.IdCiaOdt=O.IdCia
 INNER JOIN Trn_Requisicion AS RQ ON R.TipReq=RQ.TipDoc AND R.Requisicion=RQ.Requisicion AND R.IdCia=RQ.IdCia 
 LEFT JOIN MttoConceptos AS C ON R.CdConcServ=C.IdConc LEFT JOIN UndMed AS UM ON R.IdUnd=UM.IdUnd
 WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND R.IdCiaOdt LIKE ISNULL(@pmIdCia,'%%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') ) AS D ON O.TipDoc=D.TipOdt AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 --subconsulta detalles>
 INNER JOIN Terceros AS T ON D.IdOperario=T.IdTercero INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Marcas AS M ON V.IdMarca=M.IdMarca LEFT JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
 LEFT JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria LEFT JOIN Terceros AS NPO ON V.IdPoseedor=NPO.IdTercero
 LEFT JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd LEFT JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON O.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN EstadoVeh AS EV ON V.IdEstado=EV.IdEstado LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN MttoConceptos AS C ON D.IdConc=C.IdConc LEFT JOIN MttoSistemas AS S ON C.IdSistema=S.IdSistema
 LEFT JOIN MttoTipos AS TM ON D.CdMtto=TM.IdMtto LEFT JOIN MttoEstados AS ESV ON D.IdEstado=ESV.IdEstado
 LEFT JOIN ProdMcias AS P ON D.IdProducto=P.IdProducto
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryVehiculosUbi] @pmIdEst CHAR(2),@pmTipoRep INT,@pmFecInicio SMALLDATETIME,@pmIdTipoVeh VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
SELECT V.IdVehiculo AS PlacaVeh,V.NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca,V.IdLinea AS CdLinea,V.IdColor AS CdColor,CL.NomColor AS ColorVeh,V.IdCrceria AS CdCrceria,TipoCar,V.Modelo
,V.Config,V.PesoVacio,V.NumMotor,V.SerieChasis,V.CdRemque,V.CarrCapac,V.UndCapc,NitEmpresa,NE.RazonSocial AS Empresa,V.IdPropietario,NP.RazonSocial AS Propietario,V.IdPoseedor,NT.RazonSocial AS Poseedor
,V.IdConductor,NC.RazonSocial AS Conductor,V.TipoAfil,V.IdPpd,TipoProp,V.IdGrupo AS CdGrupo,GrupoProp,V.IdEstado AS CdEstado,EV.Estado,EV.NColor AS EstNColor,NumSoat,FecSoat,VigSoat,KmActual,V.Descripcion,V.Observacion,FecPriServ,FecUltServ
,V.Ubicacion,V.CdLocal AS CodCiuUbic,LU.Localidad AS CiuUbicacion,LU.IdDep AS CodDpto,Departamento,V.FechaAdd AS FechaCrea
--datos de ordenes
,TM.TipoOrd,TM.NumOrden,TM.IdCiaOrd,TM.Remesa,TM.IdCiaRem,R.Fecha AS FecOrden,R.IdCliente,C.RazonSocial AS NomCliente,IdLocOrigen,CO.Localidad AS CiuOrigen,IdLocDestino,CD.Localidad AS CiuDestino,TM.nRemolque
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 TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria
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 GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
LEFT JOIN Terceros AS NE ON V.NitEmpresa=NE.IdTercero 
LEFT JOIN Localidades AS LU ON V.CdLocal=LU.IdLocal LEFT JOIN Departamentos AS DU ON LU.IdDep=DU.IdDep
LEFT JOIN (SELECT IdOperario,IdVehiculo,nRemolque,TipoOrd,NumOrden,IdCiaOrd,Remesa,IdCiaRem FROM tm_TraInfOper WHERE IdEst=@pmIdEst AND TipoRep=@pmTipoRep) AS TM ON V.IdVehiculo=TM.IdVehiculo
LEFT JOIN (SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdCliente,IdAgencia,IdLocOrigen,IdLocDestino,IdVehiculo,IdConductor,nRemolque
FROM Trn_TraRemesa WHERE TipDoc='OCT' AND Fecha>=@pmFecInicio
UNION ALL SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdCliente,IdAgencia,IdDestino,IdDestino AS CiuDstno,IdVehiculo,IdConductor,nRemolque
FROM Trn_TraOrdenServ WHERE TipDoc='OSA' AND Fecha>=@pmFecInicio) AS R ON TM.TipoOrd=R.TipDoc AND TM.NumOrden=R.NumOrden AND TM.IdCiaOrd=R.IdCia
LEFT JOIN Terceros AS C ON R.IdCliente=C.IdTercero LEFT JOIN Localidades AS CO ON R.IdLocOrigen=CO.IdLocal LEFT JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal
WHERE V.Inactivo=0 AND (V.IdTipoVeh=@pmIdTipoVeh OR @pmIdTipoVeh IS NULL)
AND (V.IdEstado=@pmIdEstado OR @pmIdEstado IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_Nomina_Cr] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16)=Null
 AS 
 SELECT tmIdEmpleado,Apellidos,Nombres,tmItem,tmIdConcepto,tmDetalle,Concepto,tmClaseCon,tmCantDevg,tmUnidad,tmVrUnitario,tmVrTotDevg,tmCantDed,tmVrUnitario AS VrUndDed
,tmVrTotDed,tmNPrestamo,tmNCuota,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmDiasCalc-tmDiasNov AS Dias,tmCodFondo,FD.Fondo AS tm_Fondo,tmVrBaseLiq,tmFecIni,tmFecFin,tmIdPeriodo
,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmClaseLiq,tmOrigCargue,tmBasExceso,tmNitTercero
--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,FecNacmto,EsCondtor,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,E.NContrato AS NumContrato,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,ModoRet,C.IdEstado AS IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,C.IdUsuario AS Con_IdUsuari,Usuario
,pVehiculo,NumVeh,TipoAdmon,VehPropio
 FROM tm_Nomina AS N INNER JOIN Empleados AS E ON N.tmIdEmpleado=E.IdEmpleado
 INNER JOIN NomConceptos AS CN ON N.tmIdConcepto=CN.IdConcepto
 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
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN  Emp_Contrato AS C ON E.NContrato=C.NContrato  INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo  INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea
 INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep  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 TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EN ON C.IdEstado=EN.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario  INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase 
 INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada  LEFT JOIN Fondos AS FD ON N.tmCodFondo=FD.IdFondo
 LEFT JOIN NomVehiculos AS VH ON E.pVehiculo=VH.IdVehiculo
 WHERE tmNumero=@pmtmNumero  AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
 ORDER BY Apellidos,Nombres,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_Nomina] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT
 AS 
 SELECT tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq
,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo
,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud,tmBasExceso,tmNitTercero
 FROM tm_Nomina
 WHERE tmNumero=@pmtmNumero AND tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
ORDER BY tmIdEmpleado,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUptm_Nomina] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmClaseCon VARCHAR(10),@pmtmDetalle VARCHAR(100),@pmtmCantDevg DECIMAL(10,4)
,@pmtmCantDed DECIMAL(10,4),@pmtmVrUnitario MONEY,@pmtmVrTotDevg MONEY,@pmtmVrTotDed MONEY,@pmtmUnidad VARCHAR(10),@pmtmClaseLiq VARCHAR(10),@pmtmVrOrigen MONEY,@pmtmTarifa DECIMAL(10,4),@pmtmDiasCalc DECIMAL(10,4)
,@pmtmDiasNov DECIMAL(10,4),@pmtmCodFondo VARCHAR(8),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmOrigCargue VARCHAR(10),@pmtmVrBaseLiq MONEY,@pmtmNContto INT,@pmtmFecIni SMALLDATETIME,@pmtmFecFin SMALLDATETIME,@pmtmIdPeriodo VARCHAR(8)
,@pmtmVrPension MONEY,@pmtmVrSalud MONEY,@pmtmBasExceso MONEY,@pmtmNitTercero VARCHAR(16)
 AS 
 UPDATE tm_Nomina SET tmIdConcepto=@pmtmIdConcepto,tmClaseCon=@pmtmClaseCon,tmDetalle=@pmtmDetalle,tmCantDevg=@pmtmCantDevg,tmCantDed=@pmtmCantDed,tmVrUnitario=@pmtmVrUnitario,tmVrTotDevg=@pmtmVrTotDevg,tmVrTotDed=@pmtmVrTotDed
,tmUnidad=@pmtmUnidad,tmClaseLiq=@pmtmClaseLiq,tmVrOrigen=@pmtmVrOrigen,tmTarifa=@pmtmTarifa,tmDiasCalc=@pmtmDiasCalc,tmDiasNov=@pmtmDiasNov,tmCodFondo=@pmtmCodFondo,tmNPrestamo=@pmtmNPrestamo,tmNCuota=@pmtmNCuota,tmOrigCargue=@pmtmOrigCargue
,tmVrBaseLiq=@pmtmVrBaseLiq,tmNContto=@pmtmNContto,tmFecIni=@pmtmFecIni,tmFecFin=@pmtmFecFin,tmIdPeriodo=@pmtmIdPeriodo,tmVrPension=@pmtmVrPension,tmVrSalud=@pmtmVrSalud,tmBasExceso=@pmtmBasExceso,tmNitTercero=@pmtmNitTercero
 WHERE tmNumero=@pmtmNumero AND tmIdEmpleado=@pmtmIdEmpleado AND tmItem=@pmtmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_Nomina] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16),@pmtmItem INT,@pmtmIdConcepto VARCHAR(4),@pmtmClaseCon VARCHAR(10),@pmtmDetalle VARCHAR(100),@pmtmCantDevg DECIMAL(10,4),@pmtmCantDed DECIMAL(10,4),@pmtmVrUnitario MONEY,@pmtmVrTotDevg MONEY,@pmtmVrTotDed MONEY
,@pmtmUnidad VARCHAR(10),@pmtmClaseLiq VARCHAR(10),@pmtmVrOrigen MONEY,@pmtmTarifa DECIMAL(10,4),@pmtmDiasCalc DECIMAL(10,4),@pmtmDiasNov DECIMAL(10,4),@pmtmCodFondo VARCHAR(8),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmOrigCargue VARCHAR(10),@pmtmVrBaseLiq MONEY,@pmtmNContto INT,@pmtmFecIni SMALLDATETIME
,@pmtmFecFin SMALLDATETIME,@pmtmIdPeriodo VARCHAR(8),@pmtmFecUltLiq SMALLDATETIME,@pmtmFecIniPmd SMALLDATETIME,@pmtmFecFinPmd SMALLDATETIME,@pmtmLogPmdio VARCHAR(600),@pmtmVrPension MONEY,@pmtmVrSalud MONEY,@pmtmBasExceso MONEY,@pmtmNitTercero VARCHAR(16)
 AS 
 INSERT INTO tm_Nomina (tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin
,tmIdPeriodo,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmVrPension,tmVrSalud,tmBasExceso,tmNitTercero)
 VALUES (@pmtmNumero,@pmtmIdEmpleado,@pmtmItem,@pmtmIdConcepto,@pmtmClaseCon,@pmtmDetalle,@pmtmCantDevg,@pmtmCantDed,@pmtmVrUnitario,@pmtmVrTotDevg,@pmtmVrTotDed,@pmtmUnidad,@pmtmClaseLiq,@pmtmVrOrigen,@pmtmTarifa
,@pmtmDiasCalc,@pmtmDiasNov,@pmtmCodFondo,@pmtmNPrestamo,@pmtmNCuota,@pmtmOrigCargue,@pmtmVrBaseLiq,@pmtmNContto,@pmtmFecIni,@pmtmFecFin,@pmtmIdPeriodo,@pmtmFecUltLiq,@pmtmFecIniPmd,@pmtmFecFinPmd,@pmtmLogPmdio,@pmtmVrPension,@pmtmVrSalud,@pmtmBasExceso,@pmtmNitTercero)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_NominaLInt] @pmtmNumero VARCHAR(5),@pmtmIdEmpleado VARCHAR(16)
,@pmClaseLiq VARCHAR(10)=Null
 AS 
 SELECT tmNumero,tmIdEmpleado,tmItem,tmIdConcepto,tmClaseCon,tmDetalle,tmCantDevg,tmCantDed,tmVrUnitario,tmVrTotDevg,tmVrTotDed,tmUnidad,tmClaseLiq
,tmVrOrigen,tmTarifa,tmDiasCalc,tmDiasNov,tmCodFondo,tmNPrestamo,tmNCuota,tmOrigCargue,tmVrBaseLiq,tmNContto,tmFecIni,tmFecFin,tmIdPeriodo
,tmFecUltLiq,tmFecIniPmd,tmFecFinPmd,tmLogPmdio,tmNitTercero
 FROM tm_Nomina
 WHERE tmNumero=@pmtmNumero AND tmClaseLiq IN ('NOMINA','PROVISION','APORTE','NOVEDAD',@pmClaseLiq)
 AND  tmIdEmpleado like ISNULL(@pmtmIdEmpleado,'%')
 ORDER BY tmItem
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,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 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),@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)
 VALUES (@pmIdReg,@pmIdConcepto,@pmFecInicio,@pmVrAplicar,@pmModalidad,@pmOrigenBase,@pmFrecAplica,@pmCedEmpleado,@pmCodNom,@pmCodDep,@pmCodCcto,@pmCritSelect,@pmFecFinal,@pmExceptuar
,@pmObservacion,@pmCodSubCos,@pmInactivo,@pmNumPerMes,@pmFecAdd,@pmIdUsuario,@pmNitTercero)
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,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 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),@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,FecUpdate=@pmFecUpdate
 WHERE IdReg=@pmIdReg
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,FecAdd,FecUpdate,IdUsuario
 FROM Trn_NomConAuto
 WHERE IdReg=@pmIdReg
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_ReqDetalle_Sel] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_ReqDetalle (tmNumero,tmItem,tmCdProducto,tmDescripcion,tmIdSubgrupo,tmCdBodega,tmCantidad
,tmIdUnd,tmVrUnitario,tmTipOrd,tmNumOrden,tmIdCiaOrd,tmTipSal,tmNSalida,tmIdCiaSal,tmFechaSal
,tmCantSalida,tmIdOperario,tmCdCenServ,tmNitTercero,tmNumVehic,tmNumParte,tmEstado,tmObservacion,tmCodConc)
 SELECT  @pmtmNumero,Item,CdProducto,Descripcion,IdSubgrupo,CdBodega,Cantidad,IdUnd,VrUnitario,TipOrd,NumOrden,IdCiaOrd,TipSal,NSalida,IdCiaSal,FechaSal
,0,'0','','0','','',0,Referencia,''
 FROM Trn_ReqDetalle
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_ReqDetalleDso] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmCdProducto,tmDescripcion,tmCantidad,tmIdUnd,Unidad,tmVrUnitario,tmCantidad*tmVrUnitario AS ValorTotal
,tmTipOrd,tmNumOrden,tmIdCiaOrd,tmTipSal,tmNSalida,tmIdCiaSal,tmFechaSal,tmIdSubgrupo,tmCdBodega,tmObservacion,tmNumero
 FROM tm_ReqDetalle AS D INNER JOIN UndMed AS U ON D.tmIdUnd=U.IdUnd
 WHERE tmNumero=@pmtmNumero
 ORDER BY tmItem
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.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].[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,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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paDelEstadoOper] @pmIdEstado VARCHAR(4)
 AS 
 DELETE FROM EstadoOper WHERE IdEstado=@pmIdEstado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpEstadoOper] @pmIdEstado VARCHAR(4),@pmEstado VARCHAR(50),@pmNColor INT,@pmInactivo BIT
 AS 
 UPDATE EstadoOper SET Estado=@pmEstado,NColor=@pmNColor,Inactivo=@pmInactivo
 WHERE IdEstado=@pmIdEstado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryEstadoOper] @pmIdEstado VARCHAR(4)
 AS 
 IF @pmIdEstado IS NULL
  SELECT IdEstado,Estado,NColor
  FROM EstadoOper
  WHERE Inactivo=0
 ELSE
  SELECT IdEstado,Estado,NColor,Inactivo
  FROM EstadoOper
  WHERE IdEstado=@pmIdEstado
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsEstadoOper] @pmIdEstado VARCHAR(4),@pmEstado VARCHAR(50),@pmNColor INT,@pmInactivo BIT
 AS 
 INSERT INTO EstadoOper (IdEstado,Estado,NColor,Inactivo)
 VALUES (@pmIdEstado,@pmEstado,@pmNColor,@pmInactivo)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercerosUbi] @pmIdEst CHAR(2),@pmTipoRep INT,@pmFecInicio SMALLDATETIME,@pmIdEstOper VARCHAR(4)=Null
 AS 
 SELECT T.IdTercero,T.Dv,T.TipoId,T.RazonSocial,T.Codigo,T.Direccion AS Dreccion,T.IdLocal AS CodCiud,L.Localidad AS Ciudad,D.Departamento AS Departmto
,T.Telefono,T.Fax,T.TelMovil,T.SitioWeb,T.e_mail,T.EsVendedor,T.EsConductor,T.EsEmpleado,T.EsOperario,T.IdSector AS CodSector,SectorEco
,T.IdProf AS CodProf,Profesion,T.IdRegimen AS IdRegmen,Regimen,T.IdLugarCed,LC.Localidad AS LugarCedula,T.FecExpCed
,T.Observacion,T.IdEstOper AS CdEstOper,ET.Estado,ET.NColor,T.FechaAdd AS FechaCrea,T.FechaUpdate,T.FechaNac,T.IdLocUbic,LU.Localidad AS CiudadUbic,T.DescUbicac
,TM.TipoOrd,TM.NumOrden,TM.IdCiaOrd,TM.Remesa,TM.IdCiaRem,R.Fecha AS FecOrden,R.IdCliente,C.RazonSocial AS NomCliente,IdLocOrigen,CO.Localidad AS CiuOrigen,IdLocDestino,CD.Localidad AS CiuDestino
,TM.IdVehiculo,TM.nRemolque,R.IdConductor AS CedConductor,NC.RazonSocial AS Conductor
 FROM Terceros AS T INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN SectoresEco AS S ON T.IdSector=S.IdSector INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen
 INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 LEFT JOIN EstadoOper AS ET ON T.IdEstOper=ET.IdEstado LEFT JOIN Localidades AS LU ON T.IdLocUbic=LU.IdLocal
 LEFT JOIN (SELECT IdOperario,IdVehiculo,nRemolque,TipoOrd,NumOrden,IdCiaOrd,Remesa,IdCiaRem FROM tm_TraInfOper WHERE IdEst=@pmIdEst AND TipoRep=@pmTipoRep) AS TM ON T.IdTercero=TM.IdOperario
 LEFT JOIN (SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdCliente,IdAgencia,IdLocOrigen,IdLocDestino,IdVehiculo,IdConductor,nRemolque
 FROM Trn_TraRemesa WHERE TipDoc='OCT' AND Fecha>=@pmFecInicio
 UNION ALL SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdCliente,IdAgencia,IdDestino,IdDestino AS CiuDstno,IdVehiculo,IdConductor,nRemolque
 FROM Trn_TraOrdenServ WHERE TipDoc='OSA' AND Fecha>=@pmFecInicio) AS R ON TM.TipoOrd=R.TipDoc AND TM.NumOrden=R.NumOrden AND TM.IdCiaOrd=R.IdCia
 LEFT JOIN Terceros AS C ON R.IdCliente=C.IdTercero LEFT JOIN Localidades AS CO ON R.IdLocOrigen=CO.IdLocal LEFT JOIN Localidades AS CD ON R.IdLocDestino=CD.IdLocal
 LEFT JOIN Terceros AS NC ON R.IdConductor=NC.IdTercero
WHERE T.Inactivo=0 AND (@pmIdEstOper IS NULL OR T.IdEstOper=@pmIdEstOper)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercerosDso] @pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdTercero,Dv,TipoId,RazonSocial,T.Codigo AS Codtercero,NomCial,SiglaRaz,T.Direccion AS Dreccion,T.IdLocal AS CodCiud,L.Localidad AS Ciudad,D.Departamento AS Departmto
,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,TipEnte
,T.IdSector AS CodSector,SectorEco,T.IdProf AS CodProf,Profesion,T.IdRegimen AS IdRegmen,Regimen,IdLugarCed,LC.Localidad AS LugarCedula,DC.Departamento AS DptoLugcedula,FecExpCed
,Observacion,T.IdEstado AS IdEstdo,ET.Estado,T.Inactivo AS Inactvo,FechaAdd,FechaUpdate,T.IdUsuario AS IdUsario,Usuario,IniStgNom,ET.NColor,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac
,T.IdEstOper AS CdEstOper,EO.Estado AS EstOper,IdLocUbic,DescUbicac
 FROM Terceros AS T INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN SectoresEco AS S ON T.IdSector=S.IdSector 
 INNER JOIN RegimenDian AS R ON T.IdRegimen=R.IdRegimen
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado
 INNER JOIN adm_Usuarios AS U ON T.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal INNER JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf LEFT JOIN EstadoOper AS EO ON T.IdEstOper=EO.IdEstado
 WHERE (@pmIdEstado IS NULL OR T.IdEstado=@pmIdEstado)
ORDER BY RazonSocial
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Liquida] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16),@pmtmIdConcepto VARCHAR(4),@pmtmDetalle VARCHAR(100),@pmtmFecUltLiq SMALLDATETIME,@pmtmFecInicial SMALLDATETIME,@pmtmFecFinal SMALLDATETIME,@pmtmVrBase MONEY,@pmtmCantidad INT,@pmtmVrLiquida MONEY,@pmtmVrDeducido MONEY,@pmtmFecIniPmd SMALLDATETIME,@pmtmFecFinPmd SMALLDATETIME,@pmtmDiasBase INT
,@pmtmDiasNoRem INT,@pmtmTarifa DECIMAL(10,4),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmLogPmdio VARCHAR(600),@pmtmNContrato INT,@pmtmpVehiculo VARCHAR(10),@pmtmIdDep VARCHAR(4),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmIdNom VARCHAR(4),@pmtmVrSalario MONEY,@pmtmVrParcial MONEY,@pmtmNitTercero VARCHAR(16)
 AS 
 INSERT INTO tm_Liquida (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmDetalle,tmFecUltLiq,tmFecInicial,tmFecFinal,tmVrBase,tmCantidad,tmVrLiquida,tmVrDeducido,tmFecIniPmd,tmFecFinPmd,tmDiasBase,tmDiasNoRem,tmTarifa,tmNPrestamo,tmNCuota,tmLogPmdio,tmNContrato,tmpVehiculo,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVrSalario,tmVrParcial,tmNitTercero)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdEmpleado,@pmtmIdConcepto,@pmtmDetalle,@pmtmFecUltLiq,@pmtmFecInicial,@pmtmFecFinal,@pmtmVrBase,@pmtmCantidad,@pmtmVrLiquida,@pmtmVrDeducido,@pmtmFecIniPmd,@pmtmFecFinPmd,@pmtmDiasBase,@pmtmDiasNoRem,@pmtmTarifa,@pmtmNPrestamo,@pmtmNCuota,@pmtmLogPmdio
,@pmtmNContrato,@pmtmpVehiculo,@pmtmIdDep,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmIdNom,@pmtmVrSalario,@pmtmVrParcial,@pmtmNitTercero)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUptm_Liquida] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16),@pmtmIdConcepto VARCHAR(4),@pmtmDetalle VARCHAR(100),@pmtmFecUltLiq SMALLDATETIME,@pmtmFecInicial SMALLDATETIME,@pmtmFecFinal SMALLDATETIME,@pmtmVrBase MONEY,@pmtmCantidad INT,@pmtmVrLiquida MONEY
,@pmtmVrDeducido MONEY,@pmtmFecIniPmd SMALLDATETIME,@pmtmFecFinPmd SMALLDATETIME,@pmtmDiasBase INT,@pmtmDiasNoRem INT,@pmtmTarifa DECIMAL(10,4),@pmtmNPrestamo INT,@pmtmNCuota INT,@pmtmLogPmdio VARCHAR(600),@pmtmNContrato INT,@pmtmpVehiculo VARCHAR(10),@pmtmIdDep VARCHAR(4),@pmtmIdCCosto VARCHAR(16)
,@pmtmIdSubCos VARCHAR(16),@pmtmIdNom VARCHAR(4),@pmtmVrSalario MONEY,@pmtmVrParcial MONEY,@pmtmNitTercero VARCHAR(16)
 AS 
 UPDATE tm_Liquida SET tmIdEmpleado=@pmtmIdEmpleado,tmIdConcepto=@pmtmIdConcepto,tmDetalle=@pmtmDetalle,tmFecUltLiq=@pmtmFecUltLiq,tmFecInicial=@pmtmFecInicial,tmFecFinal=@pmtmFecFinal,tmVrBase=@pmtmVrBase,tmCantidad=@pmtmCantidad,tmVrLiquida=@pmtmVrLiquida,tmVrDeducido=@pmtmVrDeducido,tmFecIniPmd=@pmtmFecIniPmd
,tmFecFinPmd=@pmtmFecFinPmd,tmDiasBase=@pmtmDiasBase,tmDiasNoRem=@pmtmDiasNoRem,tmTarifa=@pmtmTarifa,tmNPrestamo=@pmtmNPrestamo,tmNCuota=@pmtmNCuota,tmLogPmdio=@pmtmLogPmdio,tmNContrato=@pmtmNContrato,tmpVehiculo=@pmtmpVehiculo,tmIdDep=@pmtmIdDep,tmIdCCosto=@pmtmIdCCosto,tmIdSubCos=@pmtmIdSubCos
,tmIdNom=@pmtmIdNom,tmVrSalario=@pmtmVrSalario,tmVrParcial=@pmtmVrParcial,tmNitTercero=@pmtmNitTercero
 WHERE tmNumero=@pmtmNumero AND tmItem=@pmtmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_Liquida] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmDetalle,tmFecUltLiq,tmFecInicial,tmFecFinal,tmVrBase,tmCantidad,tmVrLiquida,tmVrDeducido,tmFecIniPmd,tmFecFinPmd
,tmDiasBase,tmDiasNoRem,tmTarifa,tmNPrestamo,tmNCuota,tmLogPmdio,tmNContrato,tmpVehiculo,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVrSalario,tmVrParcial,tmNitTercero
 FROM tm_Liquida
 WHERE tmNumero=@pmtmNumero 
 AND (tmItem>=ISNULL(@pmtmItem,-1)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Liquida_Sel] @pmTipoLiq VARCHAR(3),@pmNumero INT,@pmtmNumero VARCHAR(5),@pmIdEmpleado VARCHAR(16)=Null
 AS 
 INSERT INTO tm_Liquida (tmNumero,tmItem,tmIdEmpleado,tmIdConcepto,tmDetalle,tmFecUltLiq,tmFecInicial,tmFecFinal,tmVrBase,tmCantidad,tmVrLiquida,tmVrDeducido,tmFecIniPmd,tmFecFinPmd
,tmDiasBase,tmDiasNoRem,tmTarifa,tmNPrestamo,tmNCuota,tmLogPmdio,tmNContrato,tmpVehiculo,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVrSalario,tmVrParcial,tmNitTercero)
 SELECT @pmtmNumero,Item,IdEmpleado,IdConcepto,Detalle,FecUltLiq,FecInicial,FecFinal,VrBase,Cantidad,VrLiquida,VrDeducido,FecIniPmd,FecFinPmd
 ,DiasBase,DiasNoRem,Tarifa,NPrestamo,NCuota,LogPmdio,NContrato,pVehiculo,IdDep,IdCCosto,IdSubCos,IdNom,VrSalario,VrParcial,NitTercero
 FROM Trn_NomLiqDet
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero AND IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_LiquidaLta] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmNumero,tmItem,tmIdEmpleado,Apellidos,Nombres,tmIdConcepto,tmDetalle,tmFecUltLiq,tmFecInicial,tmFecFinal,tmVrBase,tmCantidad,tmVrLiquida,tmVrDeducido,tmFecIniPmd,tmFecFinPmd
,tmDiasBase,tmDiasNoRem,tmTarifa,tmNPrestamo,tmNCuota,tmLogPmdio,tmNContrato,tmpVehiculo,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVrSalario,EsCondtor,pVehiculo,tmVrParcial,tmNitTercero
 FROM  tm_Liquida AS L INNER JOIN Empleados AS E ON L.tmIdEmpleado=E.IdEmpleado
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmIdEmpleado,tmIdConcepto,tmItem
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,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 OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTercerosBus] @pmCodigo VARCHAR(16)=Null,@pmTelefono VARCHAR(20)=Null
 AS 
--consultar por codigo o por telefono
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdProf,IdSector,IdRegimen,IdLugarCed,TipEnte,FecExpCed
,Observacion,IniStgNom,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE Codigo  LIKE ISNULL(@pmCodigo,'%') AND Telefono LIKE ISNULL(@pmTelefono,'%')
 ORDER BY RazonSocial
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsTerceros_Sel] @pmIdTercero VARCHAR(16),@pmNewTercero VARCHAR(16),@pmNewCodigo VARCHAR(16)
 AS 
 INSERT INTO Terceros (IdTercero,RazonSocial,Codigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac)
 SELECT @pmNewTercero,RazonSocial,@pmNewCodigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac
 FROM  Terceros 
 WHERE IdTercero=@pmIdTercero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTercerosCod] @pmIdTercero VARCHAR(16),@pmCodigo VARCHAR(16)
 AS 
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen,IdLugarCed,FecExpCed,TipEnte,IdProf
,Observacion,IniStgNom,IdEstado,Inactivo,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE IdTercero=@pmIdTercero OR Codigo=@pmCodigo
 ORDER BY  IdTercero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTerceros] @pmIdTercero VARCHAR(16),@pmRazonSocial VARCHAR(250),@pmCodigo VARCHAR(16),@pmTipoId CHAR(1),@pmDv CHAR(1),@pmDireccion VARCHAR(250),@pmIdLocal VARCHAR(8)
,@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pmTelMovil VARCHAR(20),@pmSitioWeb VARCHAR(100),@pme_mail VARCHAR(100),@pmEsCliente BIT,@pmEsVendedor BIT,@pmEsConductor BIT
,@pmEsPropietario BIT,@pmEsProveedor BIT,@pmEsEmpleado BIT,@pmEsOperario BIT,@pmEsAccnista BIT,@pmEsCiaAseg BIT,@pmIdSector VARCHAR(8),@pmIdRegimen VARCHAR(4),@pmIdLugarCed VARCHAR(8),@pmObservacion VARCHAR(250)
,@pmIniStgNom INT,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNomCial VARCHAR(250),@pmSiglaRaz VARCHAR(50),@pmIdProf VARCHAR(4),@pmTipEnte CHAR(1),@pmFecExpCed SMALLDATETIME,@pmEsCliePres BIT,@pmImgFoto VARCHAR(30),@pmImgFirma VARCHAR(30)
,@pmImagenDoc1 VARCHAR(50),@pmImagenDoc2 VARCHAR(50),@pmImagenDoc3 VARCHAR(50),@pmFechaNac SMALLDATETIME,@pmIdEstOper VARCHAR(4),@pmIdLocUbic VARCHAR(8),@pmDescUbicac VARCHAR(150),@pmFechaUpdate SMALLDATETIME
 AS 
 UPDATE Terceros SET RazonSocial=@pmRazonSocial,Codigo=@pmCodigo,TipoId=@pmTipoId,Dv=@pmDv,Direccion=@pmDireccion,IdLocal=@pmIdLocal,Telefono=@pmTelefono,Fax=@pmFax,TelMovil=@pmTelMovil,SitioWeb=@pmSitioWeb,e_mail=@pme_mail
,EsCliente=@pmEsCliente,EsVendedor=@pmEsVendedor,EsConductor=@pmEsConductor,EsPropietario=@pmEsPropietario,EsProveedor=@pmEsProveedor,EsEmpleado=@pmEsEmpleado,EsOperario=@pmEsOperario,EsAccnista=@pmEsAccnista,EsCiaAseg=@pmEsCiaAseg 
,EsCliePres=@pmEsCliePres,IdSector=@pmIdSector,IdRegimen=@pmIdRegimen,IdLugarCed=@pmIdLugarCed,Observacion=@pmObservacion,IniStgNom=@pmIniStgNom,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,FechaUpdate=@pmFechaUpdate
,NomCial=@pmNomCial,SiglaRaz=@pmSiglaRaz,IdProf=@pmIdProf,TipEnte=@pmTipEnte,FecExpCed=@pmFecExpCed,ImgFoto=@pmImgFoto,ImgFirma=@pmImgFirma,ImagenDoc1=@pmImagenDoc1,ImagenDoc2=@pmImagenDoc2,ImagenDoc3=@pmImagenDoc3,FechaNac=@pmFechaNac
,IdEstOper=@pmIdEstOper,IdLocUbic=@pmIdLocUbic,DescUbicac=@pmDescUbicac
 WHERE IdTercero=@pmIdTercero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTerceros] @pmIdTercero VARCHAR(16),@pmRazonSocial VARCHAR(250),@pmCodigo VARCHAR(16),@pmTipoId CHAR(1),@pmDv CHAR(1),@pmDireccion VARCHAR(250),@pmIdLocal VARCHAR(8)
,@pmTelefono VARCHAR(20),@pmFax VARCHAR(20),@pmTelMovil VARCHAR(20),@pmSitioWeb VARCHAR(100),@pme_mail VARCHAR(100),@pmEsCliente BIT,@pmEsVendedor BIT,@pmEsConductor BIT
,@pmEsPropietario BIT,@pmEsProveedor BIT,@pmEsEmpleado BIT,@pmEsOperario BIT,@pmEsAccnista BIT,@pmEsCiaAseg BIT,@pmIdSector VARCHAR(8),@pmIdRegimen VARCHAR(4),@pmIdLugarCed VARCHAR(8),@pmObservacion VARCHAR(250)
,@pmIniStgNom INT,@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmNomCial VARCHAR(250),@pmSiglaRaz VARCHAR(50),@pmIdProf VARCHAR(4),@pmTipEnte CHAR(1),@pmFecExpCed SMALLDATETIME,@pmEsCliePres BIT,@pmImgFoto VARCHAR(30),@pmImgFirma VARCHAR(30)
,@pmImagenDoc1 VARCHAR(50),@pmImagenDoc2 VARCHAR(50),@pmImagenDoc3 VARCHAR(50),@pmFechaNac SMALLDATETIME,@pmIdEstOper VARCHAR(4),@pmIdLocUbic VARCHAR(8),@pmDescUbicac VARCHAR(150),@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Terceros (IdTercero,RazonSocial,Codigo,TipoId,Dv,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdRegimen
,IdLugarCed,Observacion,IniStgNom,IdEstado,Inactivo,FechaAdd,IdUsuario,NomCial,SiglaRaz,IdProf,TipEnte,FecExpCed,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac)
 VALUES (@pmIdTercero,@pmRazonSocial,@pmCodigo,@pmTipoId,@pmDv,@pmDireccion,@pmIdLocal,@pmTelefono,@pmFax,@pmTelMovil,@pmSitioWeb,@pme_mail,@pmEsCliente,@pmEsVendedor,@pmEsConductor,@pmEsPropietario,@pmEsProveedor
,@pmEsEmpleado,@pmEsOperario,@pmEsAccnista,@pmEsCiaAseg,@pmEsCliePres,@pmIdSector,@pmIdRegimen,@pmIdLugarCed,@pmObservacion,@pmIniStgNom,@pmIdEstado,@pmInactivo,@pmFechaAdd,@pmIdUsuario
,@pmNomCial,@pmSiglaRaz,@pmIdProf,@pmTipEnte,@pmFecExpCed,@pmImgFoto,@pmImgFirma,@pmImagenDoc1,@pmImagenDoc2,@pmImagenDoc3,@pmFechaNac,@pmIdEstOper,@pmIdLocUbic,@pmDescUbicac)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTerceros] @pmIdTercero VARCHAR(16)
 AS 
 SELECT IdTercero,RazonSocial,Codigo,TipoId,Dv,NomCial,SiglaRaz,Direccion,IdLocal,Telefono,Fax,TelMovil,SitioWeb,e_mail,EsCliente,EsVendedor,EsConductor
,EsPropietario,EsProveedor,EsEmpleado,EsOperario,EsAccnista,EsCiaAseg,EsCliePres,IdSector,IdProf,IdRegimen,TipEnte,IdLugarCed,FecExpCed
,Observacion,IniStgNom,IdEstado,Inactivo,ImgFoto,ImgFirma,ImagenDoc1,ImagenDoc2,ImagenDoc3,FechaNac,IdEstOper,IdLocUbic,DescUbicac,FechaAdd,FechaUpdate,IdUsuario
 FROM Terceros
 WHERE IdTercero=@pmIdTercero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomLiqDet] @pmTipoLiq VARCHAR(3),@pmNumero INT,@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(100),@pmFecUltLiq SMALLDATETIME,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmVrBase MONEY,@pmCantidad INT
,@pmVrLiquida MONEY,@pmVrDeducido MONEY,@pmFecIniPmd SMALLDATETIME,@pmFecFinPmd SMALLDATETIME,@pmDiasBase INT,@pmDiasNoRem INT,@pmTarifa DECIMAL(10,4),@pmNPrestamo INT,@pmNCuota INT,@pmLogPmdio VARCHAR(600),@pmIdEmpleado VARCHAR(16),@pmNContrato INT
,@pmpVehiculo VARCHAR(10),@pmIdDep VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrParcial MONEY,@pmNitTercero VARCHAR(16)
 AS 
 INSERT INTO Trn_NomLiqDet (TipoLiq,Numero,Item,IdConcepto,Detalle,FecUltLiq,FecInicial,FecFinal,VrBase,Cantidad,VrLiquida,VrDeducido,FecIniPmd,FecFinPmd,DiasBase,DiasNoRem,Tarifa,NPrestamo,NCuota,LogPmdio,IdEmpleado,NContrato,pVehiculo,IdDep,IdCCosto,IdSubCos,IdNom,VrSalario,VrParcial,NitTercero)
 VALUES (@pmTipoLiq,@pmNumero,@pmItem,@pmIdConcepto,@pmDetalle,@pmFecUltLiq,@pmFecInicial,@pmFecFinal,@pmVrBase,@pmCantidad,@pmVrLiquida,@pmVrDeducido,@pmFecIniPmd,@pmFecFinPmd,@pmDiasBase,@pmDiasNoRem,@pmTarifa,@pmNPrestamo,@pmNCuota,@pmLogPmdio
,@pmIdEmpleado,@pmNContrato,@pmpVehiculo,@pmIdDep,@pmIdCCosto,@pmIdSubCos,@pmIdNom,@pmVrSalario,@pmVrParcial,@pmNitTercero)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomLiqDet] @pmTipoLiq VARCHAR(3),@pmNumero INT,@pmItem INT
 AS 
 SELECT TipoLiq,Numero,Item,IdConcepto,Detalle,FecUltLiq,FecInicial,FecFinal,VrBase,Cantidad,VrLiquida,VrDeducido,FecIniPmd
,FecFinPmd,DiasBase,DiasNoRem,Tarifa,NPrestamo,NCuota,LogPmdio,IdEmpleado,NContrato,pVehiculo,IdDep
,IdCCosto,IdSubCos,IdNom,VrSalario,VrParcial,NitTercero
 FROM Trn_NomLiqDet
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero
 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].[paQryReqDetalle] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,CdProducto,Descripcion,Cantidad,IdUnd,IdSubgrupo,VrUnitario
,TipOrd,NumOrden,IdCiaOrd,TipSal,NSalida,IdCiaSal,FechaSal,CdBodega,Referencia
 FROM Trn_ReqDetalle
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
 ORDER BY  Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionDet] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
 AS 
 SELECT Fecha,IdRespons,IdCCosto,IdSubCos,IdDep,NContrato,IdCiaCont,NitCliente,CdAgencia,Modalidad
,DirEntrega,IdLocEnt,NumAprob,FecAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev,Observacion,IdEstado
,Item,CdProducto,Descripcion,IdSubgrupo,CdBodega,D.Cantidad AS CantArt,IdUnd,VrUnitario,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
 WHERE R.TipDoc=@pmTipDoc AND R.Requisicion=@pmRequisicion AND R.IdCia=@pmIdCia
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsReqDetalle] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2),@pmItem INT,@pmCdProducto VARCHAR(16),@pmDescripcion VARCHAR(250),@pmIdSubgrupo VARCHAR(8)
,@pmCdBodega VARCHAR(4),@pmCantidad DECIMAL(14,4),@pmIdUnd VARCHAR(4),@pmVrUnitario MONEY,@pmTipOrd VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrd CHAR(2)
,@pmTipSal VARCHAR(3),@pmNSalida INT,@pmIdCiaSal CHAR(2),@pmFechaSal SMALLDATETIME,@pmReferencia VARCHAR(250)
 AS 
 INSERT INTO Trn_ReqDetalle (TipDoc,Requisicion,IdCia,Item,CdProducto,Descripcion,IdSubgrupo,CdBodega,Cantidad,IdUnd,VrUnitario,TipOrd,NumOrden,IdCiaOrd,TipSal,NSalida,IdCiaSal,FechaSal,Referencia)
 VALUES (@pmTipDoc,@pmRequisicion,@pmIdCia,@pmItem,@pmCdProducto,@pmDescripcion,@pmIdSubgrupo,@pmCdBodega,@pmCantidad,@pmIdUnd,@pmVrUnitario,@pmTipOrd
,@pmNumOrden,@pmIdCiaOrd,@pmTipSal,@pmNSalida,@pmIdCiaSal,@pmFechaSal,@pmReferencia)
GO