INSERT INTO Sys_ObjetosApp (IdObj,IdGrupo,SubModulo,IndObj,Nombre,Formulario,Permisos,NivelMinimo,NomArchivo)
 VALUES ('FRMLABOP','PREINF','GEN',8,'Cancelaciones de Vencimientos','FRMLABOP','S',0,'')
GO
INSERT INTO Sys_Report  (TipoRep,IdRep,Descripcion,Formato,TipoPapel,Orientacion,VistaPrevia,VerSetup,NumCopias,sp_Nombre,Ind_Location,NomRep)
 VALUES ('LAP','01','Predeterminado','CrAboCuo.rpt',1,1,1,0,1,'paQryFinCuotasAbo','','Cancelaciones de vencimientos prestamos')
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryFinCuotasAbo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[paQryFinCuotasAbo]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE paQryFinCuotasAbo @pmTipDoc VARCHAR(3)=Null,@pmIdCia CHAR(2)=Null,@pmIdCliente VARCHAR(16)=Null
 AS 
 SELECT C.TipDoc AS CdTipDoc,C.IdPrestamo AS NumDcmto,C.IdCia AS CdCia,CI.Compania AS NomCiaDoc,C.Item AS ItemDcmto,NumCuota,C.Fecha AS FecEmision,FechaVence
,Concepto,VrTotal,VrAbonado,VrTotal-VrAbonado AS VrSaldo,C.IdCliente AS NitCliente,T.RazonSocial AS NomCliente,C.IdAgencia AS CdAgencia,NomAgencia AS Agencia
,C.Detalle AS DetalleCuota,TipoCuota,C.TipoCausac AS TipoCau,NumCausacion,IdCiaCau,TipoTasa,DTF_EA,TasaNom,Spread_TA,TasaEfe,FecUltCausac,DiasLiquida,NumFactura,VrInteresFact,VrAcumCapital
,CodLinCred,LinCredito,Convenio,NitConvenio,NumPreMora,FecUltPago,FecLiqMora,FacturaInt,TipFacInt,NumFacInt,CdCiaFacInt,FechaFacInt,C.FechaCrea AS Fecha_Crea
--Datos del prestamo
,FecPrestamo,VrPrestamo,VrSeguro,VrCuota,NPlazos,Causacion,P.NContrato AS NumContrato,CdCiaCon,NActaJunta,NumCredito,P.IdVend AS CdAsesor,VN.RazonSocial AS Asesor
,TipoCredito,P.TipoCausac AS PreTipoCausac,AcumCapital,AcumIntereses,AcumIntMora,AcumCuotas,PagosCapital,PagosIntereses,PagosIntMora,CuotasPagadas,CuotasMora,P.Observacion AS Observ
,P.Anulado AS PreAnulado,P.IdEstado AS CdEstado
--datos de pagos
,TipRec,Recibo,A.IdCia AS CdCiaAbo,CA.Compania AS NomCiaAbono,A.Item AS ItemAbo,A.Fecha AS FecAbono,A.FecPago AS Fec_Pago,TotalAbono,A.Detalle AS DetalleAbono,TipoAplica
,A.IdVend AS NitVend,VNC.RazonSocial AS NomVendedor,VrBaseCms,Comision 
--Datos del tercero
,T.TipoId AS TercTipo,T.Dv AS TercDv,T.Codigo AS TercCodigo,T.Direccion AS TercDireccion,T.IdLocal AS TercCdCiudad,L.Localidad AS NomCiudad
,L.IdDep AS CdDep,DP.Departamento AS TercDpto,T.Telefono AS TercTelefono,T.e_mail AS TercEmail
,CLI.IdSzona AS CdSubzona,Subzona,SZ.IdZona AS CdZona,Zona,CLI.IdGrupo AS CdGrupoCli,GrupoClie,CLI.IdTipoTerc AS CdTipoTerc,TipoTercero,Comentarios
,TipoSalario,MaxDcto,VrSalBasico
,DirAgencia,CLI.IdLocalAge AS AgeIdCiudad,LA.Localidad AS AgeCiudad,LA.IdDep AS AgeCodDep,DA.Departamento AS AgeDpto
 FROM Trn_FinCuotas AS C INNER JOIN Companias AS CI ON C.IdCia=CI.IdCia INNER JOIN Terceros AS T ON C.IdCliente=T.IdTercero
 INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal  INNER JOIN Departamentos AS DP ON L.IdDep=DP.IdDep 
 INNER JOIN  RegimenDian AS RG ON T.IdRegimen=RG.IdRegimen INNER JOIN SectoresEco AS SE ON T.IdSector=SE.IdSector
 INNER JOIN TercCliePrestamo AS CLI ON C.IdCliente=CLI.IdClie AND C.IdAgencia=CLI.IdAgencia
 INNER JOIN Subzonas AS SZ ON CLI.IdSzona=SZ.IdSzona INNER JOIN Zonas AS Z ON SZ.IdZona=Z.IdZona
 INNER JOIN GruposCli AS GC ON CLI.IdGrupo=GC.IdGrupo INNER JOIN TiposTerc AS TTC ON CLI.IdTipoTerc=TTC.IdTipoTerc
 LEFT JOIN LineasCred AS LC ON C.CodLinCred=LC.IdLinea
 LEFT JOIN Trn_FinAbonos AS A ON C.TipDoc=A.TipDoc AND C.IdPrestamo=A.IdPrestamo AND C.IdCia=A.IdCiaPre AND C.Item=A.ItemPre
 LEFT JOIN Terceros AS VNC ON A.IdVend=VNC.IdTercero LEFT JOIN Companias AS CA ON A.IdCia=CA.IdCia
 LEFT JOIN Trn_FinPrestamo AS P ON C.TipDoc=P.TipDoc AND C.IdPrestamo=P.IdPrestamo AND C.IdCia=P.IdCia
 LEFT JOIN Terceros AS VN ON P.IdVend=VN.IdTercero 
 LEFT JOIN Localidades AS LA ON CLI.IdLocalAge=LA.IdLocal  LEFT JOIN Departamentos AS DA ON LA.IdDep=DA.IdDep 
 WHERE C.TipDoc LIKE ISNULL(@pmTipDoc,'%')   AND C.IdCia LIKE ISNULL(@pmIdCia,'%%')  AND C.IdCliente LIKE ISNULL(@pmIdCliente,'%')
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO