SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FuncOsaListaAntcipos] (@pmNumOrden INT,@pmIdCia CHAR(2))
RETURNS VARCHAR(250)
 AS  
BEGIN 
 DECLARE @Anticipos VARCHAR(250)
 DECLARE @Anticipo INT
 DECLARE TraAnticipos CURSOR FOR 
 SELECT Anticipo FROM Trn_TraOrdenAnt WHERE NumOrden=@pmNumOrden AND IdCiaOds=@pmIdCia AND Anulado=0
 SET @Anticipos=''
 OPEN TraAnticipos
 FETCH NEXT FROM TraAnticipos  
 INTO @Anticipo
 WHILE @@FETCH_STATUS=0
  BEGIN
   SET @Anticipos=@Anticipos+CAST(@Anticipo AS VARCHAR(10))+','
   FETCH NEXT FROM TraAnticipos  
   INTO @Anticipo
  END
 CLOSE TraAnticipos
 DEALLOCATE TraAnticipos
 RETURN @Anticipos
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServOpe] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdCliente VARCHAR(16)=NULL
AS
SELECT O.TipDoc AS IdTipDoc,TipoDoc,O.NumOrden AS Num_Orden,O.IdCia AS CdCia,Compania,O.Fecha,T.TipoId AS ClieTipoId,O.IdCliente,T.Dv AS ClieDv,T.RazonSocial AS NomCliente
,O.IdVehiculo AS PlacaVeh,NumVeh,O.TipoAfiVehic,O.Modalidad,TipoOrden,O.VrTotal AS ValorTotal,O.VrCosto AS TotalTercero,O.Cantidad AS CantTotal,O.VrAnticipo
,D.Item,D.IdMercancia AS CdMercancia,D.Descripcion,D.Cantidad,D.UndMed,UM.Unidad,D.VrUnitario,D.TarifaPago,D.DocCliente
,O.IdEstacion AS CdCampo,EstServicio AS NomCampo,O.CdRuta,Ruta,O.IdOrigen AS CdOrigen,LO.Localidad AS NomOrigen,O.IdDestino AS CdDestino,LD.Localidad AS NomDestino,O.IdAgencia AS CdAgencia,Agencia
,O.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,O.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,NomEstacion,TipoResiduo,O.RemCliente,NomRecibido,O.FecDespacho,O.FecRecibo,O.FecIngCertif,O.FecCertificado,O.NumRemesa,O.CdCiaRem,kmtInicial,kmtFinal,Cantkms,NumAprob,IdCiaApr,FecAprob,UsuAprob,ObservAprob
,O.Anulado,O.FecDev,O.Observacion AS Observ,O.IdEstado AS CdEstado,ED.Estado,O.TimeSys AS FechaCrea,O.FecUpdate AS FechaModif,O.IdCiaCrea AS CdCiaCrea,O.IdUsuario AS CdUsuario,Usuario
--cumplido
,EstCumplido,CM.Cumplido AS NumCumplido,CM.IdCia AS CiaCump,CM.Fecha AS FechaCump,CM.DescServicio AS CumpServicio,CM.Observacion AS ObservCump
,CM.IdMercancia AS CdMercanciaCump,CM.Descripcion AS DescripCump,CM.Cantidad AS CantCump,CM.UndMed AS UndMedCump,UMC.Unidad AS UnidadCump,CM.VrUnitario AS TarifCump
,CM.TarifaPago AS TarifPagoCump
--liquidacion
,O.EstOrden,O.TipLiq,O.NumLiquida,O.CdCiaLiq,L.Fecha AS FecLiquida,L.VrTotal AS LiqTotal,L.VrAnticipo AS LiqAnticipo,L.VrIva AS LiqIva,L.VrRetencion AS LiqRetencion
,L.VrReteIca,L.VrReteCREE,L.VrReteIva,L.VrOtros,L.VrDescuento,L.VrNeto,L.Cantidad AS LiqCantidad,L.TarifaPago AS LiqTarifaPago,L.TarifaImp,L.TarifaRet,L.TarifaIca,L.TarifaRiv
,L.Referencia,L.VrGastos,dbo.FuncOsaListaAntcipos(O.NumOrden,O.IdCia) AS Anticipos,L.TipEgr,L.Egreso,L.IdCiaEgr,EG.Fecha AS FecEgreso,EG.NumCheque,EGT.TotalEgresos
--Factura
,O.EstFactura,O.TipFact,O.NumFactura,O.CdCiaFact,FactFecha,FactCant,FactTarifClie,FactTotal,FactIva,FactTarifIVA,FactCosto,FactIdCli,TCF.RazonSocial AS FactNomCliente
--Inf vehiculo
,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,LC.Localidad AS NomCiudad,T.Telefono AS TercTelefono,T.TelMovil AS TelCelular,T.e_mail AS TercEmail
--Vehiculo
,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,M.Marca AS MarcaVeh,V.IdColor AS CdColor,NomColor,Modelo,NumMotor,SerieChasis,NumSerie,NitEmpresa,NE.RazonSocial AS VehNomEmpresa
,TipoAfil,NumSoat,FecSoat,VigSoat,NitEmpSoat,NS.RazonSocial AS CiaAsegurdora,V.Descripcion AS VehDescripcion
FROM Trn_TraOrdenServ AS O INNER JOIN Trn_TraOrdenDet AS D ON O.TipDoc=D.TipDoc AND O.NumOrden=D.NumOrden AND O.IdCia=D.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 Terceros AS NP ON O.IdPoseedor=NP.IdTercero
INNER JOIN Terceros AS CDT ON O.IdConductor=CDT.IdTercero INNER JOIN EdsServicio AS E ON O.IdEstacion=E.IdEstacion
INNER JOIN EstadoDoc AS ED ON O.IdEstado=ED.IdEstado INNER JOIN adm_Usuarios AS U ON O.IdUsuario=U.IdUsuario
INNER JOIN Sys_Um AS UM ON D.UndMed=UM.UndMed INNER JOIN Localidades AS LC ON T.IdLocal=LC.IdLocal
INNER JOIN Vehiculos AS V ON O.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
INNER JOIN Marcas AS M ON V.IdMarca=M.IdMarca INNER JOIN TiposCol AS CL ON V.IdColor=CL.IdColor 
LEFT JOIN Localidades AS LD ON O.IdDestino=LD.IdLocal LEFT JOIN Agencias AS A ON O.IdAgencia=A.IdAgencia
LEFT JOIN Terceros AS NE ON  V.NitEmpresa=NE.IdTercero LEFT JOIN Terceros AS NS ON V.NitEmpSoat=NS.IdTercero
LEFT JOIN Trn_TraOrdenLiq AS L ON O.TipLiq=L.TipDoc AND O.NumLiquida=L.Liquidacion AND O.CdCiaLiq=L.IdCia
LEFT JOIN Localidades AS LO ON O.IdOrigen=LO.IdLocal LEFT JOIN Rutas AS RT ON O.CdRuta=RT.IdRuta
--Subconsulta cumplido
LEFT JOIN (SELECT C.Cumplido,C.IdCia,C.Fecha,C.TipOds,C.NumOrden,C.IdCiaOds,C.FecDespacho,C.FecRecibo,C.FecIngCertif,C.FecCertificado,C.DescServicio
,C.RemCliente,C.Funcionario,C.Observacion,D.IdMercancia,D.Descripcion,D.Item,D.Cantidad,D.UndMed,D.VrUnitario,D.TarifaPago,D.DocCliente
FROM Trn_TraOrdenCum AS C INNER JOIN Trn_TraOrdenCumDet AS D ON C.TipDoc=D.TipDoc AND C.Cumplido=D.Cumplido AND C.IdCia=D.IdCia
WHERE C.Anulado=0) AS CM ON O.TipDoc=CM.TipOds AND O.NumOrden=CM.NumOrden AND O.IdCia=CM.IdCiaOds AND D.Item=CM.Item
LEFT JOIN Sys_Um AS UMC ON CM.UndMed=UMC.UndMed
--Subconsulta factura
LEFT JOIN (SELECT F.TipDoc,F.Factura,F.IdCia,D.TipRem,D.Remesa,D.IdCiaRem,F.IdCliente AS FactIdCli,F.Fecha AS FactFecha,SUM(D.Cantidad) AS FactCant,SUM(D.VrUnitario*D.Cantidad) AS FactTotal
,SUM(D.VrCosto*D.Cantidad) AS FactCosto,SUM(D.VrImpuesto) AS FactIva,MAX(D.TarifaIva) AS FactTarifIVA,MAX(D.VrUnitario) AS FactTarifClie,MAX(D.VrCosto) AS FactTarifTerc
FROM Trn_Facturas AS F INNER JOIN Trn_TraFacRemesas AS D ON F.TipDoc=D.TipDoc AND F.Factura=D.Factura AND F.IdCia=D.IdCia
WHERE F.Anulado=0 GROUP BY F.TipDoc,F.Factura,F.IdCia,D.TipRem,D.Remesa,D.IdCiaRem,F.IdCliente,F.Fecha) AS FS ON O.TipFact=FS.TipDoc AND O.NumFactura=FS.Factura AND O.CdCiaFact=FS.IdCia
AND O.TipDoc=FS.TipRem AND O.NumOrden=FS.Remesa AND O.IdCia=FS.IdCiaRem
LEFT JOIN Terceros AS TCF ON FS.FactIdCli=TCF.IdTercero
LEFT JOIN Trn_Comprobantes AS EG ON L.TipEgr=EG.TipCom AND L.Egreso=EG.Comprobante AND L.IdCiaEgr=EG.IdCia
LEFT JOIN (SELECT EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago,SUM(VrAbonado) AS TotalEgresos
FROM Trn_TraEgrOrden AS EO INNER JOIN Trn_Comprobantes AS E ON EO.TipCom=E.TipCom AND EO.NumEgreso=E.Comprobante AND EO.IdCia=E.IdCia
WHERE E.Anulado=0 GROUP BY EO.TipOdp,EO.OrdPago,EO.IdCiaOdp) AS EGT ON O.TipLiq=EGT.TipOrden AND O.NumLiquida=EGT.NumOPago AND O.CdCiaLiq=EGT.CdCiaOpago
WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO