SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraOrdenServFac] @pmTipDoc VARCHAR(3),@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.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
,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
--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
--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
WHERE O.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND O.TipDoc LIKE ISNULL(@pmTipDoc,'%') AND O.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND O.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO

INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLCOSF','TRAINF','GEN',10,'Ordenes de Servicio-Facturación','FRMLCOSF','SSSSSSSSSSSSS',0,'')
GO
INSERT INTO ClaseDiv  (IdClase,NomClase,Inactivo)
 VALUES ('0013','INGRESOS POR COBRAR',0)
INSERT INTO ClaseDiv  (IdClase,NomClase,Inactivo)
 VALUES ('0014','INGRESOS POR COBRAR PROPIOS',0)
INSERT INTO ClaseDiv  (IdClase,NomClase,Inactivo)
 VALUES ('0015','PROVISION INGRESOS',0)
INSERT INTO ClaseDiv  (IdClase,NomClase,Inactivo)
 VALUES ('0016','PROVISION INGRESOS PROPIOS',0)
GO
INSERT INTO  adm_Opciones (IdOpc,Opcion,TipoDato,Valor,NivUp,nModulo)
 VALUES ('CPI','CONTABILIZAR PROVISION DE INGRESOS Y CARTERA EN FACTURACION DE TRANSPORTE','BOOLEAN','0',5,'TRANS')
GO
INSERT INTO Sys_Report (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('OSF','01','Predeterminado','CrOsf.rpt',1,2,1,0,1,'paQryTraOrdenServFac','','Ordenes de Servicio-Facturas')
GO