if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomAlqNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomAlqNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsOpedido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Aportes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Aportes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_Aportes_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_Aportes_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_NiifLib]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_NiifLib]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAcuFisCuentasMay]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAcuFisCuentasMay]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAcuFisCuentasMayCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAcuFisCuentasMayCia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAcuNiifCuentasMay]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAcuNiifCuentasMay]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryAcuNiifCuentasMayCia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryAcuNiifCuentasMayCia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAlqNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAlqNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomAutoliq_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomAutoliq_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedido_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedido_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOpedidoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryOpedidoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryProdMciasLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryProdMciasLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_Aportes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_Aportes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AportesDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AportesDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AportesDsn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AportesDsn]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_AportesDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_AportesDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NiifComIB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_NiifComIB]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NiifLibDia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_NiifLibDia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_NiifLibOfi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_NiifLibOfi]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomAlqNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomAlqNov]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOpedido]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpOpedido]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptm_AportesDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUptm_AportesDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUptm_AportesNov]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUptm_AportesNov]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_Aportes] @pmtmEst VARCHAR(5),@pmtmIdInstala VARCHAR(4)=Null,@pmtmIdDep VARCHAR(4)=Null,@pmtmIdNom VARCHAR(4)=Null
,@pmIdArea VARCHAR(4)=Null
 AS 
 SELECT tmIdEmpleado,Apellidos,Nombres,tmNContto,tmDiasPen,tmBasePen,tmTarfaPen,tmValorPen,tmTarfaFsp,tmValorFsp,tmTarfaFsu,tmValorFsu,tmDiasSal
,tmBaseSal,tmTarfaSal,tmValorSal,tmDiasRie,tmBaseRie,tmTarfaRie,tmValorRie,tmDiasCom,tmBaseCom,tmTarfaCom,tmValorCom,tmTarfaIcb,tmValorIcbf,tmTarfaSen,tmValorSena
,tmTarfaMin,tmValorMin,tmTarfaEsa,tmValorESAP,tmDiasNov,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmNAutInc,tmVrIncap,tmNAutLic,tmVrLicencia,tmNAutIrp,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp
,tmIdFonPen,FP.Fondo AS FondoPen,tmIdFonSal,FS.Fondo AS FondoEps,tmIdFonRie,FR.Fondo AS FondoArp,tmIdFonCaj,CC.Fondo AS CajaComp,tmIdInstala,Instlacion,tmIdDep,Dependencia,tmIdCCosto,CCosto,tmIdSubCos,SubCosto,tmIdNom,TipoNomina,tmVehiculo,tmVrBasico
,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,CRN.ClaseRiesgo AS NewClaseRie,CRN.Tarifa AS NewTarifa,tmCodNewGru,TarfaEmp,TarfaTrab,tmEst,tmItem,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmBaseRef,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal
--Datos empleado
,Codigo,TipoId,Dv,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,EsCondtor,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,Licencia,CatLicencia,VigLicencia
,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,IdEstCivil,EstCivil,NHijos,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,CR.ClaseRiesgo AS ClasRiesgo,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,SalMinimo,SalIntegral,FPnomina,C.TipoLiquida AS TipLiq,FecAfiSeg
,ATFinMes,SegFinMes,DchDotacion,DchCafeteria,C.IdEstado AS Con_IdEstdo,Estado,C.Inactivo AS Con_inactivo,C.IdJornada AS CodJnada,Jornada,TipoTrabj,tmCdFonAvp,CdFonAvp
 FROM tm_Aportes AS A INNER JOIN Empleados AS E ON A.tmIdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON A.tmNContto=C.NContrato
 INNER JOIN Dependencias AS DP ON A.tmIdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON A.tmIdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON A.tmIdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON A.tmIdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON A.tmIdNom=TN.IdNom INNER JOIN Fondos AS FP ON A.tmIdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON A.tmIdFonSal=FS.IdFondo  INNER JOIN Fondos AS FR ON A.tmIdFonRie=FR.IdFondo
 INNER JOIN Fondos AS CC ON A.tmIdFonCaj=CC.IdFondo 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 EstadoCiv AS EC ON E.IdEstCivil=EC.IdEstado
 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  LEFT JOIN ClaseRie AS CRN ON A.tmCodNewTar=CRN.IdClase
 LEFT JOIN GruposAR AS GAR ON A.tmCodNewGru=GAR.IdGrupo
 WHERE tmEst=@pmtmEst AND tmIdInstala LIKE ISNULL(@pmtmIdInstala,'%') AND tmIdDep LIKE ISNULL(@pmtmIdDep,'%')
 AND tmIdNom LIKE ISNULL(@pmtmIdNom,'%') AND C.IdArea LIKE ISNULL(@pmIdArea,'%')
 ORDER BY Apellidos,Nombres,tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_AportesDsn] @pmtmEst VARCHAR(5)
 AS 
 SELECT tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte
,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmCodNewEps,FS.Fondo AS NuevaEps,tmCodNewPen,FP.Fondo AS NuevoFdoPen,tmCodNewIns,Instlacion
,tmCodNewTar,tmCodNewGru,tmDiasNov,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal
 FROM tm_Aportes AS A LEFT JOIN Instalaciones AS I ON A.tmCodNewIns=I.IdInstala
 LEFT JOIN Fondos AS FP ON A.tmCodNewPen=FP.IdFondo LEFT JOIN Fondos AS FS ON A.tmCodNewEps=FS.IdFondo
 WHERE tmEst=@pmtmEst AND tmDiasNov>0
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_AportesDso] @pmtmEst VARCHAR(5)
 AS 
 SELECT tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmVrBasico,tmDiasPen,tmBasePen,tmTarfaPen,tmValorPen,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmValorFsp,tmValorFsu
,tmDiasSal,tmBaseSal,tmTarfaSal,tmValorSal,tmVrUpc,tmNAutInc,tmVrIncap,tmNAutLic,tmVrLicencia,tmDiasRie,tmBaseRie,tmTarfaRie,tmValorRie,tmVrIncIrp,tmNAutIrp
,tmDiasCom,tmBaseCom,tmTarfaCom,tmValorCom,tmTarfaIcb,tmValorIcbf,tmTarfaSen,tmValorSena,tmBaseRef,tmTarfaEsa,tmValorESAP,tmTarfaMin,tmValorMin
,tmTarfaFsp,tmTarfaFsu,tmIdFonPen,FP.Fondo AS FondoPen,tmIdFonSal,FS.Fondo AS FondoEps,tmIdFonRie,FR.Fondo AS FondoArp,tmIdFonCaj,CC.Fondo AS CajaComp
,tmIdInstala,Instlacion,tmNContto,tmIdNom,tmCdFonAvp,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal
 FROM tm_Aportes AS A INNER JOIN Instalaciones AS I ON A.tmIdInstala=I.IdInstala
 INNER JOIN Fondos AS FP ON A.tmIdFonPen=FP.IdFondo INNER JOIN Fondos AS FS ON A.tmIdFonSal=FS.IdFondo
 INNER JOIN Fondos AS FR ON A.tmIdFonRie=FR.IdFondo INNER JOIN Fondos AS CC ON A.tmIdFonCaj=CC.IdFondo
 WHERE tmEst=@pmtmEst
 ORDER BY tmItem
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedido_Cr] @pmTipDoc VARCHAR(3),@pmPedidoIni INT,@pmPedidoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,Pedido,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad
,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocEnv AS IdCiuEnvio,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio,DiasEntraga,LugarEnvio
,O.NitContac AS NitContacto,O.NomContac AS NomContacto,O.TelContac AS TelContacto,O.emlContac AS EmailContacto,CargoContac,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago
,NitEmpTrans,EmpTrans,O.pVehiculo AS PlacaVeh,AsignarVeh,CdConductor,TC.RazonSocial AS Conductor,CdRuta,Ruta,RefPedido,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact
,TipRem,O.Remision AS NumRemision,O.IdCiaRem AS CdCiaRem,FechaRem,NumCotizac,CdCiaCotizac,NumAutoriza,NumAutCupo,NumAutCheq,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera
,TipoTrans,TipoOrden,TipoModifica,NumAutSicom,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS Fech_Add,O.FecUpdate AS Fech_Update,IdCiaCrea,O.IdUsuario AS IdUsuari,Usuario,Leyenda
--Información del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.NomCial AS TercNomCial,T.SiglaRaz AS TercSigal,T.Direccion AS TercDireccion
,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,D.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.Fax AS TercFax,T.e_mail AS TercEmail
,T.SitioWeb AS TercSitioWeb,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,T.TipEnte AS TercTipEnte
--información del cliente
,NitRepLeg,NomRepLeg,CLI.NitContac AS ClieNitContac,CLI.NomContac AS ClieNomContacto,CLI.TelContac AS ClieTelContac,CLI.emlContac AS ClieEmailContac,CargContac,DiasEntga,CLI.IdSzona AS CdSubzona,Subzona,Zona
,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdClase AS CdClaseCta,NumCuenta,CLI.IdBanco AS CdBanco,Banco,ExcIva,LiqFletes,CLI.FactSold AS FactSoldicom,Autoret,VrCupo,VrSaldo,Contrato,CLI.NContrato AS CliNumContrato
,DirAgncia,TelAgncia,FaxAgncia,A.IdLocal AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto,A.Referencia AS AgeReferencia
--Detalles
,Item,K.IdProducto AS CodProducto,DescripProd,K.IdBodega AS CdBodega,Bodega,Descrip AS BodDescrip,CdTanque,Entradas,Salidas,K.IdUnd AS CdUnid,UM.Unidad AS UnidMedida,VrUnitario,VrPrecio,VrCostProm,K.TarifaIva AS KarTarifIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,K.TarifaRet AS KarTarifRet,VrReteEnt,VrReteSal,K.TarifaIca AS KarTarifIca,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,K.IdTercero AS KarNitTercero,CdAgencia,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,NumOrden,IdCiaOrd,Cotizacion,IdCiaCot,K.Remision AS KarRemision,K.IdCiaRem AS CdCiaRem,K.Factura AS KarFactura,TipDocDev,NumDocDev
,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal,OtroImpto,Unidades,ItemCombo,Servcios,NoVentas,EsCombo,EsProdBase,K.ListaPrec AS KarLtaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,S.IdGrupo AS CdGrupo,Grupo,G.IdLinea AS CdLinea,Linea,PM.IdMarca AS CdMarca,Marca,Tamano,PM.UndMed AS CdUndMed
,SU.Unidad AS ProdUnidad,ExtciaMin,ExtciaMax,ExtciaAct,VrCostAnt,VrCosto,VrCostPmd,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong,Precio1,Precio2,Precio3,Precio4,Precio5
 FROM Trn_Opedido AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Pedido=K.Documento AND O.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN  Conceptos AS C ON O.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN  Plazos AS PZ ON O.IdPlazo=PZ.IdPlazo INNER JOIN  Terceros AS VN ON O.IdVend=VN.IdTercero
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia  INNER JOIN Formaspago AS FP ON O.IdForma=FP.IdForma
 INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona 
 INNER JOIN  Zonas AS Z ON SZ.IdZona=Z.IdZona  INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo
 INNER JOIN Bancos AS BC ON CLI.IdBanco=BC.IdBanco
 INNER JOIN Localidades AS LA ON A.IdLocal=LA.IdLocal  INNER JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON PM.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS LN ON G.IdLinea=LN.IdLinea INNER JOIN Marcas AS M ON PM.IdMarca=M.IdMarca
 INNER JOIN Sys_Um AS SU ON PM.UndMed=SU.UndMed 
 LEFT JOIN  Localidades AS LE ON O.IdLocEnv=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Terceros AS TC ON O.CdConductor=TC.IdTercero LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 WHERE O.TipDoc=@pmTipDoc AND Pedido BETWEEN @pmPedidoIni AND @pmPedidoFin AND O.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Pedido,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryProdMciasLta] @pmTipoRef VARCHAR(10)=Null,@pmIdLinea VARCHAR(8)=Null,@pmIdGrupo VARCHAR(8)=Null,@pmIdSubgrupo VARCHAR(8)=Null
,@pmIdMarca VARCHAR(4)=Null,@pmIdBodega VARCHAR(4)=Null,@pmIdProv VARCHAR(16)=Null,@pmIdEstado VARCHAR(4)=Null,@pmSeriales BIT=Null,@pmTanques BIT=Null
 AS 
 SELECT IdProducto,DescripProd,DescripAbrv,CodBarras,Referencia,TipoRef,G.IdLinea AS CdLinea,Linea,S.IdGrupo AS CdGrupo,Grupo,P.IdSubgrupo AS CdSubgpo,Subgrupo,P.IdMarca AS CdMarc,Marca,Color,Tamano,MedAlto,MedAncho,MedLargo,MedVolm,P.UndMed AS UndVolm
,P.IdUnd AS CdUnd,UM.Unidad AS UndadMed,IdUndP,UP.Unidad AS EmpPrim,CdUndS,CdUndE,CdUndEb,P.IdEmp AS CdEmp,Empaque,P.IdNat AS CdNat,Natlzaprod,P.IdMnjo AS CdMnjo,ManejoMcia,P.IdTmcia AS CdTmcia,TipoMcia,P.IdBodega AS CdBodga,Bodega,IdUbic,DesUbic,IdProv,RazonSocial
,GarProv,GarClie,CdDctCom,VrCostAnt,VrCosto,VrCostPmd,IdTarIva,Tarifa,IvaInc,LtPreDef,Precio1,Precio2,Precio3,Precio4,Precio5,CdMon1,CdMon2,CdMon3,CdMon4,CdMon5,BaseMgn,CdMgn1,CdMgn2,CdMgn3,CdMgn4,CdMgn5,CdDct1,CdDct2,CdDct3,CdDct4,CdDct5,P.CdTarIca AS CdTarfIca,P.CdTarRet AS CdTarfRet
,ExtciaMin,ExtciaMax,ExtciaAct,Factor1,Factor2,Factor3,Factor4,Factor5,Seriales,Lotes,Combo,NoAjustes,Tanques,ValesComb,FecUltcom,FecUltVta,CodMcia,DescripLong,Cmntarios,PathFoto,CantTpv,P.IdEstado AS CdEstdo,Estado,P.Inactivo AS Inactvo,TipoZonaFront,ExcluidoImp,P.IdUsuario AS IdUsuari,Usuario,P.FechaAdd AS FecAdd,P.FechaUpdate AS FecUpd
 FROM ProdMcias AS P INNER JOIN SubGrupos AS S ON P.IdSubgrupo=S.IdSubgrupo INNER JOIN Grupos AS G ON S.IdGrupo=G.IdGrupo 
 INNER JOIN Lineas AS L ON G.IdLinea=L.IdLinea INNER JOIN Marcas AS M ON P.IdMarca=M.IdMarca
 INNER JOIN UndMed AS UM ON P.IdUnd=UM.IdUnd  INNER JOIN UndMed AS UP ON P.IdUndP=UP.IdUnd
 INNER JOIN Empaques AS E ON P.IdEmp=E.IdEmp INNER JOIN TiposNat AS N ON P.IdNat=N.IdNat
 INNER JOIN TiposMnjo AS MM ON P.IdMnjo=MM.IdMnjo INNER JOIN TiposMcia AS TM ON P.IdTmcia=TM.IdTmcia
 INNER JOIN Bodegas AS B ON P.IdBodega=B.IdBodega INNER JOIN Terceros AS TP ON P.IdProv=TP.IdTercero
 INNER JOIN Tablapor AS TI ON P.IdTarIva=TI.IdTarifa INNER JOIN EstadoPro AS EP ON P.IdEstado=EP.IdEstado
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario 
 WHERE P.TipoRef LIKE ISNULL(@pmTipoRef,'%') AND G.IdLinea LIKE ISNULL(@pmIdLinea,'%') AND S.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND P.IdSubgrupo LIKE ISNULL(@pmIdSubgrupo,'%')  AND P.IdMarca LIKE ISNULL(@pmIdMarca,'%') AND P.IdBodega LIKE ISNULL(@pmIdBodega,'%')
 AND IdProv LIKE ISNULL(@pmIdProv,'%') AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Seriales=ISNULL(@pmSeriales,0) or  Seriales=ISNULL(@pmSeriales,1))
 AND (Tanques=ISNULL(@pmTanques,0) or  Tanques=ISNULL(@pmTanques,1))
 ORDER BY DescripProd
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAcuFisCuentasMay] @pmnAnno INT,@pmnMes INT,@pmNiv INT,@pmIdCia CHAR(2)=Null
 AS 
 SELECT SUBSTRING(A.IdCuenta,1,@pmNiv) AS CdCuenta,NomCuenta,Naturaleza
,SUM(A.SaldoAnterior) AS tSaldoAnterior,SUM(A.TotalDebitos) AS tTotalDebitos,SUM(A.TotalCreditos) AS tTotalCreditos
,SUM(A.SaldoAnterior+A.TotalDebitos-A.TotalCreditos) AS NuevoSaldo
 FROM AcuFisCuentas AS A INNER JOIN PucNiif AS P ON SUBSTRING(A.IdCuenta,1,@pmNiv)=P.IdCuenta
 WHERE A.nAnno=@pmnAnno AND A.nMes=@pmnMes AND (@pmIdCia IS NULL OR A.IdCia=@pmIdCia)
 GROUP BY SUBSTRING(A.IdCuenta,1,@pmNiv),NomCuenta,Naturaleza
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAcuFisCuentasMayCia] @pmnAnno INT,@pmnMes INT,@pmNiv INT,@pmIdCia CHAR(2)=Null
 AS 
 SELECT SUBSTRING(A.IdCuenta,1,@pmNiv) AS CdCuenta,NomCuenta,Naturaleza,A.IdCia AS CdCia,Compania
,SUM(A.SaldoAnterior) AS tSaldoAnterior,SUM(A.TotalDebitos) AS tTotalDebitos,SUM(A.TotalCreditos) AS tTotalCreditos
,SUM(A.SaldoAnterior+A.TotalDebitos-A.TotalCreditos) AS NuevoSaldo
 FROM AcuFisCuentas AS A INNER JOIN PucNiif AS P ON SUBSTRING(A.IdCuenta,1,@pmNiv)=P.IdCuenta
 INNER JOIN Companias AS C ON A.IdCia=C.IdCia
 WHERE A.nAnno=@pmnAnno AND A.nMes=@pmnMes AND (@pmIdCia IS NULL OR A.IdCia=@pmIdCia)
 GROUP BY SUBSTRING(A.IdCuenta,1,@pmNiv),NomCuenta,Naturaleza,A.IdCia,Compania
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[paQryNomAlqTot_Cr] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmTipAdm VARCHAR(3)=Null
,@pmIdFondo VARCHAR(8)=Null
 AS 
 SELECT TipoLiq,Numero,Item,TipAdm,T.IdFondo AS CodFondo,Fondo,F.IdTercero AS NitFondo,RazonSocial,TipoId,Dv,CodTipoApo,VrTotalCot,VrTotalFsp,VrTotalFsu,VrTotalAva,VrTotalAve,VrTotalUpc
,VrTotalLic,VrTotalInc,VrTotalOtr,DiasMora,TarifMora,VrIntMora,VrIMoraOtr,VrTotalSAF,VrSAFUpc,VrTotalPagar,VrTotalFon,TotalAfi,NumAutLic,NumAutInc,NumPlaSAF,TipoReg
 FROM Trn_NomAlqTot AS T LEFT JOIN Fondos AS F ON T.IdFondo=F.IdFondo
 LEFT JOIN Terceros AS TF ON F.IdTercero=TF.IdTercero
 WHERE TipoLiq=@pmTipoLiq AND Numero LIKE ISNULL(@pmNumero,'%') AND TipAdm LIKE ISNULL(@pmTipAdm,'%')
 AND T.IdFondo LIKE ISNULL(@pmIdFondo,'%')
 ORDER BY Numero,TipoReg,TipAdm,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_NiifComIB] @pmtmEst CHAR(2)
 AS 
 SELECT BC.tmIdCuenta AS IdCuent,NomCuenta,tmTipo,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo
,tmIdCia,Compania,tmIdTercero,RazonSocial,tmCodAgncia,A.Agencia AS NomAgencia,tmIdVehiculo,tmIdCCosto,CCosto,tmIdSubCos,SubCosto
--información del tercero
,TipoId,Dv,Direccion,T.IdLocal AS CdCiudad,Localidad,L.IdDep AS CdDepto,Departamento,Telefono 
--información de agencia y vehículos
,CodAgencia,Referencia
,tmItem,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12
 FROM tm_NiifCom AS BC INNER JOIN PucNiif AS P ON BC.tmIdCuenta=P.IdCuenta
 INNER JOIN Terceros AS T ON BC.tmIdTercero=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN CentroCosto AS CC ON BC.tmIdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON BC.tmIdSubCos=SC.IdSubCos
 LEFT JOIN tm_NivCue AS NV ON BC.tmIdCuenta=NV.tmIdCuenta
 LEFT JOIN Agencias AS A ON BC.tmCodAgncia=A.IdAgencia
 LEFT JOIN Companias AS CI ON BC.tmIdCia=CI.IdCia
 WHERE tmEst=@pmtmEst
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_NiifLibOfi] @pmtmEst CHAR(2)
 AS 
 SELECT O.tmIdCuenta AS CdCuenta,NomCuenta,tmTipCom,TipoCom,tmIdCia,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo,tmIdTercero,RazonSocial
,tmIdCCosto,CCosto,tmIdSubCos,SubCosto,tmSanNit,tmSanNitcc,tmSanNitCcSub
,tmNivel1,tmNivel2,tmNivel4,tmNivel6,tmNivel9,tmNivel12,tmItem,Compania,TC.IdDiario AS CdDiario,Diario
,TipoId,Dv,NomCial,SiglaRaz,Direccion,T.IdLocal AS TercCdCiudad,Localidad,L.IdDep AS CdDep
,Departamento,Telefono,TipEnte
 FROM tm_NiifLib AS O INNER JOIN TiposCom AS TC ON O.tmTipCom=TC.IdCom
 INNER JOIN Diarios AS DR ON TC.IdDiario=DR.IdDiario
 INNER JOIN PucNiif AS P ON O.tmIdCuenta=P.IdCuenta
 INNER JOIN Terceros AS T ON O.tmIdTercero=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep
 INNER JOIN CentroCosto AS CC ON O.tmIdCCosto=CC.IdCCosto LEFT JOIN SubCentros AS SC ON O.tmIdSubCos=SC.IdSubCos
 LEFT JOIN Companias AS CI ON O.tmIdCia=CI.IdCia LEFT JOIN tm_NivCue AS NV ON O.tmIdCuenta=NV.tmIdCuenta
 WHERE tmEst=@pmtmEst
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAcuNiifCuentasMayCia] @pmnAnno INT,@pmnMes INT,@pmNiv INT,@pmIdCia CHAR(2)=Null
 AS 
 SELECT SUBSTRING(A.IdCuenta,1,@pmNiv) AS CdCuenta,NomCuenta,Naturaleza,A.IdCia AS CdCia,Compania
,SUM(A.SaldoAnterior) AS tSaldoAnterior,SUM(A.TotalDebitos) AS tTotalDebitos,SUM(A.TotalCreditos) AS tTotalCreditos
,SUM(A.SaldoAnterior+A.TotalDebitos-A.TotalCreditos) AS NuevoSaldo
 FROM AcuNiifCuentas AS A INNER JOIN PucNiif AS P ON SUBSTRING(A.IdCuenta,1,@pmNiv)=P.IdCuenta
 INNER JOIN Companias AS C ON A.IdCia=C.IdCia
 WHERE A.nAnno=@pmnAnno AND A.nMes=@pmnMes AND (@pmIdCia IS NULL OR A.IdCia=@pmIdCia)
 GROUP BY SUBSTRING(A.IdCuenta,1,@pmNiv),NomCuenta,Naturaleza,A.IdCia,Compania
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_NiifLibDia] @pmtmEst CHAR(2)
AS 
 SELECT tmIdCuenta,NomCuenta,tmTipCom,TipoCom,tmIdCia,tmSaldoAnterior,tmTotalDebitos,tmTotalCreditos
,tmSaldoAnterior+tmTotalDebitos-tmTotalCreditos AS NuevoSaldo,tmIdTercero,RazonSocial
,tmIdCCosto,CCosto,tmIdSubCos,SubCosto,tmSanNit,tmSanNitcc,tmSanNitCcSub
,Compania,TC.IdDiario AS CdDiario,Diario,tmItem
--información del tercero
,TipoId,Dv,NomCial,SiglaRaz,Direccion,T.IdLocal AS TercCdCiudad,Localidad,L.IdDep AS CdDep
,Departamento,Telefono,TipEnte
 FROM tm_NiifLib AS O INNER JOIN TiposCom AS TC ON O.tmTipCom=TC.IdCom
 INNER JOIN Diarios AS DR ON TC.IdDiario=DR.IdDiario
 INNER JOIN PucNiif AS P ON O.tmIdCuenta=P.IdCuenta
 INNER JOIN Terceros AS T ON O.tmIdTercero=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN CentroCosto AS CC ON O.tmIdCCosto=CC.IdCCosto
 LEFT JOIN SubCentros AS SC ON O.tmIdSubCos=SC.IdSubCos
 LEFT JOIN Companias AS CI ON O.tmIdCia=CI.IdCia
 WHERE tmEst=@pmtmEst
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Aportes_Sel]  @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmtmEst VARCHAR(5)
 AS 
 INSERT INTO tm_Aportes (tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena,tmValorIcbf,tmValorFsp,tmValorFsu
,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto
,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmValorESAP,tmValorMin,tmCdFonAvp,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal)
 SELECT @pmtmEst,Item,IdEmpleado,Apellido1,Apellido2,Nombre1,Nombre2,NContrato,DiasPen,DiasSal,DiasRie,DiasCom,VrIBCPen,VrIBCSal,VrIBCRie,VrIBCCajas,VrIBCRef,VrPension,VrSalud,VrRiesgos,VrCajas,VrSena,VrICBF,VrApoFsp,VrApoFsu,TarifPen,TarifSal,TarifRie,TarifFsp,TarifFsu,TarifEsap,TarifEdu
,TarifCom,TarifIcb,TarifSen,VrIncap,VrLicencia,VrApoVolAfi,VrApoVolApt,VrNoReten,VrUpc,VrIncIrp,NumAutLic,NumAutInc,NumAutIrp,IdFonPen,IdFonSal,IdFonRie,IdCajCom,IdInstala,'0','0','0','0','',SalBasico,VrESAP,VrMinEdu,CdFonAvp,0,0,0
 FROM Trn_NomAlqDet
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryAcuNiifCuentasMay] @pmnAnno INT,@pmnMes INT,@pmNiv INT,@pmIdCia CHAR(2)=Null
 AS 
 SELECT SUBSTRING(A.IdCuenta,1,@pmNiv) AS CdCuenta,NomCuenta,Naturaleza
,SUM(A.SaldoAnterior) AS tSaldoAnterior,SUM(A.TotalDebitos) AS tTotalDebitos,SUM(A.TotalCreditos) AS tTotalCreditos
,SUM(A.SaldoAnterior+A.TotalDebitos-A.TotalCreditos) AS NuevoSaldo
 FROM AcuNiifCuentas AS A INNER JOIN PucNiif AS P ON SUBSTRING(A.IdCuenta,1,@pmNiv)=P.IdCuenta
 WHERE A.nAnno=@pmnAnno AND A.nMes=@pmnMes AND (@pmIdCia IS NULL OR A.IdCia=@pmIdCia)
 GROUP BY SUBSTRING(A.IdCuenta,1,@pmNiv),NomCuenta,Naturaleza
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomAlqNov] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmNovIng INT,@pmNovRet INT,@pmNovTde INT,@pmNovTae INT,@pmNovTdp INT,@pmNovTap INT
,@pmNovVsp INT,@pmNovVte INT,@pmNovVst INT,@pmNovSln INT,@pmNovIge INT,@pmNovLma INT,@pmNovVac INT,@pmNovAvp INT,@pmNovVct INT,@pmNovIrp INT,@pmCodNewPen VARCHAR(8),@pmCodNewSal VARCHAR(8),@pmCodNewIns VARCHAR(4)
,@pmCodNewRie VARCHAR(4),@pmCodNewGru VARCHAR(4),@pmDiasNov INT,@pmDiasSan DECIMAL(14,4),@pmDiasDnrPen INT,@pmDiasDnrSal INT
 AS 
 INSERT INTO Trn_NomAlqNov (TipoLiq,Numero,Item,IdEmpleado,NovIng,NovRet,NovTde,NovTae,NovTdp,NovTap,NovVsp,NovVte,NovVst,NovSln,NovIge,NovLma,NovVac,NovAvp,NovVct,NovIrp,CodNewPen,CodNewSal,CodNewIns,CodNewRie,CodNewGru,DiasNov,DiasSan,DiasDnrPen,DiasDnrSal)
 VALUES (@pmTipoLiq,@pmNumero,@pmItem,@pmIdEmpleado,@pmNovIng,@pmNovRet,@pmNovTde,@pmNovTae,@pmNovTdp,@pmNovTap,@pmNovVsp,@pmNovVte,@pmNovVst,@pmNovSln,@pmNovIge,@pmNovLma,@pmNovVac,@pmNovAvp,@pmNovVct
,@pmNovIrp,@pmCodNewPen,@pmCodNewSal,@pmCodNewIns,@pmCodNewRie,@pmCodNewGru,@pmDiasNov,@pmDiasSan,@pmDiasDnrPen,@pmDiasDnrSal)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomAlqNov] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT,@pmIdEmpleado VARCHAR(16),@pmNovIng INT,@pmNovRet INT,@pmNovTde INT,@pmNovTae INT,@pmNovTdp INT,@pmNovTap INT
,@pmNovVsp INT,@pmNovVte INT,@pmNovVst INT,@pmNovSln INT,@pmNovIge INT,@pmNovLma INT,@pmNovVac INT,@pmNovAvp INT,@pmNovVct INT,@pmNovIrp INT,@pmCodNewPen VARCHAR(8),@pmCodNewSal VARCHAR(8)
,@pmCodNewIns VARCHAR(4),@pmCodNewRie VARCHAR(4),@pmCodNewGru VARCHAR(4),@pmDiasNov INT,@pmDiasSan DECIMAL(14,4),@pmDiasDnrPen INT,@pmDiasDnrSal INT
 AS 
 UPDATE Trn_NomAlqNov SET IdEmpleado=@pmIdEmpleado,NovIng=@pmNovIng,NovRet=@pmNovRet,NovTde=@pmNovTde,NovTae=@pmNovTae,NovTdp=@pmNovTdp,NovTap=@pmNovTap,NovVsp=@pmNovVsp,NovVte=@pmNovVte
,NovVst=@pmNovVst,NovSln=@pmNovSln,NovIge=@pmNovIge,NovLma=@pmNovLma,NovVac=@pmNovVac,NovAvp=@pmNovAvp,NovVct=@pmNovVct,NovIrp=@pmNovIrp,CodNewPen=@pmCodNewPen,CodNewSal=@pmCodNewSal
,CodNewIns=@pmCodNewIns,CodNewRie=@pmCodNewRie,CodNewGru=@pmCodNewGru,DiasNov=@pmDiasNov,DiasSan=@pmDiasSan,DiasDnrPen=@pmDiasDnrPen,DiasDnrSal=@pmDiasDnrSal
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero AND Item=@pmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAlqNov] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmItem INT
 AS 
 SELECT TipoLiq,Numero,Item,IdEmpleado,NovIng,NovRet,NovTde,NovTae,NovTdp,NovTap,NovVsp,NovVte,NovVst,NovSln,NovIge,NovLma,NovVac,NovAvp
,NovVct,NovIrp,CodNewPen,CodNewSal,CodNewIns,CodNewRie,CodNewGru,DiasNov,DiasSan,DiasDnrPen,DiasDnrSal
 FROM Trn_NomAlqNov
 WHERE TipoLiq=@pmTipoLiq AND Numero=@pmNumero 
 AND (Item>=ISNULL(@pmItem,0)  AND Item<=ISNULL(@pmItem,2147483647))
 ORDER BY  Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_NiifLib] @pmtmEst CHAR(2),@pmtmTipCom VARCHAR(3),@pmtmIdCuenta VARCHAR(16),@pmtmIdTercero VARCHAR(16),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmIdCia CHAR(2)
,@pmtmTotalDebitos MONEY,@pmtmTotalCreditos MONEY,@pmtmSaldoAnterior MONEY,@pmtmSanNit MONEY,@pmtmSanNitcc MONEY,@pmtmSanNitCcSub MONEY
 AS 
 INSERT INTO tm_NiifLib (tmEst,tmTipCom,tmIdCuenta,tmIdTercero,tmIdCCosto,tmIdSubCos,tmIdCia,tmTotalDebitos,tmTotalCreditos,tmSaldoAnterior,tmSanNit,tmSanNitcc,tmSanNitCcSub)
 VALUES (@pmtmEst,@pmtmTipCom,@pmtmIdCuenta,@pmtmIdTercero,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmIdCia,@pmtmTotalDebitos,@pmtmTotalCreditos,@pmtmSaldoAnterior,@pmtmSanNit,@pmtmSanNitcc,@pmtmSanNitCcSub)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedidoLta] @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmPedidoIni INT=Null,@pmPedidoFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null
,@pmVigencia VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS
 SELECT Pedido,IdCia,Fecha,FechaVence,P.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrOtrDcto
,VrCargos,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac
,IdForma,DetallePago,MulPlazos,IdPlazo,CdMney,TipoTrans,NitEmpTrans,EmpTrans,pVehiculo,AsignarVeh,CdConductor,CdRuta,ListaPrec,RefPedido,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem
,NumAutoriza,NumAutCupo,NumAutCheq,Modalidad,Vigencia,NumAprob,IdCiaApr,FecAprob,CdUsuAprob,DetalleAprob,TipoOrden,TipoModifica,NumCotizac,CdCiaCotizac,NumAutSicom,OrigenAdd,ZonaFrontera,Anulado,FecDev,P.Observacion AS Observ
,P.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,P.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Opedido AS P INNER JOIN Terceros AS T ON P.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON P.IdVend=V.IdTercero
 INNER JOIN  Conceptos AS CN ON P.IdConcepto=CN.IdConcepto
 INNER JOIN EstadoDoc AS ED ON P.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON P.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Pedido BETWEEN ISNULL(@pmPedidoIni,0) AND ISNULL(@pmPedidoFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND P.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND P.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%') AND Vigencia LIKE ISNULL(@pmVigencia,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Pedido
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQrytm_AportesDet] @pmtmEst VARCHAR(5),@pmtmItem INT=Null
 AS 
 SELECT tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena
,tmValorIcbf,tmValorFsp,tmValorFsu,tmValorESAP,tmValorMin,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte
,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,tmCodNewGru,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp
,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmDiasNov,tmCdFonAvp,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal
 FROM tm_Aportes
 WHERE tmEst=@pmtmEst
 AND (tmItem>=ISNULL(@pmtmItem,0)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUptm_AportesNov] @pmtmEst VARCHAR(5),@pmtmItem INT,@pmtmNovIng INT,@pmtmNovRet INT,@pmtmNovTda INT,@pmtmNovTaa INT,@pmtmNovTdp INT,@pmtmNovTap INT
,@pmtmNovVsp INT,@pmtmNovVte INT,@pmtmNovVst INT,@pmtmNovSln INT,@pmtmNovIge INT,@pmtmNovLma INT,@pmtmNovVac INT,@pmtmNovAvp INT,@pmtmNovIrp INT,@pmtmNovVct INT
,@pmtmCodNewEps VARCHAR(8),@pmtmCodNewPen VARCHAR(8),@pmtmCodNewRie VARCHAR(8),@pmtmCodNewIns VARCHAR(4),@pmtmCodNewTar VARCHAR(4),@pmtmCodNewGru VARCHAR(4),@pmtmDiasNov DECIMAL(10,4),@pmtmDiasSan DECIMAL(14,4)
,@pmtmDiasDnrPen INT,@pmtmDiasDnrSal INT
 AS 
 UPDATE tm_Aportes SET tmNovIng=@pmtmNovIng,tmNovRet=@pmtmNovRet,tmNovTda=@pmtmNovTda,tmNovTaa=@pmtmNovTaa,tmNovTdp=@pmtmNovTdp,tmNovTap=@pmtmNovTap,tmNovVsp=@pmtmNovVsp,tmNovVte=@pmtmNovVte,tmNovVst=@pmtmNovVst,tmNovSln=@pmtmNovSln
,tmNovIge=@pmtmNovIge,tmNovLma=@pmtmNovLma,tmNovVac=@pmtmNovVac,tmNovAvp=@pmtmNovAvp,tmNovIrp=@pmtmNovIrp,tmNovVct=@pmtmNovVct,tmCodNewEps=@pmtmCodNewEps,tmCodNewPen=@pmtmCodNewPen,tmCodNewRie=@pmtmCodNewRie,tmCodNewIns=@pmtmCodNewIns
,tmCodNewTar=@pmtmCodNewTar,tmCodNewGru=@pmtmCodNewGru,tmDiasNov=@pmtmDiasNov,tmDiasSan=@pmtmDiasSan,tmDiasDnrPen=@pmtmDiasDnrPen,tmDiasDnrSal=@pmtmDiasDnrSal
 WHERE tmEst=@pmtmEst AND tmItem=@pmtmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paUptm_AportesDet] @pmtmEst VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16),@pmtmApellido1 VARCHAR(50),@pmtmApellido2 VARCHAR(50),@pmtmNombre1 VARCHAR(50),@pmtmNombre2 VARCHAR(50),@pmtmNContto INT,@pmtmDiasPen DECIMAL(10,4),@pmtmDiasSal DECIMAL(10,4),@pmtmDiasRie DECIMAL(10,4),@pmtmDiasCom DECIMAL(10,4),@pmtmBasePen MONEY,@pmtmBaseSal MONEY,@pmtmBaseRie MONEY,@pmtmBaseCom MONEY,@pmtmBaseRef MONEY,@pmtmValorPen MONEY,@pmtmValorSal MONEY,@pmtmValorRie MONEY,@pmtmValorCom MONEY,@pmtmValorSena MONEY,@pmtmValorIcbf MONEY,@pmtmValorFsp MONEY,@pmtmValorFsu MONEY,@pmtmValorESAP MONEY,@pmtmValorMin MONEY,@pmtmTarfaPen DECIMAL(10,4),@pmtmTarfaSal DECIMAL(10,4),@pmtmTarfaRie DECIMAL(10,4)
,@pmtmTarfaFsp DECIMAL(10,4),@pmtmTarfaFsu DECIMAL(10,4),@pmtmTarfaEsa DECIMAL(10,4),@pmtmTarfaMin DECIMAL(10,4),@pmtmTarfaCom DECIMAL(10,4),@pmtmTarfaIcb DECIMAL(10,4),@pmtmTarfaSen DECIMAL(10,4),@pmtmVrIncap MONEY,@pmtmVrLicencia MONEY,@pmtmVrVolAfi MONEY,@pmtmVrVolApo MONEY,@pmtmVrNoReten MONEY
,@pmtmVrUpc MONEY,@pmtmVrIncIrp MONEY,@pmtmNAutLic VARCHAR(30),@pmtmNAutInc VARCHAR(30),@pmtmNAutIrp VARCHAR(30),@pmtmIdFonPen VARCHAR(8),@pmtmIdFonSal VARCHAR(8),@pmtmIdFonRie VARCHAR(8),@pmtmIdFonCaj VARCHAR(8),@pmtmIdInstala VARCHAR(4),@pmtmIdDep VARCHAR(4),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmIdNom VARCHAR(4),@pmtmVehiculo VARCHAR(10),@pmtmVrBasico MONEY
,@pmtmCdFonAvp VARCHAR(8),@pmtmDiasSan DECIMAL(14,4),@pmtmDiasDnrPen INT,@pmtmDiasDnrSal INT
 AS 
 UPDATE tm_Aportes SET tmIdEmpleado=@pmtmIdEmpleado,tmApellido1=@pmtmApellido1,tmApellido2=@pmtmApellido2,tmNombre1=@pmtmNombre1,tmNombre2=@pmtmNombre2,tmNContto=@pmtmNContto,tmDiasPen=@pmtmDiasPen,tmDiasSal=@pmtmDiasSal,tmDiasRie=@pmtmDiasRie,tmDiasCom=@pmtmDiasCom,tmBasePen=@pmtmBasePen,tmBaseSal=@pmtmBaseSal,tmBaseRie=@pmtmBaseRie,tmBaseCom=@pmtmBaseCom,tmBaseRef=@pmtmBaseRef,tmValorPen=@pmtmValorPen,tmValorSal=@pmtmValorSal,tmValorRie=@pmtmValorRie,tmValorCom=@pmtmValorCom,tmValorSena=@pmtmValorSena,tmValorIcbf=@pmtmValorIcbf,tmValorFsp=@pmtmValorFsp,tmValorFsu=@pmtmValorFsu,tmValorESAP=@pmtmValorESAP
,tmValorMin=@pmtmValorMin,tmTarfaPen=@pmtmTarfaPen,tmTarfaSal=@pmtmTarfaSal,tmTarfaRie=@pmtmTarfaRie,tmTarfaFsp=@pmtmTarfaFsp,tmTarfaFsu=@pmtmTarfaFsu,tmTarfaEsa=@pmtmTarfaEsa,tmTarfaMin=@pmtmTarfaMin,tmTarfaCom=@pmtmTarfaCom,tmTarfaIcb=@pmtmTarfaIcb,tmTarfaSen=@pmtmTarfaSen,tmVrIncap=@pmtmVrIncap,tmVrLicencia=@pmtmVrLicencia,tmVrVolAfi=@pmtmVrVolAfi,tmVrVolApo=@pmtmVrVolApo,tmVrNoReten=@pmtmVrNoReten,tmVrUpc=@pmtmVrUpc,tmVrIncIrp=@pmtmVrIncIrp,tmNAutLic=@pmtmNAutLic,tmNAutInc=@pmtmNAutInc,tmNAutIrp=@pmtmNAutIrp,tmIdFonPen=@pmtmIdFonPen,tmIdFonSal=@pmtmIdFonSal,tmIdFonRie=@pmtmIdFonRie,tmIdFonCaj=@pmtmIdFonCaj,tmIdInstala=@pmtmIdInstala
,tmIdDep=@pmtmIdDep,tmIdCCosto=@pmtmIdCCosto,tmIdSubCos=@pmtmIdSubCos,tmIdNom=@pmtmIdNom,tmVehiculo=@pmtmVehiculo,tmVrBasico=@pmtmVrBasico,tmCdFonAvp=@pmtmCdFonAvp,tmDiasSan=@pmtmDiasSan,tmDiasDnrPen=@pmtmDiasDnrPen,tmDiasDnrSal=@pmtmDiasDnrSal
 WHERE tmEst=@pmtmEst AND tmItem=@pmtmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInstm_Aportes] @pmtmEst VARCHAR(5),@pmtmItem INT,@pmtmIdEmpleado VARCHAR(16),@pmtmApellido1 VARCHAR(50),@pmtmApellido2 VARCHAR(50),@pmtmNombre1 VARCHAR(50),@pmtmNombre2 VARCHAR(50),@pmtmNContto INT,@pmtmDiasPen DECIMAL(10,4),@pmtmDiasSal DECIMAL(10,4),@pmtmDiasRie DECIMAL(10,4),@pmtmDiasCom DECIMAL(10,4),@pmtmBasePen MONEY,@pmtmBaseSal MONEY,@pmtmBaseRie MONEY,@pmtmBaseCom MONEY,@pmtmBaseRef MONEY,@pmtmValorPen MONEY,@pmtmValorSal MONEY,@pmtmValorRie MONEY,@pmtmValorCom MONEY,@pmtmValorSena MONEY
,@pmtmValorIcbf MONEY,@pmtmValorFsp MONEY,@pmtmValorFsu MONEY,@pmtmValorESAP MONEY,@pmtmValorMin MONEY,@pmtmTarfaPen DECIMAL(10,4),@pmtmTarfaSal DECIMAL(10,4),@pmtmTarfaRie DECIMAL(10,4),@pmtmTarfaFsp DECIMAL(10,4),@pmtmTarfaFsu DECIMAL(10,4),@pmtmTarfaEsa DECIMAL(10,4),@pmtmTarfaMin DECIMAL(10,4),@pmtmTarfaCom DECIMAL(10,4),@pmtmTarfaIcb DECIMAL(10,4),@pmtmTarfaSen DECIMAL(10,4),@pmtmNovIng INT,@pmtmNovRet INT,@pmtmNovTda INT,@pmtmNovTaa INT,@pmtmNovTdp INT,@pmtmNovTap INT,@pmtmNovVsp INT,@pmtmNovVte INT,@pmtmNovVst INT,@pmtmNovSln INT,@pmtmNovIge INT,@pmtmNovLma INT,@pmtmNovVac INT
,@pmtmNovAvp INT,@pmtmNovIrp INT,@pmtmNovVct INT,@pmtmCodNewEps VARCHAR(8),@pmtmCodNewPen VARCHAR(8),@pmtmCodNewRie VARCHAR(8),@pmtmCodNewIns VARCHAR(4),@pmtmCodNewTar VARCHAR(4),@pmtmCodNewGru VARCHAR(4),@pmtmVrIncap MONEY,@pmtmVrLicencia MONEY,@pmtmVrVolAfi MONEY,@pmtmVrVolApo MONEY,@pmtmVrNoReten MONEY,@pmtmVrUpc MONEY,@pmtmVrIncIrp MONEY,@pmtmNAutLic VARCHAR(30),@pmtmNAutInc VARCHAR(30),@pmtmNAutIrp VARCHAR(30),@pmtmIdFonPen VARCHAR(8),@pmtmIdFonSal VARCHAR(8),@pmtmIdFonRie VARCHAR(8),@pmtmIdFonCaj VARCHAR(8),@pmtmIdInstala VARCHAR(4)
,@pmtmIdDep VARCHAR(4),@pmtmIdCCosto VARCHAR(16),@pmtmIdSubCos VARCHAR(16),@pmtmIdNom VARCHAR(4),@pmtmVehiculo VARCHAR(10),@pmtmVrBasico MONEY,@pmtmDiasNov DECIMAL(10,4),@pmtmCdFonAvp VARCHAR(8),@pmtmDiasSan DECIMAL(14,4),@pmtmDiasDnrPen INT,@pmtmDiasDnrSal INT
 AS 
 INSERT INTO tm_Aportes (tmEst,tmItem,tmIdEmpleado,tmApellido1,tmApellido2,tmNombre1,tmNombre2,tmNContto,tmDiasPen,tmDiasSal,tmDiasRie,tmDiasCom,tmBasePen,tmBaseSal,tmBaseRie,tmBaseCom,tmBaseRef,tmValorPen,tmValorSal,tmValorRie,tmValorCom,tmValorSena,tmValorIcbf,tmValorFsp,tmValorFsu,tmTarfaPen,tmTarfaSal,tmTarfaRie,tmTarfaFsp,tmTarfaFsu,tmTarfaEsa,tmTarfaMin,tmTarfaCom,tmTarfaIcb,tmTarfaSen,tmNovIng,tmNovRet,tmNovTda,tmNovTaa,tmNovTdp,tmNovTap,tmNovVsp,tmNovVte,tmNovVst,tmNovSln,tmNovIge,tmNovLma,tmNovVac,tmNovAvp,tmNovIrp,tmNovVct,tmCodNewEps,tmCodNewPen,tmCodNewRie,tmCodNewIns,tmCodNewTar,tmCodNewGru
,tmVrIncap,tmVrLicencia,tmVrVolAfi,tmVrVolApo,tmVrNoReten,tmVrUpc,tmVrIncIrp,tmNAutLic,tmNAutInc,tmNAutIrp,tmIdFonPen,tmIdFonSal,tmIdFonRie,tmIdFonCaj,tmIdInstala,tmIdDep,tmIdCCosto,tmIdSubCos,tmIdNom,tmVehiculo,tmVrBasico,tmDiasNov,tmValorESAP,tmValorMin,tmCdFonAvp,tmDiasSan,tmDiasDnrPen,tmDiasDnrSal)
 VALUES (@pmtmEst,@pmtmItem,@pmtmIdEmpleado,@pmtmApellido1,@pmtmApellido2,@pmtmNombre1,@pmtmNombre2,@pmtmNContto,@pmtmDiasPen,@pmtmDiasSal,@pmtmDiasRie,@pmtmDiasCom,@pmtmBasePen,@pmtmBaseSal,@pmtmBaseRie,@pmtmBaseCom,@pmtmBaseRef,@pmtmValorPen,@pmtmValorSal,@pmtmValorRie,@pmtmValorCom,@pmtmValorSena,@pmtmValorIcbf,@pmtmValorFsp,@pmtmValorFsu,@pmtmTarfaPen,@pmtmTarfaSal,@pmtmTarfaRie,@pmtmTarfaFsp,@pmtmTarfaFsu,@pmtmTarfaEsa,@pmtmTarfaMin,@pmtmTarfaCom,@pmtmTarfaIcb,@pmtmTarfaSen,@pmtmNovIng,@pmtmNovRet,@pmtmNovTda,@pmtmNovTaa,@pmtmNovTdp,@pmtmNovTap
,@pmtmNovVsp,@pmtmNovVte,@pmtmNovVst,@pmtmNovSln,@pmtmNovIge,@pmtmNovLma,@pmtmNovVac,@pmtmNovAvp,@pmtmNovIrp,@pmtmNovVct,@pmtmCodNewEps,@pmtmCodNewPen,@pmtmCodNewRie,@pmtmCodNewIns,@pmtmCodNewTar,@pmtmCodNewGru,@pmtmVrIncap,@pmtmVrLicencia,@pmtmVrVolAfi,@pmtmVrVolApo,@pmtmVrNoReten,@pmtmVrUpc,@pmtmVrIncIrp,@pmtmNAutLic,@pmtmNAutInc,@pmtmNAutIrp,@pmtmIdFonPen,@pmtmIdFonSal,@pmtmIdFonRie,@pmtmIdFonCaj,@pmtmIdInstala,@pmtmIdDep,@pmtmIdCCosto,@pmtmIdSubCos,@pmtmIdNom,@pmtmVehiculo,@pmtmVrBasico,@pmtmDiasNov,@pmtmValorESAP,@pmtmValorMin,@pmtmCdFonAvp,@pmtmDiasSan,@pmtmDiasDnrPen,@pmtmDiasDnrSal)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpOpedido] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY
,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmLugarEnvio VARCHAR(50),@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20)
,@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmAsignarVeh BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmListaPrec CHAR(1),@pmRefPedido VARCHAR(50)
,@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmNumAutoriza INT,@pmNumAutCupo INT,@pmNumAutCheq INT,@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2)
,@pmFechaRem SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmTipoTrans INT,@pmTipoOrden VARCHAR(3),@pmTipoModifica VARCHAR(10),@pmNumCotizac INT,@pmCdCiaCotizac CHAR(2),@pmNumAutSicom VARCHAR(50),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Opedido SET Fecha=@pmFecha,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdClieFact=@pmIdClieFact,VrSubTotal=@pmVrSubTotal,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrFletes=@pmVrFletes,VrOtros=@pmVrOtros,VrCargos=@pmVrCargos,VrOtrDcto=@pmVrOtrDcto,VrSobretasa=@pmVrSobretasa,VrImpGlobal=@pmVrImpGlobal,VrNeto=@pmVrNeto
,Cantidad=@pmCantidad,IdVend=@pmIdVend,TarifaCom=@pmTarifaCom,CodTarCom=@pmCodTarCom,DirEnvio=@pmDirEnvio,IdLocEnv=@pmIdLocEnv,LugarEnvio=@pmLugarEnvio,DiasEntraga=@pmDiasEntraga,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargoContac=@pmCargoContac,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo
,CdMney=@pmCdMney,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,AsignarVeh=@pmAsignarVeh,pVehiculo=@pmpVehiculo,CdConductor=@pmCdConductor,CdRuta=@pmCdRuta,ListaPrec=@pmListaPrec,RefPedido=@pmRefPedido,Modalidad=@pmModalidad,Vigencia=@pmVigencia,NumAutoriza=@pmNumAutoriza,NumAutCupo=@pmNumAutCupo,NumAutCheq=@pmNumAutCheq,NumAprob=@pmNumAprob,IdCiaApr=@pmIdCiaApr
,FecAprob=@pmFecAprob,DetalleAprob=@pmDetalleAprob,CdUsuAprob=@pmCdUsuAprob,TipFac=@pmTipFac,Factura=@pmFactura,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,TipRem=@pmTipRem,Remision=@pmRemision,IdCiaRem=@pmIdCiaRem,FechaRem=@pmFechaRem,NumCotizac=@pmNumCotizac,CdCiaCotizac=@pmCdCiaCotizac,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,TipoModifica=@pmTipoModifica,IdEstado=@pmIdEstado
,ZonaFrontera=@pmZonaFrontera,TipoTrans=@pmTipoTrans,TipoOrden=@pmTipoOrden, FecUpdate=@pmFecUpdate,NumAutSicom=@pmNumAutSicom
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryOpedido] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Pedido,IdCia,Fecha,FechaVence,IdConcepto,IdCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa
,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma
,DetallePago,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,AsignarVeh,pVehiculo,CdConductor,CdRuta,ListaPrec,RefPedido,Modalidad,Vigencia,NumAutoriza,NumAutCupo
,NumAutCheq,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem,NumCotizac,CdCiaCotizac,OrigenAdd,Anulado
,FecDev,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,NumAutSicom,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Opedido
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsOpedido] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdClieFact VARCHAR(16),@pmVrSubTotal MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY
,@pmVrFletes MONEY,@pmVrOtros MONEY,@pmVrCargos MONEY,@pmVrOtrDcto MONEY,@pmVrSobretasa MONEY,@pmVrImpGlobal MONEY,@pmVrNeto MONEY,@pmCantidad DECIMAL(14,4),@pmIdVend VARCHAR(16),@pmTarifaCom DECIMAL(14,4),@pmCodTarCom VARCHAR(4),@pmDirEnvio VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmLugarEnvio VARCHAR(50)
,@pmDiasEntraga INT,@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargoContac VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(100),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmCdMney VARCHAR(5),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150)
,@pmAsignarVeh BIT,@pmpVehiculo VARCHAR(10),@pmCdConductor VARCHAR(16),@pmCdRuta VARCHAR(4),@pmListaPrec CHAR(1),@pmRefPedido VARCHAR(50),@pmModalidad VARCHAR(10),@pmVigencia VARCHAR(10),@pmNumAutoriza INT,@pmNumAutCupo INT,@pmNumAutCheq INT,@pmNumAprob INT,@pmIdCiaApr CHAR(2),@pmFecAprob SMALLDATETIME
,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmTipRem VARCHAR(3),@pmRemision INT,@pmIdCiaRem CHAR(2),@pmFechaRem SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera INT,@pmTipoTrans INT,@pmTipoOrden VARCHAR(3),@pmTipoModifica VARCHAR(10),@pmNumCotizac INT,@pmCdCiaCotizac CHAR(2),@pmNumAutSicom VARCHAR(50),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Opedido (TipDoc,Pedido,IdCia,Fecha,FechaVence,IdConcepto,IdCliente,IdAgencia,IdClieFact,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocEnv,LugarEnvio,DiasEntraga,NitContac,NomContac,TelContac,emlContac,CargoContac,IdForma,DetallePago
,MulPlazos,IdPlazo,CdMney,NitEmpTrans,EmpTrans,AsignarVeh,pVehiculo,CdConductor,CdRuta,ListaPrec,RefPedido,Modalidad,Vigencia,NumAutoriza,NumAutCupo,NumAutCheq,NumAprob,IdCiaApr,FecAprob,DetalleAprob,CdUsuAprob,TipFac,Factura,IdCiaFac,FechaFact,TipRem,Remision,IdCiaRem,FechaRem,NumCotizac,CdCiaCotizac,OrigenAdd,ZonaFrontera,TipoTrans,TipoOrden,TipoModifica,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,NumAutSicom)
 VALUES (@pmTipDoc,@pmPedido,@pmIdCia,@pmFecha,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmIdClieFact,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobretasa,@pmVrImpGlobal,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom,@pmDirEnvio,@pmIdLocEnv
,@pmLugarEnvio,@pmDiasEntraga,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargoContac,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmCdMney,@pmNitEmpTrans,@pmEmpTrans,@pmAsignarVeh,@pmpVehiculo,@pmCdConductor,@pmCdRuta,@pmListaPrec,@pmRefPedido,@pmModalidad,@pmVigencia,@pmNumAutoriza,@pmNumAutCupo
,@pmNumAutCheq,@pmNumAprob,@pmIdCiaApr,@pmFecAprob,@pmDetalleAprob,@pmCdUsuAprob,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmTipRem,@pmRemision,@pmIdCiaRem,@pmFechaRem,@pmNumCotizac,@pmCdCiaCotizac,@pmOrigenAdd,@pmZonaFrontera,@pmTipoTrans,@pmTipoOrden,@pmTipoModifica,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmNumAutSicom)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomAutoliq_Cr] @pmTipoLiq VARCHAR(3),@pmNumero VARCHAR(20),@pmPerAutoliq VARCHAR(6)=Null
AS
 SELECT  A.TipoLiq AS TipoPla,A.Numero AS NumPla,Fecha,PerAutoliq,PerAutoSal,FechaPago,A.IdAportnte AS NitAportnte,NA.RazonSocial AS NomAportante,Presentacion,A.CodCia AS CodSucur,Compania,CodSucsal,DireccSuc,TelefSuc,CI.IdLocal AS CodCiuSuc
,LC.Localidad AS CiudadSuc,LC.IdDep AS CodDepSuc,CodFondo,F.Fondo AS NomFondo,CodArp,FRP.Fondo AS FondoArp,CodOper,EsCorr,PerCorr,NumCorr,TotalEmp,VrNomina,A.VrPension AS Total_Pen,A.VrSalud AS Total_Sal,A.VrRiesgos AS Total_Rie,A.VrCajas AS Total_Caj
,A.VrSena AS Total_Sen,A.VrIcbf AS Total_ICB,A.VrEsap AS Total_ESA,VrMinEduc,TotalAfipen,TotalAfiSal,TotalAfiRie,TotalAfiCaj,TotalAfiSen,TotalAfiIcb,TotalAfiEsa,TotalAfiEduc,TotAdmPen,TotAdmSal,TotAdmRie,TotAdmCom,A.Observacion AS Observ,LogAdmon,RedoMiles
,Anulado,A.IdEstado AS IdEstdo,Estado,TimeSys,A.FecUpdate AS Fec_Update,A.IdUsuario AS IdUsuari,Usuario
--datos del aportante
,NA.TipoId AS Apo_TipID,DvNit,IdRepLegal,RLG.RazonSocial AS NomRepLegal,RLG.TipoId AS RLegTID,RLG.Dv AS RLegDV,NA.Direccion AS Apo_dirccion,NA.IdLocal AS Apo_CodCiu,LA.Localidad AS Apo_NomCiu,LA.IdDep AS Apo_CodDep,Departamento,NA.Telefono AS Apo_tel,NA.Fax AS Apo_Fax,NA.e_mail AS Apo_email
,ClaseApo,TipoEco,TipoPersna,CIIUDane,FecIniCndato,TipAccCndato,FecFinCndato,CodOperInfo,TipoApo,FecMatMerc,ExonParaf,ExonCajas
--datos del detalle
,D.Item AS DetItem,D.IdEmpleado AS IdEmpldo,D.NContrato AS NumContrato,Apellido1,Apellido2,Nombre1,Nombre2,D.IdFonPen AS IdFdoPen,FP.Fondo AS FondoPen,TFP.TipoId AS Pen_TipID,FP.IdTercero AS Pen_Nit,TFP.Dv AS Pen_DV,TFP.RazonSocial AS Pen_Nombre
,IdFonSal,FS.Fondo AS FondoSal,TFS.TipoId AS Sal_TipID,FS.IdTercero AS Sal_Nit,TFS.Dv AS Sal_Dv,TFS.RazonSocial AS Sal_Nombre,IdFonRie,FR.Fondo AS FondoRie,TFR.TipoId AS Rie_TipID,FR.IdTercero AS Rie_nit,TFR.Dv AS Rie_Dv,TFR.RazonSocial AS Rie_nombre
,IdCajCom,FC.Fondo AS FondoCc,TFC.TipoId AS CC_TipId,FC.IdTercero AS CC_Nit,TFC.Dv AS CC_DV,TFC.RazonSocial AS cc_Nombre,SalBasico,VrIBCPen,DiasPen,TarifPen,D.VrPension AS VlrPension,VrApoFsp,VrApoFsu,VrApoVolAfi,VrApoVolApt,VrNoReten,VrIBCSal,DiasSal,TarifSal,D.VrSalud AS VlrSalud,D.VrUpc AS VlrUpc
,VrIncap,VrLicencia,NumAutInc,NumAutLic,VrIBCRie,DiasRie,TarifRie,D.VrRiesgos AS VlrRiesgos,VrIncIrp,NumAutIrp,D.IdInstala AS CodInstla,I.Instlacion AS CentroTrabaj,I.CodCiu AS CiuInstala
,VrIBCRef,VrIBCCajas,DiasCom,TarifCom,D.VrCajas AS VlrCajas,TarifSen,D.VrSena AS VlrSena,TarifIcb,D.VrICBF AS Vlricbf,TarifEsap,D.VrESAP AS VlrESAP,TarifEdu,VrMinEdu,TarifFsp,TarifFsu
--datos de novedades
,NovIng,NovRet,NovTde,NovTae,NovTdp,NovTap,NovVsp,NovVte,NovVst,NovSln,NovIge,NovLma,NovVac,NovAvp,NovVct,NovIrp,CodNewPen,FNP.Fondo AS NewFonPen,CodNewSal,FNS.Fondo AS NewFonEPS,CodNewIns,NI.Instlacion AS NewCentroTrabj,CodNewRie,CRN.Tarifa AS New_TarRie,CodNewGru
,NGR.TarfaTrab AS NewTarfTrab,NGR.TarfaEmp AS NewTarfEmp,DiasNov,DiasSan,DiasDnrPen,DiasDnrSal
--datos del empleado
,Apellidos,Nombres,E.Codigo AS EmpCodigo,E.TipoId AS Emp_TipID,E.Dv AS Emp_Dv,E.IdLugarCed AS Emp_IdLugrCed,L.Localidad AS Lugar_Ced,L.IdDep AS Emp_CodDep,E.Direccion AS Dirccion,E.Telefono AS Emp_Telfono,E.TelMovil AS Emp_TelMovil,E.e_mail AS Emp_Email,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,NumCuenta
,E.IdBanco AS CodBanco,EsCondtor,pVehiculo,FecNacmto,LugarNacmto,TipoSangre,FactorRh,Sexo,Licencia,CatLicencia,VigLicencia,TallaCam,TallaPan,TallaZap,IdEstCivil,EstCivil,NHijos,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdDep AS CodDepe,Dependencia,C.IdCCosto AS CodCcto,CCosto,C.IdSubCos AS CodScost,SubCosto,C.IdNom AS CodTipNom,TipoNomina,C.IdClase AS CodClaRie,CR.ClaseRiesgo AS ClasRiesgo
,CR.Tarifa AS TarifaRie,VrSalario,VrAuxTrans,C.VrUpc AS Con_VrUpc,SalMinimo,SalIntegral,FPnomina,C.TipoLiquida AS TipLiq,FecAfiSeg,C.IdCot AS CodCotz,Cotizante,C.IdGrupo AS CodGrup,GAR.TarfaTrab AS Con_TrfaTrab,GAR.TarfaEmp AS TrfaEmp,TipoTrabj
,ATFinMes,SegFinMes,C.IdEstado AS Con_IdEstdo,C.Inactivo AS Con_inactivo,IdJornada,DenyPEN,DenyEPS,DenyARP,DenyCaj,D.CdFonAvp AS CodFondoAvp,E.CdFonAvp AS Emp_FondoAVP,TipoPlanilla,SubTipoCot
 FROM Trn_NomAutoliq AS A INNER JOIN Trn_NomAlqDet AS D ON A.TipoLiq=D.TipoLiq AND A.Numero=D.Numero 
 INNER JOIN  NomAportante AS NA ON A.IdAportnte=NA.IdAportnte  INNER JOIN Terceros AS RLG ON NA.IdRepLegal=RLG.IdTercero
 INNER JOIN EstadoDoc AS ED ON A.IdEstado=ED.IdEstado 
 INNER JOIN adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario INNER JOIN Localidades AS LA ON NA.IdLocal=LA.IdLocal
 INNER JOIN Departamentos AS DE ON LA.IdDep=DE.IdDep
 INNER JOIN Empleados AS E ON D.IdEmpleado=E.IdEmpleado INNER JOIN Fondos AS FP ON D.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON D.IdFonSal=FS.IdFondo INNER JOIN Fondos AS FR ON D.IdFonRie=FR.IdFondo
 INNER JOIN Fondos  AS FC ON D.IdCajCom=FC.IdFondo INNER JOIN Terceros AS TFP ON FP.IdTercero=TFP.IdTercero
 INNER JOIN Terceros AS TFS ON FS.IdTercero=TFS.IdTercero  INNER JOIN Terceros AS TFR ON FR.IdTercero=TFR.IdTercero
 INNER JOIN Terceros AS TFC ON FC.IdTercero=TFC.IdTercero  INNER JOIN Instalaciones AS I ON D.IdInstala=I.IdInstala
 INNER JOIN Emp_Contrato AS C ON D.NContrato=C.NContrato  INNER JOIN Dependencias AS DP ON C.IdDep=DP.IdDep 
 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 TiposCot AS TCZ ON C.IdCot=TCZ.IdCot
 INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf  INNER JOIN EstadoCiv AS EC ON E.IdEstCivil=EC.IdEstado
 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 
 LEFT JOIN GruposAR AS GAR ON C.IdGrupo=GAR.IdGrupo
 LEFT JOIN Trn_NomAlqNov AS DN ON D.TipoLiq=DN.TipoLiq AND D.Numero=DN.Numero AND D.Item=DN.Item
 LEFT JOIN Fondos AS F ON A.CodFondo=F.IdFondo LEFT JOIN Fondos AS FRP ON A.CodArp=FRP.IdFondo
 LEFT JOIN Companias AS CI ON A.CodCia=CI.IdCia LEFT JOIN Localidades AS LC ON CI.IdLocal=LC.IdLocal
 LEFT JOIN Fondos AS FNP ON DN.CodNewPen=FNP.IdFondo  LEFT JOIN Fondos AS FNS ON DN.CodNewSal=FNS.IdFondo
 LEFT JOIN Instalaciones AS NI ON DN.CodNewIns=NI.IdInstala LEFT JOIN ClaseRie AS CRN ON DN.CodNewRie=CRN.IdClase
 LEFT JOIN GruposAR AS NGR ON DN.CodNewGru=NGR.IdGrupo
 WHERE A.TipoLiq=@pmTipoLiq AND A.Numero LIKE ISNULL(@pmNumero,'%') AND PerAutoliq LIKE ISNULL(@pmPerAutoliq,'%')
 ORDER BY A.Numero,Apellidos,Nombres
GO