/****** Objeto:  StoredProcedure [dbo].[paQryNomDetalleRes]    Fecha de la secuencia de comandos: 09/26/2015 10:21:37 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraOrdenLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMttoInspeccionesLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMttoInspeccionesLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomDetalleRes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomDetalleRes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiq]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraOrdenLiqRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraOrdenLiqRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTraOrdenLiq]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTraOrdenLiq]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetalleRes] @pmFecIni SMALLDATETIME,@pmFecFin SMALLDATETIME,@pmIdPeriodo VARCHAR(8)=Null
,@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdArea VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmIdNom VARCHAR(4)=Null
,@pmIdInstala VARCHAR(4)=Null,@pmIdCCosto  VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdTipCon VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
,@pmInactivo BIT=Null,@pmEmp_Inactivo BIT=Null,@pmIdConcepto VARCHAR(4)=Null,@pmClaseLiq VARCHAR(10)=Null,@pmClaseCon VARCHAR(10)=Null,@pmNPrestamo INT=Null
 AS 
 SELECT N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumCntrato,N.KeyRegistro AS KeyReg,N.IdPeriodo AS NumPeriodo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdNom AS CodNmna,TipoNomina,N.IdDep AS CodDep,Dependencia,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcos,SubCosto,N.IdInstala AS CodIstala,Instlacion
,N.VrSalario AS SalBasico,N.pVehiculo AS PlacaVeh,TipCom,TipoCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS IdEstdo,ED.Estado AS Nom_Estado,EsRetiro,NLiqRet,TipoLiq,TimeSys
,N.FecUpdate AS Fec_Update,N.IdUsuario AS IdUsuari,Usuario
--datos del detalle
,Item,D.IdConcepto AS CodConcept,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,ClaseCon,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS Tarfa,DiasCalc,DiasNov,CodFondo,FCO.Fondo AS NomFondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq
--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,FC.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.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,C.VrSalario AS VlrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,ATFinMes,SegFinMes,DchDotacion,DchCafeteria
,C.IdEstado AS Con_IdEstdo,ECO.Estado AS Con_Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj,C.IdGrupo AS CodGrup
 FROM Trn_Nomina AS N INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala  INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 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 FC ON E.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar  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 ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado 
 INNER JOIN  Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND  N.KeyRegistro=D.KeyRegistro
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto
 LEFT JOIN TiposCom AS TCM ON N.TipCom=TCM.IdCom  LEFT JOIN Fondos AS FCO ON D.CodFondo=FCO.IdFondo
 WHERE FecInicial>=@pmFecIni  AND FecFinal<=@pmFecFin AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')  AND  N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND N.IdNom LIKE ISNULL(@pmIdNom,'%') AND N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND N.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND N.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND ClaseLiq LIKE ISNULL(@pmClaseLiq,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (N.NContrato>=ISNULL(@pmNContrato,0)  AND N.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-2)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (NPrestamo>=ISNULL(@pmNPrestamo,0)  AND NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
--Descuentos en vacaciones
UNION ALL
 SELECT V.IdEmpleado,Apellidos,Nombres,V.NContrato,V.IdEmpleado,D.IdPeriodo,V.FecInicial,V.FecFinal,V.Cantidad,0,V.VrLiquida,V.VrTotDed
,C.IdNom,TipoNomina,C.IdDep,Dependencia,C.IdCCosto,CCosto,C.IdSubCos,SubCosto,C.IdInstala,Instlacion,V.VrSalario,V.pVehiculo,V.TipCom,TipoCom,V.Comprobante,V.IdCiaCom
,V.Observacion,V.IdEstado,ED.Estado,0,V.Numero,C.TipoLiquida,V.TimeSys,V.FecUpdate,V.IdUsuario,Usuario
,D.Item,D.IdConcepto,Concepto,D.Detalle,0,CASE D.IdConcepto WHEN 'DCP' THEN 0 WHEN 'DCS' THEN 0 WHEN 'FSP' THEN 0 WHEN 'FSU' THEN 0 ELSE D.Cantidad END AS CantDed,0,D.VrDevgado,D.VrDeducido,ClaseCon,'%','NOMINA',D.VrBaseLiq,D.Tarifa
,CASE D.IdConcepto WHEN 'DCP' THEN 0 WHEN 'DCS' THEN 0 WHEN 'FSP' THEN 0 WHEN 'FSU' THEN 0 ELSE D.Cantidad END AS DiasCalc,0,'0','',D.NPrestamo,D.NCuota,'VAC',D.VrBaseLiq
,E.Codigo,E.IdLugarCed,Localidad,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf,Profesion,E.IdClase,ClaseCuenta,NumCuenta,E.IdBanco,Banco,IdFonPen,FP.Fondo
,IdFonEps,FS.Fondo,IdFonArp,FR.Fondo,IdCajaCom,FC.Fondo,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo
,C.IdTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo,Cargo,C.IdArea,Area,C.IdClase,ClaseRiesgo,CR.Tarifa,C.VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina
,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado,ECO.Estado,C.Inactivo,C.IdJornada,Jornada,TipoTrabj,C.IdGrupo
 FROM Trn_NomVac AS V INNER JOIN Trn_NomVacDet AS D ON V.IdEmpleado=D.IdEmpleado AND V.Numero=D.Numero
 INNER JOIN Empleados AS E ON V.IdEmpleado=E.IdEmpleado INNER JOIN Emp_Contrato AS C ON V.NContrato=C.NContrato
 INNER JOIN adm_Usuarios AS U ON V.IdUsuario=U.IdUsuario
 INNER JOIN NomConceptos AS NC ON D.IdConcepto=NC.IdConcepto INNER JOIN TiposNom AS TN ON C.IdNom=TN.IdNom INNER JOIN Dependencias AS DPD ON C.IdDep=DPD.IdDep
 INNER JOIN CentroCosto AS CC ON C.IdCCosto=CC.IdCCosto INNER JOIN Instalaciones AS I ON C.IdInstala=I.IdInstala
 INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
 INNER JOIN Areas AS AR ON C.IdArea=AR.IdArea INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo
 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 FC ON E.IdCajaCom=FC.IdFondo 
 INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 INNER JOIN ClaseRie AS CR ON C.IdClase=CR.IdClase INNER JOIN Jornadas AS J ON C.IdJornada=J.IdJornada
 INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado INNER JOIN EstadoDoc AS ED ON V.IdEstado=ED.IdEstado
 LEFT JOIN SubCentros AS SC ON C.IdSubCos=SC.IdSubCos LEFT JOIN TiposCom AS TCM ON V.TipCom=TCM.IdCom
 WHERE V.Fecha BETWEEN @pmFecIni AND @pmFecFin AND V.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND D.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')
 AND C.IdNom LIKE ISNULL(@pmIdNom,'%') AND C.IdDep LIKE ISNULL(@pmIdDep,'%') AND C.IdInstala LIKE ISNULL(@pmIdInstala,'%')
 AND C.IdCCosto LIKE ISNULL(@pmIdCCosto,'%') AND V.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 AND C.IdTipCon LIKE ISNULL(@pmIdTipCon,'%') AND D.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND ClaseCon LIKE ISNULL(@pmClaseCon,'%')
 AND (V.NContrato>=ISNULL(@pmNContrato,0)  AND V.NContrato<=ISNULL(@pmNContrato,2147483647))
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (E.Inactivo=ISNULL(@pmEmp_Inactivo,0) or E.Inactivo=ISNULL(@pmEmp_Inactivo,1))
 AND (D.NPrestamo>=ISNULL(@pmNPrestamo,0)  AND D.NPrestamo<=ISNULL(@pmNPrestamo,2147483647))
GO
/****** Objeto:  StoredProcedure [dbo].[paQryTraOrdenLiqRel]    Fecha de la secuencia de comandos: 09/26/2015 10:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT L.TipDoc AS IdTipDoc,TipoDoc,L.Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.Modalidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.Cantidad AS CantOrden,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.VrTotal,L.VrCosto,L.VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Sys_TiposDoc AS TD ON L.TipDoc=TD.IdDoc INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
/****** Objeto:  StoredProcedure [dbo].[paQryTraOrdenLiqFmt]    Fecha de la secuencia de comandos: 09/26/2015 10:21:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqFmt] @pmTipDoc VARCHAR(3),@pmLiquidacionIni INT,@pmLiquidacionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT L.TipDoc AS IdTipDoc,TipoDoc,L.Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia,O.IdDestino,LD.Localidad AS NomDestino,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo
,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem,L.VrTotal,L.VrCosto,L.VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca
,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,L.Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,L.Referencia,L.CxPagar,CdPlazo,Plazo,DiasPago,FechaVence,L.UndMed,Unidad,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario,Leyenda
--vehiuculo
,T.TipoId AS TercTipId,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LT.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,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,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 INNER JOIN Sys_TiposDoc AS TD ON L.TipDoc=TD.IdDoc INNER JOIN Localidades AS LT ON T.IdLocal=LT.IdLocal
 INNER JOIN Vehiculos AS V ON L.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 MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Sys_Um AS UM ON L.UndMed=UM.UndMed
 LEFT JOIN Plazos AS PZ ON L.CdPlazo=PZ.IdPlazo
 WHERE L.TipDoc=@pmTipDoc AND L.Liquidacion BETWEEN @pmLiquidacionIni AND @pmLiquidacionFin AND L.IdCia=@pmIdCia
GO
/****** Objeto:  StoredProcedure [dbo].[paQryMttoInspeccionesLta]    Fecha de la secuencia de comandos: 09/26/2015 10:21:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMttoInspeccionesLta] @pmIdSistema VARCHAR(4)
 AS 
 SELECT I.IdInspec,Inspeccion,I.IdSistema AS CdSistema,Sistema,I.IdMtto AS CdMtto,TipoMtto,TipoDato,UndMed,I.Inactivo
,Item,Estado,ValorInicial,ValorFinal,NColor,ReqMtto,CdConc,Concepto
 FROM MttoInspecciones AS I INNER JOIN MttoSistemas AS S ON I.IdSistema=S.IdSistema
 INNER JOIN MttoTipos AS TM ON I.IdMtto=TM.IdMtto
 LEFT JOIN MttoInspecRangos AS R ON I.IdInspec=R.IdInspec 
 LEFT JOIN MttoConceptos AS C ON R.CdConc=C.IdConc
 WHERE I.IdSistema LIKE ISNULL(@pmIdSistema,'%')
GO
/****** Objeto:  StoredProcedure [dbo].[paQryTraOrdenLiqLta]    Fecha de la secuencia de comandos: 09/26/2015 10:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiqLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT Liquidacion,L.IdCia AS CdCia,Compania,L.Fecha AS FechaLiq,L.IdConcepto AS CdConcepto,Concepto,TipOds,L.NumOrden,IdCiaOds,O.Fecha AS FecOrden
,L.IdVehiculo AS PlacaVeh,L.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,L.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,L.TipoAfiVehic
,O.IdCliente,T.RazonSocial AS NomCliente,O.IdDestino,Localidad,O.TipoOrden,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,TipoResiduo,O.RemCliente,O.NumRemesa,O.CdCiaRem
,L.VrTotal,L.VrCosto,L.VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,L.Cantidad
,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,Referencia,CxPagar,CdPlazo,FechaVence,UndMed,L.TipCom,L.Comprobante,L.IdCiaCom,L.Anulado,L.NumDev,L.FecDev,L.Observacion AS Observ
,L.IdEstado AS CdEstado,Estado,TipEgr,Egreso,IdCiaEgr,NumCheque,L.OrigenAdd,L.TimeSys AS FechaCrea,L.FecUpdate AS FechaModif,L.IdCiaCrea AS CdCiaCrea,L.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraOrdenLiq AS L INNER JOIN Companias AS CN ON L.IdCia=CN.IdCia INNER JOIN Conceptos AS CT ON L.IdConcepto=CT.IdConcepto
 INNER JOIN adm_Usuarios AS U ON L.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS ED ON L.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS NP ON L.IdPoseedor=NP.IdTercero INNER JOIN Terceros AS CDT ON L.IdConductor=CDT.IdTercero
 INNER JOIN Trn_TraOrdenServ AS O ON L.TipOds=O.TipDoc AND L.NumOrden=O.NumOrden AND L.IdCiaOds=O.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion 
 LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 WHERE L.TipDoc=@pmTipDoc AND L.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND L.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
/****** Objeto:  StoredProcedure [dbo].[paInsTraOrdenLiq]    Fecha de la secuencia de comandos: 09/26/2015 10:21:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmTipoAfiVehic VARCHAR(10),@pmVrTotal MONEY,@pmVrCosto MONEY
,@pmVrAnticipo MONEY,@pmVrGastos MONEY,@pmVrIva MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY,@pmVrReteCREE MONEY,@pmVrReteIva MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmTarifaPago MONEY,@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmTarifaImp DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4)
,@pmTarifaIca DECIMAL(16,6),@pmTarifaRiv DECIMAL(14,4),@pmTarifaRtc DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCodTarRtc VARCHAR(4),@pmReferencia VARCHAR(50),@pmCxPagar BIT,@pmCdPlazo VARCHAR(4),@pmFechaVence SMALLDATETIME,@pmUndMed VARCHAR(10)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_TraOrdenLiq (TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,IdVehiculo,IdPoseedor,IdConductor,TipoAfiVehic,VrTotal,VrCosto,VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv
,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc,Referencia,CxPagar,CdPlazo,FechaVence,UndMed,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,OrigenAdd,TimeSys,IdCiaCrea,IdUsuario,TipEgr,Egreso,IdCiaEgr,NumCheque)
 VALUES (@pmTipDoc,@pmLiquidacion,@pmIdCia,@pmFecha,@pmIdConcepto,@pmTipOds,@pmNumOrden,@pmIdCiaOds,@pmIdVehiculo,@pmIdPoseedor,@pmIdConductor,@pmTipoAfiVehic,@pmVrTotal,@pmVrCosto,@pmVrAnticipo,@pmVrGastos,@pmVrIva,@pmVrRetencion,@pmVrReteIca,@pmVrReteCREE,@pmVrReteIva,@pmVrOtros,@pmVrDescuento,@pmVrNeto,@pmCantidad,@pmTarifaPago,@pmBaseImp
,@pmBaseRet,@pmBaseIca,@pmTarifaImp,@pmTarifaRet,@pmTarifaIca,@pmTarifaRiv,@pmTarifaRtc,@pmCodTarIva,@pmCodTarRet,@pmCodTarIca,@pmCodTarRiv,@pmCodTarRtc,@pmReferencia,@pmCxPagar,@pmCdPlazo,@pmFechaVence,@pmUndMed,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev,@pmObservacion,@pmIdEstado,@pmOrigenAdd,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipEgr,@pmEgreso,@pmIdCiaEgr,@pmNumCheque)
GO
/****** Objeto:  StoredProcedure [dbo].[paUpTraOrdenLiq]    Fecha de la secuencia de comandos: 09/26/2015 10:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmTipOds VARCHAR(3),@pmNumOrden INT,@pmIdCiaOds CHAR(2),@pmIdVehiculo VARCHAR(10),@pmIdPoseedor VARCHAR(16),@pmIdConductor VARCHAR(16),@pmTipoAfiVehic VARCHAR(10),@pmVrTotal MONEY,@pmVrCosto MONEY
,@pmVrAnticipo MONEY,@pmVrGastos MONEY,@pmVrIva MONEY,@pmVrRetencion MONEY,@pmVrReteIca MONEY,@pmVrReteCREE MONEY,@pmVrReteIva MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmTarifaPago MONEY,@pmBaseImp MONEY,@pmBaseRet MONEY,@pmBaseIca MONEY,@pmTarifaImp DECIMAL(14,4),@pmTarifaRet DECIMAL(14,4)
,@pmTarifaIca DECIMAL(16,6),@pmTarifaRiv DECIMAL(14,4),@pmTarifaRtc DECIMAL(14,4),@pmCodTarIva VARCHAR(4),@pmCodTarRet VARCHAR(4),@pmCodTarIca VARCHAR(4),@pmCodTarRiv VARCHAR(4),@pmCodTarRtc VARCHAR(4),@pmReferencia VARCHAR(50),@pmCxPagar BIT,@pmCdPlazo VARCHAR(4),@pmFechaVence SMALLDATETIME,@pmUndMed VARCHAR(10)
,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(1000),@pmIdEstado VARCHAR(4),@pmTipEgr VARCHAR(3),@pmEgreso INT,@pmIdCiaEgr CHAR(2),@pmNumCheque VARCHAR(20),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_TraOrdenLiq SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,TipOds=@pmTipOds,NumOrden=@pmNumOrden,IdCiaOds=@pmIdCiaOds,IdVehiculo=@pmIdVehiculo,IdPoseedor=@pmIdPoseedor,IdConductor=@pmIdConductor,TipoAfiVehic=@pmTipoAfiVehic,VrTotal=@pmVrTotal,VrCosto=@pmVrCosto,VrAnticipo=@pmVrAnticipo,VrGastos=@pmVrGastos,VrIva=@pmVrIva,VrRetencion=@pmVrRetencion
,VrReteIca=@pmVrReteIca,VrReteCREE=@pmVrReteCREE,VrReteIva=@pmVrReteIva,VrOtros=@pmVrOtros,VrDescuento=@pmVrDescuento,VrNeto=@pmVrNeto,Cantidad=@pmCantidad,TarifaPago=@pmTarifaPago,BaseImp=@pmBaseImp,BaseRet=@pmBaseRet,BaseIca=@pmBaseIca,TarifaImp=@pmTarifaImp,TarifaRet=@pmTarifaRet,TarifaIca=@pmTarifaIca,TarifaRiv=@pmTarifaRiv,TarifaRtc=@pmTarifaRtc
,CodTarIva=@pmCodTarIva,CodTarRet=@pmCodTarRet,CodTarIca=@pmCodTarIca,CodTarRiv=@pmCodTarRiv,CodTarRtc=@pmCodTarRtc,Referencia=@pmReferencia,CxPagar=@pmCxPagar,CdPlazo=@pmCdPlazo,FechaVence=@pmFechaVence,UndMed=@pmUndMed,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev
,Observacion=@pmObservacion,IdEstado=@pmIdEstado,FecUpdate=@pmFecUpdate,TipEgr=@pmTipEgr,Egreso=@pmEgreso,IdCiaEgr=@pmIdCiaEgr,NumCheque=@pmNumCheque
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO
/****** Objeto:  StoredProcedure [dbo].[paQryTraOrdenLiq]    Fecha de la secuencia de comandos: 09/26/2015 10:21:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenLiq] @pmTipDoc VARCHAR(3),@pmLiquidacion INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Liquidacion,IdCia,Fecha,IdConcepto,TipOds,NumOrden,IdCiaOds,IdVehiculo,IdPoseedor,IdConductor,TipoAfiVehic,VrTotal,VrCosto,VrAnticipo,VrGastos,VrIva,VrRetencion,VrReteIca,VrReteCREE,VrReteIva,VrOtros,VrDescuento,VrNeto,Cantidad,TarifaPago,BaseImp,BaseRet,BaseIca,TarifaImp,TarifaRet,TarifaIca,TarifaRiv,TarifaRtc,CodTarIva,CodTarRet,CodTarIca,CodTarRiv,CodTarRtc
,Referencia,CxPagar,CdPlazo,FechaVence,UndMed,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TipEgr,Egreso,IdCiaEgr,NumCheque,OrigenAdd,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_TraOrdenLiq
 WHERE TipDoc=@pmTipDoc AND Liquidacion=@pmLiquidacion AND IdCia=@pmIdCia
GO