if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsOComb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCliente]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsTercCliente_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTercCliente_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_PreExtoCuo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_PreExtoCuo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Crr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraDetalle_Crr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Muc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraDetalle_Muc]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Odp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraDetalle_Odp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Rem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraDetalle_Rem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_TraDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInstm_TraDetalle_Sel]
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].[paInsTraManifRem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsTraManifRem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuia_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuia_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuiaLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuiaLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuiaRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuiaRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryGuiaRelDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryGuiaRelDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOComb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOComb_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOComb_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOCombGui]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOCombGui]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOCombLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOCombLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryOCombRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryOCombRel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercCliente]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercClienteLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTercClienteLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_TraDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_TraDetalleMuc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQrytm_TraDetalleMuc]
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].[paQryTraManifRem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraManifRem]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraRemesaFac]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryTraRemesaFac]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpGuia]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpGuia]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpOComb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpOComb]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpTercCliente]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpTercCliente]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpNotaProd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paUpNotaProd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsNotaProd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paInsNotaProd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotaProd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNotaProd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotaProd_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNotaProd_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotaProdLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNotaProdLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotaProdOrd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNotaProdOrd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryNotaProdRel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryNotaProdRel]
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,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
,VrFletes,VrRetencion,VrReteIca,VrDescuento,VrAnticipo,VrAntAdic,VrNeto,VrPagos,VrCargos,VrDctos,TarifaFlete,M.Cantidad AS CantTotal,PesoTotal,BaseRet,TarifaRet,TarifaIca
,IdLocFletes,CF.Localidad AS CiuLugarFletes,FechaPago,PagoCargue,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,FechaOdp,EstOrden,OrigenAdd,Anulado,FecDev,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,TipCom,Comprobante,IdCiaCom,TimeSys,FecUpdate,IdCiaCrea,M.IdUsuario AS CdUsuario,Usuario,Leyenda
,kmsTotal,VrFleteTon,MA.TipoRuta AS MucTipoRuta,CdLocTrao,CdLocTrad,KmsTraOri,KmsTraDes,IdMneda,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,TomadorPoliza,PolizaSeguro,NitCiaPoliza,NomCiaPoliza,FecVencePol,MvoAnulacion
--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
,HorasCargue,HorasDesc,FecInicioCargue,FecFinCargue,FecIniciodesc,FecFindesc,TiempoCargue,TiempoDesc,FecLlegCargue,FecSalidaCargue,FecLlegdesc,FecSalidadesc
--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,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,CDT.Direccion AS CdtDireccion,CDT.Telefono AS CdtTelefono,CDT.e_mail AS CdtEmail
 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 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 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 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 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 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 RAN ON MR.TipRem=RAN.TipDoc  AND MR.Remesa=RAN.NumOrden AND MR.IdCiaRem=RAN.IdCia
 WHERE M.TipDoc=@pmTipDoc AND M.Manifiesto BETWEEN @pmManifiestoIni AND @pmManifiestoFin AND M.IdCia=@pmIdCia
 ORDER BY M.Manifiesto



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTraRemesaFac]  @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null
 AS 
SELECT FR.TipDoc AS CdTipDoc,TipoDoc,Factura,FR.IdCia AS CdCia,CN.Compania AS NomCiaFact,FechaFact,Item,TipoReg,TipRem,Remesa,IdCiaRem,CR.Compania AS NomCiaRem,ItemRem,FecRemesa,FR.Descripcion AS DescripRem
,FR.Cantidad AS CantFact,VrUnitario,UndTarifa,VrCosto,UndCosto,TarifaDct,VrDescuento,TarifaIva,VrImpuesto,TarifaRet,VrRetencion,TarifaIca,VrReteIca,VrFaltante,CantidadFalt,UnidadFalt,Unidades,PesoNeto,FR.UndMed AS CdUmPeso,Unidad
,Volumen,UndVol,Cases,Cajas,Palets,FR.CdConcepto AS CdConcept,CDV.Concepto AS DescConcept,CdMercancia,DescripMcia,CdAgencia,Agencia,CdCCosto,CCosto,FR.CdSubCos AS CdSubCentro,SubCosto,FR.TipoAfiVehic AS TipoAfiVeh,pVehiculo
,IdOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,IdDestino,LD.Localidad AS CiudadDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,FR.Anulado AS FacAnulado,TipDocRef,NumDocRef,IdCiaRef,FecDocRef,NumPedRem,CiaPedRem
--datos de remesa
,Modalidad,IdRemitente,NomRemite,IdDestinatario,NomDestino,R.IdConductor AS RemCedCond,CDT.RazonSocial AS RemConductor,nRemolque,NumManif,IdCiaManif,R.Observacion AS RemObserv
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo
--datos de factura encabezados
,FacCdConc,CF.Concepto AS FacConcepto,T.TipoId AS TercTipo,FacNitClie,T.Dv AS TercDv,T.RazonSocial AS NombreClie,FacNitVend,VN.RazonSocial AS Vendedor,FacCdCiudad,LF.Localidad AS FacCiudad,LF.IdDep AS FacCdDpto,DF.Departamento AS FacDpto,FacVrNeto
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie
 FROM Trn_TraFacRemesas AS FR INNER JOIN Sys_TiposDoc AS TD ON FR.TipDoc=TD.IdDoc INNER JOIN Companias AS CN ON FR.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON FR.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON FR.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 --SUBQUERY FACTURAS
 INNER JOIN (SELECT F.TipDoc AS FacTipo,F.Factura AS FacNumero,F.IdCia AS FacCdCia,F.IdConcepto AS FacCdConc,F.IdCliente AS FacNitClie,F.IdAgencia AS FacIdAgenc
 ,F.IdVend AS FacNitVend,F.IdLocEnv AS FacCdCiudad,F.VrCargos AS FacVrCargos,F.VrOtrDcto AS FacVrOtrDctos,F.VrNeto AS FacVrNeto
 FROM Trn_Facturas AS F WHERE F.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND  F.Modalidad='TRANSPORTE'  AND F.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 UNION ALL
 SELECT D.TipDev,D.Devolucion,D.IdCia,D.IdConcepto,D.IdCliente,D.IdAgencia,D.IdVend,D.IdLocEnv,D.VrCargos,D.VrOtrDcto,D.VrNeto
 FROM Trn_DevFcr AS D WHERE D.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND D.Modalidad='TRANSPORTE' AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 UNION ALL
 SELECT N.TipDoc,N.NumNota,N.IdCia,N.IdConcepto,N.IdCliente,N.IdAgencia,N.IdVend,N.IdLocal,N.VrOtros,0,N.VrNeto
 FROM Trn_Notas AS N WHERE N.Fecha BETWEEN @pmFechaIni  AND @pmFechaFin AND N.Modalidad='REMESAS' AND  N.IdCia LIKE ISNULL(@pmIdCia,'%%')
  AND N.IdCliente LIKE ISNULL(@pmIdCliente,'%')  ) AS F ON FR.TipDoc=F.FacTipo AND FR.Factura=F.FacNumero AND FR.IdCia=F.FacCdCia
 INNER JOIN Terceros AS T ON F.FacNitClie=T.IdTercero INNER JOIN TercCliente AS CLI ON F.FacNitClie=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 Terceros AS VN ON F.FacNitVend=VN.IdTercero INNER JOIN Conceptos AS CF ON F.FacCdConc=CF.IdConcepto
 LEFT JOIN Localidades AS LF ON F.FacCdCiudad=LF.IdLocal LEFT JOIN Departamentos AS DF ON LF.IdDep=DF.IdDep
 LEFT JOIN Companias AS CR ON FR.IdCiaRem=CR.IdCia  LEFT JOIN Sys_Um AS UMP ON FR.UndMed=UMP.UndMed
 LEFT JOIN ConcDiversos AS CDV ON FR.CdConcepto=CDV.IdConcepto  LEFT JOIN Agencias  AS A ON FR.CdAgencia=A.IdAgencia
 LEFT JOIN CentroCosto AS CC ON FR.CdCCosto=CC.IdCCosto  LEFT JOIN SubCentros AS SC ON FR.CdSubCos=SC.IdSubCos
 LEFT JOIN Trn_TraRemesa AS R ON FR.TipRem=R.TipDoc AND FR.Remesa=R.NumOrden AND FR.IdCiaRem=R.IdCia
 LEFT JOIN Trn_TraRemAnexo AS RA ON FR.TipRem=RA.TipDoc AND FR.Remesa=RA.NumOrden AND FR.IdCiaRem=RA.IdCia
 LEFT JOIN Terceros AS CDT ON R.IdConductor=CDT.IdTercero
 LEFT JOIN Vehiculos AS V ON FR.pVehiculo=V.IdVehiculo LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 LEFT JOIN Mercancias AS MC ON FR.CdMercancia=MC.IdMercancia
 WHERE FR.FechaFact BETWEEN @pmFechaIni AND @pmFechaFin AND FR.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND F.FacNitClie LIKE ISNULL(@pmIdCliente,'%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTercClienteLta] @pmIdLocal VARCHAR(8)=Null,@pmIdSector VARCHAR(8)=Null,@pmIdRegimen VARCHAR(4)=Null,@pmTipoId CHAR(1)=Null
,@pmIdProf VARCHAR(4)=Null,@pmIdGrupo VARCHAR(4)=Null,@pmIdLocEnv VARCHAR(8)=Null,@pmIdForma VARCHAR(4)=Null,@pmIdBanco VARCHAR(4)=Null,@pmIdZona VARCHAR(4)=Null
,@pmIdSzona VARCHAR(4)=Null,@pmIdPlazo VARCHAR(4)=Null,@pmIdEstrato VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdRuta VARCHAR(4)=Null
,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null,@pmIdEstadoTer VARCHAR(4)=Null,@pmInactivo BIT=Null
,@pmInactivoTer BIT=Null
 AS 
 SELECT IdClie,T.RazonSocial AS RaznSocial,T.TipoId AS TipId,T.Dv AS DvNit,T.NomCial AS NombreCmcial,T.SiglaRaz AS SiglaRazSocial
,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,T.Direccion AS Dirccion,T.IdLocal AS CdCiudad,L.Localidad AS Ciudad,L.IdDep AS CdDpto
,D.Departamento AS Dpto,T.Telefono AS Tlefono,T.Fax AS NumFax,T.TelMovil AS TelCeluar,T.SitioWeb AS WebSite,T.e_mail AS Email,DirEnv,IdLocEnv,LE.Localidad AS CiuEnvio
,DE.Departamento AS Dptoenvio,DiasEntga,C.IdGrupo AS CdGrupo,GrupoClie,C.IdEstrato AS CdEstrato,Estrato,SZ.IdZona AS CdZona,Zona,C.IdSzona AS CdSubzona,Subzona
,C.IdRuta AS CdRuta,Ruta,C.IdPlazo AS CdPlazo,Plazo,NVmto,PlazosImp,CdPlazoComb,C.IdForma AS CdFrma,FormaPago,T.IdSector AS CdSector,SectorEco,T.IdProf AS CdProf,Profesion,T.IdRegimen AS CdRgmen
,Regimen,T.TipEnte AS TipoEnte,IdVend,TV.RazonSocial AS NomVendedor,NitFact,C.IdClase AS CdClase,ClaseCuenta,NumCuenta,C.IdBanco AS CdBanco,Banco,B.Direccion AS Dirbanco
,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,Contrato,NContrato,CiaContMay,FecIngreso,FecVigencia
,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,T.Observacion AS Observ,Cmntario1,Cmntario2,Cmntario3,CdBandera,PrendGarant,FecUpCupo,TipoCliente,CodClieSicom,C.IdEstado AS CdEst_clie,EC.Estado AS EstadoClie,C.Inactivo AS Clie_inactivo
,T.IdEstado AS CdEst_terc,ET.Estado AS Estad_terc,T.Inactivo AS Terc_inactivo,T.Codigo AS CodTerc,T.IdLugarCed AS IdLugCed,LC.Localidad AS LugarCed,T.FecExpCed AS FecExpCedula
,T.EsVendedor AS EsVend,T.EsConductor AS EsCond,T.EsPropietario AS EsProp,T.EsProveedor AS EsProvee,T.EsEmpleado AS EsEmpldo,T.EsOperario AS EsOper,T.EsAccnista AS EsAcc
,T.EsCiaAseg AS EsAseg,CdTipBloq,C.FechaAdd AS FecAdd,C.FechaUpdate AS FecUpd,C.IdUsuario AS IdUsuari,Usuario,T.IniStgNom AS PosNomb,Restric_Cia,CupoGalones,FecPlazoDoc,EdoRadicaDoc,DescEdoDoc,ComIndustrial
 FROM TercCliente  AS C INNER JOIN Terceros AS T ON C.IdClie=T.IdTercero  INNER JOIN Terceros AS TV ON C.IdVend=TV.IdTercero
 INNER JOIN GruposCli AS G ON C.IdGrupo=G.IdGrupo INNER JOIN Estratos AS ETC ON C.IdEstrato=ETC.IdEstrato
 INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona  INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 INNER JOIN Plazos AS PZ ON C.IdPlazo=PZ.IdPlazo INNER JOIN Rutas AS R ON C.IdRuta=R.IdRuta
 INNER JOIN Formaspago AS F ON C.IdForma=F.IdForma
 INNER JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase
 INNER JOIN Bancos AS B ON C.IdBanco=B.IdBanco INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado
 INNER JOIN adm_Usuarios AS U ON C.IdUsuario=U.IdUsuario   INNER JOIN Localidades AS LE ON C.IdLocEnv=LE.IdLocal
 INNER JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep
 INNER JOIN  SectoresEco AS S ON T.IdSector=S.IdSector  INNER JOIN RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen
 INNER JOIN EstadoTer AS ET ON T.IdEstado=ET.IdEstado INNER JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal 
 INNER JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 WHERE T.IdLocal LIKE ISNULL(@pmIdLocal,'%') AND T.IdSector LIKE ISNULL(@pmIdSector,'%')  AND T.IdRegimen LIKE ISNULL(@pmIdRegimen,'%')
 AND T.TipoId LIKE ISNULL(@pmTipoId,'%') AND T.IdProf LIKE ISNULL(@pmIdProf,'%') AND C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%')
 AND IdLocEnv LIKE ISNULL(@pmIdLocEnv,'%') AND C.IdForma LIKE ISNULL(@pmIdForma,'%') AND C.IdBanco LIKE ISNULL(@pmIdBanco,'%')
 AND SZ.IdZona LIKE ISNULL(@pmIdZona,'%') AND C.IdSzona LIKE ISNULL(@pmIdSzona,'%')
 AND C.IdPlazo LIKE ISNULL(@pmIdPlazo,'%') AND C.IdEstrato LIKE ISNULL(@pmIdEstrato,'%')
 AND IdVend LIKE ISNULL(@pmIdVend,'%') AND C.IdRuta LIKE ISNULL(@pmIdRuta,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND T.IdEstado LIKE ISNULL(@pmIdEstadoTer,'%')
 AND (C.Inactivo=ISNULL(@pmInactivo,0) or C.Inactivo=ISNULL(@pmInactivo,1))
 AND (T.Inactivo=ISNULL(@pmInactivoTer,0) or T.Inactivo=ISNULL(@pmInactivoTer,1))
 AND (FecIngreso>=ISNULL(@pmFecIngIni,CAST('19100101' AS SMALLDATETIME)) AND FecIngreso<=ISNULL(@pmFecIngFin,CAST('20781230' AS SMALLDATETIME)))
 ORDER BY T.RazonSocial

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryOComb_Cr] @pmTipDoc VARCHAR(3),@pmOCargueIni INT,@pmOCargueFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc AS Tip_Doc,TipoDoc,OCargue,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaDesp,FechaVence
,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS IdAgenc,Agencia,CodAgencia
,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,DiasEntraga,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,Referncia,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos
,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo
,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,UA.Usuario AS UsuarioAprob,OrigenAdd,IdTerminal,ZonaFrontera,TipoOrden,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario,Leyenda
,O.TimeSys AS Fec_Add,O.FecUpdate AS Fech_Update,IdCiaCrea
--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,CLI.NumCuenta AS NumeroCuenta,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
--vehiculo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarcaVeh,MV.Marca AS MarcaVeh,Modelo,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,CarrCapac,V.UndCapc AS UnidCapacidad,Comptmtos,CapComp
--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,K.Descripcion AS KarDescripcion,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,M.Marca AS MarcaProd,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_OComb AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.OCargue=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 Terceros AS TC ON O.IdConductor=TC.IdTercero
 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.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 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
 LEFT JOIN adm_Usuarios AS UA ON O.CdUsuAprob=UA.IdUsuario
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
  WHERE O.TipDoc=@pmTipDoc AND OCargue BETWEEN @pmOCargueIni AND @pmOCargueFin AND O.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY OCargue,Item

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryGuiaRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmCdCiuOrigen VARCHAR(8)=Null,@pmIdLocal VARCHAR(8)=Null
,@pmIdConductor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,Guia,O.IdCia AS CdCia,Compania,Fecha,FechaDesp,FechaVence,IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS IdAgenc,Agencia,CodAgencia
,Cantidad,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,HorasVig,IdVehiculo,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,CdCiuOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CdDepOrigen,DO.Departamento AS DptoOrigen
,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,Factura,IdCiaFac,FechaFact,OrigenAdd,ZonaFrontera,TipoTemp,Temperatura,TipoOCargue,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,TimeSys,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS IdUsuari
--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,CLI.NumCuenta AS NumeroCuenta,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
 FROM  Trn_Guia AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero 
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia   INNER JOIN Terceros AS TC ON O.IdConductor=TC.IdTercero
 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
 LEFT JOIN  Localidades AS LE ON O.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Localidades AS LO ON O.CdCiuOrigen=LO.IdLocal  LEFT JOIN Departamentos AS DO ON LO.IdDep=DO.IdDep 
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND O.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%') AND CdCiuOrigen LIKE ISNULL(@pmCdCiuOrigen,'%')
 AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,Guia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryOCombRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdLocal VARCHAR(8)=Null
,@pmIdConductor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,OCargue,O.IdCia AS CdCia,Compania,Fecha,FechaDesp,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,O.IdVend AS NitVendedor,VN.RazonSocial AS Vendedor,TarifaCom,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,DiasEntraga,O.IdVehiculo AS PlacaVeh,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,Referncia,O.IdForma AS CdForma,FormaPago,DetallePago,MulPlazos
,O.IdPlazo AS CdPlazo,Plazo,NVmto,DiasPago,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo
,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,UA.Usuario AS UsuarioAprob,OrigenAdd,ZonaFrontera,TipoOrden,Anulado,FecDev,O.Observacion AS Observ,IdTerminal,O.IdEstado AS CdEstado,Estado,O.IdUsuario AS IdUsuari,U.Usuario AS NomUsuario
,TimeSys,O.FecUpdate AS Fech_Update,IdCiaCrea
--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,CLI.NumCuenta AS NumeroCuenta,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
--vehiculo
,NumVeh,V.IdTipoVeh AS CdTipoVeh,TipoVehiculo,V.IdMarca AS CdMarcaVeh,MV.Marca AS MarcaVeh,Modelo,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,CdRemque,CarrCapac,V.UndCapc AS UnidCapacidad,Comptmtos,CapComp
 FROM  Trn_OComb AS O  INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia 
 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 Terceros AS TC ON O.IdConductor=TC.IdTercero
 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
 LEFT JOIN  Localidades AS LE ON O.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN adm_Usuarios AS UA ON O.CdUsuAprob=UA.IdUsuario
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 LEFT JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo
 LEFT JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh
 LEFT JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca
  WHERE TipDoc=@pmTipDoc  AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND O.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%')
 AND O.IdVend LIKE ISNULL(@pmIdVend,'%') AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%') AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,OCargue

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryGuiaRelDet] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmCdCiuOrigen VARCHAR(8)=Null,@pmIdLocal VARCHAR(8)=Null
,@pmIdConductor VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT O.TipDoc AS CdTipDoc,Guia,O.IdCia AS CdCia,Compania,O.Fecha AS FechaGuia,FechaDesp,FechaVence,IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS IdAgenc,Agencia,CodAgencia
,Cantidad,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,HorasVig,IdVehiculo,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,CdCiuOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CdDepOrigen,DO.Departamento AS DptoOrigen
,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact,OrigenAdd,ZonaFrontera,TipoOCargue,Anulado,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.TimeSys,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS IdUsuari
--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,CLI.NumCuenta AS NumeroCuenta,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
--datos de kardex
,K.IdProducto AS CdProducto,Salidas,K.Referencia,DescripProd,Descripcion
 FROM  Trn_Guia AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero 
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia   INNER JOIN Terceros AS TC ON O.IdConductor=TC.IdTercero
 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 Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Guia=K.Documento  AND  O.IdCia=K.IdCia
 INNER JOIN ProdMcias AS P ON K.IdProducto=P.IdProducto
 LEFT JOIN  Localidades AS LE ON O.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Localidades AS LO ON O.CdCiuOrigen=LO.IdLocal  LEFT JOIN Departamentos AS DO ON LO.IdDep=DO.IdDep 
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND O.IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%')
 AND O.IdAgencia LIKE ISNULL(@pmIdAgencia ,'%') AND CdCiuOrigen LIKE ISNULL(@pmCdCiuOrigen,'%')
 AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')  AND O.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND O.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY O.IdCia,Guia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryGuia_Cr] @pmTipDoc VARCHAR(3),@pmGuiaIni INT,@pmGuiaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT  O.TipDoc AS Tip_Doc,TipoDoc,Guia,O.IdCia AS CdCia,Compania,O.Fecha AS FechaDoc,FechaDesp,FechaVence,IdCliente,T.RazonSocial AS NomCliente
,O.IdAgencia AS IdAgenc,Agencia,CodAgencia,Cantidad,DirEnvio,O.IdLocal AS CdCiudad,LE.Localidad AS CiudadEnvio,LE.IdDep AS CdDepEnvio,DE.Departamento AS DptoEnvio
,LugarEnv,HorasVig,O.IdVehiculo,O.IdConductor AS CedConductor,TC.RazonSocial AS Conductor,LibMilitar,ClaseLib,FechaLib,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,VigLicencia,NumTarjeta,VigTarjeta
,O.NitEmpTrans,EmpTrans,CdRuta,Ruta,CdCiuOrigen,LO.Localidad AS CiudadOrigen,LO.IdDep AS CdDepOrigen,DO.Departamento AS DptoOrigen
,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,O.Factura AS NumFactura,IdCiaFac,FechaFact,OrigenAdd,ZonaFrontera,Anulado,TipoTemp,O.Temperatura,TipoOCargue,FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado
,O.TimeSys AS Fec_Add,O.FecUpdate AS Fec_Update,IdCiaCrea,O.IdUsuario AS IdUsuari,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,CLI.NumCuenta AS NumeroCuenta,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,CdCCosto,K.CdSubCos AS CodSubCos,K.pVehiculo AS KarPlacaVeh,K.Referencia AS KarReferencia
,K.Descripcion AS KarDescricion,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
,ListaPrec,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,Compania
--Inf. vehículo
,CdRemque,VehArtic,NumVeh
 FROM  Trn_Guia AS O INNER JOIN Trn_Kardex AS K ON O.TipDoc=K.TipDoc AND O.Guia=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 EstadoDoc AS ED ON O.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Agencias  AS A ON O.IdAgencia=A.IdAgencia   INNER JOIN Terceros AS TC ON O.IdConductor=TC.IdTercero
 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.IdLocal=LE.IdLocal  LEFT JOIN Departamentos AS DE ON LE.IdDep=DE.IdDep 
 LEFT JOIN Localidades AS LO ON O.CdCiuOrigen=LO.IdLocal  LEFT JOIN Departamentos AS DO ON LO.IdDep=DO.IdDep 
 LEFT JOIN Rutas AS R ON O.CdRuta=R.IdRuta
 LEFT JOIN TercCndtores AS TCD ON O.IdConductor=TCD.IdConductor
 LEFT JOIN Vehiculos AS VH ON O.IdVehiculo=VH.IdVehiculo
 WHERE O.TipDoc=@pmTipDoc AND Guia BETWEEN @pmGuiaIni  AND @pmGuiaFin AND O.IdCia=@pmIdCia
 AND EsProdBase=0
 ORDER BY Guia,Item

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInstm_PreExtoCuo] @pmtmEst CHAR(2),@pmtmTipoRep INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdCia CHAR(2)=Null
 AS 
 INSERT INTO tm_PreExto (tmEst,tmTipoRep,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmCargos,tmAbonos,tmIdLinea,tmConcepto,tmDetalle,tmReferencia,tmCredito,tmVrPrestamo,tmVrCuota
,tmTotAbonos,tmNCuota,tmIdVend,tmTimeSys,tmFecVence,tmTipoAbono,tmDTF_EA,tmTasaNom,tmTasaEfe,tmTipRef,tmDocRef,tmIdCiaRef,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev
,tmSanClie,tmSanCia,tmSanAgenc,tmSanAgeCia,tmSanLinea,tmSanLineaCia,tmSanConc,tmSanConcCia)
 SELECT @pmtmEst,@pmtmTipoRep,C.TipDoc,C.IdPrestamo,C.IdCia,0,MAX(C.Fecha),C.IdCliente,C.IdAgencia,SUM(C.VrTotal),0,P.IdLinea,'CAPITAL','','',P.TipoCredito,P.VrPrestamo,P.VrCuota
,SUM(C.VrAbonado),MAX(NumCuota),P.IdVend,P.TimeSys,MAX(C.FechaVence),0,MAX(DTF_EA),MAX(TasaNom),MAX(TasaEfe),'PR1',0,'',TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,0,0,0,0,0,0,0,0
 FROM Trn_FinCuotas AS C INNER JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc  AND C.IdPrestamo=P.IdPrestamo  AND C.IdCia=P.IdCia
 WHERE C.TipDoc='PR1' AND C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.Concepto='CAPITAL' 
 AND NOT EXISTS (SELECT * FROM tm_PreExto WHERE tm_PreExto.tmTipDoc='PR1' AND tm_PreExto.tmNumero=C.IdPrestamo AND tm_PreExto.tmIdCia=C.IdCia
 AND tm_PreExto.tmConcepto='CAPITAL')
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')
 GROUP BY  C.TipDoc,C.IdPrestamo,C.IdCia,C.IdCliente,C.IdAgencia,P.IdLinea,P.TipoCredito,P.VrPrestamo,P.VrCuota
,P.IdVend,P.TimeSys,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Sel] @pmTipDoc VARCHAR(3),@pmPedido INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat
,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino
,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc)
SELECT @pmtmNumero,Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat
,IdMnjo,IdTmcia,CdRango,Cases,Cajas,Palets,0,0,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino
,'0','TERCEROS',TarifClie,TarifPago,TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,'0',Referencia1,Referencia2,'',Contenedor1,Contenedor2,UndTarifa
,TipDoc,Pedido,IdCia,Item,0,0,UndTarifPago,'',0,0,0,Null,Null,Null,Null
 FROM Trn_TraPedMcias
 WHERE TipDoc=@pmTipDoc AND Pedido=@pmPedido AND IdCia=@pmIdCia



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Odp] @pmTipDoc VARCHAR(3),@pmOrdPago INT,@pmIdCia CHAR(2)
,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets
,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3
,tmContenedor1,tmContenedor2,tmUndTarifa,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc)
 SELECT @pmtmNumero,R.Item,IdMercancia,Detalle,R.Cantidad,R.PesoNeto,R.UndMed,dmsAlto,dmsAncho,dmsLargo,R.Volumen,R.UndVol,IdUnd,IdEmp,IdNat,IdMnjo,IdTmcia,CdRango,R.Cases,R.Cajas,R.Palets,Remision,Cumplido
,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino,CdAgencia,'TERCEROS',TarifClie,R.TarifPago,R.TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,DocCliente,Referencia1,Referencia2,Referencia3
,Contenedor1,Contenedor2,D.UndTarifa,TipRem,Remesa,IdCiaRem,ItemRem,0,PesoCont,R.UndTarifa,'',0,0,0,Null,Null,Null,Null
 FROM Trn_TraOrdenRemesas AS R INNER JOIN Trn_TraRemMcias AS D ON R.TipRem=D.TipDoc AND  R.Remesa=D.NumOrden AND R.IdCiaRem=D.IdCia AND R.ItemRem=D.Item
 WHERE R.TipDoc=@pmTipDoc AND R.OrdPago=@pmOrdPago  AND R.IdCia=@pmIdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQrytm_TraDetalleMuc] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed
,tmIdNat,Natlzaprod,tmIdEmp,Empaque,tmReferencia1,tmReferencia2,tmTarifTabla,tmTarifPago,tmIdOrigen,LO.Localidad AS LugarOrigen
,tmIdDestino,LD.Localidad AS LugarDestino,tmNitRemite,tmRemitente,tmDirOrigen,tmNitDestntario,tmDestinatario,tmDirDestino
,tmdmsLargo,tmdmsAncho,tmdmsAlto,tmVolumen,tmCases,tmCajas,tmPalets,tmRemision,tmReferencia3,tmDocCliente,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado
,tmTieCargue,tmFecIniCargue,tmFecFinCargue,tmTieDesc,tmFecInidesc,tmFecFindesc
--datos pendientes
--,tmTarifClie,tmUndTarifa,tmVrDeclarado,tmTarifSeguro,tmVrSeguro,tmUndVol,tmIdUnd,U.Unidad AS UndPresenta
--,tmIdMnjo,ManejoMcia,tmIdTmcia,TipoMcia,tmCdRango,DescripRango,tmContenedor1,tmContenedor2, 
 FROM tm_TraDetalle AS T INNER JOIN Localidades AS LO ON T.tmIdOrigen=LO.IdLocal
 INNER JOIN Localidades AS LD ON T.tmIdDestino=LD.IdLocal
 INNER JOIN UndMed AS U ON T.tmIdUnd=U.IdUnd  LEFT JOIN Empaques AS E ON T.tmIdEmp=E.IdEmp
 LEFT JOIN TiposNat AS TN ON T.tmIdNat=TN.IdNat 
 --LEFT JOIN TiposMnjo AS MJ ON T.tmIdMnjo=MJ.IdMnjo
-- LEFT JOIN TiposMcia AS TM ON T.tmIdTmcia=TM.IdTmcia 
-- LEFT JOIN RangosPeso AS RP ON T.tmCdRango=RP.IdRango
 WHERE tmNumero=@pmtmNumero 
 ORDER BY tmItem


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paInsTraManifRem] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2),@pmItem INT,@pmTipRem VARCHAR(3)
,@pmRemesa INT,@pmIdCiaRem CHAR(2),@pmItemRem INT,@pmTarifTabla MONEY,@pmTarifPago MONEY,@pmRemMintrans DECIMAL(14,2),@pmEdoRemMin VARCHAR(5)
,@pmCodBodDtno VARCHAR(4),@pmTipTraslado INT,@pmHorasCargue DECIMAL(14,4),@pmHorasDesc DECIMAL(14,4),@pmFecInicioCargue SMALLDATETIME,@pmFecFinCargue SMALLDATETIME
,@pmFecIniciodesc SMALLDATETIME,@pmFecFindesc SMALLDATETIME,@pmTiempoCargue DECIMAL(14,4),@pmTiempoDesc DECIMAL(14,4),@pmFecLlegCargue SMALLDATETIME
,@pmFecSalidaCargue SMALLDATETIME,@pmFecLlegdesc SMALLDATETIME,@pmFecSalidadesc SMALLDATETIME
 AS 
 INSERT INTO Trn_TraManifRem (TipDoc,Manifiesto,IdCia,Item,TipRem,Remesa,IdCiaRem,ItemRem,TarifTabla,TarifPago,RemMintrans,EdoRemMin,CodBodDtno,TipTraslado
,HorasCargue,HorasDesc,FecInicioCargue,FecFinCargue,FecIniciodesc,FecFindesc,TiempoCargue,TiempoDesc,FecLlegCargue,FecSalidaCargue,FecLlegdesc,FecSalidadesc)
 VALUES (@pmTipDoc,@pmManifiesto,@pmIdCia,@pmItem,@pmTipRem,@pmRemesa,@pmIdCiaRem,@pmItemRem,@pmTarifTabla,@pmTarifPago,@pmRemMintrans,@pmEdoRemMin,@pmCodBodDtno,@pmTipTraslado
,@pmHorasCargue,@pmHorasDesc,@pmFecInicioCargue,@pmFecFinCargue,@pmFecIniciodesc,@pmFecFindesc,@pmTiempoCargue,@pmTiempoDesc,@pmFecLlegCargue,@pmFecSalidaCargue
,@pmFecLlegdesc,@pmFecSalidadesc)




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Muc] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat
,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino
,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc)
SELECT @pmtmNumero,RM.Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat
,IdMnjo,IdTmcia,CdRango,Cases,Cajas,Palets,Remision,Factura,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino
,CdAgencia,'TERCEROS',TarifClie,RM.TarifPago,RM.TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2,UndTarifa
,TipRem,Remesa,IdCiaRem,ItemRem,RemMintrans,PesoCont,UndTarifPago,CodBodDtno,TipTraslado,HorasCargue,HorasDesc,FecInicioCargue,FecFinCargue,FecIniciodesc,FecFindesc
 FROM Trn_TraManifRem AS RM INNER JOIN  Trn_TraRemMcias  AS D ON  RM.TipRem=D.TipDoc AND RM.Remesa=D.NumOrden AND RM.IdCiaRem=D.IdCia   AND RM.ItemRem=D.Item
 WHERE RM.TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto AND RM.IdCia=@pmIdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQryTraManifRem] @pmTipDoc VARCHAR(3),@pmManifiesto INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Manifiesto,IdCia,Item,TipRem,Remesa,IdCiaRem,ItemRem,TarifTabla,TarifPago,RemMintrans,EdoRemMin,CodBodDtno,TipTraslado
 ,HorasCargue,HorasDesc,FecInicioCargue,FecFinCargue,FecIniciodesc,FecFindesc,TiempoCargue,TiempoDesc,FecLlegCargue,FecSalidaCargue,FecLlegdesc,FecSalidadesc
 FROM Trn_TraManifRem
 WHERE TipDoc=@pmTipDoc AND Manifiesto=@pmManifiesto AND IdCia=@pmIdCia
 ORDER BY Item


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryOCombGui]  @pmGuia INT,@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdAgencia VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
,@pmIdLocal VARCHAR(8)=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT OCargue,IdCia,Fecha,T.RazonSocial AS NomCliente,IdCliente,IdAgencia,IdVehiculo,IdConductor,CD.RazonSocial AS Conductor
,FechaDesp,FechaVence,O.IdConcepto AS CdConcepto,Concepto,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,O.IdLocal AS CdCiudad,Localidad,LugarEnv,DiasEntraga,NitEmpTrans,EmpTrans,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo
,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,TipoOrden,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev
,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.IdUsuario AS IdUsuari,Usuario,TimeSys,IdCiaCrea,FecUpdate,TipDoc
 FROM Trn_OComb AS O  INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON O.IdVend=V.IdTercero
 INNER JOIN Terceros AS CD ON O.IdConductor=CD.IdTercero
 INNER JOIN  Conceptos AS CN ON O.IdConcepto=CN.IdConcepto
 INNER JOIN Localidades AS L ON O.IdLocal=L.IdLocal
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND  Anulado=0 AND Factura<=0
 AND Guia<=@pmGuia
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND IdAgencia LIKE ISNULL(@pmIdAgencia,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')   AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%') 
 ORDER BY IdCia,OCargue

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryOCombLta]  @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmOCargueIni INT=Null,@pmOCargueFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null
,@pmIdLocal VARCHAR(8)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT OCargue,IdCia,Fecha,FechaDesp,FechaVence,O.IdConcepto AS CdConcepto,Concepto,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto
,Cantidad,IdVend,V.RazonSocial AS Vendedor,TarifaCom,CodTarCom,DirEnvio,O.IdLocal AS CdCiudad,Localidad,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,CD.RazonSocial AS Conductor,NitEmpTrans,EmpTrans,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo
,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev
,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,ZonaFrontera,IdTerminal,TipoOrden,O.IdUsuario AS IdUsuari,Usuario,TimeSys,IdCiaCrea,FecUpdate,TipDoc
 FROM Trn_OComb AS O  INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS V ON O.IdVend=V.IdTercero
 INNER JOIN Terceros AS CD ON O.IdConductor=CD.IdTercero
 INNER JOIN  Conceptos AS CN ON O.IdConcepto=CN.IdConcepto
 INNER JOIN Localidades AS L ON O.IdLocal=L.IdLocal
 INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND OCargue BETWEEN ISNULL(@pmOCargueIni,0) AND ISNULL(@pmOCargueFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') AND O.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND O.IdEstado LIKE ISNULL(@pmIdEstado,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%')  AND O.IdLocal LIKE ISNULL(@pmIdLocal,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,OCargue

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpOComb] @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia 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),@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmDiasEntraga INT,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150)
,@pmCdRuta VARCHAR(4),@pmReferncia VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(150),@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipGui VARCHAR(3),@pmGuia INT,@pmIdCiaGui CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2)
,@pmFechaFact SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmAprobacion INT,@pmFechaAprob SMALLDATETIME,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmAnulado BIT,@pmFecDev SMALLDATETIME
,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmIdTerminal VARCHAR(4),@pmTipoOrden INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_OComb SET Fecha=@pmFecha,FechaDesp=@pmFechaDesp,FechaVence=@pmFechaVence,IdConcepto=@pmIdConcepto,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,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,IdLocal=@pmIdLocal,LugarEnv=@pmLugarEnv,DiasEntraga=@pmDiasEntraga,IdVehiculo=@pmIdVehiculo
,IdConductor=@pmIdConductor,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,CdRuta=@pmCdRuta,Referncia=@pmReferncia,IdForma=@pmIdForma,DetallePago=@pmDetallePago,MulPlazos=@pmMulPlazos,IdPlazo=@pmIdPlazo,TipGui=@pmTipGui,Guia=@pmGuia,IdCiaGui=@pmIdCiaGui,TipFac=@pmTipFac,Factura=@pmFactura
,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,TipPed=@pmTipPed,Pedido=@pmPedido,IdCiaPed=@pmIdCiaPed,AutzaMora=@pmAutzaMora,AutzaCupo=@pmAutzaCupo,Aprobacion=@pmAprobacion,FechaAprob=@pmFechaAprob,DetalleAprob=@pmDetalleAprob,CdUsuAprob=@pmCdUsuAprob,Anulado=@pmAnulado
,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,ZonaFrontera=@pmZonaFrontera,IdTerminal=@pmIdTerminal,TipoOrden=@pmTipoOrden,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND OCargue=@pmOCargue AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryOComb] @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,OCargue,IdCia,Fecha,FechaDesp,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto
,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocal,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans
,CdRuta,Referncia,IdForma,DetallePago,MulPlazos,IdPlazo,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob
,DetalleAprob,CdUsuAprob,OrigenAdd,ZonaFrontera,TipoOrden,IdTerminal,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_OComb
 WHERE TipDoc=@pmTipDoc AND OCargue=@pmOCargue AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsOComb] @pmTipDoc VARCHAR(3),@pmOCargue INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia 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)
,@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmDiasEntraga INT,@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmCdRuta VARCHAR(4),@pmReferncia VARCHAR(50),@pmIdForma VARCHAR(4),@pmDetallePago VARCHAR(150)
,@pmMulPlazos BIT,@pmIdPlazo VARCHAR(4),@pmTipPed VARCHAR(3),@pmPedido INT,@pmIdCiaPed CHAR(2),@pmTipGui VARCHAR(3),@pmGuia INT,@pmIdCiaGui CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmAutzaMora INT,@pmAutzaCupo INT,@pmAprobacion INT,@pmFechaAprob SMALLDATETIME
,@pmDetalleAprob VARCHAR(250),@pmCdUsuAprob VARCHAR(11),@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT,@pmIdTerminal VARCHAR(4),@pmTipoOrden INT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_OComb (TipDoc,OCargue,IdCia,Fecha,FechaDesp,FechaVence,IdConcepto,IdCliente,IdAgencia,VrSubTotal,VrDescuento,VrImpuesto,VrFletes,VrOtros,VrCargos,VrOtrDcto,VrSobretasa,VrImpGlobal,VrNeto,Cantidad,IdVend,TarifaCom,CodTarCom,DirEnvio,IdLocal,LugarEnv,DiasEntraga,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans,CdRuta,Referncia
,IdForma,DetallePago,MulPlazos,IdPlazo,TipPed,Pedido,IdCiaPed,TipGui,Guia,IdCiaGui,TipFac,Factura,IdCiaFac,FechaFact,AutzaMora,AutzaCupo,Aprobacion,FechaAprob,DetalleAprob,CdUsuAprob,IdTerminal,OrigenAdd,ZonaFrontera,TipoOrden,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmOCargue,@pmIdCia,@pmFecha,@pmFechaDesp,@pmFechaVence,@pmIdConcepto,@pmIdCliente,@pmIdAgencia,@pmVrSubTotal,@pmVrDescuento,@pmVrImpuesto,@pmVrFletes,@pmVrOtros,@pmVrCargos,@pmVrOtrDcto,@pmVrSobretasa,@pmVrImpGlobal,@pmVrNeto,@pmCantidad,@pmIdVend,@pmTarifaCom,@pmCodTarCom
,@pmDirEnvio,@pmIdLocal,@pmLugarEnv,@pmDiasEntraga,@pmIdVehiculo,@pmIdConductor,@pmNitEmpTrans,@pmEmpTrans,@pmCdRuta,@pmReferncia,@pmIdForma,@pmDetallePago,@pmMulPlazos,@pmIdPlazo,@pmTipPed,@pmPedido,@pmIdCiaPed,@pmTipGui,@pmGuia,@pmIdCiaGui,@pmTipFac,@pmFactura,@pmIdCiaFac,@pmFechaFact,@pmAutzaMora,@pmAutzaCupo,@pmAprobacion
,@pmFechaAprob,@pmDetalleAprob,@pmCdUsuAprob,@pmIdTerminal,@pmOrigenAdd,@pmZonaFrontera,@pmTipoOrden,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Rem] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat
,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino
,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc)
SELECT @pmtmNumero,Item,IdMercancia,DescripMcias,Cantidad,PesoNeto,UndMed,dmsAlto,dmsAncho,dmsLargo,Volumen,UndVol,IdUnd,IdEmp,IdNat
,IdMnjo,IdTmcia,CdRango,Cases,Cajas,Palets,Remision,Factura,NitRemite,Remitente,DirOrigen,IdOrigen,NitDestntario,Destinatario,DirDestino,IdDestino
,CdAgencia,'TERCEROS',TarifClie,TarifPago,TarifTabla,VrDeclarado,VrSeguro,TarifSeguro,DocCliente,Referencia1,Referencia2,Referencia3,Contenedor1,Contenedor2
,UndTarifa,TipFac,Factura,IdCiaFac,0,0,PesoCont,UndTarifPago,'',0,0,0,Null,Null,Null,Null
 FROM Trn_TraRemMcias
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsGuia] @pmTipDoc VARCHAR(3),@pmGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmCantidad DECIMAL(14,4),@pmDirEnvio VARCHAR(250),@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmHorasVig DECIMAL(14,4),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16)
,@pmEmpTrans VARCHAR(150),@pmCdRuta VARCHAR(4),@pmCdCiuOrigen VARCHAR(8),@pmReferncia VARCHAR(50),@pmTipOcc VARCHAR(3),@pmOCargue INT,@pmIdCiaOcc CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmZonaFrontera BIT
,@pmTipoTemp VARCHAR(10),@pmTemperatura DECIMAL(14,4),@pmTipoOCargue INT,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_Guia (TipDoc,Guia,IdCia,Fecha,FechaDesp,FechaVence,IdCliente,IdAgencia,Cantidad,DirEnvio,IdLocal,LugarEnv,HorasVig,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans,CdRuta,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,Factura,IdCiaFac,FechaFact,OrigenAdd,ZonaFrontera,TipoTemp,Temperatura,Anulado,FecDev,Observacion,IdEstado,CdCiuOrigen,TimeSys,IdCiaCrea,IdUsuario,TipoOCargue)
 VALUES (@pmTipDoc,@pmGuia,@pmIdCia,@pmFecha,@pmFechaDesp,@pmFechaVence,@pmIdCliente,@pmIdAgencia,@pmCantidad,@pmDirEnvio,@pmIdLocal,@pmLugarEnv,@pmHorasVig,@pmIdVehiculo,@pmIdConductor,@pmNitEmpTrans,@pmEmpTrans,@pmCdRuta,@pmReferncia,@pmTipOcc,@pmOCargue,@pmIdCiaOcc,@pmTipFac,@pmFactura,@pmIdCiaFac
,@pmFechaFact,@pmOrigenAdd,@pmZonaFrontera,@pmTipoTemp,@pmTemperatura,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmCdCiuOrigen,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmTipoOCargue)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpGuia] @pmTipDoc VARCHAR(3),@pmGuia INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFechaDesp SMALLDATETIME,@pmFechaVence SMALLDATETIME,@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmCantidad DECIMAL(14,4)
,@pmDirEnvio VARCHAR(250),@pmIdLocal VARCHAR(8),@pmLugarEnv VARCHAR(50),@pmHorasVig DECIMAL(14,4),@pmIdVehiculo VARCHAR(10),@pmIdConductor VARCHAR(16),@pmNitEmpTrans VARCHAR(16),@pmEmpTrans VARCHAR(150),@pmCdRuta VARCHAR(4),@pmCdCiuOrigen VARCHAR(8)
,@pmReferncia VARCHAR(50),@pmTipOcc VARCHAR(3),@pmOCargue INT,@pmIdCiaOcc CHAR(2),@pmTipFac VARCHAR(3),@pmFactura INT,@pmIdCiaFac CHAR(2),@pmFechaFact SMALLDATETIME,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4)
,@pmZonaFrontera BIT,@pmTipoTemp VARCHAR(10),@pmTemperatura DECIMAL(14,4),@pmTipoOCargue INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_Guia SET Fecha=@pmFecha,FechaDesp=@pmFechaDesp,FechaVence=@pmFechaVence,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,Cantidad=@pmCantidad,DirEnvio=@pmDirEnvio,IdLocal=@pmIdLocal,LugarEnv=@pmLugarEnv,HorasVig=@pmHorasVig,IdVehiculo=@pmIdVehiculo,IdConductor=@pmIdConductor
,NitEmpTrans=@pmNitEmpTrans,EmpTrans=@pmEmpTrans,CdRuta=@pmCdRuta,Referncia=@pmReferncia,TipOcc=@pmTipOcc,OCargue=@pmOCargue,IdCiaOcc=@pmIdCiaOcc,TipFac=@pmTipFac,Factura=@pmFactura,IdCiaFac=@pmIdCiaFac,FechaFact=@pmFechaFact,Anulado=@pmAnulado,FecDev=@pmFecDev
,Observacion=@pmObservacion,IdEstado=@pmIdEstado,CdCiuOrigen=@pmCdCiuOrigen,ZonaFrontera=@pmZonaFrontera,TipoTemp=@pmTipoTemp,Temperatura=@pmTemperatura,TipoOCargue=@pmTipoOCargue,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND Guia=@pmGuia AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryGuia] @pmTipDoc VARCHAR(3),@pmGuia INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,Guia,IdCia,Fecha,FechaDesp,FechaVence,IdCliente,IdAgencia,Cantidad,DirEnvio,IdLocal,LugarEnv,HorasVig
,IdVehiculo,IdConductor,NitEmpTrans,EmpTrans,CdRuta,CdCiuOrigen,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,Factura,IdCiaFac,FechaFact
,OrigenAdd,ZonaFrontera,TipoTemp,Temperatura,TipoOCargue,Anulado,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_Guia
 WHERE TipDoc=@pmTipDoc AND Guia=@pmGuia AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryGuiaLta]  @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmGuiaIni INT=Null,@pmGuiaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
 AS 
 SELECT Guia,IdCia,Fecha,FechaDesp,FechaVence,IdCliente,T.RazonSocial AS NomCliente,IdAgencia,Cantidad,DirEnvio,G.IdLocal AS CdCiudad,LugarEnv,HorasVig
,IdVehiculo,IdConductor,CD.RazonSocial AS Conductor,NitEmpTrans,EmpTrans,CdRuta,CdCiuOrigen,Referncia,TipOcc,OCargue,IdCiaOcc,TipFac,Factura,IdCiaFac,FechaFact
,OrigenAdd,ZonaFrontera,TipoTemp,Temperatura,TipoOCargue,Anulado,FecDev,G.Observacion AS Observ,G.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,G.IdUsuario AS IdUsuari,Usuario
 FROM Trn_Guia AS G  INNER JOIN Terceros AS T ON G.IdCliente=T.IdTercero
 INNER JOIN Terceros AS CD ON G.IdConductor=CD.IdTercero
 INNER JOIN EstadoDoc AS ED ON G.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON G.IdUsuario=U.IdUsuario 
 WHERE Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND Guia BETWEEN ISNULL(@pmGuiaIni,0) AND ISNULL(@pmGuiaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%') AND IdCliente LIKE ISNULL(@pmIdCliente,'%') 
 AND IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND G.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,Guia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpTercCliente] @pmIdClie VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100)
,@pmCargContac VARCHAR(50),@pmDirEnv VARCHAR(250),@pmIdLocEnv VARCHAR(8),@pmDiasEntga INT,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdForma VARCHAR(4),@pmIdEstrato VARCHAR(4)
,@pmIdVend VARCHAR(16),@pmNitFact VARCHAR(16),@pmIdRuta VARCHAR(4),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmCdMney VARCHAR(5),@pmCdDct VARCHAR(4),@pmCdRet VARCHAR(4)
,@pmCdRiv VARCHAR(4),@pmCdCms VARCHAR(4),@pmExcIva BIT,@pmTrfIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmLiqFletes BIT,@pmFactSold BIT,@pmAutoret BIT,@pmIncRet BIT,@pmIncRiv BIT,@pmIncIca BIT,@pmFactTipo INT,@pmVrCupo MONEY
,@pmVrSaldo MONEY,@pmUidClie VARCHAR(16),@pmPwdClie VARCHAR(10),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME
,@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmCmntario1 VARCHAR(250),@pmCmntario2 VARCHAR(250),@pmPlazosImp VARCHAR(20),@pmCdBandera VARCHAR(4),@pmContrato BIT,@pmNContrato INT,@pmCiaContMay CHAR(2)
,@pmCmntario3 VARCHAR(250),@pmPrendGarant VARCHAR(150),@pmFecUpCupo SMALLDATETIME,@pmTipoCliente VARCHAR(10),@pmRestric_Cia BIT,@pmCupoGalones DECIMAL(14,4)
,@pmCdPlazoComb VARCHAR(4),@pmCodClieSicom VARCHAR(20),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFecPlazoDoc SMALLDATETIME,@pmEdoRadicaDoc INT,@pmCdTipBloq VARCHAR(4),@pmDescEdoDoc VARCHAR(250),@pmComIndustrial INT,@pmFechaUpdate SMALLDATETIME 
 AS 
 UPDATE TercCliente SET NitRepLeg=@pmNitRepLeg,NomRepLeg=@pmNomRepLeg,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargContac=@pmCargContac,DirEnv=@pmDirEnv,IdLocEnv=@pmIdLocEnv
,DiasEntga=@pmDiasEntga,IdSzona=@pmIdSzona,IdGrupo=@pmIdGrupo,IdPlazo=@pmIdPlazo,IdForma=@pmIdForma,IdEstrato=@pmIdEstrato,IdVend=@pmIdVend,NitFact=@pmNitFact,IdRuta=@pmIdRuta,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco
,CdMney=@pmCdMney,CdDct=@pmCdDct,CdRet=@pmCdRet,CdRiv=@pmCdRiv,CdCms=@pmCdCms,ExcIva=@pmExcIva,TrfIntMora=@pmTrfIntMora,DiasGracia=@pmDiasGracia,LiqFletes=@pmLiqFletes,FactSold=@pmFactSold,Autoret=@pmAutoret,IncRet=@pmIncRet,IncRiv=@pmIncRiv
,IncIca=@pmIncIca,FactTipo=@pmFactTipo,VrCupo=@pmVrCupo,VrSaldo=@pmVrSaldo,UidClie=@pmUidClie,PwdClie=@pmPwdClie,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,MatMerc=@pmMatMerc,FecMat=@pmFecMat,PathFoto=@pmPathFoto
,PathFirma=@pmPathFirma,Cmntario1=@pmCmntario1,Cmntario2=@pmCmntario2,PlazosImp=@pmPlazosImp,CdBandera=@pmCdBandera,Contrato=@pmContrato,NContrato=@pmNContrato,CiaContMay=@pmCiaContMay,Cmntario3=@pmCmntario3,PrendGarant=@pmPrendGarant,FecUpCupo=@pmFecUpCupo
,CdPlazoComb=@pmCdPlazoComb,IdEstado=@pmIdEstado,Inactivo=@pmInactivo,TipoCliente=@pmTipoCliente,FechaUpdate=@pmFechaUpdate,Restric_Cia=@pmRestric_Cia,CupoGalones=@pmCupoGalones,CodClieSicom=@pmCodClieSicom,FecPlazoDoc=@pmFecPlazoDoc,EdoRadicaDoc=@pmEdoRadicaDoc
,CdTipBloq=@pmCdTipBloq,DescEdoDoc=@pmDescEdoDoc,ComIndustrial=@pmComIndustrial
 WHERE IdClie=@pmIdClie

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsTercCliente] @pmIdClie VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargContac VARCHAR(50),@pmDirEnv VARCHAR(250)
,@pmIdLocEnv VARCHAR(8),@pmDiasEntga INT,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdPlazo VARCHAR(4),@pmIdForma VARCHAR(4),@pmIdEstrato VARCHAR(4),@pmIdVend VARCHAR(16),@pmNitFact VARCHAR(16),@pmIdRuta VARCHAR(4),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30)
,@pmIdBanco VARCHAR(4),@pmCdMney VARCHAR(5),@pmCdDct VARCHAR(4),@pmCdRet VARCHAR(4),@pmCdRiv VARCHAR(4),@pmCdCms VARCHAR(4),@pmExcIva BIT,@pmTrfIntMora DECIMAL(14,4),@pmDiasGracia INT,@pmLiqFletes BIT,@pmFactSold BIT,@pmAutoret BIT,@pmIncRet BIT,@pmIncRiv BIT
,@pmIncIca BIT,@pmFactTipo INT,@pmVrCupo MONEY,@pmVrSaldo MONEY,@pmUidClie VARCHAR(16),@pmPwdClie VARCHAR(10),@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME
,@pmPathFoto VARCHAR(30),@pmPathFirma VARCHAR(30),@pmCmntario1 VARCHAR(250),@pmCmntario2 VARCHAR(250),@pmPlazosImp VARCHAR(20),@pmCdBandera VARCHAR(4),@pmContrato BIT,@pmNContrato INT,@pmCiaContMay CHAR(2),@pmCmntario3 VARCHAR(250),@pmPrendGarant VARCHAR(150)
,@pmFecUpCupo SMALLDATETIME,@pmTipoCliente VARCHAR(10),@pmRestric_Cia BIT,@pmCupoGalones DECIMAL(14,4),@pmCdPlazoComb VARCHAR(4),@pmCodClieSicom VARCHAR(20),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmFecPlazoDoc SMALLDATETIME,@pmEdoRadicaDoc INT,@pmCdTipBloq VARCHAR(4)
,@pmDescEdoDoc VARCHAR(250),@pmComIndustrial INT,@pmFechaAdd SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercCliente (IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario,CupoGalones,Restric_Cia,CdPlazoComb,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial)
 VALUES (@pmIdClie,@pmNitRepLeg,@pmNomRepLeg,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargContac,@pmDirEnv,@pmIdLocEnv,@pmDiasEntga,@pmIdSzona,@pmIdGrupo,@pmIdPlazo,@pmIdForma,@pmIdEstrato,@pmIdVend,@pmNitFact,@pmIdRuta,@pmIdClase,@pmNumCuenta
,@pmIdBanco,@pmCdMney,@pmCdDct,@pmCdRet,@pmCdRiv,@pmCdCms,@pmExcIva,@pmTrfIntMora,@pmDiasGracia,@pmLiqFletes,@pmFactSold,@pmAutoret,@pmIncRet,@pmIncRiv,@pmIncIca,@pmFactTipo,@pmVrCupo,@pmVrSaldo,@pmUidClie,@pmPwdClie,@pmCodClieSicom,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro
,@pmMatMerc,@pmFecMat,@pmPathFoto,@pmPathFirma,@pmCmntario1,@pmCmntario2,@pmPlazosImp,@pmIdEstado,@pmTipoCliente,@pmInactivo,@pmCdBandera,@pmContrato,@pmNContrato,@pmCiaContMay,@pmCmntario3,@pmPrendGarant,@pmFecUpCupo,@pmFechaAdd,@pmIdUsuario,@pmCupoGalones,@pmRestric_Cia,@pmCdPlazoComb,@pmFecPlazoDoc,@pmEdoRadicaDoc,@pmCdTipBloq,@pmDescEdoDoc,@pmComIndustrial)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTercCliente] @pmIdClie VARCHAR(16)
 AS 
 SELECT IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga
,IdSzona,IdGrupo,IdPlazo,CdPlazoComb,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv
,CdCms,PlazosImp,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie
,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,CdBandera,CodClieSicom,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo
,TipoCliente,CupoGalones,Restric_Cia,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial,IdEstado,Inactivo,FechaAdd,FechaUpdate,IdUsuario
 FROM TercCliente
 WHERE IdClie=@pmIdClie

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsTercCliente_Sel] @pmIdClie VARCHAR(16),@pmNewIdClie VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdClie FROM  TercCliente WHERE  IdClie=@pmIdClie)
  INSERT INTO TercCliente (IdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
 ,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario
,CupoGalones,Restric_Cia,CdPlazoComb,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial)
  SELECT @pmNewIdClie,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,DirEnv,IdLocEnv,DiasEntga,IdSzona,IdGrupo,IdPlazo,IdForma,IdEstrato,IdVend,NitFact,IdRuta,IdClase,NumCuenta,IdBanco,CdMney,CdDct,CdRet,CdRiv,CdCms,ExcIva,TrfIntMora,DiasGracia,LiqFletes,FactSold,Autoret
 ,IncRet,IncRiv,IncIca,FactTipo,VrCupo,VrSaldo,UidClie,PwdClie,CodClieSicom,FecIngreso,FecVigencia,FecRetiro,MatMerc,FecMat,PathFoto,PathFirma,Cmntario1,Cmntario2,PlazosImp,IdEstado,TipoCliente,Inactivo,CdBandera,Contrato,NContrato,CiaContMay,Cmntario3,PrendGarant,FecUpCupo,FechaAdd,IdUsuario,CupoGalones,Restric_Cia,CdPlazoComb
,FecPlazoDoc,EdoRadicaDoc,CdTipBloq,DescEdoDoc,ComIndustrial
  FROM  TercCliente 
  WHERE IdClie=@pmIdClie

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
 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)
 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)
 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)



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInstm_TraDetalle_Crr] @pmTipDoc VARCHAR(3),@pmCausacion INT,@pmIdCia CHAR(2),@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat
,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino
,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc)
SELECT @pmtmNumero,Item,'0',Descripcion,Unidades,PesoNeto,UndMed,0,0,0,Volumen,UndVol,'0','0','0','0','0','0',0,0,0,0,0,CedConductor,'0','0','0',CedConductor,'0','0','0','0','TERCEROS',0,VrUnitario,ValorTotal
,0,0,0,pVehiculo,'','',CAST(FecRemesa AS VARCHAR(20)) ,'','',UndTarifa,TipRem,Remesa,IdCiaRem,0,0,0,UndTarifa,'',0,0,0,Null,Null,Null,Null
 FROM  Trn_TraReexpedidoRem
 WHERE TipDoc=@pmTipDoc AND Causacion=@pmCausacion AND IdCia=@pmIdCia


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paInstm_TraDetalle] @pmtmNumero VARCHAR(5),@pmtmItem INT,@pmtmIdMercancia VARCHAR(16),@pmtmDescripMcias VARCHAR(250),@pmtmCantidad DECIMAL(14,4),@pmtmPesoNeto DECIMAL(14,4),@pmtmUndMed VARCHAR(10),@pmtmdmsAlto DECIMAL(14,4),@pmtmdmsAncho DECIMAL(14,4),@pmtmdmsLargo DECIMAL(14,4),@pmtmVolumen DECIMAL(14,4),@pmtmUndVol VARCHAR(10),@pmtmIdUnd VARCHAR(4),@pmtmIdEmp VARCHAR(4),@pmtmIdNat VARCHAR(4),@pmtmIdMnjo VARCHAR(4),@pmtmIdTmcia VARCHAR(4)
,@pmtmCdRango VARCHAR(4),@pmtmCases INT,@pmtmCajas INT,@pmtmPalets INT,@pmtmRemision DECIMAL(18,2),@pmtmNumOrden INT,@pmtmNitRemite VARCHAR(16),@pmtmRemitente VARCHAR(250),@pmtmDirOrigen VARCHAR(250),@pmtmIdOrigen VARCHAR(8),@pmtmNitDestntario VARCHAR(16),@pmtmDestinatario VARCHAR(250),@pmtmDirDestino VARCHAR(250),@pmtmIdDestino VARCHAR(8),@pmtmCdAgencia VARCHAR(16),@pmtmTipoVehic VARCHAR(10),@pmtmTarifClie MONEY,@pmtmTarifPago MONEY,@pmtmTarifTabla MONEY,@pmtmVrDeclarado MONEY,@pmtmVrSeguro MONEY
,@pmtmTarifSeguro DECIMAL(14,4),@pmtmDocCliente VARCHAR(30),@pmtmReferencia1 VARCHAR(50),@pmtmReferencia2 VARCHAR(50),@pmtmReferencia3 VARCHAR(50),@pmtmContenedor1 VARCHAR(50),@pmtmContenedor2 VARCHAR(50),@pmtmUndTarifa VARCHAR(10),@pmtmTipRem VARCHAR(3),@pmtmRemesa INT,@pmtmIdCiaRem CHAR(2),@pmtmItemRem INT,@pmtmNumMintrans DECIMAL(14,2),@pmtmPesoCont DECIMAL(14,4),@pmtmUndTarPago VARCHAR(10),@pmtmCodBodega VARCHAR(4),@pmtmTipoTraslado INT
,@pmtmTieCargue DECIMAL(14,4),@pmtmTieDesc DECIMAL(14,4),@pmtmFecIniCargue SMALLDATETIME,@pmtmFecFinCargue SMALLDATETIME,@pmtmFecInidesc SMALLDATETIME,@pmtmFecFindesc SMALLDATETIME
 AS 
 INSERT INTO tm_TraDetalle (tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol,tmIdUnd,tmIdEmp,tmIdNat,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCdAgencia,tmTipoVehic,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3
,tmContenedor1,tmContenedor2,tmUndTarifa,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc)
 VALUES (@pmtmNumero,@pmtmItem,@pmtmIdMercancia,@pmtmDescripMcias,@pmtmCantidad,@pmtmPesoNeto,@pmtmUndMed,@pmtmdmsAlto,@pmtmdmsAncho,@pmtmdmsLargo,@pmtmVolumen,@pmtmUndVol,@pmtmIdUnd,@pmtmIdEmp,@pmtmIdNat,@pmtmIdMnjo,@pmtmIdTmcia,@pmtmCdRango,@pmtmCases,@pmtmCajas,@pmtmPalets,@pmtmRemision,@pmtmNumOrden,@pmtmNitRemite,@pmtmRemitente,@pmtmDirOrigen,@pmtmIdOrigen,@pmtmNitDestntario,@pmtmDestinatario,@pmtmDirDestino,@pmtmIdDestino,@pmtmCdAgencia,@pmtmTipoVehic
,@pmtmTarifClie,@pmtmTarifPago,@pmtmTarifTabla,@pmtmVrDeclarado,@pmtmVrSeguro,@pmtmTarifSeguro,@pmtmDocCliente,@pmtmReferencia1,@pmtmReferencia2,@pmtmReferencia3,@pmtmContenedor1,@pmtmContenedor2,@pmtmUndTarifa,@pmtmTipRem,@pmtmRemesa,@pmtmIdCiaRem,@pmtmItemRem,@pmtmNumMintrans,@pmtmPesoCont,@pmtmUndTarPago,@pmtmCodBodega,@pmtmTipoTraslado,@pmtmTieCargue,@pmtmTieDesc,@pmtmFecIniCargue,@pmtmFecFinCargue,@pmtmFecInidesc,@pmtmFecFindesc)


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[paQrytm_TraDetalle] @pmtmNumero VARCHAR(5),@pmtmItem INT
 AS 
 SELECT tmNumero,tmItem,tmIdMercancia,tmDescripMcias,tmCantidad,tmPesoNeto,tmUndMed,tmdmsAlto,tmdmsAncho,tmdmsLargo,tmVolumen,tmUndVol
,tmIdUnd,tmIdEmp,tmIdNat,tmIdMnjo,tmIdTmcia,tmCdRango,tmCases,tmCajas,tmPalets,tmRemision,tmNumOrden
,tmNitRemite,tmRemitente,tmDirOrigen,tmIdOrigen,tmNitDestntario,tmDestinatario,tmDirDestino,tmIdDestino,tmCdAgencia,tmTipoVehic
,tmTarifClie,tmTarifPago,tmTarifTabla,tmVrDeclarado,tmVrSeguro,tmTarifSeguro,tmDocCliente,tmReferencia1,tmReferencia2,tmReferencia3,tmContenedor1,tmContenedor2,tmUndTarifa
,tmTipRem,tmRemesa,tmIdCiaRem,tmItemRem,tmNumMintrans,tmPesoCont,tmUndTarPago,tmCodBodega,tmTipoTraslado,tmTieCargue,tmTieDesc,tmFecIniCargue,tmFecFinCargue,tmFecInidesc,tmFecFindesc
 FROM tm_TraDetalle
 WHERE tmNumero=@pmtmNumero 
  AND (tmItem>=ISNULL(@pmtmItem,-1)  AND tmItem<=ISNULL(@pmtmItem,2147483647))
 ORDER BY tmItem


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryNotaProdOrd]  @pmNumOrden INT,@pmIdCia CHAR(2),@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT NumNota,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,VrCostos,CantEnt,CantSal,NumOrden,TipoProd,Modalidad
 FROM Trn_NotaProd
 WHERE  NumOrden=@pmNumOrden  AND IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND  Modalidad LIKE ISNULL(@pmModalidad,'%')
 ORDER BY IdCia,NumNota

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryNotaProd_Cr] @pmTipDoc VARCHAR(3),@pmNumNotaIni INT,@pmNumNotaFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT N.TipDoc AS Tip_Doc,TipoDoc,NumNota,N.IdCia AS CdCia,Compania,N.Fecha AS FechaDoc,N.IdConcepto AS CdConcepto,Concepto
,N.IdTercero AS NitTercero,RazonSocial,VrSubTotal,VrCostos,CantEnt,CantSal,N.NumOrden AS Num_Orden,N.IdCCosto AS CdCcosto,CC.CCosto AS NomCentCosto
,N.IdSubCos AS CdSubCent,SC.SubCosto AS SubcCosto,Modalidad,TipoProd,OrigenAdd,TipCom,TipoCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,N.TimeSys AS Fec_Add,N.FecUpdate AS Fec_Update,IdCiaCrea,N.IdUsuario AS CdUsuario,Usuario,Leyenda
,TipoId,Dv,T.Codigo AS CodTercero,NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,Telefono,Fax,e_mail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte
,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,TarifaIva,VrIvaEnt,VrIvaSal,TarifaDct,VrDctoEnt,VrDctoSal
,VrCostoEnt,VrCostoSal,VrReteEnt,VrReteSal,VrIcaEnt,VrIcaSal,VrBruto,CdUbic,NumLote,FechLote,K.CdCCosto AS KarCodCenCosto,KC.CCosto AS Kar_Ccosto
,K.CdSubCos AS KarCodSubcosto,KS.SubCosto AS Kar_Subcentro,pVehiculo,K.Referencia AS KarReferencia,Descripcion,Comptmntos,CdProdEquiv,TipOrd,K.NumOrden AS KarNumOrden,IdCiaOrd,Cotizacion,IdCiaCot
,Remision,IdCiaRem,Factura,TipDocDev,NumDocDev,Sobretasa,TasaNac,TasaDep,TasaMun,Soldicom,ImpGlobal
,OtroImpto,Unidades,ItemCombo,Servcios,EsCombo,EsProdBase,ListaPrec,VrBase,CdMoneda,VrTasaCamb,VrDivisa1,VrDivisa2,VrDivisa3,Referencia2,FecOrden
--productos
,PM.Referencia AS Prod_Referencia,TipoRef,PM.IdSubgrupo AS CdSubgrupo,Subgrupo,SG.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,Precio1,Precio2,Precio3,Precio4,Precio5,FecUltcom,FecUltVta,Seriales,Lotes,Combo,Tanques,DescripLong
 FROM Trn_NotaProd AS N  INNER JOIN Trn_Kardex AS K ON N.TipDoc=K.TipDoc AND N.NumNota=K.Documento AND N.IdCia=K.IdCia
 INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia  INNER JOIN Sys_TiposDoc AS TD ON N.TipDoc=TD.IdDoc
 INNER JOIN Terceros AS T ON N.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON N.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN ProdMcias  AS PM ON K.IdProducto=PM.IdProducto INNER JOIN Bodegas AS B ON K.IdBodega=B.IdBodega
 INNER JOIN UndMed AS UM ON K.IdUnd=UM.IdUnd  
 INNER JOIN SubGrupos AS SG ON PM.IdSubgrupo=SG.IdSubgrupo INNER JOIN Grupos AS G ON SG.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 CentroCosto AS KC ON K.CdCCosto=KC.IdCCosto LEFT JOIN SubCentros AS KS ON K.CdSubCos=KS.IdSubCos
 LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 LEFT JOIN TiposCom AS TC ON N.TipCom=TC.IdCom
 WHERE N.TipDoc=@pmTipDoc AND NumNota BETWEEN @pmNumNotaIni AND @pmNumNotaFin AND N.IdCia=@pmIdCia
 ORDER BY NumNota,Item

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryNotaProdRel] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdConcepto VARCHAR(4)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdCCosto VARCHAR(16)=Null,@pmModalidad VARCHAR(10)=Null,@pmAnulado BIT=Null
 AS 
 SELECT TipDoc,NumNota,N.IdCia AS CdCia,Compania,Fecha,N.IdConcepto AS CdConcepto,Concepto,N.IdTercero AS NitTercero,RazonSocial,VrSubTotal,VrCostos
,CantEnt,CantSal,NumOrden,N.IdCCosto AS CdCcosto,CCosto,N.IdSubCos AS CdSubCent,SubCosto,Modalidad,TipoProd,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev
,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,TimeSys,N.FecUpdate AS Fec_Update,IdCiaCrea,N.IdUsuario AS CdUsuario,Usuario
,TipoId,Dv,T.Codigo AS CodTercero,NomCial,T.Direccion AS Terc_Direccion,T.IdLocal AS CdLocal,Localidad,L.IdDep AS CdDep,Departamento,Telefono,Fax,e_mail
,T.IdSector AS CdSector,SectorEco,T.IdRegimen AS CdRegimen,Regimen,TipEnte
 FROM Trn_NotaProd AS N  INNER JOIN Companias AS CN ON N.IdCia=CN.IdCia
 INNER JOIN Terceros AS T ON N.IdTercero=T.IdTercero INNER JOIN  Conceptos AS C ON N.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado   INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON N.IdCCosto=CC.IdCCosto
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 LEFT JOIN SubCentros AS SC ON N.IdSubCos=SC.IdSubCos
 WHERE TipDoc=@pmTipDoc AND Fecha BETWEEN @pmFechaIni AND @pmFechaFin
 AND N.IdCia LIKE ISNULL(@pmIdCia,'%%') AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
 AND N.IdTercero LIKE ISNULL(@pmIdTercero,'%')  AND N.IdCCosto LIKE ISNULL(@pmIdCCosto ,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY N.IdCia,NumNota

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsNotaProd] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4)
,@pmIdTercero VARCHAR(16),@pmVrSubTotal MONEY,@pmVrCostos MONEY,@pmCantEnt DECIMAL(14,4),@pmCantSal DECIMAL(14,4),@pmNumOrden INT
,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16),@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2)
,@pmAnulado BIT,@pmNumDev INT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipoProd INT
,@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_NotaProd (TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,VrCostos,CantEnt,CantSal,NumOrden,IdCCosto,IdSubCos,Modalidad,TipoProd,OrigenAdd
,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario)
 VALUES (@pmTipDoc,@pmNumNota,@pmIdCia,@pmFecha,@pmIdConcepto,@pmIdTercero,@pmVrSubTotal,@pmVrCostos,@pmCantEnt,@pmCantSal,@pmNumOrden
,@pmIdCCosto,@pmIdSubCos,@pmModalidad,@pmTipoProd,@pmOrigenAdd,@pmTipCom,@pmComprobante,@pmIdCiaCom,@pmAnulado,@pmNumDev,@pmFecDev
,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario)


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpNotaProd] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmIdConcepto VARCHAR(4),@pmIdTercero VARCHAR(16)
,@pmVrSubTotal MONEY,@pmVrCostos MONEY,@pmCantEnt DECIMAL(14,4),@pmCantSal DECIMAL(14,4),@pmNumOrden INT,@pmIdCCosto VARCHAR(16),@pmIdSubCos VARCHAR(16)
,@pmModalidad VARCHAR(10),@pmTipCom VARCHAR(3),@pmComprobante INT,@pmIdCiaCom CHAR(2),@pmAnulado BIT,@pmNumDev INT
,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(250),@pmIdEstado VARCHAR(4),@pmTipoProd INT,@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_NotaProd SET Fecha=@pmFecha,IdConcepto=@pmIdConcepto,IdTercero=@pmIdTercero,VrSubTotal=@pmVrSubTotal,VrCostos=@pmVrCostos,CantEnt=@pmCantEnt,CantSal=@pmCantSal
,NumOrden=@pmNumOrden,IdCCosto=@pmIdCCosto,IdSubCos=@pmIdSubCos,Modalidad=@pmModalidad,TipCom=@pmTipCom,Comprobante=@pmComprobante
,IdCiaCom=@pmIdCiaCom,Anulado=@pmAnulado,NumDev=@pmNumDev,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,TipoProd=@pmTipoProd,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryNotaProd] @pmTipDoc VARCHAR(3),@pmNumNota INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumNota,IdCia,Fecha,IdConcepto,IdTercero,VrSubTotal,VrCostos,CantEnt,CantSal,NumOrden,IdCCosto,IdSubCos,Modalidad,TipoProd
,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,Observacion,IdEstado,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_NotaProd
 WHERE TipDoc=@pmTipDoc AND NumNota=@pmNumNota AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryNotaProdLta] @pmFechaIni SMALLDATETIME, @pmFechaFin SMALLDATETIME,@pmNumNotaIni INT=Null,@pmNumNotaFin INT=Null
,@pmIdCia CHAR(2)=Null,@pmIdTercero VARCHAR(16)=Null,@pmIdConcepto VARCHAR(4)=Null,@pmIdEstado VARCHAR(4)=Null,@pmAnulado BIT=Null
,@pmModalidad VARCHAR(10)=Null
 AS 
 SELECT NumNota,IdCia,Fecha,N.IdConcepto AS CdConcepto,Concepto,N.IdTercero AS NitTercero,RazonSocial,VrSubTotal,VrCostos,CantEnt,CantSal,NumOrden,N.IdCCosto AS CdCcosto,CCosto,IdSubCos
,Modalidad,TipoProd,OrigenAdd,TipCom,Comprobante,IdCiaCom,Anulado,NumDev,FecDev,N.Observacion AS Observ,N.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,N.IdUsuario AS IdUsuari,Usuario
 FROM Trn_NotaProd AS N INNER JOIN Terceros AS T ON N.IdTercero=T.IdTercero
 INNER JOIN  Conceptos AS C ON N.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON N.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON N.IdUsuario=U.IdUsuario
 INNER JOIN CentroCosto AS CC ON  N.IdCCosto=CC.IdCCosto
 WHERE  Fecha BETWEEN @pmFechaIni AND @pmFechaFin 
 AND NumNota BETWEEN ISNULL(@pmNumNotaIni,0) AND ISNULL(@pmNumNotaFin,2147483647)
 AND IdCia LIKE ISNULL(@pmIdCia,'%%')  AND N.IdTercero LIKE ISNULL(@pmIdTercero,'%')
 AND N.IdConcepto LIKE ISNULL(@pmIdConcepto,'%') AND N.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 AND Modalidad LIKE ISNULL(@pmModalidad,'%')
 AND (Anulado=ISNULL(@pmAnulado,0) or Anulado=ISNULL(@pmAnulado,1))
 ORDER BY IdCia,NumNota