IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInsAcuPrestamosCau]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paInsAcuPrestamosCau]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInsFinPagos]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paInsFinPagos]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInsLineasCredCue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paInsLineasCredCue]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInsTercClieLineas]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paInsTercClieLineas]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInsTercCliePrestamo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paInsTercCliePrestamo]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInsTercCliePrestamo_Sel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paInsTercCliePrestamo_Sel]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInstm_PreMovi_Pre]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paInstm_PreMovi_Pre]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paInstm_PresPagos_Sel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paInstm_PresPagos_Sel]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryComprobantesEgo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryComprobantesEgo]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryNomDetallePmd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryNomDetallePmd]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTercClieLineas]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTercClieLineas]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTercClieLineasLta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTercClieLineasLta]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTercCliePrestamo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTercCliePrestamo]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTercCliePrestamoLta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTercCliePrestamoLta]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTiposDocAut]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTiposDocAut]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraDespachosFmt]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTraDespachosFmt]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraDespachosLta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTraDespachosLta]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraDespachosLtap]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTraDespachosLtap]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paQryTraManifiestoOpe]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paQryTraManifiestoOpe]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paUpLineasCredCue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paUpLineasCredCue]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paUpTercClieLineas]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paUpTercClieLineas]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[paUpTercCliePrestamo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[paUpTercCliePrestamo]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryNomDetallePmd] @pmIdEmpleado VARCHAR(16),@pmNContrato INT,@pmModoPmd CHAR(1),@pmFecIni SMALLDATETIME
,@pmFecFin SMALLDATETIME,@pmIdConcepto VARCHAR(4)=Null
 AS 
 IF @pmModoPmd='F' --SALARIOS FIJO
  SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes,IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV,SUM(VrBaseLiq) AS SBASELIQ
  FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
  WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
  AND  ClaseLiq IN ('NOMINA','NOVEDAD')  AND VrBaseLiq>0 AND IdConcepto NOT IN ('BAS','AXT','LIC','IGE','IRP','HHD','HHN','DSR','VAC')
  GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
 ELSE 
  BEGIN
   IF @pmModoPmd='B' --SALARIO BASICO
    SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes,IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
   ,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV
  ,SUM(CASE  WHEN IdConcepto IN ('DNR','SAN') THEN CantDed*VrUnitario WHEN IdConcepto IN ('IGE','IRP') THEN (N.VrSalario/30)*CantDevg  ELSE VrBaseLiq END) AS SBASELIQ
    FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
    WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
   AND  ClaseLiq IN ('NOMINA','NOVEDAD')  AND IdConcepto IN ('BAS','AXT','LIC','IGE','IRP','HHD','HHN','DSR','VAC','DNR','SAN')
   AND (VrBaseLiq>0 OR IdConcepto='DNR' OR  IdConcepto='SAN')
   GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
  ELSE --SALARIO VARIABLE
   SELECT SUBSTRING(N.IdPeriodo,1,4) AS nAnno,SUBSTRING(N.IdPeriodo,5,2) AS nMes, IdConcepto,SUM(CantDevg) AS SCANDEV,SUM(VrTotDevg) AS STOTDEV
  ,SUM(DiasCalc) AS SDIASCAL,SUM(DiasNov) AS SDIASNOV
 ,SUM(CASE  WHEN IdConcepto IN ('DNR','SAN') THEN CantDed*VrUnitario WHEN IdConcepto IN ('IGE','IRP') THEN (N.VrSalario/30)*CantDevg  ELSE VrBaseLiq END) AS SBASELIQ
   FROM Trn_Nomina AS N INNER JOIN Trn_NomDetalle AS D ON  N.IdPeriodo=D.IdPeriodo AND N.KeyRegistro=D.KeyRegistro
   WHERE N.IdEmpleado=@pmIdEmpleado AND NContrato=@pmNContrato AND FecInicial>=@pmFecIni AND FecFinal<=@pmFecFin
   AND  ClaseLiq IN ('NOMINA','NOVEDAD') AND (VrBaseLiq>0  OR  IdConcepto='DNR' OR  IdConcepto='SAN')
   AND IdConcepto LIKE ISNULL(@pmIdConcepto,'%')
   GROUP BY  SUBSTRING(N.IdPeriodo,1,4),SUBSTRING(N.IdPeriodo,5,2),IdConcepto
  END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  [dbo].[paInstm_PresPagos_Sel] @pmtmNumero VARCHAR(5),@pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2) 
 AS 
 INSERT INTO tm_PresPagos (tmNumero,tmItem,tmNumForma,tmFecha,tmFecPago,tmVrCuota,tmCdBanco,tmCtaCheque,tmDetalle,tmTarifSeguro,tmVrSeguro)
 SELECT @pmtmNumero,Item,Numero,Fecha,FecPago,VrCuota,CdBanco,CtaCheque,Detalle,TarifSeguro,TotalSeguro
 FROM Trn_FinPagos
 WHERE TipDoc=@pmTipDoc AND IdPrestamo=@pmIdPrestamo AND IdCia=@pmIdCia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsAcuPrestamosCau]  @pmnAnno INT,@pmnMes INT,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 INSERT INTO AcuPrestamos (nAnno,nMes,IdCia,IdCliente,IdAgencia,SaldoAnt,Prestamos,DevPrestamos,Recibos,DevRecibos,NotasDeb,NotasCre,Facturas,DevFacturas)
 SELECT YEAR(CASE WHEN C.TipDoc='PR1' AND C.Concepto='CAPITAL' AND ISNULL(P.CauAnticipado,0)=1 THEN P.FecPrestamo ELSE C.Fecha END)
,MONTH(CASE WHEN C.TipDoc='PR1' AND C.Concepto='CAPITAL' AND ISNULL(P.CauAnticipado,0)=1 THEN P.FecPrestamo ELSE C.Fecha END)
,C.IdCia,C.IdCliente,C.IdAgencia,0,SUM(CASE C.TipDoc WHEN 'PR1' THEN C.VrTotal ELSE 0 END),0,0,0
 ,SUM(CASE C.TipDoc WHEN 'ND2' THEN C.VrTotal ELSE 0 END),0,SUM(CASE C.TipDoc WHEN 'FIF' THEN C.VrTotal ELSE 0 END),0
 FROM Trn_FinCuotas AS C LEFT JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc  AND C.IdPrestamo=P.IdPrestamo AND C.IdCia=P.IdCia
 WHERE C.TipDoc IN ('PR1','ND2','FIF')  AND YEAR(CASE WHEN C.TipDoc='PR1' AND C.Concepto='CAPITAL' AND ISNULL(P.CauAnticipado,0)=1 THEN P.FecPrestamo ELSE C.Fecha END)=@pmnAnno
 AND MONTH(CASE WHEN C.TipDoc='PR1' AND C.Concepto='CAPITAL' AND ISNULL(P.CauAnticipado,0)=1 THEN P.FecPrestamo ELSE C.Fecha END)=@pmnMes 
 AND C.IdCia LIKE ISNULL(@pmIdCia,'%%') AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
 GROUP BY YEAR(CASE WHEN C.TipDoc='PR1' AND C.Concepto='CAPITAL' AND ISNULL(P.CauAnticipado,0)=1 THEN P.FecPrestamo ELSE C.Fecha END)
 ,MONTH(CASE WHEN C.TipDoc='PR1' AND C.Concepto='CAPITAL' AND ISNULL(P.CauAnticipado,0)=1 THEN P.FecPrestamo ELSE C.Fecha END)
 ,C.IdCia,C.IdCliente,C.IdAgencia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDespachosLtap] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
--despachos-detalles de programacion
 SELECT D.TipDoc AS CdTipDoc,D.Manifiesto AS NumManif,D.IdCia AS CdCia,Compania,D.Fecha AS FecMuc,FecCargue,FecDespacho,IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.IdRuta AS CdRuta,R.Ruta AS DescRuta,D.IdVehiculo AS PlacaVeh,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,T.TipoId AS PosTipoId,D.IdPoseedor AS NitPoseedor,T.Dv AS PosDv,T.RazonSocial AS NomPoseedor,NitRemite,Remitente,NitDestntario,Destinatario,DescMcia,PesoTotal,NumOrden,IdCiaOrd,Remesa,IdCiaRem
,D.Estado AS EdoDespacho,D.Anulado AS DesAnulado,FecDev,D.Observacion AS Observ,TimeSys,FecUpdate,D.IdUsuario AS CdUsuario,Usuario
--datos del vehíclo
,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,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
,PgmItem,PgmIdPunto,HoraProg,TipoProg,ObservProg,RepItem,RepIdPunto,FecReporte,TipoRep,EstadoRep,CdNovedad,Novedad,ObservRep,TipoPunto,CdPuesto,PtoControl,TagPuesto,PC.Ubicacion AS UbicacPto,RepConductor
 FROM Trn_TraDespachos AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS CO ON  D.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON D.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON D.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero 
 INNER JOIN Vehiculos AS V ON D.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 TercCndtores AS CT ON D.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 LEFT JOIN (SELECT  ISNULL(PG.TipDoc,RP.TipDoc) AS PgmTipDoc,ISNULL(PG.Manifiesto,RP.Manifiesto) AS PgmManif,ISNULL(PG.IdCia,RP.IdCia) AS PgmCdCia
 ,PG.Item AS PgmItem,PG.IdPunto AS PgmIdPunto,HoraProg,TipoProg,ObservProg,RP.Item AS RepItem,RP.IdPunto AS RepIdPunto,FecReporte,TipoRep,EstadoRep
,CdNovedad,ObservRep,TipoPunto,CdPuesto,RP.NomConductor AS RepConductor
 FROM Trn_TraDespProgram AS PG  FULL JOIN Trn_TraReportes AS RP  ON PG.TipDoc=RP.TipDoc AND  PG.Manifiesto=RP.Manifiesto AND PG.IdCia=RP.IdCia  
 AND PG.CdRuta=RP.CdRuta AND PG.IdPunto=RP.IdPunto) AS PGM ON D.TipDoc=PGM.PgmTipDoc AND  D.Manifiesto=PGM.PgmManif AND D.IdCia=PGM.PgmCdCia
 LEFT JOIN NovedadRep AS NV ON PGM.CdNovedad=NV.IdNov  LEFT JOIN Puestos AS PC ON PGM.CdPuesto=PC.IdPto
 WHERE D.TipDoc=@pmTipDoc AND  D.FecDespacho BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTraDespachosFmt] @pmTipDoc VARCHAR(3),@pmManifiestoIni INT,@pmManifiestoFin INT,@pmIdCia CHAR(2)
 AS 
 SELECT D.TipDoc AS CdTipMuc,D.Manifiesto AS NumManif,D.IdCia AS CdCia,Compania,D.Fecha AS FecMuc,FecCargue,FecDespacho,IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.IdRuta AS CdRuta,R.Ruta AS DescRuta,D.IdVehiculo AS PlacaVeh,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,T.TipoId AS PosTipoId,D.IdPoseedor AS NitPoseedor,T.Dv AS PosDv,T.RazonSocial AS NomPoseedor,NitRemite,Remitente,NitDestntario,Destinatario,DescMcia,PesoTotal,NumOrden,IdCiaOrd,Remesa,IdCiaRem
,D.Estado AS EdoDespacho,D.Anulado AS DesAnulado,FecDev,D.Observacion AS Observ,TimeSys,FecUpdate,D.IdUsuario AS CdUsuario,Usuario
--detalles reportes-program.
,PgmItem,PgmIdPunto,HoraProg,TipoProg,ObservProg,RepItem,RepIdPunto,FecReporte,TipoRep,EstadoRep,CdNovedad,Novedad,ObservRep,TipoPunto,CdPuesto,PtoControl,TagPuesto,PC.Ubicacion AS UbicacPto,RepConductor
--datos del vehíclo
,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,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
 FROM Trn_TraDespachos AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS CO ON  D.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON D.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON D.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero 
 INNER JOIN Vehiculos AS V ON D.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 (SELECT  ISNULL(PG.TipDoc,RP.TipDoc) AS PgmTipDoc,ISNULL(PG.Manifiesto,RP.Manifiesto) AS PgmManif,ISNULL(PG.IdCia,RP.IdCia) AS PgmCdCia
 ,PG.Item AS PgmItem,PG.IdPunto AS PgmIdPunto,HoraProg,TipoProg,ObservProg,RP.Item AS RepItem,RP.IdPunto AS RepIdPunto,FecReporte,TipoRep,EstadoRep
,CdNovedad,ObservRep,TipoPunto,CdPuesto,RP.NomConductor AS RepConductor
 FROM Trn_TraDespProgram AS PG  FULL JOIN Trn_TraReportes AS RP  ON PG.TipDoc=RP.TipDoc AND  PG.Manifiesto=RP.Manifiesto AND PG.IdCia=RP.IdCia  
 AND PG.CdRuta=RP.CdRuta AND PG.IdPunto=RP.IdPunto) AS PGM ON D.TipDoc=PGM.PgmTipDoc AND  D.Manifiesto=PGM.PgmManif AND D.IdCia=PGM.PgmCdCia
 LEFT JOIN NovedadRep AS NV ON PGM.CdNovedad=NV.IdNov  LEFT JOIN Puestos AS PC ON PGM.CdPuesto=PC.IdPto
 WHERE D.TipDoc=@pmTipDoc AND D.Manifiesto BETWEEN @pmManifiestoIni AND  @pmManifiestoFin  AND D.IdCia=@pmIdCia
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[paQryTraDespachosLta] @pmTipDoc VARCHAR(3),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdRuta VARCHAR(4)=Null,@pmIdVehiculo VARCHAR(10)=Null,@pmIdConductor VARCHAR(16)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT D.TipDoc AS CdTipDoc,D.Manifiesto AS NumManif,D.IdCia AS CdCia,Compania,D.Fecha AS FecMuc,FecCargue,FecDespacho,IdOrigen,CO.Localidad AS CiuOrigen,CO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen
,IdDestino,CD.Localidad AS CiuDestino,CD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino,D.IdRuta AS CdRuta,R.Ruta AS DescRuta,D.IdVehiculo AS PlacaVeh,D.IdConductor AS CedConductor,CDT.RazonSocial AS NomConductor
,T.TipoId AS PosTipoId,D.IdPoseedor AS NitPoseedor,T.Dv AS PosDv,T.RazonSocial AS NomPoseedor,NitRemite,Remitente,NitDestntario,Destinatario,DescMcia,PesoTotal,NumOrden,IdCiaOrd,Remesa,IdCiaRem
,D.Estado AS EdoDespacho,D.Anulado AS DesAnulado,FecDev,D.Observacion AS Observ,TimeSys,FecUpdate,D.IdUsuario AS CdUsuario,Usuario
--datos del vehíclo
,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,CertGases,FecCertGas,VigCertGas,V.Descripcion AS VehDescripcion
,CertJudicial,FecJudicial,VigJudicial,Licencia,CatLicencia,CT.IdLugar AS CdLugar,LugarLic,FecLicencia,VigLicencia
 FROM Trn_TraDespachos AS D INNER JOIN Companias AS CN ON D.IdCia=CN.IdCia INNER JOIN adm_Usuarios AS U ON D.IdUsuario=U.IdUsuario
 INNER JOIN Localidades AS CO ON  D.IdOrigen=CO.IdLocal INNER JOIN Departamentos AS DPO ON CO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS CD ON D.IdDestino=CD.IdLocal INNER JOIN Departamentos AS DPD ON CD.IdDep=DPD.IdDep
 INNER JOIN Rutas AS R ON D.IdRuta=R.IdRuta  INNER JOIN Terceros AS  CDT ON D.IdConductor=CDT.IdTercero 
 INNER JOIN Terceros AS T ON D.IdPoseedor=T.IdTercero 
 INNER JOIN Vehiculos AS V ON D.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 TercCndtores AS CT ON D.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 WHERE D.TipDoc=@pmTipDoc AND  D.FecDespacho BETWEEN @pmFechaIni AND @pmFechaFin AND D.IdCia LIKE ISNULL(@pmIdCia,'%%')
 AND D.IdRuta LIKE ISNULL(@pmIdRuta,'%') AND D.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND D.IdConductor LIKE ISNULL(@pmIdConductor,'%')
 AND D.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTercCliePrestamoLta]  @pmIdGrupo VARCHAR(4)=Null,@pmIdVend VARCHAR(16)=Null,@pmIdSzona VARCHAR(4)=Null
,@pmIdTipoTerc  VARCHAR(4)=Null,@pmFecIngIni SMALLDATETIME=Null,@pmFecIngFin SMALLDATETIME=Null,@pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT IdClie,T.RazonSocial AS Razon_Social,T.TipoId AS TipId,T.Dv AS DvNit,T.NomCial AS NombreCmcial,T.SiglaRaz AS SiglaRazSocial
,IdAgencia,NomAgencia,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,T.Direccion AS TercDireccion,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
,DirAgencia,IdLocalAge,LE.Localidad AS CiuAgencia,DE.Departamento AS DptoAgencia,C.IdTipoTerc AS CdTipoTerc,TipoTercero
,SZ.IdZona AS CdZona,Zona,C.IdSzona AS CdSubzona,Subzona,C.IdGrupo AS CdGrupo,GrupoClie,T.IdSector AS CdSector,SectorEco,T.IdProf AS CdProf,Profesion,T.IdRegimen AS CdRgmen,Regimen
,T.TipEnte AS TipoEnte,IdVend,TV.RazonSocial AS NomVendedor,C.IdClase AS CdClase,ClaseCuenta,NumCuenta,C.IdBanco AS CdBanco,Banco
,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico,TipoSalario,MaxDcto,MatMerc,FecMat,IncRetFte,NumTarj,FecVenceTc,Comentarios,C.IdEstado AS CdEstado,EC.Estado AS EstadoClie,C.Inactivo AS ClieInactivo,FechaCrea,FechaAct,C.IdUsuario AS CdUsuario,Usuario
,T.IniStgNom AS PosNomb,T.IdLugarCed AS IdLugCed,LC.Localidad AS LugarCed,T.FecExpCed AS FecExpCedula,T.Inactivo AS TercInactivo,T.Codigo AS TercCodigo
 FROM TercCliePrestamo 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 TiposTerc AS TC ON C.IdTipoTerc=TC.IdTipoTerc
 INNER JOIN Subzonas AS SZ ON C.IdSzona=SZ.IdSzona  INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona 
 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.IdLocalAge=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
 LEFT JOIN ClaseCta AS CTA ON  C.IdClase=CTA.IdClase LEFT JOIN Bancos AS B ON C.IdBanco=B.IdBanco 
 LEFT JOIN Localidades AS LC ON T.IdLugarCed=LC.IdLocal  LEFT JOIN Profesiones AS PF ON T.IdProf=PF.IdProf 
 WHERE C.IdGrupo LIKE ISNULL(@pmIdGrupo,'%') AND IdVend LIKE ISNULL(@pmIdVend,'%')
 AND  C.IdSzona LIKE ISNULL(@pmIdSzona,'%')  AND  C.IdTipoTerc  LIKE ISNULL(@pmIdTipoTerc ,'%')
 AND C.IdEstado LIKE ISNULL(@pmIdEstado,'%')
 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].[paInstm_PreMovi_Pre]  @pmtmEst CHAR(2),@pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCliente VARCHAR(16)=Null
,@pmIdAgencia VARCHAR(16)=Null,@pmIdCia CHAR(2)=Null
 AS 
 INSERT INTO tm_PreMovi (tmEst,tmTipDoc,tmNumero,tmIdCia,tmItem,tmFecha,tmIdCliente,tmIdAgencia,tmIdVend,tmCargos,tmAbonos,tmNumCuota,tmTimeSys,tmFecVence,tmCdConcepto,tmConcepto,tmDetalle,tmReferencia
,tmTipoTasa,tmDTF_EA,tmTasaNom,tmPuntos,tmTasaEfe,tmTipRef,tmDocRef,tmIdCiaRef,tmNumForma,tmCdBanco,tmBeneficiario,tmCdForma,tmEnEfectivo,tmTotalAbono,tmVrAfavor,tmTipCom,tmComprobante,tmIdCiaCom
,tmAnulado,tmNumDev,tmFecDev,tmIdUsuario,tmSanClie,tmSanClieCia,tmSanAge,tmSanAgeCia)
 SELECT @pmtmEst,C.TipDoc,C.IdPrestamo,C.IdCia,Item,CASE WHEN C.Concepto='CAPITAL' AND ISNULL(P.CauAnticipado,0)=1 THEN P.FecPrestamo ELSE C.Fecha END
,C.IdCliente,C.IdAgencia,IdVend,VrTotal,0,NumCuota,TimeSys,FechaVence,IdConcepto,Concepto,Detalle,Causacion
,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,'CAU',NumCausacion,IdCiaCau,NumCredito,'0',TipoGarantia,'0',0,VrAbonado,0,TipCom,Comprobante,IdCiaCom
,Anulado,NumDev,FecDev,IdUsuario,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 CASE WHEN C.Concepto='CAPITAL' AND ISNULL(P.CauAnticipado,0)=1 THEN P.FecPrestamo ELSE C.Fecha END BETWEEN @pmFechaIni AND @pmFechaFin 
 AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')  AND C.IdAgencia LIKE ISNULL(@pmIdAgencia,'%') AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryComprobantesEgo] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null,@pmIdTercero VARCHAR(16)=Null
 AS 
 SELECT C.TipCom AS CdTipEgr,TipoCom,C.Comprobante AS NumEgreso,C.IdCia AS CdCiaEgr,Compania,C.Fecha AS FecEgreso,T.TipoId AS TercTipo,C.IdTercero AS NitTercero,T.Dv AS TercDv
,T.RazonSocial AS NomTercero,VrTotal,C.IdCta AS CdCtaCte,NumeroCta,CTA.IdBanco AS CdBanco,Banco,EnEfectivo,C.NumCheque AS NoCheque,FecCheque
,TipDoc,Documento,IdCiaDoc,Anulado,NumDev,FecDev,pVehiculo,C.VehPropio AS VehEsPropio,CedCondtor,CD.RazonSocial AS NomConductor,Beneficiario,Anticipo,C.Observacion AS Observ
 FROM Trn_Comprobantes AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Terceros AS T ON C.IdTercero=T.IdTercero
 INNER JOIN TiposCom AS TC ON C.TipCom=TC.IdCom
 LEFT JOIN CtasCorrientes AS CTA ON C.IdCta=CTA.IdCta LEFT JOIN Bancos AS BCT ON CTA.IdBanco=BCT.IdBanco
 LEFT JOIN Terceros AS CD ON C.CedCondtor=CD.IdTercero 
 WHERE C.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND C.EsEgreso=1 AND C.IdCia LIKE ISNULL(@pmIdCia,'%') AND C.IdTercero LIKE ISNULL(@pmIdTercero,'%')

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTiposDocAut]
 AS 
 SELECT  IdDoc,TipoDoc,IdDoc+'  '+ TipoDoc AS DsTip
 FROM  Sys_TiposDoc 
 WHERE IdDoc IN ('FCR','DFO','ODB','OD2','PR1') --todas las fac.credito con este codigo
 ORDER BY IdDoc
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTercClieLineasLta] @pmIdClie VARCHAR(16)=Null,@pmIdLinea VARCHAR(4)=Null,@pmIdTipoTerc  VARCHAR(4)=Null
 AS 
 SELECT LC.IdClie AS NitCliente,RazonSocial,LC.IdAgencia AS CdAgencia,NomAgencia,LC.IdLinea AS CdLinea,LinCredito,LC.IdTasa AS CdTasa,DescTasa,TasaEfectiva,nPlazo,TipoPlazo,CupoCredito
,SaldoCapital,SaldoInteres,MaxCupoSmlv,TipoCausac,DiasVence,FecPreAprob,PlazoAprob,EtdoAprob,TipoAprob,DescAprob,Garantias,TipoGarantia,DescGarantia,VrGarantia
,ReqCheques,FecActCupo,EstadoAct,LiqSeguro,CdTarifSeguro,CdTasaConv,NitConvenio,CuotasConv,LC.FechaCrea AS FecCrea,LC.FechaAct AS FecActualiza,LC.IdUsuario AS CdUsuario,Usuario
--datos del cliente
,TipoId,Dv,C.IdTipoTerc AS CdTipoTerc,TipoTercero,C.IdEstado AS CdEstado,Estado,FecIngreso,VrSalBasico,MaxDcto,Comentarios
 FROM TercClieLineas AS LC  INNER JOIN LineasCred AS L ON LC.IdLinea=L.IdLinea
 INNER JOIN  TercCliePrestamo AS C ON LC.IdClie=C.IdClie AND LC.IdAgencia=C.IdAgencia  INNER JOIN Terceros AS T ON LC.IdClie=T.IdTercero
 INNER JOIN adm_Usuarios AS U ON LC.IdUsuario=U.IdUsuario
 INNER JOIN TiposTerc AS TC ON C.IdTipoTerc=TC.IdTipoTerc  INNER JOIN EstadoTer AS EC ON C.IdEstado=EC.IdEstado
 LEFT JOIN TasasInteres AS TI ON LC.IdTasa=TI.IdTasa
 WHERE C.Inactivo=0 AND LC.IdClie LIKE ISNULL(@pmIdClie,'%') AND LC.IdLinea LIKE ISNULL(@pmIdLinea,'%')
 AND  C.IdTipoTerc  LIKE ISNULL(@pmIdTipoTerc ,'%')
 ORDER BY RazonSocial,LC.IdLinea

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTercCliePrestamo] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16)
 AS 
 SELECT IdClie,IdAgencia,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,NomAgencia,DirAgencia,IdLocalAge,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco
,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,NumTarj,PwdTarj,FecVenceTc,IncRetFte,FechaCrea,FechaAct,IdUsuario
 FROM TercCliePrestamo
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsTercCliePrestamo] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100)
,@pmCargContac VARCHAR(50),@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdTipoTerc VARCHAR(4),@pmIdVend VARCHAR(16),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmEsExento BIT,@pmCalcIntMora BIT,@pmDiasGracia INT
,@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME,@pmFecRetiro SMALLDATETIME,@pmVrSalBasico MONEY,@pmMaxDcto DECIMAL(14,4),@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME,@pmNomAgencia VARCHAR(150),@pmDirAgencia VARCHAR(250)
,@pmIdLocalAge VARCHAR(8),@pmComentarios VARCHAR(2000),@pmIdEstado VARCHAR(4),@pmInactivo BIT,@pmTipoSalario CHAR(1),@pmNumTarj VARCHAR(30),@pmPwdTarj VARCHAR(30),@pmFecVenceTc SMALLDATETIME,@pmIncRetFte BIT,@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercCliePrestamo (IdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc,IncRetFte)
 VALUES (@pmIdClie,@pmIdAgencia,@pmIdSzona,@pmIdGrupo,@pmIdTipoTerc,@pmIdVend,@pmIdClase,@pmNumCuenta,@pmIdBanco,@pmNomAgencia,@pmDirAgencia,@pmIdLocalAge,@pmNitRepLeg,@pmNomRepLeg,@pmNitContac,@pmNomContac,@pmTelContac,@pmemlContac,@pmCargContac,@pmEsExento
,@pmCalcIntMora,@pmDiasGracia,@pmFecIngreso,@pmFecVigencia,@pmFecRetiro,@pmVrSalBasico,@pmMaxDcto,@pmTipoSalario,@pmMatMerc,@pmFecMat,@pmComentarios,@pmIdEstado,@pmInactivo,@pmFechaCrea,@pmIdUsuario,@pmNumTarj,@pmPwdTarj,@pmFecVenceTc,@pmIncRetFte)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpTercCliePrestamo] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmNitRepLeg VARCHAR(16),@pmNomRepLeg VARCHAR(150),@pmNitContac VARCHAR(16),@pmNomContac VARCHAR(150),@pmTelContac VARCHAR(20),@pmemlContac VARCHAR(100),@pmCargContac VARCHAR(50)
,@pmIdSzona VARCHAR(4),@pmIdGrupo VARCHAR(4),@pmIdTipoTerc VARCHAR(4),@pmIdVend VARCHAR(16),@pmIdClase VARCHAR(4),@pmNumCuenta VARCHAR(30),@pmIdBanco VARCHAR(4),@pmEsExento BIT,@pmCalcIntMora BIT,@pmDiasGracia INT,@pmFecIngreso SMALLDATETIME,@pmFecVigencia SMALLDATETIME
,@pmFecRetiro SMALLDATETIME,@pmVrSalBasico MONEY,@pmMaxDcto DECIMAL(14,4),@pmMatMerc VARCHAR(20),@pmFecMat SMALLDATETIME,@pmNomAgencia VARCHAR(150),@pmDirAgencia VARCHAR(250),@pmIdLocalAge VARCHAR(8),@pmComentarios VARCHAR(2000),@pmIdEstado VARCHAR(4)
,@pmInactivo BIT,@pmTipoSalario CHAR(1),@pmNumTarj VARCHAR(30),@pmPwdTarj VARCHAR(30),@pmFecVenceTc SMALLDATETIME,@pmIncRetFte BIT,@pmFechaAct SMALLDATETIME
 AS 
 UPDATE TercCliePrestamo SET NitRepLeg=@pmNitRepLeg,NomRepLeg=@pmNomRepLeg,NitContac=@pmNitContac,NomContac=@pmNomContac,TelContac=@pmTelContac,emlContac=@pmemlContac,CargContac=@pmCargContac,IdSzona=@pmIdSzona,IdGrupo=@pmIdGrupo,IdTipoTerc=@pmIdTipoTerc,IdVend=@pmIdVend
,IdClase=@pmIdClase,NumCuenta=@pmNumCuenta,IdBanco=@pmIdBanco,EsExento=@pmEsExento,CalcIntMora=@pmCalcIntMora,DiasGracia=@pmDiasGracia,FecIngreso=@pmFecIngreso,FecVigencia=@pmFecVigencia,FecRetiro=@pmFecRetiro,VrSalBasico=@pmVrSalBasico,MaxDcto=@pmMaxDcto,MatMerc=@pmMatMerc,FecMat=@pmFecMat
,Comentarios=@pmComentarios,IdEstado=@pmIdEstado,NomAgencia=@pmNomAgencia,DirAgencia=@pmDirAgencia,IdLocalAge=@pmIdLocalAge,Inactivo=@pmInactivo,FechaAct=@pmFechaAct,TipoSalario=@pmTipoSalario,NumTarj=@pmNumTarj,PwdTarj=@pmPwdTarj,FecVenceTc=@pmFecVenceTc,IncRetFte=@pmIncRetFte
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsTercCliePrestamo_Sel] @pmIdClie VARCHAR(16),@pmNewIdClie VARCHAR(16)
 AS 
 IF EXISTS (SELECT IdClie FROM  TercCliePrestamo WHERE  IdClie=@pmIdClie)
  INSERT INTO TercCliePrestamo (IdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
 ,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc,IncRetFte)
 SELECT @pmNewIdClie,IdAgencia,IdSzona,IdGrupo,IdTipoTerc,IdVend,IdClase,NumCuenta,IdBanco,NomAgencia,DirAgencia,IdLocalAge,NitRepLeg,NomRepLeg,NitContac,NomContac,TelContac,emlContac,CargContac,EsExento,CalcIntMora,DiasGracia,FecIngreso,FecVigencia,FecRetiro,VrSalBasico
 ,MaxDcto,TipoSalario,MatMerc,FecMat,Comentarios,IdEstado,Inactivo,FechaCrea,IdUsuario,NumTarj,PwdTarj,FecVenceTc,IncRetFte
 FROM TercCliePrestamo WHERE IdClie=@pmIdClie


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsFinPagos] @pmTipDoc VARCHAR(3),@pmIdPrestamo INT,@pmIdCia CHAR(2),@pmItem INT,@pmNumero VARCHAR(20),@pmFecha SMALLDATETIME,@pmFecPago SMALLDATETIME,@pmVrCuota MONEY
,@pmEdoForma INT,@pmCdBanco VARCHAR(4),@pmCtaCheque VARCHAR(30),@pmDetalle VARCHAR(150),@pmTipRec VARCHAR(3),@pmRecibo INT,@pmIdCiaRec CHAR(2),@pmVrLiqMora MONEY,@pmFecLiqMora SMALLDATETIME
,@pmVrRecCaja MONEY,@pmTarifSeguro DECIMAL(14,4),@pmTotalSeguro DECIMAL(14,4)
 AS 
 INSERT INTO Trn_FinPagos (TipDoc,IdPrestamo,IdCia,Item,Numero,Fecha,FecPago,VrCuota,EdoForma,CdBanco,CtaCheque,Detalle,TipRec,Recibo,IdCiaRec,VrLiqMora,FecLiqMora,VrRecCaja,TarifSeguro,TotalSeguro)
 VALUES (@pmTipDoc,@pmIdPrestamo,@pmIdCia,@pmItem,@pmNumero,@pmFecha,@pmFecPago,@pmVrCuota,@pmEdoForma,@pmCdBanco,@pmCtaCheque
,@pmDetalle,@pmTipRec,@pmRecibo,@pmIdCiaRec,@pmVrLiqMora,@pmFecLiqMora,@pmVrRecCaja,@pmTarifSeguro,@pmTotalSeguro)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paInsLineasCredCue] @pmIdLinea VARCHAR(4),@pmIdTipoTerc VARCHAR(4),@pmConcepto VARCHAR(10),@pmIdCuentaDb VARCHAR(16),@pmIdCuentaCr VARCHAR(16)
 AS 
 INSERT INTO LineasCredCue (IdLinea,IdTipoTerc,Concepto,IdCuentaDb,IdCuentaCr)
 VALUES (@pmIdLinea,@pmIdTipoTerc,@pmConcepto,@pmIdCuentaDb,@pmIdCuentaCr)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paUpLineasCredCue] @pmIdLinea VARCHAR(4),@pmIdTipoTerc VARCHAR(4),@pmConcepto VARCHAR(10),@pmIdCuentaDb VARCHAR(16),@pmIdCuentaCr VARCHAR(16)
 AS 
 UPDATE LineasCredCue SET IdCuentaDb=@pmIdCuentaDb,IdCuentaCr=@pmIdCuentaCr
 WHERE IdLinea=@pmIdLinea AND IdTipoTerc=@pmIdTipoTerc AND Concepto=@pmConcepto

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paUpTercClieLineas] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmIdTasa VARCHAR(4),@pmnPlazo INT,@pmTipoPlazo CHAR(1),@pmCupoCredito MONEY,@pmSaldoCapital MONEY,@pmSaldoInteres MONEY,@pmMaxCupoSmlv DECIMAL(14,4),@pmTipoCausac VARCHAR(10),@pmDiasVence VARCHAR(20)
,@pmFecPreAprob SMALLDATETIME,@pmPlazoAprob INT,@pmEtdoAprob INT,@pmTipoAprob VARCHAR(20),@pmDescAprob VARCHAR(250),@pmGarantias BIT,@pmTipoGarantia VARCHAR(10),@pmDescGarantia VARCHAR(250),@pmVrGarantia MONEY,@pmReqCheques BIT,@pmFecActCupo SMALLDATETIME,@pmEstadoAct INT,@pmLiqSeguro BIT
,@pmCdTasaConv VARCHAR(4),@pmNitConvenio VARCHAR(16),@pmCuotasConv INT,@pmCdTarifSeguro VARCHAR(4),@pmFechaAct SMALLDATETIME
 AS 
 UPDATE TercClieLineas SET IdTasa=@pmIdTasa,nPlazo=@pmnPlazo,TipoPlazo=@pmTipoPlazo,CupoCredito=@pmCupoCredito,SaldoCapital=@pmSaldoCapital,SaldoInteres=@pmSaldoInteres,MaxCupoSmlv=@pmMaxCupoSmlv,TipoCausac=@pmTipoCausac,DiasVence=@pmDiasVence,FecPreAprob=@pmFecPreAprob,PlazoAprob=@pmPlazoAprob,EtdoAprob=@pmEtdoAprob
,TipoAprob=@pmTipoAprob,DescAprob=@pmDescAprob,Garantias=@pmGarantias,TipoGarantia=@pmTipoGarantia,DescGarantia=@pmDescGarantia,VrGarantia=@pmVrGarantia,ReqCheques=@pmReqCheques,FecActCupo=@pmFecActCupo,EstadoAct=@pmEstadoAct,LiqSeguro=@pmLiqSeguro,CdTasaConv=@pmCdTasaConv
,NitConvenio=@pmNitConvenio,CuotasConv=@pmCuotasConv,FechaAct=@pmFechaAct,CdTarifSeguro=@pmCdTarifSeguro
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia AND IdLinea=@pmIdLinea

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paQryTercClieLineas] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4)
 AS 
 SELECT IdClie,IdAgencia,IdLinea,IdTasa,nPlazo,TipoPlazo,CupoCredito,SaldoCapital,SaldoInteres,MaxCupoSmlv,TipoCausac,DiasVence,LiqSeguro,FecPreAprob
,PlazoAprob,EtdoAprob,TipoAprob,DescAprob,Garantias,TipoGarantia,DescGarantia,VrGarantia,ReqCheques,FecActCupo
,CdTasaConv,NitConvenio,CuotasConv,CdTarifSeguro,EstadoAct,FechaCrea,FechaAct,IdUsuario
 FROM TercClieLineas
 WHERE IdClie=@pmIdClie AND IdAgencia=@pmIdAgencia AND IdLinea=@pmIdLinea

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[paInsTercClieLineas] @pmIdClie VARCHAR(16),@pmIdAgencia VARCHAR(16),@pmIdLinea VARCHAR(4),@pmIdTasa VARCHAR(4),@pmnPlazo INT,@pmTipoPlazo CHAR(1),@pmCupoCredito MONEY,@pmSaldoCapital MONEY,@pmSaldoInteres MONEY,@pmMaxCupoSmlv DECIMAL(14,4),@pmTipoCausac VARCHAR(10),@pmDiasVence VARCHAR(20)
,@pmFecPreAprob SMALLDATETIME,@pmPlazoAprob INT,@pmEtdoAprob INT,@pmTipoAprob VARCHAR(20),@pmDescAprob VARCHAR(250),@pmGarantias BIT,@pmTipoGarantia VARCHAR(10),@pmDescGarantia VARCHAR(250),@pmVrGarantia MONEY,@pmReqCheques BIT,@pmFecActCupo SMALLDATETIME,@pmEstadoAct INT
,@pmLiqSeguro BIT,@pmCdTasaConv VARCHAR(4),@pmNitConvenio VARCHAR(16),@pmCuotasConv INT,@pmCdTarifSeguro VARCHAR(4),@pmFechaCrea SMALLDATETIME,@pmIdUsuario VARCHAR(11)
 AS 
 INSERT INTO TercClieLineas (IdClie,IdAgencia,IdLinea,IdTasa,nPlazo,TipoPlazo,CupoCredito,SaldoCapital,SaldoInteres,MaxCupoSmlv,TipoCausac,DiasVence,LiqSeguro,FecPreAprob,PlazoAprob,EtdoAprob,TipoAprob,DescAprob,Garantias,TipoGarantia,DescGarantia,VrGarantia,ReqCheques,FecActCupo,CdTasaConv,NitConvenio,CuotasConv,EstadoAct,FechaCrea,IdUsuario,CdTarifSeguro)
 VALUES (@pmIdClie,@pmIdAgencia,@pmIdLinea,@pmIdTasa,@pmnPlazo,@pmTipoPlazo,@pmCupoCredito,@pmSaldoCapital,@pmSaldoInteres,@pmMaxCupoSmlv,@pmTipoCausac,@pmDiasVence,@pmLiqSeguro,@pmFecPreAprob,@pmPlazoAprob,@pmEtdoAprob,@pmTipoAprob,@pmDescAprob,@pmGarantias,@pmTipoGarantia,@pmDescGarantia,@pmVrGarantia
,@pmReqCheques,@pmFecActCupo,@pmCdTasaConv,@pmNitConvenio,@pmCuotasConv,@pmEstadoAct,@pmFechaCrea,@pmIdUsuario,@pmCdTarifSeguro)

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[paQryTraManifiestoOpe] @pmFechaIni SMALLDATETIME,@pmFechaFin SMALLDATETIME,@pmIdCia CHAR(2)=Null
,@pmIdVehiculo VARCHAR(10)=Null,@pmIdPoseedor VARCHAR(16)=Null
 AS 
 SELECT RM.TipDoc AS TipMuc,RM.Manifiesto AS NumManif,RM.IdCia AS CdCia,Compania,M.Fecha AS MucFecha,M.FecDespacho AS MucFecDespacho
,M.IdVehiculo AS PlacaVeh,M.TipoAfiVehic AS TipoAfiVeh,M.IdConductor AS CedCondutor,CDT.RazonSocial AS NomConductor,M.IdPoseedor AS NitPoseedor,NP.RazonSocial AS NomPoseedor,M.nRemolque AS NumRemolque
,RM.TipRem AS TipRem,RM.Remesa AS NumRemesa,RM.IdCiaRem AS CdCiaRem,R.Fecha AS FechaRem,IdMercancia,DescripMcias,D.Cantidad AS Cant,D.PesoNeto AS PesoNetoRem
,D.UndMed AS CdUmPeso,UMP.Unidad AS UmPeso,D.Volumen AS VolumenRem,UndVol,Cases,Cajas,Palets,D.TarifClie AS TarifaClie,UndTarifa,RM.TarifPago AS TarifaPago,RM.TarifTabla AS TarifaTabla,UndTarifPago
,Remision,DocCliente,Referencia1,Referencia2,Referencia3,D.IdEmp AS CdEmp,Empaque,D.IdUnd AS CdUnd,UM.Unidad AS UndMedida
,D.IdOrigen AS CdCiuOrigen,LO.Localidad AS CiuOrigen,LO.IdDep AS CodDepOrigen,DPO.Departamento AS DptoOrigen,D.IdDestino AS CdCiuDestino,LD.Localidad AS CiuDestino,LD.IdDep AS CodDepDestino,DPD.Departamento AS DptoDestino
,M.IdRuta AS CdRuta,T.TipoId AS ClieTipId,IdCliente,T.Dv AS ClieDv,T.RazonSocial AS NomCliente,R.IdAgencia AS CdAgencia,Agencia,R.Modalidad AS TipoRemesa,NitRemite,Remitente,NitDestntario,Destinatario
,M.VrFletes AS MucVrFletes,dbo.FuncMucListaAntcipos(RM.Manifiesto,RM.IdCia) AS Anticipos,M.VrAnticipo+VrAntAdic AS VrAnticipos,M.VrRetencion AS MucVrRetencion,M.VrReteIca AS MucVrReteIca,M.Observacion AS MucObserv,M.Anulado AS MucAnulado,EstOrden
--datos ordenes de pago
,M.TipOdp AS CdTipOdp,M.OrdPago AS NumOrden,M.IdCiaOdp AS MucCiaOdp,M.FechaOdp AS FecOrden,OP.PesoTotal AS OdpPesoNeto,OP.Unidades AS OdpCantidad,OP.Volumen AS OdpVolumen
,OP.TarifaTabla AS OdpTarifTabla,OP.TarifaFlete AS OdpTarifFlete,OP.UnidTarifa AS OdpUndTarifa,OP.VrTotalFletes AS OdpTotalFletes,OP.VrDescuento AS OdpVrDcto,OP.VrRetencion AS OdpVrRetFte
,OP.VrReteIca AS OdpReteIca,OP.VrAnticipos AS OdpAnticipos,OP.VrFaltantes AS OdpFaltantes,VrSeguros,VrFondos,VrAportes,VrOtrosDctos,VrImpuestos,VrEstampilla,VrOtrosPagos,OP.VrNeto AS OdpVrNetoPagar,ODP.Observacion AS OdpObserv
--egresos
,TipoEgr,nEgreso,CdCiaEgr,FecEgreso,EG.NumCheque,TotalEgresos
--datos remesas
,R.NumPedido AS nPedido,IdCiaPed,FechaPed,R.TipDcm AS TipOdc,R.NumDocmto AS nOCargue,R.IdCiaDcm AS CdCiaOdc,R.FechaDcm AS FecOCargue,R.Observacion AS RemObserv
--Datos del vehic/ poseedor
,NP.TipoId AS TercTipId,NP.Dv AS TercDv,NP.NomCial AS TercNomCial,NP.Direccion AS TercDireccion,NP.IdLocal AS TercCodCiudad,L.Localidad AS NomCiudad,NP.Telefono AS TercTelefono,NP.e_mail AS TercEmail
,NumVeh,V.IdTipoVeh AS CdTipVeh,TipoVehiculo,V.IdMarca AS CdMarca,MV.Marca AS MarcaVeh,V.IdColor AS CdColor,NomColor,V.IdCrceria AS CdCarr,TipoCar,Modelo,Config
,PesoVacio,PesoMax,NumMotor,SerieChasis,NumSerie,NitEmpresa,NE.RazonSocial AS VehNomEmpresa,V.IdPpd AS CdTipProp,TipoProp,VehPropio,TipoAfil,NumSoat,FecSoat,VigSoat
,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,CDT.TelMovil AS CdtCelular
 FROM Trn_TraManifRem AS RM INNER JOIN Trn_TraManifiesto AS M ON RM.TipDoc=M.TipDoc AND RM.Manifiesto=M.Manifiesto AND RM.IdCia=M.IdCia
 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
 INNER JOIN Trn_TraRemesa AS R ON RM.TipRem=R.TipDoc AND RM.Remesa=R.NumOrden AND RM.IdCiaRem=R.IdCia
 INNER JOIN Companias AS CN ON RM.IdCia=CN.IdCia 
 INNER JOIN Localidades AS LO ON D.IdOrigen=LO.IdLocal INNER JOIN Departamentos AS DPO ON LO.IdDep=DPO.IdDep
 INNER JOIN Localidades AS LD ON D.IdDestino=LD.IdLocal INNER JOIN Departamentos AS DPD ON LD.IdDep=DPD.IdDep
 INNER JOIN UndMed AS UM ON D.IdUnd=UM.IdUnd INNER JOIN Terceros AS T ON R.IdCliente=T.IdTercero
 INNER JOIN Terceros AS CDT ON M.IdConductor=CDT.IdTercero INNER JOIN Terceros AS NP ON M.IdPoseedor=NP.IdTercero 
 INNER JOIN Localidades AS L ON NP.IdLocal=L.IdLocal 
 INNER JOIN Vehiculos AS V ON M.IdVehiculo=V.IdVehiculo INNER JOIN TiposVeh AS TV ON V.IdTipoVeh=TV.IdTipoVeh 
 INNER JOIN Marcas AS MV ON V.IdMarca=MV.IdMarca INNER JOIN 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 Trn_TraOrdenPago AS ODP ON M.TipOdp=ODP.TipDoc AND M.OrdPago=ODP.OrdPago AND M.IdCiaOdp=ODP.IdCia
 LEFT JOIN Trn_TraOrdenManif AS OP ON M.TipOdp=OP.TipDoc AND M.OrdPago=OP.OrdPago AND M.IdCiaOdp=OP.IdCia
 LEFT JOIN Agencias AS A ON R.IdAgencia=A.IdAgencia LEFT JOIN Sys_Um AS UMP ON D.UndMed=UMP.UndMed 
 LEFT JOIN Empaques AS EQ ON D.IdEmp=EQ.IdEmp
 LEFT JOIN TercCndtores AS CT ON M.IdConductor=CT.IdConductor LEFT JOIN ExpLicencias AS EL ON CT.IdLugar=EL.IdLugar
 --subconsultas para ultimo egresos 
 LEFT JOIN (SELECT TOP 1 EO.TipCom AS TipoEgr,EO.NumEgreso AS nEgreso,EO.IdCia AS CdCiaEgr,E.Fecha AS FecEgreso,NumCheque,FecCheque,VrAbonado,EO.TipOdp AS TipOrden,EO.OrdPago AS NumOPago,EO.IdCiaOdp AS CdCiaOpago
 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 ORDER BY EO.NumEgreso DESC) AS EG ON M.TipOdp=EG.TipOrden AND M.OrdPago=EG.NumOPago AND M.IdCiaOdp=EG.CdCiaOpago
 --total de egresos
 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 M.TipOdp=EGT.TipOrden AND M.OrdPago=EGT.NumOPago AND M.IdCiaOdp=EGT.CdCiaOpago
WHERE RM.TipDoc='MUC' AND M.Fecha BETWEEN @pmFechaIni AND @pmFechaFin AND M.IdCia LIKE ISNULL(@pmIdCia,'%%')
AND  M.IdVehiculo LIKE ISNULL(@pmIdVehiculo,'%') AND M.IdPoseedor LIKE ISNULL(@pmIdPoseedor,'%')