if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTarifAnticipos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTarifAnticipos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTraManifAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsTraManifAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifAnticipos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTarifAnticipos]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTarifAnticiposLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTarifAnticiposLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_KdexSal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_KdexSal]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifAnexo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifAnexo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoFmt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoFmt]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifiestoRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifiestoRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTarifAnticipos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpTarifAnticipos]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,IdRutaFle,RF.Ruta AS RutaFletes
,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrDescuento,M.VrAnticipo,M.VrAntAdic,M.VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,M.TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,M.BaseRet,M.TarifaRet,M.TarifaIca
,M.IdLocFletes,CF.Localidad AS LugarFletes,M.FechaPago,M.PagoCargue,M.PagoDescargue,NumMintrans,EdoMintrans,M.Remesa AS NumRemesa,M.IdCiaRem,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,OP.NumCheque AS EgrNumCheque,ISNULL(TotalEgresos,0) AS TotalEgresos,M.VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.Anulado,M.FecDev
,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom,TimeSys,FecUpdate,IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino,LugarFletes,NumAnticipo,MA.NumCheque,TipoMintrans,MucMintrans,ContIntegral,PuntosRuta,MA.CantViajes,VolumenCarga,ISNULL(Num_Radica,0) AS NumRadicac,MA.TipoValPacto
,Transbordo,NumMucAnu,CiaMucAnu
,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
--Datos del vehiculo
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia,V.IdGrupo AS CdGrupoPro,GrupoProp,CdCondRelev,CRV.RazonSocial AS SegConductor
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND M.IdCia=MA.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
 LEFT JOIN TercCndtores AS CT ON M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Terceros AS CRV ON M.CdCondRelev=CRV.IdTercero
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
 FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
 WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EG ON M.TipOdp=EG.TipOrden AND M.OrdPago=EG.NumOPago AND M.IdCiaOdp=EG.CdCiaOpago
 --radicacion de cumplidos
 LEFT JOIN (SELECT TipMuc,Manifiesto,IdCiaMuc,MAX(NumRadica) AS Num_Radica FROM Trn_TraCumRadica GROUP BY TipMuc,Manifiesto,IdCiaMuc) AS RC ON M.TipDoc=RC.TipMuc AND M.Manifiesto=RC.Manifiesto AND M.IdCia=RC.IdCiaMuc
--Condiciones consulta general
 WHERE M.TipDoc=@pmTipDoc AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoFmt] @pmTipDoc VARCHAR(3),@pmManifiestoIni INT,@pmManifiestoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT M.TipDoc AS CdTipDoc,TipoDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,CN.Compania AS NomCompania,M.Fecha AS FechaMuc,M.FecDespacho,M.FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,RT.Ruta AS DescRuta,IdRutaFle,RF.Ruta AS RutaFletes
,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,M.nRemolque,M.TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,M.VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,BaseRet,TarifaRet,TarifaIca
,M.IdLocFletes,CF.Localidad AS CiuLugarFletes,FechaPago,PagoCargue,PagoDescargue,NumMintrans,EdoMintrans,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,M.EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,FechaOdp,EstOrden,VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.OrigenAdd,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom
,M.TimeSys,M.FecUpdate,M.IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario,Leyenda,kmsTotal,VrFleteTon,MA.TipoRuta AS MucTipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,MA.IdMneda,MA.VrTasa,MA.NomRemite AS NomRmtente,MA.NomDestino AS NomDestnatario
,MA.LugarFletes AS Lugar_Fletes,NumAnticipo,NumCheque,MA.TipoMintrans AS TipoMucMintrans,MucMintrans,ContIntegral,VolumenCarga,MA.NumPoliza AS NumPolizaMuc,DescFirma,CodEmpresa,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,MA.MvoAnulacion,MvoSuspension
,PuntosRuta,MA.CantViajes,Transbordo,NumMucAnu,CiaMucAnu,MA.AceptaFirma,MA.TipoValPacto
--detalles
,MR.Item AS ItemMuc,TipRem,MR.Remesa AS NumRemesa,MR.IdCiaRem AS CdCiaRem,ItemRem,D.IdMercancia AS CdMercancia,DescripMcias,CodigoMcia,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol
,Cases,Cajas,Palets,NitRemite,Remitente,DirOrigen,D.IdOrigen AS RemCodOrigen,LO.Localidad AS RemCiudadOrigen,LO.IdDep AS RemCdepOrigen,DOR.Departamento AS RemDptoOrigen,NitDestntario,Destinatario,DirDestino
,D.IdDestino AS RemCodDestino,LD.Localidad AS RemCiudadDestino,LD.IdDep AS RemCdepDestino,DDN.Departamento AS RemDptoDestino,TarifClie,MR.TarifTabla AS TarifaTabla,MR.TarifPago AS TarifaPago,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro
,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,DA.Agencia AS DetAgencia
,D.Cumplido AS RemCumplido,D.IdCiaCump AS RemCiaCump,D.FechaCump AS RemFechaCum,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,D.TipOdp AS RemTipoOdp,D.NumeroOdp AS RemNumOdp,D.IdCiaOdp AS RemCiaOdp,TarifOdp,PesoCont,MR.RemMintrans AS RemMucMintrans,EdoRemMin
,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.IdMnjo AS CdMnjo,ManejoMcia,D.IdTmcia AS CdTmcia,TipoMcia,CdRango,DescripRango,CodBodDtno,TipTraslado,CodigoUN
,HorasCargue,HorasDesc,FecInicioCargue,FecFinCargue,FecIniciodesc,FecFindesc,TiempoCargue,TiempoDesc,FecLlegCargue,FecSalidaCargue,FecLlegdesc,FecSalidadesc
,IdCliente,CL.RazonSocial AS RemNomCliente,R.IdClieFact AS RemIdPropMcia,PM.RazonSocial AS RemPropMcia
--Datos del poseedor
,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
--Datos del vehiculo
,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,V.Modelo,V.Config
,V.PesoVacio,V.PesoMax,V.NumMotor,V.SerieChasis,V.NumSerie,V.CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion,RQ.PesoKg AS PesoVacioRq
,CT.CertJudicial,CT.FecJudicial,CT.VigJudicial,CT.Licencia,CT.CatLicencia,CT.IdLugar AS CdLugar,EL.LugarLic,CT.FecLicencia,CT.VigLicencia,CDT.Direccion AS CdtDireccion,CDT.Telefono AS CdtTelefono,CDT.TelMovil AS CdtMovil,CDT.e_mail AS CdtEmail
,LC.Localidad AS CdtCiudad,DC.Departamento AS CdtDpto
,CdCondRelev,CRV.RazonSocial AS SegConductor,CTR.CertJudicial  AS CertJudicialRelev,CTR.FecJudicial AS FecJudicialRelev,CTR.VigJudicial AS VigJudicialRelev,CTR.Licencia AS LicenciaRelev,CTR.CatLicencia AS CatLicenciaRelev
,CTR.IdLugar AS CdLugarRelev,ELR.LugarLic AS LugarLicRelev,CTR.FecLicencia AS FecLicenciaRelev,CTR.VigLicencia AS VigLicenciaRelev,CRV.Direccion AS CdtDireccionRelev,CRV.Telefono AS CdtTelefonoRelev,CRV.e_mail AS CdtEmailRelev
,CRV.TelMovil AS CdtMovilRelev,LCR.Localidad AS CdtCiudadRelev, DCR.Departamento AS CdtDptoRelev
 FROM Trn_TraManifiesto AS M INNER JOIN  Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND  M.IdCia=MA.IdCia
 INNER JOIN Trn_TraManifRem AS MR ON M.TipDoc=MR.TipDoc AND  M.Manifiesto=MR.Manifiesto AND  M.IdCia=MR.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON MR.TipRem=D.TipDoc  AND MR.Remesa=D.NumOrden AND MR.IdCiaRem=D.IdCia AND MR.ItemRem=D.Item
 INNER JOIN Trn_TraRemesa AS R ON MR.TipRem=R.TipDoc AND MR.Remesa=R.NumOrden AND MR.IdCiaRem=R.IdCia 
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia INNER JOIN Sys_TiposDoc AS TD ON M.TipDoc=TD.IdDoc
 INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS RT ON M.IdRuta=RT.IdRuta  INNER JOIN Terceros AS CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Mercancias AS MCA ON D.IdMercancia=MCA.IdMercancia
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DOR ON LO.IdDep=DOR.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DDN ON LD.IdDep=DDN.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Empaques AS EMP ON D.IdEmp=EMP.IdEmp
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat INNER JOIN TiposMnjo AS MJ ON D.IdMnjo=MJ.IdMnjo
 INNER JOIN TiposMcia AS TM ON D.IdTmcia=TM.IdTmcia 
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS COL ON V.IdColor=COL.IdColor  INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Terceros AS CL ON R.IdCliente=CL.IdTercero LEFT JOIN Terceros AS PM ON R.IdClieFact=PM.IdTercero
 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 M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Agencias AS DA ON D.CdAgencia=DA.IdAgencia  LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango    LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta
 LEFT JOIN Polizas AS PS ON MA.NumPoliza=PS.NumPoliza
 LEFT JOIN Trn_TraRemAnexo AS RA ON MR.TipRem=RA.TipDoc AND MR.Remesa=RA.NumOrden AND MR.IdCiaRem=RA.IdCia
 LEFT JOIN VehRemolq AS RQ ON M.nRemolque=RQ.IdRemque
 LEFT JOIN Terceros AS CRV ON M.CdCondRelev=CRV.IdTercero
 LEFT JOIN Localidades AS LCR ON CRV.IdLocal=LCR.IdLocal LEFT JOIN Departamentos AS DCR ON LCR.IdDep=DCR.IdDep
 LEFT JOIN TercCndtores AS CTR ON M.CdCondRelev=CTR.IdConductor LEFT JOIN ExpLicencias AS ELR ON CTR.IdLugar=ELR.IdLugar
 LEFT JOIN Localidades AS LC ON CDT.IdLocal=LC.IdLocal LEFT JOIN Departamentos AS DC ON LC.IdDep=DC.IdDep
 WHERE M.TipDoc=@pmTipDoc AND M.Manifiesto BETWEEN @pmManifiestoIni AND @pmManifiestoFin AND M.IdCia=@pmIdCia
 ORDER BY M.Manifiesto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT M.TipDoc AS CdTipDoc,M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,M.Fecha,M.FecDespacho,FecEntrega,M.IdOrigen AS CdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,M.IdDestino AS CdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,M.IdRuta AS CdRuta,R.Ruta AS DescRuta,IdRutaFle,RF.Ruta AS RutaFletes
,M.IdVehiculo AS PlacaVeh,M.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,NP.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,T.RazonSocial AS Poseedor
,M.VrFletes,M.VrRetencion,M.VrReteIca,M.VrDescuento,M.VrAnticipo,M.VrAntAdic,M.VrNeto,M.VrPagos,M.VrCargos,M.VrDctos,M.TarifaFlete,M.Cantidad AS CantTotal,M.PesoTotal,M.BaseRet,M.TarifaRet,M.TarifaIca
,M.IdLocFletes,CF.Localidad AS LugarFletes,M.FechaPago,M.PagoCargue,M.PagoDescargue,NumMintrans,EdoMintrans,M.Cumplido AS NumCumplido,M.IdCiaCump AS CdCiaCump,M.FechaCump AS FecCumplido,EstCumplido
,M.TipOdp AS TipoOdp,M.OrdPago AS NumOrdPago,M.IdCiaOdp AS CdCiaOdp,M.FechaOdp,M.EstOrden,OP.TipEgr,OP.Egreso,OP.IdCiaEgr,OP.NumCheque AS EgrNumCheque,ISNULL(TotalEgresos,0) AS TotalEgresos
,M.VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,M.Anulado,M.FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.TipCom,M.Comprobante,M.IdCiaCom,M.TimeSys,M.FecUpdate,M.IdCiaCrea
,M.IdUsuario AS CdUsuario,Usuario,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino,LugarFletes,NumAnticipo,MA.NumCheque,TipoMintrans,MucMintrans,ContIntegral,PuntosRuta,VolumenCarga
,CdCondRelev,CRV.RazonSocial AS SegConductor,ISNULL(Num_Radica,0) AS NumRadicac,MA.TipoValPacto,Transbordo,NumMucAnu,CiaMucAnu
--detalles
,MR.Item AS ItemMuc,TipRem,MR.Remesa AS NumRemesa,MR.IdCiaRem AS CdCiaRem,ItemRem,D.IdMercancia AS CdMercancia,DescripMcias,CodigoMcia,D.Cantidad AS Cant,PesoNeto,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,dmsAlto,dmsAncho,dmsLargo,D.Volumen,UndVol
,D.Cases,D.Cajas,D.Palets,D.NitRemite,Remitente,DirOrigen,D.IdOrigen AS CodCiuOrigen,LO.Localidad AS CiudadOrigen,NitDestntario,Destinatario,DirDestino,D.IdDestino AS CodCiuDestino,LD.Localidad AS CiudadDestino,TarifClie,MR.TarifTabla AS TarifaTabla,MR.TarifPago AS TarifaPago
,D.VrDeclarado AS DetVrDeclarado,D.VrSeguro AS DetVrSeguro,TarifSeguro,UndTarifa,UndTarifPago,Remision,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,D.CdAgencia AS DetCdAgencia,D.Cumplido AS NumCumplidoRem,D.IdCiaCump AS CdCiaCumpRem
,D.FechaCump AS FecCumRem,DetalleCump,CantidadCump,PesoCump,VolCump,CasesCump,CajasCump,PaletsCump,TarifCump,PagoCump,UndTarCump,UndTarPagoCump
,TipFac,Factura,IdCiaFac,FechaFac,TarifClieFac,D.TipOdp AS DetTipOdp,D.NumeroOdp AS NumOdp,D.IdCiaOdp AS CdCiaOdp,TarifOdp,PesoCont,RemMintrans,EdoRemMin
,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.IdMnjo AS CdMnjo,ManejoMcia,D.IdTmcia AS CdTmcia,TipoMcia,CdRango,DescripRango
--Datos del vehiculo
,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
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdLinea AS CdLinVeh,LineaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora
,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia,V.IdGrupo AS CdGrupoPro,GrupoProp
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS MA ON M.TipDoc=MA.TipDoc AND  M.Manifiesto=MA.Manifiesto AND  M.IdCia=MA.IdCia
 INNER JOIN Companias AS CN ON M.IdCia=CN.IdCia  INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS CO ON  M.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON M.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON M.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON M.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS NP ON M.IdPropietario=NP.IdTercero   INNER JOIN Terceros AS T ON M.IdPoseedor=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Localidades AS CF ON M.IdLocFletes=CF.IdLocal
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo  INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN MarcasLin AS LV ON V.IdLinea=LV.IdLinea 
 INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor INNER JOIN TiposCar AS TC ON V.IdCrceria=TC.IdCrceria 
 INNER JOIN TiposPpt AS TPR ON V.IdPpd=TPR.IdPpd
 INNER JOIN Trn_TraManifRem AS MR ON M.TipDoc=MR.TipDoc AND  M.Manifiesto=MR.Manifiesto AND  M.IdCia=MR.IdCia
 INNER JOIN Trn_TraRemMcias AS D ON MR.TipRem=D.TipDoc  AND MR.Remesa=D.NumOrden AND MR.IdCiaRem=D.IdCia AND MR.ItemRem=D.Item
 INNER JOIN Mercancias AS MCA ON D.IdMercancia=MCA.IdMercancia
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal   INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Empaques AS EMP ON D.IdEmp=EMP.IdEmp
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat INNER JOIN TiposMnjo AS MJ ON D.IdMnjo=MJ.IdMnjo
 INNER JOIN TiposMcia AS TM ON D.IdTmcia=TM.IdTmcia 
 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 M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN Rutas AS  RF ON M.IdRutaFle=RF.IdRuta  LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN RangosPeso AS RP ON D.CdRango=RP.IdRango  LEFT JOIN GruposPro AS GP ON V.IdGrupo=GP.IdGrupo
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN Terceros AS CRV ON M.CdCondRelev=CRV.IdTercero
 LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
 FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
 WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EG ON M.TipOdp=EG.TipOrden AND M.OrdPago=EG.NumOPago AND M.IdCiaOdp=EG.CdCiaOpago
 --radicacion de cumplidos
 LEFT JOIN (SELECT TipMuc,Manifiesto,IdCiaMuc,MAX(NumRadica) AS Num_Radica FROM Trn_TraCumRadica GROUP BY TipMuc,Manifiesto,IdCiaMuc) AS RC ON M.TipDoc=RC.TipMuc AND M.Manifiesto=RC.Manifiesto AND M.IdCia=RC.IdCiaMuc
--Condiciones consulta general
 WHERE M.TipDoc=@pmTipDoc AND  M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND M.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY M.IdCia,M.Manifiesto
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifAnexo] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Manifiesto,IdCia,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa
,NomRemite,NomDestino,LugarFletes,NumAnticipo,NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga
,NumPoliza,CdBodDtno,TipoTrslado,MvoAnulacion,MvoSuspension,WsSeguro,NumRadSeguro,PuntosRuta,CantViajes,Transbordo,NumMucAnu,CiaMucAnu,AceptaFirma,TipoValPacto
 FROM Trn_TraManifAnexo
 WHERE TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTraManifAnexo] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2),@pmkmsTotal DECIMAL(14,4),@pmVrFleteTon MONEY,@pmTipoRuta VARCHAR(10)
,@pmCdLocTrao VARCHAR(8),@pmCdLocTrad VARCHAR(8),@pmKmsTraOri DECIMAL(14,4),@pmKmsTraDes DECIMAL(14,4),@pmIdMneda VARCHAR(5),@pmVrTasa DECIMAL(14,4)
,@pmNomRemite VARCHAR(150),@pmNomDestino VARCHAR(150),@pmLugarFletes VARCHAR(100),@pmNumAnticipo INT,@pmNumCheque VARCHAR(20),@pmTipoMintrans VARCHAR(10)
,@pmMucMintrans INT,@pmContIntegral INT,@pmVolumenCarga DECIMAL(14,4),@pmNumPoliza VARCHAR(30),@pmCdBodDtno VARCHAR(4),@pmTipoTrslado INT,@pmMvoAnulacion VARCHAR(5),@pmMvoSuspension VARCHAR(5)
,@pmWsSeguro BIT,@pmNumRadSeguro DECIMAL(18,2),@pmPuntosRuta DECIMAL(14,4),@pmCantViajes INT,@pmTransbordo INT,@pmNumMucAnu INT,@pmCiaMucAnu CHAR(2),@pmAceptaFirma BIT,@pmTipoValPacto VARCHAR(10)
 AS 
 INSERT INTO Trn_TraManifAnexo (TipDoc,Manifiesto,IdCia,kmsTotal,VrFleteTon,TipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,VrTasa,NomRemite,NomDestino
,LugarFletes,NumAnticipo,NumCheque,TipoMintrans,MucMintrans,ContIntegral,VolumenCarga,NumPoliza,CdBodDtno,TipoTrslado,MvoAnulacion,MvoSuspension,WsSeguro,NumRadSeguro,PuntosRuta,CantViajes,Transbordo,NumMucAnu,CiaMucAnu,AceptaFirma,TipoValPacto)
 VALUES (@pmTipDoc,@pmManifiesto,@pmIdCia,@pmkmsTotal,@pmVrFleteTon,@pmTipoRuta,@pmCdLocTrao,@pmCdLocTrad,@pmKmsTraOri,@pmKmsTraDes,@pmIdMneda,@pmVrTasa
,@pmNomRemite,@pmNomDestino,@pmLugarFletes,@pmNumAnticipo,@pmNumCheque,@pmTipoMintrans,@pmMucMintrans,@pmContIntegral,@pmVolumenCarga,@pmNumPoliza,@pmCdBodDtno,@pmTipoTrslado,@pmMvoAnulacion,@pmMvoSuspension,@pmWsSeguro
,@pmNumRadSeguro,@pmPuntosRuta,@pmCantViajes,@pmTransbordo,@pmNumMucAnu,@pmCiaMucAnu,@pmAceptaFirma,@pmTipoValPacto)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraManifiestoLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdOrigen VARCHAR(8)=Null,@pmIdDestino VARCHAR(8)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT M.Manifiesto AS NumManif,M.IdCia AS CdCia,Compania,Fecha,FecDespacho,FecEntrega,IdOrigen,LO.Localidad AS CiudadOrigen,IdDestino,LD.Localidad AS CiudadDestino,IdRuta,IdRutaFle,M.IdVehiculo AS PlacaVeh,NumVeh
,M.IdConductor AS CedConductor,NC.RazonSocial AS Conductor,nRemolque,TipoAfiVehic,M.IdPropietario AS NitPropietario,T.RazonSocial AS Propietario,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS Poseedor
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,Cantidad,PesoTotal,VolumenCarga,BaseRet,TarifaRet,TarifaIca,CodTarRet
,CodTarIca,IdLocFletes,LugarFletes,FechaPago,PagoCargue,PagoDescargue,NomRemite,NomDestino,NumMintrans,EdoMintrans,Remesa,IdCiaRem,Cumplido,IdCiaCump,FechaCump,EstCumplido
,TipOdp,OrdPago,IdCiaOdp,FechaOdp,EstOrden,VrFleteNeto,TipInfVia,NumViaje,CiaInfViaje,CdCatPeaje,NumAnticipo,NumCheque,TipoMintrans,TipoRuta,MucMintrans,PuntosRuta,AceptaFirma,A.TipoValPacto,CdCondRelev,A.CantViajes
,OrigenAdd,Anulado,FecDev,Transbordo,NumMucAnu,CiaMucAnu,TipCom,Comprobante,IdCiaCom,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
 FROM Trn_TraManifiesto AS M INNER JOIN Trn_TraManifAnexo AS A ON M.TipDoc=A.TipDoc AND M.Manifiesto=A.Manifiesto AND M.IdCia=A.IdCia
 INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo INNER JOIN Companias AS CI ON M.IdCia=CI.IdCia
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero  INNER JOIN Terceros AS T ON M.IdPropietario=T.IdTercero
 INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 INNER JOIN EstadoDoc AS ED ON M.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario
 WHERE M.TipDoc=@pmTipDoc AND  Fecha BETWEEN @pmFechaIni AND @pmFechaFin  AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND IdRuta LIKE ISNULL(@pmIdRuta,'%')  AND IdOrigen LIKE ISNULL(@pmIdOrigen,'%') AND IdDestino LIKE ISNULL(@pmIdDestino,'%')
 AND M.IdVehiculo  LIKE ISNULL(@pmIdVehiculo ,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
 ORDER BY M.IdCia,M.Manifiesto
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_KdexSal] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmIdProducto,DescripProd,tmIdBodega,Bodega,tmCdTanque,tmSalidas,tmVrUnitario
,tmSalidas*tmVrUnitario AS CostoTotal,tmVrPrecio,tmSalidas*tmVrPrecio AS VrTotal,tmTarifaIva,tmVrIva
,tmUnidades,tmIdUnd,Unidad,tmReferencia,tmDescripcion,tmCdCCosto,tmCdSubCos,tmIdTercero,tmpVehiculo
,tmVrBruto,tmNumLote,tmFecVceLote,Tanques,tmServcios,tmEsCombo,tmTipDoc,tmDocumento,tmIdCia,tmRemision,tmIdCiaRem
,tmCotizacion AS ItemReq,tmVrImvCosto
 FROM  tm_Kdex AS K INNER JOIN ProdMcias AS P ON K.tmIdProducto=P.IdProducto 
 INNER JOIN Bodegas AS B ON K.tmIdBodega=B.IdBodega
 LEFT JOIN UndMed AS U ON K.tmIdUnd=U.IdUnd
 WHERE  tmNumero=@pmtmNumero AND tmEsProdBase=0
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTarifAnticiposLta] @pmTipoCuota VARCHAR(10)=Null,@pmTipoAplica VARCHAR(10)=Null,@pmInactivo BIT=Null
 AS 
 SELECT Numero,TipoAplica,CuotaMax,TipoCuota,CdRuta,CdOrigen,O.Localidad AS CiuOrigen,CdDestino,D.Localidad AS CiuDestino
,NitCliente,RazonSocial,CdTipoVeh,TipoVehiculo,TipoAfilVeh,A.Inactivo AS Inactvo,FechaCrea,A.IdUsuario AS CdUsuario,Usuario
,O.IdDep AS CdDep,DPO.Departamento AS DptoOrigen,D.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,NitPropietario,Tipo_Egreso
 FROM TarifAnticipos AS A INNER JOIN  adm_Usuarios AS U ON A.IdUsuario=U.IdUsuario
 LEFT JOIN Localidades AS O ON A.CdOrigen=O.IdLocal LEFT JOIN Localidades AS D ON  A.CdDestino=D.IdLocal
 LEFT JOIN Departamentos AS DPO ON O.IdDep=DPO.IdDep LEFT JOIN Departamentos AS DPD ON D.IdDep=DPD.IdDep
 LEFT JOIN Terceros AS TC ON A.NitCliente=TC.IdTercero LEFT JOIN TiposVeh AS TV ON A.CdTipoVeh=TV.IdTipoVeh
 WHERE  TipoCuota LIKE ISNULL(@pmTipoCuota,'%') AND  TipoAplica LIKE ISNULL(@pmTipoAplica,'%')
 AND  (A.Inactivo=ISNULL(@pmInactivo,0) or A.Inactivo=ISNULL(@pmInactivo,1))
 ORDER BY Numero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpTarifAnticipos] @pmNumero INT,@pmCuotaMax DECIMAL(14,4),@pmTipoCuota VARCHAR(10),@pmTipoAplica VARCHAR(10)
,@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmNitCliente VARCHAR(16),@pmCdTipoVeh VARCHAR(4)
,@pmTipoAfilVeh VARCHAR(10),@pmInactivo BIT,@pmCdRuta VARCHAR(4),@pmNitPropietario VARCHAR(16),@pmTipo_Egreso VARCHAR(20)
 AS 
 UPDATE TarifAnticipos SET CuotaMax=@pmCuotaMax,TipoCuota=@pmTipoCuota,TipoAplica=@pmTipoAplica,CdOrigen=@pmCdOrigen,CdDestino=@pmCdDestino
,NitCliente=@pmNitCliente,CdTipoVeh=@pmCdTipoVeh,TipoAfilVeh=@pmTipoAfilVeh,CdRuta=@pmCdRuta,Inactivo=@pmInactivo,NitPropietario=@pmNitPropietario
,Tipo_Egreso=@pmTipo_Egreso
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTarifAnticipos] @pmNumero INT
 AS 
 SELECT Numero,CuotaMax,TipoCuota,TipoAplica,CdOrigen,CdDestino,CdRuta,NitCliente
,CdTipoVeh,TipoAfilVeh,Inactivo,NitPropietario,Tipo_Egreso,FechaCrea,IdUsuario
 FROM TarifAnticipos
 WHERE Numero=@pmNumero
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsTarifAnticipos] @pmNumero INT,@pmCuotaMax DECIMAL(14,4),@pmTipoCuota VARCHAR(10),@pmTipoAplica VARCHAR(10)
,@pmCdOrigen VARCHAR(8),@pmCdDestino VARCHAR(8),@pmNitCliente VARCHAR(16),@pmCdTipoVeh VARCHAR(4),@pmTipoAfilVeh VARCHAR(10)
,@pmInactivo BIT,@pmCdRuta VARCHAR(4),@pmNitPropietario VARCHAR(16),@pmTipo_Egreso VARCHAR(20),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11) 
 AS 
 INSERT INTO TarifAnticipos (Numero,CuotaMax,TipoCuota,TipoAplica,CdOrigen,CdDestino,CdRuta,NitCliente,CdTipoVeh,TipoAfilVeh,Inactivo,FechaCrea,IdUsuario,NitPropietario,Tipo_Egreso)
 VALUES (@pmNumero,@pmCuotaMax,@pmTipoCuota,@pmTipoAplica,@pmCdOrigen,@pmCdDestino,@pmCdRuta,@pmNitCliente,@pmCdTipoVeh,@pmTipoAfilVeh,@pmInactivo,@pmFechaCrea,@pmIdUsuario,@pmNitPropietario,@pmTipo_Egreso)
GO
--agosto 5
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNERecord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNERecord]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNERecordDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNERecordDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNomina_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsNomina_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNERecordLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNERecordLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomina]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomina_Cpn]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomina_Cpn]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNomina_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNomina_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNominaDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryNominaDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNomina]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpNomina]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomina_Cr]  @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30)=Null,@pmIdEmpleado VARCHAR(16)=Null,@pmIdNom VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null
,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
 AS 
 SELECT  N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumContrato,N.IdPeriodo AS IdPerdo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdDep AS CodDep,Dependencia,N.IdInstala AS CodInst,Instlacion,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcen,SubCosto,N.IdNom AS CodNom,TipoNomina,Asistencia,NoDevenga
,TipoLiq,N.VrSalario AS VrSalBasico,N.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS Nom_IdEstdo,EN.Estado AS Nom_Estado,EsRetiro,NLiqRet
,N.CantModifica,N.NE_Estado,N.NE_Prefijo,N.NE_Numero,N.NE_FecEnvio,N.NE_CUNE,N.NE_QR,N.KeyRegistro AS KeyRegstro,N.NE_ComAju,N.NE_CntAju,N.NE_Ajustes,N.TimeSys,N.FecUpdate AS FecUpd,N.IdUsuario AS IdUsuari,Usuario
--datos detalle de nomina
,Item,D.IdConcepto AS CodConcepto,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS TarifaBase,DiasCalc,DiasNov,CodFondo,FD.Fondo AS Det_Fondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq,ClaseCon
--Datos empleado
,Codigo,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,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
,NumVeh,TipoAdmon,VehPropio,VrATransMes
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON N.IdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EN ON N.IdEstado=EN.IdEstado 
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf   INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco  INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo  INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 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 Fondos AS FD ON D.CodFondo=FD.IdFondo
 LEFT JOIN NomVehiculos AS VH ON N.pVehiculo=VH.IdVehiculo
 WHERE N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')  AND N.KeyRegistro LIKE ISNULL(@pmKeyRegistro,'%') AND N.IdNom like ISNULL(@pmIdNom,'%')
 AND  N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-1)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 ORDER BY N.KeyRegistro,Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomina_Cpn]  @pmIdPeriodo VARCHAR(8),@pmMes VARCHAR(6),@pmKeyRegistro VARCHAR(30)=Null,@pmIdEmpleado VARCHAR(16)=Null
,@pmIdNom VARCHAR(4)=Null,@pmIdDep VARCHAR(4)=Null,@pmEsRetiro BIT=Null,@pmNLiqRet INT=Null
 AS 
 SELECT  N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,N.NContrato AS NumContrato,N.IdPeriodo AS IdPerdo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido
,N.IdDep AS CodDep,Dependencia,N.IdInstala AS CodInst,Instlacion,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcen,SubCosto,N.IdNom AS CodNom,TipoNomina,Asistencia,NoDevenga
,TipoLiq,N.VrSalario AS VrSalBasico,N.pVehiculo AS PlacaVeh,TipCom,Comprobante,IdCiaCom,N.Observacion AS Observ,N.IdEstado AS Nom_IdEstdo,EN.Estado AS Nom_Estado,EsRetiro,NLiqRet
,N.CantModifica,N.NE_Estado,N.NE_Prefijo,N.NE_Numero,N.NE_FecEnvio,N.NE_CUNE,N.NE_QR,N.KeyRegistro AS KeyRegstro,N.NE_ComAju,N.NE_CntAju,N.NE_Ajustes,N.TimeSys,N.FecUpdate AS FecUpd,N.IdUsuario AS IdUsuari,Usuario
--datos detalle de nomina
,Item,D.IdConcepto AS CodConcepto,Concepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,D.Unidad AS Undad,ClaseLiq,VrOrigen,D.Tarifa AS TarifaBase,DiasCalc,DiasNov,CodFondo,FD.Fondo AS Det_Fondo
,NPrestamo,NCuota,OrigCargue,VrBaseLiq,ClaseCon
--Datos empleado
,Codigo,IdLugarCed,Localidad,E.Direccion AS Dirccion,Telefono,TelMovil,e_mail,E.IdProf AS CodProf,Profesion,E.IdClase AS CodClaCta,ClaseCuenta,NumCuenta,E.IdBanco AS CodBanco,Banco,IdFonPen,FP.Fondo AS FondoPen
,IdFonEps,FS.Fondo AS FondoEps,IdFonArp,FR.Fondo AS FondoArp,IdCajaCom,CC.Fondo AS CajaComp,EsCondtor,FecNacmto,Licencia,CatLicencia,VigLicencia,E.IdLugar AS CodLugar,LugarLic,TallaCam,TallaPan,TallaZap,E.Inactivo AS Emp_Inactvo
--datos del contrato
,C.IdTipCon AS CodTipCon,TipoContrato,FecIngreso,FecRetiro,Indefinido,C.IdCargo AS CodCargo,Cargo,C.IdArea AS CodArea,Area,C.IdClase AS CodClaRie,ClaseRiesgo,CR.Tarifa AS TarifaRie,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
,NumVeh,TipoAdmon,VehPropio,VrATransMes
 FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
 INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado INNER JOIN  Emp_Contrato AS C ON N.NContrato=C.NContrato
 INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep  INNER JOIN Instalaciones AS I ON N.IdInstala=I.IdInstala
 INNER JOIN CentroCosto AS CO ON N.IdCCosto=CO.IdCCosto  INNER JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom INNER JOIN EstadoDoc AS EN ON N.IdEstado=EN.IdEstado 
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto INNER JOIN Localidades AS L ON E.IdLugarCed=L.IdLocal
 INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf   INNER JOIN ClaseCta AS CT ON E.IdClase=CT.IdClase
 INNER JOIN Bancos AS B ON E.IdBanco=B.IdBanco  INNER JOIN Fondos AS FP ON E.IdFonPen=FP.IdFondo
 INNER JOIN Fondos AS FS ON E.IdFonEps=FS.IdFondo  INNER JOIN Fondos AS FR ON E.IdFonArp=FR.IdFondo
 INNER JOIN Fondos AS CC ON E.IdCajaCom=CC.IdFondo INNER JOIN ExpLicencias AS EL ON E.IdLugar=EL.IdLugar
 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 Fondos AS FD ON D.CodFondo=FD.IdFondo
 LEFT JOIN NomVehiculos AS VH ON N.pVehiculo=VH.IdVehiculo
 WHERE  SUBSTRING(N.IdPeriodo,1,6)=@pmMes AND ClaseLiq IN ('NOMINA','NOVEDAD','LIQUDACION')
 AND N.IdPeriodo LIKE ISNULL(@pmIdPeriodo,'%')  AND N.KeyRegistro LIKE ISNULL(@pmKeyRegistro,'%') 
 AND N.IdNom like ISNULL(@pmIdNom,'%') AND  N.IdDep LIKE ISNULL(@pmIdDep,'%') AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%')
 AND (NLiqRet>=ISNULL(@pmNLiqRet,-1)  AND NLiqRet<=ISNULL(@pmNLiqRet,2147483647))
 AND (EsRetiro=ISNULL(@pmEsRetiro,0) or EsRetiro=ISNULL(@pmEsRetiro,1))
 ORDER BY N.KeyRegistro,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNERecordDet] @pmTipoReg VARCHAR(3),@pmNumRegistro INT,@pmIdCia CHAR(2),@pmItem INT,@pmIdConcepto VARCHAR(4),@pmDetalle VARCHAR(250),@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),@pmHoraInicial DATETIME,@pmHoraFinal DATETIME,@pmTipoIncLab VARCHAR(20)
 AS 
 INSERT INTO Trn_NERecordDet (TipoReg,NumRegistro,IdCia,Item,IdConcepto,Detalle,CantDevg,CantDed,VrUnitario,VrTotDevg,VrTotDed,Unidad,ClaseLiq,VrOrigen,Tarifa,DiasCalc,DiasNov,CodFondo,NPrestamo,NCuota,OrigCargue,VrBaseLiq,VrBasExceso,NitTercero,HoraInicial,HoraFinal,TipoIncLab)
 VALUES (@pmTipoReg,@pmNumRegistro,@pmIdCia,@pmItem,@pmIdConcepto,@pmDetalle,@pmCantDevg,@pmCantDed,@pmVrUnitario,@pmVrTotDevg,@pmVrTotDed,@pmUnidad,@pmClaseLiq,@pmVrOrigen,@pmTarifa,@pmDiasCalc,@pmDiasNov,@pmCodFondo,@pmNPrestamo,@pmNCuota,@pmOrigCargue,@pmVrBaseLiq,@pmVrBasExceso,@pmNitTercero,@pmHoraInicial,@pmHoraFinal,@pmTipoIncLab)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNERecordLta] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdPeriodo VARCHAR(8)=Null
AS 
SELECT N.TipoReg,N.NumRegistro,N.IdCia,N.Fecha,N.IdEmpleado,E.Apellidos,E.Nombres,N.NContrato,N.IdPeriodo,N.FecInicial,N.FecFinal
,N.Cantidad,N.CantNoRem,N.VrDevengado,N.VrDeducido,N.IdDep,Dependencia,N.IdInstala,IST.Instlacion,N.IdCCosto,CCosto,N.IdSubCos,SubCosto
,N.IdNom,TipoNomina,N.TipoLiq,N.VrSalario,N.VrATransMes,N.EsRetiro,N.NLiqRet,N.pVehiculo,N.FecIniPmd,N.FecFinPmd,N.TipCom,N.Comprobante,N.IdCiaCom
,N.CdTipoCot,N.SubTipo_Cot,N.Sal_Integral,N.TipoIdent,N.NotasAportante,N.Observacion,N.IdEstado,EN.Estado AS Nom_Estado,N.FechaCrea,N.IdUsuario,Usuario
,N.CantModifica,N.NE_Estado,N.NE_Prefijo,N.NE_Numero,N.NE_FecEnvio,N.NE_CUNE,N.NE_CntAju,N.NE_Ajustes,N.IdRegNom
--,N.DiasVac,N.NPeriodos,N.CantRem,N.VrRemndo,N.EsParcial,N.NumLiquida,N.NumSemLiq
,D.Item,D.IdConcepto,CN.Concepto,CN.ClaseCon,D.Detalle,D.CantDevg,D.VrUnitario,D.VrTotDevg,D.CantDed,D.VrTotDed,D.Unidad,D.ClaseLiq,D.VrOrigen,D.Tarifa,D.DiasCalc,D.DiasNov
,D.CodFondo,F.Fondo,D.NPrestamo,D.NCuota,D.OrigCargue,D.VrBaseLiq,D.VrBasExceso,D.NitTercero,T.RazonSocial,D.HoraInicial,D.HoraFinal,D.TipoIncLab
--datos del contrato
,E.Codigo,E.Direccion,E.Telefono,E.TelMovil,E.e_mail,E.IdProf,Profesion,C.IdCargo,Cargo,C.IdTipCon,TipoContrato,C.FecIngreso,C.FecRetiro,C.Indefinido
,C.FPnomina,C.ATFinMes,C.SegFinMes,C.IdEstado AS Con_IdEstado,ECO.Estado AS Con_Estado,C.Inactivo AS Con_Inactivo
FROM Trn_NERecord AS N INNER JOIN Trn_NERecordDet AS D ON N.TipoReg=D.TipoReg AND N.NumRegistro=D.NumRegistro AND N.IdCia=D.IdCia
INNER JOIN Empleados AS E ON N.IdEmpleado=E.IdEmpleado  INNER JOIN Emp_Contrato AS C ON N.NContrato=C.NContrato
INNER JOIN TiposNom AS TN ON N.IdNom=TN.IdNom 
INNER JOIN Dependencias AS DP ON N.IdDep=DP.IdDep INNER JOIN Instalaciones AS IST ON N.IdInstala=IST.IdInstala
INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS EN ON N.IdEstado=EN.IdEstado
INNER JOIN Profesiones AS P ON E.IdProf=P.IdProf INNER JOIN TiposCon AS TC ON C.IdTipCon=TC.IdTipCon
INNER JOIN Cargos AS CG ON C.IdCargo=CG.IdCargo INNER JOIN EstadoDoc AS ECO ON C.IdEstado=ECO.IdEstado
INNER JOIN NomConceptos AS CN ON D.IdConcepto=CN.IdConcepto
LEFT JOIN CentroCosto AS CO ON N.IdCCosto=CO.IdCCosto LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
LEFT JOIN Terceros AS T ON D.NitTercero=T.IdTercero LEFT JOIN Fondos AS F ON D.CodFondo=F.IdFondo
WHERE N.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND (N.IdPeriodo=@pmIdPeriodo OR @pmIdPeriodo IS NULL)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNominaDso] @pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16)=Null,@pmNContrato INT=Null,@pmIdNom VARCHAR(4)=Null
 AS 
 SELECT KeyRegistro,N.IdEmpleado AS IdEmpldo,Apellidos,Nombres,IdPeriodo,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido,N.NContrato AS NumContrato,N.IdNom AS CodNom,TipoNomina
,N.IdDep AS CodDep,Dependencia,N.IdCCosto AS CodCcto,CCosto,N.IdSubCos AS CodSubcos,SubCosto,N.IdInstala
,N.VrSalario,N.pVehiculo AS PlacaVeh,N.TipCom,N.Comprobante,N.IdCiaCom,N.Observacion AS Observ,N.IdEstado AS IdEstdo,Estado,N.EsRetiro,N.NLiqRet,N.TipoLiq
,N.VrATransMes,N.CdTipoCot,N.SubTipo_Cot,N.Sal_Integral,N.TipoIdent,N.NotasAportante,N.CantModifica,N.NE_Estado,N.NE_Prefijo,N.NE_Numero,N.NE_FecEnvio,N.NE_CUNE,N.NE_QR,N.NE_ComAju,N.NE_CntAju,N.NE_Ajustes
,N.IdUsuario AS IdUsuari,Usuario,N.TimeSys,N.FecUpdate AS FecUpdte
 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 D ON N.IdDep=D.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
 WHERE IdPeriodo=@pmIdPeriodo AND N.IdEmpleado LIKE ISNULL(@pmIdEmpleado,'%') AND N.IdNom LIKE ISNULL(@pmIdNom,'%')
 AND (N.NContrato>=ISNULL(@pmNContrato,0)  AND N.NContrato<=ISNULL(@pmNContrato,2147483647))
 ORDER BY  N.IdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInsNomina_Sel]  @pmIdEmpleado  VARCHAR(16),@pmNewCedula VARCHAR(16)
 AS 
 INSERT INTO Trn_Nomina (IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,VrDevengado,VrDeducido,NContrato,IdDep
,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem,TimeSys,IdUsuario
,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_QR,NE_ComAju,NE_CntAju,NE_Ajustes)
 SELECT  IdPeriodo,@pmNewCedula +CAST(NContrato AS VARCHAR(10)) +CASE EsRetiro WHEN 1 THEN '_1' ELSE '_0'  END,@pmNewCedula,FecInicial,FecFinal,Cantidad,VrDevengado
,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem
,TimeSys,IdUsuario,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_QR,NE_ComAju,NE_CntAju,NE_Ajustes
 FROM Trn_Nomina 
 WHERE IdEmpleado=@pmIdEmpleado
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmNContrato INT,@pmIdDep VARCHAR(4)
,@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrATransMes MONEY,@pmpVehiculo VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmEsRetiro BIT,@pmNLiqRet INT
,@pmTipoLiq CHAR(1),@pmCantNoRem DECIMAL(10,4),@pmCdTipoCot VARCHAR(4),@pmSubTipo_Cot VARCHAR(3),@pmSal_Integral BIT,@pmTipoIdent CHAR(1),@pmNotasAportante VARCHAR(250),@pmCantModifica INT,@pmNE_Estado INT,@pmNE_Prefijo VARCHAR(20),@pmNE_Numero INT,@pmNE_FecEnvio DATETIME
,@pmNE_CUNE VARCHAR(500),@pmNE_ComAju BIT,@pmNE_CntAju INT,@pmNE_Ajustes VARCHAR(500),@pmTimeSys SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmFecUpdate SMALLDATETIME
 AS 
 INSERT INTO Trn_Nomina (IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,VrDevengado,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,CantNoRem,TimeSys,FecUpdate,IdUsuario
,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_QR,NE_ComAju,NE_CntAju,NE_Ajustes)
 VALUES (@pmIdPeriodo,@pmKeyRegistro,@pmIdEmpleado,@pmFecInicial,@pmFecFinal,@pmCantidad,@pmVrDevengado,@pmVrDeducido,@pmNContrato,@pmIdDep,@pmIdInstala,@pmIdCCosto,@pmIdSubCos,@pmIdNom,@pmVrSalario,@pmVrATransMes,@pmpVehiculo,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado
,@pmEsRetiro,@pmNLiqRet,@pmTipoLiq,@pmCantNoRem,@pmTimeSys,@pmFecUpdate,@pmIdUsuario,@pmCdTipoCot,@pmSubTipo_Cot,@pmSal_Integral,@pmTipoIdent,@pmNotasAportante,@pmCantModifica,@pmNE_Estado,@pmNE_Prefijo,@pmNE_Numero,@pmNE_FecEnvio,@pmNE_CUNE,Null,@pmNE_ComAju,@pmNE_CntAju,@pmNE_Ajustes)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30),@pmIdEmpleado VARCHAR(16),@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmNContrato INT,@pmIdDep VARCHAR(4)
,@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrATransMes MONEY,@pmpVehiculo VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmEsRetiro BIT,@pmNLiqRet INT,@pmTipoLiq CHAR(1),@pmCantNoRem DECIMAL(10,4),@pmCdTipoCot VARCHAR(4),@pmSubTipo_Cot VARCHAR(3),@pmSal_Integral BIT,@pmTipoIdent CHAR(1),@pmNotasAportante VARCHAR(250),@pmCantModifica INT,@pmNE_Estado INT,@pmNE_Prefijo VARCHAR(20)
,@pmNE_Numero INT,@pmNE_FecEnvio DATETIME,@pmNE_CUNE VARCHAR(500),@pmNE_ComAju BIT,@pmNE_CntAju INT,@pmNE_Ajustes VARCHAR(500),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Nomina SET IdEmpleado=@pmIdEmpleado,FecInicial=@pmFecInicial,FecFinal=@pmFecFinal,Cantidad=@pmCantidad,VrDevengado=@pmVrDevengado,VrDeducido=@pmVrDeducido,NContrato=@pmNContrato,IdDep=@pmIdDep,IdInstala=@pmIdInstala,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,IdNom=@pmIdNom
,VrSalario=@pmVrSalario,VrATransMes=@pmVrATransMes,pVehiculo=@pmpVehiculo,TipCom=@pmTipCom,Comprobante=@pmComprobante,IdCiaCom=@pmIdCiaCom,Observacion=@pmObservacion,IdEstado=@pmIdEstado,EsRetiro=@pmEsRetiro,FecUpdate=@pmFecUpdate,NLiqRet=@pmNLiqRet,TipoLiq=@pmTipoLiq,CantNoRem=@pmCantNoRem
,CdTipoCot=@pmCdTipoCot,SubTipo_Cot=@pmSubTipo_Cot,Sal_Integral=@pmSal_Integral,TipoIdent=@pmTipoIdent,NotasAportante=@pmNotasAportante,CantModifica=@pmCantModifica,NE_Estado=@pmNE_Estado,NE_Prefijo=@pmNE_Prefijo,NE_Numero=@pmNE_Numero,NE_FecEnvio=@pmNE_FecEnvio,NE_CUNE=@pmNE_CUNE
,NE_ComAju=@pmNE_ComAju,NE_CntAju=@pmNE_CntAju,NE_Ajustes=@pmNE_Ajustes
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro=@pmKeyRegistro
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryNomina] @pmIdPeriodo VARCHAR(8),@pmKeyRegistro VARCHAR(30)
 AS 
 SELECT IdPeriodo,KeyRegistro,IdEmpleado,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido,NContrato,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom
,VrSalario,VrATransMes,pVehiculo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,TimeSys,FecUpdate,IdUsuario
,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_QR,NE_ComAju,NE_CntAju,NE_Ajustes
 FROM Trn_Nomina
 WHERE IdPeriodo=@pmIdPeriodo AND KeyRegistro LIKE ISNULL(@pmKeyRegistro,'%')
 ORDER BY KeyRegistro
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsNERecord] @pmTipoReg VARCHAR(3),@pmNumRegistro INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdPeriodo VARCHAR(8),@pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmIdRegNom VARCHAR(30),@pmNumLiquida INT,@pmNumSemLiq INT,@pmFecInicial SMALLDATETIME,@pmFecFinal SMALLDATETIME,@pmCantidad DECIMAL(10,4),@pmCantNoRem DECIMAL(10,4),@pmVrDevengado MONEY,@pmVrDeducido MONEY,@pmIdDep VARCHAR(4),@pmIdInstala VARCHAR(4),@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmIdNom VARCHAR(4),@pmVrSalario MONEY,@pmVrATransMes MONEY,@pmpVehiculo VARCHAR(10),@pmFecIniPmd SMALLDATETIME,@pmFecFinPmd SMALLDATETIME
,@pmDiasVac INT,@pmNPeriodos INT,@pmCantRem INT,@pmVrRemndo MONEY,@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmEsRetiro BIT,@pmNLiqRet INT,@pmTipoLiq CHAR(1),@pmEsParcial BIT,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11),@pmCdTipoCot VARCHAR(4),@pmSubTipo_Cot VARCHAR(3),@pmSal_Integral BIT,@pmTipoIdent CHAR(1),@pmNotasAportante VARCHAR(250),@pmCantModifica INT,@pmNE_Estado INT,@pmNE_Prefijo VARCHAR(20),@pmNE_Numero INT,@pmNE_FecEnvio SMALLDATETIME,@pmNE_CUNE VARCHAR(500),@pmNE_CntAju INT,@pmNE_Ajustes VARCHAR(500)
 AS 
 INSERT INTO Trn_NERecord (TipoReg,NumRegistro,IdCia,Fecha,IdPeriodo,IdEmpleado,NContrato,IdRegNom,NumLiquida,NumSemLiq,FecInicial,FecFinal,Cantidad,CantNoRem,VrDevengado,VrDeducido,IdDep,IdInstala,IdCCosto,IdSubCos,IdNom,VrSalario,VrATransMes,pVehiculo,FecIniPmd,FecFinPmd,DiasVac,NPeriodos,CantRem,VrRemndo,TipCom,Comprobante,IdCiaCom,Observacion,IdEstado,EsRetiro,NLiqRet,TipoLiq,EsParcial,FechaCrea,IdUsuario,CdTipoCot,SubTipo_Cot,Sal_Integral,TipoIdent,NotasAportante,CantModifica,NE_Estado,NE_Prefijo,NE_Numero,NE_FecEnvio,NE_CUNE,NE_CntAju,NE_Ajustes)
 VALUES (@pmTipoReg,@pmNumRegistro,@pmIdCia,@pmFecha,@pmIdPeriodo,@pmIdEmpleado,@pmNContrato,@pmIdRegNom,@pmNumLiquida,@pmNumSemLiq,@pmFecInicial,@pmFecFinal,@pmCantidad,@pmCantNoRem,@pmVrDevengado,@pmVrDeducido,@pmIdDep,@pmIdInstala,@pmIdCCosto,@pmIdSubCos,@pmIdNom,@pmVrSalario,@pmVrATransMes,@pmpVehiculo,@pmFecIniPmd,@pmFecFinPmd,@pmDiasVac,@pmNPeriodos,@pmCantRem,@pmVrRemndo,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmObservacion,@pmIdEstado,@pmEsRetiro,@pmNLiqRet,@pmTipoLiq,@pmEsParcial,@pmFechaCrea,@pmIdUsuario,@pmCdTipoCot,@pmSubTipo_Cot,@pmSal_Integral,@pmTipoIdent,@pmNotasAportante,@pmCantModifica,@pmNE_Estado,@pmNE_Prefijo,@pmNE_Numero,@pmNE_FecEnvio,@pmNE_CUNE,@pmNE_CntAju,@pmNE_Ajustes)
GO