SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[paQrytm_PreExtoLta] @pmtmEst CHAR(2),@pmFecCorte SMALLDATETIME
AS
SELECT tmIdCliente,T.RazonSocial AS NomCliente,tmIdAgencia,NomAgencia AS Agencia,tmFecha,tmFecVence,tmTipDoc,TipoDoc,tmNumero,tmIdCia,Compania,tmItem,tmCargos,tmAbonos
,tmIdLinea,LinCredito,CupoCredito,tmConcepto,tmDetalle,tmReferencia,tmCredito,tmVrPrestamo,tmVrCuota,tmTotAbonos,tmTipoAbono,tmNCuota,tmIdVend,VN.RazonSocial AS NomVendedor
,tmTipRef,tmDocRef,tmIdCiaRef,tmTipCom,tmComprobante,tmIdCiaCom,tmAnulado,tmNumDev,tmFecDev,tmDTF_EA,tmTasaNom,tmTasaEfe
,tmSanClie,tmSanCia,tmSanAgenc,tmSanAgeCia,tmSanLinea,tmSanLineaCia,tmSanConc,tmSanConcCia,IdReg,tmTimeSys
,NumCredito,NPlazos,Causacion,FecPrestamo,TipoCredito,P.Observacion AS Observ
--Total pagos a capital
,ISNULL(CASE tmTipDoc WHEN 'PR1' THEN (SELECT SUM(Trn_FinAbonos.TotalAbono) FROM Trn_FinAbonos INNER JOIN Trn_FinCuotas ON Trn_FinAbonos.TipDoc=Trn_FinCuotas.TipDoc
 AND Trn_FinAbonos.IdPrestamo=Trn_FinCuotas.IdPrestamo AND Trn_FinAbonos.IdCiaPre=Trn_FinCuotas.IdCia AND Trn_FinAbonos.ItemPre=Trn_FinCuotas.Item
 WHERE Trn_FinAbonos.TipDoc=EX.tmTipDoc AND Trn_FinAbonos.IdPrestamo=EX.tmNumero AND Trn_FinAbonos.IdCiaPre=EX.tmIdCia
 AND Trn_FinAbonos.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='CAPITAL') ELSE 0 END,0) AS VrPagosCapital
,ISNULL(CASE tmTipDoc WHEN 'PR1' THEN (SELECT COUNT(NumCuota) FROM Trn_FinCuotas WHERE Trn_FinCuotas.TipDoc=EX.tmTipDoc AND Trn_FinCuotas.IdPrestamo=EX.tmNumero AND Trn_FinCuotas.IdCia=EX.tmIdCia
 AND Trn_FinCuotas.Fecha<=@pmFecCorte AND Trn_FinCuotas.Concepto='CAPITAL') ELSE 0 END,0) AS CuotasCausadas
--datos del cliente
,T.TipoId AS ClieTipoId,T.Dv AS ClieDv,T.Direccion AS ClieDireccion,T.IdLocal AS ClieCdCiudad,L.Localidad AS NomCiudad,L.IdDep AS CdDep,DP.Departamento AS ClieDpto
,T.Telefono AS ClieTelefono,T.Fax AS ClieFax,T.e_mail AS ClieEmail,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,EsExento
FROM tm_PreExto AS EX INNER JOIN Terceros AS T ON EX.tmIdCliente=T.IdTercero INNER JOIN  Companias AS CI ON EX.tmIdCia=CI.IdCia
INNER JOIN LineasCred AS LC ON EX.tmIdLinea=LC.IdLinea
INNER JOIN  TercCliePrestamo AS CLI ON EX.tmIdCliente=CLI.IdClie AND EX.tmIdAgencia=CLI.IdAgencia
INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo  INNER JOIN  TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
LEFT JOIN Terceros AS VN ON EX.tmIdVend=VN.IdTercero
LEFT JOIN TercClieLineas AS CL ON EX.tmIdCliente=CL.IdClie AND EX.tmIdAgencia=CL.IdAgencia AND EX.tmIdLinea=CL.IdLinea
LEFT JOIN Sys_TiposDoc AS TD ON EX.tmTipDoc=TD.IdDoc 
LEFT JOIN Trn_FinPrestamo AS P ON EX.tmTipDoc=P.TipDoc AND EX.tmNumero=P.IdPrestamo AND EX.tmIdCia=P.IdCia
WHERE tmEst=@pmtmEst
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO