if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInsReqDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInsReqDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paInstm_ReqDetalle_Sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paInstm_ReqDetalle_Sel]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenOper_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenOper_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServ_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServ_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryMudOrdenServLta]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryMudOrdenServLta]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryReqDetalle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryReqDetalle]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicion_Cr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicion_Cr]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryRequisicionDet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryRequisicionDet]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQrytm_ReqDetalleDso]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQrytm_ReqDetalleDso]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraDecAduanaLtd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraDecAduanaLtd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraManifIntLtd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraManifIntLtd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTraPorteIntLtd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTraPorteIntLtd]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paUpMudOrdenServ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paUpMudOrdenServ]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraPorteIntLtd] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecEmision,O.FecCargue,O.FecEntrega,DPO.IdPais AS CdPaisOrig,POR.NombrePais AS PaisOrig,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdOrigen,LO.Localidad AS DescOrigen
,DPD.IdPais AS CdPaisDest,PDE.NombrePais AS PaisDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,O.IdDestino,LD.Localidad AS DescDestino,O.IdNotificado,O.Notificado,O.IdRemitente,O.Remitente,O.DirOrigen
,O.IdDestinatario,O.Destinatario,O.DirDestino,O.IdConsig,O.Consignatario,O.DirConsig,O.InsTransporte,O.DocRemitente,O.ValorTotal,M.Simbolo,O.IdMoneda AS CdMoneda,M.Mneda AS Moneda,O.PesoTotal,O.Manifiesto,O.CdCiaManif
,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.OrigenAdd,O.IdUsuario AS CdUsuario,Usuario
--detalles
,D.Item,D.CdMercancia,D.DescripMcias,D.Cantidad,D.PesoNeto,D.PesoBruto,D.Volumen,D.ValorMcia,D.IdMoneda AS DetIdMoneda,MN.Mneda AS DetMoneda,MN.Simbolo AS DetMonSimb,D.IdUnd AS CdUnd,UM.Unidad,D.IdEmp AS CdEmp,Empaque
,D.IdNat AS CdNat,Natlzaprod,D.UndMed,UP.Unidad AS DesUndPeso,D.UndVol,UV.Unidad AS DesUndVol,D.Bultos,D.Observacion AS DetObservacion
 FROM Trn_TraPorteInt AS O INNER JOIN Companias AS C ON O.IdCia=C.IdCia
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN adm_Paises AS POR ON DPO.IdPais=POR.IdPais
 INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep INNER JOIN adm_Paises AS PDE ON DPD.IdPais=PDE.IdPais
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS E ON O.IdEstado=E.IdEstado
 INNER JOIN Trn_TraPorteIntMcia AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN adm_monedas AS MN ON D.IdMoneda=MN.IdMneda
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat
 LEFT JOIN adm_monedas AS M ON O.IdMoneda=M.IdMneda LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp 
 LEFT JOIN Sys_Um AS UP ON D.UndMed=UP.UndMed LEFT JOIN Sys_Um AS UV ON D.UndVol=UV.UndMed
 WHERE O.TipDoc=@pmTipDoc AND O.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDecAduanaLtd] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.NumDeclara,O.IdCia AS CdCia,Compania,O.Fecha,O.FecEmision,DPO.IdPais AS CdPaisOrig,POR.NombrePais AS PaisOrig,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,O.IdOrigen,LO.Localidad AS DescOrigen
,DPD.IdPais AS CdPaisDest,PDE.NombrePais AS PaisDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,O.IdDestino,LD.Localidad AS DescDestino
,O.IdAduCarga,AC.NomAduana AS AduanaCargo,PAC.NombrePais AS PaisAduCargo,O.IdAduOrigen,AP.NomAduana AS AduanaOrigen,PAP.NombrePais AS PaisAduOrigen,O.IdAduDestino,AD.NomAduana AS AduanaDestino,PAD.NombrePais AS PaisAduDestino
,O.IdAduFrontera,AF.NomAduana AS AduanaFrontera,PAF.NombrePais AS PaisAduFront,O.IdDeclarante,Declarante,DirDeclara,CdLocalDec,LDC.Localidad AS CiudadDec,O.IdRemitente,O.Remitente,DirOrigen,CdLocalRem,LRM.Localidad AS CiudadRemite
,O.IdDestinatario,Destinatario,DirDestino,CdLocalDest,LDT.Localidad AS CiudadDest,O.IdConsig,Consignatario,DirConsig,CdLocalConsig,LCN.Localidad AS CiudadConsig,O.IdMoneda AS CdMoneda,M.Simbolo,M.Mneda AS Moneda
,O.ValorTotal,O.PesoTotal,O.DocAnexos,O.Vehiculos,O.Remolques,O.Manifiestos,O.Contenedores,O.Precintos,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
,D.Item,D.TipOrd,D.NumOrden,D.IdCiaOrd,D.CdMercancia,D.DescripMcias,D.Cantidad,D.PesoNeto,D.PesoBruto,D.Volumen,D.ValorMcia,D.IdMoneda AS DetIdMoneda,MN.Mneda AS DetMoneda,MN.Simbolo AS DetMonSimb,D.IdUnd AS CdUnd,UM.Unidad
,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.UndMed,UP.Unidad AS DesUndPeso,D.UndVol,UV.Unidad AS DesUndVol,D.Bultos
 FROM Trn_TraDecAduana AS O INNER JOIN Companias AS C ON O.IdCia=C.IdCia
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN adm_Paises AS POR ON DPO.IdPais=POR.IdPais
 INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep INNER JOIN adm_Paises AS PDE ON DPD.IdPais=PDE.IdPais
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS E ON O.IdEstado=E.IdEstado
 INNER JOIN Aduanas AS AC ON O.IdAduCarga=AC.IdAduana INNER JOIN adm_Paises AS PAC ON AC.IdPais=PAC.IdPais
 INNER JOIN Aduanas AS AP ON O.IdAduOrigen=AP.IdAduana INNER JOIN adm_Paises AS PAP ON AP.IdPais=PAP.IdPais
 INNER JOIN Aduanas AS AD ON O.IdAduDestino=AD.IdAduana INNER JOIN adm_Paises AS PAD ON AD.IdPais=PAD.IdPais
 INNER JOIN Trn_TraDecMcia AS D ON O.TipDoc=D.TipDoc AND O.NumDeclara=D.NumDeclara AND O.IdCia=D.IdCia
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN adm_monedas AS MN ON D.IdMoneda=MN.IdMneda  
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat
 LEFT JOIN adm_monedas AS M ON O.IdMoneda=M.IdMneda
 LEFT JOIN Aduanas AS AF ON O.IdAduFrontera=AF.IdAduana LEFT JOIN adm_Paises AS PAF ON AF.IdPais=PAF.IdPais
 LEFT JOIN Localidades AS LDC ON O.CdLocalDec=LDC.IdLocal LEFT JOIN Localidades AS LRM ON O.CdLocalRem=LRM.IdLocal
 LEFT JOIN Localidades AS LDT ON O.CdLocalDest=LDT.IdLocal LEFT JOIN Localidades AS LCN ON O.CdLocalConsig=LCN.IdLocal
 LEFT JOIN Sys_Um AS UP ON D.UndMed=UP.UndMed LEFT JOIN Sys_Um AS UV ON D.UndVol=UV.UndMed
 LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 WHERE O.TipDoc=@pmTipDoc AND O.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraManifIntLtd] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT M.Manifiesto,M.IdCia AS CdCia,Compania,M.Fecha,M.FecEmision,DPO.IdPais AS CdPaisOrig,POR.NombrePais AS PaisOrig,LO.IdDep AS CdDepOrig,DPO.Departamento AS DptoOrigen,M.IdOrigen,LO.Localidad AS DescOrigen
,DPD.IdPais AS CdPaisDest,PDE.NombrePais AS PaisDestino,LD.IdDep AS CdDepDest,DPD.Departamento AS DptoDestino,M.IdDestino,LD.Localidad AS DescDestino
,M.IdVehiculo AS PlacaVeh,M.nRemolque,M.IdConductor AS CdConductor,NC.RazonSocial AS CondTitula,M.IdCondRelev,NCA.RazonSocial AS CondAuxiliar,NatCarga,M.CdAduana,AF.NomAduana AS AduanaFront,M.CdAduanaDest,AD.NomAduana AS AduanaDest
,M.IdMoneda AS CdMoneda,MN.Simbolo,MN.Mneda AS Moneda,M.ValorTotal,M.PesoTotal,M.NumMuc,M.CdCiaMuc,M.Anulado,M.FecDev
,M.Observacion AS Observ,M.IdEstado AS CdEstado,Estado,M.OrigenAdd,M.TimeSys AS FechaCrea,M.FecUpdate,M.IdCiaCrea AS CdCiaCrea,M.IdUsuario AS CdUsuario,Usuario
,D.Item,D.TipOrd,D.NumOrden,D.IdCiaOrd,D.CdMercancia,D.DescripMcias,D.Cantidad,D.PesoNeto,D.PesoBruto,D.Volumen,D.ValorMcia,D.IdMoneda AS DetIdMoneda,MD.Mneda AS DetMoneda,MD.Simbolo AS DetMonSimb,D.IdUnd AS CdUnd,UM.Unidad
,D.IdEmp AS CdEmp,Empaque,D.IdNat AS CdNat,Natlzaprod,D.UndMed,UP.Unidad AS DesUndPeso,D.UndVol,UV.Unidad AS DesUndVol,D.Bultos,CP.DocRemitente
 FROM Trn_TraManifInt AS M INNER JOIN Companias AS C ON M.IdCia=C.IdCia INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo
 INNER JOIN Localidades AS LO ON M.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep INNER JOIN adm_Paises AS POR ON DPO.IdPais=POR.IdPais
 INNER JOIN Localidades AS LD ON M.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep INNER JOIN adm_Paises AS PDE ON DPD.IdPais=PDE.IdPais
 INNER JOIN Terceros AS NC ON M.IdConductor=NC.IdTercero
 INNER JOIN adm_Usuarios AS U ON M.IdUsuario=U.IdUsuario INNER JOIN EstadoDoc AS E ON M.IdEstado=E.IdEstado
 INNER JOIN Trn_TraManifIntMcia AS D ON M.TipDoc=D.TipDoc AND M.Manifiesto=D.Manifiesto AND M.IdCia=D.IdCia
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN adm_monedas AS MD ON D.IdMoneda=MD.IdMneda
 INNER JOIN TiposNat AS NZ ON D.IdNat=NZ.IdNat 
 LEFT JOIN Terceros AS NCA ON M.IdCondRelev=NCA.IdTercero LEFT JOIN Aduanas AS AF ON M.CdAduana=AF.IdAduana LEFT JOIN Aduanas AS AD ON M.CdAduanaDest=AD.IdAduana
 LEFT JOIN adm_monedas AS MN ON M.IdMoneda=MN.IdMneda 
 LEFT JOIN Trn_TraPorteInt AS CP ON D.TipOrd=CP.TipDoc AND D.NumOrden=CP.NumOrden AND D.IdCiaOrd=CP.IdCia
 LEFT JOIN Sys_Um AS UP ON D.UndMed=UP.UndMed LEFT JOIN Sys_Um AS UV ON D.UndVol=UV.UndMed LEFT JOIN Empaques AS EP ON D.IdEmp=EP.IdEmp
 WHERE M.TipDoc=@pmTipDoc AND M.FecEmision BETWEEN @pmFechaIni AND @pmFechaFin AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc,TipoDoc,O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro,O.IdVend,VN.RazonSocial AS NomVendedor
,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,D.Item,D.IdConcepto AS CdConcepto,D.Descripcion,CD.Concepto,D.ValorTotal,D.TarifaIva,D.TipoConc,D.TipoRubro,D.CdCuenta,D.NitTercero,NT.RazonSocial AS NomTercero
,D.Referencia AS DetReferencia,D.NumDocRef,D.TipoReg,TS.Transporte,TS.Bodega,TS.Empaque,TS.Personal
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
,T.Codigo AS TercCod,T.TipoId,T.Dv,T.Direccion,T.IdLocal AS TercCdLocal,L.Localidad AS TercLocalidad,T.Telefono AS TercTelefono,T.TelMovil AS TercTerCelular,T.e_mail AS TercEmail
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero INNER JOIN TercCliente AS CLI ON O.IdCliente=CLI.IdClie
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario INNER JOIN Sys_TiposDoc AS TD ON O.TipDoc=TD.IdDoc
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Trn_MudOrdenConc AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.IdCia
 INNER JOIN ConcDiversos AS CD ON D.IdConcepto=CD.IdConcepto 
 LEFT JOIN Terceros AS NT ON D.NitTercero=NT.IdTercero
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
 AS 
 SELECT O.NumOrden,O.IdCia AS CdCia,Compania,O.Fecha,O.FecDespacho,O.IdTipoServ AS CdTipoServ,TipoServicio,O.IdCliente,T.RazonSocial AS NomCliente,O.IdAgencia AS CdAgencia,Agencia
,O.IdOrigen,LO.Localidad AS CiuOrigen,O.IdDestino,LD.Localidad AS CiuDestino,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest
,O.VrServicio,O.VrEmpaque,O.VrBodega,O.VrOtros,O.VrDescuento,O.VrImpuesto,O.VrNeto,O.VrDeclarado,O.VrSeguro,O.IdVend,VN.RazonSocial AS NomVendedor
,O.NomContacto,O.TelsContacto,O.emlContacto,O.ContactoDest,O.emlContDest,O.Servicios,O.FormaPago,O.Referencia,O.TipInv,O.NumInvent,O.IdCiaInv,O.TipRem,O.NumRemesa,O.IdCiaRem
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,O.TipCausac,O.Causacion,O.CdCiaCau,EstCumplido,FechaCump,TipoRuta,O.Volumen,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,Estado
,O.OrigenAdd,O.TimeSys AS FechaCrea,O.FecUpdate,O.IdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
 FROM Trn_MudOrdenServ AS O INNER JOIN Companias AS CN ON O.IdCia=CN.IdCia INNER JOIN Terceros AS T ON O.IdCliente=T.IdTercero
 INNER JOIN Terceros AS VN ON O.IdVend=VN.IdTercero INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado
 INNER JOIN MudServicios AS TS ON O.IdTipoServ=TS.IdTipoServ 
 INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal INNER JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal
 LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia 
 WHERE O.TipDoc=@pmTipDoc AND O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenOper_Cr] @pmTipDoc VARCHAR(3),@pmNumOrdenIni INT,@pmNumOrdenFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT O.TipDoc,O.NumOrden,O.IdCia,O.Item,O.IdOperario,NomOperario,O.IdTipoOper,TipoOperario
,O.CdConcepto,C.Concepto,C.IdCuenta,C.TipoConc,TarifaCargo
 FROM Trn_MudOrdenOper AS O INNER JOIN TiposOperarios AS TP ON O.IdTipoOper=TP.IdOper
 LEFT JOIN Terceros AS T ON O.IdOperario=T.IdTercero
 LEFT JOIN TraConcCausac AS C ON O.CdConcepto=C.IdConcepto
 WHERE O.TipDoc=@pmTipDoc AND O.NumOrden BETWEEN @pmNumOrdenIni AND @pmNumOrdenFin AND O.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQrytm_ReqDetalleDso] @pmtmNumero VARCHAR(5)
 AS 
 SELECT tmItem,tmCdProducto,tmDescripcion,tmCantidad,tmIdUnd,Unidad,tmVrUnitario,tmCantidad*tmVrUnitario AS ValorTotal
,tmTipOrd,tmNumOrden,tmIdCiaOrd,tmTipSal,tmNSalida,tmIdCiaSal,tmFechaSal,tmCantSalida,tmIdSubgrupo,tmCdBodega,tmObservacion,tmVrPrecio,tmNumero
 FROM tm_ReqDetalle AS D INNER JOIN UndMed AS U ON D.tmIdUnd=U.IdUnd
 WHERE tmNumero=@pmtmNumero
 ORDER BY tmItem
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paInstm_ReqDetalle_Sel] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
,@pmtmNumero VARCHAR(5)
 AS 
 INSERT INTO tm_ReqDetalle (tmNumero,tmItem,tmCdProducto,tmDescripcion,tmIdSubgrupo,tmCdBodega,tmCantidad
,tmIdUnd,tmVrUnitario,tmTipOrd,tmNumOrden,tmIdCiaOrd,tmTipSal,tmNSalida,tmIdCiaSal,tmFechaSal
,tmCantSalida,tmIdOperario,tmCdCenServ,tmNitTercero,tmNumVehic,tmNumParte,tmEstado,tmObservacion,tmCodConc,tmVrPrecio)
 SELECT  @pmtmNumero,Item,CdProducto,Descripcion,IdSubgrupo,CdBodega,Cantidad,IdUnd,VrUnitario,TipOrd,NumOrden,IdCiaOrd,TipSal,NSalida,IdCiaSal,FechaSal
,(CantSalida-ISNULL(CantDevSal,0)),'0','','0','','',0,Referencia,'',0
 FROM Trn_ReqDetalle
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicion_Cr] @pmTipDoc VARCHAR(3),@pmRequisicionIni INT,@pmRequisicionFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT R.TipDoc AS CdTipo,TipoDoc,R.Requisicion AS NumRequis,R.IdCia AS CdCia,Compania,Fecha,FechaVence,R.IdConcepto AS CdConcepto,Concepto,IdRespons,T.RazonSocial AS NomResponsable
,R.IdCCosto AS CdCentCosto,CCosto,R.IdSubCos AS CdSubCent,R.IdDep AS CdDep,Dependencia,VrSubTotal,R.Cantidad AS CantTotal,R.NContrato AS NumContrato,IdCiaCont
,NitCliente,CL.RazonSocial AS NomCliente,CdAgencia,Agencia,CodAgencia,Modalidad,DirEntrega,IdLocEnt,Localidad,Departamento,R.TipSal AS Tip_Sal,NumSalida,R.IdCiaSal AS CdCiaSal
,R.FechaSal AS Fec_Salida,NumAprob,FecAprob,CdUsuAprob,OrigenAdd,Anulado,NomContacto,TelsContacto,EmailContacto,Num_Vehic,Num_Trailer,TipoVigencia,FecDev,R.Observacion AS Observ
,R.IdEstado AS CdEstado,Estado,TimeSys,FecUpdate,IdCiaCrea,R.IdUsuario AS IdUsuari,Usuario
,Item,CdProducto,Descripcion,D.Cantidad AS CantArt,D.IdUnd AS CdUnid,Unidad,D.IdSubgrupo AS CdSubgrupo,Subgrupo,VrUnitario,TipOrd,NumOrden,IdCiaOrd,D.TipSal AS DetTipoSal,NSalida,D.IdCiaSal AS DetCiaSal
,D.FechaSal AS DetFecSalida,CdBodega,Bodega,D.Referencia,D.CantSalida,D.CantDevSal
 FROM Trn_Requisicion AS R INNER JOIN Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion AND R.IdCia=D.IdCia
 INNER JOIN Terceros AS T ON R.IdRespons=T.IdTercero
 INNER JOIN CentroCosto AS CC ON R.IdCCosto=CC.IdCCosto
 INNER JOIN Conceptos AS C ON R.IdConcepto=C.IdConcepto
 INNER JOIN EstadoDoc AS ED ON R.IdEstado=ED.IdEstado
 INNER JOIN adm_Usuarios AS U ON R.IdUsuario=U.IdUsuario 
 INNER JOIN Sys_TiposDoc AS TD ON R.TipDoc=TD.IdDoc
 INNER JOIN Companias AS CI ON R.IdCia=CI.IdCia
 INNER JOIN Dependencias AS DP ON R.IdDep=DP.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd
 INNER JOIN SubGrupos AS S ON D.IdSubgrupo=S.IdSubgrupo
 INNER JOIN Localidades AS L ON R.IdLocEnt=L.IdLocal
 INNER JOIN Departamentos AS DT ON L.IdDep=DT.IdDep
 LEFT JOIN Terceros AS CL ON R.NitCliente =CL.IdTercero
 LEFT JOIN Bodegas AS BG ON D.CdBodega =BG.IdBodega
 LEFT JOIN Agencias AS A ON R.CdAgencia=A.IdAgencia
 WHERE R.TipDoc=@pmTipDoc AND R.Requisicion BETWEEN  @pmRequisicionIni AND @pmRequisicionFin 
 AND R.IdCia=@pmIdCia 
 ORDER BY  R.Requisicion,Item
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmVrServicio MONEY,@pmVrEmpaque MONEY,@pmVrBodega MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrNeto MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmServicios VARCHAR(50),@pmFormaPago VARCHAR(50),@pmReferencia VARCHAR(50)
,@pmTipInv VARCHAR(3),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTipRem VARCHAR(3),@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmTipCausac VARCHAR(3),@pmCausacion INT,@pmCdCiaCau CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContactoDest VARCHAR(150),@pmemlContDest VARCHAR(100)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmEstCumplido INT,@pmFechaCump SMALLDATETIME,@pmTipoRuta VARCHAR(10),@pmVolumen VARCHAR(50),@pmFecUpdate SMALLDATETIME
 AS 
 UPDATE Trn_MudOrdenServ SET Fecha=@pmFecha,FecDespacho=@pmFecDespacho,IdTipoServ=@pmIdTipoServ,IdCliente=@pmIdCliente,IdAgencia=@pmIdAgencia,IdVend=@pmIdVend,DirCargue=@pmDirCargue,TipoInmRem=@pmTipoInmRem,PisoCargue=@pmPisoCargue,TelsRemite=@pmTelsRemite,CelRemite=@pmCelRemite,DirDescargue=@pmDirDescargue,TipoInmDest=@pmTipoInmDest,PisoDescargue=@pmPisoDescargue,TelsDest=@pmTelsDest,CelDest=@pmCelDest
,IdOrigen=@pmIdOrigen,IdDestino=@pmIdDestino,VrServicio=@pmVrServicio,VrEmpaque=@pmVrEmpaque,VrBodega=@pmVrBodega,VrOtros=@pmVrOtros,VrDescuento=@pmVrDescuento,VrImpuesto=@pmVrImpuesto,VrNeto=@pmVrNeto,VrDeclarado=@pmVrDeclarado,VrSeguro=@pmVrSeguro,Servicios=@pmServicios,FormaPago=@pmFormaPago,Referencia=@pmReferencia,TipInv=@pmTipInv,NumInvent=@pmNumInvent,IdCiaInv=@pmIdCiaInv,TipRem=@pmTipRem,NumRemesa=@pmNumRemesa,IdCiaRem=@pmIdCiaRem
,EstFactura=@pmEstFactura,TipFact=@pmTipFact,NumFactura=@pmNumFactura,CdCiaFact=@pmCdCiaFact,TipCausac=@pmTipCausac,Causacion=@pmCausacion,CdCiaCau=@pmCdCiaCau,NomContacto=@pmNomContacto,TelsContacto=@pmTelsContacto,emlContacto=@pmemlContacto,ContactoDest=@pmContactoDest,emlContDest=@pmemlContDest,Anulado=@pmAnulado,FecDev=@pmFecDev,Observacion=@pmObservacion,IdEstado=@pmIdEstado,EstCumplido=@pmEstCumplido
,FechaCump=@pmFechaCump,TipoRuta=@pmTipoRuta,Volumen=@pmVolumen,FecUpdate=@pmFecUpdate
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2),@pmFecha SMALLDATETIME,@pmFecDespacho SMALLDATETIME,@pmIdTipoServ VARCHAR(4),@pmIdCliente VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdVend VARCHAR(16),@pmDirCargue VARCHAR(500),@pmTipoInmRem VARCHAR(30),@pmPisoCargue VARCHAR(10),@pmTelsRemite VARCHAR(30),@pmCelRemite VARCHAR(30),@pmDirDescargue VARCHAR(500)
,@pmTipoInmDest VARCHAR(30),@pmPisoDescargue VARCHAR(10),@pmTelsDest VARCHAR(30),@pmCelDest VARCHAR(30),@pmIdOrigen VARCHAR(8),@pmIdDestino VARCHAR(8),@pmVrServicio MONEY,@pmVrEmpaque MONEY,@pmVrBodega MONEY,@pmVrOtros MONEY,@pmVrDescuento MONEY,@pmVrImpuesto MONEY,@pmVrNeto MONEY,@pmVrDeclarado MONEY,@pmVrSeguro MONEY,@pmServicios VARCHAR(50),@pmFormaPago VARCHAR(50),@pmReferencia VARCHAR(50)
,@pmTipInv VARCHAR(3),@pmNumInvent INT,@pmIdCiaInv CHAR(2),@pmTipRem VARCHAR(3),@pmNumRemesa INT,@pmIdCiaRem CHAR(2),@pmEstFactura INT,@pmTipFact VARCHAR(3),@pmNumFactura INT,@pmCdCiaFact CHAR(2),@pmTipCausac VARCHAR(3),@pmCausacion INT,@pmCdCiaCau CHAR(2),@pmNomContacto VARCHAR(150),@pmTelsContacto VARCHAR(30),@pmemlContacto VARCHAR(100),@pmContactoDest VARCHAR(150),@pmemlContDest VARCHAR(100)
,@pmAnulado BIT,@pmFecDev SMALLDATETIME,@pmObservacion VARCHAR(2500),@pmIdEstado VARCHAR(4),@pmEstCumplido INT,@pmFechaCump SMALLDATETIME,@pmTipoRuta VARCHAR(10),@pmVolumen VARCHAR(50),@pmOrigenAdd VARCHAR(10),@pmTimeSys SMALLDATETIME,@pmIdCiaCrea CHAR(2),@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO Trn_MudOrdenServ (TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdVend,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,VrServicio,VrEmpaque,VrBodega,VrOtros,VrDescuento,VrImpuesto,VrNeto,VrDeclarado,VrSeguro,Servicios,FormaPago,Referencia,TipInv,NumInvent,IdCiaInv
,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact,TipCausac,Causacion,CdCiaCau,NomContacto,TelsContacto,emlContacto,ContactoDest,emlContDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,TimeSys,IdCiaCrea,IdUsuario,EstCumplido,FechaCump,TipoRuta,Volumen)
 VALUES (@pmTipDoc,@pmNumOrden,@pmIdCia,@pmFecha,@pmFecDespacho,@pmIdTipoServ,@pmIdCliente,@pmIdAgencia,@pmIdVend,@pmDirCargue,@pmTipoInmRem,@pmPisoCargue,@pmTelsRemite,@pmCelRemite,@pmDirDescargue,@pmTipoInmDest,@pmPisoDescargue,@pmTelsDest,@pmCelDest,@pmIdOrigen,@pmIdDestino,@pmVrServicio,@pmVrEmpaque,@pmVrBodega,@pmVrOtros,@pmVrDescuento,@pmVrImpuesto,@pmVrNeto,@pmVrDeclarado,@pmVrSeguro
,@pmServicios,@pmFormaPago,@pmReferencia,@pmTipInv,@pmNumInvent,@pmIdCiaInv,@pmTipRem,@pmNumRemesa,@pmIdCiaRem,@pmEstFactura,@pmTipFact,@pmNumFactura,@pmCdCiaFact,@pmTipCausac,@pmCausacion,@pmCdCiaCau,@pmNomContacto,@pmTelsContacto,@pmemlContacto,@pmContactoDest,@pmemlContDest,@pmOrigenAdd,@pmAnulado,@pmFecDev,@pmObservacion,@pmIdEstado,@pmTimeSys,@pmIdCiaCrea,@pmIdUsuario,@pmEstCumplido,@pmFechaCump,@pmTipoRuta,@pmVolumen)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryMudOrdenServ] @pmTipDoc VARCHAR(3),@pmNumOrden INT,@pmIdCia CHAR(2)
 AS 
 SELECT TipDoc,NumOrden,IdCia,Fecha,FecDespacho,IdTipoServ,IdCliente,IdAgencia,IdVend,DirCargue,TipoInmRem,PisoCargue,TelsRemite,CelRemite,DirDescargue,TipoInmDest,PisoDescargue,TelsDest,CelDest,IdOrigen,IdDestino,VrServicio,VrEmpaque,VrBodega,VrOtros,VrDescuento,VrImpuesto,VrNeto,VrDeclarado,VrSeguro,Servicios,FormaPago,Referencia,TipInv,NumInvent,IdCiaInv,TipRem,NumRemesa,IdCiaRem,EstFactura,TipFact,NumFactura,CdCiaFact
,TipCausac,Causacion,CdCiaCau,NomContacto,TelsContacto,emlContacto,ContactoDest,emlContDest,OrigenAdd,Anulado,FecDev,Observacion,IdEstado,EstCumplido,FechaCump,TipoRuta,Volumen,TimeSys,FecUpdate,IdCiaCrea,IdUsuario
 FROM Trn_MudOrdenServ
 WHERE TipDoc=@pmTipDoc AND NumOrden=@pmNumOrden AND IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryReqDetalle] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
 AS 
 SELECT Item,CdProducto,Descripcion,Cantidad,IdUnd,IdSubgrupo,VrUnitario
,TipOrd,NumOrden,IdCiaOrd,TipSal,NSalida,IdCiaSal,FechaSal,CdBodega,Referencia,CantSalida,CantDevSal
 FROM Trn_ReqDetalle
 WHERE TipDoc=@pmTipDoc AND Requisicion=@pmRequisicion AND IdCia=@pmIdCia
 ORDER BY  Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryRequisicionDet] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2)
 AS 
 SELECT Fecha,IdRespons,IdCCosto,IdSubCos,IdDep,NContrato,IdCiaCont,NitCliente,CdAgencia,Modalidad
,DirEntrega,IdLocEnt,NumAprob,FecAprob,CdUsuAprob,OrigenAdd,Anulado,FecDev,Observacion,IdEstado
,Item,CdProducto,Descripcion,IdSubgrupo,CdBodega,D.Cantidad AS CantArt,IdUnd,VrUnitario,D.Referencia
,D.TipOrd,D.NumOrden,D.IdCiaOrd,D.CantSalida,D.CantDevSal
 FROM Trn_Requisicion AS R INNER JOIN Trn_ReqDetalle AS D ON R.TipDoc=D.TipDoc AND R.Requisicion=D.Requisicion
 AND R.IdCia=D.IdCia
 WHERE R.TipDoc=@pmTipDoc AND R.Requisicion=@pmRequisicion AND R.IdCia=@pmIdCia
 ORDER BY Item
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsReqDetalle] @pmTipDoc VARCHAR(3),@pmRequisicion INT,@pmIdCia CHAR(2),@pmItem INT,@pmCdProducto VARCHAR(16),@pmDescripcion VARCHAR(250),@pmIdSubgrupo VARCHAR(8)
,@pmCdBodega VARCHAR(4),@pmCantidad DECIMAL(14,4),@pmIdUnd VARCHAR(4),@pmVrUnitario MONEY,@pmTipOrd VARCHAR(3),@pmNumOrden INT,@pmIdCiaOrd CHAR(2)
,@pmTipSal VARCHAR(3),@pmNSalida INT,@pmIdCiaSal CHAR(2),@pmFechaSal SMALLDATETIME,@pmReferencia VARCHAR(250),@pmCantSalida DECIMAL(14,4),@pmCantDevSal DECIMAL(14,4)
 AS 
 INSERT INTO Trn_ReqDetalle (TipDoc,Requisicion,IdCia,Item,CdProducto,Descripcion,IdSubgrupo,CdBodega,Cantidad,IdUnd,VrUnitario,TipOrd,NumOrden,IdCiaOrd,TipSal,NSalida,IdCiaSal,FechaSal,Referencia,CantSalida,CantDevSal)
 VALUES (@pmTipDoc,@pmRequisicion,@pmIdCia,@pmItem,@pmCdProducto,@pmDescripcion,@pmIdSubgrupo,@pmCdBodega,@pmCantidad,@pmIdUnd,@pmVrUnitario,@pmTipOrd
,@pmNumOrden,@pmIdCiaOrd,@pmTipSal,@pmNSalida,@pmIdCiaSal,@pmFechaSal,@pmReferencia,@pmCantSalida,@pmCantDevSal)
GO